Initial commit: daemon, clients, docs, Docker deploy pipelinev0.1.0

1 files changed, 519 insertions, 0 deletions

diff --git a/src/db.c b/src/db.c
new file mode 100644
index 0000000..c52d763
--- /dev/null
+++ b/src/db.c
@@ -0,0 +1,519 @@
+#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 "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,"
+    "  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. */
+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;
+    }
+    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;
+}