db: schema v9 for invoicing, vouchers source rebuild

4 files changed, 277 insertions, 19 deletions

diff --git a/src/db.c b/src/db.c
index 7e23cb0..57e1efd 100644
--- a/src/db.c
+++ b/src/db.c
@@ -144,7 +144,8 @@ static const char SCHEMA_V1[] =
     "  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')),"
+    "    CHECK (source IN ('manual','agent','sie_import','system','ib',"
+    "                      'invoice')),"
     "  client_ref          TEXT,"
     "  corrects_voucher_id INTEGER,"
     "  created_at          TEXT NOT NULL,"
@@ -304,6 +305,81 @@ static const char SCHEMA_V1[] =
     "  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[] =
@@ -535,6 +611,137 @@ static int db_upgrade_v8(sqlite3 *db, char **err)
                    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)
@@ -567,6 +774,10 @@ static int db_upgrade(sqlite3 *db, int from, char **err)
         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);
@@ -651,6 +862,26 @@ static int pre_migration_snapshot(sqlite3 *db, int old_version, char **err)
     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;
@@ -694,10 +925,24 @@ int db_open(const char *path, sqlite3 **out, char **err)
                 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);
diff --git a/src/db.h b/src/db.h
index 8a3cb24..38fc6dc 100644
--- a/src/db.h
+++ b/src/db.h
@@ -4,7 +4,7 @@
 #include <sqlite3.h>
 #include <stdint.h>
 
-#define BOKF_SCHEMA_VERSION 8
+#define BOKF_SCHEMA_VERSION 9
 
 int db_open(const char *path, sqlite3 **out, char **err);
 int db_migrate(sqlite3 *db, char **err);
diff --git a/src/ledger.c b/src/ledger.c
index 2de794e..a22e15e 100644
--- a/src/ledger.c
+++ b/src/ledger.c
@@ -345,6 +345,12 @@ static int db_step(sqlite3 *db, sqlite3_stmt *st, char *errbuf, size_t n)
     return rc;
 }
 
+static void tx_rollback(sqlite3 *db, int owner)
+{
+    if (owner)
+        sqlite3_exec(db, "ROLLBACK", NULL, NULL, NULL);
+}
+
 int ledger_post(sqlite3 *db, const struct ledger_post_opts *o,
                 struct ledger_error *e, char **out_result_json)
 {
@@ -417,9 +423,12 @@ int ledger_post(sqlite3 *db, const struct ledger_post_opts *o,
                     (long long)(sum_debit - sum_credit));
     }
 
