Crawl database

Every crawl session, page record, link, image, and diagnostic finding is stored in a single SQLite file. The database is yours — open it with any SQLite-compatible tool to run ad-hoc queries, build reports, or feed data into other systems.

Overview

The database opens in WAL mode with the following pragmas set at connection time:

PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;
PRAGMA foreign_keys = ON;

WAL (write-ahead logging) allows concurrent readers while a crawl is in progress — you can query results in one tool while the crawler is still writing in another. Foreign keys are enforced, so deleting a session cascades to its pages, links, and related records.

The file is a standard SQLite database. Open it with the sqlite3 CLI, DB Browser for SQLite, DBeaver, or any language driver that speaks SQLite.

sqlite3 /path/to/crawls.db ".tables"

Tables

crawl_sessions

One row per crawl. The config column stores the full crawl configuration as JSON — depth limits, concurrency, rate limits, and any custom rules that were active.

pages

The core table. One row per URL discovered during a crawl. A unique constraint on (session_id, url) prevents duplicate entries for the same URL within a session. The seo_data column holds extended SEO analysis as JSON — structured data findings, Open Graph tags, and additional metadata that does not warrant its own column.

Unique constraint: UNIQUE(session_id, url).

links

Every link discovered on every page. The region column records where on the page the link appeared, which is useful for distinguishing editorial links from navigation or footer boilerplate.

images

One row per image element found on a crawled page. Missing alt_text is stored as NULL, distinguishing images with no alt attribute from images with an empty one.

headless_analysis

Results from the headless renderer, when a crawl runs with --headless enabled. Stores Core Web Vitals measurements, console output, resource loading data, accessibility findings, rendering diagnostics, security headers, and mobile-readiness checks.

redirect_chains

Redirect chain analysis for pages that returned a 3xx status or followed one or more redirects before landing. The chain_data column stores each hop as a JSON array of objects with URL, status code, and headers.

page_link_metrics

Link graph metrics computed after the crawl completes. The link_score is a 0 – 100 composite that considers inbound count, PageRank, and graph depth. Orphan pages — those with zero inbound internal links — are flagged with is_orphan = 1.

page_extractions

Values captured by custom extraction rules. Each row records one rule's output for one page, with the extracted values stored as a JSON array.

tls_inspections

TLS certificate inspection results, one row per host encountered during a crawl. The inspection column stores the full certificate chain details as JSON.

Example queries

These queries run against any Consuela crawl database. Replace :session_id with the session you want to inspect, or drop the filter to query across all sessions.

Pages returning 4xx or 5xx

SELECT url, status_code, response_time_ms
FROM pages
WHERE session_id = :session_id
  AND status_code >= 400
ORDER BY status_code;

Pages missing a title

SELECT url, status_code
FROM pages
WHERE session_id = :session_id
  AND status_code = 200
  AND (title IS NULL OR title = '')
ORDER BY url;

Internal broken links with source pages

SELECT
  src.url   AS source_url,
  l.target_url,
  l.anchor_text,
  tgt.status_code
FROM links l
JOIN pages src ON src.id = l.source_page_id
JOIN pages tgt ON tgt.session_id = l.session_id
  AND tgt.url = l.target_url
WHERE l.session_id = :session_id
  AND l.is_internal = 1
  AND tgt.status_code >= 400
ORDER BY tgt.status_code DESC, src.url;

Slowest pages

SELECT url, response_time_ms, size_bytes, status_code
FROM pages
WHERE session_id = :session_id
  AND status_code = 200
ORDER BY response_time_ms DESC
LIMIT 20;

Orphan pages (no inbound internal links)

SELECT p.url, p.status_code, p.depth
FROM pages p
JOIN page_link_metrics m ON m.page_id = p.id
WHERE p.session_id = :session_id
  AND m.is_orphan = 1
ORDER BY p.url;