commands: db_row_json and db_exec_bound helpers

Co-Authored-By: Claude Opus 5 <noreply@anthropic.com>

12 files changed, 250 insertions, 402 deletions

diff --git a/AGENTS.md b/AGENTS.md
index 5e206ee..f28a904 100644
--- a/AGENTS.md
+++ b/AGENTS.md
@@ -65,6 +65,10 @@ Never rewrite ledger rows in a migration.
 C11, no comments unless the reason is non-obvious, static helpers, no VLAs.
 Bounded buffers with `snprintf`, check every `sqlite3_step`, free what you
 allocate with a clear owner. Warnings must be zero (`-Wall -Wextra …`).
+New handlers use the `cmd_util` helpers instead of hand-written
+prepare/bind/step/column/finalize: `db_row_json` (column spec → object),
+`req_list` (bound SELECT → `{items:[…]}`), `req_exec`/`db_exec_bound` (bound
+INSERT/UPDATE/DELETE with the standard error codes), `db_prepare_bound`.
 User-visible server messages are English; the TUI is Swedish.
 
 ## TUI work
diff --git a/src/cmd_accounts.c b/src/cmd_accounts.c
index b5770b0..bbdd616 100644
--- a/src/cmd_accounts.c
+++ b/src/cmd_accounts.c
@@ -13,26 +13,12 @@
 /* kontoplan                                                           */
 /* ------------------------------------------------------------------ */
 
+#define ACCOUNT_SPEC \
+    "id:i,number:s,name:s,type:s,sru_code:n,vat_code:n,active:b"
+
 static yyjson_mut_val *account_json(yyjson_mut_doc *doc, sqlite3_stmt *st)
 {
-    yyjson_mut_val *o = yyjson_mut_obj(doc);
-    yyjson_mut_obj_add_int(doc, o, "id", sqlite3_column_int64(st, 0));
-    yyjson_mut_obj_add_strcpy(doc, o, "number",
-                              sq(sqlite3_column_text(st, 1)));
-    yyjson_mut_obj_add_strcpy(doc, o, "name", sq(sqlite3_column_text(st, 2)));
-    yyjson_mut_obj_add_strcpy(doc, o, "type", sq(sqlite3_column_text(st, 3)));
-    if (sqlite3_column_type(st, 4) == SQLITE_NULL)
-        yyjson_mut_obj_add_null(doc, o, "sru_code");
-    else
-        yyjson_mut_obj_add_strcpy(doc, o, "sru_code",
-                                  sq(sqlite3_column_text(st, 4)));
-    if (sqlite3_column_type(st, 5) == SQLITE_NULL)
-        yyjson_mut_obj_add_null(doc, o, "vat_code");
-    else
-        yyjson_mut_obj_add_strcpy(doc, o, "vat_code",
-                                  sq(sqlite3_column_text(st, 5)));
-    yyjson_mut_obj_add_bool(doc, o, "active", sqlite3_column_int(st, 6) != 0);
-    return o;
+    return db_row_json(doc, st, ACCOUNT_SPEC);
 }
 
 #define ACCOUNT_COLUMNS "id,number,name,type,sru_code,vat_code,active"
@@ -41,22 +27,12 @@ static yyjson_mut_val *h_account_list(struct req *r)
 {
     int active_only = 0;
     arg_bool(r->args, "active_only", &active_only);
-    yyjson_mut_val *items = yyjson_mut_arr(r->rdoc);
-    sqlite3_stmt *st = NULL;
     const char *sql = active_only
                           ? "SELECT " ACCOUNT_COLUMNS " FROM accounts"
                             " WHERE org_id=?1 AND active=1 ORDER BY number"
                           : "SELECT " ACCOUNT_COLUMNS " FROM accounts"
                             " WHERE org_id=?1 ORDER BY number";
-    if (sqlite3_prepare_v2(r->db, sql, -1, &st, NULL) != SQLITE_OK)
-        return db_error(r);
-    sqlite3_bind_int64(st, 1, r->org_id);
-    while (sqlite3_step(st) == SQLITE_ROW)
-        yyjson_mut_arr_add_val(items, account_json(r->rdoc, st));
-    sqlite3_finalize(st);
-    yyjson_mut_val *o = yyjson_mut_obj(r->rdoc);
-    yyjson_mut_obj_add_val(r->rdoc, o, "items", items);
-    return o;
+    return req_list(r, sql, ACCOUNT_SPEC, "i", r->org_id);
 }
 
 static yyjson_mut_val *h_account_get(struct req *r)
diff --git a/src/cmd_customers.c b/src/cmd_customers.c
index d259a8e..b90b87f 100644
--- a/src/cmd_customers.c
+++ b/src/cmd_customers.c
@@ -19,35 +19,10 @@
 
 static yyjson_mut_val *customer_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, "name",
