app/db/webmentions.ts

Functions

Imports

It imports app/db/client.ts, app/db/schema.ts, app/lib/webmention/retention.mjs. Imported by app/db/index.ts.

Source

1// Webmentions: receive, verify, decide, list and the retention sweep.
2import { and, count, desc, eq, exists, inArray, lt } from "drizzle-orm";
3import {
4  FAILED_RETENTION_DAYS,
5  REJECTED_RETENTION_DAYS,
6  daysInMilliseconds,
7} from "../lib/webmention/retention.mjs";
8import { posts, webmentions } from "./schema";
9import { getDb, publiclyVisible, isBlogPost } from "./client";
10
11/** Statuses the global cap counts, stated once so the cap and the sweep agree. */
12const OPEN_WEBMENTION_STATUSES = ["unverified", "pending"] as const;
13
14/** Composes `isBlogPost()` so drafts cannot be probed. Returns a boolean, never the row. */
15export async function webmentionTarget(env: Env, slug: string): Promise<boolean> {
16  const rows = await getDb(env)
17    .select({ slug: posts.slug })
18    .from(posts)
19    .where(and(eq(posts.slug, slug), isBlogPost()))
20    .limit(1);
21  return rows.length > 0;
22}
23
24/** Open mentions; the endpoint refuses at the ceiling. */
25export async function countOpenWebmentions(env: Env): Promise<number> {
26  const rows = await getDb(env)
27    .select({ n: count() })
28    .from(webmentions)
29    .where(inArray(webmentions.status, [...OPEN_WEBMENTION_STATUSES]));
30  return rows[0]?.n ?? 0;
31}
32
33/**
34 * Writes or resets one (source, target) row, clearing any decision; returns its id.
35 * `received_at` is set here: the default applies on insert only.
36 */
37export async function receiveWebmention(
38  env: Env,
39  fields: { sourceUrl: string; targetSlug: string; now?: Date },
40): Promise<number> {
41  const now = fields.now ?? new Date();
42  const rows = await getDb(env)
43    .insert(webmentions)
44    .values({
45      sourceUrl: fields.sourceUrl,
46      targetSlug: fields.targetSlug,
47      status: "unverified",
48      receivedAt: now,
49    })
50    .onConflictDoUpdate({
51      target: [webmentions.sourceUrl, webmentions.targetSlug],
52      set: {
53        status: "unverified",
54        receivedAt: now,
55        verifiedAt: null,
56        decidedAt: null,
57        failureReason: null,
58        authorName: null,
59        authorUrl: null,
60        excerpt: null,
61      },
62    })
63    .returning({ id: webmentions.id });
64  // An upsert always returns its row; none means the write did not land, and a stand-in id would send
65  // the verifier after a row that does not exist.
66  const id = rows[0]?.id;
67  if (id === undefined) {
68    throw new Error(`receiveWebmention: the upsert for ${fields.sourceUrl} returned no row`);
69  }
70  return id;
71}
72
73/** The row the queue consumer re-reads before it fetches anything, or null once it is gone. */
74export async function webmentionToVerify(
75  env: Env,
76  id: number,
77): Promise<{ sourceUrl: string; status: string } | null> {
78  const rows = await getDb(env)
79    .select({ sourceUrl: webmentions.sourceUrl, status: webmentions.status })
80    .from(webmentions)
81    .where(eq(webmentions.id, id))
82    .limit(1);
83  return rows[0] ?? null;
84}
85
86export type WebmentionVerdict =
87  | {
88      status: "pending";
89      authorName: string | null;
90      authorUrl: string | null;
91      excerpt: string | null;
92    }
93  | { status: "failed"; failureReason: string };
94
95/** Only a row still `unverified` takes a verdict, so a stale fetch cannot overwrite a reset. */
96export async function recordWebmentionVerdict(
97  env: Env,
98  id: number,
99  verdict: WebmentionVerdict,
100  now: Date = new Date(),
101): Promise<void> {
102  const shared = { verifiedAt: now, decidedAt: null };
103  const set =
104    verdict.status === "pending"
105      ? {
106          ...shared,
107          status: "pending" as const,
108          failureReason: null,
109          authorName: verdict.authorName,
110          authorUrl: verdict.authorUrl,
111          excerpt: verdict.excerpt,
112        }
113      : {
114          ...shared,
115          status: "failed" as const,
116          failureReason: verdict.failureReason,
117        };
118  await getDb(env)
119    .update(webmentions)
120    .set(set)
121    .where(and(eq(webmentions.id, id), eq(webmentions.status, "unverified")));
122}
123
124/** The EXISTS on `posts` keeps an unpublished post's mentions hidden. */
125export async function approvedMentionsFor(env: Env, slug: string) {
126  const db = getDb(env);
127  return db
128    .select({
129      id: webmentions.id,
130      sourceUrl: webmentions.sourceUrl,
131      authorName: webmentions.authorName,
132      authorUrl: webmentions.authorUrl,
133      excerpt: webmentions.excerpt,
134      decidedAt: webmentions.decidedAt,
135    })
136    .from(webmentions)
137    .where(
138      and(
139        eq(webmentions.targetSlug, slug),
140        eq(webmentions.status, "approved"),
141        exists(
142          db
143            .select({ visible: posts.id })
144            .from(posts)
145            .where(and(eq(posts.slug, slug), publiclyVisible())),
146        ),
147      ),
148    )
149    .orderBy(desc(webmentions.decidedAt));
150}
151
152export async function listWebmentionsForAdmin(env: Env) {
153  return getDb(env).select().from(webmentions).orderBy(desc(webmentions.receivedAt));
154}
155
156/** Decidable: `unverified` has no evidence and `failed` has evidence against. */
157const DECIDABLE_WEBMENTION_STATUSES = ["pending", "approved", "rejected"] as const;
158
159/** Approve or reject one mention. Reversible, so decided rows stay decidable. */
160export async function decideWebmention(
161  env: Env,
162  id: number,
163  status: "approved" | "rejected",
164  now: Date = new Date(),
165): Promise<string | null> {
166  /* Returns the slug from the write so `purgePost` names the moved row; null purges nothing. */
167  const rows = await getDb(env)
168    .update(webmentions)
169    .set({ status, decidedAt: now })
170    .where(
171      and(
172        eq(webmentions.id, id),
173        inArray(webmentions.status, [...DECIDABLE_WEBMENTION_STATUSES]),
174      ),
175    )
176    .returning({ targetSlug: webmentions.targetSlug });
177  return rows[0]?.targetSlug ?? null;
178}
179
180/** What the sweep removes, per status: shared so the count and the sweep cannot drift apart. */
181function expiryPredicates(now: Date) {
182  const olderThan = (status: "failed" | "rejected", days: number) =>
183    and(
184      eq(webmentions.status, status),
185      lt(webmentions.receivedAt, new Date(now.getTime() - daysInMilliseconds(days))),
186    );
187  return {
188    failed: olderThan("failed", FAILED_RETENTION_DAYS),
189    rejected: olderThan("rejected", REJECTED_RETENTION_DAYS),
190  };
191}
192
193/** Rows the sweep would remove now, on `sweepWebmentions`'s predicates. */
194export async function countExpiringWebmentions(
195  env: Env,
196  now: Date = new Date(),
197): Promise<{ failed: number; rejected: number }> {
198  const db = getDb(env);
199  const expired = expiryPredicates(now);
200  const [failed, rejected] = await Promise.all([
201    db
202      .select({ n: count() })
203      .from(webmentions)
204      .where(expired.failed),
205    db
206      .select({ n: count() })
207      .from(webmentions)
208      .where(expired.rejected),
209  ]);
210  return { failed: failed[0]?.n ?? 0, rejected: rejected[0]?.n ?? 0 };
211}
212
213/** Remove one mention outright. The only delete an admin makes by hand. */
214export async function deleteWebmention(env: Env, id: number): Promise<string | null> {
215  // The slug comes back for the purge.
216  const rows = await getDb(env)
217    .delete(webmentions)
218    .where(eq(webmentions.id, id))
219    .returning({ targetSlug: webmentions.targetSlug });
220  return rows[0]?.targetSlug ?? null;
221}
222
223/** One statement per retention window. Open statuses never expire: the global cap counts them. */
224export async function sweepWebmentions(
225  env: Env,
226  now: Date = new Date(),
227): Promise<{ failed: number; rejected: number }> {
228  const db = getDb(env);
229  const expired = expiryPredicates(now);
230
231  const failed = await db
232    .delete(webmentions)
233    .where(expired.failed)
234    .returning({ id: webmentions.id });
235  const rejected = await db
236    .delete(webmentions)
237    .where(expired.rejected)
238    .returning({ id: webmentions.id });
239
240  return { failed: failed.length, rejected: rejected.length };
241}