Flow
Order for adding a source to cart lines
11. Add the column
Non-null, with the by-hand marker as default, so old lines become hand-added lines.
22. Widen the unique index
Swap the old rule for one that includes the source. Check whether your runner wraps it in a transaction.
33. Pass the source everywhere
Every writer sends the source; anything that forgets gets the marker.
44. Switch the reader last
Trust the stored source and only guess for hand-added lines.
55. Make the importer keep classes
The importer keeps variant classes and refuses rows that still say any.
Within half an hour of shipping a cart label one evening, I changed how it worked. The label was a guess about which target put each line in the cart.
What the label was guessing from
My planner makes stock targets per store and category, sometimes split by variant class. Each target hands units to the products in it, and a rebuild writes those into a draft cart. The cart shows a label saying which target each line came from.
The planner knew which target made each line. The rebuild threw that away, the table had no place for it, and the label was worked out later by finding targets that hold the line's product and picking the biggest.
Where it breaks
One supplier sells generic placeholder products and, as I understand it, picks the real variant later. So one product can sit inside several targets at one store. Then the label points at the biggest target for every line, even the line a small target asked for.
Worse, the cart allowed one line per product per store. Reading the old code, I think a second target would have hit that rule instead of getting its own line. I haven't seen it happen.
Put the reason in the key
Each line now records its source: the target's ID, or a fixed marker for lines added by hand. The source is part of the uniqueness rule.
So the cart keeps one line per product, store and source.
Use a real marker, not an empty value. Postgres treats empty values as all different, so the rule wouldn't stop duplicate hand-added lines.
Then roll it out in order. Add the column with the marker as its default, so old lines become hand-added lines. Swap the old unique index for the wider one. Make every writer pass the source. Switch the reader last, so it trusts the stored source and only guesses for hand-added lines. Mine doesn't wrap the index swap in an explicit transaction, and I haven't checked whether the runner does. Check yours.
Callers that still assume one line
Removing a product without naming a source now only removes the hand-added line. A screen that groups by product alone will merge the reasons again. A target retired later leaves its lines with no label.
Why the importer had to change too
My importer used to file every target in these categories as "any," which I suspect is why this hadn't bitten yet. Now it keeps the variant class the whole way through and refuses to run if a category that needs one still says "any." As committed, it would have refused my own catalogue, because the rule went in before any rows were labelled; that took a follow-up about an hour later.