Postgres storage internals

Use this page when operating or changing @runlane/postgres-storage. For normal setup, use Postgres storage driver.

Logical collections and physical tables

The raw storage layer has one physical table for every closed StorageCollectionName value.

RunEventIdOwners is reserved for adapter compatibility. Core instead uses each opaque event UUID as the canonical RunEvents record ID and does not write that collection.

RunCompletionWaitPresence stores one small monotonic row for each source run id ever used by a durable completion wait. Ordinary runs do not create one. Core uses an absent row as exact proof that it can skip the waiter index; a present row only means it must query the canonical RunsWaitingForCompletion index. Run pruning does not delete these rows because registration and waiter release currently commit separately. Reclaiming them safely requires a future atomic or reference-counted wait-edge protocol.

Most raw tables use (partition_key, record_id) as the primary key. runlane_runs uses (environment_key, run_id), while canonical events use their event UUID as record_id in (environment_key, record_id). Every raw table stores record_version bigint and record_document jsonb.

Run rows also have record_payload jsonb. Writes remove value.payload from record_document and store it in that column. Raw and operator reads reconstruct the document from both columns; the payload has one physical owner. SQL NULL means no separated payload, while JSON null remains an explicit payload value. Replacements preserve the existing payload datum when its value is unchanged, avoiding repeated TOAST writes. Opaque documents without an object at value remain intact.

An exact canonical run and initial Created event inserted by the same mutation batch can also share that run payload. The event omits value.event.payload from record_document and sets payload_from_run; raw and operator reads join the canonical run by (environment_key, run_id) and reconstruct the logical event. Before the run payload changes, the run is deleted, or the event is replaced or transferred, the mutation statement materializes the old payload into the event and clears the marker. That copy-on-write step preserves the event's independent retention lifetime and rolls back atomically with the triggering mutation. Events that are opaque, malformed, copied from another batch, or otherwise do not prove the exact relationship keep an inline payload.

A projected raw index must supply both its partition and sort columns or neither. Database checks reject incomplete pairs.

runlane_runs and runlane_record_run_events also carry nullable operator columns for contract-owned filters and ordering. A valid canonical document and matching physical identity populate those columns in the same write. An opaque document leaves them null. There are no companion operator tables or duplicate documents.

One non-cycling bigint sequence, runlane_record_versions, assigns versions across all raw tables. Rollbacks may leave gaps. Treat versions as opaque fences, not counters.

Named indexes and physical indexes

Most contract-owned indexes map to one physical B-tree path. RunEventsBySequence merges typed run_id and sequence columns for canonical rows with a generic index for opaque raw records.

The index columns hold the canonical encoded partition and sort values. Text used for contract ordering has COLLATE "C". Query order is encoded sort, then encoded record key. Partial indexes omit records that have no entry for that logical index.

Canonical run-event rows are the exception to physical duplication. Their event UUID is the primary-key record_id, while sequence_index_native marks rows whose RunEventsBySequence value is represented by typed run_id and sequence columns. The partial (environment_key, run_id, sequence, record_id) index gives canonical rows stable sequence ordering without imposing a provider-specific uniqueness rule. Those rows leave the generic encoded sequence columns null. Opaque raw rows keep the complete generic pair. Advisory reads merge bounded pages from both paths. Exclusive canonical-only reads use the typed native path; a mixed opaque/native page must merge both candidate sets before SKIP LOCKED to preserve global ordering without locking a losing row.

The canonical run and event tables have additional indexes for created time, updated time, due time, event occurrence time, and event type. Those are operator-read indexes, not StorageIndexName mappings.

Operator read projection

getRun() and run lists filter and order canonical runlane_runs rows through normalized task, queue, status, time, key, source, and lease-expiry columns. Payloads, results, failures, and complete lease state come from the same logical document; no volatile-field overlay is needed.

Event lists first limit indexed metadata from runlane_record_run_events. A lateral run-primary-key lookup determines parent visibility and supplies any referenced initial-event payload. A second lateral event-primary-key lookup returns the reconstructed document only for a visible parent; an orphan therefore contributes only its small cursor metadata. Both directions derive continuation from the last scanned metadata row, including hidden orphans, so the bounded pages share one SELECT snapshot without gaps or repeated candidates.

A raw run or event enters this projection only when its versioned codec succeeds and its physical key matches the identity inside the document. Other opaque raw records remain point-readable but stay out of operator results.

Replacing a canonical visible run rewrites its complete operator columns, including clearing optional fields that are absent from the replacement. A tombstoned or opaque replacement clears those columns. Operator-column checks and unique indexes run in the same transaction as the raw mutation, so a derived-value failure rolls back the write.

Run and event records have independent raw retention and no foreign key between them. Deleting a run therefore retains its canonical events until explicit child cleanup. The retained event row's (environment_key, record_id) primary key also retains its event-UUID reservation; deleting that row releases it. Operator event reads hide retained children through the parent lookup.

Tombstoning hides the run and every operator event in the same commit. Operator cursors bind all filters and sort inputs. Their keyset predicate remains inside the environment and optional run-id scope.

