walletdocs

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/.

TableOwnerWhat it holds
usersn/aA person with their own private wallet.
passkeysuser (user_id)WebAuthn credentials (passkeys).
sessionsuser (user_id)Login sessions.
auth_challengesuser (user_id)Short-lived WebAuthn challenges and one-time codes (Telegram linking).
accountsuser (user_id)Anything that holds or owes money: bank accounts, brokerage, property, loans.
balance_snapshotsuser (via account)—
holdingsuser (user_id)A position inside an account: stock, ETF, fund, bond, crypto, commodity… When an account has holdings, its balance is their market value.
broker_connectionsuser (user_id)A read-only link to a brokerage (Trading 212…).
broker_activityuser (user_id)What happened at a broker, imported from its history: fills, dividends, deposits, withdrawals, interest, fees.
pricessharedDaily closing prices per market symbol (shared by all users).
fx_ratessharedExchange rates as units of `currency` per 1 USD, per day (shared by all users).
categoriesuser (user_id)—
transactionsuser (user_id)—
category_rulesuser (user_id)"Description contains <pattern>" → category.
budgetsuser (user_id)Monthly envelope per category, in its own currency (rent in EUR, groceries in AED).
remindersuser (user_id)—
company_financialsuser (user_id)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).
goalsuser (user_id)Something to save for: an amount by a date (a flat deposit, a car), or a level of net worth.
account_sharesuser (user_id)An account shared with another member of this wallet (a partner): it shows in their accounts and net worth at `sharePct` of it.
changesuser (user_id)Who changed what, when: one row per change made through the app, the assistant or the API.
presenceuser (user_id)Time spent in a country (a trip, a stay), for the tax-residency day count.
tax_inputsuser (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.
transaction_splitsuser (user_id)One payment over several categories (a supermarket receipt that is half groceries, half household).
recurringuser (user_id)Something that happens on a schedule: salary, rent, a subscription.
simulationsuser (user_id)—
agent_conversationsuser (user_id)—
agent_messagesuser (via conversation)Raw API message history (content blocks incl.
uploadsuser (user_id)Files dropped into the assistant (bank CSV / PDF statements, screenshots).
settingsuser (user_id)Per-user preferences (base currency, locale, time zone).

People & auth

users

A person with their own private wallet. The first user is the owner.

ColumnTypeDetails
idPKtextnot null
nametextnot null
roletextnot nullOne of owner, memberdefault 'member'
telegram_chat_idintegernullableTelegram chat linked to this user (reminders + bot commands).unique
created_atinteger · ms epochnot nulldefault (unixepoch() * 1000)

passkeys

WebAuthn credentials (passkeys). A user can have several (phone, laptop…).

ColumnTypeDetails
idPKtextnot nullCredential ID, base64url.
user_idtextnot nullreferences users.id (on delete cascade)
public_keyblobnot null
counterintegernot nulldefault 0
transportstext · JSONnullableJSON array of strings
device_typetextnullable
backed_upinteger · booleannot nulldefault false
nametextnullableHuman label, e.g. "iPhone" or "MacBook".
created_atinteger · ms epochnot nulldefault (unixepoch() * 1000)
last_used_atinteger · ms epochnullable
  • Index passkeys_user on (user_id)

sessions

Login sessions. `id` is the SHA-256 of the cookie token, never the token itself.

ColumnTypeDetails
idPKtextnot null
user_idtextnot nullreferences users.id (on delete cascade)
expires_atinteger · ms epochnot null
user_agenttextnullable
created_atinteger · ms epochnot nulldefault (unixepoch() * 1000)
  • Index sessions_user on (user_id)

auth_challenges

Short-lived WebAuthn challenges and one-time codes (Telegram linking).

ColumnTypeDetails
idPKtextnot null
kindtextnot nullOne of register, login, add_passkey, telegram_link, magic_link
challengetextnot null
user_idtextnullablereferences users.id (on delete cascade)
pending_nametextnullableName chosen at sign-up, kept until the passkey is verified.
expires_atinteger · 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.

ColumnTypeDetails
idPKintegernot nullauto-increment
user_idtextnot nullreferences users.id (on delete cascade)
nametextnot null
institutiontextnullable
typetextnot nullOne 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_classtextnot nullOne of cash, investments, retirement, real_estate, crypto, commodities, business, other, liabilities
currencytextnot nullISO 4217 code (or a crypto/metal code like BTC, XAU).default 'USD'
ownership_pctrealnot nullShare of the account you own (e.g. 50 for a property bought as a couple).default 100
include_in_net_worthinteger · booleannot nulldefault true
archived_attextnullable
linked_account_idintegernullableA mortgage can point at the property it finances, to show equity.
loan_paramstext · JSONnullableWhen set, the balance of a loan is derived from its amortization schedule.JSON LoanParams: principal, annualRatePct, durationMonths, startDate, insuranceRatePct?
gratuity_paramstext · JSONnullableA job: the balance is the end-of-service gratuity accrued under its country's law.GratuityParams
company_paramstext · JSONnullableA company you own (type "company"): how it is valued, where it is taxed.CompanyParams
company_idintegernullableAn account that belongs to a company (its bank, brokerage, loan): counted through the company.references accounts.id (on delete set null)
countrytextnullableISO country the account is in (a French flat: FR). Drives where its income is taxed.
notestextnullable
created_atinteger · ms epochnot nulldefault (unixepoch() * 1000)
  • Index accounts_user on (user_id)

balance_snapshots

ColumnTypeDetails
idPKintegernot nullauto-increment
account_idintegernot nullreferences accounts.id (on delete cascade)
datetextnot nullISO date (YYYY-MM-DD). One snapshot per account per day.
balance_centsintegernot nullIn the account's currency.
notetextnullable
sourcetextnot nulldefault 'manual'
created_atinteger · ms epochnot nulldefault (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.

ColumnTypeDetails
idPKintegernot nullauto-increment
user_idtextnot nullreferences users.id (on delete cascade)
account_idintegernot nullreferences accounts.id (on delete cascade)
symboltextnullableMarket symbol (Yahoo Finance style: AAPL, CW8.PA, BTC-USD, GC=F). Null for manual assets.
nametextnot null
asset_typetextnot nullOne of stock, etf, fund, bond, crypto, commodity, cash, other
quantityrealnot null
currencytextnot nullCurrency the price is quoted in.default 'USD'
cost_basisrealnullableAverage cost per unit, in `currency`.
purchase_datetextnullableWhen it was bought (on average): its cost in your currency uses that day's exchange rate.
manual_pricerealnullablePrice per unit used when there is no market symbol (or to override it).
connection_idintegernullableSet when a broker connection owns this position: the next sync overwrites it.references broker_connections.id (on delete set null)
external_idtextnullableThe broker's own id for the position (Trading 212 ticker), unique per connection.
created_atinteger · ms epochnot nulldefault (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.

ColumnTypeDetails
idPKintegernot nullauto-increment
user_idtextnot nullreferences users.id (on delete cascade)
providertextnot nullConnector id from `src/server/connectors` (e.g. "trading212").
environmenttextnot nullConnector-specific environment, e.g. "live" or "demo".
account_idintegernot nullWallet account the broker's positions are written into.references accounts.id (on delete cascade)
external_account_idtextnullableThe broker's id for the account, to refuse connecting the same one twice.
credentialstextnot nullEncrypted JSON of the connector's credential fields.
last_sync_atinteger · ms epochnullable
last_sync_okinteger · booleannullable
last_errortextnullable
history_synced_atinteger · ms epochnullableLast time past trades, dividends and cash movements were imported.
history_statustextnullablenull (never) · "running" · "ok" · "incomplete" (more next run) · an error to show.
created_atinteger · ms epochnot nulldefault (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.

ColumnTypeDetails
idPKintegernot nullauto-increment
user_idtextnot nullreferences users.id (on delete cascade)
account_idintegernot nullreferences accounts.id (on delete cascade)
connection_idintegernullablereferences broker_connections.id (on delete set null)
kindtextnot nullOne of trade, dividend, deposit, withdrawal, interest, fee, other
external_idtextnot nullThe broker's id for the event (fill id, reference).
attextnot nullISO timestamp as the broker gave it.
datetextnot nullDay in the user's time zone (YYYY-MM-DD).
tickertextnullable
symboltextnullable
nametextnullable
quantityrealnullableTrades: units, negative for a sale.
pricerealnullableTrades: price per unit, in `priceCurrency` (major unit).
price_currencytextnullable
cash_centsintegernot nullSigned effect on the account's cash, in the account currency (a buy is negative).
currencytextnot null
realized_centsintegernullableRealised profit or loss of a sale, in the account currency.
created_atinteger · ms epochnot nulldefault (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).

ColumnTypeDetails
symboltextnot null
datetextnot null
closerealnot null
currencytextnot null
fetched_atinteger · ms epochnot null
  • Primary key (symbol, date)

fx_rates

Exchange rates as units of `currency` per 1 USD, per day (shared by all users).

ColumnTypeDetails
datetextnot null
currencytextnot null
per_usdrealnot null
  • Primary key (currency, date)

categories

ColumnTypeDetails
idPKintegernot nullauto-increment
user_idtextnot nullreferences users.id (on delete cascade)
nametextnot null
kindtextnot nullOne of income, expense, transfer
icontextnullable
sort_orderintegernot nulldefault 0
created_atinteger · ms epochnot nulldefault (unixepoch() * 1000)
  • Unique index categories_user_name on (user_id, name)

transactions

ColumnTypeDetails
idPKintegernot nullauto-increment
user_idtextnot nullreferences users.id (on delete cascade)
account_idintegernullablereferences accounts.id (on delete set null)
datetextnot null
amount_centsintegernot nullSigned, in `currency`: negative = money out, positive = money in.
currencytextnot nulldefault 'USD'
descriptiontextnot null
category_idintegernullablereferences categories.id (on delete set null)
notestextnullable
import_hashtextnullableDedupe key for imported rows (unique per user).
payeetextnullableDisplay name (from a rule or the user); the bank label in `description` is kept for matching.
tagstext · JSONnullableFree tags, e.g. ["trip-japan", "reimbursable"].JSON array of strings
recurring_idintegernullableThe recurring item (salary, rent, a subscription) this payment fulfils.references recurring.id (on delete set null)
sourcetextnot nulldefault 'manual'
created_atinteger · ms epochnot nulldefault (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.

ColumnTypeDetails
idPKintegernot nullauto-increment
user_idtextnot nullreferences users.id (on delete cascade)
patterntextnot null
category_idintegernot nullreferences categories.id (on delete cascade)
match_typetextnot nullHow `pattern` is compared with the bank label.One of contains, starts, exactdefault 'contains'
account_idintegernullableConditions (all optional): only this account, only money in/out, an amount range (absolute, in the row's currency).references accounts.id (on delete cascade)
directiontextnullableOne of in, out
min_amount_centsintegernullable
max_amount_centsintegernullable
rename_totextnullableExtra actions: show the payee under this name, add this tag.
add_tagtextnullable
created_atinteger · ms epochnot nulldefault (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.

ColumnTypeDetails
idPKintegernot nullauto-increment
user_idtextnot nullreferences users.id (on delete cascade)
category_idintegernot nullunique · references categories.id (on delete cascade)
amount_centsintegernot null
currencytextnullable
created_atinteger · ms epochnot nulldefault (unixepoch() * 1000)

reminders

ColumnTypeDetails
idPKintegernot nullauto-increment
user_idtextnot nullreferences users.id (on delete cascade)
titletextnot null
messagetextnullable
kindtextnot nullOne of custom, balance_update, statement, monthly_reportdefault 'custom'
account_idintegernullablereferences accounts.id (on delete set null)
frequencytextnot nullOne of once, weekly, monthly, quarterly, yearly
day_of_monthintegernullable
day_of_weekintegernullable1 = Monday … 7 = Sunday
month_of_yearintegernullable
datetextnullableFor one-off reminders (YYYY-MM-DD).
time_of_daytextnot nulldefault '09:00'
nag_every_hoursintegernot nullRe-send every N hours until acknowledged. 0 = send once.default 0
enabledinteger · booleannot nulldefault true
next_run_atinteger · ms epochnullable
last_sent_atinteger · ms epochnullable
next_nag_atinteger · ms epochnullable
last_message_idintegernullable
acknowledged_atinteger · ms epochnullable
created_atinteger · ms epochnot nulldefault (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.

ColumnTypeDetails
idPKintegernot nullauto-increment
user_idtextnot nullreferences users.id (on delete cascade)
company_idintegernot nullreferences accounts.id (on delete cascade)
monthtextnot nullYYYY-MM
revenue_centsintegernot nullIn the company's currency, both positive.default 0
expenses_centsintegernot nulldefault 0
notetextnullable
  • 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.

ColumnTypeDetails
idPKintegernot nullauto-increment
user_idtextnot nullreferences users.id (on delete cascade)
nametextnot null
icontextnullable
target_centsintegernot null
currencytextnot null
target_datetextnullable
account_idstext · JSONnot nullAccounts whose balances count towards it; empty = the whole net worth.number[]default '[]'
monthly_centsintegernullableWhat you plan to put in each month; empty = use your average savings.
archived_attextnullable
created_atinteger · ms epochnot nulldefault (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.

ColumnTypeDetails
account_idintegernot nullreferences accounts.id (on delete cascade)
user_idtextnot nullreferences users.id (on delete cascade)
roletextnot nullOne of view, editdefault 'view'
share_pctrealnot nullTheir share of the account for their net worth (0–100).default 50
created_atinteger · ms epochnot nulldefault (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.

ColumnTypeDetails
idPKintegernot nullauto-increment
user_idtextnot nullreferences users.id (on delete cascade)
actor_idtextnullableWho did it (a partner editing a shared account, or the user themselves).
atinteger · ms epochnot nulldefault (unixepoch() * 1000)
entitytextnot null
entity_idtextnullable
actiontextnot null
summarytextnot 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.

ColumnTypeDetails
idPKintegernot nullauto-increment
user_idtextnot nullreferences users.id (on delete cascade)
countrytextnot nullISO 3166 alpha-2
from_datetextnot null
to_datetextnot nullInclusive.
notetextnullable
  • Index presence_user on (user_id)

tax_inputs

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.

ColumnTypeDetails
user_idtextnot nullreferences users.id (on delete cascade)
yearintegernot null
keytextnot null
valuetextnot 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.

ColumnTypeDetails
idPKintegernot nullauto-increment
transaction_idintegernot nullreferences transactions.id (on delete cascade)
user_idtextnot nullreferences users.id (on delete cascade)
category_idintegernullablereferences categories.id (on delete set null)
amount_centsintegernot nullSigned like the transaction, in its currency.
notetextnullable
  • 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.

ColumnTypeDetails
idPKintegernot nullauto-increment
user_idtextnot nullreferences users.id (on delete cascade)
nametextnot null
patterntextnot nullLowercase text the bank label contains ("netflix", "salary acme").
account_idintegernullablereferences accounts.id (on delete set null)
category_idintegernullablereferences categories.id (on delete set null)
amount_centsintegernot nullExpected amount, signed like a transaction (negative = money out), in `currency`.
currencytextnot null
cadencetextnot nullOne of weekly, biweekly, monthly, quarterly, yearly
next_datetextnot nullThe next date it is expected (YYYY-MM-DD).
tolerance_pctrealnot nullHow far the amount may differ and still be this payment (%).default 10
window_daysintegernot nullHow many days early or late it may arrive.default 5
statustextnot nullOne of active, suggested, paused, dismisseddefault 'active'
sourcetextnot nullOne of detected, manualdefault 'manual'
last_matched_datetextnullable
last_amount_centsintegernullable
last_change_pctrealnullableLast change of amount between two matched payments (%), e.g. a price rise.
created_atinteger · ms epochnot nulldefault (unixepoch() * 1000)
  • Index recurring_user on (user_id)

simulations

ColumnTypeDetails
idPKintegernot nullauto-increment
user_idtextnot nullreferences users.id (on delete cascade)
nametextnot null
typetextnot nullOne of mortgage, buy_vs_rent, projection
paramstext · JSONnot nullJSON object
created_atinteger · ms epochnot nulldefault (unixepoch() * 1000)

Assistant

agent_conversations

ColumnTypeDetails
idPKtextnot null
user_idtextnot nullreferences users.id (on delete cascade)
titletextnot null
channeltextnot nulldefault 'web'
providertextnot nullWhich agent backend owns this thread (anthropic | claude-code | codex).default 'anthropic'
external_session_idtextnullableSession id of a local CLI agent, used to resume its thread.
created_atinteger · ms epochnot nulldefault (unixepoch() * 1000)
updated_atinteger · ms epochnot nulldefault (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.

ColumnTypeDetails
idPKintegernot nullauto-increment
conversation_idtextnot nullreferences agent_conversations.id (on delete cascade)
roletextnot nullOne of user, assistant, system
contenttext · JSONnot null
created_atinteger · ms epochnot nulldefault (unixepoch() * 1000)
  • Index agent_messages_conversation on (conversation_id)

uploads

Files dropped into the assistant (bank CSV / PDF statements, screenshots).

ColumnTypeDetails
idPKtextnot null
user_idtextnot nullreferences users.id (on delete cascade)
filenametextnot null
mime_typetextnot null
sizeintegernot null
datablobnot null
created_atinteger · ms epochnot nulldefault (unixepoch() * 1000)
  • Index uploads_user on (user_id)

settings

Per-user preferences (base currency, locale, time zone).

ColumnTypeDetails
user_idtextnot nullreferences users.id (on delete cascade)
keytextnot null
valuetextnot null
  • Primary key (user_id, key)