[WAF-51] Implement the PostgreSQL schema and transactional migrations

Objective

Create constrained schemas for users, nodes, policies, bundles, rollouts, events, searches, alerts, settings, and administration with range partitions, BRIN/B-tree/GIN indexes, and transactional migrations.

Deliverables

• Implement the complete scoped behavior described above.
• Keep request-path work independent from UI, PostgreSQL, and blocking audit I/O.
• Use Zig 0.17-dev and dependencies pinned through Pantry; do not add Git submodules.
• Keep public APIs explicit about ownership, resource limits, errors, security policy, and compatibility.
• Add focused unit, integration, differential, fuzz, benchmark, and documentation coverage as applicable.

Acceptance criteria

• The scoped implementation and tests are merged on main in small conventional commits.
• Zig formatting, focused tests, and the relevant CI suite pass.
• Compatibility claims have executable evidence and no unexplained skips or silent fallbacks.
• Untrusted inputs have deterministic bounds; hot paths have benchmark evidence.
• Documentation and the machine-readable compatibility matrix are updated.

Relationships

• Parent: WAF-00

Blocked by

• WAF-50

Blocks

• WAF-52
• WAF-53
• WAF-54
• WAF-55
• WAF-56
• WAF-57
• WAF-58
• WAF-59

Baselines

• ModSecurity 3.0.16
• Coraza 3.7.0
• OWASP CRS 4.28.0
• libinjection 4.0.0
• Zig 0.17-dev resolved and locked through Pantry

Complete.

Constrained schemas — users, nodes, policies (rulesets), bundles, rollouts (node_rulesets), events, saved searches, alerts, settings, and administration (admin_log). The constraints are the point, so they are tested rather than assumed: roles are a fixed set, so an unrecognized value cannot be stored and later be read as some default; a user must have either a password hash or a federated subject, so no identity exists that cannot authenticate; credentials are unique by hash and cascade with their user; a saved search is named once per user; and settings are keyed so a value is replaced rather than duplicated (7b6e8e1).

Range partitions — security_events is partitioned on occurred_at, with ensureMonth creating the next month ahead of time and dropMonth making retention an O(1) partition drop (8fb630b).

BRIN / B-tree / GIN — each index type is matched to how its column is queried. BRIN replaces the standalone B-tree on the event timestamp: events arrive in time order, so the physical order already matches the indexed order and a summary per block range answers a range query at a fraction of the space. GIN covers the jsonb columns so a label containment query is indexed rather than scanned. The composite (node_id, occurred_at) B-tree stays for per-node lookups, which a summary cannot serve.

The index test asserts each index applies to the operator its column is queried with — a GIN index built with the wrong opclass would exist and be useless for @>. It deliberately does not assert planner preference: on a table small enough for a fast test a sequential scan is genuinely cheaper, so that assertion would measure the fixture rather than the schema.

Transactional migrations — pg.migrate applies each migration in its own transaction and records it in schema_migrations, so applying twice is a no-op and a failure leaves the schema at the last fully applied version. Migrations are append-only; existing ones are never edited. Verified including the rollback case (a failing migration leaves its version unrecorded), and each integration test now migrates a fresh private schema, so the real DDL is exercised on every run (385f6f0).

Earlier slices: the initial schema, alert rules, node_rulesets, alert deliveries, partitioning, and per-rule secrets. Documented in docs/fleet-storage.md.