Open source alternative to Semrush and Ahrefs
TypeScript backlink-analysisgoogle-search-console-mcpkeyword-researchmcpseoseo-agentseo-auditseo-automationseo-skillsseo-toolssite-audit
/**
* The scratchpad's finalize queries run against a real SQLite engine here —
* node:sqlite ships the same JSON1 functions the Durable Object's SQLite
* exposes, so the exact query strings the DO executes can be exercised
* without a Workers runtime.
*/
import { DatabaseSync } from "node:sqlite";
import { beforeEach, describe, expect, it } from "vitest";
import {
BROKEN_LINKS_SQL,
FRONTIER_DISCOVER_SQL,
ORPHAN_PAGES_SQL,
SCRATCHPAD_SCHEMA_SQL,
} from "@/server/features/audit/scratchpad-sql";
const START = "https://site.test/";
let db: DatabaseSync;
function addPage(
pageId: string,
url: string,
opts: {
status?: number;
fetchClass?: string;
redirectUrl?: string | null;
} = {},
) {
db.prepare(
`INSERT INTO page_mirror (page_id, url, status_code, fetch_class, redirect_url)
VALUES (?, ?, ?, ?, ?)`,
).run(
pageId,
url,
opts.status ?? 200,
opts.fetchClass ?? "ok",
opts.redirectUrl ?? null,
);
}
function addLinks(pageId: string, url: string, targets: string[]) {
db.prepare(
`INSERT OR REPLACE INTO page_links (page_id, url, targets_json) VALUES (?, ?, ?)`,
).run(pageId, url, JSON.stringify(targets));
}
function addLegacyLinks(pageId: string, url: string, targets: string[]) {
for (const target of targets) {
db.prepare(
`INSERT OR IGNORE INTO links (source_page_id, source_url, target_url) VALUES (?, ?, ?)`,
).run(pageId, url, target);
}
}
function brokenLinks(limit = 100) {
return db.prepare(BROKEN_LINKS_SQL).all(limit);
}
function orphanPages() {
return db.prepare(ORPHAN_PAGES_SQL).all(START);
}
beforeEach(() => {
db = new DatabaseSync(":memory:");
db.exec(SCRATCHPAD_SCHEMA_SQL);
});
describe("broken internal links", () => {
it("reports a crawled-and-failed target once, ignoring fine and uncrawled targets", () => {
addPage("p1", START);
addPage("p2", "https://site.test/gone", { status: 404 });
addPage("p3", "https://site.test/ok");
addLinks("p1", START, [
"https://site.test/gone",
"https://site.test/ok",
"https://site.test/never-crawled",
]);
expect(brokenLinks()).toEqual([
{
source_page_id: "p1",
source_url: START,
target_url: "https://site.test/gone",
target_status: 404,
},
]);
});
it("ignores WAF-blocked 4xx targets", () => {
addPage("p1", START);
addPage("p2", "https://site.test/waf", {
status: 403,
fetchClass: "blocked",
});
addLinks("p1", START, ["https://site.test/waf"]);
expect(brokenLinks()).toEqual([]);
});
it("includes legacy per-edge rows from a crawl that predates the JSON layout", () => {
addPage("p1", START);
addPage("p2", "https://site.test/two");
addPage("p3", "https://site.test/gone", { status: 500 });
addLegacyLinks("p1", START, ["https://site.test/gone"]);
addLinks("p2", "https://site.test/two", ["https://site.test/gone"]);
expect(brokenLinks().map((row) => row.source_page_id)).toEqual([
"p1",
"p2",
]);
});
it("respects the row limit and orders deterministically", () => {
addPage("p1", START);
for (let i = 0; i < 5; i += 1) {
addPage(`b${i}`, `https://site.test/gone-${i}`, { status: 404 });
}
addLinks(
"p1",
START,
Array.from({ length: 5 }, (_, i) => `https://site.test/gone-${i}`),
);
expect(brokenLinks(2).map((row) => row.target_url)).toEqual([
"https://site.test/gone-0",
"https://site.test/gone-1",
]);
});
});
describe("orphan pages", () => {
it("flags a 2xx page nothing links to, not the start URL or a linked page", () => {
addPage("p1", START);
addPage("p2", "https://site.test/lonely");
addPage("p3", "https://site.test/linked");
addLinks("p1", START, ["https://site.test/linked"]);
expect(orphanPages()).toEqual([
{ page_id: "p2", url: "https://site.test/lonely" },
]);
});
it("does not rescue a page that only links to itself", () => {
addPage("p1", START);
addPage("p2", "https://site.test/lonely");
addLinks("p2", "https://site.test/lonely", ["https://site.test/lonely"]);
expect(orphanPages()).toEqual([
{ page_id: "p2", url: "https://site.test/lonely" },
]);
});
it("does not flag a page something redirects to", () => {
addPage("p1", START);
addPage("p2", "https://site.test/target");
addPage("p3", "https://site.test/old", {
status: 301,
redirectUrl: "https://site.test/target",
});
expect(orphanPages()).toEqual([]);
});
it("ignores non-2xx and blocked pages", () => {
addPage("p1", START);
addPage("p2", "https://site.test/gone", { status: 404 });
addPage("p3", "https://site.test/waf", {
status: 200,
fetchClass: "blocked",
});
expect(orphanPages()).toEqual([]);
});
it("counts legacy per-edge rows as inbound links", () => {
addPage("p1", START);
addPage("p2", "https://site.test/linked");
addPage("p3", "https://site.test/self");
addLegacyLinks("p1", START, ["https://site.test/linked"]);
addLegacyLinks("p3", "https://site.test/self", ["https://site.test/self"]);
expect(orphanPages()).toEqual([
{ page_id: "p3", url: "https://site.test/self" },
]);
});
});
describe("query plans", () => {
/**
* Both checks must stay linear in link count. The failure mode to guard is
* page_mirror (or the inbound set) being rescanned inside a per-row loop,
* which is what an unhinted join order or a correlated NOT EXISTS produces.
*/
function plan(sql: string, param: string | number): string[] {
return db
.prepare(`EXPLAIN QUERY PLAN ${sql}`)
.all(param)
.map((row) => String(row["detail"]));
}
beforeEach(() => {
for (let i = 0; i < 50; i += 1) {
addPage(`p${i}`, `https://site.test/${i}`);
addLinks(
`p${i}`,
`https://site.test/${i}`,
Array.from(
{ length: 10 },
(_, k) => `https://site.test/${(i + k) % 50}`,
),
);
}
});
it("seeks page_mirror by url for every link target", () => {
const detail = plan(BROKEN_LINKS_SQL, 100);
expect(detail).toContain(
"SEARCH m USING INDEX sqlite_autoindex_page_mirror_2 (url=?)",
);
expect(detail.some((line) => /SCAN m\b/.test(line))).toBe(false);
});
it("materializes the inbound set once and probes it by index", () => {
const detail = plan(ORPHAN_PAGES_SQL, START);
expect(detail).toContain("MATERIALIZE inbound");
expect(
detail.some((line) =>
line.includes("SEARCH inbound USING AUTOMATIC COVERING INDEX"),
),
).toBe(true);
// page_mirror is scanned once as the outer loop, never per inbound row.
expect(detail.filter((line) => /SCAN m\b/.test(line))).toHaveLength(1);
});
});
describe("frontier link discovery", () => {
// Seeding covers the whole sitemap, so most linked pages already have a
// sitemap row. A link must still give them a click depth and link-queue
// priority, or the deep-page check and breadth-first crawl order go blind.
it("gives a pending sitemap seed the link's depth and priority, keeping its sitemap flag", () => {
const url = "https://site.test/post";
db.prepare(
`INSERT INTO frontier (url, depth, source, in_sitemap) VALUES (?, NULL, 'sitemap', 1)`,
).run(url);
db.prepare(FRONTIER_DISCOVER_SQL).run(url, 2);
db.prepare(FRONTIER_DISCOVER_SQL).run(url, 3);
expect(
db
.prepare(`SELECT depth, source, in_sitemap FROM frontier WHERE url = ?`)
.get(url),
).toEqual({ depth: 2, source: "link", in_sitemap: 1 });
});
});