app/db/procedures.ts

Functions

Imports

It imports app/db/client.ts, app/db/schema.ts, app/kb/procedures/render.mjs, app/lib/search/visibility.mjs. Imported by app/kb/procedures/lineage.server.ts, app/lib/carrel/procedure-handler.server.ts, app/lib/llms/save.server.ts, app/lib/operator/api.server.ts, app/lib/pages/save.server.ts, app/lib/site-urls.server.ts, app/routes/admin.kb.tsx, app/routes/kb-library.tsx, app/routes/kb-library[.csv].ts, app/routes/kb-library[.json].ts, app/routes/kb-library[.md].ts, app/routes/lab.kind.tsx, app/routes/lab[.md].ts, app/routes/procedure.sheet.tsx, app/routes/procedure.tsx, app/routes/procedure.version.tsx, app/routes/procedure[.md].ts, app/routes/protocols.tsx, app/routes/protocols[.csv].ts, app/routes/protocols[.json].ts, app/routes/protocols[.md].ts.

Source

1// The procedures table: read by the procedure pages, the sitemap and the operator API. Written only by
2// writeProcedureRow, from a compile (app/kb/procedures/compile.mjs).
3import { and, asc, eq } from "drizzle-orm";
4
5import { PUBLISHED_STATUS } from "~/lib/search/visibility.mjs";
6import type { ProcedureRecord } from "~/kb/procedures/render.mjs";
7
8import { getDb } from "./client";
9import { procedures } from "./schema";
10
11export type ProcedureRow = {
12  slug: string;
13  path: string;
14  status: "draft" | "published";
15  record: ProcedureRecord;
16};
17
18/** The page's row, or null. A draft comes back too: the route decides who may see it. */
19export async function getProcedureByPath(env: Env, path: string): Promise<ProcedureRow | null> {
20  const row = (
21    await getDb(env)
22      .select({ slug: procedures.slug, path: procedures.path, status: procedures.status, record: procedures.record })
23      .from(procedures)
24      .where(eq(procedures.path, path))
25      .limit(1)
26  )[0];
27  if (!row) return null;
28  return { ...row, record: JSON.parse(row.record) as ProcedureRecord };
29}
30
31/** The twin, published rows only: a draft's twin answers 404 like its page. */
32export async function getPublishedProcedureMarkdown(env: Env, path: string) {
33  const row = (
34    await getDb(env)
35      .select({ markdown: procedures.markdown })
36      .from(procedures)
37      .where(and(eq(procedures.path, path), eq(procedures.status, PUBLISHED_STATUS)))
38      .limit(1)
39  )[0];
40  return row?.markdown ?? null;
41}
42
43/** For the sitemap and llms-full: the published pages, by path. */
44export async function listPublishedProcedures(env: Env) {
45  return getDb(env)
46    .select({
47      path: procedures.path,
48      title: procedures.title,
49      description: procedures.description,
50      updated: procedures.updated,
51      markdown: procedures.markdown,
52      sourcePath: procedures.sourcePath,
53    })
54    .from(procedures)
55    .where(eq(procedures.status, PUBLISHED_STATUS))
56    .orderBy(asc(procedures.path));
57}
58
59/** For the operator's list_procedures: every row, drafts included. */
60export async function listProceduresForOperator(env: Env) {
61  const rows = await getDb(env)
62    .select({
63      slug: procedures.slug,
64      path: procedures.path,
65      profile: procedures.profile,
66      title: procedures.title,
67      status: procedures.status,
68      version: procedures.version,
69      updated: procedures.updated,
70      record: procedures.record,
71    })
72    .from(procedures)
73    .orderBy(asc(procedures.slug));
74  return rows.map(({ record, status, ...row }) => {
75    const parsed = JSON.parse(record) as ProcedureRecord;
76    return {
77      ...row,
78      draft: status === "draft",
79      gaps: parsed.gaps.length,
80      // The admin's view, so Dustin can find the protocols still to set: the level, or MISSING. Public output never says MISSING.
81      biosafetyLevel: row.profile === "protocol" ? (parsed.biosafetyLevel ?? "MISSING") : null,
82    };
83  });
84}
85
86/** A knowledge base's published records, for its library (app/kb/libraries.mjs). */
87export async function listPublishedRecordsOfProfile(env: Env, profile: "protocol" | "recipe" | "computational"): Promise<ProcedureRecord[]> {
88  const rows = await getDb(env)
89    .select({ record: procedures.record })
90    .from(procedures)
91    .where(and(eq(procedures.status, PUBLISHED_STATUS), eq(procedures.profile, profile)))
92    .orderBy(asc(procedures.path));
93  return rows.map((row) => JSON.parse(row.record) as ProcedureRecord);
94}
95
96/** For the protocol library: every published protocol, with its full record. */
97export async function listPublishedLibraryRecords(env: Env): Promise<ProcedureRecord[]> {
98  const rows = await getDb(env)
99    .select({ record: procedures.record })
100    .from(procedures)
101    .where(and(eq(procedures.status, PUBLISHED_STATUS), eq(procedures.profile, "protocol")))
102    .orderBy(asc(procedures.path));
103  return rows.map((row) => JSON.parse(row.record) as ProcedureRecord);
104}
105
106/** For the admin's Knowledge Base page: every row, drafts included, with each recorded gap as the compile stated it. */
107export async function listProceduresForAdmin(env: Env) {
108  const rows = await getDb(env)
109    .select({
110      slug: procedures.slug,
111      path: procedures.path,
112      profile: procedures.profile,
113      title: procedures.title,
114      description: procedures.description,
115      status: procedures.status,
116      version: procedures.version,
117      updated: procedures.updated,
118      record: procedures.record,
119    })
120    .from(procedures)
121    .orderBy(asc(procedures.slug));
122  return rows.map(({ record, status, ...row }) => ({
123    ...row,
124    draft: status === "draft",
125    gaps: (JSON.parse(record) as ProcedureRecord).gaps,
126  }));
127}