Reference
Data model
Every table and column in the SQLite database, with types, nullability, defaults, foreign keys and indexes. Generated from the drizzle schema at build time.
The whole wallet is one SQLite file (./data/wallet.db by default, WALLET_DB_PATH to move it). The schema is defined with drizzle in src/server/db/schema.ts. Migrations in drizzle/ are applied automatically when a process opens the database, with WAL journaling, foreign keys on and a 5 s busy timeout.
Conventions#
- Money is integer cents in the row's own currency (
balance_cents, amount_cents), always next to a currency column (ISO 4217, or a crypto/metal code such as BTC or XAU). Conversion to the base currency happens at read time (Multi-currency internals).
- Dates are ISO text:
YYYY-MM-DD for days, YYYY-MM for months. Timestamps (created_at, expires_at…) are integer milliseconds since the epoch.
- Ownership: every user-owned table has a
user_id that references users.id with ON DELETE CASCADE, and every service query filters on it. Snapshots and agent messages are owned through their account or conversation. prices and fx_rates are shared market data.
- Liabilities store the amount owed as a positive balance; net worth subtracts them.
- Imports are idempotent:
transactions.import_hash is unique per user, so re-importing the same statement inserts nothing.
agent_messages is append-only (see Agent internals).
After editing the schema, run npm run db:generate and commit the generated files in drizzle/.
Tables#
29 tables, generated from src/server/db/schema.ts with drizzle's getTableConfig. Migrations live in drizzle/.
People & auth#
users#
A person with their own private wallet. The first user is the owner.
| Column | Type | Details |
|---|
idPK | textnot null | |
name | textnot null | |
role | textnot null | One of owner, memberdefault 'member' |
telegram_chat_id | integernullable | Telegram chat linked to this user (reminders + bot commands).unique |
created_at | integer · ms epochnot null | default (unixepoch() * 1000) |
passkeys#
WebAuthn credentials (passkeys). A user can have several (phone, laptop…).
| Column | Type | Details |
|---|
idPK | textnot null | Credential ID, base64url. |
user_id | textnot null | references users.id (on delete cascade) |
public_key | blobnot null | |
counter | integernot null | default 0 |
transports | text · JSONnullable | JSON array of strings |
device_type | textnullable | |
backed_up | integer · booleannot null | default false |
name | textnullable | Human label, e.g. "iPhone" or "MacBook". |
created_at | integer · ms epochnot null | default (unixepoch() * 1000) |
last_used_at | integer · ms epochnullable | |
- Index
passkeys_user on (user_id)
sessions#
Login sessions. `id` is the SHA-256 of the cookie token, never the token itself.
| Column | Type | Details |
|---|
idPK | textnot null | |
user_id | textnot null | references users.id (on delete cascade) |
expires_at | integer · ms epochnot null | |
user_agent | textnullable | |
created_at | integer · ms epochnot null | default (unixepoch() * 1000) |
- Index
sessions_user on (user_id)
auth_challenges#
Short-lived WebAuthn challenges and one-time codes (Telegram linking).
| Column | Type | Details |
|---|
idPK | textnot null | |
kind | textnot null | One of register, login, add_passkey, telegram_link, magic_link |
challenge | textnot null | |
user_id | textnullable | references users.id (on delete cascade) |
pending_name | textnullable | Name chosen at sign-up, kept until the passkey is verified. |
expires_at | integer · ms epochnot null | |
Money#
accounts#
Anything that holds or owes money: bank accounts, brokerage, property, loans. Balances are in the account's own currency. Liabilities store the amount owed as a positive balance.
| Column | Type | Details |
|---|
idPK | integernot null | auto-increment |
user_id | textnot null | references users.id (on delete cascade) |
name | textnot null | |
institution | textnullable | |
type | textnot null | One of checking, savings, brokerage, pea, life_insurance, employee_savings, retirement, end_of_service, crypto, precious_metals, real_estate, company, vehicle, other_asset, mortgage, loan, credit_card, other_liability |
asset_class | textnot null | One of cash, investments, retirement, real_estate, crypto, commodities, business, other, liabilities |
currency | textnot null | ISO 4217 code (or a crypto/metal code like BTC, XAU).default 'USD' |
ownership_pct | realnot null | Share of the account you own (e.g. 50 for a property bought as a couple).default 100 |
include_in_net_worth | integer · booleannot null | default true |
archived_at | textnullable | |
linked_account_id | integernullable | A mortgage can point at the property it finances, to show equity. |
loan_params | text · JSONnullable | When set, the balance of a loan is derived from its amortization schedule.JSON LoanParams: principal, annualRatePct, durationMonths, startDate, insuranceRatePct? |
gratuity_params | text · JSONnullable | A job: the balance is the end-of-service gratuity accrued under its country's law.GratuityParams |
company_params | text · JSONnullable | A company you own (type "company"): how it is valued, where it is taxed.CompanyParams |
company_id | integernullable | An account that belongs to a company (its bank, brokerage, loan): counted through the company.references accounts.id (on delete set null) |
country | textnullable | ISO country the account is in (a French flat: FR). Drives where its income is taxed. |
notes | textnullable | |
created_at | integer · ms epochnot null | default (unixepoch() * 1000) |
- Index
accounts_user on (user_id)
balance_snapshots#
| Column | Type | Details |
|---|
idPK | integernot null | auto-increment |
account_id | integernot null | references accounts.id (on delete cascade) |
date | textnot null | ISO date (YYYY-MM-DD). One snapshot per account per day. |
balance_cents | integernot null | In the account's currency. |
note | textnullable | |
source | textnot null | default 'manual' |
created_at | integer · ms epochnot null | default (unixepoch() * 1000) |
- Unique index
balance_snapshots_account_date on (account_id, date)
holdings#
A position inside an account: stock, ETF, fund, bond, crypto, commodity… When an account has holdings, its balance is their market value.
| Column | Type | Details |
|---|
idPK | integernot null | auto-increment |
user_id | textnot null | references users.id (on delete cascade) |
account_id | integernot null | references accounts.id (on delete cascade) |
symbol | textnullable | Market symbol (Yahoo Finance style: AAPL, CW8.PA, BTC-USD, GC=F). Null for manual assets. |
name | textnot null | |
asset_type | textnot null | One of stock, etf, fund, bond, crypto, commodity, cash, other |
quantity | realnot null | |
currency | textnot null | Currency the price is quoted in.default 'USD' |
cost_basis | realnullable | Average cost per unit, in `currency`. |
purchase_date | textnullable | When it was bought (on average): its cost in your currency uses that day's exchange rate. |
manual_price | realnullable | Price per unit used when there is no market symbol (or to override it). |
connection_id | integernullable | Set when a broker connection owns this position: the next sync overwrites it.references broker_connections.id (on delete set null) |
external_id | textnullable | The broker's own id for the position (Trading 212 ticker), unique per connection. |
created_at | integer · ms epochnot null | default (unixepoch() * 1000) |
- Index
holdings_account on (account_id) - Index
holdings_user on (user_id) - Unique index
holdings_connection_external on (connection_id, external_id)
broker_connections#
A read-only link to a brokerage (Trading 212…). Each sync replaces the positions and cash of `accountId` with what the broker reports. Credentials are encrypted at rest (`src/server/secrets.ts`) and never leave the server: this table is deliberately absent from exports and the agent's SQL sandbox.
| Column | Type | Details |
|---|
idPK | integernot null | auto-increment |
user_id | textnot null | references users.id (on delete cascade) |
provider | textnot null | Connector id from `src/server/connectors` (e.g. "trading212"). |
environment | textnot null | Connector-specific environment, e.g. "live" or "demo". |
account_id | integernot null | Wallet account the broker's positions are written into.references accounts.id (on delete cascade) |
external_account_id | textnullable | The broker's id for the account, to refuse connecting the same one twice. |
credentials | textnot null | Encrypted JSON of the connector's credential fields. |
last_sync_at | integer · ms epochnullable | |
last_sync_ok | integer · booleannullable | |
last_error | textnullable | |
history_synced_at | integer · ms epochnullable | Last time past trades, dividends and cash movements were imported. |
history_status | textnullable | null (never) · "running" · "ok" · "incomplete" (more next run) · an error to show. |
created_at | integer · ms epochnot null | default (unixepoch() * 1000) |
- Index
broker_connections_user on (user_id) - Unique index
broker_connections_account on (account_id)
broker_activity#
What happened at a broker, imported from its history: fills, dividends, deposits, withdrawals, interest, fees. Rebuilds the account's past value (positions and cash on every day) and its money-in, income and realised gains.
| Column | Type | Details |
|---|
idPK | integernot null | auto-increment |
user_id | textnot null | references users.id (on delete cascade) |
account_id | integernot null | references accounts.id (on delete cascade) |
connection_id | integernullable | references broker_connections.id (on delete set null) |
kind | textnot null | One of trade, dividend, deposit, withdrawal, interest, fee, other |
external_id | textnot null | The broker's id for the event (fill id, reference). |
at | textnot null | ISO timestamp as the broker gave it. |
date | textnot null | Day in the user's time zone (YYYY-MM-DD). |
ticker | textnullable | |
symbol | textnullable | |
name | textnullable | |
quantity | realnullable | Trades: units, negative for a sale. |
price | realnullable | Trades: price per unit, in `priceCurrency` (major unit). |
price_currency | textnullable | |
cash_cents | integernot null | Signed effect on the account's cash, in the account currency (a buy is negative). |
currency | textnot null | |
realized_cents | integernullable | Realised profit or loss of a sale, in the account currency. |
created_at | integer · ms epochnot null | default (unixepoch() * 1000) |
- Unique index
broker_activity_external on (account_id, kind, external_id) - Index
broker_activity_account_date on (account_id, date)
prices#
Daily closing prices per market symbol (shared by all users).
| Column | Type | Details |
|---|
symbol | textnot null | |
date | textnot null | |
close | realnot null | |
currency | textnot null | |
fetched_at | integer · ms epochnot null | |
- Primary key
(symbol, date)
fx_rates#
Exchange rates as units of `currency` per 1 USD, per day (shared by all users).
| Column | Type | Details |
|---|
date | textnot null | |
currency | textnot null | |
per_usd | realnot null | |
- Primary key
(currency, date)
categories#
| Column | Type | Details |
|---|
idPK | integernot null | auto-increment |
user_id | textnot null | references users.id (on delete cascade) |
name | textnot null | |
kind | textnot null | One of income, expense, transfer |
icon | textnullable | |
sort_order | integernot null | default 0 |
created_at | integer · ms epochnot null | default (unixepoch() * 1000) |
- Unique index
categories_user_name on (user_id, name)
transactions#
| Column | Type | Details |
|---|
idPK | integernot null | auto-increment |
user_id | textnot null | references users.id (on delete cascade) |
account_id | integernullable | references accounts.id (on delete set null) |
date | textnot null | |
amount_cents | integernot null | Signed, in `currency`: negative = money out, positive = money in. |
currency | textnot null | default 'USD' |
description | textnot null | |
category_id | integernullable | references categories.id (on delete set null) |
notes | textnullable | |
import_hash | textnullable | Dedupe key for imported rows (unique per user). |
payee | textnullable | Display name (from a rule or the user); the bank label in `description` is kept for matching. |
tags | text · JSONnullable | Free tags, e.g. ["trip-japan", "reimbursable"].JSON array of strings |
recurring_id | integernullable | The recurring item (salary, rent, a subscription) this payment fulfils.references recurring.id (on delete set null) |
source | textnot null | default 'manual' |
created_at | integer · ms epochnot null | default (unixepoch() * 1000) |
- Index
transactions_user_date on (user_id, date) - Index
transactions_category on (category_id) - Unique index
transactions_user_import_hash on (user_id, import_hash)
category_rules#
"Description contains <pattern>" → category. Applied on import and on demand.
| Column | Type | Details |
|---|
idPK | integernot null | auto-increment |
user_id | textnot null | references users.id (on delete cascade) |
pattern | textnot null | |
category_id | integernot null | references categories.id (on delete cascade) |
match_type | textnot null | How `pattern` is compared with the bank label.One of contains, starts, exactdefault 'contains' |
account_id | integernullable | Conditions (all optional): only this account, only money in/out, an amount range (absolute, in the row's currency).references accounts.id (on delete cascade) |
direction | textnullable | One of in, out |
min_amount_cents | integernullable | |
max_amount_cents | integernullable | |
rename_to | textnullable | Extra actions: show the payee under this name, add this tag. |
add_tag | textnullable | |
created_at | integer · ms epochnot null | default (unixepoch() * 1000) |
- Index
category_rules_user on (user_id)
budgets#
Monthly envelope per category, in its own currency (rent in EUR, groceries in AED). NULL currency = the user's base currency, and follows it if the base changes.
| Column | Type | Details |
|---|
idPK | integernot null | auto-increment |
user_id | textnot null | references users.id (on delete cascade) |
category_id | integernot null | unique · references categories.id (on delete cascade) |
amount_cents | integernot null | |
currency | textnullable | |
created_at | integer · ms epochnot null | default (unixepoch() * 1000) |
reminders#
| Column | Type | Details |
|---|
idPK | integernot null | auto-increment |
user_id | textnot null | references users.id (on delete cascade) |
title | textnot null | |
message | textnullable | |
kind | textnot null | One of custom, balance_update, statement, monthly_reportdefault 'custom' |
account_id | integernullable | references accounts.id (on delete set null) |
frequency | textnot null | One of once, weekly, monthly, quarterly, yearly |
day_of_month | integernullable | |
day_of_week | integernullable | 1 = Monday … 7 = Sunday |
month_of_year | integernullable | |
date | textnullable | For one-off reminders (YYYY-MM-DD). |
time_of_day | textnot null | default '09:00' |
nag_every_hours | integernot null | Re-send every N hours until acknowledged. 0 = send once.default 0 |
enabled | integer · booleannot null | default true |
next_run_at | integer · ms epochnullable | |
last_sent_at | integer · ms epochnullable | |
next_nag_at | integer · ms epochnullable | |
last_message_id | integernullable | |
acknowledged_at | integer · ms epochnullable | |
created_at | integer · ms epochnot null | default (unixepoch() * 1000) |
- Index
reminders_user on (user_id)
company_financials#
A company's revenue and expenses for one month, entered by hand — for when its bank isn't imported, or for figures that never touch a bank (an accountant's numbers). Added to whatever its accounts' transactions show.
| Column | Type | Details |
|---|
idPK | integernot null | auto-increment |
user_id | textnot null | references users.id (on delete cascade) |
company_id | integernot null | references accounts.id (on delete cascade) |
month | textnot null | YYYY-MM |
revenue_cents | integernot null | In the company's currency, both positive.default 0 |
expenses_cents | integernot null | default 0 |
note | textnullable | |
- Unique index
company_financials_month on (company_id, month)
goals#
Something to save for: an amount by a date (a flat deposit, a car), or a level of net worth. Progress is what the linked accounts hold (or the whole net worth when none are linked), in the goal's currency.
| Column | Type | Details |
|---|
idPK | integernot null | auto-increment |
user_id | textnot null | references users.id (on delete cascade) |
name | textnot null | |
icon | textnullable | |
target_cents | integernot null | |
currency | textnot null | |
target_date | textnullable | |
account_ids | text · JSONnot null | Accounts whose balances count towards it; empty = the whole net worth.number[]default '[]' |
monthly_cents | integernullable | What you plan to put in each month; empty = use your average savings. |
archived_at | textnullable | |
created_at | integer · ms epochnot null | default (unixepoch() * 1000) |
- Index
goals_user on (user_id)
account_shares#
An account shared with another member of this wallet (a partner): it shows in their accounts and net worth at `sharePct` of it. "edit" lets them record balances; its terms, positions and history stay the owner's to change.
| Column | Type | Details |
|---|
account_id | integernot null | references accounts.id (on delete cascade) |
user_id | textnot null | references users.id (on delete cascade) |
role | textnot null | One of view, editdefault 'view' |
share_pct | realnot null | Their share of the account for their net worth (0–100).default 50 |
created_at | integer · ms epochnot null | default (unixepoch() * 1000) |
- Primary key
(account_id, user_id) - Index
account_shares_user on (user_id)
changes#
Who changed what, when: one row per change made through the app, the assistant or the API.
| Column | Type | Details |
|---|
idPK | integernot null | auto-increment |
user_id | textnot null | references users.id (on delete cascade) |
actor_id | textnullable | Who did it (a partner editing a shared account, or the user themselves). |
at | integer · ms epochnot null | default (unixepoch() * 1000) |
entity | textnot null | |
entity_id | textnullable | |
action | textnot null | |
summary | textnot null | |
- Index
changes_user_at on (user_id, at)
presence#
Time spent in a country (a trip, a stay), for the tax-residency day count.
| Column | Type | Details |
|---|
idPK | integernot null | auto-increment |
user_id | textnot null | references users.id (on delete cascade) |
country | textnot null | ISO 3166 alpha-2 |
from_date | textnot null | |
to_date | textnot null | Inclusive. |
note | textnullable | |
- Index
presence_user on (user_id)
Answers the tax view needs and can't infer, per tax year (year 0 = every year): residence, French family parts, rental regime, a figure to use instead of the computed one. Keys are documented in services/tax.ts.
| Column | Type | Details |
|---|
user_id | textnot null | references users.id (on delete cascade) |
year | integernot null | |
key | textnot null | |
value | textnot null | |
- Primary key
(user_id, year, key)
transaction_splits#
One payment over several categories (a supermarket receipt that is half groceries, half household). Splits sum to the transaction's amount; budgets and cash flow read the splits instead of the transaction's own category.
| Column | Type | Details |
|---|
idPK | integernot null | auto-increment |
transaction_id | integernot null | references transactions.id (on delete cascade) |
user_id | textnot null | references users.id (on delete cascade) |
category_id | integernullable | references categories.id (on delete set null) |
amount_cents | integernot null | Signed like the transaction, in its currency. |
note | textnullable | |
- Index
transaction_splits_tx on (transaction_id)
recurring#
Something that happens on a schedule: salary, rent, a subscription. It is an EXPECTATION, never a transaction: statements bring the real payment, which is matched to it (transactions.recurringId), so nothing is ever counted twice. Detected from history (status "suggested" until accepted) or added by hand.
| Column | Type | Details |
|---|
idPK | integernot null | auto-increment |
user_id | textnot null | references users.id (on delete cascade) |
name | textnot null | |
pattern | textnot null | Lowercase text the bank label contains ("netflix", "salary acme"). |
account_id | integernullable | references accounts.id (on delete set null) |
category_id | integernullable | references categories.id (on delete set null) |
amount_cents | integernot null | Expected amount, signed like a transaction (negative = money out), in `currency`. |
currency | textnot null | |
cadence | textnot null | One of weekly, biweekly, monthly, quarterly, yearly |
next_date | textnot null | The next date it is expected (YYYY-MM-DD). |
tolerance_pct | realnot null | How far the amount may differ and still be this payment (%).default 10 |
window_days | integernot null | How many days early or late it may arrive.default 5 |
status | textnot null | One of active, suggested, paused, dismisseddefault 'active' |
source | textnot null | One of detected, manualdefault 'manual' |
last_matched_date | textnullable | |
last_amount_cents | integernullable | |
last_change_pct | realnullable | Last change of amount between two matched payments (%), e.g. a price rise. |
created_at | integer · ms epochnot null | default (unixepoch() * 1000) |
- Index
recurring_user on (user_id)
simulations#
| Column | Type | Details |
|---|
idPK | integernot null | auto-increment |
user_id | textnot null | references users.id (on delete cascade) |
name | textnot null | |
type | textnot null | One of mortgage, buy_vs_rent, projection |
params | text · JSONnot null | JSON object |
created_at | integer · ms epochnot null | default (unixepoch() * 1000) |
Assistant#
agent_conversations#
| Column | Type | Details |
|---|
idPK | textnot null | |
user_id | textnot null | references users.id (on delete cascade) |
title | textnot null | |
channel | textnot null | default 'web' |
provider | textnot null | Which agent backend owns this thread (anthropic | claude-code | codex).default 'anthropic' |
external_session_id | textnullable | Session id of a local CLI agent, used to resume its thread. |
created_at | integer · ms epochnot null | default (unixepoch() * 1000) |
updated_at | integer · ms epochnot null | default (unixepoch() * 1000) |
agent_messages#
Raw API message history (content blocks incl. thinking & tool use), stored append-only so every request replays the exact transcript the model produced.
| Column | Type | Details |
|---|
idPK | integernot null | auto-increment |
conversation_id | textnot null | references agent_conversations.id (on delete cascade) |
role | textnot null | One of user, assistant, system |
content | text · JSONnot null | |
created_at | integer · ms epochnot null | default (unixepoch() * 1000) |
- Index
agent_messages_conversation on (conversation_id)
uploads#
Files dropped into the assistant (bank CSV / PDF statements, screenshots).
| Column | Type | Details |
|---|
idPK | textnot null | |
user_id | textnot null | references users.id (on delete cascade) |
filename | textnot null | |
mime_type | textnot null | |
size | integernot null | |
data | blobnot null | |
created_at | integer · ms epochnot null | default (unixepoch() * 1000) |
- Index
uploads_user on (user_id)
settings#
Per-user preferences (base currency, locale, time zone).
| Column | Type | Details |
|---|
user_id | textnot null | references users.id (on delete cascade) |
key | textnot null | |
value | textnot null | |
- Primary key
(user_id, key)