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.
TypeScript ai-agentsbill-of-materialserpinventory-managementleanmanufacturingmanufacturing-execution-systemmcp-servermesmrpopen-source-erppostgresqlproduction-planningqmsquality-management-systemreact-routerself-hostedsupabasetraceabilitytypescript
ALTER TABLE "methodMaterial" DROP COLUMN "description";
ALTER TABLE "methodMaterial" ADD CONSTRAINT "methodMaterial_itemId_fkey" FOREIGN KEY ("itemId") REFERENCES "item" ("id") ON UPDATE CASCADE ON DELETE RESTRICT;
DROP FUNCTION get_method_tree;
CREATE OR REPLACE FUNCTION get_method_tree(uid TEXT)
RETURNS TABLE (
"methodMaterialId" TEXT,
"makeMethodId" TEXT,
"materialMakeMethodId" TEXT,
"itemId" TEXT,
"itemReadableId" TEXT,
"itemType" TEXT,
"quantity" NUMERIC,
"methodType" "methodType",
"parentMaterialId" TEXT,
"order" DOUBLE PRECISION,
"isRoot" BOOLEAN
) AS $$
WITH RECURSIVE material AS (
SELECT
"id",
"makeMethodId",
"methodType",
"materialMakeMethodId",
"itemId",
"itemType",
"quantity",
"makeMethodId" AS "parentMaterialId",
CAST(1 AS DOUBLE PRECISION) AS "order"
FROM
"methodMaterial"
WHERE
"makeMethodId" = uid
UNION
SELECT
child."id",
child."makeMethodId",
child."methodType",
child."materialMakeMethodId",
child."itemId",
child."itemType",
child."quantity",
parent."id" AS "parentMaterialId",
child."order"
FROM
"methodMaterial" child
INNER JOIN material parent ON parent."materialMakeMethodId" = child."makeMethodId"
)
SELECT
material.id as "methodMaterialId",
material."makeMethodId",
material."materialMakeMethodId",
material."itemId",
item."readableId" AS "itemReadableId",
material."itemType",
material."quantity",
material."methodType",
material."parentMaterialId",
material."order",
false AS "isRoot"
FROM material INNER JOIN item ON material."itemId" = item.id
UNION
SELECT
mm."id" AS "methodMaterialId",
NULL AS "makeMethodId",
mm.id AS "materialMakeMethodId",
mm."itemId",
i."readableId" AS "itemReadableId",
i."type"::text,
1 AS "quantity",
'Make' AS "methodType",
NULL AS "parentMaterialId",
CAST(1 AS DOUBLE PRECISION) AS "order",
true AS "isRoot"
FROM "makeMethod" mm INNER JOIN item i ON mm."itemId" = i.id
WHERE mm.id = uid
ORDER BY "order"
$$ LANGUAGE sql STABLE;