-                              sq(sqlite3_column_text(st, 1)));
-    yyjson_mut_obj_add_strcpy(r->rdoc, o, "address",
-                              sq(sqlite3_column_text(st, 2)));
-    yyjson_mut_obj_add_strcpy(r->rdoc, o, "postal_code",
-                              sq(sqlite3_column_text(st, 3)));
-    yyjson_mut_obj_add_strcpy(r->rdoc, o, "city",
-                              sq(sqlite3_column_text(st, 4)));
-    yyjson_mut_obj_add_strcpy(r->rdoc, o, "country",
-                              sq(sqlite3_column_text(st, 5)));
-    yyjson_mut_obj_add_strcpy(r->rdoc, o, "vat_nr",
-                              sq(sqlite3_column_text(st, 6)));
-    yyjson_mut_obj_add_strcpy(r->rdoc, o, "email",
-                              sq(sqlite3_column_text(st, 7)));
-    yyjson_mut_obj_add_strcpy(r->rdoc, o, "your_ref",
-                              sq(sqlite3_column_text(st, 8)));
-    yyjson_mut_obj_add_int(r->rdoc, o, "payment_days",
-                           sqlite3_column_int64(st, 9));
-    yyjson_mut_obj_add_strcpy(r->rdoc, o, "notes",
-                              sq(sqlite3_column_text(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;
+    return db_row_json(r->rdoc, st,
+                       "id:i,name:s,address:s,postal_code:s,city:s,country:s,"
+                       "vat_nr:s,email:s,your_ref:s,payment_days:i,notes:s,"
+                       "active:b,created_at:s,updated_at:s");
 }
 
 struct customer_input {
diff --git a/src/cmd_fiscal.c b/src/cmd_fiscal.c
index 3f8e2c2..fdfeddd 100644
--- a/src/cmd_fiscal.c
+++ b/src/cmd_fiscal.c
@@ -13,36 +13,13 @@
 /* fiscal years and period locks                                       */
 /* ------------------------------------------------------------------ */
 
+#define FY_SPEC \
+    "id:i,label:s,start_date:s,end_date:s,status:s,locked_until:n," \
+    "dividend_ore:i,events:s,agm_date:s,dividend_date:s,employees:s,notes:s"
+
 static yyjson_mut_val *fy_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, "label",
-                              sq(sqlite3_column_text(st, 1)));
-    yyjson_mut_obj_add_strcpy(r->rdoc, o, "start_date",
-                              sq(sqlite3_column_text(st, 2)));
-    yyjson_mut_obj_add_strcpy(r->rdoc, o, "end_date",
-                              sq(sqlite3_column_text(st, 3)));
-    yyjson_mut_obj_add_strcpy(r->rdoc, o, "status",
-                              sq(sqlite3_column_text(st, 4)));
-    if (sqlite3_column_type(st, 5) == SQLITE_NULL)
-        yyjson_mut_obj_add_null(r->rdoc, o, "locked_until");
-    else
-        yyjson_mut_obj_add_strcpy(r->rdoc, o, "locked_until",
-                                  sq(sqlite3_column_text(st, 5)));
-    yyjson_mut_obj_add_int(r->rdoc, o, "dividend_ore",
-                           sqlite3_column_int64(st, 6));
-    yyjson_mut_obj_add_strcpy(r->rdoc, o, "events",
-                              sq(sqlite3_column_text(st, 7)));
-    yyjson_mut_obj_add_strcpy(r->rdoc, o, "agm_date",
-                              sq(sqlite3_column_text(st, 8)));
-    yyjson_mut_obj_add_strcpy(r->rdoc, o, "dividend_date",
-                              sq(sqlite3_column_text(st, 9)));
-    yyjson_mut_obj_add_strcpy(r->rdoc, o, "employees",
-                              sq(sqlite3_column_text(st, 10)));
-    yyjson_mut_obj_add_strcpy(r->rdoc, o, "notes",
-                              sq(sqlite3_column_text(st, 11)));
-    return o;
+    return db_row_json(r->rdoc, st, FY_SPEC);
 }
 
 #define FY_COLUMNS                                                      \
@@ -51,21 +28,10 @@ static yyjson_mut_val *fy_json(struct req *r, sqlite3_stmt *st)
 
 static yyjson_mut_val *h_fiscal_year_list(struct req *r)
 {
-    yyjson_mut_val *items = yyjson_mut_arr(r->rdoc);
-    sqlite3_stmt *st = NULL;
-    if (sqlite3_prepare_v2(
-            r->db,
-            "SELECT " FY_COLUMNS " FROM fiscal_years WHERE org_id=?1"
-            " ORDER BY start_date",
-            -1, &st, NULL) != SQLITE_OK)
-        return db_error(r);
-    sqlite3_bind_int64(st, 1, r->org_id);
-    while (sqlite3_step(st) == SQLITE_ROW)
-        yyjson_mut_arr_add_val(items, fy_json(r, st));
-    sqlite3_finalize(st);
-    yyjson_mut_val *o = yyjson_mut_obj(r->rdoc);
-    yyjson_mut_obj_add_val(r->rdoc, o, "items", items);
-    return o;
+    return req_list(r,
+                    "SELECT " FY_COLUMNS " FROM fiscal_years WHERE org_id=?1"
+                    " ORDER BY start_date",
+                    FY_SPEC, "i", r->org_id);
 }
 
 static yyjson_mut_val *h_fiscal_year_get(struct req *r)
@@ -355,20 +321,11 @@ static yyjson_mut_val *h_period_lock(struct req *r)
     if (fy <= 0 || !util_parse_iso_date(until))
         return fail(r, "INVALID_ARGS",
                     "fiscal_year and until (YYYY-MM-DD) are required");
-    sqlite3_stmt *st = NULL;
-    if (sqlite3_prepare_v2(
-            r->db,
-            "UPDATE fiscal_years SET locked_until=?3 WHERE org_id=?1 AND id=?2"
-            " AND ?3 BETWEEN start_date AND end_date",
-            -1, &st, NULL) != SQLITE_OK)
-        return db_error(r);
-    sqlite3_bind_int64(st, 1, r->org_id);
-    sqlite3_bind_int64(st, 2, fy);
-    sqlite3_bind_text(st, 3, until, -1, SQLITE_TRANSIENT);
-    int rc = sqlite3_step(st);
-    sqlite3_finalize(st);
-    if (rc != SQLITE_DONE)
-        return db_sqlite_error(r);
+    if (req_exec(r,
+                 "UPDATE fiscal_years SET locked_until=?3 WHERE org_id=?1 AND id=?2"
+                 " AND ?3 BETWEEN start_date AND end_date",
+                 "iis", r->org_id, fy, until) != 0)
+        return NULL;
     if (sqlite3_changes(r->db) == 0)
         return fail(r, "NOT_FOUND",
                     "fiscal year not found or until is outside it");
diff --git a/src/cmd_invoices.c b/src/cmd_invoices.c
index 4bce5ee..0892ac8 100644
--- a/src/cmd_invoices.c
+++ b/src/cmd_invoices.c
@@ -885,28 +885,10 @@ done:
 
 static yyjson_mut_val *invoice_row_json(struct req *r, sqlite3_stmt *st)
 {
-    yyjson_mut_val *o = yyjson_mut_obj(r->rdoc);
-    yyjson_mut_obj_add_int(r->rdoc, o, "line_no",
-                           sqlite3_column_int64(st, 0));
-    yyjson_mut_obj_add_strcpy(r->rdoc, o, "article_no",
-                              sq(sqlite3_column_text(st, 1)));
-    yyjson_mut_obj_add_strcpy(r->rdoc, o, "description",
-                              sq(sqlite3_column_text(st, 2)));
-    yyjson_mut_obj_add_int(r->rdoc, o, "quantity_milli",
-                           sqlite3_column_int64(st, 3));
-    yyjson_mut_obj_add_strcpy(r->rdoc, o, "unit",
-                              sq(sqlite3_column_text(st, 4)));
-    yyjson_mut_obj_add_int(r->rdoc, o, "unit_price_ore",
-                           sqlite3_column_int64(st, 5));
-    yyjson_mut_obj_add_int(r->rdoc, o, "amount_ore",
-                           sqlite3_column_int64(st, 6));
-    yyjson_mut_obj_add_strcpy(r->rdoc, o, "note",
-                              sq(sqlite3_column_text(st, 7)));
-    yyjson_mut_obj_add_strcpy(r->rdoc, o, "vat_code",
-                              sq(sqlite3_column_text(st, 8)));
-    yyjson_mut_obj_add_strcpy(r->rdoc, o, "account",
-                              sq(sqlite3_column_text(st, 9)));
-    return o;
+    return db_row_json(r->rdoc, st,
+                       "line_no:i,article_no:s,description:s,"
+                       "quantity_milli:i,unit:s,unit_price_ore:i,"
+                       "amount_ore:i,note:s,vat_code:s,account:s");
 }
 
 #define INVOICE_ROW_COLUMNS                                               \
diff --git a/src/cmd_org.c b/src/cmd_org.c
index 8adaf3b..0fe8601 100644
--- a/src/cmd_org.c
+++ b/src/cmd_org.c
@@ -17,33 +17,11 @@
 
 static yyjson_mut_val *org_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, "name", sq(sqlite3_column_text(st, 1)));
