app/db/media.ts

Functions

Imports

It imports app/db/client.ts, app/db/schema.ts, app/lib/media/classify.mjs, app/lib/media/tags.mjs, app/lib/media/usage.mjs. Imported by app/db/index.ts.

Source

1// The media library's D1 index: listing, counts, trash, tags and the upserts that keep it in step with R2.
2import { and, asc, count, desc, eq, inArray, isNotNull, ne, sql } from "drizzle-orm";
3import { digestFromKey } from "../lib/media/classify.mjs";
4import { exactTagNeedle, parseTags, serialiseTags } from "../lib/media/tags.mjs";
5import { LARGE_FILE_BYTES } from "../lib/media/usage.mjs";
6import { media, mediaRefs, type MediaRef } from "./schema";
7import { getDb, notTrashed } from "./client";
8
9export async function listMediaRecords(env: Env) {
10  return getDb(env).select().from(media);
11}
12
13/**
14 * Unattached: no post or repository code cites it. Shared by the filter and chip count.
15 * @param templateKeys keys repository code references, from the build artifact
16 */
17function uncited(templateKeys: string[] = []) {
18  const noPost = sql`NOT EXISTS (SELECT 1 FROM ${mediaRefs} WHERE ${mediaRefs.mediaKey} = ${media.key})`;
19  // `NOT IN ()` is a syntax error in SQLite, so an empty list omits the clause.
20  if (templateKeys.length === 0) return noPost;
21  return sql`${noPost} AND ${media.key} NOT IN ${templateKeys}`;
22}
23
24/** Free-text filter. `coalesce`: `lower(NULL) LIKE` is NULL and would drop the row. */
25function matchesQuery(q: string) {
26  const needle = `%${q.toLowerCase()}%`;
27  return sql`(lower(coalesce(${media.originalName}, '')) LIKE ${needle}
28    OR lower(${media.key}) LIKE ${needle}
29    OR lower(${media.alt}) LIKE ${needle}
30    OR lower(${media.caption}) LIKE ${needle}
31    OR lower(${media.tags}) LIKE ${needle})`;
32}
33
34/** ORDER BY for a listing; the caller adds the tie-break. */
35function orderFor(options: { sort?: string; dir?: string; trashed?: boolean }) {
36  if (options.trashed) return [desc(media.trashedAt)];
37
38  const descending = options.dir !== "asc";
39  const way = <T extends Parameters<typeof desc>[0]>(column: T) =>
40    descending ? desc(column) : asc(column);
41
42  switch (options.sort) {
43    case "name":
44      // Falls back to the key, or the NULL names would sort as one block.
45      return [way(sql`lower(coalesce(${media.originalName}, ${media.key}))`)];
46    case "size":
47      return [way(media.bytes)];
48    case "usage":
49      return [
50        way(
51          sql`(SELECT count(*) FROM ${mediaRefs} WHERE ${mediaRefs.mediaKey} = ${media.key})`,
52        ),
53      ];
54    default:
55      /*
56       * Role rank, then newest: `uploaded_at DESC` alone sorts static rows (no upload event, so NULL)
57       * last, burying the only insertable images.
58       */
59      return [
60        sql`CASE ${media.role} WHEN 'content' THEN 0 WHEN 'generated' THEN 1 WHEN 'brand' THEN 2 ELSE 3 END`,
61        way(media.uploadedAt),
62      ];
63  }
64}
65
66/** One library page, newest first; `key` keeps offset pagination stable. */
67export async function listMediaPage(
68  env: Env,
69  options: {
70    page?: number;
71    limit?: number;
72    insertableOnly?: boolean;
73    role?: string;
74    unusedOnly?: boolean;
75    q?: string;
76    /** An exact tag, already normalized by the caller. */
77    tag?: string;
78    /** A quality lens: 'unattached' | 'duplicates' | 'no-alt' | 'large'. Else no filter. */
79    lens?: string;
80    /** Keys repository code references. A parameter: scripts import `app/db` unbundled. */
81    templateKeys?: string[];
82    /** 'added' | 'name' | 'size' | 'usage'. Anything else falls back to added. */
83    sort?: string;
84    dir?: string;
85    /** Trashed rows only. A flag, not a `role`: trash is orthogonal to role. */
86    trashed?: boolean;
87    /** Bucket rows only, no static assets: their keys are repo paths, not storage keys. */
88    storedOnly?: boolean;
89  } = {},
90) {
91  const limit = options.limit ?? 24;
92  const page = Math.max(1, options.page ?? 1);
93  const offset = (page - 1) * limit;
94
95  // `role`, not `storage`: logos, favicons and diagram halves must never be inserted.
96  const insertable = and(eq(media.role, "content"), eq(media.kind, "image"));
97
98  /* As SQL, not a post-filter, so a page is a full page. */
99  const clauses = [];
100  /* A branch, never optional, or trashed assets reappear in the picker. */
101  clauses.push(options.trashed ? isNotNull(media.trashedAt) : notTrashed());
102  if (options.insertableOnly) clauses.push(insertable);
103  if (options.role) clauses.push(eq(media.role, options.role));
104  if (options.storedOnly) clauses.push(ne(media.storage, "static"));
105  // Pipeline citations only; the card's resolver scan stays the authority.
106  if (options.unusedOnly) clauses.push(uncited(options.templateKeys));
107  // In SQL because this listing paginates: a post-filter would search one page.
108  if (options.q) clauses.push(matchesQuery(options.q));
109  /* Needle from `exactTagNeedle`; null means no filter, never a match-all `%,,%`. */
110  if (options.tag) {
111    const needle = exactTagNeedle(options.tag);
112    if (needle) clauses.push(sql`lower(${media.tags}) LIKE ${needle}`);
113  }
114
115  if (options.lens === "unattached") clauses.push(uncited(options.templateKeys));
116  if (options.lens === "no-alt") clauses.push(eq(media.alt, ""));
117  if (options.lens === "large") clauses.push(sql`${media.bytes} > ${LARGE_FILE_BYTES}`);
118  /*
119   * Twins are found by the digest inside the key, which SQLite cannot parse, so `mediaTwins` names
120   * them; the filter is still SQL, before LIMIT and OFFSET, or a page of twins would come back short
121   * or empty. One JSON parameter, not an IN list, so the count never meets D1's bound-parameter cap.
122   */
123  if (options.lens === "duplicates") {
124    const twinKeys = JSON.stringify([...(await mediaTwins(env)).keys()]);
125    clauses.push(sql`${media.key} IN (SELECT value FROM json_each(${twinKeys}))`);
126  }
127  const where = clauses.length > 0 ? and(...clauses) : undefined;
128
129  const db = getDb(env);
130  const rows = await db
131    .select()
132    .from(media)
133    .where(where)
134    /*
135     * The role rank is only the default, never a prefix on an explicit sort. `key ASC` breaks ties,
136     * because an unstable sort makes offset pagination skip and repeat rows.
137     */
138    .orderBy(...orderFor(options), asc(media.key))
139    .limit(limit + 1)
140    .offset(offset);
141
142  // One extra row answers "is there another page" without a COUNT.
143  const hasMore = rows.length > limit;
144  return { rows: rows.slice(0, limit), page, hasMore };
145}
146
147export async function mediaCounts(env: Env) {
148  const rows = await getDb(env)
149    .select({ storage: media.storage, kind: media.kind, n: count() })
150    .from(media)
151    // Trashed rows are out, so this matches the grid.
152    .where(notTrashed())
153    .groupBy(media.storage, media.kind);
154  return rows;
155}
156
157/** By role. Separate from `mediaCounts` so a broken `roleOf()` shows. */
158export async function mediaRoleCounts(env: Env) {
159  return getDb(env)
160    .select({ role: media.role, n: count() })
161    .from(media)
162    // Trashed rows are out, so the role chips add up to the grid.
163    .where(notTrashed())
164    .groupBy(media.role);
165}
166
167
168/**
169 * Twins by the key's content hash: exact identity only, never perceptual, so any delete decision
170 * stays answerable from facts. The hash is read off the content-addressed key, not recomputed from R2.
171 * Twins are separate objects at separate URLs, either of which may be cited, so the page offers
172 * Trash, not delete. Static rows are excluded: their keys are paths.
173 */
174export async function mediaTwins(env: Env) {
175  const rows = await getDb(env)
176    .select({ key: media.key, originalName: media.originalName, storage: media.storage })
177    .from(media)
178    .where(and(notTrashed(), sql`${media.storage} <> 'static'`));
179
180  const byHash = new Map<string, { key: string; originalName: string | null }[]>();
181  for (const row of rows) {
182    const hash = digestFromKey(row.key);
183    if (!hash) continue;
184    const bucket = byHash.get(hash) ?? [];
185    bucket.push({ key: row.key, originalName: row.originalName });
186    byHash.set(hash, bucket);
187  }
188
189  /** key -> the OTHER rows carrying the same bytes. */
190  const twins = new Map<string, { key: string; originalName: string | null }[]>();
191  for (const bucket of byHash.values()) {
192    if (bucket.length < 2) continue;
193    for (const row of bucket) {
194      twins.set(
195        row.key,
196        bucket.filter((other) => other.key !== row.key),
197      );
198    }
199  }
200  return twins;
201}
202
203/** All lens counts in one query, on the filters' own predicates. Caller adds `duplicates`. */
204export async function mediaLensCounts(env: Env, templateKeys: string[] = []) {
205  const [row] = await getDb(env)
206    .select({
207      all: count(),
208      /* Through `uncited()`, never a copy, so count and filter cannot diverge. */
209      unattached: sql<number>`sum(CASE WHEN ${uncited(templateKeys)} THEN 1 ELSE 0 END)`,
210      noAlt: sql<number>`sum(CASE WHEN ${media.alt} = '' THEN 1 ELSE 0 END)`,
211      large: sql<number>`sum(CASE WHEN ${media.bytes} > ${LARGE_FILE_BYTES} THEN 1 ELSE 0 END)`,
212    })
213    .from(media)
214    .where(notTrashed());
215  return {
216    all: Number(row?.all ?? 0),
217    unattached: Number(row?.unattached ?? 0),
218    noAlt: Number(row?.noAlt ?? 0),
219    large: Number(row?.large ?? 0),
220  };
221}
222
223/** Rows in the trash. Not a pseudo-role: trash is orthogonal to role. */
224export async function mediaTrashedCount(env: Env) {
225  const [row] = await getDb(env)
226    .select({ n: count() })
227    .from(media)
228    .where(isNotNull(media.trashedAt));
229  return Number(row?.n ?? 0);
230}
231
232/** Trashes an asset; R2 untouched. A repeat keeps the first timestamp. Returns if moved. */
233export async function trashMediaRecord(env: Env, key: string) {
234  const result = await getDb(env)
235    .update(media)
236    .set({ trashedAt: sql`(datetime('now'))`, updatedAt: sql`(datetime('now'))` })
237    .where(and(eq(media.key, key), notTrashed()))
238    .run();
239  return { moved: (result.meta?.changes ?? 0) > 0 };
240}
241
242export async function restoreMediaRecord(env: Env, key: string) {
243  const result = await getDb(env)
244    .update(media)
245    .set({ trashedAt: null, updatedAt: sql`(datetime('now'))` })
246    .where(and(eq(media.key, key), isNotNull(media.trashedAt)))
247    .run();
248  return { restored: (result.meta?.changes ?? 0) > 0 };
249}
250
251export async function trashedMediaKeys(env: Env) {
252  const rows = await getDb(env)
253    .select({ key: media.key })
254    .from(media)
255    .where(isNotNull(media.trashedAt))
256    .orderBy(desc(media.trashedAt), asc(media.key));
257  return rows.map((r) => r.key);
258}
259
260/** The only tag writer; `serialiseTags` owns the stored form. */
261export async function setMediaTags(env: Env, key: string, input: string[] | string) {
262  await getDb(env)
263    .update(media)
264    .set({ tags: serialiseTags(input), updatedAt: sql`(datetime('now'))` })
265    .where(eq(media.key, key))
266    .run();
267  return parseTags(serialiseTags(input));
268}
269
270/** Tag counts, in JS since tags share one delimited column. Trashed rows excluded. */
271export async function mediaTagCounts(env: Env) {
272  const rows = await getDb(env)
273    .select({ tags: media.tags })
274    .from(media)
275    .where(notTrashed());
276  const counts = new Map<string, number>();
277  for (const row of rows) {
278    for (const tag of parseTags(row.tags)) {
279      counts.set(tag, (counts.get(tag) ?? 0) + 1);
280    }
281  }
282  return [...counts.entries()]
283    .map(([tag, n]) => ({ tag, n }))
284    .sort((a, b) => b.n - a.n || a.tag.localeCompare(b.tag));
285}
286
287/** One row by key, for the detail view, which a URL can reach from any page. */
288export async function mediaRecord(env: Env, key: string) {
289  const [row] = await getDb(env).select().from(media).where(eq(media.key, key)).limit(1);
290  return row ?? null;
291}
292
293/**
294 * Writes derived columns only, named, never spread. Never delete-then-insert: alt,
295 * caption and focal point are recoverable from nothing.
296 */
297export async function upsertDerivedMedia(
298  env: Env,
299  record: {
300    key: string;
301    storage: string;
302    kind: string;
303    role: string;
304    mime?: string | null;
305    bytes?: number | null;
306    width?: number | null;
307    height?: number | null;
308    originalName?: string | null;
309    placeholder?: string | null;
310    uploadedAt?: string | null;
311  },
312) {
313  const now = new Date().toISOString();
314  const derived = {
315    storage: record.storage,
316    kind: record.kind,
317    role: record.role,
318    mime: record.mime ?? null,
319    bytes: record.bytes ?? null,
320    uploadedAt: record.uploadedAt ?? null,
321    updatedAt: now,
322  };
323  const measured = record.width != null && record.height != null;
324  await getDb(env)
325    .insert(media)
326    .values({
327      key: record.key,
328      ...derived,
329      width: record.width ?? null,
330      height: record.height ?? null,
331      originalName: record.originalName ?? null,
332      placeholder: record.placeholder ?? null,
333    })
334    .onConflictDoUpdate({
335      target: media.key,
336      set: {
337        ...derived,
338        // Written when measured, never blanked by a caller whose Images read failed.
339        ...(measured ? { width: record.width, height: record.height } : {}),
340        // Written when present, never blanked by a caller that cannot measure one.
341        ...(record.placeholder ? { placeholder: record.placeholder } : {}),
342        // Written when present, never blanked by a caller with no name.
343        ...(record.originalName ? { originalName: record.originalName } : {}),
344      },
345    });
346}
347
348/** Creates or updates one object's annotation; the row may not exist yet. */
349export async function upsertMediaRecord(
350  env: Env,
351  record: {
352    key: string;
353    alt?: string;
354    caption?: string;
355    storage?: string;
356    kind?: string;
357    role?: string;
358    mime?: string | null;
359    bytes?: number | null;
360    originalName?: string | null;
361    uploadedAt?: string | null;
362    width?: number | null;
363    height?: number | null;
364  },
365) {
366  const now = new Date().toISOString();
367  await getDb(env)
368    .insert(media)
369    .values({
370      key: record.key,
371      alt: record.alt ?? "",
372      caption: record.caption ?? "",
373      storage: record.storage ?? "r2",
374      kind: record.kind ?? "image",
375      role: record.role ?? "content",
376      mime: record.mime ?? null,
377      bytes: record.bytes ?? null,
378      originalName: record.originalName ?? null,
379      uploadedAt: record.uploadedAt ?? null,
380      width: record.width ?? null,
381      height: record.height ?? null,
382      updatedAt: now,
383    })
384    .onConflictDoUpdate({
385      target: media.key,
386      set: {
387        // Only the fields the caller actually supplied. An alt edit must not
388        // blank the dimensions a backfill measured.
389        ...(record.alt !== undefined ? { alt: record.alt } : {}),
390        ...(record.caption !== undefined ? { caption: record.caption } : {}),
391        ...(record.storage !== undefined ? { storage: record.storage } : {}),
392        ...(record.kind !== undefined ? { kind: record.kind } : {}),
393        ...(record.role !== undefined ? { role: record.role } : {}),
394        ...(record.mime !== undefined ? { mime: record.mime } : {}),
395        ...(record.bytes !== undefined ? { bytes: record.bytes } : {}),
396        ...(record.originalName !== undefined ? { originalName: record.originalName } : {}),
397        ...(record.uploadedAt !== undefined ? { uploadedAt: record.uploadedAt } : {}),
398        ...(record.width !== undefined ? { width: record.width } : {}),
399        ...(record.height !== undefined ? { height: record.height } : {}),
400        updatedAt: now,
401      },
402    });
403}
404
405/** Drops the row once the object is gone, never to make it go: R2 wins. */
406export async function deleteMediaRecord(env: Env, key: string) {
407  await getDb(env).delete(media).where(eq(media.key, key));
408}
409
410/**
411 * Claims a key for deletion in one statement, so no citation lands between the `NOT EXISTS`
412 * check and the removal. False means refuse. Row before object: R2 wins.
413 */
414export async function claimMediaKeyForDelete(env: Env, key: string) {
415  const claimed = await getDb(env)
416    .delete(media)
417    .where(
418      and(
419        eq(media.key, key),
420        sql`NOT EXISTS (SELECT 1 FROM ${mediaRefs} WHERE ${mediaRefs.mediaKey} = ${key})`,
421      ),
422    )
423    .returning({ key: media.key });
424  return claimed.length > 0;
425}
426
427export async function mediaRefsFor(env: Env, keys: string[]) {
428  if (keys.length === 0) return new Map<string, MediaRef[]>();
429  const rows = await getDb(env)
430    .select()
431    .from(mediaRefs)
432    .where(inArray(mediaRefs.mediaKey, keys));
433  const out = new Map<string, MediaRef[]>(keys.map((key) => [key, []]));
434  for (const row of rows) out.get(row.mediaKey)?.push(row);
435  return out;
436}