# Data model reference

Full DDL: [backend/migrations/0001_schema.sql](../backend/migrations/0001_schema.sql) (tables and columns
carry `COMMENT`s). Seed data (chambers, parties, topics): `0002_seed.sql`. Design rationale:
[architecture/01-design-from-scratch.md §3](architecture/01-design-from-scratch.md).

## Overview

```
source ──< document >── story              publishers, the documents we saw (evidence), duplicate stories
   │          │
   │          └──< claim_evidence >──┐
fetch_log (raw archive index)        │
                                     claim ──< topic_assignment >── topic
person ─┐                           ╱ │ ╲
        ├── identifier, alias      ╱  │  ╲   one facet table per claim kind:
organization ─┘                   ╱   │   ╲  role, vote_position(→vote_event), money_flow,
social_account                         │      event_participant(→event), statement, legal_proceeding
                                     chunk (text + embedding for RAG)
run, state, app_user                 operations
```

## Entities

| table | notes |
|---|---|
| `person` | name parts, birth/death date, photo. **No id columns** – external ids live in `identifier`. |
| `organization` | `kind`: party, parliamentary_group, parliament, government, ministry, state_government, municipality, public_body, company, ngo, media, lobby, court, authority, eu_institution, other. `parent_id` for hierarchies. |
| `identifier` | `(entity_type, entity_id, scheme, value)`, unique per (entity_type, scheme, value). |
| `alias` | alternative names (with/without titles, short names, party abbreviations) used by the resolver and the linker. |
| `social_account` | person *or* organisation, `platform`, `url`, `verified` (official source) and `source_slug`. |

### Identifier schemes

| scheme | entity | meaning |
|---|---|---|
| `parlament` | person | Parliament person id (PAD) – `parlament.gv.at/person/<id>` |
| `wikidata` | person, org | Wikidata QID |
| `ep` | person | European Parliament MEP id |
| `firmenbuch` | org | Austrian company register number (FN), normalised `306527a` (`text.firmenbuch_number`) – from side-job declarations, procurement, party holdings |
| `zvr` | org | Austrian association register number (ZVR) – from party reports (affiliated organisations) |
| `uid` | org | VAT id (ATU…) |
| `rtr` | org | legal entity id in the RTR media-transparency data |
| `eu_transparency` | org | EU Transparency Register id |
| `google_advertiser`, `facebook_page` | org | political advertiser ids in the ad libraries |
| `politrack`, `politrack_org` | – | *virtual*: internal ids, used by enrichers in `PersonRef`/`OrgRef` (not stored) |

## Claims

`claim` holds the common fields: `claim_type`, subject (`person_id` and/or `organization_id`),
counterpart (`counterpart_person_id`, `counterpart_org_id`), time (`occurred_at` or `valid_from`/`valid_to`),
`summary_de`/`summary_en`, `data` (JSONB, type-specific extras), `status`, `sensitive`, `review_status`,
`merged_into`, `fingerprint` (identity), `created_by` (adapter/enricher name).

| claim_type | facet table | typical source |
|---|---|---|
| `role` | `role` (role, level, body, party, region, start/end) | parliament, Wikidata, EP |
| `vote` | `vote_position` → `vote_event` | parliament (faction votes, roll calls), `enrich.rollcall` |
| `donation` | `money_flow` | Rechnungshof donation lists, party reports |
| `money_flow` | `money_flow` | procurement, RTR media transparency, party funding, political ads |
| `side_job` | – (`data`, counterpart org) | parliament declarations |
| `statement` | `statement` (context, platform, url, excerpt) | speeches, social posts |
| `appearance`, `meeting`, `travel` | `event_participant` → `event` | calendar, hearings, Council of Ministers, `enrich.events`, EC meetings |
| `legal_proceeding` | `legal_proceeding` (stage, offense, authority) | immunity reports, `enrich.legal` (reviewed) |
| `lobbying` | – | EC meetings with interest representatives |
| `media_mention` | – | `enrich.linker` on news headlines |
| `career` | – | Wikidata employers/positions |
| `parliamentary_question` | – | parliament |
| `government_decision` | – | Council of Ministers minutes |
| `wiki_edit` | – | Wikipedia edits from institutional IP ranges (subject = network owner) |
| `declaration` | – | EP declarations of interests |
| `ownership`, `affiliation` | – | party reports (holdings, affiliated organisations) |
| `party_finance` | – | party annual reports (totals in `data`) |

**Status:** `reported` (one source) → `corroborated` (≥ 2 distinct sources support/mention it) or
`disputed` (a source contradicts). Computed by the Loader on every new piece of evidence and by
`enrich.corroborate`.
**Review:** `review_status` = `pending` for claims that need a human (legal matters derived from
headlines); only `published` claims are served.
**Merging:** near-duplicates are merged by `enrich.corroborate` (`merged_into`), never deleted.

### Claim identity (fingerprint)

| facet | identity |
|---|---|
| explicit `key` | `(claim_type, "key", *key)` |
| Role | person, body, role, start |
| VotePosition | vote event, person/party |
| Money | kind, payer, payee, amount, paid_on, period, reference |
| Statement | person/org, url |
| Participation | event, person/org |
| media_mention | url, person/org |

Claims without a facet must carry a `key` (see [contributing/adapters.md](contributing/adapters.md)).

## Evidence

`document` = one URL we saw (title, published_at, excerpt, `full_text` only if the publisher's terms
allow it and no TDM reservation applies). `claim_evidence` links a claim to a document with `quote`,
`locator`, `stance` (supports/contradicts/mentions) and `confidence`. `story` groups the same story
across outlets (independence for corroboration).

## Views

| view | purpose |
|---|---|
| `person_status` | active / former / party_member / deceased per person, `verified` (current office confirmed by parliament or EP data), `last_office_end`, `current_party_ids` |
| `person_identifier` | person with its main identifiers as columns (parlament, wikidata, ep) |
| `claim_with_evidence` | published, unmerged claims with an `evidence` JSON array – the easiest starting point for agents and SQL users |

## Operations tables

`fetch_log` (every HTTP fetch: request, status, sha256, stored body, parse state), `run` (every adapter /
enricher run with stats and errors), `state` (adapter cursors), `chunk` (text chunks + pgvector
embeddings), `app_user` (reviewers).