feat(transactions): honour ?q= so a link can land on one row

The Slack order nudge links here. Without it the link drops you at the top of an unfiltered ledger and the merchant has to be found by hand, which is how a nudge stops being opened.
feat(orders): record what we thought of an order, per person
2026-07-28 15:34:24 +10:00 · 2026-07-28 15:31:22 +10:00 · 2026-07-28 14:41:05 +10:00 · 2026-07-28 13:54:46 +10:00 · 2026-07-28 13:52:30 +10:00 · 2026-07-28 13:40:48 +10:00
22 changed files with 2396 additions and 345 deletions
@@ -13,6 +13,15 @@ jobs:
          node-version: 22
      - name: Install
        run: npm ci
      # The Prisma client is generated into src/generated/prisma, which is
      # gitignored — so a fresh checkout has no client and every test that
      # reaches src/lib/db.ts dies on "Cannot find package
      # '@/generated/prisma/client'". Schema-only, so it needs no database.
      # Without this the pipeline had been red on every run since at least
      # ae0c34f, which is how the failure stayed invisible: it looked like
      # the normal colour.
      - name: Generate Prisma client
        run: npx prisma generate
      # Advisory until the pre-existing lint debt is cleared (2026-07-19:
      # ~20 errors across budget/insights/shared pages) — then make blocking.
      - name: Lint (advisory)
@@ -45,3 +45,7 @@ next-env.d.ts
 # Raw statement exports — real financial data, never commit
 dump/
 # Python tooling for scripts/ (split_csv_match.py)
 .venv/
 __pycache__/
@@ -111,29 +111,64 @@ it silently deletes the unlogged remainder from spend totals.
 ### Shared expenses and settlement — read before touching
-The model is under active redesign. See `docs/shared-expenses-design.md` for the
+Rebuilt 2026-07-28. `docs/shared-expenses-design.md` describes the live model;
-proposal and what is already decided. Three traps:
+it is no longer a proposal. Everything the old version of this section warned
 about has changed — if you are working from memory of it, re-read.
-**`transaction_splits.settled` is dead data.** It is `false` on every row. Its
+**The cutover date is the primary balance gate, not the `settled` flag.**
-only writer was `/api/splits/settle`, removed in `3f04cbd` because nothing called
+`ACTIVE_OBLIGATION` (`src/lib/analytics-sql.ts`) is
-it and one request could mark all of a participant's splits settled. Do not build
+`ts.settled = false AND t.transaction_date >= '2026-01-09'`. Nothing dated
-on this flag until settlement contexts exist.
+before the cutover can ever be owed, because carryover transaction **2348**
 (dated 2026-01-09, $1,093.22) already carries the whole pre-cutover balance as
 one figure. The bound is **inclusive** — 2348 is itself dated 2026-01-09, so an
 exclusive bound would drop the carryover and the entire pre-cutover balance.
-**`getParticipantBalances` computes `splits − payments` and is correct.** Do not
+Consequence: **pre-2026 transactions can be split freely.** A split on a 2024
-"fix" it to exclude settled splits — the payments that settled them are still
+grocery shop describes how the expense was shared — which is what stops it
-subtracted, so you would double-count. The two settlement models (running tab vs
+inflating spend — without asserting a debt. 657 pre-2026 transactions carry
-per-split flag) must not be mixed.
+1,266 such splits, imported from SplitMyExpenses and marked `settled`.
-**Settlement cannot be attributed per trip.** `split_payments` records only
+**Spend counts settled splits; owed does not.** `myShare`/`mySplitOf` must NOT
-from/to/amount/date. Any per-trip settled/unsettled figure is fabricated; the
+filter on `settled` — half a 2025 grocery shop was your expense whether or not
-trip view used to show one and always reported 100% unsettled. Trips show share
+the other half was repaid. Filtering it out re-inflates exactly the figures the
-only, and point at `/shared` for real balances.
+historical import exists to correct.
-Also: settlements already exist twice. Four of eight `split_payments` match an
+**Any split write path that deletes-and-recreates must carry `settled` across.**
-offset-account credit exactly on amount and date, with `linked_transaction_id`
+`POST /api/transactions/[id]/splits` did not, and silently converted discharged
-populated on only one. And Sonu's loan contributions (`…emi` in the offset
+obligations into live debt — $37,233.28 was exposed. Fixed in `6add958`.
-account, 39 rows, $37,980.24) are categorised `transfers`, indistinguishable from
+`rule-actions.ts` is safe only by the shape of its upsert
-ordinary internal transfers.
+(`ON CONFLICT DO UPDATE SET share_percent` never touches the flag).
 The rules-apply revert route restores it explicitly.
 **Settling up is recording a payment.** There is deliberately no "mark settled"
 action. `settled` marks obligations discharged *outside* this app; doing both
 would subtract the settlement twice.
 **Payments carry scope, and one transfer can carry several rows.**
 `split_payments.trip_id` (migration 0022) says which tab a payment settles;
 NULL is the ongoing household tab. There is no unique constraint on
 `linked_transaction_id`, so a grouped transfer is recorded as one row per scope
 that re-add to the transfer — that is how Sonu's $3,779.33 and $4,794.06 were
 allocated Europe-first with the remainder to household.
 **Trip owed must be owner-scoped; trip cost must not be.** The owed query
 applies `OWNER_SCOPE`; without it a debt between the *other two* participants
 reads as owed to you ($1,605.49 on Europe 2026). Trip *cost* deliberately counts
 every payer — a trip cost what the group put into it — which is why the stat card
 says "all payers, net of refunds". Do not "fix" the missing scoping there.
 **Duplicates are superseded, never deleted.** `transactions.superseded_by_id`
 (migration 0023); 31 rows / $42,040.68 from overlapping ANZ statements 107/142/143.
 Every child of `transactions` is `ON DELETE CASCADE`. The exclusion lives *inside*
 `EXCLUDE_RECONCILED_SOURCE`, so any query applying that fragment gets it free —
 and any query that does not still double-counts.
 **Refunds:** a *partial* refund is netted in SQL (`NET_SPEND_ROWS`/`SPEND_SIGNED`);
 a *cancelled* booking has both legs untagged from the trip by hand, because a
 trip never incurred a cost it cancelled.
 **Trips:** Europe 2026 (id 1, 19 Mar–12 Apr), Auckland 2026 (id 2),
 Europe — Sonu + Sunny (id 3, 12–28 Apr, created 2026-07-28 from tag 5).
 **Partial split coverage inside a category is usually correct, not a gap.** Only
 *shared* items are split. `utilities` sits at 69% yours because Globird, OVO, GWW
@@ -144,9 +179,12 @@ Billdu, Spotify and Patreon are not. `fees` and `charity` are 100% yours and
 correct. Check the merchants before concluding a rule was never applied — a
 category-level ratio that "looks wrong" usually is not.
-Splits exist in this app from **2026-01-09** only; earlier splits lived in
+**Still true, and still a caveat:** Sonu's loan contributions (`…emi` in the
-SplitMyExpenses. So a trailing-12-month per-person series splices six months of
+offset account, 39 rows, $37,980.24) are categorised `transfers`, indistinguishable
-gross onto six months of net. Use Feb–Jun 2026 for anything per-person.
+from ordinary internal transfers. The loan model below is unbuilt.
 **`order_reviews` is a table wired to nothing** — 0 rows, no API, no UI, no
 writes. The "never order from here again" capability does not exist.
 ### The shared loan
@@ -1,57 +1,159 @@
-# Shared expenses, settlement, and the shared loan — design proposal
+# Shared expenses and settlement
-Status: **proposal, nothing built**. Written 2026-07-26 for review.
+Status: **built and live**, as of 2026-07-28. The loan section at the end is
 still a proposal — nothing there is built.
