app/db/runs.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// Run records (drizzle/0031_kb_runs.sql): append-only. Rows are only ever inserted here; the migration's triggers refuse
2// any UPDATE or DELETE, so a correction is a new run naming the one it replaces.
3
4export type RunRow = {
5  id: number;
6  uid: string;
7  procedure_slug: string;
8  procedure_version: string | null;
9  actor: string;
10  run_on: string;
11  outcome: "completed" | "failed" | "abandoned";
12  note: string | null;
13  replaces_run_id: number | null;
14  replaced_by: number | null;
15  created_at: number;
16};
17
18export type RunLotRow = {
19  run_id: number;
20  lot_id: number;
21  amount_used: number | null;
22  unit: string | null;
23  emptied: number;
24  item_kind: string;
25  item_id: string;
26  lot_number: string;
27};
28
29const RUN_SELECT = `SELECT r.id, r.uid, r.procedure_slug, r.procedure_version, r.actor, r.run_on, r.outcome, r.note, r.replaces_run_id,
30  (SELECT n.id FROM kb_runs n WHERE n.replaces_run_id = r.id) AS replaced_by, r.created_at FROM kb_runs r`;
31
32/** Runs newest first: everyone's, or one person's. */
33export async function listRuns(env: Env, actor: string | null): Promise<RunRow[]> {
34  const statement = actor
35    ? env.DB.prepare(`${RUN_SELECT} WHERE r.actor = ? ORDER BY r.id DESC LIMIT 200`).bind(actor)
36    : env.DB.prepare(`${RUN_SELECT} ORDER BY r.id DESC LIMIT 200`);
37  return (await statement.all<RunRow>()).results;
38}
39
40export async function getRun(env: Env, id: number): Promise<RunRow | null> {
41  return env.DB.prepare(`${RUN_SELECT} WHERE r.id = ?`).bind(id).first<RunRow>();
42}
43
44export async function runLots(env: Env, runId: number): Promise<RunLotRow[]> {
45  const { results } = await env.DB.prepare(
46    `SELECT u.run_id, u.lot_id, u.amount_used, u.unit, u.emptied, l.item_kind, l.item_id, l.lot_number
47     FROM kb_run_lots u JOIN kb_lots l ON l.id = u.lot_id WHERE u.run_id = ? ORDER BY l.item_kind, l.item_id`,
48  )
49    .bind(runId)
50    .all<RunLotRow>();
51  return results;
52}
53
54/** Every run that used a lot, newest first: the trace from a bad lot to its runs. Narrowed to one person's when given. */
55export async function runsUsingLot(env: Env, lotId: number, actor: string | null): Promise<RunRow[]> {
56  const where = actor ? "AND r.actor = ?" : "";
57  const statement = env.DB.prepare(`${RUN_SELECT} WHERE r.id IN (SELECT run_id FROM kb_run_lots WHERE lot_id = ?) ${where} ORDER BY r.id DESC`);
58  return (await (actor ? statement.bind(lotId, actor) : statement.bind(lotId)).all<RunRow>()).results;
59}
60
61export type NewRun = {
62  uid: string;
63  procedure_slug: string;
64  procedure_version: string | null;
65  actor: string;
66  run_on: string;
67  outcome: RunRow["outcome"];
68  note: string | null;
69  replaces_run_id: number | null;
70};
71
72/** The run's own row and one row per lot it used, the lots naming the run by its uid in the same batch. */
73export function runInsert(env: Env, run: NewRun, lots: Array<{ lot_id: number; amount_used: number | null; unit: string | null; emptied: boolean }>): D1PreparedStatement[] {
74  return [
75    env.DB.prepare(
76      "INSERT INTO kb_runs (uid, procedure_slug, procedure_version, actor, run_on, outcome, note, replaces_run_id) VALUES (?, ?, ?, ?, ?, ?, ?, ?)",
77    ).bind(run.uid, run.procedure_slug, run.procedure_version, run.actor, run.run_on, run.outcome, run.note, run.replaces_run_id),
78    ...lots.map((l) =>
79      env.DB.prepare("INSERT INTO kb_run_lots (run_id, lot_id, amount_used, unit, emptied) SELECT id, ?, ?, ?, ? FROM kb_runs WHERE uid = ?").bind(
80        l.lot_id,
81        l.amount_used,
82        l.unit,
83        l.emptied ? 1 : 0,
84        run.uid,
85      ),
86    ),
87  ];
88}
89
90export async function runIdByUid(env: Env, uid: string): Promise<number | null> {
91  const row = await env.DB.prepare("SELECT id FROM kb_runs WHERE uid = ?").bind(uid).first<{ id: number }>();
92  return row?.id ?? null;
93}
94
95/** The protocols a run can follow: the published ones, by title. */
96export async function listRunnableProcedures(env: Env): Promise<Array<{ slug: string; title: string; version: string | null }>> {
97  const { results } = await env.DB.prepare(
98    "SELECT slug, title, version FROM procedures WHERE status = 'published' AND profile = 'protocol' ORDER BY title",
99  ).all<{ slug: string; title: string; version: string | null }>();
100  return results;
101}