@alma-harness/postgres

Reference storage adapters for Alma, with row-level security as defense in depth.

Status: pre-1.0. The API is still moving; see the roadmap for where it stands.

What it owns

• PostgresSessionStore — the conversation log. A fenced append locks its admission of the session FOR SHARE, active and inside its deadline, or throws ExecutionStateError and writes nothing (spec: fenced-event-append). The admissions table must share its schema, as in the runtime's pair.

• PostgresEpisodeStore, PostgresProfileStore, PostgresErasureWatermarks — the memory tiers. alma_memory_facts.retracted_at marks a forgotten key (spec: fact-retraction); migrateMemoryStores adds it, then verify the catalog again.

• PostgresSpendStore — the persistent budget counters (spec: spend-store). The tenant-day counter is org-keyed, so its RLS policy is org-only; the app role gets no delete on either counter — spend survives scoped purge as a financial record.

• PostgresLegacyTurnClaims — erase-only cleanup of replies retained by older deployments. Wire it as createMemoryErasure({ turns }) while any historical claims exist. It validates scope, compares session identity exactly and leaves leases untouched. It cannot acquire, claim, complete, read replies or provision tables. Missing tables/permissions fail rather than report successful erasure. Existing schema grants and app-role membership are deployment prerequisites. purgeTurnClaimsBefore and purgeExpiredLeases remain explicit historical retention functions under the separate retention role, in bounded batches. Quiesce old writers before cleanup; removing an SDK does not stop old processes.

• PostgresAuditLog — the five audit trails (spec 038), written synchronously and with no buffering wrapper: a buffered sink that loses its buffer on a crash stops writing silently, which is the failure AuditSinkError was typed to catch. Five tables rather than one jsonb bag, so "metadata only, never content" is a column a reviewer can audit. The app role gets select, insert and NOT delete/update — an audit row that can be edited is not one. Legacy writes remain write-only. Settled costs have the scoped read surface below.

• purgeAuditBefore — time-based retention, per family (spec 039). It assumes the alma_retention role, which the migration creates and grants select, delete on the audit tables, with matching RLS policies. That role exists because the app role deliberately cannot delete: erasure may not remove an audit row; retention may, and they are different actors. Running the sweep on a raw pool instead looked correct in dev and deleted nothing in any deployment not connected as a superuser — FORCE ROW LEVEL SECURITY subjects even the table owner to the predicate.

• migrateSessionStore, migrateMemoryStores, migrateSpendStore, migrateRoutineRunStore, migrateRoutineStore, migrateAuditLog — idempotent migrations; running them is the product's choice, typically at startup.

• PostgresRoutineRunStore — routine runs as metadata (spec: postgres-routine-runs): the same fire again, today's count for the ceiling, the last delivery's hash for the dedupe, durable across processes. Atomic claim arbitrates execution and submitted-batch collection under RLS. The app role gets no delete — a run record is retained like a trail — and purgeRoutineRunsBefore sweeps completed runs by age as the retention role, preserving unresolved running and submitted records. Reads of one outcome, such as the tick's open submission, use an index of their own (spec: routine-run-index); verify the store schema again after migrating. See the recovery notes.

• PostgresRoutineStore — the routines themselves (spec: clock-tick), as jsonb under the shared policy; list runs as alma_scheduler, the role the tick assumes to read every scope, created by the migration. Grant it to the login the tick connects as, like the other two roles.

• PostgresScopeFenceStore — one schema's permanent scope fences (spec: scope-fence). The execution, result, session, memory, routine and routine run migrations install alma_scope_fences and a BEFORE INSERT trigger on alma_executions, alma_execution_results, alma_session_entries, alma_memory_episodes, alma_memory_facts, alma_routines and alma_routine_runs (spec: scope-erasure); scopeFenceGateSql(table) adds the same trigger to another gated table. The trigger refuses a fenced scope's insert, by any writer, with SQLSTATE AL001 (SCOPE_FENCED_SQLSTATE), which inScope maps to ScopeFencedError. It refuses a transaction that is not READ COMMITTED. The app role may select and insert fences, never update or delete them. To fence a whole runtime, use createPostgresScopeFence in @alma-harness/runtime/postgres.

Every operation runs inside a transaction that assumes a dedicated non-superuser role and binds alma.org / alma.uid as transaction-local settings, which the RLS policies compare against, in one statement: set_config('role', …, true) is SET LOCAL ROLE (spec: transaction-preamble). Application-level scoping is the first layer; this is the second, so a forgotten WHERE cannot leak across tenants.

Checkouts use checkout(pool), listening for error until release: a connection dying mid-call rejects the call, never kills the process (spec: checked-out-connection-errors). Use it for your own pool.connect().

Upgrade window

The migrations upgrade a database last migrated by one of the two previous minor releases, and rerunning them changes nothing. Upgrade an older database in steps, each within the window (spec: upgrade-window).

Before the first non-superuser connection

SET LOCAL ROLE alma_app admits only roles the connection's login is a member of, and no migration grants that membership — it cannot know which login you connect as. Once, as a role that may grant (the migrations' superuser will do):

