Schema and procedure catalogue

Everything here lives in the PostgreSQL schema queen, created by server/sql/schema.sql. That file plus exactly 23 files under server/sql/procedures/ (001–023) are embedded in the binary with include_str! and applied at every boot in lexical order under session advisory lock 778120010. All of it is idempotent, so re-apply is a no-op, and QUEEN_APPLY_SCHEMA=0 skips the step entirely.

The apply only creates. There are no teardown files, no migration blocks and no upgrade ALTERs: a deployment starts from an empty database, and schema.sql’s CREATE TABLEs declare the final shape directly. Pointing this build at a database written by an earlier schema is unsupported (fresh database, full stop). See Retired objects for the history.

The engine needs CREATE SCHEMA and no extensions, with one floor worth naming: the cgm_identity_uk index below uses NULLS NOT DISTINCT, which needs PostgreSQL 15 or newer.

The file map: 001_log_schema and 002_streams_schema are the DDL, 003_log_push through 007_log_streams are the engine’s write, read, ack, maintenance and streaming paths, 008/009 are the streams registry calls, 010_log_admin and 011_log_stats are the log-native admin and stats surface, and 012–023 (minus 020) are the shared management plane: configure, queue deletion, consumer groups, status, messages, traces, stats readers, metrics, Prometheus. 020_log_partition_counters is a special case: it only attaches triggers, and its position after both 001 (the table) and 019 (the trigger functions) is the point of it.

Queue identity: queen.queues (schema.sql)

One row per queue, and the single queue identity in the system: queen.log_partitions.queue_id references queues(id) ON DELETE CASCADE directly, so every partition→config join is by id. There is no separate log-engine queue table and no (tenant_id, name) bridge join between configuration and data, which makes the cross-tenant name-join hazard class structurally unrepresentable (the retention work list joins by id).

Identity is (tenant_id, name), enforced by the unique index queues_tenant_name_uk. The columns the engine reads directly:

The rest of the row is the queue configuration /configure writes: retry limit and delay, DLQ flags, delayed_processing, window_buffer, the retention policy columns, encryption, max_wait_time_seconds, max_queue_size, min_pop_wait_time. See Queue options.

Two things about this table are worth being precise about:

• The lease_time default of 60 is a truth fix, not a behaviour change. An implicitly push-created queue has always leased at 60 (the pop path read that default) while the config display used to show 300 from a column the pop path never read. The display lied; now the one column both paths share tells the one truth. /configure still writes an explicit 300 when leaseTime is omitted, so configured queues are unchanged.
• There is no storage column. One engine, nothing to select. The wire still echoes storage: "segments" as a hard-coded literal (012_configure, 010_log_admin, 011_log_stats) for compatibility.

A queue row is created by the first push (003_log_push provisioning), by /configure (012_configure), by the transaction wire (005_log_ack), by a streams sink (007_log_streams), and by a wildcard pop or subscription registration on a queue that does not exist yet (004_log_pop). That last one is a deliberate widening: the pop writes a durable queue-scoped consumer_groups_metadata row, whose FK needs the queue row, and subscribe-before-first-push is legitimate, so the FK must not turn an early subscriber into an error. The honest flip side: a consumer typo now creates a queue row, where it previously left only an orphan watermark.

Queue deletion is one call: queen.delete_queue_v1 (013_analytics) resolves the id, deletes the queue’s log_txns and log_dlq rows explicitly (both FK-less by design, the cascade cannot reach them), then deletes the queues row and lets the FK cascade take log_partitions → log_segments/log_consumers, consumer_watermarks, the queue-scoped consumer_groups_metadata rows, queue_lag_metrics and stats. Previously this was two non-atomic steps (the SQL delete plus a broker-side log-table teardown).

Log tables

queen.log_partitions (001_log_schema)

One row per partition, and the per-partition write serializer.

Index: idx_log_partitions_queue_write (queue_id, last_write_at), the wildcard candidate scan’s hot filter. Storage parameters: fillfactor = 70, autovacuum_vacuum_scale_factor = 0, autovacuum_vacuum_threshold = 500, autovacuum_vacuum_cost_delay = 0, vacuum_truncate = off.

The row is deleted only by the cleanup phase, and only once the partition is empty and both its timestamps are older than PARTITION_CLEANUP_DAYS. Deleting it cascades to log_segments (none, by definition) and log_consumers, so the group cursors go with it; a later push recreates the partition under a new id, allocating from last_offset = -1 again. See Retention internals.

queen.log_segments (001_log_schema)

The messages.

PRIMARY KEY (partition_id, base_offset) and no secondary indexes: the PK is also the pop path. blob is SET STORAGE EXTERNAL. Autovacuum: scale factor 0.1, insert scale factor 0.3, cost limit 4000, cost delay 0, with the same values mirrored onto the TOAST table.

queen.log_txns (001_log_schema)

