Querying a release bundle with SQL

corpus.sqlite is the artifact a release ships so that a question about the law can be answered with no network, no server and no other file. It holds the canon’s texts, the obligation atoms extracted from them, the cross-reference graph and the identity of the release itself. Everything on this page is plain SQL against a plain SQLite file; nothing here needs openregs to be installed.

regimes/fixreg/releases/2025.04/corpus.sqlite

From a shell, with the sqlite3 CLI — either the standalone binary or the one the Python interpreter ships, which takes the SQL as an argument:

sqlite3 regimes/fixreg/releases/2025.04/corpus.sqlite ".tables"
python -m sqlite3 regimes/fixreg/releases/2025.04/corpus.sqlite \
    "SELECT name FROM sqlite_schema WHERE type = 'table' ORDER BY name"

(the second form takes SQL only, not dot-commands, which is why the table list is asked for as a query.)

From a program, open it read-only, so a query can never write to an artifact whose sha256 is pinned in release-meta.yaml:

sqlite3.connect("file:corpus.sqlite?mode=ro", uri=True)

Every query below is executed against the shipped 2025.04 bundle by tooling/tests/test_sqlite_queries.py on every test run, inside a section where the interpreter’s network primitives raise on contact and where the bundle is the only file in its directory. A query on this page that stopped working, or that needed anything but the one file, would fail the build.

Two rules that decide the shape of every query

An identifier alone does not name a text. Three acts in the FIXREG canon declare art_2, and two Expressions of one act declare art_5__para_3. A row in units is one version of one provision, carrying the window it was in force for, so every query about text carries a date and — where more than one act is in scope — a Work.

An atom id alone does not name a wording. FIXREG-Art5.3-Ob1 is the same obligation before and after the 2025 amendment, but it says EUR 5000 until 2025-02-28 and EUR 10000 from 2025-03-01. A row in atoms is one version of one atom, keyed (id, effective), so every query about obligations carries a date too.

Both windows are inclusive at both ends, and an open window is NULL:

-- name: window-predicate
-- The predicate every as-of query is built from, shown on its own so that the
-- shape is stated once: both bounds inclusive, NULL meaning "still open".
SELECT
    'units' AS relation,
    COUNT(*) AS in_force_on_2025_06_01
FROM units
WHERE valid_from <= '2025-06-01'
  AND (valid_to IS NULL OR '2025-06-01' <= valid_to)
UNION ALL
SELECT
    'atoms',
    COUNT(*)
FROM atoms
WHERE effective <= '2025-06-01'
  AND (expires IS NULL OR '2025-06-01' <= expires);

The tables

release_info

Key/value, one row per fact, so a reader can tell what the file is before trusting anything in it.

key	meaning
regime	the regime this bundle is a release of
tag	the release tag, <YYYY.MM>
as_of	the day the tag speaks for, derived from the tag
commit	the canon commit the release was compiled from
regime_tree	git rev-parse <commit>:regimes/<regime> — the content-addressed fallback identity
built_at	that commit’s committer date, never the wall clock
builder	the builder and its version
schema_version	the spec schema version the corpus was built under
spec_schema_min / spec_schema_max	the schema range this bundle admits
bundle_version	the layout version of this file; 2 keyed atoms by (id, effective), 3 added modifications
sqlite_header_version	the value pinned into the SQLite header so the bundle does not name the library that wrote it
count_units / count_atoms / count_edges / count_expressions / count_modifications	row counts, so a truncated file is detectable without a full scan

-- name: release-info
SELECT key, value FROM release_info ORDER BY key;

units

One row per unit version: every addressable eId of every Expression, not one row per canon file. art_5__para_3 lives inside art-005.akn.xml, and it is the address an atom cites and an as-of query asks for, so it is a row.

