docs: rewrite the shared-expenses section, which had gone false

Every warning in it was inverted by this week's work: - "settled is dead data, false on every row" -- there are now 1,266 settled splits across 657 pre-2026 transactions. - "do not fix getParticipantBalances to exclude settled splits" -- it now excludes them, via ACTIVE_OBLIGATION, and must. - "settlement cannot be attributed per trip" -- migration 0022 added split_payments.trip_id and it is attributed. - "splits exist from 2026-01-09 only" -- pre-2026 transactions are now split deliberately, to stop them inflating spend. Replaced with what is actually true, including the rule that matters most: the cutover DATE is the primary balance gate and the settled flag only refines it, so pre-2026 expenses can be split freely.
docs(shared): the cutover date is the gate, the flag refines it
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 · 2026-07-28 13:32:31 +10:00 · 2026-07-28 13:02:56 +10:00
14 changed files with 1263 additions and 334 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
-proposal and what is already decided. Three traps:
+Rebuilt 2026-07-28. `docs/shared-expenses-design.md` describes the live model;
+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
-only writer was `/api/splits/settle`, removed in `3f04cbd` because nothing called
-it and one request could mark all of a participant's splits settled. Do not build
-on this flag until settlement contexts exist.
+**The cutover date is the primary balance gate, not the `settled` flag.**
+`ACTIVE_OBLIGATION` (`src/lib/analytics-sql.ts`) is
+`ts.settled = false AND t.transaction_date >= '2026-01-09'`. Nothing dated
+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
-"fix" it to exclude settled splits — the payments that settled them are still
-subtracted, so you would double-count. The two settlement models (running tab vs
-per-split flag) must not be mixed.
+Consequence: **pre-2026 transactions can be split freely.** A split on a 2024
+grocery shop describes how the expense was shared — which is what stops it
+inflating spend — without asserting a debt. 657 pre-2026 transactions carry
+1,266 such splits, imported from SplitMyExpenses and marked `settled`.

-**Settlement cannot be attributed per trip.** `split_payments` records only
-from/to/amount/date. Any per-trip settled/unsettled figure is fabricated; the
-trip view used to show one and always reported 100% unsettled. Trips show share
-only, and point at `/shared` for real balances.
+**Spend counts settled splits; owed does not.** `myShare`/`mySplitOf` must NOT
+filter on `settled` — half a 2025 grocery shop was your expense whether or not
+the other half was repaid. Filtering it out re-inflates exactly the figures the
+historical import exists to correct.

