feat(data-lifecycle): add retention, partitioning, archival, legal-hold, and safe purge foundations #745

Parent epic: #738
Depends on: #739
Related completed foundations: #687, #697, #698

Context

AWCMS-Mini already has several resource-specific retention/purge jobs, but high-volume tables can grow across audit, analytics, ABAC decisions, idempotency, event outbox/delivery, webhook inbox, sync queues, provider attempts, and future usage/reporting projections. A reusable governance foundation is needed so each module does not invent incompatible lifecycle semantics.

Objective

Add a module-contributed high-volume table registry and safe lifecycle engine covering retention, partitioning guidance/automation, archive ports, legal holds, dry-run, bounded purge, resume, reconciliation, and maintenance evidence.

Scope

• Define a static lifecycle descriptor contributed by owning modules, including:
  • table/resource owner;
  • tenant/global scope;
  • timestamp/cursor key;
  • retention class and safe min/max;
  • partition eligibility/granularity;
  • archive policy/format/port;
  • deletion/anonymization behavior;
  • legal-hold applicability and precedence;
  • required indexes and batch limits;
  • backup/restore implications.
• Add a high-volume registry validation gate.
• Add policy storage only where runtime/tenant overrides are necessary; do not duplicate immutable descriptor facts in mutable settings.
• Add legal-hold records with scope, reason, authority/reference metadata, start/end, approval, audit, and default-deny release.
• Add dry-run lifecycle planning that reports eligible, held, archived, purgeable, blocked, and error counts without mutation.
• Add bounded archive/purge jobs using the shared worker runner, advisory locks, tenant-first batches, lock/statement timeouts, pause/resume cursors, retry classification, and safe telemetry.
• Define a provider-neutral archive port with a local/offline option and optional external object storage adapter. Archive artifacts require manifest, row/partition range, checksum, schema/version metadata, and restore/reconciliation procedure.
• Add partitioning policy and safe migration/runbook guidance; only automate partition operations where PostgreSQL safety can be proven.
• Add metrics/readiness for table/partition volume, oldest data, retention backlog, held data, archive outcome, purge duration, and failures.
• Register representative existing high-volume resources as adoption examples without rewriting their domain logic.

Security, privacy, and compliance requirements

• Legal hold overrides ordinary retention/purge.
• Purge/anonymization is permission-gated, reason-required, idempotent where applicable, and audited.
• Cross-tenant lifecycle operations are prohibited except a dedicated system job that iterates tenant contexts safely.
• Archive and logs contain no credentials; archive access uses least privilege and encryption guidance.
• Dry-run and result artifacts minimize or aggregate PII.
• Purge must not issue unbounded deletes or hold long locks.
• Policies map practically to UU PDP/PP PSTE, ISO/IEC 27001/27002/27005/27701, ISO 22301, and evidence-retention requirements without asserting one universal legal retention period.

Out of scope

• A universal retention duration for all domains or regulations.
• Archiving binary application files into PostgreSQL.
• Destructive migration of all existing tables in one PR.
• Bypassing module ownership to purge another module's table directly.

Acceptance criteria

• Registry validates owner, scope, cursor, bounds, indexes, legal-hold behavior, archive, and purge strategy.
• Dry-run performs no mutation and reports deterministic categorized counts.
• Legal hold prevents archive deletion/purge and cannot be silently overridden by tenant policy.
• Batch jobs are bounded, resumable, lock-timeout aware, and safe after interruption/retry.
• Archive artifacts have deterministic manifests and verified checksums; reconciliation/restore is documented and tested.
• A deliberately large volume test demonstrates acceptable batching and query plans.
• Cross-tenant and missing-tenant-context operations fail under RLS tests.
• Metrics/readiness are low-cardinality and do not expose row contents or PII.
• Existing representative retention jobs are integrated or explicitly documented as compatible adopters rather than duplicated.
• ERD/data dictionary, privacy/retention policy, backup/restore, incident, deployment, jobs, threat model, and changeset are updated.
• bun run security:readiness, lifecycle dry-run tests, PostgreSQL integration tests, resilience interruption tests, and bun run check pass.