import { describe, expect, it } from "vitest"; import { migrations } from "../migrations/index.ts"; import { appliedMigrations, migrate, splitStatements } from "../src/migrate.ts"; import { BetterSqlite3Driver } from "../src/sql/better-sqlite3.ts"; import { D1Driver } from "../src/sql/d1.ts"; import { createMiniflareD1 } from "../src/testing.ts"; describe("splitStatements", () => { it("splits on end-of-line semicolons and drops comments", () => { expect(splitStatements("-- c\nCREATE TABLE a (x);\n\nCREATE TABLE b (y);")).toEqual([ "CREATE TABLE a (x)", "CREATE TABLE b (y)", ]); }); }); describe("migrate", () => { it("applies the Cloudhal schema on better-sqlite3, idempotently", async () => { const driver = await BetterSqlite3Driver.open(":memory:"); expect((await migrate(driver, migrations)).applied).toEqual([ "0001_init", "0002_auth", "0003_agent", "0004_audit_actor_name", "0005_auth_rate_limit", "0006_approvals", "0007_audit_mode", "0008_policy_detail", "0009_audit_elevation", "0010_approval_result", "0011_op_guards", "0012_roles", "0013_media", "0014_analytics", "0015_analytics_privacy_signal", "0016_comments", "0017_schedules", "0018_query_indexes", "0019_body_proposals", "0020_ai", "0021_search", "0022_search_kinds", "0023_search_usage", "0024_search_quota", "0025_translation_groups", "0026_search_cjk", "0027_multi_site", "0028_site_members", "0029_entry_list_values", "0030_rate_limits", "0031_oauth", "0032_oauth_client_kind_index", "0033_entry_refs", "0034_entry_refs_state", ]); expect((await migrate(driver, migrations)).applied).toEqual([]); expect(await appliedMigrations(driver)).toEqual([ "0001_init", "0002_auth", "0003_agent", "0004_audit_actor_name", "0005_auth_rate_limit", "0006_approvals", "0007_audit_mode", "0008_policy_detail", "0009_audit_elevation", "0010_approval_result", "0011_op_guards", "0012_roles", "0013_media", "0014_analytics", "0015_analytics_privacy_signal", "0016_comments", "0017_schedules", "0018_query_indexes", "0019_body_proposals", "0020_ai", "0021_search", "0022_search_kinds", "0023_search_usage", "0024_search_quota", "0025_translation_groups", "0026_search_cjk", "0027_multi_site", "0028_site_members", "0029_entry_list_values", "0030_rate_limits", "0031_oauth", "0032_oauth_client_kind_index", "0033_entry_refs", "0034_entry_refs_state", ]); const tables = await driver.all<{ name: string }>( "SELECT name FROM sqlite_master WHERE type = 'table' AND name NOT LIKE 'sqlite_%' ORDER BY name", ); expect(tables.map((t) => t.name)).toEqual([ "_migrations", "account", "agent_grants", "agent_owner", "ai_settings", "ai_usage", "analytics_daily", "analytics_events", "analytics_query_budget", "analytics_rollup_runs", "analytics_salts", "analytics_settings", "apikey", "approvals", "asset_refs", "audit", "automations", "body_proposals", "cache_purge_log", "comment_bans", "comment_events", "comment_purge_queue", "comment_reports", "comment_salts", "comment_secrets", "comment_settings", "comment_threads", "commenter_emails", "comments", "entry_list_state", "entry_list_values", "entry_refs", "entry_refs_state", "invitation", "jwks", "media_blobs", "media_uploads", "member", "nodes", "oauthAccessToken", "oauthClient", "oauthClientAssertion", "oauthClientResource", "oauthConsent", "oauthRefreshToken", "oauthResource", "op_guards", "organization", "password_reset", "projects", "rateLimit", "rate_limits", "release_items", "releases", "schedule_runs", "schedule_settings", "scheduler_heartbeat", "search_index", "search_index_cjk", "search_index_cjk_config", "search_index_cjk_content", "search_index_cjk_data", "search_index_cjk_docsize", "search_index_cjk_idx", "search_index_config", "search_index_content", "search_index_data", "search_index_docsize", "search_index_idx", "search_links", "search_quota", "search_state", "session", "site_hosts", "site_members", "site_workers", "snapshot_blobs", "snapshots", "system_jobs", "user", "verification", ]); await driver.close(); }); it("applies the Cloudhal schema on D1 (Miniflare)", async () => { const h = await createMiniflareD1(); const driver = new D1Driver(h.db); expect((await migrate(driver, migrations)).applied).toEqual([ "0001_init", "0002_auth", "0003_agent", "0004_audit_actor_name", "0005_auth_rate_limit", "0006_approvals", "0007_audit_mode", "0008_policy_detail", "0009_audit_elevation", "0010_approval_result", "0011_op_guards", "0012_roles", "0013_media", "0014_analytics", "0015_analytics_privacy_signal", "0016_comments", "0017_schedules", "0018_query_indexes", "0019_body_proposals", "0020_ai", "0021_search", "0022_search_kinds", "0023_search_usage", "0024_search_quota", "0025_translation_groups", "0026_search_cjk", "0027_multi_site", "0028_site_members", "0029_entry_list_values", "0030_rate_limits", "0031_oauth", "0032_oauth_client_kind_index", "0033_entry_refs", "0034_entry_refs_state", ]); expect(await appliedMigrations(driver)).toEqual([ "0001_init", "0002_auth", "0003_agent", "0004_audit_actor_name", "0005_auth_rate_limit", "0006_approvals", "0007_audit_mode", "0008_policy_detail", "0009_audit_elevation", "0010_approval_result", "0011_op_guards", "0012_roles", "0013_media", "0014_analytics", "0015_analytics_privacy_signal", "0016_comments", "0017_schedules", "0018_query_indexes", "0019_body_proposals", "0020_ai", "0021_search", "0022_search_kinds", "0023_search_usage", "0024_search_quota", "0025_translation_groups", "0026_search_cjk", "0027_multi_site", "0028_site_members", "0029_entry_list_values", "0030_rate_limits", "0031_oauth", "0032_oauth_client_kind_index", "0033_entry_refs", "0034_entry_refs_state", ]); await h.dispose(); }); it("a failing migration leaves earlier ones applied and itself unapplied", async () => { const driver = await BetterSqlite3Driver.open(":memory:"); const bad = { name: "0099_bad", sql: "CREATE TABLE ok (x);\nCREATE TABLE ok (x);" }; await expect(migrate(driver, [...migrations, bad])).rejects.toThrow(/0099_bad/); expect(await appliedMigrations(driver)).toEqual([ "0001_init", "0002_auth", "0003_agent", "0004_audit_actor_name", "0005_auth_rate_limit", "0006_approvals", "0007_audit_mode", "0008_policy_detail", "0009_audit_elevation", "0010_approval_result", "0011_op_guards", "0012_roles", "0013_media", "0014_analytics", "0015_analytics_privacy_signal", "0016_comments", "0017_schedules", "0018_query_indexes", "0019_body_proposals", "0020_ai", "0021_search", "0022_search_kinds", "0023_search_usage", "0024_search_quota", "0025_translation_groups", "0026_search_cjk", "0027_multi_site", "0028_site_members", "0029_entry_list_values", "0030_rate_limits", "0031_oauth", "0032_oauth_client_kind_index", "0033_entry_refs", "0034_entry_refs_state", ]); const ok = await driver.get("SELECT name FROM sqlite_master WHERE name = 'ok'"); expect(ok).toBeUndefined(); await driver.close(); }); }); describe("0012_roles: existing users become members (RFC-0005 §11)", () => { const before = migrations.filter((m) => m.name < "0012_roles"); const roles = migrations.filter((m) => m.name === "0012_roles"); const user = (id: string, createdAt: string) => ({ sql: `INSERT INTO "user" ("id", "name", "email", "emailVerified", "createdAt", "updatedAt") VALUES (?, ?, ?, 0, ?, ?)`, params: [id, id, `${id}@example.com`, createdAt, createdAt], }); const site = { sql: "INSERT INTO nodes (id, site_id, kind, key, rev, hash, body, created_at, updated_at) VALUES ('site_mig000001', 'site_mig000001', 'site', 'site', 0, 'h', ?, 't', 't')", params: [JSON.stringify({ id: "site_mig000001", name: "The Daily" })], }; for (const backend of ["better-sqlite3", "D1 (Miniflare)"] as const) it(`the earliest user is the Owner, others Administrators; one Owner is enforced (${backend})`, async () => { const h = backend === "D1 (Miniflare)" ? await createMiniflareD1() : undefined; const driver = h ? new D1Driver(h.db) : await BetterSqlite3Driver.open(":memory:"); await migrate(driver, before); await driver.batch([ site, user("userBBBBBB", "2026-01-02T00:00:00.000Z"), user("userAAAAAA", "2026-01-01T00:00:00.000Z"), user("userCCCCCC", "2026-01-02T00:00:00.000Z"), { sql: `INSERT INTO "apikey" ("id", "configId", "referenceId", "key", "createdAt", "updatedAt") VALUES ('KeyIdAlnum0001', 'default', 'userBBBBBB', 'hashed', 't', 't')`, }, ]); await migrate(driver, roles); const org = await driver.get<{ name: string; slug: string }>( `SELECT name, slug FROM "organization" WHERE id = 'deployment'`, ); expect(org).toEqual({ name: "The Daily", slug: "deployment" }); const members = await driver.all<{ userId: string; role: string }>( `SELECT "userId", "role" FROM "member" WHERE "organizationId" = 'deployment' ORDER BY "userId"`, ); expect(members).toEqual([ { userId: "userAAAAAA", role: "owner" }, { userId: "userBBBBBB", role: "administrator" }, { userId: "userCCCCCC", role: "administrator" }, ]); expect( await driver.get( `SELECT agent_id, user_id FROM agent_owner WHERE key_id = 'KeyIdAlnum0001'`, ), ).toEqual({ agent_id: "agent_KeyIdAlnum0001", user_id: "userBBBBBB" }); // a second Owner (I2) or a second membership is refused by the database itself await expect( driver.run(`UPDATE "member" SET "role" = 'owner' WHERE "userId" = 'userBBBBBB'`), ).rejects.toThrow(); await expect( driver.run( `INSERT INTO "member" ("id", "organizationId", "userId", "role", "createdAt") VALUES ('x', 'deployment', 'userCCCCCC', 'editor', 't')`, ), ).rejects.toThrow(); await (h ? h.dispose() : driver.close()); }); }); describe("0031_oauth: the authorization server's tables (RFC-0044 §7)", () => { const before = migrations.filter((m) => m.name < "0031_oauth"); const oauth = migrations.filter((m) => m.name === "0031_oauth"); for (const backend of ["better-sqlite3", "D1 (Miniflare)"] as const) it(`applies on a database at 0030, and agent_grants is one row per member and client (${backend})`, async () => { const h = backend === "D1 (Miniflare)" ? await createMiniflareD1() : undefined; const driver = h ? new D1Driver(h.db) : await BetterSqlite3Driver.open(":memory:"); await migrate(driver, before); expect((await migrate(driver, oauth)).applied).toEqual(["0031_oauth"]); const tables = await driver.all<{ name: string }>( "SELECT name FROM sqlite_master WHERE type = 'table' AND (name LIKE 'oauth%' OR name IN ('jwks', 'agent_grants')) ORDER BY name", ); expect(tables.map((t) => t.name)).toEqual([ "agent_grants", "jwks", "oauthAccessToken", "oauthClient", "oauthClientAssertion", "oauthClientResource", "oauthConsent", "oauthRefreshToken", "oauthResource", ]); const grant = (id: string, user: string, client: string) => ({ sql: "INSERT INTO agent_grants (id, user_id, client_id, client_name, scopes, mode, created_at) VALUES (?, ?, ?, 'Claude', '[]', 'edits', 't')", params: [id, user, client], }); await driver.batch([ grant("g1", "u1", "c1"), grant("g2", "u1", "c2"), grant("g3", "u2", "c1"), ]); await expect(driver.batch([grant("g4", "u1", "c1")])).rejects.toThrow(); const index = await driver.get<{ name: string }>( "SELECT name FROM sqlite_master WHERE type = 'index' AND name = 'agent_grants_user'", ); expect(index?.name).toBe("agent_grants_user"); const family = await driver.get<{ sql: string }>( "SELECT sql FROM sqlite_master WHERE type = 'index' AND name = 'oauthRefreshToken_family_idx'", ); expect(family?.sql).toContain('("authorizationCodeId", "createdAt")'); await (h ? h.dispose() : driver.close()); }); }); describe("0032_oauth_client_kind_index (RFC-0044 §3, review of #178)", () => { it("indexes document clients by kind and validation time, so the cap count reads the index", async () => { const driver = await BetterSqlite3Driver.open(":memory:"); await migrate(driver, migrations); const plan = await driver.all<{ detail: string }>( `EXPLAIN QUERY PLAN SELECT COUNT(*) FROM "oauthClient" WHERE "clientDiscoveryId" = 'cimd'`, ); expect(plan.map((p) => p.detail).join(" ")).toContain( "oauthClient_clientDiscoveryId_updatedAt_idx", ); await driver.close(); }); });