Table Design

Every SQLStreams table is one of two kinds: a shared control-plane table, created once by System().Register (the first stream Register runs it), or a member of one stream’s family, created by the stream’s Register and named by the stream’s id. The architecture page explains why the split exists; this page is the map of what’s actually there.

   SHARED CONTROL PLANE                           ONE FAMILY PER STREAM (suffix = id)
   created by System().Register                   created by Stream(name).Register

   ┌───────────────┐
   │ system_config │
   └───────┬───────┘
           │ system_id
   ┌───────▼──────┐      id names the family      ┌─────────────────────────────────────┐
   │ stream_config │ ····························▶ │ message_log_1  (partitioned)        │
   └───────┬──────┘                               │ idempotency_key_1                   │
           │ stream_id                             │ compaction_head_1                   │
   ┌───────┴───────────────┐                      │ exception_queue_1 · delivery_log_1  │
┌──▼───────────────┐ ┌─────▼─────────────────┐    │ consumer_group_cursor_1             │
│ stream_config_log │ │ consumer_group_config │◀───│ claim_lease_1 · message_key_lease_1 │
└──────────────────┘ └───────────────────────┘ FK │ binding_config_1                    │
                                             from │ binding_config_log_1                │
   worker_config ─▶ worker_config_log     cursor, └─────────────────────────────────────┘
     └─▶ worker_instance                  binding
   schedule_config ─▶ schedule_cursor     tables
   migration_log

The control plane

system_config -> stream_config -> consumer_group_config is the ownership spine, each link a real foreign key with ON DELETE CASCADE. The stream_config row is where a stream’s identity and config live: name is unique, and its id is what names the family’s physical tables. A payload schema bump is not a new row here — the version sits on each message_log row (schema versions). partition_size is immutable after creation because the log’s partition boundaries depend on it staying fixed. empty_compaction_head_ttl_ns bounds how long an idle, headless compaction-key row remains before the stream janitor may sweep it.

The fleet and history tables hang off the spine with one shared pattern: worker_config, schedule_config, and migration_log each carry all three owner columns (system_id, stream_id, consumer_group_id) and a CHECK that exactly one is set — a worker belongs to the system, a stream, or a group, never two. worker_instance is the live-copy row under a worker_config: a token only its creator can match and a heartbeat-renewed expires_at, the same lease shape the lifecycle uses for claims. schedule_cursor is the scheduler’s position in each job’s schedule (next_scheduled_at, last_scheduled_at), a 1:1 runtime row beside the near-static schedule_config, so per-fire churn never touches the config row.

Two of these are append-only operator trails, never updated: stream_config_log and worker_config_log snapshot every declaration, while the stream_config and worker_config rows stay the truth.

worker_instance_log snapshots each successful claim and renewal in the same transaction as the live-row change. It copies the instance’s identity, creation time, expiry, token, and failure count. Release and expiry cleanup remove the live row without deleting these snapshots; deleting the worker removes its history. The existing manager sweep retains snapshots for 24 hours after recorded lease expiry by default. The log has no lifecycle-operation field; proposed lease-coverage evaluation uses the copied timestamps.

The family

Suffix 1 throughout — the stream’s id from the catalog.

• message_log_1 — the log. id BIGSERIAL PRIMARY KEY is the position, and the table is partitioned by id range (message_log_1_0, message_log_1_1, …), partition_size rows each. Beside the payload: routing_key, message_key, compaction_rank (NULL unless the message opted into compaction), and a sparse options document.
• idempotency_key_1 — produce dedup. The caller’s key, resolved to a uuid, is the primary key; rows expire on a TTL sweep.
• compaction_head_1 — the durable row-lock identity for one compacted message key. Its head id, schema version, and rank are either all present, naming the winner, or all NULL while the key has a lock but no head. created_at records the identity’s creation; updated_at moves when the head changes or an empty row is locked again. Compacted produce upserts the winner in its transaction, and the janitor uses the partial (updated_at, compaction_key) WHERE message_id IS NULL index to find expired empty rows without scanning materialized heads.
• exception_queue_1 — deliveries off the mainline path, PRIMARY KEY (consumer_group_id, message_id): status, the message’s key and the concurrency policy the group resolved for it, attempts, can_run_after backoff, last error, and a per-row lease. The statuses are the lifecycle state machine; the key column is what a same-key predecessor lookup reads.
• delivery_log_1 — the event trail, append-only, keyed by its own id; attempt is the run each event belongs to, and a run can have more than one (a claim handed back at a busy key gate, then the run).
• consumer_group_cursor_1 — one row per group (consumer_group_id UNIQUE): claimed and committed, plus the snapshot-fence columns (settled_head, pending_head, pending_xid) that keep claims from reading past ids whose producing transactions haven’t committed.
• claim_lease_1 — range claims: (low, high), an expiry, and a reclaims counter that quarantines a range reclaimed too often.
• message_key_lease_1 — PRIMARY KEY (consumer_group_id, message_key): at most one in-flight delivery per message key per group, the ordering opt-in.
• binding_config_1 / binding_config_log_1 — the group’s routing patterns, and the append-only trail of every declaration attempt.

Waiting for earlier producers

A consumer must wait for unfinished producers before advancing past their message ids. For orders and its processor group, producer A can take an earlier transaction id, then producer B inserts message 1 and stays open. A inserts message 2 and commits. Seeing message 2 does not make it safe to claim through 2: message 1 is still invisible.

An active poll reads the visible head and allocates its own transaction id in the same statement. That statement finishes before the claim starts. The claim waits until every transaction older than that observation has finished. PostgreSQL’s snapshot xmax does not provide this bound: an already-running producer can have an id at or above it.

An empty claim saves its observation for the next poll. Caught-up polls remain read-only; active polls allocate a transaction id. Existing skipped deliveries need reconciliation; changing the claim rule does not replay them.

The deliberate absences

• No stream_id column anywhere in the family. The table name is the scope; a cross-stream read resolves ids from the catalog first and loops the families.
• No foreign key into message_log_1. Delivery rows carry bare message_id values and cursor positions are plain ids, so retention can DROP a whole partition without a constraint check walking every referencing row.
• FKs cross back to the control plane only from the long-lived tables — consumer_group_cursor_1, binding_config_1, and binding_config_log_1 declare consumer_group_id foreign keys; the churn-heavy exception_queue_1, delivery_log_1, and the lease tables carry the same column unconstrained.
• Destroying a stream drops the family’s tables outright — cleanup is DROP TABLE, never a cross-table DELETE sweep.