payroll: employees, tax tables and the monthly run (schema v10)

1 files changed, 150 insertions, 2 deletions

diff --git a/src/db.c b/src/db.c
index 57e1efd..f77d9e5 100644
--- a/src/db.c
+++ b/src/db.c
@@ -145,7 +145,7 @@ static const char SCHEMA_V1[] =
     "  description         TEXT NOT NULL CHECK (length(description) > 0),"
     "  source              TEXT NOT NULL DEFAULT 'manual'"
     "    CHECK (source IN ('manual','agent','sie_import','system','ib',"
-    "                      'invoice')),"
+    "                      'invoice','payroll','payroll_tax')),"
     "  client_ref          TEXT,"
     "  corrects_voucher_id INTEGER,"
     "  created_at          TEXT NOT NULL,"
@@ -413,6 +413,92 @@ static const char SCHEMA_V2[] =
     "CREATE INDEX idx_template_rows ON voucher_template_rows(org_id,"
     " template_id, line_no);\n";
 
+/* v10: payroll — the employee register, the monthly runs with their lines
+   and Skatteverket's national tax tables. tax_tables and tax_table_meta are
+   reference data, not tenant data, so they carry no org_id. Shared by the
+   fresh schema and the v10 migration. */
+static const char SCHEMA_PAYROLL[] =
+    "CREATE TABLE IF NOT EXISTS employees ("
+    "  org_id             INTEGER NOT NULL REFERENCES orgs(id),"
+    "  id                 INTEGER PRIMARY KEY,"
+    "  name               TEXT NOT NULL,"
+    "  personal_no_enc    TEXT NOT NULL,"
+    "  address            TEXT NOT NULL DEFAULT '',"
+    "  postal_code        TEXT NOT NULL DEFAULT '',"
+    "  city               TEXT NOT NULL DEFAULT '',"
+    "  bank_account       TEXT NOT NULL DEFAULT '',"
+    "  salary_account     TEXT NOT NULL DEFAULT '7210',"
+    "  monthly_salary_ore INTEGER NOT NULL DEFAULT 0"
+    "    CHECK (monthly_salary_ore >= 0),"
+    "  tax_table          INTEGER NOT NULL DEFAULT 30"
+    "    CHECK (tax_table BETWEEN 29 AND 42),"
+    "  tax_column         INTEGER NOT NULL DEFAULT 1"
+    "    CHECK (tax_column BETWEEN 1 AND 6),"
+    "  active             INTEGER NOT NULL DEFAULT 1 CHECK (active IN (0,1)),"
+    "  created_at         TEXT NOT NULL,"
+    "  updated_at         TEXT,"
+    "  UNIQUE (org_id, id)"
+    ") STRICT;\n"
+
+    "CREATE TABLE IF NOT EXISTS payroll_runs ("
+    "  org_id             INTEGER NOT NULL REFERENCES orgs(id),"
+    "  id                 INTEGER PRIMARY KEY,"
+    "  fiscal_year_id     INTEGER NOT NULL,"
+    "  period             TEXT NOT NULL,"
+    "  pay_date           TEXT NOT NULL,"
+    "  status             TEXT NOT NULL DEFAULT 'posted'"
+    "    CHECK (status IN ('posted','paid')),"
+    "  gross_ore          INTEGER NOT NULL,"
+    "  tax_ore            INTEGER NOT NULL,"
+    "  avgifter_ore       INTEGER NOT NULL,"
+    "  net_ore            INTEGER NOT NULL,"
+    "  voucher_id         INTEGER,"
+    "  payment_voucher_id INTEGER,"
+    "  created_at         TEXT NOT NULL,"
+    "  created_by         INTEGER NOT NULL REFERENCES users(id),"
+    "  UNIQUE (org_id, id),"
+    "  UNIQUE (org_id, period, pay_date),"
+    "  FOREIGN KEY (org_id, fiscal_year_id)"
+    "    REFERENCES fiscal_years(org_id, id),"
+    "  FOREIGN KEY (org_id, voucher_id) REFERENCES vouchers(org_id, id),"
+    "  FOREIGN KEY (org_id, payment_voucher_id) REFERENCES vouchers(org_id, id)"
+    ") STRICT;\n"
+
+    "CREATE TABLE IF NOT EXISTS payroll_run_lines ("
+    "  org_id       INTEGER NOT NULL,"
+    "  id           INTEGER PRIMARY KEY,"
+    "  run_id       INTEGER NOT NULL,"
+    "  employee_id  INTEGER NOT NULL,"
+    "  gross_ore    INTEGER NOT NULL,"
+    "  tax_ore      INTEGER NOT NULL,"
+    "  avgifter_ore INTEGER NOT NULL,"
+    "  net_ore      INTEGER NOT NULL,"
+    "  tax_table    INTEGER NOT NULL,"
+    "  tax_column   INTEGER NOT NULL,"
+    "  UNIQUE (org_id, id),"
+    "  UNIQUE (org_id, run_id, employee_id),"
+    "  FOREIGN KEY (org_id, run_id) REFERENCES payroll_runs(org_id, id),"
+    "  FOREIGN KEY (org_id, employee_id) REFERENCES employees(org_id, id)"
+    ") STRICT;\n"
+
+    "CREATE TABLE IF NOT EXISTS tax_tables ("
+    "  in_year         INTEGER NOT NULL,"
+    "  table_no        INTEGER NOT NULL,"
+    "  column_no       INTEGER NOT NULL,"
+    "  income_from_ore INTEGER NOT NULL,"
+    "  income_to_ore   INTEGER,"
+    "  tax_ore         INTEGER NOT NULL,"
+    "  pct             INTEGER,"
+    "  PRIMARY KEY (in_year, table_no, column_no, income_from_ore)"
+    ") STRICT;\n"
+
+    "CREATE TABLE IF NOT EXISTS tax_table_meta ("
+    "  in_year    INTEGER PRIMARY KEY,"
+    "  source_url TEXT NOT NULL,"
+    "  sha256     BLOB NOT NULL CHECK (length(sha256) = 32),"
+    "  fetched_at TEXT NOT NULL"
+    ") STRICT;\n";
+
 static void set_err(char **err, const char *fmt, ...)
     __attribute__((format(printf, 2, 3)));
 
@@ -453,6 +539,10 @@ int db_migrate(sqlite3 *db, char **err)
         db_exec(db, "ROLLBACK", NULL);
         return -1;
     }
+    if (db_exec(db, SCHEMA_PAYROLL, err) != 0) {
+        db_exec(db, "ROLLBACK", NULL);
+        return -1;
+    }
     char ts[32];
     util_iso8601(util_now(), ts, sizeof ts);
     char *sql = sqlite3_mprintf(
@@ -742,6 +832,60 @@ static int db_upgrade_v9(sqlite3 *db, char **err)
         err);
 }
 
+/* v10: payroll — the employee register, the monthly runs with their lines
+   and Skatteverket's national tax tables, plus 'payroll'/'payroll_tax' as
+   voucher sources. Widening that CHECK needs the same table rebuild as v9;
+   db_open disables foreign keys for the migration and re-checks them. */
+static int db_upgrade_v10(sqlite3 *db, char **err)
+{
+    if (db_exec(db, SCHEMA_PAYROLL, 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','payroll','payroll_tax')),"
+        " 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)
@@ -778,6 +922,10 @@ static int db_upgrade(sqlite3 *db, int from, char **err)
         db_exec(db, "ROLLBACK", NULL);
         return -1;
     }
+    if (from < 10 && db_upgrade_v10(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);
@@ -925,7 +1073,7 @@ 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
+            /* v9/v10 rebuild 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);