Documentation · Data model
The data model
Everything on this site is stored in one star schema. Dimensions describe things: records, the people and organisations credited for them, vocabulary terms, roles, sources, checks, AI models and dates. Facts record what happened between them: a record uses another, someone contributed to a record in a role, a record carries a tag, a check ran on a study, a specialist called a model. The analytics shown in every Insights tab are aggregates computed from the facts alone.
The schema
Facts in the middle, dimensions on both sides. A line is a foreign key; dashed lines link one dimension to another. Select a table to jump to its columns.
dimension fact aggregate (analytics)
Rules
- ·Every fact names its grain: what one row stands for.
- ·Keys are the record and person identifiers used across the site, so a row can be followed to its page.
- ·Every fact and every example record carries is_example. Real figures filter on it; nothing invented reaches a real figure.
- ·Every tag records where it comes from (via: declared, mapped, inferred, default).
- ·Every record has at least one row in fact_contribution: nothing is published without its creators.
- ·The build stops if a foreign key does not resolve.
Download
The whole database as one SQLite file: e2er.sqlite, with every table and view; the queries below run on it as written. Each star-schema table also as CSV and JSON.
| dim_record | 796 | CSV · JSON |
| dim_party | 241 | CSV · JSON |
| dim_term | 56 | CSV · JSON |
| dim_role | 15 | CSV · JSON |
| dim_source | 10 | CSV · JSON |
| dim_check | 7 | CSV · JSON |
| dim_model | 1 | CSV · JSON |
| dim_date | 33 | CSV · JSON |
| fact_use | 423 | CSV · JSON |
| fact_contribution | 1269 | CSV · JSON |
| fact_tag | 2446 | CSV · JSON |
| fact_check | 162 | CSV · JSON |
| fact_ai_usage | 17 | CSV · JSON |
| dim_dossier | 32 | CSV · JSON |
| fact_workflow_step | 39 | CSV · JSON |
| fact_dossier_component | 148 | CSV · JSON |
| agg_record_reach | 796 | CSV · JSON |
| agg_party_reach | 241 | CSV · JSON |
Dimensions
dim_record · 796 rows · One row per record: study, template, specialist, skill, dataset or external tool.
| Column | Type | Description | Key to |
|---|---|---|---|
| record_id | text | Key, e.g. skill:e2er/econometrics/did or run:bhanneke/spot-bitcoin-etf-comovement | |
| kind | text | study (run), template, specialist, skill, dataset, pipeline | |
| name | text | Title or name | |
| status | text | in-place, prototype, planned, illustration | |
| is_example | bool | Invented record of the demonstrator | |
| source_id | text | Where the record comes from | dim_source |
| collection | text | Pack, folder or collection within the source | |
| license | text | Licence as the source declares it | |
| version | text | Version, where the source has one | |
| published_date_id | date | Publication date of a study | dim_date |
| href | text | Page on the site |
dim_party · 241 rows · One row per person, group or organisation credited anywhere.
| Column | Type | Description | Key to |
|---|---|---|---|
| party_id | text | Key: person:<id> for profiles, creator:<name> for creators named by a source | |
| name | text | Name | |
| party_type | text | researcher, group or creator | |
| is_organisation | bool | A group, lab or company | |
| is_example | bool | Invented example researcher | |
| has_profile | bool | Has an E2ER profile (else credited by its source only) | |
| orcid | text | ORCID iD where known | |
| github | text | GitHub login where known | |
| affiliation | text | First recorded affiliation |
dim_term · 56 rows · One row per term of the controlled vocabularies.
| Column | Type | Description | Key to |
|---|---|---|---|
| term_id | text | Key: <facet>:<term> | |
| facet | text | discipline, method, design, stage, kind, status | |
| term | text | Term id | |
| label | text | Label | |
| broader | text | Broader term | dim_term |
| origin | text | Vocabulary origin (RISE, E2ER proposal) |
dim_role · 15 rows · One row per CRediT contributor role.
| Column | Type | Description | Key to |
|---|---|---|---|
| role_id | text | Key: CRediT role id, or unspecified | |
| label | text | Label |
dim_source · 10 rows · One row per source E2ER reads.
| Column | Type | Description | Key to |
|---|---|---|---|
| source_id | text | Key | |
| label | text | Label | |
| url | text | Address |
dim_check · 7 rows · One row per kind of check.
| Column | Type | Description | Key to |
|---|---|---|---|
| check_id | text | Key | |
| performed_by | text | author (the author's own e2er verify) or e2er (the site's re-check) | |
| description | text | What it checks |
dim_model · 1 rows · One row per AI backend and model that a published study recorded.
| Column | Type | Description | Key to |
|---|---|---|---|
| model_id | text | Key: <backend>/<model> | |
| backend | text | Backend | |
| model | text | Model |
dim_date · 33 rows · One row per calendar day that occurs in a fact.
| Column | Type | Description | Key to |
|---|---|---|---|
| date_id | date | Key: YYYY-MM-DD | |
| year | int | Year | |
| quarter | int | Quarter | |
| month | int | Month |
dim_dossier · 32 rows · One row per study dossier: the settings a study was produced with, addressed by the SHA-256 of their canonical JSON.
| Column | Type | Description | Key to |
|---|---|---|---|
| dossier_id | text | Key: sha256 of the canonical dossier JSON; resolves at /d/<short_id> | |
| short_id | text | First 16 hex characters, used in the link | |
| record_id | text | The study | dim_record |
| e2er_version | text | E2ER version the run recorded | |
| e2er_commit | text | E2ER commit the components are pinned at | |
| template | text | Template | |
| mode | text | Run mode | |
| governance | text | Governance setting | |
| backend | text | AI backend | |
| models | text | Models used, separated by ; | |
| is_example | bool | Example study |
Facts
fact_use · 423 rows · One row per directed dependency: a record uses, extends or forks another.
| Column | Type | Description | Key to |
|---|---|---|---|
| from_record_id | text | The record that uses | dim_record |
| to_record_id | text | The record used | dim_record |
| relation | text | uses, extends (study → study), forkOf (template → template) | |
| is_example | bool | Either end is an example | |
| date_id | date | Publication date of the using study | dim_date |
fact_contribution · 1269 rows · One row per party, record and role: who is credited for what.
| Column | Type | Description | Key to |
|---|---|---|---|
| party_id | text | Who | dim_party |
| record_id | text | What | dim_record |
| role_id | text | CRediT role | dim_role |
| is_example | bool | Record or party is an example |
fact_tag · 2446 rows · One row per tag on a record, with where the tag comes from.
| Column | Type | Description | Key to |
|---|---|---|---|
| record_id | text | Record | dim_record |
| term_id | text | Term | dim_term |
| via | text | declared, mapped, inferred or default | |
| rule | text | Inference rule or source term |
fact_check · 162 rows · One row per check run on a study, by its author or by E2ER.
| Column | Type | Description | Key to |
|---|---|---|---|
| record_id | text | Study | dim_record |
| check_id | text | Check | dim_check |
| status | text | PASS, FAIL or SKIP | |
| checked | int | Items checked, where stated | |
| total | int | Items in total, where stated | |
| detail | text | Detail as reported | |
| date_id | date | When | dim_date |
| is_example | bool | Invented check of an example study |
fact_ai_usage · 17 rows · One row per study, specialist and model: AI calls recorded by the run.
| Column | Type | Description | Key to |
|---|---|---|---|
| record_id | text | Study | dim_record |
| agent_record_id | text | The specialist as a record, where it exists | dim_record |
| agent | text | Specialist name as recorded | |
| model_id | text | Backend and model | dim_model |
| calls | int | Calls | |
| input_tokens | int | Input tokens | |
| output_tokens | int | Output tokens | |
| cost_usd | real | Cost in USD (0 on subscription backends) | |
| date_id | date | First call | dim_date |
| is_example | bool | Example study |
fact_workflow_step · 39 rows · One row per step of a study's run, from its dossier: the specialist or check, the model, whether it was accepted, and the intermediate file it wrote with its SHA-256.
| Column | Type | Description | Key to |
|---|---|---|---|
| dossier_id | text | Dossier | dim_dossier |
| step_no | int | Position in the run | |
| step_type | text | specialist or check | |
| phase | text | Phase of the template | |
| agent_record_id | text | Specialist | dim_record |
| name | text | Specialist or check name | |
| model_id | text | Model | dim_model |
| accepted | bool | Step accepted, or check passed | |
| output_file | text | Intermediate file the step wrote | |
| output_sha256 | text | SHA-256 of that file in the exported folder | |
| exported | bool | The file is in the exported folder | |
| is_example | bool | Example study |
fact_dossier_component · 148 rows · One row per component pinned in a dossier: which version of a template, specialist, skill or connector a study used.
| Column | Type | Description | Key to |
|---|---|---|---|
| dossier_id | text | Dossier | dim_dossier |
| record_id | text | Component | dim_record |
| pin_kind | text | git_blob (content hash of the file at the commit), example_sha256, source_address, or none | |
| pin_value | text | The hash or address | |
| path | text | File path in the repository | |
| is_example | bool | Example study |
Analytics
The Insights tab of every page shows these figures; they are computed from fact_use and fact_contribution. A study reaches a record when it uses it directly or through a chain of uses (study → template → specialist → skill). For people, studies credited to themselves or to their groups are left out.
agg_record_reach · 796 rows · One row per record: how far it reaches. Computed from fact_use and fact_contribution.
| Column | Type | Description | Key to |
|---|---|---|---|
| record_id | text | Record | dim_record |
| direct_real | int | Real records that use it directly | |
| direct_example | int | Example records that use it directly | |
| studies_real | int | Real studies that reach it directly or through a chain | |
| studies_example | int | Example studies that reach it | |
| parties | int | Parties credited for those studies |
agg_party_reach · 241 rows · One row per party: how far their work reaches, own party excluded. Computed from fact_use and fact_contribution.
| Column | Type | Description | Key to |
|---|---|---|---|
| party_id | text | Party | dim_party |
| works | int | Records they are credited for | |
| studies_by_others_real | int | Real studies by others that reach their work | |
| studies_by_others_example | int | Example studies by others that reach their work | |
| reused_by_parties | int | Other parties credited for those studies |
Named queries
Every figure on this site is the result of one of these queries, run against the database of this build. Open e2er.sqlite in any SQLite client to rerun them; a parameter such as :study takes the id shown on the page.
checks_by_performer · Checks by who performed them, real studies only
Shown on: data-model page (SQL listed with its result)
SELECT k.performed_by, f.check_id, f.status, COUNT(*) AS n FROM fact_check f JOIN dim_check k ON k.check_id = f.check_id WHERE f.is_example = 0 GROUP BY k.performed_by, f.check_id, f.status ORDER BY k.performed_by, f.check_id;
| performed_by | check_id | status | n |
|---|---|---|---|
| author | citations | PASS | 1 |
| author | integrity | PASS | 1 |
| author | numbers | PASS | 1 |
| author | spec | PASS | 1 |
| author | tables | PASS | 1 |
| e2er | e2er.content_id | PASS | 1 |
| e2er | e2er.files | PASS | 1 |
creator_figures · Work of one creator credited by an external source, by kind
Shown on: creator pages (section headings), /people (creators list) · Parameters: :creator
WITH w AS (SELECT DISTINCT coalesce(r.id, s.id) AS id, coalesce(r.kind, 'run') AS kind
FROM contribution c JOIN party p ON p.id = c.party_id AND p.public_id = :creator
LEFT JOIN record r ON r.id = c.record_id LEFT JOIN study s ON s.id = c.study_id
WHERE c.state <> 'declined' AND c.workspace_id IS NULL)
SELECT count(*) AS works,
sum(kind = 'skill') AS skills, sum(kind = 'pipeline') AS tools, sum(kind = 'dataset') AS datasets,
sum(kind = 'template') AS templates, sum(kind = 'agent') AS specialists, sum(kind = 'run') AS studies
FROM w; credit_for_study_parts · Who is credited for the blocks a study uses directly, with their roles
Shown on: data-model page (SQL listed with its result)
SELECT p.name, p.party_type, COUNT(DISTINCT c.record_id) AS blocks, GROUP_CONCAT(DISTINCT c.role_id) AS roles FROM fact_use u JOIN fact_contribution c ON c.record_id = u.to_record_id JOIN dim_party p ON p.party_id = c.party_id WHERE u.from_record_id = 'run:bhanneke/spot-bitcoin-etf-comovement' GROUP BY p.name, p.party_type ORDER BY blocks DESC;
| name | party_type | blocks | roles |
|---|---|---|---|
| E2ER contributors | group | 58 | methodology,software |
datamodel_rows · Rows in each table of the star schema
Shown on: /documentation/data-model (tables and their rows)
SELECT 'dim_record' AS tbl, count(*) AS n FROM dim_record UNION ALL SELECT 'dim_party', count(*) FROM dim_party UNION ALL SELECT 'dim_term', count(*) FROM dim_term UNION ALL SELECT 'dim_role', count(*) FROM dim_role UNION ALL SELECT 'dim_source', count(*) FROM dim_source UNION ALL SELECT 'dim_check', count(*) FROM dim_check UNION ALL SELECT 'dim_model', count(*) FROM dim_model UNION ALL SELECT 'dim_date', count(*) FROM dim_date UNION ALL SELECT 'dim_dossier', count(*) FROM dim_dossier UNION ALL SELECT 'fact_use', count(*) FROM fact_use UNION ALL SELECT 'fact_contribution', count(*) FROM fact_contribution UNION ALL SELECT 'fact_tag', count(*) FROM fact_tag UNION ALL SELECT 'fact_check', count(*) FROM fact_check UNION ALL SELECT 'fact_ai_usage', count(*) FROM fact_ai_usage UNION ALL SELECT 'fact_dossier_component', count(*) FROM fact_dossier_component UNION ALL SELECT 'fact_workflow_step', count(*) FROM fact_workflow_step UNION ALL SELECT 'agg_record_reach', count(*) FROM agg_record_reach UNION ALL SELECT 'agg_party_reach', count(*) FROM agg_party_reach;
datamodel_tables · Tables of the star schema: dimensions, facts and analytics, as views in the database
Shown on: /documentation/data-model (table count)
SELECT count(*) AS tables,
sum(name LIKE 'dim\_%' ESCAPE '\') AS dimensions,
sum(name LIKE 'fact\_%' ESCAPE '\') AS facts,
sum(name LIKE 'agg\_%' ESCAPE '\') AS analytics
FROM sqlite_master WHERE type = 'view' AND (name LIKE 'dim\_%' ESCAPE '\' OR name LIKE 'fact\_%' ESCAPE '\' OR name LIKE 'agg\_%' ESCAPE '\'); dataset_figures · Files and size of one open dataset, as its repository states them
Shown on: dataset pages · Parameters: :dataset
SELECT coalesce(json_extract(v.body, '$.dataset.files'), 0) AS files,
round(coalesce(json_extract(v.body, '$.dataset.bytes'), 0) / 1e6, 1) AS mb,
round(coalesce(json_extract(v.body, '$.dataset.bytes'), 0) / 1e9, 1) AS gb
FROM record r JOIN record_version v ON v.id = r.latest_version_id
WHERE r.public_id = :dataset; dossier_figures · One dossier: steps of the run, checks that stopped a step, and the parts it pins, by kind
Shown on: dossier pages (/d/<short>) · Parameters: :short
WITH d AS (SELECT id, doc FROM dossier WHERE short_id = :short), c AS (SELECT json_extract(x.value, '$.kind') AS kind FROM d, json_each(d.doc, '$.components') x) SELECT (SELECT count(*) FROM d, json_each(d.doc, '$.workflow')) AS steps, (SELECT count(*) FROM d, json_each(d.doc, '$.workflow') w WHERE json_extract(w.value, '$.type') = 'check' AND json_extract(w.value, '$.passed') = 0) AS checks_failed, (SELECT count(*) FROM c) AS parts, (SELECT count(*) FROM c WHERE kind = 'template') AS templates, (SELECT count(*) FROM c WHERE kind = 'agent') AS specialists, (SELECT count(*) FROM c WHERE kind = 'skill') AS skills, (SELECT count(*) FROM c WHERE kind = 'connector') AS connectors;
insights_disciplines · Disciplines of the studies that reach one study or part, real and example apart
Shown on: the Insights tab of every study and part ("Disciplines it reaches") · Parameters: :node
WITH RECURSIVE reach(node) AS ( SELECT from_record_id FROM fact_use WHERE to_record_id = :node UNION SELECT u.from_record_id FROM fact_use u JOIN reach r ON u.to_record_id = r.node), runs AS (SELECT d.record_id, d.is_example FROM reach r JOIN dim_record d ON d.record_id = r.node WHERE d.kind = 'run' AND d.record_id <> :node), disc AS ( SELECT runs.is_example, coalesce(t.term, 'general') AS discipline FROM runs LEFT JOIN fact_tag f ON f.record_id = runs.record_id AND f.term_id LIKE 'discipline:%' LEFT JOIN term t ON t.id = f.term_id) SELECT discipline, sum(is_example = 0) AS real, sum(is_example = 1) AS example FROM disc GROUP BY discipline ORDER BY count(*) DESC, discipline;
insights_party · Insights of one researcher, group or creator: studies by others that reach their work, records that use it directly, researchers behind those studies, what they contributed
Shown on: the Insights tab of profiles and creator pages · Parameters: :node
WITH RECURSIVE
owned AS (SELECT DISTINCT record_id FROM fact_contribution WHERE party_id = :node),
side AS (SELECT own_id FROM v_own WHERE party_id = :node),
reach(node) AS (
SELECT u.from_record_id FROM fact_use u JOIN owned o ON u.to_record_id = o.record_id
UNION SELECT u.from_record_id FROM fact_use u JOIN reach r ON u.to_record_id = r.node),
first_credit AS (
SELECT 'run:' || s.public_id AS record_id,
(SELECT p.public_id FROM contribution c JOIN party p ON p.id = c.party_id WHERE c.study_id = s.id AND c.state <> 'declined' ORDER BY c.position, p.public_id LIMIT 1) AS party_id
FROM study s),
runs AS (SELECT DISTINCT d.record_id, d.is_example, f.party_id AS owner FROM reach r JOIN dim_record d ON d.record_id = r.node AND d.kind = 'run'
LEFT JOIN first_credit f ON f.record_id = d.record_id
WHERE d.record_id NOT IN (SELECT record_id FROM owned) AND coalesce(f.party_id, '') NOT IN (SELECT own_id FROM side)),
direct AS (SELECT DISTINCT u.from_record_id, d.is_example FROM fact_use u JOIN owned o ON u.to_record_id = o.record_id JOIN dim_record d ON d.record_id = u.from_record_id)
SELECT
(SELECT count(*) FROM runs WHERE is_example = 0) AS studies_real,
(SELECT count(*) FROM runs WHERE is_example = 1) AS studies_example,
(SELECT count(*) FROM runs) AS studies_total,
(SELECT count(*) FROM direct WHERE is_example = 0) AS direct_real,
(SELECT count(*) FROM direct WHERE is_example = 1) AS direct_example,
(SELECT count(DISTINCT owner) FROM runs WHERE owner IS NOT NULL) AS researchers,
(SELECT count(*) FROM owned) AS works; insights_party_work · The most reused work of one researcher, group or creator: studies by others that reach each item, top ten
Shown on: the Insights tab of profiles and creator pages ("Their most reused work") · Parameters: :node
WITH RECURSIVE
owned AS (SELECT DISTINCT record_id FROM fact_contribution WHERE party_id = :node),
side AS (SELECT own_id FROM v_own WHERE party_id = :node),
reach(start, node) AS (
SELECT o.record_id, u.from_record_id FROM fact_use u JOIN owned o ON u.to_record_id = o.record_id
UNION SELECT r.start, u.from_record_id FROM fact_use u JOIN reach r ON u.to_record_id = r.node),
first_credit AS (
SELECT 'run:' || s.public_id AS record_id,
(SELECT p.public_id FROM contribution c JOIN party p ON p.id = c.party_id WHERE c.study_id = s.id AND c.state <> 'declined' ORDER BY c.position, p.public_id LIMIT 1) AS party_id
FROM study s),
hits AS (SELECT DISTINCT r.start, d.record_id, d.is_example FROM reach r JOIN dim_record d ON d.record_id = r.node AND d.kind = 'run'
LEFT JOIN first_credit f ON f.record_id = d.record_id
WHERE r.node <> r.start AND coalesce(f.party_id, '') NOT IN (SELECT own_id FROM side))
SELECT start AS record_id, sum(is_example = 0) AS real, sum(is_example = 1) AS example
FROM hits GROUP BY start ORDER BY real DESC, example DESC, start LIMIT 10; insights_record · Insights of one study or part: studies that reach it, records that use it directly, researchers behind those studies, what it builds on
Shown on: the Insights tab of every study and part · Parameters: :node
WITH RECURSIVE
reach(node) AS (
SELECT from_record_id FROM fact_use WHERE to_record_id = :node
UNION SELECT u.from_record_id FROM fact_use u JOIN reach r ON u.to_record_id = r.node),
runs AS (SELECT d.record_id, d.is_example FROM reach r JOIN dim_record d ON d.record_id = r.node WHERE d.kind = 'run' AND d.record_id <> :node),
direct AS (SELECT DISTINCT u.from_record_id, d.is_example FROM fact_use u JOIN dim_record d ON d.record_id = u.from_record_id WHERE u.to_record_id = :node),
first_credit AS (
SELECT 'run:' || s.public_id AS record_id,
(SELECT p.public_id FROM contribution c JOIN party p ON p.id = c.party_id WHERE c.study_id = s.id AND c.state <> 'declined' ORDER BY c.position, p.public_id LIMIT 1) AS party_id
FROM study s)
SELECT
(SELECT count(*) FROM runs WHERE is_example = 0) AS studies_real,
(SELECT count(*) FROM runs WHERE is_example = 1) AS studies_example,
(SELECT count(*) FROM runs) AS studies_total,
(SELECT count(*) FROM direct WHERE is_example = 0) AS direct_real,
(SELECT count(*) FROM direct WHERE is_example = 1) AS direct_example,
(SELECT count(DISTINCT f.party_id) FROM runs JOIN first_credit f ON f.record_id = runs.record_id) AS researchers,
(SELECT count(*) FROM fact_use WHERE from_record_id = :node) AS builds_on; most_reused_parts · Most reused templates, specialists and skills: studies that use them directly, real and example apart
Shown on: data-model page (SQL listed with its result)
SELECT r.kind, r.name,
SUM(CASE WHEN s.is_example = 0 THEN 1 ELSE 0 END) AS real_studies,
SUM(CASE WHEN s.is_example = 1 THEN 1 ELSE 0 END) AS example_studies
FROM fact_use u
JOIN dim_record r ON r.record_id = u.to_record_id
JOIN dim_record s ON s.record_id = u.from_record_id AND s.kind = 'run'
WHERE r.kind IN ('template', 'agent', 'skill')
GROUP BY r.kind, r.name
ORDER BY real_studies DESC, example_studies DESC, r.name
LIMIT 10; | kind | name | real_studies | example_studies |
|---|---|---|---|
| skill | Difference-in-Differences | 1 | 8 |
| skill | Financial Event Studies | 1 | 5 |
| skill | Panel Data Methods | 1 | 4 |
| template | Empirical | 1 | 3 |
| skill | Blockchain Data Skill | 1 | 1 |
| skill | Crypto & DeFi Data Skill | 1 | 1 |
| skill | Data Cleaning Best Practices for Economics Research | 1 | 1 |
| skill | Yahoo Finance via `e2er-data yfinance` | 1 | 1 |
| agent | econometrics_specialist | 1 | 1 |
| agent | identification_strategist | 1 | 1 |
part_usage · Use of one part: published studies, templates, forks and specialists that use it, real and example apart
Shown on: the usage line of every part (template, specialist, skill, connector lists and pages, discover) · Parameters: :part
WITH RECURSIVE
target AS (SELECT id FROM record WHERE public_id = :part),
edge AS (SELECT from_record_id AS f, to_record_id AS t FROM relation
WHERE from_record_id IS NOT NULL AND to_record_id IS NOT NULL AND type NOT IN ('fork_of', 'derived_from') AND workspace_id IS NULL),
study_reach(sid, node) AS (
SELECT from_study_id, to_record_id FROM relation
WHERE from_study_id IS NOT NULL AND to_record_id IS NOT NULL AND type <> 'derived_from' AND workspace_id IS NULL
UNION SELECT r.sid, e.t FROM study_reach r JOIN edge e ON e.f = r.node),
tpl_reach(tid, node) AS (
SELECT e.f, e.t FROM edge e JOIN record x ON x.id = e.f AND x.kind = 'template'
UNION SELECT r.tid, e.t FROM tpl_reach r JOIN edge e ON e.f = r.node),
studies AS (SELECT DISTINCT s.id, s.is_example FROM study_reach r JOIN target ON r.node = target.id JOIN study s ON s.id = r.sid AND s.state = 'published'),
templates AS (SELECT DISTINCT x.id, x.is_example FROM tpl_reach r JOIN target ON r.node = target.id JOIN record x ON x.id = r.tid WHERE x.id <> target.id),
forks AS (SELECT DISTINCT x.id, x.is_example FROM relation rel JOIN target ON rel.to_record_id = target.id JOIN record x ON x.id = rel.from_record_id WHERE rel.type = 'fork_of'),
loaders AS (SELECT DISTINCT x.id, x.is_example FROM edge e JOIN target ON e.t = target.id JOIN record x ON x.id = e.f AND x.kind = 'agent')
SELECT
(SELECT count(*) FROM studies WHERE is_example = 0) AS studies_real,
(SELECT count(*) FROM studies WHERE is_example = 1) AS studies_example,
(SELECT count(*) FROM templates WHERE is_example = 0) AS templates_real,
(SELECT count(*) FROM templates WHERE is_example = 1) AS templates_example,
(SELECT count(*) FROM forks WHERE is_example = 0) AS forks_real,
(SELECT count(*) FROM forks WHERE is_example = 1) AS forks_example,
(SELECT count(*) FROM loaders WHERE is_example = 0) AS specialists_real,
(SELECT count(*) FROM loaders WHERE is_example = 1) AS specialists_example; people_counts · Contributors on E2ER: researchers, groups, example researchers, creators, reuse relations
Shown on: /people (status line, graph heading, table heading, creators heading), /data
WITH owner_of AS (SELECT s.id AS study_id, s.is_example, p.public_id AS owner FROM study s JOIN party p ON p.id = s.owner_party_id WHERE s.state = 'published' AND s.workspace_id IS NULL), edges AS ( SELECT o.owner AS from_id, a.public_id AS to_id, o.is_example FROM relation rel JOIN owner_of o ON o.study_id = rel.from_study_id JOIN contribution c ON c.record_id = rel.to_record_id AND c.basis = 'declared' AND c.is_maintainer = 0 AND c.state <> 'declined' JOIN party a ON a.id = c.party_id WHERE rel.type <> 'derived_from' AND NOT EXISTS (SELECT 1 FROM v_own v WHERE v.party_id = o.owner AND v.own_id = a.public_id) UNION ALL SELECT o.owner, t.owner, o.is_example FROM relation rel JOIN owner_of o ON o.study_id = rel.from_study_id JOIN owner_of t ON t.study_id = rel.to_study_id WHERE rel.type = 'derived_from' AND NOT EXISTS (SELECT 1 FROM v_own v WHERE v.party_id = o.owner AND v.own_id = t.owner)), pairs AS (SELECT from_id, to_id, min(is_example) AS is_example FROM edges GROUP BY from_id, to_id) SELECT (SELECT count(*) FROM party WHERE kind = 'person' AND has_profile = 1 AND is_example = 0) AS researchers_real, (SELECT count(*) FROM party WHERE kind = 'group' AND has_profile = 1 AND is_example = 0) AS groups_real, (SELECT count(*) FROM party WHERE has_profile = 1 AND is_example = 1) AS researchers_example, (SELECT count(*) FROM party WHERE has_profile = 1) AS profiles, (SELECT count(*) FROM party WHERE has_profile = 0) AS creators, (SELECT count(*) FROM pairs WHERE is_example = 0) AS reuse_pairs_real, (SELECT count(*) FROM pairs WHERE is_example = 1) AS reuse_pairs_example;
persona_tiers · Field specialists of the theory lab per tier of distance from information systems
Shown on: /templates/theory-building (tiers)
SELECT json_extract(v.body, '$.persona.tier') AS tier, count(*) AS field_specialists FROM record r JOIN record_version v ON v.id = r.latest_version_id WHERE r.public_id LIKE 'agent:e2et/persona/%' GROUP BY tier ORDER BY tier;
profile_figures · Figures of one researcher or group: studies, parts by kind, studies by others that use or extend their work
Shown on: profile pages (figures, section counts), /people (cards), /signin (example researchers) · Parameters: :party_id
WITH me AS (SELECT id, public_id FROM party WHERE public_id = :party_id),
mine AS (SELECT DISTINCT c.record_id, r.kind, r.is_example FROM contribution c JOIN me ON c.party_id = me.id JOIN record r ON r.id = c.record_id
WHERE c.basis = 'declared' AND c.is_maintainer = 0 AND c.state <> 'declined' AND c.workspace_id IS NULL),
own AS (SELECT s.id, s.is_example FROM study s JOIN me ON s.owner_party_id = me.id WHERE s.state = 'published' AND s.workspace_id IS NULL),
others AS (SELECT s.id, s.is_example FROM study s JOIN party o ON o.id = s.owner_party_id
WHERE s.state = 'published' AND s.workspace_id IS NULL
AND NOT EXISTS (SELECT 1 FROM v_own v WHERE v.party_id = :party_id AND v.own_id = o.public_id)),
using_ AS (SELECT DISTINCT x.id, x.is_example FROM others x JOIN relation rel ON rel.from_study_id = x.id AND rel.type <> 'derived_from' JOIN mine ON mine.record_id = rel.to_record_id),
extending AS (SELECT DISTINCT x.id, x.is_example FROM others x JOIN relation rel ON rel.from_study_id = x.id AND rel.type = 'derived_from' JOIN own ON own.id = rel.to_study_id)
SELECT
(SELECT count(*) FROM own WHERE is_example = 0) AS studies_real,
(SELECT count(*) FROM own WHERE is_example = 1) AS studies_example,
(SELECT count(*) FROM own) AS studies_total,
(SELECT count(*) FROM using_ WHERE is_example = 0) AS used_by_others_real,
(SELECT count(*) FROM using_ WHERE is_example = 1) AS used_by_others_example,
(SELECT count(*) FROM using_) AS used_by_others_total,
(SELECT count(*) FROM extending WHERE is_example = 0) AS extended_real,
(SELECT count(*) FROM extending WHERE is_example = 1) AS extended_example,
(SELECT count(*) FROM mine WHERE kind = 'template') AS templates,
(SELECT count(*) FROM mine WHERE kind = 'agent') AS specialists,
(SELECT count(*) FROM mine WHERE kind = 'skill') AS skills,
(SELECT count(*) FROM mine WHERE kind = 'connector') AS connectors,
(SELECT count(*) FROM (SELECT id FROM using_ UNION SELECT id FROM extending)) AS studies_reusing; profile_parts_contributed · Templates, specialists, skills or connectors a person is credited for
Shown on: profile pages (/<github>, /people/<id>), figure "templates, specialists, skills or connectors contributed" · Parameters: :party_id
SELECT count(DISTINCT r.id) AS parts
FROM contribution c
JOIN party p ON p.id = c.party_id
JOIN record r ON r.id = c.record_id
WHERE p.public_id = :party_id
AND c.basis = 'declared' AND c.state <> 'declined' AND c.workspace_id IS NULL
AND r.kind IN ('template', 'agent', 'skill', 'connector'); reach_of_a_record · Reach of a record through chains of use (recursive)
Shown on: data-model page (SQL listed with its result)
WITH RECURSIVE reach(record_id) AS ( SELECT from_record_id FROM fact_use WHERE to_record_id = 'skill:e2er/econometrics/did' UNION SELECT u.from_record_id FROM fact_use u JOIN reach r ON u.to_record_id = r.record_id ) SELECT r.is_example, COUNT(*) AS studies FROM reach JOIN dim_record r ON r.record_id = reach.record_id WHERE r.kind = 'run' GROUP BY r.is_example;
| is_example | studies |
|---|---|
| 0 | 1 |
| 1 | 18 |
reuse_pairs · Reuse relations: each researcher and contributor whose work their studies use or extend, with the studies and parts that carry it
Shown on: /people (table of reuse relations)
WITH owner_of AS (SELECT s.id AS study_id, s.public_id, s.is_example, p.public_id AS owner FROM study s JOIN party p ON p.id = s.owner_party_id WHERE s.state = 'published' AND s.workspace_id IS NULL),
edges AS (
SELECT o.owner AS from_id, a.public_id AS to_id, o.public_id AS study, r.public_id AS via, o.is_example
FROM relation rel JOIN owner_of o ON o.study_id = rel.from_study_id JOIN record r ON r.id = rel.to_record_id
JOIN contribution c ON c.record_id = rel.to_record_id AND c.basis = 'declared' AND c.is_maintainer = 0 AND c.state <> 'declined'
JOIN party a ON a.id = c.party_id
WHERE rel.type <> 'derived_from' AND NOT EXISTS (SELECT 1 FROM v_own v WHERE v.party_id = o.owner AND v.own_id = a.public_id)
UNION ALL
SELECT o.owner, t.owner, o.public_id, 'run:' || t.public_id, o.is_example
FROM relation rel JOIN owner_of o ON o.study_id = rel.from_study_id JOIN owner_of t ON t.study_id = rel.to_study_id
WHERE rel.type = 'derived_from' AND NOT EXISTS (SELECT 1 FROM v_own v WHERE v.party_id = o.owner AND v.own_id = t.owner))
SELECT from_id, to_id, min(is_example) AS is_example,
(SELECT group_concat(study, ' ') FROM (SELECT DISTINCT study FROM edges e2 WHERE e2.from_id = e.from_id AND e2.to_id = e.to_id ORDER BY study)) AS studies,
count(DISTINCT via) AS through
FROM edges e GROUP BY from_id, to_id ORDER BY min(is_example), from_id, to_id; site_counts · Studies and parts across the site, real and example counted apart
Shown on: home (tiles, map caption), /build (cards), /research (figures), /specialists, /skills, /connectors, /datasets, /data
WITH
parts AS (SELECT kind, source_id, status, is_example, public_id FROM record WHERE workspace_id IS NULL AND withdrawn_at IS NULL),
studies AS (SELECT * FROM study WHERE workspace_id IS NULL AND state = 'published'),
owner_of AS (SELECT s.id AS study_id, p.public_id AS owner FROM studies s JOIN party p ON p.id = s.owner_party_id),
use_edges AS (
SELECT DISTINCT s.id AS study_id, s.is_example, rel.to_record_id, a.public_id AS author
FROM relation rel
JOIN studies s ON s.id = rel.from_study_id
JOIN contribution c ON c.record_id = rel.to_record_id AND c.basis = 'declared' AND c.is_maintainer = 0 AND c.state <> 'declined'
JOIN party a ON a.id = c.party_id
JOIN owner_of o ON o.study_id = s.id
WHERE rel.type <> 'derived_from' AND rel.to_record_id IS NOT NULL
AND NOT EXISTS (SELECT 1 FROM v_own v WHERE v.party_id = o.owner AND v.own_id = a.public_id)),
extend_edges AS (
SELECT s.id AS study_id, s.is_example
FROM relation rel
JOIN studies s ON s.id = rel.from_study_id
JOIN owner_of o ON o.study_id = rel.from_study_id
JOIN owner_of t ON t.study_id = rel.to_study_id
WHERE rel.type = 'derived_from' AND NOT EXISTS (SELECT 1 FROM v_own v WHERE v.party_id = o.owner AND v.own_id = t.owner))
SELECT
(SELECT count(*) FROM studies WHERE is_example = 0) AS studies_real,
(SELECT count(*) FROM studies WHERE is_example = 1) AS studies_example,
(SELECT count(*) FROM studies s WHERE s.is_example = 0
AND EXISTS (SELECT 1 FROM check_result c WHERE c.study_version_id = s.latest_version_id AND c.check_id = 'e2er.files' AND c.status = 'PASS')
AND EXISTS (SELECT 1 FROM check_result c WHERE c.study_version_id = s.latest_version_id AND c.check_id = 'e2er.content_id' AND c.status = 'PASS')) AS studies_checked,
(SELECT count(DISTINCT s.id) FROM studies s JOIN relation r ON r.from_study_id = s.id AND r.type = 'derived_from' WHERE s.is_example = 0) AS studies_extending_real,
(SELECT count(DISTINCT s.id) FROM studies s JOIN relation r ON r.from_study_id = s.id AND r.type = 'derived_from' WHERE s.is_example = 1) AS studies_extending_example,
(SELECT count(*) FROM use_edges WHERE is_example = 0) + (SELECT count(*) FROM extend_edges WHERE is_example = 0) AS reuse_real,
(SELECT count(*) FROM use_edges WHERE is_example = 1) + (SELECT count(*) FROM extend_edges WHERE is_example = 1) AS reuse_example,
(SELECT count(*) FROM parts WHERE kind = 'template' AND status <> 'illustration') AS templates_real,
(SELECT count(*) FROM parts WHERE kind = 'template' AND status = 'illustration') AS templates_example,
(SELECT count(*) FROM parts WHERE kind = 'agent' AND status <> 'illustration') AS specialists_real,
(SELECT count(*) FROM parts WHERE kind = 'agent' AND status = 'illustration') AS specialists_example,
(SELECT count(*) FROM parts WHERE kind = 'agent' AND source_id = 'e2er') AS specialists_e2er,
(SELECT count(*) FROM parts WHERE kind = 'agent' AND source_id = 'e2et' AND public_id NOT LIKE 'agent:e2et/persona/%') AS specialists_theory_core,
(SELECT count(*) FROM parts WHERE kind = 'agent' AND public_id LIKE 'agent:e2et/persona/%') AS specialists_field,
(SELECT count(DISTINCT json_extract(v.body, '$.persona.tier')) FROM record r JOIN record_version v ON v.id = r.latest_version_id WHERE r.public_id LIKE 'agent:e2et/persona/%') AS specialist_tiers,
(SELECT count(*) FROM parts WHERE kind = 'connector' AND status <> 'illustration') AS connectors_real,
(SELECT count(*) FROM parts WHERE kind = 'connector' AND status = 'illustration') AS connectors_example,
(SELECT count(*) FROM parts WHERE kind = 'connector' AND status = 'in-place') AS connectors_in_place,
(SELECT count(*) FROM parts WHERE kind = 'connector' AND status = 'planned') AS connectors_planned,
(SELECT count(*) FROM parts WHERE kind = 'skill' AND status <> 'illustration') AS skills_real,
(SELECT count(*) FROM parts WHERE kind = 'skill' AND status = 'illustration') AS skills_example,
(SELECT count(*) FROM parts WHERE kind = 'skill' AND source_id = 'e2er') AS skills_e2er,
(SELECT count(*) FROM parts WHERE kind = 'skill' AND source_id = 'rise-skills') AS skills_rise,
(SELECT count(DISTINCT r.collection_id) FROM record r WHERE r.kind = 'skill' AND r.source_id = 'rise-skills') AS rise_packs,
(SELECT count(*) FROM parts WHERE kind = 'dataset' AND status <> 'illustration') AS datasets_real,
(SELECT count(*) FROM parts WHERE kind = 'pipeline' AND status <> 'illustration') AS tools_real,
(SELECT count(*) FROM parts WHERE kind = 'skill') AS skill_records,
(SELECT count(*) FROM parts WHERE kind = 'pipeline') AS tool_records,
(SELECT count(*) FROM dim_record) AS records; skill_packs · Skills per project that publishes them (E2ER's bundled skills, RISE projects, example skills)
Shown on: /skills (projects table)
SELECT CASE r.source_id WHEN 'e2er' THEN 'e2er' WHEN 'illustrative' THEN 'illustrative' ELSE c.name END AS pack, count(*) AS skills FROM record r LEFT JOIN collection c ON c.id = r.collection_id WHERE r.kind = 'skill' AND r.workspace_id IS NULL GROUP BY pack ORDER BY pack;
specialist_groups · E2ER's specialists per group of the research process
Shown on: /specialists (group filters)
SELECT json_extract(v.body, '$.specialist.group') AS grp, count(*) AS specialists FROM record r JOIN record_version v ON v.id = r.latest_version_id WHERE r.kind = 'agent' AND r.source_id = 'e2er' AND r.workspace_id IS NULL GROUP BY grp ORDER BY grp;
study_ai_usage · AI use of one study per specialist: calls and tokens, in the order the run recorded them
Shown on: study pages, AI use table · Parameters: :study
SELECT u.agent, u.backend, u.model, u.calls, u.input_tokens + u.output_tokens AS tokens FROM ai_usage u JOIN study s ON s.latest_version_id = u.study_version_id WHERE s.public_id = :study ORDER BY u.first_call_at, u.agent;
study_figures · Figures of one study: files, references, table values, specialists, AI use, studies that extend it
Shown on: study pages (verdict line, figures, how it was produced, AI use, lists), /research rows, home cards, /publish · Parameters: :study
WITH s AS (SELECT * FROM study WHERE public_id = :study),
v AS (SELECT sv.* FROM study_version sv JOIN s ON sv.id = s.latest_version_id),
uses AS (SELECT r.kind FROM relation rel JOIN s ON rel.from_study_id = s.id JOIN record r ON r.id = rel.to_record_id WHERE rel.type <> 'derived_from')
SELECT
(SELECT total FROM check_result c, v WHERE c.study_version_id = v.id AND c.check_id = 'e2er.files' AND c.run_by = 'platform') AS files_listed,
(SELECT checked FROM check_result c, v WHERE c.study_version_id = v.id AND c.check_id = 'e2er.files' AND c.run_by = 'platform') AS files_matched,
(SELECT provenance_files FROM v) AS files_fingerprinted,
(SELECT coalesce(json_extract(provenance_edges, '$.table_cell'), 0) FROM v) AS table_values,
(SELECT coalesce(json_extract(provenance_edges, '$.figure'), 0) FROM v) AS figures,
(SELECT count(*) FROM study_reference r, v WHERE r.study_version_id = v.id) AS references_total,
(SELECT count(*) FROM study_reference r, v WHERE r.study_version_id = v.id AND r.status LIKE 'verified%') AS references_resolved,
(SELECT count(*) FROM study_file f, v WHERE f.study_version_id = v.id AND f.role = 'data') AS data_files,
(SELECT count(*) FROM study_file f, v WHERE f.study_version_id = v.id AND f.role = 'output') AS output_files,
(SELECT count(*) FROM study_file f, v WHERE f.study_version_id = v.id AND f.role = 'output' AND f.path LIKE 'results/%') AS result_files,
(SELECT count(*) FROM study_file f, v WHERE f.study_version_id = v.id AND f.role = 'output' AND f.path LIKE 'paper/%') AS paper_files,
(SELECT count(*) FROM study_file f, v WHERE f.study_version_id = v.id AND f.role = 'output' AND f.path LIKE 'replication/%') AS replication_files,
(SELECT count(*) FROM study_file f, v WHERE f.study_version_id = v.id AND f.role IN ('data', 'output')) AS data_and_outputs,
(SELECT count(*) FROM uses WHERE kind = 'template') AS templates_used,
(SELECT count(*) FROM uses WHERE kind = 'agent') AS specialists_used,
(SELECT count(*) FROM uses WHERE kind = 'skill') AS skills_used,
(SELECT count(*) FROM uses WHERE kind <> 'connector') AS parts_cited,
(SELECT count(*) FROM uses WHERE kind = 'connector') AS connectors_used,
(SELECT coalesce(sum(calls), 0) FROM ai_usage u, v WHERE u.study_version_id = v.id) AS ai_calls,
(SELECT coalesce(sum(input_tokens + output_tokens), 0) FROM ai_usage u, v WHERE u.study_version_id = v.id) AS ai_tokens,
(SELECT round(coalesce(sum(CASE WHEN cost_public = 1 THEN cost_micro_usd END), 0) / 1000000.0, 2) FROM ai_usage u, v WHERE u.study_version_id = v.id) AS ai_cost_usd,
(SELECT count(DISTINCT agent) FROM ai_usage u, v WHERE u.study_version_id = v.id AND u.agent NOT LIKE 'strategist%') AS specialists_recorded,
(SELECT count(*) FROM relation rel, s WHERE rel.to_study_id = s.id AND rel.type = 'derived_from') AS extended_by; tag_provenance · Tags by facet and by how they were assigned (declared, mapped, inferred, default)
Shown on: /data (provenance of tags)
SELECT t.facet, f.via, count(*) AS tags
FROM fact_tag f JOIN term t ON t.id = f.term_id
WHERE t.facet IN ('discipline', 'method', 'design', 'stage')
GROUP BY t.facet, f.via ORDER BY t.facet, f.via; term_counts · Records per vocabulary term (studies and parts, real and example together)
Shown on: /data (vocabulary tables, column "Records, real and example")
SELECT substr(f.term_id, 1, instr(f.term_id, ':') - 1) AS facet, substr(f.term_id, instr(f.term_id, ':') + 1) AS term, count(DISTINCT f.record_id) AS records FROM fact_tag f GROUP BY f.term_id UNION ALL SELECT 'kind', kind, count(*) FROM dim_record GROUP BY kind UNION ALL SELECT 'status', status, count(*) FROM dim_record GROUP BY status ORDER BY facet, term;
tokens_per_specialist · Tokens per specialist in the published study
Shown on: data-model page (SQL listed with its result)
SELECT a.agent, m.model, a.calls, a.input_tokens + a.output_tokens AS tokens FROM fact_ai_usage a JOIN dim_model m ON m.model_id = a.model_id WHERE a.is_example = 0 ORDER BY tokens DESC LIMIT 5;
| agent | model | calls | tokens |
|---|---|---|---|
| econometrics_specialist | claude-sonnet-4-5 | 1 | 155053 |
| paper_drafter | claude-sonnet-4-5 | 2 | 148106 |
| replication_packager | claude-sonnet-4-5 | 1 | 68037 |
| data_analyst | claude-sonnet-4-5 | 1 | 66660 |
| technical_reviewer | claude-sonnet-4-5 | 1 | 63241 |