justintsus-experiment Public
// toSql (apps/web/src/search/sql.tsus) against SQLite with the D1 tables it reads (issues, pull_index,// pull_review_requests, agents): each term's rows, negation as the exact complement (NULL owner_id// included), @me without a viewer, agents scoped to the organization, the date tables, LIKE escaping,// the order, and the scope that no term widens. Every value is bound.import assert from "node:assert/strict";import { DatabaseSync } from "node:sqlite";import test from "node:test";import { parseQuery, toSql, type SqlCtx, type Target } from "../../../apps/web/src/search/api.ts";const DAY = 86_400_000;const T0 = Date.UTC(2026, 9, 1); // 2026-10-01const NOW = Date.UTC(2026, 9, 16, 12);const ALICE = "aaaaaaaa-0000-4000-8000-000000000001";const BOB = "bbbbbbbb-0000-4000-8000-000000000002";const BOT = "cccccccc-0000-4000-8000-000000000003"; // agent "docs-bot" in org o1, owned by Aliceconst OTHER_BOT = "dddddddd-0000-4000-8000-000000000004"; // agent "other-bot" in org o2function db(): DatabaseSync { const d = new DatabaseSync(":memory:"); d.exec(` CREATE TABLE issues (repo_id TEXT NOT NULL, number INTEGER NOT NULL, org_id TEXT NOT NULL, author_id TEXT NOT NULL, title TEXT NOT NULL, body TEXT NOT NULL DEFAULT '', state TEXT NOT NULL DEFAULT 'open', labels TEXT NOT NULL DEFAULT '[]', comments INTEGER NOT NULL DEFAULT 0, created_at INTEGER NOT NULL, updated_at INTEGER NOT NULL, closed_at INTEGER, closed_by TEXT, PRIMARY KEY (repo_id, number)); CREATE TABLE pull_index (repo_id TEXT NOT NULL, number INTEGER NOT NULL, org_id TEXT NOT NULL, author_id TEXT NOT NULL, owner_id TEXT, state TEXT NOT NULL, draft INTEGER NOT NULL DEFAULT 0, title TEXT NOT NULL, base_ref TEXT NOT NULL, head_ref TEXT NOT NULL, created_at INTEGER NOT NULL, updated_at INTEGER NOT NULL, merged_at INTEGER, closed_at INTEGER, PRIMARY KEY (repo_id, number)); CREATE TABLE pull_review_requests (repo_id TEXT NOT NULL, number INTEGER NOT NULL, user_id TEXT NOT NULL, requested_at INTEGER NOT NULL, PRIMARY KEY (repo_id, number, user_id)); CREATE TABLE agents (user_id TEXT PRIMARY KEY, org_id TEXT NOT NULL, slug TEXT NOT NULL, owner_user_id TEXT NOT NULL, UNIQUE (org_id, slug)); `); d.prepare("INSERT INTO agents VALUES (?, 'o1', 'docs-bot', ?)").run(BOT, ALICE); d.prepare("INSERT INTO agents VALUES (?, 'o2', 'other-bot', ?)").run(OTHER_BOT, BOB); const issue = d.prepare("INSERT INTO issues (repo_id, number, org_id, author_id, title, body, state, labels, comments, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"); // number, author, title, body, state, labels, comments, created, updated const rows: [number, string, string, string, string, string[], number, number, number][] = [ [1, ALICE, "Crash on start", "out of memory", "open", ["bug"], 3, T0, T0 + 5 * DAY], [2, BOB, "Docs typo", "", "closed", ["docs", "needs review"], 0, T0 + DAY, T0 + DAY], [3, BOT, "100% CPU_usage", "a\\b", "open", [], 7, T0 + 2 * DAY, NOW - 3_600_000], [4, BOB, "Unicode Éclair", "crash later", "open", ["bug", "docs"], 1, T0 - DAY, NOW - 10 * DAY], ]; for (const [n, a, title, body, state, labels, comments, c, u] of rows) issue.run("r1", n, "o1", a, title, body, state, JSON.stringify(labels), comments, c, u); // the same numbers in another repository and another organization: never in scope for (const [n, a, title, body, state, labels, comments, c, u] of rows) issue.run("r2", n, "o2", a, title, body, state, JSON.stringify(labels), comments, c, u); const pull = d.prepare("INSERT INTO pull_index (repo_id, number, org_id, author_id, owner_id, state, draft, title, base_ref, head_ref, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?, 'refs/heads/main', 'refs/heads/x', ?, ?)"); const pulls: [number, string, string | null, string, number, string, number, number][] = [ [1, ALICE, null, "open", 0, "Fix login", T0, T0], [2, BOT, ALICE, "open", 1, "Draft from the bot", T0 + DAY, T0 + 2 * DAY], [3, BOB, null, "merged", 0, "Merge me", T0 + 2 * DAY, T0 + 3 * DAY], [4, BOB, null, "closed", 1, "Closed draft", T0 + 3 * DAY, T0 + 4 * DAY], [5, OTHER_BOT, BOB, "open", 0, "Stranger bot", T0 + 4 * DAY, T0 + 4 * DAY], ]; for (const [n, a, o, s, dr, title, c, u] of pulls) { pull.run("r1", n, "o1", a, o, s, dr, title, c, u); pull.run("r2", n, "o2", a, o, s, dr, title, c, u); } const rr = d.prepare("INSERT INTO pull_review_requests VALUES (?, ?, ?, 0)"); rr.run("r1", 1, BOB); rr.run("r1", 3, ALICE); rr.run("r1", 4, BOT); rr.run("r2", 2, BOB); return d;}const D = db();function ctx(viewerId: string | null = ALICE): SqlCtx { return { repoId: "r1", orgId: "o1", viewerId, now: NOW };}function run(input: string, target: Target, c: SqlCtx = ctx()): number[] { const r = parseQuery(input, target); assert.ok(r.ok, `${input}: ${JSON.stringify(r)}`); const s = toSql(r.query, c); const table = target === "issues" ? "issues" : "pull_index"; const rows = D.prepare(`SELECT number FROM ${table} WHERE ${s.where} AND 1 = 1 ORDER BY ${s.orderBy} LIMIT ?`).all(...s.params, 100) as { number: number }[]; return rows.map((x) => x.number);}const sorted = (xs: number[]) => [...xs].sort((a, b) => a - b);const all = (t: Target) => (t === "issues" ? [1, 2, 3, 4] : [1, 2, 3, 4, 5]);// A negated term matches exactly the rows its positive form does not.function complement(term: string, target: Target, c: SqlCtx = ctx()) { const yes = run(term, target, c); const no = run(`-${term}`, target, c); assert.deepEqual(sorted([...yes, ...no]), all(target), term); assert.equal(yes.filter((n) => no.includes(n)).length, 0, term);}test("the empty query is every row in scope, newest first, and nothing else", () => { assert.deepEqual(run("", "issues"), [3, 2, 1, 4]); assert.deepEqual(run("", "pulls"), [5, 4, 3, 2, 1]); const s = toSql((parseQuery("", "issues") as { ok: true; query: never }).query, ctx()); assert.match(s.where, /^issues\.repo_id = \? AND issues\.org_id = \?$/); assert.deepEqual(s.params, ["r1", "o1"]); assert.equal(s.orderBy, "created_at DESC, number DESC");});test("states", () => { assert.deepEqual(sorted(run("is:open", "issues")), [1, 3, 4]); assert.deepEqual(run("is:closed", "issues"), [2]); assert.deepEqual(run("is:open is:closed", "issues"), []); assert.deepEqual(sorted(run("is:open", "pulls")), [1, 2, 5]); assert.deepEqual(sorted(run("is:closed", "pulls")), [3, 4]); assert.deepEqual(run("is:merged", "pulls"), [3]); assert.deepEqual(sorted(run("is:draft", "pulls")), [2, 4]); for (const t of ["is:open", "is:closed"]) complement(t, "issues"); for (const t of ["is:open", "is:closed", "is:merged", "is:draft"]) complement(t, "pulls");});test("labels", () => { assert.deepEqual(sorted(run("label:bug", "issues")), [1, 4]); assert.deepEqual(run('label:"Needs Review"', "issues"), [2]); assert.deepEqual(run("label:bug label:docs", "issues"), [4]); assert.deepEqual(run("no:label", "issues"), [3]); assert.deepEqual(run("label:nothing", "issues"), []); complement("label:bug", "issues");});test("authors and review requests", () => { assert.deepEqual(run("author:@me", "issues"), [1]); assert.deepEqual(sorted(run(`author:${BOB}`, "issues")), [2, 4]); assert.deepEqual(run("author:docs-bot", "issues"), [3]); // an agent of another organization, or no agent, matches nothing assert.deepEqual(run("author:other-bot", "issues"), []); assert.deepEqual(run("author:nobody", "issues"), []); // an agent's PR shows under its owner assert.deepEqual(sorted(run("author:@me", "pulls")), [1, 2]); assert.deepEqual(run("author:docs-bot", "pulls"), [2]); assert.deepEqual(run("author:other-bot", "pulls"), []); assert.deepEqual(sorted(run(`author:${BOB}`, "pulls")), [3, 4, 5]); // -author: keeps the PRs with no owner whose author is someone else assert.deepEqual(sorted(run("-author:@me", "pulls")), [3, 4, 5]); for (const u of ["@me", BOB, "docs-bot", "other-bot"]) { complement(`author:${u}`, "issues"); complement(`author:${u}`, "pulls"); complement(`review-requested:${u}`, "pulls"); } assert.deepEqual(run("review-requested:@me", "pulls"), [3]); assert.deepEqual(run(`review-requested:${BOB}`, "pulls"), [1]); assert.deepEqual(run("review-requested:docs-bot", "pulls"), [4]);});test("@me with no one signed in matches nothing, and its negation everything", () => { const anon = ctx(null); assert.deepEqual(run("author:@me", "issues", anon), []); assert.deepEqual(sorted(run("-author:@me", "issues", anon)), [1, 2, 3, 4]); assert.deepEqual(run("review-requested:@me", "pulls", anon), []); assert.deepEqual(sorted(run("-review-requested:@me", "pulls", anon)), [1, 2, 3, 4, 5]); assert.deepEqual(run("author:@me", "pulls", anon), []);});test("numbers and text", () => { assert.deepEqual(run("#3", "issues"), [3]); assert.deepEqual(run("#9", "issues"), []); assert.deepEqual(sorted(run("crash", "issues")), [1, 4]); // title or body, ASCII case-insensitive assert.deepEqual(run('"out of memory"', "issues"), [1]); assert.deepEqual(run("out memory", "issues"), [1]); assert.deepEqual(run('"memory out"', "issues"), []); assert.deepEqual(run("100%", "issues"), [3]); assert.deepEqual(run("0%", "issues"), [3]); assert.deepEqual(run("%", "issues"), [3]); assert.deepEqual(run("_", "issues"), [3]); assert.deepEqual(run("u_u", "issues"), [3]); assert.deepEqual(run("x_x", "issues"), []); assert.deepEqual(run("a\\b", "issues"), [3]); assert.deepEqual(run("éclair", "issues"), []); // LIKE folds ASCII only assert.deepEqual(run("Éclair", "issues"), [4]); assert.deepEqual(run("login", "pulls"), [1]); assert.deepEqual(run("memory", "pulls"), []); complement("crash", "issues"); complement("draft", "pulls");});test("dates follow the tables", () => { // created: 4 = T0-1d, 1 = T0, 2 = T0+1d, 3 = T0+2d assert.deepEqual(run("created:2026-10-01", "issues"), [1]); assert.deepEqual(sorted(run("created:>2026-10-01", "issues")), [2, 3]); assert.deepEqual(sorted(run("created:>=2026-10-01", "issues")), [1, 2, 3]); assert.deepEqual(run("created:<2026-10-01", "issues"), [4]); assert.deepEqual(sorted(run("created:<=2026-10-01", "issues")), [1, 4]); assert.deepEqual(sorted(run("created:2026-10-01..2026-10-02", "issues")), [1, 2]); assert.deepEqual(sorted(run("created:2026-10-02..*", "issues")), [2, 3]); assert.deepEqual(sorted(run("created:*..2026-10-01", "issues")), [1, 4]); // updated: 3 = now-1h, 4 = now-10d assert.deepEqual(run("updated:<2h", "issues"), [3]); assert.deepEqual(run("updated:<1h", "issues"), []); assert.deepEqual(run("updated:<=1h", "issues"), [3]); assert.deepEqual(run("updated:>=1h", "issues").includes(3), true); assert.deepEqual(run("updated:>1h", "issues").includes(3), false); assert.deepEqual(run("updated:<1w", "issues"), [3]); assert.deepEqual(sorted(run("updated:>1w", "issues")), [1, 2, 4]);});test("the date bounds as bound values", () => { const sql = (v: string) => { const r = parseQuery(`created:${v}`, "issues"); assert.ok(r.ok); return toSql(r.query, ctx()); }; const D1 = Date.UTC(2026, 9, 1); assert.deepEqual(sql("2026-10-01").params.slice(2), [D1, D1 + DAY]); assert.deepEqual(sql(">2026-10-01").params.slice(2), [D1 + DAY]); assert.match(sql(">2026-10-01").where, /created_at >= \?\)$/); assert.match(sql("<=2026-10-01").where, /created_at < \?\)$/); assert.deepEqual(sql("<=2026-10-01").params.slice(2), [D1 + DAY]); assert.deepEqual(sql("<7d").params.slice(2), [NOW - 7 * DAY]); assert.match(sql("<7d").where, /created_at > \?\)$/); assert.match(sql(">=7d").where, /created_at <= \?\)$/); assert.deepEqual(sql(">=3w").params.slice(2), [NOW - 21 * DAY]);});test("sort: picks the order, with the number as tie-break in the same direction", () => { assert.deepEqual(run("sort:created-asc", "issues"), [4, 1, 2, 3]); assert.deepEqual(run("sort:updated-desc", "issues"), [3, 4, 1, 2]); assert.deepEqual(run("sort:comments-desc", "issues"), [3, 1, 4, 2]); assert.deepEqual(run("sort:comments-asc", "issues"), [2, 4, 1, 3]); assert.deepEqual(run("sort:updated-asc", "pulls"), [1, 2, 3, 4, 5]); const r = parseQuery("sort:updated-asc", "pulls"); assert.ok(r.ok); assert.equal(toSql(r.query, ctx()).orderBy, "updated_at ASC, number ASC");});test("nothing from the input or the context is written into the SQL text", () => { const evil = "x' OR 1=1 --"; const inputs = [`"${evil}"`, `label:"${evil.toLowerCase().replace(/[^a-z0-9 ._-]/g, "")}"`, "author:@me", "author:docs-bot", `author:${BOB}`, "review-requested:docs-bot", "#42", "created:2026-10-01", "updated:<7d", "is:open"]; const c = { repoId: "repo'); DROP TABLE issues; --", orgId: "org' OR '1'='1", viewerId: "viewer'--", now: NOW }; for (const t of ["issues", "pulls"] as const) { for (const x of inputs) { const r = parseQuery(x, t); if (!r.ok) continue; const s = toSql(r.query, c); const literals = s.where.match(/'[^']*'/g) ?? []; for (const l of literals) assert.ok(["'open'", "'closed'", "'merged'", "'\\'"].includes(l), `${x}: ${l}`); for (const v of [c.repoId, c.orgId, c.viewerId, BOB, "docs-bot", "42", String(NOW)]) assert.ok(!s.where.includes(v), `${x}: ${v}`); assert.equal((s.where.match(/\?/g) ?? []).length, s.params.length, x); assert.match(s.where, /^(issues|pull_index)\.repo_id = \? AND (issues|pull_index)\.org_id = \?( AND \(.*\))?$/); assert.deepEqual(s.params.slice(0, 2), [c.repoId, c.orgId]); // it runs, and finds nothing outside the scope D.prepare(`SELECT number FROM ${t === "issues" ? "issues" : "pull_index"} WHERE ${s.where} AND 1 = 1 ORDER BY ${s.orderBy}`).all(...s.params); } }});test("no term widens the scope", () => { const wide = ['"" OR 1', "-is:open", "-author:@me", "-label:bug", "-crash", "-review-requested:@me", "created:*..9999-12-31"]; for (const t of ["issues", "pulls"] as const) { for (const x of wide) { const r = parseQuery(x, t); if (!r.ok) continue; const s = toSql(r.query, { ...ctx(), repoId: "r2", orgId: "o1" }); const table = t === "issues" ? "issues" : "pull_index"; assert.deepEqual(D.prepare(`SELECT number FROM ${table} WHERE ${s.where} AND 1 = 1`).all(...s.params), [], `${t} ${x}`); } }});