bank: import SEB CSV and match against vouchers (schema v8)

1 files changed, 65 insertions, 3 deletions

diff --git a/docs/SCHEMA.md b/docs/SCHEMA.md
index 2a85521..0f360d5 100644
--- a/docs/SCHEMA.md
+++ b/docs/SCHEMA.md
@@ -47,6 +47,7 @@ orgs ─┬─ memberships ── users ── api_tokens
       ├─ audit_log            (global chain, org_id nullable)
       ├─ idempotency
       ├─ report_rules
+      ├─ bank_transactions ── bank_matches
       └─ settings
 ```
 
@@ -330,6 +331,64 @@ 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.
 
+### 8.1 Bank transactions and matches (schema v8)
+
+Phase 1 of bank reconciliation: imported statement rows are evidence, never
+ledger data, and matching only links them to already-booked vouchers. No path
+in the server books, edits or deletes a voucher from here.
+
+```sql
+CREATE TABLE bank_transactions (
+  org_id      INTEGER NOT NULL REFERENCES orgs(id),
+  id          INTEGER PRIMARY KEY,
+  account     TEXT NOT NULL,              -- bank account number, e.g. "1930"
+  booked_at   TEXT NOT NULL,              -- YYYY-MM-DD
+  value_date  TEXT NOT NULL,              -- YYYY-MM-DD
+  text        TEXT NOT NULL,
+  type        TEXT NOT NULL,
+  amount_ore  INTEGER NOT NULL,           -- signed: deposit positive, withdrawal negative
+  balance_ore INTEGER,                    -- nullable
+  source      TEXT NOT NULL,              -- "seb-csv"
+  source_hash BLOB NOT NULL CHECK (length(source_hash) = 32),
+  imported_at TEXT NOT NULL,
+  imported_by INTEGER NOT NULL REFERENCES users(id),
+  UNIQUE (org_id, id),
+  UNIQUE (org_id, source_hash)
+) STRICT;
+CREATE INDEX idx_bank_tx_date ON bank_transactions(org_id, booked_at);
+
+CREATE TABLE bank_matches (
+  org_id         INTEGER NOT NULL,
+  transaction_id INTEGER NOT NULL,
+  voucher_id     INTEGER NOT NULL,
+  matched_at     TEXT NOT NULL,
+  matched_by     INTEGER NOT NULL REFERENCES users(id),
+  kind           TEXT NOT NULL DEFAULT 'manual' CHECK (kind IN ('manual','auto')),
+  PRIMARY KEY (org_id, transaction_id, voucher_id),
+  FOREIGN KEY (org_id, transaction_id) REFERENCES bank_transactions(org_id, id),
+  FOREIGN KEY (org_id, voucher_id)     REFERENCES vouchers(org_id, id)
+) STRICT;
+```
+
+Imported rows are immutable in practice: the daemon exposes no update or
+delete handler for `bank_transactions`, and re-importing the same export is a
+no-op thanks to `UNIQUE (org_id, source_hash)`. `kind:'auto'` is reserved for
+a later matching phase; phase 1 writes `'manual'` only. `bank_matches` stays
+mutable so a wrong link can be removed (`bank.unmatch`); every link and unlink
+is audited. A transaction may have several matches (partial payments), and one
+voucher may reconcile several transactions.
+
+The `source_hash` is SHA-256 over a canonical encoding of the row including
+the account: `"bokf-v1-bank-tx\0"`, then each of `account`, `booked_at`,
+`value_date` as `u16len + UTF-8`, `text` and `type` as `u32len + UTF-8`,
+`amount_ore` as `i64be`, and a `u8` flag followed by `i64be balance_ore` when
+the balance is present. The SEB parser and this encoding live in
+`src/commands.c`.
+
+The `settings` key `bank_account` (digits only, up to 10 characters, default
+`1930`) selects the account `bank.import` uses when the request omits
+`account`. See `PROTOCOL.md` §7.9.
+
 ## 9. Audit log and idempotency
 
 ```sql
@@ -388,7 +447,8 @@ Actions written to the log include: `auth.open`, `auth.fail`, `session.close`,
 `account.update`, `fiscal_year.open`, `fiscal_year.close`, `period.lock`,
 `period.unlock`, `voucher.post`, `voucher.correct`, `attachment.put`,
 `attachment.link`, `sie.import`, `sie.export`, `backup.snapshot`,
-`settings.update`. Reads are logged only when `audit_reads = true`.
+`bank.import`, `bank.match`, `bank.unmatch`, `settings.update`. Reads are
+logged only when `audit_reads = true`.
 
 ## 10. Reporting rules and settings
 
@@ -489,8 +549,10 @@ another voucher is posted in between) — clients must not persist it.
 ## 12. Migrations and versioning
 
 - `meta(key TEXT PRIMARY KEY, value TEXT)` holds `schema_version` (integer)
-  and `created_at`. Current version: **3** (v3 replaces the seeded moms rules
-  with the corrected mapping; v2 adds the two template tables).
+  and `created_at`. Current version: **8** (v8 adds the two bank
+  reconciliation tables, v7 makes attachments append-only, v3 replaces the
+  seeded moms rules with the corrected mapping; v2 adds the two template
+  tables).
 - Migrations are forward-only, applied automatically at daemon start, each in
   one transaction. Before the first migration statement a consistent
   `VACUUM INTO` snapshot is written to