Something went wrong. Try again.
A self-hosted household ledger.
Something went wrong. Try again.
4.3 kB · 128 lines
SQL
at main
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129-- Quantum initial schema.-- App-generated timestamps are ISO-8601 UTC strings; SimpleFIN-native-- timestamps (posted, transacted_at) are Unix epoch seconds as provided.-- All monetary values are integer cents.
CREATE TABLE users ( did TEXT PRIMARY KEY, handle TEXT NOT NULL, created_at TEXT NOT NULL, last_login_at TEXT);
CREATE TABLE sessions ( token TEXT PRIMARY KEY, user_did TEXT NOT NULL REFERENCES users (did), created_at TEXT NOT NULL, expires_at TEXT NOT NULL);CREATE INDEX idx_sessions_expires ON sessions (expires_at);
-- Key/value stores backing @atproto/oauth-client-node.CREATE TABLE oauth_state ( key TEXT PRIMARY KEY, data TEXT NOT NULL, created_at TEXT NOT NULL);CREATE TABLE oauth_session ( key TEXT PRIMARY KEY, data TEXT NOT NULL, created_at TEXT NOT NULL, updated_at TEXT NOT NULL);
CREATE TABLE connections ( id INTEGER PRIMARY KEY AUTOINCREMENT, access_url TEXT NOT NULL, claimed_at TEXT NOT NULL);
CREATE TABLE accounts ( id TEXT PRIMARY KEY, -- SimpleFIN account id connection_id INTEGER NOT NULL REFERENCES connections (id), org_name TEXT, org_domain TEXT, org_sfin_url TEXT, name TEXT NOT NULL, currency TEXT NOT NULL, state TEXT NOT NULL DEFAULT 'NEW' CHECK (state IN ('NEW', 'ACTIVE', 'INACTIVE', 'HIDDEN')), account_type TEXT CHECK (account_type IN ('checking', 'savings', 'credit', 'investment', 'loan', 'other')), display_name TEXT, last_successful_data_at TEXT, created_at TEXT NOT NULL);
CREATE TABLE categories ( id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL UNIQUE, kind TEXT NOT NULL CHECK (kind IN ('income', 'expense', 'transfer')), builtin INTEGER NOT NULL DEFAULT 0, active INTEGER NOT NULL DEFAULT 1, created_at TEXT NOT NULL);
CREATE TABLE transactions ( id INTEGER PRIMARY KEY AUTOINCREMENT, account_id TEXT NOT NULL REFERENCES accounts (id), sfin_id TEXT NOT NULL, posted INTEGER, -- Unix seconds; NULL while pending transacted_at INTEGER, -- Unix seconds; as provided by SimpleFIN amount_cents INTEGER NOT NULL, description TEXT NOT NULL, pending INTEGER NOT NULL DEFAULT 0, extra TEXT, -- verbatim SimpleFIN extra JSON category_id INTEGER REFERENCES categories (id), -- cache of latest categorization event created_at TEXT NOT NULL, removed_at TEXT, -- soft delete (stale pending rows); events are never deleted UNIQUE (account_id, sfin_id));CREATE INDEX idx_transactions_posted ON transactions (posted);CREATE INDEX idx_transactions_category ON transactions (category_id);CREATE INDEX idx_transactions_account ON transactions (account_id);
CREATE TABLE balance_snapshots ( id INTEGER PRIMARY KEY AUTOINCREMENT, account_id TEXT NOT NULL REFERENCES accounts (id), captured_at TEXT NOT NULL, balance_cents INTEGER NOT NULL, available_balance_cents INTEGER);CREATE INDEX idx_snapshots_account_time ON balance_snapshots (account_id, captured_at);
CREATE TABLE raw_syncs ( id INTEGER PRIMARY KEY AUTOINCREMENT, connection_id INTEGER REFERENCES connections (id), fetched_at TEXT NOT NULL, ok INTEGER NOT NULL, payload TEXT, -- verbatim response body; never mutated error TEXT);
CREATE TABLE rules ( id INTEGER PRIMARY KEY AUTOINCREMENT, match_type TEXT NOT NULL CHECK (match_type IN ('exact', 'contains')), pattern TEXT NOT NULL, category_id INTEGER NOT NULL REFERENCES categories (id), created_by_did TEXT NOT NULL REFERENCES users (did), active INTEGER NOT NULL DEFAULT 1, created_at TEXT NOT NULL);
CREATE TABLE categorization_events ( id INTEGER PRIMARY KEY AUTOINCREMENT, transaction_id INTEGER NOT NULL REFERENCES transactions (id), category_id INTEGER REFERENCES categories (id), -- NULL clears the category source TEXT NOT NULL CHECK (source IN ('rule', 'manual', 'reconciliation')), rule_id INTEGER REFERENCES rules (id), actor_did TEXT REFERENCES users (did), created_at TEXT NOT NULL, CHECK (source != 'rule' OR rule_id IS NOT NULL), CHECK (source != 'manual' OR actor_did IS NOT NULL));CREATE INDEX idx_catevents_transaction ON categorization_events (transaction_id);
-- Built-in, non-deletable Transfer category (see specs/categorization).INSERT INTO categories (name, kind, builtin, created_at)VALUES ('Transfer', 'transfer', 1, strftime('%Y-%m-%dT%H:%M:%fZ', 'now'));