-- Install in a NEW private PostgreSQL database as its administrator. -- Never install this download in the Flowgrammer website database. BEGIN; CREATE SCHEMA fg_finance; CREATE ROLE fg_finance_runner NOLOGIN; CREATE ROLE fg_finance_reviewer NOLOGIN; REVOKE ALL ON SCHEMA fg_finance FROM PUBLIC; GRANT USAGE ON SCHEMA fg_finance TO fg_finance_runner, fg_finance_reviewer; CREATE TABLE fg_finance.inputs ( id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, client_id text NOT NULL, kind text NOT NULL, source_id text NOT NULL, revision text NOT NULL, payload jsonb NOT NULL, created_at timestamptz NOT NULL DEFAULT now(), UNIQUE (client_id, kind, source_id, revision) ); CREATE TABLE fg_finance.decisions ( id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, input_id bigint NOT NULL REFERENCES fg_finance.inputs, client_id text NOT NULL, dry_run boolean NOT NULL, result jsonb NOT NULL, effect_key text, status text NOT NULL CHECK(status IN ('dry-run','pending','exception','skipped','approved','dispatching','succeeded','uncertain','rejected')), approved_by text, approved_at timestamptz, dispatch_started_at timestamptz, destination_id text, created_at timestamptz NOT NULL DEFAULT now(), UNIQUE (input_id, dry_run) ); CREATE UNIQUE INDEX decisions_effect_once ON fg_finance.decisions(client_id,effect_key) WHERE NOT dry_run AND effect_key IS NOT NULL; CREATE TABLE fg_finance.audit ( id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, decision_id bigint REFERENCES fg_finance.decisions, event text NOT NULL, actor text NOT NULL DEFAULT session_user, detail jsonb NOT NULL DEFAULT '{}', created_at timestamptz NOT NULL DEFAULT now() ); -- Functions own the queue mutations; the runner has no direct table-write or approval grants. CREATE FUNCTION fg_finance.pending(p_client text, p_kind text, p_dry boolean, p_limit int) RETURNS SETOF fg_finance.inputs LANGUAGE sql SECURITY DEFINER SET search_path = pg_catalog, fg_finance AS $$ SELECT i.* FROM fg_finance.inputs i WHERE i.client_id = p_client AND i.kind = p_kind AND NOT EXISTS (SELECT 1 FROM fg_finance.decisions d WHERE d.input_id=i.id AND d.dry_run=p_dry) ORDER BY i.id LIMIT greatest(1,least(p_limit,100)); $$; CREATE FUNCTION fg_finance.record(p_input bigint, p_client text, p_dry boolean, p_result jsonb) RETURNS bigint LANGUAGE plpgsql SECURITY DEFINER SET search_path = pg_catalog, fg_finance AS $$ DECLARE v_id bigint; v_status text; v_effect text; v_source text; v_kind text; BEGIN IF NOT EXISTS (SELECT 1 FROM fg_finance.inputs WHERE id=p_input AND client_id=p_client) THEN RAISE EXCEPTION 'Input/client mismatch'; END IF; v_status := CASE WHEN p_result->>'action'='exception' THEN 'exception' WHEN p_result->>'action'='skip' THEN 'skipped' WHEN p_dry THEN 'dry-run' ELSE 'pending' END; SELECT source_id,kind INTO v_source,v_kind FROM fg_finance.inputs WHERE id=p_input; IF v_status NOT IN ('exception','skipped') AND p_result->>'action' IN ('email','quickbooks','xero') THEN v_effect := jsonb_build_array(v_kind,v_source,coalesce(p_result->>'stage','single'))::text; END IF; -- Serialize identical effects across new revisions and concurrent preparation runs. PERFORM pg_advisory_xact_lock(hashtext(p_client), hashtext(coalesce(v_effect,p_input::text))); IF NOT p_dry AND v_effect IS NOT NULL AND EXISTS(SELECT 1 FROM fg_finance.decisions WHERE client_id=p_client AND effect_key=v_effect AND NOT dry_run) THEN v_status := 'skipped'; v_effect := NULL; p_result := jsonb_build_object('action','skip','reason','External effect already prepared for this source and stage'); END IF; INSERT INTO fg_finance.decisions(input_id,client_id,dry_run,result,status,effect_key) VALUES(p_input,p_client,p_dry,p_result,v_status,v_effect) ON CONFLICT(input_id,dry_run) DO NOTHING RETURNING id INTO v_id; IF v_id IS NOT NULL THEN INSERT INTO fg_finance.audit(decision_id,event,detail) VALUES(v_id,v_status,jsonb_build_object('input_id',p_input)); ELSE SELECT id INTO v_id FROM fg_finance.decisions WHERE input_id=p_input AND dry_run=p_dry; END IF; RETURN v_id; END $$; CREATE FUNCTION fg_finance.approve(p_id bigint, p_note text) RETURNS void LANGUAGE plpgsql SECURITY DEFINER SET search_path = pg_catalog, fg_finance AS $$ BEGIN IF length(trim(p_note)) < 10 THEN RAISE EXCEPTION 'Record review evidence in approval note'; END IF; UPDATE fg_finance.decisions SET status='approved',approved_by=session_user,approved_at=now() WHERE id=p_id AND (status='pending' OR (status='approved' AND approved_at <= now()-interval '24 hours')) AND NOT dry_run AND result->>'action' IN ('email','quickbooks','xero'); IF NOT FOUND THEN RAISE EXCEPTION 'Only live pending external actions can be approved'; END IF; INSERT INTO fg_finance.audit(decision_id,event,detail) VALUES(p_id,'approved',jsonb_build_object('note',p_note)); END $$; CREATE FUNCTION fg_finance.claim(p_client text, p_action text, p_enabled boolean) RETURNS SETOF fg_finance.decisions LANGUAGE plpgsql SECURITY DEFINER SET search_path = pg_catalog, fg_finance AS $$ DECLARE v_id bigint; BEGIN IF NOT p_enabled THEN RETURN; END IF; SELECT id INTO v_id FROM fg_finance.decisions WHERE client_id=p_client AND status='approved' AND NOT dry_run AND result->>'action'=p_action AND approved_by IS NOT NULL AND approved_at > now()-interval '24 hours' ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1; IF v_id IS NULL THEN RETURN; END IF; UPDATE fg_finance.decisions SET status='dispatching',dispatch_started_at=now() WHERE id=v_id; INSERT INTO fg_finance.audit(decision_id,event) VALUES(v_id,'dispatching'); RETURN QUERY SELECT * FROM fg_finance.decisions WHERE id=v_id; END $$; CREATE FUNCTION fg_finance.finish(p_id bigint, p_client text, p_ok boolean, p_destination text) RETURNS void LANGUAGE plpgsql SECURITY DEFINER SET search_path = pg_catalog, fg_finance AS $$ BEGIN UPDATE fg_finance.decisions SET status=CASE WHEN p_ok THEN 'succeeded' ELSE 'uncertain' END, destination_id=left(p_destination,200) WHERE id=p_id AND client_id=p_client AND status='dispatching'; IF NOT FOUND THEN RAISE EXCEPTION 'No dispatching action'; END IF; INSERT INTO fg_finance.audit(decision_id,event,detail) VALUES(p_id,CASE WHEN p_ok THEN 'succeeded' ELSE 'uncertain' END,jsonb_build_object('destination_id',left(p_destination,200))); END $$; REVOKE ALL ON ALL TABLES IN SCHEMA fg_finance FROM PUBLIC; REVOKE ALL ON ALL SEQUENCES IN SCHEMA fg_finance FROM PUBLIC; REVOKE ALL ON ALL FUNCTIONS IN SCHEMA fg_finance FROM PUBLIC; GRANT EXECUTE ON FUNCTION fg_finance.pending(text,text,boolean,int),fg_finance.record(bigint,text,boolean,jsonb),fg_finance.claim(text,text,boolean),fg_finance.finish(bigint,text,boolean,text) TO fg_finance_runner; GRANT SELECT ON ALL TABLES IN SCHEMA fg_finance TO fg_finance_reviewer; GRANT EXECUTE ON FUNCTION fg_finance.approve(bigint,text) TO fg_finance_reviewer; COMMIT;