| 1 | import { describe, expect, it } from "vitest"; |
| 2 | // @ts-expect-error Node types are intentionally not part of the Worker build. |
| 3 | import { readFileSync } from "node:fs"; |
| 4 | import schema from "../schema.sql?raw"; |
| 5 | import migration from "../migrate-dashboard-indexes.sql?raw"; |
| 6 | import registryMigration from "../registry-migrate-performance-indexes.sql?raw"; |
| 7 | import registrySchema from "../registry-schema.sql?raw"; |
| 8 | // @ts-expect-error Node types are intentionally not part of the Worker build. |
| 9 | import { DatabaseSync } from "node:sqlite"; |
| 10 | |
| 11 | const workflow = readFileSync("../../.github/workflows/deploy-crash-worker.yml", "utf8"); |
| 12 | |
| 13 | describe("crash and registry performance migrations", () => { |
| 14 | it("keeps crash indexes in fresh and incremental schemas", () => { |
| 15 | for (const sql of [schema, migration]) { |
| 16 | expect(sql).toMatch(/CREATE INDEX IF NOT EXISTS report_daily_fingerprint_date\s+ON report_daily \(fingerprint, date\)/); |
| 17 | expect(sql).toMatch(/CREATE INDEX IF NOT EXISTS firebase_crash_outbox_fingerprint\s+ON firebase_crash_outbox \(fingerprint\)/); |
| 18 | expect(sql).toMatch(/CREATE INDEX IF NOT EXISTS reports_fingerprint_id\s+ON reports \(fingerprint, id DESC\)/); |
| 19 | } |
| 20 | }); |
| 21 | |
| 22 | it("keeps registry migrations additive and wired before deployment", () => { |
| 23 | for (const sql of [registrySchema, registryMigration]) { |
| 24 | expect(sql).toMatch(/CREATE INDEX IF NOT EXISTS packages_active_created/); |
| 25 | expect(sql).toMatch(/CREATE INDEX IF NOT EXISTS packages_active_kind_created/); |
| 26 | expect(sql).toMatch(/CREATE INDEX IF NOT EXISTS packages_active_installs/); |
| 27 | expect(sql).toMatch(/CREATE TABLE IF NOT EXISTS package_install_daily/); |
| 28 | expect(sql).toMatch(/CREATE TABLE IF NOT EXISTS registry_migration_meta/); |
| 29 | } |
| 30 | expect(registryMigration).not.toMatch(/\b(?:DROP|ALTER)\b/); |
| 31 | expect(registryMigration).toMatch(/INSERT INTO package_install_daily/); |
| 32 | expect(registryMigration).toMatch(/ON CONFLICT \(date, package_id\) DO UPDATE/); |
| 33 | expect(workflow).toContain("npm run migrate:registry-indexes"); |
| 34 | expect(workflow).toContain("npm run migrate:dashboard-indexes"); |
| 35 | }); |
| 36 | |
| 37 | it("makes per-group cleanup probes use the new fingerprint indexes", () => { |
| 38 | const db = new DatabaseSync(":memory:"); |
| 39 | try { |
| 40 | db.exec(schema); |
| 41 | db.exec(migration); |
| 42 | const dailyPlan = db.prepare( |
| 43 | "EXPLAIN QUERY PLAN DELETE FROM report_daily WHERE fingerprint = 'x'", |
| 44 | ).all().map((row: Record<string, unknown>) => String(row.detail)).join(" "); |
| 45 | const outboxPlan = db.prepare( |
| 46 | "EXPLAIN QUERY PLAN SELECT event_id FROM firebase_crash_outbox WHERE fingerprint = 'x' LIMIT 1", |
| 47 | ).all().map((row: Record<string, unknown>) => String(row.detail)).join(" "); |
| 48 | const reportPlan = db.prepare( |
| 49 | "EXPLAIN QUERY PLAN SELECT id FROM reports INDEXED BY reports_fingerprint_id WHERE fingerprint = 'x' ORDER BY id DESC LIMIT 5", |
| 50 | ).all().map((row: Record<string, unknown>) => String(row.detail)).join(" "); |
| 51 | expect(dailyPlan).toContain("USING INDEX report_daily_fingerprint_date"); |
| 52 | expect(outboxPlan).toContain("USING INDEX firebase_crash_outbox_fingerprint"); |
| 53 | expect(reportPlan).toContain("USING COVERING INDEX reports_fingerprint_id"); |
| 54 | } finally { |
| 55 | db.close(); |
| 56 | } |
| 57 | }); |
| 58 | |
| 59 | it("makes active package ordering use partial indexes", () => { |
| 60 | const db = new DatabaseSync(":memory:"); |
| 61 | try { |
| 62 | db.exec(registrySchema); |
| 63 | db.exec(registryMigration); |
| 64 | const installsPlan = db.prepare( |
| 65 | "EXPLAIN QUERY PLAN SELECT id FROM packages WHERE status = 'active' ORDER BY install_count DESC, created_at DESC LIMIT 24", |
| 66 | ).all().map((row: Record<string, unknown>) => String(row.detail)).join(" "); |
| 67 | const kindPlan = db.prepare( |
| 68 | "EXPLAIN QUERY PLAN SELECT id FROM packages WHERE status = 'active' AND kind = 'skill' ORDER BY created_at DESC LIMIT 24", |
| 69 | ).all().map((row: Record<string, unknown>) => String(row.detail)).join(" "); |
| 70 | expect(installsPlan).toContain("packages_active_installs"); |
| 71 | expect(installsPlan).not.toContain("USE TEMP B-TREE FOR ORDER BY"); |
| 72 | expect(kindPlan).toContain("packages_active_kind_created"); |
| 73 | expect(kindPlan).not.toContain("USE TEMP B-TREE FOR ORDER BY"); |
| 74 | } finally { |
| 75 | db.close(); |
| 76 | } |
| 77 | }); |
| 78 | |
| 79 | it("backfills install days exactly once and remains safe to rerun", () => { |
| 80 | const db = new DatabaseSync(":memory:"); |
| 81 | try { |
| 82 | db.exec(registrySchema); |
| 83 | db.exec("INSERT INTO events (type, package_id, created_at) VALUES ('install', 7, '2026-08-20T01:00:00.000Z'), ('install', 7, '2026-08-20T02:00:00.000Z')"); |
| 84 | db.exec(registryMigration); |
| 85 | db.exec(registryMigration); |
| 86 | expect(db.prepare("SELECT date, package_id, count FROM package_install_daily").all()).toEqual([ |
| 87 | { date: "2026-08-20", package_id: 7, count: 2 }, |
| 88 | ]); |
| 89 | } finally { |
| 90 | db.close(); |
| 91 | } |
| 92 | }); |
| 93 | }); |
| 94 |