Transaction and replay algorithm

The driver uses READ COMMITTED and optimistic callback execution.

1. An attempt reads point records optimistically and caches present and absent versions.
2. Index queries return candidates but do not add them to the point-read set. Explicit exclusive selection uses FOR UPDATE OF records SKIP LOCKED, carries each locked tuple directly into later point reads, and locks only the returned page without a lookahead row.
3. The first now() seals the read set. PostgreSQL acquires advisory key locks, locks present rows, validates every cached version, and only then samples clock_timestamp(). The locking read returns keys and versions; it does not transfer or decode the cached documents again.
4. If the callback stages mutations without calling now(), the commit statement seals the read set and locks the canonical union of read and mutation keys.
5. One statement validates present and absent expectations and applies raw mutations in groups by collection and operation. Each group uses a data-modifying CTE over a bounded JSON rowset, so statement shape does not grow with the number of records in a group.
6. Conditional inserts use ON CONFLICT DO NOTHING. PostgreSQL checks that every group changed exactly as many rows as staged mutations. A failed check raises a serialization failure inside the statement, rolling back every group before commit.
7. Derived operator columns and transactional wake notifications update on the same connection and inside the same transaction.
8. Optional ordered append takes the canonical head advisory lock, allocates contiguous positions, and inserts the batch in one statement. This happens after point mutations and derived operator columns, immediately before commit. Generated record keys also participate in advisory locking so append cannot bypass absent point-read dependencies.
9. PostgreSQL commits once. If no earlier operation opened a transaction and persistence needs one statement, that statement commits implicitly. Candidate reads, sealed transaction time, replay admission, and ordered follow-up statements retain an explicit transaction. Any failed check rolls the attempt back, including the allocated observation positions.

Advisory lock keys use disjoint namespaces before PostgreSQL hashes them with hashtextextended: physical records use canonical encoded record keys, while the Created-payload copy-on-write dependency uses a provider-private run/sequence key. Blind creates join the physical lock set, so they cannot bypass an in-flight absent read.

Ordered append uses the existing observation head and record relations, document envelope, and index encoding. It interoperates with point writers using those records. Stream position allocation is transactional; record-version sequence allocation is not. A rollback can leave gaps in record versions but never in appended stream positions.

The migration-owned runlane_assert_record_write(valid, error_code) function raises an exception when a native write check is false or null. It does not read or change application records and contains no lifecycle decisions. Mutation counts, generated-key locks, and append counts are checked in PostgreSQL, so an eligible write can release the allocator at implicit commit without waiting for JavaScript validation and a separate COMMIT request. The function uses RAISE EXCEPTION; disabling PostgreSQL's debugging assertions cannot disable these checks.

When an attempt must replay, the next attempt first takes blocking advisory locks for the previous read set in canonical order. The server-side lock_timeout is two seconds. Busy locks, changed dependencies, serialization failures, and deadlocks are replayable.

maxTransactionAttempts defaults to 8. Failed attempts use small downward-jittered backoff capped at 25 milliseconds. Exhaustion becomes a retryable StorageConflict error.

The callback may run again. Do not perform external effects, user code, random identity generation, or externally meaningful logging inside it. Do not open a nested storage transaction.

An error thrown by the callback returns by identity. It is not relabeled as a provider failure. A nested storage transaction can conflict with locks already held by its replaying parent and end as a bounded storage conflict.

Migrations and compatibility

The package owns schema version 1. Its single 0000_initial.sql baseline creates the current record tables, indexed operator columns, separate run payload column, initial-event payload references, event-ID-keyed canonical records with typed native sequencing, and native record-write assertion function. Drizzle generates the table/index SQL, snapshot, and journal from the schema declarations. The assertion function is custom SQL in that same migration because Drizzle does not model it.

Pending migrations apply transactionally, including their ledger entries. Explicit migration tooling rejects incomplete, changed, or newer history before applying pending migrations. Driver startup does not inspect or change migrations. The migration account needs permission to create a function in the target schema; runtime accounts need permission to execute it. A missing assertion function is a configuration error, not a reason to retry a write without its checks.

The schema shipped with Runlane 0.3 was intentionally replaced by this baseline. A 0.3 database cannot be upgraded in place and must use a fresh schema; its history must not be rewritten to claim compatibility. Starting with the 0.4 baseline, released migrations are append-only. Roll forward with a new migration, or restore a database snapshot when a rollback must restore the previous schema.

The migration runner takes a schema-scoped advisory lock, so concurrent deployment jobs settle on one migration owner.

Default profile

The driver reports durable storage, strong indexes, and a transaction-authoritative clock.

Applications should keep payloads much smaller than the hard record limit.

Verification

Run pnpm --filter @runlane/postgres-storage test:postgres:local against a disposable test database. The live suite covers migrations, tamper detection, conformance, operator-column rollback, contention, clock-after-lock behavior, and operator-query parity.

Package tests derive every logical table and named index from the canonical schema declarations.