POSTGRESQL REFERENCE · WORKING SOURCE
src/booking.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,
HoldResponse,
QuoteRequest,
QuoteResponse,
ReservationResponse,
Row,
} from "./contracts.ts";
import { audit, type DB, pool, warehouseTx } from "./db.ts";
import { customer, operator, quoteAccess, reservationAccess } from "./access.ts";
import { assert, fail, integer, interval, text, uid } from "./errors.ts";
import { compatible, DEPARTURE_ASSUMPTION, freePositions, loadCapacity } from "./capacity.ts";
import { estimate, rate } from "./billing.ts";
export async function reservationSummary(db: DB, id: string): Promise<ReservationResponse> {
const r = (await db.query(
`SELECT r.*,o.name AS customer_name,rc.version AS rate_version,(SELECT count(*)::int FROM allocations a WHERE a.reservation_id=r.id AND a.state='committed') AS unreceived,(SELECT count(*)::int FROM pallets p JOIN custodies c ON c.pallet_id=p.id WHERE p.reservation_id=r.id AND c.released_at IS NULL) AS in_custody,(SELECT count(*)::int FROM pallets p JOIN custodies c ON c.pallet_id=p.id WHERE p.reservation_id=r.id AND c.released_at IS NOT NULL) AS released FROM reservations r JOIN organizations o ON o.id=r.customer_org_id JOIN rate_cards rc ON rc.id=r.rate_card_id WHERE r.id=$1`,
[id],
)).rows[0];
return {
id: r.id,
quoteId: r.quote_id,
warehouseId: r.warehouse_id,
zoneId: r.zone_id,
customerName: r.customer_name,
customerOrgId: r.customer_org_id,
status: r.status,
receivingStatus: r.receiving_status,
startAt: r.start_at,
endAt: r.end_at,
quantity: r.quantity,
unreceived: r.unreceived,
inCustody: r.in_custody,
released: r.released,
rateVersion: r.rate_version,
estimateCents: r.estimate_cents,
declaration: r.declaration,
};
}
export async function withReservation<T>(
actor: Actor,
id: string,
fn: (db: DB, r: Row, clock: string) => Promise<T>,
idem?: { operation: string; key: string; payload: unknown },
): Promise<T> {
const db = await pool.connect();
let r;
try {
r = await reservationAccess(db, actor, id);
} finally {
db.release();
}
return warehouseTx(
actor,
r.warehouse_id,
async (db, clock) => fn(db, await reservationAccess(db, actor, id), clock),
idem,
);
}
export async function quoteDTO(db: DB, q: Row, clock: string): Promise<QuoteResponse> {
const tariff = await rate(db, q.rate_card_id),
w = (await db.query("SELECT timezone FROM warehouses WHERE id=$1", [q.warehouse_id])).rows[0];
const est = await estimate(db, q.quantity, q.start_at, q.end_at, w.timezone, tariff);
const available =
freePositions(await loadCapacity(db, q.warehouse_id, clock), q.zone_id, q.start_at, q.end_at).length;
return {
id: q.id,
warehouseId: q.warehouse_id,
zoneId: q.zone_id,
quantity: q.quantity,
startAt: q.start_at,
endAt: q.end_at,
expiresAt: q.expires_at,
rateVersion: tariff.version,
currency: tariff.currency,
lines: est.lines,
totalCents: q.estimate_cents,
terms: q.terms,
pallet: q.declaration,
status: q.expires_at <= clock ? "expired" : "quoted",
availability: { available, requested: q.quantity, assumption: DEPARTURE_ASSUMPTION },
};
}
export async function createQuote(actor: Actor, payload: QuoteRequest): Promise<QuoteResponse> {
customer(actor);
const warehouseId = text(payload.warehouseId, "warehouseId"),
zoneId = text(payload.zoneId, "zoneId"),
quantity = integer(payload.quantity, "quantity", 1, 100);
const [start, end] = interval(payload.startAt, payload.endAt);
return warehouseTx(actor, warehouseId, async (db, clock) => {
assert(start >= clock, "PAST_INTERVAL", "Choose a booking start at or after the demo clock.");
await compatible(db, warehouseId, zoneId, payload.pallet);
const w = (await db.query("SELECT * FROM warehouses WHERE id=$1", [warehouseId])).rows[0];
const tariff = (await db.query(
"SELECT * FROM rate_cards WHERE warehouse_id=$1 AND effective_at<=$2 ORDER BY effective_at DESC,id DESC LIMIT 1",
[warehouseId, clock],
)).rows[0];
assert(tariff, "NO_RATE_CARD", "No tariff applies to this warehouse.", 409);
const estimated = await estimate(db, quantity, start, end, w.timezone, tariff),
id = uid("quote"),
expiresAt = new Date(Date.parse(clock) + 30 * 60000).toISOString();
const q = (await db.query(
"INSERT INTO quotes(id,customer_org_id,warehouse_id,zone_id,rate_card_id,quantity,start_at,end_at,expires_at,declaration,estimate_cents,terms,created_by) VALUES($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13) RETURNING *",
[
id,
actor.orgId,
warehouseId,
zoneId,
tariff.id,
quantity,
start,
end,
expiresAt,
JSON.stringify(payload.pallet),
estimated.totalCents,
tariff.terms,
actor.id,
],
)).rows[0];
await audit(db, actor, clock, "quote.created", {
warehouseId,
customerOrgId: actor.orgId,
data: { quoteId: id, quantity, estimateCents: estimated.totalCents },
});
return quoteDTO(db, q, clock);
});
}
export async function createHold(actor: Actor, quoteId: string): Promise<HoldResponse> {
customer(actor);
const initial = await pool.connect();
let q;
try {
q = await quoteAccess(initial, actor, quoteId);
} finally {
initial.release();
}
return warehouseTx(actor, q.warehouse_id, async (db, clock) => {
q = await quoteAccess(db, actor, quoteId);
assert(q.expires_at > clock, "QUOTE_EXPIRED", "This quote has expired. Request a new quote.", 409);
assert(
q.start_at >= clock,
"QUOTE_INTERVAL_STARTED",
"The quoted start has elapsed. Request a new quote with a current or future start.",
409,
);
assert(q.end_at > clock, "PAST_INTERVAL", "The quoted storage interval has ended.", 409);
const existing = (await db.query("SELECT * FROM holds WHERE quote_id=$1", [quoteId])).rows[0];
if (existing) {
if (existing.status === "active" && existing.expires_at > clock) {
return {
id: existing.id,
quoteId,
expiresAt: existing.expires_at,
status: "active",
positionIds:
(await db.query("SELECT position_id FROM allocations WHERE hold_id=$1 ORDER BY position_id", [
existing.id,
])).rows.map((a) => a.position_id),
};
}
fail(
409,
"HOLD_UNAVAILABLE",
"This quote already has a confirmed or expired hold. Request a new quote.",
);
}
await compatible(db, q.warehouse_id, q.zone_id, q.declaration);
const free = freePositions(
await loadCapacity(db, q.warehouse_id, clock),
q.zone_id,
q.start_at,
q.end_at,
);
if (free.length < q.quantity) {
fail(
409,
"CAPACITY_CONFLICT",
`Only ${free.length} compatible positions are available for the entire interval; ${q.quantity} were requested.`,
{ available: free.length, requested: q.quantity, assumption: DEPARTURE_ASSUMPTION },
);
}
const id = uid("hold"),
expiresAt = new Date(Math.min(Date.parse(clock) + 10 * 60000, Date.parse(q.expires_at))).toISOString();
await db.query(
"INSERT INTO holds(id,quote_id,customer_org_id,warehouse_id,expires_at,status,created_by) VALUES($1,$2,$3,$4,$5,'active',$6)",
[id, q.id, actor.orgId, q.warehouse_id, expiresAt, actor.id],
);
const positionIds = free.slice(0, q.quantity).map((p) => p.id);
for (const p of positionIds) {
await db.query("INSERT INTO allocations VALUES($1,$2,NULL,$3,$4,$5,'held')", [
uid("alloc"),
id,
p,
q.start_at,
q.end_at,
]);
}
await audit(db, actor, clock, "hold.created", {
warehouseId: q.warehouse_id,
customerOrgId: actor.orgId,
data: { holdId: id, quoteId, positionIds, expiresAt },
});
return { id, quoteId, expiresAt, positionIds, status: "active" };
});
}
export async function confirmHold(
actor: Actor,
holdId: string,
payload: Row,
key: string,
): Promise<ReservationResponse> {
customer(actor);
const lookup = await pool.connect();
let h;
try {
h = (await lookup.query("SELECT * FROM holds WHERE id=$1 AND customer_org_id=$2", [holdId, actor.orgId]))
.rows[0];
if (!h) fail(404, "NOT_FOUND", "Hold not found.");
} finally {
lookup.release();
}
return warehouseTx(actor, h.warehouse_id, async (db, clock) => {
h = (await db.query("SELECT * FROM holds WHERE id=$1 AND customer_org_id=$2", [holdId, actor.orgId]))
.rows[0];
const q = await quoteAccess(db, actor, h.quote_id);
assert(h.status === "active", "INVALID_TRANSITION", "Only an active hold can be confirmed.", 409);
assert(
h.expires_at > clock && q.expires_at > clock,
"HOLD_EXPIRED",
"The hold or its quote expired. Request a new quote and hold.",
409,
);
assert(
q.start_at >= clock,
"QUOTE_INTERVAL_STARTED",
"The quoted start has elapsed. Request a new quote with a current or future start.",
409,
);
assert(q.end_at > clock, "PAST_INTERVAL", "The storage interval has ended.", 409);
const available = freePositions(
await loadCapacity(db, q.warehouse_id, clock),
q.zone_id,
q.start_at,
q.end_at,
{ holdId },
);
if (available.length < q.quantity) {
fail(
409,
"CAPACITY_CONFLICT",
"The hold can no longer be fulfilled because physical capacity changed. The operator must resolve the exception.",
{ available: available.length, requested: q.quantity },
);
}
const id = uid("res");
await db.query(
"INSERT INTO reservations(id,quote_id,hold_id,customer_org_id,warehouse_id,zone_id,rate_card_id,quantity,start_at,end_at,declaration,estimate_cents,created_by) VALUES($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13)",
[
id,
q.id,
h.id,
actor.orgId,
q.warehouse_id,
q.zone_id,
q.rate_card_id,
q.quantity,
q.start_at,
q.end_at,
JSON.stringify(q.declaration),
q.estimate_cents,
actor.id,
],
);
const allocs =
(await db.query("SELECT * FROM allocations WHERE hold_id=$1 ORDER BY position_id", [h.id])).rows;
for (let i = 0; i < allocs.length; i++) {
await db.query(
"UPDATE allocations SET reservation_id=$1,position_id=$2,state='committed' WHERE id=$3",
[id, available[i].id, allocs[i].id],
);
}
await db.query("UPDATE holds SET status='confirmed' WHERE id=$1", [h.id]);
await audit(db, actor, clock, "reservation.confirmed", {
warehouseId: q.warehouse_id,
customerOrgId: actor.orgId,
reservationId: id,
data: { holdId, quoteId: q.id, rateCardId: q.rate_card_id },
});
return reservationSummary(db, id);
}, { operation: `confirm:${holdId}`, key, payload });
}
export async function completeIfFinished(db: DB, id: string) {
await db.query(
`UPDATE reservations r SET status='completed' WHERE r.id=$1 AND r.status='confirmed' AND r.receiving_status<>'open' AND NOT EXISTS(SELECT 1 FROM allocations a WHERE a.reservation_id=r.id AND a.state='committed') AND NOT EXISTS(SELECT 1 FROM pallets p JOIN custodies c ON c.pallet_id=p.id WHERE p.reservation_id=r.id AND c.released_at IS NULL)`,
[id],
);
}
export async function closeReceiving(actor: Actor, id: string, payload: Row) {
operator(actor);
assert(
["release", "retain"].includes(payload.disposition),
"VALIDATION",
"Choose whether to release or retain the unreceived balance.",
);
const reason = text(payload.reason, "reason", 500);
return withReservation(actor, id, async (db, r, clock) => {
assert(
r.status === "confirmed" && r.receiving_status === "open",
"INVALID_TRANSITION",
"Receiving is not open for this reservation.",
409,
);
if (payload.disposition === "release") {
await db.query(
"UPDATE allocations SET state='released' WHERE reservation_id=$1 AND state='committed'",
[id],
);
}
await db.query("UPDATE reservations SET receiving_status=$1 WHERE id=$2", [
payload.disposition === "release" ? "closed_released" : "closed_retained",
id,
]);
await audit(db, actor, clock, "receiving.closed", {
warehouseId: r.warehouse_id,
customerOrgId: r.customer_org_id,
reservationId: id,
reason,
data: { disposition: payload.disposition },
});
await completeIfFinished(db, id);
return reservationSummary(db, id);
});
}
export async function terminateReservation(
actor: Actor,
id: string,
action: "cancel" | "expire",
payload: Row,
) {
if (action === "expire") operator(actor);
const reason = text(payload.reason, "reason", 500);
return withReservation(actor, id, async (db, r, clock) => {
assert(
r.status === "confirmed",
"INVALID_TRANSITION",
"Only a confirmed reservation can be canceled or expired.",
409,
);
if (action === "expire") {
assert(r.end_at <= clock, "NOT_EXPIRED", "The reservation interval has not ended.", 409);
}
await db.query("UPDATE allocations SET state='released' WHERE reservation_id=$1 AND state='committed'", [
id,
]);
await db.query("UPDATE reservations SET status=$1,receiving_status='closed_released' WHERE id=$2", [
action === "cancel" ? "cancelled" : "expired",
id,
]);
await audit(db, actor, clock, `reservation.${action}`, {
warehouseId: r.warehouse_id,
customerOrgId: r.customer_org_id,
reservationId: id,
reason,
data: { custodyPreserved: true },
});
return reservationSummary(db, id);
});
}
export async function amendReservation(actor: Actor, id: string, payload: Row) {
const [start, end] = interval(payload.startAt, payload.endAt, false),
quantity = integer(payload.quantity, "quantity", 1, 100),
reason = text(payload.reason, "reason", 500);
assert(Date.parse(end) % 3600000 === 0, "VALIDATION", "endAt must fall on an exact hour.");
return withReservation(actor, id, async (db, r, clock) => {
assert(
start === r.start_at || Date.parse(start) % 3600000 === 0,
"VALIDATION",
"A changed startAt must fall on an exact hour; an existing precise operational start may be retained.",
);
assert(
r.status === "confirmed",
"INVALID_TRANSITION",
"Only a confirmed reservation can be amended.",
409,
);
assert(end > clock, "PAST_INTERVAL", "The amended departure must be in the future.", 409);
assert(
!(start < r.start_at && start < clock),
"BACKDATED_AMENDMENT",
"An amendment cannot create a new promise in the past. Retain the original start or choose a current or future start.",
409,
);
const pallets = (await db.query(
"SELECT p.*,c.position_id,c.received_at,c.released_at FROM pallets p JOIN custodies c ON c.pallet_id=p.id WHERE p.reservation_id=$1",
[id],
)).rows;
assert(
quantity >= pallets.length,
"BELOW_RECEIVED",
"Quantity cannot be lower than the number already received.",
409,
);
assert(
pallets.every((p) => start <= p.received_at),
"INVALID_INTERVAL",
"The start cannot move after a recorded receipt.",
409,
);
const state = await loadCapacity(db, r.warehouse_id, clock);
const free = freePositions(state, r.zone_id, start, end, { reservationId: id });
const occupied = pallets.filter((p) => !p.released_at);
if (occupied.some((p) => !free.some((f) => f.id === p.position_id))) {
fail(
409,
"CAPACITY_CONFLICT",
"The extension conflicts with a future promise or block on an occupied position. Original allocations and actual custody are preserved.",
);
}
const remaining = quantity - pallets.length,
freeUnused = free.filter((f) => !occupied.some((p) => p.position_id === f.id));
if (freeUnused.length < remaining) {
fail(
409,
"CAPACITY_CONFLICT",
"Not enough compatible positions for this amendment. Original allocations are preserved.",
{ available: freeUnused.length, requested: remaining },
);
}
// All validation precedes changes and shares one transaction: no surrender gap.
await db.query("UPDATE allocations SET state='released' WHERE reservation_id=$1 AND state='committed'", [
id,
]);
for (const p of freeUnused.slice(0, remaining)) {
await db.query("INSERT INTO allocations VALUES($1,$2,$3,$4,$5,$6,'committed')", [
uid("alloc"),
r.hold_id,
id,
p.id,
start,
end,
]);
}
await db.query(
"UPDATE allocations SET start_at=LEAST(start_at,$1),end_at=$2 WHERE reservation_id=$3 AND state='occupied'",
[start, end, id],
);
const tariff = await rate(db, r.rate_card_id),
est = await estimate(db, quantity, start, end, r.timezone, tariff);
await db.query(
"UPDATE reservations SET start_at=$1,end_at=$2,quantity=$3,estimate_cents=$4,receiving_status=CASE WHEN $5>0 THEN 'open' ELSE receiving_status END WHERE id=$6",
[start, end, quantity, est.totalCents, remaining, id],
);
await audit(db, actor, clock, "reservation.amended", {
warehouseId: r.warehouse_id,
customerOrgId: r.customer_org_id,
reservationId: id,
reason,
data: {
before: { startAt: r.start_at, endAt: r.end_at, quantity: r.quantity },
after: { startAt: start, endAt: end, quantity },
},
});
return reservationSummary(db, id);
});
}