POSTGRESQL REFERENCE · WORKING SOURCE

src/server.ts

This file ships in the runnable source archive. Synthetic fixtures and demonstration policies are identified in the README.

import type { Actor, QuoteRequest, ReceiveRequest, ReleaseRequest, Row } from "./contracts.ts";
import { audit, type DB, now, pool, transaction, warehouseTx } from "./db.ts";
import { authenticate, checkPassword, operator, palletAccess, reservationAccess } from "./access.ts";
import { AppError, assert, fail, hash, instant, text, uid } from "./errors.ts";
import { blockCapacity, exceptions, getCapacity, loadCapacity } from "./capacity.ts";
import {
  amendReservation,
  closeReceiving,
  confirmHold,
  createHold,
  createQuote,
  quoteDTO,
  reservationSummary,
  terminateReservation,
  withReservation,
} from "./booking.ts";
import { adjustMovement, palletDTO, receive, release, relocate, requestRelease } from "./custody.ts";
import { getInvoice } from "./billing.ts";

const securityHeaders = {
  "cache-control": "no-store",
  "x-content-type-options": "nosniff",
  "referrer-policy": "same-origin",
  "x-frame-options": "DENY",
  "content-security-policy":
    "default-src 'self'; script-src 'self'; style-src 'self' 'unsafe-inline'; img-src 'self' data:; connect-src 'self'; base-uri 'none'; form-action 'self'; frame-ancestors 'none'",
};
function json(value: unknown, status = 200, headers: Record<string, string> = {}) {
  return new Response(JSON.stringify(value), {
    status,
    headers: { ...securityHeaders, "content-type": "application/json; charset=utf-8", ...headers },
  });
}
const cookie = (token: string, clear = false) =>
  `warehouse_session=${token}; HttpOnly; SameSite=Strict; Path=/; Max-Age=${clear ? 0 : 28800}${
    Deno.env.get("COOKIE_SECURE") === "true" ? "; Secure" : ""
  }`;
