ER diagram — SocialScan context (radar.ss_* + weekly_corpus_summary)

Generated from the live PostgreSQL schema (schema radar) by tbls, then compacted to keys + foreign keys for legibility. Do not hand-edit the fenced block. This is the Reference layer — it reflects the real database, not the ORM.

The 21 SocialScan tables (19 ss_-prefixed ORM models + weekly_corpus_summary with no prefix + ss_trends_daily, a raw-SQL table with no ORM model) plus the two spine boundary tables candidates and fetch_runs. ss_snapshots is the hub: nearly every table references it via snapshot_id. Only key/FK columns are shown (tables with a natural/composite PK show their first key column).

erDiagram

"radar.candidates" {
  bigint id PK
}
"radar.fetch_runs" {
  bigint candidate_id FK
  bigint id PK
}
"radar.ss_ai_reports" {
  bigint candidate_id FK
  bigint id PK
  text snapshot_id FK
}
"radar.ss_channel_archetypes" {
  text author_url
}
"radar.ss_channel_followers_weekly" {
  bigint candidate_id
}
"radar.ss_district_summaries" {
  bigint candidate_id FK
  bigint id PK
  text snapshot_id FK
}
"radar.ss_judge_results" {
  bigint candidate_id FK
  bigint id PK
  text snapshot_id FK
}
"radar.ss_mention_relevance" {
  bigint id PK
  integer candidate_id FK
  text snapshot_id FK
}
"radar.ss_narodnaya_verdicts" {
  bigint id PK
  bigint own_post_id FK
  text snapshot_id FK
  timestamp_with_time_zone own_post_posted_at FK
}
"radar.ss_network_posts" {
  bigint id PK
  text snapshot_id FK
}
"radar.ss_own_posts" {
  bigint candidate_id FK
  bigint id PK
  text snapshot_id FK
}
"radar.ss_post_classifications" {
  bigint candidate_id FK
  bigint id PK
  text snapshot_id FK
}
"radar.ss_post_events" {
  bigint id PK
  bigint own_post_id FK
  text snapshot_id FK
  timestamp_with_time_zone own_post_posted_at FK
}
"radar.ss_post_tonality" {
  bigint id PK
  integer candidate_id FK
  text snapshot_id FK
}
"radar.ss_posts" {
  bigint candidate_id FK
  bigint id PK
  text snapshot_id FK
}
"radar.ss_recommendations" {
  bigint candidate_id FK
  bigint id PK
  text snapshot_id FK
}
"radar.ss_sanity_corrections" {
  bigint id PK
  bigint verdict_id FK
}
"radar.ss_snapshots" {
  bigint id PK
  bigint source_run_id FK
}
"radar.ss_themes" {
  bigint candidate_id FK
  bigint id PK
  text snapshot_id FK
}
"radar.ss_trends" {
  bigint candidate_id FK
  bigint id PK
  text snapshot_id FK
}
"radar.ss_trends_daily" {
  bigint candidate_id FK
  bigint id PK
  text snapshot_id FK
}
"radar.ss_wave_runs" {
  bigint id PK
  text snapshot_id FK
}
"radar.weekly_corpus_summary" {
  date week_start
}

"radar.fetch_runs" }o--o| "radar.candidates" : candidate_id
"radar.ss_ai_reports" }o--|| "radar.candidates" : candidate_id
"radar.ss_ai_reports" }o--|| "radar.ss_snapshots" : snapshot_id
"radar.ss_district_summaries" }o--|| "radar.candidates" : candidate_id
"radar.ss_district_summaries" }o--|| "radar.ss_snapshots" : snapshot_id
"radar.ss_judge_results" }o--|| "radar.candidates" : candidate_id
"radar.ss_judge_results" }o--|| "radar.ss_snapshots" : snapshot_id
"radar.ss_mention_relevance" }o--|| "radar.candidates" : candidate_id
"radar.ss_mention_relevance" }o--|| "radar.ss_snapshots" : snapshot_id
"radar.ss_narodnaya_verdicts" }o--|| "radar.ss_own_posts" : own_post_id, own_post_posted_at
"radar.ss_narodnaya_verdicts" }o--|| "radar.ss_snapshots" : snapshot_id
"radar.ss_network_posts" }o--|| "radar.ss_snapshots" : snapshot_id
"radar.ss_own_posts" }o--|| "radar.candidates" : candidate_id
"radar.ss_own_posts" }o--|| "radar.ss_snapshots" : snapshot_id
"radar.ss_post_classifications" }o--|| "radar.candidates" : candidate_id
"radar.ss_post_classifications" }o--|| "radar.ss_snapshots" : snapshot_id
"radar.ss_post_events" }o--|| "radar.ss_own_posts" : own_post_id, own_post_posted_at
"radar.ss_post_events" }o--|| "radar.ss_snapshots" : snapshot_id
"radar.ss_post_tonality" }o--|| "radar.candidates" : candidate_id
"radar.ss_post_tonality" }o--|| "radar.ss_snapshots" : snapshot_id
"radar.ss_posts" }o--o| "radar.candidates" : candidate_id
"radar.ss_posts" }o--|| "radar.ss_snapshots" : snapshot_id
"radar.ss_recommendations" }o--|| "radar.candidates" : candidate_id
"radar.ss_recommendations" }o--|| "radar.ss_snapshots" : snapshot_id
"radar.ss_sanity_corrections" }o--|| "radar.ss_narodnaya_verdicts" : verdict_id
"radar.ss_snapshots" }o--o| "radar.fetch_runs" : source_run_id
"radar.ss_themes" }o--|| "radar.candidates" : candidate_id
"radar.ss_themes" }o--|| "radar.ss_snapshots" : snapshot_id
"radar.ss_trends" }o--|| "radar.candidates" : candidate_id
"radar.ss_trends" }o--|| "radar.ss_snapshots" : snapshot_id
"radar.ss_trends_daily" }o--|| "radar.candidates" : candidate_id
"radar.ss_trends_daily" }o--|| "radar.ss_snapshots" : snapshot_id
"radar.ss_wave_runs" }o--|| "radar.ss_snapshots" : snapshot_id

How SocialScan attaches to the spine. ss_snapshots.source_run_id → fetch_runs.id ties a SocialScan pull to an ingestion run; the leaf tables (ss_posts, ss_trends, ss_ai_reports, …) carry candidate_id → candidates.id. ss_trends_daily exists only in the live DB (raw SQL), so an ORM-only tool would miss it — tbls against the live schema does not.

---

Regenerate: tbls out "$DSN" -t mermaid --include <table-list> | python3 ../tools/compact_erd.py /dev/stdin. Boundary spine tables candidates/fetch_runs appear in both context diagrams by design; cross-context edges live in context-bridge.md. Drift gate: tbls diff against the live schema.