-    yyjson_mut_obj_add_strcpy(r->rdoc, o, "org_nr", sq(sqlite3_column_text(st, 2)));
-    yyjson_mut_obj_add_strcpy(r->rdoc, o, "vat_nr", sq(sqlite3_column_text(st, 3)));
-    yyjson_mut_obj_add_strcpy(r->rdoc, o, "address",
-                           sq(sqlite3_column_text(st, 4)));
-    yyjson_mut_obj_add_strcpy(r->rdoc, o, "postal_code",
-                           sq(sqlite3_column_text(st, 5)));
-    yyjson_mut_obj_add_strcpy(r->rdoc, o, "city", sq(sqlite3_column_text(st, 6)));
-    yyjson_mut_obj_add_strcpy(r->rdoc, o, "country",
-                           sq(sqlite3_column_text(st, 7)));
-    yyjson_mut_obj_add_strcpy(r->rdoc, o, "email", sq(sqlite3_column_text(st, 8)));
-    yyjson_mut_obj_add_strcpy(r->rdoc, o, "phone", sq(sqlite3_column_text(st, 9)));
-    yyjson_mut_obj_add_int(r->rdoc, o, "fiscal_year_start_month",
-                           sqlite3_column_int(st, 10));
-    yyjson_mut_obj_add_strcpy(r->rdoc, o, "moms_period",
-                           sq(sqlite3_column_text(st, 11)));
-    yyjson_mut_obj_add_strcpy(r->rdoc, o, "framework",
-                           sq(sqlite3_column_text(st, 12)));
-    yyjson_mut_obj_add_strcpy(r->rdoc, o, "created_at",
-                           sq(sqlite3_column_text(st, 13)));
-    yyjson_mut_obj_add_strcpy(r->rdoc, o, "description",
-                              sq(sqlite3_column_text(st, 14)));
-    yyjson_mut_obj_add_int(r->rdoc, o, "shares",
-                           sqlite3_column_int64(st, 15));
-    return o;
+    return db_row_json(r->rdoc, st,
+                       "id:i,name:s,org_nr:s,vat_nr:s,address:s,"
+                       "postal_code:s,city:s,country:s,email:s,phone:s,"
+                       "fiscal_year_start_month:i,moms_period:s,framework:s,"
+                       "created_at:s,description:s,shares:i");
 }
 
 #define ORG_COLUMNS                                                    \
