diff options
Diffstat (limited to 'docs')
| -rw-r--r-- | docs/PROTOCOL.md | 54 | ||||
| -rw-r--r-- | docs/SCHEMA.md | 68 |
2 files changed, 117 insertions, 5 deletions
diff --git a/docs/PROTOCOL.md b/docs/PROTOCOL.md index 6f26463..d7e70be 100644 --- a/docs/PROTOCOL.md +++ b/docs/PROTOCOL.md @@ -108,6 +108,7 @@ Scopes on a token can narrow but never widen the user's role. |---|---|---|---|---| | Read: vouchers, reports, audit, accounts | ● | ● | ● | ● | | `voucher.post`, `voucher.correct`, `attachment.put` | | ● | ● | | +| `bank.import`, `bank.match`, `bank.unmatch` | | ● | ● | | | `sie.import`, `account.create`, `account.update` | | ● | ● | | | `period.lock`, `fiscal_year.open/close/reopen`, `org.update` | | | ● | | | `org.member_*`, `token.create` for others | | | ● | | @@ -370,12 +371,14 @@ resolved rows in a dry run. | Command | Args | Notes | |---|---|---| | `settings.get` | — | effective org settings (defaults included) | -| `settings.set` | `key`, `value` | known keys: `default_series`, `attachment_dir` | +| `settings.set` | `key`, `value` | known keys: `default_series`, `attachment_dir`, `bank_account` | `default_series` (1–8 characters, e.g. `A`, `V-`, `A `) is used when `voucher.post` carries no `series` and as the default series for new templates. `attachment_dir` (a path, up to 255 characters) is the folder the -TUI file browser opens in when attaching underlag. Verification ids are the concatenation of series and number +TUI file browser opens in when attaching underlag. `bank_account` (digits +only, up to 10 characters, default `1930`) is the account `bank.import` uses +when the request carries no `account`. Verification ids are the concatenation of series and number (`V-8`), and series are free-form: only an unbroken numbering per series is required. @@ -496,6 +499,53 @@ be created. backup; `full:true` includes attachment hashes. The voucher chain is verified per org in posting order (SCHEMA.md §7.1); the audit chain globally. +### 7.9 Bank reconciliation (statement import) + +Phase 1 is mechanical reconciliation only: statements are imported as +read-only evidence and already-booked vouchers are matched against them. +**It never books anything** — no voucher is created or changed by these +commands. Suggestions are advisory; a human (or an agent) must post any +missing voucher with `voucher.post` and then match it. + +| Command | Args | Result | +|---|---|---| +| `bank.import` | `format`, `content_base64` or `path`, `account?` | `format`, `account`, `total`, `imported`, `duplicates`, `first_date`, `last_date`; `dry_run?` | +| `bank.list` | `status?`, `from?`, `to?`, `account?`, `limit` | `items[{id,account,booked_at,value_date,text,type,amount_ore,balance_ore,matches[],suggestions[]}]`, `summary{unmatched,matched,unmatched_amount_ore}` | +| `bank.match` | `transaction_id`, `voucher_id` | `transaction_id`, `voucher_id`, `difference_ore` | +| `bank.unmatch` | `transaction_id`, `voucher_id` | `transaction_id`, `voucher_id`, `unmatched:true` | + +`bank.import` takes one SEB CSV export (`format:"seb"`): UTF-8 with an +optional BOM, `;`-separated, the header exactly +`Bokförd;Valutadatum;Text;Typ;Insättningar;Uttag;Bokfört saldo`. Quoted fields +use `""` for an embedded quote; amounts use decimal comma and may group +thousands with spaces or `.`; the `Insättningar` and `Uttag` columns are +mutually exclusive and exactly one must be non-empty (a withdrawal becomes a +negative amount); the balance may be empty. Files larger than 64 MiB are +rejected with `TOO_LARGE`, an unknown account with `ACCOUNT_NOT_FOUND`, a +header mismatch or malformed row (reported with its line number) with +`INVALID_ARGS`. Each row is hashed over its canonical field encoding incl. the +account, so re-importing the same export only reports duplicates +(`imported:0`). `account` defaults to the `bank_account` setting, else `1930`. +The result's `first_date`/`last_date` span every row in the file. + +`bank.list` items carry their `matches` (`voucher_id`, `series`, `number`, +`date`, `bank_amount_ore` = the voucher's signed movement on the transaction's +account) and, for unmatched transactions, up to three advisory `suggestions` +(`voucher_id`, `series`, `number`, `date`, `amount_ore`): posted vouchers that +touch the account, whose movement on it equals the transaction amount exactly, +are dated within ±5 days and are not yet matched to any transaction. +`summary` always counts all transactions for the org (optionally narrowed by +`account`), ignoring `status`, `from` and `to`. + +`bank.match` links one transaction to one voucher. The voucher must post to +the transaction's account; matching the same pair twice is a `CONFLICT`. +Several vouchers may match one transaction (partial matching) and +`difference_ore` is the transaction amount minus the summed bank legs after +the insert, so `0` means the transaction is fully reconciled. `bank.unmatch` +removes one link and is a `NOT_FOUND` when it does not exist. Both mutate +`bank_matches` only and are audited (`bank.match`, `bank.unmatch`); +`bank.import` is audited as `bank.import`. + ## 8. The TUI is just a client `bokftui` logs in over the same socket, picks an org and issues the same 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 |