column	meaning
node	<work>/<expression>/<eId> — the primary key, naming one version
work	canon directory of the act, e.g. eu-act-reg-2024-1
expression	canon directory of the Expression, e.g. eng@2025-03-01
eid	Akoma Ntoso element id, e.g. art_5__para_3
provision	<work>/<language>/<eId> — the thing that has versions
language	ISO 639 code of the Expression
work_iri / expression_iri	FRBR identity, as the canon states it
jurisdiction	FRBRcountry, e.g. eu
celex	CELEX number where the act has one, else NULL
valid_from / valid_to	validity window, both bounds inclusive, NULL = open
known_at	transaction time: when this corpus learned the text
nested	1 when the eId is nested inside the file that carries it
path	canon-relative path of the file the text came from
text	the unit’s text including everything nested inside it
own_text	the unit’s own words — what it says, not what its children say
meta	the unit’s .meta.yaml document, as JSON

atoms

One row per atom version, keyed (id, effective). Every field of spec/schemas/atom.schema.json, flattened, plus the whole document as JSON so nothing is lost.

column	meaning
id	permanent atom id, e.g. FIXREG-Art5.3-Ob1
effective / expires	the window this wording of the duty was in force, inclusive, NULL = open
source	human-readable citation
unit_eid	eId of the unit the duty was read out of — joins to units.eid
applies_to	JSON array of entity-type ids
actor, modality, action, conditions	the duty; modality is one of MUST, MUST_NOT, SHOULD, MAY; conditions is a JSON array
status, superseded_by	active or deprecated, and where a deprecated atom points
provenance_unit_eid, provenance_char_start, provenance_char_end, provenance_quoted	the evidence span; provenance_quoted is byte-for-byte the canon text between the offsets
executable_name, executable_value, executable_unit, executable_quoted	the machine-actionable value, when the duty states one; executable_value is JSON, so 5000 and "P30D" are both representable
extraction_model, extraction_version, extraction_confidence, extraction_method	how the atom came to exist
language_divergence	JSON, or NULL when no divergence is known
path	regime-relative path of the atom file
document	the whole atom document as JSON

edges

The committed cross-reference graph, one row per edge. A unit endpoint is a node in units; an applies_to edge ends at an entity-type id.

column	meaning
id	insertion ordinal, for a stable order
type	cites, defines, modifies, exempts or applies_to
src / dst	the endpoints
attrs	JSON: term for defines, applies_to for exempts, operation/position/inserted for modifies

expressions

One row per canon Expression directory, so a units row’s work_iri has somewhere to come from that a reader can also see: key, work, component, language, valid_from, work_iri, expression_iri, jurisdiction, celex, units.

-- name: expressions
SELECT key, work_iri, expression_iri, jurisdiction, celex, units
FROM expressions
ORDER BY key;

modifications

One row per passive modification of a consolidated Expression: which instruction of which amending act produced this version of this unit, and whether that operation was substantive or editorial.

column	meaning
node	the unit version the amendment produced — a node in units
provision	that unit’s provision, <work>/<language>/<eId>
work / expression / eid	the same address, split
type	Akoma Ntoso’s word for what happened: substitution, insertion, repeal
source_href	the instruction that ordered it, as an AKN reference into the amending act
classification	substantive or editorial
classification_basis	why, in the amendment parser’s own words

The ruling is decided from the amending act’s recitals — “that correction does not alter the meaning of that Article” — and a recital is not a legal unit, so it never enters the canon on its own. Consolidation writes the ruling onto the textualMod it records in the consolidated text, and this table carries it into the release. It is what lets openregs diff call a typo correction editorial from two release bundles and nothing else.

-- name: modifications
SELECT provision, type, classification, classification_basis, source_href
FROM modifications
ORDER BY node, source_href;

Joined to the text it changed:

-- name: editorial-changes
-- Every provision an editorial amendment produced, with the words it now has.
SELECT m.provision, m.classification_basis, u.own_text
FROM modifications AS m
JOIN units AS u ON u.node = m.node
WHERE m.classification = 'editorial'
ORDER BY m.provision;

The canonical as-of query

