This document describes the SQLite storage behind .vdb7 files and every SQL
statement vdb runs against it. It is written for two readers: someone querying
the files directly from another language or a sqlite3 shell, and a maintainer
changing vdb/lib/db.py, search.py or the splitter who needs to know what
the other side of the storage contract already does.
Everything here describes v7 only. v6 files are neither read nor migrated (MIGRATING_TO_V7.md).
A vdb installation is a pair of SQLite files under VDB_HOME (default
~/.vdb, override with VDB_HOME):
| File | Default full path | Role |
|---|---|---|
data.vdb7 |
$VDB_HOME/data.vdb7 |
source blobs and per-record detail (cold, large) |
data.index.vdb7 |
$VDB_HOME/data.index.vdb7 |
locators, metadata, text indexes (hot, small) |
Defined in vdb/lib/config.py as VDB_BIN_FILE / VDB_BIN_INDEX. Connections
are opened with apsw in
vdb/lib/db.py:get(). Read-only opens use mode=ro URIs and, unless
VDB_SQLITE_IMMUTABLE=false, add immutable=1.
The split matters for query performance: every search hits the index file
first, and only matched records are hydrated from the data file. A shard
($VDB_SHARDS_DIR/<type>/data.vdb7 + data.index.vdb7) uses the same schema
in both files.
Page sizes differ by design (vdb/lib/db.py): the data file defaults to 32 KB
pages (DEFAULT_DATA_PAGE_SIZE, overridable with VDB_DATA_PAGE_SIZE=4096 to
restore 4 KB) because its ~2.6 KB WITHOUT ROWID blobs waste half of every 4 KB
page; the index file keeps SQLite’s 4096 default (VDB_INDEX_PAGE_SIZE to
override). Both files are re-paged to the configured size by the final
VACUUM INTO.
Created by ensure_schemas() in vdb/lib/db.py:156. All shipped tables are
WITHOUT ROWID.
CREATE TABLE cve_source_data(
source_data_hash TEXT PRIMARY KEY, -- blake2b of the sorted-key orjson dump
source_data BLOB NOT NULL -- CVE-5 document encoded as JSONB
) WITHOUT ROWID;
CREATE TABLE cve_data(
cve_id TEXT NOT NULL, -- CVE-2026-0001, GHSA-..., MAL-...
type TEXT NOT NULL, -- package type: npm, pypi, deb, ...
namespace TEXT, -- vendor / namespace, may be NULL/empty
name TEXT NOT NULL, -- package name
override_data BLOB, -- overrides, rarely populated
source_data_hash TEXT NOT NULL, -- FK-by-convention into cve_source_data
vers TEXT NOT NULL, -- osv-style range, e.g. vers:npm/<2.0.0
purl_prefix TEXT NOT NULL -- locator, e.g. pkg:npm/left-pad
);
CREATE INDEX idx1 ON cve_data(cve_id, vers, purl_prefix);
cve_data holds one row per (advisory, package, range) affected entry.
cve_source_data is content-addressed blob storage shared by every
cve_data row derived from the same source document, which is what makes
advisories with dozens of affected packages cheap. The hash link is enforced
by convention, not by a FOREIGN KEY.
CREATE TABLE cve_index(
cve_id TEXT NOT NULL,
type TEXT NOT NULL,
namespace TEXT,
name TEXT NOT NULL,
vers TEXT NOT NULL,
purl_prefix TEXT NOT NULL,
PRIMARY KEY(cve_id, vers, purl_prefix) ON CONFLICT IGNORE
) WITHOUT ROWID;
CREATE TABLE cve_metadata(
cve_id TEXT NOT NULL,
vers TEXT NOT NULL,
purl_prefix TEXT NOT NULL,
source TEXT NOT NULL, -- feed: NVD, OSV, GHA, ...
severity TEXT, -- LOW/MEDIUM/HIGH/CRITICAL
score REAL, -- CVSS score
published TEXT,
updated TEXT,
is_malware INTEGER NOT NULL DEFAULT 0,
PRIMARY KEY(cve_id, vers, purl_prefix)
) WITHOUT ROWID;
CREATE TABLE cve_metadata_text(
cve_id TEXT NOT NULL PRIMARY KEY,
aliases TEXT, -- newline-joined GHSA-/PYSEC-/... ids
reference_text TEXT, -- newline-joined reference URLs
description_text TEXT,
affected_symbols TEXT -- functions/modules for reachability
) WITHOUT ROWID;
CREATE INDEX cidx5 ON cve_index(purl_prefix);
CREATE INDEX cidx6 ON cve_index(name, namespace, type);
CREATE INDEX cidx8 ON cve_metadata(updated);
CREATE INDEX cidx9 ON cve_metadata(published);
cve_index is the lookup table: the search path proposes purl_prefix
candidates and asks vers_compare(version, vers) about each candidate row.
cve_index is written with plain INSERT under a PK declared ON CONFLICT
IGNORE, so the first source to write a (cve_id, vers, purl_prefix) triple
wins and later sources’ duplicates are dropped. Reordering ingestion sources
therefore changes which row survives. This is a documented property, not a
bug to fix.
Because the table is WITHOUT ROWID with PK (cve_id, vers, purl_prefix),
every secondary index carries those three columns as its row locator.
cidx6(name, namespace, type) is deliberately ordered so it covers the whole
shipped select list: package-name and CPE queries read the index alone, with
no table lookups. The column order is load-bearing; see the comment in
vdb/lib/db.py:537.
cve_metadata and cve_metadata_text exist only in databases built (or
backfilled) with metadata enabled; the text table also powers alias, reference,
full-text and symbol search.
During a build, two temp tables provide in-run dedupe so a (cve_id, vers,
purl_prefix) seen from the same source is not inserted twice:
CREATE TEMP TABLE vdb_seen_source_keys(key TEXT PRIMARY KEY) WITHOUT ROWID; -- data db
CREATE TEMP TABLE vdb_seen_index_keys(key TEXT PRIMARY KEY) WITHOUT ROWID; -- index db
They live in the connection’s temp store, never in the shipped files.
vers_compareThe index-file connection registers one scalar function
(vdb/lib/db.py:313):
index_conn.createscalarfunction("vers_compare", vers_compare, deterministic=True)
It is vdb.lib.vers.vers_compare, the single version comparison
implementation, exposed to SQL so range matching runs inside the WHERE
clause instead of in Python per row. Several search queries below call
vers_compare(?, vers). Any connection used to run those queries (including
yours, and the shard fan-out scratch connection in shard_store.py) must
register the function first, or the query fails with “no such function”.
Its semantics have two deliberate surprises: an empty vers string matches
every version, and a non-numeric version sorts above numeric ones.
Grouped by phase. “Data” / “index” name the file. Line numbers point at the statement in the current tree; grep the SQL text if they have drifted.
vdb/lib/cve.py, db.py, search_index.py)CVESource.store5() is the shared write path for every feed converter.
| Purpose | Statement | Where |
|---|---|---|
| Blob dedupe | INSERT OR IGNORE INTO cve_source_data VALUES(?, ?) |
db.py:198 |
| In-run dedupe gate | INSERT OR IGNORE INTO vdb_seen_source_keys VALUES(?) |
db.py:186 |
| In-run dedupe gate | INSERT OR IGNORE INTO vdb_seen_index_keys VALUES(?) |
db.py:192 |
| Detail row | INSERT INTO cve_data VALUES(?,?,?,?,?,?,?,?) |
cve.py:1231 |
| Locator row | INSERT INTO cve_index VALUES(?,?,?,?,?,?) |
cve.py:1260 |
| Metadata upsert | INSERT OR REPLACE INTO cve_metadata(...) VALUES(?,...,?) |
search_index.py:296 |
| Text upsert | INSERT INTO cve_metadata_text(...) VALUES(?,?,?,?,?) ON CONFLICT(cve_id) DO UPDATE SET ... WHERE <any column IS NOT excluded. ...> |
search_index.py:315 |
The metadata writes run only when VDB_INCLUDE_METADATA is set (the
published full/app images set it via --include-metadata).
vdb/lib/db.py)clear_all() (before a rebuild) drops and recreates every table and secondary
index listed above, and clears the temp tables. It does not VACUUM: builds run
on fresh CI runners, so there is no freelist to reclaim.
optimize_and_close_all() (after a rebuild) runs, in order, per file:
-- data file
CREATE INDEX if not exists idx1 on cve_data(cve_id, vers, purl_prefix);
DELETE FROM cve_source_data WHERE source_data_hash NOT IN
(SELECT DISTINCT source_data_hash FROM cve_data); -- orphan blob sweep
-- index file
CREATE INDEX if not exists cidx6 on cve_index(name, namespace, type);
CREATE INDEX if not exists cidx5 on cve_index(purl_prefix);
CREATE INDEX if not exists cidx8 on cve_metadata(updated);
CREATE INDEX if not exists cidx9 on cve_metadata(published);
-- both files
VACUUM INTO '<tmp path>'; -- then os.replace() onto the real path
The VACUUM INTO + atomic rename is what compacts the shipped artifact and
applies the configured page size. -wal/-shm sidecars are removed after the
swap. Row counts (stats(), metadata_rows_count()) use plain
SELECT count(*) FROM <table>.
vdb/lib/shard_split.py)The splitter ATTACHes the full databases read-only (ATTACH DATABASE ? AS
src_data / src_index) and copies rows into fresh shard files whose purl
type matches. The shard key is computed in SQL:
substr(purl_prefix, 5, instr(purl_prefix || '/', '/') - 5) -- the purl type
Copy set, per shard (main = the shard, src_* = the full db):
INSERT INTO main.cve_data SELECT * FROM src_data.cve_data WHERE (<type expr>) IN ('deb', ...);
INSERT INTO main.cve_source_data SELECT s.* FROM src_data.cve_source_data s
WHERE s.source_data_hash IN (SELECT DISTINCT source_data_hash FROM main.cve_data);
INSERT INTO main.cve_index SELECT * FROM src_index.cve_index WHERE (<type expr>) IN ('deb', ...);
INSERT INTO main.cve_metadata SELECT m.* FROM src_index.cve_metadata m
WHERE EXISTS (SELECT 1 FROM main.cve_index i WHERE i.cve_id = m.cve_id
AND i.vers = m.vers AND i.purl_prefix = m.purl_prefix);
INSERT INTO main.cve_metadata_text SELECT t.* FROM src_index.cve_metadata_text t
WHERE t.cve_id IN (SELECT DISTINCT cve_id FROM main.cve_index);
Group shards use IN (<types>); the cpe complement shard uses NOT IN.
Each shard then gets the same five secondary indexes as a full build, the same
VACUUM INTO compaction, and passes two integrity gates before publishing:
SELECT count(*) FROM (SELECT source_data_hash FROM main.cve_source_data
EXCEPT SELECT source_data_hash FROM main.cve_data); -- must be 0
SELECT count(*) FROM (SELECT source_data_hash FROM main.cve_data
EXCEPT SELECT source_data_hash FROM main.cve_source_data); -- must be 0
verify_split() re-opens the published files read-only, checks that group
shards’ cve_data/cve_index counts sum to the full database, and attaches
every shard on one scratch connection to prove the distinct hash union still
covers every source blob. Scheduling input comes from one grouped scan:
SELECT substr(purl_prefix, 5, instr(purl_prefix || '/', '/') - 5) AS ptype,
count(*) FROM cve_data WHERE purl_prefix LIKE 'pkg:%' GROUP BY ptype;
vdb/lib/db_cmd.py)Before a downloaded full image may replace the local database, the staged file is opened read-only and probed:
SELECT cve_id FROM cve_index LIMIT 1;
Zero rows aborts the refresh. An empty database must never look like “no vulnerabilities found”.
vdb/lib/search.py)All search queries run on the index file and return the six columns of
cve_index; hydration then joins the data file. Two shared WHERE fragments
recur:
purl_prefix IN (?,?,...) AND vers_compare(?, vers),
where the candidate prefixes are built by _purl_search_spec()
(search.py:956): the canonical pkg:<type>/[namespace/]<name> prefix,
plus distro release-folded variants when the purl carries distro_name or
(for release-scoped rpm families) distro qualifiers. Store and lookup
share the construction so the proposed prefixes always include the form the
feed stored.AND NOT (vers GLOB 'vers:*<[0-9a-f][0-9a-f]...[0-9a-f]*' AND NOT vers GLOB
'vers:*<[0-9]...[0-9]*') cheaply skips rows whose range only matches
content-addressed versions (search.py:767).Canonical purl lookup (single component, search.py:1130; the same body is
fanned out per shard, see 3.6):
SELECT cve_id, type, namespace, name, vers, purl_prefix
FROM cve_index
WHERE purl_prefix IN (?,?,...) AND vers_compare(?, vers);
Bulk purl probe (many components in one round trip, search.py:1607), chunked
to SQLite’s variable limit:
WITH lookup(pos, purl_prefix, version) AS (VALUES (?,?,?), ...)
SELECT l.pos, i.cve_id, i.type, i.namespace, i.name, i.vers, i.purl_prefix
FROM lookup l JOIN cve_index i
ON i.purl_prefix = l.purl_prefix AND vers_compare(l.version, i.vers);
rpm superseded-fix probe (search.py:560, versioned rpm lookups only):
SELECT cve_id, purl_prefix, vers FROM cve_index
WHERE type = 'rpm' AND cve_id IN (...) AND purl_prefix IN (...);
CVE id search: exact, LIKE when the id contains %, optional LIMIT
(search.py:1248); latest_malware() reuses it with MAL-%:
SELECT cve_id, type, namespace, name, vers, purl_prefix
FROM cve_index WHERE cve_id = ? ORDER BY cve_id DESC;
CPE-like search (search.py:924): a UNION of two branch shapes, run once per
normalised (vendor, product) candidate spelling, using the covering index
cidx6 on the second branch:
SELECT cve_id, type, namespace, name, vers, purl_prefix FROM cve_index
WHERE namespace = ? AND name = ? AND vers_compare(?, vers)
UNION
SELECT cve_id, type, namespace, name, vers, purl_prefix FROM cve_index
WHERE type = ? AND name = ? AND vers_compare(?, vers);
Package-name search (search.py:1482): exact-name first, then
namespace/name, then substring, ordered by relevance then recency; served
entirely from cidx6 plus a LEFT JOIN for ordering:
SELECT DISTINCT i.cve_id, i.type, i.namespace, i.name, i.vers, i.purl_prefix
FROM cve_index i
LEFT JOIN cve_metadata m ON m.cve_id = i.cve_id AND m.vers = i.vers
AND m.purl_prefix = i.purl_prefix
WHERE lower(i.name) = ? OR lower(i.namespace || '/' || i.name) = ?
OR lower(i.name) LIKE ? OR lower(i.namespace || '/' || i.name) LIKE ?
ORDER BY CASE WHEN lower(i.name) = ? THEN 0
WHEN lower(i.namespace || '/' || i.name) = ? THEN 1
WHEN lower(i.name) LIKE ? THEN 2 ELSE 3 END,
m.updated DESC, i.cve_id DESC;
Metadata, alias, reference, full-text and symbol searches join
cve_metadata_text to cve_index ( LEFT JOIN cve_metadata for recency
ordering) with lower(col) LIKE '%token%' predicates (search.py:1387-1465).
The general shape:
SELECT DISTINCT i.cve_id, i.type, i.namespace, i.name, i.vers, i.purl_prefix
FROM cve_metadata_text t JOIN cve_index i ON i.cve_id = t.cve_id
LEFT JOIN cve_metadata m ON m.cve_id = i.cve_id AND m.vers = i.vers
AND m.purl_prefix = i.purl_prefix
WHERE lower(t.<aliases|reference_text|affected_symbols>) LIKE ?
ORDER BY m.updated DESC, i.cve_id DESC;
search_full_text builds the same query with a tokenised OR (or AND) clause
over description_text, reference_text, aliases and affected_symbols.
vdb/lib/search.py)Index hits are hydrated from the data file in batches of 200
(BATCH_SIZE, search.py:693); single-hit legacy path at search.py:619:
WITH lookup(cve_id, vers, purl_prefix) AS (VALUES (?,?,?), ...)
SELECT d.cve_id, d.type, d.namespace, d.name, d.source_data_hash,
s.source_data, d.override_data, d.vers, d.purl_prefix
FROM cve_data d
JOIN cve_source_data s ON d.source_data_hash = s.source_data_hash
JOIN lookup l ON d.cve_id = l.cve_id AND d.vers = l.vers
AND d.purl_prefix = l.purl_prefix
GROUP BY d.cve_id, d.vers, d.purl_prefix
ORDER BY d.cve_id DESC;
Metadata attach runs the mirror query on cve_metadata (optionally LEFT JOIN
cve_metadata_text for aliases/description), chunked to the variable limit
(search.py:340):
WITH lookup(cve_id, vers, purl_prefix) AS (VALUES (?,?,?), ...)
SELECT m.cve_id, m.vers, m.purl_prefix, m.source, m.severity, m.score,
m.published, m.updated, m.is_malware
FROM cve_metadata m
JOIN lookup l ON m.cve_id = l.cve_id AND m.vers = l.vers
AND m.purl_prefix = l.purl_prefix;
If the database was built without metadata but has data rows, the first
filtered search lazily backfills cve_metadata/cve_metadata_text by
scanning cve_data JOIN cve_source_data once and replaying the build-time
upserts (ensure_search_metadata, search.py:257-267). This is the only
write path reachable at search time.
vdb/lib/shard_store.py)With per-type shards installed, each search fans out to the shards covering
the queried purl types; the architecture is documented in
DESIGN.md. The default strategy opens each shard’s
connections read-only and reuses the queries above. The opt-in
VDB_SHARD_FANOUT=attach strategy instead ATTACHes up to the
SQLITE_MAX_ATTACHED (10) shard index files on one scratch connection and
runs one UNION ALL sweep:
ATTACH DATABASE 'file:<shard>/data.index.vdb7?mode=ro' AS shard_<name>;
SELECT cve_id, type, namespace, name, vers, purl_prefix
FROM shard_deb.cve_index WHERE purl_prefix IN (?,?,...) AND vers_compare(?, vers)
UNION ALL
SELECT ... FROM shard_rpm.cve_index WHERE ...
;
Metadata attach and blob hydration are then routed to the owning shard’s
connections. The scratch connection registers vers_compare itself.
packages/mcp-server-vdb)Before answering any tool call or resource read, the server runs:
SELECT count(*) FROM cve_index;
against the connected index file and refuses to answer when it is zero and no shard holds data: the library-side empty-database guard, applied server-side.
sqlite3 "file:$VDB_HOME/data.index.vdb7?mode=ro" "SELECT * FROM cve_index WHERE purl_prefix LIKE 'pkg:npm/react%';"
works, with two caveats:
vers_compare need the function registered; apsw exposes
this via Connection.createscalarfunction. Plain SQL without it is fine.Write something only through a build (vdb --cache) or the library’s store
path. Hand-edited rows will not survive the next rebuild: artifacts are
rebuilt from scratch by design, and there is no migration path.