summaryrefslogtreecommitdiff
path: root/src/db.c
diff options
context:
space:
mode:
Diffstat (limited to 'src/db.c')
-rw-r--r--src/db.c519
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;
+}
generated by cgit v1.3 (git 2.53.0) at 2026-09-21 15:16:39 +0000