Database Schema

Database Schema

The database schema is managed using Drizzle ORM and serves as the backbone for the multi-currency financial tracking system.

This document provides a complete reference for all tables, columns, and their specific purposes within the application logic.

---

1. Core: Users & Infrastructure

Foundation for multi-currency support and user management.

currencies

The master dictionary for globally supported currencies (e.g., USD, EUR, JPY).

Column	Type	Constraints	Description
code	char(3)	PK	The ISO 4217 currency code (e.g., ‘USD’). Used as the primary key and foreign key reference throughout the app.
symbol	text	NOT NULL	The currency symbol (e.g., ’$’, ’€’) for display purposes.
name	text	NOT NULL	Full name of the currency (e.g., ‘United States Dollar’).
decimals	int	NOT NULL	Number of decimal places commonly used (e.g., 2 for USD, 0 for JPY).

---

users

The central entity representing an application user.

Column	Type	Constraints	Description
id	bigint	PK, Identity	Unique identifier for the user. Referenced by essentially every data table.
name	text	NOT NULL	The user’s display name.
email	text	Unique, NOT NULL	User’s email address for login and notifications.
password_hash	text	NOT NULL	Securely hashed password (managed by Auth).
currency_preference	text	NOT NULL	Stores formatting preferences or locale settings for currency display.
main_currency_code	char(3)	FK -> currencies.code	Critical: The base currency for the user’s “Net Worth”. All aggregated stats are converted to this currency.
created_at	timestamp	Default Now	Timestamp of account creation.
updated_at	timestamp	Default Now	Timestamp of last profile update.

---

exchange_rates

Stores historical exchange rates between currency pairs. Used to calculate historical net worth and valuations.

Column	Type	Constraints	Description
id	bigint	PK, Identity	Unique ID for the rate entry.
source_currency	char(3)	FK -> currencies.code	The currency being converted from.
target_currency	char(3)	FK -> currencies.code	The currency being converted to.
rate	numeric(15,6)	NOT NULL	The conversion rate. High precision (6 decimals) to minimize rounding errors.
effective_date	timestamp	NOT NULL	The date/time for which this rate is valid. The system selects the closest date when performing historical conversions.

---

2. Institutions & Accounts

Where the money lives.

institutions

A normalized list of banks, exchanges, and financial entities.

Column	Type	Constraints	Description
id	bigint	PK, Identity	Unique ID.
name	text	NOT NULL	Name of the institution (e.g., “Chase”, “Coinbase”).
logo_url	text	-	URL to the institution’s brand icon for UI display.

---

accounts

Represents a specific holding of funds (Bank Account, Wallet, Investment Account) belonging to a user.

Column	Type	Constraints	Description
id	bigint	PK, Identity	Unique ID.
user_id	bigint	FK -> users.id	Owner of the account.
institution_id	bigint	FK -> institutions.id	The bank this account belongs to. Used to fetch logos/metadata.
name	text	NOT NULL	User’s custom name (e.g., “My Savings”).
type	text	NOT NULL	Type discriminator (e.g., savings, checking, credit, investment).
currency_code	text	NOT NULL	Base Currency: The native currency of this account. All transactions in this account are impactful in this currency.
current_balance	numeric(15,2)	NOT NULL	Cached current balance. Updated largely by transactions.
icon_color	text	-	UI customization: Hex code for the account card color.
is_included_in_net_worth	boolean	Default TRUE	If false, this account is hidden from total wealth calculations.
is_active	boolean	Default TRUE	Soft delete flag. If false, account is archived.

---

3. The Engine: Transactions

The most complex sector, handling double-entry logic, categorization, and transfers.

categories

Hierarchical tree for classifying spending and income.

Column	Type	Constraints	Description
id	bigint	PK, Identity	Unique ID.
user_id	bigint	FK -> users.id	Owner.
parent_id	bigint	FK -> categories.id	Self-reference. If set, this is a sub-category (e.g., “Groceries” is child of “Food”).
name	text	NOT NULL	Category name.
type	text	NOT NULL	income or expense. Determines which transaction types it relates to.
icon	text	-	Icon name/code.
icon_slug	text	-	Identifier for icon libraries.
color_hex	text	-	Color coding for charts.

---

merchants

Normalized entities for Payees (who you pay) and Payers (who pays you).

Column	Type	Constraints	Description
id	bigint	PK, Identity	Unique ID.
name	text	NOT NULL	Clean name (e.g., “Uber”, “Netflix”).
default_category_id	bigint	FK -> categories.id	Smart feature: When creating a transaction for this merchant, this category is auto-selected (e.g., Netflix -> Entertainment).
logo_url	text	-	URL for merchant brand logo.

---

transactions

The core ledger. Every inflow and outflow is recorded here.

