payroll: design chapter

1 files changed, 225 insertions, 0 deletions

diff --git a/docs/PAYROLL.md b/docs/PAYROLL.md
new file mode 100644
index 0000000..ffb0eee
--- /dev/null
+++ b/docs/PAYROLL.md
@@ -0,0 +1,225 @@
+# bokf — payroll (lön)
+
+Status: Design draft · 2026-09-21 · License: GPL-3.0-or-later
+
+Payroll for a small Swedish AB that runs monthly salaries from the books.
+Wave 1 is the engine and the mandatory steps; the employee is the owner but
+the schema and commands are multi-employee from the start.
+
+## 1. Scope
+
+**In (wave 1)**
+
+- Employee register: name, personnummer (encrypted at rest), address,
+  bank account, employment (monthly salary), salary account, tax table and
+  column.
+- Tax tables from Skatteverket: fetch, parse and store the official monthly
+  tables, with a staleness check and a one-button refresh.
+- One monthly payroll run per period: gross, tax, employer contributions,
+  net, posting as a voucher.
+- AGI underlag: the field values per employee and period for the manual
+  declaration on skatteverket.se.
+- Payment steps as buttons: pay salaries (the run voucher) and pay tax +
+  contributions to the tax account (its own voucher), matched by the bank
+  reconciliation.
+
+**Out (later)**
+
+- Semester/vacation accrual and vacation pay (skipped for now).
+- AGI XML/filing, pension, benefits, foreign employees, växa-stöd,
+  studiesocialt, löneväxling.
+
+## 2. Accounts
+
+Taken from the org's own imported history (2022–2026): 7210 for the owner's
+salary, 7510 för arbetsgivaravgifter, 2710 personalskatt and 2731 avräkning.
+
+| Item | Debit | Credit |
+|---|---|---|
+| Lönekörning | 7210 löner (per employee, default) | 2710 personalskatt, 1930 nettolön |
+| Arbetsgivaravgifter | 7510 (31.42 %) | 2731 avräkning sociala avgifter |
+| Betalning till skattekontot | 2710 + 2731 | 1630 skattekontot |
+
+7010 is for kollektivanställda, 7210 för tjänstemän (what this org has used);
+the account is a per-employee setting. Settings: `payroll_salary_account`
+(default `7210`), `payroll_tax_account` (`2710`), `payroll_avgift_account`
+(`7510`), `payroll_avgift_liability` (`2731`), `payroll_tax_payment_account`
+(`1630`), `payroll_avgift_rate_bp` (default `3142`, basis points).
+
+## 3. Schema v10
+
+```sql
+CREATE TABLE employees (
+  org_id           INTEGER NOT NULL REFERENCES orgs(id),
+  id               INTEGER PRIMARY KEY,
+  name             TEXT NOT NULL,
+  personal_no_enc  TEXT NOT NULL,            -- enc:v1:... (AES-256-GCM)
+  address          TEXT NOT NULL DEFAULT '',
+  postal_code      TEXT NOT NULL DEFAULT '',
+  city             TEXT NOT NULL DEFAULT '',
+  bank_account     TEXT NOT NULL DEFAULT '',
+  salary_account   TEXT NOT NULL DEFAULT '7210',
+  monthly_salary_ore INTEGER NOT NULL DEFAULT 0 CHECK (monthly_salary_ore >= 0),
+  tax_table        INTEGER NOT NULL DEFAULT 30 CHECK (tax_table BETWEEN 29 AND 42),
+  tax_column       INTEGER NOT NULL DEFAULT 1 CHECK (tax_column BETWEEN 1 AND 6),
+  active           INTEGER NOT NULL DEFAULT 1 CHECK (active IN (0,1)),
+  created_at       TEXT NOT NULL,
+  updated_at       TEXT,
+  UNIQUE (org_id, id)
+) STRICT;
+
+CREATE TABLE payroll_runs (
+  org_id          INTEGER NOT NULL REFERENCES orgs(id),
+  id              INTEGER PRIMARY KEY,
+  fiscal_year_id  INTEGER NOT NULL,
+  period          TEXT NOT NULL,             -- YYYY-MM
+  pay_date        TEXT NOT NULL,
+  status          TEXT NOT NULL DEFAULT 'posted'
+                    CHECK (status IN ('posted','paid')),
+  gross_ore       INTEGER NOT NULL,
+  tax_ore         INTEGER NOT NULL,
+  avgifter_ore    INTEGER NOT NULL,
+  net_ore         INTEGER NOT NULL,
+  voucher_id      INTEGER,
+  payment_voucher_id INTEGER,
+  created_at      TEXT NOT NULL,
+  created_by      INTEGER NOT NULL REFERENCES users(id),
+  UNIQUE (org_id, id),
+  UNIQUE (org_id, period, pay_date),
+  FOREIGN KEY (org_id, fiscal_year_id) REFERENCES fiscal_years(org_id, id),
+  FOREIGN KEY (org_id, voucher_id) REFERENCES vouchers(org_id, id),
+  FOREIGN KEY (org_id, payment_voucher_id) REFERENCES vouchers(org_id, id)
+) STRICT;
+
+CREATE TABLE payroll_run_lines (
+  org_id       INTEGER NOT NULL,
+  id           INTEGER PRIMARY KEY,
+  run_id       INTEGER NOT NULL,
+  employee_id  INTEGER NOT NULL,
+  gross_ore    INTEGER NOT NULL,
+  tax_ore      INTEGER NOT NULL,
+  avgifter_ore INTEGER NOT NULL,
+  net_ore      INTEGER NOT NULL,
+  tax_table    INTEGER NOT NULL,
+  tax_column   INTEGER NOT NULL,
+  UNIQUE (org_id, id),
+  UNIQUE (org_id, run_id, employee_id),
+  FOREIGN KEY (org_id, run_id) REFERENCES payroll_runs(org_id, id),
+  FOREIGN KEY (org_id, employee_id) REFERENCES employees(org_id, id)
+) STRICT;
+
+-- National reference data, not tenant data: no org_id.
+CREATE TABLE tax_tables (
+  in_year       INTEGER NOT NULL,
+  table_no      INTEGER NOT NULL,
+  column_no     INTEGER NOT NULL,
+  income_from_ore INTEGER NOT NULL,
+  income_to_ore   INTEGER,                  -- NULL = open-ended top range
+  tax_ore       INTEGER NOT NULL,           -- whole kronor for the range
+  pct           INTEGER,                    -- top range percentage (x100)
+  PRIMARY KEY (in_year, table_no, column_no, income_from_ore)
+) STRICT;
+
+CREATE TABLE tax_table_meta (
+  in_year    INTEGER PRIMARY KEY,
+  source_url TEXT NOT NULL,
+  sha256     BLOB NOT NULL CHECK (length(sha256) = 32),
+  fetched_at TEXT NOT NULL
+) STRICT;
+```
+
+`vouchers.source` allows `'payroll'` and `'payroll_tax'` (widened CHECK in
+v10; the same table rebuild as v9).
+
+## 4. Tax tables
+
+Skatteverket publishes the complete monthly table as a fixed-width UTF-8
+(BOM) TXT per income year, linked from "Teknisk beskrivning för
+skattetabeller". 2026: `allmanna-tabeller-manad.txt`, ~389 kB, 7,965 lines.
+
+Records (verified against the 2026 file):
+
+```
+30B29   2001   2100  150    0  150    0  150    2
+30%29  80001  82200   30   30   26   27   35   35
+```
+
+- `30B<nn>`: monthly table `nn` (29–42), income range in whole kronor, then
+  the tax for columns 1–6 in whole kronor.
+- `30%<nn>`: the same tables above 80,000 kr/month; the columns are
+  percentages (hundredths) that apply to the part of the income above the
+  table's base, per SKV 433 (the technical description, fetched as PDF).
+- The tax in the B records already includes grundavdrag, jobbskatteavdrag,
+  public service fee and burial fee; use it as the withholding.
+
+Commands:
+
+- `payroll.tax_tables_fetch {year?}` (owner, audited): HTTPS GETs the
+  technical page, finds that year's `allmanna-tabeller-manad.txt`, downloads
+  and parses it, replaces the year's rows in one transaction and stores the
+  source URL + SHA-256 in `tax_table_meta`. Missing year defaults to the
+  current calendar year.
+- `payroll.tax_tables_import {year, content_base64}` (owner, audited): the
+  same parse from a supplied file (offline/air-gapped fallback).
+- `payroll.tax_tables_status`: `{"stored_years":[...],"current_year":N,
+  "stale":bool,"fetched_at":...,"source_url":...}`. The TUI shows a warning
+  and the fetch button when `stale`.
+
+The HTTPS GET uses OpenSSL directly (same stack as the SMTP client) with
+system trust; the page URL is a constant, the file link is discovered by
+matching `allmanna-tabeller-manad.txt` for the requested year.
+
+## 5. Commands (wave 1)
+
+| Command | Args | Result |
+|---|---|---|
+| `employee.list` | `active_only?` | items (personnummer masked) |
+| `employee.get` | `id` | one employee (personnummer masked) |
+| `employee.create` | `name`, `personal_no`, salary/account/table fields | created employee |
+| `employee.update` | `id` + any field | effective employee |
+| `employee.archive` | `id`, `active` | archived/reactivated |
+| `payroll.tax_tables_fetch` | `year?` | `stored`, `rows`, `source_url`, `sha256` |
+| `payroll.tax_tables_import` | `year`, `content_base64` | `stored`, `rows` |
+| `payroll.tax_tables_status` | — | stored/current year, `stale` |
+| `payroll.run_preview` | `period` (YYYY-MM) | per-employee gross/tax/avgifter/net and totals; nothing written |
+| `payroll.run_post` | `period`, `pay_date`, `dry_run?` | run id, voucher id, totals |
+| `payroll.run_list` | `limit?` | runs |
+| `payroll.run_get` | `id` | run + lines |
+| `payroll.agi` | `period` | per-employee AGI field values for the manual declaration |
+| `payroll.pay_tax` | `run_id`, `date?`, `dry_run?` | payment voucher (D 2710 + D 2731, K 1630) |
+
+`run_post` takes each active employee's `monthly_salary_ore` as gross (hourly
+employees are out of scope in wave 1), withholds the table tax for their
+table/column and books the two legs above in one transaction with
+`source:"payroll"`. Taxation below 1,000 kr/year is not withheld (SKV rule)
+and is handled at the run level. `pay_tax` books the payment with
+`source:"payroll_tax"` and marks the run paid; the bank reconciliation
+matches the bank leg to 1630 as usual.
+
+The employer contribution rate is a setting (`payroll_avgift_rate_bp`,
+default 3142) so växa-stöd/regional reductions can be handled manually later;
+the base is the gross.
+
+## 6. Documents and TUI
+
+- **Lönebesked** (wave 2): one PDF per employee and run, stored as an
+  attachment and mailable with the existing SMTP path. Layout: employer,
+  employee (personnummer masked except the last four), period, pay date,
+  gross, tax, net, vacation note omitted, payment account.
+- **TUI — Lön** (new section after Fakturering): `Lönekörningar` (list,
+  Ctrl+N for a new run), `Anställda` under Register, and a **Skattetabeller**
+  action/status that fetches and warns.
+- The run screen shows the preview (F5), posts with Ctrl+Enter after
+  confirmation, then offers the buttons **Lönebesked**, **AGI-underlag** and
+  **Betala skatt & avgifter** as manual steps.
+
+## 7. Testing
+
+- Parser: fixture lines from the real 2026 file (B and % records), off-by-one
+  ranges, BOM handling; tax lookup at range boundaries; the 80,000 kr/month
+  boundary and the top-range percentage formula per SKV 433.
+- Run posting: two employees, correct gross/tax/avgifter/net, balanced
+  voucher with the configured accounts, `source:"payroll"`, AGI JSON values,
+  pay_tax voucher and paid status; stale-table warning.
+- Personnummer: encrypted at rest, masked in responses, decrypt for the AGI
+  export only.