nala
21a91f2aff
Record explicit workshop acceptance and preserve subsequent synthetic work without accepting receivables. Keep 080000/070000 unapplied, real workshop grants pending and operational issuance blocks intact. Exclude local credentials and real-data preservation reports.
163 lines
14 KiB
PL/PgSQL
163 lines
14 KiB
PL/PgSQL
CREATE TABLE wo_equipment (
|
|
id bigserial PRIMARY KEY, tenant_id bigint NOT NULL, company_id bigint NOT NULL, client_id bigint NOT NULL REFERENCES a_clients(id),
|
|
identifier text NOT NULL, description text NOT NULL, serial text, actor_id bigint NOT NULL REFERENCES users(id), created_at timestamptz NOT NULL,
|
|
UNIQUE(company_id,identifier), FOREIGN KEY(tenant_id,company_id) REFERENCES companies(tenant_id,id)
|
|
);
|
|
CREATE TABLE wo_orders (
|
|
id bigserial PRIMARY KEY, tenant_id bigint NOT NULL, company_id bigint NOT NULL, unit_id bigint NOT NULL,
|
|
client_id bigint NOT NULL REFERENCES a_clients(id), equipment_id bigint NOT NULL REFERENCES wo_equipment(id),
|
|
reference text NOT NULL, request text NOT NULL, location text NOT NULL, evidence text NOT NULL,
|
|
actor_id bigint NOT NULL REFERENCES users(id), created_at timestamptz NOT NULL,
|
|
UNIQUE(company_id,reference), FOREIGN KEY(tenant_id,company_id,unit_id) REFERENCES units(tenant_id,company_id,id)
|
|
);
|
|
CREATE TABLE wo_entries (
|
|
id bigserial PRIMARY KEY, order_id bigint NOT NULL REFERENCES wo_orders(id),
|
|
kind text NOT NULL CHECK(kind IN ('assignment','task','authorization','start','time','material')),
|
|
origin text NOT NULL, version integer NOT NULL CHECK(version>0), previous_id bigint UNIQUE REFERENCES wo_entries(id),
|
|
task_id bigint REFERENCES wo_entries(id), authorization_id bigint REFERENCES wo_entries(id), start_id bigint REFERENCES wo_entries(id),
|
|
technician_id bigint REFERENCES users(id), starts_at timestamptz, ends_at timestamptz, minutes bigint,
|
|
quantity numeric CHECK(quantity>0 AND quantity<10000000000000000 AND quantity=trunc(quantity,8)),
|
|
outside_authority boolean NOT NULL DEFAULT false, revision integer NOT NULL CHECK(revision>=0), data jsonb NOT NULL,
|
|
actor_id bigint NOT NULL REFERENCES users(id), created_at timestamptz NOT NULL,
|
|
UNIQUE(order_id,kind,origin,version)
|
|
);
|
|
CREATE UNIQUE INDEX wo_start_completion ON wo_entries(start_id) WHERE start_id IS NOT NULL;
|
|
CREATE INDEX wo_order_entries ON wo_entries(order_id,id);
|
|
CREATE TABLE wo_decisions (
|
|
id bigserial PRIMARY KEY, entry_id bigint NOT NULL UNIQUE REFERENCES wo_entries(id),
|
|
entry_version integer NOT NULL, revision integer NOT NULL, decision text NOT NULL CHECK(decision IN ('verified','refused','acknowledged')),
|
|
reason text NOT NULL, actor_id bigint NOT NULL REFERENCES users(id), created_at timestamptz NOT NULL
|
|
);
|
|
CREATE TABLE wo_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), action text NOT NULL, command_key varchar(100) NOT NULL, request_hash varchar(64) NOT NULL,
|
|
order_id bigint NOT NULL REFERENCES wo_orders(id), result jsonb NOT NULL, created_at timestamptz NOT NULL,
|
|
UNIQUE(company_id,unit_id,actor_id,action,command_key), FOREIGN KEY(tenant_id,company_id,unit_id) REFERENCES units(tenant_id,company_id,id)
|
|
);
|
|
CREATE TABLE wo_events (
|
|
id bigserial PRIMARY KEY, command_id bigint NOT NULL UNIQUE REFERENCES wo_commands(id), type text NOT NULL,
|
|
schema_version integer NOT NULL CHECK(schema_version=1), payload jsonb NOT NULL, created_at timestamptz NOT NULL
|
|
);
|
|
CREATE TABLE wo_outbox (event_id bigint PRIMARY KEY REFERENCES wo_events(id), state text NOT NULL CHECK(state='captured_no_cost_stock_or_billing'));
|
|
|
|
CREATE OR REPLACE FUNCTION sgf_wo_environment() RETURNS void LANGUAGE plpgsql AS $$
|
|
BEGIN
|
|
IF current_database() IS DISTINCT FROM 'sgf_tempo_test' OR inet_server_port() IS DISTINCT FROM 55432 THEN RAISE EXCEPTION 'SGF_WO_TEST_DATABASE_ONLY'; END IF;
|
|
END; $$;
|
|
CREATE OR REPLACE FUNCTION sgf_wo_test_only() RETURNS trigger LANGUAGE plpgsql AS $$
|
|
BEGIN PERFORM sgf_wo_environment(); RETURN NEW; END; $$;
|
|
CREATE OR REPLACE FUNCTION sgf_wo_revision(oid bigint) RETURNS integer LANGUAGE sql VOLATILE AS $$
|
|
SELECT (SELECT count(*) FROM wo_entries WHERE order_id=oid)::integer+(SELECT count(*) FROM wo_decisions d JOIN wo_entries e ON e.id=d.entry_id WHERE e.order_id=oid)::integer;
|
|
$$;
|
|
CREATE OR REPLACE FUNCTION sgf_wo_parent_guard() RETURNS trigger LANGUAGE plpgsql AS $$
|
|
DECLARE eq wo_equipment;
|
|
BEGIN
|
|
PERFORM sgf_wo_environment();
|
|
PERFORM 1 FROM companies WHERE id=NEW.company_id FOR UPDATE;
|
|
IF NOT EXISTS(SELECT 1 FROM a_clients WHERE id=NEW.client_id AND tenant_id=NEW.tenant_id AND company_id=NEW.company_id) THEN RAISE EXCEPTION 'SGF_WO_CLIENT_SCOPE'; END IF;
|
|
IF TG_TABLE_NAME='wo_orders' THEN
|
|
SELECT * INTO STRICT eq FROM wo_equipment WHERE id=NEW.equipment_id;
|
|
IF ROW(eq.tenant_id,eq.company_id,eq.client_id) IS DISTINCT FROM ROW(NEW.tenant_id,NEW.company_id,NEW.client_id) THEN RAISE EXCEPTION 'SGF_WO_EQUIPMENT_SCOPE'; END IF;
|
|
END IF;
|
|
RETURN NEW;
|
|
EXCEPTION WHEN NO_DATA_FOUND THEN RAISE EXCEPTION 'SGF_WO_INVALID_REFERENCE';
|
|
END; $$;
|
|
CREATE TRIGGER wo_equipment_scope BEFORE INSERT ON wo_equipment FOR EACH ROW EXECUTE FUNCTION sgf_wo_parent_guard();
|
|
CREATE TRIGGER wo_order_scope BEFORE INSERT ON wo_orders FOR EACH ROW EXECUTE FUNCTION sgf_wo_parent_guard();
|
|
|
|
CREATE OR REPLACE FUNCTION sgf_wo_entry_guard() RETURNS trigger LANGUAGE plpgsql AS $$
|
|
DECLARE o wo_orders; p wo_entries; t wo_entries; au wo_entries; st wo_entries; x wo_entries; assigned bigint; pause jsonb; last_end timestamptz; pause_seconds numeric:=0; duration numeric; item text; used numeric; provided numeric; event_kind text;
|
|
BEGIN
|
|
PERFORM sgf_wo_environment();
|
|
SELECT * INTO STRICT o FROM wo_orders WHERE id=NEW.order_id;
|
|
PERFORM 1 FROM companies WHERE id=o.company_id FOR UPDATE;
|
|
IF NEW.revision<>sgf_wo_revision(o.id) OR NEW.data->>'policy' IS DISTINCT FROM 'WORKSHOP-RESOURCE-RECORD-v1' THEN RAISE EXCEPTION 'SGF_WO_STALE_REVISION'; END IF;
|
|
IF NEW.previous_id IS NOT NULL THEN
|
|
SELECT * INTO STRICT p FROM wo_entries WHERE id=NEW.previous_id;
|
|
IF ROW(p.order_id,p.kind,p.origin) IS DISTINCT FROM ROW(NEW.order_id,NEW.kind,NEW.origin) OR NEW.version<>p.version+1
|
|
OR EXISTS(SELECT 1 FROM wo_entries WHERE previous_id=p.id) OR coalesce(NEW.data->>'correction_reason','')='' THEN RAISE EXCEPTION 'SGF_WO_VERSION_CHAIN'; END IF;
|
|
IF NEW.kind IN ('task','time','material') AND p.actor_id<>NEW.actor_id THEN RAISE EXCEPTION 'SGF_WO_ORIGIN_AUTHOR'; END IF;
|
|
ELSE
|
|
IF NEW.version<>1 THEN RAISE EXCEPTION 'SGF_WO_VERSION'; END IF;
|
|
END IF;
|
|
SELECT technician_id INTO assigned FROM wo_entries WHERE order_id=o.id AND kind='assignment' ORDER BY id DESC LIMIT 1;
|
|
IF NEW.kind='assignment' THEN
|
|
IF NEW.technician_id IS NULL OR NOT EXISTS(SELECT 1 FROM permission_grants WHERE user_id=NEW.technician_id AND tenant_id=o.tenant_id AND company_id=o.company_id AND unit_id=o.unit_id AND permission='workshop.record')
|
|
OR NOT EXISTS(SELECT 1 FROM company_memberships WHERE user_id=NEW.technician_id AND company_id=o.company_id AND tenant_id=o.tenant_id) THEN RAISE EXCEPTION 'SGF_WO_ASSIGNEE'; END IF;
|
|
END IF;
|
|
IF NEW.kind IN ('task','start','time','material') AND (assigned IS DISTINCT FROM NEW.actor_id OR NEW.technician_id IS DISTINCT FROM NEW.actor_id) THEN RAISE EXCEPTION 'SGF_WO_ASSIGNED_TECHNICIAN'; END IF;
|
|
IF NEW.kind='task' THEN
|
|
IF coalesce(NEW.data->>'description','')='' THEN RAISE EXCEPTION 'SGF_WO_TASK'; END IF;
|
|
IF NEW.data->>'service_id' IS NOT NULL AND NOT EXISTS(SELECT 1 FROM a_services WHERE id=(NEW.data->>'service_id')::bigint AND company_id=o.company_id AND tenant_id=o.tenant_id) THEN RAISE EXCEPTION 'SGF_WO_SERVICE_SCOPE'; END IF;
|
|
END IF;
|
|
IF NEW.kind='authorization' THEN
|
|
IF NEW.task_id IS NULL OR coalesce(NEW.data->>'authorized_by','')='' OR coalesce(NEW.data->>'capacity','')='' OR coalesce(NEW.data->>'authorized_at','')='' OR coalesce(NEW.data->>'channel','')='' OR coalesce(NEW.data->>'scope','')='' OR coalesce(NEW.data->>'evidence','')='' THEN RAISE EXCEPTION 'SGF_WO_AUTH_EVIDENCE'; END IF;
|
|
IF (NEW.data->>'authorized_at')::timestamptz>NEW.created_at THEN RAISE EXCEPTION 'SGF_WO_FUTURE_AUTH'; END IF;
|
|
END IF;
|
|
IF NEW.kind IN ('authorization','start','time','material') THEN
|
|
SELECT * INTO STRICT t FROM wo_entries WHERE id=NEW.task_id AND order_id=o.id AND kind='task';
|
|
IF EXISTS(SELECT 1 FROM wo_entries WHERE previous_id=t.id) AND NEW.kind IN ('authorization','start') THEN RAISE EXCEPTION 'SGF_WO_STALE_TASK'; END IF;
|
|
END IF;
|
|
IF NEW.kind IN ('start','time','material') THEN
|
|
NEW.outside_authority:=true;
|
|
IF NEW.authorization_id IS NOT NULL THEN
|
|
SELECT * INTO STRICT au FROM wo_entries WHERE id=NEW.authorization_id AND order_id=o.id AND kind='authorization' AND task_id=t.id;
|
|
IF au.created_at<=coalesce(NEW.starts_at,NEW.created_at) AND (au.data->>'authorized_at')::timestamptz<=coalesce(NEW.starts_at,NEW.created_at)
|
|
AND NOT EXISTS(SELECT 1 FROM wo_entries z WHERE z.previous_id=au.id AND z.created_at<=coalesce(NEW.ends_at,NEW.starts_at,NEW.created_at))
|
|
AND NOT EXISTS(SELECT 1 FROM wo_entries z WHERE z.previous_id=t.id AND z.created_at<=coalesce(NEW.ends_at,NEW.starts_at,NEW.created_at)) THEN NEW.outside_authority:=false; END IF;
|
|
END IF;
|
|
IF coalesce(NEW.data->>'evidence','')='' THEN RAISE EXCEPTION 'SGF_WO_SOURCE_EVIDENCE'; END IF;
|
|
IF NEW.previous_id IS NOT NULL AND p.outside_authority THEN NEW.outside_authority:=true; END IF;
|
|
END IF;
|
|
IF NEW.kind='start' THEN
|
|
IF NEW.outside_authority OR NEW.starts_at IS NULL OR NEW.starts_at<>NEW.created_at THEN RAISE EXCEPTION 'SGF_WO_START_NOT_AUTHORIZED'; END IF;
|
|
IF EXISTS(SELECT 1 FROM wo_entries e JOIN wo_orders w ON w.id=e.order_id WHERE w.company_id=o.company_id AND e.technician_id=NEW.technician_id AND e.kind='start' AND NOT EXISTS(SELECT 1 FROM wo_entries done WHERE done.start_id=e.id)) THEN RAISE EXCEPTION 'SGF_WO_ACTIVE_START'; END IF;
|
|
END IF;
|
|
IF NEW.kind='time' THEN
|
|
IF coalesce(NEW.data->>'time_type','') NOT IN ('work','travel') OR jsonb_typeof(NEW.data->'pauses') IS DISTINCT FROM 'array' THEN RAISE EXCEPTION 'SGF_WO_TIME_TYPE'; END IF;
|
|
IF NEW.starts_at IS NULL OR NEW.ends_at IS NULL OR NEW.ends_at<=NEW.starts_at OR NEW.ends_at>NEW.created_at THEN RAISE EXCEPTION 'SGF_WO_TIME_RANGE'; END IF;
|
|
IF NEW.start_id IS NOT NULL THEN
|
|
SELECT * INTO STRICT st FROM wo_entries WHERE id=NEW.start_id AND order_id=o.id AND kind='start' AND technician_id=NEW.technician_id;
|
|
IF st.task_id<>NEW.task_id OR st.authorization_id IS DISTINCT FROM NEW.authorization_id OR st.starts_at<>NEW.starts_at THEN RAISE EXCEPTION 'SGF_WO_START_LINK'; END IF;
|
|
ELSIF NEW.data->>'mode' IS DISTINCT FROM 'reported' THEN RAISE EXCEPTION 'SGF_WO_START_REQUIRED'; END IF;
|
|
last_end:=NEW.starts_at;
|
|
FOR pause IN SELECT value FROM jsonb_array_elements(NEW.data->'pauses') LOOP
|
|
IF (pause->>'start')::timestamptz<last_end OR (pause->>'end')::timestamptz<=(pause->>'start')::timestamptz OR (pause->>'end')::timestamptz>NEW.ends_at THEN RAISE EXCEPTION 'SGF_WO_PAUSES'; END IF;
|
|
pause_seconds:=pause_seconds+extract(epoch FROM ((pause->>'end')::timestamptz-(pause->>'start')::timestamptz)); last_end:=(pause->>'end')::timestamptz;
|
|
END LOOP;
|
|
duration:=extract(epoch FROM (NEW.ends_at-NEW.starts_at))-pause_seconds;
|
|
IF duration IS NULL OR NEW.minutes IS NULL OR duration<=0 OR mod(duration,60)<>0 OR NEW.minutes IS DISTINCT FROM (duration/60)::bigint THEN RAISE EXCEPTION 'SGF_WO_EFFECTIVE_MINUTES'; END IF;
|
|
-- Conservative occupied interval, half-open: pauses do not permit a second simultaneous task.
|
|
IF EXISTS(SELECT 1 FROM wo_entries e JOIN wo_orders w ON w.id=e.order_id WHERE e.kind='time' AND w.company_id=o.company_id AND e.technician_id=NEW.technician_id
|
|
AND NOT EXISTS(SELECT 1 FROM wo_entries z WHERE z.previous_id=e.id) AND e.id IS DISTINCT FROM NEW.previous_id
|
|
AND tstzrange(e.starts_at,e.ends_at,'[)') && tstzrange(NEW.starts_at,NEW.ends_at,'[)')) THEN RAISE EXCEPTION 'SGF_WO_TIME_OVERLAP'; END IF;
|
|
END IF;
|
|
IF NEW.kind='material' THEN
|
|
IF NEW.quantity IS NULL OR NEW.starts_at IS NULL OR NEW.starts_at>NEW.created_at OR coalesce(NEW.data->>'event','') NOT IN ('required','delivered','consumed','returned') OR coalesce(NEW.data->>'owner','') NOT IN ('company','client') OR coalesce(NEW.data->>'resource','')='' OR coalesce(NEW.data->>'unit','') NOT IN ('UN','KG','M','L') THEN RAISE EXCEPTION 'SGF_WO_MATERIAL'; END IF;
|
|
-- resource identity, owner and UOM cannot change silently for one material reference.
|
|
IF EXISTS(SELECT 1 FROM wo_entries e WHERE e.order_id=o.id AND e.kind='material' AND e.data->>'resource'=NEW.data->>'resource'
|
|
AND ROW(e.data->>'owner',e.data->>'unit') IS DISTINCT FROM ROW(NEW.data->>'owner',NEW.data->>'unit')) THEN RAISE EXCEPTION 'SGF_WO_MATERIAL_UNIT_OWNER'; END IF;
|
|
IF NEW.previous_id IS NOT NULL AND ROW(p.data->>'resource',p.data->>'owner',p.data->>'unit',p.data->>'event') IS DISTINCT FROM ROW(NEW.data->>'resource',NEW.data->>'owner',NEW.data->>'unit',NEW.data->>'event') THEN RAISE EXCEPTION 'SGF_WO_MATERIAL_ORIGIN'; END IF;
|
|
IF NEW.data->>'event'<>'consumed' AND NOT coalesce(p.outside_authority,false) THEN NEW.outside_authority:=false; END IF;
|
|
END IF;
|
|
RETURN NEW;
|
|
EXCEPTION WHEN NO_DATA_FOUND THEN RAISE EXCEPTION 'SGF_WO_INVALID_REFERENCE';
|
|
END; $$;
|
|
CREATE TRIGGER wo_entry_guard BEFORE INSERT ON wo_entries FOR EACH ROW EXECUTE FUNCTION sgf_wo_entry_guard();
|
|
|
|
CREATE OR REPLACE FUNCTION sgf_wo_decision_guard() RETURNS trigger LANGUAGE plpgsql AS $$
|
|
DECLARE e wo_entries; o wo_orders;
|
|
BEGIN
|
|
PERFORM sgf_wo_environment();
|
|
SELECT * INTO STRICT e FROM wo_entries WHERE id=NEW.entry_id;
|
|
SELECT * INTO STRICT o FROM wo_orders WHERE id=e.order_id;
|
|
PERFORM 1 FROM companies WHERE id=o.company_id FOR UPDATE;
|
|
IF e.kind NOT IN ('task','time','material') OR NEW.actor_id=e.actor_id THEN RAISE EXCEPTION 'SGF_WO_SELF_VERIFICATION'; END IF;
|
|
IF NEW.entry_version<>e.version OR EXISTS(SELECT 1 FROM wo_entries WHERE previous_id=e.id) OR NEW.revision<>sgf_wo_revision(o.id) THEN RAISE EXCEPTION 'SGF_WO_STALE_DECISION'; END IF;
|
|
IF e.outside_authority AND NEW.decision='verified' THEN RAISE EXCEPTION 'SGF_WO_OUTSIDE_AUTHORITY'; END IF;
|
|
IF NEW.decision='acknowledged' AND NOT e.outside_authority THEN RAISE EXCEPTION 'SGF_WO_ACK_ONLY_EXCEPTION'; END IF;
|
|
RETURN NEW;
|
|
EXCEPTION WHEN NO_DATA_FOUND THEN RAISE EXCEPTION 'SGF_WO_INVALID_REFERENCE';
|
|
END; $$;
|
|
CREATE TRIGGER wo_decision_guard BEFORE INSERT ON wo_decisions FOR EACH ROW EXECUTE FUNCTION sgf_wo_decision_guard();
|