politics-tracking/docs/data-model.md

Data model reference

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

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).

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).