src/backup.ts

Functions

Imports

It imports src/env.ts, src/health.ts, src/log.ts, src/security-headers.ts, src/store-probe.ts, src/time.ts. Imported by src/index.ts, src/routes.ts.

Source

1import { dumpDatabase, pruneDumps, writeCompleteMarker, type DumpResult } from "@dustinedwards/d1-dump";
2import type { Env } from "./env";
3import { BACKUP_LAST_OK_KEY } from "./health";
4import { REPORT_PREFIX } from "./security-headers";
5import { probeFts } from "./store-probe";
6import { logEvent } from "./log";
7import { DAY_MS } from "./time";
8
9const JSON_PREFIX = "backups/json/";
10const MARKDOWN_PREFIX = "backups/markdown/";
11const PUT_CONCURRENCY = 20;
12
13// KV lease is best-effort (no CAS). Export before prune. Dump retention is d1-dump's graduated policy.
14// Two runs can both read the key empty and both take it, so the run re-reads the lease
15// after its export and refuses every destructive step unless the lease is still its own.
16//
17// The lease value carries a per-run token and a run releases only its own, so a run
18// that outlived its TTL cannot delete the lease of the run that started after it. The
19// TTL is an hour: above the 15-minute wall limit on a cron invocation, with room for
20// an /ops/backup run, which has no wall limit. A lease left by an isolate that died
21// mid-run then blocks backups for at most an hour of a daily schedule.
22const LEASE_KEY = "backup:lease";
23const LEASE_TTL_SECONDS = 3600;
24
25// Retention for the history tables. Both are covered by the dump shelf life above.
26const VERSION_RETENTION_DAYS = 90;
27const AUDIT_RETENTION_DAYS = 180;
28
29// CSP and COOP violation reports. Nothing else prunes this prefix, and a public
30// unauthenticated path writes it.
31const REPORT_RETENTION_DAYS = 30;
32
33// Both prefixes carry an ISO date at a fixed offset, so the age test is a string
34// comparison. A key with no parseable day is never pruned.
35function isOlderThan(key: string, prefix: string, cutoffDay: string): boolean {
36  const day = key.slice(prefix.length, prefix.length + 10);
37  if (!/^\d{4}-\d{2}-\d{2}$/.test(day)) return false;
38  return day < cutoffDay;
39}
40
41function cutoffDay(now: Date, days: number): string {
42  return new Date(now.getTime() - days * DAY_MS).toISOString().slice(0, 10);
43}
44
45// The dump itself is @dustinedwards/d1-dump, the package every site installs, so
46// Capsid and the sites run one implementation.
47//
48// Tables paged rather than read whole. Reading every table in one batch holds the
49// whole database in the isolate, and once version bodies grew past what the isolate
50// holds the cron died before its first put. A table qualifies only if it is
51// append-only with an AUTOINCREMENT id, so the MAX(id) bound read inside the snapshot
52// keeps the one-instant dump: rows at or below it cannot have changed, and only this
53// run's own prune (after the export, under the lease) deletes them. documents and jobs
54// are updated in place, so they stay in the batch. A version row can be a few hundred
55// KB; an audit row carries no document body. A claim row can list 500 touched paths,
56// so job_claims pages too; its two sibling tables carry no lists and stay in the batch.
57const PAGED = {
58  document_versions: { idColumn: "id", pageRows: 100 },
59  audit_log: { idColumn: "id", pageRows: 1000 },
60  job_claims: { idColumn: "id", pageRows: 200 },
61};
62// wrangler's migration ledger. A restore builds the schema from migrations/, which
63// writes its own. The weekly drill builds its scratch database from _schema.json, and a
64// run whose dumped tables differ from TABLES is refused as tables-mismatch.
65const EXCLUDED = ["d1_migrations"];
66
67// Every real table in the schema. test/backup.test.ts derives this list from
68// migrations/ and fails in both directions. Excluded: documents_fts and its
69// documents_fts_* shadow tables, which the sync triggers rebuild from documents,
70// and sqlite_sequence, which SQLite maintains for AUTOINCREMENT.
71export const TABLES = [
72  "documents",
73  "namespaces",
74  "document_versions",
75  "audit_log",
76  "document_links",
77  // Nothing prunes the tables below except improve_jti, so a dump is the only copy
78  // outside D1. job_outcomes and job_outcome_prs hold counts verified against pull
79  // requests GitHub can delete. agents carries the sha256 verifier, never a key, and
80  // a revoked agent keeps its row so the audit trail it wrote still resolves.
81  "jobs",
82  "job_outcomes",
83  "agents",
84  "job_outcome_prs",
85  // The Portal's site configuration: edited by hand in the Portal, so the dump is
86  // its only copy outside D1 besides the audit rows of each edit.
87  "ops_sites",
88  // The Portal's package configuration and its weekly GitHub numbers (migrations/0028):
89  // the configuration is edited by hand, and the weekly rows are kept nowhere else.
90  "ops_packages",
91  "ops_package_weeks",
92  // The claims dataset (migrations/0023_job_claims.sql): what each agent said it did,
93  // each check of that against GitHub, and every human touch. Append-only by trigger,
94  // and nothing prunes them, so the dump is the only copy outside D1.
95  "job_claims",
96  "job_evaluations",
97  "job_touches",
98  // The watcher's memory of each finding across jobs (migrations/0024_watcher_findings.sql),
99  // and what Claude Code sessions reported by hook and telemetry (0025). session_events
100  // holds a row per tool call since the tool events (docs/hooks.md), the largest table
101  // here, and is pruned at 30 days in code.
102  "watcher_findings",
103  "agent_sessions",
104  "session_events",
105  "session_usage",
106  // The run ledger (migrations/0029_task_runs.sql): each scheduled task's runs,
107  // pruned at 14 days in code (src/task-runs.ts).
108  "task_runs",
109  // Each job's earlier versions, kept by capsid_work_jobs action "edit" (migrations/0033_job_versions.sql).
110  // Nothing prunes it, and the job's row holds only the newest version.
111  "job_versions",
112  // Drivers' proposed changes to canon documents, pending or decided
113  // (migrations/0034_canon_proposals.sql). Nothing prunes it: a decided row is the
114  // record of who proposed and who approved a canon change.
115  "canon_proposals",
116  // The shared packages the Shared code view follows (migrations/0036_shared_packages.sql):
117  // configuration, seeded by its migration, so a restore empties it before importing.
118  "shared_packages",
119  // The merge pipeline's approvals (migrations/0037_pr_approvals.sql): who approved which
120  // pull request at which head, and how each approval ended. Nothing prunes it.
121  "pr_approvals",
122  // Revoked agents moved out of agents by the watcher pass (migrations/0038_agents_archive.sql).
123  // Nothing prunes it: an archived row keeps a name reserved and a revoked key refused.
124  "agents_archive",
125  // What became of each deploy whose live gate ran (migrations/0039_deploy_outcomes.sql),
126  // the evidence the promotion and demotion proposals read. Nothing prunes it.
127  "deploy_outcomes",
128  // The Access group pinned for each app's members page (migrations/0040_access_sync_groups.sql):
129  // configuration the seat writes by hand. Nothing prunes it.
130  "access_sync_groups",
131  // The devices that get a phone alert (migrations/0041_push_subscriptions.sql).
132  "push_subscriptions",
133  // The methods paper's paired comparison (migrations/0047_bench.sql): the registered
134  // tasks and every run's outcome, the data its primary outcome is computed from.
135  // Insert-only and close-once by trigger; nothing prunes them.
136  "bench_tasks",
137  "bench_runs",
138  // The registration and the drawn task order (migrations/0049_bench_draw.sql),
139  // insert-only by trigger; nothing prunes them.
140  "bench_registration",
141  "bench_draw",
142  // The replay cache, pruned below: a jti matters only inside the signature window.
143  "improve_jti",
144] as const;
145
146interface BackupSummary {
147  ran: true;
148  json_prefix: string;
149  json_keys: string[];
150  documents: number;
151  markdown_written: number;
152  markdown_pruned: number;
153  json_backups_kept: number;
154  json_backups_pruned: number;
155  // Stale runs an R2 bucket lock refused to delete, left for a later prune (docs/backups.md).
156  json_backups_locked: number;
157  reports_pruned: number;
158  versions_pruned: number;
159  audit_pruned: number;
160  // Null on a healthy run. Otherwise the named reason NOTHING was deleted. Read it
161  // before believing a zero in any of the *_pruned counters.
162  prune_refused: string | null;
163  preflight: { documents: number; fts: string };
164}
165
166// A run that did not run. Named rather than thrown so the caller can tell a held
167// lease from a failure.
168interface BackupSkipped {
169  ran: false;
170  skipped: string;
171}
172
173export type BackupResult = BackupSummary | BackupSkipped;
174
175// The HTTP status /ops/backup answers with, so "did nothing" is visible without the
176// body: 409 when another run holds the lease, 503 when the preflight refused the prune
177// (the dumps were written but nothing was marked, mirrored or pruned), 200 otherwise.
178export function backupHttpStatus(result: BackupResult): number {
179  if (!result.ran) return 409;
180  return result.prune_refused === null ? 200 : 503;
181}
182
183// R2's bulk delete takes at most 1000 keys per call and refuses the rest rather than
184// truncating, so an unbounded array throws after the dumps are written and before
185// backup:last-ok is stamped.
186const R2_DELETE_MAX = 1000;
187
188// One chunker for every prune, so a new delete site cannot be written unchunked
189// beside the others.
190async function deleteInChunks(bucket: R2Bucket, keys: string[]): Promise<number> {
191  for (let i = 0; i < keys.length; i += R2_DELETE_MAX) {
192    await bucket.delete(keys.slice(i, i + R2_DELETE_MAX));
193  }
194  return keys.length;
195}
196
197async function listAllKeys(bucket: R2Bucket, prefix: string): Promise<string[]> {
198  const keys: string[] = [];
199  let cursor: string | undefined;
200  do {
201    const page = await bucket.list({ prefix, cursor });
202    for (const obj of page.objects) keys.push(obj.key);
203    cursor = page.truncated ? page.cursor : undefined;
204  } while (cursor);
205  return keys;
206}
207
208export async function runBackup(env: Env): Promise<BackupResult> {
209  const held = await env.APP_KV.get(LEASE_KEY);
210  if (held !== null) {
211    logEvent("error", "BACKUP_LEASE_HELD", { message: `BACKUP_LEASE_HELD another run holds ${LEASE_KEY} (started ${held}); this run pruned nothing` });
212    return { ran: false, skipped: "lease-held" };
213  }
214  const now = new Date().toISOString();
215  const lease = `${now} ${crypto.randomUUID()}`;
216  await env.APP_KV.put(LEASE_KEY, lease, { expirationTtl: LEASE_TTL_SECONDS });
217  try {
218    return await exportAndPrune(env, now, lease);
219  } finally {
220    // Release on success or throw, and only this run's own lease: if the TTL lapsed
221    // and another run took the key, deleting it would let a third run start.
222    const current = await env.APP_KV.get(LEASE_KEY);
223    if (current === lease) await env.APP_KV.delete(LEASE_KEY);
224    else logEvent("error", "BACKUP_LEASE_LOST", { message: `BACKUP_LEASE_LOST ${LEASE_KEY} is no longer this run's (now ${current}); left in place` });
225  }
226}
227
228// The KV pins, by allowlist and never by prefix sweep. APP_KV also holds the GitHub
229// installation-token cache, and a dump leaves the account, so a prefix sweep would
230// mirror a live credential the first time someone added a key under a prefix nobody
231// re-read. Every key below is a control value, none a secret. backup:lease is absent:
232// it is this run's own bookkeeping.
233function kvPinKeys(): string[] {
234  return [BACKUP_LAST_OK_KEY];
235}
236
237// null means the key was unset. An unreadable key is { unreadable: reason }, because
238// restoring a null for it would clear a value that existed.
239type KvPin = string | null | { unreadable: string };
240
241async function readKvPins(env: Env): Promise<Record<string, KvPin>> {
242  const pins: Record<string, KvPin> = {};
243  for (const key of kvPinKeys()) {
244    try {
245      pins[key] = await env.APP_KV.get(key);
246    } catch (err) {
247      // Does not fail the run: the D1 dump must not be lost to a KV hiccup.
248      pins[key] = { unreadable: err instanceof Error ? err.message : String(err) };
249    }
250  }
251  return pins;
252}
253
254type DocRow = { namespace: string; path: string; body: string | null };
255
256// Writes one JSON object per table, _schema.json and the KV sidecar under one run
257// prefix, WITHOUT the completion marker, which exportAndPrune writes only once the
258// preflight passes. Returns the documents rows as dumped, which the preflight and the
259// markdown mirror both read, and any difference between the tables dumped and TABLES.
260//
261// The package reads every unpaged table in one D1 batch, which is one transaction, so
262// every table describes the same instant and exported_at describes it. The restore
263// invariants script (scripts/dump-invariants.ts) checks for the signature a torn read
264// leaves.
265//
266// The sidecar, underscore-prefixed so it cannot collide with a table name, holds the
267// KV pins, which are not in D1.
268async function exportDump(env: Env, now: string): Promise<{ dump: DumpResult; docs: DocRow[]; tablesMismatch: string | null }> {
269  const dump = await dumpDatabase(env.DB, env.MEDIA, {
270    prefix: JSON_PREFIX,
271    now: new Date(now),
272    paged: PAGED,
273    exclude: EXCLUDED,
274    sidecars: {
275      kv: { keys: await readKvPins(env) },
276    },
277    markComplete: false,
278  });
279
280  // Read back rather than kept from the snapshot: the package drops each table once it
281  // is written. A documents object that cannot be read fails the run.
282  const docsKey = dump.tables.find((t) => t.name === "documents")?.key;
283  const docsObject = docsKey ? await env.MEDIA.get(docsKey) : null;
284  if (!docsObject) throw new Error(`BACKUP_DOCUMENTS_UNREADABLE the dump at ${dump.prefix} has no readable documents object`);
285  const docs = (JSON.parse(await docsObject.text()) as { rows: DocRow[] }).rows;
286
287  // The package dumps what sqlite_master holds; TABLES is what the migrations create
288  // (test/backup.test.ts). A difference means the live schema is not the one the
289  // restore procedure knows, so the run is refused rather than marked complete.
290  const dumped = new Set(dump.tables.map((t) => t.name));
291  const listed = new Set<string>(TABLES);
292  const missing = TABLES.filter((t) => !dumped.has(t));
293  const extra = [...dumped].filter((t) => !listed.has(t));
294  const tablesMismatch =
295    missing.length || extra.length ? `tables-mismatch: missing ${missing.join(",") || "none"}; unlisted ${extra.join(",") || "none"}` : null;
296  return { dump, docs, tablesMismatch };
297}
298
299// The preflight, run before anything destructive. The dangerous case is a SELECT that
300// succeeds and returns nothing: an empty documents table, or a binding resolved to
301// an empty or different database, makes currentKeys empty, which marks every
302// markdown object stale and deletes the mirror in one call.
303//
304// Two probes: a count above zero, and the same pinned FTS probe /health uses, which
305// catches a binding pointed at a different database that has rows. Returns the named
306// reason to refuse, or null.
307async function preflight(env: Env, docs: DocRow[]): Promise<{ fts: string; pruneRefused: string | null }> {
308  const fts = await probeFts(env.DB);
309  const pruneRefused = docs.length === 0 ? "documents-empty" : fts === "ok" ? null : `fts-probe-failed: ${fts}`;
310  return { fts, pruneRefused };
311}
312
313// Writes every document to the markdown mirror and deletes mirror objects no document
314// backs. Returns how many were deleted.
315async function mirrorMarkdown(env: Env, docs: DocRow[]): Promise<number> {
316  const currentKeys = new Set<string>();
317  for (let i = 0; i < docs.length; i += PUT_CONCURRENCY) {
318    await Promise.all(
319      docs.slice(i, i + PUT_CONCURRENCY).map((doc) => {
320        const key = `${MARKDOWN_PREFIX}${doc.namespace}/${doc.path}`;
321        currentKeys.add(key);
322        return env.MEDIA.put(key, doc.body ?? "", { httpMetadata: { contentType: "text/markdown" } });
323      })
324    );
325  }
326
327  const existingMarkdown = await listAllKeys(env.MEDIA, MARKDOWN_PREFIX);
328  const staleMarkdown = existingMarkdown.filter((key) => !currentKeys.has(key));
329  if (staleMarkdown.length > 0) await deleteInChunks(env.MEDIA, staleMarkdown);
330  return staleMarkdown.length;
331}
332
333// Deletes JSON dump runs outside the graduated retention (14 daily, 8 weekly and 6
334// monthly copies, capsid/rulings/shared-homes-2026-10-06.md), which is d1-dump's default
335// policy and is pruneDumps's job, and CSP reports past retention (the sink stopped
336// storing them on 2026-10-20; this reaps the last of them). pruneDumps counts runs
337// and not objects, deletes a run whole, never prunes the newest complete run, and
338// deletes nothing while no complete run exists.
339async function pruneR2(env: Env, now: string): Promise<{ kept: number; pruned: number; locked: number; reports: number }> {
340  const dumps = await pruneDumps(env.MEDIA, { prefix: JSON_PREFIX, now: new Date(now) });
341
342  const reportCutoff = cutoffDay(new Date(now), REPORT_RETENTION_DAYS);
343  const staleReports = (await listAllKeys(env.MEDIA, REPORT_PREFIX)).filter((key) =>
344    isOlderThan(key, REPORT_PREFIX, reportCutoff)
345  );
346  if (staleReports.length > 0) await deleteInChunks(env.MEDIA, staleReports);
347  return { kept: dumps.kept, pruned: dumps.pruned, locked: dumps.locked, reports: staleReports.length };
348}
349
350// Prunes history after the export, so the rows leaving D1 are in today's dump.
351// meta.changes is inflated by the FTS5 triggers, so each DELETE is preceded by a
352// COUNT over the same predicate in the same transaction.
353async function pruneD1(env: Env): Promise<{ versions: number; audit: number }> {
354  const pruned = await env.DB.batch<{ n: number }>([
355    env.DB.prepare("SELECT COUNT(*) AS n FROM document_versions WHERE snapshot_at < datetime('now', ?1)").bind(
356      `-${VERSION_RETENTION_DAYS} days`
357    ),
358    env.DB.prepare("DELETE FROM document_versions WHERE snapshot_at < datetime('now', ?1)").bind(
359      `-${VERSION_RETENTION_DAYS} days`
360    ),
361    env.DB.prepare("SELECT COUNT(*) AS n FROM audit_log WHERE at < datetime('now', ?1)").bind(
362      `-${AUDIT_RETENTION_DAYS} days`
363    ),
364    env.DB.prepare("DELETE FROM audit_log WHERE at < datetime('now', ?1)").bind(`-${AUDIT_RETENTION_DAYS} days`),
365    // The replay cache, appended last so the count/delete pairs above keep the
366    // positions their counters read. A jti matters only inside the signature window.
367    env.DB.prepare("DELETE FROM improve_jti WHERE seen_at < datetime('now', '-1 day')"),
368  ]);
369  return { versions: pruned[0]?.results?.[0]?.n ?? 0, audit: pruned[2]?.results?.[0]?.n ?? 0 };
370}
371
372async function exportAndPrune(env: Env, now: string, lease: string): Promise<BackupSummary> {
373  const { dump, docs, tablesMismatch } = await exportDump(env, now);
374  const jsonPrefix = dump.prefix;
375  const jsonKeys = dump.keys;
376
377  // A failed preflight refuses everything past this point as a unit: the marker, the
378  // markdown write, the R2 prunes and the D1 deletes. A run that cannot trust its read
379  // of documents cannot trust content derived from it. The JSON dumps above are kept:
380  // an export deletes nothing, and an empty dump is the evidence of the day the store
381  // looked empty.
382  const checked = await preflight(env, docs);
383  const fts = checked.fts;
384  let pruneRefused = tablesMismatch ?? checked.pruneRefused;
385  // The lease is get-then-put, so a run that started alongside this one may hold it
386  // now. Re-read before anything destructive: a run that lost it prunes nothing.
387  if (pruneRefused === null) {
388    const holder = await env.APP_KV.get(LEASE_KEY);
389    if (holder !== lease) pruneRefused = `lease-lost: ${LEASE_KEY} is now ${holder}`;
390  }
391  if (pruneRefused !== null) {
392    logEvent("error", "BACKUP_PREFLIGHT_REFUSED", {
393      message:
394        `BACKUP_PREFLIGHT_REFUSED reason=${pruneRefused} documents=${docs.length} fts=${fts} ` +
395        `dumps_written=${jsonKeys.length} prefix=${jsonPrefix}; nothing was written to or deleted from the mirror`,
396    });
397    return {
398      ran: true,
399      json_prefix: jsonPrefix,
400      json_keys: jsonKeys,
401      documents: docs.length,
402      markdown_written: 0,
403      markdown_pruned: 0,
404      json_backups_kept: 0,
405      json_backups_pruned: 0,
406      json_backups_locked: 0,
407      reports_pruned: 0,
408      versions_pruned: 0,
409      audit_pruned: 0,
410      prune_refused: pruneRefused,
411      preflight: { documents: docs.length, fts },
412    };
413  }
414
415  // The completion marker, written after every object and only once the preflight
416  // passed, so a run that threw or was refused carries none. Only a marked run counts
417  // toward the retention floor. It lists the objects it vouches for, and the call adds
418  // its own key to jsonKeys.
419  await writeCompleteMarker(env.MEDIA, dump);
420
421  const markdownPruned = await mirrorMarkdown(env, docs);
422  const dumps = await pruneR2(env, now);
423  const history = await pruneD1(env);
424
425  // Stamp the last clean success, read by /health. A refused run returned above.
426  await env.APP_KV.put(BACKUP_LAST_OK_KEY, now);
427
428  return {
429    ran: true,
430    json_prefix: jsonPrefix,
431    json_keys: jsonKeys,
432    documents: docs.length,
433    markdown_written: docs.length,
434    markdown_pruned: markdownPruned,
435    json_backups_kept: dumps.kept,
436    json_backups_pruned: dumps.pruned,
437    json_backups_locked: dumps.locked,
438    reports_pruned: dumps.reports,
439    versions_pruned: history.versions,
440    audit_pruned: history.audit,
441    prune_refused: null,
442    preflight: { documents: docs.length, fts },
443  };
444}