BillTracker/docs/cents-migration-plan.md

# Cents Migration Plan (v1.03) — dollars (REAL) → integer cents

**Status:** Stage 1 shipped (2026-06-10). Stage 2 (schema flip) NOT yet applied.
**Stage 1 (done):** `utils/money.js` cents-core module; every float summation/rounding
site in services/routes now uses `roundMoney` / `sumMoney` / `mulMoney` (cent-exact).
**Stage 2 (this document):** flip storage and compute to integer cents.

## Why staged

The schema flip and the code conversion must land **atomically** — migrations run
automatically at boot, so flipping storage with any of the ~288 query sites still
assuming dollars corrupts data 100× on write or displays garbage on read. Stage 2
should be done on a branch, file by file, with the checklist below.

## Target architecture

- **DB:** integer cents in all money columns (matches SimpleFIN `transactions` /
  `financial_accounts`, which are already cents).
- **Services:** compute in cents end-to-end.
- **API:** keeps returning dollars (convert at route serialization, parse at input).
  This keeps the web client (142 money sites) and the mobile app untouched.

## Column inventory (12 columns, 8 tables)

| Table | Columns (currently dollars REAL) |
|---|---|
| bills | expected_amount, current_balance, minimum_payment |
| payments | amount, balance_delta |
| monthly_bill_state | actual_amount |
| monthly_starting_amounts | first_amount, fifteenth_amount, other_amount |
| monthly_income | amount |
| spending_budgets | amount |
| snowball_plans | extra_payment |
| users | snowball_extra_payment |

NOT migrated: `bills.interest_rate` (a percentage), `transactions.amount`,
`financial_accounts.balance/available_balance` (already cents).

## v1.03 migration (add to the `migrations` array in db/database.js)

```js
{
  version: 'v1.03',
  description: 'money columns: dollars (REAL) → integer cents',
  run() {
    const conv = [
      ['bills', ['expected_amount', 'current_balance', 'minimum_payment']],
      ['payments', ['amount', 'balance_delta']],
      ['monthly_bill_state', ['actual_amount']],
      ['monthly_starting_amounts', ['first_amount', 'fifteenth_amount', 'other_amount']],
      ['monthly_income', ['amount']],
      ['spending_budgets', ['amount']],
      ['snowball_plans', ['extra_payment']],
      ['users', ['snowball_extra_payment']],
    ];
    for (const [table, cols] of conv) {
      for (const col of cols) {
        db.exec(`UPDATE ${table} SET ${col} = CAST(ROUND(${col} * 100) AS INTEGER) WHERE ${col} IS NOT NULL`);
      }
    }
    console.log('[v1.03] money columns converted to integer cents');
  }
}
```

Notes:
- SQLite affinity: existing columns stay declared REAL but hold integer values —
  exact in both SQLite and JS (integers < 2^53). No table rebuild needed.
- `db/schema.sql` must change the same columns to `INTEGER` + `-- cents` comments
  so fresh installs are born in cents (v1.03 then no-ops on zero rows).
- Registration: only the `runMigrations()` array matters. (The
  reconcileLegacyMigrations sync assertion never fires — it runs before
  `_runMigrationVersions` is populated; initSchema calls handleLegacyDatabase
  before runMigrations. Worth fixing while in there.)
- `ROLLBACK_SQL_MAP` entry: same loop with `ROUND(${col} / 100.0, 2)`.

## Code conversion rules (stage 2)

1. **Reads:** anything selecting the 12 columns now yields cents. Services treat
   them as cents; `roundMoney(x)` calls on these values become `Math.round`, and
   most add/subtract/compare logic is unit-consistent and needs no change.
2. **Writes:** route input parsing converts dollars → `toCents()` once, at
   validation (`validateBillData`, `validatePaymentInput`, monthly-state,
   starting-amounts, budgets, snowball routes).
3. **API output:** every `res.json` carrying money fields converts with
   `fromCents()` — add per-entity serializers (`serializeBill`, `serializePayment`,
   ...) in services and use them in routes instead of spreading raw rows.
4. **Unification wins:** existing `cents → dollars` bridges disappear, e.g.
   `Math.round(Math.abs(tx.amount)) / 100` in routes/matches.js and
   `dollarsFromTransactionAmount()` in subscriptionService — payments and
   transactions will finally share units.
5. **Interest math:** `mulMoney` equivalents work directly in cents:
   `Math.round(balanceCents * rate / 100 / 12)`.

## Query-site inventory (grep `(FROM|INTO|UPDATE) <table>`, server-side)

bills: 127 · payments: 108 · monthly_bill_state: 18 · snowball_plans: 20 ·
monthly_starting_amounts: 7 · spending_budgets: 6 · monthly_income: 2

Suggested file order (leaf → hub): paymentValidation → billsService →
paymentAccountingService → statusService → trackerService → routes/payments →
routes/bills → snowball/apr → analytics/spending/summary → subscription →
import/export → notification/calendarFeed.

## Hazards (each needs explicit handling)

- **services/userDbImportService.js** copies raw numeric values from uploaded DBs
  (lines ~152-219). After v1.03 it MUST check the source DB's `schema_migrations`
  for v1.03: present → copy as-is; absent → `toCents()` each money field.
- **Spreadsheet/CSV imports** (`spreadsheetImportService`, `csvTransactionImportService`)
  parse user dollars — convert at their INSERT statements.
- **Backups/restore:** safe automatically — restore swaps the file, `closeDb()` →
  `getDb()` re-runs migrations, so pre-v1.03 backups get converted at next init.
- **Tests:** ~67 money assertions/fixtures insert dollars via raw SQL — multiply
  fixtures and expectations by 100 where they touch the 12 columns.
- **export.js CSV:** `toFixed(2)` on dollars becomes `formatCentsUSD`/`fromCents`.

## Verification before merging stage 2

1. `npm run check:server && npm test` (after fixture updates).
2. Invariant script: snapshot `SUM(col)` per money column pre-migration; assert
   post-migration `SUM(col) / 100` matches to the cent.
3. Manual pass: tracker totals, snowball projection, calendar summary, CSV export
   against a copy of the production DB (`backups/` has real snapshots to test with).