path: root/src/db.c

#include "db.h"

#include <errno.h>
#include <stdarg.h>
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <sys/stat.h>
#include <time.h>
#include <unistd.h>

#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')),"
    "  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";

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;
    }
    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);
}

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;
    }
    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 rebuilds 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;
}