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
// SPDX-License-Identifier: AGPL-3.0-only
// Carbon (github.com/crbnos/carbon). Modified or adapted versions of this file,
// including ports, remain AGPLv3; serving them over a network requires releasing their source.
import {
insertId,
maybeOne,
need,
one,
type Row,
rows,
sharedColumns
} from "../sql.ts";
import type { Ctx } from "../types.ts";
/**
* The seed's copy of the `get-method` operation, which it cannot call from
* inside its one SQL transaction. Narrower on purpose: no configuration rules,
* supersession redirect, assembly-instruction step expansion or method steps.
*/
export type JobOperationStatus =
| "Todo"
| "Ready"
| "Waiting"
| "In Progress"
| "Paused"
| "Done"
| "Canceled";
export type CopyMethodResult = {
operations: number;
materials: number;
levels: number;
};
type Kind = "method" | "job" | "quote";
const TABLES: Record<
Kind,
{ operation: string; material: string; tool: string; parameter: string }
> = {
method: {
operation: "methodOperation",
material: "methodMaterial",
tool: "methodOperationTool",
parameter: "methodOperationParameter"
},
job: {
operation: "jobOperation",
material: "jobMaterial",
tool: "jobOperationTool",
parameter: "jobOperationParameter"
},
quote: {
operation: "quoteOperation",
material: "quoteMaterial",
tool: "quoteOperationTool",
parameter: "quoteOperationParameter"
}
};
// get-method gives job/quote rows fresh customFields.
const NEVER_COPIED = [
"id",
"makeMethodId",
"companyId",
"createdAt",
"createdBy",
"updatedAt",
"updatedBy",
"productionQuantity"
];
type SourceOperation = Row & {
id: string;
procedureId: string | null;
procedureFound: boolean;
procedureContent: unknown;
workInstruction: unknown;
operationUnitCost: string | null;
operationMinimumCost: string | null;
supplierMinimumCost: string;
supplierLeadTime: string;
laborRate: string;
machineRate: string;
overheadRate: string;
};
type SourceMaterial = Row & {
itemId: string;
methodType: string;
materialMakeMethodId: string | null;
quantity: string;
methodOperationId: string | null;
itemName: string;
itemTrackingType: string;
standardCost: string | null;
defaultStorageUnitId: string | null;
};
type Level = { makeMethodId: string; quantity: number };
type Target = {
kind: Kind;
locationId: string | null;
operation: (op: SourceOperation, level: Level) => Row;
material: (
material: SourceMaterial,
operationId: string | null,
level: Level
) => Row;
/** Absent for a method → method copy, which (like get-method's) copies one level. */
adoptChild?: (
materialId: string,
quantityPerParent: number
) => Promise<string>;
};
export async function copyMethodToJob(
ctx: Ctx,
jobId: string,
quantity: number,
status: JobOperationStatus = "Todo"
): Promise<CopyMethodResult> {
const root = await one<{ id: string; itemId: string }>(
ctx.client,
`SELECT id, "itemId" FROM "jobMakeMethod"
WHERE "jobId" = $1 AND "parentMaterialId" IS NULL AND "companyId" = $2`,
[jobId, ctx.companyId]
);
const target: Target = {
kind: "job",
locationId: null,
operation: (op, level) => ({
jobId,
jobMakeMethodId: level.makeMethodId,
operationMinimumCost: op.supplierMinimumCost,
workInstruction: op.procedureId
? (op.procedureContent ?? {})
: op.workInstruction,
operationQuantity: level.quantity,
targetQuantity: level.quantity,
status
}),
material: (material, operationId, level) => ({
jobId,
jobMakeMethodId: level.makeMethodId,
jobOperationId: operationId,
...materialCore(material),
quantity: material.quantity,
estimatedQuantity: Number(material.quantity) * level.quantity,
requiresSerialTracking: material.itemTrackingType === "Serial",
requiresBatchTracking: material.itemTrackingType === "Batch"
}),
adoptChild: async (materialId, quantityPerParent) =>
adopted(
await maybeOne<{ id: string }>(
ctx.client,
`UPDATE "jobMakeMethod" SET "quantityPerParent" = $4
WHERE "jobId" = $1 AND "parentMaterialId" = $2 AND "companyId" = $3
RETURNING id`,
[jobId, materialId, ctx.companyId, quantityPerParent]
),
`job material ${materialId}`
)
};
return copyTree(ctx, target, root, quantity);
}
export async function copyMethodToQuoteLine(
ctx: Ctx,
quoteId: string,
quoteLineId: string
): Promise<CopyMethodResult> {
const root = await one<{ id: string; itemId: string }>(
ctx.client,
`SELECT id, "itemId" FROM "quoteMakeMethod"
WHERE "quoteLineId" = $1 AND "parentMaterialId" IS NULL AND "companyId" = $2`,
[quoteLineId, ctx.companyId]
);
const { locationId } = await one<{ locationId: string | null }>(
ctx.client,
`SELECT "locationId" FROM quote WHERE id = $1 AND "companyId" = $2`,
[quoteId, ctx.companyId]
);
const target: Target = {
kind: "quote",
locationId,
// get-method keeps the method's own minimum cost on a quote operation.
operation: (op, level) => ({
quoteId,
quoteLineId,
quoteMakeMethodId: level.makeMethodId,
operationMinimumCost: op.operationMinimumCost ?? 0
}),
material: (material, operationId, level) => ({
quoteId,
quoteLineId,
quoteMakeMethodId: level.makeMethodId,
quoteOperationId: operationId,
...materialCore(material),
quantity: material.quantity,
storageUnitId: material.defaultStorageUnitId
}),
adoptChild: async (materialId) =>
adopted(
await maybeOne<{ id: string }>(
ctx.client,
`SELECT id FROM "quoteMakeMethod"
WHERE "quoteLineId" = $1 AND "parentMaterialId" = $2 AND "companyId" = $3`,
[quoteLineId, materialId, ctx.companyId]
),
`quote material ${materialId}`
)
};
return copyTree(ctx, target, root, 1);
}
/** Mirrors the app's `copyMakeMethod`. */
export async function copyMethodToMethod(
ctx: Ctx,
sourceMakeMethodId: string,
targetMakeMethodId: string
): Promise<void> {
const materialColumns = await sharedColumns(
ctx.client,
"methodMaterial",
"methodMaterial",
NEVER_COPIED
);
const target: Target = {
kind: "method",
locationId: null,
operation: (_op, level) => ({ makeMethodId: level.makeMethodId }),
material: (material, operationId, level) => ({
...pick(material, materialColumns),
makeMethodId: level.makeMethodId,
methodOperationId: operationId
})
};
await copyLevel(
ctx,
target,
sourceMakeMethodId,
{ makeMethodId: targetMakeMethodId, quantity: 1 },
{ operations: 0, materials: 0, levels: 0 },
new Set([sourceMakeMethodId])
);
}
async function copyTree(
ctx: Ctx,
target: Target,
root: { id: string; itemId: string },
quantity: number
): Promise<CopyMethodResult> {
const makeMethodId = await methodOfItem(ctx, root.itemId);
const result: CopyMethodResult = { operations: 0, materials: 0, levels: 0 };
await copyLevel(
ctx,
target,
makeMethodId,
{ makeMethodId: root.id, quantity },
result,
new Set([makeMethodId])
);
return result;
}
// Prefers Active; falls back to the newest version (a buy part's interceptor
// method stays Draft).
async function methodOfItem(ctx: Ctx, itemId: string): Promise<string> {
const row = await maybeOne<{ id: string }>(
ctx.client,
`SELECT id FROM "makeMethod"
WHERE "itemId" = $1 AND "companyId" = $2
ORDER BY (status = 'Active') DESC, version DESC
LIMIT 1`,
[itemId, ctx.companyId]
);
if (!row) throw new Error(`Seed: item ${itemId} has no make method to copy`);
return row.id;
}
function adopted(row: { id: string } | null, what: string): string {
if (!row) {
throw new Error(`Seed: ${what} has no interceptor child make method`);
}
return row.id;
}
function pick(row: Row, columns: string[]): Row {
return Object.fromEntries(columns.map((column) => [column, row[column]]));
}
function materialCore(material: SourceMaterial): Row {
return {
itemId: material.itemId,
itemType: material.itemType,
methodType: material.methodType,
kit: material.kit,
order: material.order,
description: material.itemName,
unitOfMeasureCode: material.unitOfMeasureCode,
unitCost: material.standardCost ?? 0
};
}
// `visited` holds the make methods on the current path: the DB only blocks a
// method listing itself, and a longer cycle would recurse inside the open
// transaction forever.
async function copyLevel(
ctx: Ctx,
target: Target,
makeMethodId: string,
level: Level,
result: CopyMethodResult,
visited: Set<string>
): Promise<void> {
result.levels += 1;
const operationIds = await copyOperations(ctx, target, makeMethodId, level);
result.operations += Object.keys(operationIds).length;
const materials = await rows<SourceMaterial>(
ctx.client,
`SELECT mm.*, i.name AS "itemName", i."itemTrackingType",
ic."standardCost", pm."defaultStorageUnitId"
FROM "methodMaterial" mm
JOIN item i ON i.id = mm."itemId" AND i."companyId" = mm."companyId"
LEFT JOIN "itemCost" ic
ON ic."itemId" = mm."itemId" AND ic."companyId" = mm."companyId"
LEFT JOIN "pickMethod" pm
ON pm."itemId" = mm."itemId" AND pm."locationId" = $3
AND pm."companyId" = mm."companyId"
WHERE mm."makeMethodId" = $1 AND mm."companyId" = $2
ORDER BY mm."order"`,
[makeMethodId, ctx.companyId, target.locationId]
);
for (const material of materials) {
const operationId = material.methodOperationId
? need(operationIds, material.methodOperationId, "copied operation")
: null;
const materialId = await insertId(
ctx,
TABLES[target.kind].material,
target.material(material, operationId, level)
);
result.materials += 1;
if (!target.adoptChild || material.methodType !== "Make to Order") continue;
const childMakeMethodId =
material.materialMakeMethodId ??
(await methodOfItem(ctx, material.itemId));
if (visited.has(childMakeMethodId)) {
throw new Error(
`Seed: make method ${childMakeMethodId} is its own subassembly (BOM cycle)`
);
}
const perParent = Number(material.quantity);
const childLevel = {
makeMethodId: await target.adoptChild(materialId, perParent),
quantity: perParent * level.quantity
};
await copyLevel(
ctx,
target,
childMakeMethodId,
childLevel,
result,
new Set([...visited, childMakeMethodId])
);
}
}
async function copyOperations(
ctx: Ctx,
target: Target,
makeMethodId: string,
level: Level
): Promise<Record<string, string>> {
const tables = TABLES[target.kind];
const columns = await sharedColumns(
ctx.client,
"methodOperation",
tables.operation,
target.kind === "method" ? NEVER_COPIED : [...NEVER_COPIED, "customFields"]
);
const operations = await rows<SourceOperation>(
ctx.client,
`SELECT mo.*,
p.id IS NOT NULL AS "procedureFound", p.content AS "procedureContent",
COALESCE(sp."minimumCost", avg_sp."minimumCost", 0) AS "supplierMinimumCost",
COALESCE(sp."leadTime", avg_sp."leadTime", 0) AS "supplierLeadTime",
COALESCE(wc."laborRate", 0) AS "laborRate",
COALESCE(wc."machineRate", 0) AS "machineRate",
COALESCE(wc."overheadRate", 0) AS "overheadRate"
FROM "methodOperation" mo
LEFT JOIN procedure p
ON p.id = mo."procedureId" AND p."companyId" = mo."companyId"
LEFT JOIN "supplierProcess" sp
ON sp.id = mo."operationSupplierProcessId" AND sp."companyId" = mo."companyId"
LEFT JOIN LATERAL (
SELECT avg(s."minimumCost") AS "minimumCost", avg(s."leadTime") AS "leadTime"
FROM "supplierProcess" s
WHERE s."processId" = mo."processId" AND s."companyId" = mo."companyId"
) avg_sp ON true
LEFT JOIN "workCenter" wc
ON wc.id = mo."workCenterId" AND wc."companyId" = mo."companyId"
WHERE mo."makeMethodId" = $1 AND mo."companyId" = $2
ORDER BY mo."order"`,
[makeMethodId, ctx.companyId]
);
const operationIds: Record<string, string> = {};
for (const op of operations) {
if (op.procedureId && !op.procedureFound) {
throw new Error(
`Seed: operation ${op.id} names procedure ${op.procedureId}, which is not this company's`
);
}
const runtime =
target.kind === "method"
? {}
: {
laborRate: op.laborRate,
machineRate: op.machineRate,
overheadRate: op.overheadRate,
operationUnitCost: op.operationUnitCost ?? 0,
operationLeadTime: op.supplierLeadTime
};
const operationId = await insertId(ctx, tables.operation, {
...pick(op, columns),
...runtime,
...target.operation(op, level)
});
operationIds[op.id] = operationId;
await copyOperationChildren(ctx, target.kind, op, operationId);
}
return operationIds;
}
async function copyOperationChildren(
ctx: Ctx,
kind: Kind,
op: SourceOperation,
operationId: string
): Promise<void> {
const tables = TABLES[kind];
const params = [op.id, operationId, ctx.companyId, ctx.userId];
await ctx.client.query(
`INSERT INTO "${tables.tool}"
("operationId", "toolId", "quantity", "companyId", "createdBy")
SELECT $2, t."toolId", t."quantity", $3, $4
FROM "methodOperationTool" t
WHERE t."operationId" = $1 AND t."companyId" = $3`,
params
);
if (!op.procedureId) {
await ctx.client.query(
`INSERT INTO "${tables.parameter}"
("operationId", "key", "value", "companyId", "createdBy")
SELECT $2, p."key", p."value", $3, $4
FROM "methodOperationParameter" p
WHERE p."operationId" = $1 AND p."companyId" = $3`,
params
);
return;
}
if (kind !== "job") return;
// insertProcedureDataForJobOperation: the procedure is snapshotted onto the
// job operation, so a later revision cannot rewrite what the floor was told.
const procedureParams = [
op.procedureId,
operationId,
ctx.companyId,
ctx.userId
];
await ctx.client.query(
`INSERT INTO "jobOperationStep"
("name", "required", "sortOrder", "type", "unitOfMeasureCode",
"minValue", "maxValue", "listValues", "fileTypes", "description",
"operationId", "companyId", "createdBy")
SELECT ps."name", ps."required", ps."sortOrder", ps."type", ps."unitOfMeasureCode",
ps."minValue", ps."maxValue", ps."listValues", ps."fileTypes", ps."description",
$2, $3, $4
FROM "procedureStep" ps
WHERE ps."procedureId" = $1 AND ps."companyId" = $3
ORDER BY ps."sortOrder"`,
procedureParams
);
await ctx.client.query(
`INSERT INTO "jobOperationParameter"
("operationId", "key", "value", "companyId", "createdBy")
SELECT $2, pp."key", pp."value", $3, $4
FROM "procedureParameter" pp
WHERE pp."procedureId" = $1 AND pp."companyId" = $3`,
procedureParams
);
}