“What did this provision say on that day, and which obligations attached to it?” — one query, one date, and everything a citation needs.

-- name: as-of-2025-01-15
SELECT
    u.eid,
    u.work_iri,
    u.expression_iri,
    u.valid_from,
    u.valid_to,
    u.own_text                AS text,
    a.id                      AS atom_id,
    a.modality,
    a.actor,
    a.action,
    a.executable_name,
    a.executable_value,
    a.executable_unit,
    a.provenance_quoted
FROM units u
LEFT JOIN atoms a
       ON a.unit_eid = u.eid
      AND a.status = 'active'
      AND a.effective <= '2025-01-15'
      AND (a.expires IS NULL OR '2025-01-15' <= a.expires)
WHERE u.eid = 'art_5__para_3'
  AND u.work_iri = '/akn/eu/act/reg/2024/1'
  AND u.valid_from <= '2025-01-15'
  AND (u.valid_to IS NULL OR '2025-01-15' <= u.valid_to)
ORDER BY a.id;

returns the pre-amendment text and the pre-amendment threshold:

art_5__para_3 | 2024-06-01 | 2025-02-28 | Operators must report to the authority any
incident causing losses above EUR 5000. | FIXREG-Art5.3-Ob1 | MUST | ... | 5000 | EUR

The same query with the date moved past the amendment returns the amended text and the amended threshold, out of the same file:

-- name: as-of-2025-06-01
SELECT
    u.eid,
    u.work_iri,
    u.expression_iri,
    u.valid_from,
    u.valid_to,
    u.own_text                AS text,
    a.id                      AS atom_id,
    a.modality,
    a.actor,
    a.action,
    a.executable_name,
    a.executable_value,
    a.executable_unit,
    a.provenance_quoted
FROM units u
LEFT JOIN atoms a
       ON a.unit_eid = u.eid
      AND a.status = 'active'
      AND a.effective <= '2025-06-01'
      AND (a.expires IS NULL OR '2025-06-01' <= a.expires)
WHERE u.eid = 'art_5__para_3'
  AND u.work_iri = '/akn/eu/act/reg/2024/1'
  AND u.valid_from <= '2025-06-01'
  AND (u.valid_to IS NULL OR '2025-06-01' <= u.valid_to)
ORDER BY a.id;

art_5__para_3 | 2025-03-01 | NULL | Operators must report to the authority any
incident causing losses above EUR 10000. | FIXREG-Art5.3-Ob1 | MUST | ... | 10000 | EUR

The date is the only thing that moved. That is the whole point of shipping windows rather than a snapshot: one file, any date in the corpus’s range, one answer.

A whole article, in reading order

own_text is a provision’s own words; text is it plus everything nested inside it. To read Article 5 as a person would, ask for the article’s own row:

-- name: article-as-of
SELECT u.eid, u.valid_from, u.valid_to, u.text
FROM units u
WHERE u.eid = 'art_5'
  AND u.work_iri = '/akn/eu/act/reg/2024/1'
  AND u.valid_from <= '2025-06-01'
  AND (u.valid_to IS NULL OR '2025-06-01' <= u.valid_to);

The atoms-for-entity query

“Which obligations bound an entity of this type on that day?” This is the query the serving layer’s applicability filter is built on.

An entity profile (fixtures/profiles/operator.yaml) names an entity_type; atoms.applies_to is a JSON array of entity-type ids, so the join goes through json_each. For profile operator at 2025-06-01:

-- name: atoms-for-entity
SELECT
    a.id,
    a.unit_eid,
    a.modality,
    a.actor,
    a.action,
    a.effective,
    a.expires,
    a.executable_name,
    a.executable_value
FROM atoms a
JOIN json_each(a.applies_to) applies
WHERE applies.value = 'operator'
  AND a.status = 'active'
  AND a.effective <= '2025-06-01'
  AND (a.expires IS NULL OR '2025-06-01' <= a.expires)
