summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--docs/SCHEMA.md12
-rw-r--r--docs/STATE.md13
-rw-r--r--src/db.c29
-rw-r--r--src/db.h2
-rw-r--r--tests/test_core.c28
5 files changed, 73 insertions, 11 deletions
diff --git a/docs/SCHEMA.md b/docs/SCHEMA.md
index 89dd0b1..2a85521 100644
--- a/docs/SCHEMA.md
+++ b/docs/SCHEMA.md
@@ -306,6 +306,11 @@ CREATE TABLE attachments (
UNIQUE (org_id, sha256, filename)
) STRICT;
+CREATE TRIGGER attachments_no_update BEFORE UPDATE ON attachments
+BEGIN SELECT RAISE(ABORT, 'attachments are append-only'); END;
+CREATE TRIGGER attachments_no_delete BEFORE DELETE ON attachments
+BEGIN SELECT RAISE(ABORT, 'attachments are append-only'); END;
+
CREATE TABLE voucher_attachments (
org_id INTEGER NOT NULL,
voucher_id INTEGER NOT NULL,
@@ -317,8 +322,11 @@ CREATE TABLE voucher_attachments (
) STRICT;
```
-Attachments are content-addressed and immutable; linking is an insert into
-`voucher_attachments` and is itself audited. Unlinked attachments form the
+Attachments are content-addressed and immutable — the triggers abort updates
+and deletes even for a root `sqlite3` session, and `audit.verify full:true`
+re-hashes the content; linking is an insert into
+`voucher_attachments` and is itself audited (the link table stays mutable so
+underlag can be unlinked). Unlinked attachments form the
inbox the TUI shows. The 7-year archive rule means content must never be
garbage-collected; deduplication by hash keeps repeated receipts cheap.
diff --git a/docs/STATE.md b/docs/STATE.md
index 526c1a6..e210e1c 100644
--- a/docs/STATE.md
+++ b/docs/STATE.md
@@ -172,9 +172,9 @@ check.
and `full:true` re-hashes attachment content; result carries
`vouchers_checked`, `unbalanced_vouchers`, `attachments_checked` and the
first bad voucher/audit/attachment id. TUI Revision shows both counts and
- the bad ids. **Found while testing: `attachments` has no
- append-only triggers** (COMPLIANCE.md §2 claims it does); content changes
- are detected only by `audit.verify full:true`.
+ the bad ids. Fixed in schema v7: `attachments` now has
+ `no_update`/`no_delete` triggers; `audit.verify full:true` still detects
+ on-disk tampering.
5. ~~`report.general_ledger` and `report.voucher_list`~~ implemented
(Huvudbok, Verifikationslista) with Kapitas-style TUI tables; the ledger
API supports `accounts`/`from`/`to`, the list an optional `series`.
@@ -207,7 +207,7 @@ check.
## Environment / how to run
-- **Deployed**: `scripts/deploy.sh` (latest `v0.1.44`, healthy on nas).
+- **Deployed**: `scripts/deploy.sh` (latest `v0.1.48`, healthy on nas).
Live daemon `tls:bokf.makandra.eu:8788`, token
`~/.config/bokf/migration-token` (scopes `read,write`; owner-only actions
like closing years must be done by the human in the TUI). Git remote
@@ -254,8 +254,9 @@ check.
- Never commit unless the human asks.
- SQLite files must not be backed up live with restic; use
`backup.snapshot` (`VACUUM INTO`) and point restic at the snapshots.
-- Schema version is 6 (v3 moms rules; v4/v6 year info; v5 org
- description/shares + board members); forward migrations are in `db.c`.
+- Schema version is 7 (v3 moms rules; v4/v6 year info; v5 org
+ description/shares + board members; v7 attachments append-only triggers);
+ forward migrations are in `db.c`.
## Makandra driftstatus (org 2)
diff --git a/src/db.c b/src/db.c
index 627efbc..c809bea 100644
--- a/src/db.c
+++ b/src/db.c
@@ -206,6 +206,12 @@ static const char SCHEMA_V1[] =
" UNIQUE (org_id, id),"
" UNIQUE (org_id, sha256, filename)"
") STRICT;\n"
+ "CREATE TRIGGER attachments_no_update BEFORE UPDATE ON attachments BEGIN"
+ " SELECT RAISE(ABORT, 'attachments are append-only');"
+ "END;\n"
+ "CREATE TRIGGER attachments_no_delete BEFORE DELETE ON attachments BEGIN"
+ " SELECT RAISE(ABORT, 'attachments are append-only');"
+ "END;\n"
"CREATE TABLE voucher_attachments ("
" org_id INTEGER NOT NULL,"
@@ -433,6 +439,25 @@ static int db_upgrade_v6(sqlite3 *db, char **err)
" DEFAULT ''", err);
}
+/* v7: attachments are immutable too — the content-addressed underlag may
+ never be updated or deleted, only linked/unlinked. */
+static int db_upgrade_v7(sqlite3 *db, char **err)
+{
+ if (db_exec(db,
+ "CREATE TRIGGER IF NOT EXISTS attachments_no_update"
+ " BEFORE UPDATE ON attachments BEGIN"
+ " SELECT RAISE(ABORT, 'attachments are append-only');"
+ "END",
+ err) != 0)
+ return -1;
+ return db_exec(db,
+ "CREATE TRIGGER IF NOT EXISTS attachments_no_delete"
+ " BEFORE DELETE ON attachments BEGIN"
+ " SELECT RAISE(ABORT, 'attachments are append-only');"
+ "END",
+ err);
+}
+
static int db_upgrade(sqlite3 *db, int from, char **err)
{
if (db_exec(db, "BEGIN IMMEDIATE", err) != 0)
@@ -457,6 +482,10 @@ static int db_upgrade(sqlite3 *db, int from, char **err)
db_exec(db, "ROLLBACK", NULL);
return -1;
}
+ if (from < 7 && db_upgrade_v7(db, err) != 0) {
+ db_exec(db, "ROLLBACK", NULL);
+ return -1;
+ }
char *sql = sqlite3_mprintf(
"UPDATE meta SET value='%d' WHERE key='schema_version'",
BOKF_SCHEMA_VERSION);
diff --git a/src/db.h b/src/db.h
index fe3bfbc..03ee5e5 100644
--- a/src/db.h
+++ b/src/db.h
@@ -4,7 +4,7 @@
#include <sqlite3.h>
#include <stdint.h>
-#define BOKF_SCHEMA_VERSION 6
+#define BOKF_SCHEMA_VERSION 7
int db_open(const char *path, sqlite3 **out, char **err);
int db_migrate(sqlite3 *db, char **err);
diff --git a/tests/test_core.c b/tests/test_core.c
index 56c70dd..70a1ab7 100644
--- a/tests/test_core.c
+++ b/tests/test_core.c
@@ -508,8 +508,18 @@ static void test_pre_migration_snapshot(const char *tmpdir,
sqlite3_close(sdb);
/* the reopened database is at the current version and usable */
+ char ver[16];
+ snprintf(ver, sizeof ver, "%d", BOKF_SCHEMA_VERSION);
v = db_text(db, "SELECT value FROM meta WHERE key='schema_version'");
- CHECK(v && strcmp(v, "6") == 0);
+ CHECK(v && strcmp(v, ver) == 0);
+ free(v);
+ v = db_text(db, "SELECT name FROM sqlite_master WHERE type='trigger'"
+ " AND name='attachments_no_update'");
+ CHECK(v && strcmp(v, "attachments_no_update") == 0);
+ free(v);
+ v = db_text(db, "SELECT name FROM sqlite_master WHERE type='trigger'"
+ " AND name='attachments_no_delete'");
+ CHECK(v && strcmp(v, "attachments_no_delete") == 0);
free(v);
int64_t user_id = 0;
CHECK(db_create_user(db, "migrated", "Migrated", "secret123", 0, &user_id,
@@ -2414,7 +2424,21 @@ int main(void)
sqlite3_finalize(ast);
CHECK(attachment_id > 0);
- /* attachments are re-hashed only with full:true */
+ /* the append-only triggers block updates and deletes; dropping one
+ simulates a modified database file for the re-hash check */
+ err = NULL;
+ CHECK(db_exec(g_db, reqf("UPDATE attachments SET content=x'00'"
+ " WHERE id=%lld", (long long)attachment_id),
+ &err) != 0);
+ free(err);
+ err = NULL;
+ CHECK(db_exec(g_db, reqf("DELETE FROM attachments WHERE id=%lld",
+ (long long)attachment_id),
+ &err) != 0);
+ free(err);
+ err = NULL;
+ CHECK(db_exec(g_db, "DROP TRIGGER attachments_no_update", &err) == 0);
+ free(err);
err = NULL;
CHECK(db_exec(g_db, reqf("UPDATE attachments SET content=x'00'"
" WHERE id=%lld", (long long)attachment_id),
generated by cgit v1.3 (git 2.53.0) at 2026-09-21 21:36:30 +0000