app/lib/index.server.ts

Functions

Imports

It imports app/db/schema.ts, app/lib/sites.server.ts. Imported by app/lib/content.server.ts, app/lib/mcp/tools.ts, app/lib/refresh.server.ts, app/lib/waiting.server.ts, app/routes/editor.tsx, app/routes/project.tsx.

Source

1// The one view's index of a site's content: what the site reports, cached in D1 with FTS5 over title
2// and body, refreshed on every save and by the 15-minute cron. The site stays the authority.
3
4import type { ContentDoc, ContentStatus, ContentSummary } from "@dustinedwards/site-api";
5import { and, asc, desc, eq, sql, type SQL } from "drizzle-orm";
6import { drizzle } from "drizzle-orm/d1";
7
8import { siteItems } from "~/db/schema";
9import { siteClient, type SiteId } from "~/lib/sites.server";
10
11/**
12 * Workers Paid allows 10,000 subrequests per invocation by default, and a D1 call counts as one
13 * (developers.cloudflare.com/workers/platform/limits/#subrequests, checked 2026-09-27; Workers Free
14 * stays at 50). Carrel is on Workers Paid.
15 */
16export const SUBREQUEST_LIMIT = 10_000;
17
18const PAGE_SIZE = 200;
19const MAX_PAGES = 10;
20
21/**
22 * Bodies read per refresh. A refresh costs at most MAX_PAGES list calls, two D1 calls (the known
23 * rows and the summaries' batch), and two per body (its fetch and its batch): 12 + 2 x 200 = 412,
24 * inside a tenth of SUBREQUEST_LIMIT, so the cron's other work in the same invocation (the health check,
25 * other sites' refreshes) always has room. The rest are reported as pending and read on the next run.
26 */
27export const MAX_BODY_FETCHES = 200;
28
29/** The most subrequests one refresh can make, from the counts above. */
30export const REFRESH_MAX_SUBREQUESTS = MAX_PAGES + 2 + 2 * MAX_BODY_FETCHES;
31
32export type IndexedItem = {
33  itemId: string;
34  kind: string;
35  title: string;
36  status: ContentStatus;
37  path: string | null;
38  publishAt: string | null;
39  publishedAt: string | null;
40  updatedAt: string | null;
41};
42
43type ProjectRef = { id: number; site: SiteId };
44
45function summaryRow(projectId: number, item: ContentSummary, syncedAt: string) {
46  return {
47    projectId,
48    itemId: item.id,
49    kind: item.kind,
50    title: item.title,
51    status: item.status,
52    path: item.path,
53    publishAt: item.publishAt,
54    publishedAt: item.publishedAt,
55    updatedAt: item.updatedAt,
56    syncedAt,
57  };
58}
59
60function writeBody(db: D1Database, projectId: number, itemId: string, title: string, body: string) {
61  return [
62    db.prepare("DELETE FROM site_items_fts WHERE project_id = ? AND item_id = ?").bind(projectId, itemId),
63    db
64      .prepare("INSERT INTO site_items_fts (project_id, item_id, title, body) VALUES (?, ?, ?, ?)")
65      .bind(projectId, itemId, title, body),
66  ];
67}
68
69/** Indexes one item from a document already in hand, as after a save: no extra request to the site. */
70export async function indexDoc(db: D1Database, projectId: number, doc: ContentDoc, now = new Date()): Promise<void> {
71  const row = summaryRow(projectId, doc, now.toISOString());
72  const d = drizzle(db);
73  await d
74    .insert(siteItems)
75    .values({ ...row, bodyUpdatedAt: doc.updatedAt ?? "" })
76    .onConflictDoUpdate({
77      target: [siteItems.projectId, siteItems.itemId],
78      set: { ...row, bodyUpdatedAt: doc.updatedAt ?? "" },
79    });
80  await db.batch(writeBody(db, projectId, doc.id, doc.title, doc.source));
81}
82
83/** Drops one item from the index after the site deleted it. The index is a cache; the site stays the authority. */
84export async function removeFromIndex(db: D1Database, projectId: number, itemId: string): Promise<void> {
85  await db.batch([
86    db.prepare("DELETE FROM site_items WHERE project_id = ? AND item_id = ?").bind(projectId, itemId),
87    db.prepare("DELETE FROM site_items_fts WHERE project_id = ? AND item_id = ?").bind(projectId, itemId),
88  ]);
89}
90
91export type RefreshResult = { listed: number; fetched: number; removed: number; pending: number };
92
93/**
94 * Lists every item, upserts the summaries, drops items the site no longer lists, and fetches the
95 * body of up to MAX_BODY_FETCHES items whose updatedAt moved since their body was read. The rest are
96 * reported as pending and caught up on the next run.
97 */
98export async function refreshIndex(
99  env: Env,
100  project: ProjectRef,
101  fetcher?: typeof fetch,
102  now = new Date(),
103): Promise<RefreshResult> {
104  const client = siteClient(env, project.site, fetcher);
105  const items: ContentSummary[] = [];
106  let cursor: string | undefined;
107  for (let page = 0; page < MAX_PAGES; page++) {
108    const result = await client.list({ limit: PAGE_SIZE, cursor });
109    items.push(...result.items);
110    if (!result.nextCursor) break;
111    cursor = result.nextCursor;
112  }
113
114  const d = drizzle(env.DB);
115  const syncedAt = now.toISOString();
116  const known = new Map(
117    (
118      await d
119        .select({ itemId: siteItems.itemId, bodyUpdatedAt: siteItems.bodyUpdatedAt })
120        .from(siteItems)
121        .where(eq(siteItems.projectId, project.id))
122        .all()
123    ).map((r) => [r.itemId, r.bodyUpdatedAt]),
124  );
125
126  const listed = new Set(items.map((i) => i.id));
127  const statements: D1PreparedStatement[] = [];
128  for (const item of items) {
129    const row = summaryRow(project.id, item, syncedAt);
130    statements.push(
131      env.DB.prepare(
132        `INSERT INTO site_items (project_id, item_id, kind, title, status, path, publish_at, published_at, updated_at, synced_at)
133         VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
134         ON CONFLICT (project_id, item_id) DO UPDATE SET kind = excluded.kind, title = excluded.title,
135           status = excluded.status, path = excluded.path, publish_at = excluded.publish_at,
136           published_at = excluded.published_at, updated_at = excluded.updated_at, synced_at = excluded.synced_at`,
137      ).bind(
138        row.projectId, row.itemId, row.kind, row.title, row.status, row.path,
139        row.publishAt, row.publishedAt, row.updatedAt, row.syncedAt,
140      ),
141    );
142  }
143  const gone = [...known.keys()].filter((id) => !listed.has(id));
144  for (const itemId of gone) {
145    statements.push(
146      env.DB.prepare("DELETE FROM site_items WHERE project_id = ? AND item_id = ?").bind(project.id, itemId),
147      env.DB.prepare("DELETE FROM site_items_fts WHERE project_id = ? AND item_id = ?").bind(project.id, itemId),
148    );
149  }
150  if (statements.length > 0) await env.DB.batch(statements);
151
152  // body_updated_at is null until a body is read, and "" once read from an item with no updatedAt,
153  // so a site that omits updatedAt is read once rather than on every run.
154  const stale = items.filter((i) => {
155    const readAt = known.get(i.id);
156    return readAt === undefined || readAt === null || readAt !== (i.updatedAt ?? "");
157  });
158  const due = stale.slice(0, MAX_BODY_FETCHES);
159  for (const item of due) {
160    const doc = await client.get(item.id);
161    await env.DB.batch([
162      ...writeBody(env.DB, project.id, doc.id, doc.title, doc.source),
163      env.DB.prepare("UPDATE site_items SET body_updated_at = ? WHERE project_id = ? AND item_id = ?").bind(
164        doc.updatedAt ?? "",
165        project.id,
166        doc.id,
167      ),
168    ]);
169  }
170
171  return { listed: items.length, fetched: due.length, removed: gone.length, pending: stale.length - due.length };
172}
173
174/**
175 * The search as an FTS5 expression: each word quoted (so punctuation is text, never syntax), all
176 * required, the last one a prefix so results follow typing.
177 */
178export function ftsQuery(q: string): string | null {
179  const words = q.match(/[\p{L}\p{N}]+/gu) ?? [];
180  if (words.length === 0) return null;
181  return words
182    .slice(0, 8)
183    .map((w, i, all) => `"${w.replaceAll('"', '""')}"${i === all.length - 1 ? "*" : ""}`)
184    .join(" ");
185}
186
187export type Filters = { q?: string; status?: ContentStatus; kind?: string };
188
189/**
190 * The kinds Carrel presents as writing, read from the site's own metadata rather than named here:
191 * site-api reports a kind only as a free string. A kind is writing when any of its items has a page
192 * on the site (a path) or is not yet published. A data kind (the dustinedwards lab registry:
193 * equipment, reagent, primer, strain) is published with no page at all, so it stays in the index but
194 * out of every writing list and its count, whatever it is called. A site-api field that says
195 * "writing" would replace this rule.
196 */
197function writingKinds(projectId: number): SQL {
198  return sql`${siteItems.kind} IN (SELECT kind FROM site_items WHERE project_id = ${projectId} AND (path IS NOT NULL OR status != 'published'))`;
199}
200
201export async function searchItems(db: D1Database, projectId: number, filters: Filters): Promise<IndexedItem[]> {
202  return (await searchItemsPage(db, projectId, filters, { offset: 0, limit: 500 })).items;
203}
204
205/**
206 * One page of searchItems, in the same order: `limit` items from `offset`, and whether more follow.
207 * One row past the page is read to answer that, never a count.
208 */
209export async function searchItemsPage(
210  db: D1Database,
211  projectId: number,
212  filters: Filters,
213  page: { offset: number; limit: number },
214): Promise<{ items: IndexedItem[]; hasMore: boolean }> {
215  const conditions: SQL[] = [eq(siteItems.projectId, projectId), writingKinds(projectId)];
216  if (filters.status) conditions.push(eq(siteItems.status, filters.status));
217  if (filters.kind) conditions.push(eq(siteItems.kind, filters.kind));
218  const match = filters.q ? ftsQuery(filters.q) : null;
219  if (filters.q && !match) return { items: [], hasMore: false };
220  if (match) {
221    conditions.push(
222      sql`${siteItems.itemId} IN (SELECT item_id FROM site_items_fts WHERE site_items_fts MATCH ${match} AND project_id = ${projectId})`,
223    );
224  }
225  const rows = await drizzle(db)
226    .select({
227      itemId: siteItems.itemId,
228      kind: siteItems.kind,
229      title: siteItems.title,
230      status: siteItems.status,
231      path: siteItems.path,
232      publishAt: siteItems.publishAt,
233      publishedAt: siteItems.publishedAt,
234      updatedAt: siteItems.updatedAt,
235    })
236    .from(siteItems)
237    .where(and(...conditions))
238    .orderBy(desc(siteItems.updatedAt), asc(siteItems.itemId))
239    .limit(page.limit + 1)
240    .offset(page.offset)
241    .all();
242  return { items: rows.slice(0, page.limit), hasMore: rows.length > page.limit };
243}
244
245export async function kindsIn(db: D1Database, projectId: number): Promise<string[]> {
246  const rows = await drizzle(db)
247    .selectDistinct({ kind: siteItems.kind })
248    .from(siteItems)
249    .where(and(eq(siteItems.projectId, projectId), writingKinds(projectId)))
250    .orderBy(asc(siteItems.kind))
251    .all();
252  return rows.map((r) => r.kind);
253}
254
255/**
256 * Where each item lives on the site, for links written in the editor: its own path when the site
257 * gives one, otherwise the path its published siblings of the same kind follow (a draft post takes
258 * the address the site's published posts have, with its own id). An item whose kind shows no such
259 * pattern has no known address yet, and is left out of the link targets.
260 */
261export function sitePaths(items: readonly Pick<IndexedItem, "itemId" | "kind" | "path">[]): Map<string, string> {
262  const prefixes = new Map<string, string>();
263  for (const i of items) {
264    if (i.path && !prefixes.has(i.kind) && i.path.endsWith(`/${i.itemId}`)) prefixes.set(i.kind, i.path.slice(0, -i.itemId.length));
265  }
266  const paths = new Map<string, string>();
267  for (const i of items) {
268    const prefix = prefixes.get(i.kind);
269    const path = i.path ?? (prefix ? `${prefix}${i.itemId}` : null);
270    if (path) paths.set(i.itemId, path);
271  }
272  return paths;
273}