app/db/procedure-versions.ts

Functions

Imports

It imports app/db/client.ts, app/db/schema.ts, app/kb/procedures/render.mjs. Imported by app/kb/procedures/lineage.server.ts, app/kb/procedures/save.server.ts, app/routes/procedure.tsx, app/routes/procedure.version.sheet.tsx, app/routes/procedure.version.tsx, app/routes/procedure.version[.md].ts.

Source

1// Frozen procedure versions (drizzle/0027_procedure_versions.sql): the rendered copy of every published version,
2// written once and never changed. Read by the version pages and the twin at `<page>/v/<version>`; written by
3// freezeStatement, in the same batch as the live row (app/kb/procedures/save.server.ts, writeRow).
4import { and, asc, eq } from "drizzle-orm";
5
6import type { ProcedureRecord } from "~/kb/procedures/render.mjs";
7
8import { getDb } from "./client";
9import { procedureVersions } from "./schema";
10
11export type FrozenVersion = {
12  slug: string;
13  version: string;
14  path: string;
15  profile: "protocol" | "recipe" | "computational";
16  record: ProcedureRecord;
17  markdown: string;
18  sourceBlobSha: string;
19};
20
21/** The sealed copy of a procedure's version, or null. An unsealed row (a sync part way through) is never served. */
22export async function getFrozenVersion(env: Env, path: string, version: string): Promise<FrozenVersion | null> {
23  const row = (
24    await getDb(env)
25      .select()
26      .from(procedureVersions)
27      .where(and(eq(procedureVersions.path, path), eq(procedureVersions.version, version), eq(procedureVersions.sealed, 1)))
28      .limit(1)
29  )[0];
30  if (!row) return null;
31  return {
32    slug: row.slug,
33    version: row.version,
34    path: row.path,
35    profile: row.profile,
36    record: JSON.parse(row.record) as ProcedureRecord,
37    markdown: row.markdown,
38    sourceBlobSha: row.sourceBlobSha,
39  };
40}
41
42/** The frozen versions of a procedure, oldest first, as the history links them. */
43export async function listFrozenVersions(env: Env, slug: string): Promise<string[]> {
44  const rows = await getDb(env)
45    .select({ version: procedureVersions.version })
46    .from(procedureVersions)
47    .where(and(eq(procedureVersions.slug, slug), eq(procedureVersions.sealed, 1)))
48    .orderBy(asc(procedureVersions.frozenAt));
49  return rows.map((r) => r.version);
50}
51
52/** The blob sha a version was frozen from, or null when it has no sealed copy. */
53export async function frozenBlobSha(env: Env, slug: string, version: string): Promise<string | null> {
54  const row = await env.DB.prepare("SELECT source_blob_sha FROM procedure_versions WHERE slug = ?1 AND version = ?2 AND sealed = 1")
55    .bind(slug, version)
56    .first<{ source_blob_sha: string }>();
57  return row?.source_blob_sha ?? null;
58}
59
60/**
61 * The statement that freezes a version: inserted sealed, and left alone when a sealed copy exists. It may replace a
62 * row still unsealed (an interrupted sync:content), which the triggers allow. Bound parameters carry the record and the
63 * twin, so D1's 100 KB statement cap does not apply.
64 */
65export function freezeStatement(
66  db: D1Database,
67  frozen: { slug: string; version: string; path: string; profile: string; record: string; markdown: string; sourceBlobSha: string },
68) {
69  return db
70    .prepare(
71      `INSERT INTO procedure_versions (slug, version, path, profile, record, markdown, source_blob_sha, sealed)
72       VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, 1)
73       ON CONFLICT(slug, version) DO UPDATE SET path = excluded.path, profile = excluded.profile, record = excluded.record,
74         markdown = excluded.markdown, source_blob_sha = excluded.source_blob_sha, sealed = 1, frozen_at = unixepoch()
75       WHERE procedure_versions.sealed = 0`,
76    )
77    .bind(frozen.slug, frozen.version, frozen.path, frozen.profile, frozen.record, frozen.markdown, frozen.sourceBlobSha);
78}