Skip to main content

D85 — Audit/DLP append-only store growth: WORM segment export now, native time-partitioning as the deferred scale lever (RESOLVED, extends D31/D46/D58)

Architecture decision record, relocated verbatim from the retired single-file docs/tenancy/ decision log. Status, thematic clusters, and how to record a new ADR: the decision log index.

The append-only compliance stores grow without bound by design: audit_events/audit_checkpoints (D46 — UPDATE/DELETE raise via trigger, never pruned in-app) and the dlp logs (data_access_logs, tenant_data_access_logs, dlp_egress_events — same D31 posture, no Prunable). Note the audit partition column is a logical hash-chain discriminator (central / tenant:<id>), not native Postgres partitioning — every one of these stores is an ordinary heap table (relkind = 'r'). Three growth strategies were compared:

  • (a) Native Postgres range partitioning (by time) + retention windows. The best long-term operational lever (partition pruning for the read filters; DETACH/DROP instead of DELETE-storms). But it is a REBUILD of live tables — a partitioned parent requires every PK/unique to include the partition key, so audit_events' PRIMARY KEY (id) and the structural UNIQUE (partition, sequence) backbone would weaken to per-time-slice uniqueness (the advisory- lock appender stays correct — pg_advisory_xact_lock(hashtext(partition)) serialises sequence assignment independent of storage — but the DB-level 23505 backstop coarsens), the append-only triggers must be re-verified against partition DDL, and retention windows contradict the D31 posture until a slice is BOTH past the 7-year floor AND provably exported. No current need: verify-chain already streams (O(1) memory, lazyById), the search filters are index-covered, and volumes are nowhere near partitioning territory.
  • (b) Periodic WORM export to Object-Lock object storage. Solves the actual compliance risk NOW — the database stops being the sole custodian of the trail — with zero schema risk to the chain machinery. An append-only layout + bucket-level Object-Lock retention gives regulator-grade immutability off-platform, and each exported artifact can carry the chain material to be independently verifiable.
  • (c) External append-only catalogue (S3 Tables/Iceberg, or Cloudflare R2 + catalogue). Adds an analytics query engine the compliance requirement doesn't ask for (the need is retention + verifiability, not cross-store SQL), plus real cloud infra and a second consistency domain. Rejected for now; the (b) segment layout is Iceberg-ingestable later if analytics materialises.

Decision: (b) now, (a) deferred until table size is an operational problem, (c) rejected. Shipped as audit:export-worm / Modules\Audit\Services\ExportWormSegments: an incremental, idempotent export of closed fixed-size chain segments per partition (<prefix>/<partition>/segments/<from>-<to>.json, zero-padded; an existing object is NEVER rewritten; the watermark is derived from the listed object names, so the bucket is the state). Each segment document embeds the full verification material — every row's previous_hash/hash, the exact AppendAuditEvent::chainHash() spec, the covering signed checkpoints, and an HMAC segment signature under an app.key-derived key (the D58 checkpoint idiom, label audit-worm-segment-signing-v1) — so a segment verifies independently of the database. Config-gated

  • disabled by default (audit.worm_export.*); when enabled the provider schedules it daily onOneServer(). Locally it targets the s3-worm disk → the --with-lock MinIO bucket (stables-audit-worm, bootstrapped by minio-init, the D82 precedent); in production the disk points at an S3 bucket whose compliance-mode DEFAULT retention (≥ the D31 7-year floor) is deploy config — deliberate, because the flysystem S3 adapter does not forward per-object ObjectLockMode/ObjectLockRetainUntilDate params (AwsS3V3Adapter::AVAILABLE_OPTIONS), and a bucket default needs no per-request headers. The in-DB posture is UNCHANGED: nothing is pruned; the export is an additional custodian, not a migration. Extending the segment exporter to the dlp logs (which have no hash chain — an export would carry content only) and the eventual (a) rebuild are tracked in docs/tracking/audit-trail.md + docs/tracking/dlp-v2-scope.md.

