app/db/posts.ts

Functions

Imports

It imports app/db/client.ts, app/db/schema.ts, app/lib/blog-listing.mjs, app/lib/search/visibility.mjs, app/lib/series-path.mjs, app/lib/timing.ts. Imported by app/db/index.ts, app/lib/context.ts, app/lib/site-urls.server.ts.

Source

1// Posts, tags, series and the feeds, as read by the site, the admin and the operator.
2import {
3  and,
4  asc,
5  count,
6  desc,
7  eq,
8  gt,
9  inArray,
10  isNotNull,
11  lt,
12  max,
13  ne,
14  or,
15  sql,
16  type SQL,
17} from "drizzle-orm";
18import {
19  HOME_CARDS,
20  HOME_PICKS,
21  POSTS_PER_PAGE,
22  pageCount,
23  picksFirst,
24  startHere,
25} from "../lib/blog-listing.mjs";
26import { PUBLISHED_STATUS } from "../lib/search/visibility.mjs";
27import { seriesSlug } from "../lib/series-path.mjs";
28import { timed, type Timings } from "../lib/timing";
29import { media, postTags, posts, tags } from "./schema";
30import { getDb, type DB, publiclyVisible, isBlogPost, notTrashed } from "./client";
31
32/** Ask's cache replay re-checks visibility here because KV invalidation lags. */
33export async function publiclyVisibleSlugs(
34  env: Env,
35  slugs: string[],
36): Promise<Set<string>> {
37  if (slugs.length === 0) return new Set();
38  const rows = await getDb(env)
39    .select({ slug: posts.slug })
40    .from(posts)
41    .where(and(inArray(posts.slug, slugs), publiclyVisible()));
42  return new Set(rows.map((r) => r.slug));
43}
44
45/**
46 * The earliest publish_at still ahead on a published post, or null. Nothing purges when one passes,
47 * so the pages that list posts cap their edge lifetime at it.
48 */
49export async function nextScheduledPublishAt(env: Env): Promise<Date | null> {
50  const [row] = await getDb(env)
51    .select({ publishAt: posts.publishAt })
52    .from(posts)
53    .where(
54      and(
55        eq(posts.kind, "post"),
56        eq(posts.status, PUBLISHED_STATUS),
57        gt(posts.publishAt, new Date()),
58      ),
59    )
60    .orderBy(asc(posts.publishAt))
61    .limit(1);
62  return row?.publishAt ?? null;
63}
64
65/**
66 * The listing order, newest first: publish instant, then a series together with its latest part first, then
67 * the later row. app/lib/post-order.mjs states the rule and applies it to the header's build-time list; this is
68 * the same rule in SQL, so the /writing listing, the home page, the feeds and the header cannot disagree.
69 *
70 * `groupKey` is the largest id among the published posts sharing this row's instant and series, or the row's
71 * own id when it has no series. It is what keeps a series together and sorts it among its day's other posts.
72 */
73const groupKey = sql<number>`COALESCE(CASE WHEN "posts"."series" IS NULL THEN NULL ELSE (SELECT MAX(g.id) FROM posts g WHERE g.kind = 'post' AND g.status = ${PUBLISHED_STATUS} AND g.publish_at = "posts"."publish_at" AND g.series = "posts"."series") END, "posts"."id")`;
74const partKey = sql<number>`COALESCE(${posts.part}, 0)`;
75const newestFirstOrder = (direction: typeof desc | typeof asc = desc) => [
76  direction(posts.publishAt),
77  direction(groupKey),
78  direction(partKey),
79  direction(posts.id),
80];
81
82/** Columns the index and feed need. Deliberately excludes body and html. */
83const postCard = {
84  slug: posts.slug,
85  title: posts.title,
86  description: posts.description,
87  publishAt: posts.publishAt,
88  updatedAt: posts.updatedAt,
89  coverImage: posts.coverImage,
90  coverAlt: posts.coverAlt,
91  readingTimeMinutes: posts.readingTimeMinutes,
92  featured: posts.featured,
93  series: posts.series,
94  part: posts.part,
95};
96
97
98/**
99 * Composes `isBlogPost()` itself and takes only the extra clauses, so no caller can pass a `where`
100 * missing the visibility predicate. Years come from SQLite, as in `listBlogYears`, so two
101 * conversions cannot disagree across a new year in another time zone.
102 */
103function listingSpanSelect(db: DB, ...narrowing: (SQL | undefined)[]) {
104  return db
105    .select({
106      total: count(),
107      firstYear: sql<string | null>`strftime('%Y', min(${posts.publishAt}), 'unixepoch')`.as(
108        "first_year",
109      ),
110      lastYear: sql<string | null>`strftime('%Y', max(${posts.publishAt}), 'unixepoch')`.as(
111        "last_year",
112      ),
113      minutes: sql<number | null>`sum(${posts.readingTimeMinutes})`.as("minutes"),
114    })
115    .from(posts)
116    .where(and(isBlogPost(), ...narrowing));
117}
118
119/** A NULL is not a zero: an empty list has no span, and the row omits itself. */
120function spanOf(row: { firstYear: string | null; lastYear: string | null; minutes: number | null } | undefined) {
121  return {
122    firstYear: row?.firstYear ?? null,
123    lastYear: row?.lastYear ?? null,
124    minutes: row?.minutes ?? null,
125  };
126}
127
128/** A subquery, not a join: a join would multiply rows. */
129function carriesTag(db: DB, tagSlug: string) {
130  return inArray(
131    posts.id,
132    db
133      .select({ id: postTags.postId })
134      .from(postTags)
135      .innerJoin(tags, eq(tags.id, postTags.tagId))
136      .where(eq(tags.slug, tagSlug)),
137  );
138}
139
140/** One tag by slug, or null when no publicly visible post carries it (the archive 404s). */
141export async function getBlogTag(env: Env, tagSlug: string) {
142  const rows = await getDb(env)
143    .select({ slug: tags.slug, name: tags.name, total: count(posts.id) })
144    .from(tags)
145    .innerJoin(postTags, eq(postTags.tagId, tags.id))
146    .innerJoin(posts, eq(posts.id, postTags.postId))
147    .where(and(eq(tags.slug, tagSlug), isBlogPost()))
148    .groupBy(tags.slug, tags.name)
149    .limit(1);
150  return rows[0] ?? null;
151}
152
153/** Groups each row's value under its key, keeping row order within a group. */
154function groupValues<R, K, V>(rows: Iterable<R>, key: (row: R) => K, value: (row: R) => V) {
155  const out = new Map<K, V[]>();
156  for (const row of rows) {
157    const list = out.get(key(row));
158    if (list) list.push(value(row));
159    else out.set(key(row), [value(row)]);
160  }
161  return out;
162}
163
164async function tagsForPosts(db: DB, postIds: number[]) {
165  if (postIds.length === 0) return new Map<number, string[]>();
166
167  const rows = await db
168    .select({ postId: postTags.postId, name: tags.name, slug: tags.slug })
169    .from(postTags)
170    .innerJoin(tags, eq(tags.id, postTags.tagId))
171    .where(inArray(postTags.postId, postIds))
172    .orderBy(asc(tags.slug));
173
174  return groupValues(rows, (row) => row.postId, (row) => row.slug);
175}
176
177export async function listBlogPosts(
178  env: Env,
179  options: {
180    tag?: string | null;
181    year?: string | null;
182    page?: number;
183    perPage?: number;
184    timings?: Timings;
185  } = {},
186) {
187  const db = getDb(env);
188  const perPage = options.perPage ?? POSTS_PER_PAGE;
189  const page = Math.max(1, options.page ?? 1);
190  const tag = options.tag?.trim() || null;
191  const year = options.year?.trim() || null;
192
193  /* The filters stay apart from the visibility predicate because `listingSpanSelect` composes its own. */
194  const narrowing: (SQL | undefined)[] = [];
195  if (tag) narrowing.push(carriesTag(db, tag));
196  if (year) {
197    narrowing.push(sql`strftime('%Y', ${posts.publishAt}, 'unixepoch') = ${year}`);
198  }
199  const where = and(isBlogPost(), ...narrowing);
200
201  // One round trip. The page is a subquery the tag join wraps; joining first would LIMIT
202  // joined rows. The rest of newestFirstOrder breaks ties, or posts sharing `publish_at` interleave by tag.
203  const pageOfPosts = db
204    .select({ ...postCard, id: posts.id, groupKey: groupKey.as("group_key") })
205    .from(posts)
206    .where(where)
207    .orderBy(...newestFirstOrder())
208    .limit(perPage)
209    .offset((page - 1) * perPage)
210    .as("page_of_posts");
211
212  // Named one by one: drizzle will not select a subquery as a nested object.
213  const [statRows, joined] = await timed(options.timings, "d1_batch", () =>
214    db.batch([
215      listingSpanSelect(db, ...narrowing),
216      db
217        .select({
218          id: pageOfPosts.id,
219          slug: pageOfPosts.slug,
220          title: pageOfPosts.title,
221          description: pageOfPosts.description,
222          publishAt: pageOfPosts.publishAt,
223          updatedAt: pageOfPosts.updatedAt,
224          coverImage: pageOfPosts.coverImage,
225          coverAlt: pageOfPosts.coverAlt,
226          readingTimeMinutes: pageOfPosts.readingTimeMinutes,
227          featured: pageOfPosts.featured,
228          series: pageOfPosts.series,
229          part: pageOfPosts.part,
230          // Aliased: `posts.slug` and `tags.slug` collide in drizzle's by-name row mapping.
231          tagSlug: sql<string | null>`${tags.slug}`.as("tag_slug"),
232        })
233        .from(pageOfPosts)
234        // LEFT, so a post with no tags keeps its row.
235        .leftJoin(postTags, eq(postTags.postId, pageOfPosts.id))
236        .leftJoin(tags, eq(tags.id, postTags.tagId))
237        // Every post key before the tag key, or ties interleave rows of different posts.
238        .orderBy(
239          desc(pageOfPosts.publishAt),
240          desc(pageOfPosts.groupKey),
241          desc(sql`COALESCE(${pageOfPosts.part}, 0)`),
242          desc(pageOfPosts.id),
243          asc(tags.slug),
244        ),
245    ]),
246  );
247
248  const stats = statRows[0];
249  const total = stats?.total ?? 0;
250
251  // One entry per post; the Map keeps the ORDER BY's post and tag order.
252  type Row = (typeof joined)[number];
253  const byId = new Map<number, Omit<Row, "tagSlug"> & { tags: string[] }>();
254  for (const row of joined) {
255    const { tagSlug, ...post } = row;
256    let entry = byId.get(post.id);
257    if (!entry) {
258      entry = { ...post, tags: [] };
259      byId.set(post.id, entry);
260    }
261    if (tagSlug !== null) entry.tags.push(tagSlug);
262  }
263
264  return {
265    posts: [...byId.values()].map(({ id: _dropped, ...rest }) => rest),
266    total,
267    page,
268    perPage,
269    pageCount: pageCount(total, perPage),
270    /* The whole filtered list, not this page of it. */
271    span: spanOf(stats),
272  };
273}
274
275/** The featured post leads, then the hand-picked others, then the newest; with nothing featured the first of those leads. */
276export async function listHomeStartHere(
277  env: Env,
278  options: { cards?: number; timings?: Timings } = {},
279) {
280  const db = getDb(env);
281  /** Cards the section renders in total, lead included. */
282  const cards = Math.max(1, options.cards ?? HOME_CARDS);
283
284  /* `cards` others, not `cards - 1`: the extra row leads when nothing is featured. */
285  const [featuredRows, pickedRows, newestRows, countRows] = await timed(options.timings, "d1_batch", () =>
286    db.batch([
287      db
288        .select(postCard)
289        .from(posts)
290        .where(and(isBlogPost(), eq(posts.featured, true)))
291        .orderBy(...newestFirstOrder())
292        .limit(1),
293      db
294        .select(postCard)
295        .from(posts)
296        .where(and(isBlogPost(), eq(posts.featured, false), inArray(posts.slug, HOME_PICKS))),
297      db
298        .select(postCard)
299        .from(posts)
300        .where(and(isBlogPost(), eq(posts.featured, false)))
301        .orderBy(...newestFirstOrder())
302        .limit(cards),
303      db.select({ total: count() }).from(posts).where(isBlogPost()),
304    ]),
305  );
306
307  /* Which list leads is `startHere`'s rule, shared with `check:machine-readable`. */
308  const otherRows = picksFirst([...pickedRows, ...newestRows]);
309  const { featured, recent } = startHere(featuredRows, otherRows, cards);
310
311  return { featured, recent, total: countRows[0]?.total ?? 0 };
312}
313
314export async function listBlogYears(env: Env) {
315  return getDb(env)
316    .select({
317      year: sql<string>`strftime('%Y', ${posts.publishAt}, 'unixepoch')`.as("year"),
318      total: count(),
319    })
320    .from(posts)
321    .where(isBlogPost())
322    .groupBy(sql`strftime('%Y', ${posts.publishAt}, 'unixepoch')`)
323    .orderBy(desc(sql`strftime('%Y', ${posts.publishAt}, 'unixepoch')`));
324}
325
326export async function listSeriesParts(env: Env, series: string) {
327  return getDb(env)
328    .select({ slug: posts.slug, title: posts.title, part: posts.part })
329    .from(posts)
330    .where(and(isBlogPost(), eq(posts.series, series)))
331    .orderBy(asc(posts.part));
332}
333
334export async function listBlogSeries(env: Env) {
335  return getDb(env)
336    .select({ name: posts.series, total: count(posts.id), lastmod: max(posts.updatedAt) })
337    .from(posts)
338    .where(and(isBlogPost(), isNotNull(posts.series)))
339    .groupBy(posts.series)
340    .orderBy(asc(posts.series));
341}
342
343/** Ambiguous slugs are refused. */
344export async function getBlogSeries(env: Env, slug: string) {
345  const all = await listBlogSeries(env);
346  const matches = all.filter((row) => row.name !== null && seriesSlug(row.name) === slug);
347  const row = matches.length === 1 ? matches[0] : undefined;
348  if (!row) return null;
349  return { name: row.name as string, slug, total: Number(row.total) };
350}
351
352/** One series as cards, oldest part first. Separate so post pages skip the tag join. */
353export async function listSeriesPosts(
354  env: Env,
355  series: string,
356  options: { page?: number; perPage?: number } = {},
357) {
358  const db = getDb(env);
359  const perPage = options.perPage ?? POSTS_PER_PAGE;
360  const page = Math.max(1, options.page ?? 1);
361  const where = and(isBlogPost(), eq(posts.series, series));
362
363  const [statRows, rows] = await db.batch([
364    /* The same statement the index counts with, so every evidence row uses one arithmetic. */
365    listingSpanSelect(db, eq(posts.series, series)),
366    db
367      .select({ ...postCard, id: posts.id })
368      .from(posts)
369      .where(where)
370      .orderBy(asc(posts.part), asc(posts.id))
371      .limit(perPage)
372      .offset((page - 1) * perPage),
373  ]);
374
375  const total = Number(statRows[0]?.total ?? 0);
376  const tagMap = await tagsForPosts(db, rows.map((r) => r.id));
377  return {
378    posts: rows.map(({ id, ...rest }) => ({ ...rest, tags: tagMap.get(id) ?? [] })),
379    total,
380    page,
381    pageCount: pageCount(total, perPage),
382    span: spanOf(statRows[0]),
383  };
384}
385
386export async function listBlogTags(env: Env) {
387  return getDb(env)
388    .select({ slug: tags.slug, name: tags.name, total: count(posts.id), lastmod: max(posts.updatedAt) })
389    .from(tags)
390    .innerJoin(postTags, eq(postTags.tagId, tags.id))
391    .innerJoin(posts, eq(posts.id, postTags.postId))
392    .where(isBlogPost())
393    .groupBy(tags.slug, tags.name)
394    .orderBy(asc(tags.slug));
395}
396
397/** For the sitemap's image entries: each public post that renders an image, with its HTML. */
398export async function listPostsWithImages(env: Env) {
399  return getDb(env)
400    .select({ slug: posts.slug, html: posts.html })
401    .from(posts)
402    .where(and(isBlogPost(), sql`${posts.html} LIKE '%<img%'`));
403}
404
405export async function getBlogPost(env: Env, slug: string) {
406  const db = getDb(env);
407  const rows = await db
408    .select()
409    .from(posts)
410    .where(and(eq(posts.slug, slug), isBlogPost()))
411    .limit(1);
412
413  const post = rows[0];
414  if (!post) return null;
415
416  const tagMap = await tagsForPosts(db, [post.id]);
417  return withNeighbours(db, post, tagMap.get(post.id) ?? []);
418}
419
420export type PostRow = typeof posts.$inferSelect;
421
422/**
423 * The Carrel preview's twin of getBlogPost, for a row built in memory rather than read from D1: the
424 * same tags shape and the same neighbours, so the page renders exactly as it would once published.
425 */
426export async function previewBlogPost(env: Env, post: PostRow, tagSlugs: string[]) {
427  return withNeighbours(getDb(env), post, [...tagSlugs].sort());
428}
429
430async function withNeighbours(db: DB, post: PostRow, tagSlugs: string[]) {
431  // Neighbors use the same visibility gate, so prev/next cannot reach a draft. Ordered as the listing is
432  // (newestFirstOrder), so posts sharing a publish_at are neighbors rather than skipped, and the parts of a
433  // series follow one another. The slug is excluded so a previewed post, whose row may differ from the stored
434  // one, is not its own neighbour.
435  const at = post.publishAt ?? new Date();
436  // This post's place in the order within its instant: its group key (the newest id of its series that day,
437  // else its own id), worked out in the same statement as the neighbours so there is one round trip.
438  const ownGroup = post.series
439    ? sql`COALESCE((SELECT MAX(g.id) FROM posts g WHERE g.kind = 'post' AND g.status = ${PUBLISHED_STATUS} AND g.publish_at = ${Math.floor(at.getTime() / 1000)} AND g.series = ${post.series}), ${post.id})`
440    : sql`${post.id}`;
441  const ownPart = post.part ?? 0;
442  /** Strictly before (`lt`) or after (`gt`) this post, by the listing's keys in turn. */
443  const beyond = (cmp: typeof lt) =>
444    or(
445      cmp(posts.publishAt, at),
446      and(
447        eq(posts.publishAt, at),
448        or(
449          cmp(groupKey, ownGroup),
450          and(
451            eq(groupKey, ownGroup),
452            or(cmp(partKey, ownPart), and(eq(partKey, ownPart), cmp(posts.id, post.id))),
453          ),
454        ),
455      ),
456    );
457  const [[previous], [next]] = await Promise.all([
458    db
459      .select({ slug: posts.slug, title: posts.title })
460      .from(posts)
461      .where(and(isBlogPost(), ne(posts.slug, post.slug), beyond(lt)))
462      .orderBy(...newestFirstOrder())
463      .limit(1),
464    db
465      .select({ slug: posts.slug, title: posts.title })
466      .from(posts)
467      .where(and(isBlogPost(), ne(posts.slug, post.slug), beyond(gt)))
468      .orderBy(...newestFirstOrder(asc))
469      .limit(1),
470  ]);
471
472  return {
473    ...post,
474    tags: tagSlugs,
475    previous: previous ?? null,
476    next: next ?? null,
477  };
478}
479
480/** Exempt from `publiclyVisible()`: matches `status = 'draft'` only, with the slug from a valid preview token. */
481export async function getDraftPostForPreview(env: Env, slug: string) {
482  const db = getDb(env);
483  const rows = await db
484    .select()
485    .from(posts)
486    .where(and(eq(posts.slug, slug), eq(posts.kind, "post"), eq(posts.status, "draft")))
487    .limit(1);
488
489  const post = rows[0];
490  if (!post) return null;
491
492  const tagMap = await tagsForPosts(db, [post.id]);
493
494  // No neighbors: a draft has no place in the visible sequence.
495  return {
496    ...post,
497    tags: tagMap.get(post.id) ?? [],
498    previous: null as { slug: string; title: string } | null,
499    next: null as { slug: string; title: string } | null,
500  };
501}
502
503/** Admin only, so exempt from visibility. Media via `notTrashed()` to match the media page. */
504export async function adminNavCounts(env: Env) {
505  const db = getDb(env);
506  const [postRow, mediaRow] = await Promise.all([
507    db.select({ n: count() }).from(posts).where(eq(posts.kind, "post")),
508    db.select({ n: count() }).from(media).where(notTrashed()),
509  ]);
510  return { posts: Number(postRow[0]?.n ?? 0), media: Number(mediaRow[0]?.n ?? 0) };
511}
512
513export async function listAllPostsForAdmin(env: Env) {
514  return getDb(env)
515    .select({
516      slug: posts.slug,
517      title: posts.title,
518      status: posts.status,
519      publishAt: posts.publishAt,
520      updatedAt: posts.updatedAt,
521      featured: posts.featured,
522    })
523    .from(posts)
524    .where(eq(posts.kind, "post"))
525    .orderBy(desc(posts.publishAt));
526}
527
528/** Corpus for `withRelated`, drafts included: it filters candidates itself. */
529export async function listPostCorpusForRelated(env: Env) {
530  const rows = await getDb(env)
531    .select({
532      slug: posts.slug,
533      title: posts.title,
534      status: posts.status,
535      publishAt: posts.publishAt,
536      // For each neighbor's description in a saved related list; not scored.
537      description: posts.description,
538    })
539    .from(posts)
540    .where(and(eq(posts.kind, "post"), isNotNull(posts.sourcePath)));
541  const tagRows = await listAllPostTagsForAdmin(env);
542
543  const tagsBySlug = groupValues(tagRows, (row) => row.slug, (row) => row.tag);
544  return rows.map((r) => ({ ...r, tags: tagsBySlug.get(r.slug) ?? [] }));
545}
546
547/** Drafts included: it filters the linking post itself. Carries `html` because a backlink is read out of a rendered body. */
548export async function listPostLinkCorpus(env: Env) {
549  return getDb(env)
550    .select({
551      slug: posts.slug,
552      title: posts.title,
553      status: posts.status,
554      publishAt: posts.publishAt,
555      html: posts.html,
556    })
557    .from(posts)
558    .where(and(eq(posts.kind, "post"), isNotNull(posts.sourcePath)));
559}
560
561/** The stored `related` and `backlinks` JSON of every file-sourced post, for the save's neighbour refresh to diff against. */
562export async function listPostDerivedLists(env: Env) {
563  return getDb(env)
564    .select({ slug: posts.slug, related: posts.related, backlinks: posts.backlinks })
565    .from(posts)
566    .where(and(eq(posts.kind, "post"), isNotNull(posts.sourcePath)));
567}
568
569/** Markdown and covers for the citation scan, drafts included: they still block deletion. */
570export async function listPostSourcesForCitations(env: Env) {
571  return getDb(env)
572    .select({
573      slug: posts.slug,
574      title: posts.title,
575      body: posts.body,
576      coverImage: posts.coverImage,
577    })
578    .from(posts)
579    .where(eq(posts.kind, "post"));
580}
581
582/** Operator list_posts projection, drafts included. */
583export async function listPostsForOperator(env: Env) {
584  const rows = await getDb(env)
585    .select({
586      slug: posts.slug,
587      title: posts.title,
588      description: posts.description,
589      status: posts.status,
590      publishAt: posts.publishAt,
591      updatedAt: posts.updatedAt,
592      series: posts.series,
593      part: posts.part,
594      sourcePath: posts.sourcePath,
595    })
596    .from(posts)
597    .where(and(eq(posts.kind, "post"), isNotNull(posts.sourcePath)))
598    .orderBy(asc(posts.slug));
599  const tagRows = await listAllPostTagsForAdmin(env);
600
601  const tagsBySlug = groupValues(tagRows, (row) => row.slug, (row) => row.tag);
602  return rows.map((r) => ({ ...r, tags: tagsBySlug.get(r.slug) ?? [] }));
603}
604
605export async function getAdminPostRow(env: Env, slug: string) {
606  return (
607    (await getDb(env)
608      .select({
609        html: posts.html,
610        readingTimeMinutes: posts.readingTimeMinutes,
611      })
612      .from(posts)
613      .where(and(eq(posts.kind, "post"), eq(posts.slug, slug)))
614      .get()) ?? null
615  );
616}
617
618/** All post tags, drafts included, for the admin filter; `listBlogTags` hides drafts. */
619export async function listAllPostTagsForAdmin(env: Env) {
620  return getDb(env)
621    .select({ slug: posts.slug, tag: tags.slug })
622    .from(postTags)
623    .innerJoin(posts, eq(posts.id, postTags.postId))
624    .innerJoin(tags, eq(tags.id, postTags.tagId))
625    .where(eq(posts.kind, "post"))
626    .orderBy(asc(tags.slug));
627}
628
629/** `id` is for the tag join, stripped after. */
630const FEED_COLUMNS = {
631  id: posts.id,
632  slug: posts.slug,
633  title: posts.title,
634  description: posts.description,
635  publishAt: posts.publishAt,
636  updatedAt: posts.updatedAt,
637  coverImage: posts.coverImage,
638};
639
640type FeedOptions = {
641  perPage?: number;
642  tag?: string | null;
643  series?: string | null;
644  /** `part` for a series feed; anything else is newest first. */
645  orderBy?: "date" | "part";
646};
647
648/** The filter and order both feed listings share. Narrowed in the query: slicing afterwards would cap before narrowing. */
649function feedScope(db: DB, options: FeedOptions) {
650  const clauses = [isBlogPost()];
651  if (options.tag) clauses.push(carriesTag(db, options.tag));
652  if (options.series) clauses.push(eq(posts.series, options.series));
653  const order =
654    options.orderBy === "part" ? [asc(posts.part), asc(posts.id)] : newestFirstOrder();
655  return { where: and(...clauses), order };
656}
657
658/** Attaches each row's tags and strips the `id` the join needed. */
659async function withFeedTags<R extends { id: number }>(db: DB, rows: R[]) {
660  const tagMap = await tagsForPosts(db, rows.map((r) => r.id));
661  return rows.map(({ id, ...rest }) => ({ ...rest, tags: tagMap.get(id) ?? [] }));
662}
663
664/** Visible posts with markdown, newest first; limited in SQL because bodies are heavy. */
665export async function listBlogPostsFullText(env: Env, options: FeedOptions = {}) {
666  const db = getDb(env);
667  const { where, order } = feedScope(db, options);
668  const query = db
669    .select({ ...FEED_COLUMNS, body: posts.body })
670    .from(posts)
671    .where(where)
672    .orderBy(...order);
673  return withFeedTags(db, await (options.perPage ? query.limit(options.perPage) : query));
674}
675
676/** Same posts with rendered HTML, for RSS. A sibling, not a union-returning flag. */
677export async function listBlogPostsRendered(env: Env, options: FeedOptions = {}) {
678  const db = getDb(env);
679  const { where, order } = feedScope(db, options);
680  const query = db
681    .select({ ...FEED_COLUMNS, html: posts.html })
682    .from(posts)
683    .where(where)
684    .orderBy(...order);
685  return withFeedTags(db, await (options.perPage ? query.limit(options.perPage) : query));
686}
687
688export async function getBlogPostMarkdown(env: Env, slug: string) {
689  const rows = await getDb(env)
690    .select({ body: posts.body, title: posts.title })
691    .from(posts)
692    .where(and(eq(posts.slug, slug), isBlogPost()))
693    .limit(1);
694  return rows[0] ?? null;
695}