The deletion job that never ran once

Part 7 of an ongoing series on Moty, a fitness data platform I build and run solo in production: one multi-tenant API, two Next.js frontends. All numbers come from production measurements and git history.

Everything described here was found by my own audit, fixed, and deployed before publishing. That's what makes it writable.

The finding

While documenting my codebase for an internal audit, I traced the account-deletion path end to end and arrived at a sentence I had to read twice. The nightly cleanup job that erases withdrawn users' data had never completed successfully. Not once, since the day it shipped.

It wasn't degraded, and it wasn't flaky. It was zero percent functional, every night, in production, while the code sat in the repo looking exactly like a working feature.

The mechanism: schema drift meets a transaction

The job's SQL referenced four database objects that no longer existed: two tables that had been renamed in earlier migrations, one table that had been dropped entirely, and one column that a migration had removed before the deletion feature was even written. That last one deserves a second read. The feature was born referencing a column that was already gone, while its own migration notes claimed the column was there.

The job did its work in one transaction. The second statement hit a missing table, threw, and rolled everything back, so every night it started, failed, and left no trace of progress. The transaction, correct engineering in isolation, turned four small drifts into one total failure.

Why it was silent, twice over

The alert never fired because the job's error log used its own prefix, and the log-metric filter that pages Slack matches specific literal patterns. The failure was logged faithfully every night into a channel nothing was watching. Lesson one, which I now treat as a rule, is that alert patterns are contracts. A log line that doesn't match the filter is a letter with no address on it.

The tests never failed for a more interesting reason. The unit test mocked the database client, and a mock will cheerfully accept SQL that references tables from a parallel universe. My first fix attempt, an integration test on an in-memory database, failed differently. Our schema uses Postgres-specific column types that the in-memory engine ignores, so the very tables the job needed most never existed in the test database. The suite kept passing because it couldn't see the tables that mattered.

The test that finally works doesn't use a live database at all. It parses every schema migration in order, reconstructs the final schema, and reconciles the job's target list against it. It also runs the check in reverse, so any new table referencing users that's missing from the deletion list fails the build. That test found the dropped column on its first run.

The policy layer underneath

Fixing the SQL forced the better question: what were we actually supposed to delete, and when? The job waited out a 90-day grace period which, when I checked every active policy document, had no basis anywhere. The terms said the opposite: delete on termination. A "statutory five-year retention" note in internal docs turned out to be an unverified assumption that had fossilized into fact; the same document admitted two paragraphs earlier that the retention policy was still undecided.

So the design changed to match the documents. Destruction now happens inside the withdrawal transaction itself. If erasure fails, the withdrawal fails; there is no window where an account is gone but its data isn't. The nightly job survives as a safety net that sweeps stragglers and, on a healthy day, processes zero rows.

Two more things surfaced in the same pass. The withdrawal code was clearing four identifier fields but leaving name, gender, and birth date, an identifiable combination, untouched. And our "anonymization" was a keyed hash of the user id, which anyone holding the key could recompute. That's pseudonymization under an anonymization label. It's now a random identifier with no mapping kept anywhere. The same random value repeats across a user's analytics rows, so cohort statistics survive with nothing that points back.

What I'd pass on

• Mocks cannot test schema. Any job whose correctness depends on table and column names needs at least one test that sees the real schema, even a reconstructed one.
• Deletion needs the rigor we reserve for creation. A broken signup fails loudly in front of a user within minutes. A broken deletion fails silently in front of no one, indefinitely. The asymmetry means deletion paths need more monitoring, not less.
• Trace policy claims to documents. "Statutory retention" felt authoritative right up until I asked which statute. If a data-handling rule can't cite its source, it's a guess with seniority.
• Audits pay. This wasn't found by an incident, a user, or a regulator. It was found by writing down what the system does and noticing where the writing wouldn't hold together.