-## Why this exists
+This replaces the 2026-07-26 proposal. That document described three problems
-
+and proposed a `settlement_contexts` table to solve them. The problems were
-Three questions have no answer in the current model:
+real; the table was not built, and the reasoning for not building it is
-
+recorded under [What was rejected](#what-was-rejected).
 1. Which splits does a settlement payment settle?
 2. Is the Europe trip settled, separately from the ongoing household tab?
 3. Whose expense is a $2,500 loan repayment when Sonu funds part of it?
 They look like three problems. They are one: **the app records money moving, and
 separately records who owes whom, and the two never meet.**
 ---
-## What is actually broken
+## The one rule
-### Two settlement models, neither finished
+**Spend and owed are different questions asked of the same table, and the line
 between them is the cutover date, refined by `transaction_splits.settled`.**
-| Model | Where | State |
+`ACTIVE_OBLIGATION` is `ts.settled = false AND t.transaction_date >=
 '2026-01-09'`. Nothing before the cutover can be owed, because carryover
 transaction 2348 already carries the entire pre-cutover balance as one figure —
 so a split on an older transaction describes only *how an expense was shared*.
 That is what makes splitting history safe, and it is why the flag is a
 refinement rather than the guard: any delete-and-recreate write path resets a
 boolean, and one did.
 |  | Counts settled splits? | Why |
 |---|---|---|
-| Running tab | `split_payments` (from, to, amount, date) | **in use** — 8 payments, $37,881.10 |
+| **Spend** (`myShare`, `mySplitOf`) | **Yes** | Half a 2025 grocery shop was my expense whether or not the other half was ever repaid. |
-| Per-split flag | `transaction_splits.settled` | **never used** — all 673 splits are `false` |
+| **Owed** (balances, trip figures) | **No** | A discharged obligation is not outstanding. |
-They are honoured inconsistently:
+Getting this backwards in either direction is the failure the model exists to
 prevent. Filtering settled rows out of spend would re-inflate exactly the
 figures that importing settled history exists to correct.
- `getParticipantBalances` (the shared page) ignores `settled` entirely
+The predicate is `ACTIVE_OBLIGATION` in `src/lib/analytics-sql.ts`.
 - `/api/participants/[id]/balance` filters on `settled = false`
 - `getTripAnalytics` reports settled/unsettled **from the unused flag**
-The third is a live bug. Every trip shows 100% unsettled forever, even though
+## Two orthogonal axes
 Molina has paid $20,782.79 against $19,556.07 of splits and is square.
-### Settlements exist twice, unlinked
+`settled` and `trip_id` answer different questions and neither implies the
 other:
-Four of the eight recorded payments match an offset-account credit exactly:
+- **`transaction_splits.settled`** — *is this obligation still live?*
 - **`split_payments.trip_id`** — *which tab does this payment settle?*
  NULL means the ongoing household tab.
-| Payment date | From | Amount | Offset transaction |
+A trip can be fully paid while the household tab runs a balance, and vice
-|---|---|---:|---|
+versa. Before migration 0022 there was one global pool and this could not be
-| 2026-02-02 | Molina | 7,500.00 | `Transfer from - MEGHALEE BOSE mummy Pa…` |
+expressed, so every trip reported 100% unsettled forever — including trips paid
-| 2026-04-12 | Sonu | 3,779.33 | `Transfer from - MEGHALEE BOSE transfer` |
+in full.
 | 2026-04-21 | Molina | 1,685.24 | `Transfer from - MEGHALEE BOSE mummy split` |
 | 2026-05-16 | Sonu | 4,794.06 | `Transfer from - MEGHALEE BOSE transfer` |
-The same money is a `split_payments` row *and* a `transactions` row.
+## How settling up actually works
 `split_payments.linked_transaction_id` exists but only 1 of 8 rows uses it. So a
 settlement is bookkeeping that happens to resemble a bank credit, rather than
 being that credit.
-### The shared loan is invisible
+**By recording a payment.** There is deliberately no "mark settled" action
 anywhere in the app.
-Sonu's contributions are already in the ledger and unrecognised:
+`settled` marks obligations discharged *outside* this app — the imported
 SplitMyExpenses history, whose repayments happened on a platform we no longer
 run and which therefore have no `split_payments` row here. A live obligation is
 settled by recording the payment, and the balance nets to zero on its own.
 Doing both would subtract the settlement twice: the splits leave the sum *and*
 the payment is deducted, driving the balance negative by the amount repaid.
 ## What is built
 | Piece | Where | Note |
 |---|---|---|
 | `settled` as the single balance gate | `ACTIVE_OBLIGATION` | Applied in both arms of the balances UNION and in the trip owed query |
 | Payment scope | `split_payments.trip_id` (migration 0022) | Household payments do not settle a trip, and vice versa |
 | Owner-scoped owed | `OWNER_SCOPE` in the trip owed query | Without it, a debt between the *other two* participants was reported as owed to the owner — $1,605.49 on Europe 2026 |
 | Direction on screen | `/trips/[id]`, `/shared` | all square / owes you / ahead — you owe them |
 | Historical splits | `scripts/split_csv_match.py` | 1,242 rows across 657 transactions, all `settled` |
 | Duplicate suppression | `transactions.superseded_by_id` (migration 0023) | 31 rows, $42,040.68 |
 | Overlap detection | `STATEMENT_OVERLAPS` → statements page | Red badge; catches the cause rather than the symptom |
 ## The historical import
 The five SplitMyExpenses CSVs are the record of how expenses were shared before
 this app existed. 676 of 1,536 shareable rows matched (44%), and 1,242 split
 rows were written as `settled = true`.
 **The deliverable is historical spend, not balances.** $35,259 left my spend —
 $13,088 in 2024 and $22,117 in 2025 — because a $200 grocery shop that was
 always half hers no longer reads as $200 of mine. Balances were byte-identical
 before and after, which is the assertion that mattered.
 Three things the matcher has to get right, each of which has bitten:
 1. **Date format is decided per file.** The household export writes D/M/YYYY and
   the four trip exports write ISO; 474 rows parse validly under both readings.
   Guessing per row silently swaps January and February for some rows and not
   others.
 2. **A person's column is net balance impact, not their share.** The payer is
   whoever is positive; the other's share is `|their negative| / cost`. So a
   `+cost / -cost` row means the other party owes **100%** — not that the
   expense was unshared, which is the reading that fakes an arrangement change.
 3. **Matching is one-to-one, best pair first.** The NZ trip has two identical
   $10.16 Uber rows against three ledger rows; without this a ledger row is
   claimed repeatedly while the second CSV row looks matched and is not.
 **The 44% is a coverage ceiling, not a matcher weakness.** The CSVs describe 678
 shared expenses in 2024; the ledger holds 591 rows for all of 2024, 3 to 72 a
 month, far less than a household actually spends. South Korea April 2024 matches
 4 of 158. Chasing a higher rate is chasing transactions that were never
 imported.
 ### A reversed recommendation
 The 2026-07-26 proposal said, under *What I would not do*: "**Do not** restate
 history from the SplitMyExpenses CSVs… the value is low: those balances are
 settled and will not change."
 That was overturned on 2026-07-28, and it was wrong in an instructive way: it
 measured the value in *balances*, where it is indeed nil, and missed the value
 in *spend*, where it is $35,259. Importing as `settled` gets the second without
 touching the first. The "combining problem" it cited is real and is why the
 match rate is capped — but a partial restatement of spend beats none, and rows
 that cannot be matched simply keep their current treatment.
 ## What was rejected
 **`settlement_contexts` as a table.** The need was real — a payment must say
 what it settles. But trips already exist and already carry membership on
 `transaction_overrides.trip_id`, so scope is a read of existing data rather
 than a new grouping key. One nullable column on `split_payments` expressed it.
 A general context table would have meant a new entity to create and maintain
 before a payment could be recorded, in a two-person household with two trips.
 **Deleting duplicate transactions.** Every child of `transactions` is
 `ON DELETE CASCADE`, and which member of a duplicate pair holds the curation is
 an accident of import order. Duplicates are superseded instead: the row stays,
 keeps its children, and points at the row that replaces it.
 **Reusing `reconciled_with_id` for duplicates.** Its predicate is scoped to
 `statement_id IS NULL` on purpose — a statement line pointing at something else
 is the survivor, not the duplicate. In the duplicate-import case both rows are
 statement lines, so that predicate can never hide either.
 ## Scale note
 This is a home app for one user, occasionally two, and the second user consumes
 the splits view and little else. Reviews of this subsystem have repeatedly
 proposed enterprise-grade reconciliation, lineage and audit machinery; the
 *findings* are often right and the *sizing* is not. A one-column solution a
 person can hold in their head beats a correct-but-unmaintainable one here.
 ---
 ## Still a proposal: the shared loan
 **Nothing in this section is built.**
 Sonu's contributions are in the ledger and unrecognised. All are categorised
 `transfers` — correct for spend, but it makes a loan contribution and an expense
 settlement indistinguishable:
 | Pattern in offset credits | Rows | Total | Meaning |
 |---|---:|---:|---|
@@ -59,162 +161,15 @@ Sonu's contributions are already in the ledger and unrecognised:
 | `…mummy…` | 6 | $29,721.24 | Molina's money, forwarded by Sonu |
 | other Meghalee | 15 | $71,130.27 | Sonu's own settlements |
 All are categorised `transfers` — correct for spend purposes, but it means a
 loan contribution and an expense settlement are indistinguishable.
 Meanwhile the loan itself, over the 12 imported months:
 | | |
 |---|---:|
 | Principal repaid (`investment`, excluded from spend) | $63,500.00 |
 | Interest charged (`loan_interest`, the only part counted as spend) | $16,523.64 |
 At roughly $25,000/year of `emi` against ~$80,000 of annual repayments, Sonu
 funds about **31%** — of both the equity being built and the interest being paid.
 Today 100% of the interest counts as your spend and 100% of the equity as yours.
 ---
 ## The core problem
 The model conflates two different things:
 - **Money movement** — a credit landed in the offset account
 - **Obligation** — someone owed someone else, and now owes less
 A settlement is both. A loan contribution is both. Right now movement lives in
 `transactions` and obligation lives in `transaction_splits` / `split_payments`,
 with nothing joining them. That is why a payment cannot say what it settles: it
 was never attached to anything in the first place.
 ---
 ## Proposed model
 ### 1. Settlement contexts
 Splits belong to something that is settled **as a unit**. Payments name which
 unit they settle. Balance is computed per context, not globally.
 | Context | Splits | Settled by | State |
 |---|---|---|---|
 | Household (default) | ongoing | periodic payments | running tab |
 | Europe 2026 | trip-bound | lump sum | closeable |
 | Pre-2026 (SplitMyExpenses) | historical | settled elsewhere | **born closed** |
 A closed context still contributes to analytics — you see your true share — but
 contributes nothing to what anyone owes.
 This answers all three opening questions, and it dissolves the `splitFrom` date
 cutoff: pre-2026 splits can be applied retroactively **because they are born
 into a closed context**, so they fix the analytics without creating debt. No date
 guard needed, no risk of resurrecting settled obligations.
 Mechanically: `settlement_contexts` table; `transaction_splits.context_id`;
 `split_payments.context_id`. `transaction_splits.settled` becomes derived
 ("is my context closed?") or is dropped.
 ### 2. Payments are transactions, not a side table
 A settlement is the offset-account credit. `split_payments` becomes a thin
 attribution layer over a real transaction rather than a parallel record of it:
 - Populate `linked_transaction_id` on all existing payments where a match exists
 - On ingestion, an incoming credit that looks like a settlement is *proposed* as
  one for confirmation, rather than silently becoming `transfers`
 - A payment with no matching transaction (cash, or an account not imported)
  stays as a manual row — the model must tolerate that
 ### 3. The shared loan — a separate ledger
 Not a settlement context. The loan is a jointly funded asset with its own
 obligation, and mixing it with expense settlement would let a contribution
 accidentally settle a dinner.
 - `emi` credits are recognised as **contributions**, not generic transfers
 - A **contribution schedule** states what is owed per period (50% of the
  repayment), independent of what was actually paid
 - The running difference is a **receivable** — currently $4,000.00
 The schedule matters: during Sonu's leave the obligation did not change, only the
 payment did. A percentage-of-actual model would silently redefine her share as
 30% and make the shortfall disappear.
 ---
 ## Migration path
 ### Phase 0 — DONE (2026-07-26, commit `3f04cbd`)
 Stop the trip view reporting a settlement breakdown it cannot compute.
 The original plan was "make `getTripAnalytics` and `getParticipantBalances`
 agree". **That plan was wrong** and reading the code before building is what
 caught it:
 - `getParticipantBalances` is *not* buggy. It computes `splits − payments`,
  which is coherent. Excluding settled splits there while still subtracting the
  payments that settled them would have double-counted and broken a working page.
 - The real defect was narrower: the trip view showed Settled/Unsettled from
  `transaction_splits.settled`, which nothing sets. A correct per-trip figure is
  not computable at all, because `split_payments` has no trip attribution.
 So the fix was **subtractive**: the trip view now shows each participant's share
 and points at Shared for what is actually owed.
 Also removed `/api/splits/settle` — unreachable from the UI but live on its URL,
 where one call with `participant_id` would mark every one of that person's splits
 settled, writing a flag nothing reads.
 `transaction_splits.settled` / `settled_at` still exist and are now pure dead
 data. Phase 1 either repurposes them ("is my context closed?") or drops them.
 ### Phase 1 — settlement contexts
 Add contexts; put every existing split in "Household"; every payment likewise.
 Balance queries group by context. Touches `queries.ts` (both balance CTEs),
 `shared/page.tsx`, `trips/[id]/page.tsx`, `split-payments/route.ts`. ~1 day.
 ### Phase 2 — link payments to transactions
 Backfill `linked_transaction_id` for the four exact matches; flag the other four
 for manual linking. On ingestion, propose a matching credit as a settlement
 rather than silently categorising it `transfers`. ~half a day.
 ### Phase 3 — retroactive pre-2026 split
 Create the "Pre-2026" closed context. Apply household split rules into it via the
 rule preview (`/api/rules/[id]/matches`, built 2026-07-26) — fixes ~$97,627 of
 the trailing 12 months currently shown as 100% yours. Then delete the `splitFrom`
 cutoff entirely.
 **Validate the ratio first.** This assumes today's 50/50 held through 2025. The
 SplitMyExpenses CSVs should be used to *check* that assumption — not to
 reconcile, since transactions were sometimes combined and exact matching is
 impossible.
 ### Phase 4 — loan ledger
 Contribution schedule, contributions recognised from `emi` credits, running
 receivable. Independent of contexts — the loan is a separate ledger. ~1–2 days.
 ---
 ## Decisions taken (2026-07-26)
 ### The loan is separate from shared expenses
 Different obligations, different rhythms, different nature: one funds an asset,
-the other funds consumption. They do not share a settlement context and a
+the other funds consumption. They do not share a settlement scope, and a
-contribution is never a settlement.
+contribution must never settle a dinner.
-### The share is 50/50, fixed — with the shortfall tracked
+### The share is 50/50 fixed, with the shortfall tracked
-Not derived from actual payments, which fluctuate. Sonu's obligation is half the
+Not derived from actual payments, which fluctuate. Over 2025-07-01 → 2026-06-30:
 repayment; the difference between obligation and actual is a **receivable**, and
 it is the interesting number.
 Over 2025-07-01 → 2026-06-30:
 | | |
 |---|---:|
@@ -223,65 +178,69 @@ Over 2025-07-01 → 2026-06-30:
 | Actually contributed (26 payments) | $27,750.00 |
 | **Shortfall** | **$4,000.00** |
-She never missed a fortnight; the rate changed:
+She never missed a fortnight; the rate changed — $1,250 × 15 (Aug 2025–Feb
-
+2026, the correct 50%), $1,000 × 3 (Jul 2025, pre-adjustment), $750 × 8
-| Rate | Payments | Period |
+(Mar–Jun 2026, leave).
 |---|---:|---|
 | $1,250 | 15 | Aug 2025 – Feb 2026 (the correct 50%) |
 | $1,000 | 3 | Jul 2025 (pre-adjustment) |
 | $750 | 8 | Mar – Jun 2026 (leave) |
 So the model needs a **contribution schedule** (expected per period) alongside
-actual contributions, with the running difference as a tracked balance. A flat
+actual contributions, with the running difference as a tracked receivable. A
-percentage cannot express "obligation unchanged, payment temporarily reduced,
+flat percentage-of-actual cannot express "obligation unchanged, payment
-difference owed".
+temporarily reduced, difference owed" — it would silently redefine her share as
 30% and make the shortfall disappear.
 ### Interest: recommended as expense, pending final call
-The mechanics are as described — interest is debited to the loan and repayments
+Over 12 months $63,500 of cash left and debt fell by $44,127.36. The $16,523.64
-pay down the combined balance. Reconciles exactly:
+difference bought nothing and is not recoverable — an expense by definition.
 Excluding it leaves the balance sheet unable to reconcile cash out against
 equity gained, and understates annual cost by ~10%.
-    134:  31,000.00 − 8,553.27             = 22,446.73  = balance reduction
+The legitimate concern is that interest is non-discretionary. The answer is a
-    133:  32,500.00 − 7,970.37 − 2,849.00  = 21,680.63  = balance reduction
+fixed-commitments grouping alongside rent, insurance and utilities — a
 presentation change, not an exclusion.
-But mechanics are not the same as economics. Over 12 months $63,500 of cash left
+**Do not** model the loan as a recurring split: that would put $2,500 a
-and debt fell by $44,127.36. The $16,523.64 difference bought nothing and is not
+fortnight of principal into spend, the error migration 0014 exists to prevent.
 recoverable — that is an expense by definition. Excluding it leaves the balance
 sheet unable to reconcile cash out against equity gained, and understates annual
 cost by ~10%.
 The legitimate concern is that interest is **non-discretionary**. The answer to
 that is a fixed-commitments grouping alongside rent, insurance and utilities —
 a presentation change, not an exclusion.
 **Recommendation: keep `loan_interest` as spend, add a fixed/discretionary
 split.** Flagged rather than settled: it is a judgement about what "spend" means
 in your own reporting.
 ## Open questions
 1. **Does equity need tracking per person?** If Sonu accrues a share of the
   principal, that is a balance-sheet item the app has no concept of. Probably
-   belongs in the net-worth view rather than here.
+   belongs in a net-worth view rather than here.
-
+2. **Attribution of forwarded payments.** `mummy` in the description reliably
 4. **Attribution of forwarded payments.** `mummy` in the description reliably
   marks Molina's money in all six known cases, but it is a description match on
-   a free-text field. Acceptable as a *suggestion* requiring confirmation; not as
+   a free-text field. Acceptable as a *suggestion* requiring confirmation, not
-   an automatic rule.
+   as an automatic rule.
 3. **The solo leg.** A Qantas booking on 23 Apr (txn 2849, $1,366.40) is the
   flight to Bangkok that begins a solo leg, and the Singapore spending
   ($2,242.05, 40 rows, to 9 May) is solo — not shared. It has no trip record.
   Worth one if trip *cost* is wanted for it; nothing about sharing depends on
   it.
-5. **Retroactive split ratios.** Applying today's household rules to 2025
+## Resolved
   assumes the arrangement has not changed. The SplitMyExpenses CSVs could give
   real historical shares, but transactions were sometimes combined, so matching
   is imperfect. Recommendation: use today's ratios, accept the approximation —
   the goal is a truer analytics picture, not a restated ledger.
---
+- **Europe — Sonu + Sunny** (trip 3, 2026-04-12 → 2026-04-28, 124 rows,
  $9,914.24). The leg after the group trip, previously marked only by tag 5 and
  invisible to trip analytics. Includes one advance booking on 17 Mar
  (Ticketmaster Nanterre) and the 12 Apr handover-day rows, which were already
  held out of Europe 2026.
-## What I would not do
+- **Grouped payments, split by scope.** Payments are made grouped — one transfer
  covers several tabs — and that needs no schema change, because
  `split_payments` has no unique constraint on `linked_transaction_id`. So one
  bank transfer carries one row per scope, and the rows re-add to the transfer.
- **Do not** restate history from the SplitMyExpenses CSVs. The combining problem
+  Sonu's two "transfer" payments were allocated Europe-first, remainder to
-  makes exact reconciliation impossible, and the value is low: those balances are
+  household, chronologically so each settles what was outstanding when it was
-  settled and will not change.
+  made:
- **Do not** make the loan a shared *expense*. It is a funded asset. Modelling it
+
-  as a recurring split would put $2,500 a fortnight of principal into spend,
+  | Transfer | Scope | Amount |
-  which is the error migration 0014 was written to prevent.
+  |---|---|---:|
  | $3,779.33, 12 Apr (txn 4111) | Europe 2026 | 1,084.61 |
  | | Europe — Sonu + Sunny | 2,694.72 |
  | $4,794.06, 16 May (txn 4121) | Europe — Sonu + Sunny | 1,145.52 |
  | | household | 3,648.54 |
  Both Europe tabs now read $0.00 and her overall balance is unchanged at
  $5,428.08 — allocation moves money between tabs, never between people. That
  invariance is the check worth repeating on any future re-allocation.
@@ -0,0 +1,37 @@
 -- A transaction imported twice cannot simply be deleted.
 --
 -- Every child of `transactions` is ON DELETE CASCADE — splits, tags, overrides,
 -- expense_metadata, order_reviews. Deleting a row said to be "the duplicate"
 -- therefore destroys whatever curation happens to sit on it, silently and
 -- unrecoverably. The curation is not reliably on the surviving side either: of
 -- the 31 known duplicate pairs, one carries splits and six carry overrides, and
 -- which member holds them is an accident of import order.
 --
 -- So a duplicate is superseded, never removed. The row stays, keeps its
 -- children, and points at the row that replaces it. Reversing a mistake is then
 -- one UPDATE rather than a restore from backup.
 --
 -- This is the statement-vs-statement case. `reconciled_with_id` already covers
 -- manual-vs-statement, and deliberately cannot be reused: the predicate that
 -- hides a reconciled row is scoped to `statement_id IS NULL`, because a
 -- statement line pointing at something else is the survivor, not the duplicate.
 -- Both of these rows are statement lines.
 ALTER TABLE transactions
  ADD COLUMN IF NOT EXISTS superseded_by_id integer
    REFERENCES transactions(id) ON DELETE SET NULL;
 COMMENT ON COLUMN transactions.superseded_by_id IS
  'This row was imported twice; the named row is the one that counts. Excluded from every figure, kept for its children and its audit trail. NULL = live.';
 CREATE INDEX IF NOT EXISTS idx_transactions_superseded
    ON transactions (superseded_by_id)
 WHERE superseded_by_id IS NOT NULL;
 -- A row cannot supersede itself, and a survivor cannot itself be superseded
 -- (that would hide both members of the pair and lose the amount entirely).
 ALTER TABLE transactions
  DROP CONSTRAINT IF EXISTS transactions_no_self_supersede;
 ALTER TABLE transactions
  ADD CONSTRAINT transactions_no_self_supersede
  CHECK (superseded_by_id IS NULL OR superseded_by_id <> id);
@@ -0,0 +1,52 @@
 -- 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');
@@ -42,6 +42,7 @@ model participants {
  account_owner_mappings account_owner_mappings[]
  payments_sent         split_payments[]         @relation("payments_from")
  payments_received     split_payments[]         @relation("payments_to")
  order_reviews         order_reviews[]
 }
 model account_owner_mappings {
@@ -183,13 +184,16 @@ model transactions {
  payment_method          String?       // card | cash | bank_transfer | other; NULL = unknown (migration 0016)
  owner_id                Int?
  reconciled_with_id      Int?
  superseded_by_id        Int?
  principal_amount        Decimal?      @db.Decimal(12, 2)
  interest_amount         Decimal?      @db.Decimal(12, 2)
  statement               statements?   @relation(fields: [statement_id], references: [id], onDelete: Cascade)
  reconciled_with         transactions?  @relation("reconciled", fields: [reconciled_with_id], references: [id], onDelete: SetNull)
  reconciled_by           transactions[] @relation("reconciled")
  superseded_by           transactions?  @relation("superseded", fields: [superseded_by_id], references: [id], onDelete: SetNull)
  supersedes              transactions[] @relation("superseded")
  expense_metadata        expense_metadata?
-  order_review            order_reviews?
+  order_reviews           order_reviews[]
 }
 model expense_metadata {
@@ -218,7 +222,8 @@ model expense_metadata {
 model order_reviews {
  id             Int          @id @default(autoincrement())
-  transaction_id Int          @unique
+  transaction_id Int
  participant_id Int
  rating         String?
  order_again    Boolean?
  note           String?
@@ -226,6 +231,9 @@ model order_reviews {
  created_at     DateTime     @default(now())
  updated_at     DateTime     @updatedAt
  transaction    transactions @relation(fields: [transaction_id], references: [id], onDelete: Cascade)
  participant    participants @relation(fields: [participant_id], references: [id], onDelete: Cascade)
  @@unique([transaction_id, participant_id], name: "order_reviews_transaction_participant_key")
 }
 model rule_apply_runs {
@@ -0,0 +1,376 @@
 #!/usr/bin/env python3
 """Match SplitMyExpenses CSV rows to transactions already in the ledger.
 Dry-run by default. It prints what it would do and writes nothing; `--write`
 is a separate step (task #9) and is deliberately not implemented here.
 Why this exists
 ---------------
 The CSVs are the record of how expenses were actually shared before this app
 existed. Importing them is what makes historical *spend* correct: without a
 split row, a $200 grocery shop counts as $200 of my spending when half of it
 was never mine. The balances are already settled by carryover transaction 2348,
 so these splits are imported as `settled = true` and move no balance.
 Three things make the matching harder than "same date, same amount":
 1.  **Dates are ambiguous across files.** The household file writes D/M/YYYY;
    the four trip files write ISO. 474 rows parse validly under both readings,
    so the format is decided per file, from the file, and never guessed per row.
 2.  **The sign convention is not "who paid".** A person's column is their net
    balance impact: positive means they are owed. So the payer is whoever is
    positive, and the other person's share is |their negative| / cost. A row
    reading +cost / -cost therefore means the other party owes 100% -- NOT that
    the expense was unshared, which is the reading that would fake an
    arrangement change.
 3.  **A settlement is not an expense.** Rows where one person hands the other
    money must not become split transactions; they are already represented by
    the carryover.
 Usage:
    .venv/bin/python scripts/split_csv_match.py [--verbose] [--file NAME]
 """
 from __future__ import annotations
 import argparse
 import csv
 import glob
 import os
 import re
 import sys
 from collections import Counter
 from dataclasses import dataclass, field
 from datetime import date, datetime, timedelta
 import psycopg2
 import psycopg2.extras
 DUMP_DIR = os.path.join(os.path.dirname(os.path.dirname(os.path.abspath(__file__))), "dump")
 # Read from the same place the app does rather than hardcoding a container IP,
 # which changes on every recreate.
 def db_url() -> str:
    url = os.environ.get("DATABASE_URL")
    if url:
        return url
    for envfile in (".env", ".env.test"):
        path = os.path.join(os.path.dirname(DUMP_DIR), envfile)
        if not os.path.exists(path):
            continue
        for line in open(path):
            if line.startswith("DATABASE_URL"):
                return line.split("=", 1)[1].strip().strip('"').strip("'")
    sys.exit("No DATABASE_URL found (env, .env, or .env.test)")
 # The two people in these files. The CSV writes full names; the ledger uses
 # first names.
 CSV_ME = "Siddharth Bose"
 CSV_THEM = "Meghalee"
 PARTICIPANT_ME = 1
 PARTICIPANT_THEM = 4
 # A settlement transfers money; it is not a shared cost. These are the
 # descriptions SplitMyExpenses uses for them.
 SETTLEMENT_PAT = re.compile(
    r"payment|settle|debts? remainder|reimburse|transfer to|paid back", re.I
 )
@dataclass
 class CsvRow:
    source: str
    line: int
    when: date
    description: str
    category: str
    cost: float
    currency: str
    net_me: float
    net_them: float
    # Filled in by classify()
    mode: str = ""
    payer: int = 0            # participant id who paid
    ower: int = 0             # participant id who owes
    ower_share: float = 0.0   # 0-100
    def __str__(self) -> str:
        return f"{self.source}:{self.line} {self.when} {self.description[:38]!r} ${self.cost:.2f} [{self.mode}]"
@dataclass
 class MatchReport:
    rows: list = field(default_factory=list)
    matched: list = field(default_factory=list)
    ambiguous: list = field(default_factory=list)
    unmatched: list = field(default_factory=list)
    skipped: list = field(default_factory=list)
 def sniff_date_format(sample: list[str]) -> str:
    """Decide ISO vs D/M/YYYY for a whole file.
    Deciding per row is what produces a ledger where January and February are
    silently swapped for some rows and not others. A file is written by one
    exporter in one format, so the file is the unit of decision.
    """
    if not sample:
        return "%Y-%m-%d"
    slashes = sum(1 for s in sample if "/" in s)
    return "%d/%m/%Y" if slashes > len(sample) / 2 else "%Y-%m-%d"
 def parse_rows(path: str) -> list[CsvRow]:
    with open(path, newline="", encoding="utf-8-sig") as fh:
        reader = list(csv.DictReader(fh))
    if not reader:
        return []
    # Header names vary in quoting between exports.
    def col(row: dict, *names: str):
        for n in names:
            for k in row:
                if k.strip().strip('"') == n:
                    return row[k]
        return None
    fmt = sniff_date_format([col(r, "Date") or "" for r in reader[:40]])
    out: list[CsvRow] = []
    for i, r in enumerate(reader, start=2):
        raw_date = (col(r, "Date") or "").strip()
        try:
            when = datetime.strptime(raw_date, fmt).date()
        except ValueError:
            continue
        try:
            cost = float(col(r, "Cost") or 0)
            net_me = float(col(r, CSV_ME) or 0)
            net_them = float(col(r, CSV_THEM) or 0)
        except ValueError:
            continue
        out.append(
            CsvRow(
                source=os.path.basename(path),
                line=i,
                when=when,
                description=(col(r, "Description") or "").strip(),
                category=(col(r, "Category") or "").strip(),
                cost=cost,
                currency=(col(r, "Currency") or "AUD").strip(),
                net_me=net_me,
                net_them=net_them,
            )
        )
    return out
 def classify(row: CsvRow) -> CsvRow:
    """Work out who paid and what share the other person owes.
    A person's column is their net balance impact, not their share: positive
    means they are owed money. So the payer is whoever is positive.
    """
    if row.cost == 0:
        row.mode = "zero-cost"
        return row
    if SETTLEMENT_PAT.search(row.description):
        row.mode = "settlement"
        return row
    # Both zero against a real cost: recorded but not shared.
    if abs(row.net_me) < 0.005 and abs(row.net_them) < 0.005:
        row.mode = "unshared"
        return row
    if row.net_me > 0:
        row.payer, row.ower, owed = PARTICIPANT_ME, PARTICIPANT_THEM, abs(row.net_them)
    else:
        row.payer, row.ower, owed = PARTICIPANT_THEM, PARTICIPANT_ME, abs(row.net_me)
    row.ower_share = round(owed / row.cost * 100, 2)
    if abs(row.ower_share - 50) < 0.6:
        row.mode = "50/50"
    elif abs(row.ower_share - 100) < 0.6:
        row.mode = "other-owes-all"
    else:
        row.mode = f"uneven-{row.ower_share:.0f}"
    return row
 def norm(s: str) -> set[str]:
    return {w for w in re.split(r"[^a-z0-9]+", (s or "").lower()) if len(w) > 2}
 def score(row: CsvRow, tx: dict) -> float:
    """How well a ledger row matches a CSV row. Amount and date gate it;
    description only ranks among survivors."""
    days = abs((tx["transaction_date"] - row.when).days)
    s = 100.0 - days * 4
    overlap = norm(row.description) & (norm(tx["description"]) | norm(tx["merchant_normalized"]))
    s += 12 * len(overlap)
    return s
 def main() -> int:
    ap = argparse.ArgumentParser()
    ap.add_argument("--verbose", action="store_true")
    ap.add_argument("--file", help="only this CSV (substring match)")
    ap.add_argument("--window", type=int, default=5, help="date tolerance in days")
    ap.add_argument(
        "--write", action="store_true",
        help="actually insert the splits (settled=true). Without this, nothing is written.",
    )
    args = ap.parse_args()
    paths = sorted(glob.glob(os.path.join(DUMP_DIR, "*SplitMyExpenses*.csv")))
    if args.file:
        paths = [p for p in paths if args.file in os.path.basename(p)]
    if not paths:
        sys.exit("No SplitMyExpenses CSVs found in dump/")
    conn = psycopg2.connect(db_url())
    cur = conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
    # Superseded rows are duplicates; matching against them would attach a split
    # to a row nothing else counts.
    cur.execute(
        """
        SELECT t.id, t.transaction_date, COALESCE(t.description,'') AS description,
               COALESCE(t.merchant_normalized,'') AS merchant_normalized,
               COALESCE(t.amount_aud, t.amount)::float AS amount,
               t.superseded_by_id,
               EXISTS(SELECT 1 FROM transaction_splits x WHERE x.transaction_id=t.id) AS has_split
        FROM transactions t
        LEFT JOIN statements s ON s.id = t.statement_id
        WHERE COALESCE(t.owner_id, s.owner_id) IN (%s, %s)
          AND t.superseded_by_id IS NULL
          AND NOT (t.statement_id IS NULL AND t.reconciled_with_id IS NOT NULL)
          AND t.transaction_type IN ('debit','fee','interest')
        """,
        (PARTICIPANT_ME, PARTICIPANT_THEM),
    )
    txs = cur.fetchall()
    # Index by rounded amount: the amount must agree, so it is the only cheap
    # gate that never needs fuzzy comparison.
    by_amount: dict[float, list[dict]] = {}
    for t in txs:
        by_amount.setdefault(round(t["amount"], 2), []).append(t)
    rep = MatchReport()
    modes: Counter = Counter()
    candidates: list = []
    for path in paths:
        for row in parse_rows(path):
            classify(row)
            rep.rows.append(row)
            modes[row.mode] += 1
            if row.mode in ("settlement", "zero-cost", "unshared"):
                rep.skipped.append(row)
                continue
            if row.currency != "AUD":
                rep.skipped.append(row)
                continue
            cands = [
                t for t in by_amount.get(round(row.cost, 2), [])
                if abs((t["transaction_date"] - row.when).days) <= args.window
            ]
            if not cands:
                rep.unmatched.append(row)
            else:
                candidates.append((row, cands))
    # Assign one-to-one, best pair first.
    #
    # Without this a ledger row can be claimed by several CSV rows. That is not
    # hypothetical: the NZ trip has two identical $10.16 Uber trips on one day
    # and three matching ledger rows, and four PayMyPark rows in the same shape.
    # Attaching a split twice is harmless (the unique key absorbs it) but it
    # leaves the second CSV row silently unrepresented while looking matched,
    # which is a lie in the report rather than a defect in the data.
    scored = sorted(
        ((score(row, t), row, t) for row, cands in candidates for t in cands),
        key=lambda x: -x[0],
    )
    taken_tx: set[int] = set()
    taken_row: set[int] = set()
    for s, row, t in scored:
        if id(row) in taken_row or t["id"] in taken_tx:
            continue
        taken_row.add(id(row))
        taken_tx.add(t["id"])
        rep.matched.append((row, t))
    for row, cands in candidates:
        if id(row) not in taken_row:
            rep.ambiguous.append((row, cands))
    total = len(rep.rows)
    considered = total - len(rep.skipped)
    print(f"CSV rows              {total}")
    print(f"  skipped             {len(rep.skipped)}  (settlements, zero-cost, unshared, non-AUD)")
    print(f"  considered          {considered}")
    print(f"    matched           {len(rep.matched)}  ({len(rep.matched)/max(considered,1)*100:.1f}%)")
    print(f"    ambiguous         {len(rep.ambiguous)}")
    print(f"    unmatched         {len(rep.unmatched)}")
    print()
    print("Row modes:")
    for m, n in modes.most_common():
        print(f"  {m:<18} {n}")
    already = sum(1 for _, t in rep.matched if t["has_split"])
    print()
    print(f"Of the matched, {already} already carry a split and would be left alone;")
    print(f"{len(rep.matched) - already} would gain one.")
    if args.write:
        # Imported as settled: these obligations were discharged on a platform
        # we no longer run, and the residual is already carried by transaction
        # 2348. Writing them unsettled would re-open ~$40k of debts that were
        # paid years ago. See ACTIVE_OBLIGATION in analytics-sql.ts -- settled
        # rows stay in spend and leave every owed figure, which is exactly the
        # point: this import exists to correct historical SPEND.
        SETTLED_ON = "2026-01-09"  # the carryover's date
        written = 0
        for row, tx in rep.matched:
            if tx["has_split"]:
                continue
            payer_share = round(100 - row.ower_share, 2)
            pairs = [(row.ower, row.ower_share)]
            if payer_share > 0:
                pairs.append((row.payer, payer_share))
            for pid, share in pairs:
                cur.execute(
                    """
                    INSERT INTO transaction_splits
                      (transaction_id, participant_id, share_percent, settled, settled_at)
                    VALUES (%s, %s, %s, true, %s)
                    ON CONFLICT (transaction_id, participant_id) DO NOTHING
                    """,
                    (tx["id"], pid, share, SETTLED_ON),
                )
                written += cur.rowcount
        conn.commit()
        print(f"\nWROTE {written} split rows (settled=true, settled_at={SETTLED_ON}).")
    if args.verbose:
        print("\n--- ambiguous ---")
        for row, cands in rep.ambiguous[:40]:
            print(f"  {row}")
            for t in cands[:3]:
                print(f"      -> #{t['id']} {t['transaction_date']} {t['description'][:44]!r}")
        print("\n--- unmatched ---")
        for row in rep.unmatched[:60]:
            print(f"  {row}")
    conn.close()
    return 0
 if __name__ == "__main__":
    raise SystemExit(main())
@@ -16,6 +16,13 @@ export function mockDbWithPool(p: Pool) {
      const result = await p.query(sql, params);
      return result.rows;
    },
    // Mirrors the real module: a mock that omits an export makes it `undefined`
    // at the call site, so any route using queryRow fails with a confusing
    // "not a function" rather than a query error.
    queryRow: async (sql: string, params: unknown[] = []) => {
      const result = await p.query(sql, params);
      return result.rows[0] ?? null;
    },
    prisma: p,
  }));
 }
@@ -73,7 +80,10 @@ export async function insertTransaction(
     VALUES ($1, NULL, $2, $3, $4, $5, $6, 0) RETURNING id`,
    [
      ownerId,
-      overrides.transaction_date ?? "2024-06-15",
+      // Post-cutover by default: a split on an older transaction never counts
      // towards a balance (ACTIVE_OBLIGATION), so a pre-cutover default would
      // make every balance fixture silently read zero.
      overrides.transaction_date ?? "2026-06-15",
      overrides.description ?? "Test transaction",
      overrides.amount ?? 100,
      overrides.transaction_type ?? "debit",
@@ -0,0 +1,208 @@
 import { describe, it, expect, beforeAll, beforeEach, vi } from "vitest";
 import { createPool, mockDbWithPool, resetDB } from "./helpers";
 /**
 * Verdicts on orders — the "never order from here again" memory (ING-9).
 *
 * These cover the two things that are easy to get silently wrong and invisible
 * on screen when you do: a second person's verdict overwriting the first, and a
 * note-only save wiping the per-item opinions. Both are the same shape as the
 * bug that reset `settled` on split rewrites.
 */
 const pool = createPool();
 mockDbWithPool(pool);
 let GET: any;
 let PUT: any;
 let ownerId: number;
 let otherId: number;
 let txnId: number;
 const req = (body?: unknown) =>
  ({ headers: { get: () => null }, json: async () => body }) as any;
 const params = (id: number) => ({ params: Promise.resolve({ id: String(id) }) });
 beforeAll(async () => {
  vi.doMock("@/lib/auth", () => ({
    getCurrentUser: async () => ({ id: ownerId, name: "Owner", email: "o@x" }),
  }));
  vi.doMock("@/lib/queries", () => ({ canAccessTransactions: async () => true }));
  ({ GET, PUT } = await import("../../app/api/transactions/[id]/review/route"));
 });
 /** An ingested order: a transaction plus the expense_metadata behind it. */
 async function seedOrder(merchant: string, date = "2026-07-01") {
  const t = await pool.query(
    `INSERT INTO transactions (transaction_date, description, amount, transaction_type, merchant_name, owner_id)
     VALUES ($1, $2, 48.20, 'debit', $3, $4) RETURNING id`,
    [date, `DoorDash ${merchant}`, merchant, ownerId]
  );
  const id = t.rows[0].id as number;
  await pool.query(
    `INSERT INTO expense_metadata (transaction_id, source, order_reference, merchant_normalized, line_items)
     VALUES ($1, 'email', $2, $3, '[]'::jsonb)`,
    [id, `ref-${id}`, merchant]
  );
  return id;
 }
 beforeEach(async () => {
  await resetDB(pool);
  const a = await pool.query(`INSERT INTO participants (name) VALUES ('Owner') RETURNING id`);
  const b = await pool.query(`INSERT INTO participants (name) VALUES ('Other') RETURNING id`);
  ownerId = a.rows[0].id;
  otherId = b.rows[0].id;
  txnId = await seedOrder("Thai Palace");
 });
 describe("order verdicts — one per person", () => {
  it("keeps both people's verdicts on the same order", async () => {
    await PUT(req({ participant_id: ownerId, rating: "loved" }), params(txnId));
    const res = await PUT(req({ participant_id: otherId, rating: "never" }), params(txnId));
    const body = await res.json();
    // The bug this guards: a UNIQUE on transaction_id alone made the second
    // save overwrite the first, and the disagreement is the useful part.
    expect(body.reviews).toHaveLength(2);
    expect(body.reviews.find((r: any) => r.participant_id === ownerId).rating).toBe("loved");
    expect(body.reviews.find((r: any) => r.participant_id === otherId).rating).toBe("never");
  });
  it("defaults the verdict to the signed-in user, not the owner", async () => {
    const res = await PUT(req({ rating: "ok" }), params(txnId));
    const body = await res.json();
    expect(body.reviews[0].participant_id).toBe(ownerId);
  });
  it("revising a verdict updates rather than duplicating", async () => {
    await PUT(req({ participant_id: ownerId, rating: "loved" }), params(txnId));
    const res = await PUT(req({ participant_id: ownerId, rating: "never" }), params(txnId));
    const body = await res.json();
    expect(body.reviews).toHaveLength(1);
    expect(body.reviews[0].rating).toBe("never");
  });
  it("rejects a rating outside the scale", async () => {
    const res = await PUT(req({ rating: "amazing" }), params(txnId));
    expect(res.status).toBe(400);
  });
  it("derives order_again from the rating", async () => {
    let body = await (await PUT(req({ rating: "never" }), params(txnId))).json();
    expect(body.reviews[0].order_again).toBe(false);
    body = await (await PUT(req({ rating: "ok" }), params(txnId))).json();
    expect(body.reviews[0].order_again).toBe(true);
  });
 });
 describe("item verdicts", () => {
  it("a note-only save does not wipe item opinions", async () => {
    await PUT(
      req({
        rating: "liked",
        item_verdicts: [{ item: "Pad Thai", verdict: "loved" }],
      }),
      params(txnId)
    );
    // No item_verdicts key at all — the shape a note-only form sends.
    const res = await PUT(req({ rating: "liked", note: "slow delivery" }), params(txnId));
    const body = await res.json();
    expect(body.reviews[0].item_verdicts).toEqual([
      { item: "Pad Thai", verdict: "loved" },
    ]);
    expect(body.reviews[0].note).toBe("slow delivery");
  });
  it("an explicit empty array does clear them", async () => {
    await PUT(
      req({ rating: "liked", item_verdicts: [{ item: "Pad Thai", verdict: "loved" }] }),
      params(txnId)
    );
    const res = await PUT(req({ rating: "liked", item_verdicts: [] }), params(txnId));
    const body = await res.json();
    expect(body.reviews[0].item_verdicts).toEqual([]);
  });
  it("drops malformed entries without failing the save", async () => {
    const res = await PUT(
      req({
        rating: "ok",
        item_verdicts: [
          { item: "Pad Thai", verdict: "loved" },
          { item: "", verdict: "loved" },
          { item: "Curry", verdict: "middling" },
        ],
      }),
      params(txnId)
    );
    const body = await res.json();
    expect(body.reviews[0].rating).toBe("ok");
    expect(body.reviews[0].item_verdicts).toEqual([
      { item: "Pad Thai", verdict: "loved" },
    ]);
  });
 });
 describe("merchant history", () => {
  it("warns when the merchant was ever marked never, and excludes this order", async () => {
    const older = await seedOrder("Thai Palace", "2026-06-01");
    await PUT(req({ participant_id: ownerId, rating: "never", note: "cold" }), params(older));
    const body = await (await GET(req(), params(txnId))).json();
    expect(body.merchant.warn).toBe(true);
    expect(body.merchant.counts.never).toBe(1);
    expect(body.merchant.history.map((h: any) => h.transaction_id)).toEqual([older]);
  });
  it("does not carry a verdict across different merchants", async () => {
    const other = await seedOrder("Pizza Place", "2026-06-01");
    await PUT(req({ participant_id: ownerId, rating: "never" }), params(other));
    const body = await (await GET(req(), params(txnId))).json();
    expect(body.merchant.warn).toBe(false);
  });
  it("pools item opinions across the merchant's orders", async () => {
    const older = await seedOrder("Thai Palace", "2026-06-01");
    await PUT(
      req({ item_verdicts: [{ item: "Pad Thai", verdict: "loved" }] }),
      params(older)
    );
    const older2 = await seedOrder("Thai Palace", "2026-05-01");
    await PUT(
      req({ item_verdicts: [{ item: "pad thai", verdict: "loved" }] }),
      params(older2)
    );
    const body = await (await GET(req(), params(txnId))).json();
    // Case-folded: the same dish comes back capitalised differently between
    // receipts, and two entries for one dish is not a track record.
    expect(body.merchant.items).toEqual([{ item: "Pad Thai", loved: 2, never: 0 }]);
  });
  it("keeps item opinions from reviews that have no overall rating", async () => {
    const older = await seedOrder("Thai Palace", "2026-06-01");
    await PUT(
      req({ item_verdicts: [{ item: "Satay", verdict: "never" }] }),
      params(older)
    );
    const body = await (await GET(req(), params(txnId))).json();
    expect(body.merchant.items).toEqual([{ item: "Satay", loved: 0, never: 1 }]);
  });
 });
 describe("share state", () => {
  it("reports splits so the panel can show shared vs just me", async () => {
    let body = await (await GET(req(), params(txnId))).json();
    expect(body.splits).toEqual([]);
    await pool.query(
      `INSERT INTO transaction_splits (transaction_id, participant_id, share_percent)
       VALUES ($1, $2, 50)`,
      [txnId, otherId]
    );
    body = await (await GET(req(), params(txnId))).json();
    expect(body.splits).toHaveLength(1);
    expect(body.splits[0].participant_id).toBe(otherId);
  });
 });
@@ -8,7 +8,7 @@ mockDbWithPool(pool);
 // Dynamic import AFTER the mock ensures getTransactions / getParticipantBalances
 // use the test pool rather than Prisma's singleton.
-const { getTransactions, getParticipantBalances, getTripAnalytics } = await import("@/lib/queries");
+const { getTransactions, getParticipantBalances, getTripAnalytics, getTripById, getStatements } = await import("@/lib/queries");
 beforeEach(async () => {
  await resetDB(pool);
@@ -411,3 +411,313 @@ describe("getTripAnalytics — per-trip settlement", () => {
    expect(bob === undefined || Number(bob.owed) === 0).toBe(true);
  });
 });
 describe("getTripAnalytics — owner scoping", () => {
  it("ignores a trip expense someone else paid for", async () => {
    const { ownerId, otherId } = await seedParticipants(pool);
    const trip = await pool.query(
      `INSERT INTO trips (owner_id, name) VALUES ($1, 'Owner Scope Trip') RETURNING id`,
      [ownerId]
    );
    const tripId = trip.rows[0].id as number;
    // Bob paid this one. Alice's share of it is a debt Alice owes Bob — it is
    // not something Bob owes Alice, so it must not appear on Alice's trip view.
    const bobPaid = await insertTransaction(pool, otherId, { amount: 500, category: "travel" });
    await pool.query(`INSERT INTO transaction_overrides (transaction_id, trip_id) VALUES ($1, $2)`, [bobPaid, tripId]);
    await pool.query(
      `INSERT INTO transaction_splits (transaction_id, participant_id, share_percent)
       VALUES ($1, $2, 50), ($1, $3, 50)`,
      [bobPaid, ownerId, otherId]
    );
    const { participant_splits } = await getTripAnalytics(tripId, ownerId);
    const bob = participant_splits.find((r) => r.participant_id === otherId);
    expect(bob === undefined || Number(bob.owed) === 0).toBe(true);
  });
  it("ignores a payment settled between the other two participants", async () => {
    const { ownerId, otherId } = await seedParticipants(pool);
    const third = await pool.query(
      `INSERT INTO participants (name, email) VALUES ('Carol', 'carol@example.com') RETURNING id`
    );
    const carolId = third.rows[0].id as number;
    const trip = await pool.query(
      `INSERT INTO trips (owner_id, name) VALUES ($1, 'Third Party Trip') RETURNING id`,
      [ownerId]
    );
    const tripId = trip.rows[0].id as number;
    const txId = await insertTransaction(pool, ownerId, { amount: 300, category: "travel" });
    await pool.query(`INSERT INTO transaction_overrides (transaction_id, trip_id) VALUES ($1, $2)`, [txId, tripId]);
    await pool.query(
      `INSERT INTO transaction_splits (transaction_id, participant_id, share_percent) VALUES ($1, $2, 50)`,
      [txId, carolId]
    );
    // Carol pays Bob, not the owner. Carol still owes the owner $150.
    await pool.query(
      `INSERT INTO split_payments (from_participant_id, to_participant_id, amount, payment_date, trip_id)
       VALUES ($1, $2, 150, '2026-03-15', $3)`,
      [carolId, otherId, tripId]
    );
    const { participant_splits } = await getTripAnalytics(tripId, ownerId);
    const carol = participant_splits.find((r) => r.participant_id === carolId);
    expect(Number(carol!.owed)).toBeCloseTo(150);
  });
 });
 // A refunded trip expense must not still read as trip cost. These queries
 // filtered on debit/fee/interest, so a refund was dropped entirely and the
 // original purchase stood at full value.
 describe("getTripAnalytics — refunds reduce trip cost", () => {
  async function seedTripWithRefund(ownerId: number) {
    const trip = await pool.query(
      `INSERT INTO trips (owner_id, name, start_date, end_date)
       VALUES ($1, 'Refund Trip', '2026-03-01', '2026-03-10') RETURNING id`,
      [ownerId]
    );
    const tripId = trip.rows[0].id as number;
    const spend = await insertTransaction(pool, ownerId, {
      amount: 200, category: "travel", description: "Hotel booking", transaction_date: "2026-03-02",
    });
    const refund = await insertTransaction(pool, ownerId, {
      amount: 50, category: "travel", description: "Hotel partial refund",
      transaction_type: "refund", transaction_date: "2026-03-05",
    });
    await pool.query(
      `INSERT INTO transaction_overrides (transaction_id, trip_id) VALUES ($1, $3), ($2, $3)`,
      [spend, refund, tripId]
    );
    return tripId;
  }
  it("nets the refund out of the category total", async () => {
    const { ownerId } = await seedParticipants(pool);
    const tripId = await seedTripWithRefund(ownerId);
    const { category_breakdown } = await getTripAnalytics(tripId, ownerId);
    const travel = category_breakdown.find((c) => c.category === "travel");
    expect(Number(travel!.amount)).toBeCloseTo(150);
  });
  it("nets the refund out of the trip's headline total_spend", async () => {
    const { ownerId } = await seedParticipants(pool);
    const tripId = await seedTripWithRefund(ownerId);
    const trip = await getTripById(tripId, ownerId);
    expect(Number(trip!.total_spend)).toBeCloseTo(150);
  });
  it("shows the refund as a negative on its own day", async () => {
    const { ownerId } = await seedParticipants(pool);
    const tripId = await seedTripWithRefund(ownerId);
    const { daily_spend } = await getTripAnalytics(tripId, ownerId);
    const refundDay = daily_spend.find((d) => d.date === "2026-03-05");
    expect(Number(refundDay!.amount)).toBeCloseTo(-50);
  });
  // The owed side must be untouched: a refund carries no split, and the owed
  // query deliberately excludes credits. Netting cost must not move a balance.
  it("leaves what the other participant owes unchanged", async () => {
    const { ownerId, otherId } = await seedParticipants(pool);
    const tripId = await seedTripWithRefund(ownerId);
    const rows = await pool.query(
      `SELECT transaction_id FROM transaction_overrides WHERE trip_id = $1 ORDER BY transaction_id`,
      [tripId]
    );
    await pool.query(
      `INSERT INTO transaction_splits (transaction_id, participant_id, share_percent) VALUES ($1, $2, 50)`,
      [rows.rows[0].transaction_id, otherId]
    );
    const { participant_splits } = await getTripAnalytics(tripId, ownerId);
    const bob = participant_splits.find((r) => r.participant_id === otherId);
    expect(Number(bob!.owed)).toBeCloseTo(100);
  });
 });
 // An account cannot be billed twice for the same day. The boundary handling is
 // the whole difficulty: these statements are issued back-to-back with one
 // period ending the day the next begins, so naive inclusive ranges flag every
 // consecutive pair.
 describe("getStatements — overlapping billing periods", () => {
  async function addStatement(
    ownerId: number, account: string, start: string | null, end: string | null
  ): Promise<number> {
    const r = await pool.query(
      `INSERT INTO statements (filename, bank_name, account_number, owner_id,
                               billing_start_date, billing_end_date)
       VALUES ($1, 'ANZ', $2, $3, $4, $5) RETURNING id`,
      [`stmt-${account}-${start}.pdf`, account, ownerId, start, end]
    );
    return r.rows[0].id as number;
  }
  it("does not flag statements that merely touch at a boundary", async () => {
    const { ownerId } = await seedParticipants(pool);
    await addStatement(ownerId, "4085-56264", "2025-05-16", "2025-11-14");
    await addStatement(ownerId, "4085-56264", "2025-11-14", "2026-05-15");
    const rows = await getStatements(ownerId);
    expect(rows.every((r) => r.overlaps.length === 0)).toBe(true);
  });
  it("flags a genuine overlap on both statements, with the day count", async () => {
    const { ownerId } = await seedParticipants(pool);
    const a = await addStatement(ownerId, "4085-56264", "2025-11-12", "2026-03-12");
    const b = await addStatement(ownerId, "4085-56264", "2025-11-14", "2026-05-15");
    const rows = await getStatements(ownerId);
    const rowA = rows.find((r) => r.id === a)!;
    const rowB = rows.find((r) => r.id === b)!;
    expect(rowA.overlaps).toEqual([{ id: b, days: 118 }]);
    expect(rowB.overlaps).toEqual([{ id: a, days: 118 }]);
  });
  // The real duplicate got in because the existing key compared raw text and
  // ANZ wrote the same account both ways.
  it("matches the same account written with and without punctuation", async () => {
    const { ownerId } = await seedParticipants(pool);
    const a = await addStatement(ownerId, "408556264", "2025-11-12", "2026-03-12");
    const b = await addStatement(ownerId, "4085-56264", "2025-11-14", "2026-05-15");
    const rows = await getStatements(ownerId);
    expect(rows.find((r) => r.id === a)!.overlaps).toEqual([{ id: b, days: 118 }]);
  });
  it("ignores a different account billing the same days", async () => {
    const { ownerId } = await seedParticipants(pool);
    await addStatement(ownerId, "4085-56264", "2025-11-12", "2026-03-12");
    await addStatement(ownerId, "9999-11111", "2025-11-12", "2026-03-12");
    const rows = await getStatements(ownerId);
    expect(rows.every((r) => r.overlaps.length === 0)).toBe(true);
  });
  // NULL is unbounded to daterange, which would make an undated statement
  // overlap the entire history.
  it("does not treat an undated statement as overlapping everything", async () => {
    const { ownerId } = await seedParticipants(pool);
    await addStatement(ownerId, "4085-56264", "2025-11-12", "2026-03-12");
    await addStatement(ownerId, "4085-56264", null, null);
    const rows = await getStatements(ownerId);
    expect(rows.every((r) => r.overlaps.length === 0)).toBe(true);
  });
 });
 // A statement imported twice puts every transaction in the overlap in the
 // ledger twice. The duplicate is superseded rather than deleted, because every
 // child of `transactions` cascades on delete.
 describe("superseded duplicates are excluded but kept", () => {
  it("hides a superseded row from the transaction list", async () => {
    const { ownerId } = await seedParticipants(pool);
    const keep = await insertTransaction(pool, ownerId, { description: "RAIZ INVESTMENT", amount: 1500 });
    const dup = await insertTransaction(pool, ownerId, { description: "RAIZ INVESTMENT", amount: 1500 });
    await pool.query(`UPDATE transactions SET superseded_by_id = $1 WHERE id = $2`, [keep, dup]);
    const { data, total } = await getTransactions(ownerId, { limit: 50, offset: 0 });
    expect(total).toBe(1);
    expect(data.map((t) => t.id)).toEqual([keep]);
  });
  it("keeps the superseded row and its children in the database", async () => {
    const { ownerId, otherId } = await seedParticipants(pool);
    const keep = await insertTransaction(pool, ownerId, { amount: 100 });
    const dup = await insertTransaction(pool, ownerId, { amount: 100 });
    await pool.query(
      `INSERT INTO transaction_splits (transaction_id, participant_id, share_percent) VALUES ($1, $2, 50)`,
      [dup, otherId]
    );
    await pool.query(`UPDATE transactions SET superseded_by_id = $1 WHERE id = $2`, [keep, dup]);
    const rows = await pool.query(`SELECT superseded_by_id FROM transactions WHERE id = $1`, [dup]);
    expect(rows.rows[0].superseded_by_id).toBe(keep);
    const kids = await pool.query(`SELECT count(*)::int AS n FROM transaction_splits WHERE transaction_id = $1`, [dup]);
    expect(kids.rows[0].n).toBe(1);
  });
  // The point of excluding it: a split on a duplicate must not be owed twice.
  it("does not count a superseded row towards what someone owes", async () => {
    const { ownerId, otherId } = await seedParticipants(pool);
    const keep = await insertTransaction(pool, ownerId, { amount: 100 });
    const dup = await insertTransaction(pool, ownerId, { amount: 100 });
    for (const id of [keep, dup]) {
      await pool.query(
        `INSERT INTO transaction_splits (transaction_id, participant_id, share_percent) VALUES ($1, $2, 50)`,
        [id, otherId]
      );
    }
    await pool.query(`UPDATE transactions SET superseded_by_id = $1 WHERE id = $2`, [keep, dup]);
    const balances = await getParticipantBalances(ownerId);
    const bob = balances.find((b) => b.id === otherId);
    expect(Number(bob!.total_owed)).toBeCloseTo(50);
  });
  it("refuses to let a row supersede itself", async () => {
    const { ownerId } = await seedParticipants(pool);
    const id = await insertTransaction(pool, ownerId);
    await expect(
      pool.query(`UPDATE transactions SET superseded_by_id = $1 WHERE id = $1`, [id])
    ).rejects.toThrow();
  });
 });
 // Nothing before the cutover can be owed: carryover transaction 2348 already
 // carries the entire pre-cutover balance as one figure. Splits on older
 // transactions exist to describe how an expense was shared -- which keeps it
 // out of spend -- without asserting a debt.
 describe("the split cutover gates every balance", () => {
  it("ignores a split on a transaction before the cutover", async () => {
    const { ownerId, otherId } = await seedParticipants(pool);
    const txId = await insertTransaction(pool, ownerId, {
      amount: 100, transaction_date: "2026-01-08",
    });
    await pool.query(
      `INSERT INTO transaction_splits (transaction_id, participant_id, share_percent) VALUES ($1, $2, 50)`,
      [txId, otherId]
    );
    const balances = await getParticipantBalances(ownerId);
    const bob = balances.find((b) => b.id === otherId);
    expect(Number(bob?.total_owed ?? 0)).toBeCloseTo(0);
  });
  // Inclusive: transaction 2348, which carries the whole pre-cutover balance,
  // is itself dated 2026-01-09. An exclusive bound would drop it.
  it("counts a split dated exactly on the cutover", async () => {
    const { ownerId, otherId } = await seedParticipants(pool);
    const txId = await insertTransaction(pool, ownerId, {
      amount: 100, transaction_date: "2026-01-09",
    });
    await pool.query(
      `INSERT INTO transaction_splits (transaction_id, participant_id, share_percent) VALUES ($1, $2, 50)`,
      [txId, otherId]
    );
    const balances = await getParticipantBalances(ownerId);
    const bob = balances.find((b) => b.id === otherId);
    expect(Number(bob!.total_owed)).toBeCloseTo(50);
  });
  // The point of the date guard: it does not depend on `settled` surviving.
  it("still ignores a pre-cutover split whose settled flag was lost", async () => {
    const { ownerId, otherId } = await seedParticipants(pool);
    const txId = await insertTransaction(pool, ownerId, {
      amount: 200, transaction_date: "2025-06-01",
    });
    await pool.query(
      `INSERT INTO transaction_splits (transaction_id, participant_id, share_percent, settled)
       VALUES ($1, $2, 50, false)`,
      [txId, otherId]
    );
    const balances = await getParticipantBalances(ownerId);
    const bob = balances.find((b) => b.id === otherId);
    expect(Number(bob?.total_owed ?? 0)).toBeCloseTo(0);
  });
 });
@@ -11,6 +11,7 @@ import {
  NotAReceiptError,
  type MessageMeta,
 } from "@/lib/order-ingestion";
 import { merchantVerdict } from "@/lib/order-reviews";
 /**
 * Machine ingest endpoint for order receipts.
@@ -77,6 +78,14 @@ export async function POST(req: NextRequest) {
      subject: meta.subject,
      sender: meta.sender,
    });
    // What we said about this merchant before, so the Slack nudge can warn at
    // the moment the order lands rather than waiting for someone to open the
    // app. `result.transactionId` is excluded because a brand-new order has no
    // verdict yet — anything found is genuinely a previous visit.
    const verdict = result.skipped
      ? null
      : await merchantVerdict(order.merchant_name, result.transactionId);
    return NextResponse.json({
      kind: "order",
      order_reference: order.order_reference,
@@ -85,6 +94,11 @@ export async function POST(req: NextRequest) {
      currency: order.currency,
      is_family: order.is_family,
      ...result,
      prior_verdict: verdict && {
        warn: verdict.warn,
        counts: verdict.counts,
        last_note: verdict.history.find((h) => h.note)?.note ?? null,
      },
    });
  } catch (e) {
    // Not a receipt: promotions, delivery updates, adjustment and refund
@@ -0,0 +1,186 @@
 import { NextRequest, NextResponse } from "next/server";
 import { queryRaw, queryRow } from "@/lib/db";
 import { getCurrentUser } from "@/lib/auth";
 import { canAccessTransactions } from "@/lib/queries";
 import {
  ITEM_VERDICTS,
  RATINGS,
  merchantForTransaction,
  merchantVerdict,
  type ItemOpinion,
  type OrderReview,
  type Rating,
 } from "@/lib/order-reviews";
 /**
 * Verdicts on one delivery order, plus what was said about this merchant
 * before.
 *
 * Both halves come back together on purpose: the panel is useless without the
 * history — the whole reason to open it is to see whether this place has
 * disappointed us before. Two round trips would let it render the form first
 * and the warning second, which is the order that lets you re-order by
 * mistake.
 *
 * `reviews` is a list, not one row. A shared meal has two opinions and they
 * routinely disagree; collapsing them to one would keep whichever was saved
 * last and silently discard the other person's.
 */
 async function authorise(req: NextRequest, id: string) {
  const user = await getCurrentUser(req);
  if (!user) return { error: NextResponse.json({ error: "Unauthorized" }, { status: 403 }) };
  if (!(await canAccessTransactions(user.id, [Number(id)]))) {
    return { error: NextResponse.json({ error: "Forbidden" }, { status: 403 }) };
  }
  return { user };
 }
 const SELECT_REVIEWS = `
  SELECT r.transaction_id, r.participant_id, p.name AS participant_name,
         r.rating, r.order_again, r.note, r.item_verdicts, r.updated_at
    FROM order_reviews r
    JOIN participants p ON p.id = r.participant_id
   WHERE r.transaction_id = $1
   ORDER BY r.participant_id`;
 /**
 * Everything the order panel needs that is not the receipt itself.
 *
 * The splits come back here rather than from a separate endpoint because the
 * panel asks one question — "was this shared, and what did we think of it" —
 * and the sharing half is answered by whether a split exists. A second request
 * would let the verdict render before the share state, which is the order that
 * invites a duplicate split.
 */
 async function panelState(transactionId: number) {
  const [reviews, splits, merchant] = await Promise.all([
    queryRaw<OrderReview>(SELECT_REVIEWS, [transactionId]),
    queryRaw<{ participant_id: number; share_percent: string }>(
      `SELECT participant_id, share_percent FROM transaction_splits
        WHERE transaction_id = $1 ORDER BY participant_id`,
      [transactionId]
    ),
    merchantForTransaction(transactionId),
  ]);
  return {
    reviews,
    splits,
    merchant: await merchantVerdict(merchant, transactionId),
  };
 }
 export async function GET(
  req: NextRequest,
  { params }: { params: Promise<{ id: string }> }
 ) {
  const { id } = await params;
  const auth = await authorise(req, id);
  if (auth.error) return auth.error;
  const transactionId = Number(id);
  return NextResponse.json(await panelState(transactionId));
 }
 /**
 * Record or change one person's verdict.
 *
 * Upsert rather than insert: a verdict is an opinion and opinions get revised.
 * `ON CONFLICT (transaction_id, participant_id)` keeps one row per person per
 * order however many times the buttons are pressed — and, critically, lets the
 * second person's verdict land without touching the first.
 *
 * A null rating is meaningful — it clears the verdict rather than deleting the
 * row, so a note and the item opinions survive changing your mind about the
 * overall call.
 */
 export async function PUT(
  req: NextRequest,
  { params }: { params: Promise<{ id: string }> }
 ) {
  const { id } = await params;
  const auth = await authorise(req, id);
  if (auth.error) return auth.error;
  const transactionId = Number(id);
  let body: {
    participant_id?: number;
    rating?: Rating | null;
    order_again?: boolean | null;
    note?: string | null;
    item_verdicts?: ItemOpinion[] | null;
  };
  try {
    body = await req.json();
  } catch {
    return NextResponse.json({ error: "invalid JSON" }, { status: 400 });
  }
  // Defaults to whoever is signed in, NOT to the owner: Sonu authenticates
  // through the same Traefik OAuth as participant 4, so an owner default would
  // silently file her verdict under his name. An explicit participant_id is
  // still honoured — one person entering both opinions at the table is the
  // common case in a two-person household.
  const participantId = body.participant_id ?? auth.user!.id;
  const rating = body.rating ?? null;
  if (rating !== null && !RATINGS.includes(rating)) {
    // The DB has the same CHECK constraint; failing here gives a usable message
    // instead of a 500 carrying a Postgres constraint name.
    return NextResponse.json(
      { error: `rating must be one of ${RATINGS.join(", ")} or null` },
      { status: 400 }
    );
  }
  const note = typeof body.note === "string" ? body.note.trim() || null : null;
  // An ABSENT item_verdicts means "leave them alone"; an empty array means
  // "clear them". Without that distinction, saving a note from a form that
  // does not carry the item state silently wipes every per-item opinion — the
  // same shape as the bug that reset `settled` on split rewrites, and just as
  // invisible on screen.
  const keepItems = body.item_verdicts === undefined;
  // Drop anything malformed rather than reject the whole save: the rating and
  // the note are the parts the user is watching, and failing their edit over a
  // bad item entry loses the input they actually gave.
  const itemVerdicts: ItemOpinion[] = (body.item_verdicts ?? [])
    .filter(
      (v): v is ItemOpinion =>
        !!v &&
        typeof v.item === "string" &&
        v.item.trim().length > 0 &&
        ITEM_VERDICTS.includes(v.verdict)
    )
    .map((v) => ({ item: v.item.trim(), verdict: v.verdict }));
  // `order_again` is derived when the caller does not say. "never" is the only
  // rating that answers the question on its own; "ok" is not a refusal.
  const orderAgain =
    body.order_again ?? (rating === null ? null : rating !== "never");
  await queryRow(
    `INSERT INTO order_reviews (transaction_id, participant_id, rating, order_again, note, item_verdicts)
     VALUES ($1, $2, $3, $4, $5, $6::jsonb)
     ON CONFLICT (transaction_id, participant_id) DO UPDATE
       SET rating = EXCLUDED.rating,
           order_again = EXCLUDED.order_again,
           note = EXCLUDED.note,
           item_verdicts = CASE WHEN $7::boolean
                                THEN order_reviews.item_verdicts
                                ELSE EXCLUDED.item_verdicts END,
           updated_at = now()`,
    [
      transactionId,
      participantId,
      rating,
      orderAgain,
      note,
      JSON.stringify(itemVerdicts),
      keepItems,
    ]
  );
  return NextResponse.json(await panelState(transactionId));
 }
@@ -66,18 +66,47 @@ export async function POST(
    );
  }
  // Carry `settled` across the rewrite.
  //
  // This replaces every split rather than editing in place, so without this the
  // recreated rows take the column default of false — silently converting a
  // discharged historical obligation into a live debt. That is not theoretical:
  // 657 pre-2026 transactions carry settled splits imported from
  // SplitMyExpenses, $37,233.28 of balance that the carryover (transaction
  // 2348) already accounts for. Editing one would double-count its share, and
  // nothing on screen would say so.
  //
  // Changing someone's percentage does not re-open the obligation — it was
  // settled outside this app and stays settled. A participant added who was not
  // there before is a genuinely new obligation and correctly starts unsettled.
  const previous = await queryRaw<{
    participant_id: number;
    settled: boolean;
    settled_at: string | null;
  }>(
    `SELECT participant_id, settled, settled_at
       FROM transaction_splits WHERE transaction_id = $1`,
    [transactionId]
  );
  const settledBefore = new Map(
    previous.map((p) => [p.participant_id, { settled: p.settled, settled_at: p.settled_at }])
  );
  // Replace all splits for this transaction atomically
  await prisma.$transaction([
    prisma.transaction_splits.deleteMany({ where: { transaction_id: transactionId } }),
-    ...splits.map((s) =>
+    ...splits.map((s) => {
-      prisma.transaction_splits.create({
+      const before = settledBefore.get(s.participant_id);
      return prisma.transaction_splits.create({
        data: {
          transaction_id: transactionId,
          participant_id: s.participant_id,
          share_percent: s.share_percent,
          settled: before?.settled ?? false,
          settled_at: before?.settled_at ? new Date(before.settled_at) : null,
        },
-      })
+      });
-    ),
+    }),
  ]);
  const result = await queryRaw<SplitRow>(
@@ -206,6 +206,21 @@ export default function StatementsPage() {
                    </td>
                    <td className="px-4 py-3 text-zinc-400 whitespace-nowrap">
                      {formatPeriod(s.billing_start_date, s.billing_end_date)}
                      {/* An account cannot be billed twice for the same day, so
                          an overlap means these transactions are in the ledger
                          twice. Red rather than amber: the balance warning above
                          means a statement doesn't add up, this means the data is
                          double-counted everywhere it is summed. */}
                      {s.overlaps?.length > 0 && (
                        <div
                          className="text-[10px] text-red-400 mt-0.5"
                          title={`Billing period overlaps statement ${s.overlaps
                            .map((o) => `#${o.id} by ${o.days} day${o.days === 1 ? "" : "s"}`)
                            .join(", ")}. The overlapping transactions are likely imported twice.`}
                        >
                          ⚠ overlaps #{s.overlaps.map((o) => o.id).join(", #")}
                        </div>
                      )}
                    </td>
                    <td className="px-4 py-3 text-zinc-400 whitespace-nowrap">
                      {formatDate(s.payment_due_date ?? s.billing_end_date)}
@@ -507,6 +507,13 @@ function TransactionsContent() {
  const searchParams = useSearchParams();
  const initialStatementId = searchParams.get("statement_id") || "";
  // `?q=` lands the view on a specific row. The Slack order nudge links here,
  // and without it the link drops you at the top of an unfiltered ledger and
  // the merchant has to be found by hand — which is how a nudge stops getting
  // opened.
  const initialQuery = searchParams.get("q") || "";
  const initialParsed = parseQuery(initialQuery);
  const [filters, setFilters] = useState({
    from: "",
    to: "",
@@ -514,7 +521,7 @@ function TransactionsContent() {
    bank_names: [] as string[],
    tag_ids: [] as string[],
    transaction_types: [] as string[],
-    search: "",
+    search: initialParsed.text,
    statement_id: initialStatementId,
    sort_by: "transaction_date",
    sort_dir: "desc",
@@ -525,8 +532,8 @@ function TransactionsContent() {
    has_split: "" as string,
    trip_id: "" as string,
  });
-  const [queryInput, setQueryInput] = useState("");
+  const [queryInput, setQueryInput] = useState(initialQuery);
-  const [queryTokens, setQueryTokens] = useState<QueryToken[]>([]);
+  const [queryTokens, setQueryTokens] = useState<QueryToken[]>(initialParsed.tokens);
  function handleQueryChange(val: string) {
    setQueryInput(val);
@@ -128,7 +128,10 @@ export default function TripDetailPage({ params }: { params: Promise<{ id: strin
      {/* Stat cards */}
      <div className="grid grid-cols-2 sm:grid-cols-4 gap-4">
-        <StatCard label="Total Spend" value={`$${Number(total_spend).toFixed(2)}`} sub="all transactions" color={t.color} />
+        {/* Deliberately every payer, not just this owner — a trip cost what the
            group put into it. The split figures below are owner-scoped, so this
            says whose money it counts to stop the two being read as one lens. */}
        <StatCard label="Total Spend" value={`$${Number(total_spend).toFixed(2)}`} sub="all payers, net of refunds" color={t.color} />
        <StatCard label="Transactions" value={String(transaction_count)} sub="total" color={t.color} />
        <StatCard label="Daily Average" value={`$${Number(daily_average).toFixed(2)}`} sub="per day" color={t.color} />
        <StatCard label="Days" value={String(num_days)} sub={dateRange ?? "date range"} color={t.color} />
@@ -288,12 +291,24 @@ export default function TripDetailPage({ params }: { params: Promise<{ id: strin
                  </tr>
                </thead>
                <tbody>
-                  {participant_splits.map((p) => (
+                  {/* A negative outstanding means they have paid more towards this
                      trip than their share of it — which reads as a typo unless the
                      sign is spelled out. Shown as a magnitude plus a word, the same
                      way Shared does it, so the two pages agree on what a direction
                      means. */}
                  {participant_splits.map((p) => {
                    const owed = Number(p.owed);
                    const square = Math.abs(owed) < 0.005;
                    const theyOweMe = owed > 0;
                    return (
                      <tr key={p.participant_id} className="border-b border-zinc-800/50 last:border-0">
                        <td className="px-5 py-3 font-medium">{p.name}</td>
                        <td className="px-5 py-3 text-right tabular-nums font-mono">
-                        <span className={Math.abs(Number(p.owed)) < 0.005 ? "text-zinc-500" : ""}>
+                          <span className={square ? "text-zinc-500" : theyOweMe ? "text-amber-400" : "text-blue-400"}>
-                          ${Number(p.owed).toFixed(2)}
+                            ${Math.abs(owed).toFixed(2)}
                          </span>
                          <span className="block text-[11px] text-zinc-500 mt-0.5 font-sans">
                            {square ? "all square" : theyOweMe ? "owes you" : "ahead — you owe them"}
                          </span>
                          {p.unconverted_count > 0 && (
                            <span className="block text-[11px] text-amber-500/80 mt-0.5 font-sans">
@@ -302,18 +317,20 @@ export default function TripDetailPage({ params }: { params: Promise<{ id: strin
                          )}
                        </td>
                      </tr>
-                  ))}
+                    );
                  })}
                </tbody>
              </table>
-              {/* Settled/unsettled was reported from transaction_splits.settled,
+              {/* This note used to say a per-trip figure could not be computed,
-                  which nothing sets — so every trip showed 100% unsettled forever,
+                  because payments carried no trip attribution. Migration 0022 added
-                  including ones already paid in full. Settlement is tracked across
+                  split_payments.trip_id, so it can and now does — the figures above
-                  the whole relationship, not per trip: payments carry no trip
+                  are net of payments scoped to this trip. What the note has to say
-                  attribution, so a per-trip figure cannot be computed. */}
+                  instead is which payments are NOT in them. */}
              <p className="px-5 py-2.5 text-xs text-zinc-500 border-t border-zinc-800">
-                Settlement is tracked across all shared expenses, not per trip —
+                Net of payments recorded against this trip. Payments on the ongoing
                household tab are not counted here —
                see <Link href="/shared" className="text-zinc-400 hover:text-zinc-200 underline">Shared</Link> for
-                what is actually owed.
+                the overall balance.
              </p>
            </div>
          )}
@@ -1,6 +1,17 @@
 "use client";
-import { useOrderReceipt, type OrderReceipt } from "@/lib/hooks";
+import { useMemo, useState } from "react";
 import {
  useOrderReceipt,
  useOrderReview,
  useParticipants,
  useSetOrderReview,
  useSetSplits,
  type ItemOpinion,
  type ItemVerdict,
  type OrderReceipt,
  type OrderRating,
 } from "@/lib/hooks";
 const PLATFORM_LABEL: Record<string, string> = {
  doordash: "DoorDash",
@@ -9,11 +20,40 @@ const PLATFORM_LABEL: Record<string, string> = {
 };
 /**
- * The receipt behind a delivery order: what was actually bought, and where it
+ * Who records verdicts. A two-person household with one primary user: the
- * went. All of it was already stored at ingest and none of it was reachable —
+ * owner records almost everything, and the only other consumer is Sonu (user,
- * the row showed a merchant and a total and nothing else.
+ * 2026-07-28). Mirrors OWNER_PARTICIPANT_ID / SECOND_CONSUMER_ID in
 * `lib/order-reviews.ts` — duplicated rather than imported because that module
 * pulls in the database client and this is a client component.
 */
 const OWNER_PARTICIPANT_ID = 1;
 const SECOND_CONSUMER_ID = 4;
 const RATING_LABEL: Record<OrderRating, string> = {
  loved: "Loved it",
  liked: "Liked it",
  ok: "OK",
  never: "Never again",
 };
 const RATING_STYLE: Record<OrderRating, string> = {
  loved: "border-emerald-600 bg-emerald-950 text-emerald-300",
  liked: "border-emerald-800 bg-emerald-950/50 text-emerald-400",
  ok: "border-zinc-600 bg-zinc-800 text-zinc-300",
  never: "border-red-800 bg-red-950 text-red-300",
 };
 const RATING_ORDER: OrderRating[] = ["loved", "liked", "ok", "never"];
 /**
 * The receipt behind a delivery order: what was actually bought, where it went,
 * and what we thought of it.
 *
- * Read-only on purpose. This is what a provider sent, not something to edit.
+ * The receipt half is read-only — it is what a provider sent, not something to
 * edit. The verdict half is the only part of an order that changes, and it is
 * the reason the receipts are ingested at all (ING-9): the ledger already knew
 * we had ordered from here, but not that it was bad, so orders got repeated
 * from places we disliked because nobody remembered.
 */
 export function OrderDetails({
  transactionId,
@@ -26,6 +66,9 @@ export function OrderDetails({
  bare?: boolean;
 }) {
  const { data: receipt, isLoading } = useOrderReceipt(transactionId);
  const { data: review } = useOrderReview(transactionId);
  const [reviewer, setReviewer] = useState(OWNER_PARTICIPANT_ID);
  if (isLoading || !receipt) return null;
  const cur = receipt.currency ?? currency ?? "AUD";
@@ -33,6 +76,8 @@ export function OrderDetails({
  const items: OrderReceipt["line_items"] = receipt.line_items ?? [];
  const route: OrderReceipt["route"] = receipt.route ?? [];
  const mine = review?.reviews.find((r) => r.participant_id === reviewer);
  return (
    <div className={bare ? "" : "border-t border-zinc-800 pt-4"}>
      <div className="flex items-baseline justify-between mb-2">
@@ -50,7 +95,7 @@ export function OrderDetails({
      {items.length > 0 ? (
        <ul className="space-y-1.5 mb-3">
          {items.map((it, i) => (
-            <li key={i} className="flex gap-2 text-xs">
+            <li key={i} className="flex gap-2 text-xs items-start">
              <span className="text-zinc-600 tabular-nums shrink-0">{it.qty}×</span>
              <span className="text-zinc-300 flex-1 min-w-0">
                {it.description}
@@ -58,6 +103,14 @@ export function OrderDetails({
                  <span className="block text-zinc-600">{it.options.join(" · ")}</span>
                )}
              </span>
              <ItemVerdictToggle
                transactionId={transactionId}
                reviewer={reviewer}
                item={it.description}
                current={mine?.item_verdicts ?? []}
                rating={mine?.rating ?? null}
                note={mine?.note ?? null}
              />
              <span className="text-zinc-400 tabular-nums shrink-0">{fmt(Number(it.amount))}</span>
            </li>
          ))}
@@ -90,6 +143,307 @@ export function OrderDetails({
          {receipt.order_reference}
        </p>
      )}
      <OrderVerdict
        transactionId={transactionId}
        reviewer={reviewer}
        onReviewerChange={setReviewer}
      />
    </div>
  );
 }
 /**
 * Loved / never on a single line item, for the currently selected reviewer.
 *
 * Only the two poles are offered. A per-item "OK" is noise: the question at the
 * next order is "what should I get, what should I avoid", and a middling dish
 * answers neither.
 *
 * Every press sends the whole item array plus the current rating and note,
 * because the endpoint upserts a row rather than patching fields — sending a
 * partial would blank whatever it omitted.
 */
 function ItemVerdictToggle({
  transactionId,
  reviewer,
  item,
  current,
  rating,
  note,
 }: {
  transactionId: number;
  reviewer: number;
  item: string;
  current: ItemOpinion[];
  rating: OrderRating | null;
  note: string | null;
 }) {
  const save = useSetOrderReview();
  const existing = current.find(
    (v) => v.item.trim().toLowerCase() === item.trim().toLowerCase()
  );
  const toggle = (verdict: ItemVerdict) => {
    const rest = current.filter(
      (v) => v.item.trim().toLowerCase() !== item.trim().toLowerCase()
    );
    // Pressing the active verdict clears it — a mis-tap must be reversible, and
    // there is no other route back to "no opinion on this dish".
    const next =
      existing?.verdict === verdict ? rest : [...rest, { item, verdict }];
    save.mutate({
      transactionId,
      participantId: reviewer,
      rating,
      note,
      itemVerdicts: next,
    });
  };
  return (
    <span className="flex gap-0.5 shrink-0">
      {(["loved", "never"] as ItemVerdict[]).map((v) => (
        <button
          key={v}
          type="button"
          onClick={() => toggle(v)}
          disabled={save.isPending}
          title={v === "loved" ? "Loved this item" : "Never order this again"}
          className={`rounded px-1 leading-none transition-opacity disabled:opacity-40 ${
            existing?.verdict === v
              ? "opacity-100"
              : "opacity-25 hover:opacity-60"
          }`}
        >
          {v === "loved" ? "👍" : "👎"}
        </button>
      ))}
    </span>
  );
 }
 /**
 * Was this order shared? One tap, and the split is the answer.
 *
 * "Shared" means shared in both senses — we both ate it and we both pay for it
 * — so this writes a real 50/50 `transaction_splits` row rather than a
 * decorative flag (user, 2026-07-28: the split was part of the original
 * requirement). There is no separate "shared" column precisely because the
 * split already IS that record, and two records of one fact drift apart.
 *
 * Unsharing clears the splits. That is safe on an order because an ingested
 * order is post-cutover by construction — the DB CHECK forbids credits orders
 * before 2026-01-09 — so no settled historical obligation can be sitting on it
 * to lose.
 */
 function SharedToggle({
  transactionId,
  splits,
  otherName,
 }: {
  transactionId: number;
  splits: { participant_id: number; share_percent: string }[];
  otherName: string;
 }) {
  const setSplits = useSetSplits();
  const shared = splits.some((s) => s.participant_id === SECOND_CONSUMER_ID);
  return (
    <div className="mb-2 flex items-center gap-2">
      <button
        type="button"
        disabled={setSplits.isPending}
        onClick={() =>
          setSplits.mutate({
            transactionId,
            splits: shared
              ? []
              : [{ participant_id: SECOND_CONSUMER_ID, share_percent: 50 }],
          })
        }
        className={`rounded border px-2 py-1 text-xs transition-colors disabled:opacity-50 ${
          shared
            ? "border-sky-700 bg-sky-950 text-sky-300"
            : "border-zinc-700 text-zinc-500 hover:border-zinc-600 hover:text-zinc-300"
        }`}
      >
        {shared ? `Shared 50/50 with ${otherName}` : "Just me"}
      </button>
      {shared && (
        <span className="text-[11px] text-zinc-600">
          ask {otherName} for her verdict too
        </span>
      )}
    </div>
  );
 }
 /**
 * The overall verdict, whose it is, and this merchant's track record.
 *
 * The history sits above the buttons deliberately: it is read before the next
 * order, not after, and burying it under the form is how you re-order from a
 * place you already rejected.
 */
 function OrderVerdict({
  transactionId,
  reviewer,
  onReviewerChange,
 }: {
  transactionId: number;
  reviewer: number;
  onReviewerChange: (id: number) => void;
 }) {
  const { data, isLoading } = useOrderReview(transactionId);
  const { data: participants } = useParticipants();
  const save = useSetOrderReview();
  const [noteDraft, setNoteDraft] = useState<string | null>(null);
  const reviewers = useMemo(
    () =>
      [OWNER_PARTICIPANT_ID, SECOND_CONSUMER_ID].map((id) => ({
        id,
        name:
          id === OWNER_PARTICIPANT_ID
            ? "Me"
            : participants?.find((p) => p.id === id)?.name ?? "Them",
      })),
    [participants]
  );
  // No merchant means no receipt behind this row — nothing to have a view on.
  if (isLoading || !data?.merchant) return null;
  const mine = data.reviews.find((r) => r.participant_id === reviewer);
  const current = mine?.rating ?? null;
  const noteValue = noteDraft ?? mine?.note ?? "";
  const { history, warn, items } = data.merchant;
  const others = data.reviews.filter((r) => r.participant_id !== reviewer && r.rating);
  const set = (rating: OrderRating) =>
    save.mutate({
      transactionId,
      participantId: reviewer,
      // Pressing the active rating clears it — otherwise a mis-tap is
      // permanent, and there is no other way back to "no opinion".
      rating: rating === current ? null : rating,
      note: noteValue.trim() || null,
    });
  return (
    <div className="mt-4 border-t border-zinc-800 pt-3">
      {warn && (
        <p className="mb-2 text-xs text-red-400">
          Marked &ldquo;never again&rdquo; here before.
        </p>
      )}
      {items.length > 0 && (
        <p className="mb-2 text-[11px] text-zinc-500">
          {items
            .filter((i) => i.loved > i.never)
            .slice(0, 3)
            .map((i) => `👍 ${i.item}`)
            .concat(
              items
                .filter((i) => i.never > 0)
                .slice(0, 3)
                .map((i) => `👎 ${i.item}`)
            )
            .join(" · ")}
        </p>
      )}
      <SharedToggle
        transactionId={transactionId}
        splits={data.splits}
        otherName={reviewers[1].name}
      />
      <div className="flex items-center gap-2 mb-2">
        {reviewers.map((r) => (
          <button
            key={r.id}
            type="button"
            onClick={() => {
              setNoteDraft(null); // the draft belongs to the person who typed it
              onReviewerChange(r.id);
            }}
            className={`text-xs transition-colors ${
              reviewer === r.id
                ? "text-zinc-200 underline underline-offset-4"
                : "text-zinc-600 hover:text-zinc-400"
            }`}
          >
            {r.name}
            {data.reviews.some((v) => v.participant_id === r.id && v.rating) && " ✓"}
          </button>
        ))}
      </div>
      <div className="flex flex-wrap gap-1.5">
        {RATING_ORDER.map((r) => (
          <button
            key={r}
            type="button"
            onClick={() => set(r)}
            disabled={save.isPending}
            className={`rounded border px-2 py-1 text-xs transition-colors disabled:opacity-50 ${
              current === r
                ? RATING_STYLE[r]
                : "border-zinc-700 text-zinc-500 hover:border-zinc-600 hover:text-zinc-300"
            }`}
          >
            {RATING_LABEL[r]}
          </button>
        ))}
      </div>
      <input
        type="text"
        value={noteValue}
        placeholder="What was wrong (or right)?"
        onChange={(e) => setNoteDraft(e.target.value)}
        onBlur={() => {
          const next = noteValue.trim() || null;
          if (next !== (mine?.note ?? null)) {
            save.mutate({
              transactionId,
              participantId: reviewer,
              rating: current,
              note: next,
            });
          }
        }}
        className="mt-2 w-full rounded border border-zinc-800 bg-zinc-900 px-2 py-1 text-xs text-zinc-300 placeholder:text-zinc-700 focus:border-zinc-600 focus:outline-none"
      />
      {others.map((o) => (
        <p key={o.participant_id} className="mt-1.5 text-[11px] text-zinc-500">
          <span className="text-zinc-400">{o.participant_name}:</span>{" "}
          {o.rating && RATING_LABEL[o.rating]}
          {o.note && <span className="italic"> — {o.note}</span>}
        </p>
      ))}
      {history.length > 0 && (
        <ul className="mt-2 space-y-1">
          {history.map((h) => (
            <li
              key={`${h.transaction_id}-${h.participant_id}`}
              className="text-[11px] text-zinc-600"
            >
              <span className="tabular-nums">{h.transaction_date}</span>
              <span className="ml-1.5 text-zinc-500">
                {h.participant_name}
                {h.rating ? ` · ${RATING_LABEL[h.rating]}` : ""}
              </span>
              {h.note && <span className="ml-1.5 italic">{h.note}</span>}
            </li>
          ))}
        </ul>
      )}
    </div>
  );
 }
@@ -36,7 +36,9 @@ export const OWNER_SCOPE = `COALESCE(t.owner_id, s.owner_id)`;
 export const STATEMENTS_JOIN = `LEFT JOIN statements s ON s.id = t.statement_id`;
 /**
- * Drops the manual/CSV row that a statement line has superseded.
+ * Drops rows that a different row has replaced. Two distinct cases:
 *
 * **1. The manual/CSV row a statement line superseded** (`reconciled_with_id`).
 *
 * Reconciliation keeps both rows: the manual one the user entered and the
 * statement line it turned out to be. Only the statement line should count, or
@@ -52,8 +54,26 @@ export const STATEMENTS_JOIN = `LEFT JOIN statements s ON s.id = t.statement_id`
 * inserted with `reconciled_with_id` NULL and are held out of the reconcile
 * queue by `needsCardMatch()`, so nothing ever sets it. If one is reconciled by
 * hand against a card line, this is what stops it double-counting.
 *
 * **2. The statement row imported twice** (`superseded_by_id`, migration 0023).
 *
 * When two statements for one account bill overlapping periods, every
 * transaction in the overlap arrives twice — 31 pairs on ANZ `4085-56264`, from
 * statements 107/142/143. Case 1 cannot express this: its predicate is scoped
 * to `statement_id IS NULL` on purpose, and here BOTH rows are statement lines.
 *
 * The superseded row is excluded rather than deleted because every child of
 * `transactions` cascades on delete, and the curation is not reliably on the
 * surviving side.
 *
 * Adding it to this fragment rather than making a new one is deliberate: every
 * query that already asks "count each purchase once" now excludes both kinds
 * without being edited. Anything summing transactions without this fragment
 * still double-counts — that is the same gap that let reconciled rows into the
 * analytics routes in the first place.
 */
-export const EXCLUDE_RECONCILED_SOURCE = `NOT (t.statement_id IS NULL AND t.reconciled_with_id IS NOT NULL)`;
+export const EXCLUDE_RECONCILED_SOURCE = `NOT (t.statement_id IS NULL AND t.reconciled_with_id IS NOT NULL)
  AND t.superseded_by_id IS NULL`;
 /**
 * The currency `t.amount` is actually denominated in.
@@ -197,9 +217,29 @@ END`;
 * deliberately no "mark settled" action anywhere: settling up is recording a
 * payment, and this column is only ever written by the historical import.
 *
- * Assumes the `transaction_splits` alias is `ts`.
+ * **The date is the real guard, and the flag is only a refinement of it.**
 * Nothing before SPLIT_CUTOVER can be owed, because carryover transaction 2348
 * already carries the entire pre-cutover balance as a single figure. A split on
 * an older transaction is therefore free to describe *how an expense was shared*
 * — which is what stops it inflating spend — without ever asserting a debt.
 *
 * That separation is what makes splitting history safe. Before it, the only
 * thing keeping $37,233.28 of paid debt out of the balances was a boolean that
 * any delete-and-recreate write path silently reset to false. Now losing the
 * flag on a pre-cutover row costs nothing: the date still excludes it. The flag
 * matters only for rows on or after the cutover, where it marks the handful
 * settled outside this app.
 *
 * The boundary is inclusive because transaction 2348 is itself dated
 * 2026-01-09 — an exclusive bound would drop the carryover and with it the
 * entire pre-cutover balance.
 *
 * Assumes the `transaction_splits` alias is `ts` and `transactions` is `t`.
 */
-export const ACTIVE_OBLIGATION = `ts.settled = false`;
+export const SPLIT_CUTOVER = "2026-01-09";
 export const ACTIVE_OBLIGATION = `ts.settled = false
  AND t.transaction_date >= '${SPLIT_CUTOVER}'`;
 /**
 * The tab a split belongs to: its transaction's trip, else the household.
@@ -277,6 +277,103 @@ export function useOrderReceipt(transactionId: number) {
  });
 }
 export type OrderRating = "loved" | "liked" | "ok" | "never";
 export type ItemVerdict = "loved" | "never";
 export interface ItemOpinion {
  item: string;
  verdict: ItemVerdict;
 }
 export interface OrderReviewRow {
  transaction_id: number;
  participant_id: number;
  participant_name: string;
  rating: OrderRating | null;
  order_again: boolean | null;
  note: string | null;
  item_verdicts: ItemOpinion[];
 }
 export interface OrderReviewState {
  /** One row per person who has an opinion. Empty until someone records one. */
  reviews: OrderReviewRow[];
  /** Current splits — an empty list means the order was not shared. */
  splits: { participant_id: number; share_percent: string }[];
  merchant: {
    merchant: string;
    history: {
      transaction_id: number;
      participant_id: number;
      participant_name: string;
      rating: OrderRating | null;
      note: string | null;
      transaction_date: string | null;
    }[];
    counts: Record<OrderRating, number>;
    warn: boolean;
    items: { item: string; loved: number; never: number }[];
  } | null;
 }
 /**
 * The verdict on an order and this merchant's track record.
 *
 * No `staleTime: Infinity` here, unlike the receipt hook next to it — a receipt
 * never changes, but a verdict is the one part of an order that does.
 */
 export function useOrderReview(transactionId: number) {
  return useQuery<OrderReviewState>({
    queryKey: ["order-review", transactionId],
    queryFn: async () => {
      const res = await fetch(`/api/transactions/${transactionId}/review`);
      if (!res.ok) return { reviews: [], splits: [], merchant: null };
      return res.json();
    },
  });
 }
 export function useSetOrderReview() {
  const qc = useQueryClient();
  return useMutation({
    mutationFn: async ({
      transactionId,
      participantId,
      rating,
      note,
      itemVerdicts,
    }: {
      transactionId: number;
      participantId: number;
      rating: OrderRating | null;
      note?: string | null;
      /** Omit to leave existing item opinions untouched. */
      itemVerdicts?: ItemOpinion[];
    }) => {
      const res = await fetch(`/api/transactions/${transactionId}/review`, {
        method: "PUT",
        headers: { "Content-Type": "application/json" },
        body: JSON.stringify({
          participant_id: participantId,
          rating,
          note,
          ...(itemVerdicts === undefined ? {} : { item_verdicts: itemVerdicts }),
        }),
      });
      if (!res.ok) {
        const err = await res.json();
        throw new Error(err.error || "Failed to save verdict");
      }
      return res.json();
    },
    // Every order from the same merchant now shows a different track record,
    // so invalidate the whole key rather than this one transaction.
    onSuccess: () => {
      qc.invalidateQueries({ queryKey: ["order-review"] });
    },
  });
 }
 export function useSetSplits() {
  const qc = useQueryClient();
  return useMutation({
@@ -302,6 +399,8 @@ export function useSetSplits() {
      qc.invalidateQueries({ queryKey: ["splits"] });
      qc.invalidateQueries({ queryKey: ["shared-transactions"] });
      qc.invalidateQueries({ queryKey: ["participant-balances"] });
      // The order panel shows share state from this same data.
      qc.invalidateQueries({ queryKey: ["order-review"] });
    },
  });
 }
@@ -0,0 +1,190 @@
 import { queryRaw, queryRow } from "@/lib/db";
 /**
 * Verdicts on delivery orders — the "don't order from here again" memory.
 *
 * The problem this exists for is not accounting. Orders were placed twice from
 * places we disliked because nobody remembered by the time the next order went
 * in (user, 2026-07-28). The ledger already knew we had been there; it just had
 * nowhere to record what we thought of it.
 *
 * **A verdict is recorded per order per person, but read per merchant.**
 * `order_reviews` keys on `(transaction_id, participant_id)`, because what you
 * are judging is one delivery — this Thai place was bad *that night*, with
 * those items — and because a shared meal produces two opinions that routinely
 * disagree. That disagreement is the useful part; one row per transaction
 * cannot hold it.
 *
 * The signal you need later is about the merchant, so it is derived by
 * aggregating a merchant's orders rather than stored on one. Storing it per
 * merchant instead would mean the second verdict silently overwrites the first
 * and you lose the fact that it was fine twice and awful once.
 *
 * The join key is `expense_metadata.merchant_normalized`, not
 * `transactions.merchant_name`: the latter is a bank descriptor and reads
 * differently for the same restaurant on different nights.
 */
 /**
 * Four levels, because three collapsed the distinction that decides a
 * re-order: "loved" and "liked" are both "would order again", but only one is
 * worth going out of your way for, and "ok" is not a recommendation at all
 * (user, 2026-07-28).
 */
 export type Rating = "loved" | "liked" | "ok" | "never";
 export const RATINGS: Rating[] = ["loved", "liked", "ok", "never"];
 /**
 * Per-item opinions, keyed by the line item's description.
 *
 * Only the poles are offered. A per-item "ok" is noise: the useful question at
 * the next order is "what should I get / what should I avoid here", and a
 * middling dish answers neither.
 */
 export type ItemVerdict = "loved" | "never";
 export const ITEM_VERDICTS: ItemVerdict[] = ["loved", "never"];
 export interface ItemOpinion {
  item: string;
  verdict: ItemVerdict;
 }
 /**
 * Whose verdict this is by default, and who the other one is.
 *
 * A two-person household with one primary user: the owner records almost every
 * verdict, and the only other consumer is Sonu (user, 2026-07-28). Named rather
 * than inlined so the Slack nudge, the split it creates and the second verdict
 * it asks for cannot drift apart.
 */
 export const OWNER_PARTICIPANT_ID = 1;
 export const SECOND_CONSUMER_ID = 4;
 export interface OrderReview {
  transaction_id: number;
  participant_id: number;
  participant_name?: string;
  rating: Rating | null;
  order_again: boolean | null;
  note: string | null;
  item_verdicts: ItemOpinion[];
  updated_at: string;
 }
 export interface MerchantVerdict {
  merchant: string;
  /** Verdicts on OTHER orders from this merchant, newest first. */
  history: {
    transaction_id: number;
    participant_id: number;
    participant_name: string;
    rating: Rating | null;
    note: string | null;
    transaction_date: string | null;
  }[];
  counts: Record<Rating, number>;
  /** True when this merchant has ever been marked `never`. */
  warn: boolean;
  /**
   * What to get and what to avoid here, pooled across every order from this
   * merchant. This is the payoff for recording items at all — the order-level
   * rating tells you whether to come back, this tells you what to order when
   * you do.
   */
  items: { item: string; loved: number; never: number }[];
 }
 /**
 * `merchant_normalized` for a transaction, resolving both directions.
 *
 * A card-settled order has no transaction of its own — the statement line is
 * the transaction and the receipt points at it through
 * `matched_transaction_id`. Looking only at `transaction_id` misses exactly the
 * orders that were paid by card, which is most of them.
 */
 export async function merchantForTransaction(
  transactionId: number
 ): Promise<string | null> {
  const row = await queryRow<{ merchant_normalized: string | null }>(
    `SELECT merchant_normalized FROM expense_metadata
      WHERE transaction_id = $1 OR matched_transaction_id = $1
      LIMIT 1`,
    [transactionId]
  );
  return row?.merchant_normalized ?? null;
 }
 /**
 * What we have previously said about a merchant.
 *
 * `exclude` drops the order being looked at, so the panel shows "what you said
 * the other times" rather than echoing the verdict you are currently editing.
 * Pass null when there is no current order — the ingest path, where the whole
 * point is that nothing has been said about this one yet.
 */
 export async function merchantVerdict(
  merchant: string | null,
  exclude: number | null = null
 ): Promise<MerchantVerdict | null> {
  if (!merchant) return null;
  const rows = await queryRaw<{
    transaction_id: number;
    participant_id: number;
    participant_name: string;
    rating: Rating | null;
    note: string | null;
    transaction_date: string | null;
    item_verdicts: ItemOpinion[] | null;
  }>(
    // `rating IS NOT NULL` is deliberately NOT in the WHERE clause: a review
    // can carry item verdicts and no overall rating, and dropping those would
    // lose exactly the "the noodles here are great" signal this exists for.
    `SELECT r.transaction_id, r.participant_id, p.name AS participant_name,
            r.rating, r.note, r.item_verdicts,
            to_char(t.transaction_date, 'YYYY-MM-DD') AS transaction_date
       FROM order_reviews r
       JOIN transactions t ON t.id = r.transaction_id
       JOIN participants p ON p.id = r.participant_id
       JOIN expense_metadata em
         ON em.transaction_id = r.transaction_id
         OR em.matched_transaction_id = r.transaction_id
      WHERE em.merchant_normalized = $1
        AND ($2::int IS NULL OR r.transaction_id <> $2)
      ORDER BY t.transaction_date DESC, r.participant_id
      LIMIT 50`,
    [merchant, exclude]
  );
  const counts: Record<Rating, number> = { loved: 0, liked: 0, ok: 0, never: 0 };
  for (const r of rows) if (r.rating) counts[r.rating] += 1;
  // Pool item opinions across orders. Case-folded because the same dish comes
  // back with inconsistent capitalisation between receipts; the first spelling
  // seen is kept for display.
  const pool = new Map<string, { item: string; loved: number; never: number }>();
  for (const r of rows) {
    for (const v of r.item_verdicts ?? []) {
      if (!v?.item) continue;
      const key = v.item.trim().toLowerCase();
      const entry = pool.get(key) ?? { item: v.item.trim(), loved: 0, never: 0 };
      if (v.verdict === "loved") entry.loved += 1;
      else if (v.verdict === "never") entry.never += 1;
      pool.set(key, entry);
    }
  }
  return {
    merchant,
    history: rows
      .filter((r) => r.rating !== null || r.note)
      .map(({ item_verdicts: _drop, ...h }) => h),
    counts,
    warn: counts.never > 0,
    items: [...pool.values()].sort(
      (a, b) => b.loved + b.never - (a.loved + a.never)
    ),
  };
 }
@@ -1,5 +1,5 @@
 import { queryRaw } from "./db";
-import { EXCLUDE_RECONCILED_SOURCE, NATIVE_CURRENCY, AMOUNT_UNCONVERTED, ACTIVE_OBLIGATION, STATEMENTS_JOIN } from "./analytics-sql";
+import { EXCLUDE_RECONCILED_SOURCE, NATIVE_CURRENCY, AMOUNT_UNCONVERTED, ACTIVE_OBLIGATION, STATEMENTS_JOIN, OWNER_SCOPE, NET_SPEND_ROWS, SPEND_SIGNED } from "./analytics-sql";
 export interface RoutePointRow {
  label: string;
@@ -105,6 +105,12 @@ export interface StatementRow {
  // opening/closing balance to check against.
  expected_closing: number | null;
  balance_diff: number | null;
  /**
   * Other statements for this account billing the same days — see
   * STATEMENT_OVERLAPS. Non-empty means some of these transactions are almost
   * certainly imported twice. Empty array, never null.
   */
  overlaps: { id: number; days: number }[];
 }
 interface TransactionFilters {
@@ -361,15 +367,56 @@ export const BALANCE_DELTA = `SUM(CASE
    ELSE CASE WHEN t.transaction_type IN ('debit', 'fee', 'interest') THEN -t.amount ELSE t.amount END
 END)`;
 /**
 * Other statements for the same account whose billing period overlaps this one.
 *
 * An account cannot be billed twice for the same day, so an overlap means the
 * same transactions were imported twice. This is not hypothetical: ANZ
 * statements 107 and 143 overlap by 118 days and put ~$42,000 of duplicate rows
 * in the ledger, which silently inflated spend and dragged the CSV split match
 * rate down to 46.5%.
 *
 * Two details are what make it actually catch that case:
 *
 *  - The account number is compared with non-digits stripped. The duplicate got
 *    in precisely because the existing duplicate key compared raw text, and ANZ
 *    wrote the same account as `408556264` on one statement and `4085-56264` on
 *    the other.
 *  - The range is half-open `[)`. These statements are issued back-to-back with
 *    one period's end date equal to the next one's start, so inclusive bounds
 *    flag every consecutive pair — 5 hits of which 3 were false. Half-open
 *    leaves exactly the 2 real ones.
 *
 * NULL bounds are excluded rather than passed to `daterange`, where NULL means
 * unbounded and would make an undated statement overlap the entire history.
 */
 const STATEMENT_OVERLAPS = `
      SELECT COALESCE(json_agg(json_build_object(
               'id', o.id,
               'days', (LEAST(s.billing_end_date, o.billing_end_date)
                        - GREATEST(s.billing_start_date, o.billing_start_date))
             ) ORDER BY o.id), '[]'::json) AS overlaps
      FROM statements o
      WHERE o.id <> s.id
        AND o.owner_id = s.owner_id
        AND regexp_replace(o.account_number, '\\D', '', 'g')
          = regexp_replace(s.account_number, '\\D', '', 'g')
        AND o.billing_start_date IS NOT NULL AND o.billing_end_date IS NOT NULL
        AND s.billing_start_date IS NOT NULL AND s.billing_end_date IS NOT NULL
        AND daterange(s.billing_start_date, s.billing_end_date, '[)')
         && daterange(o.billing_start_date, o.billing_end_date, '[)')`;
 export async function getStatements(ownerId: number) {
  const sql = `
    SELECT s.*,
      (SELECT COUNT(*)::int FROM transactions t WHERE t.statement_id = s.id) as transaction_count,
      p.name as owner_name,
      recon.expected_closing,
-      recon.balance_diff
+      recon.balance_diff,
      ov.overlaps
    FROM statements s
    LEFT JOIN participants p ON p.id = s.owner_id
    LEFT JOIN LATERAL (${STATEMENT_OVERLAPS}) ov ON true
    LEFT JOIN LATERAL (
      SELECT
        (s.opening_balance + ${BALANCE_DELTA})::numeric(12,2) as expected_closing,
@@ -844,40 +891,43 @@ export interface TripAnalytics {
  }[];
 }
 // `total_spend` is the headline figure on the trips list and the trip header,
 // and it nets refunds for the same reason getTripAnalytics does — see the note
 // there. Trips are aliased `tr` so that `t` can be `transactions`, which is the
 // alias the shared fragments assume.
 const TRIP_TOTAL_SPEND = `COALESCE(SUM(
        CASE WHEN ${NET_SPEND_ROWS}
             AND ${EXCLUDE_RECONCILED_SOURCE}
             AND COALESCE(o.category_override, t.category, 'other') NOT IN ('transfers', 'investment')
        THEN ${SPEND_SIGNED} ELSE 0 END
      ), 0)::float AS total_spend`;
 export async function getTrips(ownerId: number): Promise<TripRow[]> {
  return queryRaw<TripRow>(`
    SELECT
-      t.*,
+      tr.*,
-      COALESCE(SUM(
+      ${TRIP_TOTAL_SPEND},
        CASE WHEN tx.transaction_type IN ('debit','fee','interest')
             AND COALESCE(o.category_override, tx.category, 'other') NOT IN ('transfers', 'investment')
        THEN COALESCE(tx.amount_aud, tx.amount) ELSE 0 END
      ), 0)::float AS total_spend,
      COUNT(o.transaction_id)::int AS transaction_count
-    FROM trips t
+    FROM trips tr
-    LEFT JOIN transaction_overrides o ON o.trip_id = t.id
+    LEFT JOIN transaction_overrides o ON o.trip_id = tr.id
-    LEFT JOIN transactions tx ON tx.id = o.transaction_id
+    LEFT JOIN transactions t ON t.id = o.transaction_id
-    WHERE t.owner_id = $1
+    WHERE tr.owner_id = $1
-    GROUP BY t.id
+    GROUP BY tr.id
-    ORDER BY t.created_at DESC
+    ORDER BY tr.created_at DESC
  `, [ownerId]);
 }
 export async function getTripById(id: number, ownerId: number): Promise<TripRow | null> {
  const rows = await queryRaw<TripRow>(`
    SELECT
-      t.*,
+      tr.*,
-      COALESCE(SUM(
+      ${TRIP_TOTAL_SPEND},
        CASE WHEN tx.transaction_type IN ('debit','fee','interest')
             AND COALESCE(o.category_override, tx.category, 'other') NOT IN ('transfers', 'investment')
        THEN COALESCE(tx.amount_aud, tx.amount) ELSE 0 END
      ), 0)::float AS total_spend,
      COUNT(o.transaction_id)::int AS transaction_count
-    FROM trips t
+    FROM trips tr
-    LEFT JOIN transaction_overrides o ON o.trip_id = t.id
+    LEFT JOIN transaction_overrides o ON o.trip_id = tr.id
-    LEFT JOIN transactions tx ON tx.id = o.transaction_id
+    LEFT JOIN transactions t ON t.id = o.transaction_id
-    WHERE t.id = $1 AND t.owner_id = $2
+    WHERE tr.id = $1 AND tr.owner_id = $2
-    GROUP BY t.id
+    GROUP BY tr.id
  `, [id, ownerId]);
  return rows[0] ?? null;
 }
@@ -886,44 +936,66 @@ export async function getTripAnalytics(tripId: number, ownerId: number): Promise
  const trip = await getTripById(tripId, ownerId);
  if (!trip) throw new Error("Trip not found");
  // What the trip cost, with refunds subtracted.
  //
  // These four queries filtered on `transaction_type IN ('debit','fee','interest')`,
  // which drops every refund and credit — so money that came back was still
  // counted as trip spend. A partly-refunded booking read at its full price and
  // a fully-refunded one read as pure cost.
  //
  // NET_SPEND_ROWS admits the refunds and SPEND_SIGNED carries their direction,
  // the same pair the general analytics adopted after a refunded Expedia
  // purchase read as $2,888.92 of spend. `transactions` is aliased `t` because
  // the fragments assume that alias.
  //
  // A cancelled booking is a different case and is NOT handled here: both its
  // legs are untagged from the trip by hand, because a trip the booking was
  // cancelled from never incurred that cost at all. This nets the partial
  // refunds — a price adjustment on a booking that did happen.
  //
  // COUNT(*) deliberately still counts refund rows: a refund is a transaction
  // that occurred on the trip, even though it subtracts from the total.
  const [categoryRows, dailyRows, merchantRows, tagRows, splitRows] = await Promise.all([
    queryRaw<{ category: string; amount: number; count: number }>(`
      SELECT
-        COALESCE(o.category_override, tx.category, 'other') AS category,
+        COALESCE(o.category_override, t.category, 'other') AS category,
-        SUM(COALESCE(tx.amount_aud, tx.amount))::float AS amount,
+        SUM(${SPEND_SIGNED})::float AS amount,
        COUNT(*)::int AS count
      FROM transaction_overrides o
-      JOIN transactions tx ON tx.id = o.transaction_id
+      JOIN transactions t ON t.id = o.transaction_id
      WHERE o.trip_id = $1
-        AND tx.transaction_type IN ('debit','fee','interest')
+        AND ${NET_SPEND_ROWS}
-        AND COALESCE(o.category_override, tx.category, 'other') NOT IN ('transfers', 'investment')
+        AND ${EXCLUDE_RECONCILED_SOURCE}
        AND COALESCE(o.category_override, t.category, 'other') NOT IN ('transfers', 'investment')
      GROUP BY 1
      ORDER BY 2 DESC
    `, [tripId]),
    queryRaw<{ date: string; amount: number }>(`
      SELECT
-        tx.transaction_date::text AS date,
+        t.transaction_date::text AS date,
-        SUM(COALESCE(tx.amount_aud, tx.amount))::float AS amount
+        SUM(${SPEND_SIGNED})::float AS amount
      FROM transaction_overrides o
-      JOIN transactions tx ON tx.id = o.transaction_id
+      JOIN transactions t ON t.id = o.transaction_id
      WHERE o.trip_id = $1
-        AND tx.transaction_type IN ('debit','fee','interest')
+        AND ${NET_SPEND_ROWS}
-        AND COALESCE(o.category_override, tx.category, 'other') NOT IN ('transfers', 'investment')
+        AND ${EXCLUDE_RECONCILED_SOURCE}
        AND COALESCE(o.category_override, t.category, 'other') NOT IN ('transfers', 'investment')
      GROUP BY 1
      ORDER BY 1
    `, [tripId]),
    queryRaw<{ merchant: string; amount: number; count: number }>(`
      SELECT
-        COALESCE(o.merchant_normalized, tx.merchant_normalized, tx.merchant_name, tx.description) AS merchant,
+        COALESCE(o.merchant_normalized, t.merchant_normalized, t.merchant_name, t.description) AS merchant,
-        SUM(COALESCE(tx.amount_aud, tx.amount))::float AS amount,
+        SUM(${SPEND_SIGNED})::float AS amount,
        COUNT(*)::int AS count
      FROM transaction_overrides o
-      JOIN transactions tx ON tx.id = o.transaction_id
+      JOIN transactions t ON t.id = o.transaction_id
      WHERE o.trip_id = $1
-        AND tx.transaction_type IN ('debit','fee','interest')
+        AND ${NET_SPEND_ROWS}
-        AND COALESCE(o.category_override, tx.category, 'other') NOT IN ('transfers', 'investment')
+        AND ${EXCLUDE_RECONCILED_SOURCE}
        AND COALESCE(o.category_override, t.category, 'other') NOT IN ('transfers', 'investment')
      GROUP BY 1
      ORDER BY 2 DESC
      LIMIT 10
@@ -932,15 +1004,16 @@ export async function getTripAnalytics(tripId: number, ownerId: number): Promise
    queryRaw<{ tag_id: number; name: string; color: string; amount: number; count: number }>(`
      SELECT
        tg.id AS tag_id, tg.name, tg.color,
-        SUM(COALESCE(tx.amount_aud, tx.amount))::float AS amount,
+        SUM(${SPEND_SIGNED})::float AS amount,
-        COUNT(DISTINCT tx.id)::int AS count
+        COUNT(DISTINCT t.id)::int AS count
      FROM transaction_overrides o
-      JOIN transactions tx ON tx.id = o.transaction_id
+      JOIN transactions t ON t.id = o.transaction_id
-      JOIN transaction_tags tt ON tt.transaction_id = tx.id
+      JOIN transaction_tags tt ON tt.transaction_id = t.id
      JOIN tags tg ON tg.id = tt.tag_id
      WHERE o.trip_id = $1
-        AND tx.transaction_type IN ('debit','fee','interest')
+        AND ${NET_SPEND_ROWS}
-        AND COALESCE(o.category_override, tx.category, 'other') NOT IN ('transfers', 'investment')
+        AND ${EXCLUDE_RECONCILED_SOURCE}
        AND COALESCE(o.category_override, t.category, 'other') NOT IN ('transfers', 'investment')
      GROUP BY tg.id
      ORDER BY 4 DESC
    `, [tripId]),
@@ -966,6 +1039,16 @@ export async function getTripAnalytics(tripId: number, ownerId: number): Promise
    //    so netting a EUR figure against AUD ones silently is most likely to
    //    bite exactly here.
    //
    // A fourth, and it is what "owed" actually means: only rows THIS owner paid
    // for. Without ${OWNER_SCOPE} the figure sums every split on every trip
    // transaction regardless of who paid, so it silently mixes debts owed to
    // different people. On Europe 2026 that put $1,605.49 of Molina's share of
    // Sonu-paid rows into a number labelled as owed to the owner — a debt that
    // is real, but between the other two participants, and which they settled
    // directly (split_payments id 5, Molina -> Sonu, exactly $1,605.49).
    // A participant's own share of a row they paid for was in there too, which
    // is nobody's debt at all.
    //
    // `transactions` is aliased `t` so the shared fragments apply directly —
    // they assume that alias, and hand-inlining a copy is what let the
    // reconciled-row exclusion drift out of the analytics routes to begin with.
@@ -979,16 +1062,22 @@ export async function getTripAnalytics(tripId: number, ownerId: number): Promise
        ${STATEMENTS_JOIN}
        JOIN transaction_splits ts ON ts.transaction_id = t.id
        WHERE o.trip_id = $1
          AND ${OWNER_SCOPE} = $2
          AND ts.participant_id <> $2
          AND t.transaction_type IN ('debit','fee','interest')
          AND COALESCE(o.category_override, t.category, 'other') NOT IN ('transfers', 'investment')
          AND ${ACTIVE_OBLIGATION}
          AND ${EXCLUDE_RECONCILED_SOURCE}
        GROUP BY ts.participant_id
      ),
      -- Only payments made TO this owner. Payment 5 on Europe is Molina -> Sonu:
      -- a real settlement, but of a debt between those two, so it must not
      -- reduce what Molina owes here. Symmetrical with the owner scoping above.
      paid AS (
        SELECT sp.from_participant_id AS pid, SUM(sp.amount) AS amt
        FROM split_payments sp
        WHERE sp.trip_id = $1
          AND sp.to_participant_id = $2
        GROUP BY sp.from_participant_id
      )
      SELECT p.id AS participant_id, p.name,
@@ -999,7 +1088,7 @@ export async function getTripAnalytics(tripId: number, ownerId: number): Promise
      LEFT JOIN paid ON paid.pid = p.id
      WHERE owed.pid IS NOT NULL OR paid.pid IS NOT NULL
      ORDER BY 3 DESC
-    `, [tripId]),
+    `, [tripId, ownerId]),
  ]);
  const num_days = (trip.start_date && trip.end_date)