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
-- Make externalId nullable in externalIntegrationMapping table
ALTER TABLE "externalIntegrationMapping" ALTER COLUMN "externalId" DROP NOT NULL;
DROP VIEW IF EXISTS "suppliers";
DROP VIEW IF EXISTS "customers";
DROP VIEW IF EXISTS "parts";
DROP VIEW IF EXISTS "materials";
DROP VIEW IF EXISTS "tools";
DROP VIEW IF EXISTS "consumables";
DROP VIEW IF EXISTS "services";
DROP VIEW IF EXISTS "salesOrders";
DELETE FROM "externalIntegrationMapping";
-- Migration: Convert all externalId JSONB columns to the externalIntegrationMapping table
-- Then drop the old columns and indexes.
--
-- Three data patterns exist:
-- 1. Object with "id" field (Xero, Linear): { "xero": { "id": "...", "provider": "xero", ... } }
-- 2. Object with "Part number" field (OnShape): { "onshapeData": { "Part number": "PRT-000587", ... } }
-- 3. Plain string value (Paperless Parts): { "paperlessPartsId": "123" }
--
-- externalId extraction:
-- - string value → use the string itself
-- - onshapeData → use "Part number" field (only for items, parts, materials, tools, consumables, services)
-- - other objects → use "id" field
--
-- metadata: store full object for objects, NULL for strings
-- ============================================================================
-- STEP 1: Migrate tables with direct companyId
-- ============================================================================
-- customer
INSERT INTO "externalIntegrationMapping" (
"entityType", "entityId", "integration", "externalId",
"metadata", "lastSyncedAt", "companyId"
)
SELECT
'customer',
t."id",
kv.key,
CASE
WHEN jsonb_typeof(kv.value) = 'string' THEN kv.value #>> '{}'
ELSE kv.value->>'id'
END,
CASE WHEN jsonb_typeof(kv.value) = 'object' THEN kv.value ELSE NULL END,
CASE WHEN jsonb_typeof(kv.value) = 'object' THEN (kv.value->>'lastSyncedAt')::timestamptz ELSE NULL END,
t."companyId"
FROM "customer" t,
LATERAL jsonb_each(t."externalId") AS kv(key, value)
WHERE t."externalId" IS NOT NULL
AND t."externalId" != '{}'::jsonb
AND (
CASE
WHEN jsonb_typeof(kv.value) = 'string' THEN kv.value #>> '{}'
ELSE kv.value->>'id'
END
) IS NOT NULL
ON CONFLICT ("integration", "externalId", "entityType", "companyId") WHERE "allowDuplicateExternalId" = false DO NOTHING;
-- supplier
INSERT INTO "externalIntegrationMapping" (
"entityType", "entityId", "integration", "externalId",
"metadata", "lastSyncedAt", "companyId"
)
SELECT
'supplier',
t."id",
kv.key,
CASE
WHEN jsonb_typeof(kv.value) = 'string' THEN kv.value #>> '{}'
ELSE kv.value->>'id'
END,
CASE WHEN jsonb_typeof(kv.value) = 'object' THEN kv.value ELSE NULL END,
CASE WHEN jsonb_typeof(kv.value) = 'object' THEN (kv.value->>'lastSyncedAt')::timestamptz ELSE NULL END,
t."companyId"
FROM "supplier" t,
LATERAL jsonb_each(t."externalId") AS kv(key, value)
WHERE t."externalId" IS NOT NULL
AND t."externalId" != '{}'::jsonb
AND (
CASE
WHEN jsonb_typeof(kv.value) = 'string' THEN kv.value #>> '{}'
ELSE kv.value->>'id'
END
) IS NOT NULL
ON CONFLICT ("integration", "externalId", "entityType", "companyId") WHERE "allowDuplicateExternalId" = false DO NOTHING;
-- contact
INSERT INTO "externalIntegrationMapping" (
"entityType", "entityId", "integration", "externalId",
"metadata", "lastSyncedAt", "companyId"
)
SELECT
'contact',
t."id",
kv.key,
CASE
WHEN jsonb_typeof(kv.value) = 'string' THEN kv.value #>> '{}'
ELSE kv.value->>'id'
END,
CASE WHEN jsonb_typeof(kv.value) = 'object' THEN kv.value ELSE NULL END,
CASE WHEN jsonb_typeof(kv.value) = 'object' THEN (kv.value->>'lastSyncedAt')::timestamptz ELSE NULL END,
t."companyId"
FROM "contact" t,
LATERAL jsonb_each(t."externalId") AS kv(key, value)
WHERE t."externalId" IS NOT NULL
AND t."externalId" != '{}'::jsonb
AND (
CASE
WHEN jsonb_typeof(kv.value) = 'string' THEN kv.value #>> '{}'
ELSE kv.value->>'id'
END
) IS NOT NULL
ON CONFLICT ("integration", "externalId", "entityType", "companyId") WHERE "allowDuplicateExternalId" = false DO NOTHING;
-- item
INSERT INTO "externalIntegrationMapping" (
"entityType", "entityId", "integration", "externalId",
"metadata", "lastSyncedAt", "companyId"
)
SELECT
'item',
t."id",
kv.key,
CASE
WHEN jsonb_typeof(kv.value) = 'string' THEN kv.value #>> '{}'
ELSE kv.value->>'id'
END,
CASE WHEN jsonb_typeof(kv.value) = 'object' THEN kv.value ELSE NULL END,
CASE WHEN jsonb_typeof(kv.value) = 'object' THEN (kv.value->>'lastSyncedAt')::timestamptz ELSE NULL END,
t."companyId"
FROM "item" t,
LATERAL jsonb_each(t."externalId") AS kv(key, value)
WHERE t."externalId" IS NOT NULL
AND t."externalId" != '{}'::jsonb
AND (
CASE
WHEN jsonb_typeof(kv.value) = 'string' THEN kv.value #>> '{}'
ELSE kv.value->>'id'
END
) IS NOT NULL
ON CONFLICT ("integration", "externalId", "entityType", "companyId") WHERE "allowDuplicateExternalId" = false DO NOTHING;
-- part
INSERT INTO "externalIntegrationMapping" (
"entityType", "entityId", "integration", "externalId",
"metadata", "lastSyncedAt", "companyId"
)
SELECT
'part',
t."id",
kv.key,
CASE
WHEN jsonb_typeof(kv.value) = 'string' THEN kv.value #>> '{}'
ELSE kv.value->>'id'
END,
CASE WHEN jsonb_typeof(kv.value) = 'object' THEN kv.value ELSE NULL END,
CASE WHEN jsonb_typeof(kv.value) = 'object' THEN (kv.value->>'lastSyncedAt')::timestamptz ELSE NULL END,
t."companyId"
FROM "part" t,
LATERAL jsonb_each(t."externalId") AS kv(key, value)
WHERE t."externalId" IS NOT NULL
AND t."externalId" != '{}'::jsonb
AND (
CASE
WHEN jsonb_typeof(kv.value) = 'string' THEN kv.value #>> '{}'
ELSE kv.value->>'id'
END
) IS NOT NULL
ON CONFLICT ("integration", "externalId", "entityType", "companyId") WHERE "allowDuplicateExternalId" = false DO NOTHING;
-- material
INSERT INTO "externalIntegrationMapping" (
"entityType", "entityId", "integration", "externalId",
"metadata", "lastSyncedAt", "companyId"
)
SELECT
'material',
t."id",
kv.key,
CASE
WHEN jsonb_typeof(kv.value) = 'string' THEN kv.value #>> '{}'
ELSE kv.value->>'id'
END,
CASE WHEN jsonb_typeof(kv.value) = 'object' THEN kv.value ELSE NULL END,
CASE WHEN jsonb_typeof(kv.value) = 'object' THEN (kv.value->>'lastSyncedAt')::timestamptz ELSE NULL END,
t."companyId"
FROM "material" t,
LATERAL jsonb_each(t."externalId") AS kv(key, value)
WHERE t."externalId" IS NOT NULL
AND t."externalId" != '{}'::jsonb
AND (
CASE
WHEN jsonb_typeof(kv.value) = 'string' THEN kv.value #>> '{}'
ELSE kv.value->>'id'
END
) IS NOT NULL
ON CONFLICT ("integration", "externalId", "entityType", "companyId") WHERE "allowDuplicateExternalId" = false DO NOTHING;
-- tool
INSERT INTO "externalIntegrationMapping" (
"entityType", "entityId", "integration", "externalId",
"metadata", "lastSyncedAt", "companyId"
)
SELECT
'tool',
t."id",
kv.key,
CASE
WHEN jsonb_typeof(kv.value) = 'string' THEN kv.value #>> '{}'
ELSE kv.value->>'id'
END,
CASE WHEN jsonb_typeof(kv.value) = 'object' THEN kv.value ELSE NULL END,
CASE WHEN jsonb_typeof(kv.value) = 'object' THEN (kv.value->>'lastSyncedAt')::timestamptz ELSE NULL END,
t."companyId"
FROM "tool" t,
LATERAL jsonb_each(t."externalId") AS kv(key, value)
WHERE t."externalId" IS NOT NULL
AND t."externalId" != '{}'::jsonb
AND (
CASE
WHEN jsonb_typeof(kv.value) = 'string' THEN kv.value #>> '{}'
ELSE kv.value->>'id'
END
) IS NOT NULL
ON CONFLICT ("integration", "externalId", "entityType", "companyId") WHERE "allowDuplicateExternalId" = false DO NOTHING;
-- fixture
INSERT INTO "externalIntegrationMapping" (
"entityType", "entityId", "integration", "externalId",
"metadata", "lastSyncedAt", "companyId"
)
SELECT
'fixture',
t."id",
kv.key,
CASE
WHEN jsonb_typeof(kv.value) = 'string' THEN kv.value #>> '{}'
ELSE kv.value->>'id'
END,
CASE WHEN jsonb_typeof(kv.value) = 'object' THEN kv.value ELSE NULL END,
CASE WHEN jsonb_typeof(kv.value) = 'object' THEN (kv.value->>'lastSyncedAt')::timestamptz ELSE NULL END,
t."companyId"
FROM "fixture" t,
LATERAL jsonb_each(t."externalId") AS kv(key, value)
WHERE t."externalId" IS NOT NULL
AND t."externalId" != '{}'::jsonb
AND (
CASE
WHEN jsonb_typeof(kv.value) = 'string' THEN kv.value #>> '{}'
ELSE kv.value->>'id'
END
) IS NOT NULL
ON CONFLICT ("integration", "externalId", "entityType", "companyId") WHERE "allowDuplicateExternalId" = false DO NOTHING;
-- consumable
INSERT INTO "externalIntegrationMapping" (
"entityType", "entityId", "integration", "externalId",
"metadata", "lastSyncedAt", "companyId"
)
SELECT
'consumable',
t."id",
kv.key,
CASE
WHEN jsonb_typeof(kv.value) = 'string' THEN kv.value #>> '{}'
ELSE kv.value->>'id'
END,
CASE WHEN jsonb_typeof(kv.value) = 'object' THEN kv.value ELSE NULL END,
CASE WHEN jsonb_typeof(kv.value) = 'object' THEN (kv.value->>'lastSyncedAt')::timestamptz ELSE NULL END,
t."companyId"
FROM "consumable" t,
LATERAL jsonb_each(t."externalId") AS kv(key, value)
WHERE t."externalId" IS NOT NULL
AND t."externalId" != '{}'::jsonb
AND (
CASE
WHEN jsonb_typeof(kv.value) = 'string' THEN kv.value #>> '{}'
ELSE kv.value->>'id'
END
) IS NOT NULL
ON CONFLICT ("integration", "externalId", "entityType", "companyId") WHERE "allowDuplicateExternalId" = false DO NOTHING;
-- address
INSERT INTO "externalIntegrationMapping" (
"entityType", "entityId", "integration", "externalId",
"metadata", "lastSyncedAt", "companyId"
)
SELECT
'address',
t."id",
kv.key,
CASE
WHEN jsonb_typeof(kv.value) = 'string' THEN kv.value #>> '{}'
ELSE kv.value->>'id'
END,
CASE WHEN jsonb_typeof(kv.value) = 'object' THEN kv.value ELSE NULL END,
CASE WHEN jsonb_typeof(kv.value) = 'object' THEN (kv.value->>'lastSyncedAt')::timestamptz ELSE NULL END,
t."companyId"
FROM "address" t,
LATERAL jsonb_each(t."externalId") AS kv(key, value)
WHERE t."externalId" IS NOT NULL
AND t."externalId" != '{}'::jsonb
AND (
CASE
WHEN jsonb_typeof(kv.value) = 'string' THEN kv.value #>> '{}'
ELSE kv.value->>'id'
END
) IS NOT NULL
ON CONFLICT ("integration", "externalId", "entityType", "companyId") WHERE "allowDuplicateExternalId" = false DO NOTHING;
-- salesOrder
INSERT INTO "externalIntegrationMapping" (
"entityType", "entityId", "integration", "externalId",
"metadata", "lastSyncedAt", "companyId"
)
SELECT
'salesOrder',
t."id",
kv.key,
CASE
WHEN jsonb_typeof(kv.value) = 'string' THEN kv.value #>> '{}'
ELSE kv.value->>'id'
END,
CASE WHEN jsonb_typeof(kv.value) = 'object' THEN kv.value ELSE NULL END,
CASE WHEN jsonb_typeof(kv.value) = 'object' THEN (kv.value->>'lastSyncedAt')::timestamptz ELSE NULL END,
t."companyId"
FROM "salesOrder" t,
LATERAL jsonb_each(t."externalId") AS kv(key, value)
WHERE t."externalId" IS NOT NULL
AND t."externalId" != '{}'::jsonb
AND (
CASE
WHEN jsonb_typeof(kv.value) = 'string' THEN kv.value #>> '{}'
ELSE kv.value->>'id'
END
) IS NOT NULL
ON CONFLICT ("integration", "externalId", "entityType", "companyId") WHERE "allowDuplicateExternalId" = false DO NOTHING;
-- purchaseOrder
INSERT INTO "externalIntegrationMapping" (
"entityType", "entityId", "integration", "externalId",
"metadata", "lastSyncedAt", "companyId"
)
SELECT
'purchaseOrder',
t."id",
kv.key,
CASE
WHEN jsonb_typeof(kv.value) = 'string' THEN kv.value #>> '{}'
ELSE kv.value->>'id'
END,
CASE WHEN jsonb_typeof(kv.value) = 'object' THEN kv.value ELSE NULL END,
CASE WHEN jsonb_typeof(kv.value) = 'object' THEN (kv.value->>'lastSyncedAt')::timestamptz ELSE NULL END,
t."companyId"
FROM "purchaseOrder" t,
LATERAL jsonb_each(t."externalId") AS kv(key, value)
WHERE t."externalId" IS NOT NULL
AND t."externalId" != '{}'::jsonb
AND (
CASE
WHEN jsonb_typeof(kv.value) = 'string' THEN kv.value #>> '{}'
ELSE kv.value->>'id'
END
) IS NOT NULL
ON CONFLICT ("integration", "externalId", "entityType", "companyId") WHERE "allowDuplicateExternalId" = false DO NOTHING;
-- quote
INSERT INTO "externalIntegrationMapping" (
"entityType", "entityId", "integration", "externalId",
"metadata", "lastSyncedAt", "companyId"
)
SELECT
'quote',
t."id",
kv.key,
CASE
WHEN jsonb_typeof(kv.value) = 'string' THEN kv.value #>> '{}'
ELSE kv.value->>'id'
END,
CASE WHEN jsonb_typeof(kv.value) = 'object' THEN kv.value ELSE NULL END,
CASE WHEN jsonb_typeof(kv.value) = 'object' THEN (kv.value->>'lastSyncedAt')::timestamptz ELSE NULL END,
t."companyId"
FROM "quote" t,
LATERAL jsonb_each(t."externalId") AS kv(key, value)
WHERE t."externalId" IS NOT NULL
AND t."externalId" != '{}'::jsonb
AND (
CASE
WHEN jsonb_typeof(kv.value) = 'string' THEN kv.value #>> '{}'
ELSE kv.value->>'id'
END
) IS NOT NULL
ON CONFLICT ("integration", "externalId", "entityType", "companyId") WHERE "allowDuplicateExternalId" = false DO NOTHING;
-- salesInvoice
INSERT INTO "externalIntegrationMapping" (
"entityType", "entityId", "integration", "externalId",
"metadata", "lastSyncedAt", "companyId"
)
SELECT
'salesInvoice',
t."id",
kv.key,
CASE
WHEN jsonb_typeof(kv.value) = 'string' THEN kv.value #>> '{}'
ELSE kv.value->>'id'
END,
CASE WHEN jsonb_typeof(kv.value) = 'object' THEN kv.value ELSE NULL END,
CASE WHEN jsonb_typeof(kv.value) = 'object' THEN (kv.value->>'lastSyncedAt')::timestamptz ELSE NULL END,
t."companyId"
FROM "salesInvoice" t,
LATERAL jsonb_each(t."externalId") AS kv(key, value)
WHERE t."externalId" IS NOT NULL
AND t."externalId" != '{}'::jsonb
AND (
CASE
WHEN jsonb_typeof(kv.value) = 'string' THEN kv.value #>> '{}'
ELSE kv.value->>'id'
END
) IS NOT NULL
ON CONFLICT ("integration", "externalId", "entityType", "companyId") WHERE "allowDuplicateExternalId" = false DO NOTHING;
-- nonConformanceActionTask (JSON type, cast to JSONB)
INSERT INTO "externalIntegrationMapping" (
"entityType", "entityId", "integration", "externalId",
"metadata", "lastSyncedAt", "companyId"
)
SELECT
'nonConformanceActionTask',
t."id",
kv.key,
CASE
WHEN jsonb_typeof(kv.value) = 'string' THEN kv.value #>> '{}'
ELSE kv.value->>'id'
END,
CASE WHEN jsonb_typeof(kv.value) = 'object' THEN kv.value ELSE NULL END,
CASE WHEN jsonb_typeof(kv.value) = 'object' THEN (kv.value->>'lastSyncedAt')::timestamptz ELSE NULL END,
t."companyId"
FROM "nonConformanceActionTask" t,
LATERAL jsonb_each(t."externalId"::jsonb) AS kv(key, value)
WHERE t."externalId" IS NOT NULL
AND t."externalId"::jsonb != '{}'::jsonb
AND (
CASE
WHEN jsonb_typeof(kv.value) = 'string' THEN kv.value #>> '{}'
ELSE kv.value->>'id'
END
) IS NOT NULL
ON CONFLICT ("integration", "externalId", "entityType", "companyId") WHERE "allowDuplicateExternalId" = false DO NOTHING;
-- ============================================================================
-- STEP 2: Migrate tables requiring JOINs for companyId
-- ============================================================================
-- customerLocation (join customer for companyId)
INSERT INTO "externalIntegrationMapping" (
"entityType", "entityId", "integration", "externalId",
"metadata", "lastSyncedAt", "companyId"
)
SELECT
'customerLocation',
t."id",
kv.key,
CASE
WHEN jsonb_typeof(kv.value) = 'string' THEN kv.value #>> '{}'
ELSE kv.value->>'id'
END,
CASE WHEN jsonb_typeof(kv.value) = 'object' THEN kv.value ELSE NULL END,
CASE WHEN jsonb_typeof(kv.value) = 'object' THEN (kv.value->>'lastSyncedAt')::timestamptz ELSE NULL END,
c."companyId"
FROM "customerLocation" t
JOIN "customer" c ON t."customerId" = c."id",
LATERAL jsonb_each(t."externalId") AS kv(key, value)
WHERE t."externalId" IS NOT NULL
AND t."externalId" != '{}'::jsonb
AND (
CASE
WHEN jsonb_typeof(kv.value) = 'string' THEN kv.value #>> '{}'
ELSE kv.value->>'id'
END
) IS NOT NULL
ON CONFLICT ("integration", "externalId", "entityType", "companyId") WHERE "allowDuplicateExternalId" = false DO NOTHING;
-- supplierLocation (join supplier for companyId)
INSERT INTO "externalIntegrationMapping" (
"entityType", "entityId", "integration", "externalId",
"metadata", "lastSyncedAt", "companyId"
)
SELECT
'supplierLocation',
t."id",
kv.key,
CASE
WHEN jsonb_typeof(kv.value) = 'string' THEN kv.value #>> '{}'
ELSE kv.value->>'id'
END,
CASE WHEN jsonb_typeof(kv.value) = 'object' THEN kv.value ELSE NULL END,
CASE WHEN jsonb_typeof(kv.value) = 'object' THEN (kv.value->>'lastSyncedAt')::timestamptz ELSE NULL END,
s."companyId"
FROM "supplierLocation" t
JOIN "supplier" s ON t."supplierId" = s."id",
LATERAL jsonb_each(t."externalId") AS kv(key, value)
WHERE t."externalId" IS NOT NULL
AND t."externalId" != '{}'::jsonb
AND (
CASE
WHEN jsonb_typeof(kv.value) = 'string' THEN kv.value #>> '{}'
ELSE kv.value->>'id'
END
) IS NOT NULL
ON CONFLICT ("integration", "externalId", "entityType", "companyId") WHERE "allowDuplicateExternalId" = false DO NOTHING;
-- ============================================================================
-- STEP 3: Migrate special tables
-- ============================================================================
-- company (id IS the companyId)
INSERT INTO "externalIntegrationMapping" (
"entityType", "entityId", "integration", "externalId",
"metadata", "lastSyncedAt", "companyId"
)
SELECT
'company',
t."id",
kv.key,
CASE
WHEN jsonb_typeof(kv.value) = 'string' THEN kv.value #>> '{}'
ELSE kv.value->>'id'
END,
CASE WHEN jsonb_typeof(kv.value) = 'object' THEN kv.value ELSE NULL END,
CASE WHEN jsonb_typeof(kv.value) = 'object' THEN (kv.value->>'lastSyncedAt')::timestamptz ELSE NULL END,
t."id"
FROM "company" t,
LATERAL jsonb_each(t."externalId") AS kv(key, value)
WHERE t."externalId" IS NOT NULL
AND t."externalId" != '{}'::jsonb
AND (
CASE
WHEN jsonb_typeof(kv.value) = 'string' THEN kv.value #>> '{}'
ELSE kv.value->>'id'
END
) IS NOT NULL
ON CONFLICT ("integration", "externalId", "entityType", "companyId") WHERE "allowDuplicateExternalId" = false DO NOTHING;
-- user (many-to-many via userToCompany, one mapping per company membership)
INSERT INTO "externalIntegrationMapping" (
"entityType", "entityId", "integration", "externalId",
"metadata", "lastSyncedAt", "companyId"
)
SELECT
'user',
u."id",
kv.key,
CASE
WHEN jsonb_typeof(kv.value) = 'string' THEN kv.value #>> '{}'
ELSE kv.value->>'id'
END,
CASE WHEN jsonb_typeof(kv.value) = 'object' THEN kv.value ELSE NULL END,
CASE WHEN jsonb_typeof(kv.value) = 'object' THEN (kv.value->>'lastSyncedAt')::timestamptz ELSE NULL END,
uc."companyId"
FROM "user" u
JOIN "userToCompany" uc ON u."id" = uc."userId",
LATERAL jsonb_each(u."externalId") AS kv(key, value)
WHERE u."externalId" IS NOT NULL
AND u."externalId" != '{}'::jsonb
AND (
CASE
WHEN jsonb_typeof(kv.value) = 'string' THEN kv.value #>> '{}'
ELSE kv.value->>'id'
END
) IS NOT NULL
ON CONFLICT ("integration", "externalId", "entityType", "companyId") WHERE "allowDuplicateExternalId" = false DO NOTHING;
-- ============================================================================
-- STEP 4: Drop old indexes and columns
-- ============================================================================
-- Drop GIN indexes
DROP INDEX IF EXISTS "idx_customer_external_id";
DROP INDEX IF EXISTS "idx_supplier_external_id";
DROP INDEX IF EXISTS "idx_contact_external_id";
DROP INDEX IF EXISTS "idx_item_external_id";
DROP INDEX IF EXISTS "idx_part_external_id";
DROP INDEX IF EXISTS "idx_material_external_id";
DROP INDEX IF EXISTS "idx_tool_external_id";
DROP INDEX IF EXISTS "idx_fixture_external_id";
DROP INDEX IF EXISTS "idx_consumable_external_id";
DROP INDEX IF EXISTS "idx_address_external_id";
DROP INDEX IF EXISTS "idx_customerLocation_external_id";
DROP INDEX IF EXISTS "idx_supplierLocation_external_id";
DROP INDEX IF EXISTS "idx_salesOrder_external_id";
DROP INDEX IF EXISTS "idx_purchaseOrder_external_id";
DROP INDEX IF EXISTS "idx_quote_external_id";
DROP INDEX IF EXISTS "salesInvoice_externalId_idx";
DROP INDEX IF EXISTS "idx_user_external_id";
DROP INDEX IF EXISTS "idx_company_external_id";
-- Drop externalId columns
ALTER TABLE "customer" DROP COLUMN IF EXISTS "externalId";
ALTER TABLE "supplier" DROP COLUMN IF EXISTS "externalId";
ALTER TABLE "contact" DROP COLUMN IF EXISTS "externalId";
ALTER TABLE "item" DROP COLUMN IF EXISTS "externalId";
ALTER TABLE "part" DROP COLUMN IF EXISTS "externalId";
ALTER TABLE "material" DROP COLUMN IF EXISTS "externalId";
ALTER TABLE "tool" DROP COLUMN IF EXISTS "externalId";
ALTER TABLE "fixture" DROP COLUMN IF EXISTS "externalId";
ALTER TABLE "consumable" DROP COLUMN IF EXISTS "externalId";
ALTER TABLE "address" DROP COLUMN IF EXISTS "externalId";
ALTER TABLE "customerLocation" DROP COLUMN IF EXISTS "externalId";
ALTER TABLE "supplierLocation" DROP COLUMN IF EXISTS "externalId";
ALTER TABLE "salesOrder" DROP COLUMN IF EXISTS "externalId";
ALTER TABLE "purchaseOrder" DROP COLUMN IF EXISTS "externalId";
ALTER TABLE "quote" DROP COLUMN IF EXISTS "externalId";
ALTER TABLE "salesInvoice" DROP COLUMN IF EXISTS "externalId";
ALTER TABLE "user" DROP COLUMN IF EXISTS "externalId";
ALTER TABLE "company" DROP COLUMN IF EXISTS "externalId";
ALTER TABLE "nonConformanceActionTask" DROP COLUMN IF EXISTS "externalId";
-- ============================================================================
-- STEP 4: Recreate RPC functions that referenced item.externalId
-- ============================================================================
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,
"description" TEXT,
"unitOfMeasureCode" TEXT,
"unitCost" NUMERIC,
"quantity" NUMERIC,
"methodType" "methodType",
"itemTrackingType" TEXT,
"parentMaterialId" TEXT,
"order" DOUBLE PRECISION,
"operationId" TEXT,
"isRoot" BOOLEAN,
"kit" BOOLEAN,
"revision" TEXT,
"externalId" JSONB,
"version" NUMERIC(10,2),
"shelfIds" JSONB
) AS $$
WITH RECURSIVE material AS (
SELECT
"id",
"makeMethodId",
"methodType",
"materialMakeMethodId",
"itemId",
"itemType",
"quantity",
"makeMethodId" AS "parentMaterialId",
NULL AS "operationId",
COALESCE("order", 1) AS "order",
"kit",
"shelfIds"
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."methodOperationId" AS "operationId",
child."order",
child."kit",
child."shelfIds"
FROM
"methodMaterial" child
INNER JOIN material parent ON parent."materialMakeMethodId" = child."makeMethodId"
)
SELECT
material.id as "methodMaterialId",
material."makeMethodId",
material."materialMakeMethodId",
material."itemId",
item."readableIdWithRevision" AS "itemReadableId",
material."itemType",
item."name" AS "description",
item."unitOfMeasureCode",
cost."unitCost",
material."quantity",
material."methodType",
item."itemTrackingType",
material."parentMaterialId",
material."order",
material."operationId",
false AS "isRoot",
material."kit",
item."revision",
(
SELECT COALESCE(
jsonb_object_agg(
eim."integration",
CASE
WHEN eim."metadata" IS NOT NULL THEN eim."metadata"
ELSE to_jsonb(eim."externalId")
END
) FILTER (WHERE eim."externalId" IS NOT NULL),
'{}'::jsonb
)
FROM "externalIntegrationMapping" eim
WHERE eim."entityType" = 'item' AND eim."entityId" = item.id
) AS "externalId",
mm2."version",
material."shelfIds"
FROM material
INNER JOIN item
ON material."itemId" = item.id
INNER JOIN "itemCost" cost
ON item.id = cost."itemId"
INNER JOIN "makeMethod" mm
ON material."makeMethodId" = mm.id
LEFT JOIN "makeMethod" mm2
ON material."materialMakeMethodId" = mm2.id
UNION
SELECT
mm."id" AS "methodMaterialId",
NULL AS "makeMethodId",
mm.id AS "materialMakeMethodId",
mm."itemId",
item."readableIdWithRevision" AS "itemReadableId",
item."type"::text,
item."name" AS "description",
item."unitOfMeasureCode",
cost."unitCost",
1 AS "quantity",
'Make' AS "methodType",
item."itemTrackingType",
NULL AS "parentMaterialId",
CAST(1 AS DOUBLE PRECISION) AS "order",
NULL AS "operationId",
true AS "isRoot",
false AS "kit",
item."revision",
(
SELECT COALESCE(
jsonb_object_agg(
eim."integration",
CASE
WHEN eim."metadata" IS NOT NULL THEN eim."metadata"
ELSE to_jsonb(eim."externalId")
END
) FILTER (WHERE eim."externalId" IS NOT NULL),
'{}'::jsonb
)
FROM "externalIntegrationMapping" eim
WHERE eim."entityType" = 'item' AND eim."entityId" = item.id
) AS "externalId",
mm."version",
'{}'::JSONB AS "shelfIds"
FROM "makeMethod" mm
INNER JOIN item
ON mm."itemId" = item.id
INNER JOIN "itemCost" cost
ON item.id = cost."itemId"
WHERE mm.id = uid
ORDER BY "order"
$$ LANGUAGE sql STABLE;
DROP FUNCTION IF EXISTS get_quote_methods_by_method_id;
CREATE OR REPLACE FUNCTION get_quote_methods_by_method_id(mid TEXT)
RETURNS TABLE (
"quoteId" TEXT,
"quoteLineId" TEXT,
"methodMaterialId" TEXT,
"quoteMakeMethodId" TEXT,
"quoteMaterialMakeMethodId" TEXT,
"itemId" TEXT,
"itemReadableId" TEXT,
"description" TEXT,
"unitOfMeasureCode" TEXT,
"itemType" TEXT,
"itemTrackingType" TEXT,
"quantity" NUMERIC,
"unitCost" NUMERIC,
"methodType" "methodType",
"parentMaterialId" TEXT,
"order" DOUBLE PRECISION,
"isRoot" BOOLEAN,
"kit" BOOLEAN,
"revision" TEXT,
"externalId" JSONB,
"version" NUMERIC(10,2),
"shelfId" TEXT
) AS $$
WITH RECURSIVE material AS (
SELECT
"quoteId",
"quoteLineId",
"id",
"id" AS "quoteMakeMethodId",
'Make'::"methodType" AS "methodType",
"id" AS "quoteMaterialMakeMethodId",
"version",
"itemId",
'Part' AS "itemType",
1::NUMERIC AS "quantity",
0::NUMERIC AS "unitCost",
"parentMaterialId",
CAST(1 AS DOUBLE PRECISION) AS "order",
TRUE AS "isRoot",
FALSE AS "kit",
NULL::TEXT AS "shelfId"
FROM
"quoteMakeMethod"
WHERE
"id" = mid
UNION
SELECT
child."quoteId",
child."quoteLineId",
child."id",
child."quoteMakeMethodId",
child."methodType",
child."quoteMaterialMakeMethodId",
child."version",
child."itemId",
child."itemType",
child."quantity",
child."unitCost",
parent."id" AS "parentMaterialId",
child."order",
FALSE AS "isRoot",
child."kit",
child."shelfId"
FROM
"quoteMaterialWithMakeMethodId" child
INNER JOIN material parent ON parent."quoteMaterialMakeMethodId" = child."quoteMakeMethodId"
WHERE parent."methodType" = 'Make'
)
SELECT
material."quoteId",
material."quoteLineId",
material.id as "methodMaterialId",
material."quoteMakeMethodId",
material."quoteMaterialMakeMethodId",
material."itemId",
item."readableIdWithRevision" AS "itemReadableId",
item."name" AS "description",
item."unitOfMeasureCode",
material."itemType",
item."itemTrackingType",
material."quantity",
material."unitCost",
material."methodType",
material."parentMaterialId",
material."order",
material."isRoot",
material."kit",
item."revision",
(
SELECT COALESCE(
jsonb_object_agg(
eim."integration",
CASE
WHEN eim."metadata" IS NOT NULL THEN eim."metadata"
ELSE to_jsonb(eim."externalId")
END
) FILTER (WHERE eim."externalId" IS NOT NULL),
'{}'::jsonb
)
FROM "externalIntegrationMapping" eim
WHERE eim."entityType" = 'item' AND eim."entityId" = item.id
) AS "externalId",
material."version",
material."shelfId"
FROM material
INNER JOIN item ON material."itemId" = item.id
ORDER BY "order"
$$ LANGUAGE sql STABLE;
DROP FUNCTION IF EXISTS get_quote_methods;
CREATE OR REPLACE FUNCTION get_quote_methods(qid TEXT)
RETURNS TABLE (
"quoteId" TEXT,
"quoteLineId" TEXT,
"methodMaterialId" TEXT,
"quoteMakeMethodId" TEXT,
"quoteMaterialMakeMethodId" TEXT,
"itemId" TEXT,
"itemReadableId" TEXT,
"description" TEXT,
"itemType" TEXT,
"quantity" NUMERIC,
"unitCost" NUMERIC,
"methodType" "methodType",
"parentMaterialId" TEXT,
"order" DOUBLE PRECISION,
"isRoot" BOOLEAN,
"kit" BOOLEAN,
"revision" TEXT,
"externalId" JSONB,
"version" NUMERIC(10,2),
"shelfId" TEXT
) AS $$
WITH RECURSIVE material AS (
SELECT
"quoteId",
"quoteLineId",
"id",
"id" AS "quoteMakeMethodId",
'Make'::"methodType" AS "methodType",
"id" AS "quoteMaterialMakeMethodId",
"itemId",
'Part' AS "itemType",
1::NUMERIC AS "quantity",
0::NUMERIC AS "unitCost",
"parentMaterialId",
CAST(1 AS DOUBLE PRECISION) AS "order",
TRUE AS "isRoot",
FALSE AS "kit",
"version",
NULL::TEXT AS "shelfId"
FROM
"quoteMakeMethod"
WHERE
"quoteId" = qid
AND "parentMaterialId" IS NULL
UNION
SELECT
child."quoteId",
child."quoteLineId",
child."id",
child."quoteMakeMethodId",
child."methodType",
child."quoteMaterialMakeMethodId",
child."itemId",
child."itemType",
child."quantity",
child."unitCost",
parent."id" AS "parentMaterialId",
child."order",
FALSE AS "isRoot",
child."kit",
child."version",
child."shelfId"
FROM
"quoteMaterialWithMakeMethodId" child
INNER JOIN material parent ON parent."quoteMaterialMakeMethodId" = child."quoteMakeMethodId"
)
SELECT
material."quoteId",
material."quoteLineId",
material.id as "methodMaterialId",
material."quoteMakeMethodId",
material."quoteMaterialMakeMethodId",
material."itemId",
item."readableIdWithRevision" AS "itemReadableId",
item."name" AS "description",
material."itemType",
material."quantity",
material."unitCost",
material."methodType",
material."parentMaterialId",
material."order",
material."isRoot",
material."kit",
item."revision",
(
SELECT COALESCE(
jsonb_object_agg(
eim."integration",
CASE
WHEN eim."metadata" IS NOT NULL THEN eim."metadata"
ELSE to_jsonb(eim."externalId")
END
) FILTER (WHERE eim."externalId" IS NOT NULL),
'{}'::jsonb
)
FROM "externalIntegrationMapping" eim
WHERE eim."entityType" = 'item' AND eim."entityId" = item.id
) AS "externalId",
material."version",
material."shelfId"
FROM material
INNER JOIN item ON material."itemId" = item.id
WHERE material."quoteId" = qid
ORDER BY "order"
$$ LANGUAGE sql STABLE;
DROP FUNCTION IF EXISTS get_job_method;
CREATE OR REPLACE FUNCTION get_job_method(jid TEXT)
RETURNS TABLE (
"jobId" TEXT,
"methodMaterialId" TEXT,
"jobMakeMethodId" TEXT,
"jobMaterialMakeMethodId" TEXT,
"itemId" TEXT,
"itemReadableId" TEXT,
"description" TEXT,
"itemType" TEXT,
"quantity" NUMERIC,
"unitCost" NUMERIC,
"methodType" "methodType",
"parentMaterialId" TEXT,
"order" DOUBLE PRECISION,
"isRoot" BOOLEAN,
"kit" BOOLEAN,
"revision" TEXT,
"externalId" JSONB,
"version" NUMERIC(10,2),
"shelfId" TEXT
) AS $$
WITH RECURSIVE material AS (
SELECT
"jobId",
"id",
"id" AS "jobMakeMethodId",
'Make'::"methodType" AS "methodType",
"id" AS "jobMaterialMakeMethodId",
"itemId",
'Part' AS "itemType",
1::NUMERIC AS "quantity",
0::NUMERIC AS "unitCost",
"parentMaterialId",
CAST(1 AS DOUBLE PRECISION) AS "order",
TRUE AS "isRoot",
FALSE AS "kit",
"version",
NULL::TEXT AS "shelfId"
FROM
"jobMakeMethod"
WHERE
"jobId" = jid
AND "parentMaterialId" IS NULL
UNION
SELECT
child."jobId",
child."id",
child."jobMakeMethodId",
child."methodType",
child."jobMaterialMakeMethodId",
child."itemId",
child."itemType",
child."quantity",
child."unitCost",
parent."id" AS "parentMaterialId",
child."order",
FALSE AS "isRoot",
child."kit",
child."version",
child."shelfId"
FROM
"jobMaterialWithMakeMethodId" child
INNER JOIN material parent ON parent."jobMaterialMakeMethodId" = child."jobMakeMethodId"
WHERE parent."methodType" = 'Make'
)
SELECT
material."jobId",
material.id as "methodMaterialId",
material."jobMakeMethodId",
material."jobMaterialMakeMethodId",
material."itemId",
item."readableIdWithRevision" AS "itemReadableId",
item."name" AS "description",
material."itemType",
material."quantity",
material."unitCost",
material."methodType",
material."parentMaterialId",
material."order",
material."isRoot",
material."kit",
item."revision",
(
SELECT COALESCE(
jsonb_object_agg(
eim."integration",
CASE
WHEN eim."metadata" IS NOT NULL THEN eim."metadata"
ELSE to_jsonb(eim."externalId")
END
) FILTER (WHERE eim."externalId" IS NOT NULL),
'{}'::jsonb
)
FROM "externalIntegrationMapping" eim
WHERE eim."entityType" = 'item' AND eim."entityId" = item.id
) AS "externalId",
material."version",
material."shelfId"
FROM material
INNER JOIN item ON material."itemId" = item.id
WHERE material."jobId" = jid
ORDER BY "order"
$$ LANGUAGE sql STABLE;
DROP VIEW IF EXISTS "suppliers";
CREATE OR REPLACE VIEW "suppliers" WITH(SECURITY_INVOKER=true) AS
SELECT
s.id,
s.name,
s."supplierTypeId",
s."supplierStatusId",
s."taxId",
s."accountManagerId",
s.logo,
s.assignee,
s."companyId",
s."createdAt",
s."createdBy",
s."updatedAt",
s."updatedBy",
s."customFields",
s."currencyCode",
s.website,
(
SELECT COALESCE(
jsonb_object_agg(
eim."integration",
CASE
WHEN eim."metadata" IS NOT NULL THEN eim."metadata"
ELSE to_jsonb(eim."externalId")
END
) FILTER (WHERE eim."externalId" IS NOT NULL),
'{}'::jsonb
)
FROM "externalIntegrationMapping" eim
WHERE eim."entityType" = 'supplier' AND eim."entityId" = s.id
) AS "externalId",
s.tags,
s."taxPercent",
s."purchasingContactId",
s."invoicingContactId",
s.embedding,
st.name AS "type",
ss.name AS "status",
po.count AS "orderCount",
p.count AS "partCount",
pc."workPhone" AS "phone",
pc.fax AS "fax"
FROM "supplier" s
LEFT JOIN "supplierType" st ON st.id = s."supplierTypeId"
LEFT JOIN "supplierStatus" ss ON ss.id = s."supplierStatusId"
LEFT JOIN (
SELECT
"supplierId",
COUNT(*) AS "count"
FROM "purchaseOrder"
GROUP BY "supplierId"
) po ON po."supplierId" = s.id
LEFT JOIN (
SELECT
"supplierId",
COUNT(*) AS "count"
FROM "supplierPart"
GROUP BY "supplierId"
) p ON p."supplierId" = s.id
LEFT JOIN (
SELECT DISTINCT ON (sc."supplierId")
sc."supplierId" AS id,
co."workPhone",
co."fax"
FROM "supplierContact" sc
JOIN "contact" co
ON co.id = sc."contactId"
ORDER BY sc."supplierId", sc.id
) pc
ON pc.id = s.id;
DROP VIEW IF EXISTS "customers";
CREATE OR REPLACE VIEW "customers" WITH(SECURITY_INVOKER=true) AS
SELECT
c.id,
c.name,
c."customerTypeId",
c."customerStatusId",
c."taxId",
c."accountManagerId",
c.logo,
c.assignee,
c."taxPercent",
c.website,
c."companyId",
c."createdAt",
c."createdBy",
c."updatedAt",
c."updatedBy",
c."customFields",
c."currencyCode",
c."salesContactId",
c."invoicingContactId",
(
SELECT COALESCE(
jsonb_object_agg(
eim."integration",
CASE
WHEN eim."metadata" IS NOT NULL THEN eim."metadata"
ELSE to_jsonb(eim."externalId")
END
) FILTER (WHERE eim."externalId" IS NOT NULL),
'{}'::jsonb
)
FROM "externalIntegrationMapping" eim
WHERE eim."entityType" = 'customer' AND eim."entityId" = c.id
) AS "externalId",
ct.name AS "type",
cs.name AS "status",
so.count AS "orderCount",
pc."workPhone" AS "phone",
pc."fax" AS "fax"
FROM "customer" c
LEFT JOIN "customerType" ct ON ct.id = c."customerTypeId"
LEFT JOIN "customerStatus" cs ON cs.id = c."customerStatusId"
LEFT JOIN (
SELECT
"customerId",
COUNT(*) AS "count"
FROM "salesOrder"
GROUP BY "customerId"
) so ON so."customerId" = c.id
LEFT JOIN (
SELECT DISTINCT ON (cc."customerId")
cc."customerId",
co."workPhone",
co."fax"
FROM "customerContact" cc
INNER JOIN "contact" co ON co.id = cc."contactId"
ORDER BY cc."customerId"
) pc ON pc."customerId" = c.id;
DROP VIEW IF EXISTS "parts";
CREATE OR REPLACE VIEW "parts" WITH (SECURITY_INVOKER=true) AS
WITH latest_items AS (
SELECT DISTINCT ON (i."readableId", i."companyId")
i.*,
mu.id as "modelUploadId",
mu."modelPath",
mu."thumbnailPath" as "modelThumbnailPath",
mu."name" as "modelName",
mu."size" as "modelSize"
FROM "item" i
LEFT JOIN "modelUpload" mu ON mu.id = i."modelUploadId"
ORDER BY i."readableId", i."companyId", i."createdAt" DESC NULLS LAST
),
item_revisions AS (
SELECT
i."readableId",
i."companyId",
json_agg(
json_build_object(
'id', i.id,
'revision', i."revision",
'name', i."name",
'description', i."description",
'active', i."active",
'createdAt', i."createdAt"
) ORDER BY i."createdAt"
) as "revisions"
FROM "item" i
GROUP BY i."readableId", i."companyId"
)
SELECT
li."active",
li."assignee",
li."defaultMethodType",
li."description",
li."itemTrackingType",
li."name",
li."replenishmentSystem",
li."unitOfMeasureCode",
li."notes",
li."revision",
li."readableId",
li."readableIdWithRevision",
li."id",
li."companyId",
CASE
WHEN li."thumbnailPath" IS NULL AND li."modelThumbnailPath" IS NOT NULL THEN li."modelThumbnailPath"
ELSE li."thumbnailPath"
END as "thumbnailPath",
li."modelPath",
li."modelName",
li."modelSize",
ps."supplierIds",
uom.name as "unitOfMeasure",
ir."revisions",
p."customFields",
p."tags",
ic."itemPostingGroupId",
(
SELECT COALESCE(
jsonb_object_agg(
eim."integration",
CASE
WHEN eim."metadata" IS NOT NULL THEN eim."metadata"
ELSE to_jsonb(eim."externalId")
END
) FILTER (WHERE eim."externalId" IS NOT NULL),
'{}'::jsonb
)
FROM "externalIntegrationMapping" eim
WHERE eim."entityType" = 'item' AND eim."entityId" = li.id
) AS "externalId",
li."createdBy",
li."createdAt",
li."updatedBy",
li."updatedAt"
FROM "part" p
INNER JOIN latest_items li ON li."readableId" = p."id" AND li."companyId" = p."companyId"
LEFT JOIN item_revisions ir ON ir."readableId" = p."id" AND ir."companyId" = p."companyId"
LEFT JOIN (
SELECT
"itemId",
"companyId",
string_agg(ps."supplierPartId", ',') AS "supplierIds"
FROM "supplierPart" ps
GROUP BY "itemId", "companyId"
) ps ON ps."itemId" = li."id" AND ps."companyId" = li."companyId"
LEFT JOIN "unitOfMeasure" uom ON uom.code = li."unitOfMeasureCode" AND uom."companyId" = li."companyId"
LEFT JOIN "itemCost" ic ON ic."itemId" = li.id;
DROP VIEW IF EXISTS "materials";
CREATE OR REPLACE VIEW "materials" WITH (SECURITY_INVOKER=true) AS
WITH latest_items AS (
SELECT DISTINCT ON (i."readableId", i."companyId")
i.*,
mu."modelPath",
mu."thumbnailPath" as "modelThumbnailPath",
mu."name" as "modelName",
mu."size" as "modelSize"
FROM "item" i
LEFT JOIN "modelUpload" mu ON mu.id = i."modelUploadId"
ORDER BY i."readableId", i."companyId", i."createdAt" DESC NULLS LAST
),
item_revisions AS (
SELECT
i."readableId",
i."companyId",
json_agg(
json_build_object(
'id', i.id,
'revision', i."revision",
'methodType', i."defaultMethodType",
'type', i."type"
) ORDER BY i."createdAt"
) as "revisions"
FROM "item" i
GROUP BY i."readableId", i."companyId"
)
SELECT
i."active",
i."assignee",
i."defaultMethodType",
i."description",
i."itemTrackingType",
i."name",
i."replenishmentSystem",
i."unitOfMeasureCode",
i."notes",
i."revision",
i."readableId",
i."readableIdWithRevision",
i."id",
i."companyId",
CASE
WHEN i."thumbnailPath" IS NULL AND i."modelThumbnailPath" IS NOT NULL THEN i."modelThumbnailPath"
ELSE i."thumbnailPath"
END as "thumbnailPath",
i."modelUploadId",
i."modelPath",
i."modelName",
i."modelSize",
ps."supplierIds",
uom.name as "unitOfMeasure",
ir."revisions",
mf."name" AS "materialForm",
ms."name" AS "materialSubstance",
md."name" AS "dimensions",
mfin."name" AS "finish",
mg."name" AS "grade",
mt."name" AS "materialType",
m."materialSubstanceId",
m."materialFormId",
m."customFields",
m."tags",
ic."itemPostingGroupId",
(
SELECT COALESCE(
jsonb_object_agg(
eim."integration",
CASE
WHEN eim."metadata" IS NOT NULL THEN eim."metadata"
ELSE to_jsonb(eim."externalId")
END
) FILTER (WHERE eim."externalId" IS NOT NULL),
'{}'::jsonb
)
FROM "externalIntegrationMapping" eim
WHERE eim."entityType" = 'item' AND eim."entityId" = i.id
) AS "externalId",
i."createdBy",
i."createdAt",
i."updatedBy",
i."updatedAt"
FROM "material" m
INNER JOIN latest_items i ON i."readableId" = m."id" AND i."companyId" = m."companyId"
LEFT JOIN item_revisions ir ON ir."readableId" = m."id" AND ir."companyId" = i."companyId"
LEFT JOIN (
SELECT
ps."itemId",
ps."companyId",
string_agg(ps."supplierPartId", ',') AS "supplierIds"
FROM "supplierPart" ps
GROUP BY ps."itemId", ps."companyId"
) ps ON ps."itemId" = i."id" AND ps."companyId" = i."companyId"
LEFT JOIN "modelUpload" mu ON mu.id = i."modelUploadId"
LEFT JOIN "unitOfMeasure" uom ON uom.code = i."unitOfMeasureCode" AND uom."companyId" = i."companyId"
LEFT JOIN "materialForm" mf ON mf."id" = m."materialFormId"
LEFT JOIN "materialSubstance" ms ON ms."id" = m."materialSubstanceId"
LEFT JOIN "materialDimension" md ON m."dimensionId" = md."id"
LEFT JOIN "materialFinish" mfin ON m."finishId" = mfin."id"
LEFT JOIN "materialGrade" mg ON m."gradeId" = mg."id"
LEFT JOIN "materialType" mt ON m."materialTypeId" = mt."id"
LEFT JOIN "itemCost" ic ON ic."itemId" = i.id;
DROP VIEW IF EXISTS "tools";
CREATE OR REPLACE VIEW "tools" WITH (SECURITY_INVOKER=true) AS
WITH latest_items AS (
SELECT DISTINCT ON (i."readableId", i."companyId")
i.*,
mu.id as "modelUploadId",
mu."modelPath",
mu."thumbnailPath" as "modelThumbnailPath",
mu."name" as "modelName",
mu."size" as "modelSize"
FROM "item" i
LEFT JOIN "modelUpload" mu ON mu.id = i."modelUploadId"
ORDER BY i."readableId", i."companyId", i."createdAt" DESC NULLS LAST
),
item_revisions AS (
SELECT
i."readableId",
i."companyId",
json_agg(
json_build_object(
'id', i.id,
'revision', i."revision",
'methodType', i."defaultMethodType",
'type', i."type"
) ORDER BY i."createdAt"
) as "revisions"
FROM "item" i
GROUP BY i."readableId", i."companyId"
)
SELECT
li."active",
li."assignee",
li."defaultMethodType",
li."description",
li."itemTrackingType",
li."name",
li."replenishmentSystem",
li."unitOfMeasureCode",
li."notes",
li."revision",
li."readableId",
li."readableIdWithRevision",
li."id",
li."companyId",
CASE
WHEN li."thumbnailPath" IS NULL AND li."modelThumbnailPath" IS NOT NULL THEN li."modelThumbnailPath"
ELSE li."thumbnailPath"
END as "thumbnailPath",
li."modelPath",
li."modelName",
li."modelSize",
ps."supplierIds",
uom.name as "unitOfMeasure",
ir."revisions",
t."customFields",
t."tags",
ic."itemPostingGroupId",
(
SELECT COALESCE(
jsonb_object_agg(
eim."integration",
CASE
WHEN eim."metadata" IS NOT NULL THEN eim."metadata"
ELSE to_jsonb(eim."externalId")
END
) FILTER (WHERE eim."externalId" IS NOT NULL),
'{}'::jsonb
)
FROM "externalIntegrationMapping" eim
WHERE eim."entityType" = 'item' AND eim."entityId" = li.id
) AS "externalId",
li."createdBy",
li."createdAt",
li."updatedBy",
li."updatedAt"
FROM "tool" t
INNER JOIN latest_items li ON li."readableId" = t."id" AND li."companyId" = t."companyId"
LEFT JOIN item_revisions ir ON ir."readableId" = t."id" AND ir."companyId" = li."companyId"
LEFT JOIN (
SELECT
"itemId",
"companyId",
string_agg(ps."supplierPartId", ',') AS "supplierIds"
FROM "supplierPart" ps
GROUP BY "itemId", "companyId"
) ps ON ps."itemId" = li."id" AND ps."companyId" = li."companyId"
LEFT JOIN "unitOfMeasure" uom ON uom.code = li."unitOfMeasureCode" AND uom."companyId" = li."companyId"
LEFT JOIN "itemCost" ic ON ic."itemId" = li.id;
DROP VIEW IF EXISTS "consumables";
CREATE OR REPLACE VIEW "consumables" WITH (SECURITY_INVOKER=true) AS
WITH latest_items AS (
SELECT DISTINCT ON (i."readableId", i."companyId")
i.*,
mu."modelPath",
mu."thumbnailPath" as "modelThumbnailPath",
mu."name" as "modelName",
mu."size" as "modelSize"
FROM "item" i
LEFT JOIN "modelUpload" mu ON mu.id = i."modelUploadId"
ORDER BY i."readableId", i."companyId", i."createdAt" DESC NULLS LAST
),
item_revisions AS (
SELECT
i."readableId",
i."companyId",
json_agg(
json_build_object(
'id', i.id,
'revision', i."revision",
'methodType', i."defaultMethodType",
'type', i."type"
) ORDER BY i."createdAt"
) as "revisions"
FROM "item" i
GROUP BY i."readableId", i."companyId"
)
SELECT
li."active",
li."assignee",
li."defaultMethodType",
li."description",
li."itemTrackingType",
li."name",
li."replenishmentSystem",
li."unitOfMeasureCode",
li."notes",
li."revision",
li."readableId",
li."readableIdWithRevision",
li."id",
li."companyId",
CASE
WHEN li."thumbnailPath" IS NULL AND li."modelThumbnailPath" IS NOT NULL THEN li."modelThumbnailPath"
ELSE li."thumbnailPath"
END as "thumbnailPath",
li."modelUploadId",
li."modelPath",
li."modelName",
li."modelSize",
ps."supplierIds",
uom.name as "unitOfMeasure",
ir."revisions",
c."customFields",
c."tags",
ic."itemPostingGroupId",
(
SELECT COALESCE(
jsonb_object_agg(
eim."integration",
CASE
WHEN eim."metadata" IS NOT NULL THEN eim."metadata"
ELSE to_jsonb(eim."externalId")
END
) FILTER (WHERE eim."externalId" IS NOT NULL),
'{}'::jsonb
)
FROM "externalIntegrationMapping" eim
WHERE eim."entityType" = 'item' AND eim."entityId" = li.id
) AS "externalId",
li."createdBy",
li."createdAt",
li."updatedBy",
li."updatedAt"
FROM "consumable" c
INNER JOIN latest_items li ON li."readableId" = c."id" AND li."companyId" = c."companyId"
LEFT JOIN item_revisions ir ON ir."readableId" = c."id" AND ir."companyId" = li."companyId"
LEFT JOIN (
SELECT
"itemId",
"companyId",
string_agg(ps."supplierPartId", ',') AS "supplierIds"
FROM "supplierPart" ps
GROUP BY "itemId", "companyId"
) ps ON ps."itemId" = li."id" AND ps."companyId" = li."companyId"
LEFT JOIN "unitOfMeasure" uom ON uom.code = li."unitOfMeasureCode" AND uom."companyId" = li."companyId"
LEFT JOIN "itemCost" ic ON ic."itemId" = li.id;
CREATE OR REPLACE VIEW "services" WITH(SECURITY_INVOKER=true) AS
WITH latest_items AS (
SELECT DISTINCT ON (i."readableId", i."companyId")
i.*
FROM "item" i
ORDER BY i."readableId", i."companyId", i."createdAt" DESC NULLS LAST
),
item_revisions AS (
SELECT
i."readableId",
i."companyId",
json_agg(
json_build_object(
'id', i.id,
'revision', i."revision",
'methodType', i."defaultMethodType",
'type', i."type"
) ORDER BY i."createdAt"
) as "revisions"
FROM "item" i
GROUP BY i."readableId", i."companyId"
)
SELECT
li."active",
li."assignee",
li."defaultMethodType",
li."description",
li."itemTrackingType",
li."name",
li."replenishmentSystem",
li."unitOfMeasureCode",
li."notes",
li."revision",
li."readableId",
li."readableIdWithRevision",
li."id",
li."companyId",
li."thumbnailPath",
ps."supplierIds",
uom.name as "unitOfMeasure",
ir."revisions",
s."customFields",
s."tags",
ic."itemPostingGroupId",
(
SELECT COALESCE(
jsonb_object_agg(
eim."integration",
CASE
WHEN eim."metadata" IS NOT NULL THEN eim."metadata"
ELSE to_jsonb(eim."externalId")
END
) FILTER (WHERE eim."externalId" IS NOT NULL),
'{}'::jsonb
)
FROM "externalIntegrationMapping" eim
WHERE eim."entityType" = 'item' AND eim."entityId" = li.id
) AS "externalId",
li."createdBy",
li."createdAt",
li."updatedBy",
li."updatedAt"
FROM "service" s
INNER JOIN latest_items li ON li."readableId" = s."id" AND li."companyId" = s."companyId"
LEFT JOIN item_revisions ir ON ir."readableId" = s."id" AND ir."companyId" = li."companyId"
LEFT JOIN (
SELECT
"itemId",
"companyId",
string_agg(ps."supplierPartId", ',') AS "supplierIds"
FROM "supplierPart" ps
GROUP BY "itemId", "companyId"
) ps ON ps."itemId" = li."id" AND ps."companyId" = li."companyId"
LEFT JOIN "unitOfMeasure" uom ON uom.code = li."unitOfMeasureCode" AND uom."companyId" = li."companyId"
LEFT JOIN "itemCost" ic ON ic."itemId" = li.id;
DROP VIEW IF EXISTS "salesOrders";
CREATE OR REPLACE VIEW "salesOrders" WITH(SECURITY_INVOKER=true) AS
SELECT
s.*,
sl."thumbnailPath",
sl."itemType",
sl."orderTotal" + COALESCE(ss."shippingCost", 0) AS "orderTotal",
sl."jobs",
sl."lines",
st."name" AS "shippingTermName",
sp."paymentTermId",
ss."shippingMethodId",
ss."receiptRequestedDate",
ss."receiptPromisedDate",
ss."dropShipment",
ss."shippingCost",
(
SELECT COALESCE(
jsonb_object_agg(
eim."integration",
CASE
WHEN eim."metadata" IS NOT NULL THEN eim."metadata"
ELSE to_jsonb(eim."externalId")
END
) FILTER (WHERE eim."externalId" IS NOT NULL),
'{}'::jsonb
)
FROM "externalIntegrationMapping" eim
WHERE eim."entityType" = 'salesOrder' AND eim."entityId" = s.id
) AS "externalId"
FROM "salesOrder" s
LEFT JOIN (
SELECT
sol."salesOrderId",
MIN(CASE
WHEN i."thumbnailPath" IS NULL AND mu."thumbnailPath" IS NOT NULL THEN mu."thumbnailPath"
ELSE i."thumbnailPath"
END) AS "thumbnailPath",
SUM(
DISTINCT (1+COALESCE(sol."taxPercent", 0))*(COALESCE(sol."saleQuantity", 0)*(COALESCE(sol."unitPrice", 0)) + COALESCE(sol."shippingCost", 0) + COALESCE(sol."addOnCost", 0))
) AS "orderTotal",
MIN(i."type") AS "itemType",
ARRAY_AGG(
CASE
WHEN j.id IS NOT NULL THEN json_build_object(
'id', j.id,
'jobId', j."jobId",
'status', j."status",
'dueDate', j."dueDate",
'productionQuantity', j."productionQuantity",
'quantityComplete', j."quantityComplete",
'quantityShipped', j."quantityShipped",
'quantity', j."quantity",
'scrapQuantity', j."scrapQuantity",
'salesOrderLineId', sol.id,
'assignee', j."assignee"
)
ELSE NULL
END
) FILTER (WHERE j.id IS NOT NULL) AS "jobs",
ARRAY_AGG(
json_build_object(
'id', sol.id,
'methodType', sol."methodType",
'saleQuantity', sol."saleQuantity"
)
) AS "lines"
FROM "salesOrderLine" sol
LEFT JOIN "item" i
ON i."id" = sol."itemId"
LEFT JOIN "modelUpload" mu ON mu.id = i."modelUploadId"
LEFT JOIN "job" j ON j."salesOrderId" = sol."salesOrderId" AND j."salesOrderLineId" = sol."id"
GROUP BY sol."salesOrderId"
) sl ON sl."salesOrderId" = s."id"
LEFT JOIN "salesOrderShipment" ss ON ss."id" = s."id"
LEFT JOIN "shippingTerm" st ON st."id" = ss."shippingTermId"
LEFT JOIN "salesOrderPayment" sp ON sp."id" = s."id";