latest.sql
The schema of a SQLite archive from the latest release, every column commented. /schemas/sqlite/<version>.sql holds each release’s.
-- zshot 0.54.2
-- zshot SQLite archive schema.
--
-- `zshot -t sqlite` writes a page, or a crawl of pages, as one SQLite database. It holds what
-- a WARC or WACZ of the same capture holds: every HTTP exchange with its payload, which page
-- each belongs to, and which responses revisit a payload stored already. Beside them are
-- each page's rendered HTML, text and screenshot, the links a crawl followed, and full-text
-- indexes over pages and text payloads.
--
-- The zshot version that wrote a database is `crawl.zshot_version`, and it is the version
-- of this schema.
--
-- Times are ISO-8601 UTC text with microseconds, such as `2026-10-21T15:00:21.660475Z`.
-- Headers are JSON arrays of `[name, value]` pairs in the order they were sent or received,
-- names as sent; the `headers` view gives one row per header. Booleans are 0 or 1.
PRAGMA application_id = 2054383727;
-- The capture this database holds. Always exactly one row.
CREATE TABLE crawl (
id INTEGER PRIMARY KEY CHECK (id = 1), -- always 1
request_id TEXT NOT NULL, -- the UUID zshot gave the request
zshot_version TEXT NOT NULL, -- the zshot that wrote the database
target_url TEXT NOT NULL, -- the URL, sitemap or manifest the capture was of
robots_checked INTEGER NOT NULL, -- whether robots.txt and page robot directives were obeyed
started_at TEXT NOT NULL, -- when zshot received the request
finished_at TEXT NOT NULL, -- when the database was written
invocation TEXT -- the request as a zshot command line, secrets filtered
);
-- Each response payload, stored once however many exchanges were served it.
CREATE TABLE payloads (
digest TEXT PRIMARY KEY, -- `sha256:` and the hex SHA-256 of the body, as a WARC's WARC-Payload-Digest gives it
body BLOB NOT NULL, -- the body as the browser received it, after content decoding
size INTEGER NOT NULL, -- length of body in bytes
truncated INTEGER NOT NULL -- 1 when the body was cut at --archive-body-size-limit
);
-- Each HTTP exchange the capture made: a request, and the response it was served when it
-- has one. `id` is archive order, the order of the records in a WARC of the same capture.
CREATE TABLE resources (
id INTEGER PRIMARY KEY, -- archive order, from 1
started_at TEXT NOT NULL, -- when the request was sent
url TEXT NOT NULL, -- the request URL
method TEXT NOT NULL, -- the request method, such as GET
request_headers TEXT NOT NULL CHECK (json_valid(request_headers)), -- the request's headers as JSON
request_body BLOB, -- the request body, such as a POST's form data
status INTEGER, -- the response status; NULL for an omitted or incomplete exchange
status_text TEXT, -- the response's reason phrase
response_headers TEXT CHECK (json_valid(response_headers)), -- the response's headers as JSON, as received: a Content-Encoding names the wire's encoding, which the stored payload no longer has. For a revisit of a 304, the stored response's headers as the 304 updated them.
content_type TEXT, -- the response's Content-Type
resource_type TEXT, -- document, stylesheet, script, image, font, media, fetch or other, from the Content-Type
payload_digest TEXT REFERENCES payloads(digest) -- the payload it was served, stored in full or revisited; NULL when it had none
);
-- What each page loaded, and how each resource reached it. Every exchange belongs to one page.
CREATE TABLE page_resources (
page_id INTEGER NOT NULL REFERENCES pages(id), -- the page that loaded it
resource_id INTEGER NOT NULL REFERENCES resources(id), -- the exchange
served TEXT NOT NULL CHECK (served IN ('full', 'revisit', 'omitted', 'incomplete')), -- full: a WARC response record with its payload. revisit: a WARC revisit record, its payload stored by revisit_of. omitted: the body was lost or failed its digest, so only the request is kept. incomplete: the request never completed.
revisit_reason TEXT CHECK (revisit_reason IN ('not-modified', 'identical-payload')), -- for a revisit: not-modified when the browser revalidated its cached copy with a 304, identical-payload when the payload matches one stored already
revisit_of INTEGER REFERENCES resources(id), -- for a revisit, the exchange whose full response stores the payload; it may belong to another page
is_document INTEGER NOT NULL, -- 1 for the page's own HTML document
PRIMARY KEY (page_id, resource_id)
);
-- Each page captured. A navigation that landed on a page captured already adds no page:
-- what it loaded is filed under that page. A crawled page that asked for `noarchive` is
-- left out, with what it loaded.
CREATE TABLE pages (
id INTEGER PRIMARY KEY, -- capture order, from 1; the rowid of pages_fts
page_id TEXT NOT NULL UNIQUE, -- the UUID zshot gave the page
url TEXT NOT NULL, -- the page URL after redirects
requested_url TEXT NOT NULL, -- the URL the crawl loaded, before redirects
title TEXT, -- document.title after scripts ran
depth INTEGER NOT NULL, -- links followed from a seed; 0 for a seed
parent_id INTEGER REFERENCES pages(id), -- the page whose link led here; NULL for a seed, or when that page asked for `noarchive`
captured_at TEXT NOT NULL, -- when its document was requested, else when its navigation began
document_resource_id INTEGER REFERENCES resources(id), -- the exchange that served its HTML document
document_status INTEGER, -- that document's status
html TEXT, -- the main frame's HTML as the browser serialized it after scripts ran, as `-t html` writes it
text TEXT, -- the main frame's innerText, the text a reader sees
accessible_text TEXT, -- the text its accessibility tree reads, as WACZ pages.jsonl gives it
screenshot BLOB, -- a PNG of the viewport
robots TEXT -- the robot directives the crawl honored for it, such as `noindex, nofollow`; NULL when none
);
-- The links each crawled page offered, and what the crawl made of each. A page at the
-- crawl's maximum depth, a page that asked for `nofollow`, and a page of a sitemap or
-- manifest offer none.
CREATE TABLE links (
page_id INTEGER NOT NULL REFERENCES pages(id), -- the page that offered the link
position INTEGER NOT NULL, -- its place among the page's links, from 0
url TEXT NOT NULL, -- the link's absolute URL
verdict TEXT NOT NULL CHECK (verdict IN ('queued', 'seen', 'not-http', 'out-of-scope', 'robots-disallowed', 'adblocked', 'unchecked')), -- queued: admitted to the crawl. seen: captured or queued already. not-http: not an http(s) URL. out-of-scope: outside the seeds' scope. robots-disallowed and adblocked: refused by robots.txt or the ad blocker. unchecked: the crawl could not check it.
target_page_id INTEGER REFERENCES pages(id), -- the page captured at the link's URL, when one was
PRIMARY KEY (page_id, position)
);
CREATE INDEX resources_url ON resources(url);
CREATE INDEX page_resources_resource ON page_resources(resource_id);
CREATE INDEX pages_url ON pages(url);
CREATE INDEX links_target ON links(target_page_id);
-- Full-text search over pages: `SELECT ... FROM pages_fts WHERE pages_fts MATCH 'word'`.
-- Its rowid is pages.id. A page that asked for `noindex` is not in it.
CREATE VIRTUAL TABLE pages_fts USING fts5(
title, -- pages.title
text, -- pages.text
html_text, -- pages.html with its markup, scripts and styles removed
tokenize = 'unicode61'
);
-- Full-text search over text payloads: HTML, CSS, JavaScript, JSON, XML, SVG and other
-- text/* bodies, but not the document of a page that asked for `noindex`. Its rowid is
-- the rowid of payloads.
CREATE VIRTUAL TABLE payloads_fts USING fts5(
body, -- payloads.body as UTF-8
tokenize = 'unicode61'
);
-- Each page's resources with what was served.
CREATE VIEW page_resources_v AS
SELECT
p.id AS page_id,
p.url AS page_url,
r.id AS resource_id,
r.url AS resource_url,
r.method,
r.status,
pr.served,
pr.revisit_reason,
pr.is_document,
r.content_type,
r.resource_type,
pl.size
FROM page_resources pr
JOIN pages p ON p.id = pr.page_id
JOIN resources r ON r.id = pr.resource_id
LEFT JOIN payloads pl ON pl.digest = r.payload_digest;
-- Per page, how many resources were served each way, and the bytes of their payloads.
CREATE VIEW page_summary AS
SELECT
p.id AS page_id,
p.url,
p.title,
p.depth,
count(pr.resource_id) AS resources,
sum(pr.served = 'full') AS full,
sum(pr.served = 'revisit') AS revisits,
sum(pr.served = 'omitted') AS omitted,
sum(pr.served = 'incomplete') AS incomplete,
coalesce(sum(pl.size), 0) AS bytes
FROM pages p
LEFT JOIN page_resources pr ON pr.page_id = p.id
LEFT JOIN resources r ON r.id = pr.resource_id
LEFT JOIN payloads pl ON pl.digest = r.payload_digest
GROUP BY p.id;
-- Each revisit, with the exchange and page that store its payload.
CREATE VIEW revisits_v AS
SELECT
pr.page_id,
p.url AS page_url,
r.id AS resource_id,
r.url AS resource_url,
pr.revisit_reason,
pr.revisit_of,
o.url AS original_url,
opr.page_id AS original_page_id
FROM page_resources pr
JOIN pages p ON p.id = pr.page_id
JOIN resources r ON r.id = pr.resource_id
JOIN resources o ON o.id = pr.revisit_of
LEFT JOIN page_resources opr ON opr.resource_id = o.id
WHERE pr.served = 'revisit';
-- Each header of each exchange, one row per header: `WHERE name = 'cache-control'`.
CREATE VIEW headers AS
SELECT
r.id AS resource_id,
'request' AS direction,
h.key AS position,
lower(h.value ->> 0) AS name,
h.value ->> 1 AS value
FROM resources r, json_each(r.request_headers) h
UNION ALL
SELECT
r.id,
'response',
h.key,
lower(h.value ->> 0),
h.value ->> 1
FROM resources r, json_each(r.response_headers) h;