185 lines
8.3 KiB
SQL
185 lines
8.3 KiB
SQL
|
-- BOC Ledger Schema
|
||
|
|
-- Fullständigt bokföringsschema för LandveX AB
|
||
|
|
-- BAS-kontoplan, verifikationer, moms, arbetsgivaravgift
|
||
|
|
|
||
|
|
-- Kontoplan (BAS-standard)
|
||
|
|
CREATE TABLE IF NOT EXISTS boc_accounts (
|
||
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
|
|
tenant_id UUID REFERENCES boc_tenants(id) ON DELETE CASCADE,
|
||
|
|
account_number TEXT NOT NULL, -- BAS-kontonummer, t.ex. 1930
|
||
|
|
name TEXT NOT NULL,
|
||
|
|
account_type TEXT NOT NULL, -- asset, liability, equity, income, expense
|
||
|
|
vat_code TEXT, -- moms-kod, t.ex. 25, 12, 6
|
||
|
|
parent_account TEXT, -- överordnat konto
|
||
|
|
is_active BOOLEAN DEFAULT TRUE,
|
||
|
|
metadata JSONB DEFAULT '{}',
|
||
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
|
|
UNIQUE(tenant_id, account_number)
|
||
|
|
);
|
||
|
|
|
||
|
|
-- Verifikationer (bokföringsposter)
|
||
|
|
CREATE TABLE IF NOT EXISTS boc_vouchers (
|
||
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
|
|
tenant_id UUID REFERENCES boc_tenants(id) ON DELETE CASCADE,
|
||
|
|
voucher_series TEXT NOT NULL DEFAULT 'A', -- verifikationsserie
|
||
|
|
voucher_number INTEGER NOT NULL,
|
||
|
|
date DATE NOT NULL,
|
||
|
|
description TEXT NOT NULL,
|
||
|
|
reference TEXT, -- fakturanummer, referens etc
|
||
|
|
attachments JSONB DEFAULT '[]', -- bilagor
|
||
|
|
created_by UUID REFERENCES boc_users(id),
|
||
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
|
|
UNIQUE(tenant_id, voucher_series, voucher_number)
|
||
|
|
);
|
||
|
|
|
||
|
|
-- Verifikationstransaktioner (dubbel bokföring)
|
||
|
|
CREATE TABLE IF NOT EXISTS boc_voucher_lines (
|
||
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
|
|
tenant_id UUID REFERENCES boc_tenants(id) ON DELETE CASCADE,
|
||
|
|
voucher_id UUID REFERENCES boc_vouchers(id) ON DELETE CASCADE,
|
||
|
|
account_id UUID REFERENCES boc_accounts(id) ON DELETE RESTRICT,
|
||
|
|
debit DECIMAL(15,2) NOT NULL DEFAULT 0,
|
||
|
|
credit DECIMAL(15,2) NOT NULL DEFAULT 0,
|
||
|
|
description TEXT,
|
||
|
|
project TEXT, -- projektkod
|
||
|
|
cost_center TEXT, -- kostnadsställe
|
||
|
|
metadata JSONB DEFAULT '{}',
|
||
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
||
|
|
);
|
||
|
|
|
||
|
|
-- Saldon per konto och period
|
||
|
|
CREATE TABLE IF NOT EXISTS boc_account_balances (
|
||
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
|
|
tenant_id UUID REFERENCES boc_tenants(id) ON DELETE CASCADE,
|
||
|
|
account_id UUID REFERENCES boc_accounts(id) ON DELETE CASCADE,
|
||
|
|
fiscal_year INTEGER NOT NULL,
|
||
|
|
period INTEGER NOT NULL, -- 1-12 för månad, 0 för årssaldo
|
||
|
|
opening_balance DECIMAL(15,2) NOT NULL DEFAULT 0,
|
||
|
|
closing_balance DECIMAL(15,2) NOT NULL DEFAULT 0,
|
||
|
|
total_debit DECIMAL(15,2) NOT NULL DEFAULT 0,
|
||
|
|
total_credit DECIMAL(15,2) NOT NULL DEFAULT 0,
|
||
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
|
|
UNIQUE(tenant_id, account_id, fiscal_year, period)
|
||
|
|
);
|
||
|
|
|
||
|
|
-- Momsredovisning
|
||
|
|
CREATE TABLE IF NOT EXISTS boc_vat_reports (
|
||
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
|
|
tenant_id UUID REFERENCES boc_tenants(id) ON DELETE CASCADE,
|
||
|
|
period_start DATE NOT NULL,
|
||
|
|
period_end DATE NOT NULL,
|
||
|
|
vat_in DECIMAL(15,2) NOT NULL DEFAULT 0, -- ingående moms
|
||
|
|
vat_out DECIMAL(15,2) NOT NULL DEFAULT 0, -- utgående moms
|
||
|
|
vat_payable DECIMAL(15,2) NOT NULL DEFAULT 0, -- moms att betala
|
||
|
|
status TEXT NOT NULL DEFAULT 'draft', -- draft, filed, paid
|
||
|
|
filed_at TIMESTAMPTZ,
|
||
|
|
paid_at TIMESTAMPTZ,
|
||
|
|
metadata JSONB DEFAULT '{}',
|
||
|
|
created_by UUID REFERENCES boc_users(id),
|
||
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
||
|
|
);
|
||
|
|
|
||
|
|
-- Moms-transaktioner per verifikation
|
||
|
|
CREATE TABLE IF NOT EXISTS boc_vat_transactions (
|
||
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
|
|
tenant_id UUID REFERENCES boc_tenants(id) ON DELETE CASCADE,
|
||
|
|
voucher_line_id UUID REFERENCES boc_voucher_lines(id) ON DELETE CASCADE,
|
||
|
|
vat_rate DECIMAL(5,2) NOT NULL, -- 25.00, 12.00, 6.00
|
||
|
|
vat_amount DECIMAL(15,2) NOT NULL,
|
||
|
|
base_amount DECIMAL(15,2) NOT NULL,
|
||
|
|
vat_type TEXT NOT NULL, -- input, output
|
||
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
||
|
|
);
|
||
|
|
|
||
|
|
-- Löner och arbetsgivaravgifter
|
||
|
|
CREATE TABLE IF NOT EXISTS boc_payroll (
|
||
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
|
|
tenant_id UUID REFERENCES boc_tenants(id) ON DELETE CASCADE,
|
||
|
|
employee_id UUID REFERENCES boc_employees(id),
|
||
|
|
period DATE NOT NULL, -- löne månad
|
||
|
|
gross_salary DECIMAL(15,2) NOT NULL,
|
||
|
|
net_salary DECIMAL(15,2) NOT NULL,
|
||
|
|
tax_deduction DECIMAL(15,2) NOT NULL DEFAULT 0, -- skatteavdrag
|
||
|
|
pension_contribution DECIMAL(15,2) DEFAULT 0, -- pensionsinbetalning
|
||
|
|
employer_contribution DECIMAL(15,2) DEFAULT 0, -- arbetsgivaravgift
|
||
|
|
benefits JSONB DEFAULT '{}', -- förmåner
|
||
|
|
deductions JSONB DEFAULT '{}', -- avdrag
|
||
|
|
status TEXT NOT NULL DEFAULT 'draft',
|
||
|
|
paid_at TIMESTAMPTZ,
|
||
|
|
metadata JSONB DEFAULT '{}',
|
||
|
|
created_by UUID REFERENCES boc_users(id),
|
||
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
||
|
|
);
|
||
|
|
|
||
|
|
-- Arbetsgivaravgiftsberäkning
|
||
|
|
CREATE TABLE IF NOT EXISTS boc_employer_contributions (
|
||
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
|
|
tenant_id UUID REFERENCES boc_tenants(id) ON DELETE CASCADE,
|
||
|
|
period DATE NOT NULL,
|
||
|
|
total_salary DECIMAL(15,2) NOT NULL,
|
||
|
|
health_insurance DECIMAL(15,2) NOT NULL DEFAULT 0, -- sjukförsäkringsavgift
|
||
|
|
pension_fee DECIMAL(15,2) NOT NULL DEFAULT 0, -- ålderspensionsavgift
|
||
|
|
parental_fee DECIMAL(15,2) NOT NULL DEFAULT 0, -- föräldraförsäkringsavgift
|
||
|
|
occupational_fee DECIMAL(15,2) NOT NULL DEFAULT 0, -- arbetsmarknadsförsäkringsavgift
|
||
|
|
general_payroll_tax DECIMAL(15,2) NOT NULL DEFAULT 0, -- allmän löneavgift
|
||
|
|
total_contribution DECIMAL(15,2) NOT NULL DEFAULT 0,
|
||
|
|
status TEXT NOT NULL DEFAULT 'draft',
|
||
|
|
paid_at TIMESTAMPTZ,
|
||
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
||
|
|
);
|
||
|
|
|
||
|
|
-- Fakturor (kund och leverantör)
|
||
|
|
CREATE TABLE IF NOT EXISTS boc_invoices_ledger (
|
||
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
|
|
tenant_id UUID REFERENCES boc_tenants(id) ON DELETE CASCADE,
|
||
|
|
invoice_type TEXT NOT NULL, -- customer, supplier
|
||
|
|
invoice_number TEXT NOT NULL,
|
||
|
|
counterparty TEXT NOT NULL, -- kund/leverantör namn
|
||
|
|
org_number TEXT,
|
||
|
|
amount DECIMAL(15,2) NOT NULL,
|
||
|
|
vat_amount DECIMAL(15,2) NOT NULL DEFAULT 0,
|
||
|
|
total_amount DECIMAL(15,2) NOT NULL,
|
||
|
|
currency TEXT NOT NULL DEFAULT 'SEK',
|
||
|
|
issue_date DATE NOT NULL,
|
||
|
|
due_date DATE NOT NULL,
|
||
|
|
paid_date DATE,
|
||
|
|
paid_amount DECIMAL(15,2) DEFAULT 0,
|
||
|
|
status TEXT NOT NULL DEFAULT 'draft', -- draft, sent, paid, overdue, cancelled
|
||
|
|
voucher_id UUID REFERENCES boc_vouchers(id),
|
||
|
|
metadata JSONB DEFAULT '{}',
|
||
|
|
created_by UUID REFERENCES boc_users(id),
|
||
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
||
|
|
);
|
||
|
|
|
||
|
|
-- Räkenskapsår
|
||
|
|
CREATE TABLE IF NOT EXISTS boc_fiscal_years (
|
||
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
|
|
tenant_id UUID REFERENCES boc_tenants(id) ON DELETE CASCADE,
|
||
|
|
year INTEGER NOT NULL,
|
||
|
|
start_date DATE NOT NULL,
|
||
|
|
end_date DATE NOT NULL,
|
||
|
|
status TEXT NOT NULL DEFAULT 'open', -- open, closed, locked
|
||
|
|
closed_at TIMESTAMPTZ,
|
||
|
|
closed_by UUID REFERENCES boc_users(id),
|
||
|
|
metadata JSONB DEFAULT '{}',
|
||
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
|
|
UNIQUE(tenant_id, year)
|
||
|
|
);
|
||
|
|
|
||
|
|
-- Index
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_accounts_tenant ON boc_accounts(tenant_id, account_number);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_vouchers_date ON boc_vouchers(tenant_id, date DESC);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_voucher_lines_account ON boc_voucher_lines(account_id, created_at DESC);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_balances_period ON boc_account_balances(tenant_id, fiscal_year, period);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_vat_period ON boc_vat_reports(tenant_id, period_start, period_end);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_payroll_period ON boc_payroll(tenant_id, period);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_invoices_ledger ON boc_invoices_ledger(tenant_id, invoice_type, status);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_fiscal_years ON boc_fiscal_years(tenant_id, year);
|