Labels / Complexity: area:backend, feature, data-integrity · High — 8
Context
The backend persists high-volume records with no lifecycle: analytics events (backend/src/analytic/entities/analytic.entity.ts and its migration 1706400000000-create-analytics-events.sql), audit logs (backend/src/audit-log/entities/audit-log.entity.ts), user activity logs (backend/src/user-activity-log/) and puzzle access logs (backend/src/puzzle-access-log/). Every request path writes to at least one of these.
backend/src/multiplayer-queue/multiplayer-queue.service.ts already demonstrates the intended pattern with a nightly cleanup cron (EVERY_DAY_AT_MIDNIGHT, deleting rows older than a day), so the mechanism exists - it is simply not applied to the append-only tables that grow without bound. Without retention, each of these tables grows until query plans degrade and the database storage cost becomes the dominant cost of running the service.
Goal
Give the append-only operational tables a documented retention policy and a bounded, observable cleanup mechanism, without deleting records that are still needed for user-facing history or audit obligations.
Scope
1. Retention policy
For each append-only table, state the retention window and the reason (operational convenience, audit obligation, user history). The policy must distinguish records a user can see or export from purely operational telemetry.
2. Bounded deletion
Implement deletion in batches bounded by time and row count so a cleanup run cannot lock a table or exceed a transaction budget, and make it safe to run on every replica (idempotent, non-overlapping).
3. Observability
Record rows deleted and duration per run so a job that stops working is detectable.
Acceptance criteria
Out of scope
Data-warehouse export and user data-deletion requests.
Getting started
- In scope:
backend/src/analytic/, backend/src/audit-log/, backend/src/user-activity-log/, backend/src/puzzle-access-log/.
- Verify:
cd backend && npm test -- analytic.
- Good first files to read:
backend/src/multiplayer-queue/multiplayer-queue.service.ts (the existing cleanup cron), analytic.entity.ts, audit-log.entity.ts.
Labels / Complexity: area:backend, feature, data-integrity · High — 8
Context
The backend persists high-volume records with no lifecycle: analytics events (
backend/src/analytic/entities/analytic.entity.tsand its migration1706400000000-create-analytics-events.sql), audit logs (backend/src/audit-log/entities/audit-log.entity.ts), user activity logs (backend/src/user-activity-log/) and puzzle access logs (backend/src/puzzle-access-log/). Every request path writes to at least one of these.backend/src/multiplayer-queue/multiplayer-queue.service.tsalready demonstrates the intended pattern with a nightly cleanup cron (EVERY_DAY_AT_MIDNIGHT, deleting rows older than a day), so the mechanism exists - it is simply not applied to the append-only tables that grow without bound. Without retention, each of these tables grows until query plans degrade and the database storage cost becomes the dominant cost of running the service.Goal
Give the append-only operational tables a documented retention policy and a bounded, observable cleanup mechanism, without deleting records that are still needed for user-facing history or audit obligations.
Scope
1. Retention policy
For each append-only table, state the retention window and the reason (operational convenience, audit obligation, user history). The policy must distinguish records a user can see or export from purely operational telemetry.
2. Bounded deletion
Implement deletion in batches bounded by time and row count so a cleanup run cannot lock a table or exceed a transaction budget, and make it safe to run on every replica (idempotent, non-overlapping).
3. Observability
Record rows deleted and duration per run so a job that stops working is detectable.
Acceptance criteria
Out of scope
Data-warehouse export and user data-deletion requests.
Getting started
backend/src/analytic/,backend/src/audit-log/,backend/src/user-activity-log/,backend/src/puzzle-access-log/.cd backend && npm test -- analytic.backend/src/multiplayer-queue/multiplayer-queue.service.ts(the existing cleanup cron),analytic.entity.ts,audit-log.entity.ts.