Migrations¶

BillTracker applies additive SQLite migrations when the database opens. Docker also runs the migration script before starting the server.

node scripts/migrate-db.js

Tracking¶

Applied versions are recorded in:

schema_migrations

The migration layer can reconcile a legacy database that predates version tracking by inspecting existing tables and columns before recording versions.

Recent Schema Families¶

Versions	Area
`v0.40-v0.47`	User ownership, demo-data markers, history ranges, indexes, audit log, cycles, backup retention
`v0.48-v0.58`	Debt Snowball, release state, login history, user settings, soft delete, autopay, templates
`v0.59-v0.64`	Payment-source metadata, transaction foundation, match rejection, subscriptions, account monitoring
`v0.65-v0.69`	Subscription catalog, declined hints, merchant rules, advisory filters, catalog v2
`v0.70-v0.77`	Snooze persistence, persistence hardening, OIDC client-secret encryption, SMTP password encryption, billing-schedule migration
`v0.78`	HKDF key derivation re-encryption (`v2:` prefix)
`v0.79-v0.83`	OIDC encryption hardening, push notification columns, `auto_match` → `provider_sync` rename, merchant-rule composite index, `auto_attribute_late`
`v0.84-v0.85`	Login history encryption, location columns, success flag, session fingerprint
`v0.86`	TOTP / Authenticator App 2FA
`v0.87-v0.89`	Spending categories, budgets, merchant rules, spending-enabled flag, default category seeding
`v0.90-v0.91`	Matching pipeline corrections, `match_suggestion_rejections.created_at`, composite indexes
`v0.92`	WebAuthn / FIDO2 credentials and challenges
`v0.93`	SimpleFIN transaction dedupe key rewrite; debt interest `interest_accrued_month` and `payments.interest_delta`
`v0.94`	Session token hashing, `geolocation_enabled` setting, deletes existing plaintext sessions
`v0.95`	Subscription catalog subcategory, starting monthly/annual USD, price notes, `subscription_catalog_descriptors` table
`v0.96`	`bills.catalog_id` foreign key, `user_catalog_descriptors` table, backfill of catalog ids for existing subscription bills
`v0.97`	`subscription_recommendation_feedback` table
`v0.99`	`bills.autopay_verified_at`, `bills.inactive_reason`, `bills.inactivated_at`, `payments.autopay_failure`, `calendar_tokens`
`v0.70`	Snooze persistence (`monthly_bill_state.snoozed_until`)
`v0.78`	HKDF key derivation re-encryption (`v2:` prefix)
`v0.82`	Renamed `auto_match` payment source to `provider_sync`
`v0.84-v0.85`	Login history encryption, location columns, success flag, session fingerprint
`v0.86`	TOTP / Authenticator App 2FA
`v0.87-v0.89`	Spending categories, budgets, merchant rules, spending-enabled flag, default category seeding
`v0.90`	Matching pipeline corrections, `match_suggestion_rejections.created_at`
`v0.91`	Composite indexes on user-scoped tables
`v0.92`	WebAuthn / FIDO2 credentials and challenges
`v0.93`	SimpleFIN transaction dedupe key rewrite; debt interest `interest_accrued_month` and `payments.interest_delta`
`v0.94`	Session token hashing, `geolocation_enabled` setting, deletes existing plaintext sessions
`v0.95`	Subscription catalog subcategory, starting monthly/annual USD, price notes, `subscription_catalog_descriptors` table
`v0.96`	`bills.catalog_id` foreign key, `user_catalog_descriptors` table
`v0.97`	`subscription_recommendation_feedback` table
`v0.99`	`bills.autopay_verified_at`, `bills.inactive_reason`, `bills.inactivated_at`, `payments.autopay_failure`

Rollback¶

The Admin API exposes rollback for migrations with an explicit rollback definition. Rollback is transactional, audited, and intentionally unavailable for migrations without a defined reverse operation.

Warning

Take a database backup before rollback. A supported rollback is a maintenance tool, not a replacement for recovery planning.