fix(trips): carry the currency and reconcile rules into the trip figure

a4ab543 landed six hours ago and this branch rewrote one of the queries it had just fixed, quietly dropping both of its guarantees. That commit made EXCLUDE_RECONCILED_SOURCE "one fragment both sides import" because an inlined copy is how the reconciled-row exclusion drifted out of the analytics routes and double-counted 48 rows / $4,474.79. The trip owed query here had hand-inlined its own copy — the fragment assumes the alias `t` and this query used `tx`, so the path of least resistance was to re-create exactly the divergence that was being removed. Aliased to `t` so the fragments apply directly. The same commit made balances count rows whose AUD value is unknown rather than netting a foreign figure against AUD ones. The trip figure had no equivalent — on the query where it matters most, because a trip is where foreign rows actually live. A Europe total silently mixing EUR into AUD is the whole failure that fix was written to prevent. The column header still read "Share of this trip" while the number is now net of payments, which is the same class of drift a4ab543 set out to fix. It reads "Outstanding on this trip", carries the approx/unconverted caveat the Shared cards use, and greys a settled zero.
feat(shared): give a payment a tab to settle
2026-07-27 23:15:28 +10:00 · 2026-07-27 23:12:05 +10:00 · 2026-07-27 17:10:27 +10:00 · 2026-07-27 11:52:33 +10:00 · 2026-07-27 11:43:55 +10:00 · 2026-07-27 11:33:37 +10:00
36 changed files with 3555 additions and 235 deletions
@@ -0,0 +1,34 @@
 # Repository Guidelines
 ## Project Structure & Module Organization
 Application code lives in `src/`. Next.js App Router pages and API route handlers belong in `src/app/`; reusable UI components are in `src/components/`; database access, query functions, hooks, authentication, and domain helpers are in `src/lib/`. Tests are separated into `src/__tests__/unit/` and `src/__tests__/integration/`. PostgreSQL schema and numbered SQL migrations live under `prisma/`, static assets under `public/`, operational scripts under `scripts/`, and design notes under `docs/`.
 Keep data flow consistent: API routes call query functions in `src/lib/queries.ts`, which use `queryRaw()` from `src/lib/db.ts`; client components access APIs through TanStack Query hooks in `src/lib/hooks.ts`.
 ## Build, Test, and Development Commands
 - `npm ci` installs the locked dependency set (Node 22 is used in CI).
 - `npm run dev` starts the local Next.js development server.
 - `npm run build` creates a production build; `npm start` serves it.
 - `npm run lint` runs the Next.js ESLint configuration. Existing lint debt makes CI lint advisory, but new code should pass.
 - `npm test` runs fast unit tests.
 - `npm run test:setup` prepares the PostgreSQL test database using `.env.test`.
 - `npm run test:integration` runs database-backed tests.
 - `npm run test:all` runs both test suites.
 ## Coding Style & Naming Conventions
 Use strict TypeScript, two-space indentation, semicolons, and double quotes, matching existing files. Name React components and types in PascalCase, functions and variables in camelCase, and files/routes in kebab-case. Use the `@/` alias for imports from `src/`. Preserve owner scoping and prefer transaction overrides with `COALESCE` in financial queries. Every API route must authenticate before accessing data.
 ## Testing Guidelines
 Vitest is the test framework. Name tests `*.test.ts` and place pure logic tests under `unit/`; put PostgreSQL-dependent behavior under `integration/`. Add regression coverage for query, rule, reconciliation, and category changes. No numeric coverage threshold is configured; focus on meaningful edge cases and run `npm run test:all` before submitting database-related changes.
 ## Database, Security & Configuration
 Add schema changes as the next numbered `prisma/migrations/NNNN_description/migration.sql`. Never commit `.env`, `.env.test`, raw statements in `dump/`, or other financial data. Consult `CLAUDE.md` and relevant `docs/` notes before changing splits, settlements, loans, reconciliation, or statement accounting.
 ## Commits & Pull Requests
 History follows concise Conventional Commit-style subjects such as `feat(rules): preview rule changes`, `fix(trips): ...`, and `docs: ...`. Keep commits focused. Pull requests should explain behavior and data-model impact, link related issues, list validation commands, and include screenshots for UI changes. Ensure unit tests and the production build pass; call out any known lint warnings or migration steps.
@@ -0,0 +1,715 @@
 # UI and information architecture review
 **Date:** 2026-07-26  
 **Status:** Priority 0 implemented 2026-07-27 (see below). Priorities 1–4 remain
 proposals.
 ## Implementation status — Priority 0 (2026-07-27)
 All six Priority 0 items landed, with three amendments found while verifying the
 proposals against the code:
 1. **Reconciled source rows** — the exclusion was missing from *all five*
   analytics routes, not only `/monthly`. It is now one fragment
   (`EXCLUDE_RECONCILED_SOURCE`) that `queries.ts` also imports, so the two
   halves cannot drift apart again. Real effect: 48 rows, **$4,474.79** of
   double-counted spend removed from every category total, mover, Pareto and
   merchant ranking.
 2. **Spend pace** — now served by `/api/analytics/daily`, built from the same
   fragments as the headline. Measured on live data, the old client-side series
   ended July at **$4,747.31** against a headline of **$3,597.10** — a 32%
   overstatement of the number directly above it.
 3. **Fees and interest** — bounded by an explicit period (default 12 months,
   `months=0` for all time), with the range shown and selectable. The unbounded
   figure was overstating the last 12 months by roughly **$2,700 of fees**.
 4. **Split-coverage warning** — *deliberately not implemented* (user decision,
   2026-07-27).
 5. **Shared foreign currency** — amended. The obvious fix, reading `s.currency`,
   would have mislabelled every order row as AUD, because an order receipt has
   no statement and carries its own currency. Sourcing is now
   `NATIVE_CURRENCY = COALESCE(s.currency, t.foreign_currency_code, 'AUD')`,
   whose COALESCE order keeps two opposite denomination conventions apart. Note
   this change is **latent on today's data**: no foreign transaction is
   currently split, so nothing on Shared looks different yet.
 6. **Partial-month comparisons** — the hero average, the top movers and the pace
   baseline now exclude the in-progress month, and compare through the same day
   of the month when the selected month is the current one.
 Also fixed while in here, both found by checking rather than by proposal:
 - **Every analytics window was a day early.** `toISOString()` on a local-midnight
  `Date` converts backwards through UTC in any timezone east of Greenwich. Now
  `toDateStr()`. This was pre-existing in `/monthly` and `/merchants`.
 - **Rounding grain.** `/monthly` rounded per category and `/daily` per
  category-day, so the pace chart ended the month a few cents off its own
  headline. Both now carry 4dp and round once, at display.
 Guarded by `src/__tests__/integration/analytics-sql.test.ts`.
 The doc's characterisation of `REGULAR_CATEGORIES` (Insights section) is also
 slightly off: the set has 13 members including rent, utilities, insurance and
 subscriptions, not the 8 listed. The case for replacing it stands — a flat
 binary cannot express obligation — but that is the reason, not arbitrary
 membership. Note too that the proposed Fixed/Essential/Lifestyle model needs a
 commitment dimension that does not exist yet: `fees` cannot be split into
 avoidable versus known-annual, `subscriptions` cannot be split into contractual
 versus cancellable, and the contracted loan repayment is not in the spend stream
 at all (`SPEND_BASE` keeps only the interest portion). That is a data-model
 change, not an Insights rework.
 ## Executive summary
 The July 19 UI refresh gave the app a cohesive and distinctive visual identity.
 The ink-and-copper palette, typography, financial number treatment, month spine,
 and transaction drill-downs are all strong foundations.
 The larger remaining issue is not appearance. It is information hierarchy.
 Analytics and Insights contain useful data, but they are reporting-heavy rather
 than decision-oriented. Shared communicates the immediate running balance, but
 the current settlement model prevents it from answering which expenses a payment
 settled, whether a trip is closed, or how the shared loan should be represented.
 The product should make four questions easy to answer:
 1. Am I financially okay?
 2. What changed and why?
 3. What needs my attention?
 4. Who owes what, and for which expenses?
 Today there is no single page that answers the first three. The app opens on
 Transactions and presents ten equally weighted navigation items.
 The recommended direction is:
 - Add an Overview as the default landing page.
 - Keep Analytics focused on historical exploration: **what happened?**
 - Rebuild Insights around decisions and attention: **what should I know or do?**
 - Rebuild Shared around settlement contexts: **who owes what, and why?**
 - Keep the shared loan as a separate ledger from shared consumption expenses.
 - Fix calculation and coverage inconsistencies before adding more visualisations.
 ## Context reviewed
 This review covered:
 - The current Next.js pages and shared components.
 - Analytics SQL and API calculations.
 - Shared-expense balance and transaction queries.
 - `CLAUDE.md`.
 - `docs/shared-expenses-design.md`.
 - `docs/expense-baseline.md`.
 - Recent repository history.
 - Recent finance-app memories retrieved from OpenViking.
 The OpenViking history confirmed:
 - The July 19 redesign intentionally introduced the ink-and-copper theme,
  Fraunces display type, month-spine navigation, top movers, category
  sparklines, and heat-tinted ledger tables.
 - The user prefers a modern, high-fidelity interface and actionable analytics.
 - Later July 25–26 work changed the financial meaning under those screens:
  split-aware personal spend, AUD-aware settlement, refund netting, loan
  principal/interest separation, rule previews, and the proposed contextual
  settlement model.
 - The preferred settlement model links payments to real transactions, separates
  Household, Trip, and Historical contexts, and keeps the shared loan separate.
 ## What already works
 ### Visual system
 - The dark ink-and-copper theme is coherent and distinctive.
 - Serif headings and mono financial figures create useful hierarchy.
 - The copper accent is used consistently for selection and emphasis.
 - The design feels like one application rather than a collection of unrelated
  pages.
 ### Analytics interactions
 - The month spine is an effective year-at-a-glance navigation control.
 - “What changed” is more useful than a generic category chart.
 - Category sparklines make direction visible without creating a large
  multi-series chart.
 - Category rows can be expanded into their transactions.
 - Inline recategorisation allows users to correct the data while investigating
  it.
 ### Shared workflow
 - “Owes you,” “you owe,” and “all square” communicate the immediate relationship
  balance clearly.
 - Payment history is preserved rather than reducing settlement to a boolean.
 - Participant and tag filters support practical investigation.
 - Split transactions can be edited without returning to the main transaction
  page.
 ## App-wide information architecture
 ### Current problem
 The app redirects `/` to `/transactions`. This makes the operational ledger the
 default product surface. Transactions are important, but they do not tell the
 user whether anything needs attention or what the current financial position
 means.
 The sidebar also gives equal weight to:
 - operational screens such as Reconcile;
 - analytical screens such as Analytics;
 - configuration screens such as Rules;
 - organisational screens such as Tags.
 This makes the product feel like a database administration interface even when
 the individual pages are well designed.
 The `/budget` route is labelled Analytics in navigation. This is a leftover from
 an older product concept and should become `/analytics`.
 ### Recommended navigation
 Group navigation by intent:
 **Overview**
 - Overview
 **Money**
 - Transactions
 - Statements
 - Reconcile
 **Understand**
 - Analytics
 - Insights
 - Merchants
 **Shared**
 - Shared
 - Trips
 - Loan
 **Organise**
 - Tags
 - Rules
 Lower-frequency configuration items can be visually separated or collapsed.
 ### Recommended Overview
 The default landing page should be a concise status and attention surface, not
 another full analytics dashboard.
 Suggested structure:
 1. **This month**
   - Personal spend to date
   - Expected baseline at this point in the month
   - Income
   - Net cash
 2. **Financial resilience**
   - Realistic monthly baseline
   - Cash coverage in months
   - Redraw shown separately from cash
 3. **Needs attention**
   - Uncategorised or `other` transactions
   - Unreconciled transactions
   - Statements failing balance assertions
   - New or unusual recurring charges
   - Shared expenses added since the last settlement
 4. **Shared**
   - Current balances by person and context
   - Loan contribution shortfall shown separately
 5. **Recent change**
   - The two or three categories that explain the largest movement
 The Overview should link into Analytics, Insights, Shared, and Reconcile rather
 than reproduce their complete tables.
 ## Analytics review
 ### What the current page does
 The current Analytics page includes:
 - selected-month spend hero;
 - twelve-month month spine;
 - income, expenses, invested, and net-cash strip;
 - top category movers;
 - eight category sparkline cards;
 - spend-concentration Pareto chart;
 - cumulative spend pace;
 - expandable category table;
 - six-month heat-tinted category ledger.
 Each component is defensible in isolation. Together, they create too many
 competing summaries of the same category data.
 ### What Analytics should answer
 Analytics should answer:
 > What happened during this period, how does it compare, and what explains the
 > difference?
 Recommended primary structure:
 1. Period and comparison controls.
 2. Personal spend, income, invested, and net cash.
 3. Explanation of the change versus the selected comparison.
 4. One main category/trend visualisation.
 5. Category breakdown with transaction drill-down.
 6. An optional Explore section for detailed tables.
 ### Recommended removals and consolidation
 - Keep either category sparklines or the six-month ledger as the primary
  category-trend representation, not both.
 - Move the Pareto chart behind an Explore section. It describes concentration
  but rarely produces an immediate decision.
 - Retain “What changed,” but make each item clickable and explain which
  transactions caused the movement.
 - Avoid comparing a partial current month with full prior months unless values
  are projected or compared through the same day.
 - Add gross-versus-personal-share switching only if it is clearly labelled.
  Personal share should remain the default.
 ### Calculation and trust issues
 #### Reconciled source rows can be double-counted
 `/api/analytics/monthly` does not currently exclude manual source rows where
 `reconciled_with_id IS NOT NULL`. The baseline analysis identified 48
 double-counted rows.
 The analytics query should apply the same reconciled-row exclusion used by the
 main transaction queries.
 #### Spend pace compares unlike numbers
 The Analytics headline uses:
 - split-adjusted personal share;
 - fees and interest;
 - refund and credit netting;
 - loan interest rather than principal;
 - non-spend-category exclusions.
 The cumulative spend-pace chart uses only `transaction_type === "debit"` and
 adds gross `amount_aud ?? amount`. It does not use personal share and does not
 apply the same refund, fee, interest, or loan semantics.
 The chart can therefore disagree with the headline while both appear to
 represent “spend.” The cumulative series should be produced by the same
 server-side spend semantics as the monthly total.
 #### Split coverage changes mid-series
 Reliable in-app split data begins on 2026-01-09. A trailing twelve-month personal
 series currently combines older gross spending with newer split-adjusted
 spending.
 Until historical splits are restored:
 - default personal trend analysis to February–June 2026;
 - visibly mark periods with incomplete split coverage; or
 - offer gross-only twelve-month comparison separately.
 Do not present the mixed series as one comparable personal-spend trend.
 #### Comparison baseline is too naive
 The selected month is compared against the average of all other months with
 data. That average can include travel, annual fees, tax payments, incomplete
 current periods, and months with incompatible split coverage.
 Better comparison choices:
 - previous month;
 - same month last year;
 - median of comparable complete months;
 - recurring baseline;
 - user-selected comparison.
 ### Data trust indicator
 Analytics should include a compact methodology and coverage indicator:
 > Personal share · refunds netted · investments excluded · split coverage
 > reliable from Feb 2026 · 12 transactions need classification
 This makes the meaning of the numbers inspectable without overwhelming the page.
 ## Insights review
 ### Current problem
 The current Insights page contains:
 - Regular versus occasional spending;
 - another monthly category breakdown;
 - recurring charges;
 - fees and interest.
 The monthly breakdown duplicates Analytics. The page does not yet surface the
 most decision-relevant findings already known from the data: sustainable monthly
 cost, liquidity, the loan-overpayment lever, data-quality weaknesses, or unusual
 changes requiring attention.
 ### “Regular” is not the same as committed or essential
 `REGULAR_CATEGORIES` includes:
 - groceries;
 - dining;
 - transport;
 - health;
 - personal care;
 - government;
 - charity;
 - pets.
 These may recur, but they have very different flexibility and obligation.
 “Regular” describes transaction behaviour, not financial necessity.
 The current chart therefore cannot answer:
 - What is the minimum monthly cost?
 - What can be cut?
 - What is contractually committed?
 - What is lifestyle spending?
 - What is a one-off?
 ### Recommended model
 Replace Regular versus Occasional with:
 1. **Fixed commitments**
   - Contracted loan repayment
   - Insurance
   - Rates and registration
   - Known annual fees
   - Contractual subscriptions
 2. **Essential variable spending**
   - Utilities
   - Groceries
   - Transport
   - Health
 3. **Lifestyle and discretionary**
   - Dining
   - Shopping
   - Entertainment
   - Personal care
 4. **One-offs and travel**
 5. **Investments and transfers**
   - Shown for cashflow context, excluded from spending
 This should support scenario views rather than claiming there is one true
 baseline.
 ### Recommended Insights structure
 #### 1. Financial baseline
 Show the scenarios already established by the expense-baseline analysis:
 - Survival: contracted loan repayment and essentials only.
 - Realistic: contracted loan repayment plus ordinary dining and charity.
 - Status quo: current loan overpayment and normal life excluding travel.
 For each scenario show:
 - monthly amount;
 - six-month reserve;
 - twelve-month reserve.
 #### 2. Liquidity and resilience
 Show:
 - cash available;
 - redraw available separately;
 - months covered under each baseline;
 - a warning that redraw is lender-controlled and not equivalent to cash.
 #### 3. Biggest flexible levers
 Examples:
 - voluntary loan overpayment;
 - dining;
 - shopping;
 - subscriptions;
 - travel.
 The loan should always show both the contracted floor and actual repayment.
 #### 4. Attention and anomalies
 Examples:
 - a new recurring charge;
 - a charge larger than its prior range;
 - a category materially above baseline;
 - a fee increase;
 - an unexpected incoming credit categorised as spend;
 - a merchant still classified as `other`;
 - an investment incorrectly counted as spending.
 Each insight should link directly to the affected transactions.
 #### 5. Data-quality work queue
 The baseline analysis found that data quality is currently a larger blocker than
 visualisation:
 - `other` remains a large unresolved category;
 - Raiz, Vanguard Super, and moomoo need investment classification;
 - incoming `other` credits can make spending negative;
 - `government` conflates tax with rates and registration;
 - annual fees distort short-window monthly averages.
 Insights should make these visible as fixable tasks.
 ### Recurring charges
 The current detector identifies merchants with regular transaction intervals.
 That does not necessarily mean a subscription or commitment. Weekly grocery
 shopping can look recurring.
 Recommended changes:
 - Rename the section **Recurring patterns** unless contractual charges can be
  distinguished.
 - Show confidence and the basis for classification.
 - Show the next expected charge date.
 - Separate likely subscriptions from recurring merchants.
 - Allow dismissing or confirming a detected pattern.
 - Highlight price changes.
 - Collapse inactive patterns by default.
 The current eight-column table is also too wide for a primary page. Put secondary
 fields such as first seen, total paid, and count into an expandable detail row.
 ### Fees and interest
 The current fees query aggregates statement summary values across all available
 statements without a date filter. The UI does not label the period, so the total
 looks like a current-period figure even though it is effectively lifetime to
 date.
 Recommended presentation:
 - Explicit date range.
 - Avoidable fees.
 - Known annual fees.
 - Credit-card interest.
 - Loan interest.
 - Change versus prior comparable period.
 - Drill-down transactions.
 Loan interest should remain spending, but appear under fixed or
 non-discretionary costs rather than being hidden.
 ## Shared review
 ### What the current page answers well
 The unfiltered balance cards correctly implement a running ledger:
 > splits minus payments
 This is coherent and should not be changed to exclude splits marked `settled`.
 The `transaction_splits.settled` field is dead data and must not be used for new
 UI claims.
 ### What the current model cannot answer
 - Which split expenses did a payment settle?
 - Is a particular trip settled?
 - Can a trip be closed without closing Household?
 - Is an imported offset-account credit already represented by a manual payment?
 - What remains open inside one settlement context?
 - How should the shared-loan contribution shortfall be shown?
 The page should not imply answers that the data model cannot support.
 ### Tag-filtered balance cards are semantically misleading
 When a tag filter is active, participant balance queries intentionally stop
 subtracting payments because payments are not attributable to a tag. The cards
 then show raw split totals for the tag.
 This behavior is explained in small text, but the card still says “owes you” or
 “you owe.” That looks like a real payable balance when it is not.
 When filtered, relabel the cards:
 > Split total in Europe 2026
 Do not show payment or settlement actions from that state.
 ### Recommended settlement-context design
 Use explicit settlement contexts:
 - Household
 - Individual trips
 - Historical / Pre-2026
 - Closed contexts
 Recommended Shared navigation:
 - All
 - Household
 - Trips
 - Closed
 Within a context show:
 1. Net balance and direction.
 2. Expenses added since the last settlement.
 3. Payments attributed to that context.
 4. A chronological activity ledger combining expenses and payments.
 5. Context status: running, ready to settle, or closed.
 6. Settlement action.
 ### Payments should link to transactions
 An offset-account credit and a manual `split_payments` row can represent the same
 money. The page should:
 - propose matching an imported credit to a settlement;
 - display the linked transaction;
 - prevent silent duplication;
 - allow a manual payment only when no matching transaction exists.
 “Record Payment” should become a context-aware settlement flow:
 1. Choose what is being settled.
 2. Match an existing incoming transaction where possible.
 3. Confirm amount and residual balance.
 4. Preserve an auditable history.
 ### Shared transaction table
 The table currently shows raw `tx.amount` with a dollar sign and no currency
 indicator. Participant balances correctly convert to AUD.
 For foreign transactions, show:
 - the native amount and currency;
 - the AUD equivalent;
 - splits based on the AUD settlement amount.
 This prevents a visible mismatch between transaction rows and participant
 balances.
 ### Shared loan
 The loan is not a shared-expense settlement context. It funds an asset rather
 than consumption, and a loan contribution must never settle a dinner or utility
 bill.
 Give it a separate page or clearly separated ledger showing:
 - expected contribution by period;
 - actual contribution;
 - running shortfall or receivable;
 - principal reduction;
 - interest expense;
 - contracted repayment;
 - actual repayment;
 - voluntary overpayment;
 - redraw movement.
 The partner obligation is a fixed 50% of the repayment schedule, not a percentage
 inferred from actual contributions.
 ## Responsive and interaction improvements
 - Replace wide eight-column primary tables with compact rows and expandable
  details.
 - Keep financial summaries readable at mobile widths without horizontal
  scrolling.
 - Add explicit loading skeletons rather than only text.
 - Add error states for failed analytics requests.
 - Ensure chart meaning is not conveyed by colour alone.
 - Give interactive chart regions keyboard-accessible equivalents.
 - Confirm material deletions, including payment-history deletion.
 - Make expandable table rows use buttons with appropriate accessibility state.
 - Use consistent labels for personal share, gross amount, native currency, and
  AUD equivalent.
 ## Recommended implementation order
 ### Priority 0 — metric integrity
 1. Exclude reconciled source rows from monthly analytics.
 2. Make spend pace use the same spend semantics as the headline.
 3. Add date ranges to fees and interest.
 4. Add split-coverage warnings to historical personal-share analysis.
 5. Fix Shared foreign-currency presentation.
 6. Avoid partial-month versus full-month comparisons.
 ### Priority 1 — product hierarchy
 1. Add Overview and make it the default route.
 2. Group sidebar navigation by user intent.
 3. Rename `/budget` to `/analytics`.
 4. Add consistent methodology and coverage indicators.
 ### Priority 2 — Analytics and Insights
 1. Simplify Analytics around period, comparison, change explanation, trend, and
   drill-down.
 2. Remove the duplicate monthly breakdown from Insights.
 3. Add baseline scenarios and liquidity coverage.
 4. Add flexible-spending levers, anomalies, and a data-quality work queue.
 5. Rework recurring patterns and fees into decision-oriented summaries.
 ### Priority 3 — Shared
 1. Add settlement contexts.
 2. Link payments to real transactions.
 3. Add context activity ledgers and closeable trip contexts.
 4. Introduce the separate loan contribution ledger.
 5. Backfill historical closed-context splits so long-range personal analytics
   become comparable.
 ### Priority 4 — polish
 1. Improve mobile layouts.
 2. Add accessibility semantics.
 3. Add richer loading, error, and empty states.
 4. Consolidate repeated card, table, filter, and page-header patterns into shared
   components.
 ## Proposed success criteria
 The redesign is successful when:
 - The first page explains current status and outstanding actions without opening
  multiple screens.
 - Analytics can explain why one comparable period differs from another.
 - Insights identifies baseline cost, financial resilience, flexible levers, and
  data-quality problems.
 - Every displayed total states or clearly implies its period and whether it is
  gross or personal share.
 - Historical charts do not silently combine incompatible split coverage.
 - Shared can distinguish Household, Trip, and Historical balances.
 - A settlement can be traced to both the obligation it reduces and the real
  transaction representing the payment.
 - Loan contributions cannot affect ordinary shared-expense balances.