-    if (sqlite3_exec(db, "BEGIN IMMEDIATE", NULL, NULL, NULL) != SQLITE_OK) {
-        free(series_owned);
-        return fail(e, "DB_BUSY", "could not start transaction");
+    if (!o->already_in_tx) {
+        if (sqlite3_exec(db, "BEGIN IMMEDIATE", NULL, NULL, NULL) !=
+            SQLITE_OK) {
+            free(series_owned);
+            return fail(e, "DB_BUSY", "could not start transaction");
+        }
     }
 
     int rc = -1;
@@ -443,7 +452,7 @@ int ledger_post(sqlite3 *db, const struct ledger_post_opts *o,
     sqlite3_bind_text(st, 2, o->date, -1, SQLITE_TRANSIENT);
     if (sqlite3_step(st) != SQLITE_ROW) {
         sqlite3_finalize(st);
-        sqlite3_exec(db, "ROLLBACK", NULL, NULL, NULL);
+        tx_rollback(db, !o->already_in_tx);
         free(series_owned);
         return fail(e, "DATE_OUT_OF_RANGE",
                     "no fiscal year contains %s; open one first", o->date);
@@ -462,13 +471,13 @@ int ledger_post(sqlite3 *db, const struct ledger_post_opts *o,
     sqlite3_finalize(st);
 
     if (strcmp(fy_status, "closed") == 0) {
-        sqlite3_exec(db, "ROLLBACK", NULL, NULL, NULL);
+        tx_rollback(db, !o->already_in_tx);
         free(series_owned);
         return fail(e, "FISCAL_YEAR_CLOSED", "fiscal year %s is closed",
                     fy_label);
     }
     if (locked_until[0] && strcmp(o->date, locked_until) <= 0) {
-        sqlite3_exec(db, "ROLLBACK", NULL, NULL, NULL);
+        tx_rollback(db, !o->already_in_tx);
         free(series_owned);
         return fail(e, "PERIOD_LOCKED", "period is locked through %s",
                     locked_until);
@@ -490,7 +499,7 @@ int ledger_post(sqlite3 *db, const struct ledger_post_opts *o,
         if (sqlite3_step(st) != SQLITE_ROW) {
             sqlite3_finalize(st);
             free(acct_ids);
-            sqlite3_exec(db, "ROLLBACK", NULL, NULL, NULL);
+            tx_rollback(db, !o->already_in_tx);
             free(series_owned);
             return fail(e, "ACCOUNT_NOT_FOUND", "account %s does not exist",
                         o->rows[i].account);
@@ -500,7 +509,7 @@ int ledger_post(sqlite3 *db, const struct ledger_post_opts *o,
         sqlite3_finalize(st);
         if (!active) {
             free(acct_ids);
-            sqlite3_exec(db, "ROLLBACK", NULL, NULL, NULL);
+            tx_rollback(db, !o->already_in_tx);
             free(series_owned);
             return fail(e, "ACCOUNT_INACTIVE", "account %s is inactive",
                         o->rows[i].account);
@@ -524,7 +533,7 @@ int ledger_post(sqlite3 *db, const struct ledger_post_opts *o,
         sqlite3_finalize(st);
         if (!exists) {
             free(acct_ids);
-            sqlite3_exec(db, "ROLLBACK", NULL, NULL, NULL);
+            tx_rollback(db, !o->already_in_tx);
             free(series_owned);
             return fail(e, "NOT_FOUND", "voucher %lld does not exist",
                         (long long)o->corrects_voucher_id);
@@ -624,7 +633,7 @@ int ledger_post(sqlite3 *db, const struct ledger_post_opts *o,
         *out_result_json = yyjson_mut_write(doc, 0, NULL);
         yyjson_mut_doc_free(doc);
         free(acct_ids);
-        sqlite3_exec(db, "ROLLBACK", NULL, NULL, NULL);
+        tx_rollback(db, !o->already_in_tx);
         free(series_owned);
         return 0;
     }
@@ -722,13 +731,13 @@ int ledger_post(sqlite3 *db, const struct ledger_post_opts *o,
         }
         sqlite3_finalize(st);
         if (!exists) {
-            sqlite3_exec(db, "ROLLBACK", NULL, NULL, NULL);
+            tx_rollback(db, !o->already_in_tx);
             free(series_owned);
             return fail(e, "NOT_FOUND", "attachment %lld does not exist",
                         (long long)aid);
         }
         if (linked) {
-            sqlite3_exec(db, "ROLLBACK", NULL, NULL, NULL);
+            tx_rollback(db, !o->already_in_tx);
             free(series_owned);
             return fail(e, "CONFLICT", "attachment %lld is already linked",
                         (long long)aid);
@@ -783,10 +792,13 @@ int ledger_post(sqlite3 *db, const struct ledger_post_opts *o,
         }
     }
 
-    if (sqlite3_exec(db, "COMMIT", NULL, NULL, NULL) != SQLITE_OK) {
-        free(result_json);
-        free(series_owned);
-        return fail(e, "DB_BUSY", "commit failed: %s", sqlite3_errmsg(db));
+    if (!o->already_in_tx) {
+        if (sqlite3_exec(db, "COMMIT", NULL, NULL, NULL) != SQLITE_OK) {
+            free(result_json);
+            free(series_owned);
+            return fail(e, "DB_BUSY", "commit failed: %s",
+                        sqlite3_errmsg(db));
+        }
     }
     free(series_owned);
     *out_result_json = result_json;
@@ -794,7 +806,7 @@ int ledger_post(sqlite3 *db, const struct ledger_post_opts *o,
 
 busy:
     free(series_owned);
-    sqlite3_exec(db, "ROLLBACK", NULL, NULL, NULL);
+    tx_rollback(db, !o->already_in_tx);
     e->code = "DB_BUSY";
     if (!e->msg[0])
         snprintf(e->msg, sizeof e->msg, "database error: %s",
diff --git a/src/ledger.h b/src/ledger.h
index cf8f9b1..dee8be0 100644
--- a/src/ledger.h
+++ b/src/ledger.h
@@ -34,6 +34,7 @@ struct ledger_post_opts {
     const char *client_ref; /* NULL = none */
     int dry_run;
     const char *source; /* NULL -> "manual" or "agent" */
+    int already_in_tx;  /* caller owns the transaction (no BEGIN/COMMIT) */
 };
 
 int ledger_post(sqlite3 *db, const struct ledger_post_opts *o,