app/lib/backup/policy.mjs

Functions

Imports

It imports no other module of this repo. Imported by app/lib/backup/d1-backup.server.ts.

Source

1// PURE on purpose, like verdicts.mjs: check:tests cannot reach a module that imports a binding, and
2// the rules that decide whether a dump may be marked complete must be provable by `node --test`.
3
4/**
5 * The D1 tables that are NOT derived from the repository or the bucket, so the dump
6 * is the only copy of their rows. Read off drizzle/*.sql on 2026-10-11; the reason names the source
7 * of each. Every other table converges toward the repository or a bucket and is repaired through
8 * its derivation, so its absence from a dump would cost a rebuild, not data.
9 *
10 * `settings` is not here: its one key, llms.txt, is derived from the repository by sync_llms.
11 */
12export const NON_DERIVED_TABLES = Object.freeze({
13  webmentions: "rows came from strangers' POSTs and the moderation decisions on them (migration 0014)",
14  zero_result_queries: "search demand signal, swept by retention, converges toward nothing (0015)",
15  procedure_versions: "the sealed frozen copy of every published procedure version, never rewritten (0027)",
16  kb_locations: "Knowledge Base inventory locations, written through site-api (0029)",
17  kb_lots: "Knowledge Base inventory lots, written through site-api (0029)",
18  kb_audit: "the audit trail of the two inventory tables (0029)",
19  members: "lab members and their titles (0030)",
20  member_audit: "the audit trail of membership changes (0030)",
21  member_terms: "lab term end dates (0030)",
22});
23
24/** A dump that lacks any of these is not complete, whatever else it holds. */
25export const REQUIRED_TABLES = Object.freeze(Object.keys(NON_DERIVED_TABLES));
26
27/**
28 * Tables left out of the dump because they hold live credentials or one-time tokens. EMPTY, and
29 * deliberately so: no table in the current schema holds either. The Better Auth tables (user,
30 * session, account, verification) were dropped by migration 0026, admin sign-in is Cloudflare
31 * Access and keeps no row, and preview-link tokens live in KV (app/lib/preview-token.mjs), not D1.
32 * A table added later that matches CREDENTIAL_COLUMN fails the dump by name until it is listed
33 * here, with the reason, or its column is renamed.
34 */
35/** @type {readonly string[]} */
36export const EXCLUDED_TABLES = Object.freeze(/** @type {string[]} */ ([]));
37
38/** A column name that says it holds a credential. Matched case-insensitively, camelCase included. */
39export const CREDENTIAL_COLUMN = /password|passwd|secret|token|api_?key|credential|private_?key/i;
40
41/**
42 * @param {string} sql a CREATE TABLE statement
43 * @returns {string[]} the column names it declares
44 */
45export function columnNames(sql) {
46  const open = sql.indexOf("(");
47  const close = sql.lastIndexOf(")");
48  if (open < 0 || close < open) return [];
49  const body = sql.slice(open + 1, close);
50  const parts = [];
51  let depth = 0;
52  let current = "";
53  for (const ch of body) {
54    if (ch === "(") depth += 1;
55    if (ch === ")") depth -= 1;
56    if (ch === "," && depth === 0) {
57      parts.push(current);
58      current = "";
59    } else current += ch;
60  }
61  parts.push(current);
62  const names = [];
63  for (const part of parts) {
64    const text = part.trim();
65    if (!text || /^(constraint|primary|unique|foreign|check)\b/i.test(text)) continue;
66    const match = /^(?:"([^"]+)"|`([^`]+)`|\[([^\]]+)\]|(\S+))/.exec(text);
67    const name = match?.[1] ?? match?.[2] ?? match?.[3] ?? match?.[4];
68    if (name) names.push(name);
69  }
70  return names;
71}
72
73/**
74 * This site's own problems that stop a dump being marked complete, each naming the table. d1-dump's
75 * runCheckedBackup runs its standard checks first (not empty, every REQUIRED_TABLES table present,
76 * nothing excluded in the dump), then these. An empty list from both is the only pass.
77 *
78 * @param {{
79 *   tables: Array<{ name: string, rows: number }>,
80 *   excluded: string[],
81 *   schema: Array<{ type: string, name: string, sql: string }>,
82 * }} dump what dumpDatabase returned, and the CREATE statements of the database it read
83 * @param {{ allowedExcluded?: readonly string[] }} [policy]
84 * @returns {string[]}
85 */
86export function dumpProblems(dump, policy = {}) {
87  const allowedExcluded = policy.allowedExcluded ?? EXCLUDED_TABLES;
88  const problems = [];
89  const held = new Set(dump.tables.map((t) => t.name));
90
91  for (const name of dump.excluded) {
92    if (!allowedExcluded.includes(name)) problems.push(`table ${name} was excluded but is not on the excluded list, so its rows would be lost silently`);
93  }
94
95  for (const entry of dump.schema) {
96    if (entry.type !== "table" || /^\s*CREATE\s+VIRTUAL/i.test(entry.sql)) continue;
97    if (dump.excluded.includes(entry.name)) continue;
98    if (!held.has(entry.name)) continue;
99    const credential = columnNames(entry.sql).filter((c) => CREDENTIAL_COLUMN.test(c));
100    if (credential.length > 0) {
101      problems.push(`table ${entry.name} has credential-looking column(s) ${credential.join(", ")} and is in the dump; exclude it with a reason or rename the column`);
102    }
103  }
104  return problems;
105}