@@ -0,0 +1,39 @@
 -- Order provenance: which platform the receipt came from, and the message it
 -- came from.
 --
 -- The parser has always known the platform (it has to, to read the template)
 -- and then threw it away. Without it a transaction reads "Order - Burger
 -- Corner" with no way to tell whether to look in DoorDash or Uber Eats for the
 -- detail, and no way to answer "how much of this is DoorDash?" at all.
 --
 -- `source_email_subject` / `source_email_from` already existed for the
 -- Paperless expense path and were simply never populated by order ingestion.
 ALTER TABLE expense_metadata ADD COLUMN IF NOT EXISTS platform text;
 COMMENT ON COLUMN expense_metadata.platform IS
  'doordash | ubereats | uber — the receipt template the order was read from.';
 -- Backfill the 101 rows written by the 2026-07-27 backfill. DoorDash receipts
 -- carry no order id of their own, so ingestion synthesises `msg:<message-id>`;
 -- Uber receipts carry a real trip UUID. That is the only surviving
 -- discriminator, and it is exact.
 UPDATE expense_metadata
   SET platform = CASE WHEN order_reference LIKE 'msg:%' THEN 'doordash' ELSE 'ubereats' END
 WHERE platform IS NULL
   AND source = 'email'
   AND paperless_doc_id IS NULL   -- exclude the Paperless expense path
   AND order_reference IS NOT NULL;
 -- Pick-up / delivery stops, as the receipt prints them. Uber puts these on
 -- every order under `Order details`; DoorDash prints no addresses at all, so
 -- this stays '[]' there. Same block a *trip* receipt uses for start and
 -- destination, so this column already fits rides when they come into scope.
 ALTER TABLE expense_metadata ADD COLUMN IF NOT EXISTS route jsonb NOT NULL DEFAULT '[]'::jsonb;
 COMMENT ON COLUMN expense_metadata.route IS
  'Uber only: [{label, time, address}] — pick-up and delivery stops as printed.';
 CREATE INDEX IF NOT EXISTS idx_expense_metadata_platform
    ON expense_metadata (platform)
 WHERE platform IS NOT NULL;
@@ -0,0 +1,48 @@
 -- Settlement scope: which tab a payment settles.
 --
 -- `split_payments` has carried from/to/amount/date since it was written and
 -- nothing else. That is the whole reason a per-trip balance has never been
 -- computable — `getTripAnalytics` says so in a comment where the figure should
 -- be: "split_payments carries no trip attribution, so a payment cannot be
 -- assigned to a trip. Settlement is a property of the whole relationship."
 --
 -- It is also the reason the Shared page silently drops payments the moment a
 -- tag filter is applied (`getParticipantBalances`): with one global payments
 -- pool there is no honest way to show a filtered balance, so it showed gross
 -- splits under the same label instead. A tag is a view; a scope is a ledger.
 --
 -- The scope is a *trip*, not a new `settlement_contexts` table. `trips` already
 -- has owner_id, dates and an archived flag, and `transaction_overrides.trip_id`
 -- already decides which transactions belong to it. A second grouping beside it
 -- would be two unsynchronised scopes over the same rows — a trip could hold a
 -- mix of contexts and a context could span trips, with no invariant saying
 -- which one governs.
 --
 -- NULL means the ongoing household tab. That tab never closes, which is why
 -- this is nullable rather than defaulted to some "general" row: absence is the
 -- honest representation of "not attached to a trip", and it keeps every
 -- existing payment correct without a backfill.
 ALTER TABLE split_payments
  ADD COLUMN IF NOT EXISTS trip_id integer REFERENCES trips(id) ON DELETE SET NULL;
 COMMENT ON COLUMN split_payments.trip_id IS
  'The trip this payment settles. NULL = the ongoing household tab.';
 CREATE INDEX IF NOT EXISTS idx_split_payments_trip
    ON split_payments (trip_id)
 WHERE trip_id IS NOT NULL;
 -- `settled` answers a different question and the two must not be collapsed:
 -- trip_id is *which tab*, settled is *is this obligation still live*. A
 -- pre-2026 historical split is settled with no tab; a Europe split becomes
 -- settled when Europe's payment lands; a household split stays unsettled and
 -- open indefinitely.
 --
 -- Nothing writes `settled` today. The comment in queries.ts claims
 -- /api/splits/settle does — that route does not exist, and the column is false
 -- on all 1,279 rows, which is why every trip has always reported 100%
 -- unsettled including trips paid in full.
 COMMENT ON COLUMN transaction_splits.settled IS
  'Obligation discharged. Excluded from owed figures; still counted in spend analytics.';
