#include "db.h" #include #include #include #include #include #include #include #include #include "auth.h" #include "config.h" #include "log.h" #include "seed.h" #include "util.h" static const char SCHEMA_V1[] = "CREATE TABLE meta (" " key TEXT PRIMARY KEY," " value TEXT NOT NULL" ") STRICT;\n" "CREATE TABLE users (" " id INTEGER PRIMARY KEY," " username TEXT NOT NULL COLLATE NOCASE UNIQUE," " display_name TEXT NOT NULL," " pw_hash TEXT NOT NULL," " is_admin INTEGER NOT NULL DEFAULT 0 CHECK (is_admin IN (0,1))," " created_at TEXT NOT NULL," " disabled_at TEXT" ") STRICT;\n" "CREATE TABLE orgs (" " id INTEGER PRIMARY KEY," " name TEXT NOT NULL," " org_nr TEXT," " vat_nr TEXT," " address TEXT," " postal_code TEXT," " city TEXT," " country TEXT NOT NULL DEFAULT 'SE'," " email TEXT," " phone TEXT," " description TEXT NOT NULL DEFAULT ''," " shares INTEGER NOT NULL DEFAULT 0," " fiscal_year_start_month INTEGER NOT NULL DEFAULT 1" " CHECK (fiscal_year_start_month BETWEEN 1 AND 12)," " moms_period TEXT NOT NULL DEFAULT 'month'" " CHECK (moms_period IN ('month','quarter','year'))," " framework TEXT NOT NULL DEFAULT 'K2'" " CHECK (framework IN ('K2','K3'))," " created_at TEXT NOT NULL," " created_by INTEGER NOT NULL REFERENCES users(id)," " archived_at TEXT" ") STRICT;\n" "CREATE TABLE board_members (" " org_id INTEGER NOT NULL REFERENCES orgs(id)," " id INTEGER PRIMARY KEY," " name TEXT NOT NULL," " title TEXT NOT NULL," " created_at TEXT NOT NULL," " UNIQUE (org_id, id)" ") STRICT;\n" "CREATE TABLE memberships (" " org_id INTEGER NOT NULL REFERENCES orgs(id)," " user_id INTEGER NOT NULL REFERENCES users(id)," " role TEXT NOT NULL CHECK (role IN ('owner','bookkeeper','viewer'))," " created_at TEXT NOT NULL," " PRIMARY KEY (org_id, user_id)" ") STRICT;\n" "CREATE TABLE api_tokens (" " id INTEGER PRIMARY KEY," " org_id INTEGER NOT NULL REFERENCES orgs(id)," " user_id INTEGER NOT NULL REFERENCES users(id)," " label TEXT NOT NULL," " token_hash BLOB NOT NULL UNIQUE CHECK (length(token_hash) = 32)," " scopes TEXT NOT NULL," " created_at TEXT NOT NULL," " expires_at TEXT," " last_used_at TEXT," " revoked_at TEXT" ") STRICT;\n" "CREATE TABLE accounts (" " org_id INTEGER NOT NULL REFERENCES orgs(id)," " id INTEGER PRIMARY KEY," " number TEXT NOT NULL" " CHECK (number GLOB '[0-9]*' AND length(number) BETWEEN 1 AND 10)," " name TEXT NOT NULL," " type TEXT NOT NULL" " CHECK (type IN ('asset','liability','equity','revenue','expense'))," " sru_code TEXT," " vat_code TEXT," " active INTEGER NOT NULL DEFAULT 1 CHECK (active IN (0,1))," " created_at TEXT NOT NULL," " updated_at TEXT," " UNIQUE (org_id, id)," " UNIQUE (org_id, number)" ") STRICT;\n" "CREATE INDEX idx_accounts_type ON accounts(org_id, type);\n" "CREATE TABLE fiscal_years (" " org_id INTEGER NOT NULL REFERENCES orgs(id)," " id INTEGER PRIMARY KEY," " label TEXT NOT NULL," " start_date TEXT NOT NULL," " end_date TEXT NOT NULL," " status TEXT NOT NULL DEFAULT 'open' CHECK (status IN ('open','closed'))," " locked_until TEXT," " dividend_ore INTEGER NOT NULL DEFAULT 0," " events TEXT NOT NULL DEFAULT ''," " agm_date TEXT NOT NULL DEFAULT ''," " dividend_date TEXT NOT NULL DEFAULT ''," " employees TEXT NOT NULL DEFAULT ''," " notes TEXT NOT NULL DEFAULT ''," " created_at TEXT NOT NULL," " closed_at TEXT," " closed_by INTEGER REFERENCES users(id)," " UNIQUE (org_id, id)," " UNIQUE (org_id, label)," " CHECK (start_date < end_date)," " CHECK (locked_until IS NULL OR locked_until <= end_date)" ") STRICT;\n" "CREATE TABLE sequences (" " org_id INTEGER NOT NULL," " fiscal_year_id INTEGER NOT NULL," " series TEXT NOT NULL," " next_number INTEGER NOT NULL DEFAULT 1," " PRIMARY KEY (org_id, fiscal_year_id, series)," " FOREIGN KEY (org_id, fiscal_year_id) REFERENCES fiscal_years(org_id, id)" ") STRICT;\n" "CREATE TABLE vouchers (" " org_id INTEGER NOT NULL REFERENCES orgs(id)," " id INTEGER PRIMARY KEY," " fiscal_year_id INTEGER NOT NULL," " series TEXT NOT NULL," " number INTEGER NOT NULL CHECK (number > 0)," " date TEXT NOT NULL CHECK (date LIKE '____-__-__')," " description TEXT NOT NULL CHECK (length(description) > 0)," " source TEXT NOT NULL DEFAULT 'manual'" " CHECK (source IN ('manual','agent','sie_import','system','ib'," " 'invoice','payroll','payroll_tax'))," " client_ref TEXT," " corrects_voucher_id INTEGER," " created_at TEXT NOT NULL," " created_by_user INTEGER NOT NULL REFERENCES users(id)," " created_by_token INTEGER REFERENCES api_tokens(id)," " hash_prev BLOB NOT NULL CHECK (length(hash_prev) = 32)," " hash BLOB NOT NULL CHECK (length(hash) = 32)," " UNIQUE (org_id, id)," " UNIQUE (org_id, fiscal_year_id, series, number)," " UNIQUE (org_id, client_ref)," " FOREIGN KEY (org_id, fiscal_year_id) REFERENCES fiscal_years(org_id, id)," " FOREIGN KEY (org_id, corrects_voucher_id) REFERENCES vouchers(org_id, id)" ") STRICT;\n" "CREATE TABLE voucher_rows (" " org_id INTEGER NOT NULL," " id INTEGER PRIMARY KEY," " voucher_id INTEGER NOT NULL," " line_no INTEGER NOT NULL," " account_id INTEGER NOT NULL," " debit_ore INTEGER NOT NULL DEFAULT 0 CHECK (debit_ore >= 0)," " credit_ore INTEGER NOT NULL DEFAULT 0 CHECK (credit_ore >= 0)," " description TEXT," " CHECK ((debit_ore = 0) <> (credit_ore = 0))," " CHECK (debit_ore > 0 OR credit_ore > 0)," " UNIQUE (org_id, id)," " UNIQUE (org_id, voucher_id, line_no)," " FOREIGN KEY (org_id, voucher_id) REFERENCES vouchers(org_id, id)," " FOREIGN KEY (org_id, account_id) REFERENCES accounts(org_id, id)" ") STRICT;\n" "CREATE INDEX idx_vouchers_date ON vouchers(org_id, date);\n" "CREATE INDEX idx_vouchers_fy ON vouchers(org_id, fiscal_year_id, series, number);\n" "CREATE INDEX idx_rows_voucher ON voucher_rows(org_id, voucher_id, line_no);\n" "CREATE INDEX idx_rows_account ON voucher_rows(org_id, account_id);\n" "CREATE TRIGGER vouchers_no_update BEFORE UPDATE ON vouchers BEGIN" " SELECT RAISE(ABORT, 'vouchers are append-only');" "END;\n" "CREATE TRIGGER vouchers_no_delete BEFORE DELETE ON vouchers BEGIN" " SELECT RAISE(ABORT, 'vouchers are append-only');" "END;\n" "CREATE TRIGGER voucher_rows_no_update BEFORE UPDATE ON voucher_rows BEGIN" " SELECT RAISE(ABORT, 'voucher rows are append-only');" "END;\n" "CREATE TRIGGER voucher_rows_no_delete BEFORE DELETE ON voucher_rows BEGIN" " SELECT RAISE(ABORT, 'voucher rows are append-only');" "END;\n" "CREATE TABLE attachments (" " org_id INTEGER NOT NULL REFERENCES orgs(id)," " id INTEGER PRIMARY KEY," " sha256 BLOB NOT NULL CHECK (length(sha256) = 32)," " filename TEXT NOT NULL," " mime TEXT NOT NULL," " size_bytes INTEGER NOT NULL CHECK (size_bytes >= 0)," " content BLOB NOT NULL," " created_at TEXT NOT NULL," " created_by INTEGER NOT NULL REFERENCES users(id)," " UNIQUE (org_id, id)," " UNIQUE (org_id, sha256, filename)" ") STRICT;\n" "CREATE TRIGGER attachments_no_update BEFORE UPDATE ON attachments BEGIN" " SELECT RAISE(ABORT, 'attachments are append-only');" "END;\n" "CREATE TRIGGER attachments_no_delete BEFORE DELETE ON attachments BEGIN" " SELECT RAISE(ABORT, 'attachments are append-only');" "END;\n" "CREATE TABLE voucher_attachments (" " org_id INTEGER NOT NULL," " voucher_id INTEGER NOT NULL," " attachment_id INTEGER NOT NULL," " created_at TEXT NOT NULL," " PRIMARY KEY (org_id, voucher_id, attachment_id)," " FOREIGN KEY (org_id, voucher_id) REFERENCES vouchers(org_id, id)," " FOREIGN KEY (org_id, attachment_id) REFERENCES attachments(org_id, id)" ") STRICT;\n" "CREATE TABLE audit_log (" " seq INTEGER PRIMARY KEY," " org_id INTEGER REFERENCES orgs(id)," " at TEXT NOT NULL," " actor_user_id INTEGER REFERENCES users(id)," " actor_token_id INTEGER REFERENCES api_tokens(id)," " action TEXT NOT NULL," " request_json TEXT NOT NULL DEFAULT '{}'," " result_code TEXT NOT NULL," " hash_prev BLOB NOT NULL CHECK (length(hash_prev) = 32)," " hash BLOB NOT NULL CHECK (length(hash) = 32)" ") STRICT;\n" "CREATE INDEX idx_audit_org ON audit_log(org_id, seq);\n" "CREATE TRIGGER audit_no_update BEFORE UPDATE ON audit_log BEGIN" " SELECT RAISE(ABORT, 'audit log is append-only');" "END;\n" "CREATE TRIGGER audit_no_delete BEFORE DELETE ON audit_log BEGIN" " SELECT RAISE(ABORT, 'audit log is append-only');" "END;\n" "CREATE TABLE idempotency (" " org_id INTEGER NOT NULL," " client_ref TEXT NOT NULL," " cmd TEXT NOT NULL," " response_json TEXT NOT NULL," " created_at TEXT NOT NULL," " PRIMARY KEY (org_id, client_ref)," " FOREIGN KEY (org_id) REFERENCES orgs(id)" ") STRICT;\n" "CREATE TABLE report_rules (" " org_id INTEGER NOT NULL REFERENCES orgs(id)," " id INTEGER PRIMARY KEY," " report TEXT NOT NULL," " box TEXT NOT NULL," " match_type TEXT NOT NULL CHECK (match_type IN ('account','range','type'))," " pattern TEXT NOT NULL," " sign INTEGER NOT NULL DEFAULT 1 CHECK (sign IN (1,-1))," " sort_order INTEGER NOT NULL DEFAULT 0," " UNIQUE (org_id, id)" ") STRICT;\n" "CREATE TABLE settings (" " org_id INTEGER NOT NULL REFERENCES orgs(id)," " 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" "CREATE TABLE customers (" " org_id INTEGER NOT NULL REFERENCES orgs(id)," " id INTEGER PRIMARY KEY," " name TEXT NOT NULL," " address TEXT NOT NULL DEFAULT ''," " postal_code TEXT NOT NULL DEFAULT ''," " city TEXT NOT NULL DEFAULT ''," " country TEXT NOT NULL DEFAULT 'SE'," " vat_nr TEXT NOT NULL DEFAULT ''," " email TEXT NOT NULL DEFAULT ''," " your_ref TEXT NOT NULL DEFAULT ''," " payment_days INTEGER NOT NULL DEFAULT 30 CHECK (payment_days >= 0)," " notes TEXT NOT NULL DEFAULT ''," " active INTEGER NOT NULL DEFAULT 1 CHECK (active IN (0,1))," " created_at TEXT NOT NULL," " updated_at TEXT," " UNIQUE (org_id, id)," " UNIQUE (org_id, name)" ") STRICT;\n" "CREATE TABLE invoice_sequence (" " org_id INTEGER NOT NULL PRIMARY KEY REFERENCES orgs(id)," " next_number INTEGER NOT NULL CHECK (next_number > 0)" ") STRICT;\n" "CREATE TABLE invoices (" " org_id INTEGER NOT NULL REFERENCES orgs(id)," " id INTEGER PRIMARY KEY," " customer_id INTEGER NOT NULL," " number INTEGER NOT NULL CHECK (number > 0)," " ocr TEXT NOT NULL," " invoice_date TEXT NOT NULL," " due_date TEXT NOT NULL," " delivery_date TEXT NOT NULL DEFAULT ''," " your_ref TEXT NOT NULL DEFAULT ''," " our_ref TEXT NOT NULL DEFAULT ''," " notes TEXT NOT NULL DEFAULT ''," " net_ore INTEGER NOT NULL," " vat_ore INTEGER NOT NULL," " total_ore INTEGER NOT NULL," " status TEXT NOT NULL DEFAULT 'issued'" " CHECK (status IN ('issued','credited'))," " document_id INTEGER," " voucher_id INTEGER," " last_sent_at TEXT," " last_sent_to TEXT," " created_at TEXT NOT NULL," " created_by INTEGER NOT NULL REFERENCES users(id)," " UNIQUE (org_id, id)," " UNIQUE (org_id, number)," " FOREIGN KEY (org_id, customer_id) REFERENCES customers(org_id, id)," " FOREIGN KEY (org_id, document_id) REFERENCES attachments(org_id, id)," " FOREIGN KEY (org_id, voucher_id) REFERENCES vouchers(org_id, id)" ") STRICT;\n" "CREATE TABLE invoice_rows (" " org_id INTEGER NOT NULL," " id INTEGER PRIMARY KEY," " invoice_id INTEGER NOT NULL," " line_no INTEGER NOT NULL," " article_no TEXT NOT NULL DEFAULT ''," " description TEXT NOT NULL," " quantity_milli INTEGER NOT NULL CHECK (quantity_milli > 0)," " unit TEXT NOT NULL DEFAULT 'st'," " unit_price_ore INTEGER NOT NULL," " amount_ore INTEGER NOT NULL," " note TEXT NOT NULL DEFAULT ''," " vat_code TEXT NOT NULL DEFAULT '25'" " CHECK (vat_code IN ('25','12','6','0','rc','eu'))," " account TEXT NOT NULL DEFAULT ''," " UNIQUE (org_id, id)," " UNIQUE (org_id, invoice_id, line_no)," " FOREIGN KEY (org_id, invoice_id) REFERENCES invoices(org_id, id)" ") STRICT;\n"; static const char SCHEMA_V2[] = "CREATE TABLE voucher_templates (" " org_id INTEGER NOT NULL REFERENCES orgs(id)," " id INTEGER PRIMARY KEY," " name TEXT NOT NULL," " series TEXT NOT NULL DEFAULT 'A'," " description TEXT NOT NULL DEFAULT ''," " active INTEGER NOT NULL DEFAULT 1 CHECK (active IN (0,1))," " created_at TEXT NOT NULL," " updated_at TEXT," " UNIQUE (org_id, id)," " UNIQUE (org_id, name)" ") STRICT;\n" "CREATE TABLE voucher_template_rows (" " org_id INTEGER NOT NULL," " id INTEGER PRIMARY KEY," " template_id INTEGER NOT NULL," " line_no INTEGER NOT NULL," " account_id INTEGER NOT NULL," " formula TEXT NOT NULL CHECK (length(formula) > 0)," " description TEXT," " UNIQUE (org_id, id)," " UNIQUE (org_id, template_id, line_no)," " FOREIGN KEY (org_id, template_id)" " REFERENCES voucher_templates(org_id, id)," " FOREIGN KEY (org_id, account_id) REFERENCES accounts(org_id, id)" ") STRICT;\n" "CREATE INDEX idx_template_rows ON voucher_template_rows(org_id," " template_id, line_no);\n"; /* v10: payroll — the employee register, the monthly runs with their lines and Skatteverket's national tax tables. tax_tables and tax_table_meta are reference data, not tenant data, so they carry no org_id. Shared by the fresh schema and the v10 migration. */ static const char SCHEMA_PAYROLL[] = "CREATE TABLE IF NOT EXISTS employees (" " org_id INTEGER NOT NULL REFERENCES orgs(id)," " id INTEGER PRIMARY KEY," " name TEXT NOT NULL," " personal_no_enc TEXT NOT NULL," " address TEXT NOT NULL DEFAULT ''," " postal_code TEXT NOT NULL DEFAULT ''," " city TEXT NOT NULL DEFAULT ''," " bank_account TEXT NOT NULL DEFAULT ''," " salary_account TEXT NOT NULL DEFAULT '7210'," " monthly_salary_ore INTEGER NOT NULL DEFAULT 0" " CHECK (monthly_salary_ore >= 0)," " tax_table INTEGER NOT NULL DEFAULT 30" " CHECK (tax_table BETWEEN 29 AND 42)," " tax_column INTEGER NOT NULL DEFAULT 1" " CHECK (tax_column BETWEEN 1 AND 6)," " active INTEGER NOT NULL DEFAULT 1 CHECK (active IN (0,1))," " created_at TEXT NOT NULL," " updated_at TEXT," " UNIQUE (org_id, id)" ") STRICT;\n" "CREATE TABLE IF NOT EXISTS payroll_runs (" " org_id INTEGER NOT NULL REFERENCES orgs(id)," " id INTEGER PRIMARY KEY," " fiscal_year_id INTEGER NOT NULL," " period TEXT NOT NULL," " pay_date TEXT NOT NULL," " status TEXT NOT NULL DEFAULT 'posted'" " CHECK (status IN ('posted','paid'))," " gross_ore INTEGER NOT NULL," " tax_ore INTEGER NOT NULL," " avgifter_ore INTEGER NOT NULL," " net_ore INTEGER NOT NULL," " voucher_id INTEGER," " payment_voucher_id INTEGER," " created_at TEXT NOT NULL," " created_by INTEGER NOT NULL REFERENCES users(id)," " UNIQUE (org_id, id)," " UNIQUE (org_id, period, pay_date)," " FOREIGN KEY (org_id, fiscal_year_id)" " REFERENCES fiscal_years(org_id, id)," " FOREIGN KEY (org_id, voucher_id) REFERENCES vouchers(org_id, id)," " FOREIGN KEY (org_id, payment_voucher_id) REFERENCES vouchers(org_id, id)" ") STRICT;\n" "CREATE TABLE IF NOT EXISTS payroll_run_lines (" " org_id INTEGER NOT NULL," " id INTEGER PRIMARY KEY," " run_id INTEGER NOT NULL," " employee_id INTEGER NOT NULL," " gross_ore INTEGER NOT NULL," " tax_ore INTEGER NOT NULL," " avgifter_ore INTEGER NOT NULL," " net_ore INTEGER NOT NULL," " tax_table INTEGER NOT NULL," " tax_column INTEGER NOT NULL," " UNIQUE (org_id, id)," " UNIQUE (org_id, run_id, employee_id)," " FOREIGN KEY (org_id, run_id) REFERENCES payroll_runs(org_id, id)," " FOREIGN KEY (org_id, employee_id) REFERENCES employees(org_id, id)" ") STRICT;\n" "CREATE TABLE IF NOT EXISTS tax_tables (" " in_year INTEGER NOT NULL," " table_no INTEGER NOT NULL," " column_no INTEGER NOT NULL," " income_from_ore INTEGER NOT NULL," " income_to_ore INTEGER," " tax_ore INTEGER NOT NULL," " pct INTEGER," " PRIMARY KEY (in_year, table_no, column_no, income_from_ore)" ") STRICT;\n" "CREATE TABLE IF NOT EXISTS tax_table_meta (" " in_year INTEGER PRIMARY KEY," " source_url TEXT NOT NULL," " sha256 BLOB NOT NULL CHECK (length(sha256) = 32)," " fetched_at TEXT NOT NULL" ") STRICT;\n"; static void set_err(char **err, const char *fmt, ...) __attribute__((format(printf, 2, 3))); static void set_err(char **err, const char *fmt, ...) { if (!err) return; char buf[1024]; va_list ap; va_start(ap, fmt); vsnprintf(buf, sizeof buf, fmt, ap); va_end(ap); free(*err); *err = xstrdup(buf); } int db_exec(sqlite3 *db, const char *sql, char **err) { char *msg = NULL; int rc = sqlite3_exec(db, sql, NULL, NULL, &msg); if (rc != SQLITE_OK) { set_err(err, "%s", msg ? msg : sqlite3_errmsg(db)); sqlite3_free(msg); return -1; } return 0; } int db_migrate(sqlite3 *db, char **err) { if (db_exec(db, "BEGIN IMMEDIATE", err) != 0) return -1; if (db_exec(db, SCHEMA_V1, err) != 0) { db_exec(db, "ROLLBACK", NULL); return -1; } if (db_exec(db, SCHEMA_V2, err) != 0) { db_exec(db, "ROLLBACK", NULL); return -1; } if (db_exec(db, SCHEMA_PAYROLL, err) != 0) { db_exec(db, "ROLLBACK", NULL); return -1; } char ts[32]; util_iso8601(util_now(), ts, sizeof ts); char *sql = sqlite3_mprintf( "INSERT INTO meta(key,value) VALUES('schema_version','%d')" " ON CONFLICT(key) DO UPDATE SET value=excluded.value;" "INSERT INTO meta(key,value) VALUES('created_at','%q');", BOKF_SCHEMA_VERSION, ts); int rc = db_exec(db, sql, err); sqlite3_free(sql); if (rc != 0) { db_exec(db, "ROLLBACK", NULL); return -1; } if (db_exec(db, "COMMIT", err) != 0) return -1; log_info("database schema created at version %d", BOKF_SCHEMA_VERSION); return 0; } /* Forward-only upgrades for existing databases. */ /* v3: the moms starter rules missed 33xx sales, sent 2614 to box 10 and had box 48 with the wrong sign. Rules were never editable, so replace them with the corrected defaults for every org. */ static int db_upgrade_v3(sqlite3 *db, char **err) { if (db_exec(db, "DELETE FROM report_rules WHERE report='vat'", err) != 0) return -1; sqlite3_stmt *st = NULL; if (sqlite3_prepare_v2(db, "SELECT id FROM orgs", -1, &st, NULL) != SQLITE_OK) { set_err(err, "database error"); return -1; } while (sqlite3_step(st) == SQLITE_ROW) { int64_t id = sqlite3_column_int64(st, 0); if (seed_vat_rules(db, id, err) != 0) { sqlite3_finalize(st); return -1; } } sqlite3_finalize(st); return 0; } /* v4: the årsredovisning keeps the board's proposed dividend per year. */ static int db_upgrade_v4(sqlite3 *db, char **err) { return db_exec(db, "ALTER TABLE fiscal_years ADD COLUMN dividend_ore" " INTEGER NOT NULL DEFAULT 0", err); } /* v5: årsredovisning details — the company description and share count, the year's material events, and a board-member list for signatures. */ static int db_upgrade_v5(sqlite3 *db, char **err) { if (db_exec(db, "ALTER TABLE orgs ADD COLUMN description TEXT NOT NULL" " DEFAULT ''", err) != 0) return -1; if (db_exec(db, "ALTER TABLE orgs ADD COLUMN shares INTEGER NOT NULL" " DEFAULT 0", err) != 0) return -1; if (db_exec(db, "ALTER TABLE fiscal_years ADD COLUMN events TEXT NOT NULL" " DEFAULT ''", err) != 0) return -1; return db_exec(db, "CREATE TABLE IF NOT EXISTS board_members (" " org_id INTEGER NOT NULL REFERENCES orgs(id)," " id INTEGER PRIMARY KEY," " name TEXT NOT NULL," " title TEXT NOT NULL," " created_at TEXT NOT NULL," " UNIQUE (org_id, id)" ") STRICT", err); } /* v6: "Information om året" — the year-specific årsredovisning details. */ static int db_upgrade_v6(sqlite3 *db, char **err) { if (db_exec(db, "ALTER TABLE fiscal_years ADD COLUMN agm_date TEXT NOT NULL" " DEFAULT ''", err) != 0) return -1; if (db_exec(db, "ALTER TABLE fiscal_years ADD COLUMN dividend_date TEXT" " NOT NULL DEFAULT ''", err) != 0) return -1; if (db_exec(db, "ALTER TABLE fiscal_years ADD COLUMN employees TEXT NOT NULL" " DEFAULT ''", err) != 0) return -1; return db_exec(db, "ALTER TABLE fiscal_years ADD COLUMN notes TEXT NOT NULL" " DEFAULT ''", err); } /* v7: attachments are immutable too — the content-addressed underlag may never be updated or deleted, only linked/unlinked. */ static int db_upgrade_v7(sqlite3 *db, char **err) { if (db_exec(db, "CREATE TRIGGER IF NOT EXISTS attachments_no_update" " BEFORE UPDATE ON attachments BEGIN" " SELECT RAISE(ABORT, 'attachments are append-only');" "END", err) != 0) return -1; return db_exec(db, "CREATE TRIGGER IF NOT EXISTS attachments_no_delete" " BEFORE DELETE ON attachments BEGIN" " SELECT RAISE(ABORT, 'attachments are append-only');" "END", 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); } /* v9: invoicing — customers, the global invoice number series, invoices and their rows, plus a widened voucher source. A CHECK cannot be altered in place, so the vouchers table is rebuilt; db_open turns foreign keys off for the migration and runs PRAGMA foreign_key_check afterwards. */ static int db_upgrade_v9(sqlite3 *db, char **err) { if (db_exec(db, "CREATE TABLE IF NOT EXISTS customers (" " org_id INTEGER NOT NULL REFERENCES orgs(id)," " id INTEGER PRIMARY KEY," " name TEXT NOT NULL," " address TEXT NOT NULL DEFAULT ''," " postal_code TEXT NOT NULL DEFAULT ''," " city TEXT NOT NULL DEFAULT ''," " country TEXT NOT NULL DEFAULT 'SE'," " vat_nr TEXT NOT NULL DEFAULT ''," " email TEXT NOT NULL DEFAULT ''," " your_ref TEXT NOT NULL DEFAULT ''," " payment_days INTEGER NOT NULL DEFAULT 30" " CHECK (payment_days >= 0)," " notes TEXT NOT NULL DEFAULT ''," " active INTEGER NOT NULL DEFAULT 1 CHECK (active IN (0,1))," " created_at TEXT NOT NULL," " updated_at TEXT," " UNIQUE (org_id, id)," " UNIQUE (org_id, name)" ") STRICT;" "CREATE TABLE IF NOT EXISTS invoice_sequence (" " org_id INTEGER NOT NULL PRIMARY KEY REFERENCES orgs(id)," " next_number INTEGER NOT NULL CHECK (next_number > 0)" ") STRICT;" "CREATE TABLE IF NOT EXISTS invoices (" " org_id INTEGER NOT NULL REFERENCES orgs(id)," " id INTEGER PRIMARY KEY," " customer_id INTEGER NOT NULL," " number INTEGER NOT NULL CHECK (number > 0)," " ocr TEXT NOT NULL," " invoice_date TEXT NOT NULL," " due_date TEXT NOT NULL," " delivery_date TEXT NOT NULL DEFAULT ''," " your_ref TEXT NOT NULL DEFAULT ''," " our_ref TEXT NOT NULL DEFAULT ''," " notes TEXT NOT NULL DEFAULT ''," " net_ore INTEGER NOT NULL," " vat_ore INTEGER NOT NULL," " total_ore INTEGER NOT NULL," " status TEXT NOT NULL DEFAULT 'issued'" " CHECK (status IN ('issued','credited'))," " document_id INTEGER," " voucher_id INTEGER," " last_sent_at TEXT," " last_sent_to TEXT," " created_at TEXT NOT NULL," " created_by INTEGER NOT NULL REFERENCES users(id)," " UNIQUE (org_id, id)," " UNIQUE (org_id, number)," " FOREIGN KEY (org_id, customer_id)" " REFERENCES customers(org_id, id)," " FOREIGN KEY (org_id, document_id)" " REFERENCES attachments(org_id, id)," " FOREIGN KEY (org_id, voucher_id)" " REFERENCES vouchers(org_id, id)" ") STRICT;" "CREATE TABLE IF NOT EXISTS invoice_rows (" " org_id INTEGER NOT NULL," " id INTEGER PRIMARY KEY," " invoice_id INTEGER NOT NULL," " line_no INTEGER NOT NULL," " article_no TEXT NOT NULL DEFAULT ''," " description TEXT NOT NULL," " quantity_milli INTEGER NOT NULL CHECK (quantity_milli > 0)," " unit TEXT NOT NULL DEFAULT 'st'," " unit_price_ore INTEGER NOT NULL," " amount_ore INTEGER NOT NULL," " note TEXT NOT NULL DEFAULT ''," " vat_code TEXT NOT NULL DEFAULT '25'" " CHECK (vat_code IN ('25','12','6','0','rc','eu'))," " account TEXT NOT NULL DEFAULT ''," " UNIQUE (org_id, id)," " UNIQUE (org_id, invoice_id, line_no)," " FOREIGN KEY (org_id, invoice_id)" " REFERENCES invoices(org_id, id)" ") STRICT", err) != 0) return -1; return db_exec( db, "CREATE TABLE vouchers_new (" " org_id INTEGER NOT NULL REFERENCES orgs(id)," " id INTEGER PRIMARY KEY," " fiscal_year_id INTEGER NOT NULL," " series TEXT NOT NULL," " number INTEGER NOT NULL CHECK (number > 0)," " date TEXT NOT NULL CHECK (date LIKE '____-__-__')," " description TEXT NOT NULL CHECK (length(description) > 0)," " source TEXT NOT NULL DEFAULT 'manual'" " CHECK (source IN ('manual','agent','sie_import','system','ib'," " 'invoice'))," " client_ref TEXT," " corrects_voucher_id INTEGER," " created_at TEXT NOT NULL," " created_by_user INTEGER NOT NULL REFERENCES users(id)," " created_by_token INTEGER REFERENCES api_tokens(id)," " hash_prev BLOB NOT NULL CHECK (length(hash_prev) = 32)," " hash BLOB NOT NULL CHECK (length(hash) = 32)," " UNIQUE (org_id, id)," " UNIQUE (org_id, fiscal_year_id, series, number)," " UNIQUE (org_id, client_ref)," " FOREIGN KEY (org_id, fiscal_year_id)" " REFERENCES fiscal_years(org_id, id)," " FOREIGN KEY (org_id, corrects_voucher_id)" " REFERENCES vouchers(org_id, id)" ") STRICT;" "INSERT INTO vouchers_new(org_id,id,fiscal_year_id,series,number,date," " description,source,client_ref,corrects_voucher_id,created_at," " created_by_user,created_by_token,hash_prev,hash)" " SELECT org_id,id,fiscal_year_id,series,number,date,description,source," " client_ref,corrects_voucher_id,created_at,created_by_user," " created_by_token,hash_prev,hash FROM vouchers;" "DROP TABLE vouchers;" "ALTER TABLE vouchers_new RENAME TO vouchers;" "CREATE INDEX idx_vouchers_date ON vouchers(org_id, date);" "CREATE INDEX idx_vouchers_fy" " ON vouchers(org_id, fiscal_year_id, series, number);" "CREATE TRIGGER vouchers_no_update BEFORE UPDATE ON vouchers" " BEGIN SELECT RAISE(ABORT, 'vouchers are append-only'); END;" "CREATE TRIGGER vouchers_no_delete BEFORE DELETE ON vouchers" " BEGIN SELECT RAISE(ABORT, 'vouchers are append-only'); END;", err); } /* v10: payroll — the employee register, the monthly runs with their lines and Skatteverket's national tax tables, plus 'payroll'/'payroll_tax' as voucher sources. Widening that CHECK needs the same table rebuild as v9; db_open disables foreign keys for the migration and re-checks them. */ static int db_upgrade_v10(sqlite3 *db, char **err) { if (db_exec(db, SCHEMA_PAYROLL, err) != 0) return -1; return db_exec( db, "CREATE TABLE vouchers_new (" " org_id INTEGER NOT NULL REFERENCES orgs(id)," " id INTEGER PRIMARY KEY," " fiscal_year_id INTEGER NOT NULL," " series TEXT NOT NULL," " number INTEGER NOT NULL CHECK (number > 0)," " date TEXT NOT NULL CHECK (date LIKE '____-__-__')," " description TEXT NOT NULL CHECK (length(description) > 0)," " source TEXT NOT NULL DEFAULT 'manual'" " CHECK (source IN ('manual','agent','sie_import','system','ib'," " 'invoice','payroll','payroll_tax'))," " client_ref TEXT," " corrects_voucher_id INTEGER," " created_at TEXT NOT NULL," " created_by_user INTEGER NOT NULL REFERENCES users(id)," " created_by_token INTEGER REFERENCES api_tokens(id)," " hash_prev BLOB NOT NULL CHECK (length(hash_prev) = 32)," " hash BLOB NOT NULL CHECK (length(hash) = 32)," " UNIQUE (org_id, id)," " UNIQUE (org_id, fiscal_year_id, series, number)," " UNIQUE (org_id, client_ref)," " FOREIGN KEY (org_id, fiscal_year_id)" " REFERENCES fiscal_years(org_id, id)," " FOREIGN KEY (org_id, corrects_voucher_id)" " REFERENCES vouchers(org_id, id)" ") STRICT;" "INSERT INTO vouchers_new(org_id,id,fiscal_year_id,series,number,date," " description,source,client_ref,corrects_voucher_id,created_at," " created_by_user,created_by_token,hash_prev,hash)" " SELECT org_id,id,fiscal_year_id,series,number,date,description,source," " client_ref,corrects_voucher_id,created_at,created_by_user," " created_by_token,hash_prev,hash FROM vouchers;" "DROP TABLE vouchers;" "ALTER TABLE vouchers_new RENAME TO vouchers;" "CREATE INDEX idx_vouchers_date ON vouchers(org_id, date);" "CREATE INDEX idx_vouchers_fy" " ON vouchers(org_id, fiscal_year_id, series, number);" "CREATE TRIGGER vouchers_no_update BEFORE UPDATE ON vouchers" " BEGIN SELECT RAISE(ABORT, 'vouchers are append-only'); END;" "CREATE TRIGGER vouchers_no_delete BEFORE DELETE ON vouchers" " BEGIN SELECT RAISE(ABORT, 'vouchers are append-only'); END;", err); } static int db_upgrade(sqlite3 *db, int from, char **err) { if (db_exec(db, "BEGIN IMMEDIATE", err) != 0) return -1; if (from < 2 && db_exec(db, SCHEMA_V2, err) != 0) { db_exec(db, "ROLLBACK", NULL); return -1; } if (from < 3 && db_upgrade_v3(db, err) != 0) { db_exec(db, "ROLLBACK", NULL); return -1; } if (from < 4 && db_upgrade_v4(db, err) != 0) { db_exec(db, "ROLLBACK", NULL); return -1; } if (from < 5 && db_upgrade_v5(db, err) != 0) { db_exec(db, "ROLLBACK", NULL); return -1; } if (from < 6 && db_upgrade_v6(db, err) != 0) { db_exec(db, "ROLLBACK", NULL); return -1; } if (from < 7 && db_upgrade_v7(db, err) != 0) { db_exec(db, "ROLLBACK", NULL); return -1; } if (from < 8 && db_upgrade_v8(db, err) != 0) { db_exec(db, "ROLLBACK", NULL); return -1; } if (from < 9 && db_upgrade_v9(db, err) != 0) { db_exec(db, "ROLLBACK", NULL); return -1; } if (from < 10 && db_upgrade_v10(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); int rc = db_exec(db, sql, err); sqlite3_free(sql); if (rc != 0) { db_exec(db, "ROLLBACK", NULL); return -1; } if (db_exec(db, "COMMIT", err) != 0) return -1; log_info("database schema upgraded from version %d to %d", from, BOKF_SCHEMA_VERSION); return 0; } static int mkdir_p(const char *path, mode_t mode) { char tmp[4096]; if (!path || strlen(path) >= sizeof tmp) return -1; strcpy(tmp, path); for (char *p = tmp + 1; *p; p++) { if (*p == '/') { *p = '\0'; if (mkdir(tmp, mode) != 0 && errno != EEXIST) return -1; *p = '/'; } } if (mkdir(tmp, mode) != 0 && errno != EEXIST) return -1; return 0; } static int pre_migration_snapshot(sqlite3 *db, int old_version, char **err) { if (mkdir_p(g_cfg.backup_dir, 0700) != 0) { set_err(err, "pre-migration snapshot: cannot create backup directory %s", g_cfg.backup_dir); return -1; } char stamp[32]; time_t t = (time_t)util_now(); struct tm tm; gmtime_r(&t, &tm); strftime(stamp, sizeof stamp, "%Y%m%dT%H%M%SZ", &tm); char path[4096]; for (int n = 0;; n++) { int len = n == 0 ? snprintf(path, sizeof path, "%s/pre-migration-v%d-%s.db", g_cfg.backup_dir, old_version, stamp) : snprintf(path, sizeof path, "%s/pre-migration-v%d-%s-%d.db", g_cfg.backup_dir, old_version, stamp, n); if (len < 0 || (size_t)len >= sizeof path) { set_err(err, "pre-migration snapshot path is too long"); return -1; } if (access(path, F_OK) != 0) break; if (n >= 1000) { set_err(err, "cannot find a free pre-migration snapshot name in %s", g_cfg.backup_dir); return -1; } } char *sql = sqlite3_mprintf("VACUUM INTO %Q", path); if (!sql) { set_err(err, "out of memory"); return -1; } int rc = db_exec(db, sql, err); sqlite3_free(sql); if (rc != 0) { set_err(err, "pre-migration snapshot to %s failed: %s", path, err && *err ? *err : sqlite3_errmsg(db)); return -1; } log_info("pre-migration snapshot: %s", path); return 0; } static int db_foreign_key_check(sqlite3 *db, char **err) { sqlite3_stmt *st = NULL; if (sqlite3_prepare_v2(db, "PRAGMA foreign_key_check", -1, &st, NULL) != SQLITE_OK) { set_err(err, "database error"); return -1; } if (sqlite3_step(st) == SQLITE_ROW) { const char *table = (const char *)sqlite3_column_text(st, 0); int64_t rowid = sqlite3_column_int64(st, 1); set_err(err, "foreign key violation in %s row %lld after migration", table ? table : "?", (long long)rowid); sqlite3_finalize(st); return -1; } sqlite3_finalize(st); return 0; } int db_open(const char *path, sqlite3 **out, char **err) { sqlite3 *db = NULL; int rc = sqlite3_open_v2(path, &db, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, NULL); if (rc != SQLITE_OK) { set_err(err, "cannot open database %s: %s", path, db ? sqlite3_errmsg(db) : "unknown error"); sqlite3_close(db); return -1; } sqlite3_extended_result_codes(db, 1); char pragmas[512]; snprintf(pragmas, sizeof pragmas, "PRAGMA journal_mode=WAL;" "PRAGMA foreign_keys=ON;" "PRAGMA busy_timeout=5000;" "PRAGMA synchronous=%s;" "PRAGMA wal_autocheckpoint=1000;", g_cfg.synchronous ? g_cfg.synchronous : "FULL"); if (db_exec(db, pragmas, err) != 0) { sqlite3_close(db); return -1; } sqlite3_stmt *st = NULL; rc = sqlite3_prepare_v2(db, "SELECT value FROM meta WHERE key='schema_version'", -1, &st, NULL); if (rc == SQLITE_OK && sqlite3_step(st) == SQLITE_ROW) { int version = atoi((const char *)sqlite3_column_text(st, 0)); sqlite3_finalize(st); if (version > BOKF_SCHEMA_VERSION) { set_err(err, "database schema version %d is newer than this binary (%d)", version, BOKF_SCHEMA_VERSION); sqlite3_close(db); return -1; } if (version < BOKF_SCHEMA_VERSION) { if (pre_migration_snapshot(db, version, err) != 0) { sqlite3_close(db); return -1; } /* v9/v10 rebuild the vouchers table; foreign keys are re-checked right after the migration and re-enabled for normal use */ if (db_exec(db, "PRAGMA foreign_keys=OFF", err) != 0) { sqlite3_close(db); return -1; } if (db_upgrade(db, version, err) != 0) { sqlite3_close(db); return -1; } if (db_foreign_key_check(db, err) != 0) { sqlite3_close(db); return -1; } if (db_exec(db, "PRAGMA foreign_keys=ON", err) != 0) { sqlite3_close(db); return -1; } } } else { sqlite3_finalize(st); if (db_migrate(db, err) != 0) { sqlite3_close(db); return -1; } } *out = db; return 0; } int64_t db_last_id(sqlite3 *db) { return sqlite3_last_insert_rowid(db); } int64_t db_count(sqlite3 *db, const char *sql) { sqlite3_stmt *st = NULL; int64_t n = -1; if (sqlite3_prepare_v2(db, sql, -1, &st, NULL) == SQLITE_OK && sqlite3_step(st) == SQLITE_ROW) n = sqlite3_column_int64(st, 0); sqlite3_finalize(st); return n; } int db_create_user(sqlite3 *db, const char *username, const char *display_name, const char *password, int is_admin, int64_t *out_id, char **err) { if (!username || !*username) { set_err(err, "username is required"); return -1; } char *phc = NULL; if (auth_hash_password(password, &phc) != 0) { set_err(err, "password hashing failed"); return -1; } char ts[32]; util_iso8601(util_now(), ts, sizeof ts); sqlite3_stmt *st = NULL; int rc = sqlite3_prepare_v2( db, "INSERT INTO users(username,display_name,pw_hash,is_admin,created_at)" " VALUES(?1,?2,?3,?4,?5)", -1, &st, NULL); if (rc != SQLITE_OK) { set_err(err, "prepare failed: %s", sqlite3_errmsg(db)); free(phc); return -1; } sqlite3_bind_text(st, 1, username, -1, SQLITE_TRANSIENT); sqlite3_bind_text(st, 2, display_name && *display_name ? display_name : username, -1, SQLITE_TRANSIENT); sqlite3_bind_text(st, 3, phc, -1, SQLITE_TRANSIENT); sqlite3_bind_int(st, 4, is_admin ? 1 : 0); sqlite3_bind_text(st, 5, ts, -1, SQLITE_TRANSIENT); rc = sqlite3_step(st); sqlite3_finalize(st); free(phc); if (rc != SQLITE_DONE) { if ((rc & 0xff) == SQLITE_CONSTRAINT) set_err(err, "username already exists"); else set_err(err, "insert failed: %s", sqlite3_errmsg(db)); return -1; } if (out_id) *out_id = db_last_id(db); return 0; } char *db_setting(sqlite3 *db, int64_t org_id, const char *key) { sqlite3_stmt *st = NULL; char *value = NULL; if (sqlite3_prepare_v2( db, "SELECT value FROM settings WHERE org_id=?1 AND key=?2", -1, &st, NULL) != SQLITE_OK) return NULL; sqlite3_bind_int64(st, 1, org_id); sqlite3_bind_text(st, 2, key, -1, SQLITE_TRANSIENT); if (sqlite3_step(st) == SQLITE_ROW) { const unsigned char *v = sqlite3_column_text(st, 0); if (v) value = xstrdup((const char *)v); } sqlite3_finalize(st); return value; } char *db_membership_role(sqlite3 *db, int64_t org_id, int64_t user_id) { sqlite3_stmt *st = NULL; char *role = NULL; if (sqlite3_prepare_v2( db, "SELECT role FROM memberships WHERE org_id=?1 AND user_id=?2", -1, &st, NULL) != SQLITE_OK) return NULL; sqlite3_bind_int64(st, 1, org_id); sqlite3_bind_int64(st, 2, user_id); if (sqlite3_step(st) == SQLITE_ROW) { const unsigned char *r = sqlite3_column_text(st, 0); if (r) role = xstrdup((const char *)r); } sqlite3_finalize(st); return role; }