Database Architecture

Patrimoine uses SQLAlchemy 2.0 async models with SQLite (default) or PostgreSQL as the database. Migrations are managed by Alembic with an async runner.

SQLAlchemy Models

All models inherit from Base (an AsyncAttrs + DeclarativeBase subclass) defined in app/models/base.py. Most models also use the TimestampMixin which provides created_at and updated_at columns with server defaults.

Base Classes and Mixins

class Base(AsyncAttrs, DeclarativeBase):
    """Declarative base with async lazy-load support."""
    pass

class TimestampMixin:
    """Provides created_at and updated_at with server defaults."""
    created_at: Mapped[datetime] = mapped_column(server_default=func.now())
    updated_at: Mapped[datetime] = mapped_column(server_default=func.now(), onupdate=func.now())

Enums

class AccountType(StrEnum):
    CHECKING = "checking"
    SAVINGS = "savings"
    INVESTMENT = "investment"
    RETIREMENT = "retirement"
    CASH = "cash"
    REAL_ESTATE = "real_estate"
    VEHICLE = "vehicle"
    COLLECTIBLE = "collectible"
    OTHER = "other"

class Currency(StrEnum):
    EUR = "EUR"
    USD = "USD"
    GBP = "GBP"
    CHF = "CHF"
    JPY = "JPY"
    CAD = "CAD"
    AUD = "AUD"
    CNY = "CNY"
    NOK = "NOK"
    SEK = "SEK"

Tables Overview

50 tables (47 model classes + 3 association tables — savings_goal_accounts, transaction_tags, planned_transaction_tags).

Reverify the count

