Skip to content

Open-source manufacturing ERP, MES and QMS. Quoting, MRP, inventory, shop floor, quality and lot/serial traceability on one Postgres schema, with a REST API and MCP server. Self-host or use Carbon Cloud.

1.8 KB
Raw Blame
-- invariant: a published workflow's workflowTriggerEvent rows equal the event ids
-- on its published version's trigger nodes; returns violating rows (none = healthy)
WITH published AS (
    SELECT w."id" AS "workflowId",
           w."companyId",
           w."publishedVersionId",
           v."nodes"
    FROM "workflow" w
    JOIN "workflowVersion" v
        ON v."id" = w."publishedVersionId"
       AND v."companyId" = w."companyId"
    WHERE w."publishedVersionId" IS NOT NULL
),
declared AS (
    SELECT a."workflowId",
           a."companyId",
           a."publishedVersionId",
           e AS "eventId"
    FROM published a,
         LATERAL jsonb_array_elements(a."nodes") n,
         LATERAL jsonb_array_elements_text(n -> 'data' -> 'events') e
    WHERE n ->> 'type' = 'trigger'
)
SELECT d."workflowId", d."companyId", d."eventId",
       'trigger node lists this event but no dispatch row exists' AS "violation"
FROM declared d
LEFT JOIN "workflowTriggerEvent" t
    ON t."workflowId" = d."workflowId"
   AND t."companyId" = d."companyId"
   AND t."eventId" = d."eventId"
WHERE t."eventId" IS NULL

UNION ALL

SELECT t."workflowId", t."companyId", t."eventId",
       'dispatch row with no matching trigger node on a published version' AS "violation"
FROM "workflowTriggerEvent" t
LEFT JOIN declared d
    ON d."workflowId" = t."workflowId"
   AND d."companyId" = t."companyId"
   AND d."eventId" = t."eventId"
WHERE d."eventId" IS NULL

UNION ALL

SELECT t."workflowId", t."companyId", t."eventId",
       'dispatch row points at a version that is no longer the published one' AS "violation"
FROM "workflowTriggerEvent" t
JOIN declared d
    ON d."workflowId" = t."workflowId"
   AND d."companyId" = t."companyId"
   AND d."eventId" = t."eventId"
WHERE t."workflowVersionId" <> d."publishedVersionId";