Something went wrong. Try again.
This repository has no description
Something went wrong. Try again.
3.2 kB · 96 lines
SQL
12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364656667686970717273747576777879808182838485868788899091929394959697PRAGMA foreign_keys = ON;
CREATE TABLE IF NOT EXISTS schema_migrations ( version TEXT PRIMARY KEY, applied_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP);
CREATE TABLE accounts ( id INTEGER PRIMARY KEY, name TEXT NOT NULL, type TEXT NOT NULL CHECK (type IN ('checking','savings','credit','investment','loan')), institution TEXT NOT NULL, is_retirement INTEGER NOT NULL DEFAULT 0 CHECK (is_retirement IN (0,1)), currency TEXT NOT NULL CHECK (currency = 'USD'), simplefin_id TEXT UNIQUE);
CREATE TABLE balance_snapshots ( id INTEGER PRIMARY KEY, account_id INTEGER NOT NULL REFERENCES accounts(id) ON DELETE CASCADE, snapshot_date TEXT NOT NULL, balance_cents INTEGER NOT NULL, UNIQUE(account_id, snapshot_date));
CREATE TABLE categories ( name TEXT PRIMARY KEY);
CREATE TABLE category_rules ( id INTEGER PRIMARY KEY, priority INTEGER NOT NULL, pattern TEXT NOT NULL, field TEXT NOT NULL CHECK (field IN ('description','merchant')), category TEXT NOT NULL REFERENCES categories(name), UNIQUE(priority, pattern, field));
CREATE TABLE transactions ( id INTEGER PRIMARY KEY, account_id INTEGER NOT NULL REFERENCES accounts(id) ON DELETE CASCADE, posted_date TEXT NOT NULL, amount_cents INTEGER NOT NULL, description TEXT NOT NULL, merchant TEXT NOT NULL, category TEXT NOT NULL DEFAULT 'Uncategorized' REFERENCES categories(name), is_transfer INTEGER NOT NULL DEFAULT 0 CHECK (is_transfer IN (0,1)), transfer_override INTEGER CHECK (transfer_override IN (0,1) OR transfer_override IS NULL), pending INTEGER NOT NULL DEFAULT 0 CHECK (pending IN (0,1)), source TEXT NOT NULL CHECK (source IN ('seed','csv','simplefin')), external_id TEXT, dedupe_hash TEXT NOT NULL);CREATE UNIQUE INDEX transactions_external_id ON transactions(source, account_id, external_id) WHERE external_id IS NOT NULL;CREATE UNIQUE INDEX transactions_dedupe ON transactions(account_id, dedupe_hash) WHERE external_id IS NULL;CREATE INDEX transactions_date ON transactions(posted_date);CREATE INDEX transactions_category ON transactions(category);
CREATE TABLE budgets ( category TEXT PRIMARY KEY REFERENCES categories(name), monthly_target_cents INTEGER NOT NULL CHECK (monthly_target_cents >= 0));
CREATE TABLE holdings ( id INTEGER PRIMARY KEY, account_id INTEGER NOT NULL REFERENCES accounts(id) ON DELETE CASCADE, symbol TEXT NOT NULL, quantity REAL NOT NULL, cost_basis_cents INTEGER NOT NULL, latest_price_cents INTEGER NOT NULL, as_of_date TEXT NOT NULL, UNIQUE(account_id, symbol));
CREATE TABLE settings ( id INTEGER PRIMARY KEY CHECK (id = 1), birth_year INTEGER NOT NULL, retirement_age INTEGER NOT NULL, terminal_age INTEGER NOT NULL DEFAULT 95, annual_salary_cents INTEGER NOT NULL, contribution_percent REAL NOT NULL, employer_match_percent REAL NOT NULL, employer_match_cap_percent REAL NOT NULL, annual_retirement_spending_cents INTEGER NOT NULL, tax_rate_percent REAL NOT NULL, nominal_return_percent REAL NOT NULL DEFAULT 7, return_stdev_percent REAL NOT NULL DEFAULT 15, inflation_percent REAL NOT NULL DEFAULT 2.5);
CREATE TABLE csv_mapping_profiles ( name TEXT PRIMARY KEY, mapping_json TEXT NOT NULL);