Deriving the base unit instead of flagging it

A pharmacy buys paracetamol by the tablet, the strip, the box, and the carton. Same drug, four things you can put in a cart, four prices, one pile of stock behind all of them.

So: where does the unit live? Easy to get wrong, annoying to unpick later. I got it wrong first.

Three layers, not two

The catalogue splits identity from sellability from branch:

products                    global SKU: name, barcode, manufacturer
  └── product_units         sellable unit: name, conversion_factor
        └── branch_product_units    price, stock, points, visibility

conversion_factor is how many base pieces are in a unit — tablet 1, strip 10, box 100. Price and stock sit on the bottom layer, per branch, because the same box costs something different in another province and the stock definitely isn't shared.

A product is buyable only when both the branch row and the specific unit row are active and public. Two gates, not one, because "hide this product entirely" and "we stopped selling it by the carton" are genuinely different decisions and the shop makes both.

The boolean that looks obvious

Every product needs a base unit — the denominator you count stock in. Obvious model:

ALTER TABLE product_units ADD COLUMN is_base_unit boolean;

Reads fine. Queries fast. It's also a second source of truth for something the data already tells you, which means it can disagree with that data. Give it time and it will.

All of these are reachable from the admin UI:

• two units on one product both flagged base
• no unit flagged, because the first one got deleted
• the flag on the strip while a tablet unit exists with a smaller factor

None of that is exotic. It's a Tuesday. And each one gives you a product whose stock maths is quietly wrong instead of loudly broken, which is the worst kind.

Just derive it

The base unit isn't extra information. The conversion factors already determine it: it's the unit with the smallest factor, tie-broken by sort order, then id.

Migration 0040 dropped the column and replaced it with a correlated subquery. The tie-break matters more than it looks — two units at factor 1 is legal, someone will do it, and "smallest" on its own isn't a total order. Without the tie-break the base unit can change between two runs of the same query, which is a much more confusing afternoon than just getting a wrong answer.

The rule, generally: if you can compute a value from data you already store, storing it too means writing code whose only job is keeping two things agreeing. That code is never done.

Cost is a subquery on read paths. You pay it with an index:

CREATE INDEX ON product_units (product_id, conversion_factor);

These stayed correlated subqueries instead of becoming LATERAL joins, for a deeply unglamorous reason: the catalogue's price-range filter binds to the price inner join, and a lateral would have restructured the query enough to make that filter awkward. The prettier version fought the feature that pays the bills, so the prettier version lost.

There's a second variant that turned out to matter more than the first. The public base unit isn't the base unit — it's the smallest unit that's active and public at this branch. Stop selling loose tablets and the displayed price and unit name have to become the strip immediately, with nobody touching an admin screen. Same derivation, restricted to visible rows.

Fill the grid so nothing's missing

Deriving instead of flagging kills one class of inconsistency and opens another: branches × units is a grid, and a grid with holes breaks joins.

Add a unit and every branch needs a row for it. Add a branch and every existing unit needs one. Both directions handled explicitly, on write:

• create a product unit → insert a row for every branch
• create a branch → insert rows for every existing unit

ON CONFLICT DO NOTHING, with deliberately useless defaults: price 0, quantity 0, is_active false, is_public false.

That last part is the safety property. A new unit exists everywhere and is on sale nowhere, at a price of zero nobody can reach. Forget to configure it and you get an invisible product, not a product sold for free.

One footnote that ate an afternoon: the branch-product upsert has to set updatedAt by hand. The ORM's $onUpdate doesn't fire on the conflict-update path, so rows quietly kept their original timestamp. Fine — right up until something downstream syncs on updatedAt and skips them forever.

What it bought

Base-unit inconsistency stopped being a category of bug instead of becoming a rarer one. No repair script, no admin screen for fixing a bad flag, no support ticket about a product whose stock doesn't add up. The state that would cause it can't be written down.

The trade is real: reads carry a subquery, and someone reading the schema cold can't see "which unit is the base" at a glance. I'd take it again. The invariant is enforced by the shape of the data instead of by everyone remembering.