siddharthd
0cb46a087b
The ledger already knew we had ordered from a place; it did not know the food was bad. Orders got repeated from places we disliked because nobody remembered by the time the next one went in. That is what the receipt ingestion was for (ING-9) and the last piece was missing: order_reviews existed as a table with no API, no UI and no writes. Four levels, not three. "Loved" and "liked" are both "would order again" but only one is worth a detour, and "ok" is not a recommendation. A verdict belongs to a person, not to an order. A shared meal produces two opinions and they routinely disagree — that disagreement is the useful part, and the old UNIQUE on transaction_id alone could not hold it. Now UNIQUE (transaction_id, participant_id), and the default is the signed-in user rather than the owner: Sonu authenticates through the same Traefik OAuth as participant 4, so an owner default would have filed her verdict under his name. Per-item opinions key on the item DESCRIPTION, not its index. An index is meaningless across orders; "the Pad Thai here is good" is the signal that has to survive into the next order from the same merchant. Only the two poles are offered — a per-item "ok" answers neither of the questions you ask at order time. Sharing is recorded as a real 50/50 split, not a decorative flag. The split already IS the record that an order was shared, and two records of one fact drift apart. An ABSENT item_verdicts means "leave them alone"; an empty array clears them. Without that distinction a note-only save silently wipes every per-item opinion — the same shape as the bug that reset `settled` on split rewrites, and just as invisible on screen. Mutation-tested: making keepItems a no-op fails exactly one test. mockDbWithPool gained queryRow. Omitting an export from the mock makes it undefined at the call site, which fails as "not a function" and reads like a code bug rather than a test-harness gap.
53 lines
2.7 KiB
SQL
53 lines
2.7 KiB
SQL
-- A four-level verdict, per-item opinions, and one verdict per PERSON.
|
|
--
|
|
-- Three levels collapsed the distinction that actually drives a re-order:
|
|
-- "loved" and "liked" are both "would order again", but only one is worth a
|
|
-- detour, and "ok" is not a recommendation. Asked for by the user 2026-07-28.
|
|
--
|
|
-- Safe as a straight swap: order_reviews had 0 rows when this was written, so
|
|
-- there are no old values to map. If that ever stops being true, map
|
|
-- again->liked, fine->ok, never->never BEFORE adding the constraint.
|
|
ALTER TABLE order_reviews DROP CONSTRAINT IF EXISTS chk_order_review_rating;
|
|
|
|
ALTER TABLE order_reviews ADD CONSTRAINT chk_order_review_rating
|
|
CHECK (rating IS NULL OR rating IN ('loved', 'liked', 'ok', 'never'));
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- A verdict belongs to a person, not to an order.
|
|
--
|
|
-- A shared meal produces two opinions and they routinely disagree — that
|
|
-- disagreement is the useful part, and one row per transaction cannot hold it.
|
|
-- The Slack nudge asks whether the order was shared; a yes both splits the
|
|
-- expense and asks the other person for their verdict, so the second row is
|
|
-- the normal case for anything shared, not an edge case.
|
|
--
|
|
-- No DEFAULT on participant_id on purpose: a verdict silently attributed to
|
|
-- whoever happens to be id 1 is worse than an insert that fails loudly.
|
|
ALTER TABLE order_reviews
|
|
ADD COLUMN IF NOT EXISTS participant_id integer
|
|
REFERENCES participants(id) ON DELETE CASCADE;
|
|
|
|
UPDATE order_reviews SET participant_id = 1 WHERE participant_id IS NULL;
|
|
|
|
ALTER TABLE order_reviews ALTER COLUMN participant_id SET NOT NULL;
|
|
|
|
-- Replace the per-transaction uniqueness with per-transaction-per-person.
|
|
-- Dropping this is what allows the second opinion to exist at all.
|
|
ALTER TABLE order_reviews DROP CONSTRAINT IF EXISTS order_reviews_transaction_id_key;
|
|
|
|
ALTER TABLE order_reviews
|
|
ADD CONSTRAINT order_reviews_transaction_participant_key
|
|
UNIQUE (transaction_id, participant_id);
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- item_verdicts already exists as jsonb DEFAULT '[]'. It has never been
|
|
-- written. The shape is now fixed as:
|
|
-- [{"item": "<line item description>", "verdict": "loved"|"never"}]
|
|
--
|
|
-- Keyed by description rather than by position in line_items: an index is
|
|
-- meaningless across orders, and the reusable signal is "the Pad Thai here is
|
|
-- good", which has to survive into the next order from the same merchant.
|
|
-- Only the poles are offered — a per-item "ok" is noise nobody would ever read.
|
|
ALTER TABLE order_reviews ADD CONSTRAINT chk_order_review_item_verdicts
|
|
CHECK (jsonb_typeof(item_verdicts) = 'array');
|