ORDER BY a.id;

FIXREG-Art5.2-Ob1  | art_5__para_2 | MUST   | ... |            |
FIXREG-Art5.3-Ob1  | art_5__para_3 | MUST   | ... | ..._eur    | 10000
FIXREG-Art5a-Ob1   | art_5a        | SHOULD | ... |            |

The pre-amendment version of FIXREG-Art5.3-Ob1 is in the bundle too and is not returned: it expired on 2025-02-28. Move the date to 2025-01-15 and the row comes back carrying 5000, while FIXREG-Art5a-Ob1 disappears, because Article 5a did not exist yet.

The same answer can be reached through the graph rather than through the atom’s own column, which is what a caller that already holds a node does:

-- name: atoms-for-entity-via-edges
SELECT a.id, a.modality, a.unit_eid
FROM edges e
JOIN atoms a ON a.id = e.src
WHERE e.type = 'applies_to'
  AND e.dst = 'operator'
  AND a.status = 'active'
  AND a.effective <= '2025-06-01'
  AND (a.expires IS NULL OR '2025-06-01' <= a.expires)
ORDER BY a.id;

Derogations

A narrower entity type inherits its parent’s obligations — small_operator declares operator as its parent in spec/taxonomies/entity-types.yaml — and a provision takes some of them back off it. Those are exempts edges, whose attrs name the types exempted:

-- name: derogations
SELECT
    e.src                                   AS derogating_unit,
    e.dst                                   AS disapplied_unit,
    json_extract(e.attrs, '$.applies_to')   AS exempted_types
FROM edges e
JOIN units u ON u.node = e.src
WHERE e.type = 'exempts'
  AND u.valid_from <= '2025-06-01'
  AND (u.valid_to IS NULL OR '2025-06-01' <= u.valid_to)
ORDER BY e.src;

Subtracting them gives the applicable set for a small operator on the same day — Article 9 disapplies Article 5(2), so FIXREG-Art5.2-Ob1 drops out while the other two stay:

-- name: atoms-for-small-operator
SELECT a.id, a.unit_eid, a.modality
FROM atoms a
JOIN json_each(a.applies_to) applies
WHERE applies.value IN ('operator', 'small_operator')
  AND a.status = 'active'
  AND a.effective <= '2025-06-01'
  AND (a.expires IS NULL OR '2025-06-01' <= a.expires)
  AND a.unit_eid NOT IN (
        SELECT u.eid
        FROM edges e
        JOIN units u ON u.node = e.dst
        WHERE e.type = 'exempts'
          AND json_extract(e.attrs, '$.applies_to') LIKE '%small_operator%'
          AND u.valid_from <= '2025-06-01'
          AND (u.valid_to IS NULL OR '2025-06-01' <= u.valid_to)
  )
ORDER BY a.id;

Executable values

A downstream system usually wants the number, not the sentence. executable_value is JSON, so it is cast where a number is wanted:

-- name: threshold-as-of
SELECT
    a.id,
    a.executable_name,
    CAST(a.executable_value AS INTEGER) AS value,
    a.executable_unit,
    a.effective,
    a.expires,
    a.executable_quoted
FROM atoms a
WHERE a.executable_name = 'incident_report_threshold_eur'
  AND a.effective <= '2025-06-01'
  AND (a.expires IS NULL OR '2025-06-01' <= a.expires);

Every value the regime states, over the whole of time, is the same query without the date — which is how a reader sees that a constant has a history:

-- name: executable-history
SELECT id, executable_name, executable_value, executable_unit, effective, expires
FROM atoms
WHERE executable_name IS NOT NULL
ORDER BY executable_name, effective;

Provenance

Every atom quotes the canon, and the quote is checkable inside the bundle: the provenance_char_start / provenance_char_end offsets index the unit text of the version in force on the atom’s own effective date.