The hash sidecar. Written on every push, whether deduplication is enabled or not, because ack-by-transactionId resolves through it.

The missing foreign key keeps FK-trigger cost off the purge path, at the price of one obligation: whatever deletes a partition must delete these rows itself. The purge phase walks live partitions, so a row orphaned by a partition delete would be unreachable forever. Exactly two things delete partitions, log_partition_cleanup_step_v1 and delete_queue_v1, and both delete the sidecar explicitly first.

queen.log_consumers (001_log_schema)

Coordination state per (partition, consumer_group).

Storage parameters: fillfactor = 50, autovacuum_vacuum_scale_factor = 0, autovacuum_vacuum_threshold = 500, autovacuum_vacuum_cost_delay = 0, vacuum_truncate = off.

queen.log_dlq (005_log_ack)

Dead-lettered frames, as snapshots.

Index: idx_log_dlq_partition_failed_at (partition_id, failed_at DESC).

Nothing in the retention cycle purges this table. Because these rows have no foreign key either, a partition that still holds one is never reclaimed by the cleanup phase: dropping the partition would leave the snapshots unreachable rather than clean them up, since every DLQ reader joins through log_partitions. (delete_queue_v1 does clear them, explicitly, as part of deleting the queue.)

Shared tables the log engine uses

These are created by schema.sql (and 019_worker_metrics for the metrics tables) and are not engine-specific. Since the queue-identity merge they key on queen.queues(id) wherever they used to carry a queue name, so a deleted queue cascades its shared rows away.

Procedures, file by file

Every function below is in the schema queen. Where a signature ends in p_tenant UUID DEFAULT '00000000-...0001', the parameter was added last with a default so an unscoped caller lands on the default tenant, and the older signature is explicitly dropped on re-apply so no ambiguous overload survives.

001_log_schema.sql (tables)

The four log_* tables above (log_dlq lives in 005_log_ack), plus one helper.

003_log_push.sql (push)

log_push_one_v1 is also called by the transaction wire and the streams cycle, so there is exactly one place in the system where an offset is assigned.

004_log_pop.sql (pop)

log_pop_v1 is DROP-and-CREATE rather than CREATE OR REPLACE, because its OUT row type is part of the signature and OR REPLACE refuses return-type changes, which would brick the boot re-apply.

005_log_ack.sql (ack, lease renewal, DLQ, transactions)

Also creates queen.log_dlq (above).

get_dlq_messages_v1 used to be defined here too; it is not any more. 010_log_admin applies after this file and redefines the same signature, so the copy here was overwritten at every boot and never ran. 010_log_admin owns it now.

006_log_maintenance.sql (retention steps)

This file also adds idx_retention_history_executed_at (executed_at DESC), the index the retention analytics readers scan.

007_log_streams.sql (streaming)

010_log_admin.sql (admin and observability)

Ported from the retired seg-era consumer-group, observability and traces files. The rows-engine legs those redefinitions once carried are gone. There is one engine, so every body here runs unconditionally, and the join to queen.queues on p.queue_id is itself the queue-existence guard (it supplies namespace, task, priority and tenant scoping in the same touch).

Three procedures from the retired observability file were deliberately not ported, because they have no Rust call site: get_queue_messages_v1, has_pending_messages and get_prometheus_metrics_v1.

011_log_stats.sql (stats and analytics)

020_log_partition_counters.sql (triggers)

Defines no functions; attaches trg_partitions_created_counter and trg_partitions_deleted_counter to queen.log_partitions. It is a separate file for two reasons. It must run after both 001_log_schema (the table) and 019_worker_metrics (the trigger functions), so a fresh database gets the attachment on its very first boot. And the attachment is conditional on the trigger being absent: DROP TRIGGER takes ACCESS EXCLUSIVE and CREATE TRIGGER takes SHARE ROW EXCLUSIVE on the engine’s hottest table, so re-running the pair every boot would stall every push and pop behind each rolling restart. The trade-off is that changing a trigger definition needs a manual drop; changing the function body still takes effect on restart, since CREATE OR REPLACE FUNCTION rebinds it without touching the table.

Advisory locks

The one global lock order

Every multi-row locker follows it, which is what makes the write paths deadlock-free by construction:

1. provisioning inserts, each ORDER BY its own unique key;
2. queen.log_partitions rows, ascending by id;
3. queen.log_consumers rows, ascending by (partition_id, consumer_group).

Consumer-row locks are only ever taken after all partition-row locks, so the two lock spaces cannot form a cross-space cycle. The ack path takes only consumer locks and never a partition lock; the per-partition retention steps take exactly one partition lock each, so they cannot deadlock against a multi-partition locker regardless of iteration order.

log_partition_cleanup_step_v1 is the one maintenance step that holds many partition locks at once, so it is bound by rule 2 like any other multi-partition writer: it takes them ascending by id in a single statement, with SKIP LOCKED so it steps over a partition a pusher is holding instead of queueing in front of the write path.