await grantRole(pool, { to: "svc_myproduct" });                  // alma_app
await grantRole(pool, { role: "alma_retention", to: "svc_ops" }); // the sweeps, if a different login

Re-granting is a no-op, so a product may run it at every startup. Without it the first least-privilege deployment fails every call with permission denied to set role — fail-closed, and a surprise the superuser-connected test suites cannot see (spec: erasure-reaches-the-claims).

import { migrateMemoryStores, PostgresEpisodeStore } from "@alma-harness/postgres";

await migrateMemoryStores(pool);
const episodes = new PostgresEpisodeStore(pool);

Both accept statementTimeoutMs (default 30s) and role — a query shaped by model-supplied input needs a ceiling that does not depend on the caller remembering one.

What it must never do

• Rank in SQL. Adapters filter candidates — a superset of what the tokenizer matches — and hand them to the shared ranker in @alma-harness/memory, so every backend orders results identically.
• Depend on the database's collation for correctness: case folding happens in the adapter, because ilike under lc_ctype=C folds ASCII only.
• Reinterpret a contract. It proves itself against the shared suites in @alma-harness/testing, run against a real Postgres 17 — which is why pnpm check:all refuses to pass without a DATABASE_URL (spec 031).

Routine warning metadata uses alma_routine_runs.caps_crossed; absent or empty warnings read as absent (spec: financial-warning-policy).

Idempotent cost settlement

Run migrateCostSettlementStore(pool) before constructing PostgresCostSettlementStore(pool). The migration includes audit/spend schema setup and additively extends alma_audit_cost; legacy rows retain no settlement ID and are excluded from the new reads. Grant alma_app to the service login as described above. Governed runners use this writer explicitly.

A settlement transaction increments session and UTC org-day totals once, inserts an immutable receipt, and queues each named projection consumer. After an uncertain commit, retry the identical settlement only. Changed input raises SettlementConflictError. The host must persist identity before provider dispatch and preserve priced usage for recovery; these execution contracts are not implemented by this storage adapter. Never also call AuditLog.cost or SpendStore.add for the same charge.

pending(scope, consumer) returns full receipts, at most 100 per call. Apply idempotently by scope/settlement ID, then call acknowledge(scope, consumer, id). Only configure consumers the host can drain; an empty list creates no obligations. The app role can delete pending rows but cannot update/delete financial receipts. Retention skips pending receipts and continues other audit families; its role has SELECT only on pending work. The composite foreign key also prevents raw retention SQL from removing unresolved work. Stop retries before receipt expiry.

On a large populated database, the migration needs a maintenance window: ordinary index builds block cost inserts. Alternatively, before migration add the nullable settlement columns, then prebuild the six identically named/defined indexes listed in costSettlementStoreMigrationSql using CREATE [UNIQUE] INDEX CONCURRENTLY outside a transaction. Apply the remaining migration in a short maintenance window for constraints/permissions. This delivery performs no production migration or deployment.

The JSON financial extension has DB key/shape checks; callers cannot add prompt or response fields. Typed legacy cost/tier/cache/warning columns are populated as well. turn_id stays null: operation identity is distinct and lives in the closed receipt. The DB checks shape/content surfaces; the adapter enforces value validity too. Direct SQL receipt writes are unsupported. Reads fail explicitly on a corrupt receipt rather than silently omitting financial data; reconcile such corruption through an operator. list filters occurrence, operation, model and priced tier using indexes. It is not a projection cursor: late historical settlements remain visible through pending, which has no high-water offset.

Durable execution journal

migrateExecutionStore(pool) adds alma_executions; PostgresExecutionStore(pool) implements ExecutionStore with the standard role/scope/timeout options. Migration is additive/idempotent and grants select/insert/update, never delete. A recursive SQL shape check excludes arbitrary content even inside price bands and usage; read-time core normalization rejects corrupt values. Grant alma_app membership to the application login using grantRole, as for other scoped stores.

Claims serialize through scoped unique identities. Mutation clocks are read after row locks; bounded expiry sweeps use SKIP LOCKED. A lost commit acknowledgement is an uncertain result, not permission to dispatch again: read the record or replay the same immutable claim/evidence. Recovery cannot dispatch. Paginated list is observational, not a notification delivery cursor.

Existing session erasure and audit/turn retention leave this content-free journal intact. There is no journal retention or production backfill in this migration. Governed runners use this journal. Future state extensions require upgrading readers before writers; old readers reject unknown states.

not_dispatched_cause is nullable, and a separately named CHECK allows it only on a not_dispatched record (spec: not-dispatched-cause).

Documentation

Docs index · Invariants §1 · Session store spec · Memory storage spec

Apache-2.0

Scoped execution results (spec 081)

Run migrateExecutionResultStore(pool) with the migration principal and grant the application login the configured RLS role. Construct new PostgresExecutionResultStore(pool, { maxSensitivity: "personal", maxRetentionMs: 60000, maxChars: 10000 }). The two tables alma_result_scopes and alma_execution_results force org/user RLS; the app receives SELECT/INSERT/UPDATE, no DELETE or global retention grant.

