Data Flows¶

This page traces the major BillTracker workflows from browser request to persistent state. Use it when debugging behavior that crosses route, service, worker, and database boundaries.

HTTP Request Lifecycle¶

Most requests pass through the following layers:

Browser
  |
  +-- securityHeaders
  +-- JSON parser
  +-- cookie parser
  +-- csrfTokenProvider
  |
  +-- mounted route namespace
        |
        +-- csrfMiddleware for mutating API requests
        +-- requireAuth where protected
        +-- requireUser or requireAdmin where role-scoped
        +-- namespace-specific rate limiter where configured
        +-- route handler
              |
              +-- service layer
              +-- SQLite statements and transactions

Express serves the React build from dist/ after API and /legacy routes. Unknown frontend paths return dist/index.html so React Router can resolve the SPA page.

POST /api/auth/login
  |
  +-- login rate limiter unless no users exist
  +-- username lookup
  +-- reject inactive or OIDC-only user
  +-- bcrypt password comparison
  +-- remove expired sessions for user
  +-- generate UUID session id
  +-- INSERT sessions with 7-day expiry
  +-- record login metadata
  +-- Set-Cookie: bt_session

The browser uses the server-side session for subsequent protected requests.

GET /api/auth/oidc/login
  |
  +-- resolve DB-first OIDC configuration
  +-- discover provider
  +-- create PKCE verifier/challenge
  +-- create state and nonce
  +-- redirect browser to provider

GET /api/auth/oidc/callback
  |
  +-- validate callback state
  +-- exchange authorization code
  +-- validate token, issuer, audience, expiry, and nonce
  +-- map or provision local user
  +-- apply configured admin group mapping
  +-- create local server-side session
  +-- redirect browser into SPA

CSRF Flow¶

Since v0.35, the CSRF cookie is httpOnly by default. The SPA fetches the token once from GET /api/auth/csrf-token and stores it in a module-level memory cache; mutations send it in the x-csrf-token header:

GET or page request
  |
  +-- csrfTokenProvider
  +-- set bt_csrf_token (httpOnly) when absent

POST / PUT / PATCH / DELETE
  |
  +-- SPA sends x-csrf-token (from in-memory cache, not from document.cookie)
  +-- csrfMiddleware compares header and cookie
  +-- reject with 403 and audit csrf.failure on mismatch

Login is exempt because no authenticated session exists yet. CSRF_HTTP_ONLY=false is still supported for custom clients that need document.cookie access.

Monthly Tracker Flow¶

GET /api/tracker?year=YYYY&month=M
  |
  +-- authenticated user ownership
  +-- load active user-owned bills
  +-- resolve cycle occurrence and due date
  +-- load active payments in cycle range
  +-- merge monthly_bill_state override
  +-- calculate row status
  +-- aggregate bucket and month summaries
  +-- return tracker payload

Key inputs:

bills
payments
monthly_bill_state
User setting grace_period_days
Cycle resolver in services/statusService.js

Payment Flow¶

A manual payment:

POST /api/payments
  |
  +-- validate bill ownership
  +-- validate positive finite amount
  +-- validate real YYYY-MM-DD date
  +-- INSERT payments
  +-- optionally update debt balance delta
  +-- return payment

A transaction match creates or updates a canonical payment:

POST /api/transactions/:id/match
  |
  +-- load user-owned transaction and bill
  +-- calculate positive payment amount from transaction
  +-- create or update linked payment
  +-- adjust debt balance if needed
  +-- mark transaction matched

Unmatch reverses the linked payment and restores debt balance effects.

SimpleFIN Flow¶

Connect¶

