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 {
type Database,
getLocationTimeZone as readLocationTimeZone
} from "@carbon/database";
import type { DB } from "@carbon/database/client";
import {
getJobMethodTree,
getJobMethodTreeArrayToTree,
type JobMethod,
type JobMethodTreeItem
} from "@carbon/database/methods";
import { groupBy } from "@carbon/utils";
import { parseDate } from "@internationalized/date";
import type { SupabaseClient } from "@supabase/supabase-js";
import { type Kysely, type Selectable, sql } from "kysely";
import { type CalendarWindow, subtractIntervals } from "./calendar-utils.ts";
import {
businessDayFromMs,
msToInstantIso,
toInstantIso,
toInstantMs,
toIsoDate
} from "./date-utils.ts";
import {
closeHolidays,
downtimeOutages,
type LadderShiftRow,
resolveLocationWindows,
resolveWorkCenterWindows,
type WorkCenterAvailabilityInput
} from "./machine-availability.ts";
import {
type JobWrites,
mergeCrossJobOperations,
visibleReservations
} from "./run-overlay.ts";
import {
type BaseOperation,
capacityHoldingJobStatuses,
type Job,
type JobOperationDependency
} from "./types.ts";
// peopleAssignment.date is a plant-calendar day — resolve the range instants to
// days in the plant's own timezone, padded one day each side so an overnight
// shift row whose windows spill past its calendar day is never cut at the
// range boundary.
const peopleDateLowerBound = (instant: number, timeZone: string) =>
parseDate(businessDayFromMs(instant, timeZone))
.subtract({ days: 1 })
.toString();
const peopleDateUpperBound = (instant: number, timeZone: string) =>
parseDate(businessDayFromMs(instant, timeZone)).add({ days: 1 }).toString();
export type JobMaterialWithMakeMethod = {
id: string | null;
jobMaterialMakeMethodId: string | null;
jobOperationId: string | null;
};
export type UnassignedMaterial = {
id: string | null;
jobMakeMethodId: string | null;
};
export type UnlinkedMaterial = {
id: string | null;
jobMakeMethodId: string;
};
export type RootMakeMethod = {
id: string | null;
itemId: string | null;
};
export type ProcessWorkCenters = {
id: string | null;
workCenters: string[] | null;
};
export type ActiveWorkCenter = {
id: string | null;
locationId: string | null;
};
export type CrossJobOperation = {
id: string | null;
dueDate: string | null;
startDate: string | null;
priority: number | null;
deadlineType: Database["public"]["Enums"]["deadlineType"] | null;
jobPriority: number | null;
workCenterId: string | null;
createdAt: Date | string | null;
projectedCompletionAt: Date | string | null;
setupTime: number | null;
setupUnit: Database["public"]["Enums"]["factor"] | null;
laborTime: number | null;
laborUnit: Database["public"]["Enums"]["factor"] | null;
machineTime: number | null;
machineUnit: Database["public"]["Enums"]["factor"] | null;
operationQuantity: number | null;
operationLeadTime: number | null;
};
export type LiveReservation = {
/** "OperatorPool" is legacy — read-tolerated, never written anymore. */
resourceKind: "WorkCenter" | "OperatorPool" | "Employee";
resourceId: string;
/** epoch-ms */
startAt: number;
/** epoch-ms */
endAt: number;
jobId: string;
/** Human-readable job number (job."jobId", e.g. J000001) for conflict messages */
readableJobId: string;
/** Set on a coalesced batch reservation (the batch pre-pass owns those rows). */
jobOperationBatchId: string | null;
};
/** A process that requires an ability, with its 1:1 linked ability. */
export type ProcessRequirementRow = {
processId: string;
abilityId: string;
abilityName: string;
};
export type QualifiedEmployeeRow = {
abilityId: string;
employeeId: string;
expiresAt: string | null;
};
/** One weekday window of an employee's assigned shift (employeeShift ⋈ shift). */
export type EmployeeShiftRow = {
employeeId: string;
dayOfWeek: number; // 0 = Sunday .. 6 = Saturday
startTime: string;
endTime: string;
timezone: string;
};
// Row types live in people-utils.ts (pure module) so its tests don't pull the
// DB dependency graph
import {
type PeopleAbsenceRow,
type PeopleAssignmentRow,
subtractDates
} from "./people-utils.ts";
export type { PeopleAbsenceRow, PeopleAssignmentRow };
/**
* Master Data Provider
* The single read seam for the scheduling engine. All master/transactional
* reads go through this interface so the engine can later be pointed at
* "live ⊕ scenario overrides" without touching the placement logic.
* Writes stay on the concrete Kysely client.
*/
export interface MasterDataProvider {
getJob(jobId: string): Promise<Job | undefined>;
getOperations(
jobId: string,
opts?: { includeDone?: boolean }
): Promise<BaseOperation[]>;
getDependencies(jobId: string): Promise<JobOperationDependency[]>;
getReworkDependencies(
jobId: string,
reworkOpIds: string[]
): Promise<JobOperationDependency[]>;
getMaterialsWithMakeMethod(
makeMethodIds: string[]
): Promise<JobMaterialWithMakeMethod[]>;
getUnassignedMakeToOrderMaterials(
makeMethodIds: string[]
): Promise<UnassignedMaterial[]>;
getUnlinkedMaterials(jobId: string): Promise<UnlinkedMaterial[]>;
getRootMakeMethod(jobId: string): Promise<RootMakeMethod | undefined>;
getJobMethodTree(
methodId: string
): Promise<{ data: JobMethodTreeItem[] | null; error: unknown }>;
/** The location's timezone, falling back to the company's. */
getLocationTimeZone(locationId: string): Promise<string>;
getProcessesWithWorkCenters(): Promise<ProcessWorkCenters[]>;
getActiveWorkCenters(locationId: string): Promise<ActiveWorkCenter[]>;
getCrossJobOperationsAtWorkCenters(
workCenterIds: string[]
): Promise<CrossJobOperation[]>;
// ---- finite-capacity reads ----
getLiveReservations(
fromDate: number,
excludeJobIds: string[]
): Promise<LiveReservation[]>;
/** The subset of the given operation ids that have ≥1 production event
* (used by remaining-work netting to decide setup is already done). */
getOperationsWithEvents(operationIds: string[]): Promise<Set<string>>;
getProcessRequirements(
processIds: string[]
): Promise<ProcessRequirementRow[]>;
getQualifiedEmployees(abilityIds: string[]): Promise<QualifiedEmployeeRow[]>;
getEmployeeShiftWindows(employeeIds: string[]): Promise<EmployeeShiftRow[]>;
/**
* Machine-availability ladder per work center: explicit workCenterShift rows
* → the location's shifts → a stock Mon–Fri 08:00–16:00 week; or one open
* window for an `alwaysOn` machine. Returns id → open windows.
*/
getWorkCenterAvailability(
workCenterIds: string[],
rangeStart: number,
rangeEnd: number
): Promise<Map<string, CalendarWindow[]>>;
/**
* A location's default calendar (rung 2/3) — the fallback availability for
* people with no `employeeShift` rows (plant hours, not 24×7).
*/
getLocationCalendarWindows(
locationId: string,
rangeStart: number,
rangeEnd: number
): Promise<CalendarWindow[]>;
/** The location's `requiresStaffing` scheduling policy (default false). */
getLocationRequiresStaffing(locationId: string): Promise<boolean>;
/** The subset of the given work centers flagged `alwaysOn` (lights-out). */
getAlwaysOnWorkCenterIds(workCenterIds: string[]): Promise<Set<string>>;
getPeopleAssignments(
rangeStart: number,
rangeEnd: number,
timeZone: string
): Promise<PeopleAssignmentRow[]>;
getPeopleAbsences(
rangeStart: number,
rangeEnd: number,
timeZone: string
): Promise<PeopleAbsenceRow[]>;
/**
* The company's holiday dates ("YYYY-MM-DD") around [rangeStart, rangeEnd].
* A holiday closes every shift-based work center and is a day off for
* every person.
*/
getHolidayDates(rangeStart: number, rangeEnd: number): Promise<Set<string>>;
}
function toJob<
T extends {
dueDate: unknown;
timezone: string | null;
projectedCompletionAt: unknown;
}
>(job: T) {
// pg returns DATE columns as JS Date objects; every consumer compares
// dueDate lexicographically as "YYYY-MM-DD" (a Date silently fails those
// comparisons — string > Date is always false)
return {
...job,
dueDate: toIsoDate(job.dueDate),
timezone: job.timezone ?? "UTC",
projectedCompletionAt:
job.projectedCompletionAt == null
? null
: toInstantIso(job.projectedCompletionAt as unknown as Date | string)
};
}
type Row<T extends keyof DB> = Selectable<DB[T]>;
/**
* What the jobs of a location run have computed so far. Nothing is written
* until the run ends, so a later job reads the earlier jobs' results here.
*/
type RunOverlay = {
now: number;
/** Rows no job of the run rewrites: other jobs' and batch-tagged ones. */
fixedReservations: LiveReservation[];
/** Per job of the run: its stored rows, then its new ones once it has run. */
reservationsByJob: Map<string, LiveReservation[]>;
storedOperationsByWorkCenter: Map<string, CrossJobOperation[]>;
placedOperations: Map<string, CrossJobOperation>;
};
type JobPreload = {
jobIds: Set<string>;
jobs: Map<string, Job>;
operations: Record<string, Row<"jobOperation">[]>;
dependencies: Record<string, Row<"jobOperationDependency">[]>;
unlinked: Record<
string,
{ id: string; jobMakeMethodId: string; methodType: string }[]
>;
materials: Row<"jobMaterialWithMakeMethodId">[];
jobIdByMakeMethodId: Map<string, string>;
rootByJobId: Map<string, { id: string; itemId: string | null }>;
treeByRootId: Record<string, JobMethod[]>;
operationIds: Set<string>;
operationsWithEvents: Set<string>;
};
/**
* Live implementation backed by Kysely (and the Supabase client for the
* job-method-tree RPC). Queries are moved verbatim from the engine,
* work-center selector, assembly handler, and material manager.
*/
export class KyselyMasterDataProvider implements MasterDataProvider {
private db: Kysely<DB>;
private client: SupabaseClient<Database>;
private companyId: string;
/**
* Batch mode: when several jobs are scheduled in one invocation, the
* company's STATIC master data (processes, work centers, qualifications,
* shift windows, dispatch policies) is identical for every job — cache it
* on first read instead of re-querying per job. Job-scoped rows (operations,
* dependencies, method trees) are read once for the batch by `preloadJobs`.
* Live reservations and other jobs' operations change as the batch runs:
* `beginRun` reads them once and `recordJob` keeps them current.
*/
private companyCache: Map<string, Promise<unknown>> | null = null;
private preload: JobPreload | null = null;
private run: RunOverlay | null = null;
private processRequirements = new Map<
string,
Promise<ProcessRequirementRow[]>
>();
constructor(
db: Kysely<DB>,
client: SupabaseClient<Database>,
companyId: string,
options?: { cacheCompanyData?: boolean }
) {
this.db = db;
this.client = client;
this.companyId = companyId;
if (options?.cacheCompanyData) {
this.companyCache = new Map();
}
}
private cached<T>(key: string, load: () => Promise<T>): Promise<T> {
if (!this.companyCache) return load();
let hit = this.companyCache.get(key);
if (!hit) {
hit = load();
this.companyCache.set(key, hit);
}
return hit as Promise<T>;
}
/**
* Read every job-scoped row the engine needs for these jobs in one query per
* table, so a location run does not repeat ~15 reads per job. Call it after
* anything that writes those rows for the batch (the batch pre-pass). The
* engine writes nothing while the batch runs, so the rows stay the stored
* state for the whole run.
*/
async preloadJobs(jobIds: string[]): Promise<void> {
if (jobIds.length === 0) return;
const [jobs, operations, dependencies, makeMethods, unlinked, materials] =
await Promise.all([
this.jobQuery().where("job.id", "in", jobIds).execute(),
this.db
.selectFrom("jobOperation")
.selectAll()
.where("jobId", "in", jobIds)
.where("companyId", "=", this.companyId)
.orderBy("order")
.execute(),
this.db
.selectFrom("jobOperationDependency")
.selectAll()
.where("jobId", "in", jobIds)
.where("companyId", "=", this.companyId)
.execute(),
this.db
.selectFrom("jobMakeMethod")
.select(["id", "itemId", "jobId", "parentMaterialId"])
.where("jobId", "in", jobIds)
.where("companyId", "=", this.companyId)
.execute(),
this.db
.selectFrom("jobMaterial")
.select(["id", "jobMakeMethodId", "jobId", "methodType"])
.where("jobId", "in", jobIds)
.where("companyId", "=", this.companyId)
.where("jobOperationId", "is", null)
.execute(),
this.db
.selectFrom("jobMaterialWithMakeMethodId")
.selectAll()
.where("jobId", "in", jobIds)
.where("companyId", "=", this.companyId)
.execute()
]);
const roots = makeMethods.filter((m) => m.parentMaterialId === null);
const rootIds = roots.map((m) => m.id);
const operationIds = operations.map((o) => o.id);
const [trees, withEvents] = await Promise.all([
rootIds.length === 0
? { rows: [] as (JobMethod & { rootMethodId: string })[] }
: sql<JobMethod & { rootMethodId: string }>`
SELECT r.id AS "rootMethodId", t.*
FROM unnest(${rootIds}::text[]) AS r(id),
LATERAL get_job_methods_by_method_id(r.id) WITH ORDINALITY AS t
ORDER BY r.id, t.ordinality
`.execute(this.db),
this.readOperationsWithEvents(operationIds)
]);
const preload: JobPreload = {
jobs: new Map(jobs.map((j) => [j.id as string, toJob(j)])),
operations: groupBy(operations, (o) => o.jobId as string),
dependencies: groupBy(dependencies, (d) => d.jobId),
unlinked: groupBy(unlinked, (m) => m.jobId),
materials,
jobIdByMakeMethodId: new Map(makeMethods.map((m) => [m.id, m.jobId])),
rootByJobId: new Map(roots.map((m) => [m.jobId, m])),
treeByRootId: groupBy(trees.rows, (t) => t.rootMethodId),
operationIds: new Set(operationIds),
operationsWithEvents: withEvents,
jobIds: new Set(jobIds)
};
this.preload = preload;
}
private jobQuery() {
return this.db
.selectFrom("job")
.leftJoin("location", "location.id", "job.locationId")
.select([
"job.id",
"job.status",
"job.dueDate",
"job.deadlineType",
"job.locationId",
"job.priority",
"job.jobId as readableJobId",
"job.assignee",
"job.projectedCompletionAt",
"location.timezone"
])
.where("job.companyId", "=", this.companyId);
}
async getJob(jobId: string): Promise<Job | undefined> {
if (this.preload?.jobIds.has(jobId)) return this.preload.jobs.get(jobId);
const job = await this.jobQuery()
.where("job.id", "=", jobId)
.executeTakeFirst();
return job && toJob(job);
}
async getOperations(
jobId: string,
opts?: { includeDone?: boolean }
): Promise<BaseOperation[]> {
if (this.preload?.jobIds.has(jobId)) {
const all = this.preload.operations[jobId] ?? [];
return (
opts?.includeDone
? all
: all.filter((o) => o.status !== "Done" && o.status !== "Canceled")
).map((o) => ({ ...o })) as BaseOperation[];
}
let query = this.db
.selectFrom("jobOperation")
.selectAll()
.where("jobId", "=", jobId);
if (!opts?.includeDone) {
query = query.where("status", "not in", ["Done", "Canceled"]);
}
return (await query.orderBy("order").execute()) as BaseOperation[];
}
async getDependencies(jobId: string): Promise<JobOperationDependency[]> {
const deps = this.preload?.jobIds.has(jobId)
? (this.preload.dependencies[jobId] ?? [])
: await this.db
.selectFrom("jobOperationDependency")
.selectAll()
.where("jobId", "=", jobId)
.execute();
return deps.map((d) => ({
operationId: d.operationId,
dependsOnId: d.dependsOnId,
jobId: d.jobId
}));
}
async getReworkDependencies(
jobId: string,
reworkOpIds: string[]
): Promise<JobOperationDependency[]> {
if (reworkOpIds.length === 0) {
return [];
}
const deps = await this.db
.selectFrom("jobOperationDependency")
.selectAll()
.where("jobId", "=", jobId)
.where((eb) =>
eb.or([
eb("operationId", "in", reworkOpIds),
eb("dependsOnId", "in", reworkOpIds)
])
)
.execute();
return deps.map((d) => ({
operationId: d.operationId,
dependsOnId: d.dependsOnId,
jobId: d.jobId
}));
}
async getMaterialsWithMakeMethod(
makeMethodIds: string[]
): Promise<JobMaterialWithMakeMethod[]> {
if (makeMethodIds.length === 0) {
return [];
}
if (this.arePreloaded(makeMethodIds)) {
const ids = new Set(makeMethodIds);
return this.preload!.materials.filter(
(m) => m.jobMakeMethodId && ids.has(m.jobMakeMethodId)
);
}
return await this.db
.selectFrom("jobMaterialWithMakeMethodId")
.selectAll()
.where("jobMakeMethodId", "in", makeMethodIds)
.execute();
}
async getUnassignedMakeToOrderMaterials(
makeMethodIds: string[]
): Promise<UnassignedMaterial[]> {
if (makeMethodIds.length === 0) {
return [];
}
if (this.arePreloaded(makeMethodIds)) {
const ids = new Set(makeMethodIds);
return Object.values(this.preload!.unlinked)
.flat()
.filter(
(m) => m.methodType === "Make to Order" && ids.has(m.jobMakeMethodId)
);
}
return await this.db
.selectFrom("jobMaterial")
.select(["id", "jobMakeMethodId"])
.where("jobMakeMethodId", "in", makeMethodIds)
.where("methodType", "=", "Make to Order")
.where("jobOperationId", "is", null)
.execute();
}
/**
* True when these ids come from a preloaded job's method tree. The tree
* also carries the ids of its bought materials, which are no make method's
* id and match no row, so one known make method is enough to tell.
*/
private arePreloaded(makeMethodIds: string[]): boolean {
const preload = this.preload;
if (!preload) return false;
return makeMethodIds.some((id) => preload.jobIdByMakeMethodId.has(id));
}
async getUnlinkedMaterials(jobId: string): Promise<UnlinkedMaterial[]> {
if (this.preload?.jobIds.has(jobId)) {
return (this.preload.unlinked[jobId] ?? []).map((m) => ({
id: m.id,
jobMakeMethodId: m.jobMakeMethodId
}));
}
return await this.db
.selectFrom("jobMaterial")
.select(["id", "jobMakeMethodId"])
.where("jobId", "=", jobId)
.where("jobOperationId", "is", null)
.execute();
}
async getRootMakeMethod(jobId: string): Promise<RootMakeMethod | undefined> {
if (this.preload?.jobIds.has(jobId)) {
const root = this.preload.rootByJobId.get(jobId);
return root && { id: root.id, itemId: root.itemId };
}
return await this.db
.selectFrom("jobMakeMethod")
.select(["id", "itemId"])
.where("jobId", "=", jobId)
.where("parentMaterialId", "is", null)
.executeTakeFirst();
}
async getJobMethodTree(
methodId: string
): Promise<{ data: JobMethodTreeItem[] | null; error: unknown }> {
const rows = this.preload?.treeByRootId[methodId];
if (rows) return { data: getJobMethodTreeArrayToTree(rows), error: null };
return await getJobMethodTree(this.db, methodId);
}
getLocationTimeZone(locationId: string): Promise<string> {
return this.cached(`locationTimeZone:${locationId}`, () =>
readLocationTimeZone(this.db, locationId, this.companyId)
);
}
async getProcessesWithWorkCenters(): Promise<ProcessWorkCenters[]> {
return this.cached("processesWithWorkCenters", () =>
this.loadProcessesWithWorkCenters()
);
}
private async loadProcessesWithWorkCenters(): Promise<ProcessWorkCenters[]> {
return await this.db
.selectFrom("processes")
.select(["id", "workCenters"])
.where("companyId", "=", this.companyId)
.execute();
}
async getActiveWorkCenters(locationId: string): Promise<ActiveWorkCenter[]> {
return this.cached(`activeWorkCenters:${locationId}`, () =>
this.loadActiveWorkCenters(locationId)
);
}
private async loadActiveWorkCenters(
locationId: string
): Promise<ActiveWorkCenter[]> {
return await this.db
.selectFrom("workCenter")
.select(["id", "locationId"])
.where("locationId", "=", locationId)
.where("companyId", "=", this.companyId)
.where("active", "=", true)
.execute();
}
async getCrossJobOperationsAtWorkCenters(
workCenterIds: string[]
): Promise<CrossJobOperation[]> {
if (workCenterIds.length === 0) {
return [];
}
const run = this.run;
if (!run) return this.readCrossJobOperations(workCenterIds);
const missing = workCenterIds.filter(
(id) => !run.storedOperationsByWorkCenter.has(id)
);
if (missing.length > 0) {
const rows = groupBy(
await this.readCrossJobOperations(missing),
(op) => op.workCenterId as string
);
for (const id of missing) {
run.storedOperationsByWorkCenter.set(id, rows[id] ?? []);
}
}
return mergeCrossJobOperations(
workCenterIds.flatMap(
(id) => run.storedOperationsByWorkCenter.get(id) ?? []
),
run.placedOperations,
workCenterIds
);
}
private async readCrossJobOperations(
workCenterIds: string[]
): Promise<CrossJobOperation[]> {
return await this.db
.selectFrom("jobOperation as jo")
.innerJoin("job as j", "j.id", "jo.jobId")
.select([
"jo.id",
"jo.dueDate",
"jo.startDate",
"jo.priority",
"j.deadlineType",
"j.priority as jobPriority",
"jo.workCenterId",
"jo.createdAt",
"jo.projectedCompletionAt",
"jo.setupTime",
"jo.setupUnit",
"jo.laborTime",
"jo.laborUnit",
"jo.machineTime",
"jo.machineUnit",
"jo.operationQuantity",
"jo.operationLeadTime"
])
.where("jo.companyId", "=", this.companyId)
.where("jo.workCenterId", "in", workCenterIds)
.where("jo.status", "not in", ["Done", "Canceled"])
// Ops can outlive their job's lifecycle (cancelling a job does not
// cancel its ops) — terminal jobs must not compete in dispatch order
.where("j.status", "not in", ["Cancelled", "Completed", "Closed"])
.execute();
}
async getLiveReservations(
fromDate: number,
excludeJobIds: string[]
): Promise<LiveReservation[]> {
const run = this.run;
if (!run || run.now !== fromDate) {
return this.readLiveReservations(fromDate, excludeJobIds);
}
const excluded = new Set(excludeJobIds);
const rows = [...run.fixedReservations];
for (const [jobId, reservations] of run.reservationsByJob) {
if (!excluded.has(jobId)) rows.push(...reservations);
}
return rows;
}
/**
* Start a location run over these jobs: read the live reservations once.
* Call it after the batch pre-pass, which rewrites the batch-tagged rows.
*/
async beginRun(jobIds: string[], now: number): Promise<void> {
const batch = new Set(jobIds);
const fixedReservations: LiveReservation[] = [];
const reservationsByJob = new Map<string, LiveReservation[]>();
for (const row of await this.readLiveReservations(now, [])) {
// The same split as the read's excludeJobIds filter: a batch-tagged row
// is never excluded.
if (row.jobOperationBatchId !== null || !batch.has(row.jobId)) {
fixedReservations.push(row);
continue;
}
const rows = reservationsByJob.get(row.jobId);
if (rows) rows.push(row);
else reservationsByJob.set(row.jobId, [row]);
}
this.run = {
now,
fixedReservations,
reservationsByJob,
storedOperationsByWorkCenter: new Map(),
placedOperations: new Map()
};
}
/** Make a job's result visible to the jobs that run after it. */
recordJob(writes: JobWrites): void {
const run = this.run;
const job = this.preload?.jobs.get(writes.jobId);
if (!run || !job) return;
run.reservationsByJob.set(
writes.jobId,
visibleReservations(writes, job.readableJobId ?? "", run.now)
);
// Terminal jobs never compete in dispatch order (the stored read's filter).
if (["Cancelled", "Completed", "Closed"].includes(job.status ?? "")) return;
const stored = new Map(
(this.preload?.operations[writes.jobId] ?? []).map((o) => [o.id, o])
);
for (const { id, placement } of writes.placements) {
const op = stored.get(id);
if (!op) continue;
run.placedOperations.set(id, {
id,
dueDate:
placement.dueDate !== undefined
? (placement.dueDate as string | null)
: (op.dueDate as string | null),
startDate: placement.startDate as string | null,
priority:
placement.priority !== undefined
? (placement.priority as number)
: op.priority,
deadlineType: job.deadlineType ?? null,
jobPriority: job.priority ?? null,
workCenterId: placement.workCenterId as string | null,
createdAt: op.createdAt,
projectedCompletionAt: placement.projectedCompletionAt as string | null,
setupTime: op.setupTime,
setupUnit: op.setupUnit,
laborTime: op.laborTime,
laborUnit: op.laborUnit,
machineTime: op.machineTime,
machineUnit: op.machineUnit,
operationQuantity: op.operationQuantity,
operationLeadTime: op.operationLeadTime
});
}
}
private async readLiveReservations(
fromDate: number,
excludeJobIds: string[]
): Promise<LiveReservation[]> {
let query = this.db
.selectFrom("capacityReservation as cr")
.innerJoin("job as j", "j.id", "cr.jobId")
.select([
"cr.resourceKind",
"cr.resourceId",
"cr.startAt",
"cr.endAt",
"cr.jobId",
"cr.jobOperationBatchId",
"j.jobId as readableJobId"
])
.where("cr.companyId", "=", this.companyId)
.where("cr.scenarioId", "is", null)
// Placeholder reservations are display-only markers for unplaceable ops —
// they must never hold capacity against other jobs or the next regen.
.where("cr.isPlaceholder", "is not", true)
.where("cr.endAt", ">", msToInstantIso(fromDate));
// Exclude the whole batch: each engine run sees only non-batch reservations
// plus the in-run placements of already-run batch jobs (no pre-clear step).
// Rows tagged with a jobOperationBatchId are never excluded: a batch
// reservation is already placed and must stay visible to member jobs' runs.
if (excludeJobIds.length > 0) {
query = query.where((eb) =>
eb.or([
eb("cr.jobId", "not in", excludeJobIds),
eb("cr.jobOperationBatchId", "is not", null)
])
);
}
const rows = await query
// Reservations are only deleted when their job is rescheduled, so
// rows from jobs outside these statuses linger (terminal jobs) or
// pre-date release (Draft/Planned) — neither may hold capacity
// against live jobs. A BATCH-tagged row is the exception: the batch
// holds capacity by virtue of the BATCH being released, and its anchor
// job may legitimately still be Draft/Planned (a Released batch pulls
// members ahead of their jobs).
.where((eb) =>
eb.or([
eb("j.status", "in", [...capacityHoldingJobStatuses]),
eb("cr.jobOperationBatchId", "is not", null)
])
)
.execute();
return rows.map((r) => ({
resourceKind: r.resourceKind as LiveReservation["resourceKind"],
resourceId: r.resourceId,
startAt: toInstantMs(r.startAt as unknown as Date | string),
endAt: toInstantMs(r.endAt as unknown as Date | string),
jobId: r.jobId,
readableJobId: r.readableJobId,
jobOperationBatchId: r.jobOperationBatchId ?? null
}));
}
async getOperationsWithEvents(operationIds: string[]): Promise<Set<string>> {
const preload = this.preload;
if (preload && operationIds.every((id) => preload.operationIds.has(id))) {
return new Set(
operationIds.filter((id) => preload.operationsWithEvents.has(id))
);
}
return this.readOperationsWithEvents(operationIds);
}
private async readOperationsWithEvents(
operationIds: string[]
): Promise<Set<string>> {
const result = new Set<string>();
if (operationIds.length === 0) {
return result;
}
// Chunk the IN list — one query per chunk, never per-row (N+1 rule).
const CHUNK = 200;
for (let i = 0; i < operationIds.length; i += CHUNK) {
const chunk = operationIds.slice(i, i + CHUNK);
const rows = await this.db
.selectFrom("productionEvent")
.select("jobOperationId")
.distinct()
.where("jobOperationId", "in", chunk)
.where("companyId", "=", this.companyId)
.execute();
for (const r of rows) {
if (r.jobOperationId) result.add(r.jobOperationId);
}
}
return result;
}
getLocationRequiresStaffing(locationId: string): Promise<boolean> {
return this.cached(`locationRequiresStaffing:${locationId}`, async () => {
const row = await this.db
.selectFrom("location")
.select("requiresStaffing")
.where("id", "=", locationId)
.where("companyId", "=", this.companyId)
.executeTakeFirst();
return !!row?.requiresStaffing;
});
}
getAlwaysOnWorkCenterIds(workCenterIds: string[]): Promise<Set<string>> {
return this.cached(
`alwaysOnWorkCenters:${[...workCenterIds].sort().join(",")}`,
async () => {
const result = new Set<string>();
if (workCenterIds.length === 0) return result;
const rows = await this.db
.selectFrom("workCenter")
.select("id")
.where("id", "in", workCenterIds)
.where("companyId", "=", this.companyId)
.where("alwaysOn", "=", true)
.execute();
for (const r of rows) result.add(r.id);
return result;
}
);
}
async getProcessRequirements(
processIds: string[]
): Promise<ProcessRequirementRow[]> {
if (!this.companyCache) return this.loadProcessRequirements(processIds);
// Cached per process, so two jobs sharing a process share its read.
const missing = processIds.filter(
(id) => !this.processRequirements.has(id)
);
if (missing.length > 0) {
const loaded = this.loadProcessRequirements(missing);
for (const id of missing) {
this.processRequirements.set(
id,
loaded.then((rows) => rows.filter((r) => r.processId === id))
);
}
}
const rows = await Promise.all(
processIds.map((id) => this.processRequirements.get(id)!)
);
return rows.flat();
}
private async loadProcessRequirements(
processIds: string[]
): Promise<ProcessRequirementRow[]> {
if (processIds.length === 0) {
return [];
}
const rows = await this.db
.selectFrom("process as p")
.innerJoin("ability as a", (join) =>
join
.onRef("a.processId", "=", "p.id")
.on("a.companyId", "=", this.companyId)
.on("a.active", "=", true)
)
.select([
"p.id as processId",
"a.id as abilityId",
// The ability's name IS the process's name (abilities store none).
"p.name as abilityName"
])
.where("p.id", "in", processIds)
.where("p.companyId", "=", this.companyId)
.where("p.requiresAbility", "=", true)
.execute();
return rows.map((r) => ({
processId: r.processId,
abilityId: r.abilityId,
abilityName: r.abilityName
}));
}
async getQualifiedEmployees(
abilityIds: string[]
): Promise<QualifiedEmployeeRow[]> {
return this.cached(
`qualifiedEmployees:${[...abilityIds].sort().join(",")}`,
() => this.loadQualifiedEmployees(abilityIds)
);
}
private async loadQualifiedEmployees(
abilityIds: string[]
): Promise<QualifiedEmployeeRow[]> {
if (abilityIds.length === 0) {
return [];
}
const rows = await this.db
.selectFrom("employeeAbility as ea")
.select(["ea.abilityId", "ea.employeeId", "ea.expiresAt"])
.where("ea.abilityId", "in", abilityIds)
.where("ea.companyId", "=", this.companyId)
.execute();
return rows.map((r) => ({
abilityId: r.abilityId,
employeeId: r.employeeId,
expiresAt: toIsoDate(r.expiresAt)
}));
}
async getEmployeeShiftWindows(
employeeIds: string[]
): Promise<EmployeeShiftRow[]> {
return this.cached(
`employeeShiftWindows:${[...employeeIds].sort().join(",")}`,
() => this.loadEmployeeShiftWindows(employeeIds)
);
}
private async loadEmployeeShiftWindows(
employeeIds: string[]
): Promise<EmployeeShiftRow[]> {
if (employeeIds.length === 0) {
return [];
}
const rows = await this.db
.selectFrom("employeeShift as es")
.innerJoin("shift as s", "s.id", "es.shiftId")
.leftJoin("location as l", "l.id", "s.locationId")
.select([
"es.employeeId",
"s.startTime",
"s.endTime",
"s.sunday",
"s.monday",
"s.tuesday",
"s.wednesday",
"s.thursday",
"s.friday",
"s.saturday",
"l.timezone"
])
.where("es.employeeId", "in", employeeIds)
.where("es.companyId", "=", this.companyId)
.where("s.active", "=", true)
.execute();
const result: EmployeeShiftRow[] = [];
for (const r of rows) {
const days = [
r.sunday,
r.monday,
r.tuesday,
r.wednesday,
r.thursday,
r.friday,
r.saturday
];
for (let dayOfWeek = 0; dayOfWeek < 7; dayOfWeek++) {
if (!days[dayOfWeek]) continue;
result.push({
employeeId: r.employeeId,
dayOfWeek,
startTime: String(r.startTime),
endTime: String(r.endTime),
timezone: r.timezone ?? "UTC"
});
}
}
return result;
}
/** Flatten a shift row's weekday booleans into one LadderShiftRow per day. */
private expandShiftDays(row: {
startTime: unknown;
endTime: unknown;
timezone: string | null;
sunday: boolean | null;
monday: boolean | null;
tuesday: boolean | null;
wednesday: boolean | null;
thursday: boolean | null;
friday: boolean | null;
saturday: boolean | null;
}): LadderShiftRow[] {
const days = [
row.sunday,
row.monday,
row.tuesday,
row.wednesday,
row.thursday,
row.friday,
row.saturday
];
const out: LadderShiftRow[] = [];
for (let dayOfWeek = 0; dayOfWeek < 7; dayOfWeek++) {
if (!days[dayOfWeek]) continue;
out.push({
dayOfWeek,
startTime: String(row.startTime),
endTime: String(row.endTime),
timezone: row.timezone ?? "UTC"
});
}
return out;
}
async getWorkCenterAvailability(
workCenterIds: string[],
rangeStart: number,
rangeEnd: number
): Promise<Map<string, CalendarWindow[]>> {
if (workCenterIds.length === 0) {
return new Map();
}
const cache = this.companyCache;
if (!cache) {
return this.loadWorkCenterAvailability(
workCenterIds,
rangeStart,
rangeEnd
);
}
// Cached per work center: jobs in a batch ask for overlapping sets, and a
// work center's windows do not depend on which others were asked for.
const key = (id: string) =>
`workCenterAvailability:${id}:${rangeStart}:${rangeEnd}`;
const missing = workCenterIds.filter((id) => !cache.has(key(id)));
if (missing.length > 0) {
const loaded = this.loadWorkCenterAvailability(
missing,
rangeStart,
rangeEnd
);
for (const id of missing) {
cache.set(
key(id),
loaded.then((windows) => windows.get(id))
);
}
}
const result = new Map<string, CalendarWindow[]>();
for (const id of workCenterIds) {
const windows = (await cache.get(key(id))) as
| CalendarWindow[]
| undefined;
if (windows) result.set(id, windows);
}
return result;
}
private async loadWorkCenterAvailability(
workCenterIds: string[],
rangeStart: number,
rangeEnd: number
): Promise<Map<string, CalendarWindow[]>> {
// a. work centers with their lights-out flag + location timezone
const wcRows = await this.db
.selectFrom("workCenter as wc")
.leftJoin("location as l", "l.id", "wc.locationId")
.select(["wc.id", "wc.alwaysOn", "wc.locationId", "l.timezone"])
.where("wc.id", "in", workCenterIds)
.where("wc.companyId", "=", this.companyId)
.execute();
const workCenters: WorkCenterAvailabilityInput[] = wcRows.map((r) => ({
id: r.id,
alwaysOn: !!r.alwaysOn,
locationId: r.locationId ?? null,
timezone: r.timezone ?? "UTC"
}));
// b. explicit work-center shifts (rung 1)
const wcShiftRaw = await this.db
.selectFrom("workCenterShift as wcs")
.innerJoin("shift as s", "s.id", "wcs.shiftId")
.leftJoin("location as l", "l.id", "s.locationId")
.select([
"wcs.workCenterId",
"s.startTime",
"s.endTime",
"s.sunday",
"s.monday",
"s.tuesday",
"s.wednesday",
"s.thursday",
"s.friday",
"s.saturday",
"l.timezone"
])
.where("wcs.workCenterId", "in", workCenterIds)
.where("wcs.companyId", "=", this.companyId)
.where("s.active", "=", true)
.execute();
const workCenterShiftRows = wcShiftRaw.flatMap((r) =>
this.expandShiftDays(r).map((d) => ({
...d,
workCenterId: r.workCenterId
}))
);
// c. the location's shifts (rung 2)
const locationIds = Array.from(
new Set(
workCenters
.map((w) => w.locationId)
.filter((x): x is string => x != null)
)
);
const locShiftRaw =
locationIds.length === 0
? []
: await this.db
.selectFrom("shift as s")
.leftJoin("location as l", "l.id", "s.locationId")
.select([
"s.locationId",
"s.startTime",
"s.endTime",
"s.sunday",
"s.monday",
"s.tuesday",
"s.wednesday",
"s.thursday",
"s.friday",
"s.saturday",
"l.timezone"
])
.where("s.locationId", "in", locationIds)
.where("s.companyId", "=", this.companyId)
.where("s.active", "=", true)
.execute();
const locationShiftRows = locShiftRaw.flatMap((r) =>
this.expandShiftDays(r).map((d) => ({ ...d, locationId: r.locationId }))
);
const windowsMap = closeHolidays({
windowsByWorkCenter: resolveWorkCenterWindows({
workCenters,
workCenterShiftRows,
locationShiftRows,
rangeStart,
rangeEnd
}),
workCenters,
holidayDates: await this.getHolidayDates(rangeStart, rangeEnd)
});
// Machine downtime: subtract open maintenance dispatches flagged
// takesWorkCenterOffline from the resolved windows (derived, not stored —
// completing the dispatch restores the hours at the next regen). Even an
// alwaysOn machine is subtracted: a broken lights-out machine is still down.
const outagesByWc = await this.loadDowntimeOutages(workCenterIds, rangeEnd);
for (const [wcId, outages] of outagesByWc) {
const windows = windowsMap.get(wcId);
if (windows) {
windowsMap.set(wcId, subtractIntervals(windows, outages));
}
}
return windowsMap;
}
/**
* Outage windows per work center from OPEN maintenance dispatches flagged
* takesWorkCenterOffline. One dispatch is down from
* (actualStartTime ?? plannedStartTime ?? createdAt) to
* (actualEndTime ?? plannedEndTime ?? rangeEnd — open-ended = down to the
* horizon), and applies to its own workCenterId AND every joined work center.
* Two selects (no per-WC queries).
*/
private async loadDowntimeOutages(
workCenterIds: string[],
rangeEnd: number
): Promise<Map<string, CalendarWindow[]>> {
const outagesByWc = new Map<string, CalendarWindow[]>();
if (workCenterIds.length === 0) return outagesByWc;
const dispatches = await this.db
.selectFrom("maintenanceDispatch")
.select([
"id",
"workCenterId",
"plannedStartTime",
"plannedEndTime",
"actualStartTime",
"actualEndTime",
"createdAt"
])
.where("companyId", "=", this.companyId)
.where("takesWorkCenterOffline", "=", true)
.where("status", "not in", ["Completed", "Cancelled"])
.execute();
if (dispatches.length === 0) return outagesByWc;
// The work centers each dispatch takes offline via the join table.
const joinRows = await this.db
.selectFrom("maintenanceDispatchWorkCenter")
.select(["maintenanceDispatchId", "workCenterId"])
.where(
"maintenanceDispatchId",
"in",
dispatches.map((d) => d.id)
)
.execute();
return downtimeOutages({
dispatches,
dispatchWorkCenters: joinRows,
workCenterIds,
rangeEnd
});
}
async getLocationCalendarWindows(
locationId: string,
rangeStart: number,
rangeEnd: number
): Promise<CalendarWindow[]> {
return this.cached(
`locationCalendar:${locationId}:${rangeStart}:${rangeEnd}`,
() => this.loadLocationCalendarWindows(locationId, rangeStart, rangeEnd)
);
}
private async loadLocationCalendarWindows(
locationId: string,
rangeStart: number,
rangeEnd: number
): Promise<CalendarWindow[]> {
const rows = await this.db
.selectFrom("shift as s")
.leftJoin("location as l", "l.id", "s.locationId")
.select([
"s.startTime",
"s.endTime",
"s.sunday",
"s.monday",
"s.tuesday",
"s.wednesday",
"s.thursday",
"s.friday",
"s.saturday",
"l.timezone"
])
.where("s.locationId", "=", locationId)
.where("s.companyId", "=", this.companyId)
.where("s.active", "=", true)
.execute();
const locationShiftRows = rows.flatMap((r) => this.expandShiftDays(r));
// The stock-week fallback needs the location tz even with no shifts.
let timezone = rows[0]?.timezone ?? null;
if (!timezone) {
const loc = await this.db
.selectFrom("location")
.select("timezone")
.where("id", "=", locationId)
.where("companyId", "=", this.companyId)
.executeTakeFirst();
timezone = loc?.timezone ?? "UTC";
}
// A holiday closes the plant for people with no shift of their own too.
return subtractDates(
resolveLocationWindows({
timezone: timezone ?? "UTC",
locationShiftRows,
rangeStart,
rangeEnd
}),
await this.getHolidayDates(rangeStart, rangeEnd),
timezone ?? "UTC"
);
}
async getPeopleAssignments(
rangeStart: number,
rangeEnd: number,
timeZone: string
): Promise<PeopleAssignmentRow[]> {
return this.cached(
`peopleAssignments:${rangeStart}:${rangeEnd}:${timeZone}`,
() => this.loadPeopleAssignments(rangeStart, rangeEnd, timeZone)
);
}
private async loadPeopleAssignments(
rangeStart: number,
rangeEnd: number,
timeZone: string
): Promise<PeopleAssignmentRow[]> {
const rows = await this.db
.selectFrom("peopleAssignment as ca")
.select([
"ca.workCenterId",
"ca.employeeId",
"ca.date",
"ca.shiftId",
"ca.overtimeHours",
"ca.hours"
])
.where("ca.companyId", "=", this.companyId)
.where("ca.date", ">=", peopleDateLowerBound(rangeStart, timeZone))
.where("ca.date", "<=", peopleDateUpperBound(rangeEnd, timeZone))
// stable order so split days deal their hours out deterministically
.orderBy("ca.date")
.orderBy("ca.id")
.execute();
return rows.flatMap((r) => {
const date = toIsoDate(r.date);
return date
? [
{
workCenterId: r.workCenterId,
employeeId: r.employeeId,
date,
shiftId: r.shiftId,
overtimeHours: Number(r.overtimeHours ?? 0),
hours: r.hours == null ? null : Number(r.hours)
}
]
: [];
});
}
async getPeopleAbsences(
rangeStart: number,
rangeEnd: number,
timeZone: string
): Promise<PeopleAbsenceRow[]> {
return this.cached(
`peopleAbsences:${rangeStart}:${rangeEnd}:${timeZone}`,
() => this.loadPeopleAbsences(rangeStart, rangeEnd, timeZone)
);
}
private async loadPeopleAbsences(
rangeStart: number,
rangeEnd: number,
timeZone: string
): Promise<PeopleAbsenceRow[]> {
const rows = await this.db
.selectFrom("peopleAbsence as ca")
.select(["ca.employeeId", "ca.date", "ca.shiftId"])
.where("ca.companyId", "=", this.companyId)
.where("ca.date", ">=", peopleDateLowerBound(rangeStart, timeZone))
.where("ca.date", "<=", peopleDateUpperBound(rangeEnd, timeZone))
.execute();
return rows.flatMap((r) => {
const date = toIsoDate(r.date);
return date
? [
{
employeeId: r.employeeId,
date,
shiftId: r.shiftId
}
]
: [];
});
}
async getHolidayDates(
rangeStart: number,
rangeEnd: number
): Promise<Set<string>> {
return this.cached(`holidays:${rangeStart}:${rangeEnd}`, () =>
this.loadHolidayDates(rangeStart, rangeEnd)
);
}
private async loadHolidayDates(
rangeStart: number,
rangeEnd: number
): Promise<Set<string>> {
// Holidays are company-wide calendar days; pad one day each side so every
// timezone's local day inside the range is covered.
const rows = await this.db
.selectFrom("holiday")
.select("date")
.where("companyId", "=", this.companyId)
.where("date", ">=", peopleDateLowerBound(rangeStart, "UTC"))
.where("date", "<=", peopleDateUpperBound(rangeEnd, "UTC"))
.execute();
return new Set(
rows.flatMap((r) => {
const date = toIsoDate(r.date);
return date ? [date] : [];
})
);
}
}