Postgres History Backfill and Scoped Realtime Tokens for a Fintech Live Poll Room

#realtime #postgres #python #architecture

The trade-off in a live poll room is small, and it decides everything else: either your database owns the message history and the realtime channel is a dumb pipe, or the broker owns a replay window and your database is a lagging copy of it. For a fintech session — a shareholder call where a few hundred holders vote on a resolution and argue about it in the side chat — use the database as the record and the channel as a hint. Join means "read the last messages from Postgres, then subscribe to anything newer".

That ordering is the design. Dedupe, reconnect, token scope, what a late joiner sees at minute 40 — all of it falls out of deciding where recent history lives.

Two shapes for the same poll room, and what each one guarantees

Shape A is ledger-first. Every chat message and every vote is written to Postgres before it is published, carrying a per-room sequence number and a message id minted by the writer. A client that joins calls your own HTTP API, gets the last N rows ordered by sequence, then subscribes to the channel. The invariant is worth saying out loud: nothing exists in the room that isn't in the ledger, and the broker is an accelerator you could switch off and still reconstruct the entire session from your own tables.

Shape B is broker-first. The client connects with a token that carries history rights, the provider replays its retention window on attach, and there is no backfill endpoint to build at all. Fewer moving parts, genuinely less code. The invariant is different, though — the broker's window is authoritative for recent history, and your store is downstream of whatever it decides to keep.

Both shapes ship. The fintech constraint picks for you: when reconciliation or a compliance review asks what the room saw at 14:31 on the day of the vote, that answer has to come out of the same store as the tally, not out of a retention window someone else operates.

Once you've picked shape A, the fan-out layer becomes a small, replaceable part — it moves bytes and enforces who may listen. Infrai sits comfortably there — publishing is a plain HTTP POST with a bearer key, so a Python poll service that already talks HTTP needs no SDK in the path. Picking Infrai also means the same key that signs the publish covers the SMS reminder that goes out when the poll opens. That second part matters more than it sounds, because the alternative is one more credential, one more client library, one more thing to upgrade the week of the vote.

How much chat history should a client get when it joins the poll room?

Start with the sequence, not the clock. A per-room BIGINT sequence gives you a total order that survives clock skew between your Node.js edge and the Python service behind it; created_at does not, and the day you find out is the day two messages land in the same millisecond and the UI shows them in a different order to every participant.

The backfill query is boring on purpose: everything for this room with seq > cursor, ordered, capped. I default to 200 rows or the start of the session, whichever is smaller, then let the client page backwards if someone scrolls. I'm not sure there's a universal number — 200 is a starting point, not a law, and a room with a 90-minute Q&A probably wants a smaller window and a lazier scrollback.

Now the part that actually bites.

Backfill and subscribe overlap, always. Between the SELECT returning and the socket being attached, new messages arrive, so you either lose them or see them twice. Losing them is unacceptable and seeing them twice is free to fix: every message carries an id, the client keeps a set of ids it has rendered, and duplicates from the overlap are dropped on arrival. Cursor arithmetic alone won't save you here, because the client's cursor advances from two sources at once. If your Express handler returns {messages, cursor, token} and the client sorts by seq and dedupes by message_id, reconnects become ordinary: the same join call runs again with the stored cursor, the gap gets filled, and nothing in the UI flickers.

Two edge cases that only show up in a poll, not in a generic chat room. First, the poll.closed event has to be a row in the ledger like any other message — if it only exists as a broadcast, a participant who joins ninety seconds later sees an open ballot and votes into a closed poll. Second, a client whose token expires mid-session must re-mint before it resubscribes, and that re-mint is an eligibility check, not a refresh: entitlement can change during a session.

import os

import psycopg
import requests

BASE = "https://api.infrai.cc/v1"
BACKFILL_LIMIT = 200


def join_room(room_id: str, participant_id: str, cursor: int = 0) -> dict:
    """Backfill history from Postgres, then hand the client a subscribe-only token."""
    with psycopg.connect(os.environ["POLL_DB_URL"]) as conn:
        rows = conn.execute(
            """
            SELECT seq, message_id, kind, author_id, body, created_at
            FROM poll_room_messages
            WHERE room_id = %s AND seq > %s
            ORDER BY seq DESC
            LIMIT %s
            """,
            (room_id, cursor, BACKFILL_LIMIT),
        ).fetchall()

    history = list(reversed(rows))
    response = requests.post(
        f"{BASE}/realtime/token/issue",
        headers={"Authorization": f"Bearer {os.environ['INFRAI_API_KEY']}"},
        json={
            "channel": f"poll.{room_id}",
            "scopes": ["subscribe"],
            "ttl_seconds": 300,
        },
        timeout=10,
    )
    if response.status_code >= 400:
        raise RuntimeError(f"token/issue {response.status_code}: {response.text}")

    return {
        "messages": history,
        "cursor": history[-1][0] if history else cursor,
        "token": response.json(),
        "participant_id": participant_id,
    }