-Also: settlements already exist twice. Four of eight `split_payments` match an
-offset-account credit exactly on amount and date, with `linked_transaction_id`
-populated on only one. And Sonu's loan contributions (`…emi` in the offset
-account, 39 rows, $37,980.24) are categorised `transfers`, indistinguishable from
-ordinary internal transfers.
+**Any split write path that deletes-and-recreates must carry `settled` across.**
+`POST /api/transactions/[id]/splits` did not, and silently converted discharged
+obligations into live debt — $37,233.28 was exposed. Fixed in `6add958`.
+`rule-actions.ts` is safe only by the shape of its upsert
+(`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
-SplitMyExpenses. So a trailing-12-month per-person series splices six months of
-gross onto six months of net. Use Feb–Jun 2026 for anything per-person.
+**Still true, and still a caveat:** Sonu's loan contributions (`…emi` in the
+offset account, 39 rows, $37,980.24) are categorised `transfers`, indistinguishable
+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
-
-Three questions have no answer in the current model:
-
-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.**
+This replaces the 2026-07-26 proposal. That document described three problems
+and proposed a `settlement_contexts` table to solve them. The problems were
+real; the table was not built, and the reasoning for not building it is
+recorded under [What was rejected](#what-was-rejected).

 ---

-## 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 |
-| Per-split flag | `transaction_splits.settled` | **never used** — all 673 splits are `false` |
+| **Spend** (`myShare`, `mySplitOf`) | **Yes** | Half a 2025 grocery shop was my expense whether or not the other half was ever repaid. |
+| **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
- `/api/participants/[id]/balance` filters on `settled = false`
- `getTripAnalytics` reports settled/unsettled **from the unused flag**
+The predicate is `ACTIVE_OBLIGATION` in `src/lib/analytics-sql.ts`.

-The third is a live bug. Every trip shows 100% unsettled forever, even though
-Molina has paid $20,782.79 against $19,556.07 of splits and is square.
+## Two orthogonal axes

-### 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 |
-|---|---|---:|---|
-| 2026-02-02 | Molina | 7,500.00 | `Transfer from - MEGHALEE BOSE mummy Pa…` |
-| 2026-04-12 | Sonu | 3,779.33 | `Transfer from - MEGHALEE BOSE transfer` |
-| 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` |
+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
+expressed, so every trip reported 100% unsettled forever — including trips paid
+in full.

-The same money is a `split_payments` row *and* a `transactions` row.
-`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.
+## How settling up actually works

-### 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
-contribution is never a settlement.
+the other funds consumption. They do not share a settlement scope, and a
+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
-repayment; the difference between obligation and actual is a **receivable**, and
-it is the interesting number.
-
-Over 2025-07-01 → 2026-06-30:
+Not derived from actual payments, which fluctuate. 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:
-
-| Rate | Payments | Period |
-|---|---:|---|
-| $1,250 | 15 | Aug 2025 – Feb 2026 (the correct 50%) |
-| $1,000 | 3 | Jul 2025 (pre-adjustment) |
-| $750 | 8 | Mar – Jun 2026 (leave) |
+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
+(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
-percentage cannot express "obligation unchanged, payment temporarily reduced,
-difference owed".
+actual contributions, with the running difference as a tracked receivable. A
+flat percentage-of-actual cannot express "obligation unchanged, payment
+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
-pay down the combined balance. Reconciles exactly:
+Over 12 months $63,500 of cash left and debt fell by $44,127.36. The $16,523.64
+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
-    133:  32,500.00 − 7,970.37 − 2,849.00  = 21,680.63  = balance reduction
+The legitimate concern is that interest is non-discretionary. The answer is a
+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
-and debt fell by $44,127.36. The $16,523.64 difference bought nothing and is not
-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.
+**Do not** model the loan as a recurring split: that would put $2,500 a
+fortnight of principal into spend, the error migration 0014 exists to prevent.

 ## 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.
-
-4. **Attribution of forwarded payments.** `mummy` in the description reliably
+   belongs in a net-worth view rather than here.
+2. **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
-   an automatic rule.
+   a free-text field. Acceptable as a *suggestion* requiring confirmation, not
+   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
-   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.
+## Resolved

---
+- **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
-  makes exact reconciliation impossible, and the value is low: those balances are
-  settled and will not change.
- **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,
-  which is the error migration 0014 was written to prevent.
+  Sonu's two "transfer" payments were allocated Europe-first, remainder to
+  household, chronologically so each settles what was outstanding when it was
+  made:
+
+  | Transfer | Scope | Amount |
+  |---|---|---:|
+  | $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);
@@ -183,11 +183,14 @@ 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?
 }
@@ -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())
@@ -73,7 +73,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",
@@ -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);
+  });
+});
@@ -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) =>
-      prisma.transaction_splits.create({
+    ...splits.map((s) => {
+      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)}
@@ -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,32 +291,46 @@ export default function TripDetailPage({ params }: { params: Promise<{ id: strin
                  </tr>
                </thead>
                <tbody>
-                  {participant_splits.map((p) => (
-                    <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" : ""}>
-                          ${Number(p.owed).toFixed(2)}
-                        </span>
-                        {p.unconverted_count > 0 && (
-                          <span className="block text-[11px] text-amber-500/80 mt-0.5 font-sans">
-                            approx · {p.unconverted_count} unconverted
+                  {/* 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={square ? "text-zinc-500" : theyOweMe ? "text-amber-400" : "text-blue-400"}>
+                            ${Math.abs(owed).toFixed(2)}
                          </span>
-                        )}
-                      </td>
-                    </tr>
-                  ))}
+                          <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">
+                              approx · {p.unconverted_count} unconverted
+                            </span>
+                          )}
+                        </td>
+                      </tr>
+                    );
+                  })}
                </tbody>
              </table>
-              {/* Settled/unsettled was reported from transaction_splits.settled,
-                  which nothing sets — so every trip showed 100% unsettled forever,
-                  including ones already paid in full. Settlement is tracked across
-                  the whole relationship, not per trip: payments carry no trip
-                  attribution, so a per-trip figure cannot be computed. */}
+              {/* This note used to say a per-trip figure could not be computed,
+                  because payments carried no trip attribution. Migration 0022 added
+                  split_payments.trip_id, so it can and now does — the figures above
+                  are net of payments scoped to this trip. What the note has to say
+                  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>
          )}
@@ -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.
@@ -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.*,
-      COALESCE(SUM(
-        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,
+      tr.*,
+      ${TRIP_TOTAL_SPEND},
      COUNT(o.transaction_id)::int AS transaction_count
-    FROM trips t
-    LEFT JOIN transaction_overrides o ON o.trip_id = t.id
-    LEFT JOIN transactions tx ON tx.id = o.transaction_id
-    WHERE t.owner_id = $1
-    GROUP BY t.id
-    ORDER BY t.created_at DESC
+    FROM trips tr
+    LEFT JOIN transaction_overrides o ON o.trip_id = tr.id
+    LEFT JOIN transactions t ON t.id = o.transaction_id
+    WHERE tr.owner_id = $1
+    GROUP BY tr.id
+    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.*,
-      COALESCE(SUM(
-        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,
+      tr.*,
+      ${TRIP_TOTAL_SPEND},
      COUNT(o.transaction_id)::int AS transaction_count
-    FROM trips t
-    LEFT JOIN transaction_overrides o ON o.trip_id = t.id
-    LEFT JOIN transactions tx ON tx.id = o.transaction_id
-    WHERE t.id = $1 AND t.owner_id = $2
-    GROUP BY t.id
+    FROM trips tr
+    LEFT JOIN transaction_overrides o ON o.trip_id = tr.id
+    LEFT JOIN transactions t ON t.id = o.transaction_id
+    WHERE tr.id = $1 AND tr.owner_id = $2
+    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,
-        SUM(COALESCE(tx.amount_aud, tx.amount))::float AS amount,
+        COALESCE(o.category_override, t.category, 'other') AS category,
+        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 COALESCE(o.category_override, tx.category, 'other') NOT IN ('transfers', 'investment')
+        AND ${NET_SPEND_ROWS}
+        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,
-        SUM(COALESCE(tx.amount_aud, tx.amount))::float AS amount
+        t.transaction_date::text AS date,
+        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 COALESCE(o.category_override, tx.category, 'other') NOT IN ('transfers', 'investment')
+        AND ${NET_SPEND_ROWS}
+        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,
-        SUM(COALESCE(tx.amount_aud, tx.amount))::float AS amount,
+        COALESCE(o.merchant_normalized, t.merchant_normalized, t.merchant_name, t.description) AS merchant,
+        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 COALESCE(o.category_override, tx.category, 'other') NOT IN ('transfers', 'investment')
+        AND ${NET_SPEND_ROWS}
+        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,
-        COUNT(DISTINCT tx.id)::int AS count
+        SUM(${SPEND_SIGNED})::float AS amount,
+        COUNT(DISTINCT t.id)::int AS count
      FROM transaction_overrides o
-      JOIN transactions tx ON tx.id = o.transaction_id
-      JOIN transaction_tags tt ON tt.transaction_id = tx.id
+      JOIN transactions t ON t.id = o.transaction_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 COALESCE(o.category_override, tx.category, 'other') NOT IN ('transfers', 'investment')
+        AND ${NET_SPEND_ROWS}
+        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)
Author	SHA1	Message	Date
siddharthd	d081d80a3f	docs: rewrite the shared-expenses section, which had gone false ci / lint-test (push) Successful in 39s Details Every warning in it was inverted by this week's work: - "settled is dead data, false on every row" -- there are now 1,266 settled splits across 657 pre-2026 transactions. - "do not fix getParticipantBalances to exclude settled splits" -- it now excludes them, via ACTIVE_OBLIGATION, and must. - "settlement cannot be attributed per trip" -- migration 0022 added split_payments.trip_id and it is attributed. - "splits exist from 2026-01-09 only" -- pre-2026 transactions are now split deliberately, to stop them inflating spend. Replaced with what is actually true, including the rule that matters most: the cutover DATE is the primary balance gate and the settled flag only refines it, so pre-2026 expenses can be split freely.	2026-07-28 14:41:05 +10:00
siddharthd	db6b7f8375	docs(shared): the cutover date is the gate, the flag refines it ci / lint-test (push) Successful in 44s Details	2026-07-28 13:54:46 +10:00
siddharthd	788219b9fd	fix(splits): the cutover date, not a boolean, is what gates a balance ci / lint-test (push) Successful in 46s Details Nothing dated before 2026-01-09 can be owed, because carryover transaction 2348 already carries the entire pre-cutover balance as a single figure. ACTIVE_OBLIGATION now says so directly. This inverts which mechanism is load-bearing, and that is the point. Until now the only thing keeping $37,233.28 of paid debt out of the balances was transaction_splits.settled -- a boolean that any delete-and-recreate write path resets to false, as the split modal did until commit `6add958`. Losing the flag on a pre-cutover row now costs nothing: the date still excludes it. The flag matters only on or after the cutover, marking the few settled outside this app. It also makes splitting history safe to do freely. A split on a 2024 grocery shop can now describe how the expense was shared -- which is what stops it inflating spend -- without asserting a debt that was settled years ago. That was the whole reason not to split pre-2026 expenses, and it no longer applies. The bound is inclusive because transaction 2348 is itself dated 2026-01-09; an exclusive one would drop the carryover and with it the entire pre-cutover balance. Corrects one live figure: a Woolworths on 2026-01-06 was split 50/50 three days before the cutover, double-counting $7.55 against the carryover. Sonu $5,428.08 -> $5,420.53. The test fixture default moved to 2026-06-15 -- it was 2024-06-15, which is now pre-cutover and made every balance fixture read zero. That the suite caught this is the guard working.	2026-07-28 13:52:30 +10:00
siddharthd	6add958132	fix(splits): editing a split must not resurrect a settled debt ci / lint-test (push) Successful in 47s Details The route replaces every split for a transaction rather than editing in place, so the recreated rows took the column default settled=false. Opening the split modal on a historical transaction and saving it therefore converted a discharged obligation into a live one, with nothing on screen saying so. That is not theoretical. 657 pre-2026 transactions now 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 against a debt that was paid years ago. Now carries settled and settled_at across the rewrite, per participant, the same way the rules revert route already does. Changing someone's percentage does not re-open the obligation: it was settled outside this app and stays settled. A participant who was not on the transaction before is a genuinely new obligation and correctly starts unsettled. rule-actions.ts was already safe here -- it upserts ON CONFLICT DO UPDATE SET share_percent, so it never touches the flag.	2026-07-28 13:40:48 +10:00
siddharthd	3339a0b9b7	chore: ignore the scripts venv ci / lint-test (push) Successful in 45s Details scripts/split_csv_match.py needs psycopg2, so scripts/ now has a venv beside it. This repo deploys from its working tree, so an untracked .venv would be swept into the Docker build context.	2026-07-28 13:32:31 +10:00
siddharthd	3b9d302ce2	docs(shared): record the grouped-payment allocation ci / lint-test (push) Successful in 49s Details Sonu's two "transfer" payments are split by scope, Europe first and the remainder to household, chronologically so each settles what was outstanding when it was made. Both Europe tabs now read $0.00. No schema change was needed and that is the point worth writing down: 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 -- verified, 4111 sums to $3,779.33 and 4121 to $4,794.06. Her overall balance is unchanged at $5,428.08. Allocation moves money between tabs, never between people; that invariance is the check to repeat on any future re-allocation.	2026-07-28 13:02:56 +10:00
siddharthd	4fc8eeac95	docs(shared): record the Sonu+Sunny leg as its own trip ci / lint-test (push) Successful in 51s Details Trip 3, 2026-04-12 to 2026-04-28, 124 rows, $9,914.24. It was marked only by tag 5 and so was invisible to every trip figure. It reads at first like a sharing scope overlapping the group trip, because the tag's earliest row is 17 March. It is not: that is a single advance booking (Ticketmaster Nanterre), 8 more rows fall on the 12 April handover day and were already held out of Europe 2026, and the remaining 115 run 13-28 April. A clean sequential leg. Also records that grouped payments need no schema change -- split_payments has no unique constraint on linked_transaction_id, so one transfer can carry a row per scope -- and that the 23 Apr Qantas booking is the flight to Bangkok starting a solo leg, which is why it stays out of this trip.	2026-07-28 12:59:53 +10:00
siddharthd	89300450a7	docs(shared): describe what was built, not what was proposed ci / lint-test (push) Successful in 50s Details The 2026-07-26 document was a proposal marked "nothing built". Everything it described as broken is now fixed, and the fix is not the one it proposed, so leaving it in place would misdescribe the system to whoever reads it next. Records what the code now does: settled as the single balance gate, settled and trip_id as orthogonal axes, settling up by recording a payment rather than flipping a flag, and the reasons duplicates are superseded rather than deleted. Keeps the loan design intact and clearly marked as still a proposal -- it was never built and nothing in this work touched it. Also records that the proposal's own recommendation not to restate history from the CSVs was overturned, and why it was wrong: it measured the value in balances, where it is nil, and missed it in spend, where it is $35,259.	2026-07-28 12:06:56 +10:00
siddharthd	b4a116c134	feat(scripts): import the matched split history as settled ci / lint-test (push) Successful in 46s Details Adds --write to the matcher. Wrote 1,242 split rows across 657 transactions. Imported settled, and that is the whole design. These obligations were discharged years ago on a platform we no longer run, and their residual is already carried by transaction 2348. Writing them unsettled would re-open roughly $40k of debts that were paid. ACTIVE_OBLIGATION keeps settled splits out of every owed figure while myShare/mySplitOf still count them, which is exactly the asymmetry this needs: the import exists to correct historical SPEND, not to move a balance. Effect: $35,259 leaves my historical spend -- $13,088 in 2024, $22,117 in 2025 -- because a $200 grocery shop that was always half hers no longer reads as $200 of mine. Balances are byte-identical before and after (Molina -1226.72/145, Sonu 5428.08/419), which is the assertion that matters. Shares are written as the CSV computed them, so a 50/50 row can land as 50.01/49.99. That is faithful rather than tidy; no transaction exceeds 100%. Rehearsed on the 37-row Rome file first (24 rows) and verified before the full run -- both the balances and one split read back through the API.	2026-07-28 12:02:57 +10:00
siddharthd	c9b000a428	feat(scripts): dry-run matcher for the SplitMyExpenses history ci / lint-test (push) Successful in 48s Details Matches the five CSV exports against transactions already in the ledger and reports what it would do. Writes nothing -- importing is a separate step, and rehearsing it first is what catches the defects that tests do not. What it found, and why the number is what it is: 676 of 1,536 shareable rows match (44%). The ceiling is ledger coverage, not matcher quality. The CSVs describe 678 shared expenses in 2024 alone; the ledger holds 591 rows for the whole of that year, 3 to 72 a month, which is far less than a household actually spends. Most 2024 CSV rows have no transaction to attach a split to and never will. South Korea April 2024 matches 4 of 158 for that reason. Three decisions are encoded deliberately: - Date format is decided per FILE, not per row. The household export writes D/M/YYYY and the four trip exports write ISO, and 474 rows parse validly under both readings -- per-row guessing silently swaps January and February for some rows and not others. - A person's column is their 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 -- the reading that would fake an arrangement change. - Matching is one-to-one, best pair first. The NZ trip has two identical $10.16 Uber rows against three ledger rows and four PayMyPark rows in the same shape; without this a ledger row is claimed repeatedly and the second CSV row looks matched while being unrepresented.	2026-07-28 12:00:18 +10:00
siddharthd	dbfbd5196d	fix(transactions): supersede rows imported twice instead of deleting them ci / lint-test (push) Successful in 48s Details Statements 107, 142 and 143 bill overlapping periods on one ANZ account, so 31 transactions -- $42,040.68 -- are in the ledger twice. They are marked superseded, not deleted. Every child of transactions is ON DELETE CASCADE (splits, tags, overrides, expense_metadata, order_reviews), so deleting "the duplicate" destroys whatever curation sits on it, and which member of a pair holds that curation is an accident of import order: here 1 pair carries splits and 6 carry overrides, all on the surviving side, but nothing guarantees that. Superseding keeps the row, keeps its children, and makes a mistake one UPDATE to undo rather than a restore from backup. reconciled_with_id could not be reused. Its predicate is scoped to statement_id IS NULL on purpose -- a statement line pointing at something else is the survivor, not the duplicate -- and here both rows are statement lines. The exclusion goes into EXCLUDE_RECONCILED_SOURCE rather than into a new fragment, so every query already asking "count each purchase once" gets it without being edited. The trip cost queries did not use that fragment at all and now do; verified a no-op on current data (0 trip-tagged rows are either reconciled sources or duplicates), but they were one import away from double-counting. Most of the $42k is transfers and investments, which spend already excludes. The damage was elsewhere: duplicated rows in the list, and rules re-splitting a duplicate -- txn 3807 is one of these 31 and was a candidate for splitting earlier today. Balances are unchanged: no duplicate carried a split.	2026-07-28 11:54:25 +10:00
siddharthd	d5589b2980	feat(statements): flag billing periods that overlap another statement ci / lint-test (push) Successful in 46s Details An account cannot be billed twice for the same day, so an overlap means those transactions are in the ledger twice. ANZ statements 107 and 143 overlap by 118 days and put roughly $42,000 of duplicate rows in; nothing anywhere said so. Two details decide whether this catches the real case: - Account numbers compare with non-digits stripped. The duplicate got in because the existing 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 ending the day the next starts, so inclusive bounds flagged 5 pairs of which 3 were consecutive and fine. Half-open leaves exactly the 2 real ones. NULL bounds are excluded rather than handed to daterange, where NULL means unbounded and an undated statement would overlap all of history. Detection only. It does not refuse the import or touch the duplicate rows -- cleaning those is separate, and must supersede rather than delete because every child of transactions is ON DELETE CASCADE and the curation sits on the duplicate side. Both subtleties have a test, and both fail if you undo them.	2026-07-28 11:46:45 +10:00
siddharthd	7a1acc32a9	feat(trips): say which direction a trip balance points ci / lint-test (push) Successful in 45s Details A participant who has overpaid a trip showed as "$-816.16" under a column headed "Outstanding on this trip". A negative outstanding reads as a bug rather than as "they are ahead", so the sign is now spelled out: magnitude plus one of all square / owes you / ahead — you owe them, coloured the same way Shared colours the same three states. Also corrects the footer, which had gone stale and was now simply false. It said settlement could not be computed per trip because payments carried no trip attribution. Migration 0022 added split_payments.trip_id and the figures above it have been net of trip-scoped payments since. What a reader needs to know is the opposite of what it said: household-tab payments are the ones NOT counted here.	2026-07-28 11:39:11 +10:00
siddharthd	e92fcb709f	docs(trips): say whose money the trip total counts ci / lint-test (push) Successful in 48s Details Total Spend is every payer's trip-tagged spending; the split figures directly below it are scoped to the owner. Two lenses on one screen read as one unless the card says which it is. The number is unchanged and deliberate -- a trip cost what the group put into it -- so this is a label, not a fix.	2026-07-28 11:30:37 +10:00
siddharthd	8c21893cc2	fix(trips): money that came back is not what the trip cost ci / lint-test (push) Successful in 1m28s Details Every trip figure filtered on transaction_type IN ('debit','fee','interest'), which drops refunds and credits outright. A partly-refunded booking therefore read at its full price and the refund subtracted nothing, anywhere: the headline total_spend, the category breakdown, the daily chart, top merchants and the tag breakdown were all gross. This is the same defect the general analytics fixed once already, which is why NET_SPEND_ROWS and SPEND_SIGNED exist -- a refunded Expedia purchase read as $2,888.92 of spend until they did. Trip analytics never adopted them. Doing so now costs one predicate and one expression per query. getTrips/getTripById needed the trips alias moved to `tr`: the fragments assume `t` is `transactions`, and hand-inlining a copy rather than renaming is exactly how the reconciled-row exclusion drifted out of the analytics routes before. On Europe 2026 this is $821.12 -- a LuxuryEscapes booking with two part-credits against it, and a FreeNow hold adjustment. Fully cancelled bookings are a different case and are handled by untagging both legs from the trip by hand, because a trip never incurred a cost it cancelled. No balance moves: the owed query already excludes credits and a refund carries no split. There is a test asserting exactly that, and it passes with or without this change -- it is a guard, not a proof. The three that do prove it fail without it.	2026-07-28 11:25:03 +10:00
siddharthd	4fcb135805	fix(trips): a trip figure must only count what the owner is owed ci / lint-test (push) Successful in 46s Details The per-trip owed number shipped in `689fadc` counted every split on every trip transaction regardless of who paid, so it silently mixed debts owed to different people under one label. On Europe 2026 that meant Molina "owed" $21,572.12, of which $1,605.49 was her share of rows Sonu paid for — a real debt, but between the other two participants, and one they had already settled directly (split_payments id 5, Molina -> Sonu, exactly $1,605.49). A participant's own share of a row they themselves paid for was in there too, which is nobody's debt at all. Both sides needed scoping, not just one: the owed side to rows this owner paid for, and the paid side to payments made to this owner. Scoping only the first would have let a Molina -> Sonu payment reduce what Molina owes the owner. The corrected figures reproduce a number derived independently, months of data apart: Molina now reads -$816.16 on Europe, matching her known overpayment to the cent ($19,966.63 of splits against $20,782.79 paid). Sonu goes from $8,793.10 to $1,084.61, and the owner correctly disappears from a list of people who owe the owner. Found by checking a household total against what the app had been showing all along — the query was gross, gave a number about twice the real one, and I had quoted it as "owed". Worth stating plainly: the defect was not in the number the app displayed, it was in the number I computed to explain it.	2026-07-27 23:40:58 +10:00
siddharthd	ff0629462c	fix(ci): generate the Prisma client before running tests ci / lint-test (push) Successful in 53s Details The pipeline has been red on every run since at least `ae0c34f`. npm ci installs dependencies but the Prisma client is generated into src/generated/prisma, which is gitignored — so a fresh CI checkout has no client and anything importing src/lib/db.ts fails with 'Cannot find package @/generated/prisma/client' before a single assertion runs. prisma generate reads only the schema, so it needs no database and no secrets. Worth noting why this went unnoticed for a dozen commits: a pipeline that is always red carries no signal, so it stopped being read.	2026-07-27 23:23:32 +10:00