Trust model + residual risk (review hardening). The name-derived watermark makes bucket object NAMES attacker-writable state: a principal with bucket WRITE access can pre-create a far-future segment name to poison the resume point, or pre-write the next segment path with altered content — either would permanently suppress real exports if names were trusted blind. Mitigations shipped: (1) verify-before-trust in the export — the max-to watermark object and any about-to-be-skipped existing object are READ and verified (parseable, format, segment_signature HMAC, closing_hash == the DB chain hash at its to, AND a full recompute of the segment's INTERIOR chain: every row's previous_hash linkage + chainHash() recomputation walking from opening_previous_hash through to closing_hash, plus the event count vs the coordinates — load-bearing because the HMAC covers only partition|from|to|closing_hash, so a content rewrite or stripping of events that preserves the closing tuple would otherwise pass) before the export advances; a mismatch ABORTS that partition's export with a loud anomaly (command exits FAILURE). An IO READ failure (get() null under the disk's throw => false) is a DISTINCT retry-first anomaly (not a tamper verdict), and a failed segment WRITE (put() === false) is an anomaly too — never a silent success; (2) audit:verify-worm (scheduled daily onOneServer() alongside the export) reconciles the whole archive against the DB — contiguous from..to coverage per partition up to the watermark (interior-gap detection, which the max-based watermark cannot see) plus the same full per-object verification (HMAC + closing-hash + the interior chain recompute) plus cross-segment linkage (each segment's opening_previous_hash must chain onto the prior segment's closing_hash) — O(segment_size) per object, the daily sweep's deliberate cost; (3) dedicated least-privilege writer credentials — the s3-worm disk reads AUDIT_WORM_ACCESS_KEY_ID/ AUDIT_WORM_SECRET_ACCESS_KEY (falling back to the general AWS_* for local dev); production uses a dedicated identity with s3:PutObject + s3:GetObject + s3:ListBucket on the WORM bucket only (get/list are required by the watermark + verify-before-skip reads), NO s3:DeleteObject, NO s3:BypassGovernanceRetention, distinct from any app-request-path credential; wider bucket READ is auditor-scoped (the archive is the whole cross-tenant trail). RESIDUAL: a principal holding bucket write access can still land garbage objects (denial-of-export noise) and, absent Object-Lock, damage unexported names — the bucket's compliance-mode default retention plus the daily reconciliation are the floor under that. Poison-wedge under Object-Lock (accepted posture): WITH Object-Lock a far-future junk object landed via the writer credential becomes the partition's (undeletable) watermark candidate and WEDGES that partition's export — every run re-derives the same poisoned watermark, fails verification, and aborts loudly until the object is cleared. The fail-loud abort is the chosen posture (silently skipping unverifiable bucket state would be worse). Operational runbook: audit:verify-worm pinpoints the offending key(s); clearing them requires bucket-owner intervention (compliance-mode retention cannot be bypassed by any credential — expect a lifecycle exception / support path on the bucket owner's side, or waiting out retention on a governance-mode bucket); exports for the partition resume automatically once the poison object is gone, since the bucket is the only state. The segment document also embeds a signature_spec block (mirroring hash_spec) so it is fully self-describing; NOTE an APP_KEY rotation is a signature-versioning event — segment HMACs (and D58 checkpoint signatures, which share the derived-key idiom) minted under the old key stop verifying under the new one, while the hash_spec chain recomputation stays valid regardless (re-anchor/re-sign explicitly on rotation). Production-rollout gate (accepted 2026-07-04): failure alerting. Enabling the export in production is gated on a concrete failure alert paging on any non-zero exit of the scheduled audit:export-worm/audit:verify-worm runs — the commands exit non-zero on every anomaly (and on an explicitly targeted --partition matching nothing) precisely so a scheduler can page; an unwatched non-zero exit silently defeats the second-custodian purpose (rollout checklist: docs/tracking/audit-trail.md).

← Decision log index