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
Each table as CSV and JSON. Load them into any database or spreadsheet; the queries below run as written in SQLite and DuckDB.
| dim_record | 796 | CSV · JSON |
| dim_party | 241 | CSV · JSON |
| dim_term | 56 | CSV · JSON |
| dim_role | 15 | CSV · JSON |
| dim_source | 9 | 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 · 9 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 |
Example queries
Each query was run against the tables of this build.
Most reused templates, specialists and skills: studies that use them directly, real and example apart
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;Who is credited for the blocks a study uses directly, with their roles
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;
Checks by who performed them, real studies only
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;
Reach of a record through chains of use (recursive)
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;
Tokens per specialist in the published study
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;