diff options
Diffstat (limited to 'docs/SCHEMA.md')
| -rw-r--r-- | docs/SCHEMA.md | 490 |
1 files changed, 490 insertions, 0 deletions
diff --git a/docs/SCHEMA.md b/docs/SCHEMA.md new file mode 100644 index 0000000..4b22271 --- /dev/null +++ b/docs/SCHEMA.md @@ -0,0 +1,490 @@ +# bokf database schema + +Status: Draft 0.1 · 2026-09-17 · License: GPL-3.0-or-later + +Storage is a single SQLite database (WAL mode) owned exclusively by `bokfd`. +Clients never see it. This document defines the schema, the invariants it +enforces, and the canonical hash encodings that make the ledger tamper-evident. + +## 1. Principles + +1. **STRICT tables.** Type errors are bugs, not data. +2. **Integer öre.** All amounts are `INTEGER` öre. No floats anywhere. +3. **Append-only ledger.** Vouchers, rows, attachments and audit entries can be + inserted but never updated or deleted. Corrections are new vouchers. +4. **Tenant isolation in the engine.** Every tenant table has + `UNIQUE(org_id, id)` and every reference is a composite foreign key + `(org_id, target_id)`. A row cannot physically reference another org's data, + even if application code is wrong. +5. **One writer.** `bokfd` serializes all writes; SQLite's single-writer model + is therefore never contended between threads. +6. **Hash chains.** Voucher history and audit history are independently + SHA-256 chained and verifiable (`audit.verify`). + +## 2. Connection pragmas + +Every connection: + +```sql +PRAGMA journal_mode = WAL; +PRAGMA foreign_keys = ON; +PRAGMA busy_timeout = 5000; +PRAGMA synchronous = FULL; -- configurable: NORMAL for speed, FULL default +PRAGMA wal_autocheckpoint = 1000; +``` + +Writes use `BEGIN IMMEDIATE`. This is what makes `DB_BUSY` a transient error +instead of silent corruption. + +## 3. Overview + +``` +orgs ─┬─ memberships ── users ── api_tokens + ├─ accounts + ├─ fiscal_years ── sequences + ├─ vouchers ─┬─ voucher_rows + │ └─ voucher_attachments ── attachments + ├─ audit_log (global chain, org_id nullable) + ├─ idempotency + ├─ report_rules + └─ settings +``` + +`org_id` is present on every tenant row. `audit_log` is a global chain with a +nullable `org_id`, because user and system events (logins, org creation) are +not org-scoped. + +## 4. Identity and tenancy + +```sql +CREATE TABLE orgs ( + id INTEGER PRIMARY KEY, + name TEXT NOT NULL, + org_nr TEXT, -- "5560123456", leading zeros preserved + vat_nr TEXT, -- "SE556012345601" + address TEXT, + postal_code TEXT, + city TEXT, + country TEXT NOT NULL DEFAULT 'SE', + email TEXT, + phone TEXT, + fiscal_year_start_month INTEGER NOT NULL DEFAULT 1 CHECK (fiscal_year_start_month BETWEEN 1 AND 12), + moms_period TEXT NOT NULL DEFAULT 'month' CHECK (moms_period IN ('month','quarter','year')), + framework TEXT NOT NULL DEFAULT 'K2' CHECK (framework IN ('K2','K3')), + created_at TEXT NOT NULL, + created_by INTEGER NOT NULL REFERENCES users(id), + archived_at TEXT +) STRICT; + +CREATE TABLE users ( + id INTEGER PRIMARY KEY, + username TEXT NOT NULL UNIQUE COLLATE NOCASE, + display_name TEXT NOT NULL, + pw_hash TEXT NOT NULL, -- Argon2id PHC string + is_admin INTEGER NOT NULL DEFAULT 0 CHECK (is_admin IN (0,1)), + created_at TEXT NOT NULL, + disabled_at TEXT +) STRICT; + +CREATE TABLE memberships ( + org_id INTEGER NOT NULL REFERENCES orgs(id), + user_id INTEGER NOT NULL REFERENCES users(id), + role TEXT NOT NULL CHECK (role IN ('owner','bookkeeper','viewer')), + created_at TEXT NOT NULL, + PRIMARY KEY (org_id, user_id) +) STRICT; + +CREATE TABLE api_tokens ( + id INTEGER PRIMARY KEY, + org_id INTEGER NOT NULL REFERENCES orgs(id), + user_id INTEGER NOT NULL REFERENCES users(id), + label TEXT NOT NULL, + token_hash BLOB NOT NULL UNIQUE CHECK (length(token_hash) = 32), + scopes TEXT NOT NULL, -- JSON array of 'read','write','admin' + created_at TEXT NOT NULL, + expires_at TEXT, + last_used_at TEXT, + revoked_at TEXT +) STRICT; +``` + +Sessions are deliberately absent from the schema: they live in `bokfd` memory +and die with the process. + +## 5. Kontoplan + +```sql +CREATE TABLE accounts ( + org_id INTEGER NOT NULL REFERENCES orgs(id), + id INTEGER PRIMARY KEY, + number TEXT NOT NULL CHECK (number GLOB '[0-9]*' AND length(number) BETWEEN 1 AND 10), + name TEXT NOT NULL, -- kontobeteckning (BFL 3 kap) + type TEXT NOT NULL CHECK (type IN ('asset','liability','equity','revenue','expense')), + sru_code TEXT, -- for SRU/INK2 mapping later + vat_code TEXT, -- default momskod for reporting + active INTEGER NOT NULL DEFAULT 1 CHECK (active IN (0,1)), + created_at TEXT NOT NULL, + updated_at TEXT, + UNIQUE (org_id, id), + UNIQUE (org_id, number) +) STRICT; + +CREATE INDEX idx_accounts_type ON accounts(org_id, type); +``` + +Accounts are seeded from BAS 2026 at org creation (separately attributed seed +data, see `THIRD_PARTY_NOTICES`). They are mutable configuration, not ledger +data: renaming or deactivating an account is allowed and audited; posting to an +inactive account is rejected unless the fiscal year is closed. + +## 6. Fiscal years, sequences and locks + +```sql +CREATE TABLE fiscal_years ( + org_id INTEGER NOT NULL REFERENCES orgs(id), + id INTEGER PRIMARY KEY, + label TEXT NOT NULL, -- "2026", "2025/2026" + start_date TEXT NOT NULL, + end_date TEXT NOT NULL, + status TEXT NOT NULL DEFAULT 'open' CHECK (status IN ('open','closed')), + locked_until TEXT, -- inclusive last locked date + created_at TEXT NOT NULL, + closed_at TEXT, + closed_by INTEGER REFERENCES users(id), + UNIQUE (org_id, id), + UNIQUE (org_id, label), + CHECK (start_date < end_date), + CHECK (locked_until IS NULL OR locked_until <= end_date) +) STRICT; + +CREATE TABLE sequences ( + org_id INTEGER NOT NULL, + fiscal_year_id INTEGER NOT NULL, + series TEXT NOT NULL, -- "A", "B", "IB", ... + next_number INTEGER NOT NULL DEFAULT 1, + PRIMARY KEY (org_id, fiscal_year_id, series), + FOREIGN KEY (org_id, fiscal_year_id) REFERENCES fiscal_years(org_id, id) +) STRICT; +``` + +`next_number` is incremented in the same transaction as the voucher insert, so +a failed post never consumes a number and a committed post never skips one. +Numbering is per fiscal year and series, matching BFL's requirement of an +unbroken series (obruten nummerserie). + +## 7. Vouchers (append-only) + +```sql +CREATE TABLE vouchers ( + org_id INTEGER NOT NULL REFERENCES orgs(id), + id INTEGER PRIMARY KEY, + fiscal_year_id INTEGER NOT NULL, + series TEXT NOT NULL, + number INTEGER NOT NULL CHECK (number > 0), + date TEXT NOT NULL CHECK (date LIKE '____-__-__'), + description TEXT NOT NULL CHECK (length(description) > 0), + source TEXT NOT NULL DEFAULT 'manual' + CHECK (source IN ('manual','agent','sie_import','system','ib')), + client_ref TEXT, + corrects_voucher_id INTEGER, + created_at TEXT NOT NULL, + created_by_user INTEGER NOT NULL REFERENCES users(id), + created_by_token INTEGER REFERENCES api_tokens(id), + hash_prev BLOB NOT NULL CHECK (length(hash_prev) = 32), + hash BLOB NOT NULL CHECK (length(hash) = 32), + UNIQUE (org_id, id), + UNIQUE (org_id, fiscal_year_id, series, number), + UNIQUE (org_id, client_ref), + FOREIGN KEY (org_id, fiscal_year_id) REFERENCES fiscal_years(org_id, id), + FOREIGN KEY (org_id, corrects_voucher_id) REFERENCES vouchers(org_id, id) +) STRICT; + +CREATE TABLE voucher_rows ( + org_id INTEGER NOT NULL, + id INTEGER PRIMARY KEY, + voucher_id INTEGER NOT NULL, + line_no INTEGER NOT NULL, + account_id INTEGER NOT NULL, + debit_ore INTEGER NOT NULL DEFAULT 0 CHECK (debit_ore >= 0), + credit_ore INTEGER NOT NULL DEFAULT 0 CHECK (credit_ore >= 0), + description TEXT, + CHECK ((debit_ore = 0) <> (credit_ore = 0)), -- exactly one side non-zero + CHECK (debit_ore > 0 OR credit_ore > 0), + UNIQUE (org_id, id), + UNIQUE (org_id, voucher_id, line_no), + FOREIGN KEY (org_id, voucher_id) REFERENCES vouchers(org_id, id), + FOREIGN KEY (org_id, account_id) REFERENCES accounts(org_id, id) +) STRICT; + +CREATE INDEX idx_vouchers_date ON vouchers(org_id, date); +CREATE INDEX idx_vouchers_fy ON vouchers(org_id, fiscal_year_id, series, number); +CREATE INDEX idx_rows_voucher ON voucher_rows(org_id, voucher_id, line_no); +CREATE INDEX idx_rows_account ON voucher_rows(org_id, account_id); +``` + +Immutability is enforced by triggers, not convention: + +```sql +CREATE TRIGGER vouchers_no_update BEFORE UPDATE ON vouchers +BEGIN SELECT RAISE(ABORT, 'vouchers are append-only'); END; + +CREATE TRIGGER vouchers_no_delete BEFORE DELETE ON vouchers +BEGIN SELECT RAISE(ABORT, 'vouchers are append-only'); END; + +CREATE TRIGGER voucher_rows_no_update BEFORE UPDATE ON voucher_rows +BEGIN SELECT RAISE(ABORT, 'voucher rows are append-only'); END; + +CREATE TRIGGER voucher_rows_no_delete BEFORE DELETE ON voucher_rows +BEGIN SELECT RAISE(ABORT, 'voucher rows are append-only'); END; +``` + +Consequences worth stating explicitly: + +- There is no `updated_at` on a voucher. There is no way to change one, + including through `sqlite3` as root, without dropping the trigger first. +- `corrects_voucher_id` points old → new is the reverse of the wording: the + **new** voucher carries `corrects_voucher_id = <original>`. The original stays + untouched, and reports show both. +- Balance is enforced by the posting algorithm inside a transaction; `audit.verify` + additionally scans for any unbalanced voucher as a integrity backstop. + +### 7.1 Voucher hash (canonical bytes) + +``` +SHA256( "bokf-v1-voucher\0" + prev_hash[32] + org_id u64be + fiscal_year label u16len + utf8 bytes + series u16len + utf8 bytes + number u64be + date "YYYY-MM-DD" (10 bytes) + description u32len + utf8 bytes + row_count u32be + for each row, line_no order: + account number u16len + utf8 bytes + debit_ore u64be + credit_ore u64be + row description u32len + utf8 bytes ) +``` + +`prev_hash` is the `hash` of the previous voucher **by `id`** (insertion +order = posting order) in the same org, or 32 zero bytes for the first voucher +of the org. The chain therefore spans fiscal years, and any retroactive edit, +insertion or deletion breaks it at a detectable point. + +## 8. Attachments (underlag) + +```sql +CREATE TABLE attachments ( + org_id INTEGER NOT NULL REFERENCES orgs(id), + id INTEGER PRIMARY KEY, + sha256 BLOB NOT NULL CHECK (length(sha256) = 32), + filename TEXT NOT NULL, + mime TEXT NOT NULL, + size_bytes INTEGER NOT NULL CHECK (size_bytes >= 0), + content BLOB NOT NULL, -- stored in-DB; backups stay trivial + created_at TEXT NOT NULL, + created_by INTEGER NOT NULL REFERENCES users(id), + UNIQUE (org_id, id), + UNIQUE (org_id, sha256, filename) +) STRICT; + +CREATE TABLE voucher_attachments ( + org_id INTEGER NOT NULL, + voucher_id INTEGER NOT NULL, + attachment_id INTEGER NOT NULL, + created_at TEXT NOT NULL, + PRIMARY KEY (org_id, voucher_id, attachment_id), + FOREIGN KEY (org_id, voucher_id) REFERENCES vouchers(org_id, id), + FOREIGN KEY (org_id, attachment_id) REFERENCES attachments(org_id, id) +) STRICT; +``` + +Attachments are content-addressed and immutable; linking is an insert into +`voucher_attachments` and is itself audited. 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. + +## 9. Audit log and idempotency + +```sql +CREATE TABLE audit_log ( + seq INTEGER PRIMARY KEY, + org_id INTEGER REFERENCES orgs(id), + at TEXT NOT NULL, -- RFC 3339 UTC + actor_user_id INTEGER REFERENCES users(id), + actor_token_id INTEGER REFERENCES api_tokens(id), + action TEXT NOT NULL, -- "voucher.post", "auth.fail", ... + request_json TEXT NOT NULL DEFAULT '{}', -- redacted, exact bytes are hashed + result_code TEXT NOT NULL, -- "OK", "UNBALANCED", ... + hash_prev BLOB NOT NULL CHECK (length(hash_prev) = 32), + hash BLOB NOT NULL CHECK (length(hash) = 32) +) STRICT; + +CREATE TRIGGER audit_no_update BEFORE UPDATE ON audit_log +BEGIN SELECT RAISE(ABORT, 'audit log is append-only'); END; +CREATE TRIGGER audit_no_delete BEFORE DELETE ON audit_log +BEGIN SELECT RAISE(ABORT, 'audit log is append-only'); END; + +CREATE TABLE idempotency ( + org_id INTEGER NOT NULL, + client_ref TEXT NOT NULL, + cmd TEXT NOT NULL, + response_json TEXT NOT NULL, + created_at TEXT NOT NULL, + PRIMARY KEY (org_id, client_ref), + FOREIGN KEY (org_id) REFERENCES orgs(id) +) STRICT; +``` + +### 9.1 Audit hash (canonical bytes) + +``` +SHA256( "bokf-v1-audit\0" + prev_hash[32] + seq u64be + at u16len + utf8 + actor_user_id u64be -- 0 = none + actor_token_id u64be -- 0 = none + org_id u64be -- 0 = none + action u16len + utf8 + request_json u32len + exact stored bytes + result_code u16len + utf8 ) +``` + +Hashing the stored bytes verbatim avoids canonicalization disputes. Secrets are +redacted **before** the bytes are stored and hashed: `session.open` records +`{"method":"password","username":"anders"}` and the outcome, never the +password or token. + +Actions written to the log include: `auth.open`, `auth.fail`, `session.close`, +`org.create`, `org.update`, `member.add`, `member.set_role`, `member.remove`, +`user.create`, `token.create`, `token.revoke`, `account.create`, +`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`. + +## 10. Reporting rules and settings + +```sql +CREATE TABLE report_rules ( + org_id INTEGER NOT NULL REFERENCES orgs(id), + id INTEGER PRIMARY KEY, + report TEXT NOT NULL, -- "vat", "income_statement", ... + box TEXT NOT NULL, -- "05", "10", ... + match_type TEXT NOT NULL CHECK (match_type IN ('account','range','type')), + pattern TEXT NOT NULL, -- "2610" | "2610-2619" | "revenue" + sign INTEGER NOT NULL DEFAULT 1 CHECK (sign IN (1,-1)), + sort_order INTEGER NOT NULL DEFAULT 0, + UNIQUE (org_id, id) +) STRICT; + +CREATE TABLE settings ( + org_id INTEGER NOT NULL REFERENCES orgs(id), + key TEXT NOT NULL, + value TEXT NOT NULL, + PRIMARY KEY (org_id, key) +) STRICT; +``` + +`report_rules` is seeded per org with a moms mapping over BAS account ranges and +is editable by owners when Skatteverket changes the blankett. The seed rules +are data, not code: the system ships a reviewed default set per fiscal year and +keeps older sets for older years. + +### 10.1 Voucher templates (schema v2) + +Konteringsmallar: named sets of rows with a formula over the variable `x`. +Configuration data, mutable and audited, never part of the ledger. + +```sql +CREATE TABLE voucher_templates ( + org_id INTEGER NOT NULL REFERENCES orgs(id), + id INTEGER PRIMARY KEY, + name TEXT NOT NULL, + series TEXT NOT NULL DEFAULT 'A', + description TEXT NOT NULL DEFAULT '', + active INTEGER NOT NULL DEFAULT 1 CHECK (active IN (0,1)), + created_at TEXT NOT NULL, + updated_at TEXT, + UNIQUE (org_id, id), + UNIQUE (org_id, name) +) STRICT; + +CREATE TABLE voucher_template_rows ( + org_id INTEGER NOT NULL, + id INTEGER PRIMARY KEY, + template_id INTEGER NOT NULL, + line_no INTEGER NOT NULL, + account_id INTEGER NOT NULL, + formula TEXT NOT NULL CHECK (length(formula) > 0), + description TEXT, + UNIQUE (org_id, id), + UNIQUE (org_id, template_id, line_no), + FOREIGN KEY (org_id, template_id) + REFERENCES voucher_templates(org_id, id), + FOREIGN KEY (org_id, account_id) REFERENCES accounts(org_id, id) +) STRICT; +``` + +Formulas are evaluated in kronor, rounded per row to whole öre, and the +rounding remainder is assigned to the largest row before posting, so the +resulting voucher always balances. See `PROTOCOL.md` §7.4.1. + +## 11. The posting algorithm + +Every write path (`voucher.post`, `voucher.correct`, `sie.import`) runs the same +steps inside one `BEGIN IMMEDIATE` transaction: + +1. Authenticate, resolve org, check role/scope. +2. Validate arguments; resolve accounts by number; reject inactive. +3. Resolve fiscal year by date; reject `FISCAL_YEAR_CLOSED`, + `DATE_OUT_OF_RANGE`, `PERIOD_LOCKED`. +4. Rows: ≥ 2, exactly one non-zero side per row, `sum(debit) == sum(credit)`, + else `UNBALANCED` with `difference_ore`. +5. Reserve and increment `sequences.next_number`; the assigned number is the + only number the voucher can have (`SEQUENCE_GAP` on mismatch). +6. Read `hash_prev` = last voucher hash by id for the org; compute `hash`. +7. Insert voucher, rows, and `voucher_attachments` links. +8. Insert `audit_log` entry with its own chain hash. +9. Insert `idempotency` row if `client_ref` was given. +10. Commit. On any failure, roll back everything — no number is consumed. + +`dry_run` runs steps 1–5 and 7's validation, computes the hash with the +*candidate* number and a temporary `id`, and returns the preview without +writing. The preview hash is not the final hash (the number could change if +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: **2** (v2 adds the two template tables). +- Migrations are forward-only, applied automatically at daemon start, each in + one transaction, and require an automatic `VACUUM INTO` snapshot next to the + database before starting (`bokfd.db.pre-migration-<version>`). +- `audit.verify` must pass before and after any migration; migrations never + rewrite ledger rows. + +## 13. Integrity and operations + +- Daily sanity checks by the daemon: `PRAGMA quick_check`, `audit.verify`, + unbalanced-voucher scan. Failures are logged loudly and surfaced in `meta`. +- `backup.snapshot` uses `VACUUM INTO` to a timestamped file: consistent, no + downtime, and safe for restic to pick up. The live `*.db`/`-wal`/`-shm` files + must never be handed to a file-copy backup tool. +- Restore procedure: stop `bokfd`, replace the database with a snapshot, start, + run `audit.verify` and a report smoke test. Restores are tested on a + schedule; the same procedure is the 7-year archive retrieval path. +- Size: SQLite with `page_size` default and WAL suits the load easily + (thousands of vouchers per year are trivial). Attachments dominate growth; + they live in the same file so a snapshot remains a single artifact. + +## 14. Seeds + +| Data | Source | Notes | +|---|---|---| +| BAS 2026 kontoplan | `data/bas2026.csv` | separately attributed (FAR); loaded at org creation | +| Moms report rules | `data/vat_rules_<year>.json` | account → ruta mapping, reviewed per year | +| Standard series | code | `A` (normal), `IB` (opening balances), `SIE` (import) | +| SRU codes | `data/sru_map.csv` | for later INK2/SRU export | |