28 lines
4.9 KiB
PL/PgSQL
28 lines
4.9 KiB
PL/PgSQL
CREATE TABLE fr_events(id bigserial PRIMARY KEY,tenant_id bigint NOT NULL,company_id bigint NOT NULL,unit_id bigint NOT NULL,rule_key text NOT NULL CHECK(rule_key ~ '^[A-Z0-9_-]{3,80}$'),revision bigint NOT NULL CHECK(revision>0),rule_version integer NOT NULL CHECK(rule_version>0),previous_id bigint UNIQUE REFERENCES fr_events(id),target_id bigint REFERENCES fr_events(id),action text NOT NULL CHECK(action IN('prepare','review','withdraw')),state text NOT NULL CHECK(state IN('prepared','reviewed','refused','withdrawn')),prepared_by bigint NOT NULL REFERENCES users(id),actor_id bigint NOT NULL REFERENCES users(id),definition jsonb NOT NULL,reason text NOT NULL CHECK(length(reason)>=10),created_at timestamptz NOT NULL DEFAULT now(),UNIQUE(tenant_id,company_id,unit_id,rule_key,revision),FOREIGN KEY(tenant_id,company_id,unit_id) REFERENCES units(tenant_id,company_id,id));
|
|
CREATE TABLE fr_calculations(id bigserial PRIMARY KEY,tenant_id bigint NOT NULL,company_id bigint NOT NULL,unit_id bigint NOT NULL,actor_id bigint NOT NULL REFERENCES users(id),rule_event_id bigint REFERENCES fr_events(id),input jsonb NOT NULL,result jsonb NOT NULL,dependencies jsonb NOT NULL,reason text NOT NULL CHECK(length(reason)>=10),created_at timestamptz NOT NULL DEFAULT now(),FOREIGN KEY(tenant_id,company_id,unit_id) REFERENCES units(tenant_id,company_id,id));
|
|
CREATE TABLE fr_commands(id bigserial PRIMARY KEY,tenant_id bigint NOT NULL,company_id bigint NOT NULL,unit_id bigint NOT NULL,actor_id bigint NOT NULL REFERENCES users(id),command_key uuid NOT NULL,request_hash char(64) NOT NULL,result jsonb NOT NULL,created_at timestamptz NOT NULL DEFAULT now(),UNIQUE(tenant_id,company_id,unit_id,actor_id,command_key));
|
|
CREATE FUNCTION sgf_fr_guard() RETURNS trigger LANGUAGE plpgsql AS $$
|
|
DECLARE lastrow fr_events;t fr_events;v bigint;required_right text;
|
|
BEGIN
|
|
PERFORM sgf_wo_environment();PERFORM 1 FROM companies WHERE tenant_id=NEW.tenant_id AND id=NEW.company_id FOR UPDATE;
|
|
IF TG_TABLE_NAME='fr_calculations' THEN required_right:='fiscal_rules.prepare'; ELSE required_right:='fiscal_rules.'||CASE NEW.action WHEN 'prepare' THEN 'prepare' ELSE 'review' END; END IF;
|
|
IF NOT EXISTS(SELECT 1 FROM company_memberships WHERE user_id=NEW.actor_id AND tenant_id=NEW.tenant_id AND company_id=NEW.company_id) OR NOT EXISTS(SELECT 1 FROM permission_grants WHERE user_id=NEW.actor_id AND tenant_id=NEW.tenant_id AND company_id=NEW.company_id AND unit_id=NEW.unit_id AND permission=required_right) THEN RAISE EXCEPTION 'SGF_FR_ACCESS'; END IF;
|
|
IF TG_TABLE_NAME='fr_calculations' THEN RETURN NEW; END IF;
|
|
SELECT * INTO lastrow FROM fr_events WHERE (tenant_id,company_id,unit_id,rule_key)=(NEW.tenant_id,NEW.company_id,NEW.unit_id,NEW.rule_key) ORDER BY revision DESC LIMIT 1;
|
|
IF NEW.revision<>coalesce(lastrow.revision,0)+1 OR NEW.previous_id IS DISTINCT FROM lastrow.id THEN RAISE EXCEPTION 'SGF_FR_STALE'; END IF;
|
|
IF NEW.action='prepare' THEN SELECT coalesce(max(rule_version),0)+1 INTO v FROM fr_events WHERE (tenant_id,company_id,unit_id,rule_key)=(NEW.tenant_id,NEW.company_id,NEW.unit_id,NEW.rule_key); IF NEW.rule_version<>v OR NEW.state<>'prepared' OR NEW.prepared_by<>NEW.actor_id OR NEW.target_id IS NOT NULL THEN RAISE EXCEPTION 'SGF_FR_PREPARE'; END IF;
|
|
ELSE SELECT * INTO t FROM fr_events WHERE id=NEW.target_id AND (tenant_id,company_id,unit_id,rule_key)=(NEW.tenant_id,NEW.company_id,NEW.unit_id,NEW.rule_key);
|
|
IF t.id IS NULL OR EXISTS(SELECT 1 FROM fr_events WHERE (tenant_id,company_id,unit_id,rule_key,rule_version)=(t.tenant_id,t.company_id,t.unit_id,t.rule_key,t.rule_version) AND revision>t.revision) OR NEW.rule_version<>t.rule_version OR NEW.prepared_by<>t.prepared_by OR NEW.actor_id=t.prepared_by OR NEW.definition IS DISTINCT FROM t.definition THEN RAISE EXCEPTION 'SGF_FR_REVIEW'; END IF;
|
|
IF (NEW.action='review' AND (t.state<>'prepared' OR NEW.state NOT IN('reviewed','refused'))) OR (NEW.action='withdraw' AND (t.state<>'reviewed' OR NEW.state<>'withdrawn')) THEN RAISE EXCEPTION 'SGF_FR_STATE'; END IF; END IF;
|
|
RETURN NEW;
|
|
END; $$;
|
|
CREATE TRIGGER fr_event_guard BEFORE INSERT ON fr_events FOR EACH ROW EXECUTE FUNCTION sgf_fr_guard();
|
|
CREATE TRIGGER fr_calculation_guard BEFORE INSERT ON fr_calculations FOR EACH ROW EXECUTE FUNCTION sgf_fr_guard();
|
|
CREATE TRIGGER fr_events_immutable BEFORE UPDATE OR DELETE ON fr_events FOR EACH ROW EXECUTE FUNCTION sgf_append_only_audit();
|
|
CREATE TRIGGER fr_calculations_immutable BEFORE UPDATE OR DELETE ON fr_calculations FOR EACH ROW EXECUTE FUNCTION sgf_append_only_audit();
|
|
CREATE TRIGGER fr_commands_immutable BEFORE UPDATE OR DELETE ON fr_commands FOR EACH ROW EXECUTE FUNCTION sgf_append_only_audit();
|
|
CREATE TRIGGER fr_commands_test BEFORE INSERT ON fr_commands FOR EACH ROW EXECUTE FUNCTION sgf_wo_test_only();
|
|
REVOKE ALL ON fr_events,fr_calculations,fr_commands FROM PUBLIC;
|
|
REVOKE ALL ON SEQUENCE fr_events_id_seq,fr_calculations_id_seq,fr_commands_id_seq FROM PUBLIC;
|
|
REVOKE ALL ON FUNCTION sgf_fr_guard() FROM PUBLIC;
|