Something went wrong. Try again.
A self-hosted household ledger.
Something went wrong. Try again.
1.4 kB · 28 lines
SQL
at main
1234567891011121314151617181920212223242526272829-- CSV backfill import (csv-backfill-import). A connected account whose SimpleFIN-- feed reaches back only a week has a permanent hole in its past that no sync can-- fill; the bank publishes that history as a CSV. Imports are additive only: they-- attach to an account sync already discovered, and never reconcile, sweep, or-- overwrite anything sync owns.
-- One row per uploaded file. `payload` is verbatim and never mutated, mirroring-- raw_syncs. `mapping` and `decisions` live here too because the outcome of an-- import is a function of the bytes AND the human's choices — bytes alone would-- not replay deterministically.CREATE TABLE imports ( id INTEGER PRIMARY KEY AUTOINCREMENT, account_id TEXT NOT NULL REFERENCES accounts (id), filename TEXT, uploaded_at TEXT NOT NULL, payload TEXT NOT NULL, -- verbatim uploaded bytes; never mutated mapping TEXT, -- JSON: field -> column, chosen by the user decisions TEXT, -- JSON: synthetic id -> 'keep' | 'skip' status TEXT NOT NULL DEFAULT 'draft' CHECK (status IN ('draft', 'committed', 'undone')), committed_at TEXT, undone_at TEXT);
-- NULL means synced, so every pre-existing row is already correct and no backfill-- is needed. Carries origin for the ledger source filter and scopes undo.ALTER TABLE transactions ADD COLUMN import_id INTEGER REFERENCES imports (id);
CREATE INDEX idx_transactions_import ON transactions (import_id);