Telegram bots on PostgreSQL alone: Sponsor Bot's queues, forced-join gate and TRON payments
How Sponsor Bot runs many Telegram bots on one PostgreSQL database, no Redis: inbox, outbox and leases, a forced-join gate, TRON payment checks, and a load test to 200 updates a second.
Ramin ValadanProved GitHub account and website, confirmed e-mail: checked by UpFork·17 min read··Updated
A self-hosted Telegram uploader bot: content links ask visitors to join your sponsor channels first. Sponsor campaigns with reports, an ad shop paid in USDT or TRX on TRON, statistics, broadcasts and a team, in one admin panel in English and Persian. One-script install.
Sponsor Bot is a self-hosted platform for Telegram "uploader" bots. An owner connects any number of bots to one panel. A visitor opens a content link, and the bot sends the file only after they have joined the sponsor channels: the forced-join gate. Around that: sponsor campaigns with their own invite links, an ad shop where advertisers book slots inside the bot and pay in USDT or TRX on TRON, broadcasts, statistics, a team with roles, and a panel in English and Persian. It is ASP.NET Core 10 and PostgreSQL behind a Next.js panel, installed with Docker Compose.
Try it live:sponsorbot-demo.upfork.dev runs the real panel on a simulated Telegram, with a month of made-up bots, visitors, campaigns and ad orders. The demo account's sign-in is shown on its sign-in page.
This article is about the engine underneath, for anyone (person or AI) building a Telegram bot that has to stay up under real traffic: how many bots run from one database with no Redis, how updates stay in order per person while workers handle hundreds at once, what a load test changed before 200 updates a second held, how the gate asks Telegram about membership, and how a payment is verified on TRON without the bot ever holding a key. Each lesson sits beside the part where it was learned.
One database, no Redis
Every moving part is a table in PostgreSQL:
Telegram ──webhook / long polling──▶ inbox (one row per update, deduplicated by update_id) │ workers claim rows: in order per person, in parallel across people ▼ UpdateHandler, one transaction: the person, the gate, the content, statistics events, and the replies… │ ▼ outbox (one queue per bot) │ one sender per bot, holding the bot's lease ▼ Telegram
The same binary runs as --role api (the panel's API and the webhooks) or --role worker, and any number of workers can run side by side. Queues are claimed with FOR UPDATE SKIP LOCKED, exclusive jobs (polling a bot, sending its messages) are held as leases, and LISTEN/NOTIFY wakes whoever waits.
Why not Redis: the buyer runs this on one small server, and one service fewer is one thing fewer to break. It also gives a guarantee a separate queue can't: an update's writes and the replies it queues commit in the same transaction, so a crash never leaves a person recorded without their answer, or an answer sent for something that rolled back.
The inbox: in order per person, in parallel across people
Two updates from the same person must be handled one after the other (a /start and then a tap on "I've joined"), but different people shouldn't wait for each other. One query claims work for a worker:
Read it in three steps. due takes the oldest waiting updates, plus any whose worker died with them in hand (their two-minute lock ran out). candidates keeps the earliest update of each person, and only if that person's previous update isn't still being handled. locked takes the rows no other worker has locked. A failed update goes back with a growing delay and, after its last attempt, is set aside as dead instead of blocking the person forever.
Each worker handles up to 32 updates at once and asks for more as soon as a quarter of its slots are free, so a slow update never holds up the others.
Leases: exactly one sender per bot
Telegram limits each bot to about 30 messages a second. A counter shared between workers would be one more round trip per message, so instead exactly one worker sends for each bot, holding a lease in a table:
The statement takes the lease if it is free or expired, and renews it if this process already holds it; nothing comes back if someone else does. Leases last 30 seconds and are renewed well before then, so a worker that dies hands its bots to another within 30 seconds. Polling a bot (poll:{bot}) uses the same mechanism. Because one process owns a bot's sending, the rate limit lives in memory: 25 messages a second per bot (under Telegram's 30) and 3 a second to any one chat.
The outbox: Telegram's limits, and four chats at once
The sender reads its bot's outbox in priority order (replies before broadcasts) and sends each chat's messages in order, several chats at once:
A 429 with retry_after stops the batch and waits as long as Telegram asked; what wasn't sent stays queued. A 401 marks the token invalid and stops the sender. A 403 (the person blocked the bot) fails that message and marks the person blocked. A 400 message is not modified (an edit that changes nothing) counts as done.
The HTTP client for the Bot API is set up once:
Waking the workers: LISTEN/NOTIFY, gathered
Workers shouldn't poll an empty table all day, so a new inbox or outbox row sends pg_notify, and each process keeps one LISTEN connection. Waiters in the same process are woken at once; the notifications to other processes are gathered for 5 ms and sent together:
Everyone still looks on their own every second or two, so a lost notification only costs a little latency.
What the load test changed in PostgreSQL
The load test (below) found three more settings, each measured on its own.
Each update's transaction lasts as long as its calls to Telegram, and every row it writes (the person, the gate, the outbox row, a delivery) points at a parent: the bot, the content.
Tried and left out: workstation garbage collection (no difference), and synchronous_commit = off, which cut PostgreSQL's CPU by 13% and halved answer times, but a crash could lose updates Telegram had already been told were received.
The load test
scripts/load-test.ps1 starts an api and N workers against the fake Telegram with a set round trip per call, then sets up 10 bots in webhook mode, two gate channels each and a video per bot, through the same API an owner uses. Made-up people then open content links at a steady rate (open loop: the rate doesn't slow down when the bot does), and every one is shown the gate: two membership checks and a gate message. Afterwards the database says when each update arrived, was handled, and was answered. The machine: a 4-core Intel Core i7-1065G7 laptop with 7.7 GB, running PostgreSQL 18 and the load generator too.
Setup (10 bots)
Offered
Handled
Answer sent: p50 / p95 / p99
api + 1 worker, Telegram 40 ms away
200/s
200/s
0.25 s / 0.68 s / 1.2 s
api + 2 workers, Telegram 40 ms away
200/s
200/s
0.43 s / 1.1 s / 1.5 s
api + 2 workers, Telegram 80 ms away
200/s
200/s
1.0 s / 2.5 s / 4.0 s
api + 1 worker, Telegram 80 ms away
200/s
200/s, falling behind
0.35 s / 17 s / 22 s
At 300 a second over 10 bots each bot would have 30 answers a second to send, more than Telegram allows one bot, so those queue by design.
The forced-join gate
A content link is t.me/<bot>?start=<code>. When it is opened, the bot asks Telegram whether the visitor is in each of the gate's channels, all at once:
The rules behind it:
"Joined" is remembered for 5 minutes in the worker's memory and dropped as soon as a chat_member update says the person left. "Not joined" is never remembered, so "I've joined" always asks again.
The bot must be an admin of every gate channel: getChatMember only answers reliably about other users for an admin. The panel checks the rights when a channel is added and keeps checking; a channel where the bot lost them reads Unknown, and the gate lets the visitor through rather than lock everyone out of a channel nobody can join.
Telegram unreachable throws instead, so the update is tried again later rather than letting everyone through.
The visitor is shown only the channels they're missing. The gate message shrinks as they join, deletes itself once they're through, and the content follows.
Sponsor campaigns credit joins through their own invite links: each campaign gets a named link from createChatInviteLink, and with chat_member in allowed_updates every join and leave in the channel arrives as an update, so each person is credited once and leaves are tracked.
Getting paid on TRON without holding a key
When the owner approves an ad order, the bot sends the shop's TRON address, a QR code and the amount. The advertiser pays from any wallet and sends the bot the transaction hash. A runner looks the hash up on TronGrid every few seconds and judges it:
Before this, a transaction the network doesn't know yet, or hasn't confirmed, waits (up to 15 minutes), and a failed one is turned down. The rules that matter:
Only the network's official USDT counts. USDT transfers are read from the event log of one contract per network (mainnet TR7NHqjeKQxGTCi8q8ZY4pL8otSzgjLj6t); any other token, including one named "USDT", is "no USDT".
It must reach the shop's address. Coins sent elsewhere are rejected, with the address they went to, so the owner can explain.
It must be sent after the order was approved (two minutes of clock skew allowed). The chain is public: without this rule anyone could send the hash of somebody else's earlier payment.
A hash pays one order once, checked against the database when it is credited. Amounts aren't a rule here: payments add up until they reach the total, and a part payment gets 48 hours for the rest, with reminders.
TRX is taken too, at a rate locked when the order is approved (from CoinGecko, then Kraken, then Binance, kept 5 minutes), because a USDT transfer's fee isn't worth paying for an ad under a dollar. Refunds are by hand: the bot never holds a wallet's private key, so it can see money arrive and never send any.
Small things that cost an afternoon
Shipping it to someone else's server
The release is a ZIP with install.sh (Docker, the .env, Caddy for HTTPS, a firewall rule, a setup code printed once) and update.sh. First-run setup asks for that code, because a new domain shows up in public certificate logs within minutes of getting a certificate, and an open setup page is a race. The install was rehearsed on a real server that was already running another site, then removed and compared with a snapshot taken before.
Use it
Everything the owner sees is in the panel: bots, content, channels and the gate, campaigns, the ad shop and payments, statistics, users, broadcasts, team and the audit log, in English or Persian. Open the live demo to click through all of it on a simulated Telegram. The release ships the full install guide and user guide (both also in Persian), and the buyer brings their own bot tokens, TronGrid key and server: Sponsor Bot ships with none.
Sponsor Bot's panel in English and Persian: logical sides, one rule for arrows, digits and dates, text that keeps its own direction, Telegram's bidi, and nine things that broke right to left.
Ramin ValadanProved GitHub account and website, confirmed e-mail: checked by UpFork·10 min read··AI
9 lessons
Behind the build
Behind the build · Daybook — Event Calendar & Scheduler for React, shadcn/ui & Tailwind (Drag & Drop)
How Daybook lays out overlapping events, moves them by mouse, touch and keyboard with dnd-kit, and eight traps we hit in zustand, Base UI, dnd-kit, the React Compiler lint and WCAG.
Ramin ValadanProved GitHub account and website, confirmed e-mail: checked by UpFork·7 min read··AI
8 lessons
Behind the build
Behind the build · Mailroom — Email Client & Inbox UI for React, shadcn/ui and Tailwind
How Mailroom builds a three-pane email client on shadcn/ui: one store per inbox, TipTap loaded in idle time, autosave that stops on send, and six traps from Radix, ProseMirror and the DOM.
Ramin ValadanProved GitHub account and website, confirmed e-mail: checked by UpFork·6 min read··AI
6 lessons
under load, a worker's claim that finished between another worker's two steps had already set the row to Processing; the second worker's lock step, which only said WHERE id IN (candidates), took it anyway. 24% more messages went out than people came in.
Why
after waiting for or skipping a lock, PostgreSQL rechecks only the conditions written in that same step, not the ones in an earlier CTE.
The fix
the locking step repeats the status conditions (status = 'Pending' AND next_attempt_at <= @now, or an expired Processing lock). Exactly one answer per update since.
Evidence
commit 7eecc9f (phase 12, "a race that let two workers handle the same update").
The fix
two partial indexes, ix_inbox_pending (id) WHERE status = 'Pending' and ix_inbox_in_hand (bot_id, chat_key) WHERE status = 'Processing', and each branch of due written as its own ordered, limited subquery with no join or OR in front of the ORDER BY. The claim takes 2 ms however long the queue.
Evidence
commit 7eecc9f and migration LoadTuning; measured with pg_stat_statements.
The fix
continuous processing with 32 slots (Worker:Concurrency): a semaphore hands out slots, and the claim runs whenever a quarter are free.
Evidence
commit 7eecc9f; docs/load-test.md, "What had to change", item 2.
The fix
each bot sends to 4 chats at once (Telegram:SendConcurrency), messages to the same chat still one after another and in order, all four sharing the bot's limiter. In every load-test run the busiest second of any bot was 26 to 29 messages: the limit held.
Evidence
commit 7eecc9f (a test checks that parallel sending keeps each chat in order).
the first live test showed IHttpClientFactory's default logging writing every request URL at Information level, and Bot API URLs are https://api.telegram.org/bot<token>/<method>: both test bots' tokens were in the dev log.
The fix
.RemoveAllLoggers() on the Telegram client (and on the TronGrid clients, whose key goes in a header), and System.Net.Http.HttpClient logs warnings only. A test captures every log line at every level and fails if a token appears; it fails without the fix. The exposed test tokens had to be revoked.
Evidence
commit d19801f.
The fix
each bot's sender calls getMe as it starts, opening the connection before anyone waits on it (a refused token is noticed there too), and idle connections stay open for 15 minutes. A test makes three update-to-reply round trips and wants each under 0.9 s.
Evidence
commit f56ddcc.
The fix
notifications are asked for only after the rows are committed, deduplicated, and sent together every 5 ms in one SELECT pg_notify(…), pg_notify(…) (Notifier).
Evidence
commit 7eecc9f; docs/load-test.md item 5.
PostgreSQL was parsing and planning them every time: about a third of its CPU under load.
The fix
the host adds Max Auto Prepare=64 to the connection string (and at most 50 connections a process; Docker Compose lets PostgreSQL take 200). PostgreSQL's CPU went from 2.2 to 1.4 cores at 100 updates a second.
Evidence
commit 7eecc9f (DatabaseConnection.WithDefaults).
on the first real-server install, every api and worker start wrote Error: libgssapi_krb5.so.2: cannot open shared object file to the log: Npgsql tries GSS encryption first by default, and the .NET Docker image has no Kerberos library.
The fix
the connection-string defaults set Gss Encryption Mode=Disable unless the owner's connection string says otherwise.
Evidence
commit 401e721.
The fix
the foreign keys every update writes to are DEFERRABLE INITIALLY DEFERRED, checked at commit, so the locks last milliseconds. EF Core can't express it, so it lives in a migration (LoadTuning) that any migration recreating those keys must repeat. Two workers went from 157 to 200 handled updates a second.
Evidence
commit 7eecc9f, migration LoadTuning.
The fix
every run warms up for 30 s at half the rate before measuring, and CPU is counted per process and for PostgreSQL apart; pg_stat_statements and sampling pg_stat_activity every 2 s found every database bottleneck.
Evidence
docs/load-test.md, "What a test does", step 3.
The fix
a channel is added by its @username, a link, a post link or a forwarded post, and checked live with getChat and getChatMember (the bot's own rights) before anything is saved. Channels the bot is added to later are still picked up from my_chat_member, and a watch job re-checks every channel's rights.
Evidence
the phase 2 live test written up in the build plan (fb68abe); phase 4, commit 99e4914.
a regular expression with a {6,16} quantifier in the SQL was read as a format placeholder: no error, and no rows matched.
The fix
keep braces out of raw SQL (or double them), and keep SQL built from code constants in a static readonly string; anything a request carries stays a parameter.
Evidence
phase 9, commit 4c8b408.
the entity's current value went back to its original value, so it read 0 again.
The fix
set the property's OriginalValue to the new value instead: the entity keeps it and EF sees nothing to write.
Evidence
phase 10, commit a442731.
The fix
the expiry goes inside the protected text and is checked against the app's own clock (the two-step sign-in challenge does this).
Evidence
phase 11, commit 0c3e538.
The fix
C#'s HourStart is "now minus the local minutes and seconds" in the workspace's zone, the same boundary PostgreSQL uses; the dashboard reads the roll-up up to it and the live events after it.
Evidence
phase 9, commit 4c8b408.
The fix
Telegram's HTML needs only &, <, > and " escaped; MessageTexts.Escape does just that.
Evidence
phase 3, commit fd4a358.
install.sh
grep
\r
The fix
scripts/release.sh runs git -c core.eol=lf archive …, and checks with tr -cd '\r' | wc -c, which does count carriage returns.
Evidence
commit 7eecc9f (scripts/release.sh).
on a shared server, /etc/docker/daemon.json sets "ip": "127.0.0.1" before Docker's first start, so ports publish on localhost only and the existing reverse proxy forwards to them. Docker and its builds also went into one capped systemd slice (CPUQuota=300%, MemoryMax=6G) so the test couldn't starve the site already running there.
Evidence
the real-server install recorded in the build plan, commit f345e4c.
WITH due AS ( (SELECT i.id, i.bot_id, i.chat_key FROM inbox i WHERE i.status = 'Pending' AND i.next_attempt_at <= @now AND i.bot_id = ANY(ARRAY(SELECT id FROM bots WHERE status = 'Active')) ORDER BY i.id LIMIT @scan) UNION ALL (SELECT i.id, i.bot_id, i.chat_key FROM inbox i WHERE i.status = 'Processing' AND i.locked_until < @now AND i.bot_id = ANY(ARRAY(SELECT id FROM bots WHERE status = 'Active')) ORDER BY i.id LIMIT @scan)), candidates AS ( SELECT DISTINCT ON (d.bot_id, d.chat_key) d.id FROM due d WHERE NOT EXISTS ( SELECT 1 FROM inbox p WHERE p.status = 'Processing' AND p.bot_id = d.bot_id AND p.chat_key = d.chat_key AND p.id <> d.id AND p.locked_until >= @now) ORDER BY d.bot_id, d.chat_key, d.id LIMIT @limit), locked AS ( SELECT id FROM inbox WHERE id IN (SELECT id FROM candidates) AND ((status = 'Pending' AND next_attempt_at <= @now) OR (status = 'Processing' AND locked_until < @now)) FOR UPDATE SKIP LOCKED)UPDATE inbox SET status = 'Processing', locked_until = @lock, attempts = inbox.attempts + 1FROM locked WHERE inbox.id = locked.idRETURNING inbox.id, inbox.bot_id, inbox.update_id, inbox.kind, inbox.chat_key, inbox.payload::text, inbox.attempts
INSERT INTO leases (name, owner, expires_at) VALUES (@name, @owner, @expires)ON CONFLICT (name) DO UPDATE SET owner = excluded.owner, expires_at = excluded.expires_atWHERE leases.owner = excluded.owner OR leases.expires_at < @nowRETURNING owner
builder.Services.AddHttpClient<TelegramApi>(http => http.Timeout = Timeout.InfiniteTimeSpan) // The Bot API carries the token in the URL, and HttpClient's default logging writes every URL: never log here. .RemoveAllLoggers() .ConfigurePrimaryHttpMessageHandler(services => { if (telegram.UseFake) return services.GetRequiredService<FakeTelegram>(); // Opening a TLS connection to Telegram can cost seconds on slow routes: keep them open while a bot is quiet. var handler = new SocketsHttpHandler { PooledConnectionLifetime = TimeSpan.FromMinutes(30), PooledConnectionIdleTimeout = TimeSpan.FromMinutes(15), };
private async Task SendAsync(CancellationToken ct){ var batch = new List<(string Channel, string Payload)>(); foreach (var key in _pending.Keys) if (_pending.TryRemove(key, out _)) batch.Add(key); if (batch.Count == 0) return; var sql = new StringBuilder("SELECT "); await using var command = dataSource.CreateCommand(); for (var i = 0; i < batch.Count; i++) { if (i > 0) sql.Append(", "); sql.Append($"pg_notify(@c{i}, @p{i})"); command.Parameters.AddWithValue($"c{i}", batch[i].Channel); command.Parameters.AddWithValue($"p{i}", batch[i].Payload); } command.CommandText = sql.ToString(); await command.ExecuteNonQueryAsync(ct);}
ALTER TABLE deliveries ALTER CONSTRAINT fk_deliveries_contents_content_id DEFERRABLE INITIALLY DEFERRED;
private async Task<MemberCheck> CheckOneAsync(string token, Channel channel, long userId, CancellationToken ct){ var now = clock.GetUtcNow().ToUnixTimeMilliseconds(); if (_joined.TryGetValue((channel.ChatId, userId), out var until) && until > now) return new MemberCheck(channel, MemberState.Joined); try { var member = await api.GetChatMemberAsync(token, channel.ChatId, userId, ct); if (!GateRules.IsMember(member.Status, member.IsMember)) return new MemberCheck(channel, MemberState.Missing); Remember(channel.ChatId, userId); return new MemberCheck(channel, MemberState.Joined); } catch (TelegramException e) when (IsUnknownUser(e)) { // Someone Telegram has never seen in that chat: not a member. return new MemberCheck(channel, MemberState.Missing); } catch (TelegramException e) when (!e.IsTransient && !e.IsUnauthorized) { return new MemberCheck(channel, MemberState.Unknown, e.Description); }}
Comments
0 comments
No comments yet. Questions, what you built with it, what could be better: its developer reads them.
Sign in to comment.
Sign in to comment