POST /api/data-sources/simplefin/connect
  |
  +-- verify bank sync enabled
  +-- claim one-time setup token
  +-- encrypt returned access URL
  +-- INSERT data_sources
  +-- seed sync for 44 days (one day under SimpleFIN Bridge's 45-day hard limit)
  +-- upsert financial_accounts
  +-- insert new transactions
  +-- apply merchant rules, auto-match, and spending-category rules
  +-- return decorated source status

Sync¶

SimpleFIN access URL
  |
  +-- GET /accounts?start-date=<epoch>&version=2
  |     (with AbortSignal.timeout(30000); up to 3 retries on transient 5xx
  |      or network errors with 1s / 2s backoff)
  +-- sanitize remote errors and raw data
  +-- normalize accounts
  +-- normalize transactions
  +-- upsert account metadata
  +-- INSERT OR SKIP transactions using provider dedupe
  |     (simplefin:{account_id}:{tx_id} since v0.93 — stable across reconnect)
  +-- update source last_sync_at, status, last_error
  +-- apply stored merchant rules
  +-- apply auto-match scoring
  +-- apply spending-category rules

Connection-level warning lists can be recorded alongside an otherwise successful sync so users see partial failures without losing imported data.

The syncLimiter (10 requests per 15 minutes per authenticated user, since v0.37) is applied to POST /:id/sync, POST /sync-all, and POST /:id/backfill so a single client cannot hammer SimpleFIN.

Background Worker¶

setTimeout(configured interval)
  |
  +-- load SimpleFIN sources oldest-sync-first
  +-- skip sources synced within 1 hour
  +-- sync source
  +-- run suggestion auto-match
  +-- wait 3 seconds before next source
  +-- schedule next cycle

CSV Import Flow¶

POST /api/import/csv/preview
  |
  +-- require DATA_IMPORT_ENABLED
  +-- accept <= 10 MB raw CSV
  +-- parse rows and normalize headers
  +-- suggest mapping
  +-- save expiring import session
  +-- return preview

POST /api/import/csv/commit
  |
  +-- load user-owned preview session
  +-- validate mapping
  +-- normalize amount, dates, account, description
  +-- hash stable transaction identity
  +-- insert unseen transactions
  +-- skip duplicates
  +-- write import_history

XLSX Import Flow¶

POST /api/import/spreadsheet/preview
  |
  +-- accept <= 10 MB workbook
  +-- validate XLSX signature
  +-- parse selected or all sheets
  +-- detect one or more header groups
  +-- infer month, year, due bucket, amount, notes, category
  +-- score possible bill matches
  +-- save preview session

POST /api/import/spreadsheet/apply
  |
  +-- validate <= 5000 explicit row decisions
  +-- run transactional apply
  +-- create or update bills, monthly state, and payments
  +-- record import_history

Preview does not write bill or payment data. Apply writes only confirmed decisions.

User Export And Import¶

Export¶

GET /api/export/user-db
  |
  +-- select current user's live records only
  +-- create temporary SQLite export
  +-- stream download
  +-- delete temp file in callback

Cleanup removes orphaned temp files after crashes.

Import¶

POST /api/import/user-db/preview
  |
  +-- accept <= 50 MB SQLite
  +-- validate SQLite signature and tables
  +-- sanitize known columns
  +-- build user-owned preview

POST /api/import/user-db/apply
  |
  +-- load preview session
  +-- map categories and bills to current user
  +-- import payments, monthly state, and starting amounts

Admin Backup Flow¶

Backups are full server database snapshots rather than user-owned exports:

POST /api/admin/backups
  |
  +-- WAL checkpoint
  +-- better-sqlite3 online backup to .partial
  +-- SQLite integrity_check
  +-- atomic rename
  +-- chmod 0600
  +-- checksum
  +-- retention

Restore always creates a pre-restore backup before replacing the live database.

Autopay Verification Flow¶

POST /api/bills/:id/verify-autopay
  |
  +-- requireAuth + bill ownership check
  +-- UPDATE bills SET autopay_verified_at = <now> WHERE id = ? AND user_id = ?
  +-- return { ok: true, autopay_verified_at }

GET /api/tracker  (subsequent)
  |
  +-- for each bill, statusService computes the AP confidence score from
  |     the last 12 months of payments
  +-- statusService exposes autopay_verified_at so the row badge can show
        a verification nudge when the timestamp is older than 90 days

Bulk Unmatch Flow¶

POST /api/transactions/unmatch-bulk
  |
  +-- requireAuth + bill ownership check
  +-- begin db.transaction
  +-- for each transaction_id:
  |     - if payment_source = 'provider_sync':
  |         soft-delete the linked payment and restore the bill balance
  |         (including any interest from payments.interest_delta)
  |     - if payment_source = 'transaction_match':
  |         call the standard unmatch service
  |     - mark the transaction as unmatched
  +-- optionally delete the merchant rule
  +-- commit
  +-- return { ok: true, unmatched_count, balance_restored }

Calendar Feed Flow¶

POST /api/calendar/feed
  |
  +-- requireAuth
  +-- generate a cryptographically random token
  +-- INSERT calendar_tokens (user_id, token, label, active, created_at)
  +-- return the raw URL once; never show the token again

GET /api/calendar/feed.ics?token=<token>
  |
  +-- no session check (public bearer-style URL)
  +-- lookup calendar_tokens by hashed token
  +-- mark last_used_at
  +-- emit CRLF / no-BOM / no-VTIMEZONE ICS body
  +-- all-day DATE events with stable per-bill-cycle UIDs

Late Attribution Flow¶

Bank sync (applyMerchantRules) detects a payment whose posted_date falls
within 5 days into a new month while the bill's due_day was in the prior
month
  |
  +-- tag the payment as a late-attribution candidate
  +-- TrackerPage (or BillModal Sync) dispatches a queue of candidates

User accepts a candidate
  |
  +-- PATCH /api/payments/:id/attribute-to-month
  |     (allowed only for provider_sync payments)
  +-- move paid_date to the last day of the prior month
  +-- amount and bank link are not changed

Daily Maintenance Flow¶

The daily worker runs at startup and at 6:00 AM server-local time:

dailyWorker
  |
  +-- update autopay assumed-paid state
  +-- prune expired sessions
  +-- evaluate and send reminders
  +-- run cleanup tasks
        |
        +-- expired import previews
        +-- stale temp exports
        +-- stale partial backup files
        +-- optional old import history
        +-- soft-deleted bills and categories older than 30 days