47 lines
5.3 KiB
PL/PgSQL
47 lines
5.3 KiB
PL/PgSQL
CREATE TABLE wb_proposals(id bigserial PRIMARY KEY,order_id bigint NOT NULL REFERENCES wo_orders(id),version integer NOT NULL CHECK(version>0),previous_id bigint UNIQUE REFERENCES wb_proposals(id),snapshot jsonb NOT NULL,actor_id bigint NOT NULL REFERENCES users(id),created_at timestamptz NOT NULL,UNIQUE(order_id,version));
|
|
CREATE TABLE wb_decisions(id bigserial PRIMARY KEY,proposal_id bigint UNIQUE NOT NULL REFERENCES wb_proposals(id),version integer NOT NULL,decision text NOT NULL CHECK(decision IN ('approved','refused')),reason text NOT NULL CHECK(length(reason)>=3),actor_id bigint NOT NULL REFERENCES users(id),created_at timestamptz NOT NULL);
|
|
CREATE TABLE wb_generations(id bigserial PRIMARY KEY,order_id bigint UNIQUE NOT NULL REFERENCES wo_orders(id),proposal_id bigint UNIQUE NOT NULL REFERENCES wb_proposals(id),service_draft_id bigint UNIQUE NOT NULL REFERENCES a_service_drafts(id),billing_draft_id bigint UNIQUE NOT NULL REFERENCES a_billing_drafts(id),actor_id bigint NOT NULL REFERENCES users(id),created_at timestamptz NOT NULL);
|
|
CREATE TABLE wb_claims(id bigserial PRIMARY KEY,generation_id bigint NOT NULL REFERENCES wb_generations(id),order_id bigint NOT NULL REFERENCES wo_orders(id),origin text NOT NULL,entry_id bigint UNIQUE NOT NULL REFERENCES wo_entries(id),entry_version integer NOT NULL,UNIQUE(order_id,origin));
|
|
CREATE TABLE wb_commands(id bigserial PRIMARY KEY,order_id bigint NOT NULL REFERENCES wo_orders(id),actor_id bigint NOT NULL REFERENCES users(id),action text NOT NULL CHECK(action IN ('prepare','review','generate')),command_key text NOT NULL,request_hash text NOT NULL,result jsonb NOT NULL,created_at timestamptz NOT NULL,UNIQUE(order_id,actor_id,action,command_key));
|
|
CREATE TABLE wb_events(id bigserial PRIMARY KEY,command_id bigint UNIQUE NOT NULL REFERENCES wb_commands(id),payload jsonb NOT NULL,created_at timestamptz NOT NULL);
|
|
CREATE TABLE wb_outbox(event_id bigint PRIMARY KEY REFERENCES wb_events(id),state text NOT NULL CHECK(state='captured_no_stock_or_accounting'));
|
|
CREATE OR REPLACE FUNCTION sgf_wb_integrity() RETURNS trigger LANGUAGE plpgsql AS $$
|
|
DECLARE p wb_proposals; n integer; prev bigint; e wo_entries;
|
|
BEGIN
|
|
PERFORM sgf_ic_environment();
|
|
IF TG_TABLE_NAME='wb_decisions' THEN
|
|
SELECT * INTO STRICT p FROM wb_proposals WHERE id=NEW.proposal_id;
|
|
IF p.version<>NEW.version OR p.actor_id=NEW.actor_id OR EXISTS(SELECT 1 FROM wb_proposals WHERE previous_id=p.id) THEN RAISE EXCEPTION 'SGF_WB_REVIEW_IDENTITY_VERSION'; END IF;
|
|
ELSIF TG_TABLE_NAME='wb_proposals' THEN
|
|
PERFORM 1 FROM companies WHERE id=(SELECT company_id FROM wo_orders WHERE id=NEW.order_id) FOR UPDATE;
|
|
SELECT coalesce(max(version),0) INTO n FROM wb_proposals WHERE order_id=NEW.order_id;
|
|
SELECT id INTO prev FROM wb_proposals WHERE order_id=NEW.order_id AND version=n;
|
|
IF NEW.version<>n+1 OR NEW.previous_id IS DISTINCT FROM prev THEN RAISE EXCEPTION 'SGF_WB_VERSION'; END IF;
|
|
ELSIF TG_TABLE_NAME='wb_generations' THEN
|
|
SELECT * INTO STRICT p FROM wb_proposals WHERE id=NEW.proposal_id AND order_id=NEW.order_id;
|
|
IF EXISTS(SELECT 1 FROM wb_proposals WHERE previous_id=p.id) OR NOT EXISTS(SELECT 1 FROM wb_decisions WHERE proposal_id=p.id AND version=p.version AND decision='approved') THEN RAISE EXCEPTION 'SGF_WB_APPROVED_CURRENT'; END IF;
|
|
ELSIF TG_TABLE_NAME='wb_claims' THEN
|
|
SELECT * INTO STRICT e FROM wo_entries WHERE id=NEW.entry_id AND order_id=NEW.order_id AND version=NEW.entry_version AND origin=NEW.origin;
|
|
IF NOT EXISTS(SELECT 1 FROM wb_generations WHERE id=NEW.generation_id AND order_id=NEW.order_id) OR e.outside_authority OR EXISTS(SELECT 1 FROM wo_entries WHERE previous_id=e.id) OR NOT EXISTS(SELECT 1 FROM wo_decisions WHERE entry_id=e.id AND entry_version=e.version AND decision='verified') THEN RAISE EXCEPTION 'SGF_WB_SOURCE'; END IF;
|
|
END IF;
|
|
RETURN NEW;
|
|
END; $$;
|
|
CREATE TRIGGER wb_proposal_integrity BEFORE INSERT ON wb_proposals FOR EACH ROW EXECUTE FUNCTION sgf_wb_integrity();
|
|
CREATE TRIGGER wb_review_integrity BEFORE INSERT ON wb_decisions FOR EACH ROW EXECUTE FUNCTION sgf_wb_integrity();
|
|
CREATE TRIGGER wb_generation_integrity BEFORE INSERT ON wb_generations FOR EACH ROW EXECUTE FUNCTION sgf_wb_integrity();
|
|
CREATE TRIGGER wb_claim_integrity BEFORE INSERT ON wb_claims FOR EACH ROW EXECUTE FUNCTION sgf_wb_integrity();
|
|
CREATE OR REPLACE FUNCTION sgf_wb_draft_locked() RETURNS trigger LANGUAGE plpgsql AS $$
|
|
BEGIN
|
|
IF (TG_TABLE_NAME='a_service_drafts' AND EXISTS(SELECT 1 FROM wb_generations WHERE service_draft_id=OLD.id)) OR (TG_TABLE_NAME='a_billing_drafts' AND EXISTS(SELECT 1 FROM wb_generations WHERE billing_draft_id=OLD.id)) THEN RAISE EXCEPTION 'SGF_WB_LINKED_DRAFT_REQUIRES_REVIEWED_CORRECTION'; END IF;
|
|
IF TG_OP='UPDATE' THEN RETURN NEW; END IF;
|
|
RETURN OLD;
|
|
END; $$;
|
|
CREATE TRIGGER wb_service_locked BEFORE UPDATE OR DELETE ON a_service_drafts FOR EACH ROW EXECUTE FUNCTION sgf_wb_draft_locked();
|
|
CREATE TRIGGER wb_billing_locked BEFORE UPDATE OR DELETE ON a_billing_drafts FOR EACH ROW EXECUTE FUNCTION sgf_wb_draft_locked();
|
|
CREATE OR REPLACE FUNCTION sgf_wb_no_second_billing() RETURNS trigger LANGUAGE plpgsql AS $$
|
|
BEGIN
|
|
IF EXISTS(SELECT 1 FROM wb_generations WHERE service_draft_id=NEW.source_id) THEN RAISE EXCEPTION 'SGF_WB_SOURCE_ALREADY_CONVERTED'; END IF;
|
|
RETURN NEW;
|
|
END; $$;
|
|
CREATE TRIGGER wb_no_second_billing BEFORE INSERT ON a_billing_drafts FOR EACH ROW EXECUTE FUNCTION sgf_wb_no_second_billing();
|