simonandClaude Opus 5.5
583ccba31c
Initial import of Politrack, a self-hosted service that tracks Austrian politicians from federal to municipal level - roles, votes, money flows, statements, meetings, social media and legal proceedings - with every claim linked to its sources. - backend: FastAPI + Postgres/pgvector; adapter framework (fetch -> parse -> load -> enrich) with robots.txt/TDM-aware fetching, a raw-response archive for reparsing, and a claim/evidence model with corroboration - sources: Parliament, Wikidata, EU Parliament/Commission, Council of Ministers, Rechnungshof party reports and donations, procurement, RTR media transparency, political ad libraries, WKStA, news feeds, Wikipedia edits, Bluesky, Mastodon, public Telegram channels, YouTube and X (official API) - web: Next.js frontend in German and English with typed API client - mcp: MCP server for AI agents and RAG - docs: architecture, data model, sources, legal notes, Unraid deployment and contributor guides; Docker Compose setup for Unraid Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>
116 lines
6.5 KiB
Markdown
116 lines
6.5 KiB
Markdown
# 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).
|