A transcript flush is a distributed commit problem
Retries, reconnects, races, and shutdown turn “save the conversation” into an occurrence-identity problem: the database must know which logical event committed, not which transport attempt happened to arrive.
- problem
- A long-lived real-time session emits transcripts, tool events, state transitions, and lifecycle markers from concurrent tasks; retries and reconnects can duplicate or reorder those writes while shutdown races with the final flush.
- scope
- A public persistence model for stable occurrence identity, transactional deduplication, monotonic session state, partial-batch recovery, retry ambiguity, and bounded shutdown in streamed agent services.
- environment
- Async Python services with PostgreSQL persistence, streamed STT and agent events, reconnecting WebRTC sessions, retrying workers, task supervision, and session-close cleanup.
Assumptions
- Each logical event can be assigned a deterministic occurrence identity before the database transaction begins.
- PostgreSQL is the final authority for uniqueness and session-state transitions, not an in-process lock.
- Event producers can distinguish observed time, logical order, and persistence attempt identity.
Limitations
- The schema is a compact public pattern and omits private message content, tenant topology, retention rules, and migration history.
- Exactly-once delivery is not claimed; the design targets effectively-once committed effects through stable identity and idempotent transactions.
- Cross-region active-active ordering and consensus are outside this note.
Table of contents 5 sections
Identity must exist before retry
A retry identifier is not an event identifier. If a worker creates a new UUID on each attempt, the database sees several legitimate rows and faithfully stores duplicates. The identity has to name the logical occurrence before transport retries begin: session identity, source stream, producer epoch, event kind, and a source-local sequence or deterministic content boundary. The retry attempt gets its own diagnostic identity, but it never changes the uniqueness key of the event being committed.
Occurrence identity is also separated from generation fencing. The occurrence answers which event this is. The generation answers whether the producer still has authority to advance session state. A late transcript fragment may remain a valid historical event while being forbidden from changing the current assistant turn. Conflating the two forces a false choice between losing audit history and allowing stale work to overwrite live state.
The schema is part of the concurrency control
An asyncio.Lock can serialize coroutines in one process. It cannot coordinate a retrying worker, a reconnect handler, another replica, or a process restarting after it sent the SQL but before it received the response. The database constraint is the final arbiter because it participates in the commit. The application lock can reduce redundant work, but correctness must survive its absence.
The event table uses a unique occurrence_id, stores the producer generation and logical sequence as data, and returns whether the row was inserted. Session progression is a separate compare-and-set update guarded by the expected generation and monotonic revision. Both operations happen in one transaction when the event is allowed to advance state. A duplicate insert becomes a no-op; a stale generation can preserve the event while failing the state update; a conflicting payload for the same identity is an integrity alert, not a silent overwrite.
CREATE TABLE session_event ( occurrence_id uuid PRIMARY KEY, session_id uuid NOT NULL, producer_generation bigint NOT NULL, logical_sequence bigint NOT NULL, event_kind text NOT NULL, payload jsonb NOT NULL, payload_hash bytea NOT NULL, observed_at timestamptz NOT NULL, committed_at timestamptz NOT NULL DEFAULT now(), UNIQUE (session_id, producer_generation, logical_sequence, event_kind)); INSERT INTO session_event (...)VALUES (...)ON CONFLICT (occurrence_id) DO NOTHINGRETURNING occurrence_id; UPDATE session_stateSET revision = revision + 1, state = $next_stateWHERE session_id = $session_id AND generation = $expected_generation AND revision = $expected_revision;Partial batches need replay semantics
Batching improves throughput and reduces transaction overhead, but it changes the failure shape. A process may assemble twenty events, commit them successfully, lose the connection before receiving acknowledgement, and replay the same batch. It may also fail validation on event twelve, leaving the producer unsure whether the first eleven were attempted. The safe contract is that every event in the batch is individually identifiable and the transaction either commits the selected set atomically or reports a deterministic rejection before commit.
For high-volume streams, staging or array parameters present the batch, validate identity collisions, and insert with conflict handling. The transaction returns inserted, already-present-with-same-hash, stale-for-state, and conflicting-payload outcomes. The caller advances its durable high-water mark only from that receipt. A reconnect does not infer progress from local memory; it queries the committed boundary and replays from the last proven point.
| outcome | database fact | caller action | operator signal |
|---|---|---|---|
| inserted | new occurrence committed | advance proven boundary | normal counter |
| already present / same hash | retry of identical occurrence | treat as success | deduplicated retry counter |
| already present / different hash | identity collision or corruption | stop and quarantine | high-severity integrity alert |
| event stored / state stale | history valid; generation lost authority | do not advance live state | stale generation counter |
| commit outcome unknown | connection lost around commit | query by occurrence IDs before retry | ambiguous transaction counter |
Preserve order without inventing a global clock
Real-time sessions contain several clocks: audio sample position, provider timestamps, wall-clock observation, monotonic process time, source-local sequence, database commit time, and user-visible turn order. Sorting everything by committed_at is easy and wrong. A delayed STT final may commit after a tool result that causally depended on its partial. A reconnect can replay old observations after the new transport is live. Wall clocks can move.
Each clock is stored for the questions it can answer. observed_at supports human chronology; source sequence preserves producer order; turn and generation establish conversational authority; causal parent links explain derivation; committed_at supports database operations. The read model chooses an ordering contract explicitly instead of pretending one timestamp is truth. When concurrent events have no defined order, the schema should preserve that ambiguity rather than manufacture it.
- Occurrence identity: which logical event is this?
- Producer generation: did the producer still own the session state?
- Source sequence: what order did this producer establish?
- Observed time: when did the source report the event?
- Commit time: when did PostgreSQL make the event durable?
- Causal parent: which event or turn produced this event?
Shutdown must produce proof, not hope
Session close is the worst time to discover that persistence is owned by a detached background task. The close path must stop new event admission, snapshot the producer boundaries, enqueue the final lifecycle events, wait for a bounded flush, and record whether the durable boundary reached the snapshot. If the timeout expires, the session closes with an explicit persistence-incomplete state and a replay packet that another worker can resume. Dropping the task and logging an exception later is not recovery.
The trace links each flush attempt to the stable occurrence IDs, transaction receipt, retry reason, session generation, and shutdown phase. The useful metrics are duplicate suppression, conflicting identities, ambiguous commits, stale state updates, replay distance after reconnect, final-flush latency, and sessions closed with unproven durability. Those signals make idempotency observable. Without them, duplicate-free tables can still hide missing events and shutdown can look clean while discarding the tail of the conversation.
Why ON CONFLICT alone is insufficient
ON CONFLICT prevents one class of duplicate row. It does not define occurrence identity, detect conflicting payloads under the same key, coordinate session-state progression, prove which batch committed, preserve causal order, or recover an ambiguous transaction. Idempotency is the full protocol around the constraint.
Replace attempt identity with occurrence identity
- observation
- Retries generated fresh identifiers, so the database could not distinguish a replay from a new transcript or lifecycle event.
- hypothesis
- A deterministic logical occurrence key plus database uniqueness would convert transport retries into idempotent reapplication.
- change
- Assign occurrence identity before enqueue, carry it through every attempt, and make the database transaction return inserted versus already committed.
- measurement
- Track deduplicated retries, conflicting payload hashes, ambiguous transactions reconciled by lookup, and missing logical sequences after reconnect.
- mechanism
- The unique constraint participates in the commit, so process crashes and cross-replica races cannot create a second durable effect for the same occurrence.
- decision
- Keep attempt IDs for tracing only; use stable occurrence IDs and transactional receipts for correctness.
- implemented Retry-safe transcript and event flushing
The public CV records implementation of idempotent transcript and event flushing that recovers from retries, partial failures, races, reconnects, and shutdown without duplicate writes or inconsistent state.
- inferred Occurrence identity model
The schema and state machine below generalize that persistence doctrine without exposing private product data or claiming a universal event model.
- reference PostgreSQL transaction documentationopen ↗
Public reference for transaction isolation, constraints, INSERT conflict handling, and atomic commit semantics.
- reference PostgreSQL INSERT documentationopen ↗
Authoritative semantics for unique constraints, ON CONFLICT, RETURNING, and idempotent insert patterns.