spb/tools-spboucher-ai

1import { describe, it, expect, beforeAll, afterAll } from "vitest";2import { migrateTest, resetContent, TEST_URL } from "./setup";3import postgres from "postgres";4let sql: ReturnType<typeof postgres>; let imp: typeof import("@/lib/content/import"); let queue: typeof import("@/lib/jobs/queue"); let dbmod: typeof import("@/db");5beforeAll(async () => { await migrateTest(); await resetContent(); sql = postgres(TEST_URL, { max: 2, onnotice: () => {} }); imp = await import("@/lib/content/import"); queue = await import("@/lib/jobs/queue"); dbmod = await import("@/db"); }, 60_000);6afterAll(async () => { await sql.end(); await dbmod.closeDb(); });78describe("content import", () => {9  it("imports categories, a record, relations and publishes; is idempotent", async () => {10    const r1 = await imp.importAll({ root: "content", publish: true, only: ["duckdb", "python", "sqlite"] });11    expect(r1.categories).toBeGreaterThan(70); expect(r1.tools.created.sort()).toEqual(["duckdb", "python", "sqlite"]); expect(r1.tools.published.length).toBe(3);12    const r2 = await imp.importAll({ root: "content", publish: true, only: ["duckdb", "python", "sqlite"] });13    expect(r2.tools.unchanged.length).toBe(3); expect(r2.tools.created.length).toBe(0);14    const [rev] = await sql`SELECT count(*)::int AS n FROM tool_revisions`; expect(rev.n).toBe(3); // one revision per create, none for unchanged15    const [alt] = await sql`SELECT count(*)::int AS n FROM tool_alternatives ta JOIN tools t ON t.id=ta.tool_id WHERE t.slug='duckdb'`; expect(alt.n).toBeGreaterThanOrEqual(1); // duckdb ↔ sqlite16    const [pub] = await sql`SELECT count(*)::int AS n FROM public_tools`; expect(pub.n).toBe(3);17  }, 60_000);18  it("public view hides private and unpublished records", async () => {19    await sql`UPDATE tools SET visibility='private' WHERE slug='python'`;20    await sql`UPDATE tools SET editorial_status='reviewed' WHERE slug='sqlite'`;21    const [pub] = await sql`SELECT array_agg(slug) AS s FROM public_tools`; expect(pub.s).toEqual(["duckdb"]);22    await sql`UPDATE tools SET visibility='public', editorial_status='published' WHERE slug IN ('python','sqlite')`;23  });24  it("search vector and trigram index answer typo queries", async () => {25    const rows = await sql`SELECT slug FROM public_tools WHERE similarity(f_unaccent(lower(name)), 'postgress') > 0.3 OR search_vector @@ to_tsquery('english', 'analyt:*') ORDER BY slug`;26    expect(rows.map((r) => r.slug)).toContain("duckdb");27  });28});29describe("database boundary", () => {30  it("tools_reader can read public views but not base tables or private data", async () => {31    const [role] = await sql`SELECT rolname FROM pg_roles WHERE rolname='tools_reader'`; if (!role) return; // roles are cluster-level; skip if absent32    const url = new URL(TEST_URL); url.username = "tools_reader"; url.password = process.env.DATABASE_URL_PUBLIC?.match(/tools_reader:([^@]+)@/)?.[1] ?? ""; url.hostname = "127.0.0.1";33    if (!url.password) return;34    const r = postgres(url.toString(), { max: 1, onnotice: () => {} });35    expect((await r`SELECT count(*)::int AS n FROM public_tools`)[0].n).toBeGreaterThanOrEqual(0);36    await expect(r`SELECT count(*) FROM tools`).rejects.toThrow(/permission denied/);37    await expect(r`SELECT count(*) FROM owner_notes`).rejects.toThrow(/permission denied/);38    await expect(r`SELECT count(*) FROM users`).rejects.toThrow(/permission denied/);39    await expect(r`SELECT count(*) FROM audit_log`).rejects.toThrow(/permission denied/);40    await expect(r`UPDATE vendors SET name='x'`).rejects.toThrow(/permission denied/);41    await r.end();42  });43  it("audit_log and tool_revisions are append-only", async () => {44    await sql`INSERT INTO audit_log (actor, action) VALUES ('test', 'x')`;45    await expect(sql`UPDATE audit_log SET action='y' WHERE actor='test'`).rejects.toThrow(/append-only/);46    await expect(sql`DELETE FROM audit_log WHERE actor='test'`).rejects.toThrow(/append-only/);47    await expect(sql`DELETE FROM tool_revisions`).rejects.toThrow(/append-only/);48  });49  it("one primary category per tool is enforced", async () => {50    const [t] = await sql`SELECT id FROM tools WHERE slug='duckdb'`; const [c] = await sql`SELECT id FROM categories WHERE slug='notebooks'`;51    await expect(sql`INSERT INTO tool_categories (tool_id, category_id, is_primary) VALUES (${t.id}, ${c.id}, true)`).rejects.toThrow(/duplicate key|unique/);52  });53});54describe("job queue", () => {55  it("dedupes, claims with lock, retries with backoff and dead-letters", async () => {56    await sql`DELETE FROM jobs`;57    const a = await queue.enqueue("link_check", { toolId: "t1" }, { dedupeKey: "k1", maxAttempts: 2 }); const b = await queue.enqueue("link_check", { toolId: "t1" }, { dedupeKey: "k1" });58    expect(a).not.toBeNull(); expect(b === null || b === a).toBe(true);59    expect((await sql`SELECT count(*)::int AS n FROM jobs`)[0].n).toBe(1);60    const job = await queue.claim("w1"); expect(job?.id).toBe(a); expect(job?.payload).toEqual({ toolId: "t1" });61    expect(await queue.claim("w2")).toBeNull(); // locked62    await queue.fail(job!, "boom"); let [row] = await sql`SELECT status, run_at > now() AS later, attempts FROM jobs WHERE id=${a}`; expect(row.status).toBe("queued"); expect(row.later).toBe(true); expect(row.attempts).toBe(1);63    await sql`UPDATE jobs SET run_at = now() WHERE id=${a}`; const j2 = await queue.claim("w1"); await queue.fail(j2!, "boom again");64    [row] = await sql`SELECT status, last_error FROM jobs WHERE id=${a}`; expect(row.status).toBe("dead"); expect(row.last_error).toContain("boom again");65    await queue.retry(a!); [row] = await sql`SELECT status, attempts FROM jobs WHERE id=${a}`; expect(row.status).toBe("queued"); expect(row.attempts).toBe(0);66    const j3 = await queue.claim("w1"); await queue.succeed(j3!.id, { ok: 1 }); [row] = await sql`SELECT status, result FROM jobs WHERE id=${a}`; expect(row.status).toBe("succeeded"); expect(row.result).toEqual({ ok: 1 });67  });68  it("reaps stale running jobs", async () => {69    const id = await queue.enqueue("backup", {}, { dedupeKey: "stale" }); await queue.claim("w9"); await sql`UPDATE jobs SET locked_at = now() - interval '2 hours' WHERE id=${id}`;70    expect(await queue.reapStale(20)).toBe(1); expect((await sql`SELECT status FROM jobs WHERE id=${id}`)[0].status).toBe("queued");71  });72});73describe("auth primitives", () => {74  it("argon2id hash verifies and policy rejects weak passwords", async () => {75    const { hashPassword, verifyPassword, passwordPolicy } = await import("@/lib/auth/password");76    const h = await hashPassword("Correct-Horse-Battery-9"); expect(h.startsWith("$argon2id$")).toBe(true); expect(await verifyPassword(h, "Correct-Horse-Battery-9")).toBe(true); expect(await verifyPassword(h, "wrong")).toBe(false);77    expect(passwordPolicy("492592")).toBeTruthy(); expect(passwordPolicy("alllowercaseletters")).toBeTruthy(); expect(passwordPolicy("Correct-Horse-Battery-9")).toBeNull();78  });79  it("login limiter locks after 5 failures", async () => {80    const { loginAllowed, recordAttempt } = await import("@/lib/auth/attempts");81    for (let i = 0; i < 5; i++) await recordAttempt("9.9.9.9", "x@y.z", false);82    const g = await loginAllowed("9.9.9.9", "other@y.z"); expect(g.ok).toBe(false);83    expect((await loginAllowed("8.8.8.8", "fresh@y.z")).ok).toBe(true);84  });85});86