# Shared expenses and settlement

Status: **built and live**, as of 2026-07-28. The loan section at the end is
still a proposal — nothing there is built.

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).

---

## The one rule

**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`.**

`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 |
|---|---|---|
| **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. |

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.

The predicate is `ACTIVE_OBLIGATION` in `src/lib/analytics-sql.ts`.

## Two orthogonal axes

`settled` and `trip_id` answer different questions and neither implies the
other:

- **`transaction_splits.settled`** — *is this obligation still live?*
- **`split_payments.trip_id`** — *which tab does this payment settle?*
  NULL means the ongoing household tab.

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.

## How settling up actually works

**By recording a payment.** There is deliberately no "mark settled" action
anywhere in the app.

`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 |
|---|---:|---:|---|
| `…emi` | 39 | $37,980.24 | Sonu's loan contribution |
| `…mummy…` | 6 | $29,721.24 | Molina's money, forwarded by Sonu |
| other Meghalee | 15 | $71,130.27 | Sonu's own settlements |

### 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 scope, and a
contribution must never settle a dinner.

### The share is 50/50 fixed, with the shortfall tracked

Not derived from actual payments, which fluctuate. Over 2025-07-01 → 2026-06-30:

| | |
|---|---:|
| Repayments | $63,500.00 |
| Sonu's 50% obligation | $31,750.00 |
| Actually contributed (26 payments) | $27,750.00 |
| **Shortfall** | **$4,000.00** |

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 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

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%.

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.

**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 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.
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.

## 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.

- **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.

  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.