-- name: provenance-check
SELECT
    a.id,
    a.provenance_unit_eid,
    a.provenance_quoted,
    substr(u.own_text, a.provenance_char_start + 1,
           a.provenance_char_end - a.provenance_char_start) AS sliced,
    substr(u.own_text, a.provenance_char_start + 1,
           a.provenance_char_end - a.provenance_char_start) = a.provenance_quoted AS matches
FROM atoms a
JOIN units u
  ON u.eid = a.provenance_unit_eid
 AND u.valid_from <= a.effective
 AND (u.valid_to IS NULL OR a.effective <= u.valid_to)
ORDER BY a.id, a.effective;

SQLite’s substr is 1-based and takes a length; the atom’s offsets are 0-based and are a half-open range, which is what the + 1 and the subtraction are for.

The graph

-- name: definitions-in-force
SELECT
    json_extract(e.attrs, '$.term') AS term,
    e.src                           AS defined_by,
    e.dst                           AS used_by
FROM edges e
JOIN units d ON d.node = e.src
JOIN units s ON s.node = e.dst
WHERE e.type = 'defines'
  AND json_extract(e.attrs, '$.term') = 'operator'
  AND d.valid_from <= '2025-06-01'
  AND (d.valid_to IS NULL OR '2025-06-01' <= d.valid_to)
  AND s.valid_from <= '2025-06-01'
  AND (s.valid_to IS NULL OR '2025-06-01' <= s.valid_to)
ORDER BY e.dst;

-- name: what-amended-this
SELECT
    e.src                                  AS amending_unit,
    e.dst                                  AS amended_unit,
    json_extract(e.attrs, '$.operation')   AS operation,
    json_extract(e.attrs, '$.position')    AS position
FROM edges e
WHERE e.type = 'modifies'
ORDER BY e.src, e.dst;

-- name: inbound-citations
SELECT e.src AS citing_unit, e.dst AS cited_unit
FROM edges e
JOIN units s ON s.node = e.src
WHERE e.type = 'cites'
  AND e.dst LIKE '%art_5__para_3'
  AND s.valid_from <= '2025-06-01'
  AND (s.valid_to IS NULL OR '2025-06-01' <= s.valid_to)
ORDER BY e.src;

The other time axis

known_at is transaction time — when this corpus learned the text, taken from the capture’s retrieved_at and never from a clock. It is what makes a past answer reproducible rather than re-derived: “what would this corpus have said on that day” is a different question from “what was the law on that day”.

-- name: transaction-time
SELECT node, valid_from, valid_to, known_at
FROM units
WHERE eid = 'art_5__para_3'
  AND known_at <= '2026-01-01T00:00:00Z'
ORDER BY node;

An integrity check you can run before trusting the file

release_info records the row counts the builder wrote. They are cheap to re-derive, and a mismatch means the file is not the artifact the manifest describes — check the sha256 in release-meta.yaml before going further.

-- name: self-check
SELECT
    (SELECT value FROM release_info WHERE key = 'count_units')       AS declared_units,
    (SELECT COUNT(*) FROM units)                                     AS actual_units,
    (SELECT value FROM release_info WHERE key = 'count_atoms')       AS declared_atoms,
    (SELECT COUNT(*) FROM atoms)                                     AS actual_atoms,
    (SELECT value FROM release_info WHERE key = 'count_edges')       AS declared_edges,
    (SELECT COUNT(*) FROM edges)                                     AS actual_edges,
    (SELECT value FROM release_info WHERE key = 'count_expressions') AS declared_expressions,
    (SELECT COUNT(*) FROM expressions)                               AS actual_expressions,
    (SELECT value FROM release_info WHERE key = 'count_modifications') AS declared_modifications,
    (SELECT COUNT(*) FROM modifications)                             AS actual_modifications;

Verifying the signature over release-meta.yaml, the digests it declares, the provenance chain and transparency-log inclusion is openregs verify --release <regime>@<tag> — see docs/runbooks/release-sign.md. Nothing on this page substitutes for it: these queries assume you already trust the bytes.