@@ -313,29 +291,11 @@ static yyjson_mut_val *h_org_update(struct req *r)
 
 static yyjson_mut_val *h_org_member_list(struct req *r)
 {
-    yyjson_mut_val *items = yyjson_mut_arr(r->rdoc);
-    sqlite3_stmt *st = NULL;
-    if (sqlite3_prepare_v2(
-            r->db,
-            "SELECT u.id,u.username,u.display_name,m.role FROM memberships m"
-            " JOIN users u ON u.id=m.user_id WHERE m.org_id=?1 ORDER BY u.id",
-            -1, &st, NULL) != SQLITE_OK)
-        return db_error(r);
-    sqlite3_bind_int64(st, 1, r->org_id);
-    while (sqlite3_step(st) == SQLITE_ROW) {
-        yyjson_mut_val *o = yyjson_mut_arr_add_obj(r->rdoc, items);
-        yyjson_mut_obj_add_int(r->rdoc, o, "user_id", sqlite3_column_int64(st, 0));
-        yyjson_mut_obj_add_strcpy(r->rdoc, o, "username",
-                               sq(sqlite3_column_text(st, 1)));
-        yyjson_mut_obj_add_strcpy(r->rdoc, o, "display_name",
-                               sq(sqlite3_column_text(st, 2)));
-        yyjson_mut_obj_add_strcpy(r->rdoc, o, "role",
-                               sq(sqlite3_column_text(st, 3)));
-    }
-    sqlite3_finalize(st);
-    yyjson_mut_val *out = yyjson_mut_obj(r->rdoc);
-    yyjson_mut_obj_add_val(r->rdoc, out, "items", items);
-    return out;
+    return req_list(r,
+                    "SELECT u.id,u.username,u.display_name,m.role FROM memberships m"
+                    " JOIN users u ON u.id=m.user_id WHERE m.org_id=?1 ORDER BY u.id",
+                    "user_id:i,username:s,display_name:s,role:s", "i",
+                    r->org_id);
 }
 
 static int64_t username_to_id(sqlite3 *db, const char *username)
@@ -461,17 +421,9 @@ static yyjson_mut_val *h_org_member_remove(struct req *r)
         return fail(r, "CONFLICT", "cannot remove the last owner");
     }
     free(current);
-    sqlite3_stmt *st = NULL;
-    if (sqlite3_prepare_v2(
-            r->db, "DELETE FROM memberships WHERE org_id=?1 AND user_id=?2", -1,
-            &st, NULL) != SQLITE_OK)
-        return db_error(r);
-    sqlite3_bind_int64(st, 1, r->org_id);
-    sqlite3_bind_int64(st, 2, uid);
-    int rc = sqlite3_step(st);
-    sqlite3_finalize(st);
-    if (rc != SQLITE_DONE)
-        return db_sqlite_error(r);
+    if (req_exec(r, "DELETE FROM memberships WHERE org_id=?1 AND user_id=?2",
+                 "ii", r->org_id, uid) != 0)
+        return NULL;
     char *reqjson = audit_args_json(r->args);
     audit_append(r->db, r->org_id, r->sess->user_id, 0, "member.remove",
                  reqjson, "OK", NULL);
@@ -483,33 +435,19 @@ static yyjson_mut_val *h_org_member_remove(struct req *r)
 /* board members (årsredovisning signatures)                           */
 /* ------------------------------------------------------------------ */
 
+#define BOARD_SPEC "id:i,name:s,title:s"
+
 static yyjson_mut_val *board_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, "name",
-                              sq(sqlite3_column_text(st, 1)));
-    yyjson_mut_obj_add_strcpy(r->rdoc, o, "title",
-                              sq(sqlite3_column_text(st, 2)));
-    return o;
+    return db_row_json(r->rdoc, st, BOARD_SPEC);
 }
 
 static yyjson_mut_val *h_board_list(struct req *r)
 {
-    yyjson_mut_val *items = yyjson_mut_arr(r->rdoc);
-    sqlite3_stmt *st = NULL;
-    if (sqlite3_prepare_v2(r->db,
-                           "SELECT id,name,title FROM board_members"
-                           " WHERE org_id=?1 ORDER BY id",
-                           -1, &st, NULL) != SQLITE_OK)
-        return db_error(r);
-    sqlite3_bind_int64(st, 1, r->org_id);
-    while (sqlite3_step(st) == SQLITE_ROW)
-        yyjson_mut_arr_add_val(items, board_json(r, st));
-    sqlite3_finalize(st);
-    yyjson_mut_val *o = yyjson_mut_obj(r->rdoc);
-    yyjson_mut_obj_add_val(r->rdoc, o, "items", items);
-    return o;
+    return req_list(r,
+                    "SELECT id,name,title FROM board_members"
+                    " WHERE org_id=?1 ORDER BY id",
+                    BOARD_SPEC, "i", r->org_id);
 }
 
 static yyjson_mut_val *board_get(struct req *r, int64_t id)
@@ -628,18 +566,9 @@ static yyjson_mut_val *h_board_remove(struct req *r)
         yyjson_mut_obj_add_int(r->rdoc, o, "id", id);
         return o;
     }
