Retention/pruning sweep for append-only log tables

[important-new]

Append-only log tables — audit_logs, esign_audit_logs, inspection_events, sms_consent_log, erasure_log, processed_webhook_events / processed_cmd_events / parked_cmd_events, etc. — have no pruning; they grow unbounded. server/scheduled.ts has a GDPR retention sweep for agreement satellite data only (server/lib/compliance/retention-sweep.ts) — it touches no log table.

Scope: per-table retention windows + a pruning job wired into scheduled.ts. (Blocked only on the retention-window policy decision; no code exists yet.)

Out of scope: automation_logs — retained indefinitely by design. It was listed here originally; see the comment below for why it was removed.

[important-new]

automation_logs is out of scope — it is retained indefinitely by design

Removed from the table list above, where it was listed first.

automation_logs is becoming a user-facing read, not only an internal log:

• a per-inspection delivery view for the inspector, grouped by notice, showing which recipients were reached on which channel, and the reason when a send was skipped;
• a per-recipient view for clients and agents — "here is what we sent you" — surfacing only the failures they can act on.

Once a row is something a user opens, deleting it deletes the answer to "did they get the report?", which is the question those views exist to answer. The same rows are also the evidence trail behind consent-gated sends: a sweep would quietly erase the record of why an SMS was skipped, which is precisely what one wants to keep.

Growth is handled by indexing rather than deletion. The table already carries idx_automation_logs_insp for the per-inspection read; the recipient-scoped and notice-grouped reads get their own indexes as those views land.

The rest of this issue stands. audit_logs, esign_audit_logs, inspection_events, sms_consent_log, erasure_log, and the webhook/command dedup ledgers have different lifetimes and different legal footing, and still need per-table retention windows.

[important-new]

Progress (2026-08-07, #299/#300): Task 1 and Task 1b shipped. The sweep itself did not. Tasks 2, 3, 3b and 4 remain open.

What landed is the part of this issue that closed an exposure rather than shortening a retention:

cmd.tenant.update sparsely carries an admin password hash on password-change commands, and both dead-letter paths stored the payload — the raw envelope on parse-failed and JSON.stringify(env) on unknown-type-or-version. A parked row now holds an allow-listed fingerprint ({v, type, dataschema, cmdId, tenantseq, bytes, sha256, invalidFields?}); the payload is never read, so a field added to data tomorrow is dropped by construction rather than by a redaction list that has to be kept current. Pre-existing rows were cleared by a data-only migration, because "new rows are safe" is a different statement from "the exposure is closed" — production held zero parked rows, so nothing had leaked.

Task 1b did the same one layer over: audit metadata is redacted at write and scrubbed on erasure, with the primary filter on the value (email / phone / IPv4 under any key) rather than the key name.

Still open, and why the sweep was not just done alongside:

• Task 2's gate heuristic would red CI on ~39 pre-existing tables. It needs a narrowed predicate or a checked-in baseline before it can land, plus enumeration of four log tables added since this plan was written (idempotency_keys, notifications, inspection_messages, integration_test_results).
• Task 3b is blocked on two columns that do not exist: inspections.completed_at and inspection_retention_years. The latter is permanently absent by decision — the single retention number is agreementRetentionYears; do not introduce a second one.

The four retention periods are confirmed as written (audit_logs anonymize at 24 months; processed_webhook_events / processed_cmd_events delete at 90 days; parked_cmd_events at 30 days; sms_consent_log / erasure_log / esign_audit_logs never). These get published in the privacy policy — they are not to be adjusted while implementing.

[important-new]

Closing — verified against main, not against a status line.

Every table this issue names is now under a retention rule. All eight are in
server/lib/compliance/retention-manifest.ts: audit_logs, esign_audit_logs,
inspection_events, sms_consent_log, erasure_log,
processed_webhook_events, processed_cmd_events, parked_cmd_events.

And the sweep actually runs. runLogRetentionSweep is invoked from
server/scheduled.ts — once daily in the 04:00 UTC window, always-on in both
topologies, wrapped so a failure logs rather than kills the tick. That check
mattered: a manifest without a call site is the "rule that exists but never
runs" failure, and this repo has shipped that shape before.

The windows are the ones confirmed rather than defaulted, because they get
published in the privacy policy: audit_logs anonymise at 24 months;
processed_webhook_events / processed_cmd_events delete at 90 days;
parked_cmd_events at 30 days; sms_consent_log, erasure_log,
esign_audit_logs never, each with a recorded reason.

npm run lint:retention keeps a new log table from arriving without a decision.

⚠️ One task from the implementing plan is deliberately NOT done, and it is out
of this issue's scope. Task 3b covered retention of the inspection record
itself and is blocked on two columns that do not exist
(inspections.completed_at, inspection_retention_years — the latter
permanently, per the erasure-freetext decision). This issue is scoped to
append-only log tables, so that gap does not belong here. If inspection-record
retention is wanted, it needs its own issue.