app/db/schema.ts

Functions

Imports

It imports no other module of this repo. Imported by app/db/client.ts, app/db/cv.ts, app/db/dictionary.ts, app/db/media.ts, app/db/members.ts, app/db/pages.ts, app/db/phages.ts, app/db/posts.ts, app/db/procedure-versions.ts, app/db/procedures.ts, app/db/publications.ts, app/db/registry.ts, app/db/roster.ts, app/db/settings.ts, app/db/webmentions.ts, app/lib/carrel/media-adapter.server.ts, app/lib/carrel/site-adapter.server.ts, app/lib/citations.server.ts, app/lib/media/actions.server.ts, app/lib/operator/api.server.ts, app/lib/operator/sync-tools.server.ts, app/lib/search/zero-result.server.ts, app/routes/admin.mentions.tsx.

Source

1import { sql } from "drizzle-orm";
2import {
3  type AnySQLiteColumn,
4  check,
5  index,
6  integer,
7  primaryKey,
8  real,
9  sqliteTable,
10  text,
11  uniqueIndex,
12} from "drizzle-orm/sqlite-core";
13
14/** Pages and posts, split by `kind`. FTS5 mirrors (title, body) through triggers in drizzle/0001_init.sql. */
15export const posts = sqliteTable(
16  "posts",
17  {
18    id: integer("id").primaryKey({ autoIncrement: true }),
19    slug: text("slug").notNull().unique(),
20    kind: text("kind", { enum: ["page", "post"] }).notNull(),
21    title: text("title").notNull(),
22    body: text("body").notNull(),
23    excerpt: text("excerpt"),
24    status: text("status", { enum: ["draft", "published"] }).notNull(),
25    publishAt: integer("publish_at", { mode: "timestamp" }),
26    createdAt: integer("created_at", { mode: "timestamp" })
27      .notNull()
28      .default(sql`(unixepoch())`),
29    updatedAt: integer("updated_at", { mode: "timestamp" })
30      .notNull()
31      .default(sql`(unixepoch())`),
32    html: text("html"),
33    description: text("description"),
34    coverImage: text("cover_image"),
35    coverAlt: text("cover_alt"),
36    readingTimeMinutes: integer("reading_time_minutes"),
37    sourcePath: text("source_path"),
38    /** JSON array of { depth, id, text }, written by the content generator. */
39    toc: text("toc"),
40    featured: integer("featured", { mode: "boolean" }).notNull().default(false),
41    series: text("series"),
42    part: integer("part"),
43    /** JSON array of { title, url }. */
44    furtherReading: text("further_reading"),
45    ogTitle: text("og_title"),
46    ogDescription: text("og_description"),
47    /** JSON array of { slug, title, shared }, computed over the whole corpus. */
48    related: text("related"),
49    /** JSON array of { slug, title }: posts on THIS site linking here. Not webmentions, which are other sites. */
50    backlinks: text("backlinks"),
51    /**
52     * Not `status`, which is the draft/published visibility column: a published post can still carry
53     * `writing_status: draft`, meaning the prose is a draft.
54     */
55    writingStatus: text("writing_status"),
56    assumedAudience: text("assumed_audience"),
57    /** JSON array of strings. */
58    keyTakeaways: text("key_takeaways"),
59    /** JSON array of { date, note }, authored. Not `updatedAt`, which every sync rewrites. */
60    changelog: text("changelog"),
61    ogImage: text("og_image"),
62    /** Git blob sha1 of the source markdown (`gitBlobSha()`), what the content-drift check compares. */
63    sourceBlobSha: text("source_blob_sha"),
64    renderHash: text("render_hash"),
65  },
66  (t) => [
67    check("posts_kind_check", sql`${t.kind} in ('page', 'post')`),
68    check("posts_status_check", sql`${t.status} in ('draft', 'published')`),
69    /** Covers `publiclyVisible()`, which every public read composes. */
70    index("posts_status_publish_idx").on(t.status, t.publishAt),
71    uniqueIndex("posts_source_path_idx").on(t.sourcePath),
72    /** Covers the home page's featured selection. */
73    index("posts_featured_idx").on(t.featured, t.publishAt),
74    /** Covers a series' parts, in order. */
75    index("posts_series_idx").on(t.series, t.part),
76  ],
77);
78
79export const tags = sqliteTable("tags", {
80  id: integer("id").primaryKey({ autoIncrement: true }),
81  slug: text("slug").notNull().unique(),
82  name: text("name").notNull(),
83});
84
85export const postTags = sqliteTable(
86  "post_tags",
87  {
88    postId: integer("post_id")
89      .notNull()
90      .references(() => posts.id, { onDelete: "cascade" }),
91    tagId: integer("tag_id")
92      .notNull()
93      .references(() => tags.id, { onDelete: "cascade" }),
94  },
95  (t) => [
96    primaryKey({ columns: [t.postId, t.tagId] }),
97    index("post_tags_tag_idx").on(t.tagId),
98  ],
99);
100
101export const settings = sqliteTable("settings", {
102  key: text("key").primaryKey(),
103  value: text("value").notNull(),
104});
105
106/**
107 * Derived: R2 and `public/` win any conflict. A row with no object is deleted, an object with no row
108 * is backfilled, never the reverse. Citations live in `mediaRefs`, never here.
109 */
110export const media = sqliteTable(
111  "media",
112  {
113    /** R2 object key, or the site-absolute public path when storage='static'. */
114    key: text("key").primaryKey(),
115    /** 'r2' | 'r2-derived' | 'static'. */
116    storage: text("storage").notNull().default("r2"),
117    /** 'image' | 'document' | 'other'. */
118    kind: text("kind").notNull().default("image"),
119    /**
120     * 'content' | 'brand' | 'generated' | 'icon'. The picker takes `content` only, because inserting
121     * one half of a generated diagram pair produces a broken post.
122     */
123    role: text("role").notNull().default("content"),
124    mime: text("mime"),
125    bytes: integer("bytes"),
126    /** NULL means "not measured", which an SVG and a PDF legitimately are. */
127    width: integer("width"),
128    height: integer("height"),
129    /** Keys are content-addressed, so the readable upload name survives only here. */
130    originalName: text("original_name"),
131
132    /* Authored, and recoverable from nothing: a rebuild merges these rather than truncating. */
133    alt: text("alt").notNull().default(""),
134    caption: text("caption").notNull().default(""),
135    focalX: real("focal_x"),
136    focalY: real("focal_y"),
137
138    /**
139     * Delimiter-wrapped, `,alpha,beta,` or "", so an exact LIKE match on `art` does not hit `chart`.
140     * Only `serialiseTags()` writes it; it strips the delimiter and both LIKE wildcards.
141     */
142    tags: text("tags").notNull().default(""),
143
144    /**
145     * NULL means not trashed. Library state only: R2 and the public URL are untouched, so a trashed
146     * asset a post cites keeps rendering. Reconciliation ignores it on purpose.
147     */
148    trashedAt: text("trashed_at"),
149
150    /** LQIP as a base64 data URI, so it renders with no script. */
151    placeholder: text("placeholder"),
152    uploadedAt: text("uploaded_at"),
153    createdAt: text("created_at").notNull().default(sql`(datetime('now'))`),
154    updatedAt: text("updated_at").notNull().default(sql`(datetime('now'))`),
155  },
156  (t) => [
157    index("media_uploaded_idx").on(t.uploadedAt),
158    index("media_storage_idx").on(t.storage),
159    index("media_kind_idx").on(t.kind),
160    index("media_role_idx").on(t.role),
161    /*
162     * Partial in the migration (`WHERE trashed_at IS NOT NULL`). Drizzle cannot model the predicate
163     * and the schema test does not compare predicates, so it lives only in the SQL.
164     */
165    index("media_trashed_idx").on(t.trashedAt),
166  ],
167);
168
169/**
170 * Who cites what, written by the pipeline at render time. A record of what the renderer emitted,
171 * never a cache to consult when deciding whether an object exists.
172 */
173export const mediaRefs = sqliteTable(
174  "media_refs",
175  {
176    mediaKey: text("media_key").notNull(),
177    sourceType: text("source_type").notNull(),
178    sourceId: text("source_id").notNull(),
179    /** markdown-image | figure-directive | frontmatter-cover | link | html | other */
180    form: text("form").notNull(),
181    /** Line number, so a refusal can name where the citation is. */
182    detail: text("detail"),
183  },
184  (t) => [
185    primaryKey({
186      columns: [t.mediaKey, t.sourceType, t.sourceId, t.form, t.detail],
187    }),
188    index("media_refs_key_idx").on(t.mediaKey),
189    index("media_refs_source_idx").on(t.sourceType, t.sourceId),
190  ],
191);
192
193/**
194 * The site-wide search index, derived. Every read stays in raw SQL: the visibility check scans raw
195 * SQL for the composed predicate and would not see a query-builder read. The two FTS5 mirrors are
196 * `CREATE VIRTUAL TABLE` and stay out of drizzle.
197 */
198export const searchDocs = sqliteTable(
199  "search_docs",
200  {
201    id: integer("id").primaryKey({ autoIncrement: true }),
202    uid: text("uid").notNull().unique(),
203    url: text("url").notNull(),
204    type: text("type", { enum: ["post", "page"] }).notNull(),
205    title: text("title").notNull(),
206    body: text("body").notNull(),
207    /** Feeds the identity index. Carried by the DOCUMENT record only. */
208    tags: text("tags").notNull().default(""),
209    /** Feeds filtering and facets. Carried by EVERY record, pipe-delimited. */
210    docTags: text("doc_tags").notNull().default(""),
211    /** Grouping. For a document record this equals `uid` and `anchor` is null. */
212    docUid: text("doc_uid").notNull(),
213    docTitle: text("doc_title").notNull(),
214    docUrl: text("doc_url").notNull(),
215    anchor: text("anchor"),
216    ordinal: integer("ordinal").notNull().default(0),
217    /**
218     * Visibility is carried on the record, not joined: a future `publish_at` must be evaluated per
219     * request, or a scheduled post would leak or stay hidden once its date passed.
220     */
221    status: text("status", { enum: ["draft", "published"] }).notNull(),
222    publishAt: integer("publish_at"),
223  },
224  (t) => [
225    check("search_docs_type_check", sql`${t.type} in ('post', 'page')`),
226    check("search_docs_status_check", sql`${t.status} in ('draft', 'published')`),
227    /** Covers the visibility predicate every public search read composes. */
228    index("search_docs_visible_idx").on(t.status, t.publishAt),
229    /** Covers the type facet and its count. */
230    index("search_docs_type_idx").on(t.type),
231    /** Covers collapsing section hits back onto their owning document. */
232    index("search_docs_doc_idx").on(t.docUid, t.ordinal),
233  ],
234);
235
236/**
237 * Webmentions from other sites: neither authored nor derived, so no rebuild can repair it.
238 * No IP column, ever: `/privacy` relies on its absence. Author and excerpt fields come from pages
239 * this site does not control, so they are stored as plain text, never markup.
240 */
241export const webmentions = sqliteTable(
242  "webmentions",
243  {
244    id: integer("id").primaryKey({ autoIncrement: true }),
245    /** Absolute http(s) URL of the page that linked here. */
246    sourceUrl: text("source_url").notNull(),
247    /**
248     * A slug, not a URL: a stored URL carries the origin it arrived on, so a host change would split
249     * one post into two dedup targets.
250     */
251    targetSlug: text("target_slug").notNull(),
252    /**
253     * `unverified -> pending | failed`, then `pending -> approved | rejected`. The endpoint writes
254     * `unverified` before fetching so the global cap counts it at once. Only `approved` renders.
255     */
256    status: text("status", {
257      enum: ["unverified", "pending", "approved", "rejected", "failed"],
258    }).notNull(),
259    authorName: text("author_name"),
260    authorUrl: text("author_url"),
261    /** Plain text, at most 280 characters. Never markup. */
262    excerpt: text("excerpt"),
263    /** One of the fixed reasons in app/lib/webmention/verify.server.ts. */
264    failureReason: text("failure_reason"),
265    receivedAt: integer("received_at", { mode: "timestamp" })
266      .notNull()
267      .default(sql`(unixepoch())`),
268    verifiedAt: integer("verified_at", { mode: "timestamp" }),
269    decidedAt: integer("decided_at", { mode: "timestamp" }),
270  },
271  (t) => [
272    check(
273      "webmentions_status_check",
274      sql`${t.status} in ('unverified', 'pending', 'approved', 'rejected', 'failed')`,
275    ),
276    /** One row per (source, target), so re-announcing a mention cannot grow the table. */
277    uniqueIndex("webmentions_source_target_idx").on(t.sourceUrl, t.targetSlug),
278    /** Covers the moderation queue's grouping and the retention sweep's window. */
279    index("webmentions_status_received_idx").on(t.status, t.receivedAt),
280  ],
281);
282
283/**
284 * Zero-result search queries, kept as a demand signal. Neither authored nor derived, so nothing
285 * rebuilds it; the retention sweep and the primary key bound it. No IP, user agent, session or
286 * cookie column by design: `/privacy` states that as a fact about the schema.
287 */
288export const zeroResultQueries = sqliteTable(
289  "zero_result_queries",
290  {
291    /** Normalized by the writer, so a repeated miss is one row with a count, not many rows. */
292    query: text("query").primaryKey(),
293    count: integer("count").notNull().default(1),
294    firstSeen: integer("first_seen", { mode: "timestamp" })
295      .notNull()
296      .default(sql`(unixepoch())`),
297    lastSeen: integer("last_seen", { mode: "timestamp" })
298      .notNull()
299      .default(sql`(unixepoch())`),
300  },
301  (t) => [
302    /** Capped in the database too. The schema test does not compare check predicates, so the SQL owns the number. */
303    check("zero_result_queries_length_check", sql`length(${t.query}) <= 200`),
304    check("zero_result_queries_nonempty_check", sql`length(${t.query}) > 0`),
305    /** The admin list orders by demand. */
306    index("zero_result_queries_count_idx").on(t.count),
307    /** The 90-day retention sweep selects on the window. */
308    index("zero_result_queries_last_seen_idx").on(t.lastSeen),
309  ],
310);
311
312export type MediaRef = typeof mediaRefs.$inferSelect;
313export type Webmention = typeof webmentions.$inferSelect;
314
315/**
316 * Procedures (drizzle/0019_procedures.sql, docs/PROCEDURES.md): derived from content/procedures/*.md by
317 * app/kb/procedures/compile.mjs. `record` is the rendered procedure as JSON and `markdown` its twin.
318 */
319export const procedures = sqliteTable(
320  "procedures",
321  {
322    id: integer("id").primaryKey({ autoIncrement: true }),
323    slug: text("slug").notNull().unique(),
324    path: text("path").notNull().unique(),
325    profile: text("profile", { enum: ["protocol", "recipe", "computational"] }).notNull(),
326    title: text("title").notNull(),
327    description: text("description").notNull(),
328    status: text("status", { enum: ["draft", "published"] }).notNull(),
329    version: text("version"),
330    updated: text("updated"),
331    record: text("record").notNull(),
332    markdown: text("markdown").notNull(),
333    sourcePath: text("source_path").notNull().unique(),
334    sourceBlobSha: text("source_blob_sha").notNull(),
335    syncedAt: integer("synced_at", { mode: "timestamp" })
336      .notNull()
337      .default(sql`(unixepoch())`),
338  },
339  (t) => [
340    check("procedures_profile_check", sql`${t.profile} in ('protocol', 'recipe', 'computational')`),
341    check("procedures_status_check", sql`${t.status} in ('draft', 'published')`),
342    index("procedures_status_idx").on(t.status, t.profile),
343  ],
344);
345
346/**
347 * Frozen versions (drizzle/0027_procedure_versions.sql): the rendered copy of every published procedure version, written
348 * once and never changed. Unlike `procedures` this is not derived from the file: the triggers refuse an UPDATE or DELETE
349 * of a sealed row. `record` is the ProcedureRecord JSON and `markdown` its twin, as the live row held them when frozen.
350 */
351export const procedureVersions = sqliteTable(
352  "procedure_versions",
353  {
354    slug: text("slug").notNull(),
355    version: text("version").notNull(),
356    path: text("path").notNull(),
357    profile: text("profile", { enum: ["protocol", "recipe", "computational"] }).notNull(),
358    record: text("record").notNull(),
359    markdown: text("markdown").notNull(),
360    sourceBlobSha: text("source_blob_sha").notNull(),
361    sealed: integer("sealed").notNull().default(0),
362    frozenAt: integer("frozen_at", { mode: "timestamp" })
363      .notNull()
364      .default(sql`(unixepoch())`),
365  },
366  (t) => [
367    primaryKey({ columns: [t.slug, t.version] }),
368    check("procedure_versions_profile_check", sql`${t.profile} in ('protocol', 'recipe', 'computational')`),
369    check("procedure_versions_sealed_check", sql`${t.sealed} in (0, 1)`),
370  ],
371);
372
373/**
374 * Publications (drizzle/0020_publications.sql, docs/PUBLICATIONS.md): derived from content/publications/*.md
375 * by app/lib/publications/compile.mjs. `record` is the compiled publication as JSON, `csl` the raw
376 * Crossref record and `markdown` the twin.
377 */
378export const publications = sqliteTable(
379  "publications",
380  {
381    id: integer("id").primaryKey({ autoIncrement: true }),
382    slug: text("slug").notNull().unique(),
383    doiKey: text("doi_key"),
384    status: text("status", { enum: ["draft", "published"] }).notNull(),
385    stage: text("stage", { enum: ["published", "submitted"] }).notNull(),
386    type: text("type", { enum: ["article", "review", "chapter", "abstract", "teaching-resource"] }).notNull(),
387    title: text("title").notNull(),
388    year: integer("year").notNull(),
389    selected: integer("selected").notNull().default(0),
390    record: text("record").notNull(),
391    csl: text("csl"),
392    markdown: text("markdown").notNull(),
393    sourcePath: text("source_path").notNull().unique(),
394    sourceBlobSha: text("source_blob_sha").notNull(),
395    syncedAt: integer("synced_at", { mode: "timestamp" })
396      .notNull()
397      .default(sql`(unixepoch())`),
398  },
399  (t) => [
400    check("publications_status_check", sql`${t.status} in ('draft', 'published')`),
401    check("publications_stage_check", sql`${t.stage} in ('published', 'submitted')`),
402    check(
403      "publications_type_check",
404      sql`${t.type} in ('article', 'review', 'chapter', 'abstract', 'teaching-resource')`,
405    ),
406    index("publications_status_idx").on(t.status, t.year),
407    uniqueIndex("publications_doi_key_idx").on(t.doiKey),
408  ],
409);
410
411/** The citation count a page shows, with no expiry (drizzle/0020_publications.sql). Keyed by the lower-cased DOI. */
412export const publicationCitations = sqliteTable(
413  "publication_citations",
414  {
415    doi: text("doi").primaryKey(),
416    count: integer("count").notNull(),
417    url: text("url"),
418    fetchedAt: text("fetched_at").notNull(),
419  },
420  (t) => [check("publication_citations_count_check", sql`${t.count} >= 0`)],
421);
422
423/**
424 * Pages (drizzle/0021_pages.sql, docs/PAGES.md): the Research, Teaching and Software prose pages, derived
425 * from content/pages/*.md by app/lib/pages/compile.mjs. `record` is the rendered page as JSON and
426 * `markdown` its twin.
427 */
428export const pages = sqliteTable(
429  "pages",
430  {
431    id: integer("id").primaryKey({ autoIncrement: true }),
432    slug: text("slug").notNull().unique(),
433    path: text("path").notNull().unique(),
434    title: text("title").notNull(),
435    description: text("description").notNull(),
436    status: text("status", { enum: ["draft", "published"] }).notNull(),
437    record: text("record").notNull(),
438    markdown: text("markdown").notNull(),
439    sourcePath: text("source_path").notNull().unique(),
440    sourceBlobSha: text("source_blob_sha").notNull(),
441    syncedAt: integer("synced_at", { mode: "timestamp" })
442      .notNull()
443      .default(sql`(unixepoch())`),
444  },
445  (t) => [
446    check("pages_status_check", sql`${t.status} in ('draft', 'published')`),
447    index("pages_status_idx").on(t.status),
448  ],
449);
450
451/** The CV: one row per file in content/cv/ (migration 0022, docs/CV.md). */
452export const cv = sqliteTable(
453  "cv",
454  {
455    id: integer("id").primaryKey({ autoIncrement: true }),
456    slug: text("slug").notNull().unique(),
457    type: text("type", {
458      enum: ["profile", "appointment", "education", "publication", "grant", "award", "talk", "course", "mentoring", "service", "development"],
459    }).notNull(),
460    record: text("record").notNull(),
461    sourcePath: text("source_path").notNull().unique(),
462    sourceBlobSha: text("source_blob_sha").notNull(),
463    syncedAt: integer("synced_at", { mode: "timestamp" })
464      .notNull()
465      .default(sql`(unixepoch())`),
466  },
467  (t) => [
468    check(
469      "cv_type_check",
470      sql`${t.type} in ('profile', 'appointment', 'education', 'publication', 'grant', 'award', 'talk', 'course', 'mentoring', 'service', 'development')`,
471    ),
472  ],
473);
474
475/**
476 * Dictionary entries (drizzle/0023_dictionary.sql, docs/DICTIONARY.md): the entry that opens a named Software
477 * page, derived from content/dictionary/<key>.md by app/lib/dictionary/compile.mjs. `record` is the entry as
478 * JSON.
479 */
480export const dictionaryEntries = sqliteTable(
481  "dictionary_entries",
482  {
483    id: integer("id").primaryKey({ autoIncrement: true }),
484    key: text("key").notNull().unique(),
485    path: text("path").notNull().unique(),
486    term: text("term").notNull(),
487    status: text("status", { enum: ["draft", "published"] }).notNull(),
488    record: text("record").notNull(),
489    sourcePath: text("source_path").notNull().unique(),
490    sourceBlobSha: text("source_blob_sha").notNull(),
491    syncedAt: integer("synced_at", { mode: "timestamp" })
492      .notNull()
493      .default(sql`(unixepoch())`),
494  },
495  (t) => [
496    check("dictionary_entries_status_check", sql`${t.status} in ('draft', 'published')`),
497    index("dictionary_entries_status_idx").on(t.status),
498  ],
499);
500
501// The Phage Discovery cohorts (docs/ROSTER.md). Derived from content/roster/*.md, one row per cohort, written
502// only by writeRosterRow (app/lib/roster/save.server.ts) and sync:content.
503export const roster = sqliteTable("roster", {
504  id: integer("id").primaryKey({ autoIncrement: true }),
505  slug: text("slug").notNull().unique(),
506  year: integer("year").notNull().unique(),
507  record: text("record").notNull(),
508  sourcePath: text("source_path").notNull().unique(),
509  sourceBlobSha: text("source_blob_sha").notNull(),
510  syncedAt: integer("synced_at", { mode: "timestamp" })
511    .notNull()
512    .default(sql`(unixepoch())`),
513});
514
515// The phages of the table on /research/phages (docs/PHAGES.md). Derived from content/phages/*.md, one row per
516// phage, written only by writePhageRow (app/lib/phages/save.server.ts) and sync:content. The page is compiled
517// from these rows, so a write re-derives the page's own row too.
518export const phages = sqliteTable(
519  "phages",
520  {
521    id: integer("id").primaryKey({ autoIncrement: true }),
522    slug: text("slug").notNull().unique(),
523    name: text("name").notNull(),
524    year: integer("year").notNull(),
525    record: text("record").notNull(),
526    sourcePath: text("source_path").notNull().unique(),
527    sourceBlobSha: text("source_blob_sha").notNull(),
528    syncedAt: integer("synced_at", { mode: "timestamp" })
529      .notNull()
530      .default(sql`(unixepoch())`),
531  },
532  (t) => [index("phages_year_idx").on(t.year)],
533);
534
535// The lab registry: the primers, strains, reagents and equipment of docs/REGISTRY.md. Derived from
536// content/registry/<kind>/<id>.md, one row per item, written only by writeRegistryRow
537// (app/kb/registry/save.server.ts) and sync:content. The shared columns are what every kind has; `record` is the item
538// as JSON, validated by its kind's own rules.
539export const registry = sqliteTable(
540  "registry",
541  {
542    kind: text("kind").notNull(),
543    id: text("id").notNull(),
544    name: text("name").notNull(),
545    status: text("status").notNull(),
546    record: text("record").notNull(),
547    sourcePath: text("source_path").notNull().unique(),
548    sourceBlobSha: text("source_blob_sha").notNull(),
549    syncedAt: integer("synced_at", { mode: "timestamp" })
550      .notNull()
551      .default(sql`(unixepoch())`),
552  },
553  (t) => [
554    primaryKey({ columns: [t.kind, t.id] }),
555    index("registry_kind_name_idx").on(t.kind, t.name),
556    check("registry_status_check", sql`${t.status} in ('published', 'draft')`),
557  ],
558);
559
560// The Knowledge Base's inventory (docs/KNOWLEDGE-BASE.md step 4): D1 is the source, not a copy of a file, because a
561// lot changes daily and is private. Nothing reads or writes these tables yet. A lot names a registry item by address,
562// with no foreign key: the registry is rewritten by sync:content.
563export const kbLocations = sqliteTable("kb_locations", {
564  id: integer("id").primaryKey({ autoIncrement: true }),
565  name: text("name").notNull().unique(),
566  parentId: integer("parent_id").references((): AnySQLiteColumn => kbLocations.id),
567  note: text("note"),
568});
569
570export const kbLots = sqliteTable(
571  "kb_lots",
572  {
573    id: integer("id").primaryKey({ autoIncrement: true }),
574    itemKind: text("item_kind").notNull(),
575    itemId: text("item_id").notNull(),
576    lotNumber: text("lot_number").notNull(),
577    expiry: text("expiry"),
578    amount: real("amount"),
579    unit: text("unit"),
580    locationId: integer("location_id").references(() => kbLocations.id),
581    received: text("received"),
582    opened: text("opened"),
583    status: text("status").notNull().default("in_stock"),
584    createdAt: integer("created_at", { mode: "timestamp" })
585      .notNull()
586      .default(sql`(unixepoch())`),
587    updatedAt: integer("updated_at", { mode: "timestamp" })
588      .notNull()
589      .default(sql`(unixepoch())`),
590  },
591  (t) => [
592    uniqueIndex("kb_lots_item_kind_item_id_lot_number_unique").on(t.itemKind, t.itemId, t.lotNumber),
593    index("kb_lots_location_idx").on(t.locationId),
594    check("kb_lots_status_check", sql`${t.status} in ('in_stock', 'empty', 'discarded')`),
595    check("kb_lots_amount_check", sql`${t.amount} IS NULL OR ${t.amount} >= 0`),
596  ],
597);
598
599/** Append-only: one row per write to a lot or a location, with the row before and after as JSON. */
600export const kbAudit = sqliteTable(
601  "kb_audit",
602  {
603    id: integer("id").primaryKey({ autoIncrement: true }),
604    at: integer("at", { mode: "timestamp" })
605      .notNull()
606      .default(sql`(unixepoch())`),
607    actor: text("actor").notNull(),
608    tableName: text("table_name").notNull(),
609    rowId: integer("row_id").notNull(),
610    action: text("action").notNull(),
611    beforeRow: text("before_row"),
612    afterRow: text("after_row"),
613  },
614  (t) => [
615    index("kb_audit_row_idx").on(t.tableName, t.rowId, t.at),
616    check("kb_audit_action_check", sql`${t.action} in ('insert', 'update', 'delete')`),
617    check("kb_audit_table_check", sql`${t.tableName} in ('kb_lots', 'kb_locations')`),
618  ],
619);
620
621// Run records (docs/KNOWLEDGE-BASE.md step 7, drizzle/0031_kb_runs.sql): append-only, so the migration's triggers
622// refuse any UPDATE or DELETE and a correction is a new run naming the one it replaces. A run's lot changes are writes to
623// kb_lots, each with its kb_audit row. Private to lab members.
624export const kbRuns = sqliteTable(
625  "kb_runs",
626  {
627    id: integer("id").primaryKey({ autoIncrement: true }),
628    uid: text("uid").notNull().unique(),
629    procedureSlug: text("procedure_slug").notNull(),
630    procedureVersion: text("procedure_version"),
631    actor: text("actor").notNull(),
632    runOn: text("run_on").notNull(),
633    outcome: text("outcome").notNull().default("completed"),
634    note: text("note"),
635    replacesRunId: integer("replaces_run_id")
636      .unique()
637      .references((): AnySQLiteColumn => kbRuns.id),
638    createdAt: integer("created_at", { mode: "timestamp" })
639      .notNull()
640      .default(sql`(unixepoch())`),
641  },
642  (t) => [
643    index("kb_runs_actor_idx").on(t.actor, t.createdAt),
644    check("kb_runs_outcome_check", sql`${t.outcome} in ('completed', 'failed', 'abandoned')`),
645    check("kb_runs_run_on_check", sql`${t.runOn} GLOB '[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]'`),
646  ],
647);
648
649/** The lots one run used, how much of each, and whether it emptied one. Append-only, with its run. */
650export const kbRunLots = sqliteTable(
651  "kb_run_lots",
652  {
653    runId: integer("run_id")
654      .notNull()
655      .references(() => kbRuns.id),
656    lotId: integer("lot_id")
657      .notNull()
658      .references(() => kbLots.id),
659    amountUsed: real("amount_used"),
660    unit: text("unit"),
661    emptied: integer("emptied").notNull().default(0),
662  },
663  (t) => [
664    primaryKey({ columns: [t.runId, t.lotId] }),
665    index("kb_run_lots_lot_idx").on(t.lotId),
666    check("kb_run_lots_amount_check", sql`${t.amountUsed} IS NULL OR ${t.amountUsed} >= 0`),
667    check("kb_run_lots_emptied_check", sql`${t.emptied} in (0, 1)`),
668  ],
669);
670
671// The /lab workspace's people (docs/KNOWLEDGE-BASE.md step 5, drizzle/0030_lab_members.sql). D1 is the source: people
672// change on /lab/members and nowhere else. The rules are site-runtime's `./members`, the titles `app/lab/titles.mjs`;
673// the Primary Owner is PRIMARY_OWNER_EMAIL and never a row.
674export const members = sqliteTable(
675  "members",
676  {
677    email: text("email").primaryKey().notNull(),
678    name: text("name").notNull().default(""),
679    title: text("title").notNull(),
680    endDate: text("end_date"),
681    status: text("status").notNull().default("active"),
682    addedBy: text("added_by").notNull(),
683    addedAt: integer("added_at", { mode: "timestamp" })
684      .notNull()
685      .default(sql`(unixepoch())`),
686    updatedAt: integer("updated_at", { mode: "timestamp" })
687      .notNull()
688      .default(sql`(unixepoch())`),
689  },
690  (t) => [
691    check("members_status_check", sql`${t.status} in ('active', 'removed', 'expired')`),
692    check("members_email_check", sql`${t.email} = lower(trim(${t.email})) AND instr(${t.email}, '@') > 1`),
693  ],
694);
695
696/** Append-only: one row per change to a person or to the term dates, with the Access sync's answer. */
697export const memberAudit = sqliteTable(
698  "member_audit",
699  {
700    id: integer("id").primaryKey({ autoIncrement: true }),
701    at: text("at").notNull(),
702    actor: text("actor").notNull(),
703    actorTitle: text("actor_title").notNull(),
704    action: text("action").notNull(),
705    memberEmail: text("member_email"),
706    beforeRow: text("before_row"),
707    afterRow: text("after_row"),
708    reason: text("reason"),
709    cloudflare: text("cloudflare").notNull().default("pending"),
710    cloudflareError: text("cloudflare_error"),
711  },
712  (t) => [
713    index("member_audit_member_idx").on(t.memberEmail, t.at),
714    index("member_audit_cloudflare_idx").on(t.cloudflare),
715    check(
716      "member_audit_action_check",
717      sql`${t.action} in ('add', 'change_title', 'renew', 'remove', 'expire', 'review_keep', 'set_terms')`,
718    ),
719    check("member_audit_cloudflare_check", sql`${t.cloudflare} in ('pending', 'accepted', 'failed', 'not_needed')`),
720  ],
721);
722
723/** The semester end dates an Owner enters once a year; a lab title's end date defaults to the next one. */
724export const memberTerms = sqliteTable(
725  "member_terms",
726  {
727    endDate: text("end_date").primaryKey().notNull(),
728    addedBy: text("added_by").notNull(),
729    addedAt: integer("added_at", { mode: "timestamp" })
730      .notNull()
731      .default(sql`(unixepoch())`),
732  },
733  (t) => [check("member_terms_date_check", sql`${t.endDate} GLOB '[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]'`)],
734);