@lssm-tech/integration.lssm-os-state-postgres
Multi-node LSSM OS persistence for PostgreSQL and Supabase. Every operation is tenant-filtered in SQL and sets a transaction-local tenant context so forced RLS provides defense in depth. The deterministic migration uses the private
lssm_os schema, indexed policy columns, append-only receipts, lease fencing, and FOR UPDATE SKIP LOCKED contention handling.
The adapter accepts a structural pool rather than a connection string. Secret custody and least-privilege role grants remain deployment responsibilities. Use a private backend role with only the required lssm_os schema/table privileges; never expose that role or a Supabase service_role credential to a client. The transaction-local tenant setting is a defense against missed SQL filters, not a substitute for Connect authorization or trusted server identity.
Lease scopes use the reversible, collision-proof lssm-scope:v1
length-prefixed tuple encoding, with tenant identity also stored separately. Legacy slash-composed keys are rejected rather than guessed or silently re-keyed. Operators upgrading a database containing legacy lease rows must stop workers, explicitly map each row to a reviewed tenant/workspace/environment tuple, and migrate it before enabling this adapter version.
Runtime Node V2
The adapter persists issued work orders, immutable claim history, monotonically fenced leases, externally verified provider receipts, and exact completions in tenant-scoped tables. Migration preflight rejects partial schemas and malformed legacy rows before DDL. Runtime tables use FORCE ROW LEVEL SECURITY, explicit tenant indexes and policies, append-only claim/receipt guards, and terminal order immutability. Migration must run through a privileged operator; runtime traffic must use the migration-created lssm_os_runtime NOBYPASSRLS role. Each acquired Runtime Node connection inspects the live role, search path, and required/forbidden grants before opening a tenant transaction; unsafe pools are rejected before state reads or writes.
Migration 0003_runtime_node_registry_v2 adds tenant-scoped, append-only node registration, heartbeat, attestation, and qualification histories. Exact tenant-leading indexes and a per-scope lock serialize concurrent rotations. Upgrade certification recomputes runtime digests with JavaScript-compatible UTF-16 code-unit ordering and length limits, including supplementary Unicode identifiers, before accepting existing history. All five registry tables force RLS. The runtime role receives only SELECT and INSERT; UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER, and schema CREATE remain forbidden and are verified on every acquired connection.
Run bun run pack:verify before publication. It builds and installs the actual npm tarball with the local runtime/spec tarballs in a clean consumer, verifies the dependency tree, and imports every public subpath under Node and Bun.
Agent application installation lifecycle
./agent-application-lifecycle implements the
AgentApplicationLifecyclePortV1 from
@lssm-tech/lib.agent-protocol-spec/protocol/application-lifecycle over
lssm_os.agent_application_lifecycle. Reads and compare-and-set run in a tenant transaction as the live lssm_os_runtime role, validate the exact installation scope and the protocol move, then call the security-definer
lssm_os.agent_lifecycle_cas function. provisionPostgresAgentApplicationLifecycleV1
is owner-only and idempotent for the initial install_disabled revision.
./agent-application-lifecycle-migration holds the owner-applied DDL: forced tenant RLS, a CAS function whose transition table is tested against the protocol table, and runtime grants limited to SELECT plus that function.
Standing mandates and Connect approvals
createPostgresAgentStandingMandateWriterV1 (Connect role) admits a mandate revision only when its owner signature over mandateDigest verifies with an active, non-revoked human key registered to the owner in
lssm_os.connect_principal_keys and not shared with the delegate; without such a key every revision is refused. Agent Host re-verifies on every read.
createPostgresAgentStandingMandateStoreV1 (runtime role) reads the current revision and reserves rate-window slots atomically (per mandate/action-class lock row). The legacy unsigned recordApproval/approvals.approved path is deprecated and always refuses: approvals are Connect operation-approval records (createPostgresOperationApprovalStoreV1) that carry a signed approver identity. Tests run these stores as the non-superuser
lssm_os_runtime/lssm_os_connect roles and prove cross-tenant isolation;
scripts/qualify-postgres-mandate-reservations.ts repeats the window and nonce concurrency proof on real PostgreSQL under the same roles, and proves isolation with unfiltered reads: each role sees its own tenant's usage and approval rows and none of another tenant's.
bun run qualify:store-kill (env LSSM_OS_QUALIFY_POSTGRES_URL, a disposable server whose role may create databases) SIGKILLs a process reserving a window slot, issuing an approval or consuming its nonce, once with the transaction open and once right after COMMIT. After the server has dropped the backend it reconnects under the same roles and proves committed state survived, an open write took no slot, record or nonce and left no lock, and retries stay idempotent or single-use.
Runtime Node V3 operation state
Migration 0010_v3_provider_receipts adds the durable evidence boundary used by Agent Host T1. It validates the signed V3 one-step projection, requires the current operation claim/attempt/fence, enforces one immutable receipt per step attempt, and preserves exact replay across restart. Tenant-forced RLS and the runtime-role audit cover the table alongside V3 operations.
Migration 0004_v3_operation_state adds tenant-forced, RLS-scoped V3 operation and terminal-receipt tables. createPostgresV3OperationState exposes the canonical Runtime Node operation-state port over SERIALIZABLE transactions with bounded serialization/deadlock retry. Creation is pristine and idempotent; version CAS guards every transition; only the dedicated authority binding can introduce the attempt, claim, and work-order fence tuple; and a terminal receipt commits in the same transaction as its terminal operation revision.
Migration preflight rejects partial, weakened, or malformed schemas and rows. The runtime role receives only select/insert/update on mutable operations and select/insert on immutable receipts; role inspection rejects excess receipt or history privileges. This is the authoritative V3 operation foundation for T1. The SQL outbox/queue admission must share this exact transaction boundary and is qualified separately before the database-only topology is complete.
T1 durable queue and co-atomic admission
Migration 0005_durable_queue_v2 adds the PostgreSQL Durable Queue V2 outbox, message, and renewal state. admitPostgresOperationAndQueue persists a pristine V3 operation and its queue admission inside one SERIALIZABLE transaction; an existing operation can never be backfilled into a new queue message.
createPostgresDurableQueueV2 provides bounded claims through
FOR UPDATE SKIP LOCKED, visibility renewal, deterministic dead-letter and redrive fencing, reconciliation, and health observations. Claim timestamps are read from the database immediately before each guarded claim transition, so a slow candidate scan cannot invalidate its own trusted time. Digest-valid redrive evidence remains durable across later claims without being rebound to their newer claim digests.
Migration 0012_queue_claim_authority keeps raw queue-message mutation denied to the runtime role while granting a narrow, tenant-bound claim-candidate function. PostgreSQL performs FOR UPDATE SKIP LOCKED inside that database- owned boundary, so real runtime-role claims do not require broad table UPDATE.
Run bun run qualify:concurrency against a local PostgreSQL server to execute the exact T1 concurrency shape: 10,000 temporary durable messages, 100 public claim calls per message, one fenced winner, and one simulated effect record per trial. The command uses a uniquely named temporary database, the complete current migration set and its runtime role, an owner-provisioned profile bound to the actual server and pg client versions, fixed process shards, and unconditional database teardown. It never contacts an external database or promotes this credential-free local simulation to production qualification.
The small-soak package test requires initdb and pg_ctl on PATH. It starts and stops an owned temporary PostgreSQL cluster on a separate local port; it does not reuse an application database.
Run bun run qualify:small-soak for the exact T1 S-small gate: an evenly paced 10 operations per second for two hours through co-atomic operation/queue admission, lifecycle issuance, claim and dispatch, one simulated provider effect, immutable provider and operation receipts, acknowledgment, settlement, and cleanup release. The command requires 72,000 complete lifecycles with exact counts, zero loss or duplicate effect, control-plane p99 below 500 ms after subtracting only the simulated provider-effect interval, and schedule-lag p99 within the 100 ms arrival interval. It reports PostgreSQL/client/runtime and host-capacity bindings, emits progress every minute, uses a unique local database, and tears it down unconditionally. The harness is implemented, but S-small remains blocked until the complete command passes on one exact integrated release SHA; abbreviated runs and overloaded-host probes are not qualification evidence.
Run bun run qualify:dr to exercise three chained local PostgreSQL disaster- recovery generations. The command uses pg_dump and pg_restore, reapplies the complete migration preflight after every restore, and reloads the exact terminal V3 operation, immutable receipt, standard order, and cleanup order through runtime-role public readers. It enforces receipt RPO 0, the v4 15-minute RTO, and the 30-minute reconciliation ceiling, then removes every temporary database and archive. This is credential-free local-live evidence; production backup media, encryption/key recovery, operator runbooks, regional failover, and live RTO qualification remain release blockers.
Run bun run qualify:process-kill to exercise the T1 completion boundary with actual process termination. It co-atomically admits 100 isolated operations, persists one simulated effect and terminal receipt per operation, then SIGKILLs one worker per trial after terminal verification but before acknowledgement. A fresh worker reclaims the expired lease and commits the exact acknowledgement. The command requires 100 kills, recoveries, acknowledgements, and effects with zero duplicates, pins PostgreSQL to the local /tmp socket, and removes the temporary database unconditionally. Other authority-to-settlement kill boundaries and production-host evidence remain separately blocked.
Run bun run qualify:outbox-kill to exercise actual termination immediately after the co-atomic T1 operation/outbox/message transaction. It SIGKILLs 100 workers after their first successful admission, then requires a fresh process to receive exact already_applied replay for every request. Final inventory is exactly 100 accepted operations, 100 outbox rows, and 100 queue messages with zero effects. The temporary database and every child are removed on success or failure. Remaining authority, dispatch, effect, receipt, cleanup, and settlement boundaries are qualified separately.
Run bun run qualify:authority-kill to SIGKILL 100 workers after atomic standard/cleanup authority issuance. Fresh processes call the same public API again; exact durable replay returns without recharging the UTC-day budget or reinserting orders, reservations, or cleanup authority. The command requires 100 operations, 200 orders, 200 reservations, 100 cleanup records, and exactly 100 EUR micros in each budget class.
Run bun run qualify:dispatch-kill to SIGKILL 100 workers after the guarded dispatch transaction binds its running operation tuple and moves only the normal reservation to dispatched. Fresh processes must observe the exact already-committed attempt/claim/fence without another transition. Final counts are 100 running operations, 100 dispatched normal reservations, 100 held cleanup reservations, and zero effects.
Run bun run qualify:effect-kill to SIGKILL 100 workers after one durable simulated provider mutation but before any provider or operation receipt. Fresh workers observe the existing effect, never insert it again, and complete one terminal operation receipt. Final counts are 100 effects, 100 succeeded operations, 100 operation receipts, and zero duplicates.
Run bun run qualify:receipt-kill to SIGKILL 100 workers after the effect and immutable V3 provider receipt append but before terminal operation completion. Fresh workers load the exact step/attempt receipt and complete without another effect. Final counts are 100 effects, 100 provider receipts, 100 operation receipts, and 100 succeeded operations.
Run bun run qualify:cleanup-kill to SIGKILL 100 workers after one permit-bound cleanup effect but before signed absence completion. Fresh workers exact-replay the same cleanup permit, observe one existing effect, and commit teardown verification. Final counts are 100 permits, effects, absence evidence records, and teardown-completed authorities with zero duplicates.
Run bun run qualify:settlement-kill to SIGKILL 100 workers after settlement evidence commits. Fresh workers call the same public settlement API and receive exact durable replay. Final counts are 100 settled normal reservations, evidence records, and verification records with zero effects.
Migration 0015_qualification_cleanup_authority adds the narrow tenant-bound cleanup-authority row lock required by real PostgreSQL. Immutable order reads no longer request a row lock, and cleanup/reservation mutable locks remain inside database-owned functions while raw UPDATE stays denied.
Queue acknowledgments are transport evidence, never effect authority. The adapter accepts one only after the exact referenced V3 operation revision is terminal and the claimed receipt digest exists in durable operation history. Checkpoint acknowledgments stay fail-closed until an authoritative checkpoint store is composed. Migration preflight rejects partial, weakened, or malformed schemas and payload bindings. Append-only outbox/renewal history and guarded message transitions reject direct identity, state, attempt, fence, receipt, and deletion attacks. Apply migration 0005, update the lssm_os_runtime grants, and call provisionPostgresQueueProfile through a migration-owner connection before enabling the T1 database-only topology. Runtime-role composition only reads and verifies that immutable qualified profile; it cannot provision or replace one and fails closed while the profile is absent.
T1 Runtime Topology V1 state
Migration 0006_runtime_topology_state_v1 adds tenant-forced active topology profile and transition-receipt stores. A privileged owner must call
provisionPostgresTopologyProfile before runtime traffic starts. Runtime workers can read the exact profile and invoke only guarded observation and transition CAS functions; they cannot write raw topology rows.
createPostgresTopologyState binds every call to the full tenant, workspace, environment, product, and application scope plus topology generation and exact component, capability, adapter, server, and client digests. The first receipt must be observed; later updates preserve the operation, claim, attempt, fence, target topology, and completed-step history while following the frozen graph. Topology receipts remain evidence and state—not Connect or Runtime Node effect authority.
The T1 queue implements the additive fenced claimed-envelope read. It uses the database clock and returns the immutable envelope only for the exact live claim digest and queue fence. This closes transport context loading without widening the queue into Runtime Node execution authority.
Durable scale policy and decision history
Migration 0011_topology_scale_history adds an owner-provisioned immutable scale policy, a tenant-forced decision head, and append-only scale decisions.
createPostgresScalePolicyVerifier gives the deterministic control-plane planner a runtime read of the exact qualified policy, while
createPostgresScaleDecisionHistory provides restart-safe latest-decision loading and serializable compare-and-set recording. One hundred controllers starting from the same head produce one winner; the others fail closed without overwriting history.
The runtime role can select these tables and execute only the guarded decision record function. It cannot provision policies or insert, update, or delete raw history. The database function revalidates the exact topology binding, qualified policy, canonical decision digest, ordered observations and reasons, replica step, quota, concurrency, cost, freeze mode, and reversible target before advancing the head. This is durable planning evidence only: it does not authorize or execute a topology effect, and it does not claim live autoscaling qualification.
V3 qualification issuance
Migration 0007_v3_qualification_issuance adds immutable signed-order storage, reservation and cleanup-custody state, and one global UTC-day budget ledger.
Migration 0008_v3_qualification_dispatch adds the sole PostgreSQL transition that binds a queued V3 operation to its pre-issued attempt, claim, and fence while moving the exact normal reservation from held to dispatched in the same transaction. Apply it before composing authorizeDispatch. The authority role can no longer update reservation or cleanup rows directly; it receives only the guarded dispatch function until later evidence and cleanup transitions are qualified. Connect verification runs outside the database lock and the final transaction rechecks the operation, signed order, trusted time, and reservation CAS before either row changes.
Migration 0013_qualification_operation_authority supplies the authority role with a narrow tenant-bound FOR UPDATE function for the final operation CAS. Real PostgreSQL requires UPDATE privilege for a raw locking read; the function keeps that lock inside a database-owned boundary while raw operation UPDATE remains denied to lssm_os_authority.
Migration 0014_qualification_reservation_authority applies the same narrow, tenant-bound lock boundary to the normal reservation used by dispatch. Raw reservation UPDATE remains denied. Exact lifecycle issuance retries first compare the complete durable order/reservation/cleanup graph and return without charging budget or inserting rows again; partial or substituted replay fails.
The separate lssm_os_authority role may verify accepted operations and issue one standard/cleanup lifecycle inside a SERIALIZABLE transaction. Connect is verified outside the lock and the exact operation is rechecked before commit; the database enforces the 800,000/200,000/1,000,000 EUR-micro caps across all tenants.
The runtime role receives read-only access to signed orders and no reservation, cleanup, or budget access. createPostgresQualificationAuthorityIssuance
therefore requires a dedicated authority pool, while
createPostgresQualificationAuthorityOrderReader is safe for Agent Host T1. This slice activates standard dispatch only. Cleanup, settlement, and provider effects still require their own durable authority migration and evidence.
Migration 0009_v3_qualification_cleanup_evidence adds immutable signed evidence, cleanup permits/nonces, and two-principal custody receipts. The V2 authority factory verifies observation, evidence, and custody dependencies outside the database lock. A dedicated no-login verifier role may then persist only an immutable digest-bound verification record; it cannot execute lifecycle transitions or mutate authority state. The separate authority role can consume that exact record during a serializable reload and guarded state transition, but cannot mint verification records. verificationServiceRef binds the configured verifier identity, and the two pools must use distinct credentials. Cleanup permit issuance atomically consumes the global nonce, persists the permit, moves custody to executing, and dispatches the cleanup reservation. Settlement amounts come only from signed billing evidence; release requires both not-applied and terminal-fence evidence; signed absence is the only path to teardown_verified. Runtime and authority roles cannot write lifecycle history or mutate reservation/custody rows directly.
The additive V2 order reader dereferences standard and cleanup orders after a restart through exact scope, operation, id, and digest bindings. Provider decommission and durable indeterminate recovery remain a later Runtime Node effect slice; migration 0009 does not claim that provider boundary complete.
The additive QualificationAuthorityOrderReadPortV3 can discover the single issued standard order for an exact tenant/workspace/environment/operation tuple. This allows server recovery after issuance without accepting an authority digest from a member request. Absence, ambiguity and malformed or cross-scope payloads return no order. A recovered order still requires current Connect verification and dispatch authority before execution.
