302 lines
14 KiB
SQL
302 lines
14 KiB
SQL
-- REQ-001 payroll foundation integrated after the attendance portal.
|
|
-- Money is BIGINT in minor units; rates and weights use basis points (10000 = 100%).
|
|
|
|
CREATE TABLE staff_org_units (
|
|
id VARCHAR(26) PRIMARY KEY CHECK (id ~ '^[0-9A-HJKMNP-TV-Z]{26}$'),
|
|
tenant_id VARCHAR(26) NOT NULL CHECK (tenant_id ~ '^[0-9A-HJKMNP-TV-Z]{26}$'),
|
|
code TEXT NOT NULL CHECK (code ~ '^[a-z0-9_-]{1,40}$'),
|
|
name TEXT NOT NULL,
|
|
timezone TEXT NOT NULL DEFAULT 'Asia/Novosibirsk',
|
|
external_ref TEXT,
|
|
is_active BOOLEAN NOT NULL DEFAULT TRUE,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
deleted_at TIMESTAMPTZ,
|
|
UNIQUE (tenant_id, id)
|
|
);
|
|
CREATE UNIQUE INDEX staff_org_units_code_idx ON staff_org_units (tenant_id, code)
|
|
WHERE deleted_at IS NULL;
|
|
|
|
CREATE TABLE staff_org_unit_terms (
|
|
id VARCHAR(26) PRIMARY KEY CHECK (id ~ '^[0-9A-HJKMNP-TV-Z]{26}$'),
|
|
tenant_id VARCHAR(26) NOT NULL CHECK (tenant_id ~ '^[0-9A-HJKMNP-TV-Z]{26}$'),
|
|
org_unit_id VARCHAR(26) NOT NULL,
|
|
effective_from DATE NOT NULL,
|
|
effective_to DATE,
|
|
pool_rule JSONB,
|
|
shift_norm_minutes INTEGER NOT NULL DEFAULT 720 CHECK (shift_norm_minutes BETWEEN 60 AND 2880),
|
|
rounding_unit_minor BIGINT NOT NULL DEFAULT 100 CHECK (rounding_unit_minor > 0),
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
created_by VARCHAR(26) NOT NULL CHECK (created_by ~ '^[0-9A-HJKMNP-TV-Z]{26}$'),
|
|
UNIQUE (tenant_id, id),
|
|
FOREIGN KEY (tenant_id, org_unit_id) REFERENCES staff_org_units (tenant_id, id),
|
|
CHECK (effective_to IS NULL OR effective_to >= effective_from)
|
|
);
|
|
CREATE INDEX staff_org_unit_terms_lookup_idx
|
|
ON staff_org_unit_terms (tenant_id, org_unit_id, effective_from DESC);
|
|
|
|
ALTER TABLE staff_employees
|
|
ADD COLUMN primary_org_unit_id VARCHAR(26),
|
|
ADD COLUMN external_ref TEXT,
|
|
ADD COLUMN hired_on DATE,
|
|
ADD COLUMN terminated_on DATE,
|
|
ADD CONSTRAINT staff_employees_primary_org_unit_fk
|
|
FOREIGN KEY (tenant_id, primary_org_unit_id) REFERENCES staff_org_units (tenant_id, id);
|
|
|
|
ALTER TABLE staff_attendance_events
|
|
ADD COLUMN org_unit_id VARCHAR(26),
|
|
ADD COLUMN payload JSONB NOT NULL DEFAULT '{}'::jsonb,
|
|
ADD CONSTRAINT staff_attendance_events_org_unit_fk
|
|
FOREIGN KEY (tenant_id, org_unit_id) REFERENCES staff_org_units (tenant_id, id);
|
|
|
|
CREATE TABLE staff_metric_definitions (
|
|
id VARCHAR(26) PRIMARY KEY CHECK (id ~ '^[0-9A-HJKMNP-TV-Z]{26}$'),
|
|
tenant_id VARCHAR(26) NOT NULL CHECK (tenant_id ~ '^[0-9A-HJKMNP-TV-Z]{26}$'),
|
|
key TEXT NOT NULL CHECK (key ~ '^[a-z][a-z0-9_]{0,60}$'),
|
|
label TEXT NOT NULL,
|
|
value_kind TEXT NOT NULL DEFAULT 'money' CHECK (value_kind IN ('money', 'percent', 'number')),
|
|
source TEXT NOT NULL DEFAULT 'manual' CHECK (source IN ('manual', 'module', 'import')),
|
|
is_active BOOLEAN NOT NULL DEFAULT TRUE,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
UNIQUE (tenant_id, id),
|
|
UNIQUE (tenant_id, key)
|
|
);
|
|
|
|
CREATE TABLE staff_daily_metrics (
|
|
id VARCHAR(26) PRIMARY KEY CHECK (id ~ '^[0-9A-HJKMNP-TV-Z]{26}$'),
|
|
tenant_id VARCHAR(26) NOT NULL CHECK (tenant_id ~ '^[0-9A-HJKMNP-TV-Z]{26}$'),
|
|
org_unit_id VARCHAR(26) NOT NULL,
|
|
business_date DATE NOT NULL,
|
|
metric_key TEXT NOT NULL CHECK (metric_key ~ '^[a-z][a-z0-9_]{0,60}$'),
|
|
value BIGINT NOT NULL,
|
|
currency_code CHAR(3) NOT NULL DEFAULT 'RUB',
|
|
breakdown JSONB NOT NULL DEFAULT '{}'::jsonb,
|
|
source TEXT NOT NULL DEFAULT 'manual' CHECK (source IN ('manual', 'module', 'import')),
|
|
recorded_by VARCHAR(26) NOT NULL CHECK (recorded_by ~ '^[0-9A-HJKMNP-TV-Z]{26}$'),
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
UNIQUE (tenant_id, id),
|
|
UNIQUE (tenant_id, org_unit_id, business_date, metric_key),
|
|
FOREIGN KEY (tenant_id, org_unit_id) REFERENCES staff_org_units (tenant_id, id),
|
|
FOREIGN KEY (tenant_id, metric_key) REFERENCES staff_metric_definitions (tenant_id, key)
|
|
);
|
|
|
|
CREATE TABLE staff_work_items (
|
|
id VARCHAR(26) PRIMARY KEY CHECK (id ~ '^[0-9A-HJKMNP-TV-Z]{26}$'),
|
|
tenant_id VARCHAR(26) NOT NULL CHECK (tenant_id ~ '^[0-9A-HJKMNP-TV-Z]{26}$'),
|
|
employee_id VARCHAR(26) NOT NULL,
|
|
org_unit_id VARCHAR(26) NOT NULL,
|
|
business_date DATE NOT NULL,
|
|
item_kind TEXT NOT NULL CHECK (item_kind ~ '^[a-z][a-z0-9_]{0,40}$'),
|
|
attributes JSONB NOT NULL DEFAULT '{}'::jsonb,
|
|
source TEXT NOT NULL DEFAULT 'manual' CHECK (source IN ('manual', 'module', 'import')),
|
|
external_key TEXT,
|
|
created_by VARCHAR(26) NOT NULL CHECK (created_by ~ '^[0-9A-HJKMNP-TV-Z]{26}$'),
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
UNIQUE (tenant_id, id),
|
|
FOREIGN KEY (tenant_id, employee_id) REFERENCES staff_employees (tenant_id, id),
|
|
FOREIGN KEY (tenant_id, org_unit_id) REFERENCES staff_org_units (tenant_id, id)
|
|
);
|
|
CREATE INDEX staff_work_items_day_idx ON staff_work_items (tenant_id, employee_id, business_date);
|
|
CREATE UNIQUE INDEX staff_work_items_external_idx ON staff_work_items (tenant_id, external_key)
|
|
WHERE external_key IS NOT NULL;
|
|
|
|
CREATE TABLE staff_pay_rules (
|
|
id VARCHAR(26) PRIMARY KEY CHECK (id ~ '^[0-9A-HJKMNP-TV-Z]{26}$'),
|
|
tenant_id VARCHAR(26) NOT NULL CHECK (tenant_id ~ '^[0-9A-HJKMNP-TV-Z]{26}$'),
|
|
code TEXT NOT NULL CHECK (code ~ '^[a-z][a-z0-9_]{0,40}$'),
|
|
version INTEGER NOT NULL CHECK (version > 0),
|
|
name TEXT NOT NULL,
|
|
description TEXT NOT NULL DEFAULT '',
|
|
preset_code TEXT,
|
|
rule JSONB NOT NULL,
|
|
param_declarations JSONB NOT NULL DEFAULT '[]'::jsonb,
|
|
pool_weight_bp INTEGER NOT NULL DEFAULT 0 CHECK (pool_weight_bp BETWEEN 0 AND 20000),
|
|
effective_from DATE NOT NULL,
|
|
effective_to DATE,
|
|
approved_by VARCHAR(26) CHECK (approved_by IS NULL OR approved_by ~ '^[0-9A-HJKMNP-TV-Z]{26}$'),
|
|
approved_at TIMESTAMPTZ,
|
|
created_by VARCHAR(26) NOT NULL CHECK (created_by ~ '^[0-9A-HJKMNP-TV-Z]{26}$'),
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
UNIQUE (tenant_id, id),
|
|
UNIQUE (tenant_id, code, version),
|
|
CHECK (effective_to IS NULL OR effective_to >= effective_from)
|
|
);
|
|
CREATE INDEX staff_pay_rules_lookup_idx ON staff_pay_rules (tenant_id, code, effective_from DESC);
|
|
|
|
CREATE TABLE staff_pay_assignments (
|
|
id VARCHAR(26) PRIMARY KEY CHECK (id ~ '^[0-9A-HJKMNP-TV-Z]{26}$'),
|
|
tenant_id VARCHAR(26) NOT NULL CHECK (tenant_id ~ '^[0-9A-HJKMNP-TV-Z]{26}$'),
|
|
employee_id VARCHAR(26) NOT NULL,
|
|
org_unit_id VARCHAR(26),
|
|
pay_rule_id VARCHAR(26) NOT NULL,
|
|
effective_from DATE NOT NULL,
|
|
effective_to DATE,
|
|
params JSONB NOT NULL DEFAULT '{}'::jsonb,
|
|
pool_weight_bp INTEGER CHECK (pool_weight_bp IS NULL OR pool_weight_bp BETWEEN 0 AND 20000),
|
|
note TEXT,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
created_by VARCHAR(26) NOT NULL CHECK (created_by ~ '^[0-9A-HJKMNP-TV-Z]{26}$'),
|
|
UNIQUE (tenant_id, id),
|
|
FOREIGN KEY (tenant_id, employee_id) REFERENCES staff_employees (tenant_id, id),
|
|
FOREIGN KEY (tenant_id, org_unit_id) REFERENCES staff_org_units (tenant_id, id),
|
|
FOREIGN KEY (tenant_id, pay_rule_id) REFERENCES staff_pay_rules (tenant_id, id),
|
|
CHECK (effective_to IS NULL OR effective_to >= effective_from)
|
|
);
|
|
CREATE INDEX staff_pay_assignments_lookup_idx
|
|
ON staff_pay_assignments (tenant_id, employee_id, effective_from DESC);
|
|
|
|
CREATE TABLE staff_work_sessions (
|
|
id VARCHAR(26) PRIMARY KEY CHECK (id ~ '^[0-9A-HJKMNP-TV-Z]{26}$'),
|
|
tenant_id VARCHAR(26) NOT NULL CHECK (tenant_id ~ '^[0-9A-HJKMNP-TV-Z]{26}$'),
|
|
employee_id VARCHAR(26) NOT NULL,
|
|
org_unit_id VARCHAR(26) NOT NULL,
|
|
business_date DATE NOT NULL,
|
|
started_at TIMESTAMPTZ,
|
|
ended_at TIMESTAMPTZ,
|
|
worked_minutes INTEGER NOT NULL DEFAULT 0 CHECK (worked_minutes BETWEEN 0 AND 2880),
|
|
source TEXT NOT NULL DEFAULT 'terminal' CHECK (source IN ('terminal', 'manual')),
|
|
status TEXT NOT NULL DEFAULT 'open' CHECK (status IN ('open', 'pending_review', 'confirmed', 'rejected')),
|
|
anomaly TEXT,
|
|
correction_note TEXT,
|
|
confirmed_by VARCHAR(26) CHECK (confirmed_by IS NULL OR confirmed_by ~ '^[0-9A-HJKMNP-TV-Z]{26}$'),
|
|
confirmed_at TIMESTAMPTZ,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
revision INTEGER NOT NULL DEFAULT 1 CHECK (revision > 0),
|
|
UNIQUE (tenant_id, id),
|
|
UNIQUE (tenant_id, employee_id, org_unit_id, business_date),
|
|
FOREIGN KEY (tenant_id, employee_id) REFERENCES staff_employees (tenant_id, id),
|
|
FOREIGN KEY (tenant_id, org_unit_id) REFERENCES staff_org_units (tenant_id, id),
|
|
CHECK (ended_at IS NULL OR started_at IS NULL OR ended_at >= started_at)
|
|
);
|
|
CREATE INDEX staff_work_sessions_review_idx ON staff_work_sessions (tenant_id, business_date DESC, status);
|
|
|
|
CREATE TABLE staff_payroll_runs (
|
|
id VARCHAR(26) PRIMARY KEY CHECK (id ~ '^[0-9A-HJKMNP-TV-Z]{26}$'),
|
|
tenant_id VARCHAR(26) NOT NULL CHECK (tenant_id ~ '^[0-9A-HJKMNP-TV-Z]{26}$'),
|
|
org_unit_id VARCHAR(26),
|
|
period_start DATE NOT NULL,
|
|
period_end DATE NOT NULL,
|
|
status TEXT NOT NULL DEFAULT 'draft' CHECK (status IN ('draft', 'calculated', 'approved', 'paid')),
|
|
total_accrued_minor BIGINT NOT NULL DEFAULT 0,
|
|
currency_code CHAR(3) NOT NULL DEFAULT 'RUB',
|
|
calculated_at TIMESTAMPTZ,
|
|
approved_by VARCHAR(26) CHECK (approved_by IS NULL OR approved_by ~ '^[0-9A-HJKMNP-TV-Z]{26}$'),
|
|
approved_at TIMESTAMPTZ,
|
|
created_by VARCHAR(26) NOT NULL CHECK (created_by ~ '^[0-9A-HJKMNP-TV-Z]{26}$'),
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
revision INTEGER NOT NULL DEFAULT 1 CHECK (revision > 0),
|
|
UNIQUE (tenant_id, id),
|
|
FOREIGN KEY (tenant_id, org_unit_id) REFERENCES staff_org_units (tenant_id, id),
|
|
CHECK (period_end >= period_start)
|
|
);
|
|
CREATE UNIQUE INDEX staff_payroll_runs_period_unit_idx
|
|
ON staff_payroll_runs (tenant_id, org_unit_id, period_start) WHERE org_unit_id IS NOT NULL;
|
|
CREATE UNIQUE INDEX staff_payroll_runs_period_all_idx
|
|
ON staff_payroll_runs (tenant_id, period_start) WHERE org_unit_id IS NULL;
|
|
|
|
CREATE TABLE staff_payroll_lines (
|
|
id VARCHAR(26) PRIMARY KEY CHECK (id ~ '^[0-9A-HJKMNP-TV-Z]{26}$'),
|
|
tenant_id VARCHAR(26) NOT NULL CHECK (tenant_id ~ '^[0-9A-HJKMNP-TV-Z]{26}$'),
|
|
run_id VARCHAR(26) NOT NULL,
|
|
employee_id VARCHAR(26) NOT NULL,
|
|
org_unit_id VARCHAR(26) NOT NULL,
|
|
business_date DATE NOT NULL,
|
|
pay_rule_id VARCHAR(26) NOT NULL,
|
|
rule_code TEXT NOT NULL,
|
|
worked_minutes INTEGER NOT NULL DEFAULT 0,
|
|
pool_amount_minor BIGINT NOT NULL DEFAULT 0,
|
|
pool_divisor_bp INTEGER NOT NULL DEFAULT 0,
|
|
pool_share_minor BIGINT NOT NULL DEFAULT 0,
|
|
raw_amount_minor BIGINT NOT NULL DEFAULT 0,
|
|
amount_minor BIGINT NOT NULL DEFAULT 0,
|
|
currency_code CHAR(3) NOT NULL DEFAULT 'RUB',
|
|
explain JSONB NOT NULL DEFAULT '{}'::jsonb,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
UNIQUE (tenant_id, id),
|
|
UNIQUE (tenant_id, run_id, employee_id, org_unit_id, business_date),
|
|
FOREIGN KEY (tenant_id, run_id) REFERENCES staff_payroll_runs (tenant_id, id) ON DELETE CASCADE,
|
|
FOREIGN KEY (tenant_id, employee_id) REFERENCES staff_employees (tenant_id, id),
|
|
FOREIGN KEY (tenant_id, org_unit_id) REFERENCES staff_org_units (tenant_id, id),
|
|
FOREIGN KEY (tenant_id, pay_rule_id) REFERENCES staff_pay_rules (tenant_id, id)
|
|
);
|
|
CREATE INDEX staff_payroll_lines_employee_idx
|
|
ON staff_payroll_lines (tenant_id, employee_id, business_date DESC);
|
|
|
|
CREATE TABLE staff_payouts (
|
|
id VARCHAR(26) PRIMARY KEY CHECK (id ~ '^[0-9A-HJKMNP-TV-Z]{26}$'),
|
|
tenant_id VARCHAR(26) NOT NULL CHECK (tenant_id ~ '^[0-9A-HJKMNP-TV-Z]{26}$'),
|
|
employee_id VARCHAR(26) NOT NULL,
|
|
run_id VARCHAR(26),
|
|
paid_on DATE NOT NULL,
|
|
amount_minor BIGINT NOT NULL CHECK (amount_minor > 0),
|
|
currency_code CHAR(3) NOT NULL DEFAULT 'RUB',
|
|
method TEXT NOT NULL DEFAULT 'cash' CHECK (method IN ('cash', 'transfer')),
|
|
note TEXT,
|
|
created_by VARCHAR(26) NOT NULL CHECK (created_by ~ '^[0-9A-HJKMNP-TV-Z]{26}$'),
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
UNIQUE (tenant_id, id),
|
|
FOREIGN KEY (tenant_id, employee_id) REFERENCES staff_employees (tenant_id, id),
|
|
FOREIGN KEY (tenant_id, run_id) REFERENCES staff_payroll_runs (tenant_id, id)
|
|
);
|
|
CREATE INDEX staff_payouts_employee_idx ON staff_payouts (tenant_id, employee_id, paid_on DESC);
|
|
|
|
CREATE TABLE staff_payroll_ledger (
|
|
id VARCHAR(26) PRIMARY KEY CHECK (id ~ '^[0-9A-HJKMNP-TV-Z]{26}$'),
|
|
tenant_id VARCHAR(26) NOT NULL CHECK (tenant_id ~ '^[0-9A-HJKMNP-TV-Z]{26}$'),
|
|
employee_id VARCHAR(26) NOT NULL,
|
|
business_date DATE NOT NULL,
|
|
kind TEXT NOT NULL CHECK (kind IN ('accrual', 'payout', 'adjustment')),
|
|
amount_minor BIGINT NOT NULL,
|
|
currency_code CHAR(3) NOT NULL DEFAULT 'RUB',
|
|
ref_type TEXT NOT NULL CHECK (ref_type IN ('payroll_run', 'payout', 'manual')),
|
|
ref_id VARCHAR(26),
|
|
correlation_id VARCHAR(26) NOT NULL CHECK (correlation_id ~ '^[0-9A-HJKMNP-TV-Z]{26}$'),
|
|
note TEXT,
|
|
created_by VARCHAR(26) NOT NULL CHECK (created_by ~ '^[0-9A-HJKMNP-TV-Z]{26}$'),
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
UNIQUE (tenant_id, id),
|
|
FOREIGN KEY (tenant_id, employee_id) REFERENCES staff_employees (tenant_id, id)
|
|
);
|
|
CREATE INDEX staff_payroll_ledger_balance_idx
|
|
ON staff_payroll_ledger (tenant_id, employee_id, business_date);
|
|
|
|
DO $$
|
|
DECLARE target_table TEXT;
|
|
BEGIN
|
|
FOREACH target_table IN ARRAY ARRAY[
|
|
'staff_org_units', 'staff_org_unit_terms', 'staff_metric_definitions',
|
|
'staff_daily_metrics', 'staff_work_items', 'staff_pay_rules', 'staff_pay_assignments',
|
|
'staff_work_sessions', 'staff_payroll_runs', 'staff_payroll_lines', 'staff_payouts'
|
|
] LOOP
|
|
EXECUTE format('ALTER TABLE %I ENABLE ROW LEVEL SECURITY', target_table);
|
|
EXECUTE format('ALTER TABLE %I FORCE ROW LEVEL SECURITY', target_table);
|
|
EXECUTE format(
|
|
'CREATE POLICY %I ON %I USING (tenant_id = current_setting(''app.tenant_id'', true))'
|
|
|| ' WITH CHECK (tenant_id = current_setting(''app.tenant_id'', true))',
|
|
target_table || '_tenant_isolation', target_table
|
|
);
|
|
END LOOP;
|
|
END
|
|
$$;
|
|
|
|
ALTER TABLE staff_payroll_ledger ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE staff_payroll_ledger FORCE ROW LEVEL SECURITY;
|
|
CREATE POLICY staff_payroll_ledger_tenant_select ON staff_payroll_ledger
|
|
FOR SELECT USING (tenant_id = current_setting('app.tenant_id', true));
|
|
CREATE POLICY staff_payroll_ledger_tenant_insert ON staff_payroll_ledger
|
|
FOR INSERT WITH CHECK (
|
|
tenant_id = current_setting('app.tenant_id', true)
|
|
AND created_by = current_setting('app.user_id', true)
|
|
);
|
|
|
|
GRANT SELECT, INSERT, UPDATE, DELETE ON
|
|
staff_org_units, staff_org_unit_terms, staff_metric_definitions, staff_daily_metrics,
|
|
staff_work_items, staff_pay_rules, staff_pay_assignments, staff_work_sessions,
|
|
staff_payroll_runs, staff_payroll_lines, staff_payouts
|
|
TO krona_staff_app;
|
|
GRANT SELECT, INSERT ON staff_payroll_ledger TO krona_staff_app;
|
|
REVOKE UPDATE, DELETE, TRUNCATE ON staff_payroll_ledger FROM krona_staff_app;
|