diff options
Diffstat (limited to 'docs/SCHEMA.md')
| -rw-r--r-- | docs/SCHEMA.md | 135 |
1 files changed, 126 insertions, 9 deletions
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:<nonce>:<ciphertext>`, `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 | |---|---|---| |