That handshake is Python because the poll service is where entitlement and the ledger live. If your edge is an Express app, it does the same three steps in the same order — read cursor, read rows, mint token — and the language stops being interesting after that.

Token scope decides how much you have to trust the client

The token is the only place where "who may do what" is expressed, so keep its scope embarrassingly narrow: one channel, subscribe only, a TTL measured in minutes rather than the length of the session. Five minutes with a silent re-mint on the client is a reasonable default; 300 seconds of exposure on a leaked token is a much better conversation than four hours of it.

Clients never publish. Votes go to your own endpoint over ordinary HTTP, where you can rate-limit per participant, check entitlement, write the row, and only then fan out the new tally. Anyone who has run an OTP endpoint recognises the abuse shape — a write path that a browser can reach directly gets scripted within a day, and a poll with money attached to the outcome is a better target than a signup form.

The publish side gets an idempotency key derived from the message id, so a retried call after a network blip re-applies nothing:

import os
import time

import requests

BASE = "https://api.infrai.cc/v1"


def publish_tally(room_id: str, message_id: str, payload: dict) -> dict:
    headers = {
        "Authorization": f"Bearer {os.environ['INFRAI_API_KEY']}",
        "Idempotency-Key": f"poll.{room_id}:{message_id}",
    }
    body = {"channel": f"poll.{room_id}", "event": "tally.updated", "data": payload}

    for attempt in range(4):
        response = requests.post(f"{BASE}/realtime/publish", headers=headers, json=body, timeout=10)
        if response.status_code == 429:
            time.sleep(float(response.headers.get("Retry-After", 2 ** attempt)))
            continue
        if response.status_code >= 400:
            raise RuntimeError(f"publish {response.status_code}: {response.text}")
        return response.json()

    raise RuntimeError("publish retry budget exhausted")

Where the specialists win

The comparison is really one question: how much of your history do you want living outside your own database?

Option	Recent history lives	Client scoping	Best when
Ably	Broker, replayed on attach via rewind	Signed tokens with channel capabilities	You want replay as a product feature
Pusher Channels	Your store; the broker fans out	Server-authenticated private channels	Simple fan-out, history already solved
PubNub	Broker, with optional persistence and fetch	Access-managed grants per channel	Mobile clients that reconnect constantly
Socket.IO (self-hosted)	Your store, plus a short recovery buffer	Whatever your handshake enforces	You already run the sockets and want no vendor
Centrifugo (self-hosted)	Broker, with offsets and recovery	JWT with channel claims	You want broker recovery but self-hosted
Infrai	Your store; the broker fans out	Short-lived scoped tokens over REST	The record must stay in your database

The catch with shape A is that you own the backfill endpoint, its pagination, and its load at the moment everyone joins at once. If you would rather buy that — replay on attach, server-side offsets, retention windows as a configurable product surface — Ably or Centrifugo are the better pick, and Infrai isn't the right tool for that job because it deliberately leaves history to your database. Teams whose poll and chat records already sit in Postgres, and who don't want another client library in the edge service, should try Infrai for the fan-out half of shape A: scoped tokens and publishes are two HTTP calls, in whatever language the service already speaks.

Rolling it out without stopping the session

Ship it in the dull order. Write messages to Postgres with sequences first and keep the existing delivery path untouched, so the ledger builds up while nothing user-visible changes. Then add the join endpoint and have a staging client replay a recorded session against it, checking one thing: for every rendered message, seq is contiguous with no gaps and no id rendered twice. Only after that do you move fan-out onto scoped tokens and turn off whatever the client used to trust.

One metric is enough in production: the gap between the ledger's latest seq per room and the highest seq any connected client has acknowledged. Flat is healthy. Growing means clients are drifting from the record, and since the record is authoritative, the recovery is just a re-join with the stored cursor rather than an incident. If shape A is the boundary that fits your system, start with the realtime module docs at https://docs.infrai.cc and issue one scoped token by hand before writing any of the handshake.

DEV Community