@lssm-tech/integration.customer-support-postgres
Immutable PostgreSQL migration and adapter primitives for both managed and self-hosted SupportOS authorities. The schema uses forced RLS, transaction-local tenant/workspace context, append-only evidence, composite/keyset indexes, and a SKIP LOCKED outbox claim.
Applications must establish authenticated scope with withSupportPostgresScope; public callers never supply authority columns directly. Sensitive values are persisted as protected references produced by the injected SupportOS data-protection port.
Apply the immutable migrations in order through
0036_supportos_retention_enforcement. Migration 0035 adds governed redacted/sensitive case exports, immutable snapshot rows, typed event/audit evidence, forced RLS, database-issued keyset cursors, and an atomic export.generate job. Request and cancellation replay exact committed receipts before revalidating expiring governance; new mutations require live, mode-specific membership and a state-bound Connect decision. Snapshot values are frozen transactionally before the durable worker streams them. Every execution step binds the exact current job, worker, lease generation, expiry, and rollout decision; artifact digests and byte counts are compared with the exact emitted stream. Lifecycle chronology, cursor retries, role-scoped RLS, and execution outcome receipts fail closed. Protected artifact retrieval/expiry deletion, retry-exhaustion and cancellation cleanup, real PostgreSQL contention, million-row load, restore, and production canaries remain unqualified. Migration 0031 upgrades the existing DecisionGraph tables with revision-fenced draft lifecycle state and immutable, forced-RLS lifecycle receipts. Publishing validates the deterministic graph and writes an immutable digest-pinned revision; editing never mutates published material. Exact retries replay their stored snapshot, lists use a database-issued fixed watermark, and audit/outbox evidence contains no tenant-supplied authority. Real PostgreSQL multi-connection contention, backup/restore, load, and production canaries remain unqualified. Migration 0032 adds encrypted, immutable DecisionGraph evaluation evidence and typed validation, simulation, replay, and revision-comparison receipts. Every evidence-producing operation requires a caller-stable retry key, reauthorizes against live membership, validates its public output before persistence, emits typed audit/outbox evidence, and replays only the exact scoped request digest. The evaluation authority reserves each retry identity before invoking an idempotent protected-input provider, compensates failed persistence with erasure, passes a bounded cancellation signal to every protection call, and validates stored schema, identity, and digests before use. Historical re-execution also requires an installed matching engine version, while exact receipt retries do not. Both evidence tables use forced RLS and immutable triggers; legacy evaluations are upgraded without inventing replayable plaintext inputs. Migration 0033 adds redacted evidence list/detail administration over immutable evaluation receipts. Queries reauthorize against live membership, validate every stored receipt plus its typed event, audit, and outbox linkage before projection, omit protected inputs and outcome payload data, and use database-issued, scope-bound, graph-filter-bound fixed-watermark cursors over a monotonic persisted evidence sequence with a 30-minute TTL. Explicitly non-replayable legacy rows remain unavailable rather than poisoning newer pages. Dedicated composite indexes cover filtered and unfiltered keyset scans; cursor rows use forced RLS and lazy expiry cleanup. Evidence retention, legal holds, erasure, real PostgreSQL contention, load, restore, and production canaries remain explicitly unqualified. Migration 0034 adds revision-fenced retention policies, legal holds, erasure requests, owner evidence, tombstones, and opaque legal-hold cursors under forced RLS. Governed mutations use database time, live membership, exact replay, immutable typed event receipts, and a durable erasure.execute job. Completion serializes against legal-hold creation on the case row, freshly reauthorizes the persisted state-bound Connect decision, requires terminal evidence from every canonical owner, and records the protection-deletion receipt before removing the orchestration reference. Production owner adapters, retention deployment composition, real PostgreSQL multi-connection contention, load, restore, manual review, and canaries remain unqualified. Migration 0036 adds the scheduled SupportOS-owned retention executor and backfills and caps the documented 730-day closed-case/evidence policy for existing workspaces, caps temporary state at 90 days, and installs the same defaults for new workspaces. Daily database-time watermarks and persisted keyset cursors discover only allowlisted closed-case subject and case-event protected values, with closed-case and evidence horizons applied independently. Candidate reservation and durable-job enqueue are atomic; queue payloads contain opaque work references. Execution locks and revalidates live rollout, the active job lease, current policy, case revision, and legal holds before calling durable-deletion protection with the exact job, worker, lease generation, deadline, and persisted effect identity. Immutable dispositions distinguish erased, absent, held, and stale work. CommunicationOS messages and attachments, Forms answers, Knowledge content, CompanyOS records, and provider data remain outside this authority. Temporary-state retention, production KMS deletion, multi-connection contention, load, restore, manual review, and canaries remain explicitly unqualified. Hosts must invoke evaluation through createPostgresDecisionAuthority; the source-level store, persistence, and runtime helpers are trusted adapter internals and do not provide authorization or rollout boundaries on their own. The workspace administration adapter records revision-fenced brand, product, host, locale, and residency provenance with immutable events and live membership checks. It deliberately refuses direct isolated/pooled mode changes until the separate collision-aware migration planner is qualified. Migration 0012 adds forced-RLS workspace migration plans and immutable lifecycle events. The local authority supports deterministic preview, state-bound governance, revision and fence checks, exact replay, resumable advance, topology application, cutover reauthorization, and rollback. The dedicated rollout port fails closed independently of the HTTP feature gate. Identity/case/host collision scanners, resolution mutations, durable orchestration, a managed Connect bridge, and production rehearsal remain explicit qualification blockers. Migration 0013 adds forced-RLS, immutable onboarding receipts and a scoped namespace lock. PostgresSupportWorkspaceOnboarding checks the independent rollout port, verifies a state-bound expiring Connect decision, and creates the isolated workspace, founding administrator, membership history, audit, and outbox evidence in one transaction. Exact retries replay the original snapshots; changed input, an existing workspace, or any partial-write failure is denied. Migration 0005 binds every immutable event receipt to the exact scoped mutation digest. Migration 0006 adds expiring leases, monotonically increasing fencing tokens, immutable completion receipts, bounded retry scheduling, and idempotent dead-letter replay. PostgreSQL time—not worker time—controls scheduling, lease expiry, and settlement. Typed jobs carry a stable downstream effect key across retries and replay. The same sequence and fresh/upgrade conformance tests apply to managed and self-hosted deployments.
Migration 0008 adds immutable, forced-RLS case-relation evidence. Merge, split, and link mutations lock both case rows in canonical order, fence both revisions, append both case events, and persist the replay snapshot in one transaction. Stable JSON pair keys avoid delimiter ambiguity; exact idempotent replays return the original projections and changed input fails closed.
Migration 0014 adds immutable, forced-RLS internal-note evidence. SupportOS stores only opaque CommunicationOS note/evidence references, never note content. The adapter requires a case-bound private-note verifier receipt and enforces scoped permissions, exact replay, a current-case revision fence, database timestamps, duplicate-reference denial, and stable-watermark keyset pagination.
Migration 0016 adds revisioned team and queue projections plus immutable routing events. The routing authority serializes mutations under the workspace fence, replays exact receipts, rejects stale or changed writes, validates active team and fallback references, prevents fallback cycles and unsafe archival, and emits audit and outbox evidence atomically. Production contention, capacity policy, AuthOS membership synchronization, load, and canaries remain open.
Migration 0020 adds forced-RLS OPA handoffs and immutable lifecycle evidence.
PostgresSupportOpaHandoffAuthority stores only opaque destination, consent, booking, and evidence references; it never stores public booking tokens, calendar/provider data, visitor answers, or case bodies. Creation locks and fences the current case revision, resolves the public HTTPS destination through an injected server-side OPA port, and provides exact changed-input-aware replay. Returns are terminal, revisioned, and require booking evidence for completed booking handoffs.
Migration 0021 adds target-bound first-response and resolution clocks, immutable clock and breach evidence, and resumable breach dispatch state. PostgreSQL time controls clock mutation and due selection. Breach recording updates the case projection atomically; stable worker effect keys make pinned Workflow/SOP escalation replayable. Managed worker registration, production business-hours resolution, and production qualification remain fail-closed rollout gates.
Migration 0022 adds immutable, forced-RLS CSAT v2 responses and recording events. The authority accepts only a 1–5 score plus opaque Forms definition, response, revision, and stable-question references, requires an exact server-side Forms verification receipt, fences the current case revision, replays exact receipts, and reports against a fixed database-time watermark. Form answers and free-text feedback are never copied into SupportOS.
Migration 0024 adds forced-RLS saved-view projections, immutable replay evidence, and case-search indexes. The authority returns only redacted operational case fields, uses database-issued watermarks and keyset cursors, keeps private views owner-only, and revision-fences every idempotent mutation.
Migration 0025 upgrades service profiles to encrypted field-envelope references, consent and source-evidence receipts, verified evidence expiry, revision fencing, and immutable forced-RLS mutation events. Exact replay occurs before external verification; sensitive reads require a dedicated permission, active consent and evidence, and append an immutable audit record. Profile events and outbox rows contain references and redacted projections only.
Migration 0027 adds forced-RLS attachment safety projections and immutable mutation evidence. Registration fences the current case, requires exact CommunicationOS scan and consent evidence, and stores no bytes, names, MIME claims, storage locations, or digests. Quarantine release is administrator-only and requires a distinct exact governance decision. Database time controls evidence expiry; exact retries replay before external dependencies.
Migration 0028 adds revision-fenced notification preferences, body-free escalation-pinned intents, immutable event/audit/outbox evidence, and CommunicationOS-verified delivery settlement. PostgreSQL time controls watermarks and evidence validity; exact replay occurs before policy or delivery dependencies, while forged, stale, duplicate, future-dated, and cross-scope evidence fails closed.
createPostgresSupportReadiness verifies database access, the current migration, worker, queue, feature-flag, and required-adapter probes on every readiness request. Callers provide a deadline signal; the managed postgres.js adapter cancels active queries when readiness times out.
