Database Reference¶
BillTracker uses SQLite with WAL journaling and foreign-key enforcement.
Core Tables¶
| Table | Purpose |
|---|---|
users |
Local and OIDC identities, roles, profile state, Snowball settings, TOTP and WebAuthn credentials |
sessions |
Server-side login sessions (token stored as SHA-256(token) since v0.94) |
user_login_history |
Recent device and login metadata, encrypted IP and user agent, optional geolocation |
categories |
User-owned bill categories, soft-deletable, with a spending_enabled flag |
bills |
Recurring bill definitions, cycle metadata, debt fields, subscription state, autopay verification, cancellation tracking, and catalog linkage |
payments |
Bill payments, source metadata, debt balance changes, autopay-failure flag, and per-payment interest delta |
monthly_bill_state |
Month-specific amount, notes, skip overrides, and snooze dates |
monthly_income |
Monthly income planning |
monthly_starting_amounts |
Monthly starting-cash planning |
bill_history_ranges |
Historical visibility ranges for bills |
Imports And Matching¶
| Table | Purpose |
|---|---|
data_sources |
Manual and SimpleFIN source configuration, encrypted access URL |
financial_accounts |
Imported accounts and monitoring state |
transactions |
Imported bank and CSV transactions, with stable provider_transaction_id (v0.93+) and optional spending category |
match_suggestion_rejections |
Dismissed transaction-to-bill suggestions (filtered by rejected_at, pruned after 90 days) |
bill_merchant_rules |
Persistent merchant-to-bill matching rules, with auto_fix_late_attribution toggle |
spending_category_rules |
Persistent merchant-to-spending-category rules |
spending_budgets |
Per-category monthly budgets |
import_sessions |
Preview state for staged imports |
import_history |
Per-user import audit history |
subscription_recommendation_feedback |
Per-user accept/decline/link history for recommendations (v0.97) |
Subscription Catalog¶
| Table | Purpose |
|---|---|
subscription_catalog |
Known recurring services, with subcategory and starting monthly/annual pricing (v0.95) |
subscription_catalog_descriptors |
Bank statement descriptors and slang per service (1,501 rows in the seeded dataset) |
user_catalog_descriptors |
Per-user custom payee strings that improve auto-matching for that user (v0.96) |
declined_subscription_hints |
Dismissed subscription recommendations |
Authentication¶
| Table | Purpose |
|---|---|
totp_challenges |
Short-lived (5-minute) TOTP login challenges |
webauthn_credentials |
Per-user registered FIDO2 security keys, multiple keys supported (v0.92) |
webauthn_challenges |
Short-lived registration, authentication, and login challenges |
calendar_tokens |
Idempotent bearer tokens for the private calendar subscription feed |
Product Support¶
| Table | Purpose |
|---|---|
settings |
Server-wide settings, including the auto-generated encryption key and persisted worker state |
user_settings |
Per-user display and workflow preferences (search panel state, geolocation, etc.) |
notifications |
Sent reminder deduplication |
audit_log |
Security-sensitive event audit records |
bill_templates |
Saved bill templates |
advisory_non_bill_filters |
Non-bill transaction patterns |
advisory_bill_like_overrides |
Terms that override non-bill suppression |
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 |
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 |
Ownership¶
Personal financial records are scoped to a user. User-facing route handlers derive ownership from the authenticated session rather than trusting a client-supplied user ID.
Amount Storage¶
Bill and payment amounts use SQLite numeric columns. Imported
financial-account and transaction amounts are stored as integer minor
units where provided by the sync pipeline. Transaction currency is
read from the source account, not hardcoded to USD (corrected in
v0.37.0).