Skip to content
Sign in

flagon-io/g1t

public
0
113 linesCodeBlameRaw
1// node --test "scripts/deploy/*.test.mjs" (npm run test:deploy)
2//
3// The queries that run on a timer or on every page load, planned by SQLite
4// against each service's migrations as they apply in order: each must be a
5// search of an index, never a scan of the table. D1 is SQLite, so its
6// planner chooses the same way.
7import assert from "node:assert/strict";
8import { readdirSync, readFileSync } from "node:fs";
9import { join } from "node:path";
10import { DatabaseSync } from "node:sqlite";
11import { test } from "node:test";
12
13import { ROOT } from "./stack.mjs";
14
15/** A database with every one of `service`'s migrations applied, in order. */
16function migrated(service) {
17 const db = new DatabaseSync(":memory:");
18 const dir = join(ROOT, "services", service, "migrations");
19 for (const file of readdirSync(dir).filter((name) => name.endsWith(".sql")).sort()) {
20 db.exec(readFileSync(join(dir, file), "utf8"));
21 }
22 return db;
23}
24
25/** How SQLite would run `sql`, one line per step. */
26function plan(db, sql, ...params) {
27 return db
28 .prepare(`EXPLAIN QUERY PLAN ${sql}`)
29 .all(...params)
30 .map((row) => row.detail);
31}
32
33function assertSearches(steps, index) {
34 assert.ok(
35 steps.some((step) => step.includes(`INDEX ${index}`)),
36 `expected a search of ${index}, planned:\n ${steps.join("\n ")}`,
37 );
38}
39
40test("webhooks: the hourly purge deletes old deliveries by time", () => {
41 const steps = plan(
42 migrated("webhooks"),
43 "DELETE FROM deliveries WHERE rowid IN (SELECT rowid FROM deliveries WHERE created_at < ? ORDER BY created_at LIMIT ?)",
44 "2026-09-24T00:00:00.000Z",
45 1000,
46 );
47 assertSearches(steps, "deliveries_by_time");
48});
49
50test("billing: this month's users are read from an index, not the ledger", () => {
51 const steps = plan(
52 migrated("billing"),
53 "SELECT DISTINCT workspace FROM ledger WHERE kind = 'usage' AND created_at >= ?",
54 "2026-10-01",
55 );
56 assertSearches(steps, "ledger_usage_by_time");
57 assert.ok(steps.some((step) => step.includes("COVERING INDEX")), steps.join("\n"));
58});
59
60test("events: a repository's events of one type, newest first, page by page", () => {
61 const db = migrated("events");
62 const first = plan(db, "SELECT * FROM events WHERE repo_id = ? AND type IN (?) ORDER BY id DESC LIMIT ?", "rep_1", "git.push", 50);
63 assertSearches(first, "events_repo_type");
64 const next = plan(db, "SELECT * FROM events WHERE repo_id = ? AND type IN (?) AND id < ? ORDER BY id DESC LIMIT ?", "rep_1", "git.push", "evt_9", 200);
65 assertSearches(next, "events_repo_type");
66 // Read in order: no sort of every match.
67 assert.ok(!next.some((step) => step.includes("TEMP B-TREE")), next.join("\n"));
68});
69
70test("actions: a self-hosted runner's poll finds queued jobs by namespace, whatever its case", () => {
71 const steps = plan(
72 migrated("actions"),
73 `SELECT jobs.*, runs.repo AS run_repo FROM jobs JOIN runs ON runs.id = jobs.run_id
74 WHERE jobs.status = 'queued' AND jobs.labels IS NOT NULL AND lower(jobs.namespace) = ?
75 ORDER BY jobs.rowid LIMIT 50`,
76 "acme",
77 );
78 assertSearches(steps, "jobs_self_hosted_lower");
79});
80
81test("chat: a channel's messages and a thread's replies, newest first, page by page", () => {
82 const db = migrated("chat");
83 // The first page passes `~` as the cursor, so both read as a range.
84 const top = plan(
85 db,
86 `SELECT * FROM messages WHERE channel_id = ?1 AND thread_root IS NULL AND id < ?2
87 AND (deleted_at IS NULL OR reply_count > 0) ORDER BY id DESC LIMIT ?3`,
88 "chn_1",
89 "~",
90 51,
91 );
92 assertSearches(top, "messages_by_channel");
93 assert.ok(!top.some((step) => step.includes("TEMP B-TREE")), top.join("\n"));
94 const thread = plan(db, "SELECT * FROM messages WHERE thread_root = ?1 AND channel_id = ?2 AND id < ?3 ORDER BY id DESC LIMIT ?4", "msg_1", "chn_1", "~", 51);
95 assertSearches(thread, "messages_by_thread");
96});
97
98test("chat: the sidebar's unread messages are read from each channel's index, after the last read", () => {
99 const steps = plan(
100 migrated("chat"),
101 `SELECT msg.channel_id, msg.id, msg.author, msg.mentions
102 FROM channel_members m
103 JOIN channels c ON c.id = m.channel_id
104 JOIN messages msg ON msg.channel_id = m.channel_id AND msg.id > COALESCE(m.last_read_id, '')
105 WHERE m.principal = ?1 AND c.workspace_id = ?2 AND c.archived_at IS NULL
106 AND msg.deleted_at IS NULL AND msg.author != ?1
107 LIMIT 5000`,
108 "user:usr_1",
109 "wsp_1",
110 );
111 assertSearches(steps, "channel_members_by_principal");
112 assert.ok(steps.some((step) => step.includes("messages_by_channel (channel_id=? AND id>?)")), steps.join("\n"));
113});