Feature: Harden audit log (append-only guarantee, scheduled integrity check, retention)

[larryro]

Context

Compliance requires that logs are protected against tampering and unauthorized access, that admins cannot delete or falsify their own activity logs, and usually a defined retention.

We already have a strong base:

• Hash-chained audit log in services/platform/convex/lib/helpers/audit_hash.ts
• Admin-only chain verification in services/platform/convex/audit_logs/verify_integrity.ts
• Export in services/platform/convex/audit_logs/export_audit_logs.ts

Gaps:

1. No explicit guarantee that there is no mutation/delete endpoint on the auditLogs table. Hash chain detects tampering but doesn't prevent row deletion.
2. No scheduled chain verification — breaks would only be seen if an admin manually runs the verify query.
3. No retention policy.

Proposed change

1. Audit existing codebase — confirm no patch/delete/replace on auditLogs. Remove any that exist. Add a unit test asserting the table is append-only.
2. Add a scheduled Convex cron that runs verify_integrity nightly and emits a security audit entry + email to admins on failure.
3. Add configurable retention (default: keep forever, optional archive-then-delete after N days). Archived exports must also be signed/hashed.
4. Review read-side access: only org admins should be able to read audit logs. Confirm RLS in services/platform/convex/lib/rls/ enforces this.

Acceptance criteria

• No code path allows deletion or mutation of auditLogs rows.
• Nightly integrity check runs automatically.
• Retention policy configurable per org.
• Docs updated.

Part of the security compliance baseline.

[yannickmonney]

Triaged — keeping open but re-scoping, since the base is stronger than the issue assumes. Already implemented: the audit-log hash chain (audit_logs/helpers.ts), an admin integrity-verify action (verify_integrity.ts), export, and retention (daily cron hard-deletes beyond the window, with signed checkpoints + re-anchoring).

Remaining, genuinely-open work:

1. An explicit append-only test asserting no patch/delete surface on auditLogs (note: the table is patched for chain-linking, so "append-only" needs careful wording — it's "no semantic mutation/deletion outside retention").
2. A scheduled integrity-check cron that runs verify_integrity and alerts on failure.

Assigned to me.