From e692f0fbe16342195297048534ad1be227493107 Mon Sep 17 00:00:00 2001 From: Anders Betts Date: Mon, 21 Sep 2026 09:09:39 +0200 Subject: payroll: employees, tax tables and the monthly run (schema v10) --- docs/PROTOCOL.md | 109 +++++++++++++++++++++++++++++++++++++++++++- docs/SCHEMA.md | 135 +++++++++++++++++++++++++++++++++++++++++++++++++++---- 2 files changed, 233 insertions(+), 11 deletions(-) (limited to 'docs') diff --git a/docs/PROTOCOL.md b/docs/PROTOCOL.md index aafa1c9..9a2a3c4 100644 --- a/docs/PROTOCOL.md +++ b/docs/PROTOCOL.md @@ -111,6 +111,8 @@ Scopes on a token can narrow but never widen the user's role. | `bank.import`, `bank.match`, `bank.unmatch` | | ● | ● | | | `sie.import`, `account.create`, `account.update` | | ● | ● | | | `period.lock`, `fiscal_year.open/close/reopen`, `org.update` | | | ● | | +| `employee.create/update/archive`, `payroll.run_post`, `payroll.pay_tax`, `payroll.settings_set` | | ● | ● | | +| `payroll.agi` (decrypted personnummer), `payroll.tax_tables_fetch/import` | | | ● | | | `org.member_*`, `token.create` for others | | | ● | | | `user.create`, any org | | | | ● | | `backup.snapshot` | | | ● | ● | @@ -155,8 +157,8 @@ enforced at the database level via composite keys (see `SCHEMA.md`). `AUTH_REQUIRED`, `AUTH_FAILED`, `SESSION_EXPIRED`, `RATE_LIMITED`, `ORG_REQUIRED`, `ORG_FORBIDDEN`, `FORBIDDEN`, `NOT_FOUND`, `CONFLICT`, `UNBALANCED`, `ACCOUNT_NOT_FOUND`, `ACCOUNT_INACTIVE`, `FISCAL_YEAR_NOT_FOUND`, -`FISCAL_YEAR_CLOSED`, `PERIOD_LOCKED`, `DATE_OUT_OF_RANGE`, `IMMUTABLE`, -`SEQUENCE_GAP`, `TOO_LARGE`, `UNSUPPORTED`, `SMTP_NOT_CONFIGURED`, +`FISCAL_YEAR_CLOSED`, `FETCH_FAILED`, `PERIOD_LOCKED`, `DATE_OUT_OF_RANGE`, +`IMMUTABLE`, `SEQUENCE_GAP`, `TOO_LARGE`, `UNSUPPORTED`, `SMTP_NOT_CONFIGURED`, `SMTP_FAILED`, `DB_BUSY`, `INTERNAL`. Codes are stable; `message` is human-readable and may change. `details` is @@ -644,6 +646,93 @@ updated and the `invoice.send` audit entry stores `{id,to,subject}` only. `dry_run` validates configuration, recipient and stored document and returns the recipient and subject without sending or updating anything. +### 7.11 Anställda (employees) + +The employee register. `personal_no` is checked for shape (10 or 12 digits, +with or without a hyphen at the usual position) but not for checksum. It is +encrypted at rest with AES-256-GCM under `BOKFD_SECRET_KEY`; a write without +a valid key is `INTERNAL`. Every response except `payroll.agi` masks it, +keeping only the last four characters (`********-1234`); when the key is +missing, reads return an all-masked `********` instead of failing. + +| Command | Args | Result | +|---|---|---| +| `employee.list` | `active_only?` | `items[]` ordered by name, personnummer masked | +| `employee.get` | `id` | one employee, personnummer masked | +| `employee.create` | `name`, `personal_no`, `address?`, `postal_code?`, `city?`, `bank_account?`, `salary_account?`, `monthly_salary_ore?`, `tax_table?`, `tax_column?` | the employee | +| `employee.update` | `id` plus any field (merged) | the effective employee | +| `employee.archive` | `id`, `active` | `id`, `active` | + +`salary_account` (digits only) defaults to the `payroll_salary_account` +setting (default `7210`), `tax_table` is 29–42 and `tax_column` is 1–6. +Creating a second employee with the same personnummer is a `CONFLICT`. All +mutations support `dry_run` and are audited (`employee.create`, +`employee.update`, `employee.archive`); the audit entry redacts the +personnummer. + +### 7.12 Lön (payroll) + +| Command | Args | Result | +|---|---|---| +| `payroll.tax_tables_fetch` | `year?` | `year`, `rows`, `bytes`, `sha256`, `source_url` (owner; audited) | +| `payroll.tax_tables_import` | `year`, `content_base64` | same parse from a supplied file (owner; audited) | +| `payroll.tax_tables_status` | — | `stored_years[]`, `current_year`, `stale`, `fetched_at`, `source_url` | +| `payroll.run_preview` | `period` (YYYY-MM) | per-employee gross/tax/avgifter/net and totals; nothing written | +| `payroll.run_post` | `period`, `pay_date`, `dry_run?` | `id`, `voucher_id`, totals; posts the run voucher | +| `payroll.run_list` | `limit?` | runs, newest period first | +| `payroll.run_get` | `id` | run with `lines[]` | +| `payroll.agi` | `period` | per-employee AGI underlag incl. the personnummer in clear (owner) | +| `payroll.pay_tax` | `run_id`, `date?`, `dry_run?` | payment voucher; marks the run `paid` | +| `payroll.settings_get` | — | effective payroll settings (defaults included) | +| `payroll.settings_set` | `key`, `value` | digits-only write of one `payroll_*` key | + +`payroll.tax_tables_fetch` GETs Skatteverket's "Specialversioner för +programföretag" page over HTTPS (system trust store), takes the year's +`allmanna-tabeller-manad.txt` link in document order (current year first, +so `year` defaults to the current calendar year), downloads and parses the +fixed-width file, and replaces the year's rows plus `tax_table_meta` +(`source_url`, SHA-256, `fetched_at`) in one transaction. The file's B +records (income up to 80,000 kr/month) give the withholding in whole kronor +per table, column and income range; the % records above 80,000 kr are stored +(`pct` is percent × 100) but wave 1 does not implement them: a lookup above +the top B range fails with `INVALID_ARGS` and the message "income above the +tabulated range is not supported yet". SKV 433 (Teknisk beskrivning) +states only that the deduction for income over 80,000 kr "anges i procent" +and that it "får beräknas på samma sätt som beskrivits ovan"; no formula +for applying the percentage is given, so the safe reading is not guessed. +`payroll.tax_tables_import` parses a supplied file the same way +(`source_url` is `import`) for air-gapped installs. A network or TLS failure +is `FETCH_FAILED`. `stale` is true when no table is stored for the current +calendar year. + +`payroll.run_preview` computes, per active employee with a salary, gross = +`monthly_salary_ore`, the table tax for their `tax_table`/`tax_column` +(period year; `run_post` uses the pay date's year), employer contributions +`round(gross × payroll_avgift_rate_bp / 10000)` and net = gross − tax. +Annual income below 1,000 kr is not taxed (SKV rule). `run_post` validates +that `pay_date` lies in a fiscal year whose months include `period`, then in +one transaction posts the `source:"payroll"` voucher — debit each employee's +salary account, debit `payroll_avgift_account`, credit `payroll_tax_account`, +credit `1930` for the total net and credit `payroll_avgift_liability` — and +inserts `payroll_runs` plus one `payroll_run_lines` row per employee. One +run per period: a second `run_post` for the same period is a `CONFLICT`. +Nothing is written on `dry_run`. + +`payroll.agi` returns the posted run's per-employee gross, tax, contributions +and net together with the decrypted `personal_no`; it is owner-only and the +only command that reveals the number. `payroll.pay_tax` books +`source:"payroll_tax"` (debit `payroll_tax_account` and +`payroll_avgift_liability`, credit `payroll_tax_payment_account`) on the +given date (default: today) and sets the run's `status` to `paid` with +`payment_voucher_id`; paying twice is a `CONFLICT`. + +The settings `payroll_salary_account` (`7210`, the default for new +employees), `payroll_tax_account` (`2710`), `payroll_avgift_account` +(`7510`), `payroll_avgift_liability` (`2731`), +`payroll_tax_payment_account` (`1630`) and `payroll_avgift_rate_bp` (`3142`) +are digits-only and are read with `payroll.settings_get` / +`payroll.settings_set`; stored keys also appear in `settings.get`. + ## Command catalogue (generated) @@ -741,6 +830,22 @@ Args: `name:type(values)[!][=default]`, `!` = required. | `invoice.list` | viewer | yes | no | no | `customer_id:int`, `status:enum(issued\|credited)`, `limit:int=200` | | `invoice.pdf` | viewer | yes | no | no | `id:int!` | | `invoice.send` | bookkeeper | yes | yes | yes | `id:int!`, `to:string` | +| `employee.list` | viewer | yes | no | no | `active_only:bool` | +| `employee.get` | viewer | yes | no | no | `id:int!` | +| `employee.create` | bookkeeper | yes | yes | yes | `name:string!`, `personal_no:string!`, `address:string`, `postal_code:string`, `city:string`, `bank_account:string`, `salary_account:string`, `monthly_salary_ore:int=0`, `tax_table:int=30`, `tax_column:int=1` | +| `employee.update` | bookkeeper | yes | yes | yes | `id:int!`, `name:string`, `personal_no:string`, `address:string`, `postal_code:string`, `city:string`, `bank_account:string`, `salary_account:string`, `monthly_salary_ore:int`, `tax_table:int`, `tax_column:int`, `active:bool` | +| `employee.archive` | bookkeeper | yes | yes | yes | `id:int!`, `active:bool!` | +| `payroll.tax_tables_fetch` | owner | yes | yes | yes | `year:int` | +| `payroll.tax_tables_import` | owner | yes | yes | yes | `year:int!`, `content_base64:string!` | +| `payroll.tax_tables_status` | viewer | yes | no | no | — | +| `payroll.run_preview` | bookkeeper | yes | no | no | `period:string!` | +| `payroll.run_post` | bookkeeper | yes | yes | yes | `period:string!`, `pay_date:date!` | +| `payroll.run_list` | viewer | yes | no | no | `limit:int=100` | +| `payroll.run_get` | viewer | yes | no | no | `id:int!` | +| `payroll.agi` | owner | yes | no | no | `period:string!` | +| `payroll.pay_tax` | bookkeeper | yes | yes | yes | `run_id:int!`, `date:date` | +| `payroll.settings_get` | viewer | yes | no | no | — | +| `payroll.settings_set` | bookkeeper | yes | yes | yes | `key:string!`, `value:string!` | ## 8. The TUI is just a client diff --git a/docs/SCHEMA.md b/docs/SCHEMA.md index 15e375a..f2e93ac 100644 --- a/docs/SCHEMA.md +++ b/docs/SCHEMA.md @@ -48,12 +48,19 @@ orgs ─┬─ memberships ── users ── api_tokens ├─ idempotency ├─ report_rules ├─ bank_transactions ── bank_matches + ├─ employees ── payroll_run_lines + ├─ payroll_runs ── payroll_run_lines └─ settings + +tax_tables / tax_table_meta (national reference data, no org_id) ``` `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. +not org-scoped. `tax_tables` and `tax_table_meta` are the one deliberate +exception: they hold Skatteverket's published tables for the whole country, +identical for every org, so they carry no `org_id` and are shared read-only +reference data. Every command that writes them is owner-only and audited. ## 4. Identity and tenancy @@ -203,7 +210,7 @@ CREATE TABLE vouchers ( description TEXT NOT NULL CHECK (length(description) > 0), source TEXT NOT NULL DEFAULT 'manual' CHECK (source IN ('manual','agent','sie_import','system','ib', - 'invoice')), + 'invoice','payroll','payroll_tax')), client_ref TEXT, corrects_voucher_id INTEGER, created_at TEXT NOT NULL, @@ -448,8 +455,11 @@ 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`, -`bank.import`, `bank.match`, `bank.unmatch`, `settings.update`. Reads are -logged only when `audit_reads = true`. +`bank.import`, `bank.match`, `bank.unmatch`, `settings.update`, +`customer.create`, `invoice.issue`, `invoice.send`, `employee.create`, +`employee.update`, `employee.archive`, `payroll.tax_tables_fetch`, +`payroll.tax_tables_import`, `payroll.run_post`, `payroll.pay_tax`, +`payroll.settings_set`. Reads are logged only when `audit_reads = true`. ## 10. Reporting rules and settings @@ -550,10 +560,11 @@ 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: **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). + and `created_at`. Current version: **10** (v10 adds the payroll tables and + the `payroll`/`payroll_tax` voucher sources, v9 adds the invoicing tables + and `invoice`, v8 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 @@ -588,7 +599,113 @@ change afterwards. The DDL and field semantics are in `docs/INVOICING.md` rebuilding the table in the v9 migration (foreign keys are disabled for the migration and `PRAGMA foreign_key_check` runs before they are re-enabled). -## 15. Seeds +## 15. Payroll (schema v10) + +The employee register and the monthly runs. `personal_no_enc` holds the +AES-256-GCM envelope (`enc:v1::`, `src/secret.c`) under +`BOKFD_SECRET_KEY`; the plain number never touches the database. The run +tables are mutable configuration/business documents, not ledger data: the +money is in the immutable voucher of each run. `vouchers.source` gained +`'payroll'` (the monthly run) and `'payroll_tax'` (the payment to the tax +account); widening that CHECK required rebuilding the table in the v10 +migration, exactly like v9 (foreign keys are disabled for the migration and +`PRAGMA foreign_key_check` runs before they are re-enabled). + +```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; +``` + +`payroll_runs` stores the posted totals and links both vouchers; the +one-run-per-period rule is enforced by `payroll.run_post` on top of the +`(org_id, period, pay_date)` key, and `status` flips to `paid` when +`payroll.pay_tax` links the payment voucher. + +Skatteverket's allmänna monthly tables are national reference data and the +only tables without `org_id` (an explicit exception to principle 4): + +```sql +CREATE TABLE tax_tables ( + in_year INTEGER NOT NULL, + table_no INTEGER NOT NULL, -- 29..42 + column_no INTEGER NOT NULL, -- 1..6 + income_from_ore INTEGER NOT NULL, + income_to_ore INTEGER, -- NULL = open-ended top range + tax_ore INTEGER NOT NULL, -- whole kronor x100 for B rows + pct INTEGER, -- % rows: percent x100, else NULL + 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; +``` + +B rows (`pct IS NULL`) hold the withholding in öre; % rows above the +tabulated 80,000 kr/month range hold the percentage ×100 in `pct` with +`tax_ore = 0` and may have `income_to_ore IS NULL` for the open-ended top +range. Wave 1 looks up only B ranges (see `PROTOCOL.md` §7.12). + +## 16. Seeds | Data | Source | Notes | |---|---|---| -- cgit v1.3