Every operation locks an exact scope guard, including first preparation and empty scope erasure. Reads also take that lock because they can physically expire content. Unknown reads may create scope metadata. Clock sampling occurs after locking; expiry nulling commits even when a conflicting prepare is rejected. erase clears pending and available rows, including expired unswept content, without deleting metadata. The host schedules bounded purgeExpired calls from its tenant/user inventory; tombstones and scope metadata remain indefinitely. Neither migration nor store changes financial or execution journals. Erasure cannot revoke input already read by a host or provider, and the host must audit its erasure composition.

Durable usage inbox

Install the new @alma-harness/execution peer and run migrateUsageInbox(pool). PostgresUsageInbox(pool, opts?) uses standard role/scope/timeout settings. Journal and inbox migrations emit the same shared shape validator and work in either order. Immutable alma_usage_inbox rows allow app SELECT/INSERT only. A separate scoped pending table permits DELETE for acknowledgement, with its receipt timestamp bound by FK. Both tables force RLS. Winner-only pending insertion and exact replay resolve lost commit acknowledgements without reviving already acknowledged work.

list retains acknowledged history; listPending selects work. Both use bounded exclusive (receivedAt,id) pages; restart every pass to catch late commits. One logical consumer owns recovery and acknowledgements. Compare full journal binding and persist recovery/disposition before acknowledgement; no subscriber fan-out, automatic settlement or redispatch. Session/result erasure and retention do not remove inbox metadata. See the execution package.

Governed financial receipts

Run migrateGovernedCostSettlementStore(pool) before using settleGoverned, getGoverned, listGoverned or pendingGoverned on PostgresCostSettlementStore. The migration also updates the ordinary settlement schema. Upgrading ordinary settlement code requires rerunning migrateCostSettlementStore for its mode flag. The existing payload CHECK is unchanged; an immutable FORCE-RLS extension stores closed request/decision metadata. Application grants are SELECT/INSERT only.

One transaction locks session then org-day counters, captures actual previous amounts, and commits increments, cost, decisions and pending work. One governed operation/call has one receipt. Cross-mode or identity/policy reuse conflicts and rolls back; replay preserves original decisions. capsCrossed remains warn-only. Block evidence never throws a monetary stop from this store. No provider is called.

The ordinary retention path skips governed costs indefinitely, even after their projections are acknowledged. Other eligible costs and audit families still expire. Product erasure cannot reset these receipts. Use the original occurrence time and policy for recovery, and never charge through both accounting writers. Governed runners and operation accounting are separate packages.

Governed financial connections require PostgreSQL float round-trip output (extra_float_digits >= 1, the PostgreSQL 12+ default); rounded float text can fail exact financial consistency checks. Preserve this setting in role/pool options.

Governed journals need both migrateExecutionStore and migrateUsageInbox. Their CHECKs call the versioned v2 shape function. Settled/completed metadata survives erasure, but historical success cannot restore expired result content. Completion and financial settlement remain separate, idempotently recoverable transactions. The journal never increments costs itself.

Governed single-call adoption adds optional operation/attempt/call columns to the routing and access trails through migrateAuditLog. Existing rows remain readable with null correlation. Run this migration with the other governed stores before using the matching single-call runner and audit adapter.

Temperature controls use the v2 structural check over a projection excluding only that new key, plus a numeric 0..2 check. Old application readers reject it, so deploy matching readers before temperature writers (spec: explicit-temperature-controls).

Operation lineage is available separately in @alma-harness/postgres-execution; it reuses the exported inScope, resolveRlsRole and resolveStatementTimeout helpers (spec: durable-operation-lineage).

Migrations pin SQL function dependencies to pg_catalog, installation_schema, pg_temp (spec: restore-safe-sql-functions). Select the trusted installation schema first on migration connections; do not grant runtime CREATE there. Function rights stay invoker and pg_dump preserves the path. Rerun current migrations before backup; frozen old startup SQL can replace definitions and remove this configuration.

Rerun execution/inbox migrations for attested context rejection; deploy matching readers first (spec: safe-context-rejection-rotation). The same rerun installs the pre-stream rejection proofs, each bound to its provider (spec: pre-stream-rejections). Never run an older release's migrations afterwards: they restore a check that refuses those proofs.

Retired turn coordination

Fresh hosts use the canonical runtime and never create alma_turn_claims or alma_turn_leases. The old constructor and migration exports are removed. Existing SQL data is not dropped or rewritten. Mixed deployments must retain the historical cleanup adapter and turnRecords: "claims" posture until their content is gone; only hosts without historical claims may declare turnRecords: "none". No role membership or execution authority is created by cleanup. Spend, financial receipts, journals and audit stores remain separate retained surfaces.

Tool choice (spec: governed-tool-choice): a separate versioned SQL helper checks the closed choice and bounded client-tool name, beside the v2 shape and rejection helpers. The CHECKs hold in either migration order and when the migrations run again. Records without the optional field stay valid. This does not grant execution authority, change RLS or persist response content in the journal/inbox.