Ledger schema and views

Every table and view in the ledger with its columns and what it holds, plus the query shapes SQL steps take, generated from spec/ledger.sql

What you'll learn

You're about to write SQL against the ledger and need the views to read first, the tables behind them, and a query shape to start from.

the views a query reads first
every table and what its rows hold
the query shapes a sql step usually takes

The ledger is one SQLite file with 11 tables and 5 views, as spec/ledger.sql states them. Each object's own page has its columns and the DDL. Read-only SQL (sql/transform, sql/filter, {query:} values, gtme query) is written against these. Prefer current_values and group_membership; the raw tables are for provenance. A sql/* step's result must carry identity_id; :run_id is bound when referenced.

Views

The read surface. A sql/* step, a {query:} value, and gtme query are written against these first.

Object	Columns	Holds
`field_value_ranks`	id, identity_id, field, value, source, confidence, run_id, created_at, rank	field_values ranked per (identity, field): confidence DESC, newest first
`current_fields`	identity_id, field, value, source, confidence, run_id, created_at	rank-1 field_values — the current value per (identity, field), value still JSON-encoded
`group_members`	group_id, identity_id	current membership as (group_id, identity_id)
`current_values`	identity_id, field, value, source, confidence, run_id, created_at	current_fields with value unwrapped from JSON — the view to query for plain values
`group_membership`	group_name, group_id, identity_id	current membership keyed by group_name — the view to query for membership

Tables

The rows the views rank and join. Read them for provenance.

Object	Columns	Holds
`identities`	id, entity_type, identity_key, created_at	one row per person/company; identity_key per SPEC §4
`field_values`	id, identity_id, field, value, source, confidence, run_id, referent, created_at	append-only facts: (identity_id, field, value JSON, source, confidence, run_id, created_at)
`relations`	from_id, relation, to_id, created_at	typed edges between identities, such as works_at (person → company)
`runs`	id, pipeline, config_json, started_at, finished_at, status, dry, pid, host	one row per gtme run; config_json is the resolved pipeline
`run_records`	run_id, identity_id, state, verdicts	a run's membership: state = last completed step id, verdicts = {step_id: pass\|fail}
`step_events`	id, run_id, step_id, identity_id, event, detail, created_at	per-record trail: claimed\|done\|failed\|skipped_cache\|dry_run\|simulated, detail JSON
`costs`	id, run_id, step_id, identity_id, provider, amount_usd, basis, detail, created_at	spend per step/record; basis measured\|estimated (ADR-046: measured only when read back from vendor-reported cost metadata; a rate multiplied out is estimated)
`deliveries`	id, identity_id, target, scope, idempotency, run_id, created_at, status, sent_at, variables_hash	sends and handoffs, UNIQUE(target, scope, idempotency) — scope is the resolved idempotency_scope config value (ADR-044, '' = unscoped); status accepted\|confirmed\|contradicted\|sent; variables_hash drives redeliver: on_change (ADR-045)
`groups`	id, name, note, created_at, entity_type	named associations (ADR-021); entity_type is the members' type (ADR-054); character derived from events
`group_events`	id, group_id, identity_id, event, detail, run_id, created_at	append-only added\|removed\|touched events per (group, identity)
`payloads`	id, identity_id, adapter, run_id, content_type, body, created_at, expires_at	raw vendor responses, a purgeable cache tier (ADR-030) — never facts

Query shapes

The queries a sql/* step or a config value is usually one of, from gtme help --agent.

cross-type membership gate (sql/filter)

Keep people whose company is in a group — membership by ledger facts, via relations.

SELECT r.from_id AS identity_id
FROM relations r
JOIN group_membership gm ON gm.identity_id = r.to_id AND gm.group_name = 'target-accounts'
WHERE r.relation = 'works_at'

cross-record aggregate (sql/transform, company pipeline)

fan-in: count a company's known people into a field; declare provides: [acct.people_count].

SELECT r.to_id AS identity_id, count(*) AS "acct.people_count"
FROM relations r
WHERE r.relation = 'works_at'
GROUP BY r.to_id

per-record derivation (sql/transform)

Derive one field from another; declare provides: [sql.shout].

SELECT identity_id, upper(value) AS "sql.shout"
FROM current_values WHERE field = 'full_name'

Config value (one column → a list)

with: {domains: {query: ...}} — the list a source is parameterised by; zero rows fails plan.

SELECT DISTINCT v.value
FROM current_values v
JOIN group_membership gm ON gm.identity_id = v.identity_id AND gm.group_name = 'qualified'
WHERE v.field = 'company_domain'

Used in

The ledger: The full DDL, one table at a time
Identity keys: The DDL for identities and field_values
Facts have provenance: The DDL for field_values, field_value_ranks, and payloads