POSTGRESQL REFERENCE · WORKING SOURCE
src/capacity.ts
This file ships in the runnable source archive. Synthetic fixtures and demonstration policies are identified in the README.
Download the complete project ↓import type { Actor, PalletDeclaration, Row } from "./contracts.ts";
import { audit, type DB, warehouseTx } from "./db.ts";
import { operator } from "./access.ts";
import { assert, fail, integer, interval, overlaps, text, uid } from "./errors.ts";
const FOREVER = "9999-12-31T23:59:59.999Z";
export const DEPARTURE_ASSUMPTION =
"Future offers assume on-time pallets leave at their agreed reservation end. Goods remain in physical custody until a release is recorded. An overdue pallet has no assumed departure; it blocks new offers and flags threatened bookings.";
export type Claim = {
positionId: string;
kind: "held" | "committed" | "occupied" | "blocked";
start: string;
end: string;
reservationId?: string;
allocationId?: string;
holdId?: string;
palletId?: string;
ownerOrgId?: string;
blockId?: string;
};
export interface CapacityState {
positions: Row[];
claims: Claim[];
pallets: Row[];
reservations: Row[];
blocks: Row[];
}
export async function compatible(
db: DB,
warehouseId: string,
zoneId: string,
declaration: unknown,
): Promise<Row> {
const z =
(await db.query("SELECT * FROM zones WHERE id=$1 AND warehouse_id=$2", [zoneId, warehouseId])).rows[0];
if (!z) fail(404, "NOT_FOUND", "Zone not found.");
const p = declaration as PalletDeclaration;
assert(
p && typeof p === "object",
"INCOMPATIBLE_PALLET",
"Provide the pallet dimensions, gross weight, goods and storage condition.",
);
for (const field of ["lengthMm", "widthMm", "heightMm", "weightKg"] as const) {
integer(p[field], field, 1, 100000);
}
if (
p.lengthMm !== z.length_mm || p.widthMm !== z.width_mm || p.heightMm > z.max_height_mm ||
p.weightKg > z.max_weight_kg || p.condition !== z.storage_condition || p.goods !== z.goods
) {
fail(
422,
"INCOMPATIBLE_PALLET",
`This zone accepts ${z.length_mm} × ${z.width_mm} mm pallets up to ${z.max_height_mm} mm high and ${z.max_weight_kg} kg, ambient packaged dry nonhazardous goods only.`,
{
zoneId,
limits: {
lengthMm: z.length_mm,
widthMm: z.width_mm,
maxHeightMm: z.max_height_mm,
maxWeightKg: z.max_weight_kg,
condition: z.storage_condition,
goods: z.goods,
},
},
);
}
return z;
}
export async function loadCapacity(db: DB, warehouseId: string, clock: string): Promise<CapacityState> {
const positions = (await db.query(
"SELECT p.*,z.name AS zone_name FROM positions p JOIN zones z ON z.id=p.zone_id WHERE z.warehouse_id=$1 ORDER BY p.id",
[warehouseId],
)).rows;
const allocations = (await db.query(
`SELECT a.*,h.status AS hold_status,h.expires_at,h.customer_org_id,r.status AS reservation_status FROM allocations a JOIN holds h ON h.id=a.hold_id LEFT JOIN reservations r ON r.id=a.reservation_id WHERE h.warehouse_id=$1 AND a.state IN ('held','committed')`,
[warehouseId],
)).rows;
const pallets = (await db.query(
`SELECT p.*,c.id AS custody_id,c.position_id,c.received_at,c.released_at,r.end_at AS expected_end_at,r.warehouse_id,r.status AS reservation_status FROM pallets p JOIN custodies c ON c.pallet_id=p.id JOIN reservations r ON r.id=p.reservation_id WHERE r.warehouse_id=$1`,
[warehouseId],
)).rows;
const blocks =
(await db.query("SELECT * FROM capacity_blocks WHERE warehouse_id=$1 AND revoked_at IS NULL", [
warehouseId,
])).rows;
const reservations =
(await db.query("SELECT * FROM reservations WHERE warehouse_id=$1", [warehouseId])).rows;
const claims: Claim[] = [];
for (const a of allocations) {
if (a.state === "held" && !(a.hold_status === "active" && a.expires_at > clock)) continue;
if (a.state === "committed" && a.reservation_status !== "confirmed") continue;
claims.push({
positionId: a.position_id,
kind: a.state,
start: a.start_at,
end: a.end_at,
reservationId: a.reservation_id,
allocationId: a.id,
holdId: a.hold_id,
ownerOrgId: a.customer_org_id,
});
}
for (const p of pallets) {
// Historical departed pallets are retained as evidence, not current demand.
// Current position cannot be reconstructed across history by this forecast endpoint.
if (p.released_at) continue;
const end = p.expected_end_at > clock ? p.expected_end_at : FOREVER;
claims.push({
positionId: p.position_id,
kind: "occupied",
start: p.received_at,
end,
reservationId: p.reservation_id,
allocationId: p.allocation_id,
palletId: p.id,
ownerOrgId: p.owner_org_id,
});
}
for (const b of blocks) {
claims.push({
positionId: b.position_id,
kind: "blocked",
start: b.start_at,
end: b.end_at,
blockId: b.id,
});
}
return { positions, claims, pallets, reservations, blocks };
}
export function freePositions(
state: CapacityState,
zoneId: string,
start: string,
end: string,
exclude: { reservationId?: string; holdId?: string; allocationIds?: string[]; palletIds?: string[] } = {},
): Row[] {
return state.positions.filter((p) =>
p.zone_id === zoneId &&
!state.claims.some((c) =>
c.positionId === p.id && overlaps(start, end, c.start, c.end) &&
!(exclude.reservationId && c.reservationId === exclude.reservationId) &&
!(exclude.holdId && c.holdId === exclude.holdId) &&
!exclude.allocationIds?.includes(c.allocationId ?? "") && !exclude.palletIds?.includes(c.palletId ?? "")
)
);
}
export function exceptions(state: CapacityState, clock: string, actor: Actor): Row[] {
const out: Row[] = [];
for (const p of state.pallets.filter((p) => !p.released_at && p.expected_end_at <= clock)) {
if (actor.role === "customer" && p.owner_org_id !== actor.orgId) continue;
const threatened = state.claims.filter((c) =>
c.positionId === p.position_id && c.reservationId !== p.reservation_id &&
["held", "committed"].includes(c.kind) && c.end > clock
);
out.push({
id: `overstay-${p.id}`,
type: "overstay",
severity: "action",
palletId: p.id,
palletLabel: p.label,
reservationId: p.reservation_id,
positionId: p.position_id,
expectedEndAt: p.expected_end_at,
threatenedReservationIds: actor.role === "operator"
? [...new Set(threatened.map((c) => c.reservationId).filter(Boolean))]
: [],
threatenedCount: threatened.length,
message: `${p.label} remains at ${p.position_id} after its expected departure.${
threatened.length ? ` ${threatened.length} future position commitment(s) are threatened.` : ""
}`,
action:
"Record the actual release, relocate to a compatible available position, or request a capacity-checked extension. Contact the affected shipper; do not receive into an occupied position.",
});
}
for (const b of state.blocks) {
if (b.end_at <= clock) continue;
const threatened = state.claims.filter((c) =>
c.kind !== "blocked" && c.positionId === b.position_id && overlaps(c.start, c.end, b.start_at, b.end_at)
);
for (const c of threatened) {
if (actor.role === "customer" && c.ownerOrgId !== actor.orgId) continue;
out.push({
id: `blocked-${b.id}-${c.allocationId ?? c.palletId}`,
type: "blocked_capacity",
severity: "action",
blockId: b.id,
positionId: b.position_id,
reservationId: c.reservationId,
palletId: c.palletId,
message: `${b.position_id} is blocked: ${b.reason}. Existing ${c.kind} capacity is affected.`,
action:
"Inspect the rack and goods, record a safe relocation or amend the affected commitment. Unblock only after the physical restriction is resolved.",
});
}
}
return out;
}
export function capacityView(
state: CapacityState,
actor: Actor,
warehouseId: string,
zoneId: string,
start: string,
end: string,
clock: string,
) {
const positions = state.positions.filter((p) => p.zone_id === zoneId);
const claims = state.claims.filter((c) => positions.some((p) => p.id === c.positionId));
const active = state.pallets.filter((p) => !p.released_at && positions.some((x) => x.id === p.position_id));
const blockedIds = new Set(
claims.filter((c) => c.kind === "blocked" && c.start <= clock && c.end > clock).map((c) => c.positionId),
);
const occupiedIds = new Set(active.map((p) => p.position_id));
const timeline: Row[] = [];
for (let n = Date.parse(start); n < Date.parse(end); n += 3600000) {
const at = new Date(n).toISOString(),
through = new Date(Math.min(n + 3600000, Date.parse(end))).toISOString();
const bucket = claims.filter((c) => overlaps(at, through, c.start, c.end));
const kindCount = (kind: string) =>
new Set(bucket.filter((c) => c.kind === kind).map((c) => c.positionId)).size;
// A cell promises a position for its entire hour. Distinct sequential uses within
// a cell do not create a false simultaneous deficit: calculate at change points.
const points = [at, ...bucket.flatMap((c) => [c.start, c.end]).filter((p) => p >= at && p < through)];
let deficit = 0;
for (const point of points) {
deficit = Math.max(
deficit,
positions.reduce((sum, p) => {
const current = bucket.filter((c) => c.positionId === p.id && c.start <= point && point < c.end);
const demand = current.filter((c) => c.kind !== "blocked").length +
(current.some((c) => c.kind === "blocked") ? 1 : 0);
return sum + Math.max(0, demand - 1);
}, 0),
);
}
timeline.push({
at,
through,
occupied: kindCount("occupied"),
held: kindCount("held"),
committed: kindCount("committed"),
blocked: kindCount("blocked"),
available: positions.length - new Set(bucket.map((c) => c.positionId)).size,
deficit,
});
}
return {
warehouseId,
zoneId,
now: clock,
totalPositions: positions.length,
physical: {
occupied: occupiedIds.size,
blocked: blockedIds.size,
free: positions.length - new Set([...occupiedIds, ...blockedIds]).size,
},
projected: {
minimumAvailable: freePositions(state, zoneId, start, end).length,
assumption: DEPARTURE_ASSUMPTION,
},
timeline,
positions: positions.map((p) => ({
id: p.id,
label: p.label,
currentPalletId: actor.role === "operator"
? active.find((x) => x.position_id === p.id)?.id ?? null
: active.find((x) => x.position_id === p.id && x.owner_org_id === actor.orgId)?.id ?? null,
occupied: occupiedIds.has(p.id),
blocked: blockedIds.has(p.id),
})),
exceptions: exceptions(state, clock, actor),
};
}
export async function getCapacity(
actor: Actor,
warehouseId: string,
zoneId: string,
start: string,
end: string,
) {
const [a, b] = interval(start, end);
return await warehouseTx(actor, warehouseId, async (db, clock) => {
const zone =
(await db.query("SELECT 1 FROM zones WHERE id=$1 AND warehouse_id=$2", [zoneId, warehouseId])).rowCount;
assert(zone, "NOT_FOUND", "Zone not found.", 404);
return capacityView(await loadCapacity(db, warehouseId, clock), actor, warehouseId, zoneId, a, b, clock);
});
}
export async function blockCapacity(actor: Actor, payload: Row) {
operator(actor);
const warehouseId = text(payload.warehouseId, "warehouseId"), reason = text(payload.reason, "reason", 500);
const [start, end] = interval(payload.startAt, payload.endAt);
assert(
Array.isArray(payload.positionIds) && payload.positionIds.length > 0 && payload.positionIds.length <= 100,
"VALIDATION",
"Choose positions to block.",
);
return await warehouseTx(actor, warehouseId, async (db, clock) => {
const state = await loadCapacity(db, warehouseId, clock);
const ids = [...new Set(payload.positionIds)] as string[];
const blocks = [];
for (const id of ids) {
assert(
state.positions.some((p) => p.id === id),
"NOT_FOUND",
"Position not found in this warehouse.",
404,
);
const blockId = uid("block");
await db.query(
"INSERT INTO capacity_blocks(id,warehouse_id,position_id,start_at,end_at,reason,created_by) VALUES($1,$2,$3,$4,$5,$6,$7)",
[blockId, warehouseId, id, start, end, reason, actor.id],
);
blocks.push({ id: blockId, positionId: id, startAt: start, endAt: end, reason });
}
await audit(db, actor, clock, "capacity.blocked", { warehouseId, reason, data: { blocks } });
return { blocks, exceptions: exceptions(await loadCapacity(db, warehouseId, clock), clock, actor) };
});
}