Skip to main content
Search ranks, and it stops at --limit. If you ask it how many facts there are about a subject, you get the number that made the top ten. vitruvio sql answers that kind of question over every accessible member of a module, exactly. It also names the Merkle roots it read, so you can check that the answer belongs to the brain you think it does. Use search to ask what the brain knows about something. Use sql to ask how much of something the brain holds, which blocks meet a condition, or how blocks in one memory refer to blocks in another. SQL needs the engine, which ships as an extra:
For an agent-focused workflow covering exact counts, registered datasets and compound sql, see the vitruvio-sql skill.

​
The tables

Each module is a table with the name of its memory type, and each block is a row. blocks is a union of every module, restricted to the columns they all share. vitruvio sql --schema lists every column with its type. Every table has the columns id, memory_type, schema_version, resolvable, superseded and demoted. Values are stored exactly as the block wrote them:
  • subject = 'physics' does not match Physics. Write lower(subject) = 'physics' or use ILIKE when you don’t know the case.
  • Timestamps are UTC, and a comparison such as occurred_at >= '2025-03-01' works as written.
  • List fields are SQL lists. Use list_contains(participants, 'ana') to test membership, and UNNEST to group over the values.
A block with two tags appears once per tag after UNNEST, so the group counts can add up to more than the number of blocks.

​
About a topic

Structure answers “how many facts have subject X”. Meaning needs the brain’s vector indices, and two functions bring them into SQL:
  • A threshold, not a ranking. about is true for every block whose score reaches min_score, scored over every block. It is not a top-k, so the count is complete for that threshold.
  • Choose the threshold from similarity(). The score is the vector index’s cosine, between 0 and 1. It is not the score search reports, which measures agreement between retrieval strategies.
  • Write the text and the threshold as literals. Each distinct text is embedded once per query.
  • Such an answer is never exact. A query that uses either function returns exact: false. It also returns one entry in approximate per text, naming the model that scored each module and every module that could not be scored: no vector index, a stale one, or an embedder that is unavailable. A block in an unscored module is never about anything, and the result says so. If nothing at all can be scored, the query is refused rather than answered with zero.
  • Scoring can be slow. Only these queries open the brain’s retrieval indices. They may need vitruvio index build, or a working embedder (see vitruvio config embedder).

​
Data files as tables

A CSV, TSV or Parquet file registered into the brain is a table as well:
  • Naming. A dataset is named after the last segment of the path it was registered from. It can also be named by its block id, or by any unique id prefix of at least 8 hex digits: data."sha256:3ba21fde".
  • Ambiguous names are refused. If two accessible datasets share a file name, the query fails and the error lists their ids.
  • Superseded versions. A superseded version is not reachable by its name unless superseded blocks are included, but its id always works, because an id names exactly one version.
  • What counts as data is decided at registration. Only text/csv, text/tab-separated-values and Parquet (application/vnd.apache.parquet) are read as tables, and the media type is part of the block’s identity. A CSV registered as text/plain stays text. Pass --media-type text/csv when the file extension would guess wrong.
  • Bytes come from the store. They are checked against their digest there, and the result lists each dataset it read, with the block it resolved to, under datasets. A query can name a dataset but never a path.
  • Only named datasets are parsed. Each query parses the datasets it names. datasets lists them all without reading any.
  • Joins with knowledge work. A dataset joins with the knowledge tables like any other table: JOIN semantic s ON s.label = v.region.
In a compound, qualify a dataset with its brain: shop.data."ventas.csv".

​
Several brains

vitruvio compound sql runs one query over several brains of a project in a single database. A bare semantic spans every brain, with a leading brain column; algebra.semantic is one brain’s table; every root is reported as brain.module. See Searching several brains.

​
What counts

  • Superseded and demoted blocks are hidden by default, the same way search hides them. --include-superseded shows them, and in either case the result’s hidden field says how many members were left out. The ledger that decides what to hide is read from the provenance module, so the provenance root appears in verified_against even when your query never names that table.
  • Every member is a row, including one whose bytes are gone. A redacted or uninstalled block has resolvable = false and null fields. It still proves into the root, so count(*) still counts it. Write WHERE resolvable to count only the blocks you can read.
  • A module this brain does not hold is an empty table, and not_installed names it. A count of zero from a missing module does not mean the knowledge is absent. If the provenance module is missing, nothing can be hidden: the result says exact: false and explains why.

​
What is refused

Queries are read-only and bound to the brain:
  • You may send exactly one SELECT. WITH, joins, UNION, HAVING and window functions are allowed.
  • Nothing that writes is run: no INSERT, CREATE, COPY, ATTACH, SET or PRAGMA.
  • Nothing that reads outside the brain is run: no read_csv, read_parquet, glob, or other table functions.
  • Names starting with __vitruvio_ are reserved for the engine’s own tables, so no CTE, alias or column may use them.
  • Sampling (TABLESAMPLE, USING SAMPLE) is refused, because it answers over a subset while the result claims every member.
  • Functions that return a different value on each run (random(), uuid(), now(), current_date, …) are refused. Compare against a literal instead.
A refused query exits with code 2 and names the construct. If something slipped past those checks, it would still run in a database with file access switched off.

​
Reading the result

With --json, the data field of the envelope holds: Values are never rounded:
  • A DECIMAL comes back as its exact decimal string, for example "0.12345678901234567890". The column type tells you to parse it.
  • Infinite and not-a-number floats come back as "Infinity", "-Infinity" and "NaN", because JSON has no numbers for them.
A query with no ORDER BY is ordered by every column. DuckDB runs single-threaded over rows stored in identity order, so order-sensitive functions such as list(), or a LIMIT inside a subquery, see the same order on every run. --explain shows the query as it will actually run, after the table names are rewritten, and DuckDB’s plan for it. Nothing is executed.

​
Limits and cost

  • Time. One budget of 30 seconds covers the whole request: building or loading the tables, running the query, and fetching and verifying the rows. A query that runs past it exits with code 2 (SQL_TIMEOUT). Narrow it with WHERE, or aggregate before you join.
  • The first query after a change is slower. The first query after a module changes projects its blocks into a table and caches it under <brain>/.vitruvio/sql/, keyed by the module’s root. Later queries over the same composition read the cache.
  • The cache stays local. It is not published with the brain. Every drop, prune and redaction deletes it, so no copy of removed knowledge stays behind.
  • Tables do not use the brain’s indices. DuckDB scans them column by column, which takes milliseconds on brains of this size.