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

1 files changed, 665 insertions, 0 deletions

diff --git a/src/cmd_employees.c b/src/cmd_employees.c
new file mode 100644
index 0000000..788dd83
--- /dev/null
+++ b/src/cmd_employees.c
@@ -0,0 +1,665 @@
+#include "commands.h"
+#include "cmd_util.h"
+
+#include <stdio.h>
+#include <stdlib.h>
+#include <string.h>
+
+#include "audit.h"
+#include "db.h"
+#include "secret.h"
+#include "util.h"
+
+/* ------------------------------------------------------------------ */
+/* employee register                                                   */
+/* ------------------------------------------------------------------ */
+
+#define EMPLOYEE_COLUMNS                                                    \
+    "id,name,personal_no_enc,address,postal_code,city,bank_account,"       \
+    "salary_account,monthly_salary_ore,tax_table,tax_column,active,"       \
+    "created_at,COALESCE(updated_at,'')"
+
+static int personal_no_valid(const char *s)
+{
+    if (!s)
+        return 0;
+    size_t n = strlen(s);
+    if (n != 10 && n != 11 && n != 12 && n != 13)
+        return 0;
+    if (n == 11 && s[6] != '-')
+        return 0;
+    if (n == 13 && s[8] != '-')
+        return 0;
+    for (size_t i = 0; i < n; i++) {
+        if (s[i] >= '0' && s[i] <= '9')
+            continue;
+        if (s[i] == '-' && ((n == 11 && i == 6) || (n == 13 && i == 8)))
+            continue;
+        return 0;
+    }
+    return 1;
+}
+
+static char *personal_no_mask(const char *pn)
+{
+    size_t n = pn ? strlen(pn) : 0;
+    char *out = xmalloc(n + 1);
+    for (size_t i = 0; i < n; i++) {
+        if (i + 4 >= n || pn[i] == '-')
+            out[i] = pn[i];
+        else
+            out[i] = '*';
+    }
+    out[n] = '\0';
+    return out;
+}
+
+static yyjson_mut_val *employee_json(struct req *r, sqlite3_stmt *st)
+{
+    char *pn = NULL;
+    if (secret_decrypt(sq(sqlite3_column_text(st, 2)), &pn) != 0)
+        pn = xstrdup("********");
+    char *masked = personal_no_mask(pn);
+    free(pn);
+
+    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, "name",
+                              sq(sqlite3_column_text(st, 1)));
+    yyjson_mut_obj_add_strcpy(r->rdoc, o, "personal_no", masked);
+    free(masked);
+    yyjson_mut_obj_add_strcpy(r->rdoc, o, "address",
+                              sq(sqlite3_column_text(st, 3)));
+    yyjson_mut_obj_add_strcpy(r->rdoc, o, "postal_code",
+                              sq(sqlite3_column_text(st, 4)));
+    yyjson_mut_obj_add_strcpy(r->rdoc, o, "city",
+                              sq(sqlite3_column_text(st, 5)));
+    yyjson_mut_obj_add_strcpy(r->rdoc, o, "bank_account",
+                              sq(sqlite3_column_text(st, 6)));
+    yyjson_mut_obj_add_strcpy(r->rdoc, o, "salary_account",
+                              sq(sqlite3_column_text(st, 7)));
+    yyjson_mut_obj_add_int(r->rdoc, o, "monthly_salary_ore",
+                           sqlite3_column_int64(st, 8));
+    yyjson_mut_obj_add_int(r->rdoc, o, "tax_table",
+                           sqlite3_column_int64(st, 9));
+    yyjson_mut_obj_add_int(r->rdoc, o, "tax_column",
+                           sqlite3_column_int64(st, 10));
+    yyjson_mut_obj_add_bool(r->rdoc, o, "active",
+                            sqlite3_column_int(st, 11) != 0);
+    yyjson_mut_obj_add_strcpy(r->rdoc, o, "created_at",
+                              sq(sqlite3_column_text(st, 12)));
+    yyjson_mut_obj_add_strcpy(r->rdoc, o, "updated_at",
+                              sq(sqlite3_column_text(st, 13)));
+    return o;
+}
+
+/* The register's audit entries must not contain the personnummer. */
+static char *employee_audit_json(yyjson_val *args)
+{
+    if (!args || !yyjson_is_obj(args))
+        return xstrdup("{}");
+    yyjson_mut_doc *doc = yyjson_mut_doc_new(NULL);
+    yyjson_mut_val *out = yyjson_mut_obj(doc);
+    yyjson_mut_doc_set_root(doc, out);
+    yyjson_obj_iter it = yyjson_obj_iter_with(args);
+    yyjson_val *key;
+    while ((key = yyjson_obj_iter_next(&it))) {
+        const char *k = yyjson_get_str(key);
+        yyjson_val *v = yyjson_obj_iter_get_val(key);
+        if (!k || !v)
+            continue;
+        if (strcmp(k, "personal_no") == 0) {
+            yyjson_mut_obj_add_strcpy(doc, out, k, "[redacted]");
+            continue;
+        }
+        yyjson_mut_val *copy = yyjson_val_mut_copy(doc, v);
+        if (copy)
+            yyjson_mut_obj_add(out, yyjson_mut_strcpy(doc, k), copy);
+    }
+    char *json = yyjson_mut_write(doc, 0, NULL);
+    yyjson_mut_doc_free(doc);
+    return json ? json : xstrdup("{}");
+}
+
+struct employee_input {
+    const char *name;
+    const char *personal_no;
+    const char *address;
+    const char *postal_code;
+    const char *city;
+    const char *bank_account;
+    const char *salary_account;
+    int64_t monthly_salary_ore;
+    int have_salary;
+    int64_t tax_table;
+    int have_table;
+    int64_t tax_column;
+    int have_column;
+    int active;
+    int have_active;
+};
+
+static void employee_input_read(struct req *r, struct employee_input *in)
+{
+    memset(in, 0, sizeof *in);
+    in->name = arg_str(r->args, "name");
+    in->personal_no = arg_str(r->args, "personal_no");
+    in->address = arg_str(r->args, "address");
+    in->postal_code = arg_str(r->args, "postal_code");
+    in->city = arg_str(r->args, "city");
+    in->bank_account = arg_str(r->args, "bank_account");
+    in->salary_account = arg_str(r->args, "salary_account");
+    in->have_salary = arg_int(r->args, "monthly_salary_ore",
+                              &in->monthly_salary_ore);
+    in->have_table = arg_int(r->args, "tax_table", &in->tax_table);
+    in->have_column = arg_int(r->args, "tax_column", &in->tax_column);
+    in->have_active = arg_bool(r->args, "active", &in->active);
+}
+
+static int employee_input_validate(struct req *r,
+                                   const struct employee_input *in,
+                                   int is_create)
+{
+    if (is_create && (!in->name || !*in->name)) {
+        fail(r, "INVALID_ARGS", "name is required");
+        return -1;
+    }
+    if (in->name && !*in->name) {
+        fail(r, "INVALID_ARGS", "name cannot be empty");
+        return -1;
+    }
+    if (in->name && strlen(in->name) > 200) {
+        fail(r, "INVALID_ARGS", "name is too long");
+        return -1;
+    }
+    if (is_create && !in->personal_no) {
+        fail(r, "INVALID_ARGS", "personal_no is required");
+        return -1;
+    }
+    if (in->personal_no && !personal_no_valid(in->personal_no)) {
+        fail(r, "INVALID_ARGS",
+             "personal_no must be 10 or 12 digits, with or without a hyphen");
+        return -1;
+    }
+    static const char *const names[] = { "address", "postal_code", "city",
+                                         "bank_account" };
+    static const size_t maxlen[] = { 500, 32, 120, 64 };
+    const char *values[] = { in->address, in->postal_code, in->city,
+                             in->bank_account };
+    for (size_t i = 0; i < sizeof maxlen / sizeof maxlen[0]; i++) {
+        if (values[i] && strlen(values[i]) > maxlen[i]) {
+            failf(r, "INVALID_ARGS", "%s is too long", names[i]);
+            return -1;
+        }
+    }
+    if (in->salary_account &&
+        (!is_digits(in->salary_account) ||
+         strlen(in->salary_account) > 10)) {
+        fail(r, "INVALID_ARGS", "salary_account must be digits only");
+        return -1;
+    }
+    if (in->have_salary && in->monthly_salary_ore < 0) {
+        fail(r, "INVALID_ARGS", "monthly_salary_ore must be >= 0");
+        return -1;
+    }
+    if (in->have_table && (in->tax_table < 29 || in->tax_table > 42)) {
+        fail(r, "INVALID_ARGS", "tax_table must be between 29 and 42");
+        return -1;
+    }
+    if (in->have_column && (in->tax_column < 1 || in->tax_column > 6)) {
+        fail(r, "INVALID_ARGS", "tax_column must be between 1 and 6");
+        return -1;
+    }
+    return 0;
+}
+
+static yyjson_mut_val *employee_lookup(struct req *r, int64_t id, int *found)
+{
+    sqlite3_stmt *st = NULL;
+    *found = 0;
+    if (sqlite3_prepare_v2(
+            r->db,
+            "SELECT " EMPLOYEE_COLUMNS " FROM employees"
+            " WHERE org_id=?1 AND id=?2",
+            -1, &st, NULL) != SQLITE_OK) {
+        *found = -1;
+        db_error(r);
+        return NULL;
+    }
+    sqlite3_bind_int64(st, 1, r->org_id);
+    sqlite3_bind_int64(st, 2, id);
+    if (sqlite3_step(st) != SQLITE_ROW) {
+        sqlite3_finalize(st);
+        return NULL;
+    }
+    yyjson_mut_val *o = employee_json(r, st);
+    sqlite3_finalize(st);
+    *found = 1;
+    return o;
+}
+
+/* Encrypts the personnummer and rejects a duplicate (compared on the
+   decrypted value, since the stored ciphertext has a fresh nonce). */
+static int employee_personal_no_store(struct req *r, const char *plain,
+                                      int64_t except_id, char **stored_out)
+{
+    sqlite3_stmt *st = NULL;
+    if (sqlite3_prepare_v2(
+            r->db,
+            "SELECT personal_no_enc FROM employees WHERE org_id=?1 AND id<>?2",
+            -1, &st, NULL) != SQLITE_OK) {
+        db_error(r);
+        return -1;
+    }
+    sqlite3_bind_int64(st, 1, r->org_id);
+    sqlite3_bind_int64(st, 2, except_id);
+    while (sqlite3_step(st) == SQLITE_ROW) {
+        char *other = NULL;
+        if (secret_decrypt(sq(sqlite3_column_text(st, 0)), &other) != 0)
+            continue;
+        int same = strcmp(other, plain) == 0;
+        free(other);
+        if (same) {
+            sqlite3_finalize(st);
+            fail(r, "CONFLICT",
+                 "an employee with this personal_no already exists");
+            return -1;
+        }
+    }
+    sqlite3_finalize(st);
+    if (!secret_available()) {
+        fail(r, "INTERNAL", "BOKFD_SECRET_KEY is missing or invalid");
+        return -1;
+    }
+    char *enc = NULL;
+    if (secret_encrypt(plain, &enc) != 0) {
+        fail(r, "INTERNAL", "could not encrypt the personnummer");
+        return -1;
+    }
+    *stored_out = enc;
+    return 0;
+}
+
+static yyjson_mut_val *h_employee_list(struct req *r)
+{
+    int active_only = 0;
+    arg_bool(r->args, "active_only", &active_only);
+    sqlite3_stmt *st = NULL;
+    if (sqlite3_prepare_v2(
+            r->db,
+            "SELECT " EMPLOYEE_COLUMNS " FROM employees WHERE org_id=?1"
+            " AND (?2=0 OR active=1) ORDER BY name COLLATE NOCASE, id",
+            -1, &st, NULL) != SQLITE_OK)
+        return db_error(r);
+    sqlite3_bind_int64(st, 1, r->org_id);
+    sqlite3_bind_int(st, 2, active_only);
+    yyjson_mut_val *items = yyjson_mut_arr(r->rdoc);
+    while (sqlite3_step(st) == SQLITE_ROW)
+        yyjson_mut_arr_add_val(items, employee_json(r, st));
+    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_employee_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");
+    int found = 0;
+    yyjson_mut_val *o = employee_lookup(r, id, &found);
+    if (found < 0)
+        return NULL;
+    if (!found)
+        return fail(r, "NOT_FOUND", "employee not found");
+    return o;
+}
+
+static yyjson_mut_val *h_employee_create(struct req *r)
+{
+    struct employee_input in;
+    employee_input_read(r, &in);
+    if (employee_input_validate(r, &in, 1) != 0)
+        return NULL;
+    if (!in.have_salary)
+        in.monthly_salary_ore = 0;
+    if (!in.have_table)
+        in.tax_table = 30;
+    if (!in.have_column)
+        in.tax_column = 1;
+
+    char *stored = NULL;
+    if (employee_personal_no_store(r, in.personal_no, 0, &stored) != 0)
+        return NULL;
+
+    char account[16];
+    if (in.salary_account) {
+        snprintf(account, sizeof account, "%s", in.salary_account);
+    } else {
+        char *setting = db_setting(r->db, r->org_id, "payroll_salary_account");
+        snprintf(account, sizeof account, "%s",
+                 setting && *setting ? setting : "7210");
+        free(setting);
+    }
+    const char *address = in.address ? in.address : "";
+    const char *postal = in.postal_code ? in.postal_code : "";
+    const char *city = in.city ? in.city : "";
+    const char *bank = in.bank_account ? in.bank_account : "";
+
+    if (r->dry_run) {
+        free(stored);
+        char *masked = personal_no_mask(in.personal_no);
+        yyjson_mut_val *o = yyjson_mut_obj(r->rdoc);
+        yyjson_mut_obj_add_int(r->rdoc, o, "id", 0);
+        yyjson_mut_obj_add_strcpy(r->rdoc, o, "name", in.name);
+        yyjson_mut_obj_add_strcpy(r->rdoc, o, "personal_no", masked);
+        free(masked);
+        yyjson_mut_obj_add_strcpy(r->rdoc, o, "address", address);
+        yyjson_mut_obj_add_strcpy(r->rdoc, o, "postal_code", postal);
+        yyjson_mut_obj_add_strcpy(r->rdoc, o, "city", city);
+        yyjson_mut_obj_add_strcpy(r->rdoc, o, "bank_account", bank);
+        yyjson_mut_obj_add_strcpy(r->rdoc, o, "salary_account", account);
+        yyjson_mut_obj_add_int(r->rdoc, o, "monthly_salary_ore",
+                               in.monthly_salary_ore);
+        yyjson_mut_obj_add_int(r->rdoc, o, "tax_table", in.tax_table);
+        yyjson_mut_obj_add_int(r->rdoc, o, "tax_column", in.tax_column);
+        yyjson_mut_obj_add_bool(r->rdoc, o, "active", true);
+        yyjson_mut_obj_add_bool(r->rdoc, o, "dry_run", true);
+        return o;
+    }
+
+    char ts[32];
+    util_iso8601(util_now(), ts, sizeof ts);
+    sqlite3_stmt *st = NULL;
+    if (sqlite3_prepare_v2(
+            r->db,
+            "INSERT INTO employees(org_id,name,personal_no_enc,address,"
+            "postal_code,city,bank_account,salary_account,monthly_salary_ore,"
+            "tax_table,tax_column,created_at)"
+            " VALUES(?1,?2,?3,?4,?5,?6,?7,?8,?9,?10,?11,?12)",
+            -1, &st, NULL) != SQLITE_OK) {
+        free(stored);
+        return db_error(r);
+    }
+    sqlite3_bind_int64(st, 1, r->org_id);
+    sqlite3_bind_text(st, 2, in.name, -1, SQLITE_TRANSIENT);
+    sqlite3_bind_text(st, 3, stored, -1, SQLITE_TRANSIENT);
+    sqlite3_bind_text(st, 4, address, -1, SQLITE_TRANSIENT);
+    sqlite3_bind_text(st, 5, postal, -1, SQLITE_TRANSIENT);
+    sqlite3_bind_text(st, 6, city, -1, SQLITE_TRANSIENT);
+    sqlite3_bind_text(st, 7, bank, -1, SQLITE_TRANSIENT);
+    sqlite3_bind_text(st, 8, account, -1, SQLITE_TRANSIENT);
+    sqlite3_bind_int64(st, 9, in.monthly_salary_ore);
+    sqlite3_bind_int64(st, 10, in.tax_table);
+    sqlite3_bind_int64(st, 11, in.tax_column);
+    sqlite3_bind_text(st, 12, ts, -1, SQLITE_TRANSIENT);
+    int rc = sqlite3_step(st);
+    sqlite3_finalize(st);
+    free(stored);
+    if (rc != SQLITE_DONE) {
+        if ((rc & 0xff) == SQLITE_CONSTRAINT)
+            return fail(r, "CONFLICT",
+                        "an employee with this personal_no already exists");
+        return db_sqlite_error(r);
+    }
+    int64_t id = db_last_id(r->db);
+    char *reqjson = employee_audit_json(r->args);
+    audit_append(r->db, r->org_id, r->sess->user_id, r->sess->token_id,
+                 "employee.create", reqjson, "OK", NULL);
+    free(reqjson);
+    int found = 0;
+    yyjson_mut_val *o = employee_lookup(r, id, &found);
+    if (found < 0)
+        return NULL;
+    if (!found)
+        return fail(r, "INTERNAL", "could not read the new employee");
+    return o;
+}
+
+static yyjson_mut_val *h_employee_update(struct req *r)
+{
+    int64_t id = 0;
+    if (!arg_int(r->args, "id", &id) || id <= 0)
+        return fail(r, "INVALID_ARGS", "id is required");
+    struct employee_input in;
+    employee_input_read(r, &in);
+    if (employee_input_validate(r, &in, 0) != 0)
+        return NULL;
+    if (!in.name && !in.personal_no && !in.address && !in.postal_code &&
+        !in.city && !in.bank_account && !in.salary_account &&
+        !in.have_salary && !in.have_table && !in.have_column &&
+        !in.have_active)
+        return fail(r, "INVALID_ARGS", "nothing to update");
+
+    int found = 0;
+    yyjson_mut_val *existing = employee_lookup(r, id, &found);
+    (void)existing;
+    if (found < 0)
+        return NULL;
+    if (!found)
+        return fail(r, "NOT_FOUND", "employee not found");
+
+    char *stored = NULL;
+    if (in.personal_no &&
+        employee_personal_no_store(r, in.personal_no, id, &stored) != 0)
+        return NULL;
+
+    if (r->dry_run) {
+        sqlite3_stmt *st = NULL;
+        if (sqlite3_prepare_v2(
+                r->db,
+                "SELECT id,COALESCE(?3,name),COALESCE(?4,personal_no_enc),"
+                "COALESCE(?5,address),COALESCE(?6,postal_code),"
+                "COALESCE(?7,city),COALESCE(?8,bank_account),"
+                "COALESCE(?9,salary_account),"
+                "CASE WHEN ?10<0 THEN monthly_salary_ore ELSE ?10 END,"
+                "CASE WHEN ?11<0 THEN tax_table ELSE ?11 END,"
+                "CASE WHEN ?12<0 THEN tax_column ELSE ?12 END,"
+                "CASE WHEN ?13<0 THEN active ELSE ?13 END,"
+                "created_at,COALESCE(updated_at,'')"
+                " FROM employees WHERE org_id=?1 AND id=?2",
+                -1, &st, NULL) != SQLITE_OK) {
+            free(stored);
+            return db_error(r);
+        }
+        sqlite3_bind_int64(st, 1, r->org_id);
+        sqlite3_bind_int64(st, 2, id);
+        bind_text_or_null(st, 3, in.name);
+        bind_text_or_null(st, 4, stored);
+        bind_text_or_null(st, 5, in.address);
+        bind_text_or_null(st, 6, in.postal_code);
+        bind_text_or_null(st, 7, in.city);
+        bind_text_or_null(st, 8, in.bank_account);
+        bind_text_or_null(st, 9, in.salary_account);
+        sqlite3_bind_int64(st, 10, in.have_salary ? in.monthly_salary_ore
+                                                  : -1);
+        sqlite3_bind_int64(st, 11, in.have_table ? in.tax_table : -1);
+        sqlite3_bind_int64(st, 12, in.have_column ? in.tax_column : -1);
+        sqlite3_bind_int64(st, 13, in.have_active ? in.active : -1);
+        yyjson_mut_val *o = NULL;
+        if (sqlite3_step(st) == SQLITE_ROW)
+            o = employee_json(r, st);
+        sqlite3_finalize(st);
+        free(stored);
+        if (!o)
+            return fail(r, "NOT_FOUND", "employee not found");
+        yyjson_mut_obj_add_bool(r->rdoc, o, "dry_run", true);
+        return o;
+    }
+
+    char ts[32];
+    util_iso8601(util_now(), ts, sizeof ts);
+    sqlite3_stmt *st = NULL;
+    if (sqlite3_prepare_v2(
+            r->db,
+            "UPDATE employees SET"
+            " name=COALESCE(?3,name),"
+            " personal_no_enc=COALESCE(?4,personal_no_enc),"
+            " address=COALESCE(?5,address),"
+            " postal_code=COALESCE(?6,postal_code),"
+            " city=COALESCE(?7,city),"
+            " bank_account=COALESCE(?8,bank_account),"
+            " salary_account=COALESCE(?9,salary_account),"
+            " monthly_salary_ore=CASE WHEN ?10<0 THEN monthly_salary_ore"
+            "   ELSE ?10 END,"
+            " tax_table=CASE WHEN ?11<0 THEN tax_table ELSE ?11 END,"
+            " tax_column=CASE WHEN ?12<0 THEN tax_column ELSE ?12 END,"
+            " active=CASE WHEN ?13<0 THEN active ELSE ?13 END,"
+            " updated_at=?14 WHERE org_id=?1 AND id=?2",
+            -1, &st, NULL) != SQLITE_OK) {
+        free(stored);
+        return db_error(r);
+    }
+    sqlite3_bind_int64(st, 1, r->org_id);
+    sqlite3_bind_int64(st, 2, id);
+    bind_text_or_null(st, 3, in.name);
+    bind_text_or_null(st, 4, stored);
+    bind_text_or_null(st, 5, in.address);
+    bind_text_or_null(st, 6, in.postal_code);
+    bind_text_or_null(st, 7, in.city);
+    bind_text_or_null(st, 8, in.bank_account);
+    bind_text_or_null(st, 9, in.salary_account);
+    sqlite3_bind_int64(st, 10, in.have_salary ? in.monthly_salary_ore : -1);
+    sqlite3_bind_int64(st, 11, in.have_table ? in.tax_table : -1);
+    sqlite3_bind_int64(st, 12, in.have_column ? in.tax_column : -1);
+    sqlite3_bind_int64(st, 13, in.have_active ? in.active : -1);
+    sqlite3_bind_text(st, 14, ts, -1, SQLITE_TRANSIENT);
+    int rc = sqlite3_step(st);
+    sqlite3_finalize(st);
+    free(stored);
+    if (rc != SQLITE_DONE) {
+        if ((rc & 0xff) == SQLITE_CONSTRAINT)
+            return fail(r, "CONFLICT",
+                        "an employee with this personal_no already exists");
+        return db_sqlite_error(r);
+    }
+    if (sqlite3_changes(r->db) == 0)
+        return fail(r, "NOT_FOUND", "employee not found");
+    char *reqjson = employee_audit_json(r->args);
+    audit_append(r->db, r->org_id, r->sess->user_id, r->sess->token_id,
+                 "employee.update", reqjson, "OK", NULL);
+    free(reqjson);
+    found = 0;
+    yyjson_mut_val *o = employee_lookup(r, id, &found);
+    if (found < 0)
+        return NULL;
+    if (!found)
+        return fail(r, "INTERNAL", "could not read the employee");
+    return o;
+}
+
+static yyjson_mut_val *h_employee_archive(struct req *r)
+{
+    int64_t id = 0;
+    int active = 0;
+    if (!arg_int(r->args, "id", &id) || id <= 0 ||
+        !arg_bool(r->args, "active", &active))
+        return fail(r, "INVALID_ARGS", "id and active are required");
+    int found = 0;
+    yyjson_mut_val *existing = employee_lookup(r, id, &found);
+    (void)existing;
+    if (found < 0)
+        return NULL;
+    if (!found)
+        return fail(r, "NOT_FOUND", "employee not found");
+
+    if (r->dry_run) {
+        yyjson_mut_val *o = yyjson_mut_obj(r->rdoc);
+        yyjson_mut_obj_add_int(r->rdoc, o, "id", id);
+        yyjson_mut_obj_add_bool(r->rdoc, o, "active", active != 0);
+        yyjson_mut_obj_add_bool(r->rdoc, o, "dry_run", true);
+        return o;
+    }
+
+    char ts[32];
+    util_iso8601(util_now(), ts, sizeof ts);
+    sqlite3_stmt *st = NULL;
+    if (sqlite3_prepare_v2(
+            r->db,
+            "UPDATE employees SET active=?3, updated_at=?4"
+            " 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);
+    sqlite3_bind_int(st, 3, active);
+    sqlite3_bind_text(st, 4, ts, -1, SQLITE_TRANSIENT);
+    int rc = sqlite3_step(st);
+    sqlite3_finalize(st);
+    if (rc != SQLITE_DONE)
+        return db_sqlite_error(r);
+    if (sqlite3_changes(r->db) == 0)
+        return fail(r, "NOT_FOUND", "employee not found");
+    char *reqjson = employee_audit_json(r->args);
+    audit_append(r->db, r->org_id, r->sess->user_id, r->sess->token_id,
+                 "employee.archive", reqjson, "OK", NULL);
+    free(reqjson);
+    yyjson_mut_val *o = yyjson_mut_obj(r->rdoc);
+    yyjson_mut_obj_add_int(r->rdoc, o, "id", id);
+    yyjson_mut_obj_add_bool(r->rdoc, o, "active", active != 0);
+    return o;
+}
+
+static const struct cmd_arg args_employee_list[] = {
+    { "active_only", ARG_BOOL, 0, NULL, NULL, "Only active employees" },
+};
+
+static const struct cmd_arg args_employee_get[] = {
+    { "id", ARG_INT, 1, NULL, NULL, "Employee id" },
+};
+
+static const struct cmd_arg args_employee_create[] = {
+    { "name", ARG_STR, 1, NULL, NULL, "Employee name" },
+    { "personal_no", ARG_STR, 1, NULL, NULL,
+      "10 or 12 digits, with or without a hyphen; encrypted at rest" },
+    { "address", ARG_STR, 0, NULL, NULL, "Street address" },
+    { "postal_code", ARG_STR, 0, NULL, NULL, "Postal code" },
+    { "city", ARG_STR, 0, NULL, NULL, "City" },
+    { "bank_account", ARG_STR, 0, NULL, NULL, "Bank account for the net pay" },
+    { "salary_account", ARG_STR, 0, NULL, NULL,
+      "Salary account, digits only; defaults to payroll_salary_account" },
+    { "monthly_salary_ore", ARG_INT, 0, "0", NULL,
+      "Monthly gross salary in öre" },
+    { "tax_table", ARG_INT, 0, "30", NULL, "Skatteverket table 29-42" },
+    { "tax_column", ARG_INT, 0, "1", NULL, "Tax column 1-6" },
+};
+
+static const struct cmd_arg args_employee_update[] = {
+    { "id", ARG_INT, 1, NULL, NULL, "Employee id" },
+    { "name", ARG_STR, 0, NULL, NULL, "Employee name" },
+    { "personal_no", ARG_STR, 0, NULL, NULL,
+      "New personnummer, encrypted at rest" },
+    { "address", ARG_STR, 0, NULL, NULL, "Street address" },
+    { "postal_code", ARG_STR, 0, NULL, NULL, "Postal code" },
+    { "city", ARG_STR, 0, NULL, NULL, "City" },
+    { "bank_account", ARG_STR, 0, NULL, NULL, "Bank account for the net pay" },
+    { "salary_account", ARG_STR, 0, NULL, NULL, "Salary account, digits only" },
+    { "monthly_salary_ore", ARG_INT, 0, NULL, NULL,
+      "Monthly gross salary in öre" },
+    { "tax_table", ARG_INT, 0, NULL, NULL, "Skatteverket table 29-42" },
+    { "tax_column", ARG_INT, 0, NULL, NULL, "Tax column 1-6" },
+    { "active", ARG_BOOL, 0, NULL, NULL, "Active flag" },
+};
+
+static const struct cmd_arg args_employee_archive[] = {
+    { "id", ARG_INT, 1, NULL, NULL, "Employee id" },
+    { "active", ARG_BOOL, 1, NULL, NULL, "false archives, true reactivates" },
+};
+
+const struct command g_cmd_employees[] = {
+    { "employee.list", "List employees ordered by name", PERM_READ, 1, 0, 0,
+      h_employee_list, CMD_ARGS(args_employee_list) },
+    { "employee.get", "Get one employee (personnummer masked)", PERM_READ, 1,
+      0, 0, h_employee_get, CMD_ARGS(args_employee_get) },
+    { "employee.create", "Create an employee", PERM_WRITE, 1, 1, 1,
+      h_employee_create, CMD_ARGS(args_employee_create) },
+    { "employee.update", "Update an employee (merged)", PERM_WRITE, 1, 1, 1,
+      h_employee_update, CMD_ARGS(args_employee_update) },
+    { "employee.archive", "Archive or reactivate an employee", PERM_WRITE, 1,
+      1, 1, h_employee_archive, CMD_ARGS(args_employee_archive) },
+};
+
+const struct cmd_table g_cmd_table_employees = {
+    g_cmd_employees, sizeof g_cmd_employees / sizeof g_cmd_employees[0]
+};