path: root/src/db.c

#include "db.h"

#include <stdarg.h>
#include <stdio.h>
#include <stdlib.h>
#include <string.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,"
    "  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 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,"
    "  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')),"
    "  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 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";

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

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

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 (db_upgrade(db, version, 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;
}