@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.

• PostgresEpisodeStore, PostgresProfileStore, PostgresErasureWatermarks — the memory tiers.

• 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. The migration upgrades the outcome constraint on existing tables. See the upgrade and 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.

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. Application-level scoping is the first layer; this is the second, so a forgotten WHERE cannot leak across tenants.

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. Rerun migrateRoutineRunStore before deploying the financial-warning-policy packages; existing records are preserved, and absent/empty warnings read as absent. Scope, claims and unresolved-run retention keep their existing contracts.

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.

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 journal upgrades require both migrateExecutionStore and migrateUsageInbox before new writers. They bind CHECKs to the versioned v2 shape function, so replaying older startup SQL cannot downgrade governed input checks. Run these locking migrations in a maintenance window; upgrade strict readers first. 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 migration replay remains compatible; old application readers do not. 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). Old SQL replay retains the stricter checks.

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 writers require matching readers and rerunning both migrateExecutionStore and migrateUsageInbox first (spec: governed-tool-choice). The upgrade preserves old records without the optional field. A separate versioned SQL helper checks the closed choice and bounded client-tool name; existing v2 shape and rejection helpers retain their contracts. Current CHECKs survive replay of earlier startup SQL in either migration order. Old application binaries reject new-field records, so deploy readers before enabling writers. Apply the locking CHECK upgrade in a maintenance window for populated tables. This does not grant execution authority, change RLS or persist response content in the journal/inbox.