Body composition, and the charter rule we wrote against it
Charter rule C6 banned body composition framing at every age. Then we specified a product built on exactly those numbers. Here is the whole argument.
The four that fell over immediately
A Snap outside the 90 to 300 second window. A prescription stored without the context decision that authorised it. A minor account with no linked guardian. A cohort report materialised with fewer than eight contributing people.
All four were forbidden. All four had passing unit tests. All four were reachable through a path that did not run the validating code, and we found them within hours of the constraints going in, because Postgres does not care which service is calling.
What a constraint proves that a test does not
A unit test proves that one function rejects one input. Database constraints prove that no row can exist in that state, whatever wrote it — a service, a migration, a back-office script, a person with psql open at two in the morning.
That last case is the one that matters. Every serious incident involving health data that I have read about involved a write path nobody had modelled.
Testing the constraints themselves
A constraint you have not attempted to violate is a constraint you are assuming. So there are fourteen tests in `db/test`, and each one issues the write that should be rejected and asserts the rejection, including the error class.
They run in CI against a real Postgres 16, not a mock. `pnpm db:test` printing fourteen rejections is a release gate.
What does not belong in the database
Anything that is a judgement rather than an invariant belongs outside the database constraints. Dose calibration, timing, the ranking of one movement over another — these change weekly and belong in code where they can be reasoned about and reverted.
The line we drew: if violating it would be a safeguarding failure or a privacy breach, it goes in the schema. If violating it would just be a worse recommendation, it stays in the service.
The four that were already wrong
Worth being specific, because "it found bugs" is the kind of claim that costs nothing to make. The Snap duration violation came from a seeding script that predated the 90 to 300 second window and had been re-run during a data migration three weeks earlier. The context-decision violation came from a retry path that rebuilt a prescription from a cached candidate and forgot to carry the decision identifier across.
The guardian violation was the uncomfortable one. An account created through an organisation import could reach an under-18 age band without a guardian link, because the import validated against the organisation schema rather than the consumer one. Nobody had done it in production. The path existed.
The k-anonymity violation was a materialised view refreshing on a schedule, which recomputed cohorts as membership changed and did not re-check the threshold on refresh. A cohort of eleven people in January was a cohort of six by March, and the view happily reported it.
What this costs on a normal day
Migrations get slower to write, because a constraint has to be satisfied by every row that already exists before it can be added. Twice we have had to write a backfill first and add the constraint second, which is more work than not having the constraint and is the entire point.
Error messages also have to be handled properly. A raw Postgres constraint violation is not something to show a person, so each one maps to a domain error with a sentence that says what was wrong and what to do instead.
Where we drew the line
The rule of thumb that settled the arguments: if violating it would be a safeguarding failure or a privacy breach, it belongs in the schema. If violating it would only produce a worse recommendation, it stays in the service where it can be tuned weekly and reverted on a Friday afternoon.
That puts fourteen things in the database and several hundred in code, which is about the ratio we expected. The fourteen are the ones we would have to explain to a regulator.