198 lines
9.3 KiB
SQL
198 lines
9.3 KiB
SQL
CREATE TABLE IF NOT EXISTS finance_project (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
project_code VARCHAR(50) UNIQUE,
|
|
name VARCHAR(180) NOT NULL,
|
|
description TEXT,
|
|
status VARCHAR(30) NOT NULL DEFAULT 'aktiv' CHECK (status IN ('aktiv','pausiert','abgeschlossen','archiviert')),
|
|
calculation_mode VARCHAR(30) NOT NULL DEFAULT 'sum_only' CHECK (calculation_mode IN ('sum_only','project_budget','annual_budget')),
|
|
committee_id BIGINT REFERENCES committee(id) ON DELETE SET NULL,
|
|
created_by BIGINT REFERENCES app_user(id),
|
|
updated_by BIGINT REFERENCES app_user(id),
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
deleted_at TIMESTAMPTZ
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS finance_project_budget (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
project_id BIGINT NOT NULL REFERENCES finance_project(id) ON DELETE CASCADE,
|
|
budget_type VARCHAR(20) NOT NULL CHECK (budget_type IN ('project','annual')),
|
|
budget_year INTEGER,
|
|
amount NUMERIC(14,2) NOT NULL DEFAULT 0 CHECK (amount >= 0),
|
|
currency CHAR(3) NOT NULL DEFAULT 'EUR',
|
|
created_by BIGINT REFERENCES app_user(id),
|
|
updated_by BIGINT REFERENCES app_user(id),
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
CONSTRAINT finance_project_budget_year_check CHECK (
|
|
(budget_type = 'project' AND budget_year IS NULL)
|
|
OR
|
|
(budget_type = 'annual' AND budget_year IS NOT NULL)
|
|
)
|
|
);
|
|
|
|
CREATE UNIQUE INDEX IF NOT EXISTS ux_finance_project_budget_project
|
|
ON finance_project_budget(project_id)
|
|
WHERE budget_type = 'project';
|
|
|
|
CREATE UNIQUE INDEX IF NOT EXISTS ux_finance_project_budget_annual
|
|
ON finance_project_budget(project_id, budget_year)
|
|
WHERE budget_type = 'annual';
|
|
|
|
CREATE TABLE IF NOT EXISTS finance_expense (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
project_id BIGINT NOT NULL REFERENCES finance_project(id) ON DELETE CASCADE,
|
|
expense_date DATE NOT NULL DEFAULT CURRENT_DATE,
|
|
budget_year INTEGER NOT NULL DEFAULT EXTRACT(YEAR FROM CURRENT_DATE)::INTEGER,
|
|
title VARCHAR(255) NOT NULL,
|
|
vendor VARCHAR(180),
|
|
cost_category VARCHAR(120),
|
|
net_amount NUMERIC(14,2) NOT NULL CHECK (net_amount >= 0),
|
|
tax_rate_percent NUMERIC(6,3) NOT NULL DEFAULT 19 CHECK (tax_rate_percent >= 0),
|
|
tax_amount NUMERIC(14,2) NOT NULL DEFAULT 0 CHECK (tax_amount >= 0),
|
|
gross_amount NUMERIC(14,2) NOT NULL DEFAULT 0 CHECK (gross_amount >= 0),
|
|
currency CHAR(3) NOT NULL DEFAULT 'EUR',
|
|
payment_status VARCHAR(30) NOT NULL DEFAULT 'offen' CHECK (payment_status IN ('geplant','offen','bezahlt','storniert')),
|
|
notes TEXT,
|
|
created_by BIGINT REFERENCES app_user(id),
|
|
updated_by BIGINT REFERENCES app_user(id),
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
deleted_at TIMESTAMPTZ
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_finance_expense_project ON finance_expense(project_id);
|
|
CREATE INDEX IF NOT EXISTS idx_finance_expense_budget_year ON finance_expense(budget_year);
|
|
CREATE INDEX IF NOT EXISTS idx_finance_expense_date ON finance_expense(expense_date);
|
|
|
|
CREATE TABLE IF NOT EXISTS finance_expense_document (
|
|
expense_id BIGINT NOT NULL REFERENCES finance_expense(id) ON DELETE CASCADE,
|
|
document_id BIGINT NOT NULL REFERENCES document_item(id) ON DELETE CASCADE,
|
|
created_by BIGINT REFERENCES app_user(id),
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
PRIMARY KEY (expense_id, document_id)
|
|
);
|
|
|
|
DO $$
|
|
BEGIN
|
|
IF NOT EXISTS (SELECT 1 FROM pg_trigger WHERE tgname = 'trg_finance_project_updated_at') THEN
|
|
CREATE TRIGGER trg_finance_project_updated_at BEFORE UPDATE ON finance_project FOR EACH ROW EXECUTE FUNCTION set_updated_at();
|
|
END IF;
|
|
|
|
IF NOT EXISTS (SELECT 1 FROM pg_trigger WHERE tgname = 'trg_finance_project_budget_updated_at') THEN
|
|
CREATE TRIGGER trg_finance_project_budget_updated_at BEFORE UPDATE ON finance_project_budget FOR EACH ROW EXECUTE FUNCTION set_updated_at();
|
|
END IF;
|
|
|
|
IF NOT EXISTS (SELECT 1 FROM pg_trigger WHERE tgname = 'trg_finance_expense_updated_at') THEN
|
|
CREATE TRIGGER trg_finance_expense_updated_at BEFORE UPDATE ON finance_expense FOR EACH ROW EXECUTE FUNCTION set_updated_at();
|
|
END IF;
|
|
END $$;
|
|
|
|
INSERT INTO app_page(page_key, path, label, nav_group, sort_order, description)
|
|
VALUES ('finance', '/finance', 'Finanzen', 'work', 65, 'Kostenüberwachung, Budgets, Jahresbudgets und Belege')
|
|
ON CONFLICT (page_key) DO UPDATE
|
|
SET path = EXCLUDED.path,
|
|
label = EXCLUDED.label,
|
|
nav_group = EXCLUDED.nav_group,
|
|
sort_order = EXCLUDED.sort_order,
|
|
description = EXCLUDED.description;
|
|
|
|
INSERT INTO app_permission(permission_key, label, category, description)
|
|
VALUES
|
|
('finance.read', 'Finanzen lesen', 'Finanzen', 'Finanzprojekte, Budgets, Ausgaben und Belege anzeigen'),
|
|
('finance.write', 'Finanzen bearbeiten', 'Finanzen', 'Finanzprojekte, Budgets, Ausgaben und Belege anlegen und ändern'),
|
|
('finance.delete', 'Finanzdaten löschen', 'Finanzen', 'Finanzprojekte und Ausgaben archivieren/löschen'),
|
|
('finance.budget', 'Budgets verwalten', 'Finanzen', 'Projektbudgets und Jahresbudgets pflegen')
|
|
ON CONFLICT (permission_key) DO UPDATE
|
|
SET label = EXCLUDED.label,
|
|
category = EXCLUDED.category,
|
|
description = EXCLUDED.description;
|
|
|
|
INSERT INTO app_role_page(role_profile_id, page_key, can_view)
|
|
SELECT rp.id, 'finance', TRUE
|
|
FROM app_role_profile rp
|
|
WHERE rp.code IN ('admin','chair')
|
|
ON CONFLICT (role_profile_id, page_key) DO UPDATE SET can_view = TRUE;
|
|
|
|
INSERT INTO app_role_permission(role_profile_id, permission_key, granted)
|
|
SELECT rp.id, perm.permission_key, TRUE
|
|
FROM app_role_profile rp
|
|
CROSS JOIN (VALUES ('finance.read'), ('finance.write'), ('finance.delete'), ('finance.budget')) AS perm(permission_key)
|
|
WHERE rp.code IN ('admin','chair')
|
|
ON CONFLICT (role_profile_id, permission_key) DO UPDATE SET granted = TRUE;
|
|
|
|
ALTER TABLE finance_project ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE finance_project_budget ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE finance_expense ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE finance_expense_document ENABLE ROW LEVEL SECURITY;
|
|
|
|
DROP POLICY IF EXISTS finance_project_select ON finance_project;
|
|
DROP POLICY IF EXISTS finance_project_modify ON finance_project;
|
|
CREATE POLICY finance_project_select ON finance_project FOR SELECT USING (
|
|
deleted_at IS NULL AND app_has_permission('finance.read')
|
|
);
|
|
CREATE POLICY finance_project_modify ON finance_project FOR ALL USING (
|
|
app_has_permission('finance.write')
|
|
) WITH CHECK (app_has_permission('finance.write'));
|
|
|
|
DROP POLICY IF EXISTS finance_project_budget_select ON finance_project_budget;
|
|
DROP POLICY IF EXISTS finance_project_budget_modify ON finance_project_budget;
|
|
CREATE POLICY finance_project_budget_select ON finance_project_budget FOR SELECT USING (
|
|
app_has_permission('finance.read')
|
|
);
|
|
CREATE POLICY finance_project_budget_modify ON finance_project_budget FOR ALL USING (
|
|
app_has_permission('finance.budget') OR app_has_permission('finance.write')
|
|
) WITH CHECK (app_has_permission('finance.budget') OR app_has_permission('finance.write'));
|
|
|
|
DROP POLICY IF EXISTS finance_expense_select ON finance_expense;
|
|
DROP POLICY IF EXISTS finance_expense_modify ON finance_expense;
|
|
CREATE POLICY finance_expense_select ON finance_expense FOR SELECT USING (
|
|
deleted_at IS NULL AND app_has_permission('finance.read')
|
|
);
|
|
CREATE POLICY finance_expense_modify ON finance_expense FOR ALL USING (
|
|
app_has_permission('finance.write')
|
|
) WITH CHECK (app_has_permission('finance.write'));
|
|
|
|
DROP POLICY IF EXISTS finance_expense_document_select ON finance_expense_document;
|
|
DROP POLICY IF EXISTS finance_expense_document_modify ON finance_expense_document;
|
|
CREATE POLICY finance_expense_document_select ON finance_expense_document FOR SELECT USING (
|
|
app_has_permission('finance.read')
|
|
);
|
|
CREATE POLICY finance_expense_document_modify ON finance_expense_document FOR ALL USING (
|
|
app_has_permission('finance.write')
|
|
) WITH CHECK (app_has_permission('finance.write'));
|
|
|
|
DROP POLICY IF EXISTS document_select_policy ON document_item;
|
|
DROP POLICY IF EXISTS document_modify_policy ON document_item;
|
|
CREATE POLICY document_select_policy ON document_item FOR SELECT USING (
|
|
app_can_read_document(id)
|
|
OR EXISTS (
|
|
SELECT 1
|
|
FROM finance_expense_document fed
|
|
JOIN finance_expense fe ON fe.id = fed.expense_id AND fe.deleted_at IS NULL
|
|
WHERE fed.document_id = document_item.id
|
|
AND app_has_permission('finance.read')
|
|
)
|
|
);
|
|
CREATE POLICY document_modify_policy ON document_item FOR ALL USING (
|
|
app_can_write_document(id)
|
|
OR (document_item.document_type = 'finance_receipt' AND document_item.uploaded_by = app_user_id() AND app_has_permission('finance.write'))
|
|
OR EXISTS (
|
|
SELECT 1
|
|
FROM finance_expense_document fed
|
|
JOIN finance_expense fe ON fe.id = fed.expense_id AND fe.deleted_at IS NULL
|
|
WHERE fed.document_id = document_item.id
|
|
AND app_has_permission('finance.write')
|
|
)
|
|
) WITH CHECK (
|
|
app_role() IN ('admin','chair')
|
|
OR app_has_permission('documents.write.all')
|
|
OR (app_has_permission('documents.write') AND uploaded_by = app_user_id())
|
|
OR (case_id IS NOT NULL AND app_can_write_case(case_id) AND app_has_permission('documents.write'))
|
|
OR (document_item.document_type = 'finance_receipt' AND document_item.uploaded_by = app_user_id() AND app_has_permission('finance.write'))
|
|
OR app_has_permission('finance.write')
|
|
);
|
|
|
|
GRANT SELECT, INSERT, UPDATE, DELETE ON finance_project, finance_project_budget, finance_expense, finance_expense_document TO br_app;
|
|
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO br_app;
|