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

1 files changed, 1254 insertions, 0 deletions

diff --git a/src/cmd_payroll.c b/src/cmd_payroll.c
new file mode 100644
index 0000000..e7cafa3
--- /dev/null
+++ b/src/cmd_payroll.c
@@ -0,0 +1,1254 @@
+#include "commands.h"
+#include "cmd_util.h"
+
+#include <stdio.h>
+#include <stdlib.h>
+#include <string.h>
+#include <time.h>
+
+#include "audit.h"
+#include "db.h"
+#include "ledger.h"
+#include "secret.h"
+#include "tax_table.h"
+#include "util.h"
+
+/* ------------------------------------------------------------------ */
+/* payroll settings                                                    */
+/* ------------------------------------------------------------------ */
+
+#define NET_SALARY_ACCOUNT "1930"
+
+struct payroll_cfg {
+    char salary[16];
+    char tax[16];
+    char avgift[16];
+    char liability[16];
+    char payment[16];
+    int64_t rate_bp;
+};
+
+static const struct {
+    const char *key;
+    const char *def;
+} PAYROLL_SETTINGS[] = {
+    { "payroll_salary_account", "7210" },
+    { "payroll_tax_account", "2710" },
+    { "payroll_avgift_account", "7510" },
+    { "payroll_avgift_liability", "2731" },
+    { "payroll_tax_payment_account", "1630" },
+    { "payroll_avgift_rate_bp", "3142" },
+};
+
+static const size_t PAYROLL_SETTINGS_N =
+    sizeof PAYROLL_SETTINGS / sizeof PAYROLL_SETTINGS[0];
+
+static int payroll_setting_index(const char *key)
+{
+    if (!key)
+        return -1;
+    for (size_t i = 0; i < PAYROLL_SETTINGS_N; i++)
+        if (strcmp(PAYROLL_SETTINGS[i].key, key) == 0)
+            return (int)i;
+    return -1;
+}
+
+static void payroll_cfg_load(struct req *r, struct payroll_cfg *c)
+{
+    char *v = NULL;
+    v = db_setting(r->db, r->org_id, "payroll_salary_account");
+    snprintf(c->salary, sizeof c->salary, "%s",
+             v && is_digits(v) && strlen(v) <= 10 ? v : "7210");
+    free(v);
+    v = db_setting(r->db, r->org_id, "payroll_tax_account");
+    snprintf(c->tax, sizeof c->tax, "%s",
+             v && is_digits(v) && strlen(v) <= 10 ? v : "2710");
+    free(v);
+    v = db_setting(r->db, r->org_id, "payroll_avgift_account");
+    snprintf(c->avgift, sizeof c->avgift, "%s",
+             v && is_digits(v) && strlen(v) <= 10 ? v : "7510");
+    free(v);
+    v = db_setting(r->db, r->org_id, "payroll_avgift_liability");
+    snprintf(c->liability, sizeof c->liability, "%s",
+             v && is_digits(v) && strlen(v) <= 10 ? v : "2731");
+    free(v);
+    v = db_setting(r->db, r->org_id, "payroll_tax_payment_account");
+    snprintf(c->payment, sizeof c->payment, "%s",
+             v && is_digits(v) && strlen(v) <= 10 ? v : "1630");
+    free(v);
+    c->rate_bp = 3142;
+    v = db_setting(r->db, r->org_id, "payroll_avgift_rate_bp");
+    if (v && is_digits(v)) {
+        long rate = strtol(v, NULL, 10);
+        if (rate >= 1 && rate <= 10000)
+            c->rate_bp = rate;
+    }
+    free(v);
+}
+
+static yyjson_mut_val *h_payroll_settings_get(struct req *r)
+{
+    struct payroll_cfg c;
+    payroll_cfg_load(r, &c);
+    yyjson_mut_val *o = yyjson_mut_obj(r->rdoc);
+    yyjson_mut_obj_add_strcpy(r->rdoc, o, "payroll_salary_account", c.salary);
+    yyjson_mut_obj_add_strcpy(r->rdoc, o, "payroll_tax_account", c.tax);
+    yyjson_mut_obj_add_strcpy(r->rdoc, o, "payroll_avgift_account", c.avgift);
+    yyjson_mut_obj_add_strcpy(r->rdoc, o, "payroll_avgift_liability",
+                              c.liability);
+    yyjson_mut_obj_add_strcpy(r->rdoc, o, "payroll_tax_payment_account",
+                              c.payment);
+    yyjson_mut_obj_add_int(r->rdoc, o, "payroll_avgift_rate_bp", c.rate_bp);
+    return o;
+}
+
+static yyjson_mut_val *h_payroll_settings_set(struct req *r)
+{
+    const char *key = arg_str(r->args, "key");
+    const char *value = arg_str(r->args, "value");
+    if (payroll_setting_index(key) < 0)
+        return fail(r, "UNSUPPORTED", "unknown payroll setting");
+    if (!value || !*value)
+        return fail(r, "INVALID_ARGS", "value is required");
+    if (!is_digits(value))
+        return failf(r, "INVALID_ARGS", "%s must be digits only", key);
+    if (strcmp(key, "payroll_avgift_rate_bp") == 0) {
+        if (strlen(value) > 5)
+            return fail(r, "INVALID_ARGS",
+                        "payroll_avgift_rate_bp must be 1-10000");
+        long rate = strtol(value, NULL, 10);
+        if (rate < 1 || rate > 10000)
+            return fail(r, "INVALID_ARGS",
+                        "payroll_avgift_rate_bp must be 1-10000");
+    } else if (strlen(value) > 10) {
+        return failf(r, "INVALID_ARGS", "%s must be 1-10 digits", key);
+    }
+    if (r->dry_run) {
+        yyjson_mut_val *o = yyjson_mut_obj(r->rdoc);
+        yyjson_mut_obj_add_strcpy(r->rdoc, o, "key", key);
+        yyjson_mut_obj_add_strcpy(r->rdoc, o, "value", value);
+        yyjson_mut_obj_add_bool(r->rdoc, o, "dry_run", true);
+        return o;
+    }
+    sqlite3_stmt *st = NULL;
+    if (sqlite3_prepare_v2(
+            r->db,
+            "INSERT INTO settings(org_id,key,value) VALUES(?1,?2,?3)"
+            " ON CONFLICT(org_id,key) DO UPDATE SET value=excluded.value",
+            -1, &st, NULL) != SQLITE_OK)
+        return db_error(r);
+    sqlite3_bind_int64(st, 1, r->org_id);
+    sqlite3_bind_text(st, 2, key, -1, SQLITE_TRANSIENT);
+    sqlite3_bind_text(st, 3, value, -1, SQLITE_TRANSIENT);
+    int rc = sqlite3_step(st);
+    sqlite3_finalize(st);
+    if (rc != SQLITE_DONE)
+        return db_sqlite_error(r);
+    char *reqjson = audit_args_json(r->args);
+    audit_append(r->db, r->org_id, r->sess->user_id, r->sess->token_id,
+                 "payroll.settings_set", reqjson, "OK", NULL);
+    free(reqjson);
+    yyjson_mut_val *o = yyjson_mut_obj(r->rdoc);
+    yyjson_mut_obj_add_strcpy(r->rdoc, o, "key", key);
+    yyjson_mut_obj_add_strcpy(r->rdoc, o, "value", value);
+    return o;
+}
+
+/* ------------------------------------------------------------------ */
+/* the monthly run                                                     */
+/* ------------------------------------------------------------------ */
+
+struct payroll_line {
+    int64_t employee_id;
+    char name[200];
+    char salary_account[16];
+    int tax_table;
+    int tax_column;
+    int64_t gross_ore;
+    int64_t tax_ore;
+    int64_t avgifter_ore;
+    int64_t net_ore;
+};
+
+static int payroll_current_year(void)
+{
+    time_t t = time(NULL);
+    struct tm tm;
+    gmtime_r(&t, &tm);
+    return tm.tm_year + 1900;
+}
+
+static int payroll_period_parse(const char *p, int *year, int *month)
+{
+    if (!p || strlen(p) != 7 || p[4] != '-')
+        return -1;
+    for (int i = 0; i < 7; i++) {
+        if (i == 4)
+            continue;
+        if (p[i] < '0' || p[i] > '9')
+            return -1;
+    }
+    int y = (p[0] - '0') * 1000 + (p[1] - '0') * 100 + (p[2] - '0') * 10 +
+            (p[3] - '0');
+    int m = (p[5] - '0') * 10 + (p[6] - '0');
+    if (m < 1 || m > 12)
+        return -1;
+    *year = y;
+    *month = m;
+    return 0;
+}
+
+static int payroll_period_in_range(const char *period, const char *start,
+                                   const char *end)
+{
+    int y = 0, m = 0;
+    if (payroll_period_parse(period, &y, &m) != 0)
+        return 0;
+    char first[16], last[16];
+    snprintf(first, sizeof first, "%04d-%02d-01", y, m);
+    snprintf(last, sizeof last, "%04d-%02d-%02d", y, m,
+             util_days_in_month(y, m));
+    return strcmp(start, last) <= 0 && strcmp(end, first) >= 0;
+}
+
+static int64_t payroll_avgifter(int64_t gross_ore, int64_t rate_bp)
+{
+    return (gross_ore * rate_bp + 5000) / 10000;
+}
+
+static int payroll_compute(struct req *r, int table_year,
+                           const struct payroll_cfg *cfg,
+                           struct payroll_line **out, size_t *out_n)
+{
+    *out = NULL;
+    *out_n = 0;
+    sqlite3_stmt *st = NULL;
+    if (sqlite3_prepare_v2(
+            r->db,
+            "SELECT id,name,salary_account,monthly_salary_ore,tax_table,"
+            "tax_column FROM employees WHERE org_id=?1 AND active=1"
+            " AND monthly_salary_ore>0 ORDER BY id",
+            -1, &st, NULL) != SQLITE_OK) {
+        db_error(r);
+        return -1;
+    }
+    sqlite3_bind_int64(st, 1, r->org_id);
+    struct payroll_line *lines = NULL;
+    size_t n = 0, cap = 0;
+    int rc = 0;
+    while (sqlite3_step(st) == SQLITE_ROW) {
+        if (n == cap) {
+            cap = cap ? cap * 2 : 8;
+            lines = xrealloc(lines, cap * sizeof *lines);
+        }
+        struct payroll_line *l = &lines[n];
+        memset(l, 0, sizeof *l);
+        l->employee_id = sqlite3_column_int64(st, 0);
+        snprintf(l->name, sizeof l->name, "%s",
+                 sq(sqlite3_column_text(st, 1)));
+        snprintf(l->salary_account, sizeof l->salary_account, "%s",
+                 sq(sqlite3_column_text(st, 2)));
+        l->gross_ore = sqlite3_column_int64(st, 3);
+        l->tax_table = (int)sqlite3_column_int64(st, 4);
+        l->tax_column = (int)sqlite3_column_int64(st, 5);
+        n++;
+    }
+    sqlite3_finalize(st);
+    for (size_t i = 0; i < n; i++) {
+        struct payroll_line *l = &lines[i];
+        int64_t tax = 0;
+        int look;
+        if (l->gross_ore * 12 < 100000) {
+            look = 0;
+        } else {
+            look = tax_table_lookup(r->db, table_year, l->tax_table,
+                                    l->tax_column, l->gross_ore, &tax);
+        }
+        if (look == 1) {
+            failf(r, "INVALID_ARGS",
+                  "income above the tabulated range is not supported yet"
+                  " (employee %s, table %d column %d)",
+                  l->name, l->tax_table, l->tax_column);
+            rc = -1;
+            break;
+        }
+        if (look == 2) {
+            failf(r, "INVALID_ARGS",
+                  "no stored tax table for %d table %d column %d"
+                  " (employee %s)",
+                  table_year, l->tax_table, l->tax_column, l->name);
+            rc = -1;
+            break;
+        }
+        if (look < 0) {
+            db_error(r);
+            rc = -1;
+            break;
+        }
+        l->tax_ore = tax;
+        l->avgifter_ore = payroll_avgifter(l->gross_ore, cfg->rate_bp);
+        l->net_ore = l->gross_ore - tax;
+    }
+    if (rc != 0) {
+        free(lines);
+        return -1;
+    }
+    *out = lines;
+    *out_n = n;
+    return 0;
+}
+
+static yyjson_mut_val *payroll_items_json(struct req *r,
+                                          const struct payroll_line *lines,
+                                          size_t n,
+                                          const struct payroll_cfg *cfg)
+{
+    yyjson_mut_val *items = yyjson_mut_arr(r->rdoc);
+    int64_t total_gross = 0, total_tax = 0, total_avg = 0, total_net = 0;
+    for (size_t i = 0; i < n; i++) {
+        const struct payroll_line *l = &lines[i];
+        yyjson_mut_val *o = yyjson_mut_arr_add_obj(r->rdoc, items);
+        yyjson_mut_obj_add_int(r->rdoc, o, "employee_id", l->employee_id);
+        yyjson_mut_obj_add_strcpy(r->rdoc, o, "name", l->name);
+        yyjson_mut_obj_add_strcpy(r->rdoc, o, "salary_account",
+                                  l->salary_account);
+        yyjson_mut_obj_add_int(r->rdoc, o, "tax_table", l->tax_table);
+        yyjson_mut_obj_add_int(r->rdoc, o, "tax_column", l->tax_column);
+        yyjson_mut_obj_add_int(r->rdoc, o, "gross_ore", l->gross_ore);
+        yyjson_mut_obj_add_int(r->rdoc, o, "tax_ore", l->tax_ore);
+        yyjson_mut_obj_add_int(r->rdoc, o, "avgifter_ore", l->avgifter_ore);
+        yyjson_mut_obj_add_int(r->rdoc, o, "net_ore", l->net_ore);
+        total_gross += l->gross_ore;
+        total_tax += l->tax_ore;
+        total_avg += l->avgifter_ore;
+        total_net += l->net_ore;
+    }
+    yyjson_mut_val *out = yyjson_mut_obj(r->rdoc);
+    yyjson_mut_obj_add_val(r->rdoc, out, "items", items);
+    yyjson_mut_val *totals = yyjson_mut_obj(r->rdoc);
+    yyjson_mut_obj_add_int(r->rdoc, totals, "gross_ore", total_gross);
+    yyjson_mut_obj_add_int(r->rdoc, totals, "tax_ore", total_tax);
+    yyjson_mut_obj_add_int(r->rdoc, totals, "avgifter_ore", total_avg);
+    yyjson_mut_obj_add_int(r->rdoc, totals, "net_ore", total_net);
+    yyjson_mut_obj_add_val(r->rdoc, out, "totals", totals);
+    yyjson_mut_val *accounts = yyjson_mut_obj(r->rdoc);
+    yyjson_mut_obj_add_strcpy(r->rdoc, accounts, "tax", cfg->tax);
+    yyjson_mut_obj_add_strcpy(r->rdoc, accounts, "avgift", cfg->avgift);
+    yyjson_mut_obj_add_strcpy(r->rdoc, accounts, "liability", cfg->liability);
+    yyjson_mut_obj_add_strcpy(r->rdoc, accounts, "payment", cfg->payment);
+    yyjson_mut_obj_add_strcpy(r->rdoc, accounts, "net", NET_SALARY_ACCOUNT);
+    yyjson_mut_obj_add_val(r->rdoc, out, "accounts", accounts);
+    yyjson_mut_obj_add_int(r->rdoc, out, "avgift_rate_bp", cfg->rate_bp);
+    return out;
+}
+
+static yyjson_mut_val *h_payroll_run_preview(struct req *r)
+{
+    const char *period = arg_str(r->args, "period");
+    int year = 0, month = 0;
+    if (payroll_period_parse(period, &year, &month) != 0)
+        return fail(r, "INVALID_ARGS", "period must be YYYY-MM");
+    struct payroll_cfg cfg;
+    payroll_cfg_load(r, &cfg);
+    struct payroll_line *lines = NULL;
+    size_t n = 0;
+    if (payroll_compute(r, year, &cfg, &lines, &n) != 0)
+        return NULL;
+    yyjson_mut_val *out = payroll_items_json(r, lines, n, &cfg);
+    free(lines);
+    yyjson_mut_obj_add_strcpy(r->rdoc, out, "period", period);
+    yyjson_mut_obj_add_int(r->rdoc, out, "tax_year", year);
+    return out;
+}
+
+static int payroll_run_exists(struct req *r, const char *period)
+{
+    sqlite3_stmt *st = NULL;
+    if (sqlite3_prepare_v2(
+            r->db,
+            "SELECT id FROM payroll_runs WHERE org_id=?1 AND period=?2",
+            -1, &st, NULL) != SQLITE_OK) {
+        db_error(r);
+        return -1;
+    }
+    sqlite3_bind_int64(st, 1, r->org_id);
+    sqlite3_bind_text(st, 2, period, -1, SQLITE_TRANSIENT);
+    int exists = sqlite3_step(st) == SQLITE_ROW;
+    sqlite3_finalize(st);
+    return exists;
+}
+
+static yyjson_mut_val *h_payroll_run_post(struct req *r)
+{
+    const char *period = arg_str(r->args, "period");
+    const char *pay_date = arg_str(r->args, "pay_date");
+    int year = 0, month = 0;
+    if (payroll_period_parse(period, &year, &month) != 0)
+        return fail(r, "INVALID_ARGS", "period must be YYYY-MM");
+    if (!pay_date || !util_parse_iso_date(pay_date))
+        return fail(r, "INVALID_ARGS", "pay_date must be YYYY-MM-DD");
+    int pay_year = (pay_date[0] - '0') * 1000 + (pay_date[1] - '0') * 100 +
+                   (pay_date[2] - '0') * 10 + (pay_date[3] - '0');
+
+    struct payroll_cfg cfg;
+    payroll_cfg_load(r, &cfg);
+    char fy_start[16] = "", fy_end[16] = "";
+    int64_t fy_id = 0;
+    sqlite3_stmt *st = NULL;
+    if (sqlite3_prepare_v2(
+            r->db,
+            "SELECT id,start_date,end_date FROM fiscal_years"
+            " WHERE org_id=?1 AND start_date<=?2 AND end_date>=?2",
+            -1, &st, NULL) != SQLITE_OK)
+        return db_error(r);
+    sqlite3_bind_int64(st, 1, r->org_id);
+    sqlite3_bind_text(st, 2, pay_date, -1, SQLITE_TRANSIENT);
+    if (sqlite3_step(st) == SQLITE_ROW) {
+        fy_id = sqlite3_column_int64(st, 0);
+        snprintf(fy_start, sizeof fy_start, "%s",
+                 sq(sqlite3_column_text(st, 1)));
+        snprintf(fy_end, sizeof fy_end, "%s",
+                 sq(sqlite3_column_text(st, 2)));
+    }
+    sqlite3_finalize(st);
+    if (!fy_id)
+        return failf(r, "DATE_OUT_OF_RANGE",
+                     "no fiscal year contains %s; open one first", pay_date);
+    if (!payroll_period_in_range(period, fy_start, fy_end))
+        return failf(r, "INVALID_ARGS",
+                     "period %s is not in the fiscal year that contains %s",
+                     period, pay_date);
+
+    yyjson_mut_val *res = NULL;
+    struct payroll_line *lines = NULL;
+    size_t n = 0;
+    struct ledger_row *vrows = NULL;
+    char (*descs)[256] = NULL;
+    char *voucher_json = NULL;
+    int in_tx = 0;
+
+    if (db_exec(r->db, "BEGIN IMMEDIATE", NULL) != 0)
+        return fail(r, "DB_BUSY", "could not start transaction");
+    in_tx = 1;
+    int exists = payroll_run_exists(r, period);
+    if (exists < 0)
+        goto done;
+    if (exists) {
+        failf(r, "CONFLICT", "a payroll run for period %s already exists",
+              period);
+        goto done;
+    }
+    if (payroll_compute(r, pay_year, &cfg, &lines, &n) != 0)
+        goto done;
+    if (n == 0) {
+        fail(r, "INVALID_ARGS", "no active employees with a monthly salary");
+        goto done;
+    }
+
+    size_t cap = n + 4;
+    vrows = xcalloc(cap, sizeof *vrows);
+    descs = xcalloc(n ? n : 1, 256);
+    size_t vn = 0;
+    int64_t total_gross = 0, total_tax = 0, total_avg = 0, total_net = 0;
+    for (size_t i = 0; i < n; i++) {
+        const struct payroll_line *l = &lines[i];
+        snprintf(descs[i], 256, "Lön %s", l->name);
+        vrows[vn].account = l->salary_account;
+        vrows[vn].debit_ore = l->gross_ore;
+        vrows[vn].description = descs[i];
+        vn++;
+        total_gross += l->gross_ore;
+        total_tax += l->tax_ore;
+        total_avg += l->avgifter_ore;
+        total_net += l->net_ore;
+    }
+    if (total_avg > 0) {
+        vrows[vn].account = cfg.avgift;
+        vrows[vn].debit_ore = total_avg;
+        vrows[vn].description = "Arbetsgivaravgifter";
+        vn++;
+    }
+    if (total_tax > 0) {
+        vrows[vn].account = cfg.tax;
+        vrows[vn].credit_ore = total_tax;
+        vrows[vn].description = "Personalskatt";
+        vn++;
+    }
+    if (total_net > 0) {
+        vrows[vn].account = NET_SALARY_ACCOUNT;
+        vrows[vn].credit_ore = total_net;
+        vrows[vn].description = "Nettolön";
+        vn++;
+    }
+    if (total_avg > 0) {
+        vrows[vn].account = cfg.liability;
+        vrows[vn].credit_ore = total_avg;
+        vrows[vn].description = "Avräkning sociala avgifter";
+        vn++;
+    }
+
+    char description[64];
+    snprintf(description, sizeof description, "Lönekörning %s", period);
+    struct ledger_post_opts o;
+    memset(&o, 0, sizeof o);
+    o.org_id = r->org_id;
+    o.user_id = r->sess->user_id;
+    o.token_id = r->sess->token_id;
+    o.date = pay_date;
+    o.description = description;
+    o.rows = vrows;
+    o.nrows = vn;
+    o.source = "payroll";
+    o.dry_run = r->dry_run;
+    o.already_in_tx = 1;
+    struct ledger_error e;
+    if (ledger_post(r->db, &o, &e, &voucher_json) != 0) {
+        fail(r, e.code ? e.code : "INTERNAL", e.msg);
+        goto done;
+    }
+
+    int64_t run_id = 0, voucher_id = 0;
+    char ts[32];
+    util_iso8601(util_now(), ts, sizeof ts);
+    if (!r->dry_run) {
+        if (!voucher_json) {
+            fail(r, "INTERNAL", "empty voucher result");
+            goto done;
+        }
+        yyjson_doc *vd = yyjson_read(voucher_json, strlen(voucher_json), 0);
+        if (vd) {
+            yyjson_val *idv = yyjson_obj_get(yyjson_doc_get_root(vd), "id");
+            if (idv && yyjson_is_int(idv))
+                voucher_id = yyjson_get_int(idv);
+            yyjson_doc_free(vd);
+        }
+        if (voucher_id <= 0) {
+            fail(r, "INTERNAL", "voucher id missing from the posting");
+            goto done;
+        }
+        if (sqlite3_prepare_v2(
+                r->db,
+                "INSERT INTO payroll_runs(org_id,fiscal_year_id,period,pay_date,"
+                "status,gross_ore,tax_ore,avgifter_ore,net_ore,voucher_id,"
+                "created_at,created_by)"
+                " VALUES(?1,?2,?3,?4,'posted',?5,?6,?7,?8,?9,?10,?11)",
+                -1, &st, NULL) != SQLITE_OK) {
+            db_error(r);
+            goto done;
+        }
+        sqlite3_bind_int64(st, 1, r->org_id);
+        sqlite3_bind_int64(st, 2, fy_id);
+        sqlite3_bind_text(st, 3, period, -1, SQLITE_TRANSIENT);
+        sqlite3_bind_text(st, 4, pay_date, -1, SQLITE_TRANSIENT);
+        sqlite3_bind_int64(st, 5, total_gross);
+        sqlite3_bind_int64(st, 6, total_tax);
+        sqlite3_bind_int64(st, 7, total_avg);
+        sqlite3_bind_int64(st, 8, total_net);
+        sqlite3_bind_int64(st, 9, voucher_id);
+        sqlite3_bind_text(st, 10, ts, -1, SQLITE_TRANSIENT);
+        sqlite3_bind_int64(st, 11, r->sess->user_id);
+        int rc = sqlite3_step(st);
+        sqlite3_finalize(st);
+        if (rc != SQLITE_DONE) {
+            if ((rc & 0xff) == SQLITE_CONSTRAINT)
+                failf(r, "CONFLICT",
+                      "a payroll run for period %s already exists", period);
+            else
+                db_sqlite_error(r);
+            goto done;
+        }
+        run_id = db_last_id(r->db);
+        for (size_t i = 0; i < n; i++) {
+            if (sqlite3_prepare_v2(
+                    r->db,
+                    "INSERT INTO payroll_run_lines(org_id,run_id,employee_id,"
+                    "gross_ore,tax_ore,avgifter_ore,net_ore,tax_table,"
+                    "tax_column) VALUES(?1,?2,?3,?4,?5,?6,?7,?8,?9)",
+                    -1, &st, NULL) != SQLITE_OK) {
+                db_error(r);
+                goto done;
+            }
+            sqlite3_bind_int64(st, 1, r->org_id);
+            sqlite3_bind_int64(st, 2, run_id);
+            sqlite3_bind_int64(st, 3, lines[i].employee_id);
+            sqlite3_bind_int64(st, 4, lines[i].gross_ore);
+            sqlite3_bind_int64(st, 5, lines[i].tax_ore);
+            sqlite3_bind_int64(st, 6, lines[i].avgifter_ore);
+            sqlite3_bind_int64(st, 7, lines[i].net_ore);
+            sqlite3_bind_int(st, 8, lines[i].tax_table);
+            sqlite3_bind_int(st, 9, lines[i].tax_column);
+            rc = sqlite3_step(st);
+            sqlite3_finalize(st);
+            if (rc != SQLITE_DONE) {
+                db_sqlite_error(r);
+                goto done;
+            }
+        }
+        if (sqlite3_exec(r->db, "COMMIT", NULL, NULL, NULL) != SQLITE_OK) {
+            fail(r, "DB_BUSY", "commit failed");
+            goto done;
+        }
+        in_tx = 0;
+        char *reqjson = audit_args_json(r->args);
+        audit_append(r->db, r->org_id, r->sess->user_id, r->sess->token_id,
+                     "payroll.run_post", reqjson, "OK", NULL);
+        free(reqjson);
+    }
+
+    res = payroll_items_json(r, lines, n, &cfg);
+    yyjson_mut_obj_add_strcpy(r->rdoc, res, "period", period);
+    yyjson_mut_obj_add_strcpy(r->rdoc, res, "pay_date", pay_date);
+    yyjson_mut_obj_add_int(r->rdoc, res, "fiscal_year_id", fy_id);
+    yyjson_mut_obj_add_int(r->rdoc, res, "tax_year", pay_year);
+    if (r->dry_run) {
+        yyjson_mut_obj_add_bool(r->rdoc, res, "dry_run", true);
+    } else {
+        yyjson_mut_obj_add_int(r->rdoc, res, "id", run_id);
+        yyjson_mut_obj_add_int(r->rdoc, res, "voucher_id", voucher_id);
+        yyjson_mut_obj_add_strcpy(r->rdoc, res, "status", "posted");
+    }
+
+done:
+    if (in_tx)
+        sqlite3_exec(r->db, "ROLLBACK", NULL, NULL, NULL);
+    free(voucher_json);
+    free(vrows);
+    free(descs);
+    free(lines);
+    return res;
+}
+
+#define RUN_COLUMNS                                                         \
+    "id,period,pay_date,status,gross_ore,tax_ore,avgifter_ore,net_ore,"    \
+    "COALESCE(voucher_id,0),COALESCE(payment_voucher_id,0),created_at"
+
+static yyjson_mut_val *run_json(struct req *r, sqlite3_stmt *st)
+{
+    yyjson_mut_val *o = yyjson_mut_obj(r->rdoc);
+    yyjson_mut_obj_add_int(r->rdoc, o, "id", sqlite3_column_int64(st, 0));
+    yyjson_mut_obj_add_strcpy(r->rdoc, o, "period",
+                              sq(sqlite3_column_text(st, 1)));
+    yyjson_mut_obj_add_strcpy(r->rdoc, o, "pay_date",
+                              sq(sqlite3_column_text(st, 2)));
+    yyjson_mut_obj_add_strcpy(r->rdoc, o, "status",
+                              sq(sqlite3_column_text(st, 3)));
+    yyjson_mut_obj_add_int(r->rdoc, o, "gross_ore",
+                           sqlite3_column_int64(st, 4));
+    yyjson_mut_obj_add_int(r->rdoc, o, "tax_ore", sqlite3_column_int64(st, 5));
+    yyjson_mut_obj_add_int(r->rdoc, o, "avgifter_ore",
+                           sqlite3_column_int64(st, 6));
+    yyjson_mut_obj_add_int(r->rdoc, o, "net_ore", sqlite3_column_int64(st, 7));
+    yyjson_mut_obj_add_int(r->rdoc, o, "voucher_id",
+                           sqlite3_column_int64(st, 8));
+    yyjson_mut_obj_add_int(r->rdoc, o, "payment_voucher_id",
+                           sqlite3_column_int64(st, 9));
+    yyjson_mut_obj_add_strcpy(r->rdoc, o, "created_at",
+                              sq(sqlite3_column_text(st, 10)));
+    return o;
+}
+
+static yyjson_mut_val *h_payroll_run_list(struct req *r)
+{
+    int64_t limit = 100;
+    arg_int(r->args, "limit", &limit);
+    if (limit < 1)
+        limit = 1;
+    if (limit > 1000)
+        limit = 1000;
+    sqlite3_stmt *st = NULL;
+    if (sqlite3_prepare_v2(
+            r->db,
+            "SELECT " RUN_COLUMNS ",(SELECT COUNT(*) FROM payroll_run_lines l"
+            " WHERE l.org_id=payroll_runs.org_id AND l.run_id=payroll_runs.id)"
+            " FROM payroll_runs WHERE org_id=?1"
+            " ORDER BY period DESC, id DESC LIMIT ?2",
+            -1, &st, NULL) != SQLITE_OK)
+        return db_error(r);
+    sqlite3_bind_int64(st, 1, r->org_id);
+    sqlite3_bind_int64(st, 2, limit);
+    yyjson_mut_val *items = yyjson_mut_arr(r->rdoc);
+    while (sqlite3_step(st) == SQLITE_ROW) {
+        yyjson_mut_val *o = run_json(r, st);
+        yyjson_mut_obj_add_int(r->rdoc, o, "line_count",
+                               sqlite3_column_int64(st, 11));
+        yyjson_mut_arr_add_val(items, o);
+    }
+    sqlite3_finalize(st);
+    yyjson_mut_val *out = yyjson_mut_obj(r->rdoc);
+    yyjson_mut_obj_add_val(r->rdoc, out, "items", items);
+    return out;
+}
+
+static yyjson_mut_val *h_payroll_run_get(struct req *r)
+{
+    int64_t id = 0;
+    if (!arg_int(r->args, "id", &id) || id <= 0)
+        return fail(r, "INVALID_ARGS", "id is required");
+    sqlite3_stmt *st = NULL;
+    if (sqlite3_prepare_v2(
+            r->db,
+            "SELECT " RUN_COLUMNS " FROM payroll_runs"
+            " WHERE org_id=?1 AND id=?2",
+            -1, &st, NULL) != SQLITE_OK)
+        return db_error(r);
+    sqlite3_bind_int64(st, 1, r->org_id);
+    sqlite3_bind_int64(st, 2, id);
+    if (sqlite3_step(st) != SQLITE_ROW) {
+        sqlite3_finalize(st);
+        return fail(r, "NOT_FOUND", "payroll run not found");
+    }
+    yyjson_mut_val *o = run_json(r, st);
+    sqlite3_finalize(st);
+
+    yyjson_mut_val *lines = yyjson_mut_arr(r->rdoc);
+    if (sqlite3_prepare_v2(
+            r->db,
+            "SELECT l.employee_id,e.name,l.gross_ore,l.tax_ore,l.avgifter_ore,"
+            "l.net_ore,l.tax_table,l.tax_column"
+            " FROM payroll_run_lines l JOIN employees e"
+            " ON e.org_id=l.org_id AND e.id=l.employee_id"
+            " WHERE l.org_id=?1 AND l.run_id=?2 ORDER BY e.name COLLATE NOCASE,"
+            " l.id",
+            -1, &st, NULL) != SQLITE_OK)
+        return db_error(r);
+    sqlite3_bind_int64(st, 1, r->org_id);
+    sqlite3_bind_int64(st, 2, id);
+    while (sqlite3_step(st) == SQLITE_ROW) {
+        yyjson_mut_val *lo = yyjson_mut_arr_add_obj(r->rdoc, lines);
+        yyjson_mut_obj_add_int(r->rdoc, lo, "employee_id",
+                               sqlite3_column_int64(st, 0));
+        yyjson_mut_obj_add_strcpy(r->rdoc, lo, "name",
+                                  sq(sqlite3_column_text(st, 1)));
+        yyjson_mut_obj_add_int(r->rdoc, lo, "gross_ore",
+                               sqlite3_column_int64(st, 2));
+        yyjson_mut_obj_add_int(r->rdoc, lo, "tax_ore",
+                               sqlite3_column_int64(st, 3));
+        yyjson_mut_obj_add_int(r->rdoc, lo, "avgifter_ore",
+                               sqlite3_column_int64(st, 4));
+        yyjson_mut_obj_add_int(r->rdoc, lo, "net_ore",
+                               sqlite3_column_int64(st, 5));
+        yyjson_mut_obj_add_int(r->rdoc, lo, "tax_table",
+                               sqlite3_column_int64(st, 6));
+        yyjson_mut_obj_add_int(r->rdoc, lo, "tax_column",
+                               sqlite3_column_int64(st, 7));
+    }
+    sqlite3_finalize(st);
+    yyjson_mut_obj_add_val(r->rdoc, o, "lines", lines);
+    return o;
+}
+
+/* ------------------------------------------------------------------ */
+/* AGI underlag                                                        */
+/* ------------------------------------------------------------------ */
+
+static yyjson_mut_val *h_payroll_agi(struct req *r)
+{
+    const char *period = arg_str(r->args, "period");
+    int year = 0, month = 0;
+    if (payroll_period_parse(period, &year, &month) != 0)
+        return fail(r, "INVALID_ARGS", "period must be YYYY-MM");
+    sqlite3_stmt *st = NULL;
+    if (sqlite3_prepare_v2(
+            r->db,
+            "SELECT id,pay_date FROM payroll_runs"
+            " WHERE org_id=?1 AND period=?2",
+            -1, &st, NULL) != SQLITE_OK)
+        return db_error(r);
+    sqlite3_bind_int64(st, 1, r->org_id);
+    sqlite3_bind_text(st, 2, period, -1, SQLITE_TRANSIENT);
+    int64_t run_id = 0;
+    char pay_date[16] = "";
+    if (sqlite3_step(st) == SQLITE_ROW) {
+        run_id = sqlite3_column_int64(st, 0);
+        snprintf(pay_date, sizeof pay_date, "%s",
+                 sq(sqlite3_column_text(st, 1)));
+    }
+    sqlite3_finalize(st);
+    if (!run_id)
+        return failf(r, "NOT_FOUND", "no payroll run for period %s", period);
+
+    if (!secret_available())
+        return fail(r, "INTERNAL", "BOKFD_SECRET_KEY is missing or invalid");
+    yyjson_mut_val *items = yyjson_mut_arr(r->rdoc);
+    int64_t total_gross = 0, total_tax = 0, total_avg = 0, total_net = 0;
+    if (sqlite3_prepare_v2(
+            r->db,
+            "SELECT l.employee_id,e.name,e.personal_no_enc,l.gross_ore,"
+            "l.tax_ore,l.avgifter_ore,l.net_ore,l.tax_table,l.tax_column"
+            " FROM payroll_run_lines l JOIN employees e"
+            " ON e.org_id=l.org_id AND e.id=l.employee_id"
+            " WHERE l.org_id=?1 AND l.run_id=?2 ORDER BY e.name COLLATE NOCASE,"
+            " l.id",
+            -1, &st, NULL) != SQLITE_OK)
+        return db_error(r);
+    sqlite3_bind_int64(st, 1, r->org_id);
+    sqlite3_bind_int64(st, 2, run_id);
+    int rc = 0;
+    while (sqlite3_step(st) == SQLITE_ROW) {
+        char *pn = NULL;
+        if (secret_decrypt(sq(sqlite3_column_text(st, 2)), &pn) != 0) {
+            sqlite3_finalize(st);
+            fail(r, "INTERNAL",
+                 "could not decrypt a personnummer; check BOKFD_SECRET_KEY");
+            return NULL;
+        }
+        yyjson_mut_val *o = yyjson_mut_arr_add_obj(r->rdoc, items);
+        yyjson_mut_obj_add_int(r->rdoc, o, "employee_id",
+                               sqlite3_column_int64(st, 0));
+        yyjson_mut_obj_add_strcpy(r->rdoc, o, "name",
+                                  sq(sqlite3_column_text(st, 1)));
+        yyjson_mut_obj_add_strcpy(r->rdoc, o, "personal_no", pn);
+        free(pn);
+        int64_t gross = sqlite3_column_int64(st, 3);
+        int64_t tax = sqlite3_column_int64(st, 4);
+        int64_t avg = sqlite3_column_int64(st, 5);
+        int64_t net = sqlite3_column_int64(st, 6);
+        yyjson_mut_obj_add_int(r->rdoc, o, "gross_ore", gross);
+        yyjson_mut_obj_add_int(r->rdoc, o, "tax_ore", tax);
+        yyjson_mut_obj_add_int(r->rdoc, o, "avgifter_ore", avg);
+        yyjson_mut_obj_add_int(r->rdoc, o, "net_ore", net);
+        yyjson_mut_obj_add_int(r->rdoc, o, "tax_table",
+                               sqlite3_column_int64(st, 7));
+        yyjson_mut_obj_add_int(r->rdoc, o, "tax_column",
+                               sqlite3_column_int64(st, 8));
+        total_gross += gross;
+        total_tax += tax;
+        total_avg += avg;
+        total_net += net;
+        rc++;
+    }
+    sqlite3_finalize(st);
+    if (rc == 0)
+        return failf(r, "NOT_FOUND", "payroll run %lld has no lines",
+                     (long long)run_id);
+    yyjson_mut_val *out = yyjson_mut_obj(r->rdoc);
+    yyjson_mut_obj_add_int(r->rdoc, out, "run_id", run_id);
+    yyjson_mut_obj_add_strcpy(r->rdoc, out, "period", period);
+    yyjson_mut_obj_add_strcpy(r->rdoc, out, "pay_date", pay_date);
+    yyjson_mut_obj_add_val(r->rdoc, out, "items", items);
+    yyjson_mut_val *totals = yyjson_mut_obj(r->rdoc);
+    yyjson_mut_obj_add_int(r->rdoc, totals, "gross_ore", total_gross);
+    yyjson_mut_obj_add_int(r->rdoc, totals, "tax_ore", total_tax);
+    yyjson_mut_obj_add_int(r->rdoc, totals, "avgifter_ore", total_avg);
+    yyjson_mut_obj_add_int(r->rdoc, totals, "net_ore", total_net);
+    yyjson_mut_obj_add_val(r->rdoc, out, "totals", totals);
+    return out;
+}
+
+/* ------------------------------------------------------------------ */
+/* paying the tax and contributions                                    */
+/* ------------------------------------------------------------------ */
+
+static yyjson_mut_val *h_payroll_pay_tax(struct req *r)
+{
+    int64_t run_id = 0;
+    if (!arg_int(r->args, "run_id", &run_id) || run_id <= 0)
+        return fail(r, "INVALID_ARGS", "run_id is required");
+    sqlite3_stmt *st = NULL;
+    if (sqlite3_prepare_v2(
+            r->db,
+            "SELECT period,pay_date,status,tax_ore,avgifter_ore,"
+            "COALESCE(payment_voucher_id,0) FROM payroll_runs"
+            " WHERE org_id=?1 AND id=?2",
+            -1, &st, NULL) != SQLITE_OK)
+        return db_error(r);
+    sqlite3_bind_int64(st, 1, r->org_id);
+    sqlite3_bind_int64(st, 2, run_id);
+    if (sqlite3_step(st) != SQLITE_ROW) {
+        sqlite3_finalize(st);
+        return fail(r, "NOT_FOUND", "payroll run not found");
+    }
+    char period[16], pay_date[16], status[16];
+    snprintf(period, sizeof period, "%s", sq(sqlite3_column_text(st, 0)));
+    snprintf(pay_date, sizeof pay_date, "%s", sq(sqlite3_column_text(st, 1)));
+    snprintf(status, sizeof status, "%s", sq(sqlite3_column_text(st, 2)));
+    int64_t tax = sqlite3_column_int64(st, 3);
+    int64_t avg = sqlite3_column_int64(st, 4);
+    sqlite3_finalize(st);
+    if (strcmp(status, "paid") == 0)
+        return fail(r, "CONFLICT", "payroll run is already paid");
+    int64_t total = tax + avg;
+    if (total <= 0)
+        return fail(r, "INVALID_ARGS", "nothing to pay for this run");
+
+    const char *date = arg_str(r->args, "date");
+    char today[16];
+    if (!date) {
+        time_t t = time(NULL);
+        struct tm tm;
+        gmtime_r(&t, &tm);
+        util_date_fmt(tm.tm_year + 1900, tm.tm_mon + 1, tm.tm_mday, today,
+                      sizeof today);
+        date = today;
+    }
+    struct payroll_cfg cfg;
+    payroll_cfg_load(r, &cfg);
+
+    struct ledger_row rows[3];
+    memset(rows, 0, sizeof rows);
+    size_t vn = 0;
+    if (tax > 0) {
+        rows[vn].account = cfg.tax;
+        rows[vn].debit_ore = tax;
+        rows[vn].description = "Personalskatt";
+        vn++;
+    }
+    if (avg > 0) {
+        rows[vn].account = cfg.liability;
+        rows[vn].debit_ore = avg;
+        rows[vn].description = "Avräkning sociala avgifter";
+        vn++;
+    }
+    rows[vn].account = cfg.payment;
+    rows[vn].credit_ore = total;
+    rows[vn].description = "Skattekontot";
+    vn++;
+
+    char description[64];
+    snprintf(description, sizeof description,
+             "Betalning skatt och arbetsgivaravgifter %s", period);
+    char *voucher_json = NULL;
+    struct ledger_error e;
+    yyjson_mut_val *res = NULL;
+    int in_tx = 0;
+    if (db_exec(r->db, "BEGIN IMMEDIATE", NULL) != 0)
+        return fail(r, "DB_BUSY", "could not start transaction");
+    in_tx = 1;
+    struct ledger_post_opts o;
+    memset(&o, 0, sizeof o);
+    o.org_id = r->org_id;
+    o.user_id = r->sess->user_id;
+    o.token_id = r->sess->token_id;
+    o.date = date;
+    o.description = description;
+    o.rows = rows;
+    o.nrows = vn;
+    o.source = "payroll_tax";
+    o.dry_run = r->dry_run;
+    o.already_in_tx = 1;
+    if (ledger_post(r->db, &o, &e, &voucher_json) != 0) {
+        fail(r, e.code ? e.code : "INTERNAL", e.msg);
+        goto done;
+    }
+    int64_t voucher_id = 0;
+    if (!r->dry_run) {
+        if (!voucher_json) {
+            fail(r, "INTERNAL", "empty voucher result");
+            goto done;
+        }
+        yyjson_doc *vd = yyjson_read(voucher_json, strlen(voucher_json), 0);
+        if (vd) {
+            yyjson_val *idv = yyjson_obj_get(yyjson_doc_get_root(vd), "id");
+            if (idv && yyjson_is_int(idv))
+                voucher_id = yyjson_get_int(idv);
+            yyjson_doc_free(vd);
+        }
+        if (voucher_id <= 0) {
+            fail(r, "INTERNAL", "voucher id missing from the posting");
+            goto done;
+        }
+        if (sqlite3_prepare_v2(
+                r->db,
+                "UPDATE payroll_runs SET status='paid',payment_voucher_id=?3"
+                " WHERE org_id=?1 AND id=?2 AND status='posted'",
+                -1, &st, NULL) != SQLITE_OK) {
+            db_error(r);
+            goto done;
+        }
+        sqlite3_bind_int64(st, 1, r->org_id);
+        sqlite3_bind_int64(st, 2, run_id);
+        sqlite3_bind_int64(st, 3, voucher_id);
+        int rc = sqlite3_step(st);
+        sqlite3_finalize(st);
+        if (rc != SQLITE_DONE || sqlite3_changes(r->db) == 0) {
+            fail(r, "CONFLICT", "payroll run is already paid");
+            goto done;
+        }
+        if (sqlite3_exec(r->db, "COMMIT", NULL, NULL, NULL) != SQLITE_OK) {
+            fail(r, "DB_BUSY", "commit failed");
+            goto done;
+        }
+        in_tx = 0;
+        char *reqjson = audit_args_json(r->args);
+        audit_append(r->db, r->org_id, r->sess->user_id, r->sess->token_id,
+                     "payroll.pay_tax", reqjson, "OK", NULL);
+        free(reqjson);
+    }
+    res = yyjson_mut_obj(r->rdoc);
+    yyjson_mut_obj_add_int(r->rdoc, res, "run_id", run_id);
+    yyjson_mut_obj_add_strcpy(r->rdoc, res, "period", period);
+    yyjson_mut_obj_add_strcpy(r->rdoc, res, "pay_date", pay_date);
+    yyjson_mut_obj_add_strcpy(r->rdoc, res, "date", date);
+    yyjson_mut_obj_add_int(r->rdoc, res, "tax_ore", tax);
+    yyjson_mut_obj_add_int(r->rdoc, res, "avgifter_ore", avg);
+    yyjson_mut_obj_add_int(r->rdoc, res, "total_ore", total);
+    if (r->dry_run) {
+        yyjson_mut_obj_add_bool(r->rdoc, res, "dry_run", true);
+        yyjson_mut_obj_add_strcpy(r->rdoc, res, "status", "posted");
+        yyjson_mut_obj_add_int(r->rdoc, res, "payment_voucher_id", 0);
+    } else {
+        yyjson_mut_obj_add_strcpy(r->rdoc, res, "status", "paid");
+        yyjson_mut_obj_add_int(r->rdoc, res, "payment_voucher_id",
+                               voucher_id);
+    }
+
+done:
+    if (in_tx)
+        sqlite3_exec(r->db, "ROLLBACK", NULL, NULL, NULL);
+    free(voucher_json);
+    return res;
+}
+
+/* ------------------------------------------------------------------ */
+/* Skatteverket tax tables                                             */
+/* ------------------------------------------------------------------ */
+
+static yyjson_mut_val *tax_tables_store_and_respond(struct req *r, int year,
+                                                    const unsigned char *data,
+                                                    size_t len,
+                                                    const char *source_url)
+{
+    unsigned char sha[32];
+    util_sha256(data, len, sha);
+    struct tax_row *rows = NULL;
+    size_t n = 0;
+    char *err = NULL;
+    if (tax_table_parse(data, len, &rows, &n, &err) != 0) {
+        yyjson_mut_val *res = failf(r, "INVALID_ARGS", "could not parse: %s",
+                                    err ? err : "bad table file");
+        free(err);
+        return res;
+    }
+    char ts[32];
+    util_iso8601(util_now(), ts, sizeof ts);
+    if (!r->dry_run) {
+        if (db_exec(r->db, "BEGIN IMMEDIATE", NULL) != 0) {
+            free(rows);
+            return fail(r, "DB_BUSY", "could not start transaction");
+        }
+        if (tax_table_store(r->db, year, rows, n, source_url, sha, ts,
+                            &err) != 0) {
+            sqlite3_exec(r->db, "ROLLBACK", NULL, NULL, NULL);
+            yyjson_mut_val *res = failf(r, "INTERNAL", "%s",
+                                        err ? err : "could not store");
+            free(err);
+            free(rows);
+            return res;
+        }
+        if (sqlite3_exec(r->db, "COMMIT", NULL, NULL, NULL) != SQLITE_OK) {
+            free(rows);
+            return fail(r, "DB_BUSY", "commit failed");
+        }
+    }
+    free(rows);
+    char hex[65];
+    util_hex(sha, 32, hex);
+    yyjson_mut_val *o = yyjson_mut_obj(r->rdoc);
+    yyjson_mut_obj_add_int(r->rdoc, o, "year", year);
+    yyjson_mut_obj_add_int(r->rdoc, o, "rows", (int64_t)n);
+    yyjson_mut_obj_add_int(r->rdoc, o, "bytes", (int64_t)len);
+    yyjson_mut_obj_add_strcpy(r->rdoc, o, "sha256", hex);
+    yyjson_mut_obj_add_strcpy(r->rdoc, o, "source_url", source_url);
+    if (r->dry_run)
+        yyjson_mut_obj_add_bool(r->rdoc, o, "dry_run", true);
+    return o;
+}
+
+static yyjson_mut_val *h_tax_tables_fetch(struct req *r)
+{
+    int64_t requested = 0;
+    arg_int(r->args, "year", &requested);
+    int year = requested ? (int)requested : payroll_current_year();
+    int current = payroll_current_year();
+    if (year < 2000 || year > current)
+        return failf(r, "INVALID_ARGS", "year must be between 2000 and %d",
+                     current);
+    unsigned char *data = NULL;
+    size_t len = 0;
+    char *url = NULL;
+    char *err = NULL;
+    if (tax_table_fetch_year(year, &data, &len, &url, &err) != 0) {
+        yyjson_mut_val *res = failf(r, "FETCH_FAILED", "%s",
+                                    err ? err : "download failed");
+        free(err);
+        return res;
+    }
+    yyjson_mut_val *o = tax_tables_store_and_respond(r, year, data, len, url);
+    free(data);
+    free(url);
+    if (!o || r->dry_run)
+        return o;
+    char *reqjson = audit_args_json(r->args);
+    audit_append(r->db, r->org_id, r->sess->user_id, r->sess->token_id,
+                 "payroll.tax_tables_fetch", reqjson, "OK", NULL);
+    free(reqjson);
+    return o;
+}
+
+static yyjson_mut_val *h_tax_tables_import(struct req *r)
+{
+    int64_t year = 0;
+    const char *b64 = arg_str(r->args, "content_base64");
+    if (!arg_int(r->args, "year", &year) || year < 2000 || year > 9999)
+        return fail(r, "INVALID_ARGS", "year is required");
+    if (!b64)
+        return fail(r, "INVALID_ARGS", "content_base64 is required");
+    unsigned char *data = NULL;
+    size_t len = 0;
+    if (util_b64_decode(b64, strlen(b64), &data, &len) != 0)
+        return fail(r, "INVALID_ARGS", "content_base64 is not valid base64");
+    yyjson_mut_val *o =
+        tax_tables_store_and_respond(r, (int)year, data, len, "import");
+    free(data);
+    if (!o || r->dry_run)
+        return o;
+    char *reqjson = audit_args_json(r->args);
+    audit_append(r->db, r->org_id, r->sess->user_id, r->sess->token_id,
+                 "payroll.tax_tables_import", reqjson, "OK", NULL);
+    free(reqjson);
+    return o;
+}
+
+static yyjson_mut_val *h_tax_tables_status(struct req *r)
+{
+    int current = payroll_current_year();
+    yyjson_mut_val *years = yyjson_mut_arr(r->rdoc);
+    int have_current = 0;
+    sqlite3_stmt *st = NULL;
+    if (sqlite3_prepare_v2(
+            r->db, "SELECT DISTINCT in_year FROM tax_tables ORDER BY in_year",
+            -1, &st, NULL) != SQLITE_OK)
+        return db_error(r);
+    while (sqlite3_step(st) == SQLITE_ROW) {
+        int64_t y = sqlite3_column_int64(st, 0);
+        yyjson_mut_arr_add_int(r->rdoc, years, y);
+        if (y == current)
+            have_current = 1;
+    }
+    sqlite3_finalize(st);
+
+    char fetched_at[64] = "";
+    char source_url[1024] = "";
+    if (sqlite3_prepare_v2(
+            r->db,
+            "SELECT source_url,fetched_at FROM tax_table_meta"
+            " WHERE in_year=?1",
+            -1, &st, NULL) != SQLITE_OK)
+        return db_error(r);
+    sqlite3_bind_int(st, 1, current);
+    if (sqlite3_step(st) == SQLITE_ROW) {
+        snprintf(source_url, sizeof source_url, "%s",
+                 sq(sqlite3_column_text(st, 0)));
+        snprintf(fetched_at, sizeof fetched_at, "%s",
+                 sq(sqlite3_column_text(st, 1)));
+    }
+    sqlite3_finalize(st);
+    if (!fetched_at[0]) {
+        if (sqlite3_prepare_v2(
+                r->db,
+                "SELECT source_url,fetched_at FROM tax_table_meta"
+                " ORDER BY fetched_at DESC LIMIT 1",
+                -1, &st, NULL) != SQLITE_OK)
+            return db_error(r);
+        if (sqlite3_step(st) == SQLITE_ROW) {
+            snprintf(source_url, sizeof source_url, "%s",
+                     sq(sqlite3_column_text(st, 0)));
+            snprintf(fetched_at, sizeof fetched_at, "%s",
+                     sq(sqlite3_column_text(st, 1)));
+        }
+        sqlite3_finalize(st);
+    }
+
+    yyjson_mut_val *o = yyjson_mut_obj(r->rdoc);
+    yyjson_mut_obj_add_val(r->rdoc, o, "stored_years", years);
+    yyjson_mut_obj_add_int(r->rdoc, o, "current_year", current);
+    yyjson_mut_obj_add_bool(r->rdoc, o, "stale", !have_current);
+    if (fetched_at[0])
+        yyjson_mut_obj_add_strcpy(r->rdoc, o, "fetched_at", fetched_at);
+    else
+        yyjson_mut_obj_add_null(r->rdoc, o, "fetched_at");
+    if (source_url[0])
+        yyjson_mut_obj_add_strcpy(r->rdoc, o, "source_url", source_url);
+    else
+        yyjson_mut_obj_add_null(r->rdoc, o, "source_url");
+    return o;
+}
+
+/* ------------------------------------------------------------------ */
+/* command table                                                       */
+/* ------------------------------------------------------------------ */
+
+static const struct cmd_arg args_tax_tables_fetch[] = {
+    { "year", ARG_INT, 0, NULL, NULL,
+      "Income year; defaults to the current calendar year" },
+};
+
+static const struct cmd_arg args_tax_tables_import[] = {
+    { "year", ARG_INT, 1, NULL, NULL, "Income year of the file" },
+    { "content_base64", ARG_STR, 1, NULL, NULL,
+      "allmanna-tabeller-manad.txt content, base64" },
+};
+
+static const struct cmd_arg args_run_preview[] = {
+    { "period", ARG_STR, 1, NULL, NULL, "Salary period YYYY-MM" },
+};
+
+static const struct cmd_arg args_run_post[] = {
+    { "period", ARG_STR, 1, NULL, NULL, "Salary period YYYY-MM" },
+    { "pay_date", ARG_DATE, 1, NULL, NULL, "Payment date YYYY-MM-DD" },
+};
+
+static const struct cmd_arg args_run_list[] = {
+    { "limit", ARG_INT, 0, "100", NULL, "Maximum number of runs" },
+};
+
+static const struct cmd_arg args_run_get[] = {
+    { "id", ARG_INT, 1, NULL, NULL, "Payroll run id" },
+};
+
+static const struct cmd_arg args_agi[] = {
+    { "period", ARG_STR, 1, NULL, NULL, "Salary period YYYY-MM" },
+};
+
+static const struct cmd_arg args_pay_tax[] = {
+    { "run_id", ARG_INT, 1, NULL, NULL, "Payroll run id" },
+    { "date", ARG_DATE, 0, NULL, NULL,
+      "Payment date; defaults to today" },
+};
+
+static const struct cmd_arg args_settings_set[] = {
+    { "key", ARG_STR, 1, NULL, NULL,
+      "One of the payroll_* settings" },
+    { "value", ARG_STR, 1, NULL, NULL, "Digits only" },
+};
+
+const struct command g_cmd_payroll[] = {
+    { "payroll.tax_tables_fetch", "Download a year's Skatteverket monthly"
+      " tables", PERM_OWNER, 1, 1, 1, h_tax_tables_fetch,
+      CMD_ARGS(args_tax_tables_fetch) },
+    { "payroll.tax_tables_import", "Import a monthly table file offline",
+      PERM_OWNER, 1, 1, 1, h_tax_tables_import,
+      CMD_ARGS(args_tax_tables_import) },
+    { "payroll.tax_tables_status", "Stored tax table years and staleness",
+      PERM_READ, 1, 0, 0, h_tax_tables_status, NULL, 0 },
+    { "payroll.run_preview", "Preview a monthly payroll run", PERM_WRITE, 1,
+      0, 0, h_payroll_run_preview, CMD_ARGS(args_run_preview) },
+    { "payroll.run_post", "Post the monthly payroll voucher and run",
+      PERM_WRITE, 1, 1, 1, h_payroll_run_post, CMD_ARGS(args_run_post) },
+    { "payroll.run_list", "List payroll runs, newest first", PERM_READ, 1, 0,
+      0, h_payroll_run_list, CMD_ARGS(args_run_list) },
+    { "payroll.run_get", "Get a payroll run with its lines", PERM_READ, 1, 0,
+      0, h_payroll_run_get, CMD_ARGS(args_run_get) },
+    { "payroll.agi", "AGI underlag per employee (owner; personnummer in"
+      " clear)", PERM_OWNER, 1, 0, 0, h_payroll_agi, CMD_ARGS(args_agi) },
+    { "payroll.pay_tax", "Pay the run's tax and contributions", PERM_WRITE,
+      1, 1, 1, h_payroll_pay_tax, CMD_ARGS(args_pay_tax) },
+    { "payroll.settings_get", "Read effective payroll settings", PERM_READ, 1,
+      0, 0, h_payroll_settings_get, NULL, 0 },
+    { "payroll.settings_set", "Change a payroll setting", PERM_WRITE, 1, 1, 1,
+      h_payroll_settings_set, CMD_ARGS(args_settings_set) },
+};
+
+const struct cmd_table g_cmd_table_payroll = {
+    g_cmd_payroll, sizeof g_cmd_payroll / sizeof g_cmd_payroll[0]
+};