Our nightly deletion job failed silently on every run, and the bug was a Date object

CogniPrep publishes exact retention periods in its privacy policy. Not "we keep data no longer than necessary", but numbers: game sessions and scores deleted after 2 years, application goals deleted 2 years after you last updated them, device session tokens removed after 30 days of inactivity.

Publishing a number like that turns a policy sentence into a cron job that has to work. Here is what that job ended up looking like, and the two mistakes it survived on the way.

Mistake one: a Date is not a parameter

The job is written with Drizzle's sql template over postgres.js, and the first version passed the cutoff straight in:

const twoYearsAgo = new Date();
twoYearsAgo.setFullYear(twoYearsAgo.getFullYear() - 2);

await db.execute(sql`
  DELETE FROM game_sessions WHERE completed_at < ${twoYearsAgo}
`);

That throws. Not always, and not obviously: it throws under our specific configuration, which is a pooled connection with prepare: false because we sit behind a transaction pooler. Without prepared statements the value reaches the wire serializer still a JavaScript Date, and you get:

Buffer.byteLength(...) received an instance of Date

The job ran nightly. It threw nightly. It was caught, logged as an error, and nothing downstream cared, because nothing consumes a retention job's output. The only symptom was a table that never got smaller, which is exactly the symptom nobody is looking at.

.toISOString() fixes it, and the comment explaining why is now the longest thing in the function, because this is not a bug you can rediscover from the fix.

Mistake two: reading a table to count it

The original shape was:

const expiring = await db.select().from(gameSessions).where(lt(completedAt, cutoff));
await db.delete(gameSessions).where(lt(completedAt, cutoff));
return { deletedCount: expiring.length };

That first query pulls every expiring row into the function's memory, including a metrics JSONB column, purely to read .length. The DELETE already reports the number. Two statements, one of which exists to produce a number the other one hands you for free.

Batching, because the function has a deadline

The route runs with maxDuration = 60. A single unbounded DELETE has no upper bound on how long it holds locks or how much WAL it writes.

The interesting part is what happens if it ever exceeds the limit. The function gets killed mid statement, the transaction rolls back, and no rows are removed. The next night there are more rows, so it takes longer, so it is killed again. An unbatched delete against a growing table is a job that can enter a state it never leaves.

Batching makes the work incremental, because each batch commits:

const DELETE_BATCH_SIZE = 1_000;
const MAX_BATCHES_PER_RUN = 50;

while (batches < MAX_BATCHES_PER_RUN) {
  const result = await db.execute(sql`
    DELETE FROM game_sessions
    WHERE id IN (
      SELECT id FROM game_sessions
      WHERE completed_at < ${cutoff.toISOString()}
      LIMIT ${DELETE_BATCH_SIZE}
    )
  `);

  const rowsDeleted = Number(result?.count ?? 0);
  deletedCount += rowsDeleted;
  batches++;
  if (rowsDeleted < DELETE_BATCH_SIZE) break;
}

Three things in there are deliberate.

The subquery exists because Postgres has no DELETE ... LIMIT. You cannot cap a delete directly, so the inner SELECT picks a bounded set of ids and the outer statement removes exactly those. Cascades to dependent rows still fire normally.

MAX_BATCHES_PER_RUN is a safety valve, not a target. 50 batches is 50,000 rows a night. If a run hits the cap it logs that it did and stops, and the next run continues from where it stopped, because the rows it did not reach are still older than the cutoff. A retention job does not need to finish tonight. It needs to never get stuck.

A short batch ends the loop. Fewer rows than the batch size means there was nothing left to match, so there is no reason to issue a query that will delete nothing.

The deadline is stored, not computed

The session cleanup computes its cutoff at delete time. The onboarding answers do not: each row carries its own expires_at, written when the answers are saved, and the job deletes on that column.

That is not a stylistic difference. The user is shown their deletion date in the app. If the job recomputed "two years before now" instead of reading the stored column, then the date shown and the date enforced would be two independent implementations of the same rule, free to drift. Storing it also makes editing your answers restart the clock for free, because saving writes a new expires_at, with no extra code path.

Logging a job that nobody watches

Our logger has a logInfo that is development only, which is the right default for request handlers and completely wrong for a nightly job. If a job deletes up to 50,000 rows and logs nothing in production, there is no way to answer "how much did last night's run remove?" after the fact.

So jobs use a separate logJob that always writes, and the rule for what may go in it is simple: counts and batch state only, never row contents. That keeps the detail useful and keeps it safe to leave on in production.

The route response follows the same idea. It used to return only the session count while quietly dropping the onboarding and device session counts, which made a run that removed thousands of onboarding rows look identical to one that removed none. All three counts are now in the payload, so the scheduled run and the manual trigger are directly comparable.

See it

Open cogniprep.app/privacy and search for "2 years". You will find three separate retention lines, each naming what is deleted, when it is deleted automatically, and what triggers immediate deletion instead. The device session line names 30 days of inactivity and says plainly that it exists as a safety net against permanent lockouts from lost devices.

Those sentences are the specification. The job above is the implementation, and the whole point of writing the numbers down publicly is that it becomes obvious when the implementation is not running.

The takeaway

If your privacy policy contains a number, something in your infrastructure has to enforce it on a schedule, in batches small enough to always make progress, with production visible output so you can tell it ran. Ours failed nightly for a while on a serialization error that never reached a user, and the only way that gets caught is by going and looking.