Run grep -rn '__tablename__' backend/app/models/*.py plus grep -rn '= Table(' backend/app/models/*.py to reverify this count.

The diagram below covers the core relationships, not every table. These tables are omitted from it for readability:

• tags
• categorization_rules
• planned_transactions
• reconciliations
• net_worth_thresholds
• chart_annotations
• webhook_deliveries
• email_change_tokens
• password_reset_tokens
• user_invites
• transaction_splits
• saved_filters
• planned_suggestion_dismissals
• report_settings
• saved_reports
• dashboards
• budget_month_limits
• alert_rules
• alerts
• notification_preferences
• payees
• payee_aliases

See the Database Models table in Backend Architecture for the full model list.

erDiagram accTitle: Database entity relationships accDescr: Entity-relationship diagram of the main tables. Every user owns accounts, categories, institutions, goals, budgets, transactions, holdings, exchange rates, API tokens, webhooks, audit logs, delta cache entries, browser sessions and recovery codes, and accounts link to institutions, categories, snapshots, transactions and holdings. users ||--o{ accounts : owns users ||--o{ categories : owns users ||--o{ institutions : owns users ||--o{ savings_goals : owns users ||--o{ budgets : owns users ||--o{ transactions : owns users ||--o{ holdings : owns users ||--o{ exchange_rates : owns users ||--o{ api_tokens : owns users ||--o{ webhooks : owns users ||--o{ audit_logs : owns users ||--o{ delta_cache : owns users ||--o{ user_sessions : "signs in through" users ||--o{ totp_recovery_codes : "can sign in with" institutions ||--o{ accounts : "linked to" accounts ||--o{ snapshots : "has balance history" accounts ||--o{ recurring_snapshots : "scheduled by" accounts ||--o{ transactions : "posted to" accounts ||--o{ holdings : "held in" holdings ||--o{ dividends : pays savings_goals }o--o{ accounts : "funded by (savings_goal_accounts)" users { int id PK string sub string email string password_hash string auth_provider string locale string base_currency } accounts { int id PK int user_id FK int institution_id FK string name string account_type string currency bool is_liability bool on_budget bool exclude_from_net_worth int linked_account_id FK datetime archived_at datetime deleted_at } snapshots { int id PK int account_id FK date date numeric balance string currency datetime deleted_at } institutions { int id PK int user_id FK string name bool is_default datetime archived_at datetime deleted_at } categories { int id PK int user_id FK string name } recurring_snapshots { int id PK int account_id FK string frequency bool enabled datetime next_run_at } savings_goals { int id PK int user_id FK string name numeric target_amount date deadline } budgets { int id PK int user_id FK int category_id FK numeric monthly_limit } transactions { int id PK int user_id FK int account_id FK int category_id FK numeric amount string transaction_type date transaction_date string transfer_group_id } holdings { int id PK int user_id FK int account_id FK string ticker numeric quantity numeric cost_basis numeric fee string asset_class } dividends { int id PK int holding_id FK numeric amount date ex_date date pay_date } exchange_rates { int id PK int user_id FK string from_currency string to_currency numeric rate date date } api_tokens { int id PK int user_id FK string name string token_hash datetime expires_at } webhooks { int id PK int user_id FK string url bool is_active } audit_logs { int id PK int user_id FK string event string method string path int status_code } revoked_tokens { int id PK string jti string user_sub datetime expires_at } user_sessions { int id PK int user_id FK string sid datetime expires_at datetime revoked_at } totp_recovery_codes { int id PK int user_id FK string lookup_id string code_hash datetime used_at } delta_cache { int id PK int user_id FK date snapshot_date numeric delta numeric total }

Key columns are a selection.

Table	Model File	Purpose	Key Columns
users	user.py	User accounts (OIDC + local auth)	id, sub, email, password_hash, auth_provider, locale, base_currency, tour_seen, legacy_sessions_revoked_at, custom_css, missing_rates_dismissed, setup_checklist_hidden
accounts	account.py	Financial accounts with soft-delete	id, user_id, name, account_type, currency, institution_id, is_liability, liquidity_horizon, on_budget, exclude_from_net_worth, linked_account_id (nullable self-reference, ON DELETE SET NULL), archived_at, sort_order, deleted_at
snapshots	snapshot.py	Balance records per account per date	id, account_id, date, balance (Numeric 12,2), currency, source, note, deleted_at
categories	category.py	User-owned spending categories (with subcategories)	id, user_id, name, color, sort_order, exclude_from_spending (Boolean, default false)
institutions	institution.py	User-owned financial institutions	id, user_id, name, color, sort_order, is_default, archived_at, deleted_at
recurring_snapshots	recurring_snapshot.py	Scheduled snapshot rules	id, account_id, frequency, day_of_week, day_of_month, balance, enabled, next_run_at
savings_goals	savings_goal.py	Savings targets with deadlines	id, user_id, name, target_amount, current_amount, deadline, start_date, archived, funding_schedule_id, template_id
savings_goal_accounts	savings_goal.py	M2M association (SavingsGoal ↔ Account)	goal_id, account_id
envelopes	envelope.py	Money set aside for a non-monthly expense	id, user_id, name, target_amount, due_date, interval_months, color, archived, funding_schedule_id
funding_schedules	funding_schedule.py	A user’s pay schedule: a name and a cadence, dates only	id, user_id, name, frequency, repeat_every, day_of_week, day_of_month, second_day_of_month, month_of_year, start_date, weekend_shift, is_default, created_at, updated_at
goal_templates	goal_template.py	A user’s reusable contribution: a fixed amount per paycheck or per month	id, user_id, name, kind, amount, created_at, updated_at
envelope_contributions	envelope.py	Amounts added to (or taken from) an envelope	id, user_id, envelope_id, amount, contribution_date, note
budgets	budget.py	Monthly spending limits per category	id, user_id, category_id, subcategory_id, monthly_limit, rollover_enabled, archived
budget_month_limits	budget.py	A budget’s own limit for one month; months without a row use the budget’s default limit	id, user_id, budget_id, month, monthly_limit, created_at, updated_at
transactions	budget.py	Income/expense transactions	id, user_id, account_id, amount, transaction_type, category_id, subcategory_id, description, transaction_date, status, deleted_at, reconcile_id, transfer_group_id, payee_id
payees	payee.py	Per-user payees a transaction points at	id, user_id, name, name_key (unique per user), default_category_id, default_subcategory_id
payee_aliases	payee.py	Other spellings of a payee, resolving to it	id, user_id, payee_id (FK, ON DELETE CASCADE), alias, alias_key (unique per user)
transaction_splits	transaction_split.py	Category lines of a split transaction	id, transaction_id (FK, ON DELETE CASCADE), category_id, subcategory_id, amount (Numeric 12,2), position
saved_filters	saved_filter.py	Named transaction filters, one set per user	id, user_id, name, filters (JSON), created_at, updated_at
report_settings	report_settings.py	One user’s assumptions for the financial independence report	id, user_id (unique), investable_account_ids (JSON, nullable), withdrawal_rate (Numeric 5,2), real_return (Numeric 5,2), annual_spending_override (Numeric 12,2, nullable), current_age (nullable), retirement_age, retirement_length, volatility (Numeric 5,2), seed, created_at, updated_at
saved_reports	saved_report.py	Named report definitions, one set per user	id, user_id, name, definition (JSON), created_at, updated_at
dashboards	dashboard.py	Named dashboards of one user, their tab order and card layout	id, user_id, name, sort_order, layout (JSON), created_at, updated_at
reconciliations	reconciliation.py	Accounts reconciled against a statement	id, user_id, account_id, statement_date, statement_balance, snapshot_id, replaced_snapshot_id, replaced_snapshot_balance, undone_at
holdings	investment.py	Investment holdings	id, user_id, account_id, ticker, name, quantity (Numeric 24,8), cost_basis, fee (Numeric 12,2, default 0), purchase_date, current_price, asset_class (String 20, nullable)
dividends	investment.py	Dividend payments	id, holding_id, amount, ex_date, pay_date
exchange_rates	exchange_rate.py	FX rates (manual + auto)	id, user_id, from_currency, to_currency, rate (Numeric 24,10), date, source
api_tokens	api_token.py	Long-lived API tokens	id, user_id, name, token_hash (SHA-256), expires_at, last_used_at
webhooks	webhook.py	Webhook endpoint configs	id, user_id, url, secret, is_active
audit_logs	audit_log.py	Persisted audit events	id, user_id, event, method, path, status_code, ip_address, duration_ms
revoked_tokens	revoked_token.py	JWT blacklist	id, jti, user_sub, revoked_at, expires_at
user_sessions	user_session.py	Tracked browser sessions of local users	id, user_id, sid (unique), created_at, last_seen_at, expires_at, revoked_at, user_agent, ip_network
totp_recovery_codes	totp_recovery_code.py	One-time two-factor recovery codes	id, user_id, lookup_id, code_hash (Argon2), used_at, created_at
subcategories	category.py	Subcategories of a category	id, user_id, category_id, name, sort_order
tags	tag.py	User-owned tags	id, user_id, name, color
transaction_tags	budget.py	M2M association (Transaction ↔ Tag)	transaction_id, tag_id
planned_transactions	planned_transaction.py	Planned/recurring transactions (merged bills + recurring)	id, user_id, account_id, amount, transaction_type, next_due_date, frequency, repeat_every, payee_id, weekend_shift, skipped_dates, auto_post, linked_planned_transaction_id, savings_goal_id, deleted_at
planned_transaction_tags	planned_transaction.py	M2M association (PlannedTransaction ↔ Tag)	planned_transaction_id, tag_id
planned_suggestion_dismissals	planned_suggestion_dismissal.py	Dismissed planned-transaction suggestions	id, user_id, account_id, transaction_type, description_key
categorization_rules	categorization_rule.py	Rule-based auto-categorization	id, user_id, name, priority, enabled, apply_on_import, conditions (JSON), action_category_id, action_subcategory_id, action_tag_ids, action_suggested_label, deleted_at
chart_annotations	chart_annotation.py	Annotations overlaid on charts	id, user_id, date, label
net_worth_thresholds	net_worth_threshold.py	Net worth thresholds	id, user_id, target_amount, direction, enabled, is_past
alert_rules	alert.py	A user’s alert rule settings	id, user_id, rule_type, enabled, category_id, account_id, percent, percent2, days
alerts	alert.py	Alerts fired by the rules, one row per occurrence	id, user_id, rule_type, dedup_key, payload, resolved_at, read_at, dismissed_at
notification_preferences	alert.py	Channel of each notification type	id, user_id, rule_type, channel
transaction_duplicate_dismissals	transaction_duplicate_dismissal.py	Transaction pairs declared “not duplicates”	id, user_id, transaction_a_id, transaction_b_id
user_invites	user_invite.py	Admin-issued invites	id, email, token_hash, created_by_user_id, redeemed_user_id, expires_at, redeemed_at, revoked_at
password_reset_tokens	password_reset_token.py	Single-use password reset tokens	id, user_id, token_hash, expires_at, used_at
email_change_tokens	email_change_token.py	Single-use email change tokens	id, user_id, new_email, token_hash, expires_at, used_at
webhook_deliveries	webhook_delivery.py	Per-attempt webhook deliveries	id, webhook_id, event, event_id, status, attempt_count, last_error, next_retry_at
delta_cache	delta_cache.py	Precomputed daily net-worth deltas	id, user_id, snapshot_date, delta (Numeric 14,2), total (Numeric 14,2)

Soft-Delete Pattern

The Account, Snapshot, Institution, Transaction, CategorizationRule and PlannedTransaction models implement soft-delete via a deleted_at column (users carry it too, for account deletion):

• Account: deleted_at: Mapped[datetime | None] — when set, the account is hidden from queries but preserved for historical snapshots
• Snapshot: deleted_at: Mapped[datetime | None] — same pattern; the unique index on (account_id, date) uses a partial index (sqlite_where=text("deleted_at IS NULL")) so soft-deleted snapshots don’t block new entries
• Institution: deleted_at: Mapped[datetime | None] — same pattern
  • the unique index on (user_id, name) uses a partial index (sqlite_where=text("deleted_at IS NULL")) so soft-deleted institutions don’t block re-creation with the same name
  • soft-deleting an institution cascades to its linked accounts and their snapshots
  • restoring cascades back
• Transaction: deleted_at: Mapped[datetime | None] — a trashed transaction is excluded from every normal read by ledger_scope.live(), but it keeps its slot in the unique indexes (such as the external-id index used to de-duplicate imports) as a tombstone, so re-importing the same bank file does not bring it back. A restore that would collide with a live row is refused with a 409

The Institution model also implements archiving via an archived_at column:

• Institution: archived_at: Mapped[datetime | None] — when set, the institution is visually dimmed and can be hidden from the main list; archiving cascades to linked accounts (sets their archived_at)

The Account model similarly has an archived_at column for the same purpose.

Queries filter with .where(Model.deleted_at.is_(None)) to exclude soft-deleted records.

Account Unique Constraint

The accounts table has a partial unique index on (institution_id, name):

Index(
    "ix_accounts_institution_name",
    "institution_id",
    "name",
    unique=True,
    sqlite_where=text("deleted_at IS NULL"),
),

This allows accounts with the same name but different institutions (e.g., “Courant” at Boursorama, N26, and Revolut are all permitted). The import logic matches accounts by (name, institution) pair — the name-only fallback is only used when the backup account has no institution, preventing accidental merging of same-name accounts from different institutions.

Institution Unique Constraint

The institutions table has a partial unique index on (user_id, name):

Index(
    "uq_institutions_user_name",
    "user_id",
    "name",
    unique=True,
    sqlite_where=text("deleted_at IS NULL"),
),

This allows soft-deleted institutions to be re-created with the same name. The original table-level UniqueConstraint was replaced by this partial unique index in migration 030.

Trash (Soft-Deleted Records)

Soft-deleted accounts and institutions are listed via GET /api/v1/accounts/deleted/list and GET /api/v1/institutions/deleted/list respectively. Records can then be permanently deleted in two ways:

• One record at a time, via DELETE /api/v1/{entity}/{id}/permanent.
• All soft-deleted records in one operation, via DELETE /api/v1/{entity}/deleted/empty (empty trash).

Permanent deletion and cascades depend on the entity:

• For institutions, permanent deletion hard-deletes the institution row; CASCADE FK removes linked accounts, snapshots, and recurring snapshots.
• Soft-deleting an institution cascades to its linked accounts (sets deleted_at) and their snapshots.
• Restoring an institution cascades back, clearing deleted_at on linked accounts and snapshots.
• For accounts, permanent deletion hard-deletes snapshots first (FK is RESTRICT/CASCADE), then the account row. Recurring snapshots are auto-deleted via CASCADE FK.
• Before a permanent account or institution delete (single or empty trash), transaction_lock.release_account_locks downgrades the reconciled transactions of the accounts being removed to cleared and clears their reconcile_id, so the reconciliation rows can go with the account without leaving locked orphans.
• Transactions are listed, restored, permanently deleted and emptied through /api/v1/budget/transactions/... (see API Design Conventions). Nothing purges the transaction trash automatically.

Reconciliations

The reconciliations table records each reconcile of an account against a statement: the statement date and balance, the snapshot the reconcile wrote (snapshot_id) or replaced (replaced_snapshot_id, replaced_snapshot_balance, so an undo can put it back), and undone_at. The transactions a reconcile locked point back at it through transactions.reconcile_id; undoing it sets undone_at and releases them. status is NOT NULL with a server default of cleared; reconciled can only be reached through the reconcile flow. Backups carry no database ids for these links: a transaction names its reconciliation by statement date inside its own account.

Split Lines

A split transaction keeps its own transactions row and amount; its category lines live in transaction_splits: transaction_id (foreign key to transactions.id, ON DELETE CASCADE, so permanently deleting a parent never leaves an orphan line), category_id, a nullable subcategory_id, a signed non-zero amount (Numeric(12, 2)) and position, the order of the line inside its split. The table has no user_id: ownership comes from the parent, and every read reaches a line through a parent that is already scoped to the user.

While a parent has lines its own category_id and subcategory_id are NULL. A split has 2 to 20 lines whose sum equals the parent’s signed amount, and a reconciled row or a transfer leg is never split. split_service is the one place that saves or removes lines and keeps these rules; no database constraint backs them. position is deliberately not unique, so replacing the lines inserts the new ones before deleting the old ones in one flush. The category readers expand lines through ledger_scope.select_flow_lines (see Backend Architecture); totals, balances and the cash scope keep reading the parent row.

Saved Filters and the Tour Flag

saved_filters holds a user’s named transaction filters: user_id, name (String(120)), the filter set as JSON in filters, and created_at / updated_at. The unique index uq_saved_filters_user_name on (user_id, name) makes a name unique per user, and saved_filter_service caps a user at 100 filters. The JSON is the validated SavedFilterPayload and holds database ids (category, subcategory, account, tag and payee ids), which is why a backup carries names instead.

users.tour_seen is a Boolean NOT NULL with a server default of false: it records that the guided tour has been offered. A new account starts false and is offered the tour once.

Migrations 092 (transaction_splits), 093 (saved_filters) and 094 (users.tour_seen) are automatic and additive: each creates its table, indexes or column only when missing, runs the same DDL on SQLite and PostgreSQL, and touches no existing table or index. Migration 094 backfills every user who exists when the column is added to true, so existing users are not offered a tour of an app they already know; the backfill runs only when the column is added, so re-running the upgrade never rewrites a choice. Because no manual admin step is needed, there is no entry in Updating a Deployment. downgrade() drops the indexes and tables or the column: every split line, saved filter and tour flag is lost, and a split parent keeps its NULL category.

Planned Transaction Options and Dismissed Suggestions

planned_transactions has two columns for the weekend option and skipped dates. weekend_shift is a String(6) NOT NULL with a server default of none (none, before or after): where an occurrence that falls on a Saturday or Sunday is posted, and only a recurring item may hold anything but none. skipped_dates is a nullable JSON list of sorted ISO anchor dates the user stepped over; SQL NULL (never JSON null) means none, and the list is pruned of dates before the next due date so it stays bounded. next_due_date and day_of_month stay the recurrence anchor: the effective date is computed by planned_schedule and never stored. payee_id is a nullable foreign key to payees (ON DELETE SET NULL, indexed): the payee every transaction the item generates is created with. Changing it later never touches transactions already posted.

planned_suggestion_dismissals records a suggested planned transaction that the user dismissed: user_id, account_id (foreign key to accounts.id, ON DELETE CASCADE, so permanently deleting an account takes its dismissals with it), transaction_type (String(10)), description_key (String(150), the description with whitespace collapsed and case folded) and created_at / updated_at. The unique constraint uq_planned_suggestion_dismissals_key on (user_id, account_id, transaction_type, description_key) makes dismissing idempotent. The key holds no frequency, so a pattern that changes cadence stays dismissed. Suggestions themselves are never stored.

Migrations 095 (planned_transactions.weekend_shift and skipped_dates) and 096 (planned_suggestion_dismissals) are automatic and additive: each adds its columns, table or indexes only when missing, runs the same DDL on SQLite and PostgreSQL, backfills nothing (existing rows read none and NULL) and touches no existing index. Because no manual admin step is needed, there is no entry in Updating a Deployment. downgrade() drops the columns, or the indexes and the table: every weekend option, skipped date and dismissed suggestion is lost.

Report Settings and Saved Reports

report_settings holds one row per user, enforced by the unique index uq_report_settings_user on user_id: investable_account_ids (JSON, nullable), withdrawal_rate and real_return (Numeric(5, 2), in percent, both defaulting to 4.00), annual_spending_override (Numeric(12, 2), nullable), the five retirement simulation settings and created_at / updated_at. The simulation settings are current_age (nullable, no default: the simulation asks for it), retirement_age (default 65), retirement_length in years (default 30), volatility in percent (Numeric(5, 2), default 15.00) and seed (default 1). A user with no row has the defaults, and reading the settings never creates a row. The account list is a plain JSON list with no foreign key: SQL NULL (declared with JSON(none_as_null=True), never the JSON literal null) means “use the default accounts”, and an id that is archived, deleted, a liability or not the user’s own is dropped whenever the list is read, saved or restored.

saved_reports holds a user’s named report definitions: user_id (indexed), name (String(120)), the definition as JSON in definition, and created_at / updated_at. The unique index uq_saved_reports_user_name on (user_id, name) makes a name unique per user, and saved_report_service caps a user at 100 reports. The JSON is the validated, versioned ReportDefinition (schema_version, at most 8 KB). Its filters hold database ids (category, subcategory, account and tag ids), which is why a backup carries names instead.

Migrations 097 (report_settings) and 098 (saved_reports) are automatic and additive: each creates its table and indexes only when missing, runs the same DDL on SQLite and PostgreSQL, backfills nothing and touches no existing table. Because no manual admin step is needed, there is no entry in Updating a Deployment. downgrade() drops the indexes and the tables: every report setting and saved report is lost.

Migration 099 adds the five simulation columns to report_settings. It is automatic and additive too: each column is added only when missing, with a server default for existing rows, and the same DDL runs on SQLite and PostgreSQL. There is no entry in Updating a Deployment. downgrade() drops the five columns, so the saved simulation settings are lost and every other report setting is kept.

Recovery Codes and Sessions

totp_recovery_codes holds the one-time recovery codes of a local user who enabled TOTP: user_id (foreign key to users.id), lookup_id (String(5), the first group of the code, stored in plain), code_hash (String(255), an Argon2 hash of the secret part; the code itself is never stored), used_at (nullable, set when the code is spent) and created_at. The unique index uq_totp_recovery_codes_user_lookup on (user_id, lookup_id) makes a lookup id unique per user. Disabling TOTP, starting a new enrolment and permanently deleting the account delete the rows.

user_sessions tracks one signed-in browser session per row: user_id (foreign key to users.id), sid (String(32), the random id carried by the session cookies, unique through uq_user_sessions_sid), created_at, last_seen_at (nullable, written only when the session’s refresh token rotates), expires_at, revoked_at (nullable, set to end the session early), user_agent (String(100), a coarse “Browser on System” label) and ip_network (String(64), the client address truncated to its /24 or /48 network). ix_user_sessions_user_id and ix_user_sessions_expires_at serve the per-user list and the daily purge, which deletes a row 30 days after its expires_at. users.legacy_sessions_revoked_at is a nullable DateTime stamped when a user signs out everywhere else: a token without a sid whose sign-in time is at or before it is refused (see Authentication).

Migrations 100 (totp_recovery_codes) and 101 (user_sessions and users.legacy_sessions_revoked_at) are automatic and additive: each creates its table, indexes or column only when missing, runs the same DDL on SQLite and PostgreSQL, backfills nothing and touches no existing row. Nobody is signed out on upgrade, because tokens issued before sessions were tracked carry no sid and keep working until they expire. Because no manual admin step is needed, there is no entry in Updating a Deployment. downgrade() drops the column, the indexes and the table for 101, so every tracked session is forgotten (the tokens themselves stay valid), and the index and the table for 100, so every recovery code is lost.

Migration 102 adds users.custom_css, a nullable Text column that holds a user’s custom theme as the canonical --name: value; lines the API validated and re-serialised (see custom_css_service in Backend Architecture); NULL means no custom theme. It is automatic and additive: the column is added only when it is missing, the same DDL runs on SQLite and PostgreSQL, and nothing is backfilled, so every existing user starts without a theme. Because no manual admin step is needed, there is no entry in Updating a Deployment. downgrade() drops the column, so every saved custom theme is lost.

Migration 115 adds users.missing_rates_dismissed, a nullable VARCHAR(1024) column that holds the sorted, comma-joined currencies (as the X-Missing-Rates header names them) for which the user dismissed the web app’s missing-rates notice; NULL means nothing is dismissed. The user’s own exchange-rate changes clear it. It is automatic and additive: the column is added only when it is missing, the same DDL runs on SQLite and PostgreSQL, and nothing is backfilled, so every existing user sees the notice as before. Because no manual admin step is needed, there is no entry in Updating a Deployment. downgrade() drops the column, so every dismissal is forgotten.

Migration 117 creates alert_rules and alerts. alert_rules holds a user’s saved setting for each alert rule type (stale_snapshot, bill_due, budget_overspent): whether it is enabled, an optional category or account the rule is narrowed to, and an optional percent or days threshold. It is unique on (user_id, rule_type) (uq_alert_rules_user_type), and its category_id and account_id foreign keys are ON DELETE SET NULL, so deleting a category or account widens the rule to all instead of blocking the delete. alerts holds one row per fired occurrence of a rule, unique on (user_id, dedup_key) (uq_alerts_user_dedup_key); the ids of accounts, categories and planned items live in its JSON payload, not in foreign keys, and an alert whose condition no longer holds keeps its row with resolved_at set. Rules nobody saved are served from defaults, so no per-user row is inserted. The migration is automatic and additive: each table is created only when missing, the same DDL runs on SQLite and PostgreSQL, and nothing is backfilled. Because no manual admin step is needed, there is no entry in Updating a Deployment. downgrade() drops both tables, so every stored rule and every fired alert is lost.

Migration 118 adds users.setup_checklist_hidden, a Boolean NOT NULL column with a server default of false: it records that the user hid the web app’s Dashboard setup checklist, on every device they sign in on. The migration is automatic and additive: the column is added only when it is missing, the same DDL runs on SQLite and PostgreSQL, and nothing is backfilled, so every existing user starts at false. Because no manual admin step is needed, there is no entry in Updating a Deployment. downgrade() drops the column, so every Hide is forgotten and the checklist shows again.

Migration 120 creates payees and payee_aliases and adds transactions.payee_id. A payee belongs to one user and is identified inside that user by name_key, the case-folded form of its name with every run of whitespace collapsed to one space (unique per user, uq_payees_user_name_key); it can store a default_category_id and a default_subcategory_id (both ON DELETE SET NULL). payee_aliases holds other spellings of a payee, unique per user on alias_key (uq_payee_aliases_user_alias_key) and removed with its payee (ON DELETE CASCADE). transactions.payee_id is nullable, indexed and ON DELETE SET NULL. The migration backfills every existing transaction, transfer legs and trashed rows included: the payee is named after suggested_label when it is not blank, otherwise after description, and a row with neither gets no payee. Rows of one user whose text matches after trimming, collapsing whitespace runs and ignoring case share one payee, which keeps the spelling of the lowest transaction id. The grouping is done in Python with the same normalization the application uses (payee_service.normalize_payee_name, copied into the migration because a migration never imports application code), not with SQL string functions, whose case folding differs between SQLite and PostgreSQL. description and suggested_label are never written. The migration is automatic, additive and safe to run again: each table, column and index is created only when missing, only rows with no payee are read, and only missing names are inserted. The same DDL runs on SQLite (batch mode for the transactions column) and PostgreSQL. Because no manual admin step is needed, there is no entry in Updating a Deployment. downgrade() drops the index, the foreign key, the column and both tables, so every payee, rename, alias and default category is lost; descriptions are untouched. Migration 121 later regroups the payees this backfill created.

Migration 121 regroups the payees that migration 120 named after exact bank labels, so that “Carte 02/08/24 Sncf Internet Cb0908” and “Carte 15/09/24 Sncf Internet Cb0908” become one payee. It applies the cleaning that imports use (payee_service.derive_payee_text, copied into the migration and asserted equal by a test): the operation prefix (“Carte”, “Avoir”, “Annul Carte” and its date), a trailing card suffix (“Cb*0908”), standalone dates (dd/mm, dd/mm/yy, dd/mm/yyyy, mm/yyyy) and a trailing long reference number are removed, the first 2,000 characters are cleaned until the text stops changing, and a label that would clean to nothing keeps its own text. A payee is label-backed when one of its own transactions, trashed ones included, has a description or suggested label that equals its key without any cleaning; only those are cleaned. Any other payee was typed by a person, renamed or is unused: it is never cleaned, renamed or merged away, and only receives a group whose cleaned name equals its own name or one of its alias names (two such candidates leave the group untouched). The one case this cannot see, a name typed by hand that equals one of its own transactions’ labels exactly, is cleaned like a backfilled payee. Label-backed payees of one user with the same cleaned key form a group, and a label-backed payee whose alias equals that key joins it. The survivor is the user-made receiver when there is one, otherwise the payee with aliases, then with a default category, then with the most transactions (trash included), then the lowest id. Transactions, aliases, rule payee_id conditions, saved-filter payee_ids and saved-report filters.payee_ids move to the survivor, a blank default category is filled from a merged payee that has one, and the merged payees are deleted. A survivor without aliases is renamed to the cleaned name; one with aliases keeps its name and gains the cleaned name as an alias; a merged payee that carries an alias or a default category leaves its name as an alias of a label-backed survivor. Descriptions and suggested labels are never written. The grouping runs in Python on the loaded payees, not with SQL string functions, and the writes are chunked. The migration is data-only (no DDL), identical on SQLite and PostgreSQL, rolls back entirely on any error, checks before and after that the number of transactions with a payee is unchanged and that no name/alias key clash or dangling payee_id appears, and changes nothing when run again. It is automatic, so there is no entry in Updating a Deployment. downgrade() does nothing: merged payees are not split again, and since transactions keep their text a split can be recomputed, while the names, aliases and defaults of the payees folded away are recoverable only from a backup. Migration 122 later applies the extended rules (store numbers, star references) to the payees that already exist.

Migration 122 regroups the payees again with the extended cleaning, because bank labels also carry per-transaction tokens that 121 left alone (“Freenow4uo2fi-2”, “Helpling Fr-1928”, “Bolt.eu /o/200227”, “Google Cloud 585z”, “Monoprix 4”, “Franprix 5524”). Imports use the same rules (payee_service.derive_payee_text); the migration carries its own copy, asserted equal by a test, and imports no application code and none from migration 121. After 121’s rules, and repeating until the text stops changing, it removes a *token glued to the name, a reference after a star and a space, a /o/200227 suffix, the 4-character code after Google Cloud (written “Google Cloud”), a trailing store or terminal number of one to three digits and a trailing id of four or five digits that is not a year; a rule applies only when a letter is left. A name with a space before the star (“Paypal google Cu”), a leading star, a bare “Cb0908”, a year and digits glued to a word stay as they are. Migration 121 already ran on real databases, so it is not edited; 122 keeps a frozen copy of 121’s rules only to recognise the payees 121 named, because after 121 a payee carries its cleaned name, which no longer equals any whole label: a payee is label-backed when one of its own transactions has a description or suggested label whose whole text, 121 cleaning or extended cleaning equals its key. The survivor, alias, default category and user-made rules, the JSON stores that are remapped, the checks, the data-only and no-DDL behavior and the no-op downgrade() are the same as for 121, whose planner, applier and checks are copied verbatim (a test compares their source). It is automatic, so there is no entry in Updating a Deployment.

Migration 123 adds users.suggest_category_from_payee, a Boolean NOT NULL column with a server default of true: it is the per-user switch of the payee category suggestion. The migration is automatic and additive: the column is added only when it is missing, the same DDL runs on SQLite and PostgreSQL, and nothing is backfilled, so every existing user starts ON. Because no manual admin step is needed, there is no entry in Updating a Deployment. downgrade() drops the column, so every stored choice is forgotten.

Migration 124 adds alerts.read_at and alerts.dismissed_at, two nullable timestamps that record when the user read or dismissed an alert, and creates notification_preferences, which holds a user’s channel (in_app, email, both or off) for one notification type. It is unique on (user_id, rule_type) (uq_notification_preferences_user_type), rule_type is limited to the five notification types (ck_notification_preferences_rule_type), channel to the four values (ck_notification_preferences_channel), and user_id is indexed (ix_notification_preferences_user_id). A user with no row for a type gets in_app, so no per-user row is inserted, and the alert_rules CHECK keeps its three types. The migration is automatic and additive: each column, table and index is created only when missing, the same DDL runs on SQLite and PostgreSQL, and nothing is backfilled. Because no manual admin step is needed, there is no entry in Updating a Deployment. downgrade() drops the index, the table and both columns, so every stored channel and every read or dismissed mark is lost.

Migration 125 adds alert_rules.percent2, a nullable integer: the second budget level of a rule (percent is the first, and a null percent2 means a single level). The budget alert key gains the level, budget:<category_id>:<YYYY-MM>:<level>. Only when it adds the column, the migration appends the user’s stored percent (200 at most, 100 when the user has no budget rule) to every existing budget key with two colons, so an alert already raised is the one the evaluator finds again; turns a stored percent of 100 into the pair 80 and 100; lowers a percent above 200 to 200; and resets users.alerts_baselined, so the first check after the update stores the new alerts without webhooks or emails. It uses CASE, CAST, ||, length and replace only, so the same SQL runs on SQLite and PostgreSQL, and re-running the upgrade over an upgraded database changes nothing. It is automatic; its effect on saved rules and on the PUT /api/v1/alert-rules contract is documented in Updating a Deployment. downgrade() deletes the budget alerts whose key carries a level, resets the baseline and drops the column, so every second level is lost.

Migration 126 changes no table: it resets users.alerts_baselined, so the first alert check after the update stores the net worth thresholds that are already crossed as notifications (key threshold:<threshold_id>:<first date of the run past the threshold>) without webhooks or emails. It is automatic and runs the same SQL on SQLite and PostgreSQL. downgrade() deletes the net_worth_threshold alerts and leaves the baseline as it is.

Migration 127 changes no table. It gives every user who owns an enabled recurring snapshot rule on an account that is not deleted a snapshot_reminder preference of email, because the hourly job used to email every reminder, unless that user already has a snapshot_reminder row (a NOT EXISTS guard, so a stored choice is never overwritten and a re-run adds nothing). Every other user stays on the in_app default. It also resets users.alerts_baselined, so the first alert check after the update stores the reminders that are already due (key reminder:<recurring_snapshot_id>:<date of the rule's last scheduled run>) without webhooks or emails; past reminder emails are not backfilled. It is automatic and runs the same SQL on SQLite and PostgreSQL (built with SQLAlchemy Core). downgrade() deletes the snapshot_reminder alerts and keeps the preference rows and the baseline as they are.

Dashboards

dashboards holds a user’s named dashboards: user_id (indexed), name (String(40)), sort_order (the tab position), the card layout as JSON in layout, and created_at / updated_at. The layout is {"cards": [{"id", "x", "y", "w", "h"}], "hidden": [id]} and is validated on the way in: at most 64 cards and 64 hidden ids, card ids of 1 to 40 lowercase letters, digits and hyphens, a 12-column grid (x + w at most 12), and no id placed twice or both placed and hidden. The server keeps no list of cards, so ids are format-checked only. dashboard_service caps a user at 8 dashboards, refuses to delete the last one, and refuses a layout that places a card another of the user’s dashboards already places (409 dashboard_card_conflict); deleting a dashboard adds its hidden cards to the first remaining dashboard so they stay hidden. Every write first takes a per-user lock (a no-op update of the user’s row), so two requests cannot go past the cap or create two first dashboards. Dashboards are part of the JSON backup from version 14 (see the version 14 paragraph below).

Migration 128 creates dashboards and its index ix_dashboards_user_id. It is automatic and additive: the table and the index are created only when missing, the same DDL runs on SQLite and PostgreSQL, nothing is backfilled (the browser uploads its first dashboard on its next visit) and no existing table is touched. Because no manual admin step is needed, there is no entry in Updating a Deployment. downgrade() drops the index and the table: every dashboard is forgotten.

Migration 129 adds planned_transactions.payee_id, its foreign key and its index ix_planned_transactions_payee_id. It is automatic and additive: the column, the foreign key and the index are created only when missing, the same DDL runs on SQLite and PostgreSQL, nothing is backfilled (existing planned items stay without a payee) and no existing column is touched. Because no manual admin step is needed, there is no entry in Updating a Deployment. downgrade() drops the index, the foreign key and the column: every planned payee link is forgotten.

Migration 130 creates budget_month_limits with its unique index uq_budget_month_limits_budget_month on (budget_id, month) and its index ix_budget_month_limits_user_id. A row is one budget’s own limit for one calendar month (month is a YYYY-MM key); a month without a row keeps using the budget’s default limit. It is automatic and additive: the table and the indexes are created only when missing, the same DDL runs on SQLite and PostgreSQL, a limit is removed with its budget (ON DELETE CASCADE), nothing is backfilled and no existing table is touched. Because no manual admin step is needed, there is no entry in Updating a Deployment. downgrade() drops both indexes and the table: every per-month limit is forgotten and every month goes back to its budget’s default limit.

Migration 131 creates funding_schedules with its index ix_funding_schedules_user_id, its unique index uq_funding_schedules_user_name on (user_id, lower(name)) and its partial unique index uq_funding_schedules_user_default on user_id WHERE is_default, which allows at most one default schedule per user. A row is a user’s pay schedule: a name and a cadence (weekly, semi_monthly, monthly, quarterly or yearly, with repeat_every, the day anchors, weekend_shift and a start_date), dates only and no amount; names are unique per user without regard to case. It is automatic and additive: the table and the indexes are created only when missing, the same DDL runs on SQLite and PostgreSQL, nothing is backfilled and no existing table is touched. Because no manual admin step is needed, it has no entry of its own in Updating a Deployment; the entry for migrations 131 and 132 covers it. downgrade() drops the three indexes and the table: every pay schedule is forgotten.

Migration 132 adds funding_schedule_id to envelopes and savings_goals: a nullable foreign key to funding_schedules.id (ON DELETE SET NULL; fk_envelopes_funding_schedule_id and fk_savings_goals_funding_schedule_id) with an index (ix_envelopes_funding_schedule_id and ix_savings_goals_funding_schedule_id). NULL means the user’s default schedule. It then moves the data of funding_income_id, the earlier link to a planned income, and drops that column, its foreign key and its index from both tables. A link moves only when its planned item is live (not deleted), of income type, enabled and recurring, with no end date and no occurrence cap: that income becomes a schedule of its user (cadence copied as it is, the income’s next due date as the start date, no default set); incomes of one user with the same cadence and next due date share one schedule, named after the first income, with a number added when the name is taken. Links to one-time, ended, capped, disabled and deleted incomes are cleared, so those items show the monthly suggestion until a schedule is picked, and skipped dates still ahead are not carried; for every other moved item the paydays and the per-payday amount are unchanged. The migration is automatic and the same DDL runs on SQLite and PostgreSQL; re-running the upgrade changes nothing. It needs no manual admin step, but it is a breaking API change and has an entry in Updating a Deployment. downgrade() re-adds funding_income_id, relinks each item whose schedule has exactly the name of one live planned income of the same user, and drops funding_schedule_id; schedules are kept, and links cleared by the upgrade or edited since cannot be recovered.

Migration 133 creates goal_templates, a user’s reusable contribution (a fixed amount per paycheck or per month), with its index ix_goal_templates_user_id and its unique index uq_goal_templates_user_name on (user_id, lower(name)), so names are unique per user without regard to case. It also adds template_id to savings_goals: a nullable foreign key to goal_templates.id (ON DELETE SET NULL; fk_savings_goals_template_id) with an index (ix_savings_goals_template_id); NULL means the goal has no template. A goal’s projected completion is computed from the template and the goal’s pay schedule on every read and is never stored. The migration is additive and automatic, the same DDL runs on SQLite and PostgreSQL (batch mode on SQLite), and nothing is backfilled. It needs no manual admin step, so it has no entry in the Updating page. downgrade() drops the link (index, foreign key and column), then the table; every template and every goal link is forgotten.

Migration 134 adds name_key to goal_templates, a String(400) column holding the name stripped and case-folded (casefold(), which can expand a name: ß becomes ss), and moves name uniqueness onto it: the unique index uq_goal_templates_user_name_key on (user_id, name_key) replaces uq_goal_templates_user_name. SQLite’s lower() only folds ASCII, so the old index let Épargne and épargne coexist there; the service and the backup restore already compared names with casefold(), and the database now agrees with them on SQLite and PostgreSQL alike. The model keeps name_key in step with name on every write, so no caller sets it, and it is not exported in backups. Existing rows are backfilled in Python; if case-folding newly makes two names of one user equal, the lowest id keeps the plain key and each later row gets a \x1f<id> suffix on its key (no name is changed or deleted, and only a count is logged). The migration is additive and automatic, re-runnable, and the same DDL runs on SQLite (batch mode) and PostgreSQL. It needs no manual admin step, so it has no entry in the Updating page. downgrade() drops the new index and the column and recreates the lower(name) index.

Migration 135 adds name_key to funding_schedules, a String(400) column holding the name stripped and case-folded (casefold(), which can expand a name: ß becomes ss), and moves name uniqueness onto it: the unique index uq_funding_schedules_user_name_key on (user_id, name_key) replaces uq_funding_schedules_user_name. SQLite’s lower() only folds ASCII, so the old index let Épargne and épargne coexist there, and PostgreSQL’s lower() does not turn ß into ss; the service and the backup restore now compare names with casefold() as well, and the database agrees with them on SQLite and PostgreSQL alike. The model keeps name_key in step with name on every write, so no caller sets it, and it is not exported in backups. Existing rows are backfilled in Python; if case-folding newly makes two names of one user equal, the lowest id keeps the plain key and each later row gets a \x1f<id> suffix on its key (no name is changed or deleted, and only a count is logged). The partial unique index on the default schedule and the links from envelopes and savings goals are untouched. The migration is automatic, re-runnable, and the same DDL runs on SQLite (batch mode) and PostgreSQL. It needs no manual admin step, so it has no entry in the Updating page. downgrade() drops the new index and the column and recreates the lower(name) index.

Migration 136 adds fee to holdings, a Numeric(12, 2) column, NOT NULL with a server default of 0, holding the fee paid for the purchase in the holding’s currency. A lot’s total cost is cost_basis x quantity + fee, computed when read (Holding.total_cost) and never stored; cost_basis stays the per-unit price. Every existing holding starts at 0, so no cost or gain changes and there is no backfill. The migration is additive and automatic, re-runnable (the column is added only when it is missing, so a re-run keeps stored fees), and the same DDL runs on SQLite and PostgreSQL. It needs no manual admin step, so it has no entry in the Updating page. downgrade() drops the column and every stored fee is forgotten.

Migration 137 adds exclude_from_net_worth to accounts, a Boolean NOT NULL column with a server default of false: the account stays visible but every net-worth total leaves it out. The migration is additive and automatic with alembic upgrade head; every existing account starts included, so no total changes and there is no backfill. The column is added only when it is missing, so a re-run keeps stored values, and the same DDL runs on SQLite and PostgreSQL. Downgrading drops the column and forgets every stored flag. The totals read the flag through services/net_worth_scope.py (counted()); GET /api/v1/snapshots/latest-per-account never does.

Migration 138 adds asset_class to holdings, a String(20) column, nullable with no default, holding the asset class of a lot, set by hand and validated against a fixed list by the API (stock, etf, bond, crypto, cash, real_estate, commodity, other). There is no database enum, so SQLite and PostgreSQL behave the same. Every existing holding stays unclassified, so no figure changes and there is no backfill. The migration is additive and automatic, re-runnable (the column is added only when it is missing, so a re-run keeps stored classes), and the same DDL runs on SQLite and PostgreSQL. It needs no manual admin step, so it has no entry in the Updating page. downgrade() drops the column and every stored class is forgotten.

Migration 139 adds linked_account_id to accounts, a nullable foreign key to accounts.id with ON DELETE SET NULL (named fk_accounts_linked_account_id) that links a loan to the asset it finances. The API validates it: only a liability account can carry a link, and the target must be a live, non-liability account of the same user. Every existing account stays unlinked, so no figure changes and there is no backfill. The migration is additive and automatic with alembic upgrade head, and re-runnable (the column is added only when it is missing, so a re-run keeps stored links). SQLite cannot add a foreign key to an existing table, so it runs in batch mode, while PostgreSQL gets a plain ALTER. It needs no manual admin step, so it has no entry in the Updating page. downgrade() drops the column and every stored link is forgotten. The equity of an asset is read through GET /api/v1/accounts/{id}/equity and never feeds a net-worth total.

Transfers and Off-Budget Accounts

A transfer is two transactions rows, one expense and one income, that share a transfer_group_id: an opaque String(36) with no foreign key, minted by the server and NULL on every ordinary row. Migration 090 adds the column, the plain index ix_transactions_transfer_group_id and the partial unique index uq_transactions_transfer_leg on (transfer_group_id, transaction_type) WHERE transfer_group_id IS NOT NULL, declared with the same predicate for SQLite and PostgreSQL. A group can therefore hold at most one expense leg and one income leg. The index has no deleted_at predicate: a trashed leg keeps its slot, like the other transaction indexes.

A group is consistent when it has two legs, one trash state and two different accounts:

• Shared trash state. Both legs are trashed and restored together with one deleted_at, so a trashed transfer never leaves a live half behind.
• Two accounts. The legs sit on different accounts; an edit that would put both on one account is refused.
• Permanent delete of an account. transactions.account_id is ON DELETE SET NULL, so the legs of a transfer stay linked with a NULL account: the surviving leg is still excluded from budget readers (the group id keeps it out of the flow scope), its transfer_peer.account_id is null, and it can still be unlinked, trashed and restored.

transfer_service.find_inconsistent_groups reports any group that breaks these rules, and the transfer tests call it after every step.

accounts.on_budget is a Boolean NOT NULL column with a server default of true, so every account that existed before migration 090 stays on-budget. An off-budget account keeps its rows in lists, running balances and net worth; the flow scope drops them from budgets and analytics. The flag is read live from the account, never copied onto a transaction.

Upgrading is automatic and needs no backfill. downgrade() drops both indexes and both columns: every transfer becomes unlinked (its two legs stay as ordinary income and expense rows) and each account’s on-budget setting is lost. See Updating a Deployment.

Backup version 7 carries transactions[].transfer_ref, a token that is only meaningful inside one export, and accounts[].on_budget; the group id itself never leaves the database. A restore pairs rows by token, links a pair only when it is provably a transfer (two legs, opposite types, two different accounts, one trash state, no group yet) and stamps a freshly minted group id. Older backups import with every transaction unlinked and every account on-budget.

Migration 091 adds three columns and nothing else: accounts.csv_import_mapping (JSON, nullable) holds the CSV column mapping last confirmed for that account, categorization_rules.action_suggested_label (String(255), nullable) holds the label a matching rule writes, and users.offer_rule_on_categorize (Boolean NOT NULL, server default false) is the per-user switch for the rule offer. Each column is added only when missing, the same DDL runs on SQLite and PostgreSQL, and no index is touched. Upgrading is automatic and needs no backfill, so every existing user keeps the current behaviour until they switch the offer on. downgrade() drops the three columns: every saved CSV mapping, rule label and rule-offer setting is lost. The mapping column is declared with JSON(none_as_null=True), so an account with no saved mapping stores SQL NULL, never the JSON literal null; the same condition reads “unset” on both engines. See Updating a Deployment for the automatic upgrade path.

Backup version 8 carries accounts[].csv_import_mapping, categorization_rules[].action_suggested_label and user.offer_rule_on_categorize. A restore reads each key only when the backup contains it: a missing key leaves the stored value alone, so a version 7 or older file still restores and never wipes a mapping or label. A restored mapping is validated against the same CsvImportMapping schema as the API and ignored with a warning if it does not validate (an explicit null clears it); a restored label is trimmed to 255 characters. The rule-offer setting is a real boolean, so false is a value rather than an absence, and anything that is not a boolean (1, "yes") is ignored with a warning.

Backup version 9 carries transactions[].splits, saved_filters and user.tour_seen. Each split line names its category and subcategory (never an id) and carries a signed amount and its position. Splits are restored inline with their transaction: a split that cannot be restored (not 2 to 20 objects, an unknown category or subcategory, an amount that is not a valid non-zero money value, or a transfer leg) restores the transaction unsplit and records one row error, and never fails the import. A backup without the splits key leaves the stored lines alone, so a version 8 or older file never removes a split. A saved filter names its categories (category_names), subcategories (subcategory_refs), accounts (account_refs) and tags (tag_names) instead of carrying ids; the restore resolves each among the restoring user’s own data, and a filter with an unresolvable reference, a rejected payload or no room left under the 100-filter cap is skipped with a row error rather than restored wider. Filters are matched by exact name: the same content is a duplicate, other content updates the stored filter. user.tour_seen is read as a real boolean, like user.offer_rule_on_categorize.

Backup version 10 carries planned_transactions[].weekend_shift and skipped_dates, and the dismissed suggestions nested on each account (accounts[].suggestion_dismissals). A backup without these keys leaves the stored values alone, so a version 9 or older file never removes a weekend option, a skipped date or a dismissal.

Backup version 11 carries report_settings and saved_reports. report_settings is a top-level object, null when the user never saved any, with investable_account_refs (a list of {name, institution_name}, or null for the default accounts) and withdrawal_rate, real_return and annual_spending_override as strings, plus the simulation settings current_age, retirement_age, retirement_length, volatility (a string) and seed. A restore resolves each account among the restoring user’s own accounts and keeps only the eligible ones, and a value outside the bounds of the settings schema records one row error that does not echo the value. A saved report is {name, definition}, and the filters of its definition name their categories (category_names), subcategories (subcategory_refs), accounts (account_refs) and tags (tag_names) like a saved filter does. A report with an unresolvable reference, an invalid definition or no room left under the 100-report cap is skipped with a row error rather than restored wider. Reports are matched by exact name: the same definition is a duplicate, another definition updates the stored report. Both are sections of a partial export, and a version 10 or older file restores without touching the stored settings or reports. The user section also carries custom_css, the canonical text or an empty string when the user has none. A restore validates it again with the rules of a profile save and skips an invalid value without failing, logging a warning that does not repeat it; an empty value leaves a stored theme alone.

Backup version 12 carries duplicate_dismissals, a top-level list of {ref_a, ref_b} pairs, and a duplicate_ref token on each transaction a pair names. The two rows of a pair are identical on purpose, so no field can tell them apart: the token is a per-export label, like transfer_ref, null on every other row, and the list is absent when there is no dismissal. A restore maps each token to the row it created or matched in the same run, drops a pair whose token is unknown or whose row was not restored, ignores a pair that already exists (a second import adds none) and ignores the list in a file older than version 12. The list belongs to the transactions section: a partial export includes it with that section and a restore reads it only when that section is restored. transaction_duplicate_dismissals stores each pair ordered (transaction_a_id < transaction_b_id), unique per user, with both foreign keys cascading on a permanent delete.

Backup version 13 carries payees, a top-level list of {name, default_category_name, default_subcategory_name, aliases} entries (the default category and subcategory by name, null when there is none; aliases a sorted list of strings), and transactions[].payee_name (null on a transaction without a payee). No payee or alias id is ever written, and a restore reads none. A restore resolves every name among the restoring user’s own payees and aliases, normalized like payees.name_key: an unknown payee_name creates a plain payee, a payee whose name or alias already exists is a duplicate that keeps its stored default category and gains the aliases still free, an alias that belongs to another payee is a row error in the payees section and is not stored, and a default category that does not exist leaves the payee without a default. payee_name is read even when the payees section is not selected. A file older than version 13 has neither key: each of its transactions gets the payee named by its suggested_label, else its description, cleaned by derive_payee_text (the same function imports use, which probes the key of the whole label before the cleaned key, so a payee or alias already stored for that exact text wins), while an explicit payee_name is resolved exactly as written. On a transaction that is already stored, a derived payee only fills a blank payee, while an explicit payee_name replaces it like the other restored fields. Restoring the same file again creates no second payee or alias. A saved filter or saved report names its payees in payee_names (a restore refuses payee_ids, like the other id keys), and one that names a payee that does not exist where you restore is skipped with a row error rather than restored wider. A rule condition on a payee carries the payee name (equals) or a list of names (one_of) instead of ids, and a restore maps the names back to the restoring user’s payee ids without creating a payee. A name that matches no payee becomes the id 2147483647, which no payee has, so the condition matches nothing and the rule still passes its own validation. The user section of version 13 also carries suggest_category_from_payee (a boolean); a restore leaves the stored value alone when the key is absent or is not a boolean.

Backup version 14 carries dashboards ({name, layout} in tab order, layout being the stored {"cards", "hidden"} object; card ids name a kind of card, so no database id is written), alert_rules ({rule_type, enabled, category_name, account_ref, percent, percent2, days} for each rule the user saved, category_name a category name or null, account_ref {name, institution_name} or null) and notification_preferences ({rule_type, channel}). A rule never saved is the default and is not written, and a rule whose category or account no longer resolves is not exported, because it would come back wider. A restore reads each section only when it is selected and looks every name up among the restoring user’s own data; it never creates a category or an account. Dashboards match by exact name: a same-name dashboard takes the file’s layout, and a new one is appended after the last sort_order under the per-user dashboard lock, so the cap of 8 and the one-dashboard-per-card rule hold, each violation being a row error in dashboards. Alert rules match by rule_type and are written straight to alert_rules after the rule schemas validate them (an unknown category or account, or a value out of range, is a row error); a restore does not evaluate alerts. Notification preferences match by type and are checked against the known types and channels. A file older than version 14 has none of the three sections and changes nothing.

Backup version 15 carries planned_transactions[].payee_name (null when the item has no payee). Like transactions[].payee_name it is a name, never an id, and a restore resolves it among the restoring user’s own payees and aliases: an unknown name creates a plain payee, and the name is read even when the payees section is not selected. On a planned item that is already stored, a payee_name that is present replaces its payee (null clears it) and counts as a difference, so a file that differs only by the payee updates the item instead of being skipped as a duplicate; a key absent from the file (a backup older than version 15) leaves the stored payee alone, and a value that is not text is a row error in planned_transactions that restores the item without changing its payee.

Backup version 16 carries budgets[].month_limits, a list of {month, monthly_limit} sorted by month ([] when the budget has none), names only and no id. A restore validates each entry’s month key and amount (0 is allowed, a negative or unreadable amount is not) and upserts it by budget and month under the budget it resolved or created for the restoring user; a month the file omits is never deleted, and a repeated month keeps its last entry. A bad entry, or a month_limits value that is not a list, is a row error in budgets while the budget and its other month limits are still restored. A matched budget whose only difference is its month limits counts as updated rather than as a skipped duplicate, and month limits are never counted as rows of their own. A file older than version 16 changes no stored month limit.

Backup version 17 carries funding_schedules[] (name, frequency, repeat_every, day_of_week, day_of_month, second_day_of_month, month_of_year, start_date, weekend_shift and is_default; names only, no id) and funding_schedule_name on savings_goals[] and envelopes[] (null when the item picks none). The section is exported and restored whenever envelopes or savings_goals is selected, so an older client that sends its own section list still round-trips the links. A restore runs funding_schedules before the goals and envelopes, matches each schedule by name without regard to case among the restoring user’s own schedules (no id from the file is read), and validates each row: a bad name, frequency, repeat, day, start date, weekend value or default flag is a row error in funding_schedules, and the items naming it stay unlinked while the rest of the file restores. Cadence fields are copied verbatim, never re-derived. A matched schedule takes the file’s cadence and counts as updated when it differs, otherwise as a skipped duplicate; a schedule flagged default in the file becomes the default only when the user has none. An item is linked on create, and relinked on match when the link differs (counted as updated); a missing or unresolved name leaves the stored link alone. A version 16 file maps the planned-income names it carries to schedules as described for migration 132, and a version 17 file never runs that mapping.

Backup version 18 carries goal_templates[] (name, kind and amount; names only, no id) and template_name on savings_goals[] (null when the goal has none). The section is exported and restored whenever savings_goals is selected, so an older client that sends its own section list still round-trips the links. A restore runs goal_templates before the goals, matches each template by name without regard to case among the restoring user’s own templates (no id from the file is read), and validates each row: a bad name, kind, or an amount that is not above zero within Numeric(12,2) is a row error in goal_templates, and the goals naming it stay unlinked while the rest of the file restores. A matched template takes the file’s kind and amount and counts as updated when they differ, otherwise as a skipped duplicate. A goal is linked on create, and relinked on match when the link differs (counted as updated); a missing or unresolved name leaves the stored link alone, and a restore never deletes a template. A version 17 file has no templates and changes no link.

Backup version 19 carries fee on holdings[] (a decimal string, "0.00" without a fee). A restore validates it like the other amounts (finite, zero or more, within Numeric(12,2)): a bad fee is a row error in holdings and that holding is skipped. A file without the key (version 18 and older) or with null creates holdings with fee 0 and leaves the fee of a matched holding unchanged. A matched holding whose fee differs from the file counts as updated. Version 19 also carries exclude_from_net_worth on accounts[]. A restore reads it like on_budget: only a real boolean counts, anything else is treated as absent. A file without the key (older versions) creates accounts included in net worth and leaves the flag of a matched account unchanged. A matched account whose flag differs from the file counts as updated.

Backup version 20 carries asset_class on holdings[] (a string from the fixed list, or null when the holding has no class). A restore reads it by key presence. A file without the key (version 19 and older) creates holdings with no class and leaves the class of a matched holding unchanged. A value outside the list (another string, a different case, the empty string or a non-string) restores as unclassified without a row error, so a file written by a later version still restores. A matched holding whose class differs from the file counts as updated.

Backup version 21 carries linked_account_name and linked_account_institution_name on accounts[] (strings, or null when the account has no link or its target is in the trash): the target’s name and its legacy institution text, the same reference every other account reference uses. A restore reads them by key presence and resolves the link in a second pass once every account of the file exists, so the order of the accounts does not matter. A file without the keys (version 20 and older) creates loans without a link and leaves the link of a matched loan unchanged. A name that resolves to nothing, to the loan itself, to a trashed account or to a liability, an empty or non-string value, or a loan the file marks as not a liability, restores as unlinked without a row error. The second pass never changes the accounts counts of the summary.

Backup version 22 carries font_size (a string: 87.5, 100, 112.5, 125 or 150, with 100 in place of an unset value) and density (compact, comfortable or roomy, with comfortable in place of an unset value) in the user section, the same vocabulary as the profile API. A restore reads them by key presence and validates them with the profile update rules, so a file without the keys (version 21 and older) leaves the stored values unchanged and an invalid value is ignored without a row error. Restoring the default value stores NULL.

Alembic Migrations

Migrations use an async runner (async_engine_from_config + asyncio.run) defined in backend/alembic/env.py. The target_metadata is set to Base.metadata, and all models are imported via app.models.__init__ to register table definitions.

Migration List

133 sequential migrations (latest: 133) with numeric revision IDs (run ls backend/alembic/versions/*.py | wc -l to reverify this count — it grows with every schema change):

Revision	Description
001	Initial schema and seed user
002	Remove stub user
003	Add revoked_tokens table for JWT revocation
004	Add is_liability column to accounts
005	Add category column to accounts
006	Add indexes for common query patterns
007	Add archived_at column to accounts
008	Add sort_order column to accounts
009	Add local auth columns to users
010	Fix revoked_tokens.user_id column type (Integer → String)
011	Create categories table
012	Create recurring_snapshots table
013	Create savings_goals table
014	Create api_tokens table
015	Create webhooks table
016	Create exchange_rates table
017	Add user.locale column
018	Add user.base_currency, exchange_rate.source, fix exchange_rate.created_at type
019	Add account.category_id FK to categories
020	Add savings_goal_accounts association table and savings_goal.start_date
021	Create audit_logs table
022	Create transactions and budgets tables
023	Create holdings and dividends tables
024	Backfill categories from free-text account.category and set category_id
025	Add account.institution_id FK, backfill from free-text
026	Create delta_cache table
027	Add account.liquidity_horizon column (enum: immediate, semi_liquid, blocked, long_term)
028	Add is_default to institutions, default institution per user, NOT NULL institution_id on accounts, CASCADE FKs, unique constraints
029	Move color from accounts to institutions (backfill + drop column)
030	Add deleted_at and archived_at to institutions; replace unique constraint with partial unique index (WHERE deleted_at IS NULL)
031	Add entity_type/entity_id to audit_logs; widen event to String(30)
032	Add detail JSON column to audit_logs
033	create webhook_deliveries table
034	add currency, price_source, current_price_updated_at to holdings
035	add user.theme column
036	Create net_worth_thresholds table
037	add user.totp_secret and totp_enabled columns
038	Create recurring_transactions table
039	Create password_reset_tokens table
040	Create email_change_tokens table
041	add user.report_frequency and report_next_run_at columns
042	Create chart_annotations table
043	add user.date_format and user.number_format columns
044	Create subcategories table
045	Add category_id/subcategory_id to transactions and recurring_transactions; rename their free-text category column to legacy_category
046	Add required category_id to budgets, backfilled from free-text category; rename the old free-text column to legacy_category
047	Drop Account.category and Account.category_id – account grouping is now AccountType-only (CONTEXT.md decision #1). Category rows previously linked to accounts are NOT deleted here – they remain in the categories table, immediately available for Transaction/Budget use (CONTEXT.md decision #7)
048	Widen categories.name and subcategories.name from String(50) to String(120)
049	Add OFX import fields — Transaction.external_id and Account.ofx_account_id
050	Store expense amounts negative, income positive (sign matches type)
051	Store recurring_snapshots.balance as Numeric(12,2) instead of Float
052	Add (user_id, name) uniqueness to categories and a (user_id, transaction_date) composite index to transactions
053	Add updated_at to webhooks, matching every sibling TimestampMixin model
054	Add tags, transaction_tags, recurring_transaction_tags
055	Make recurring_snapshots.balance nullable – reminder emails replace auto-creation
056	Add nullable subcategory_id to budgets, narrowing a budget to one subcategory of its category when set
057	Add nullable short_name to accounts
058	Add nullable opened_at to accounts
059	Add nullable suggested_label to transactions
060	Add is_admin and is_active columns to users
061	Create user_invites table
062	Widen holdings.cost_basis from Numeric(18,2) to Numeric(18,5)
063	Widen holdings.current_price from Numeric(18,2) to Numeric(18,5)
064	Widen audit_logs.event/entity_type to accommodate longer semantic names
065	Change users.theme default from “noir” to “fintech” for new accounts
066	add user.theme_mode column
067	Widen users.theme_mode from String(5) to String(10)
068	Create categorization_rules table
069	Create bills table
070	Add real_estate, vehicle, collectible to account_type enum
071	Add apply_on_import to categorization_rules
072	Add revoked_by_user_id to user_invites
073	Add deleted_at to users for account-deletion soft-delete
074	Add failed_login_attempts and locked_until to users for login lockout
075	Add suggested_label_user_set to transactions
076	Create planned_transactions – merge bills + recurring_transactions
077	Add recurrence bounds to planned_transactions and ledger link columns to transactions
078	Add last_login_at to users (admin user-management last-connexion column)
079	Add deleted_at (soft-delete/trash) to categorization_rules and planned_transactions
080	Enforce one active budget per category/subcategory slot
081	Add a composite (user_id, account_id) index to holdings
082	Add totp_last_step to users for TOTP replay protection
083	Make soft-delete-aware unique indexes partial on PostgreSQL
084	Add a nullable savings_goal_id FK to planned_transactions, so a planned transaction can optionally declare which savings goal it funds (surfaced on both the Planned Transaction form and the Savings Goals page)
085	Add users.token_version (bumped to end every session)
086	Encrypt stored webhook secrets
087	Normalize local account emails (lowercase, unique on lower(email))
088	Create envelopes and envelope_contributions
089	Add the transaction status, trash and reconcile state
090	Add transfers (transactions.transfer_group_id) and accounts.on_budget
091	Add accounts.csv_import_mapping, categorization_rules.action_suggested_label and users.offer_rule_on_categorize
092	Create transaction_splits, the category lines of a split transaction
093	Create saved_filters, a user’s named transaction filters
094	Add users.tour_seen, backfilled to true for existing users
095	Add planned_transactions.weekend_shift and skipped_dates
096	Create planned_suggestion_dismissals, a user’s dismissed planned-transaction suggestions
097	Create report_settings, a user’s financial independence assumptions
098	Create saved_reports, a user’s named report definitions
099	Add the retirement simulation settings to report_settings
100	Create totp_recovery_codes, a user’s one-time two-factor recovery codes
101	Create user_sessions, a user’s tracked browser sessions, and add users.legacy_sessions_revoked_at
102	Add users.custom_css, a user’s custom theme
103	Add funding_income_id (nullable FK to planned_transactions, ON DELETE SET NULL) to envelopes and savings_goals, the planned income a per-payday amount is computed from
104	Add categories.exclude_from_spending, which leaves a category out of the annual spending of the financial independence report
105	Widen exchange-rate precision
106	Add audit-log indexes
107	Add a (user_id, amount) index on transactions
108	Add envelope_contributions.idempotency_key
109	Index user_sessions.created_at
110	Add repeat_every to planned transactions
111	Add loan fields (interest rate, monthly payment) to accounts
112	Add CHECK constraints on transaction and planned-transaction type and status
113	Add reconciliation snapshot indexes
114	Create transaction_duplicate_dismissals
115	Add users.missing_rates_dismissed, the rate set whose missing-rates notice the user dismissed
116	Add users.email_verified, whether the identity provider vouched for the email
117	Create alert_rules and alerts, a user’s alert settings and the alerts they fired
118	Add users.setup_checklist_hidden, whether the user hid the Dashboard setup checklist
119	Add users.alerts_baselined, whether the user’s alerts have had their first evaluation, which posts nothing to webhooks
120	Create payees and payee_aliases and add transactions.payee_id, backfilled from suggested_label or description
121	Regroup existing payees by cleaned bank label (data-only)
122	Regroup existing payees by store, terminal and star references in bank labels (data-only)
123	Add users.suggest_category_from_payee, the per-user switch of the payee category suggestion
124	Add alerts.read_at and alerts.dismissed_at and create notification_preferences, a user’s channel for each notification type
125	Add alert_rules.percent2, the second budget level, and carry the budget alert keys over to the level format
126	Reset users.alerts_baselined so the net worth thresholds already crossed are stored silently as notifications (data-only)
127	Give the owners of an enabled recurring snapshot rule the email channel for snapshot reminders and reset users.alerts_baselined (data-only)
128	Create dashboards, a user’s named dashboards with their card layout
129	Add a nullable payee_id FK to planned_transactions, the payee its generated transactions carry
130	Create budget_month_limits, a budget’s own limit for one month
131	Create funding_schedules, a user’s named pay schedules
132	Add funding_schedule_id (nullable FK to funding_schedules, ON DELETE SET NULL) to envelopes and savings_goals, move the planned-income links into pay schedules and drop funding_income_id
133	Create goal_templates, a user’s contribution templates, and add template_id (nullable FK to goal_templates, ON DELETE SET NULL) to savings_goals
136	Add holdings.fee, the fee paid for a purchase (default 0)
137	Add accounts.exclude_from_net_worth, to keep an account out of net worth (default false)
138	Add holdings.asset_class, the optional asset class of a lot (null by default)
139	Add accounts.linked_account_id, the optional asset a loan finances (null by default)
140	Add users.font_size and users.density, the web text size and row density preferences (null by default, meaning 100 and comfortable)
141	Change the users.theme default from “fintech” to “ledger” for new accounts (existing users keep their skin)
142	Widen holdings.quantity to Numeric(24,8), holdings.cost_basis and holdings.current_price to Numeric(20,8), and exchange_rates.rate to Numeric(24,10); lossless, and the downgrade refuses if a stored value would not fit

env.py Configuration

# Async migration runner
async def run_async_migrations() -> None:
    connectable = async_engine_from_config(
        config.get_section(config.config_ini_section, {}),
        prefix="sqlalchemy.",
        poolclass=pool.NullPool,
    )
    async with connectable.connect() as connection:
        await connection.run_sync(do_run_migrations)
    await connectable.dispose()

def run_migrations_online() -> None:
    asyncio.run(run_async_migrations())

The DATABASE_URL environment variable overrides sqlalchemy.url in alembic.ini, enabling Docker deployment without modifying config files.

SQLite WAL Mode

When using SQLite, four PRAGMAs are set on every new connection via a SQLAlchemy event listener:

@event.listens_for(engine.sync_engine, "connect")
def set_sqlite_pragmas(dbapi_connection, connection_record):
    cursor = dbapi_connection.cursor()
    cursor.execute("PRAGMA journal_mode=WAL")
    cursor.execute("PRAGMA foreign_keys=ON")
    cursor.execute("PRAGMA busy_timeout=5000")
    cursor.execute("PRAGMA synchronous=NORMAL")
    cursor.close()

• WAL mode (journal_mode=WAL): Enables Write-Ahead Logging for concurrent read/write access — readers don’t block writers and vice versa.
• Foreign key enforcement (foreign_keys=ON): SQLite disables FK constraints by default; this PRAGMA must be set per-connection.
• Busy timeout (busy_timeout=5000): Waits up to 5 seconds on lock contention before raising SQLITE_BUSY.
• Synchronous NORMAL (synchronous=NORMAL): With WAL mode, only fsyncs on WAL checkpoint, not every commit — 10-100x faster commits than the default FULL while still crash-safe (last transaction may be lost on power failure, but the database won’t corrupt).

Do not use sqlite3 directly on the live database

WAL mode allows concurrent reads and writes within a single process, but SQLite does not support multi-process write access.

Never run sqlite3 commands directly on ./data/patrimoine.db while the backend container is running — this causes database is locked errors and can corrupt the WAL/SHM file permissions. Use podman compose exec backend sqlite3 ... instead, or stop the backend first.

See Debugging for details.

PostgreSQL Alternative

When DATABASE_URL starts with postgresql+asyncpg://, the SQLite PRAGMA event listener is not registered. PostgreSQL enforces FK constraints natively and does not use WAL mode.

IS_SQLITE = settings.database_url.startswith("sqlite")

engine = create_async_engine(settings.database_url, **engine_options(settings.database_url, settings))

For PostgreSQL engine_options() sets pool_pre_ping=True, pool_size (DB_POOL_SIZE, default 5) and max_overflow (DB_MAX_OVERFLOW, default 10); SQLite gets only echo=settings.debug.

Use docker-compose.postgres.yml to deploy with PostgreSQL, or ./podman.sh with DATABASE_URL set to a postgresql+asyncpg:// URL (see Podman Compose: Optional PostgreSQL container).

Naive-Datetime Storage Convention

Every DateTime column in this schema is timezone-naive, and every write strips tzinfo before assignment: datetime.now(UTC).replace(tzinfo=None), interpreted as UTC by convention (never local time).

• SQLite accepts a timezone-aware datetime bound against a naive column silently.
• asyncpg on PostgreSQL rejects it (can't subtract offset-naive and offset-aware datetimes or a comparable DataError).
• Reads follow the mirror convention: code that needs an aware value calls .replace(tzinfo=UTC) on the way out (see ApiToken.is_expired(), UserInvite.is_valid()) rather than the column itself carrying timezone information.

Caution

Any new code that writes a datetime to a mapped column must follow this convention; SQLite alone will not catch a violation.

Client-Supplied Datetimes

This applies to client-supplied datetimes too, not just server-generated ones.

• A generic for field, value in data.model_dump(exclude_unset=True).items(): setattr(model, field, value) update loop (the pattern most *_service.py update functions use) passes through whatever the client sent verbatim.
• If an *Update schema has a bare datetime field, a frontend Date.toISOString()/similar aware timestamp reaches setattr unmodified.
• HoldingUpdate.current_price_updated_at is one such field (investment_service.py’s update_holding).

Normalize any datetime value with tzinfo is not None via .astimezone(UTC).replace(tzinfo=None) before setattr, the same way refresh_all_holding_prices()’s bulk write already does for its own server-generated timestamp.

DB Session Pattern

The get_db dependency yields an AsyncSession per request with the following behavior:

async def get_db(request: Request) -> AsyncGenerator[AsyncSession]:
    async with async_session_factory() as session:
        event.listen(session.sync_session, "after_flush", mark_session_flushed)
        try:
            yield session
            assert_get_is_read_only(request, session)
            await session.commit()
        except Exception:
            await session.rollback()
            raise

Request method	Commit	Rollback on exception
All methods	Committed	Yes

Note

A previous optimization skipped commit for GET requests to avoid unnecessary write transactions on read-only requests. This was removed because it caused silent data loss for GET endpoints that write to the database (OIDC callback user upsert, GET logout token revocation).

The test suite’s override_get_db always committed, masking the production bug. The cost of an empty commit on read-only GET requests is negligible (SQLite WAL no-op, PostgreSQL no-op).

A GET request outside GET_WRITE_ALLOWLIST (app/database.py) that produces an ORM write fails with WriteOnGetError; a new GET that must write has to be added to the allowlist.

The session factory uses expire_on_commit=False so objects remain usable after commit.

CI Migration Testing

The CI pipeline tests migrations by running both upgrade and downgrade against a temporary SQLite database:

# Upgrade to latest
alembic upgrade head

# Downgrade to base (verifies all down_revisions)
alembic downgrade base

This ensures every migration has a working downgrade path and the schema can be recreated from scratch.

Related Documents

• Backend Architecture — Routers, services, lifespan tasks
• Backend Development — Local setup, testing, linting
• PostgreSQL Deployment — Docker Compose with PostgreSQL