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)

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