@@ -18,6 +18,7 @@ model trips {
  archived    Boolean                 @default(false)
  created_at  DateTime                @default(now())
  overrides   transaction_overrides[]
  payments    split_payments[]
 }
 model transaction_overrides {
@@ -75,9 +76,13 @@ model split_payments {
  payment_date          DateTime      @db.Date
  notes                 String?
  linked_transaction_id Int?
  trip_id               Int?
  created_at            DateTime      @default(now())
  from_participant      participants  @relation("payments_from", fields: [from_participant_id], references: [id])
  to_participant        participants  @relation("payments_to", fields: [to_participant_id], references: [id])
  trip                  trips?        @relation(fields: [trip_id], references: [id], onDelete: SetNull)
  @@index([trip_id])
 }
 model tags {
@@ -0,0 +1,20 @@
 {
  "ut-00": {
    "messageId": "AQMkADAwATM0MDAAMS1hYzQyLTVjZDktMDACLTAwCgBGAAADS6kDcxBxKkKwbCK1wU1QlwcA7Ex_hlaTTU0AgHQ16optl_AAAAIBDAAAAOxMfoZWk01NAIB0NeqKbZfgAAkH8zVAAAAA",
    "subject": "Your Sunday evening trip with Uber",
    "receivedAt": "2026-06-21T10:11:16Z",
    "sender": "noreply@uber.com"
  },
  "ut-01": {
    "messageId": "AQMkADAwATM0MDAAMS1hYzQyLTVjZDktMDACLTAwCgBGAAADS6kDcxBxKkKwbCK1wU1QlwcA7Ex_hlaTTU0AgHQ16optl_AAAAIBDAAAAOxMfoZWk01NAIB0NeqKbZfgAAkH8zU-AAAA",
    "subject": "Your Sunday morning trip with Uber",
    "receivedAt": "2026-06-21T09:02:09Z",
    "sender": "noreply@uber.com"
  },
  "ut-summary": {
    "messageId": "AQMkADAwATM0MDAAMS1hYzQyLTVjZDktMDACLTAwCgBGAAADS6kDcxBxKkKwbCK1wU1QlwcA7Ex_hlaTTU0AgHQ16optl_AAAAIBDAAAAOxMfoZWk01NAIB0NeqKbZfgAAkH8zU3AAAA",
    "subject": "Your Sunday morning trip with Uber",
    "receivedAt": "2026-06-20T22:27:54Z",
    "sender": "noreply@uber.com"
  }
 }
@@ -0,0 +1,126 @@
 import { describe, it, expect } from "vitest";
 import { queryRaw, queryRow } from "../../lib/db";
 import {
  EXCLUDE_RECONCILED_SOURCE,
  NATIVE_CURRENCY,
  AMOUNT_UNCONVERTED,
 } from "../../lib/analytics-sql";
 /**
 * These fragments are the ones that drifted.
 *
 * The reconciled-row exclusion lived only in `queries.ts` for months while every
 * analytics route counted the superseded manual rows as spend — 48 rows, $4,474
 * of double count, invisible because the transaction list (which did exclude
 * them) looked right. The currency expression has the same shape of risk: it is
 * read by two call sites with two different denomination conventions.
 *
 * Assertions are per-row against known fixtures rather than against aggregate
 * totals, so a change in unrelated data cannot mask a regression.
 */
 async function scratchTxn(cols: string, vals: string, params: unknown[] = []) {
  const row = await queryRow<{ id: number }>(
    `INSERT INTO transactions (${cols}) VALUES (${vals}) RETURNING id`,
    params
  );
  return row!.id;
 }
 /** Does this row survive the predicate? */
 async function passes(predicate: string, id: number): Promise<boolean> {
  const rows = await queryRaw(
    `SELECT t.id FROM transactions t
       LEFT JOIN statements s ON s.id = t.statement_id
      WHERE t.id = $1 AND (${predicate})`,
    [id]
  );
  return rows.length === 1;
 }
 describe("EXCLUDE_RECONCILED_SOURCE", () => {
  it("drops a manual row that a statement line has superseded", async () => {
    const survivor = await scratchTxn(
      "transaction_date, description, amount, transaction_type",
      "'2026-03-01','Analytics fixture — survivor', 10.00, 'debit'"
    );
    const superseded = await scratchTxn(
      "transaction_date, description, amount, transaction_type, reconciled_with_id",
      "'2026-03-01','Analytics fixture — superseded', 10.00, 'debit', $1",
      [survivor]
    );
    expect(await passes(EXCLUDE_RECONCILED_SOURCE, superseded)).toBe(false);
  });
  it("keeps an ordinary manual row that was never reconciled", async () => {
    const id = await scratchTxn(
      "transaction_date, description, amount, transaction_type",
      "'2026-03-01','Analytics fixture — unreconciled', 10.00, 'debit'"
    );
    expect(await passes(EXCLUDE_RECONCILED_SOURCE, id)).toBe(true);
  });
  it("keeps a credits order row — nothing ever sets reconciled_with_id on one", async () => {
    // The order slice records the card match in expense_metadata, not on the
    // transaction, and needsCardMatch() holds these out of the reconcile queue.
    // If that ever changes, this exclusion would start eating real spend.
    const id = await scratchTxn(
      "transaction_date, description, amount, transaction_type, payment_method",
      "'2026-03-01','Order - Analytics fixture', 25.00, 'debit', 'credits'"
    );
    expect(await passes(EXCLUDE_RECONCILED_SOURCE, id)).toBe(true);
  });
 });
 describe("NATIVE_CURRENCY", () => {
  async function currencyOf(id: number) {
    const row = await queryRow<{ ccy: string; unconverted: boolean }>(
      `SELECT ${NATIVE_CURRENCY} AS ccy, ${AMOUNT_UNCONVERTED} AS unconverted
         FROM transactions t LEFT JOIN statements s ON s.id = t.statement_id
        WHERE t.id = $1`,
      [id]
    );
    return row!;
  }
  it("reads a statement-less order row's own currency, not 'AUD'", async () => {
    // The bug this guards: sourcing currency from s.currency alone labelled
    // every foreign order row AUD, because an order has no statement.
    const id = await scratchTxn(
      "transaction_date, description, amount, transaction_type, payment_method, foreign_currency_amount, foreign_currency_code",
      "'2026-03-01','Order - Foreign fixture', 3500.00, 'debit', 'credits', 3500.00, 'LKR'"
    );
    const { ccy, unconverted } = await currencyOf(id);
    expect(ccy).toBe("LKR");
    // No amount_aud: the ingest path refuses to assert an FX rate it lacks, so
    // this row's AUD value is genuinely unknown and must be reported as such.
    expect(unconverted).toBe(true);
  });
  it("prefers the statement's currency over the foreign-charge record", async () => {
    // Opposite convention: on an AUD statement, `amount` is AUD and
    // foreign_currency_code merely notes what was originally charged. Reading
    // the foreign code here would mislabel an AUD row as USD.
    const stmt = await queryRow<{ id: number }>(
      `INSERT INTO statements (bank_name, account_number, billing_end_date, currency, filename)
       VALUES ('Analytics Fixture Bank','0000','2026-03-31','AUD','analytics-fixture.pdf') RETURNING id`
    );
    const id = await scratchTxn(
      "transaction_date, description, amount, amount_aud, transaction_type, statement_id, foreign_currency_amount, foreign_currency_code",
      "'2026-03-01','Overseas purchase fixture', 45.00, 45.00, 'debit', $1, 30.00, 'USD'",
      [stmt!.id]
    );
    const { ccy, unconverted } = await currencyOf(id);
    expect(ccy).toBe("AUD");
    expect(unconverted).toBe(false);
  });
  it("defaults a plain manual row to AUD", async () => {
    const id = await scratchTxn(
      "transaction_date, description, amount, transaction_type",
      "'2026-03-01','Plain manual fixture', 12.00, 'debit'"
    );
    expect((await currencyOf(id)).ccy).toBe("AUD");
  });
 });
@@ -35,6 +35,7 @@ export async function resetDB(pool: Pool) {
      transactions,
      statements,
      tags,
      trips,
      participants
    RESTART IDENTITY CASCADE
  `);
@@ -9,11 +9,11 @@ import {
  reconcilePendingOrders,
  parseOrderAmendment,
  applyOrderAmendment,
  OrderParseError,
  NotAReceiptError,
  type MessageMeta,
 } from "../../lib/order-ingestion";
 import { EXCLUDE_NON_SPEND } from "../../lib/analytics-sql";
 import { bankLabel, needsCardMatch } from "../../lib/queries";
 /**
 * These run against REAL captured receipts, not synthetic fixtures. The earlier
@@ -142,6 +142,19 @@ describe("Order ingestion — invariants", () => {
  beforeEach(async () => {
    await queryRaw(`DELETE FROM expense_metadata WHERE source = 'email'`);
    await queryRaw(`DELETE FROM transactions WHERE description LIKE 'Order - %'`);
    // The statement fixtures these tests insert survived into the next run, and
    // reconcileCardLeg matched a leftover charge at ingest time — so an order
    // meant to park "awaiting_card_statement" resolved immediately instead.
    // That is the whole story behind the intermittent failure in "parks an
    // unresolvable split": not a race, just fixtures that were never cleaned.
    // Must happen BEFORE ingest, which is why cleaning up at the end of the
    // test was not enough.
    await queryRaw(
      `DELETE FROM statements WHERE filename IN ('test-westpac-2026-03.pdf', 'panel-cba.pdf', 'panel-plain.pdf')`
    );
    await queryRaw(
      `DELETE FROM transactions WHERE description IN ('DD *DOORDASH WOOLWORTHS MELBOURNE AUS', 'UBER *EATS ZURICH')`
    );
  });
  it("I6: a credits order creates one transaction at face value", async () => {
@@ -164,7 +177,7 @@ describe("Order ingestion — invariants", () => {
    expect(b.skipped).toBe("already_ingested");
    expect(b.metadataId).toBe(a.metadataId);
    const n = await queryRow<{ c: string }>(
-      `SELECT count(*)::text c FROM transactions WHERE description = 'Order - Mad Mex'`
+      `SELECT count(*)::text c FROM transactions WHERE description = 'Order - Mad Mex (DoorDash)'`
    );
    expect(Number(n!.c)).toBe(1);
  });
@@ -181,14 +194,22 @@ describe("Order ingestion — invariants", () => {
    ).rejects.toThrow();
  });
-  it("I11: a [Family] order is imported, tagged, and excluded from spend", async () => {
+  it("I11: a [Family] order records provenance and creates no transaction", async () => {
    // Requirement was "import them but tag so they're excluded from budgets".
    // Correct mechanism: the CARD statement line is the transaction and carries
    // the family tag. Creating a second, credits-flavoured row duplicated it.
    const p = parseOrderHTML(
      html("ue-04"),
      meta({ subject: "[Family] Your Tuesday evening order with Uber Eats", sender: "uber.com", receivedAt: "2026-07-07T10:08:00Z" })
    );
    const res = await processOrderIngestion(p);
-    expect(res.transactionId).not.toBeNull();
+    expect(res.transactionId).toBeNull();
-    expect(res.flags).toContain("family_payment_assumed_credits");
+    expect(res.flags).toContain("family_card_settled_no_transaction");
    const meta_ = await queryRow<{ currency: string }>(
      `SELECT currency FROM expense_metadata WHERE id = $1`, [res.metadataId]
    );
    expect(meta_!.currency).toBe("LKR");
  });
  it("a foreign-currency order records the original amount and code", async () => {
@@ -227,8 +248,11 @@ describe("Order ingestion — invariants", () => {
    );
    const out = await reconcilePendingOrders();
-    expect(out.resolved).toBeGreaterThanOrEqual(1);
+    // Deliberately not asserting global counts: reconcilePendingOrders() scans
-    expect(out.created).toBeGreaterThanOrEqual(1);
+    // every pending row in the database, so another test's leftovers change the
    // totals. Assert on THIS order's outcome instead — that is what the test is
    // actually about, and it does not depend on what else is in the table.
    expect(out.examined).toBeGreaterThanOrEqual(1);
    const credits = await queryRow<{ amount: string }>(
      `SELECT t.amount::text FROM transactions t
@@ -240,9 +264,12 @@ describe("Order ingestion — invariants", () => {
  });
  it("reconciliation is idempotent — a second pass creates nothing", async () => {
-    const before = await queryRow<{ c: string }>(`SELECT count(*)::text c FROM transactions`);
+    // A first pass has already run above; a second must add nothing. Scoped to
    // 'Order - %' rows so unrelated fixtures cannot move the number.
    const q = `SELECT count(*)::text c FROM transactions WHERE description LIKE 'Order - %'`;
    const before = await queryRow<{ c: string }>(q);
    const out = await reconcilePendingOrders();
-    const after = await queryRow<{ c: string }>(`SELECT count(*)::text c FROM transactions`);
+    const after = await queryRow<{ c: string }>(q);
    expect(out.created).toBe(0);
    expect(after!.c).toBe(before!.c);
  });
@@ -320,39 +347,162 @@ describe("Refund amendments", () => {
  });
 });
-describe("[Family] orders import rather than park", () => {
+describe("[Family] orders are card-settled, not credits", () => {
-  it("records a family order as credits and tags it", async () => {
+  it("creates provenance but NO transaction — the statement line is the transaction", async () => {
    // Regression: these were assumed credits-funded because the receipt names
    // the payer and no instrument. The card statement carries all four (CBA
    // ...3893, exact LKR matches), so creating a transaction double-counted
    // spend already recorded.
    const p = parseOrderHTML(
      html("ue-04"),
      meta({ subject: "[Family] Your Tuesday evening order with Uber Eats", sender: "uber.com", receivedAt: "2026-07-07T10:08:00Z" })
    );
-    expect(p.flags).toContain("family_payment_assumed_credits");
+    expect(p.flags).toContain("family_card_settled_no_transaction");
    expect(p.payment.credits_amount).toBeNull();
    const res = await processOrderIngestion(p);
-    expect(res.transactionId).not.toBeNull();
+    expect(res.transactionId).toBeNull();
    expect(res.metadataId).not.toBeNull();
  });
 });
-    const tag = await queryRow<{ name: string }>(
+describe("owner scoping", () => {
-      `SELECT tg.name FROM transaction_tags tt JOIN tags tg ON tg.id = tt.tag_id
+  it("sets owner_id so the row is visible to the app", async () => {
-        WHERE tt.transaction_id = $1`,
+    // Regression: analytics scope on COALESCE(t.owner_id, s.owner_id). An
    // ingested order has no statement, so a NULL owner_id made all 85
    // backfilled rows invisible in every view while sitting in the table.
    const p = parseOrderHTML(html("dd-01"), meta({ messageId: `owner-${Date.now()}` }));
    const res = await processOrderIngestion(p);
    const row = await queryRow<{ owner_id: number | null }>(
      `SELECT owner_id FROM transactions WHERE id = $1`,
      [res.transactionId]
    );
-    expect(tag!.name).toBe("family");
+    expect(row!.owner_id).not.toBeNull();
-    // LKR is preserved, and amount_aud stays NULL — no FX rate is available.
+    const visible = await queryRaw(
-    const txn = await queryRow<{ foreign_currency_code: string; amount_aud: string | null }>(
+      `SELECT t.id FROM transactions t LEFT JOIN statements s ON s.id = t.statement_id
-      `SELECT foreign_currency_code, amount_aud::text FROM transactions WHERE id = $1`,
+        WHERE t.id = $1 AND COALESCE(t.owner_id, s.owner_id) = $2`,
      [res.transactionId, row!.owner_id]
    );
    expect(visible).toHaveLength(1);
  });
 });
 describe("how an ingested order presents in the app", () => {
  it("names the platform in the description", async () => {
    // "Order - Burger Corner" gives no way to know where to look for the
    // detail, and the same restaurant can be on both platforms.
    const p = parseOrderHTML(html("dd-01"), meta({ messageId: `desc-${Date.now()}` }));
    const res = await processOrderIngestion(p);
    const row = await queryRow<{ description: string }>(
      `SELECT description FROM transactions WHERE id = $1`, [res.transactionId]
    );
    expect(row!.description).toMatch(/\(DoorDash\)$/);
  });
  it("reads as 'Gift Card', not 'Manual', and stays out of the reconcile queue", async () => {
    // bank_name is derived — no statement means "Manual", which reads as
    // "hand-entered, awaiting a card line". A credits order has no card line
    // coming, ever; 81 of them sat in the queue waiting for one.
    const p = parseOrderHTML(html("dd-01"), meta({ messageId: `bank-${Date.now()}` }));
    const res = await processOrderIngestion(p);
    const row = await queryRow<{ bank_name: string }>(
      `SELECT ${bankLabel()} as bank_name
         FROM transactions t LEFT JOIN statements s ON s.id = t.statement_id
        WHERE t.id = $1`,
      [res.transactionId]
    );
-    expect(txn!.foreign_currency_code).toBe("LKR");
+    expect(row!.bank_name).toBe("Gift Card");
    expect(txn!.amount_aud).toBeNull();
-    // And it must not reach spend.
+    const queued = await queryRaw(
    const visible = await queryRaw(
      `SELECT t.id FROM transactions t
-        LEFT JOIN transaction_overrides o ON o.transaction_id = t.id
+        WHERE t.id = $1 AND t.statement_id IS NULL AND ${needsCardMatch("t")}`,
       WHERE t.id = $1 AND (${EXCLUDE_NON_SPEND})`,
      [res.transactionId]
    );
    expect(queued).toHaveLength(0);
  });
  it("records the platform and the message it came from", async () => {
    const p = parseOrderHTML(
      html("ue-00"),
      meta({ messageId: `prov-${Date.now()}`, subject: "Your Wednesday order with Uber Eats", sender: "uber.com" })
    );
    const res = await processOrderIngestion(p, {
      messageId: `prov-${Date.now()}`,
      subject: "Your Wednesday order with Uber Eats",
      sender: "uber.com",
    });
    const row = await queryRow<{
      platform: string; source_email_from: string; route: { label: string }[];
    }>(
      `SELECT platform, source_email_from, route FROM expense_metadata WHERE id = $1`,
      [res.metadataId]
    );
    expect(row!.platform).toBe("ubereats");
    expect(row!.source_email_from).toBe("uber.com");
    expect(row!.route.map((r) => r.label)).toEqual(["Pick-up", "Delivery"]);
  });
 });
 describe("receipt lookup for the transaction detail panel", () => {
  // Mirrors /api/transactions/[id]/order — the panel resolves a receipt from
  // either side, and a card-settled order only has the matched_transaction_id
  // side, which is exactly where the detail would otherwise go missing.
  const receiptFor = (txnId: number) =>
    queryRow<{ platform: string; route: { label: string; address: string }[] }>(
      `SELECT platform, route FROM expense_metadata
        WHERE transaction_id = $1 OR matched_transaction_id = $1 LIMIT 1`,
      [txnId]
    );
  it("finds the receipt for a credits order", async () => {
    // ue-00 is Uber Cash — credits, so it creates a transaction. ue-09 names a
    // payer with no instrument and correctly parks awaiting a card statement,
    // which would leave nothing to look the receipt up by.
    const p = parseOrderHTML(
      html("ue-00"),
      meta({ messageId: `panel-${Date.now()}`, subject: "Your Wednesday order with Uber Eats", sender: "uber.com" })
    );
    const res = await processOrderIngestion(p);
    const r = await receiptFor(res.transactionId!);
    expect(r!.platform).toBe("ubereats");
    expect(r!.route.map((x) => x.label)).toEqual(["Pick-up", "Delivery"]);
  });
  it("finds it from the statement line for a card-settled order", async () => {
    const st = await queryRow<{ id: number }>(
      `INSERT INTO statements (bank_name, account_number, filename)
       VALUES ('CBA', '5523504401723893', 'panel-cba.pdf') RETURNING id`
    );
    const card = await queryRow<{ id: number }>(
      `INSERT INTO transactions (statement_id, transaction_date, description, amount, transaction_type)
       VALUES ($1, '2026-04-07', 'UBER *EATS ZURICH', 51.23, 'debit') RETURNING id`,
      [st!.id]
    );
    const m = await queryRow<{ id: number }>(
      `INSERT INTO expense_metadata (source, order_reference, platform, route, matched_transaction_id)
       VALUES ('email', $1, 'ubereats', '[{"label":"Pick-up","time":null,"address":"Ebikon"}]'::jsonb, $2)
       RETURNING id`,
      [`panel-card-${Date.now()}`, card!.id]
    );
    expect(m).not.toBeNull();
    const r = await receiptFor(card!.id);
    expect(r!.platform).toBe("ubereats");
    expect(r!.route[0].address).toBe("Ebikon");
  });
  it("returns nothing for an ordinary transaction", async () => {
    const st = await queryRow<{ id: number }>(
      `INSERT INTO statements (bank_name, account_number, filename)
       VALUES ('CBA', '1111', 'panel-plain.pdf') RETURNING id`
    );
    const t = await queryRow<{ id: number }>(
      `INSERT INTO transactions (statement_id, transaction_date, description, amount, transaction_type)
       VALUES ($1, '2026-04-07', 'COLES 1234', 12.00, 'debit') RETURNING id`,
      [st!.id]
    );
-    expect(visible).toHaveLength(0);
+    expect(await receiptFor(t!.id)).toBeNull();
  });
 });
@@ -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 } = await import("@/lib/queries");
+const { getTransactions, getParticipantBalances, getTripAnalytics } = await import("@/lib/queries");
 beforeEach(async () => {
  await resetDB(pool);
@@ -253,3 +253,161 @@ describe("getParticipantBalances", () => {
    expect(bobBalance!.unsettled_count).toBe(2);
  });
 });
 describe("getTransactions — order provenance for the description sub-line", () => {
  it("carries the route and platform of an order-derived row", async () => {
    // Five rows all reading "Order - Uber Trip" are indistinguishable in the
    // list; where the trip went is the only thing that separates them, and it
    // was already stored.
    const { ownerId } = await seedParticipants(pool);
    const txId = await insertTransaction(pool, ownerId, { description: "Order - Uber Trip" });
    await pool.query(
      `INSERT INTO expense_metadata (source, order_reference, platform, route, transaction_id)
       VALUES ('email', $1, 'uber',
         '[{"label":"Pick-up","time":"7:32 pm","address":"Terminal 2, Melbourne Airport (MEL), Tullamarine VIC 3045, Australia"},
           {"label":"Drop-off","time":"8:10 pm","address":"19 Lady Penrhyn Dr, Wyndham Vale VIC 3024, Australia"}]'::jsonb,
         $2)`,
      [`route-${Date.now()}`, txId]
    );
    const { data } = await getTransactions(ownerId, { limit: 50, offset: 0 });
    const row = data.find((r) => r.id === txId)!;
    expect(row.order_platform).toBe("uber");
    expect(row.order_route).toHaveLength(2);
    expect(row.order_route![0].address).toContain("Melbourne Airport");
  });
  it("resolves from the statement line for a card-settled order", async () => {
    // A card-settled order creates no transaction of its own (I5) — the
    // receipt points at the statement line through matched_transaction_id.
    const { ownerId } = await seedParticipants(pool);
    const txId = await insertTransaction(pool, ownerId, { description: "UBER *TRIP AUCKLAND" });
    await pool.query(
      `INSERT INTO expense_metadata (source, order_reference, platform, route, matched_transaction_id)
       VALUES ('email', $1, 'uber',
         '[{"label":"Pick-up","time":null,"address":"64 Federal Street, Auckland 1010, NZ"},
           {"label":"Drop-off","time":null,"address":"International Terminal, Auckland 2022, New Zealand"}]'::jsonb,
         $2)`,
      [`route-card-${Date.now()}`, txId]
    );
    const { data } = await getTransactions(ownerId, { limit: 50, offset: 0 });
    const row = data.find((r) => r.id === txId)!;
    expect(row.order_route).toHaveLength(2);
  });
  it("leaves an ordinary transaction with no route", async () => {
    const { ownerId } = await seedParticipants(pool);
    const txId = await insertTransaction(pool, ownerId, { description: "COLES 1234" });
    const { data } = await getTransactions(ownerId, { limit: 50, offset: 0 });
    const row = data.find((r) => r.id === txId)!;
    expect(row.order_route).toBeNull();
    expect(row.order_platform).toBeNull();
  });
 });
 // ── settlement scope: settled + split_payments.trip_id (migration 0022) ───────
 describe("getParticipantBalances — settled", () => {
  it("excludes a settled split from what is owed", async () => {
    const { ownerId, otherId } = await seedParticipants(pool);
    const txId = await insertTransaction(pool, ownerId, { amount: 100 });
    await pool.query(
      `INSERT INTO transaction_splits (transaction_id, participant_id, share_percent, settled)
       VALUES ($1, $2, 50, true)`,
      [txId, otherId]
    );
    const balances = await getParticipantBalances(ownerId);
    const bob = balances.find((b) => b.id === otherId);
    expect(Number(bob!.total_owed)).toBeCloseTo(0);
  });
  it("still counts an unsettled split alongside a settled one", async () => {
    const { ownerId, otherId } = await seedParticipants(pool);
    const settledTx = await insertTransaction(pool, ownerId, { amount: 100 });
    const liveTx = await insertTransaction(pool, ownerId, { amount: 40 });
    await pool.query(
      `INSERT INTO transaction_splits (transaction_id, participant_id, share_percent, settled)
       VALUES ($1, $2, 50, true), ($3, $2, 50, false)`,
      [settledTx, otherId, liveTx]
    );
    const balances = await getParticipantBalances(ownerId);
    const bob = balances.find((b) => b.id === otherId);
    // Only the live split counts: 50% of 40.
    expect(Number(bob!.total_owed)).toBeCloseTo(20);
  });
 });
 describe("getTripAnalytics — per-trip settlement", () => {
  async function seedTrip(ownerId: number, otherId: number) {
    const trip = await pool.query(
      `INSERT INTO trips (owner_id, name, start_date, end_date)
       VALUES ($1, 'Test Trip', '2026-03-01', '2026-03-10') RETURNING id`,
      [ownerId]
    );
    const tripId = trip.rows[0].id as number;
    const txId = await insertTransaction(pool, ownerId, { amount: 200, 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, otherId]
    );
    return tripId;
  }
  it("reports the gross share before any payment", async () => {
    const { ownerId, otherId } = await seedParticipants(pool);
    const tripId = await seedTrip(ownerId, otherId);
    const { participant_splits } = await getTripAnalytics(tripId, ownerId);
    const bob = participant_splits.find((r) => r.participant_id === otherId);
    expect(Number(bob!.owed)).toBeCloseTo(100);
  });
  it("nets off a payment scoped to that trip", async () => {
    const { ownerId, otherId } = await seedParticipants(pool);
    const tripId = await seedTrip(ownerId, otherId);
    await pool.query(
      `INSERT INTO split_payments (from_participant_id, to_participant_id, amount, payment_date, trip_id)
       VALUES ($1, $2, 60, '2026-03-15', $3)`,
      [otherId, ownerId, tripId]
    );
    const { participant_splits } = await getTripAnalytics(tripId, ownerId);
    const bob = participant_splits.find((r) => r.participant_id === otherId);
    expect(Number(bob!.owed)).toBeCloseTo(40);
  });
  // The point of the whole scope column: settling the household tab must not
  // make a trip look paid. Before trip_id existed there was one global pool and
  // this distinction could not be expressed.
  it("ignores a household payment when reporting the trip", async () => {
    const { ownerId, otherId } = await seedParticipants(pool);
    const tripId = await seedTrip(ownerId, otherId);
    await pool.query(
      `INSERT INTO split_payments (from_participant_id, to_participant_id, amount, payment_date, trip_id)
       VALUES ($1, $2, 60, '2026-03-15', NULL)`,
      [otherId, ownerId]
    );
    const { participant_splits } = await getTripAnalytics(tripId, ownerId);
    const bob = participant_splits.find((r) => r.participant_id === otherId);
    expect(Number(bob!.owed)).toBeCloseTo(100);
  });
  it("drops a settled split from the trip figure", async () => {
    const { ownerId, otherId } = await seedParticipants(pool);
    const tripId = await seedTrip(ownerId, otherId);
    await pool.query(`UPDATE transaction_splits SET settled = true WHERE participant_id = $1`, [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);
  });
 });
@@ -5,8 +5,8 @@ import {
  parseOrderHTML,
  validateOrderTotals,
  resolveCategory,
  OrderParseError,
  NotAReceiptError,
  orderDescription,
  type MessageMeta,
  type ParsedOrder,
 } from "../../lib/order-ingestion";
@@ -71,6 +71,7 @@ describe("validateOrderTotals", () => {
      service_fee: null, tip: null, discounts: null, total_charged: 10,
    },
    line_items: [],
    route: [],
    is_family: false,
    flags: [],
    ...over,
@@ -164,3 +165,158 @@ describe("order_reference anchoring", () => {
    expect(p.flags).toContain("order_uuid_ambiguous");
  });
 });
 describe("mixed Uber payment (issuer-named card leg)", () => {
  it("captures both legs when the card is labelled by issuer, not brand", () => {
    // Real receipt: Uber Cash $1.17 + Westpac ••••8032 $15.33 = $16.50.
    // A brand allowlist (Visa|MasterCard|Amex) misses "Westpac" and drops the
    // card half, leaving payments that do not account for the total.
    const p = parseOrderHTML(
      readFileSync(resolve(dir, "ue-mixed.html"), "utf-8"),
      meta({ subject: "Your Friday morning order with Uber Eats", sender: "uber.com", receivedAt: "2026-01-09T09:26:44Z" })
    );
    expect(p.totals.total_charged).toBeCloseTo(16.50, 2);
    expect(p.payment.credits_amount).toBeCloseTo(1.17, 2);
    expect(p.payment.card_amount).toBeCloseTo(15.33, 2);
    expect(p.payment.card_last4).toBe("8032");
    expect(validateOrderTotals(p).ok).toBe(true);
  });
 });
 describe("Uber route (pick-up / delivery)", () => {
  const uber = (f: string, subject = "Your Wednesday order with Uber Eats") =>
    parseOrderHTML(html(f), meta({ subject, sender: "uber.com" }));
  it("reads both stops with their times, as printed", () => {
    const p = uber("ue-00");
    expect(p.route).toEqual([
      { label: "Pick-up", time: "1:20 pm", address: "197 Watton St, Werribee VIC 3030, Australia" },
      { label: "Delivery", time: "1:40 pm", address: "19 Lady Penrhyn Dr, Wyndham Vale VIC 3024, Australia" },
    ]);
  });
  it("de-duplicates the block Uber renders twice", () => {
    // The receipt emits the whole address section a second time for narrow
    // screens. Without de-duplication every trip has four stops.
    expect(uber("ue-00").route).toHaveLength(2);
    expect(uber("ue-26").route).toHaveLength(2);
  });
  it("keeps the receipt's own wording rather than normalising it", () => {
    // Uber is not internally consistent: "Pick-up" on some receipts,
    // "Pickup" on others. Inventing a canonical spelling would hide that a
    // template changed.
    expect(uber("ue-mixed", "Your Friday morning order with Uber Eats").route[0].label).toBe("Pickup");
  });
  it("works on an international receipt", () => {
    const p = uber("ue-26");
    expect(p.route[1].address).toContain("Luzern, Switzerland");
  });
  it("DoorDash has no route — its receipts carry no addresses", () => {
    expect(parseOrderHTML(html("dd-01"), meta()).route).toEqual([]);
  });
 });
 describe("Uber line items", () => {
  it("itemises a grocery order, binding qty/title/amount by item id", () => {
    const p = parseOrderHTML(
      html("ue-09"),
      meta({ subject: "Your Sunday evening order with Uber Eats", sender: "uber.com" })
    );
    expect(p.line_items).toHaveLength(5);
    expect(p.line_items[0]).toMatchObject({
      qty: 1,
      description: "Highland Brewing MILK FULL CREAM U H T 900ML",
      amount: 440,
    });
    // A sold-out item prints 0.00 and is kept: it is why the total is lower
    // than what was ordered, and dropping it makes the receipt unexplainable.
    expect(p.line_items.map((i) => i.amount)).toContain(0);
  });
  it("a restaurant order legitimately has none", () => {
    // Uber itemises groceries only; a restaurant receipt states a total and
    // nothing else. Empty here is the receipt, not a parse failure — so it
    // must not raise no_line_items_parsed either.
    const p = parseOrderHTML(
      html("ue-00"),
      meta({ subject: "Your Wednesday order with Uber Eats", sender: "uber.com" })
    );
    expect(p.line_items).toEqual([]);
    expect(p.flags).not.toContain("no_line_items_parsed");
  });
 });
 /**
 * Uber trips. Captured 2026-06 via a dry-run against the real mailbox after the
 * user pointed out that only *overseas* rides go on a card — local rides are
 * paid with credits, which puts them in the same class as delivery orders.
 */
 describe("Uber trips", () => {
  const utMeta: Record<string, MessageMeta> = JSON.parse(
    readFileSync(resolve(dir, "ut-meta.json"), "utf-8")
  );
  const trip = (f: string) => parseOrderHTML(html(f), utMeta[f]);
  it("a local trip is credits-funded", () => {
    const p = trip("ut-00");
    expect(p.platform).toBe("uber");
    expect(p.currency).toBe("AUD");
    expect(p.totals.total_charged).toBeCloseTo(84.78, 2);
    expect(p.payment.credits_amount).toBeCloseTo(84.78, 2);
    expect(p.payment.card_last4).toBeNull();
    expect(validateOrderTotals(p).ok).toBe(true);
  });
  it("an overseas trip is card-settled", () => {
    const p = trip("ut-01");
    expect(p.currency).toBe("NZD");
    expect(p.totals.total_charged).toBeCloseTo(55.51, 2);
    expect(p.payment.card_amount).toBeCloseTo(55.51, 2);
    expect(p.payment.card_last4).toBe("3893");
  });
  it("labels the two ends of a trip, which the receipt does not", () => {
    // Delivery receipts write "1:20 pm - Pick-up"; trip receipts print the time
    // alone. The naive split put the time in `label` and left `time` null.
    const p = trip("ut-00");
    expect(p.route).toEqual([
      {
        label: "Pick-up",
        time: "7:32 pm",
        address: "Terminal 2, Melbourne Airport (MEL), Tullamarine VIC 3045, Australia",
      },
      {
        label: "Drop-off",
        time: "8:10 pm",
        address: "19 Lady Penrhyn Dr, Wyndham Vale VIC 3024, Australia",
      },
    ]);
  });
  it("rejects the charge summary Uber sends before the receipt", () => {
    // Uber sends two mails per trip with the same subject and the same total.
    // The first says "This is not a payment receipt" and carries no
    // tripReference, so order_reference would fall back to msg:<id> and I7
    // could not dedupe it — every trip would be recorded twice.
    expect(() => trip("ut-summary")).toThrow(NotAReceiptError);
  });
 });
 describe("orderDescription", () => {
  it("names the platform", () => {
    expect(orderDescription("doordash", "Mad Mex")).toBe("Order - Mad Mex (DoorDash)");
    expect(orderDescription("ubereats", "Coles (Wyndham Vale)")).toBe(
      "Order - Coles (Wyndham Vale) (Uber Eats)"
    );
  });
  it("does not restate a platform the merchant already names", () => {
    // A trip's merchant is literally "Uber Trip"; "(Uber)" after it says
    // nothing. What identifies a trip is its addresses, and those live in the
    // Order details panel.
    expect(orderDescription("uber", "Uber Trip")).toBe("Order - Uber Trip");
  });
 });
@@ -0,0 +1,87 @@
 import { NextRequest, NextResponse } from "next/server";
 import { getCurrentUser } from "@/lib/auth";
 import { queryRaw } from "@/lib/db";
 import {
  OWNER_SCOPE,
  STATEMENTS_JOIN,
  EXCLUDE_NON_SPEND,
  EXCLUDE_RECONCILED_SOURCE,
  EFFECTIVE_CATEGORY,
  NET_SPEND_ROWS,
  SPEND_SIGNED,
  mySplitOf,
  toDateStr,
 } from "@/lib/analytics-sql";
 /**
 * Daily net spend, by month, day-of-month and category.
 *
 * This exists so the spend-pace chart stops computing its own totals. It used to
 * sum gross `amount_aud ?? amount` over `transaction_type = 'debit'` in the
 * browser, which meant it ignored personal share, refunds, fees, interest, and
 * itemised loan repayments — every rule the headline applies. The two numbers
 * could disagree while both were labelled "spend", and the chart's own baseline
 * line was drawn from the split-adjusted monthly totals, so the two series in
 * one chart were on different bases.
 *
 * Day-of-month granularity is also what lets the page compare a partial current
 * month against prior months *through the same day*, instead of against their
 * full-month totals — which always made a month in progress look thrifty.
 *
 * Same fragments as /api/analytics/monthly. If that route's semantics change,
 * this one changes with it.
 */
 export async function GET(req: NextRequest) {
  const user = await getCurrentUser(req);
  if (!user) return NextResponse.json({ error: "Unauthorized" }, { status: 403 });
  const { searchParams } = new URL(req.url);
  const monthCount = Math.min(Math.max(Number(searchParams.get("months") || "12"), 1), 24);
  const now = new Date();
  const endDate = new Date(now.getFullYear(), now.getMonth() + 1, 1);
  const startDate = new Date(now.getFullYear(), now.getMonth() - monthCount + 1, 1);
  const rows = await queryRaw<{ month: string; day: number; category: string; spent: string }>(
    `SELECT
      TO_CHAR(DATE_TRUNC('month', t.transaction_date::date), 'YYYY-MM') as month,
      EXTRACT(DAY FROM t.transaction_date::date)::int as day,
      ${EFFECTIVE_CATEGORY} as category,
      -- 4dp, not 2. This is grouped finer than /monthly (by day as well as
      -- category), so rounding each bucket to cents and summing accumulates a
      -- different error than rounding per category does — the pace chart ended
      -- the month a few cents off the headline it sits under. Round once, at
      -- display time.
      SUM(${mySplitOf(SPEND_SIGNED)})::numeric(14,4) as spent
    FROM transactions t
    LEFT JOIN transaction_overrides o ON o.transaction_id = t.id
    LEFT JOIN transaction_splits ts ON ts.transaction_id = t.id AND ts.participant_id = $1
    ${STATEMENTS_JOIN}
    WHERE ${OWNER_SCOPE} = $1
      AND ${NET_SPEND_ROWS}
      AND ${EXCLUDE_NON_SPEND}
      AND ${EXCLUDE_RECONCILED_SOURCE}
      AND t.transaction_date >= $2
      AND t.transaction_date < $3
    GROUP BY 1, 2, 3
    ORDER BY 1, 2`,
    [user.id, toDateStr(startDate), toDateStr(endDate)]
  );
  // Sparse by design — a day with no spend has no entry, and the client treats
  // a missing day as zero. Emitting 31 zeroes per month per category would
  // dominate the payload.
  const daily: Record<string, Record<number, number>> = {};
  const byCategory: Record<string, Record<string, Record<number, number>>> = {};
  for (const r of rows) {
    const spent = Number(r.spent);
    const m = (daily[r.month] ??= {});
    m[r.day] = (m[r.day] ?? 0) + spent;
    const c = ((byCategory[r.month] ??= {})[r.category] ??= {});
    c[r.day] = (c[r.day] ?? 0) + spent;
  }
  return NextResponse.json({ daily, byCategory });
 }
@@ -1,12 +1,35 @@
 import { NextRequest, NextResponse } from "next/server";
 import { getCurrentUser } from "@/lib/auth";
 import { queryRaw } from "@/lib/db";
-import { OWNER_SCOPE, STATEMENTS_JOIN, mySplitOf } from "@/lib/analytics-sql";
+import { OWNER_SCOPE, STATEMENTS_JOIN, EXCLUDE_RECONCILED_SOURCE, mySplitOf, toDateStr } from "@/lib/analytics-sql";
 /**
 * Fees and interest over an explicit window.
 *
 * This used to aggregate every statement ever imported with no date filter, and
 * the UI printed the result with no period label — so a lifetime-to-date total
 * read as a current-period one, and grew forever. `months=0` asks for all time
 * deliberately, which is a different claim from asking for it by accident.
 */
 export async function GET(req: NextRequest) {
  const user = await getCurrentUser(req);
  if (!user) return NextResponse.json({ error: "Unauthorized" }, { status: 403 });
  const { searchParams } = new URL(req.url);
  const monthsParam = Number(searchParams.get("months") ?? "12");
  const months = Number.isFinite(monthsParam) ? Math.min(Math.max(monthsParam, 0), 120) : 12;
  const allTime = months === 0;
  const now = new Date();
  const from = new Date(now.getFullYear(), now.getMonth() - months + 1, 1);
  const fromStr = toDateStr(from);
  const toStr = toDateStr(new Date(now.getFullYear(), now.getMonth() + 1, 1));
  // A statement is dated by the period it covers, not by when it was imported.
  const stmtWindow = allTime ? "" : `AND billing_end_date >= $2 AND billing_end_date < $3`;
  const txnWindow = allTime ? "" : `AND t.transaction_date >= $2 AND t.transaction_date < $3`;
  const windowParams = allTime ? [] : [fromStr, toStr];
  // Statement-level fees and interest (aggregated by Gemini from the PDF)
  const stmtRows = await queryRaw<{
    bank_name: string;
@@ -19,10 +42,11 @@ export async function GET(req: NextRequest) {
      SUM(COALESCE(interest_charged, 0))::numeric(12,2) AS interest
    FROM statements
    WHERE owner_id = $1
    ${stmtWindow}
    GROUP BY bank_name
    HAVING SUM(COALESCE(fees_charged, 0)) + SUM(COALESCE(interest_charged, 0)) > 0
    ORDER BY (SUM(COALESCE(fees_charged, 0)) + SUM(COALESCE(interest_charged, 0))) DESC`,
-    [user.id]
+    [user.id, ...windowParams]
  );
  // Transaction-level fee and interest line items (split-adjusted)
@@ -49,8 +73,10 @@ export async function GET(req: NextRequest) {
    ${STATEMENTS_JOIN}
    WHERE ${OWNER_SCOPE} = $1
      AND t.transaction_type IN ('fee', 'interest')
      AND ${EXCLUDE_RECONCILED_SOURCE}
      ${txnWindow}
    ORDER BY t.transaction_date DESC`,
-    [user.id]
+    [user.id, ...windowParams]
  );
  const by_bank = stmtRows.map((r) => ({
@@ -69,5 +95,13 @@ export async function GET(req: NextRequest) {
  const total_fees = by_bank.reduce((s, r) => s + r.fees, 0);
  const total_interest = by_bank.reduce((s, r) => s + r.interest, 0);
-  return NextResponse.json({ by_bank, transactions, total_fees, total_interest });
+  return NextResponse.json({
    by_bank,
    transactions,
    total_fees,
    total_interest,
    // The period is part of the answer — the client must be able to say what
    // window these totals cover rather than implying "now".
    period: { months, from: allTime ? null : fromStr, to: allTime ? null : toStr, all_time: allTime },
  });
 }
@@ -1,7 +1,8 @@
 import { NextRequest, NextResponse } from "next/server";
 import { getCurrentUser } from "@/lib/auth";
 import { queryRaw } from "@/lib/db";
-import { OWNER_SCOPE, STATEMENTS_JOIN, EFFECTIVE_CATEGORY, mySplitOf } from "@/lib/analytics-sql";
+import { OWNER_SCOPE, STATEMENTS_JOIN, EXCLUDE_RECONCILED_SOURCE, EFFECTIVE_CATEGORY, mySplitOf } from "@/lib/analytics-sql";
 import { bankLabel } from "@/lib/queries";
 const MY_AMOUNT = mySplitOf(`COALESCE(t.amount_aud, t.amount)`);
@@ -39,7 +40,7 @@ export async function GET(
      END::numeric(10,2) as my_amount,
      t.transaction_type,
      ${EFFECTIVE_CATEGORY} as category,
-      COALESCE(s.bank_name, 'Manual') as bank_name,
+      ${bankLabel()} as bank_name,
      t.statement_id
    FROM transactions t
    ${STATEMENTS_JOIN}
@@ -48,6 +49,7 @@ export async function GET(
    WHERE ${OWNER_SCOPE} = $1
      AND t.transaction_type IN ('debit', 'fee', 'interest', 'refund', 'credit')
      AND COALESCE(o.merchant_normalized, t.merchant_normalized, t.merchant_name, t.description) = $2
      AND ${EXCLUDE_RECONCILED_SOURCE}
    ORDER BY t.transaction_date DESC
    LIMIT 500
  `, [user.id, decoded]);
@@ -1,7 +1,7 @@
 import { NextRequest, NextResponse } from "next/server";
 import { getCurrentUser } from "@/lib/auth";
 import { queryRaw } from "@/lib/db";
-import { OWNER_SCOPE, STATEMENTS_JOIN, EXCLUDE_NON_SPEND, EFFECTIVE_CATEGORY, mySplitOf } from "@/lib/analytics-sql";
+import { OWNER_SCOPE, STATEMENTS_JOIN, EXCLUDE_NON_SPEND, EXCLUDE_RECONCILED_SOURCE, EFFECTIVE_CATEGORY, mySplitOf, toDateStr } from "@/lib/analytics-sql";
 // Split-adjusted amount helper (positive for spend, negative for refunds)
 const MY_AMOUNT = mySplitOf(`COALESCE(t.amount_aud, t.amount)`);
@@ -21,7 +21,7 @@ export async function GET(req: NextRequest) {
  const cutoff = new Date();
  cutoff.setMonth(cutoff.getMonth() - months);
-  const fromDate = cutoff.toISOString().slice(0, 10);
+  const fromDate = toDateStr(cutoff);
  // Merchant aggregates — net spend (debits + fees - refunds/credits)
  const rows = await queryRaw<{
@@ -69,6 +69,7 @@ export async function GET(req: NextRequest) {
      AND t.transaction_type IN ('debit', 'fee', 'interest', 'refund', 'credit')
      AND t.transaction_date >= $2
      AND ${EXCLUDE_NON_SPEND}
      AND ${EXCLUDE_RECONCILED_SOURCE}
    GROUP BY 1
    HAVING SUM(${SPEND_EXPR}) > 0
    ORDER BY net_spend DESC
@@ -95,6 +96,7 @@ export async function GET(req: NextRequest) {
        AND t.transaction_date >= $2
        AND COALESCE(o.merchant_normalized, t.merchant_normalized, t.merchant_name, t.description) = ANY($3)
        AND ${EXCLUDE_NON_SPEND}
        AND ${EXCLUDE_RECONCILED_SOURCE}
      GROUP BY 1, 2
      ORDER BY 1, 2
    `, [user.id, fromDate, topMerchants]);
@@ -6,9 +6,11 @@ import {
  STATEMENTS_JOIN,
  EFFECTIVE_CATEGORY,
  EXCLUDE_NON_SPEND,
  EXCLUDE_RECONCILED_SOURCE,
  NET_SPEND_ROWS,
  SPEND_SIGNED,
  mySplitOf,
  toDateStr,
 } from "@/lib/analytics-sql";
 export async function GET(req: NextRequest) {
@@ -22,8 +24,8 @@ export async function GET(req: NextRequest) {
  const endDate = new Date(now.getFullYear(), now.getMonth() + 1, 1);
  const startDate = new Date(now.getFullYear(), now.getMonth() - monthCount + 1, 1);
-  const startStr = startDate.toISOString().slice(0, 10);
+  const startStr = toDateStr(startDate);
-  const endStr = endDate.toISOString().slice(0, 10);
+  const endStr = toDateStr(endDate);
  // Expenses: debits excluding transfers and investments, split-adjusted
  const spendRows = await queryRaw<{
@@ -35,7 +37,9 @@ export async function GET(req: NextRequest) {
    `SELECT
      TO_CHAR(DATE_TRUNC('month', t.transaction_date::date), 'YYYY-MM') as month,
      ${EFFECTIVE_CATEGORY} as category,
-      SUM(${mySplitOf(SPEND_SIGNED)})::numeric(12,2) as total_spent,
+      -- 4dp so the month total is summed from unrounded parts; every consumer
      -- rounds for display. See the note in /api/analytics/daily.
      SUM(${mySplitOf(SPEND_SIGNED)})::numeric(14,4) as total_spent,
      COUNT(*)::int as transaction_count
    FROM transactions t
    LEFT JOIN transaction_overrides o ON o.transaction_id = t.id
@@ -44,6 +48,7 @@ export async function GET(req: NextRequest) {
    WHERE ${OWNER_SCOPE} = $1
      AND ${NET_SPEND_ROWS}
      AND ${EXCLUDE_NON_SPEND}
      AND ${EXCLUDE_RECONCILED_SOURCE}
      AND t.transaction_date >= $2
      AND t.transaction_date < $3
    GROUP BY 1, 2
@@ -67,6 +72,7 @@ export async function GET(req: NextRequest) {
    WHERE ${OWNER_SCOPE} = $1
      AND t.transaction_type IN ('credit', 'payment')
      AND ${EFFECTIVE_CATEGORY} = 'income'
      AND ${EXCLUDE_RECONCILED_SOURCE}
      AND t.transaction_date >= $2
      AND t.transaction_date < $3
    GROUP BY 1
@@ -89,6 +95,7 @@ export async function GET(req: NextRequest) {
    ${STATEMENTS_JOIN}
    WHERE ${OWNER_SCOPE} = $1
      AND ${EFFECTIVE_CATEGORY} = 'investment'
      AND ${EXCLUDE_RECONCILED_SOURCE}
      AND t.transaction_date >= $2
      AND t.transaction_date < $3
    GROUP BY 1
@@ -1,7 +1,7 @@
 import { NextRequest, NextResponse } from "next/server";
 import { getCurrentUser } from "@/lib/auth";
 import { queryRaw } from "@/lib/db";
-import { OWNER_SCOPE, STATEMENTS_JOIN, EXCLUDE_NON_SPEND, EFFECTIVE_CATEGORY, mySplitOf } from "@/lib/analytics-sql";
+import { OWNER_SCOPE, STATEMENTS_JOIN, EXCLUDE_NON_SPEND, EXCLUDE_RECONCILED_SOURCE, EFFECTIVE_CATEGORY, mySplitOf } from "@/lib/analytics-sql";
 export async function GET(req: NextRequest) {
  const user = await getCurrentUser(req);
@@ -31,6 +31,7 @@ export async function GET(req: NextRequest) {
      WHERE ${OWNER_SCOPE} = $1
        AND t.transaction_type IN ('debit', 'fee')
        AND ${EXCLUDE_NON_SPEND}
        AND ${EXCLUDE_RECONCILED_SOURCE}
        AND COALESCE(o.merchant_normalized, t.merchant_normalized, t.merchant_name) IS NOT NULL
    ),
    merchant_with_lag AS (
@@ -72,7 +72,11 @@ export async function POST(req: NextRequest) {
    if (dryRun) return NextResponse.json({ kind: "order", order });
-    const result = await processOrderIngestion(order, { messageId: meta.messageId });
+    const result = await processOrderIngestion(order, {
      messageId: meta.messageId,
      subject: meta.subject,
      sender: meta.sender,
    });
    return NextResponse.json({
      kind: "order",
      order_reference: order.order_reference,
@@ -1,35 +0,0 @@
 import { NextRequest, NextResponse } from "next/server";
 import { queryRaw } from "@/lib/db";
 import { getCurrentUser } from "@/lib/auth";
 interface BalanceRow {
  participant_id: number;
  name: string;
  total_owed: number;
  transaction_count: number;
 }
 export async function GET(
  req: NextRequest,
  { params }: { params: Promise<{ id: string }> }
 ) {
  const user = await getCurrentUser(req);
  if (!user) return NextResponse.json({ error: "Unauthorized" }, { status: 403 });
  const { id } = await params;
  const rows = await queryRaw<BalanceRow>(
    `SELECT ts.participant_id, p.name,
       SUM(COALESCE(t.amount_aud, t.amount) * ts.share_percent / 100)::numeric(12,2) as total_owed,
       COUNT(*)::int as transaction_count
     FROM transaction_splits ts
     JOIN transactions t ON t.id = ts.transaction_id
     JOIN participants p ON p.id = ts.participant_id
     WHERE ts.participant_id = $1 AND ts.settled = false
     GROUP BY ts.participant_id, p.name`,
    [Number(id)]
  );
  return NextResponse.json(
    rows[0] ?? { participant_id: Number(id), total_owed: 0, transaction_count: 0 }
  );
 }
@@ -0,0 +1,44 @@
 import { NextRequest, NextResponse } from "next/server";
 import { queryRow } from "@/lib/db";
 import { getCurrentUser } from "@/lib/auth";
 import { canAccessTransactions } from "@/lib/queries";
 /**
 * Order provenance for one transaction.
 *
 * `expense_metadata` has held the itemised receipt since ingestion started and
 * nothing in the UI ever read it — a transaction that came from a DoorDash or
 * Uber Eats receipt showed a merchant and an amount, with the item list and the
 * delivery addresses sitting unread in the row behind it (user, 2026-07-27).
 *
 * Read-only. The receipt is a record of what a provider sent; editing it here
 * would make provenance mean nothing.
 */
 export async function GET(
  req: NextRequest,
  { params }: { params: Promise<{ id: string }> }
 ) {
  const user = await getCurrentUser(req);
  if (!user) return NextResponse.json({ error: "Unauthorized" }, { status: 403 });
  const { id } = await params;
  if (!(await canAccessTransactions(user.id, [Number(id)]))) {
    return NextResponse.json({ error: "Forbidden" }, { status: 403 });
  }
  const row = await queryRow(
    `SELECT platform, order_reference, line_items, route, subtotal, amount,
            currency, card_last4, flags, source_email_subject, transaction_date
       FROM expense_metadata
      -- A card-settled order creates no transaction of its own (I5): the
      -- statement line is the transaction, and the receipt points at it
      -- through matched_transaction_id. Both directions have to resolve or the
      -- detail is missing on exactly the orders that were paid by card.
      WHERE transaction_id = $1 OR matched_transaction_id = $1
      LIMIT 1`,
    [Number(id)]
  );
  // Not an order — most transactions aren't. Null, not 404: the caller is
  // asking "is there a receipt behind this?", and "no" is a normal answer.
  return NextResponse.json(row ?? null);
 }
@@ -16,7 +16,7 @@ import {
  ReferenceLine,
 } from "recharts";
 import { useQueryClient } from "@tanstack/react-query";
-import { useMonthlyAnalytics, useTransactions, useUpdateTransaction } from "@/lib/hooks";
+import { useMonthlyAnalytics, useDailySpend, useTransactions, useUpdateTransaction } from "@/lib/hooks";
 import { formatCategory, CATEGORIES } from "@/lib/categories";
 import { CATEGORY_COLORS, CHART, TOOLTIP_STYLE } from "@/lib/category-colors";
@@ -37,6 +37,26 @@ function formatShortMonth(m: string): string {
  const [year, month] = m.split("-");
  return new Date(Number(year), Number(month) - 1, 1).toLocaleString("default", { month: "short" });
 }
 function daysInMonthOf(m: string): number {
  const [year, month] = m.split("-").map(Number);
  return new Date(year, month, 0).getDate();
 }
 /**
 * How much of `month` has actually happened. A month in progress is only
 * complete up to today; every earlier month is complete.
 */
 function elapsedDays(m: string): number {
  return m === currentMonthStr() ? new Date().getDate() : daysInMonthOf(m);
 }
 /** Spend in `month` from day 1 through `throughDay` inclusive. */
 function spendThrough(days: Record<number, number> | undefined, throughDay: number): number {
  if (!days) return 0;
  let sum = 0;
  for (const [d, v] of Object.entries(days)) {
    if (Number(d) <= throughDay) sum += v;
  }
  return sum;
 }
 function fmt(n: number): string { return `$${Math.round(n).toLocaleString()}`; }
 function fmtExact(n: number): string { return `$${n.toFixed(2)}`; }
 function fmtSigned(n: number): string { return `${n >= 0 ? "+" : "−"}$${Math.abs(n) >= 100 ? Math.round(Math.abs(n)).toLocaleString() : Math.abs(n).toFixed(0)}`; }
@@ -195,12 +215,14 @@ export default function AnalyticsPage() {
    // eslint-disable-next-line react-hooks/exhaustive-deps
  }, [months]);
-  // Cumulative chart: fetch this month's transactions
+  // Day-of-month spend, split-adjusted server-side by the same rules as the
-  const smFrom = `${selectedMonth}-01`;
+  // headline. Also what makes the comparisons below like-for-like.
-  const [smYear, smMonth] = selectedMonth.split("-").map(Number);
+  const { data: dailyData } = useDailySpend(12);
-  const smNextDate = new Date(smYear, smMonth, 1);
+
-  const smTo = `${smNextDate.getFullYear()}-${String(smNextDate.getMonth() + 1).padStart(2, "0")}-01`;
+  // A month in progress is only comparable to prior months through the same day.
-  const { data: monthTxData } = useTransactions({ from: smFrom, to: smTo, limit: 1000 });
+  // Comparing 27 days of July against a full June always flattered July.
  const compareDay = elapsedDays(selectedMonth);
  const selectedIsPartial = selectedMonth === currentMonthStr();
  // Category rows for selected month
  const categoryRows = useMemo(() => {
@@ -226,22 +248,27 @@ export default function AnalyticsPage() {
      .slice(0, 8);
  }, [analytics, months, selectedMonth]);
-  // Top movers vs previous month
+  // Top movers vs previous month, compared through the same day of the month so
  // a month in progress is not measured against a complete one.
  const movers = useMemo(() => {
    if (!analytics) return [];
    const pm = prevMonth(selectedMonth);
    if (!months.includes(pm)) return [];
-    return analytics.rows
+
-      .map((r) => ({
+    const catsNow = dailyData?.byCategory?.[selectedMonth] ?? {};
-        category: r.category,
+    const catsBefore = dailyData?.byCategory?.[pm] ?? {};
-        delta: (r.spent[selectedMonth] || 0) - (r.spent[pm] || 0),
+    const categories = new Set([...Object.keys(catsNow), ...Object.keys(catsBefore)]);
-        now: r.spent[selectedMonth] || 0,
+
-        before: r.spent[pm] || 0,
+    return Array.from(categories)
-      }))
+      .map((category) => {
        const now = spendThrough(catsNow[category], compareDay);
        const before = spendThrough(catsBefore[category], compareDay);
        return { category, delta: now - before, now, before };
      })
      .filter((r) => Math.abs(r.delta) >= 1)
      .sort((a, b) => Math.abs(b.delta) - Math.abs(a.delta))
      .slice(0, 6);
-  }, [analytics, months, selectedMonth]);
+  }, [analytics, dailyData, months, selectedMonth, compareDay]);
  // Pareto chart data
  const paretoData = useMemo(() => {
@@ -258,39 +285,40 @@ export default function AnalyticsPage() {
    });
  }, [categoryRows]);
-  // Cumulative spend chart data
+  // Cumulative spend chart data.
  //
  // Both series now come from the same server-side spend definition as the
  // headline. The typical line is also a real averaged curve rather than the
  // month total spread evenly — spending is lumpy (rent on the 1st, a shop on
  // the weekend), so a straight line made ordinary months look erratic.
  const cumulativeData = useMemo(() => {
-    const daysInMonth = new Date(smYear, smMonth, 0).getDate();
+    const daysInMonth = daysInMonthOf(selectedMonth);
-    const isCurrentMonth = selectedMonth === currentMonthStr();
+    const lastDay = elapsedDays(selectedMonth);
    const today = new Date();
    const lastDay = isCurrentMonth ? today.getDate() : daysInMonth;
-    const daily: Record<number, number> = {};
+    const daily = dailyData?.daily?.[selectedMonth] ?? {};
-    (monthTxData?.data ?? [])
+    // Only complete months with data form the baseline. A month still in
-      .filter((tx) => tx.transaction_type === "debit" &&
+    // progress has no spend recorded past today, so including it would pull the
-              !["transfers", "investment"].includes(tx.effective_category) &&
+    // typical curve down by however much of it has not happened yet.
-              !tx.tags?.some((t: any) => (typeof t === "string" ? t : t.name) === "family"))
+    const priorMonths = (analytics?.months ?? []).filter(
-      .forEach((tx) => {
+      (m) => m !== selectedMonth && m !== currentMonthStr() && (analytics?.totals[m]?.spent || 0) > 0
-        const day = new Date(tx.transaction_date).getDate();
+    );
        daily[day] = (daily[day] || 0) + Number(tx.amount_aud ?? tx.amount);
      });
    const priorMonths = analytics?.months.filter((m) => m !== selectedMonth) ?? [];
    const priorAvg = priorMonths.length > 0
      ? priorMonths.reduce((s, m) => s + (analytics?.totals[m]?.spent || 0), 0) / priorMonths.length
      : 0;
    let cum = 0;
    return Array.from({ length: daysInMonth }, (_, i) => {
      const day = i + 1;
      if (day <= lastDay) cum += daily[day] || 0;
      const typical = priorMonths.length
        ? priorMonths.reduce((s, m) => s + spendThrough(dailyData?.daily?.[m], day), 0) / priorMonths.length
        : 0;
      return {
        day,
        actual: day <= lastDay ? Math.round(cum * 100) / 100 : null,
-        typical: Math.round((priorAvg * day / daysInMonth) * 100) / 100,
+        typical: Math.round(typical * 100) / 100,
      };
    });
-  }, [monthTxData, analytics, selectedMonth, smYear, smMonth]);
+  }, [dailyData, analytics, selectedMonth]);
  if (isLoading || !analytics) {
    return (
@@ -305,16 +333,30 @@ export default function AnalyticsPage() {
  const hasIncome = months.some((m) => (analytics.totals[m]?.income || 0) > 0);
  const hasInvestments = months.some((m) => (analytics.totals[m]?.investments || 0) > 0);
-  // Hero delta vs the average of the other months that have data
+  // Hero delta vs the average of the other *complete* months that have data.
-  const otherMonths = months.filter((m) => m !== selectedMonth && (analytics.totals[m]?.spent || 0) > 0);
+  //
  // Two partial-month traps here. The month in progress never belongs in the
  // baseline, because most of it has not happened. And when the month in
  // progress is the one selected, its running total has to be measured against
  // the same slice of each prior month, not against their full totals.
  const otherMonths = months.filter(
    (m) => m !== selectedMonth && m !== currentMonthStr() && (analytics.totals[m]?.spent || 0) > 0
  );
  const comparableSpend = selectedIsPartial
    ? spendThrough(dailyData?.daily?.[selectedMonth], compareDay)
    : totals.spent;
  const avgSpend = otherMonths.length
-    ? otherMonths.reduce((s, m) => s + (analytics.totals[m]?.spent || 0), 0) / otherMonths.length
+    ? otherMonths.reduce(
        (s, m) => s + (selectedIsPartial ? spendThrough(dailyData?.daily?.[m], compareDay) : analytics.totals[m]?.spent || 0),
        0
      ) / otherMonths.length
    : 0;
-  const avgDeltaPct = avgSpend > 0 ? Math.round(((totals.spent - avgSpend) / avgSpend) * 100) : 0;
+  const avgDeltaPct = avgSpend > 0 ? Math.round(((comparableSpend - avgSpend) / avgSpend) * 100) : 0;
  const throughQualifier = selectedIsPartial ? ` through day ${compareDay}` : "";
  const heroSentence =
    avgSpend === 0 ? "" :
-    Math.abs(avgDeltaPct) <= 3 ? `in line with your ${otherMonths.length}-month average` :
+    Math.abs(avgDeltaPct) <= 3 ? `in line with your ${otherMonths.length}-month average${throughQualifier}` :
-    `${Math.abs(avgDeltaPct)}% ${avgDeltaPct > 0 ? "above" : "below"} your ${otherMonths.length}-month average of ${fmt(avgSpend)}`;
+    `${Math.abs(avgDeltaPct)}% ${avgDeltaPct > 0 ? "above" : "below"} your ${otherMonths.length}-month average of ${fmt(avgSpend)}${throughQualifier}`;
  const pareto80idx = paretoData.findIndex((r) => r.cumulative >= 80);
  const tableMonths = analytics.months.slice(0, 6); // newest-first, last 6
@@ -373,7 +415,10 @@ export default function AnalyticsPage() {
      {movers.length > 0 && (
        <div className="bg-zinc-900 border border-zinc-800 rounded-xl p-4">
          <h3 className="text-sm font-medium mb-1">What changed</h3>
-          <p className="text-xs text-zinc-500 mb-4">Biggest category moves vs {formatShortMonth(prevMonth(selectedMonth))}</p>
+          <p className="text-xs text-zinc-500 mb-4">
            Biggest category moves vs {formatShortMonth(prevMonth(selectedMonth))}
            {selectedIsPartial && `, both through day ${compareDay}`}
          </p>
          <div className="grid sm:grid-cols-2 gap-x-8 gap-y-2.5">
            {movers.map((m) => (
              <div key={m.category} className="flex items-center gap-3">
@@ -10,6 +10,18 @@ import { CHART, TOOLTIP_STYLE } from "@/lib/category-colors";
 const SPEND_TYPES = new Set(["debit", "fee", "interest"]);
 /**
 * The API returns an exclusive upper bound (first day of the month after the
 * window). Showing that date verbatim would claim a month the totals exclude,
 * so `exclusive` steps back a day for display.
 */
 function formatPeriodBound(iso: string | null, exclusive = false): string {
  if (!iso) return "—";
  const d = new Date(`${iso}T00:00:00`);
  if (exclusive) d.setDate(d.getDate() - 1);
  return d.toLocaleDateString("default", { month: "short", year: "numeric" });
 }
 function fmt(n: number) {
  return new Intl.NumberFormat("en-AU", { style: "currency", currency: "AUD", maximumFractionDigits: 0 }).format(n);
 }
@@ -41,10 +53,13 @@ const FREQ_LABEL: Record<string, string> = {
 };
 // ─── Section wrapper ────────────────────────────────────────────────
-function Section({ title, children }: { title: string; children: React.ReactNode }) {
+function Section({ title, aside, children }: { title: string; aside?: React.ReactNode; children: React.ReactNode }) {
  return (
    <div className="mb-8">
-      <h3 className="text-base font-semibold text-zinc-200 mb-3">{title}</h3>
+      <div className="flex flex-wrap items-baseline justify-between gap-x-4 gap-y-1 mb-3">
        <h3 className="text-base font-semibold text-zinc-200">{title}</h3>
        {aside}
      </div>
      {children}
    </div>
  );
@@ -298,7 +313,8 @@ export default function InsightsPage() {
  const { data: analytics } = useMonthlyAnalytics(12);
  const { data: analytics6 } = useMonthlyAnalytics(6);
  const { data: subData } = useSubscriptions();
-  const { data: feesData } = useFees();
+  const [feeMonths, setFeeMonths] = useState(12);
  const { data: feesData } = useFees(feeMonths);
  // Build regular/occasional chart data
  const chartData = useMemo(() => {
@@ -431,11 +447,38 @@ export default function InsightsPage() {
      </Section>
      {/* ── 4. Fees & Interest ── */}
-      <Section title="Fees & interest">
+      <Section
        title="Fees & interest"
        aside={
          <div className="flex items-center gap-3">
            {feesData?.period && (
              <span className="text-xs text-zinc-500 tabular-nums">
                {feesData.period.all_time
                  ? "All time"
                  : `${formatPeriodBound(feesData.period.from)} – ${formatPeriodBound(feesData.period.to, true)}`}
              </span>
            )}
            <select
              value={feeMonths}
              onChange={(e) => setFeeMonths(Number(e.target.value))}
              className="bg-zinc-900 border border-zinc-800 rounded px-2 py-1 text-xs text-zinc-300 focus:outline-none focus:border-indigo-500 cursor-pointer"
              aria-label="Fees period"
            >
              <option value={3}>Last 3 months</option>
              <option value={6}>Last 6 months</option>
              <option value={12}>Last 12 months</option>
              <option value={24}>Last 24 months</option>
              <option value={0}>All time</option>
            </select>
          </div>
        }
      >
        {!feesData ? (
          <p className="text-zinc-500 text-sm">Loading...</p>
        ) : feesData.by_bank.length === 0 && feesData.transactions.length === 0 ? (
-          <p className="text-zinc-500 text-sm">No fees or interest recorded across your statements.</p>
+          <p className="text-zinc-500 text-sm">
            No fees or interest recorded {feesData.period?.all_time ? "on any statement" : "in this period"}.
          </p>
        ) : (
          <div className="space-y-4">
            {feesData.by_bank.length > 0 && (
@@ -22,8 +22,12 @@ function formatDate(d: string) {
 const SPEND_TYPES = new Set(["debit", "fee", "interest"]);
-function formatAmount(n: number, type?: string) {
+function formatAmount(n: number, type?: string, currency?: string) {
-  const formatted = `$${Number(n).toFixed(2)}`;
+  // A bare "$" on a non-AUD row was the visible half of the problem: the row
  // read as dollars while the participant balances converted to AUD, so the
  // two disagreed on screen with nothing to explain why.
  const value = Number(n).toFixed(2);
  const formatted = !currency || currency === "AUD" ? `$${value}` : `${currency} ${value}`;
  return type && !SPEND_TYPES.has(type) ? `+${formatted}` : formatted;
 }
@@ -298,7 +302,17 @@ export default function SharedPage() {
    return <span className="ml-0.5">{sortDir === "desc" ? "↓" : "↑"}</span>;
  }
  const { data: balances = [], isLoading: balLoading } = useParticipantBalances(realTagIds);
  const { data: allTags = [] } = useTags();
  const { data: me } = useCurrentUser();
  // Names the tag scope when one is active. Non-empty means the cards below are
  // split totals rather than payable balances.
  const tagScopeLabel =
    realTagIds.length === 0
      ? null
      : realTagIds.length === 1
        ? (allTags.find((t) => String(t.id) === realTagIds[0])?.name ?? "this tag")
        : `${realTagIds.length} tags`;
  const [addingParticipant, setAddingParticipant] = useState(false);
  const [paymentModal, setPaymentModal] = useState<{ id: number; name: string; balance: number } | null>(null);
  const [showHistory, setShowHistory] = useState<number | null>(null);
@@ -351,23 +365,37 @@ export default function SharedPage() {
                  <div>
                    <p className="font-medium">{b.name}</p>
                    <p className="text-xs text-zinc-500">
-                      {settled ? "all square" : theyOweMe ? `owes you` : "you owe"}
+                      {/* With a tag filter on, payments are deliberately not
                          subtracted — so this is a split total, not a payable
                          balance, and must not claim to be one. */}
                      {tagScopeLabel
                        ? `split total in ${tagScopeLabel}`
                        : settled ? "all square" : theyOweMe ? "owes you" : "you owe"}
                    </p>
                  </div>
                  <div className="text-right">
-                    <p className={`text-lg font-semibold ${settled ? "text-zinc-500" : theyOweMe ? "text-amber-400" : "text-blue-400"}`}>
+                    <p className={`text-lg font-semibold ${tagScopeLabel ? "text-zinc-300" : settled ? "text-zinc-500" : theyOweMe ? "text-amber-400" : "text-blue-400"}`}>
                      ${net.toFixed(2)}
                    </p>
                    {b.unconverted_count > 0 && (
                      <p className="text-[11px] text-amber-500/80 mt-0.5">
                        approx · {b.unconverted_count} unconverted
                      </p>
                    )}
                  </div>
                </div>
                <div className="flex gap-2">
                  {/* Settling against a tag-scoped total would record a payment
                      for a figure that never was the debt. */}
                  {!tagScopeLabel && (
                    <button
                      onClick={() => setPaymentModal({ id: b.id, name: b.name, balance: b.total_owed })}
                      className="flex-1 py-1.5 text-xs font-medium bg-zinc-800 hover:bg-zinc-700 text-zinc-300 rounded-lg"
                    >
                      Record Payment
                    </button>
                  )}
                  <button
                    onClick={() => setShowHistory(showHistory === b.id ? null : b.id)}
                    className={`px-3 py-1.5 text-xs rounded-lg ${showHistory === b.id ? "bg-zinc-700 text-white" : "bg-zinc-800 text-zinc-500 hover:text-zinc-300"}`}
@@ -440,7 +468,16 @@ export default function SharedPage() {
                      )}
                    </td>
                    <td className={`px-4 py-3 text-right font-medium tabular-nums ${SPEND_TYPES.has(tx.transaction_type) ? "" : "text-green-400"}`}>
-                      {formatAmount(tx.amount, tx.transaction_type)}
+                      {formatAmount(tx.amount, tx.transaction_type, tx.currency)}
                      {tx.currency !== "AUD" && (
                        // Splits settle on the AUD figure, so show it next to the
                        // native one rather than leaving the two to differ silently.
                        <span className="block text-xs font-normal text-zinc-500">
                          {tx.amount_unconverted
                            ? "AUD value unknown"
                            : `≈ ${formatAmount(Number(tx.amount_aud), tx.transaction_type, "AUD")} AUD`}
                        </span>
                      )}
                    </td>
                    <td className="px-4 py-3">
                      <div className="flex flex-wrap gap-1">
@@ -1,6 +1,6 @@
 "use client";
-import { useState, useCallback, useRef, useEffect, Suspense } from "react";
+import { useState, useCallback, useRef, useEffect, Suspense, Fragment } from "react";
 import { useSearchParams } from "next/navigation";
 import { useTransactions, useBanks, useUpdateTransaction, useBulkAction, useTags, useStatement, useCreateRule, useParticipants, useRecordPayment, useCurrentUser, useTrips, useAssignTransactionsToTrip, useRules } from "@/lib/hooks";
 import { CATEGORIES, formatCategory } from "@/lib/categories";
@@ -9,7 +9,8 @@ import { TagPicker } from "@/components/tag-picker";
 import { AddTransactionModal } from "@/components/add-transaction-modal";
 import { EditTransactionModal } from "@/components/edit-transaction-modal";
 import { CsvImportModal } from "@/components/csv-import-modal";
-import type { TransactionRow } from "@/lib/queries";
+import type { TransactionRow, RoutePointRow } from "@/lib/queries";
 import { OrderDetails } from "@/components/order-details";
 import type { RuleRow } from "@/lib/hooks";
 function formatDate(d: string) {
@@ -475,6 +476,25 @@ function MultiSelect({
  );
 }
 /**
 * "Melbourne Airport (MEL) → Wyndham Vale VIC 3024" from the two stops on an
 * Uber *trip* receipt. Deliveries are excluded by the caller: their merchant
 * already identifies them, so the restaurant's street address would be clutter
 * on every food order.
 *
 * Keeps the first two comma-segments of each address — a truncation, not a
 * guess about geography. The venue or street comes first in Uber's format and
 * is the identifying part; the full text stays in the title attribute.
 */
 function routeSummary(route: RoutePointRow[] | null | undefined): string | null {
  if (!route || route.length < 2) return null;
  const short = (a: string) => a.split(",").slice(0, 2).join(",").trim();
  const from = short(route[0].address);
  const to = short(route[route.length - 1].address);
  if (!from || !to) return null;
  return `${from} → ${to}`;
 }
 export default function TransactionsPage() {
  return (
    <Suspense fallback={<p className="text-zinc-500 text-sm">Loading...</p>}>
@@ -537,6 +557,9 @@ function TransactionsContent() {
  const [splitModal, setSplitModal] = useState<{ transactionId?: number; transactionIds?: number[]; amount?: number; description: string; merchant?: string } | null>(null);
  const [addModal, setAddModal] = useState<{ prefill?: Parameters<typeof AddTransactionModal>[0]["prefill"]; title?: string } | null>(null);
  const [editModal, setEditModal] = useState<TransactionRow | null>(null);
  // Rows expanded to show the ingested receipt. A set, not a single id: the
  // point is comparing several orders without losing your place.
  const [expanded, setExpanded] = useState<Set<number>>(new Set());
  const [showImportModal, setShowImportModal] = useState(false);
  const [paymentModal, setPaymentModal] = useState<TransactionRow | null>(null);
  const [rulePrompt, setRulePrompt] = useState<{
@@ -911,8 +934,8 @@ function TransactionsContent() {
              <tr><td colSpan={11} className="p-8 text-center text-zinc-500">No transactions found</td></tr>
            ) : (
              data.data.map((t) => (
                <Fragment key={t.id}>
                <tr
                  key={t.id}
                  className={`border-b border-zinc-800/50 hover:bg-zinc-900/30 ${
                    selected.has(t.id) ? "bg-zinc-800/40" : ""
                  }`}
@@ -928,9 +951,39 @@ function TransactionsContent() {
                  <td className={`p-2 whitespace-nowrap sticky left-8 z-10 border-r border-zinc-800/80 ${selected.has(t.id) ? "bg-zinc-800" : "bg-zinc-950"}`}>{formatDate(t.transaction_date)}</td>
                  <td className="p-2 whitespace-nowrap text-zinc-500 text-xs">{formatDate(t.created_at)}</td>
                  <td className="p-2 max-w-xs">
                    <div className="flex items-start gap-1.5">
                      {t.order_platform && (
                        // Only where there IS a receipt behind the row. A
                        // disclosure arrow on every transaction would promise
                        // detail that mostly does not exist.
                        <button
                          onClick={() => setExpanded((prev) => {
                            const next = new Set(prev);
                            if (next.has(t.id)) next.delete(t.id); else next.add(t.id);
                            return next;
                          })}
                          className="text-zinc-600 hover:text-zinc-300 leading-none mt-0.5 shrink-0"
                          title={expanded.has(t.id) ? "Hide receipt" : "Show the receipt this came from"}
                          aria-expanded={expanded.has(t.id)}
                        >
                          {expanded.has(t.id) ? "▾" : "▸"}
                        </button>
                      )}
                      <p className="truncate" title={t.description}>{t.description}</p>
-                    {t.notes && (
+                    </div>
                    {t.notes ? (
                      <p className="truncate text-xs text-zinc-500 italic mt-0.5" title={t.notes}>{t.notes}</p>
                    ) : t.order_platform === "uber" && routeSummary(t.order_route) && (
                      // Five rows all reading "Order - Uber Trip" are
                      // indistinguishable. Where the trip went is what tells
                      // them apart, and it was already stored. A note the user
                      // wrote always wins — this only fills an empty line.
                      <p
                        className="truncate text-xs text-zinc-500 italic mt-0.5"
                        title={t.order_route!.map((r) => `${r.label}${r.time ? ` ${r.time}` : ""}: ${r.address}`).join("\n")}
                      >
                        {routeSummary(t.order_route)}
                      </p>
                    )}
                  </td>
                  <td className="p-2 max-w-[150px]">
@@ -1056,6 +1109,15 @@ function TransactionsContent() {
                    </button>
                  </td>
                </tr>
                {expanded.has(t.id) && (
                  <tr className="border-b border-zinc-800/50 bg-zinc-900/40">
                    <td />
                    <td colSpan={10} className="px-4 py-3">
                      <OrderDetails transactionId={t.id} currency={t.currency ?? null} bare />
                    </td>
                  </tr>
                )}
                </Fragment>
              ))
            )}
          </tbody>
@@ -277,7 +277,7 @@ export default function TripDetailPage({ params }: { params: Promise<{ id: strin
              <table className="w-full text-sm">
                <thead>
                  <tr className="border-b border-zinc-800">
-                    {["Person", "Share of this trip"].map((h) => (
+                    {["Person", "Outstanding on this trip"].map((h) => (
                      <th
                        key={h}
                        className={`px-5 py-2.5 text-xs text-zinc-500 font-medium ${h === "Person" ? "text-left" : "text-right"}`}
@@ -291,7 +291,16 @@ export default function TripDetailPage({ params }: { params: Promise<{ id: strin
                  {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">${Number(p.owed).toFixed(2)}</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
                          </span>
                        )}
                      </td>
                    </tr>
                  ))}
                </tbody>
@@ -10,6 +10,7 @@ import {
  useTrips,
 } from "@/lib/hooks";
 import { SplitModal } from "./split-modal";
 import { OrderDetails } from "./order-details";
 import { CATEGORIES, formatCategory } from "@/lib/categories";
 import type { TransactionRow, TagRow } from "@/lib/queries";
@@ -314,6 +315,8 @@ export function EditTransactionModal({
              )}
            </div>
            <OrderDetails transactionId={transaction.id} currency={transaction.currency ?? null} />
          </div>
          {/* Footer */}
@@ -0,0 +1,95 @@
 "use client";
 import { useOrderReceipt, type OrderReceipt } from "@/lib/hooks";
 const PLATFORM_LABEL: Record<string, string> = {
  doordash: "DoorDash",
  ubereats: "Uber Eats",
  uber: "Uber",
 };
 /**
 * The receipt behind a delivery order: what was actually bought, and where it
 * went. All of it was already stored at ingest and none of it was reachable —
 * the row showed a merchant and a total and nothing else.
 *
 * Read-only on purpose. This is what a provider sent, not something to edit.
 */
 export function OrderDetails({
  transactionId,
  currency,
  bare = false,
 }: {
  transactionId: number;
  currency: string | null;
  /** Drop the top border and heading spacing when embedded in a table row. */
  bare?: boolean;
 }) {
  const { data: receipt, isLoading } = useOrderReceipt(transactionId);
  if (isLoading || !receipt) return null;
  const cur = receipt.currency ?? currency ?? "AUD";
  const fmt = (n: number) => (cur === "AUD" ? `$${n.toFixed(2)}` : `${cur} ${n.toFixed(2)}`);
  const items: OrderReceipt["line_items"] = receipt.line_items ?? [];
  const route: OrderReceipt["route"] = receipt.route ?? [];
  return (
    <div className={bare ? "" : "border-t border-zinc-800 pt-4"}>
      <div className="flex items-baseline justify-between mb-2">
        <p className="text-xs text-zinc-500">
          Order details
          {receipt.platform && (
            <span className="ml-1.5 text-zinc-400">{PLATFORM_LABEL[receipt.platform] ?? receipt.platform}</span>
          )}
        </p>
        {receipt.card_last4 && (
          <span className="text-xs text-zinc-600">card ••••{receipt.card_last4}</span>
        )}
      </div>
      {items.length > 0 ? (
        <ul className="space-y-1.5 mb-3">
          {items.map((it, i) => (
            <li key={i} className="flex gap-2 text-xs">
              <span className="text-zinc-600 tabular-nums shrink-0">{it.qty}×</span>
              <span className="text-zinc-300 flex-1 min-w-0">
                {it.description}
                {it.options && it.options.length > 0 && (
                  <span className="block text-zinc-600">{it.options.join(" · ")}</span>
                )}
              </span>
              <span className="text-zinc-400 tabular-nums shrink-0">{fmt(Number(it.amount))}</span>
            </li>
          ))}
        </ul>
      ) : (
        // Uber itemises groceries but not restaurant orders, and orders taken
        // before this was parsed have none either. Say which, rather than
        // showing an empty list that reads like a bug.
        <p className="text-xs text-zinc-600 italic mb-3">
          No itemised list on this receipt
        </p>
      )}
      {route.length > 0 && (
        <div className="space-y-1">
          {route.map((pt, i) => (
            <div key={i} className="flex gap-2 text-xs">
              <span className="text-zinc-600 shrink-0 w-24">
                {pt.label}
                {pt.time && <span className="block text-zinc-700">{pt.time}</span>}
              </span>
              <span className="text-zinc-400 flex-1">{pt.address}</span>
            </div>
          ))}
        </div>
      )}
      {receipt.order_reference && !receipt.order_reference.startsWith("msg:") && (
        <p className="mt-3 text-[11px] text-zinc-700 font-mono break-all">
          {receipt.order_reference}
        </p>
      )}
    </div>
  );
 }
@@ -1,7 +1,7 @@
 // Shared SQL fragments for analytics queries, so spend/income semantics stay
 // identical across routes.
 //
-// Two rules every analytics query must follow:
+// Three rules every analytics query must follow:
 //
 // 1. Join `statements` with LEFT JOIN and scope on COALESCE(t.owner_id, s.owner_id).
 //    An INNER JOIN silently drops every manual/CSV transaction (statement_id IS
@@ -11,6 +11,23 @@
 //    bank account and again as the underlying purchases on the card statement.
 //    Categorising the money movement as `transfers` and excluding it here is what
 //    stops the double count. Investments are a balance-sheet move, not spend.
 // 3. Apply EXCLUDE_RECONCILED_SOURCE to every row-level query. The transaction
 //    queries have always done this (`queries.ts`); analytics never did, which is
 //    the other half of the same double count.
 /**
 * `YYYY-MM-DD` for a Date, read in local time.
 *
 * `toISOString().slice(0, 10)` is the obvious thing and it is wrong here: these
 * are calendar boundaries built with `new Date(y, m, 1)`, which is local
 * midnight. In any timezone east of UTC that converts to the *previous* day, so
 * every window silently started and ended a day early — visible once the fees
 * endpoint began reporting the range it had used ("2026-04-30" for a window
 * meant to open on 1 May).
 */
 export function toDateStr(d: Date): string {
  return `${d.getFullYear()}-${String(d.getMonth() + 1).padStart(2, "0")}-${String(d.getDate()).padStart(2, "0")}`;
 }
 /** Owner scoping that works for both statement-linked and manual transactions. */
 export const OWNER_SCOPE = `COALESCE(t.owner_id, s.owner_id)`;
@@ -18,6 +35,56 @@ export const OWNER_SCOPE = `COALESCE(t.owner_id, s.owner_id)`;
 /** Join clause to pair with OWNER_SCOPE. */
 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.
 *
 * 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
 * the same purchase is spent twice. `queries.ts` has always applied this; the
 * analytics routes did not, so every reconciled row was double-counted in the
 * category totals, movers, Pareto, and merchant rankings.
 *
 * Scoped to `statement_id IS NULL` deliberately: the *source* row is the manual
 * one. A statement line pointing at something else is the survivor, not the
 * duplicate.
 *
 * Order-receipt rows (`payment_method = 'credits'`) are unaffected — they are
 * 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.
 */
 export const EXCLUDE_RECONCILED_SOURCE = `NOT (t.statement_id IS NULL AND t.reconciled_with_id IS NOT NULL)`;
 /**
 * The currency `t.amount` is actually denominated in.
 *
 * Two different conventions meet here and the COALESCE order is what keeps them
 * apart:
 *
 *  - A statement row is denominated in its statement's currency. If that row is
 *    an overseas purchase on an AUD statement, `amount` is still AUD and
 *    `foreign_currency_code` merely records what was originally charged — so
 *    `s.currency` must win.
 *  - An order-receipt row has no statement. There, `amount` IS the native
 *    figure and `foreign_currency_code` names it (order-ingestion.ts leaves
 *    `amount_aud` NULL rather than asserting an FX rate it does not have).
 *
 * Reading `s.currency` alone labels every foreign order row as AUD.
 * `s` must be the statements alias in scope.
 */
 export const NATIVE_CURRENCY = `COALESCE(s.currency, t.foreign_currency_code, 'AUD')`;
 /**
 * True when a row's AUD value is unknown: it is denominated in something other
 * than AUD and carries no converted figure.
 *
 * Every settlement total uses `COALESCE(t.amount_aud, t.amount)`, which for such
 * a row silently nets a foreign figure against AUD ones. Rather than drop the
 * row (which changes a balance with no trace) or convert it (with no rate),
 * count these and let the UI say the balance is incomplete.
 */
 export const AMOUNT_UNCONVERTED = `(t.amount_aud IS NULL AND ${NATIVE_CURRENCY} <> 'AUD')`;
 /** Transaction types that represent money going out. */
 export const SPEND_TYPES = `('debit', 'fee', 'interest')`;
@@ -104,3 +171,42 @@ export const SPEND_SIGNED = `CASE
  WHEN t.transaction_type IN ('refund', 'credit') THEN -(${SPEND_BASE})
  ELSE (${SPEND_BASE})
 END`;
 /**
 * A split that still counts towards what someone owes.
 *
 * This is the line between the two questions the same table answers, and
 * conflating them is what made three different "owed" figures disagree:
 *
 *  - **Owed** — what is still outstanding between two people. Must apply this.
 *  - **Spend** — what a purchase cost me. Must NOT apply this.
 *
 * A settled split is still a real expense: my half of a 2025 grocery shop is my
 * spend whether or not the other half was ever repaid. Filtering settled rows
 * out of `myShare`/`mySplitOf` would re-inflate exactly the figures that
 * importing settled history exists to correct.
 *
 * `settled` marks obligations discharged OUTSIDE this app — imported
 * SplitMyExpenses history, whose repayments happened on a platform we no longer
 * run and which therefore has no `split_payments` row here.
 *
 * A live obligation is NOT settled by flipping this. It 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. So there is
 * 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`.
 */
 export const ACTIVE_OBLIGATION = `ts.settled = false`;
 /**
 * The tab a split belongs to: its transaction's trip, else the household.
 *
 * Membership already lives on `transaction_overrides.trip_id`, so this is a
 * read of existing data rather than a new grouping key. Assumes an
 * `transaction_overrides` alias `o` is joined (LEFT — a transaction with no
 * override row has no trip, which is the common case and means household).
 */
 export const SPLIT_SCOPE = `o.trip_id`;
@@ -1,7 +1,7 @@
 "use client";
 import { useQuery, useMutation, useQueryClient } from "@tanstack/react-query";
-import type { TransactionRow, StatementRow, TagRow, TripRow, TripAnalytics } from "./queries";
+import type { TransactionRow, StatementRow, TagRow, TripRow, TripAnalytics, ParticipantBalance } from "./queries";
 export type { TripRow, TripAnalytics };
 import type { CurrentUser } from "./auth";
@@ -216,7 +216,7 @@ export function useParticipants() {
 }
 export function useParticipantBalances(tagIds?: string[]) {
-  return useQuery<{ id: number; name: string; total_owed: number; unsettled_count: number }[]>({
+  return useQuery<ParticipantBalance[]>({
    queryKey: ["participant-balances", tagIds],
    queryFn: async () => {
      const params = tagIds?.length ? `?tag_ids=${tagIds.join(",")}` : "";
@@ -250,6 +250,33 @@ export function useTransactionSplits(transactionId: number) {
  });
 }
 export interface OrderReceipt {
  platform: "doordash" | "ubereats" | "uber" | null;
  order_reference: string | null;
  line_items: { qty: number; description: string; amount: number; options?: string[] }[];
  route: { label: string; time: string | null; address: string }[];
  subtotal: string | null;
  amount: string | null;
  currency: string | null;
  card_last4: string | null;
  flags: string[];
  source_email_subject: string | null;
  transaction_date: string | null;
 }
 /** The receipt behind a transaction, or null when it did not come from one. */
 export function useOrderReceipt(transactionId: number) {
  return useQuery<OrderReceipt | null>({
    queryKey: ["order-receipt", transactionId],
    queryFn: async () => {
      const res = await fetch(`/api/transactions/${transactionId}/order`);
      if (!res.ok) return null;
      return res.json();
    },
    staleTime: Infinity, // a receipt never changes
  });
 }
 export function useSetSplits() {
  const qc = useQueryClient();
  return useMutation({
@@ -748,6 +775,27 @@ export function useMonthlyAnalytics(months?: number) {
  });
 }
 /**
 * Sparse by day-of-month; a missing day means zero.
 *   daily      → { "2026-07": { 3: 42.10 } }
 *   byCategory → { "2026-07": { dining: { 3: 42.10 } } }
 */
 export interface DailySpend {
  daily: Record<string, Record<number, number>>;
  byCategory: Record<string, Record<string, Record<number, number>>>;
 }
 export function useDailySpend(months?: number) {
  const m = months || 12;
  return useQuery<DailySpend>({
    queryKey: ["analytics", "daily", m],
    queryFn: async () => {
      const res = await fetch(`/api/analytics/daily?months=${m}`);
      return res.json();
    },
  });
 }
 export interface SubscriptionRow {
  merchant: string;
  category: string;
@@ -788,16 +836,25 @@ export interface FeeTxnRow {
  bank_name: string;
 }
-export function useFees() {
+export interface FeePeriod {
  months: number;
  from: string | null;
  to: string | null;
  all_time: boolean;
 }
 /** `months = 0` means all time. */
 export function useFees(months = 12) {
  return useQuery<{
    by_bank: FeeBankRow[];
    transactions: FeeTxnRow[];
    total_fees: number;
    total_interest: number;
    period: FeePeriod;
  }>({
-    queryKey: ["analytics", "fees"],
+    queryKey: ["analytics", "fees", months],
    queryFn: async () => {
-      const res = await fetch("/api/analytics/fees");
+      const res = await fetch(`/api/analytics/fees?months=${months}`);
      return res.json();
    },
  });
@@ -5,6 +5,36 @@ export * from "./order-parse";
 export const CUTOVER_DATE = "2026-01-09";
 /**
 * Owner for ingested orders. Analytics scope on COALESCE(t.owner_id,
 * s.owner_id); an ingested order has no statement, so leaving owner_id NULL
 * hides it from every view in the app while it sits in the table.
 */
 export const DEFAULT_OWNER_ID = 1;
 /** Human labels for the platform a receipt came from. */
 export const PLATFORM_LABEL: Record<ParsedOrder["platform"], string> = {
  doordash: "DoorDash",
  ubereats: "Uber Eats",
  uber: "Uber",
 };
 /**
 * Transaction description.
 *
 * The merchant alone ("Order - Burger Corner") does not say where to go and
 * look for the detail, and there are restaurants on both platforms. The
 * platform is the one thing the parser always knows and used to discard.
 */
 export function orderDescription(platform: ParsedOrder["platform"], merchant: string): string {
  const label = PLATFORM_LABEL[platform];
  // A trip's merchant is literally "Uber Trip", so the suffix would restate it
  // — "Order - Uber Trip (Uber)". The addresses that actually identify a trip
  // are in the Order details panel, not squeezed into the description.
  if (merchant.toLowerCase().includes(label.toLowerCase())) return `Order - ${merchant}`;
  return `Order - ${merchant} (${label})`;
 }
 export interface IngestResult {
  transactionId: number | null;
  metadataId: number | null;
@@ -86,7 +116,13 @@ async function ensureTag(name: string): Promise<number> {
 */
 export async function processOrderIngestion(
  order: ParsedOrder,
-  options: { messageId?: string; backfillMode?: boolean } = {}
+  options: {
    messageId?: string;
    backfillMode?: boolean;
    ownerId?: number;
    subject?: string;
    sender?: string;
  } = {}
 ): Promise<IngestResult> {
  const flags = [...order.flags];
  const day = order.order_datetime.slice(0, 10);
@@ -108,7 +144,6 @@ export async function processOrderIngestion(
  // ---- resolve the credits portion ----------------------------------------
  let creditsAmount: number | null = null;
  let cardAmount: number | null = order.payment.card_amount;
  if (order.payment.ambiguous) {
    const { cardAmount: reconciled } = await reconcileCardLeg(order);
@@ -122,9 +157,7 @@ export async function processOrderIngestion(
      // resolves it once the statement lands. Backfill hits the same path and
      // resolves immediately, because those statements are already imported.
      flags.push("awaiting_card_statement");
      cardAmount = null;
    } else {
      cardAmount = reconciled;
      const remainder = Number((order.totals.total_charged - reconciled).toFixed(2));
      if (remainder > 0.02) {
        creditsAmount = remainder;
@@ -151,11 +184,11 @@ export async function processOrderIngestion(
         transaction_date, description, amount, amount_aud, category, payment_method,
         merchant_name, merchant_normalized, transaction_type,
         foreign_currency_amount, foreign_currency_code, owner_id
-       ) VALUES ($1,$2,$3,$4,$5,'credits',$6,$6,'debit',$7,$8,NULL)
+       ) VALUES ($1,$2,$3,$4,$5,'credits',$6,$6,'debit',$7,$8,$9)
       RETURNING id`,
      [
        day,
-        `Order - ${order.merchant_name}`,
+        orderDescription(order.platform, order.merchant_name),
        creditsAmount,
        // No FX rate is available at ingest, so amount_aud is left NULL for
        // foreign orders rather than asserting a conversion we cannot make.
@@ -164,6 +197,11 @@ export async function processOrderIngestion(
        order.merchant_name,
        isAud ? null : creditsAmount,
        isAud ? null : order.currency,
        // Owner scoping is COALESCE(t.owner_id, s.owner_id). These rows carry
        // no statement, so a NULL owner_id makes them invisible in every view
        // in the app — present in the table, absent from the UI. The backfill
        // inserted 85 rows nobody could see.
        options.ownerId ?? DEFAULT_OWNER_ID,
      ]
    );
    transactionId = txn!.id;
@@ -185,8 +223,9 @@ export async function processOrderIngestion(
    `INSERT INTO expense_metadata (
       transaction_id, source, source_message_id, order_reference, line_items,
       subtotal, amount, merchant_normalized, transaction_date,
-       card_last4, currency, flags, reconciled_at
+       card_last4, currency, flags, reconciled_at,
-     ) VALUES ($1,'email',$2,$3,$4::jsonb,$5,$6,$7,$8,$9,$10,$11::jsonb,$12)
+       platform, source_email_subject, source_email_from, route
     ) VALUES ($1,'email',$2,$3,$4::jsonb,$5,$6,$7,$8,$9,$10,$11::jsonb,$12,$13,$14,$15,$16::jsonb)
     RETURNING id`,
    [
      transactionId,
@@ -201,6 +240,10 @@ export async function processOrderIngestion(
      order.currency,
      JSON.stringify(flags),
      pending ? null : new Date().toISOString(),
      order.platform,
      options.subject ?? null,
      options.sender ?? null,
      JSON.stringify(order.route ?? []),
    ]
  );
@@ -230,9 +273,10 @@ export async function reconcilePendingOrders(): Promise<{
    merchant_normalized: string;
    card_last4: string | null;
    currency: string | null;
    platform: ParsedOrder["platform"] | null;
  }>(
    `SELECT id, order_reference, amount::text, transaction_date::text,
-            merchant_normalized, card_last4, currency
+            merchant_normalized, card_last4, currency, platform
       FROM expense_metadata
      WHERE transaction_id IS NULL
        AND reconciled_at IS NULL
@@ -246,7 +290,7 @@ export async function reconcilePendingOrders(): Promise<{
    const total = Number(row.amount);
    const probe: ParsedOrder = {
      order_reference: row.order_reference,
-      platform: "doordash",
+      platform: row.platform ?? "doordash",
      merchant_name: row.merchant_normalized,
      order_datetime: `${row.transaction_date}T00:00:00Z`,
      currency: row.currency || "AUD",
@@ -256,6 +300,7 @@ export async function reconcilePendingOrders(): Promise<{
        service_fee: null, tip: null, discounts: null, total_charged: total,
      },
      line_items: [],
      route: [],
      is_family: false,
      flags: [],
    };
@@ -280,9 +325,9 @@ export async function reconcilePendingOrders(): Promise<{
        `INSERT INTO transactions (
           transaction_date, description, amount, amount_aud, category,
           payment_method, merchant_name, merchant_normalized, transaction_type, owner_id
-         ) VALUES ($1,$2,$3,$3,$5,'credits',$4,$4,'debit',NULL)
+         ) VALUES ($1,$2,$3,$3,$5,'credits',$4,$4,'debit',$6)
         RETURNING id`,
-        [row.transaction_date, `Order - ${row.merchant_normalized}`, remainder, row.merchant_normalized, category]
+        [row.transaction_date, orderDescription(row.platform ?? "doordash", row.merchant_normalized), remainder, row.merchant_normalized, category, DEFAULT_OWNER_ID]
      );
      txnId = txn!.id;
      created++;
@@ -20,6 +20,18 @@ export interface LineItem {
  options?: string[];
 }
 /**
 * A stop on the receipt's map: pick-up, delivery, or (for a trip) the ride's
 * start and end. Uber prints these for every order under `Order details`.
 */
 export interface RoutePoint {
  /** "Pick-up" / "Delivery" — whatever the receipt itself calls it. */
  label: string;
  /** Local time as printed, e.g. "1:20 pm". No date; the receipt gives none. */
  time: string | null;
  address: string;
 }
 export interface PaymentBreakdown {
  credits_amount: number | null;
  card_amount: number | null;
@@ -47,6 +59,8 @@ export interface ParsedOrder {
  payment: PaymentBreakdown;
  totals: OrderTotals;
  line_items: LineItem[];
  /** Uber only. Empty for DoorDash, whose receipts carry no addresses. */
  route: RoutePoint[];
  is_family: boolean;
  flags: string[];
 }
@@ -98,15 +112,6 @@ const decodeEntities = (s: string) =>
 const collapse = (s: string) => s.replace(/\s+/g, " ").trim();
 /** URL-decodes without throwing on malformed percent-escapes. */
 function safeDecode(s: string): string {
  try {
    return decodeURIComponent(s.replace(/%(?![0-9a-f]{2})/gi, "%25"));
  } catch {
    return s;
  }
 }
 const money = (raw: string): number => Math.abs(parseFloat(raw.replace(/[$,]/g, "")));
 /**
@@ -125,6 +130,103 @@ function tdPairValue(html: string, label: string): number | null {
  return m ? money(m[1]) : null;
 }
 /**
 * Uber itemises only *grocery* orders — a restaurant receipt states a total and
 * nothing else, which is why 67 of the 101 backfilled orders have no items.
 * When it does itemise, the markup is far better than DoorDash's: every cell
 * carries a `data-testid` naming its role and the item's own uuid, so quantity,
 * title and amount can be bound to each other by id rather than by position.
 */
 function parseUberLineItems(html: string): LineItem[] {
  const items: LineItem[] = [];
  const titleRe =
    /data-testid="shoppingCart_item_title_([0-9a-f-]+)"[^>]*>([\s\S]*?)<\/td>/gi;
  for (const m of html.matchAll(titleRe)) {
    const [, id, rawTitle] = m;
    const description = collapse(decodeEntities(stripTags(rawTitle)));
    if (!description) continue;
    const qtyM = html.match(
      new RegExp(`data-testid="shoppingCart_item_quantity_${id}"[^>]*>\\s*(\\d+)\\s*<`, "i")
    );
    const amtM = html.match(
      new RegExp(
        `data-testid="shoppingCart_item_amount_${id}"[^>]*>([\\s\\S]*?)<\\/td>`,
        "i"
      )
    );
    const amtText = amtM ? collapse(decodeEntities(stripTags(amtM[1]))) : "";
    const amtNum = amtText.match(/(-?[\d,]+\.\d{2})/);
    items.push({
      qty: qtyM ? parseInt(qtyM[1], 10) : 1,
      description,
      // A sold-out item prints 0.00 and is genuinely part of the order — it
      // explains a total that does not match what was asked for. Keep it.
      amount: amtNum ? money(amtNum[1]) : 0,
    });
  }
  return items;
 }
 /**
 * Uber's `Order details` block, anchored on `data-testid="address_point_N_*"`.
 *
 * The template repeats the whole block twice (once hidden for narrow screens),
 * so the same stop appears more than once and has to be de-duplicated. This is
 * the same markup a *trip* receipt uses for its start and destination — rides
 * are not ingested today, but the reader will not need changing when they are.
 */
 function parseUberRoute(html: string): RoutePoint[] {
  const seen = new Set<string>();
  const points: RoutePoint[] = [];
  const labelRe = /data-testid="address_point_(\d+)_time"[^>]*>([\s\S]*?)<\/td>/gi;
  for (const m of html.matchAll(labelRe)) {
    const [, idx, rawLabel] = m;
    const addrM = html.match(
      new RegExp(`data-testid="address_point_${idx}_address"[^>]*>([\\s\\S]*?)<\\/td>`, "i")
    );
    if (!addrM) continue;
    const address = collapse(decodeEntities(stripTags(addrM[1])));
    // Delivery receipts share one cell between time and label — "1:20 pm -
    // Pick-up". Trip receipts print the time alone, with no label at all, so
    // the naive split put the time in `label` and left `time` null. Position
    // carries the meaning there: first stop is where the ride began.
    const combined = collapse(decodeEntities(stripTags(rawLabel)));
    const split = combined.match(/^(.*?)\s+-\s+(.*)$/);
    let time: string | null;
    let label: string;
    if (split) {
      time = split[1];
      label = split[2];
    } else if (/^\d{1,2}:\d{2}\s*(am|pm)?$/i.test(combined)) {
      time = combined;
      label = ""; // filled in positionally below — the receipt gives none
    } else {
      time = null;
      label = combined;
    }
    const key = `${label}|${time}|${address}`;
    if (!address || seen.has(key)) continue;
    seen.add(key);
    points.push({ label, time, address });
  }
  // A trip receipt labels neither end. Position is the only thing that says
  // which is which, and for a two-stop trip it says it unambiguously. Only
  // filled where the receipt itself was silent, so a future template that does
  // label its stops keeps its own wording.
  if (points.length === 2 && points.every((p) => !p.label)) {
    points[0].label = "Pick-up";
    points[1].label = "Drop-off";
  }
  return points;
 }
 function parseDoorDashLineItems(html: string): LineItem[] {
  // <td width="10%">1x</td><td width="75%"><b>Name</b> (Cat)<br><font>• Opt</font>…</td><td width="15%">$22.10</td>
  const re =
@@ -181,7 +283,6 @@ function parseMerchant(platform: string, meta: MessageMeta, text: string): strin
 }
 function parsePayment(platform: string, html: string, text: string): PaymentBreakdown {
  // eslint-disable-next-line no-param-reassign
  const out: PaymentBreakdown = {
    credits_amount: null,
    card_amount: null,
@@ -226,8 +327,14 @@ function parsePayment(platform: string, html: string, text: string): PaymentBrea
  const cash = text.match(/Uber Cash\s*(?:[A-Z]{3})?\s*\$?([\d,]+\.\d{2})/i);
  if (cash) out.credits_amount = money(cash[1]);
  // Anchor on the masking, not on a list of card brands. Uber labels the card
  // leg with whatever the issuer is called — "Westpac ••••8032 $15.33",
  // "Mastercard ••••3893 (CBA Ultimate) CHF 51.23" — so a brand allowlist
  // silently drops the card half of a mixed payment. Found in the backfill
  // dry-run: Uber Cash $1.17 + Westpac ••••8032 $15.33 against a $16.50 total,
  // which validateOrderTotals correctly refused rather than under-recording.
  const card = text.match(
-    /(?:Visa|MasterCard|American Express|Amex)[^\d]*(\d{4})[^\d]*(?:[A-Z]{3})?\s*\$?([\d,]+\.\d{2})/i
+    /(?:••••|\*{4}|\u2022{4})\s*(\d{4})[^\d]{0,40}?\$?\s*([\d,]+\.\d{2})/
  );
  if (card) {
    out.card_last4 = card[1];
@@ -272,6 +379,20 @@ export function parseOrderHTML(html: string, meta: MessageMeta): ParsedOrder {
    );
  }
  // Uber sends TWO mails per trip with the same subject and the same total: a
  // "charge summary" when the trip ends, then the real receipt once payment
  // settles. The summary says so itself — "This is not a payment receipt ...
  // You will receive a trip receipt when the payment is processed with payment
  // information" — and it carries no tripReference, so order_reference would
  // fall back to `msg:<message-id>` and I7 could not dedupe it against the
  // receipt that follows. Every trip would be recorded twice.
  if (/This is not a payment receipt|This is your charge summary/i.test(text)) {
    throw new NotAReceiptError(
      "charge summary, not a payment receipt — the real receipt follows",
      meta.messageId
    );
  }
  const platform = detectPlatform(meta, text);
  const merchant_name = parseMerchant(platform, meta, text);
@@ -379,14 +500,15 @@ export function parseOrderHTML(html: string, meta: MessageMeta): ParsedOrder {
  const payment = parsePayment(platform, clean, text);
  if (payment.ambiguous && is_family) {
    // [Family] receipts name the payer, not an instrument ("Payments Siddharth
-    // LKR 3,783.20"), so no split is recoverable and there is no card leg to
+    // LKR 3,783.20"). An earlier version read that as credits-funded. It is
-    // reconcile against — parking them would mean never importing them, which
+    // not: the card statement carries all four of them (CBA ...3893, exact
-    // fails the actual requirement (import, tag, exclude from budgets).
+    // foreign_currency_amount matches), so creating a transaction duplicated
-    // Treated as credits so the order is recorded and tagged. Safe because the
+    // spend that was already recorded — precisely the double-count I5 exists to
-    // family tag removes it from every budget regardless of instrument.
+    // prevent.
-    payment.ambiguous = false;
+    //
-    payment.credits_amount = totals.total_charged;
+    // Treated as card-settled: provenance only, no transaction. The statement
-    flags.push("family_payment_assumed_credits");
+    // line IS the transaction, and it is what should carry the `family` tag.
    flags.push("family_card_settled_no_transaction");
  } else if (payment.ambiguous) {
    // Split not stated and resolvable from the card statement — left for the
    // ingestion runner to reconcile, not guessed here.
@@ -400,11 +522,15 @@ export function parseOrderHTML(html: string, meta: MessageMeta): ParsedOrder {
  // ---- line items ----------------------------------------------------------
  // Uber Eats receipts carry no itemisation (verified across 29 real mails).
  const line_items =
-    platform === "doordash" ? parseDoorDashLineItems(clean) : [];
+    platform === "doordash" ? parseDoorDashLineItems(clean) : parseUberLineItems(clean);
  if (platform === "doordash" && line_items.length === 0) {
    flags.push("no_line_items_parsed");
  }
  // Uber prints addresses on every receipt; DoorDash prints none at all, so an
  // empty route there is expected rather than a parse failure.
  const route = platform === "doordash" ? [] : parseUberRoute(clean);
  const currency =
    explicitCurrency ||
    (/\b(NZD|USD|LKR|CHF|EUR|GBP|SGD|INR)\b/.test(text)
@@ -420,6 +546,7 @@ export function parseOrderHTML(html: string, meta: MessageMeta): ParsedOrder {
    payment,
    totals,
    line_items,
    route,
    is_family,
    flags,
  };
@@ -1,4 +1,11 @@
 import { queryRaw } from "./db";
 import { EXCLUDE_RECONCILED_SOURCE, NATIVE_CURRENCY, AMOUNT_UNCONVERTED, ACTIVE_OBLIGATION, STATEMENTS_JOIN } from "./analytics-sql";
 export interface RoutePointRow {
  label: string;
  time: string | null;
  address: string;
 }
 export interface TagRow {
  id: number;
@@ -26,8 +33,11 @@ export interface TransactionRow {
  principal_amount: number | null;
  interest_amount: number | null;
  // How it was paid (migration 0016). NULL = unknown, treated as reconcilable.
-  // 'cash' is excluded from reconciliation — see notCash().
+  // 'cash' and 'credits' are excluded from reconciliation — see needsCardMatch().
  payment_method: string | null;
  /** Uber pick-up/drop-off, when this row came from an order receipt. */
  order_route: RoutePointRow[] | null;
  order_platform: "doordash" | "ubereats" | "uber" | null;
  // override fields
  category_override: string | null;
  merchant_override: string | null;
@@ -40,9 +50,13 @@ export interface TransactionRow {
  my_amount: number;
  // statement context (null for manual transactions)
  bank_name: string;
-  // Native currency of the statement this row came from ('AUD' for manual rows).
+  // The currency `amount` is denominated in; `amount_aud` is the converted
-  // `amount` is in this currency; `amount_aud` is the converted figure.
+  // figure where one exists. Usually the statement's currency, but an
  // order-receipt row has no statement and carries its own — see
  // NATIVE_CURRENCY. Not simply 'AUD' for every statement-less row.
  currency: string;
  /** True when `amount` is non-AUD and no converted figure exists. */
  amount_unconverted: boolean;
  owner_id: number;
  owner_name: string;
  // tags
@@ -115,7 +129,7 @@ interface TransactionFilters {
 export async function getTransactions(ownerId: number, filters: TransactionFilters) {
  const conditions: string[] = [
    `(COALESCE(t.owner_id, s.owner_id) = $1 OR EXISTS (SELECT 1 FROM transaction_splits ts_me WHERE ts_me.transaction_id = t.id AND ts_me.participant_id = $1))`,
-    `NOT (t.statement_id IS NULL AND t.reconciled_with_id IS NOT NULL)`,
+    EXCLUDE_RECONCILED_SOURCE,
  ];
  const params: unknown[] = [ownerId];
  let paramIdx = 2;
@@ -133,17 +147,24 @@ export async function getTransactions(ownerId: number, filters: TransactionFilte
    params.push(filters.categories);
  }
  if (filters.bank_names?.length) {
    // "Manual" and "Gift Card" are not banks — they are the two shapes a
    // statement-less row can take, and bankLabel() decides which. The filter
    // has to split on the same condition or the chip selects nothing.
    const hasManual = filters.bank_names.includes("Manual");
-    const bankList = filters.bank_names.filter((b) => b !== "Manual");
+    const hasGiftCard = filters.bank_names.includes("Gift Card");
-    if (hasManual && bankList.length > 0) {
+    const bankList = filters.bank_names.filter((b) => b !== "Manual" && b !== "Gift Card");
-      conditions.push(`(t.statement_id IS NULL OR s.bank_name = ANY($${paramIdx++}::text[]))`);
+    const alternatives: string[] = [];
-      params.push(bankList);
+    if (hasManual) {
-    } else if (hasManual) {
+      alternatives.push(`(t.statement_id IS NULL AND t.payment_method IS DISTINCT FROM 'credits')`);
-      conditions.push(`t.statement_id IS NULL`);
+    }
-    } else {
+    if (hasGiftCard) {
-      conditions.push(`s.bank_name = ANY($${paramIdx++}::text[])`);
+      alternatives.push(`(t.statement_id IS NULL AND t.payment_method = 'credits')`);
    }
    if (bankList.length > 0) {
      alternatives.push(`s.bank_name = ANY($${paramIdx++}::text[])`);
      params.push(bankList);
    }
    conditions.push(`(${alternatives.join(" OR ")})`);
  }
  if (filters.tag_ids?.length) {
    const noTags = filters.tag_ids.includes("untagged");
@@ -210,8 +231,9 @@ export async function getTransactions(ownerId: number, filters: TransactionFilte
      o.category_override, o.merchant_normalized as merchant_override, o.notes, o.my_share_percent,
      COALESCE(o.category_override, t.category) as effective_category,
      COALESCE(o.merchant_normalized, t.merchant_normalized, t.merchant_name) as effective_merchant,
-      COALESCE(s.bank_name, 'Manual') as bank_name,
+      ${bankLabel()} as bank_name,
-      COALESCE(s.currency, 'AUD') as currency,
+      ${NATIVE_CURRENCY} as currency,
      ${AMOUNT_UNCONVERTED} as amount_unconverted,
      -- My share, resolved the same way analytics does it (see myShare in
      -- analytics-sql.ts): explicit split row, then override, then whatever is
      -- left after everyone else. Computed here so the UI cannot drift from
@@ -237,10 +259,23 @@ export async function getTransactions(ownerId: number, filters: TransactionFilte
      tr.name as trip_name,
      tr.color as trip_color,
      txn_tags.tags,
-      txn_splits.splits
+      txn_splits.splits,
      order_ctx.route as order_route,
      order_ctx.platform as order_platform
    FROM transactions t
    LEFT JOIN transaction_overrides o ON o.transaction_id = t.id
    LEFT JOIN statements s ON s.id = t.statement_id
    -- Order provenance, for the sub-line under the description. Five rows all
    -- reading "Order - Uber Trip" are indistinguishable; where they went is the
    -- only thing that tells them apart, and it was already stored.
    -- Both directions, because a card-settled order has no transaction of its
    -- own and points at the statement line instead (I5).
    LEFT JOIN LATERAL (
      SELECT em.route, em.platform
        FROM expense_metadata em
       WHERE em.transaction_id = t.id OR em.matched_transaction_id = t.id
       LIMIT 1
    ) order_ctx ON true
    LEFT JOIN participants p ON p.id = COALESCE(t.owner_id, s.owner_id)
    LEFT JOIN transactions src ON src.reconciled_with_id = t.id AND src.statement_id IS NULL
    LEFT JOIN trips tr ON tr.id = o.trip_id
@@ -294,7 +329,7 @@ export async function getTransactionById(id: number) {
      o.category_override, o.merchant_normalized as merchant_override, o.notes, o.my_share_percent,
      COALESCE(o.category_override, t.category) as effective_category,
      COALESCE(o.merchant_normalized, t.merchant_normalized, t.merchant_name) as effective_merchant,
-      COALESCE(s.bank_name, 'Manual') as bank_name,
+      ${bankLabel()} as bank_name,
      COALESCE(t.owner_id, s.owner_id) as owner_id,
      p.name as owner_name
    FROM transactions t
@@ -376,12 +411,20 @@ export async function getMerchantSuggestions(search: string) {
 }
 export async function getBankNames() {
-  const [bankRows, manualCount] = await Promise.all([
+  const [bankRows, statementless] = await Promise.all([
    queryRaw<{ bank_name: string }>(`SELECT DISTINCT bank_name FROM statements ORDER BY bank_name`),
-    queryRaw<{ count: number }>(`SELECT COUNT(*)::int as count FROM transactions WHERE statement_id IS NULL`),
+    queryRaw<{ label: string }>(
      `SELECT DISTINCT ${bankLabel("t", "s")} as label
         FROM transactions t LEFT JOIN statements s ON s.id = t.statement_id
        WHERE t.statement_id IS NULL`
    ),
  ]);
  const banks = bankRows.map((r) => r.bank_name);
-  if (manualCount[0]?.count > 0) banks.push("Manual");
+  // Order matters for the filter chips: real banks first, then the
  // statement-less kinds, in a stable order rather than whatever the DB returns.
  for (const label of ["Manual", "Gift Card"]) {
    if (statementless.some((r) => r.label === label)) banks.push(label);
  }
  return banks;
 }
@@ -390,6 +433,8 @@ export interface ParticipantBalance {
  name: string;
  total_owed: number;
  unsettled_count: number;
  /** Splits counted at a non-AUD figure because no converted amount exists. */
  unconverted_count: number;
 }
 export async function getParticipantBalances(ownerId: number, tagIds?: number[]) {
@@ -403,6 +448,13 @@ export async function getParticipantBalances(ownerId: number, tagIds?: number[])
  // Payments settle the total relationship between two people, not a specific tag.
  // Only subtract payments when viewing the unfiltered total; with a tag filter
  // active, show the raw split amount for that tag context only.
  //
  // That asymmetry is a symptom, not a design: a tag is a view and has no
  // payments, so a tag-filtered balance had nothing honest to subtract. A trip
  // does have payments (`split_payments.trip_id`, migration 0022), which is why
  // the per-trip figure in getTripAnalytics can be netted and this one cannot.
  // The fix for the tag case is to stop showing a balance there, not to invent
  // one — see docs/shared-expenses-design.md.
  const paymentsJoin = tagIds?.length ? "" : `
    LEFT JOIN (
      SELECT
@@ -419,7 +471,11 @@ export async function getParticipantBalances(ownerId: number, tagIds?: number[])
    SELECT p.id, p.name,
      COALESCE(SUM(splits.signed_amount), 0)::numeric(12,2)
        ${paymentsSelect} AS total_owed,
-      COALESCE(SUM(splits.split_count), 0)::int AS unsettled_count
+      COALESCE(SUM(splits.split_count), 0)::int AS unsettled_count,
      -- Splits whose AUD value is unknown. They are still summed above (as
      -- their native figure), so a non-zero count means this balance is
      -- approximate and the UI has to say so.
      COALESCE(SUM(splits.unconverted_count), 0)::int AS unconverted_count
    FROM participants p
    LEFT JOIN (
@@ -428,12 +484,14 @@ export async function getParticipantBalances(ownerId: number, tagIds?: number[])
      -- currency, so splitting on it nets a USD figure against AUD ones.
      SELECT ts.participant_id AS pid,
        (CASE WHEN t.transaction_type IN ('debit', 'fee', 'interest') THEN COALESCE(t.amount_aud, t.amount) ELSE -COALESCE(t.amount_aud, t.amount) END) * ts.share_percent / 100 AS signed_amount,
-        1 AS split_count
+        1 AS split_count,
        (CASE WHEN ${AMOUNT_UNCONVERTED} THEN 1 ELSE 0 END) AS unconverted_count
      FROM transaction_splits ts
      JOIN transactions t ON t.id = ts.transaction_id
      LEFT JOIN statements s ON s.id = t.statement_id
      WHERE COALESCE(t.owner_id, s.owner_id) = $1 AND ts.participant_id != $1
-        AND NOT (t.statement_id IS NULL AND t.reconciled_with_id IS NOT NULL)
+        AND ${EXCLUDE_RECONCILED_SOURCE}
        AND ${ACTIVE_OBLIGATION}
      ${tagFilter}
      UNION ALL
@@ -441,12 +499,14 @@ export async function getParticipantBalances(ownerId: number, tagIds?: number[])
      -- I owe them: my splits on transactions they own
      SELECT COALESCE(t.owner_id, s.owner_id) AS pid,
        -((CASE WHEN t.transaction_type IN ('debit', 'fee', 'interest') THEN COALESCE(t.amount_aud, t.amount) ELSE -COALESCE(t.amount_aud, t.amount) END) * ts.share_percent / 100) AS signed_amount,
-        0 AS split_count
+        0 AS split_count,
        (CASE WHEN ${AMOUNT_UNCONVERTED} THEN 1 ELSE 0 END) AS unconverted_count
      FROM transaction_splits ts
      JOIN transactions t ON t.id = ts.transaction_id
      LEFT JOIN statements s ON s.id = t.statement_id
      WHERE ts.participant_id = $1 AND COALESCE(t.owner_id, s.owner_id) != $1
-        AND NOT (t.statement_id IS NULL AND t.reconciled_with_id IS NOT NULL)
+        AND ${EXCLUDE_RECONCILED_SOURCE}
        AND ${ACTIVE_OBLIGATION}
      ${tagFilter}
    ) splits ON splits.pid = p.id
    ${paymentsJoin}
@@ -533,8 +593,25 @@ export async function batchInsertCSVTransactions(
 * transaction accounts are imported, and NULL means unknown — both stay
 * candidates, which preserves the behaviour of every pre-existing row.
 */
-export const notCash = (alias = "t") =>
+/**
-  `(${alias}.payment_method IS NULL OR ${alias}.payment_method <> 'cash')`;
+ * Payment methods that can still be matched against a card statement line.
 *
 * Cash never appears on one. Neither does a credits-funded delivery order: the
 * gift card already paid it, so there is no card leg coming, ever. Leaving
 * those in the queue meant 81 orders sat in "pending reconciliation" waiting
 * for a match that could not exist (user, 2026-07-27).
 */
 export const needsCardMatch = (alias = "t") =>
  `(${alias}.payment_method IS NULL OR ${alias}.payment_method NOT IN ('cash', 'credits'))`;
 /**
 * Bank label for a transaction. A row with no statement was not imported from
 * one, and the label has to say *why*: "Manual" reads as "hand-entered, still
 * awaiting a card line", which is wrong for a gift-card order — nothing is
 * awaited. `s` must be the statements alias in scope.
 */
 export const bankLabel = (t = "t", s = "s") =>
  `COALESCE(${s}.bank_name, CASE WHEN ${t}.payment_method = 'credits' THEN 'Gift Card' ELSE 'Manual' END)`;
 export interface PotentialMatch {
  id: number;
@@ -578,7 +655,7 @@ export async function getPendingReconciliations(ownerId: number): Promise<Manual
       WHERE ts.transaction_id = t.id
     ) txn_splits ON true
     WHERE t.statement_id IS NULL AND t.owner_id = $1 AND t.reconciled_with_id IS NULL
-       AND ${notCash("t")}
+       AND ${needsCardMatch("t")}
     ORDER BY t.transaction_date DESC, t.row_index ASC`,
    [ownerId]
  );
@@ -620,7 +697,7 @@ export async function getPendingReconciliations(ownerId: number): Promise<Manual
       WHERE m.statement_id IS NULL
         AND m.owner_id = $1
         AND m.reconciled_with_id IS NULL
-         AND ${notCash("m")}
+         AND ${needsCardMatch("m")}
         AND COALESCE(t.owner_id, s.owner_id) = $1
         AND NOT EXISTS (
           SELECT 1 FROM transactions mt WHERE mt.reconciled_with_id = t.id
@@ -687,7 +764,11 @@ export async function getSharedTransactions(ownerId: number, tagIds?: number[],
      o.category_override, o.merchant_normalized as merchant_override, o.notes,
      COALESCE(o.category_override, t.category) as effective_category,
      COALESCE(o.merchant_normalized, t.merchant_normalized, t.merchant_name) as effective_merchant,
-      COALESCE(s.bank_name, 'Manual') as bank_name,
+      ${bankLabel()} as bank_name,
      -- The table renders t.amount, which is not always AUD. Without these the
      -- rows visibly disagreed with the participant balances, which do convert.
      ${NATIVE_CURRENCY} as currency,
      ${AMOUNT_UNCONVERTED} as amount_unconverted,
      COALESCE(t.owner_id, s.owner_id) as owner_id,
      p_owner.name as owner_name,
      COALESCE(src.created_at, t.created_at) as created_at,
@@ -714,10 +795,10 @@ export async function getSharedTransactions(ownerId: number, tagIds?: number[],
          AND EXISTS (SELECT 1 FROM transaction_splits ts_me WHERE ts_me.transaction_id = t.id AND ts_me.participant_id = $1)
        )
      )
-      AND NOT (t.statement_id IS NULL AND t.reconciled_with_id IS NOT NULL)
+      AND ${EXCLUDE_RECONCILED_SOURCE}
    ${tagClause}
    ${participantClause}
-    GROUP BY t.id, o.category_override, o.merchant_normalized, o.notes, s.bank_name, s.owner_id, p_owner.name, src.created_at
+    GROUP BY t.id, o.category_override, o.merchant_normalized, o.notes, s.bank_name, s.currency, s.owner_id, p_owner.name, src.created_at
    ORDER BY t.transaction_date DESC
  `, params);
@@ -753,7 +834,14 @@ export interface TripAnalytics {
  daily_spend: { date: string; amount: number }[];
  top_merchants: { merchant: string; amount: number; count: number }[];
  tag_breakdown: { tag_id: number; name: string; color: string; amount: number; count: number }[];
-  participant_splits: { participant_id: number; name: string; owed: number }[];
+  participant_splits: {
    participant_id: number;
    name: string;
    /** Their share of this trip, net of payments scoped to it. */
    owed: number;
    /** Splits counted at a non-AUD figure because no converted amount exists. */
    unconverted_count: number;
  }[];
 }
 export async function getTrips(ownerId: number): Promise<TripRow[]> {
@@ -857,27 +945,59 @@ export async function getTripAnalytics(tripId: number, ownerId: number): Promise
      ORDER BY 4 DESC
    `, [tripId]),
-    // No settled/unsettled breakdown here. It was computed from
+    // Owed per participant for THIS trip, net of payments made against it.
-    // transaction_splits.settled, which only /api/splits/settle writes and
+    //
-    // nothing in the UI calls — so it is false on all 673 splits and every trip
+    // This was gross splits, with a comment explaining why it could not be
-    // reported 100% unsettled, including trips paid in full. A real per-trip
+    // anything better: `split_payments` carried no trip attribution, so a
-    // figure is not computable either: split_payments carries no trip
+    // payment could not be assigned to a trip and every trip reported 100%
-    // attribution, so a payment cannot be assigned to a trip. Settlement is a
+    // unsettled including trips paid in full. `split_payments.trip_id`
-    // property of the whole relationship until settlement contexts exist
+    // (migration 0022) closes that, so the figure is now real.
-    // (see docs/shared-expenses-design.md).
+    //
-    queryRaw<{ participant_id: number; name: string; owed: number }>(`
+    // Three exclusions, all load-bearing:
-      SELECT
+    //  - ACTIVE_OBLIGATION drops settled splits, so a closed trip reads zero
-        p.id AS participant_id,
+    //    rather than its original gross.
-        p.name,
+    //  - EXCLUDE_RECONCILED_SOURCE drops the manual row a statement line has
-        SUM(ts.share_percent / 100.0 * COALESCE(tx.amount_aud, tx.amount))::float AS owed
+    //    superseded. The trip queries never applied it, so a reconciled trip
    //    expense was counted twice here.
    //  - AMOUNT_UNCONVERTED counts rows whose AUD value is unknown, the same
    //    way getParticipantBalances does. They are still summed (at their
    //    native figure), so a non-zero count means this total is approximate
    //    and the UI has to say so. A trip is where foreign rows actually live,
    //    so netting a EUR figure against AUD ones silently is most likely to
    //    bite exactly here.
    //
    // `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.
    queryRaw<{ participant_id: number; name: string; owed: number; unconverted_count: number }>(`
      WITH owed AS (
        SELECT ts.participant_id AS pid,
          SUM(ts.share_percent / 100.0 * COALESCE(t.amount_aud, t.amount)) AS gross,
          SUM(CASE WHEN ${AMOUNT_UNCONVERTED} THEN 1 ELSE 0 END) AS unconverted
        FROM transaction_overrides o
-      JOIN transactions tx ON tx.id = o.transaction_id
+        JOIN transactions t ON t.id = o.transaction_id
-      JOIN transaction_splits ts ON ts.transaction_id = tx.id
+        ${STATEMENTS_JOIN}
-      JOIN participants p ON p.id = ts.participant_id
+        JOIN transaction_splits ts ON ts.transaction_id = t.id
        WHERE o.trip_id = $1
-        AND tx.transaction_type IN ('debit','fee','interest')
+          AND t.transaction_type IN ('debit','fee','interest')
-        AND COALESCE(o.category_override, tx.category, 'other') NOT IN ('transfers', 'investment')
+          AND COALESCE(o.category_override, t.category, 'other') NOT IN ('transfers', 'investment')
-      GROUP BY p.id
+          AND ${ACTIVE_OBLIGATION}
          AND ${EXCLUDE_RECONCILED_SOURCE}
        GROUP BY ts.participant_id
      ),
      paid AS (
        SELECT sp.from_participant_id AS pid, SUM(sp.amount) AS amt
        FROM split_payments sp
        WHERE sp.trip_id = $1
        GROUP BY sp.from_participant_id
      )
      SELECT p.id AS participant_id, p.name,
        (COALESCE(owed.gross, 0) - COALESCE(paid.amt, 0))::float AS owed,
        COALESCE(owed.unconverted, 0)::int AS unconverted_count
      FROM participants p
      LEFT JOIN owed ON owed.pid = p.id
      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]),
  ]);
Author	SHA1	Message	Date
siddharthd	ae23b03d5d	fix(trips): carry the currency and reconcile rules into the trip figure ci / lint-test (pull_request) Failing after 43s Details ci / lint-test (push) Failing after 43s Details `a4ab543` landed six hours ago and this branch rewrote one of the queries it had just fixed, quietly dropping both of its guarantees. That commit made EXCLUDE_RECONCILED_SOURCE "one fragment both sides import" because an inlined copy is how the reconciled-row exclusion drifted out of the analytics routes and double-counted 48 rows / $4,474.79. The trip owed query here had hand-inlined its own copy — the fragment assumes the alias `t` and this query used `tx`, so the path of least resistance was to re-create exactly the divergence that was being removed. Aliased to `t` so the fragments apply directly. The same commit made balances count rows whose AUD value is unknown rather than netting a foreign figure against AUD ones. The trip figure had no equivalent — on the query where it matters most, because a trip is where foreign rows actually live. A Europe total silently mixing EUR into AUD is the whole failure that fix was written to prevent. The column header still read "Share of this trip" while the number is now net of payments, which is the same class of drift `a4ab543` set out to fix. It reads "Outstanding on this trip", carries the approx/unconverted caveat the Shared cards use, and greys a settled zero.	2026-07-27 23:15:28 +10:00
siddharthd	689fadc8b9	feat(shared): give a payment a tab to settle A payment has only ever recorded from, to, amount and date. That is why the per-trip owed figure did not exist — getTripAnalytics said so where the number should have been: "split_payments carries no trip attribution, so a payment cannot be assigned to a trip. Settlement is a property of the whole relationship." Every trip therefore read 100% unsettled, including trips paid in full. It is also why the Shared page silently drops payments under a tag filter. With one global pool there was nothing honest to subtract, so it showed gross splits under the same label. A tag is a view; a scope is a ledger. The scope is a trip, not a new settlement_contexts table. trips already has owner_id, dates and archived, and transaction_overrides.trip_id already decides membership. A second grouping beside it would be two unsynchronised scopes over the same rows, with no invariant saying which governs. NULL means the ongoing household tab, which never closes. settled answers a different question and the two must not be collapsed: trip_id is which tab, settled is whether the obligation is still live. Critically, a live obligation is NOT settled by flipping the flag — it is settled by recording the payment, and the balance nets to zero on its own. Doing both would subtract the settlement twice. So settled is written only by the historical import, for repayments made on a platform we no longer run, and there is deliberately no "mark settled" action. Both owed figures now exclude settled splits and the trip figure nets its own payments. Spend analytics (myShare/mySplitOf) deliberately still count settled rows: my half of a 2025 grocery shop is my spend whether or not the other half was ever repaid, and filtering them would re-inflate exactly the figures importing settled history exists to correct. Also drops /api/participants/[id]/balance. It had no consumers, no owner scoping, no debit/credit signs and no EXCLUDE_RECONCILED_SOURCE — a fourth balance implementation that disagreed with the others and would have imported three bugs if anything had aligned to it. getTripAnalytics had no test at all. It has five now, including the one that matters: a household payment must not make a trip look paid. Verified by mutation — neutering the settled filter fails three, and dropping the trip filter on payments fails that one.	2026-07-27 23:12:05 +10:00
siddharthd	a4ab543a6c	fix(analytics): make the displayed numbers mean what they say ci / lint-test (push) Failing after 44s Details Six metric-integrity defects from the UI/IA review, plus two found while verifying the review's own claims against the code. The reconciled-row exclusion existed only in queries.ts. Every analytics route counted the superseded manual rows as spend — 48 rows, $4,474.79 of double count, invisible precisely because the transaction list looked right. It is now one fragment both sides import. The spend-pace chart computed its own totals in the browser: gross amounts, debits only, no personal share, no refunds, fees, interest or itemised loan repayments. On live data it ended July at $4,747.31 under a headline reading $3,597.10 — and its own baseline line was drawn from the split-adjusted monthly totals, so the two series in one chart disagreed with each other. Both now come from /api/analytics/daily, built from the same fragments as the headline. Fees aggregated every statement ever imported with no date filter, under a heading with no period, so a lifetime figure read as a current one and grew forever. Now bounded, labelled, and selectable. Comparisons no longer measure a month in progress against complete ones: the in-progress month is out of every baseline, and a selected current month is compared through the same day. Two the review did not catch: - Every analytics window was a day early. toISOString() on a local-midnight Date converts backwards through UTC. Surfaced only once fees started reporting the range it had used. - /monthly rounded per category, /daily per category-day, so the pace chart ended a few cents off the headline above it. Shared currency needed amending rather than applying. Reading s.currency would have labelled every order row AUD, since an order receipt has no statement and carries its own currency — the opposite convention from a foreign charge on an AUD statement, where amount IS AUD. NATIVE_CURRENCY's COALESCE order keeps the two apart. Balances also now count rows whose AUD value is genuinely unknown instead of netting a foreign figure against AUD ones. Latent today: no foreign transaction is currently split. Tag-filtered balance cards no longer claim "owes you". With a filter on, payments are deliberately not subtracted, so the figure is a split total and settling against it would record a payment for a debt that never was. Split-coverage warnings deliberately omitted (user decision).	2026-07-27 17:10:27 +10:00
siddharthd	5ee5ee24cf	feat(orders): expand a row to see the receipt it came from ci / lint-test (push) Failing after 44s Details Enrichment is the point of the ingestion pipeline (DECISIONS ING-8) — a bank statement gives a date, an amount and a mangled descriptor, and everything that makes a transaction understandable arrives by email. It was all reachable only by opening the edit modal, which is a strange place to look for "what was in this order". Rows with a receipt behind them get a disclosure arrow in the description cell; clicking expands an inline panel with the line items, the pick-up and drop-off stops, the card tail and the provider's reference. Several rows can be open at once — the point is comparing orders without losing your place. The arrow appears only where `order_platform` is set. Putting one on every transaction would promise detail that mostly does not exist. OrderDetails moves out of edit-transaction-modal.tsx into its own component so both surfaces render the same thing; `bare` drops the modal's top border when it sits in a table row.	2026-07-27 11:52:33 +10:00
siddharthd	3bb67f370d	feat(orders): show where an Uber trip went, in the list ci / lint-test (push) Failing after 41s Details Five rows all reading "Order - Uber Trip" are indistinguishable — the list gives you a date and an amount and nothing to tell one ride from another (user, 2026-07-27). Where the trip went is exactly what separates them, and it was already stored on expense_metadata.route since this morning; nothing in the list read it. getTransactions now joins the receipt (both directions — transaction_id OR matched_transaction_id, since a card-settled order points at the statement line instead) and the description cell renders "Terminal 2, Melbourne Airport (MEL) → 19 Lady Penrhyn Dr" in the same italic sub-line notes use. Two deliberate limits: - A note the user wrote always wins. This only fills an empty sub-line; it never occupies the notes field, which is theirs. - Deliveries are excluded. Their merchant already identifies them, so the restaurant's street address would be clutter on every food order. Gated on platform = 'uber'. The summary keeps the first two comma-segments of each address — a truncation, not a guess about geography. Uber puts the venue or street first, which is the identifying part; the full stops with their times stay in the title attribute.	2026-07-27 11:43:55 +10:00
siddharthd	6161ddc9de	fix(orders): don't restate a platform the merchant already names ci / lint-test (push) Failing after 41s Details Trip rows read "Order - Uber Trip (Uber)". The suffix exists so you can tell where to go and look; when the merchant is literally "Uber Trip" it says nothing. What identifies a trip is its two addresses, and those are in the Order details panel. Existing rows updated in prod.	2026-07-27 11:33:37 +10:00
siddharthd	c656f5d26b	feat(orders): read Uber trips, and reject the charge summary that duplicates them ci / lint-test (push) Failing after 45s Details Local rides are paid with credits (only overseas ones go on a card), so trips belong to this slice and were simply never fetched — the Graph query searched "order with Uber", the Eats subject. Captured 15 real messages from the mailbox via a dry-run before touching anything, which found two defects that no amount of reasoning about the template would have: Uber sends two mails per trip. A "charge summary" when the ride ends, then the real receipt when payment settles — same subject, same total. The summary carries no tripReference, so order_reference fell back to `msg:<message-id>` and I7 could not dedupe it against the receipt that follows. Every trip would have been recorded twice. It says so itself ("This is not a payment receipt ... You will receive a trip receipt when the payment is processed"), so it is now a NotAReceiptError — 200 and silent, like every other expected non-receipt. Trip receipts label neither end of the journey. Delivery receipts write "1:20 pm - Pick-up"; trips print the time alone. The split regex put the time into `label` and left `time` null. Time is now read properly, and a two-stop trip is labelled Pick-up/Drop-off positionally — only where the receipt was silent, so a template that does label its stops keeps its own wording. Verified against all 15 captured messages: 7 trips recorded, 5 charge summaries and 3 promotions skipped, 0 failures, no duplicate references. Two of the seven are AUD credits-funded ($84.78 + $47.97) and would become transactions; the five NZD ones are card-settled and correctly create provenance only (I5). Fixtures ut-00 (local credits trip), ut-01 (overseas card trip) and ut-summary (the charge summary) are captured mail, not written by hand.	2026-07-27 11:21:13 +10:00
siddharthd	4febf38292	test(orders): clean statement fixtures before ingest, not after ci / lint-test (push) Failing after 40s Details These tests insert a Westpac statement and a `DD *DOORDASH ...` charge, and only removed them at the end of the test — so they survived into the next run, where `reconcileCardLeg` could match one at ingest time and resolve an order that was meant to park `awaiting_card_statement`. That is a real ordering bug in the fixtures regardless. It is my best explanation for the intermittent failure in "parks an unresolvable split", but I could not reproduce it: seeding the exact leftover row and running the old code passed anyway. So this is hygiene with a plausible mechanism, not a confirmed fix — if that test fails again, this was not the cause.	2026-07-27 11:01:23 +10:00
siddharthd	b6cd62f7b5	feat(orders): show the receipt in the transaction detail panel ci / lint-test (push) Failing after 47s Details `expense_metadata` has held the itemised receipt since ingestion started and nothing in the UI ever read it. A transaction that came from a DoorDash or Uber Eats receipt showed a merchant and an amount, with the item list and the delivery addresses sitting unread in the row behind it (user, 2026-07-27). Adds GET /api/transactions/[id]/order and an "Order details" section in the edit modal: line items with their options, pick-up/delivery stops with times and addresses, the card tail when one was involved, and the provider's own order reference. Two details that matter: - The lookup resolves from both sides — `transaction_id` OR `matched_transaction_id`. A card-settled order creates no transaction of its own (I5); the receipt points at the statement line instead. Matching only on transaction_id would have left the panel blank on exactly the card-paid orders, which are the ones whose detail is hardest to find elsewhere. - An empty item list says so in words rather than rendering nothing. Uber itemises groceries but not restaurant orders, and orders ingested before the Uber item parser existed have none either — a blank section reads as a bug when it is usually the receipt. Read-only. This is what a provider sent; editing it would make provenance mean nothing.	2026-07-27 10:55:57 +10:00
siddharthd	df4b875b82	feat(orders): make an ingested order legible in the transactions view ci / lint-test (push) Failing after 43s Details Four things the view could not tell you, all from reading the rows (user, 2026-07-27). Which platform. The parser has always known — it has to, to read the template — and then discarded it. "Order - Burger Corner" gives no way to know whether to open DoorDash or Uber Eats for the detail, and restaurants exist on both. Now stored on expense_metadata and named in the description: "Order - Burger Corner (Uber Eats)". Migration 0021 recovers it for the 101 backfilled rows from the order_reference shape — DoorDash receipts carry no id of their own so ingestion synthesises `msg:<message-id>`, Uber carries a real trip UUID, which makes the discriminator exact. Bank said "Manual". That label is derived, not stored, and "Manual" reads as "hand-entered, still awaiting a card line to match". A gift-card order has no card line coming, ever. It now reads "Gift Card", and — the part that actually mattered — credits joins cash in needsCardMatch(), so these stop sitting in the pending-reconciliation queue. All 81 were queued against a match that could not exist. Uber line items were never parsed. 67 of 101 orders had none. Uber itemises groceries but not restaurant orders, so some of that is genuine; the rest was simply unread. Its markup is better than DoorDash's — every cell carries a data-testid with the item's uuid, so qty/title/amount bind by id rather than by column position. Sold-out items (0.00) are kept: they are why a total is lower than what was ordered. Uber prints pick-up and delivery addresses on every receipt and they were thrown away. Captured as `route` [{label, time, address}], de-duplicated because the template renders the whole block twice for narrow screens. Wording is kept as printed ("Pick-up" on some receipts, "Pickup" on others) rather than normalised, so a template change stays visible. This is the same block a trip receipt uses for start and destination — rides are not ingested today, but the reader will not need changing when they are. Also stores source_email_subject/from, which order ingestion had left null on columns that already existed. Verified against the captured corpus: route on all 6 Uber fixtures, 5/5 items on the GLOMARK grocery receipt including the sold-out one. Production data updated by smarthome:docker/scripts/order-presentation-2026-07-27.sql (81 descriptions, `backfill` tag, re-run clean). `route` and Uber line items are parsed from here on only — recovering them for already-ingested orders means re-reading the mail, which I7 idempotency refuses by design.	2026-07-27 10:51:30 +10:00
siddharthd	ae0c34fce7	fix(orders): two defects the backfill exposed that tests could not ci / lint-test (push) Failing after 1m26s Details Both were found by looking at the data after the live backfill, not by the suite — 105 tests were green while 85 rows were invisible and 4 were double counted. owner_id was NULL on every ingested order. Analytics scope on COALESCE(t.owner_id, s.owner_id), and an ingested order carries no statement, so the coalesce resolved to NULL and matched no owner. The rows existed in `transactions` and appeared in no view in the app. Ingestion now sets DEFAULT_OWNER_ID, and a regression test asserts the row survives the same COALESCE scoping the UI uses. [Family] orders are card-settled, not credits-funded. Their receipts name the payer ("Payments Siddharth LKR 3,783.20") and no instrument, which an earlier version read as credits. The card statement carries all four of them (CBA ...3893, exact foreign_currency_amount matches), so creating a transaction duplicated spend already recorded — the double-count I5 exists to prevent. They now record provenance only; the statement line is the transaction and is what carries the `family` tag that keeps them out of budgets. Production data corrected separately by smarthome:docker/scripts/fix-order-backfill-2026-07-27.sql. Also: reconciliation tests no longer assert global row counts. reconcilePendingOrders() scans every pending row, so leftovers from other files moved the totals — the source of an intermittent failure that only appeared on the first run after a source edit.	2026-07-27 10:41:10 +10:00
siddharthd	5db42f086f	fix(orders): match the card leg by masking, not by card brand ci / lint-test (push) Failing after 45s Details Backfill dry-run over 130 real messages surfaced one 422: 'payments sum to 1.17 but receipt states 16.50'. The receipt is a mixed Uber payment — Uber Cash $1.17 + Westpac ••••8032 $15.33 — and the card regex only matched Visa\|MasterCard\|American Express\|Amex, so an issuer-named leg was dropped entirely. validateOrderTotals correctly refused it rather than recording $1.17 as the cost of a $16.50 order. Anchors on the ••••NNNN masking instead, which also covers the form already seen in the corpus ('Mastercard ••••3893 (CBA Ultimate) CHF 51.23'). Fixture and regression test added. Also repoints .env.test at the current postgres-personal container IP and documents why: the container publishes no host port, so the address changes on every recreate and the whole integration suite fails with connection errors until it is refreshed.	2026-07-27 01:39:42 +10:00