@lssm-tech/integration.personal-os-state-postgres
Server-authoritative PostgreSQL state for PersonalOS. It provides subject-bound immutable events and receipts, idempotent optimistic commits, encrypted snapshots/projections, transactional outbox delivery, and export/deletion jobs. An integrity-only inspector compares row counts and encrypted digests after a host-managed backup restore without exposing personal values.
The local PGlite restore rehearsal archives the complete data directory of an in-memory primary to an isolated backup, discards the primary, restores a new cluster from the archive, and proves encrypted manifest parity, snapshot hydration, and idempotent command replay. Each step runs under a bounded deadline that names the stalled step. A post-restore digest mutation is detected. A separate on-disk test closes and reopens a file-backed cluster and proves the same manifest parity, snapshot hydration, and audit replay. These repeatable local rehearsals do not qualify managed PostgreSQL backup transport, KMS recovery, regional failover, operator runbooks, recovery objectives, or a production restore drill.
The adapter never encrypts data itself: callers must supply ciphertext, nonce, algorithm, key reference, canonical AAD digest, and cipher-integrity digest from their managed key service. Snapshots persist the exact subject ref and semantic state digest separately from randomized ciphertext. Event envelopes contain digests and references only. Apply the migration before serving traffic; forced RLS is mandatory, and each operation uses transaction-local subject scope.
Rights jobs, consent, sharing, delegation, connection, and deletion commands are online-only and must be authorized before reaching this adapter.
The server-only ./personalization subpath adds a durable BehaviorStore for PersonalOS behavior evidence. It revalidates exact subject, tenant, event, purpose, active consent, revocation freshness, retention, and pre-sanitized metadata before encryption or database effects. PostgreSQL stores minimized indexes, digests, consent/evidence references, and host-produced AES-256-GCM ciphertext; it never stores decrypted event JSON. Reads verify both cipher and semantic event integrity before returning evidence.
Corrections insert the exact authorized replacement before superseding the original. Missing or cross-scope replacements cannot drop data. Export and delete require fresh exact rights decisions and leave digest-bound idempotency receipts. Expired ciphertext is purged with cascading delivery work.
./personalization-delivery provides leases, retry, stale-worker rejection, bounded dead-letter, and dead-letter-count primitives for host workers.
The server-only ./personalization-preferences subpath persists explicit seven-dimension preference patches for the exact subject and tenant. Only user, workspace-user, and surface scopes are durable. Every save or reset uses fresh subject-only authorization, a domain-separated request digest, optimistic revision, and exact idempotency. Reset writes an encrypted empty explicit patch at the next revision, so stale commands cannot resurrect prior choices. Export and deletion require fresh rights authorization; PostgreSQL stores ciphertext, semantic/cipher/AAD digests, opaque scope keys, and digest-only receipts—never the preference values themselves.
The server-only ./personalization-denial-audit subpath records denied export and fine-tuning decisions in a forced-RLS, subject-and-tenant-bound table. A trusted verifier must bind every categorical field and the canonical record digest before insertion. Exact retries replay, idempotency or operation collisions fail closed, and the table never stores the attempted payload. Behavior and preference exports derive the audit request digest from the exact rights request and record or replay the denial before returning the authority failure. If the denial recorder or integrity port is absent, export fails closed as an audit-composition error rather than emitting an unaudited denial.
PGlite coverage includes disk restart, forced-RLS isolation, ciphertext substitution, consent denial, metadata rejection, batch rollback, replay and idempotency conflict, correction no-data-loss, export/delete, expiry purge, and worker retry/dead-letter behavior. Preference coverage additionally proves revision conflict, reset, rights denial with no data loss, tenant isolation, ciphertext tamper denial, restart hydration, and value-free receipts. Managed KMS operations, backup/replica/cache deletion propagation, production alert delivery, and canary evidence remain host qualification gates.
Production hosts use commitState to persist the encrypted aggregate snapshot in the same transaction as its command result, exact authority receipt, event, and outbox records. getSnapshot, loadEvent, and (recordedAt,eventId) cursor pages support restart hydration and deterministic workers. The compatibility
commit method remains available for event-only callers, but it is not the production workspace state seam.
Both commit methods require a configured authorityReceiptVerifier and the two-phase authority proposal (draft plus before/proposed state digests). Exact command/receipt equality alone is not authority: the verifier must validate its signature/replay evidence and return an integrity-bound canonical receipt digest. Missing, stale, mismatched, or unverified results fail closed before persistence. The adapter recomputes AAD and cipher integrity, rejects subject-version or proposal substitution, and never compares receipt semantics to ciphertext bytes.
PostgresPersonalAuthorityDecisionStore durably records verified denied and confirmation-required outcomes using only command/receipt/draft/proposal digests, minimal identifiers, and timestamps. It never stores raw reasons, consent detail, evidence payloads, events, snapshots, or outbox work.
The server-only ./rolemorph-audit subpath implements the canonical durable RoleMorph audit sink. It requires an exact subject-bound store plus a host-keyed pseudonymizer, rejects undeclared or payload-like fields, and stores only the minimized policy decision with a canonical digest. A successful write creates pending delivery work before the governed result proceeds. Exact worker leases support acknowledgement, bounded retry, dead-letter health, and explicit incident recovery. Forced RLS hides other subjects, and the backup inspector includes audit row counts and record digests. External log shipping, alert routing, managed PostgreSQL recovery, and production canaries remain host gates.
RT-008 activation state
PostgresPersonalDomainActivationStore keeps one subject activation per private domain (finance, health, life-admin) under forced RLS. Rows hold only the validated record (ids, status, origin, evidence refs) and its digest; a digest mismatch reads as no activation, so tampering fails closed.
runPostgresPersonalActivationCarryForward is the one-time, idempotent backfill: subjects with a current consent receive a revocable
carried_forward_migration record citing the consent ids and migration id; others stay dormant; re-runs replay the ledger. Run order: apply
migratePostgresPersonalStore, run the backfill for every AuthOS subject, then enable the activation gate on finance, health and life-admin.
createPostgresPersonalDomainActivationHost binds the production activation store, the key-recovery probe (fresh bound attestations written by the host key service after verifying recoverability) and the outbox-processor probe (fresh per-destination heartbeats written by the processor loop). Absent, stale or revoked evidence fails closed.
Packaged migration runner
The server-only ./migration-runner subpath is the packaged install step for PersonalOS (install-sequence step 5). runPostgresPersonalOsMigrations applies the store DDL under an advisory lock and records each store revision in the append-only, digest-bound <schema>.personal_os_schema_migrations ledger in the same transaction; a current ledger skips the DDL, and an unknown or digest-mismatched row fails closed before any change. It then runs the RT-008 activation carry-forward for every AuthOS subject the deployer supplies (PersonalOsCarryForwardSource: subjects plus consent facts from the host key service, deduplicated and validated). Both halves are idempotent: a re-run replays the ledgers, and an interrupted carry-forward resumes per subject and domain. The report carries ledger ids and outcome counts only, never subject ids. carryForward: null applies the store only, for a first install without existing subjects.
PERSONAL_OS_POSTGRES_MIGRATIONS is the catalog that founder-ops
qualify-migrations reads. The store DDL is convergent, so each revision pins the digest of the normalized DDL it shipped (digestPersonalStoreMigrationSource); the runner refuses a source that drifts from the head revision. Never edit a shipped revision: a DDL change appends a new one.
./migration-rehearsal (rehearsePostgresPersonalOsMigrations) rehearses the step on a disposable database inside a throwaway personal_os_rehearsal_*
schema: a fresh apply and carry-forward for synthetic subjects, an exact replay, forced RLS on every subject table and an exact ledger, then it always drops the schema. The personalos-api app wires both as migrate and
migrate:rehearse.
Real PostgreSQL qualification
The *.integration.test.ts qualifiers run the store on a real PostgreSQL 17 login instead of PGlite. They read PERSONAL_OS_TEST_DATABASE_URL, a superuser URL of a disposable loopback database whose name contains test,
disposable or rehearsal, and self-skip without it. Each suite creates its own database owned by a LOGIN NOSUPERUSER NOBYPASSRLS migrator, installs it with the packaged runner, grants a separate runtime login of the same kind schema usage and DML on the store tables (never the ledger), and drops the database and both roles afterwards. The crash and restore suites also need cluster control: PERSONAL_OS_TEST_PG_BIN (binary directory) and
PERSONAL_OS_TEST_PGDATA (data directory of that cluster).
bun run --cwd packages/integrations/personal-os-state-postgres qualify:postgres
(PERSONAL_OS_QUALIFICATION_PG_BIN names a PostgreSQL 17 binary directory) creates a throwaway TCP-only cluster on a free loopback port, runs every qualifier with those variables, and deletes the cluster; an optional directory argument receives count-only crash and restore receipts.
Subject isolation is proven per store table: one forced ALL policy each, no rows without a subject scope, no foreign rows under another scope, rejected cross-subject inserts and re-keying updates, no-op foreign updates and deletes, and no way for the runtime login to disable RLS, drop a policy, set
row_security = off, assume the migrator, read the ledger or create tables. Forced RLS binds the schema owner too. Durable-store qualification covers racing commits and idempotent replays, SKIP LOCKED outbox workers, racing activations, concurrent runner invocations, an interrupted carry-forward that resumes, a runtime backend killed mid-commit, an immediate postmaster shutdown with WAL crash recovery, lease expiry and stale-worker rejection, behavior delivery retry and dead-letter, retention purge with cascading delivery work, rights erasure, a pg_dump by a BYPASSRLS read-only backup role (a
NOBYPASSRLS reader's dump is refused) restored into a new database, and a
pg_basebackup started as a second cluster. These local samples do not qualify managed PostgreSQL, KMS recovery, regional failover, operator runbooks or production recovery objectives (RPO/RTO).
The store migration grants no runtime role: the deployer grants its runtime login USAGE on the store schema and DML on the subject and heartbeat tables, as the qualifiers do. Behind the production postgres-js provider the outbox-processor probe binds one scalar parameter per destination, and
activate serializes first activations of a subject's domain, so racing activations return one created and conflict for the rest.
