audit: verify voucher chains and attachment hashes

3 files changed, 286 insertions, 10 deletions

diff --git a/src/commands.c b/src/commands.c
index 448d03e..d21d4c8 100644
--- a/src/commands.c
+++ b/src/commands.c
@@ -1545,25 +1545,119 @@ static yyjson_mut_val *h_audit_list(struct req *r)
     return out;
 }
 
+static int verify_attachments(sqlite3 *db, int64_t *checked, int64_t *bad_id,
+                              char **err)
+{
+    sqlite3_stmt *st = NULL;
+    if (sqlite3_prepare_v2(db,
+                           "SELECT id,sha256,size_bytes,content FROM attachments"
+                           " ORDER BY id",
+                           -1, &st, NULL) != SQLITE_OK) {
+        if (err && !*err)
+            *err = xstrdup(sqlite3_errmsg(db));
+        return -1;
+    }
+    int ret = 0;
+    for (;;) {
+        int step = sqlite3_step(st);
+        if (step == SQLITE_DONE)
+            break;
+        if (step != SQLITE_ROW) {
+            if (err && !*err)
+                *err = xstrdup(sqlite3_errmsg(db));
+            ret = -1;
+            break;
+        }
+        int64_t id = sqlite3_column_int64(st, 0);
+        const void *sha = sqlite3_column_blob(st, 1);
+        int sha_n = sqlite3_column_bytes(st, 1);
+        int64_t size = sqlite3_column_int64(st, 2);
+        const void *content = sqlite3_column_blob(st, 3);
+        int content_n = sqlite3_column_bytes(st, 3);
+        unsigned char digest[32];
+        util_sha256(content, (size_t)content_n, digest);
+        (*checked)++;
+        if (sha_n != 32 || memcmp(sha, digest, 32) != 0 ||
+            size != content_n) {
+            if (!*bad_id)
+                *bad_id = id;
+            ret = 1;
+        }
+    }
+    sqlite3_finalize(st);
+    return ret;
+}
+
 static yyjson_mut_val *h_audit_verify(struct req *r)
 {
-    int64_t checked = 0, bad_seq = 0;
+    int full = 0;
+    arg_bool(r->args, "full", &full);
+
+    int64_t audit_checked = 0, audit_bad = 0;
     char *err = NULL;
-    int rc = audit_verify(r->db, &checked, &bad_seq, &err);
-    if (rc < 0) {
+    int audit_rc = audit_verify(r->db, &audit_checked, &audit_bad, &err);
+    if (audit_rc < 0) {
+        yyjson_mut_val *res = fail(r, "INTERNAL", err ? err : "verify failed");
+        free(err);
+        return res;
+    }
+    free(err);
+    err = NULL;
+
+    struct ledger_verify_result lv;
+    int ledger_rc = ledger_verify(r->db, &lv, &err);
+    if (ledger_rc < 0) {
         yyjson_mut_val *res = fail(r, "INTERNAL", err ? err : "verify failed");
         free(err);
         return res;
     }
     free(err);
+    err = NULL;
+
+    int64_t att_checked = 0, att_bad = 0;
+    int att_rc = 0;
+    if (full) {
+        att_rc = verify_attachments(r->db, &att_checked, &att_bad, &err);
+        if (att_rc < 0) {
+            yyjson_mut_val *res =
+                fail(r, "INTERNAL", err ? err : "verify failed");
+            free(err);
+            return res;
+        }
+        free(err);
+    }
+
+    int ok = audit_rc == 0 && ledger_rc == 0 && att_rc == 0;
     yyjson_mut_val *o = yyjson_mut_obj(r->rdoc);
-    yyjson_mut_obj_add_bool(r->rdoc, o, "ok", rc == 0);
-    yyjson_mut_obj_add_strcpy(r->rdoc, o, "kind", "audit");
-    yyjson_mut_obj_add_int(r->rdoc, o, "checked", checked);
-    if (rc == 1)
-        yyjson_mut_obj_add_int(r->rdoc, o, "first_bad_seq", bad_seq);
+    yyjson_mut_obj_add_bool(r->rdoc, o, "ok", ok);
+    yyjson_mut_obj_add_int(r->rdoc, o, "checked", audit_checked);
+    yyjson_mut_obj_add_int(r->rdoc, o, "vouchers_checked",
+                           lv.vouchers_checked);
+    yyjson_mut_obj_add_int(r->rdoc, o, "unbalanced_vouchers",
+                           lv.unbalanced_vouchers);
+    if (audit_bad)
+        yyjson_mut_obj_add_int(r->rdoc, o, "first_bad_seq", audit_bad);
     else
         yyjson_mut_obj_add_null(r->rdoc, o, "first_bad_seq");
+    if (lv.first_bad_voucher_id)
+        yyjson_mut_obj_add_int(r->rdoc, o, "first_bad_voucher_id",
+                               lv.first_bad_voucher_id);
+    else
+        yyjson_mut_obj_add_null(r->rdoc, o, "first_bad_voucher_id");
+    if (lv.first_unbalanced_voucher_id)
+        yyjson_mut_obj_add_int(r->rdoc, o, "first_unbalanced_voucher_id",
+                               lv.first_unbalanced_voucher_id);
+    else
+        yyjson_mut_obj_add_null(r->rdoc, o, "first_unbalanced_voucher_id");
+    if (full) {
+        yyjson_mut_obj_add_int(r->rdoc, o, "attachments_checked",
+                               att_checked);
+        if (att_bad)
+            yyjson_mut_obj_add_int(r->rdoc, o, "first_bad_attachment_id",
+                                   att_bad);
+        else
+            yyjson_mut_obj_add_null(r->rdoc, o, "first_bad_attachment_id");
+    }
     return o;
 }
 
@@ -4438,8 +4532,8 @@ const struct command g_commands[] = {
       h_agent_instructions },
     { "audit.list", "Read the audit log for the active org", PERM_READ, 1, 0,
       0, h_audit_list },
-    { "audit.verify", "Verify the audit hash chain", PERM_READ, 0, 0, 0,
-      h_audit_verify },
+    { "audit.verify", "Verify the voucher and audit hash chains", PERM_READ, 0,
+      0, 0, h_audit_verify },
     { "backup.snapshot", "Create a consistent database snapshot", PERM_ADMIN,
       0, 1, 0, h_backup_snapshot },
     { "account.list", "List the chart of accounts", PERM_READ, 1, 0, 0,
diff --git a/src/ledger.c b/src/ledger.c
index 72402f0..4dacde4 100644
--- a/src/ledger.c
+++ b/src/ledger.c
@@ -66,6 +66,177 @@ void ledger_voucher_hash(const unsigned char prev[32], int64_t org_id,
     buf_free(&b);
 }
 
+static void verify_err(char **err, const char *msg)
+{
+    if (err && !*err)
+        *err = xstrdup(msg);
+}
+
+static char *copy_text(const unsigned char *p)
+{
+    return xstrdup(p ? (const char *)p : "");
+}
+
+static void free_rows(struct ledger_row *rows, size_t nrows)
+{
+    for (size_t i = 0; i < nrows; i++) {
+        free((char *)rows[i].account);
+        free((char *)rows[i].description);
+    }
+    free(rows);
+}
+
+static int verify_org(sqlite3 *db, int64_t org_id,
+                      struct ledger_verify_result *out, char **err)
+{
+    sqlite3_stmt *vs = NULL, *rs = NULL;
+    if (sqlite3_prepare_v2(
+            db,
+            "SELECT v.id,v.hash_prev,v.hash,fy.label,v.series,v.number,v.date,"
+            "v.description FROM vouchers v JOIN fiscal_years fy"
+            " ON fy.org_id=v.org_id AND fy.id=v.fiscal_year_id"
+            " WHERE v.org_id=?1 ORDER BY v.id",
+            -1, &vs, NULL) != SQLITE_OK) {
+        verify_err(err, sqlite3_errmsg(db));
+        return -1;
+    }
+    if (sqlite3_prepare_v2(
+            db,
+            "SELECT a.number,r.debit_ore,r.credit_ore,r.description"
+            " FROM voucher_rows r JOIN accounts a"
+            " ON a.org_id=r.org_id AND a.id=r.account_id"
+            " WHERE r.org_id=?1 AND r.voucher_id=?2 ORDER BY r.line_no",
+            -1, &rs, NULL) != SQLITE_OK) {
+        sqlite3_finalize(vs);
+        verify_err(err, sqlite3_errmsg(db));
+        return -1;
+    }
+
+    unsigned char running[32];
+    memset(running, 0, sizeof running);
+    int ret = 0;
+    sqlite3_bind_int64(vs, 1, org_id);
+    for (;;) {
+        int step = sqlite3_step(vs);
+        if (step == SQLITE_DONE)
+            break;
+        if (step != SQLITE_ROW) {
+            verify_err(err, sqlite3_errmsg(db));
+            ret = -1;
+            break;
+        }
+        int64_t id = sqlite3_column_int64(vs, 0);
+        const void *hprev = sqlite3_column_blob(vs, 1);
+        int hprev_n = sqlite3_column_bytes(vs, 1);
+        const void *hstored = sqlite3_column_blob(vs, 2);
+        int hstored_n = sqlite3_column_bytes(vs, 2);
+        char *label = copy_text(sqlite3_column_text(vs, 3));
+        char *series = copy_text(sqlite3_column_text(vs, 4));
+        int64_t number = sqlite3_column_int64(vs, 5);
+        char *date = copy_text(sqlite3_column_text(vs, 6));
+        char *vdesc = copy_text(sqlite3_column_text(vs, 7));
+
+        struct ledger_row *rows = NULL;
+        size_t nrows = 0, cap = 0;
+        int64_t sum_debit = 0, sum_credit = 0;
+        sqlite3_bind_int64(rs, 1, org_id);
+        sqlite3_bind_int64(rs, 2, id);
+        int rstep;
+        while ((rstep = sqlite3_step(rs)) == SQLITE_ROW) {
+            if (nrows == cap) {
+                cap = cap ? cap * 2 : 8;
+                rows = xrealloc(rows, cap * sizeof *rows);
+            }
+            rows[nrows].account = copy_text(sqlite3_column_text(rs, 0));
+            rows[nrows].debit_ore = sqlite3_column_int64(rs, 1);
+            rows[nrows].credit_ore = sqlite3_column_int64(rs, 2);
+            rows[nrows].description = copy_text(sqlite3_column_text(rs, 3));
+            sum_debit += rows[nrows].debit_ore;
+            sum_credit += rows[nrows].credit_ore;
+            nrows++;
+        }
+        sqlite3_reset(rs);
+        sqlite3_clear_bindings(rs);
+        if (rstep != SQLITE_DONE) {
+            verify_err(err, sqlite3_errmsg(db));
+            free_rows(rows, nrows);
+            free(label);
+            free(series);
+            free(date);
+            free(vdesc);
+            ret = -1;
+            break;
+        }
+
+        unsigned char expect[32];
+        ledger_voucher_hash(running, org_id, label, series, number, date, vdesc,
+                            rows, nrows, expect);
+        out->vouchers_checked++;
+        if (sum_debit != sum_credit) {
+            out->unbalanced_vouchers++;
+            if (!out->first_unbalanced_voucher_id)
+                out->first_unbalanced_voucher_id = id;
+            if (ret == 0)
+                ret = 2;
+        }
+        if (hprev_n != 32 || hstored_n != 32 ||
+            memcmp(hprev, running, 32) != 0 ||
+            memcmp(hstored, expect, 32) != 0) {
+            out->first_bad_voucher_id = id;
+            ret = 1;
+        } else {
+            memcpy(running, expect, 32);
+        }
+        free_rows(rows, nrows);
+        free(label);
+        free(series);
+        free(date);
+        free(vdesc);
+        if (ret == 1)
+            break;
+    }
+    sqlite3_finalize(vs);
+    sqlite3_finalize(rs);
+    return ret;
+}
+
+int ledger_verify(sqlite3 *db, struct ledger_verify_result *out, char **err)
+{
+    memset(out, 0, sizeof *out);
+    sqlite3_stmt *st = NULL;
+    if (sqlite3_prepare_v2(db, "SELECT DISTINCT org_id FROM vouchers"
+                               " ORDER BY org_id",
+                           -1, &st, NULL) != SQLITE_OK) {
+        verify_err(err, sqlite3_errmsg(db));
+        return -1;
+    }
+    int rc = 0;
+    for (;;) {
+        int step = sqlite3_step(st);
+        if (step == SQLITE_DONE)
+            break;
+        if (step != SQLITE_ROW) {
+            verify_err(err, sqlite3_errmsg(db));
+            rc = -1;
+            break;
+        }
+        int64_t org_id = sqlite3_column_int64(st, 0);
+        int r = verify_org(db, org_id, out, err);
+        if (r < 0) {
+            rc = -1;
+            break;
+        }
+        if (r == 1) {
+            rc = 1;
+            break;
+        }
+        if (r == 2)
+            rc = 1;
+    }
+    sqlite3_finalize(st);
+    return rc;
+}
+
 static int idempotent_lookup(sqlite3 *db, int64_t org_id, const char *client_ref,
                              char **out_json)
 {
diff --git a/src/ledger.h b/src/ledger.h
index 30271d0..cf8f9b1 100644
--- a/src/ledger.h
+++ b/src/ledger.h
@@ -48,4 +48,15 @@ void ledger_voucher_hash(const unsigned char prev[32], int64_t org_id,
                          const struct ledger_row *rows, size_t nrows,
                          unsigned char out[32]);
 
+struct ledger_verify_result {
+    int64_t vouchers_checked;
+    int64_t first_bad_voucher_id;        /* 0 = none */
+    int64_t unbalanced_vouchers;
+    int64_t first_unbalanced_voucher_id; /* 0 = none */
+};
+
+/* Recomputes every org's voucher hash chain (SCHEMA.md 7.1) and scans for
+   unbalanced vouchers. 0 = intact, 1 = broken, -1 = database error. */
+int ledger_verify(sqlite3 *db, struct ledger_verify_result *out, char **err);
+
 #endif