From e5abd28a9806ee8f4239a1db068876be82363975 Mon Sep 17 00:00:00 2001 From: Anders Betts Date: Sun, 20 Sep 2026 12:59:05 +0200 Subject: bank: import SEB CSV and match against vouchers (schema v8) --- src/db.c | 81 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 81 insertions(+) (limited to 'src/db.c') diff --git a/src/db.c b/src/db.c index c809bea..7e23cb0 100644 --- a/src/db.c +++ b/src/db.c @@ -271,6 +271,39 @@ static const char SCHEMA_V1[] = " key TEXT NOT NULL," " value TEXT NOT NULL," " PRIMARY KEY (org_id, key)" + ") STRICT;\n" + + "CREATE TABLE bank_transactions (" + " org_id INTEGER NOT NULL REFERENCES orgs(id)," + " id INTEGER PRIMARY KEY," + " account TEXT NOT NULL," + " booked_at TEXT NOT NULL," + " value_date TEXT NOT NULL," + " text TEXT NOT NULL," + " type TEXT NOT NULL," + " amount_ore INTEGER NOT NULL," + " balance_ore INTEGER," + " source TEXT NOT NULL," + " source_hash BLOB NOT NULL CHECK (length(source_hash) = 32)," + " imported_at TEXT NOT NULL," + " imported_by INTEGER NOT NULL REFERENCES users(id)," + " UNIQUE (org_id, id)," + " UNIQUE (org_id, source_hash)" + ") STRICT;\n" + "CREATE INDEX idx_bank_tx_date ON bank_transactions(org_id, booked_at);\n" + + "CREATE TABLE bank_matches (" + " org_id INTEGER NOT NULL," + " transaction_id INTEGER NOT NULL," + " voucher_id INTEGER NOT NULL," + " matched_at TEXT NOT NULL," + " matched_by INTEGER NOT NULL REFERENCES users(id)," + " kind TEXT NOT NULL DEFAULT 'manual'" + " CHECK (kind IN ('manual','auto'))," + " PRIMARY KEY (org_id, transaction_id, voucher_id)," + " FOREIGN KEY (org_id, transaction_id)" + " REFERENCES bank_transactions(org_id, id)," + " FOREIGN KEY (org_id, voucher_id) REFERENCES vouchers(org_id, id)" ") STRICT;\n"; static const char SCHEMA_V2[] = @@ -458,6 +491,50 @@ static int db_upgrade_v7(sqlite3 *db, char **err) err); } +/* v8: bank reconciliation — imported statement evidence and the mutable + transaction/voucher links (phase 1 never books anything). */ +static int db_upgrade_v8(sqlite3 *db, char **err) +{ + if (db_exec(db, + "CREATE TABLE IF NOT EXISTS bank_transactions (" + " org_id INTEGER NOT NULL REFERENCES orgs(id)," + " id INTEGER PRIMARY KEY," + " account TEXT NOT NULL," + " booked_at TEXT NOT NULL," + " value_date TEXT NOT NULL," + " text TEXT NOT NULL," + " type TEXT NOT NULL," + " amount_ore INTEGER NOT NULL," + " balance_ore INTEGER," + " source TEXT NOT NULL," + " source_hash BLOB NOT NULL CHECK (length(source_hash) = 32)," + " imported_at TEXT NOT NULL," + " imported_by INTEGER NOT NULL REFERENCES users(id)," + " UNIQUE (org_id, id)," + " UNIQUE (org_id, source_hash)" + ") STRICT;" + "CREATE INDEX IF NOT EXISTS idx_bank_tx_date" + " ON bank_transactions(org_id, booked_at);", + err) != 0) + return -1; + return db_exec(db, + "CREATE TABLE IF NOT EXISTS bank_matches (" + " org_id INTEGER NOT NULL," + " transaction_id INTEGER NOT NULL," + " voucher_id INTEGER NOT NULL," + " matched_at TEXT NOT NULL," + " matched_by INTEGER NOT NULL REFERENCES users(id)," + " kind TEXT NOT NULL DEFAULT 'manual'" + " CHECK (kind IN ('manual','auto'))," + " PRIMARY KEY (org_id, transaction_id, voucher_id)," + " FOREIGN KEY (org_id, transaction_id)" + " REFERENCES bank_transactions(org_id, id)," + " FOREIGN KEY (org_id, voucher_id)" + " REFERENCES vouchers(org_id, id)" + ") STRICT", + err); +} + static int db_upgrade(sqlite3 *db, int from, char **err) { if (db_exec(db, "BEGIN IMMEDIATE", err) != 0) @@ -486,6 +563,10 @@ static int db_upgrade(sqlite3 *db, int from, char **err) db_exec(db, "ROLLBACK", NULL); return -1; } + if (from < 8 && db_upgrade_v8(db, err) != 0) { + db_exec(db, "ROLLBACK", NULL); + return -1; + } char *sql = sqlite3_mprintf( "UPDATE meta SET value='%d' WHERE key='schema_version'", BOKF_SCHEMA_VERSION); -- cgit v1.3