Column	Type	Constraints	Description
id	bigint	PK, Identity	Unique ID.
user_id	bigint	FK -> users.id	Owner.
account_id	bigint	FK -> accounts.id	The account impacted by this transaction.
category_id	bigint	FK -> categories.id	Classification of the transaction.
merchant_id	bigint	FK -> merchants.id	”Who” the transaction interacts with. Cleaner than raw descriptions.
amount	numeric(15,2)	NOT NULL	The magnitude of the transaction.
currency_code	text	NOT NULL	Currency of the amount. Usually matches accounts.currency_code.
transaction_date	timestamptz	NOT NULL	When the transaction occurred.
type	text	NOT NULL	income, expense, or transfer.
status	text	NOT NULL	pending, cleared, reconciled.
notes	text	-	User comments.
attachment_url	text	-	Link to receipt image/PDF.
exchange_rate_applied	numeric(15,6)	-	Audit Trail: The specific rate used at the moment of transaction. Critical for multi-currency transfers to lock in the value.
transfer_group_id	uuid	-	Transfer Logic: Groups two related transactions (one withdrawal, one deposit) into a single logical “Transfer”. The system knows that tx_A (Out of Account 1) and tx_B (Into Account 2) are the same event if they share this ID.
location_lat / _lon	numeric	-	Geotagging coordinates.

---

transaction_splits

Handles complex transactions where a single payment covers multiple categories.

Column	Type	Constraints	Description
id	bigint	PK, Identity	Unique ID.
transaction_id	bigint	FK -> transactions.id	The parent transaction (totals).
category_id	bigint	FK -> categories.id	The category for this specific portion of the total.
amount	numeric(15,2)	NOT NULL	The amount allocated to this split. Sum of splits should equal parent amount.

---

4. Planning: Budgets & Goals

budgets

Spending limits defined for specific periods.

Column	Type	Constraints	Description
id	bigint	PK, Identity	Unique ID.
user_id	bigint	FK -> users.id	Owner.
category_id	bigint	FK -> categories.id	The category this budget limits (e.g., “Restaurants”).
limit_amount	numeric(15,2)	NOT NULL	The maximum spending target.
period	text	NOT NULL	weekly, monthly, yearly.
start_date	date	NOT NULL	Effective start of budget.
end_date	date	NOT NULL	Effective end of budget.
rollover	boolean	Default FALSE	Logic: If true, unspent money from the previous period is added to the next period’s limit. Excess spending is deducted from next period.

---

goals

Financial targets for saving.

Column	Type	Constraints	Description
id	bigint	PK, Identity	Unique ID.
user_id	bigint	FK -> users.id	Owner.
name	text	NOT NULL	Name (e.g., “New Car”).
target_amount	numeric(15,2)	NOT NULL	The saving goal.
current_amount	numeric(15,2)	NOT NULL	Amount saved so far.
linked_account_id	bigint	FK -> accounts.id	Smart Goal: If linked, the goal’s current_amount automatically mirrors the balance of this specific account. If null, it’s a “virtual” goal updated manually.
monthly_contribution	numeric(15,2)	-	Recommended monthly saving to hit target by deadline.
deadline_date	date	-	Target date to reach goal.
status	text	NOT NULL	active, achieved, paused.

---

recurring_rules

Automation engine for repeating transactions.

Note: This table does NOT store transactions. It stores instructions.

Column	Type	Constraints	Description
id	bigint	PK, Identity	Unique ID.
type	text	NOT NULL	expense (Subscription), income (Salary), transfer.
amount	numeric(15,2)	NOT NULL	The amount to generate.
frequency	text	NOT NULL	daily, weekly, monthly, yearly.
next_execution_date	date	NOT NULL	The trigger date. A background job checks this daily. If today >= next_execution_date, it executes the logic and updates this field.
goal_id	bigint	FK -> goals.id	If present, the rule automatically increases a Goal’s balance (Auto-Save).

---

5. Utilities

currency_exchanges

Standalone log for calculation history (The “Calculator” feature).

Column	Type	Constraints	Description
id	bigint	PK, Identity	Unique ID.
user_id	bigint	FK -> users.id	Owner.
source_currency	text	NOT NULL	From user input.
target_currency	text	NOT NULL	From user input.
source_amount	numeric	NOT NULL	Amount converted.
target_amount	numeric	NOT NULL	Result amount.
exchange_rate	numeric	NOT NULL	Rate used for the calculation.
fee_percentage	numeric	-	Optional fee simulation.
created_at	timestamptz	Default Now	Log timestamp.

---

Key Relationships Summary

1. Users -> Currencies: main_currency_code determines how the user views their entire financial life.
2. Transactions -> Transfer Group: Linking two transactions (Withdrawal + Deposit) via transfer_group_id allows the system to treat them as a single “Movement” event, preventing double-counting in Expense/Income reports.
3. Merchants -> Categories: default_category_id enables auto-categorization AI/Automation when selecting a merchant.
4. Goals -> Accounts: linked_account_id turns a conceptual goal into a real tracking mechanism tied to a physical bank account.