Our force flag did strictly less work than not passing it

CogniPrep deletes old data on a schedule, because the privacy policy promises it will. Practice sessions go after two years, device sessions after thirty days of inactivity, and the answers people give during onboarding two years after they last touched them.

The job that does this had accumulated three separate pieces of machinery that did nothing, and one bug that meant it deleted nothing at all. They are worth going through in order, because each one is a different way for code to look like it works.

1. The flag that did less

The admin endpoint took a body with a force flag. The documented meaning was "bypass the recency check and run now".

There was no recency check. Nothing anywhere recorded when the job last ran, so there was nothing to bypass.

What force: true actually selected was a different branch, and that branch called only the session cleanup. The normal path ran all three. Passing the flag named "do it anyway, harder" skipped two thirds of the work.

That is the specific danger of a flag whose name describes an intention rather than a behaviour. Nobody rereads the branch, because the name already told them what it does. The fix was not to make force correct, it was to delete it:

/**
 * There is no `force` flag any more. It existed to "bypass the recency check",
 * but no recency check was ever implemented, and the branch it selected called
 * only `deleteOldSessions` - skipping the device-session and onboarding
 * cleanups. So `force: true` did strictly LESS work than a normal run, the
 * opposite of what the name implied. A body containing `force` is now simply
 * ignored: POST always runs the same full sweep the cron does.
 */

Ignoring the field rather than rejecting it is deliberate: anything out there still sending it now gets the behaviour it always wanted.

2. The boolean that was a constant

The underlying function was called runDataRetentionIfNeeded and returned { ran: boolean }. The endpoint branched on it to return skipped: true.

ran was never false. "If needed" was decided by the same non-existent recency check, so every caller got true, and the skipped: true response was unreachable code that had been shipping for months. Anyone reading the endpoint would reasonably assume a skip was a thing that happened in production sometimes.

The guarantee the flag pretended to provide already existed one layer up, in the scheduler:

{ "path": "/api/admin/data-retention", "schedule": "0 3 * * *" }

Once a day, from cron. And every delete in the job is idempotent, so a second run on the same day finds nothing left to remove and costs almost nothing. There is now a comment saying exactly that, and saying what a real gate would need if anyone ever wants one: somewhere durable to record the last run, not a boolean that cannot be false.

3. The response that threw away two of its three numbers

The job cleans three things. The response reported one count.

function retentionResponse(result: {
  deletedCount: number;
  deviceSessionsDeleted: number;
  onboardingDeleted: number;
}) {
  return NextResponse.json({ success: true, /* ...all three counts... */ });
}

A night that removed thousands of onboarding rows and a night that removed none produced byte-identical output. Both handlers now share this one shape, so a manual admin run and a scheduled run are directly comparable, which is the only way "is the cron actually working?" is answerable after the fact.

Progress logging goes through a production-visible logger rather than the development-only one, for the same reason, with one rule: every line carries counts and batch state, never row contents. Retention logs are the last place you want to be quoting the rows you just deleted.

4. The bug: a Date that never reached the database

Here is the one that mattered. The job ran nightly, reported no errors anyone noticed, and deleted nothing.

The cutoff was computed as a Date and interpolated into a Drizzle sql template. Under our pooled connection config, which sets prepare: false, that value reaches the postgres.js wire serializer still a Date object, and it throws:

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

The fix is one method call:

WHERE ${gameSessionsTable.completed_at} < ${twoYearsAgo.toISOString()}

There is nothing clever to learn about toISOString. The lesson is that a scheduled job is the easiest place in a codebase to fail silently forever: no user sees it, no request waits on it, and unless the run reports a number you are watching, "threw on every invocation" and "found nothing to do" look exactly alike from outside. Point 3 above is what turns this class of failure from invisible into obvious.

What the job looks like now

The deletes are batched, and the reason is specific to running on a function with a time limit:

/** Rows removed per statement. */
const DELETE_BATCH_SIZE = 1_000;

/** Safety valve so a runaway loop cannot spin for the whole function lifetime. */
const MAX_BATCHES_PER_RUN = 50;

A single unbounded DELETE has no upper bound on how long it holds locks or how much write-ahead log it generates. If it ever exceeded the 60 second limit the function would be killed mid-statement and the whole transaction would roll back, which means it could never make progress: the backlog grows, the next run is slower, and it fails again, forever. Batching makes it incremental. Each batch commits, so partial progress survives, and the next run resumes where this one stopped.

Postgres has no DELETE ... LIMIT, so the bound goes in a subquery:

DELETE FROM ${gameSessionsTable}
WHERE ${gameSessionsTable.id} IN (
  SELECT ${gameSessionsTable.id}
  FROM ${gameSessionsTable}
  WHERE ${gameSessionsTable.completed_at} < ${twoYearsAgo.toISOString()}
  LIMIT ${DELETE_BATCH_SIZE}
)

The previous version of this ran a SELECT for every expiring row, all columns including a jsonb metrics blob, called .length on the result to get a count, and then issued an unbounded DELETE. It pulled the entire expiring dataset into the function's memory to produce a number the DELETE itself already reports.

The three cleanups are independent, so they go out together under the one 60 second budget rather than serially, and if the batch cap is hit the job says so in the log and leaves the rest for tomorrow.

The one that is driven by the row, not the clock

The onboarding cleanup does not compute its own cutoff. Each row carries expires_at, written when the answers are saved:

WHERE ${onboardingProfilesTable.expires_at} < ${now.toISOString()}

The date a user is shown in their settings and the date actually enforced are then the same value, and editing the answers restarts the two year clock for free, because saving writes a new deadline. A cutoff computed inside the job would be a second implementation of the promise made in the UI, and the two would drift.

See it: the promises this job keeps are written out at cogniprep.app/privacy, under Data Retention. "Game sessions and scores: automatically deleted after 2 years". "Device session tokens: automatically removed after 30 days of inactivity". "Application goals: automatically deleted 2 years after you last updated them, or whenever you delete the answers themselves from Settings". That last clause is the expires_at column, described in English. Every one of those sentences is a scheduled DELETE that has to actually run, which is why the job now reports three numbers instead of one.