« Hold on, we need to talk, this doesn't add up »
Friday morning, 9:15 AM. Françoise is in her cockpit — three screens: on the left the Excel attendance sheet she's kept up to date for fifteen years, on the right Sage, and in the middle Rembrandt for three weeks now. Cup in hand, the one with her face printed on it that someone gave her at Christmas. She swivels in her chair and calls over from her office:
« Michel, combien on a d'inscrits pour la rentrée, dis-moi ? » — Michel, how many are enrolled for the new term, tell me?
I type SELECT COUNT(*) FROM inscriptions WHERE statut='inscrit' and give her a number. She writes it down, checks her Excel, counts line by line the way she always does, and forty-five seconds later I hear her step out of the next office:
« Bon attends, il faut qu'on se voie toi et moi, ça colle pas. » — Hold on, we need to sit down, this doesn't add up.
Céline had enrolled in September for seven courses. One contract, one signature, one instalment plan. And yet, in my inscriptions table, seven rows. Céline counted as seven students.
This isn't an anecdote. It's the moment I understood that the name of my table and the object it stored had stopped corresponding. That divergence taught me something about what it means to model a business.
If you have 30 seconds. When a table's name stops matching what it actually stores (here,
inscriptionsthat in fact stores seats), three classic options appear: status quo, rename, split. There is a quieter fourth option: keep the schema and hold the rule as a documented invariant. What follows: the real business case, three SQL queries that stop lying, and a discipline applicable to your own ambiguous schemas.
The trap of naive modeling
When I started Rembrandt, the ERP of our ceramics network (six sites, several hundred students), I reasoned like every developer who knows a business only from the outside. One student, one course, one enrollment. Three obvious tables, three clean foreign keys. The inscriptions table bore that name logically because I thought a row represented an enrollment event.
Sure, I had put a UNIQUE index on (contact_id, cours_id). Technically, multi-course was possible. I hadn't really thought about it. I had done it by reflex, because you always put a composite unique to avoid duplicates.
But real business showed up. Céline takes seven courses because she loves ceramics and she has the time. Other students take two or three. In practice, fewer than 15% of enrollees accumulate, but that's enough for every poorly-scoped query to lie. And I had written many poorly-scoped queries — not out of carelessness, but because the table's name had suggested a false identity.
The night I counted the lies
Audit April 11th–12th. I compared the CRM Google Sheet of the new term against my Supabase database. I found 81 multi-course seats missing from Rembrandt, and 60 orphan contacts floating without a course attachment. The sheet, for its part, handled a student like Céline cleanly across its columns. Françoise had never had the problem — she had been checking off attendance by hand, row by row, for fifteen years. Her Excel was right.
I could have migrated quietly and gone on living with a schema that lied. I did the opposite. I wrote the rule I should have written in November.
A row in the
inscriptionstable represents a seat (one contact × one course), not a commercial enrollment.
What the business calls "an enrollment" — the annual contract signed by Céline — no longer has a dedicated row in the database. That enrollment is derived, rebuilt on the fly from the contacts table (status column, code_cours column that aggregates the codes of the courses occupied). The seat, on the other hand, is stored. Two business objects, one stored, the other computed.
Three options, one tenable
I considered three alternatives before deciding.
| Option | Cost | Outcome |
|---|---|---|
| Status quo | Nothing short-term | Double counts forever, a trap for every future query |
Rename inscriptions → places |
Heavy migration: FKs, RLS, triggers, views, audit scripts, application code | Explicit semantics, zero ambiguity |
Split into two tables (commercial inscriptions + places) |
Multi-week project, duplicates workflows | Clean, extensible domain model |
I chose a fourth, quieter option. Keep the schema, clarify the semantics, hold the rule as an invariant. The table name keeps lying, and I own it. What I can no longer do is write a query without reminding myself what the table actually stores.
Why that choice? Because a table name, once it's been used in migrations, RLS, triggers, indexes, views, business code and audit scripts, stops being a name and becomes infrastructure. Renaming it to fix a semantic error is like moving a load-bearing beam to repaint a wall. The rule, on the other hand, fits in one line and gets recalled in every Claude skill.
I don't pretend that splitting into two tables wouldn't be the cleanest model. It may become the right move the day we have variable per-contract pricing, or OPCO agreements with multiple trainees and multiple courses grouped on one invoice. I've documented those thresholds in the ADR that carries the decision. Above a certain volume, semantic clarification is no longer enough, the structure must follow. We're not there.
What a table really cuts up
There's a lesson here that goes beyond my case. A database doesn't record reality, it cuts it up. And that cutting carries implicit decisions about what a thing is. A commercial act? An occupied slot? Both at once? When the business talks about enrollments and the database stores seats, it isn't a spelling mistake. It's a sign that a choice has been made, possibly unwittingly, about the relevant unit.
The conscious choice, for me, came late — after the bugs, after the audit, after Françoise looking at me one morning with an enrollment count that wasn't the right one. It rarely happens the other way around.
What it changes day to day
The rule plays out in five concrete moves.
Counting distinct students now goes through COUNT(DISTINCT contact_id) FROM inscriptions, never COUNT(*). Counting occupied seats in a given course uses WHERE cours_id=X, and the denormalized cours.places_inscrits column is maintained by a trigger. Multi-course UPSERTs all go through onConflict='contact_id,cours_id' with a pipe-add on contacts.code_cours. UI labels say "seats", not "enrollees", everywhere the source is the inscriptions table. And the rembrandt-conventions skill that Claude auto-loads reminds the rule at every coding session on this scope.
The next morning, I went back to Françoise with the right number. She checked, counted, put her cup down, and delivered her verdict without lifting her head:
« Bon. Faut pas se prendre la tête, c'est déjà ça. » — Alright. No point overthinking it, that'll do.
The schema holds on 91,000 lines of code, six sites, 29 days of production. Not because it's beautiful. Because we finally named what it does — and because Françoise, in the next office, still keeps checking her Excel alongside Rembrandt.
What you can copy into your project
The three queries below, plus a minimal contacts / cours / inscriptions schema with the composite index, live in the series' companion repo, MIT license: github.com/michelfaure/rembrandt-samples.
Three SQL queries that translate the invariant rule, directly usable on any schema where a composite-key table stores relations rather than entities:
-- Count distinct people, never rows
SELECT COUNT(DISTINCT contact_id) FROM inscriptions;
-- Count seats in a given course
SELECT COUNT(*) FROM inscriptions WHERE cours_id = $1;
-- Multi-course upsert without wiping the contact's other seats
INSERT INTO inscriptions (contact_id, cours_id, statut) VALUES ($1, $2, $3)
ON CONFLICT (contact_id, cours_id) DO UPDATE SET statut = EXCLUDED.statut;
And a broader discipline: as soon as a table name diverges from its content, write the invariant rule into your CLAUDE.md or equivalent, and have it echoed in the skill that auto-loads your project context. You'll make fewer mistakes than if you rely on memory.
And you — where do your table names lie? I read the comments.
Companion code: rembrandt-samples/inscriptions-places/ — minimal contact × course schema and the three SQL queries that answer "how many enrolled" without lying, MIT, copy-pastable.
Top comments (0)