async function body(req: Request): Promise<Row> {
  assert(
    req.headers.get("content-type")?.startsWith("application/json"),
    "CONTENT_TYPE",
    "Send application/json.",
    415,
  );
  const raw = await req.text();
  assert(raw.length <= 65536, "PAYLOAD_TOO_LARGE", "Request exceeds the 64 KiB demo limit.", 413);
  let value;
  try {
    value = JSON.parse(raw);
  } catch {
    fail(400, "INVALID_JSON", "The JSON body could not be read.");
  }
  assert(value && typeof value === "object" && !Array.isArray(value), "VALIDATION", "Send a JSON object.");
  return value;
}
async function bootstrap(actor: Actor) {
  const db = await pool.connect();
  try {
    await db.query("BEGIN ISOLATION LEVEL REPEATABLE READ READ ONLY");
    const clock = await now(db);
    const wh = (await db.query(
      `SELECT w.* FROM warehouses w WHERE ($1='operator' AND w.operator_org_id=$2) OR ($1='customer' AND EXISTS(SELECT 1 FROM operator_customers oc WHERE oc.operator_org_id=w.operator_org_id AND oc.customer_org_id=$2)) ORDER BY w.id`,
      [actor.role, actor.orgId],
    )).rows;
    const warehouses = [], allExceptions: Row[] = [];
    for (const w of wh) {
      const zones = [];
      for (
        const z of (await db.query("SELECT * FROM zones WHERE warehouse_id=$1 ORDER BY id", [w.id])).rows
      ) {
        zones.push({
          id: z.id,
          name: z.name,
          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,
          arrangement: z.arrangement,
          positions:
            (await db.query("SELECT id,label FROM positions WHERE zone_id=$1 ORDER BY id", [z.id])).rows,
        });
      }
      warehouses.push({ id: w.id, name: w.name, timezone: w.timezone, zones });
      allExceptions.push(...exceptions(await loadCapacity(db, w.id, clock), clock, actor));
    }
    const rIds = (await db.query(
      `SELECT r.id FROM reservations r JOIN warehouses w ON w.id=r.warehouse_id WHERE ($1='customer' AND r.customer_org_id=$2) OR ($1='operator' AND w.operator_org_id=$2) ORDER BY r.recorded_at DESC,r.id`,
      [actor.role, actor.orgId],
    )).rows;
    const reservations = [];
    for (const r of rIds) reservations.push(await reservationSummary(db, r.id));
    const pIds = (await db.query(
      `SELECT p.id FROM pallets p JOIN reservations r ON r.id=p.reservation_id JOIN warehouses w ON w.id=r.warehouse_id WHERE ($1='customer' AND p.owner_org_id=$2) OR ($1='operator' AND w.operator_org_id=$2) ORDER BY p.label,p.id`,
      [actor.role, actor.orgId],
    )).rows;
    const pallets = [];
    for (const p of pIds) pallets.push(await palletDTO(db, p.id));
    const qRows = (await db.query(
      `SELECT q.* FROM quotes q JOIN warehouses w ON w.id=q.warehouse_id WHERE ($1='customer' AND q.customer_org_id=$2) OR ($1='operator' AND w.operator_org_id=$2) ORDER BY q.recorded_at DESC,q.id`,
      [actor.role, actor.orgId],
    )).rows;
    const quotes = [];
    for (const q of qRows) quotes.push(await quoteDTO(db, q, clock));
    const hRows = (await db.query(
      `SELECT h.* FROM holds h JOIN warehouses w ON w.id=h.warehouse_id WHERE ($1='customer' AND h.customer_org_id=$2) OR ($1='operator' AND w.operator_org_id=$2) ORDER BY h.recorded_at DESC,h.id`,
      [actor.role, actor.orgId],
    )).rows;
    const holds = [];
    for (const h of hRows) {
      holds.push({
        id: h.id,
        quoteId: h.quote_id,
        expiresAt: h.expires_at,
        status: h.status === "active" && h.expires_at <= clock ? "expired" : h.status,
        positionIds:
          (await db.query("SELECT position_id FROM allocations WHERE hold_id=$1 ORDER BY position_id", [
            h.id,
          ])).rows.map((a) => a.position_id),
      });
    }
    const releaseRequests = (await db.query(
      `SELECT rr.* FROM release_requests rr JOIN reservations r ON r.id=rr.reservation_id JOIN warehouses w ON w.id=r.warehouse_id WHERE ($1='customer' AND rr.customer_org_id=$2) OR ($1='operator' AND w.operator_org_id=$2) ORDER BY rr.recorded_at DESC`,
      [actor.role, actor.orgId],
    )).rows.map((r) => ({
      id: r.id,
      reservationId: r.reservation_id,
      customerOrgId: r.customer_org_id,
      palletIds: r.pallet_ids,
      reason: r.reason,
      status: r.status,
    }));
    const blocks = (await db.query(
      `SELECT b.* FROM capacity_blocks b JOIN warehouses w ON w.id=b.warehouse_id WHERE $1='operator' AND w.operator_org_id=$2 AND b.revoked_at IS NULL ORDER BY b.recorded_at DESC`,
      [actor.role, actor.orgId],
    )).rows.map((b) => ({
      id: b.id,
      warehouseId: b.warehouse_id,
      positionId: b.position_id,
      startAt: b.start_at,
      endAt: b.end_at,
      reason: b.reason,
    }));
    await db.query("COMMIT");
    return {
      user: actor,
      csrfToken: actor.csrfToken,
      now: clock,
      demoMode: Deno.env.get("DEMO_MODE") === "true",
      warehouses,
      reservations,
      pallets,
      quotes,
      holds,
      releaseRequests,
      exceptions: allExceptions,
      blocks,
    };
  } catch (e) {
    await db.query("ROLLBACK").catch(() => {});
    throw e;
  } finally {
    db.release();
  }
}
async function clockAction(actor: Actor, payload: Row) {
  operator(actor);
  assert(
    Deno.env.get("DEMO_MODE") === "true" && actor.id === "user-operator",
    "DEMO_DISABLED",
    "The demo clock is unavailable.",
    403,
  );
  const target = instant(payload.now, "now");
  return transaction(async (db) => {
    // Global order: all warehouse rows sorted, THEN demo clock. Warehouse mutations
    // never lock the clock row, avoiding a reverse lock order.
    const warehouses = (await db.query("SELECT * FROM warehouses ORDER BY id FOR UPDATE")).rows;
    const current = (await db.query("SELECT now_at FROM demo_clock WHERE id=1 FOR UPDATE")).rows[0].now_at;
    assert(
      target >= current,
      "CLOCK_BACKWARDS",
      "The demo clock can only advance. Use the isolated CLI reset to start over.",
      409,
    );
    await db.query("UPDATE demo_clock SET now_at=$1 WHERE id=1", [target]);
    await audit(db, actor, target, "demo.clock_advanced", {
      reason: "Operator advanced the synthetic demo clock",
      data: { from: current, to: target },
    });
    const ex = [];
    for (const w of warehouses) ex.push(...exceptions(await loadCapacity(db, w.id, target), target, actor));
    return { now: target, exceptions: ex };
  });
}
export async function handler(req: Request): Promise<Response> {
  try {
    const url = new URL(req.url), path = url.pathname, method = req.method;
    if (path === "/healthz" && method === "GET") {
      await pool.query("SELECT 1");
      return json({ ok: true, service: "svc_warehouse" });
    }
    if (!path.startsWith("/api/")) {
      if (method !== "GET" && method !== "HEAD") {
        return json({ error: { code: "METHOD_NOT_ALLOWED", message: "Method not allowed." } }, 405);
      }
      const files: Record<string, [string, string]> = {
        "/": ["index.html", "text/html"],
        "/index.html": ["index.html", "text/html"],
        "/app.js": ["app.js", "text/javascript"],
        "/styles.css": ["styles.css", "text/css"],
      };
      const file = files[path];
      if (!file) return json({ error: { code: "NOT_FOUND", message: "Page not found." } }, 404);
      const bytes = await Deno.readFile(new URL(`../public/${file[0]}`, import.meta.url));
      return new Response(method === "HEAD" ? null : bytes, {
        headers: { ...securityHeaders, "content-type": `${file[1]}; charset=utf-8` },
      });
    }
    if (!["GET", "POST"].includes(method)) fail(405, "METHOD_NOT_ALLOWED", "Method not allowed.");
    if (method === "POST") {
      const origin = req.headers.get("origin");
      if (origin && origin !== url.origin) {
        fail(403, "CSRF", "The request origin does not match this application.");
      }
    }
    if (path === "/api/login" && method === "POST") {
      const p = await body(req),
        email = text(p.email, "email", 200).toLowerCase(),
        password = text(p.password, "password", 200);
      const u = (await pool.query(
        "SELECT u.*,m.org_id,m.role,o.name AS org_name FROM users u JOIN memberships m ON m.user_id=u.id JOIN organizations o ON o.id=m.org_id WHERE u.email=$1",
        [email],
      )).rows[0];
      if (!u || !checkPassword(password, u.password_hash)) {
        fail(401, "INVALID_CREDENTIALS", "Email or password is incorrect.");
      }
      const token = crypto.randomUUID() + crypto.randomUUID(), csrfToken = crypto.randomUUID();
      await pool.query(
        "INSERT INTO sessions(token_hash,user_id,csrf_token,expires_at) VALUES($1,$2,$3,clock_timestamp()+interval '8 hours')",
        [await hash(token), u.id, csrfToken],
      );
      const actor: Actor = {
        id: u.id,
        email: u.email,
        name: u.name,
        orgId: u.org_id,
        orgName: u.org_name,
        role: u.role,
        csrfToken,
      };
      const db = await pool.connect();
      let clock;
      try {
        clock = await now(db);
      } finally {
        db.release();
      }
      return json({ user: actor, csrfToken, now: clock }, 200, { "set-cookie": cookie(token) });
    }
    const actor = await authenticate(req);
    if (method === "POST" && req.headers.get("x-csrf-token") !== actor.csrfToken) {
      fail(403, "CSRF", "Refresh your session and retry with the session CSRF token.");
    }
    if (path === "/api/session" && method === "GET") {
      const db = await pool.connect();
      try {
        return json({ user: actor, csrfToken: actor.csrfToken, now: await now(db) });
      } finally {
        db.release();
      }
    }
    if (path === "/api/logout" && method === "POST") {
      await pool.query("DELETE FROM sessions WHERE user_id=$1 AND csrf_token=$2", [
        actor.id,
        actor.csrfToken,
      ]);
      return json({ ok: true }, 200, { "set-cookie": cookie("", true) });
    }
    if (path === "/api/bootstrap" && method === "GET") return json(await bootstrap(actor));
    if (path === "/api/capacity" && method === "GET") {
      return json(
        await getCapacity(
          actor,
          text(url.searchParams.get("warehouseId"), "warehouseId"),
          text(url.searchParams.get("zoneId"), "zoneId"),
          text(url.searchParams.get("startAt"), "startAt"),
          text(url.searchParams.get("endAt"), "endAt"),
        ),
      );
    }
    if (path === "/api/quotes" && method === "POST") {
      return json(await createQuote(actor, await body(req) as QuoteRequest), 201);
    }
    if (path === "/api/holds" && method === "POST") {
      const p = await body(req);
      return json(await createHold(actor, text(p.quoteId, "quoteId")), 201);
    }
    const hold = path.match(/^\/api\/holds\/([^/]+)\/confirm$/);
    if (hold && method === "POST") {
      return json(
        await confirmHold(actor, hold[1], await body(req), req.headers.get("idempotency-key") ?? ""),
      );
    }
    const res = path.match(/^\/api\/reservations\/([^/]+)(?:\/([^/]+))?$/);
    if (res) {
      const id = res[1], action = res[2];
      if (method === "GET" && !action) {
        return json(await withReservation(actor, id, (db) => reservationSummary(db, id)));
      }
      if (method === "GET" && action === "invoice") return json(await getInvoice(actor, id));
      if (method === "POST") {
        const p = await body(req), key = req.headers.get("idempotency-key") ?? "";
        if (action === "receive") return json(await receive(actor, id, p as ReceiveRequest, key));
        if (action === "release") return json(await release(actor, id, p as ReleaseRequest, key));
        if (action === "release-requests") return json(await requestRelease(actor, id, p), 201);
        if (action === "close-receiving") return json(await closeReceiving(actor, id, p));
        if (action === "cancel" || action === "expire") {
          return json(await terminateReservation(actor, id, action, p));
        }
        if (action === "amend") return json(await amendReservation(actor, id, p));
      }
    }
    const pallet = path.match(/^\/api\/pallets\/([^/]+)(?:\/(relocate))?$/);
    if (pallet) {
      if (method === "POST" && pallet[2]) return json(await relocate(actor, pallet[1], await body(req)));
      if (method === "GET" && !pallet[2]) {
        const db = await pool.connect();
        try {
          await palletAccess(db, actor, pallet[1]);
          return json(await palletDTO(db, pallet[1]));
        } finally {
          db.release();
        }
      }
    }
    if (path === "/api/blocks" && method === "POST") {
      return json(await blockCapacity(actor, await body(req)), 201);
    }
    const block = path.match(/^\/api\/blocks\/([^/]+)\/unblock$/);
    if (block && method === "POST") {
      operator(actor);
      const p = await body(req), reason = text(p.reason, "reason", 500);
      const b = (await pool.query(
        "SELECT b.* FROM capacity_blocks b JOIN warehouses w ON w.id=b.warehouse_id WHERE b.id=$1 AND w.operator_org_id=$2",
        [block[1], actor.orgId],
      )).rows[0];
      assert(b, "NOT_FOUND", "Block not found.", 404);
      return json(
        await warehouseTx(actor, b.warehouse_id, async (db, clock) => {
          const updated = (await db.query(
            "UPDATE capacity_blocks SET revoked_at=$1 WHERE id=$2 AND revoked_at IS NULL RETURNING *",
            [clock, b.id],
          )).rows[0];
          assert(updated, "INVALID_TRANSITION", "This block is already cleared.", 409);
          await audit(db, actor, clock, "capacity.unblocked", {
            warehouseId: b.warehouse_id,
            reason,
            data: { blockId: b.id },
          });
          return { id: b.id, revokedAt: clock };
        }),
      );
    }
    const adjustment = path.match(/^\/api\/movements\/([^/]+)\/adjustments$/);
    if (adjustment && method === "POST") {
      return json(
        await adjustMovement(actor, adjustment[1], await body(req), req.headers.get("idempotency-key") ?? ""),
        201,
      );
    }
    if (path === "/api/demo/clock" && method === "POST") {
      return json(await clockAction(actor, await body(req)));
    }
    if (path === "/api/audit" && method === "GET") {
      const db = await pool.connect();
      try {
        const reservationId = url.searchParams.get("reservationId");
        if (reservationId) await reservationAccess(db, actor, reservationId);
        const events = (await db.query(
          `SELECT a.*,u.name AS actor_name FROM audit_events a JOIN users u ON u.id=a.actor_id LEFT JOIN warehouses w ON w.id=a.warehouse_id WHERE ($1::text IS NULL OR a.reservation_id=$1) AND (($2='customer' AND a.customer_org_id=$3) OR ($2='operator' AND (w.operator_org_id=$3 OR (a.warehouse_id IS NULL AND a.actor_id=$4)))) ORDER BY a.recorded_at,a.id`,
          [reservationId, actor.role, actor.orgId, actor.id],
        )).rows.map((a) => ({
          id: a.id,
          action: a.action,
          actorName: a.actor_name,
          reservationId: a.reservation_id,
          occurredAt: a.occurred_at,
          recordedAt: a.recorded_at,
          reason: a.reason,
          data: a.data,
        }));
        return json({ events });
      } finally {
        db.release();
      }
    }
    fail(404, "NOT_FOUND", "API route not found.");
  } catch (error) {
    if (error instanceof AppError) {
      return json({
        error: {
          code: error.code,
          message: error.message,
          details: error.details,
          retryable: error.retryable,
        },
      }, error.status);
    }
    const code = (error as { code?: string }).code;
    if (code === "23505") {
      return json({
        error: {
          code: "RESOURCE_CONFLICT",
          message: "This identifier or position was already used. Refresh and inspect the recorded state.",
          retryable: false,
        },
      }, 409);
    }
    console.error("svc_warehouse request failed:", error);
    return json({
      error: {
        code: "INTERNAL_ERROR",
        message: "The operation could not be completed. No partial changes were saved.",
        retryable: true,
      },
    }, 500);
  }
}
export const createHandler = () => handler;
if (import.meta.main) {
  const port = Number(Deno.env.get("PORT") ?? 8000), hostname = Deno.env.get("HOST") ?? "127.0.0.1";
  const server = Deno.serve({
    port,
    hostname,
    onListen: () =>
      console.log(`svc_warehouse listening on http://${hostname}:${port} — synthetic demonstration`),
  }, handler);
  let stopping = false;
  const stop = () => {
    if (stopping) return;
    stopping = true;
    void server.shutdown().then(() => pool.end()).then(() => Deno.exit(0)).catch((error) => {
      console.error("Shutdown failed", error);
      Deno.exit(1);
    });
  };
  if (Deno.build.os !== "windows") {
    Deno.addSignalListener("SIGINT", stop);
    Deno.addSignalListener("SIGTERM", stop);
  }
}