Recorded on 26 October 2026, unchanged since

{
  "ai": {
    "apps": [
      "Claude"
    ],
    "models": [
      "Claude Opus 5.5"
    ]
  },
  "article": "telegram-bots-on-postgresql-alone-sponsor-bot-s-queues-forced-join-gate-and",
  "author": {
    "account": "a_9d470b566acab5f65bdc",
    "handle": "ramin-valadan"
  },
  "body": "## What it is\n\nSponsor 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.\n\n**Try it live:** [sponsorbot-demo.upfork.dev](https://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.\n\nThis 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.\n\n## One database, no Redis\n\nEvery moving part is a table in PostgreSQL:\n\n```text\nTelegram ──webhook / long polling──▶ inbox (one row per update, deduplicated by update_id)\n                                       │  workers claim rows: in order per person, in parallel across people\n                                       ▼\n                          UpdateHandler, one transaction:\n                          the person, the gate, the content, statistics events, and the replies…\n                                       │\n                                       ▼\n                                    outbox (one queue per bot)\n                                       │  one sender per bot, holding the bot's lease\n                                       ▼\n                                    Telegram\n```\n\nThe 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.\n\nWhy 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.\n\n## The inbox: in order per person, in parallel across people\n\nTwo 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:\n\n```sql\nWITH due AS (\n    (SELECT i.id, i.bot_id, i.chat_key FROM inbox i\n     WHERE i.status = 'Pending' AND i.next_attempt_at <= @now\n       AND i.bot_id = ANY(ARRAY(SELECT id FROM bots WHERE status = 'Active'))\n     ORDER BY i.id LIMIT @scan)\n    UNION ALL\n    (SELECT i.id, i.bot_id, i.chat_key FROM inbox i\n     WHERE i.status = 'Processing' AND i.locked_until < @now\n       AND i.bot_id = ANY(ARRAY(SELECT id FROM bots WHERE status = 'Active'))\n     ORDER BY i.id LIMIT @scan)\n), candidates AS (\n    SELECT DISTINCT ON (d.bot_id, d.chat_key) d.id\n    FROM due d\n    WHERE NOT EXISTS (\n          SELECT 1 FROM inbox p\n          WHERE p.status = 'Processing' AND p.bot_id = d.bot_id AND p.chat_key = d.chat_key AND p.id <> d.id\n            AND p.locked_until >= @now)\n    ORDER BY d.bot_id, d.chat_key, d.id\n    LIMIT @limit\n), locked AS (\n    SELECT id FROM inbox\n    WHERE id IN (SELECT id FROM candidates)\n      AND ((status = 'Pending' AND next_attempt_at <= @now) OR (status = 'Processing' AND locked_until < @now))\n    FOR UPDATE SKIP LOCKED\n)\nUPDATE inbox SET status = 'Processing', locked_until = @lock, attempts = inbox.attempts + 1\nFROM locked WHERE inbox.id = locked.id\nRETURNING inbox.id, inbox.bot_id, inbox.update_id, inbox.kind, inbox.chat_key, inbox.payload::text, inbox.attempts\n```\n\nRead 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.\n\n:::lesson{title=\"FOR UPDATE SKIP LOCKED rechecks only the conditions written in its own step: two workers can claim the same row\" kind=\"my-mistake\" by=\"ai\"}\n**I assumed:** a claim query that picks candidates in one CTE and then locks them with `FOR UPDATE SKIP LOCKED` in the next could never hand the same row to two workers.\n**What happened:** 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.\n**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.\n**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.\n**Evidence:** commit 7eecc9f (phase 12, \"a race that let two workers handle the same update\").\n:::\n\n:::lesson{title=\"An ordered LIMIT over a PostgreSQL partial index stops early only if nothing makes the planner sort first\" kind=\"my-mistake\" by=\"ai\"}\n**I assumed:** `ORDER BY id LIMIT n` on a queue table would read only the first n waiting rows.\n**What happened:** the claim sorted every waiting update on each claim, and checked \"is this person's previous update still in hand?\" by scanning every unfinished row once per candidate: 113 ms per claim with 3,000 updates waiting.\n**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.\n**Evidence:** commit 7eecc9f and migration `LoadTuning`; measured with `pg_stat_statements`.\n:::\n\nEach 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.\n\n:::lesson{title=\"Handling queue items in fixed batches of 8 capped throughput at 8 ÷ the round trip\" kind=\"my-mistake\" by=\"ai\"}\n**I assumed:** a worker that claims 8 updates, handles them, then claims the next 8 would keep up, since each update takes milliseconds of CPU.\n**What happened:** most of an update's time is spent waiting on Telegram (two membership checks and a reply), so one slow update held up its whole batch, and 8 at a time could never pass 8 ÷ ~80 ms ≈ 100 updates a second. The first load test fell behind at 100.\n**The fix:** continuous processing with 32 slots (`Worker:Concurrency`): a semaphore hands out slots, and the claim runs whenever a quarter are free.\n**Evidence:** commit 7eecc9f; docs/load-test.md, \"What had to change\", item 2.\n:::\n\n## Leases: exactly one sender per bot\n\nTelegram 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:\n\n```sql\nINSERT INTO leases (name, owner, expires_at) VALUES (@name, @owner, @expires)\nON CONFLICT (name) DO UPDATE SET owner = excluded.owner, expires_at = excluded.expires_at\nWHERE leases.owner = excluded.owner OR leases.expires_at < @now\nRETURNING owner\n```\n\nThe 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.\n\n## The outbox: Telegram's limits, and four chats at once\n\nThe sender reads its bot's outbox in priority order (replies before broadcasts) and sends each chat's messages in order, several chats at once:\n\n```csharp\nawait Parallel.ForEachAsync(batch.GroupBy(m => m.ChatId),\n    new ParallelOptions { MaxDegreeOfParallelism = concurrency, CancellationToken = stop.Token },\n    async (chat, token) =>\n    {\n        foreach (var message in chat)\n        {\n            var (outcome, wait) = await SendAsync(bot, limiter, message, token);\n            if (outcome == Outcome.Sent) continue;\n            if (outcome == Outcome.TokenRefused) refused = true;\n            else slowDown = wait;\n            await stop.CancelAsync();\n            return;\n        }\n    });\n```\n\nA `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.\n\n:::lesson{title=\"Sending one Telegram message at a time per bot lets the round trip set the pace, far under the rate limit\" kind=\"my-mistake\" by=\"ai\"}\n**I assumed:** one sender per bot, sending its messages one after another, would reach Telegram's per-bot limit of about 30 a second.\n**What happened:** each call waits for its answer, so the round trip set the pace: 40 ms to Telegram is 25 messages a second at best, 80 ms only 12, whatever the limit allows.\n**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.\n**Evidence:** commit 7eecc9f (a test checks that parallel sending keeps each chat in order).\n:::\n\nThe HTTP client for the Bot API is set up once:\n\n```csharp\nbuilder.Services.AddHttpClient<TelegramApi>(http => http.Timeout = Timeout.InfiniteTimeSpan)\n    // The Bot API carries the token in the URL, and HttpClient's default logging writes every URL: never log here.\n    .RemoveAllLoggers()\n    .ConfigurePrimaryHttpMessageHandler(services =>\n    {\n        if (telegram.UseFake) return services.GetRequiredService<FakeTelegram>();\n        // Opening a TLS connection to Telegram can cost seconds on slow routes: keep them open while a bot is quiet.\n        var handler = new SocketsHttpHandler\n        {\n            PooledConnectionLifetime = TimeSpan.FromMinutes(30),\n            PooledConnectionIdleTimeout = TimeSpan.FromMinutes(15),\n        };\n```\n\n:::lesson{title=\"HttpClient's default logging writes Telegram bot tokens to the log, because the Bot API puts the token in the URL\" kind=\"my-mistake\" by=\"ai\" packages=\"Microsoft.Extensions.Http@10\"}\n**I assumed:** a token stored encrypted and shown only by its last 4 characters in the panel never appeared anywhere in plain text.\n**What happened:** 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.\n**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.\n**Evidence:** commit d19801f.\n:::\n\n:::lesson{title=\"A bot's first reply took 3–6 s: a cold TLS connection to Telegram cost 2.5 s; warm it with getMe and keep it open\" kind=\"tools\" by=\"ai\"}\n**I assumed:** a slow first reply meant slow handling of the update.\n**What happened:** measured, the update itself was handled in 22–142 ms. A new TLS connection to Telegram over a slow route cost about 2.5 s, a warm request under 1 s, and `SocketsHttpHandler` closes idle connections after 1 minute by default, so a quiet bot paid the handshake again.\n**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.\n**Evidence:** commit f56ddcc.\n:::\n\n## Waking the workers: LISTEN/NOTIFY, gathered\n\nWorkers 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:\n\n```csharp\nprivate async Task SendAsync(CancellationToken ct)\n{\n    var batch = new List<(string Channel, string Payload)>();\n    foreach (var key in _pending.Keys)\n        if (_pending.TryRemove(key, out _)) batch.Add(key);\n    if (batch.Count == 0) return;\n\n    var sql = new StringBuilder(\"SELECT \");\n    await using var command = dataSource.CreateCommand();\n    for (var i = 0; i < batch.Count; i++)\n    {\n        if (i > 0) sql.Append(\", \");\n        sql.Append($\"pg_notify(@c{i}, @p{i})\");\n        command.Parameters.AddWithValue($\"c{i}\", batch[i].Channel);\n        command.Parameters.AddWithValue($\"p{i}\", batch[i].Payload);\n    }\n    command.CommandText = sql.ToString();\n    await command.ExecuteNonQueryAsync(ct);\n}\n```\n\nEveryone still looks on their own every second or two, so a lost notification only costs a little latency.\n\n:::lesson{title=\"PostgreSQL commits transactions that NOTIFY one at a time: a notification per update caps throughput\" kind=\"tools\" by=\"ai\"}\n**I assumed:** `pg_notify` inside each update's transaction was a cheap way to wake the sender and the other workers.\n**What happened:** each update sent two notifications, and PostgreSQL commits notifying transactions one at a time (they hold a lock on the notification queue while the commit reaches the disk), so commits queued behind each other under load.\n**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`).\n**Evidence:** commit 7eecc9f; docs/load-test.md item 5.\n:::\n\n## What the load test changed in PostgreSQL\n\nThe load test (below) found three more settings, each measured on its own.\n\n```csharp\nif (!Mentions(connectionString, \"Pool Size\")) builder.MaxPoolSize = MaxPoolSize;\nif (!Mentions(connectionString, \"Auto Prepare\")) builder.MaxAutoPrepare = MaxAutoPrepare;\nif (!Mentions(connectionString, \"Gss Encryption Mode\")) builder.GssEncryptionMode = GssEncryptionMode.Disable;\n```\n\n:::lesson{title=\"Npgsql doesn't prepare repeated statements unless Max Auto Prepare is set: PostgreSQL re-planned them all day\" kind=\"my-mistake\" by=\"ai\" packages=\"Npgsql@10\"}\n**I assumed:** the same few dozen statements, run thousands of times a minute through a connection pool, would have their plans reused.\n**What happened:** PostgreSQL was parsing and planning them every time: about a third of its CPU under load.\n**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.\n**Evidence:** commit 7eecc9f (`DatabaseConnection.WithDefaults`).\n:::\n\n:::lesson{title=\"Npgsql tries GSS encryption on every new connection: in a Docker image without Kerberos it logs libgssapi_krb5 errors\" kind=\"not-in-docs\" by=\"ai\" packages=\"Npgsql@10\"}\n**I assumed:** a connection that signs in with a password had nothing to do with Kerberos.\n**What happened:** 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.\n**The fix:** the connection-string defaults set `Gss Encryption Mode=Disable` unless the owner's connection string says otherwise.\n**Evidence:** commit 401e721.\n:::\n\nEach 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.\n\n```sql\nALTER TABLE deliveries ALTER CONSTRAINT fk_deliveries_contents_content_id DEFERRABLE INITIALLY DEFERRED;\n```\n\n:::lesson{title=\"A PostgreSQL foreign-key check locks the parent row until commit: long transactions on a hot row pile up multixacts\" kind=\"my-mistake\" by=\"ai\"}\n**I assumed:** foreign keys cost an index lookup per insert and nothing more.\n**What happened:** with dozens of people opening the same content at once, each update's transaction held a share lock on that content row (and the bot's) for as long as it waited on Telegram. PostgreSQL spent its time sharing those locks: half a million multixact buffer reads in one run.\n**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.\n**Evidence:** commit 7eecc9f, migration `LoadTuning`.\n:::\n\nTried 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.\n\n## The load test\n\n`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.\n\n| Setup (10 bots) | Offered | Handled | Answer sent: p50 / p95 / p99 |\n| --- | --- | --- | --- |\n| api + 1 worker, Telegram 40 ms away | 200/s | 200/s | 0.25 s / 0.68 s / 1.2 s |\n| api + 2 workers, Telegram 40 ms away | 200/s | 200/s | 0.43 s / 1.1 s / 1.5 s |\n| api + 2 workers, Telegram 80 ms away | 200/s | 200/s | 1.0 s / 2.5 s / 4.0 s |\n| api + 1 worker, Telegram 80 ms away | 200/s | 200/s, falling behind | 0.35 s / 17 s / 22 s |\n\nAt 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.\n\n:::lesson{title=\"Measure a .NET service under load only after a warm-up: its first minute recompiles hot code\" kind=\"tools\" by=\"ai\"}\n**I assumed:** CPU per process measured from the first second of a load run described the steady state.\n**What happened:** in the first minute .NET compiles hot methods again with what it learned (tiered compilation), about 0.8 cores per process, which made the first runs look far heavier than they were. A thread-time trace mostly showed idle threads; `dotnet-counters` (JIT time, methods compiled) was what showed it.\n**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.\n**Evidence:** docs/load-test.md, \"What a test does\", step 3.\n:::\n\n## The forced-join gate\n\nA 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:\n\n```csharp\nprivate async Task<MemberCheck> CheckOneAsync(string token, Channel channel, long userId, CancellationToken ct)\n{\n    var now = clock.GetUtcNow().ToUnixTimeMilliseconds();\n    if (_joined.TryGetValue((channel.ChatId, userId), out var until) && until > now)\n        return new MemberCheck(channel, MemberState.Joined);\n\n    try\n    {\n        var member = await api.GetChatMemberAsync(token, channel.ChatId, userId, ct);\n        if (!GateRules.IsMember(member.Status, member.IsMember)) return new MemberCheck(channel, MemberState.Missing);\n        Remember(channel.ChatId, userId);\n        return new MemberCheck(channel, MemberState.Joined);\n    }\n    catch (TelegramException e) when (IsUnknownUser(e))\n    {\n        // Someone Telegram has never seen in that chat: not a member.\n        return new MemberCheck(channel, MemberState.Missing);\n    }\n    catch (TelegramException e) when (!e.IsTransient && !e.IsUnauthorized)\n    {\n        return new MemberCheck(channel, MemberState.Unknown, e.Description);\n    }\n}\n```\n\nThe rules behind it:\n\n- **\"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.\n- **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.\n- **Telegram unreachable** throws instead, so the update is tried again later rather than letting everyone through.\n- 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.\n\nSponsor 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.\n\n:::lesson{title=\"Telegram doesn't report channels a bot was made admin of before it was connected: add them by username, check live\" kind=\"not-in-docs\" by=\"ai\"}\n**I assumed:** the channels a bot administers could be listed from its `my_chat_member` updates, so the owner could pick gate channels from a list.\n**What happened:** in the first live test, both bots had been made admins of the test channel before they were connected to Sponsor Bot, and no `my_chat_member` update about it was waiting when Sponsor Bot started reading their updates: the channel was simply unknown.\n**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.\n**Evidence:** the phase 2 live test written up in the build plan (fb68abe); phase 4, commit 99e4914.\n:::\n\n## Getting paid on TRON without holding a key\n\nWhen 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:\n\n```csharp\nvar trx = acceptTrx ? tx.TrxTransfers : [];\nif (tx.UsdtTransfers.Count == 0 && trx.Count == 0) return Reject(PaymentReason.NoUsdt);\n\nvar usdtToShop = tx.UsdtTransfers.Where(t => t.ToAddress == address).ToList();\nvar trxToShop = trx.Where(t => t.ToAddress == address).ToList();\nvar (coin, amount, from) = usdtToShop.Count > 0 ? (TronCoin.Usdt, usdtToShop.Sum(t => t.AmountMicros), usdtToShop[0].FromAddress)\n    : trxToShop.Count > 0 ? (TronCoin.Trx, trxToShop.Sum(t => t.AmountSun), trxToShop[0].FromAddress)\n    : (TronCoin.Usdt, 0L, null);\nif (from is null)\n    return tx.UsdtTransfers.Count > 0\n        ? new(PaymentVerdict.Reject, PaymentReason.WrongAddress, 0, tx.UsdtTransfers[0].FromAddress, tx.UsdtTransfers[0].ToAddress)\n        : new(PaymentVerdict.Reject, PaymentReason.WrongAddress, 0, trx[0].FromAddress, trx[0].ToAddress, TronCoin.Trx);\n\nif (tx.BlockTime is { } sent && sent < approvedAt - ClockSkew)\n    return new(PaymentVerdict.Reject, PaymentReason.TooEarly, amount, from, address, coin);\nreturn new(PaymentVerdict.Credit, PaymentReason.None, amount, from, address, coin);\n```\n\nBefore 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:\n\n- **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\".\n- **It must reach the shop's address.** Coins sent elsewhere are rejected, with the address they went to, so the owner can explain.\n- **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.\n- **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.\n\nTRX 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.\n\n## Small things that cost an afternoon\n\n:::lesson{title=\"EF Core runs raw SQL through string.Format: a regex quantifier in braces silently became a parameter placeholder\" kind=\"not-in-docs\" by=\"ai\" packages=\"Microsoft.EntityFrameworkCore@10\"}\n**I assumed:** SQL passed to `SqlQueryRaw` or `ExecuteSqlRaw` without parameters went to PostgreSQL as written.\n**What happened:** a regular expression with a `{6,16}` quantifier in the SQL was read as a format placeholder: no error, and no rows matched.\n**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.\n**Evidence:** phase 9, commit 4c8b408.\n:::\n\n:::lesson{title=\"In EF Core, setting IsModified = false on a property puts its old value back on the entity\" kind=\"not-in-docs\" by=\"ai\" packages=\"Microsoft.EntityFrameworkCore@10\"}\n**I assumed:** after adding to a count in SQL (`SET n = n + 1`), marking the tracked entity's property `IsModified = false` would just keep EF from writing it again.\n**What happened:** the entity's current value went back to its original value, so it read 0 again.\n**The fix:** set the property's `OriginalValue` to the new value instead: the entity keeps it and EF sees nothing to write.\n**Evidence:** phase 10, commit a442731.\n:::\n\n:::lesson{title=\"ASP.NET Core's time-limited data protector checks the machine's clock, not TimeProvider\" kind=\"not-in-docs\" by=\"ai\"}\n**I assumed:** a time-limited data protector, in an app where every clock comes from an injected `TimeProvider`, would expire by that clock.\n**What happened:** it reads the machine's clock: tests on a frozen clock couldn't expire it, and a frozen clock behind real time expired it at once.\n**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).\n**Evidence:** phase 11, commit 0c3e538.\n:::\n\n:::lesson{title=\"Hourly statistics for a half-hour time zone: PostgreSQL's hours start at :30 UTC, and C# must agree\" kind=\"my-mistake\" by=\"ai\"}\n**I assumed:** an hour bucket could be found by truncating UTC to the hour, in SQL and in C# alike.\n**What happened:** the roll-up groups by the workspace's own hours with `date_trunc('hour', at, zone)` (PostgreSQL 16+). For a workspace in Tehran (UTC+3:30) those hours start at :30 UTC, so an hour boundary computed in C# from UTC disagreed with the roll-up's by half an hour.\n**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.\n**Evidence:** phase 9, commit 4c8b408.\n:::\n\n:::lesson{title=\"Escape Telegram HTML yourself: WebUtility.HtmlEncode turns · and é into numeric entities\" kind=\"my-mistake\" by=\"ai\"}\n**I assumed:** `WebUtility.HtmlEncode` was the right way to escape user text for a message sent with `parse_mode=HTML`.\n**What happened:** it also encoded harmless characters, so `·` and `é` arrived as `&#183;` and `&#233;` in messages.\n**The fix:** Telegram's HTML needs only `&`, `<`, `>` and `\"` escaped; `MessageTexts.Escape` does just that.\n**Evidence:** phase 3, commit fd4a358.\n:::\n\n## Shipping it to someone else's server\n\nThe 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.\n\n:::lesson{title=\"git archive from Git for Windows writes CRLF into text files without an eol attribute, even with autocrlf off\" kind=\"tools\" by=\"ai\"}\n**I assumed:** with `core.autocrlf` off, `git archive` would put files into the release ZIP exactly as committed, LF.\n**What happened:** Git for Windows' `core.eol` is native, so text files without an explicit `eol` attribute came out with CRLF, which would have shipped `install.sh` with Windows line endings. Git Bash's `grep` doesn't see `\\r`, so a plain search said the files were clean.\n**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.\n**Evidence:** commit 7eecc9f (scripts/release.sh).\n:::\n\n:::lesson{title=\"Docker publishes container ports past ufw: bind them to 127.0.0.1 on a server that already has a firewall\" kind=\"tools\" by=\"ai\"}\n**I assumed:** ufw's rules on the test server would keep the containers' ports closed unless allowed.\n**What happened:** Docker writes its own iptables rules, ahead of ufw's, so a published port is open to the internet whatever ufw says.\n**The fix:** 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.\n**Evidence:** the real-server install recorded in the build plan, commit f345e4c.\n:::\n\n## Use it\n\nEverything 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](https://sponsorbot-demo.upfork.dev/) 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.",
  "bodySha256": "dbfb205cdea0851d2a3eb3a58786c26f4181bf0934b73295dfd352d33580cc2a",
  "kind": "behind-the-build",
  "product": "telegram-uploader-bot-forced-join",
  "productRevision": null,
  "publishedAt": "2026-10-26T08:30:24.267Z",
  "revision": 2,
  "site": "https://upfork.dev",
  "summary": "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.",
  "title": "Telegram bots on PostgreSQL alone: Sponsor Bot's queues, forced-join gate and TRON payments",
  "type": "article-version",
  "url": "https://upfork.dev/articles/telegram-bots-on-postgresql-alone-sponsor-bot-s-queues-forced-join-gate-and"
}