path: root/docs/SCHEMA.md

# 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.

Several rules may target the same `box`; `report.vat` sums them into one entry
per box. Ruta 49 is the sum of the payable boxes only (`10`,`11`,`12`,`30`,
`31`,`32`,`48`,`60`,`61`,`62`); the other boxes are underlag and never change
what is payable.

### 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: **3** (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, 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 |