1// Copies of the site's databases, which `node run download-db` saves into a
2// clone of this repository so that it can build the site. With the source of
3// truth's key, a copy is whole. Without it, a copy has only the tables listed
4// below, less their private rows, which is what the site shows anyone.
5export const app = new Hono();
6
7const token = process.env.CLOVER_SOT_KEY ?? null;
8
9/** per database, the tables a public copy keeps, and the statements that first delete what is private */
10const publicCopies = new Map([
11 ["cache.sqlite", {
12 tables: ["media_files", "permissions", "derived_roots", "derived_files", "derived_refs"],
13 redact: [
14 // what listings leave out: paths behind a password, and hidden or unlisted names.
15 // a directory takes its children with it, and a file its derived refs
16 `delete from media_files where exists (select 1 from permissions where lower(media_files.path) glob lower(prefix) || '*')
17 or path like '%/.%' or path like '%/\\_unlisted%' escape '\\'`,
18 "delete from permissions",
19 "delete from derived_roots where id not in (select root from derived_refs)",
20 ],
21 }],
22 ["questions.sqlite", {
23 tables: ["questions"],
24 redact: [`delete from questions where type <> ${questions.types.normal}`],
25 }],
26]);
27
28/** a connection of each database's own. copies read through it, and its `data_version` changes once another connection commits */
29const readers = new Map<string, sqlite.DatabaseSync>();
30incremental.onDispose(import.meta, () => readers.forEach((reader) => reader.close()));
31/** the `data_version` each copy was written at, by its file */
32const versions = new Map<string, number>();
33
34app.get("/db/:name", (c) => {
35 const name = c.req.param("name");
36 const publicCopy = publicCopies.get(name);
37 if (!publicCopy) return c.notFound();
38 const authorization = c.req.header("Authorization");
39 // a wrong key fails, so a deploy never builds from a public copy
40 if (authorization !== undefined && authorization !== token) {
41 return c.json({ error: "invalid authorization header" }, 401);
42 }
43 const file = getCopy(name, authorization === undefined ? publicCopy : null);
44 // a newer copy can replace the file, while this response reads the one it opened
45 const fd = fs.openSync(file, "r");
46 c.header("Content-Type", "application/vnd.sqlite3");
47 c.header("Content-Length", String(fs.fstatSync(fd).size));
48 return c.body(stream.Readable.toWeb(fs.createReadStream(file, { fd })) as ReadableStream);
49});
50
51/** a file with a copy of a database, written again once the database changes */
52function getCopy(name: string, publicCopy: { tables: string[]; redact: string[] } | null): string {
53 const { location } = getDatabase(name);
54 let reader = readers.get(location);
55 if (!reader) readers.set(location, reader = new sqlite.DatabaseSync(location, { readOnly: true }));
56 const version = Number(reader.prepare("pragma data_version;").get()?.data_version);
57 const file = `${location}.${publicCopy ? "public" : "full"}`;
58 if (versions.get(file) === version && fs.existsSync(file)) return file;
59
60 const temp = `${file}.tmp`;
61 fs.rmSync(temp, { force: true });
62 reader.prepare("vacuum into ?").run(temp);
63 if (publicCopy) {
64 using copy = new sqlite.DatabaseSync(temp);
65 copy.exec("pragma foreign_keys = on;");
66 for (const statement of publicCopy.redact) copy.exec(statement);
67 // every other table goes. a virtual table goes first, taking the tables that hold its rows
68 const objects = copy.prepare(`
69 select type, name from sqlite_master
70 where type in ('table', 'view', 'trigger') and name not like 'sqlite_%'
71 order by sql like 'create virtual%' desc
72 `).all() as Array<{ type: string; name: string }>;
73 for (const { type, name } of objects) {
74 if (type === "table" && (name === "clover_migrations" || publicCopy.tables.includes(name))) continue;
75 copy.exec(`drop ${type} if exists "${name}";`);
76 }
77 // a model creates the table again, empty, once the copy no longer says it was migrated
78 copy.exec("delete from clover_migrations where key not in (select name from sqlite_master where type = 'table');");
79 // without `secure_delete`, deleted rows linger in a file until a vacuum writes it anew
80 copy.exec("vacuum;");
81 }
82 fs.renameSync(temp, file);
83 versions.set(file, version);
84 return file;
85}
86
87import { getDatabase } from "#sitegen/drizzle";
88import * as questions from "#src/q+a/models/questions.ts";
89import * as incremental from "@clo/lib/incremental";
90import { Hono } from "hono";
91import * as fs from "node:fs";
92import * as sqlite from "node:sqlite";
93import * as stream from "node:stream";