app/db/inventory.ts

Functions

Imports

It imports no other module of this repo. Imported by app/lab/inventory.server.ts, app/lab/runs.server.ts.

Source

1// The lab's lots, locations and their audit (drizzle/0029_kb_inventory.sql): D1 is their source. Every write to a lot
2// or a location goes through the statements here, which put its kb_audit row in the same batch, so no lot changes
3// without one (docs/KNOWLEDGE-BASE.md steps 4 and 7).
4
5/** A lot as the audit stores it, read by SQLite itself inside the write's transaction, so before and after are exact. */
6const LOT_JSON = `json_object('id', id, 'item_kind', item_kind, 'item_id', item_id, 'lot_number', lot_number, 'expiry', expiry, 'amount', amount, 'unit', unit, 'location_id', location_id, 'received', received, 'opened', opened, 'status', status)`;
7const LOCATION_JSON = `json_object('id', id, 'name', name, 'parent_id', parent_id, 'note', note)`;
8
9export type LotRow = {
10  id: number;
11  item_kind: string;
12  item_id: string;
13  lot_number: string;
14  expiry: string | null;
15  amount: number | null;
16  unit: string | null;
17  location_id: number | null;
18  location: string | null;
19  received: string | null;
20  opened: string | null;
21  status: "in_stock" | "empty" | "discarded";
22};
23
24export type LocationRow = { id: number; name: string; parent_id: number | null; note: string | null };
25
26export type AuditRow = { id: number; at: number; actor: string; action: string; before_row: string | null; after_row: string | null };
27
28const LOT_SELECT = `SELECT l.id, l.item_kind, l.item_id, l.lot_number, l.expiry, l.amount, l.unit, l.location_id, c.name AS location,
29  l.received, l.opened, l.status FROM kb_lots l LEFT JOIN kb_locations c ON c.id = l.location_id`;
30
31export async function listLots(env: Env): Promise<LotRow[]> {
32  const { results } = await env.DB.prepare(`${LOT_SELECT} ORDER BY l.status = 'in_stock' DESC, l.item_kind, l.item_id, l.lot_number`).all<LotRow>();
33  return results;
34}
35
36export async function getLot(env: Env, id: number): Promise<LotRow | null> {
37  return env.DB.prepare(`${LOT_SELECT} WHERE l.id = ?`).bind(id).first<LotRow>();
38}
39
40export async function listLocations(env: Env): Promise<LocationRow[]> {
41  const { results } = await env.DB.prepare("SELECT id, name, parent_id, note FROM kb_locations ORDER BY name").all<LocationRow>();
42  return results;
43}
44
45export async function lotAudit(env: Env, lotId: number): Promise<AuditRow[]> {
46  const { results } = await env.DB.prepare(
47    "SELECT id, at, actor, action, before_row, after_row FROM kb_audit WHERE table_name = 'kb_lots' AND row_id = ? ORDER BY id DESC",
48  )
49    .bind(lotId)
50    .all<AuditRow>();
51  return results;
52}
53
54/**
55 * A change to one lot, with its audit row: the lot as it was is copied into a new kb_audit row, the change runs, and the
56 * lot as it is now is copied onto that row. One batch is one transaction, so the row cannot miss the change or
57 * describe a lot that never existed.
58 */
59export function lotChange(env: Env, actor: string, lotId: number, update: D1PreparedStatement): D1PreparedStatement[] {
60  return [
61    env.DB.prepare(`INSERT INTO kb_audit (actor, table_name, row_id, action, before_row) SELECT ?, 'kb_lots', id, 'update', ${LOT_JSON} FROM kb_lots WHERE id = ?`).bind(
62      actor,
63      lotId,
64    ),
65    update,
66    env.DB.prepare(
67      `UPDATE kb_audit SET after_row = (SELECT ${LOT_JSON} FROM kb_lots WHERE id = ?1) WHERE id = (SELECT max(id) FROM kb_audit WHERE table_name = 'kb_lots' AND row_id = ?1)`,
68    ).bind(lotId),
69  ];
70}
71
72export type NewLot = Omit<LotRow, "id" | "location" | "status" | "opened">;
73
74/** A new lot and its insert audit row. */
75export function lotInsert(env: Env, actor: string, lot: NewLot): D1PreparedStatement[] {
76  return [
77    env.DB.prepare(
78      "INSERT INTO kb_lots (item_kind, item_id, lot_number, expiry, amount, unit, location_id, received) VALUES (?, ?, ?, ?, ?, ?, ?, ?)",
79    ).bind(lot.item_kind, lot.item_id, lot.lot_number, lot.expiry, lot.amount, lot.unit, lot.location_id, lot.received),
80    env.DB.prepare(
81      `INSERT INTO kb_audit (actor, table_name, row_id, action, after_row) SELECT ?, 'kb_lots', id, 'insert', ${LOT_JSON} FROM kb_lots WHERE item_kind = ? AND item_id = ? AND lot_number = ?`,
82    ).bind(actor, lot.item_kind, lot.item_id, lot.lot_number),
83  ];
84}
85
86/** A new location and its insert audit row. */
87export function locationInsert(env: Env, actor: string, name: string, parentId: number | null): D1PreparedStatement[] {
88  return [
89    env.DB.prepare("INSERT INTO kb_locations (name, parent_id) VALUES (?, ?)").bind(name, parentId),
90    env.DB.prepare(`INSERT INTO kb_audit (actor, table_name, row_id, action, after_row) SELECT ?, 'kb_locations', id, 'insert', ${LOCATION_JSON} FROM kb_locations WHERE name = ?`).bind(
91      actor,
92      name,
93    ),
94  ];
95}