[P0] Add an authoritative PostgreSQL control plane with tenant isolation and recoverable migrations

[seonghobae]

Production blocker

The current authoritative state is one optional JSON file containing routes, threat indicators, DNSBL entries, events, audit logs, feed status, and commercial metadata. In-process locking plus sibling-file rename protects a single process, but it cannot provide multi-replica concurrency, tenant isolation, transactional history, online migrations, point-in-time recovery, or independently testable integrity guarantees.

Audited protected base: b53dc7a1b8904a16752abbdc04429df893a4e32e.

Required architecture

Keep the file adapter for standalone/community use, but introduce a repository interface with PostgreSQL as the only production authority.

Ownership model

Every mutable production row must carry explicit ownership instead of relying on the single top-level commercial tenant_id:

• tenant_id on routes, policies, threat indicators, DNSBL entries, feed configurations, events, audit records, integration credentials/references, approvals, and jobs;
• created_by_subject_id / updated_by_subject_id where an operator or service identity acts;
• optional participant_id only where a domain workflow genuinely has a participant distinct from the authenticated subject;
• immutable stable IDs and timestamps generated by the authority, not trusted from clients.

Integrity and transactions

1. Add schema migrations with explicit forward and rollback/restore procedures; use expand/contract for online changes.
2. Enforce primary keys, foreign keys, uniqueness, non-empty/check constraints, enum/domain constraints, and monotonic versioning in the database.
3. Use optimistic concurrency or row locking for policy changes; stale writers must receive a deterministic conflict, never silently overwrite newer state.
4. Store high-value changes and their audit entry in the same transaction.
5. Define retention/partitioning for high-volume security events without weakening auditability.
6. Encrypt transport, document at-rest encryption ownership, and keep credential values outside ordinary policy tables.

Tenant isolation

1. Enable PostgreSQL row-level security on every tenant table.
2. Use default-deny policies; production application roles must not own tables and must not have BYPASSRLS.
3. Use FORCE ROW LEVEL SECURITY where owner access could otherwise bypass isolation.
4. Set tenant/subject context transaction-locally and clear it by transaction end; pooled connections must never leak context.
5. Keep authorization in Wardnet as defense in depth, but prove database-enforced isolation independently.

Reliability and recovery

• documented backup schedule, retention, encryption, and restore authority;
• automated restore drill into an isolated environment;
• declared and measured RPO/RTO;
• startup migration locking and compatibility checks;
• fail closed when schema version or migration state is unsupported;
• readiness separate from liveness and dependent on authoritative-store health.

RED → GREEN verification

• Cross-tenant read/update/delete/insert attempts must fail at the database layer, including malformed/missing tenant context.
• A table-owner-like maintenance path must be tested separately from the runtime role.
• Concurrent policy updates must prove deterministic conflict handling.
• Crash/retry tests must prove no policy mutation exists without its audit record and no partial migration is accepted.
• Migration tests cover empty database, previous supported version, rollback/restore, incompatible future schema, and connection-pool context reuse.
• Restore a real backup artifact and compare row counts, constraints, checksums where applicable, and critical semantic invariants.
• Preserve the standalone JSON adapter and its deterministic failure tests tracked in Make persistence failure tests deterministic across root and constrained filesystems #74; it must never be selected for production mode.

Acceptance criteria

• PostgreSQL is the documented and enforced authority for production mode.
• Tenant isolation is default-deny and proven below the HTTP layer.
• All critical mutations are transactional, versioned, attributable, and auditable.
• Backup/restore evidence meets declared RPO/RTO.
• A production instance cannot become ready on an unsupported schema or file-only state.
• 100% production statement/branch coverage includes repository selection, transaction, conflict, and migration error paths.

Boundaries

• Keyverse supplies authenticated subject and tenant claims; Wardnet validates and applies authorization and database context.
• The product that owns billing/credits remains responsible for its own atomic ledger; Wardnet stores only policy/risk/usage references needed for enforcement.
• Noema may provide shared infrastructure capabilities, but Wardnet remains responsible for its state invariants and tenant isolation.

References

• PostgreSQL Global Development Group. (2026). PostgreSQL 18 documentation: Row security policies. https://www.postgresql.org/docs/18/ddl-rowsecurity.html
• PostgreSQL Global Development Group. (2026). PostgreSQL documentation: Constraints. https://www.postgresql.org/docs/current/ddl-constraints.html
• PostgreSQL Global Development Group. (2026). PostgreSQL documentation: Transaction isolation. https://www.postgresql.org/docs/current/transaction-iso.html
• National Institute of Standards and Technology. (2022). Secure Software Development Framework (SSDF) version 1.1 (NIST SP 800-218). https://doi.org/10.6028/NIST.SP.800-218