-    sqlite3_stmt *st = NULL;
-    if (sqlite3_prepare_v2(r->db,
-                           "DELETE FROM board_members"
-                           " 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);
-    int rc = sqlite3_step(st);
-    sqlite3_finalize(st);
-    if (rc != SQLITE_DONE)
-        return db_sqlite_error(r);
+    if (req_exec(r, "DELETE FROM board_members WHERE org_id=?1 AND id=?2",
+                 "ii", r->org_id, id) != 0)
+        return NULL;
     if (sqlite3_changes(r->db) == 0)
         return fail(r, "NOT_FOUND", "board member not found");
     char *reqjson = audit_args_json(r->args);
diff --git a/src/cmd_rules.c b/src/cmd_rules.c
index d9a1594..080009d 100644
--- a/src/cmd_rules.c
+++ b/src/cmd_rules.c
@@ -327,17 +327,9 @@ static yyjson_mut_val *h_report_rule_delete(struct req *r)
         yyjson_mut_obj_add_bool(r->rdoc, o, "dry_run", true);
         return o;
     }
-    sqlite3_stmt *st = NULL;
-    if (sqlite3_prepare_v2(
-            r->db, "DELETE FROM report_rules 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);
-    int rc = sqlite3_step(st);
-    sqlite3_finalize(st);
-    if (rc != SQLITE_DONE)
-        return db_sqlite_error(r);
+    if (req_exec(r, "DELETE FROM report_rules WHERE org_id=?1 AND id=?2",
+                 "ii", r->org_id, id) != 0)
+        return NULL;
     if (sqlite3_changes(r->db) == 0)
         return fail(r, "NOT_FOUND", "report rule not found");
     char *reqjson = audit_args_json(r->args);
diff --git a/src/cmd_templates.c b/src/cmd_templates.c
index 7e40886..eb98ea4 100644
--- a/src/cmd_templates.c
+++ b/src/cmd_templates.c
@@ -93,25 +93,14 @@ static int parse_template_rows(struct req *r, yyjson_val *rowsv,
 
 static yyjson_mut_val *tpl_rows_json(struct req *r, sqlite3_stmt *st)
 {
-    yyjson_mut_val *o = yyjson_mut_obj(r->rdoc);
-    yyjson_mut_obj_add_int(r->rdoc, o, "line_no", sqlite3_column_int64(st, 0));
-    yyjson_mut_obj_add_strcpy(r->rdoc, o, "account",
-                              sq(sqlite3_column_text(st, 1)));
-    yyjson_mut_obj_add_strcpy(r->rdoc, o, "name",
-                              sq(sqlite3_column_text(st, 2)));
-    yyjson_mut_obj_add_strcpy(r->rdoc, o, "formula",
-                              sq(sqlite3_column_text(st, 3)));
-    yyjson_mut_obj_add_strcpy(r->rdoc, o, "description",
-                              sq(sqlite3_column_text(st, 4)));
-    return o;
+    return db_row_json(r->rdoc, st,
+                       "line_no:i,account:s,name:s,formula:s,description:s");
 }
 
 static yyjson_mut_val *h_template_list(struct req *r)
 {
     int active_only = 0;
     arg_bool(r->args, "active_only", &active_only);
-    yyjson_mut_val *items = yyjson_mut_arr(r->rdoc);
-    sqlite3_stmt *st = NULL;
     const char *sql =
         active_only
             ? "SELECT t.id,t.name,t.series,t.description,t.active,"
@@ -123,27 +112,9 @@ static yyjson_mut_val *h_template_list(struct req *r)
               "(SELECT count(*) FROM voucher_template_rows tr"
               " WHERE tr.org_id=t.org_id AND tr.template_id=t.id)"
               " FROM voucher_templates t WHERE t.org_id=?1 ORDER BY t.name";
-    if (sqlite3_prepare_v2(r->db, sql, -1, &st, NULL) != SQLITE_OK)
-        return db_error(r);
-    sqlite3_bind_int64(st, 1, r->org_id);
-    while (sqlite3_step(st) == SQLITE_ROW) {
-        yyjson_mut_val *o = yyjson_mut_arr_add_obj(r->rdoc, items);
-        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, "series",
-                                  sq(sqlite3_column_text(st, 2)));
-        yyjson_mut_obj_add_strcpy(r->rdoc, o, "description",
-                                  sq(sqlite3_column_text(st, 3)));
-        yyjson_mut_obj_add_bool(r->rdoc, o, "active",
-                                sqlite3_column_int(st, 4) != 0);
-        yyjson_mut_obj_add_int(r->rdoc, o, "row_count",
-                               sqlite3_column_int64(st, 5));
-    }
-    sqlite3_finalize(st);
-    yyjson_mut_val *out = yyjson_mut_obj(r->rdoc);
-    yyjson_mut_obj_add_val(r->rdoc, out, "items", items);
-    return out;
+    return req_list(r, sql,
+                    "id:i,name:s,series:s,description:s,active:b,row_count:i",
+                    "i", r->org_id);
 }
 
 static yyjson_mut_val *h_template_get(struct req *r)
@@ -450,23 +421,17 @@ static yyjson_mut_val *h_template_archive(struct req *r)
     const char *name = arg_str(r->args, "name");
     if (id <= 0 && !name)
         return fail(r, "INVALID_ARGS", "id or name is required");
-    sqlite3_stmt *st = NULL;
-    const char *sql =
-        id > 0 ? "UPDATE voucher_templates SET active=0 WHERE org_id=?1"
-                 " AND id=?2"
-               : "UPDATE voucher_templates SET active=0 WHERE org_id=?1"
-                 " AND name=?2";
-    if (sqlite3_prepare_v2(r->db, sql, -1, &st, NULL) != SQLITE_OK)
-        return db_error(r);
-    sqlite3_bind_int64(st, 1, r->org_id);
-    if (id > 0)
-        sqlite3_bind_int64(st, 2, id);
-    else
-        sqlite3_bind_text(st, 2, name, -1, SQLITE_TRANSIENT);
-    int rc = sqlite3_step(st);
-    sqlite3_finalize(st);
-    if (rc != SQLITE_DONE)
-        return db_sqlite_error(r);
+    int rc = id > 0
+        ? req_exec(r,
+                   "UPDATE voucher_templates SET active=0 WHERE org_id=?1"
+                   " AND id=?2",
+                   "ii", r->org_id, id)
+        : req_exec(r,
+                   "UPDATE voucher_templates SET active=0 WHERE org_id=?1"
+                   " AND name=?2",
+                   "is", r->org_id, name);
+    if (rc != 0)
+        return NULL;
     if (sqlite3_changes(r->db) == 0)
         return fail(r, "NOT_FOUND", "template not found");
     char *reqjson = audit_args_json(r->args);
diff --git a/src/cmd_users.c b/src/cmd_users.c
index 7fae2cc..04ee42c 100644
--- a/src/cmd_users.c
+++ b/src/cmd_users.c
@@ -50,35 +50,12 @@ static yyjson_mut_val *h_user_create(struct req *r)
 
 static yyjson_mut_val *h_user_list(struct req *r)
 {
-    yyjson_mut_val *items = yyjson_mut_arr(r->rdoc);
-    sqlite3_stmt *st = NULL;
-    if (sqlite3_prepare_v2(
-            r->db,
-            "SELECT id,username,display_name,is_admin,created_at,disabled_at"
-            " FROM users ORDER BY id",
-            -1, &st, NULL) != SQLITE_OK)
-        return db_error(r);
-    while (sqlite3_step(st) == SQLITE_ROW) {
-        yyjson_mut_val *o = yyjson_mut_arr_add_obj(r->rdoc, items);
-        yyjson_mut_obj_add_int(r->rdoc, o, "id", sqlite3_column_int64(st, 0));
-        yyjson_mut_obj_add_strcpy(r->rdoc, o, "username",
-                               sq(sqlite3_column_text(st, 1)));
-        yyjson_mut_obj_add_strcpy(r->rdoc, o, "display_name",
-                               sq(sqlite3_column_text(st, 2)));
-        yyjson_mut_obj_add_bool(r->rdoc, o, "is_admin",
-                                sqlite3_column_int(st, 3) != 0);
-        yyjson_mut_obj_add_strcpy(r->rdoc, o, "created_at",
-                               sq(sqlite3_column_text(st, 4)));
-        if (sqlite3_column_type(st, 5) == SQLITE_NULL)
-            yyjson_mut_obj_add_null(r->rdoc, o, "disabled_at");
-        else
-            yyjson_mut_obj_add_strcpy(r->rdoc, o, "disabled_at",
-                                   sq(sqlite3_column_text(st, 5)));
-    }
-    sqlite3_finalize(st);
-    yyjson_mut_val *out = yyjson_mut_obj(r->rdoc);
-    yyjson_mut_obj_add_val(r->rdoc, out, "items", items);
-    return out;
+    return req_list(r,
+                    "SELECT id,username,display_name,is_admin,created_at,disabled_at"
+                    " FROM users ORDER BY id",
+                    "id:i,username:s,display_name:s,is_admin:b,created_at:s,"
+                    "disabled_at:n",
+                    "");
 }
 
 static int scope_allowed(const char *role, const char *scope)
@@ -173,46 +150,13 @@ static yyjson_mut_val *h_token_create(struct req *r)
 
 static yyjson_mut_val *h_token_list(struct req *r)
 {
-    yyjson_mut_val *items = yyjson_mut_arr(r->rdoc);
-    sqlite3_stmt *st = NULL;
-    if (sqlite3_prepare_v2(
-            r->db,
-            "SELECT id,label,scopes,created_at,expires_at,last_used_at,"
-            "revoked_at FROM api_tokens WHERE org_id=?1 AND user_id=?2"
-            " ORDER BY id",
-            -1, &st, NULL) != SQLITE_OK)
-        return db_error(r);
-    sqlite3_bind_int64(st, 1, r->org_id);
-    sqlite3_bind_int64(st, 2, r->sess->user_id);
-    while (sqlite3_step(st) == SQLITE_ROW) {
-        yyjson_mut_val *o = yyjson_mut_arr_add_obj(r->rdoc, items);
-        yyjson_mut_obj_add_int(r->rdoc, o, "id", sqlite3_column_int64(st, 0));
-        yyjson_mut_obj_add_strcpy(r->rdoc, o, "label",
-                               sq(sqlite3_column_text(st, 1)));
-        yyjson_mut_obj_add_strcpy(r->rdoc, o, "scopes",
-                               sq(sqlite3_column_text(st, 2)));
-        yyjson_mut_obj_add_strcpy(r->rdoc, o, "created_at",
-                               sq(sqlite3_column_text(st, 3)));
-        if (sqlite3_column_type(st, 4) == SQLITE_NULL)
-            yyjson_mut_obj_add_null(r->rdoc, o, "expires_at");
-        else
-            yyjson_mut_obj_add_strcpy(r->rdoc, o, "expires_at",
-                                   sq(sqlite3_column_text(st, 4)));
-        if (sqlite3_column_type(st, 5) == SQLITE_NULL)
-            yyjson_mut_obj_add_null(r->rdoc, o, "last_used_at");
-        else
-            yyjson_mut_obj_add_strcpy(r->rdoc, o, "last_used_at",
-                                   sq(sqlite3_column_text(st, 5)));
-        if (sqlite3_column_type(st, 6) == SQLITE_NULL)
-            yyjson_mut_obj_add_null(r->rdoc, o, "revoked_at");
-        else
-            yyjson_mut_obj_add_strcpy(r->rdoc, o, "revoked_at",
-                                   sq(sqlite3_column_text(st, 6)));
-    }
-    sqlite3_finalize(st);
-    yyjson_mut_val *out = yyjson_mut_obj(r->rdoc);
-    yyjson_mut_obj_add_val(r->rdoc, out, "items", items);
-    return out;
+    return req_list(r,
+                    "SELECT id,label,scopes,created_at,expires_at,last_used_at,"
+                    "revoked_at FROM api_tokens WHERE org_id=?1 AND user_id=?2"
+                    " ORDER BY id",
+                    "id:i,label:s,scopes:s,created_at:s,expires_at:n,"
+                    "last_used_at:n,revoked_at:n",
+                    "ii", r->org_id, r->sess->user_id);
 }
 
 static yyjson_mut_val *h_token_revoke(struct req *r)
@@ -223,24 +167,16 @@ static yyjson_mut_val *h_token_revoke(struct req *r)
     char ts[32];
     util_iso8601(util_now(), ts, sizeof ts);
     int is_owner = strcmp(r->role, "owner") == 0;
-    sqlite3_stmt *st = NULL;
-    if (sqlite3_prepare_v2(
-            r->db,
-            is_owner
-                ? "UPDATE api_tokens SET revoked_at=?1 WHERE id=?2 AND org_id=?3"
-                : "UPDATE api_tokens SET revoked_at=?1 WHERE id=?2 AND org_id=?3"
-                  " AND user_id=?4",
-            -1, &st, NULL) != SQLITE_OK)
-        return db_error(r);
-    sqlite3_bind_text(st, 1, ts, -1, SQLITE_TRANSIENT);
-    sqlite3_bind_int64(st, 2, id);
-    sqlite3_bind_int64(st, 3, r->org_id);
-    if (!is_owner)
-        sqlite3_bind_int64(st, 4, r->sess->user_id);
-    int rc = sqlite3_step(st);
-    sqlite3_finalize(st);
-    if (rc != SQLITE_DONE)
-        return db_sqlite_error(r);
+    int rc = is_owner
+        ? req_exec(r,
+                   "UPDATE api_tokens SET revoked_at=?1 WHERE id=?2 AND org_id=?3",
+                   "sii", ts, id, r->org_id)
+        : req_exec(r,
+                   "UPDATE api_tokens SET revoked_at=?1 WHERE id=?2 AND org_id=?3"
+                   " AND user_id=?4",
+                   "siii", ts, id, r->org_id, r->sess->user_id);
+    if (rc != 0)
+        return NULL;
     if (sqlite3_changes(r->db) == 0)
         return fail(r, "NOT_FOUND", "token not found");
     audit_append(r->db, r->org_id, r->sess->user_id, id, "token.revoke", "{}",
diff --git a/src/cmd_util.c b/src/cmd_util.c
index 5347d48..abe56d6 100644
--- a/src/cmd_util.c
+++ b/src/cmd_util.c
@@ -216,6 +216,138 @@ void bind_text_or_null(sqlite3_stmt *st, int idx, const char *s)
         sqlite3_bind_null(st, idx);
 }
 
+yyjson_mut_val *db_row_json(yyjson_mut_doc *doc, sqlite3_stmt *st,
+                            const char *spec)
+{
+    yyjson_mut_val *o = yyjson_mut_obj(doc);
+    const char *p = spec;
+    for (int col = 0; *p; col++) {
+        while (*p == ',' || *p == ' ')
+            p++;
+        if (!*p)
+            break;
+        const char *name = p;
+        while (*p && *p != ':')
+            p++;
+        size_t nlen = (size_t)(p - name);
+        char type = *p == ':' ? p[1] : 's';
+        while (*p && *p != ',' && *p != ' ')
+            p++;
+        yyjson_mut_val *key = yyjson_mut_strncpy(doc, name, nlen);
+        yyjson_mut_val *val;
+        int is_null = sqlite3_column_type(st, col) == SQLITE_NULL;
+        switch (type) {
+        case 'i':
+            val = yyjson_mut_int(doc, sqlite3_column_int64(st, col));
+            break;
+        case 'b':
+            val = yyjson_mut_bool(doc, sqlite3_column_int(st, col) != 0);
+            break;
+        case 'n':
+            val = is_null ? yyjson_mut_null(doc)
+                          : yyjson_mut_strcpy(doc,
+                                              sq(sqlite3_column_text(st, col)));
+            break;
+        case 'r':
+            val = is_null ? yyjson_mut_null(doc)
+                          : yyjson_mut_int(doc, sqlite3_column_int64(st, col));
+            break;
+        default:
+            val = yyjson_mut_strcpy(doc, sq(sqlite3_column_text(st, col)));
+            break;
+        }
+        yyjson_mut_obj_add(o, key, val);
+    }
+    return o;
+}
+
+static sqlite3_stmt *prepare_boundv(sqlite3 *db, const char *sql,
+                                    const char *types, va_list ap)
+{
+    sqlite3_stmt *st = NULL;
+    if (sqlite3_prepare_v2(db, sql, -1, &st, NULL) != SQLITE_OK)
+        return NULL;
+    for (int i = 0; types[i]; i++) {
+        switch (types[i]) {
+        case 'i':
+            sqlite3_bind_int64(st, i + 1, va_arg(ap, int64_t));
+            break;
+        case 's':
+            sqlite3_bind_text(st, i + 1, va_arg(ap, const char *), -1,
+                              SQLITE_TRANSIENT);
+            break;
+        case 'n':
+            bind_text_or_null(st, i + 1, va_arg(ap, const char *));
+            break;
+        default:
+            sqlite3_finalize(st);
+            return NULL;
+        }
+    }
+    return st;
+}
+
+sqlite3_stmt *db_prepare_bound(sqlite3 *db, const char *sql, const char *types,
+                               ...)
+{
+    va_list ap;
+    va_start(ap, types);
+    sqlite3_stmt *st = prepare_boundv(db, sql, types, ap);
+    va_end(ap);
+    return st;
+}
+
+static int exec_boundv(sqlite3 *db, const char *sql, const char *types,
+                       va_list ap)
+{
+    sqlite3_stmt *st = prepare_boundv(db, sql, types, ap);
+    if (!st)
+        return -1;
+    int rc = sqlite3_step(st);
+    sqlite3_finalize(st);
+    return rc == SQLITE_DONE ? 0 : rc;
+}
+
+int db_exec_bound(sqlite3 *db, const char *sql, const char *types, ...)
+{
+    va_list ap;
+    va_start(ap, types);
+    int rc = exec_boundv(db, sql, types, ap);
+    va_end(ap);
+    return rc;
+}
+
+int req_exec(struct req *r, const char *sql, const char *types, ...)
+{
+    va_list ap;
+    va_start(ap, types);
+    int rc = exec_boundv(r->db, sql, types, ap);
+    va_end(ap);
+    if (rc == -1)
+        db_error(r);
+    else if (rc != 0)
+        db_sqlite_error(r);
+    return rc == 0 ? 0 : -1;
+}
+
+yyjson_mut_val *req_list(struct req *r, const char *sql, const char *spec,
+                         const char *types, ...)
+{
+    va_list ap;
+    va_start(ap, types);
+    sqlite3_stmt *st = prepare_boundv(r->db, sql, types, ap);
+    va_end(ap);
+    if (!st)
+        return db_error(r);
+    yyjson_mut_val *items = yyjson_mut_arr(r->rdoc);
+    while (sqlite3_step(st) == SQLITE_ROW)
+        yyjson_mut_arr_add_val(items, db_row_json(r->rdoc, st, spec));
+    sqlite3_finalize(st);
+    yyjson_mut_val *o = yyjson_mut_obj(r->rdoc);
+    yyjson_mut_obj_add_val(r->rdoc, o, "items", items);
+    return o;
+}
+
 int mkdir_p(const char *path, mode_t mode)
 {
     char tmp[4096];
diff --git a/src/cmd_util.h b/src/cmd_util.h
index ccab9f4..6187efe 100644
--- a/src/cmd_util.h
+++ b/src/cmd_util.h
@@ -25,6 +25,23 @@ yyjson_mut_val *failf(struct req *r, const char *code, const char *fmt, ...)
 yyjson_mut_val *db_error(struct req *r);
 yyjson_mut_val *db_sqlite_error(struct req *r);
 void bind_text_or_null(sqlite3_stmt *st, int idx, const char *s);
+/* spec: "id:i,name:s,active:b" — i int, s text ("" for NULL), b bool,
+ * n nullable text (JSON null for NULL), r nullable int; columns from 0. */
+yyjson_mut_val *db_row_json(yyjson_mut_doc *doc, sqlite3_stmt *st,
+                            const char *spec);
+/* types: one letter per bind — i int64_t, s const char *, n nullable
+ * const char *. Returns the statement bound at ?1.., NULL on prepare error. */
+sqlite3_stmt *db_prepare_bound(sqlite3 *db, const char *sql, const char *types,
+                               ...);
+/* 0 on SQLITE_DONE, -1 when prepare fails, else the step result. */
+int db_exec_bound(sqlite3 *db, const char *sql, const char *types, ...);
+/* db_exec_bound with the request's error set: db_error on prepare failure,
+ * db_sqlite_error on any other step result. Returns 0 on success. */
+int req_exec(struct req *r, const char *sql, const char *types, ...);
+/* {"items":[db_row_json(spec) per row]} for a bound SELECT; NULL with
+ * db_error set when prepare fails. */
+yyjson_mut_val *req_list(struct req *r, const char *sql, const char *spec,
+                         const char *types, ...);
 int mkdir_p(const char *path, mode_t mode);
 yyjson_mut_val *json_to_mut(yyjson_mut_doc *doc, const char *json);
 int is_digits(const char *s);
diff --git a/src/cmd_vouchers.c b/src/cmd_vouchers.c
index 8977878..f6f0146 100644
--- a/src/cmd_vouchers.c
+++ b/src/cmd_vouchers.c
@@ -265,28 +265,11 @@ static yyjson_mut_val *h_voucher_post(struct req *r)
 
 static yyjson_mut_val *voucher_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_int(r->rdoc, o, "fiscal_year_id",
-                           sqlite3_column_int64(st, 1));
-    yyjson_mut_obj_add_strcpy(r->rdoc, o, "series",
-                              sq(sqlite3_column_text(st, 2)));
-    yyjson_mut_obj_add_int(r->rdoc, o, "number", sqlite3_column_int64(st, 3));
-    yyjson_mut_obj_add_strcpy(r->rdoc, o, "date",
-                              sq(sqlite3_column_text(st, 4)));
-    yyjson_mut_obj_add_strcpy(r->rdoc, o, "description",
-                              sq(sqlite3_column_text(st, 5)));
-    yyjson_mut_obj_add_strcpy(r->rdoc, o, "source",
-                              sq(sqlite3_column_text(st, 6)));
-    if (sqlite3_column_type(st, 7) == SQLITE_NULL)
-        yyjson_mut_obj_add_null(r->rdoc, o, "corrects_voucher_id");
-    else
-        yyjson_mut_obj_add_int(r->rdoc, o, "corrects_voucher_id",
-                               sqlite3_column_int64(st, 7));
-    yyjson_mut_obj_add_strcpy(r->rdoc, o, "created_at",
-                              sq(sqlite3_column_text(st, 8)));
-    yyjson_mut_obj_add_int(r->rdoc, o, "created_by_user",
-                           sqlite3_column_int64(st, 9));
+    yyjson_mut_val *o = db_row_json(r->rdoc, st,
+                                    "id:i,fiscal_year_id:i,series:s,number:i,"
+                                    "date:s,description:s,source:s,"
+                                    "corrects_voucher_id:r,created_at:s,"
+                                    "created_by_user:i");
     char hex[65];
     const void *hb = sqlite3_column_blob(st, 10);
     if (hb && sqlite3_column_bytes(st, 10) == 32) {