Database Schema

SQLite database with per-user isolation.

Storage Location

data/users/{user_id}/transactions.db

Each user gets their own SQLite database file.

Tables

`transactions`

Raw imported transactions (before classification).

`transactions_analyzed`

Classified transactions (after pipeline processing).

Both tables share the same schema:

Column	Type	Description
`id`	INTEGER	Auto-increment primary key
`source_file`	TEXT	Origin filename
`source_row`	INTEGER	Row number in source
`booking_date`	TEXT	Transaction date (ISO format)
`value_date`	TEXT	Value date
`amount`	REAL	Transaction amount
`balance`	REAL	Account balance after txn
`currency`	TEXT	Currency code (EUR, USD)
`payee`	TEXT	Recipient name
`payer`	TEXT	Sender name
`merchant`	TEXT	Normalized merchant name
`name_clean`	TEXT	Cleaned display name
`description`	TEXT	Transaction description
`group`	TEXT	Grouping field
`cl_category`	TEXT	Classification: category
`cl_subcategory`	TEXT	Classification: subcategory
`cl_tags`	TEXT	Comma-separated tags
`cl_type`	TEXT	`income` or `expense`
`cl_direction`	TEXT	Flow direction
`cl_confidence`	REAL	Classification confidence (0-1)
`cl_source`	TEXT	What classified it (rule, ML, user)
`cl_finished`	INTEGER	1 = fully classified
`cl_recurring`	INTEGER	1 = recurring transaction
`cl_recurring_period`	TEXT	`monthly`, `quarterly`, etc.
`visible`	INTEGER	1 = show in reports
`target_iban`	TEXT	Recipient IBAN
`bic`	TEXT	Bank BIC
`txn_id`	TEXT	Bank transaction ID
`creditor_id`	TEXT	SEPA creditor ID
`mandate`	TEXT	SEPA mandate reference
`customer_ref`	TEXT	Customer reference

Full-Text Search

FTS5 virtual tables for fast text search:

transactions_fts
transactions_analyzed_fts

Indexed fields: payee, description, merchant, name_clean

Kept in sync via triggers (*_ai, *_ad, *_au).

Indexes

Each table has indexes on: - booking_date - cl_category - amount - source_file - visible (partial index WHERE visible = 1) - (booking_date, cl_category) composite

Schema Versioning

schema_meta table tracks version:

Column	Type	Description
`version`	INTEGER	Current schema version (1)