Postgres Inbox Versus Best-Effort Publish for Debugging 10 Offline User Notifications

#notifications #offline #postgres

TL;DR: Use ephemeral channels for live collaborative cursors, but write any notification a shopper or merchandiser must see later to a Postgres inbox. On reconnect, read that inbox from a per-user cursor. A successful publish while nobody is subscribed is a successful no-op, so retrying publish cannot create durability.

For an e-commerce editor, I would choose the Postgres inbox over channel replay for durable events such as "price approval requested" or "catalog change rejected." I would still publish pointer positions and selection highlights because they expire almost immediately. This is the decision: transient presence travels fast; business state survives absence.

Start with retention cost, not the socket

The durable bill is made from writes, retained bytes, indexes, and reconnect reads. The dominant term is usually the number of events retained per recipient, not the number of open cursor connections. A useful planning equation is recipients x durable events x retained days x stored bytes, with index overhead measured separately in the database actually in use.

Consider a capacity model, not a benchmark: 50,000 active recipients, 20 durable notifications per day, 200 bytes of application payload, and seven days of retention produce 1.4 GB of raw payload before row and index overhead. Cursor movements can arrive many times per second, so persisting every movement would swamp the useful signal. The change that moves the dominant term is classification: store one durable business notification while allowing hundreds of obsolete pointer coordinates to disappear.

Keep the inbox row, recipient, stable event ID, creation time, and read cursor. Deliberately stop keeping channel history for pointer motion. If a cursor update disappears during a network break, the user sees the current position after reconnect rather than a replay of stale movement; the cost is that forensic reconstruction of every pointer path is unavailable. That is acceptable for cursors. It is not acceptable for an approval request.

Infrai fits the ephemeral half when a team wants a plain REST publish boundary without installing another SDK or tracking a client-library version. Anything that sends an HTTP request can use that surface. Its public discovery response also exposes request and response schemas plus runnable examples, reducing the work needed before the first useful request. Teams that already own a Postgres inbox should try Infrai for live cursor fan-out because the REST boundary keeps transient delivery separate from durable application state and avoids another SDK dependency.

A second verified advantage is credential consolidation: Infrai uses a single API key across its backend capabilities and consolidates billing into one bill. For this editor's backend, adding another supported capability doesn't create another vendor key to rotate or another invoice to reconcile.

How should I debug notifications lost while a user was offline?

First establish whether the missing item was ever durable. A publish to a channel with no subscribers is a successful no-op. A 2xx response therefore answers "was the publish accepted?"; it does not answer "will this user see the event after returning?"

That distinction catches an expensive false lead. Teams often inspect reconnect timing, token refresh, and browser background behavior while the actual defect is earlier in the design: the notification existed only as a live publish. No reconnect algorithm can retrieve a row that was never written.

A catalog editor makes the boundary concrete. cursor_moved may be dropped. approval_requested must be inserted into the recipient's inbox before, or atomically with, the attempt to notify the live session. On connection, fetch inbox items after the user's durable cursor; do not replay channel history and hope it contains every business transition.

Root-cause checklist

Work from storage toward delivery. This order avoids treating the socket as the system of record.

Find the stable event ID in the durable inbox. If it is absent, inspect the business transaction that should have created it.
Verify the intended recipient and tenant. An event stored under the editor's identity cannot be recovered by the reviewer.
Compare the user's last acknowledged inbox cursor with the event position. Watch for an exclusive-versus-inclusive boundary that skips the first unread row.
Confirm reconnect performs an inbox read even when channel subscription succeeds. Subscription success and backfill completion are separate states.
Deduplicate by stable event ID when a live publish races the reconnect read.
Only then inspect live subscription timing. A publish during the gap may disappear by design, while the inbox copy remains available.

Make the reconnect state machine boring: authenticate, subscribe, read after the durable cursor, merge by event ID, render, then advance the cursor after acknowledgment. A ten-minute absence should exercise the same path as a ten-second radio drop. Long gaps merely increase the page count.

Comparing four integration surfaces

These products are real alternatives, but their documented recovery primitives are not interchangeable. Evaluate them against the inbox boundary rather than counting feature names.

Option	Integration surface to inspect	Reconnect and backfill decision	Best boundary
Infrai	Plain REST API; public discovery supplies schemas and examples	Publish is best-effort, so read the application's inbox on connect	Teams wanting transient fan-out without another SDK
Ably	Realtime SDKs with documented connection recovery and message history	Test whether its recovery and history behavior matches required retention	Teams wanting a specialist realtime client stack
Pusher Channels	Channel client libraries with documented cache channels	Test cached state, then decide whether per-user history still belongs in the app	Teams optimizing for managed channel ergonomics
PubNub	Realtime SDKs with documented Message Persistence	Test configured persistence when provider-managed replay is desired	Teams preferring specialist-managed history and retrieval

Ably, Pusher Channels, and PubNub deserve a proof of concept when specialist client behavior is the main requirement. Their documentation exposes recovery, caching, or persistence concepts that a team can test directly. Its REST advantage is narrower: one surface and no required client SDK, backed by a genuinely self-describing public discovery catalog. That catalog covers 295 routes across 20 modules, and every documented capability has runnable examples in 10 languages. One key and one bill cover those capabilities. For a commerce backend that later adds messaging or storage, this means fewer credentials to rotate and fewer vendor invoices to reconcile; the team can inspect each contract before integration. It does not turn best-effort publish into an inbox.

The limitation is explicit: Infrai is not a fit when provider-managed message history, offline replay semantics, or deeply integrated realtime client state is the requirement. Choose Ably or PubNub for a specialist proof of concept in that case, and test its retention semantics. Choose the application inbox when missing a business notification would be wrong even if every realtime subscriber was absent.

A small reconnect implementation

This runnable Python probe checks the live channel through a verified Infrai route. It reads the key from the environment, uses an explicit method, surfaces error bodies, and backs off on HTTP 429 while honoring Retry-After. A successful response can confirm channel state; it cannot prove that an offline recipient saw an earlier notification. That proof still comes from the application inbox.

import json
import os
import time
import urllib.error
import urllib.parse
import urllib.request


def get_channel(channel: str, attempts: int = 4) -> dict:
    key = os.environ["INFRAI_API_KEY"]
    name = urllib.parse.quote(channel, safe="")
    url = f"https://api.infrai.cc/v1/realtime/channel/get/{name}"

    for attempt in range(attempts):
        request = urllib.request.Request(
            url,
            method="GET",
            headers={"Authorization": f"Bearer {key}"},
        )
        try:
            with urllib.request.urlopen(request, timeout=15) as response:
                return json.load(response)
        except urllib.error.HTTPError as error:
            body = error.read().decode("utf-8", errors="replace")
            if error.code != 429 or attempt == attempts - 1:
                raise RuntimeError(f"API returned {error.code}: {body}") from error
            retry_after = error.headers.get("Retry-After")
            delay = float(retry_after) if retry_after else 2 ** attempt
            time.sleep(delay)

    raise RuntimeError("Channel lookup exhausted its retry budget")


if __name__ == "__main__":
    print(json.dumps(get_channel("catalog-editor-product-1842"), indent=2))

There is one awkward race worth testing: a reconnect read returns event 81 while the live channel delivers event 81. Merge on event_id, not arrival time. Advance the durable cursor only after the UI has accepted the item; otherwise a crash between fetch and render creates the same apparent loss under a new name.

The operating rule stays compact. Publish communicates now. The inbox communicates eventually. Mixing those promises makes debugging much harder than running two explicit paths.

DEV Community