The alias you forgot

The convenience

Soft deletes are an obviously good idea. Set a timestamp rather than removing the row. Nothing is ever truly gone, mistakes are reversible, and history stays intact for anybody who needs to look.

Every ORM supports it. Mark the model, and every query it generates quietly appends the predicate.

WHERE deleted_at IS NULL

You stop thinking about it. That is the entire point, and it is also the mechanism of the failure.

Where it breaks

A report was slow. The ORM was generating something inefficient across several tables, so I wrote the query by hand. Faster, clearer, and it produced the right answer in testing.

In production it included students who had been removed.

The main table had the predicate. I had remembered that, because it was the table the query was obviously about. The join to another table, three lines further down, did not.

SELECT s.*, c.name
FROM students s
JOIN classes c ON c.id = s.class_id
WHERE s.deleted_at IS NULL           -- remembered
  -- AND c.deleted_at IS NULL        -- did not

A deleted class was still matching, and rows that should have been excluded came back through the join.

Why it is worse than an ordinary bug

Three reasons, and they compound.

It is silent. Nothing errors. The query returns rows. They look entirely plausible, because they were real rows once.

It is invisible in development. Development data rarely contains soft deleted records in interesting combinations. Everything passes locally, forever.

It appears in the highest stakes place. Raw SQL gets written for reports and exports, because that is where performance matters. Which means the wrong numbers appear in exactly the documents somebody prints, signs, and sends onwards.

Mine surfaced when a member of staff noticed a name in a report for somebody who had left. Not a crash. A person reading a piece of paper and knowing it was wrong, which is a considerably worse way to find out.

The rule I now apply without exception

Every alias in every FROM and every JOIN gets the predicate. No exceptions, no judgement calls.

FROM students s
JOIN classes c   ON c.id = s.class_id  AND c.deleted_at IS NULL
JOIN terms t     ON t.id = s.term_id   AND t.deleted_at IS NULL
WHERE s.deleted_at IS NULL

It is verbose and it looks like noise. That is acceptable, because the alternative is a judgement about whether this particular table matters, and that judgement is wrong occasionally, and occasionally is enough.

Reviewing raw SQL now starts by counting aliases and counting predicates. If the numbers differ, there had better be a comment saying why.

What I would do differently in the design

The deeper problem is that soft deletion was invisible at the point of writing the query. The ORM had made it something you do not think about, and then raw SQL removed the mechanism while leaving the habit.

Two things would have prevented it structurally.

Views that encapsulate the predicate. A view per table that already filters, so hand written SQL selects from active_students rather than students. Getting it wrong then requires deliberately choosing the raw table, which is a visible act rather than an omission.

A test that reads the query. Parse the SQL, count the aliases, count the predicates, and fail if they do not match. Crude, and it would have caught this exact bug in the pull request. This is the same principle as reading the output rather than the source: check the artefact, not the intent.

The wider lesson

Any convenience that works by being invisible will fail the moment somebody steps outside the mechanism that provides it, and they will step outside it precisely when performance matters, which is when the stakes are highest.

That is not an argument against soft deletes. It is an argument for knowing which of your guarantees are enforced and which are merely usually applied, because the second kind will find the one place you did not apply them, and it will not tell you.