spb/qc-election

1# QC Élection Forecast — Plateforme de prévision électorale du Québec 20262# Auteur : Simon-Pierre Boucher3# Contact : contact@spboucher.ai4# https://www.qc-election.com5"""Schéma de données — chaque valeur conserve sa provenance (source, URL, dates)."""6from __future__ import annotations78from datetime import date, datetime, timezone910from sqlalchemy import (JSON, Boolean, Date, DateTime, Float, ForeignKey, Index, Integer,11                        String, Text, UniqueConstraint)12from sqlalchemy.orm import Mapped, mapped_column, relationship1314from .db import Base151617def utcnow() -> datetime:18    return datetime.now(timezone.utc)192021class Election(Base):22    __tablename__ = "elections"23    id: Mapped[int] = mapped_column(primary_key=True)24    name: Mapped[str] = mapped_column(String(120), unique=True)25    election_date: Mapped[date] = mapped_column(Date, index=True)26    total_seats: Mapped[int] = mapped_column(Integer)27    majority_seats: Mapped[int] = mapped_column(Integer)28    is_target: Mapped[bool] = mapped_column(Boolean, default=False)29    # Résultat provincial réel {party: pct} — rempli pour les élections passées30    actual_result: Mapped[dict | None] = mapped_column(JSON, nullable=True)313233class Party(Base):34    __tablename__ = "parties"35    id: Mapped[int] = mapped_column(primary_key=True)36    code: Mapped[str] = mapped_column(String(8), unique=True)37    name: Mapped[str] = mapped_column(String(80))38    leader: Mapped[str | None] = mapped_column(String(80), nullable=True)39    color: Mapped[str | None] = mapped_column(String(9), nullable=True)404142class District(Base):43    __tablename__ = "districts"44    id: Mapped[int] = mapped_column(primary_key=True)45    name: Mapped[str] = mapped_column(String(80), unique=True)46    region: Mapped[str] = mapped_column(String(60), index=True)47    # Résultat 2022 transposé (approx.) {party: pct}48    baseline_shares: Mapped[dict] = mapped_column(JSON)49    baseline_source: Mapped[str] = mapped_column(String(200), default="")50    incumbent_party: Mapped[str | None] = mapped_column(String(8), nullable=True)51    incumbent_running: Mapped[bool] = mapped_column(Boolean, default=True)52    current_holder_party: Mapped[str | None] = mapped_column(String(8), nullable=True)53    turnout_2022: Mapped[float | None] = mapped_column(Float, nullable=True)54    is_new_2026: Mapped[bool] = mapped_column(Boolean, default=False)55    notes: Mapped[str | None] = mapped_column(Text, nullable=True)565758class Candidate(Base):59    __tablename__ = "candidates"60    id: Mapped[int] = mapped_column(primary_key=True)61    district_id: Mapped[int] = mapped_column(ForeignKey("districts.id"), index=True)62    party: Mapped[str] = mapped_column(String(8))63    party_full: Mapped[str | None] = mapped_column(String(120), nullable=True)64    name: Mapped[str] = mapped_column(String(120))65    is_incumbent: Mapped[bool] = mapped_column(Boolean, default=False)66    source_url: Mapped[str | None] = mapped_column(Text, nullable=True)67    fetched_at: Mapped[datetime] = mapped_column(DateTime, default=utcnow)686970class Pollster(Base):71    __tablename__ = "pollsters"72    id: Mapped[int] = mapped_column(primary_key=True)73    name: Mapped[str] = mapped_column(String(80), unique=True)74    default_mode: Mapped[str] = mapped_column(String(20), default="unknown")75    transparency: Mapped[str | None] = mapped_column(String(20), nullable=True)767778class Poll(Base):79    __tablename__ = "polls"80    __table_args__ = (81        UniqueConstraint("pollster_id", "field_end", "sample_size", name="uq_poll_dedupe"),82        Index("ix_polls_field_end", "field_end"),83    )84    id: Mapped[int] = mapped_column(primary_key=True)85    election_id: Mapped[int] = mapped_column(ForeignKey("elections.id"), index=True)86    pollster_id: Mapped[int] = mapped_column(ForeignKey("pollsters.id"))87    sponsor: Mapped[str | None] = mapped_column(String(120), nullable=True)88    field_start: Mapped[date | None] = mapped_column(Date, nullable=True)89    field_end: Mapped[date] = mapped_column(Date)90    published: Mapped[date | None] = mapped_column(Date, nullable=True)91    sample_size: Mapped[int | None] = mapped_column(Integer, nullable=True)92    population: Mapped[str] = mapped_column(String(30), default="adults")  # adults/RV/LV93    mode: Mapped[str] = mapped_column(String(20), default="unknown")94    region: Mapped[str] = mapped_column(String(60), default="QC")  # provincial par défaut95    moe: Mapped[float | None] = mapped_column(Float, nullable=True)96    undecided: Mapped[float | None] = mapped_column(Float, nullable=True)97    # Provenance98    source_name: Mapped[str] = mapped_column(String(120), default="")99    source_url: Mapped[str | None] = mapped_column(Text, nullable=True)100    accessed_at: Mapped[datetime] = mapped_column(DateTime, default=utcnow)101    methodology_notes: Mapped[str | None] = mapped_column(Text, nullable=True)102    excluded: Mapped[bool] = mapped_column(Boolean, default=False)103    exclusion_reason: Mapped[str | None] = mapped_column(String(200), nullable=True)104105    pollster: Mapped["Pollster"] = relationship()106    results: Mapped[list["PollResult"]] = relationship(cascade="all, delete-orphan")107108109class PollResult(Base):110    __tablename__ = "poll_results"111    __table_args__ = (UniqueConstraint("poll_id", "party", name="uq_poll_party"),)112    id: Mapped[int] = mapped_column(primary_key=True)113    poll_id: Mapped[int] = mapped_column(ForeignKey("polls.id"), index=True)114    party: Mapped[str] = mapped_column(String(8))115    raw_value: Mapped[float] = mapped_column(Float)         # valeur publiée116    normalized_value: Mapped[float] = mapped_column(Float)  # après renormalisation117118119class PollsterRating(Base):120    __tablename__ = "pollster_ratings"121    __table_args__ = (UniqueConstraint("pollster_id", "computed_for", name="uq_rating"),)122    id: Mapped[int] = mapped_column(primary_key=True)123    pollster_id: Mapped[int] = mapped_column(ForeignKey("pollsters.id"))124    computed_for: Mapped[str] = mapped_column(String(40))   # ex.: "2026-general"125    n_polls: Mapped[int] = mapped_column(Integer, default=0)126    mae_pp: Mapped[float | None] = mapped_column(Float, nullable=True)127    house_effects: Mapped[dict] = mapped_column(JSON, default=dict)  # {party: pp}128    weight_multiplier: Mapped[float] = mapped_column(Float, default=1.0)129    detail: Mapped[dict] = mapped_column(JSON, default=dict)130    computed_at: Mapped[datetime] = mapped_column(DateTime, default=utcnow)131132    pollster: Mapped["Pollster"] = relationship()133134135class HistoricalDistrictResult(Base):136    __tablename__ = "historical_results"137    __table_args__ = (UniqueConstraint("election_id", "district_name", "party", name="uq_hist"),)138    id: Mapped[int] = mapped_column(primary_key=True)139    election_id: Mapped[int] = mapped_column(ForeignKey("elections.id"), index=True)140    district_name: Mapped[str] = mapped_column(String(80), index=True)141    party: Mapped[str] = mapped_column(String(8))142    pct: Mapped[float] = mapped_column(Float)143    source_url: Mapped[str | None] = mapped_column(Text, nullable=True)144145146class ForecastRun(Base):147    __tablename__ = "forecast_runs"148    id: Mapped[int] = mapped_column(primary_key=True)149    run_at: Mapped[datetime] = mapped_column(DateTime, default=utcnow, index=True)150    as_of: Mapped[date] = mapped_column(Date, index=True)   # date "vue" par le modèle151    model_version: Mapped[str] = mapped_column(String(20))152    n_polls_used: Mapped[int] = mapped_column(Integer)153    n_simulations: Mapped[int] = mapped_column(Integer)154    is_backtest: Mapped[bool] = mapped_column(Boolean, default=False)155    label: Mapped[str | None] = mapped_column(String(80), nullable=True)156    # Résumés complets sérialisés (nowcast, forecast, sièges, distributions, diagnostics)157    national: Mapped[dict] = mapped_column(JSON)158    seats: Mapped[dict] = mapped_column(JSON)159    diagnostics: Mapped[dict] = mapped_column(JSON, default=dict)160161    district_results: Mapped[list["ForecastDistrictResult"]] = relationship(162        cascade="all, delete-orphan")163164165class ForecastDistrictResult(Base):166    __tablename__ = "forecast_district_results"167    __table_args__ = (Index("ix_fdr_run", "run_id"),)168    id: Mapped[int] = mapped_column(primary_key=True)169    run_id: Mapped[int] = mapped_column(ForeignKey("forecast_runs.id"))170    district_name: Mapped[str] = mapped_column(String(80))171    # {party: {mean, lo, hi, win_prob}}, favori, catégorie Safe/Likely/Lean/Toss-up172    detail: Mapped[dict] = mapped_column(JSON)173    favorite: Mapped[str] = mapped_column(String(8))174    category: Mapped[str] = mapped_column(String(12))175176177class SentimentDocument(Base):178    __tablename__ = "sentiment_documents"179    __table_args__ = (UniqueConstraint("url", name="uq_sent_url"),)180    id: Mapped[int] = mapped_column(primary_key=True)181    fetched_at: Mapped[datetime] = mapped_column(DateTime, default=utcnow)182    published: Mapped[datetime | None] = mapped_column(DateTime, nullable=True, index=True)183    source: Mapped[str] = mapped_column(String(80))184    url: Mapped[str] = mapped_column(Text)185    title: Mapped[str] = mapped_column(Text)186    summary: Mapped[str | None] = mapped_column(Text, nullable=True)187    cluster_key: Mapped[str | None] = mapped_column(String(64), nullable=True, index=True)188189    scores: Mapped[list["SentimentScore"]] = relationship(cascade="all, delete-orphan")190191192class SentimentScore(Base):193    __tablename__ = "sentiment_scores"194    id: Mapped[int] = mapped_column(primary_key=True)195    document_id: Mapped[int] = mapped_column(ForeignKey("sentiment_documents.id"), index=True)196    entity: Mapped[str] = mapped_column(String(40), index=True)  # code parti ou nom de chef197    sentiment: Mapped[float] = mapped_column(Float)   # [-1, 1]198    stance: Mapped[str] = mapped_column(String(12))   # pro/anti/neutre/ambigu199    method: Mapped[str] = mapped_column(String(20), default="lexicon")  # lexicon | llm200201202class NewsEvent(Base):203    __tablename__ = "news_events"204    id: Mapped[int] = mapped_column(primary_key=True)205    event_date: Mapped[date] = mapped_column(Date, index=True)206    title: Mapped[str] = mapped_column(String(200))207    description: Mapped[str | None] = mapped_column(Text, nullable=True)208    kind: Mapped[str] = mapped_column(String(30), default="event")  # debate/announcement/anomaly...209    parties: Mapped[list | None] = mapped_column(JSON, nullable=True)210    importance: Mapped[float] = mapped_column(Float, default=0.5)211    detected_by: Mapped[str] = mapped_column(String(30), default="manual")212213214class ByElection(Base):215    """Élection partielle depuis la dernière générale — vote RÉEL, converti en216    observation nationale bruitée (v2 « au-delà des sondages »)."""217    __tablename__ = "byelections"218    __table_args__ = (UniqueConstraint("district_name", "held_on", name="uq_bye"),)219    id: Mapped[int] = mapped_column(primary_key=True)220    district_name: Mapped[str] = mapped_column(String(80))221    held_on: Mapped[date] = mapped_column(Date, index=True)222    result: Mapped[dict] = mapped_column(JSON)              # {party: pct}223    turnout: Mapped[float | None] = mapped_column(Float, nullable=True)224    winner: Mapped[str] = mapped_column(String(8))225    previous_winner: Mapped[str | None] = mapped_column(String(8), nullable=True)226    source_url: Mapped[str | None] = mapped_column(Text, nullable=True)227    verified: Mapped[bool] = mapped_column(Boolean, default=False)  # confirmé par scrape228    used_in_model: Mapped[bool] = mapped_column(Boolean, default=True)229    notes: Mapped[str | None] = mapped_column(Text, nullable=True)230231232class WebSignal(Base):233    """Détection de la veille web continue (Firecrawl) : nouveau sondage repéré,234    article pertinent, mesure de satisfaction… Provenance complète."""235    __tablename__ = "web_signals"236    __table_args__ = (UniqueConstraint("url", "kind", name="uq_signal"),)237    id: Mapped[int] = mapped_column(primary_key=True)238    detected_at: Mapped[datetime] = mapped_column(DateTime, default=utcnow, index=True)239    kind: Mapped[str] = mapped_column(String(30), index=True)  # poll-radar / satisfaction / presse240    title: Mapped[str] = mapped_column(Text)241    url: Mapped[str] = mapped_column(Text)242    snippet: Mapped[str | None] = mapped_column(Text, nullable=True)243    pollster: Mapped[str | None] = mapped_column(String(80), nullable=True)244    status: Mapped[str] = mapped_column(String(12), default="nouveau")  # nouveau/traité/ignoré245    extra: Mapped[dict] = mapped_column(JSON, default=dict)246247248class Indicator(Base):249    """Indicateur de fondamentaux (ex. % satisfaits du gouvernement — Léger),250    avec provenance et méthode (firecrawl / fallback-config / manuel)."""251    __tablename__ = "indicators"252    __table_args__ = (UniqueConstraint("name", "as_of", name="uq_indicator"),)253    id: Mapped[int] = mapped_column(primary_key=True)254    name: Mapped[str] = mapped_column(String(60), index=True)255    value: Mapped[float] = mapped_column(Float)256    as_of: Mapped[date] = mapped_column(Date)257    source: Mapped[str] = mapped_column(String(120), default="")258    source_url: Mapped[str | None] = mapped_column(Text, nullable=True)259    method: Mapped[str] = mapped_column(String(30), default="manual")260    fetched_at: Mapped[datetime] = mapped_column(DateTime, default=utcnow)261    extra: Mapped[dict] = mapped_column(JSON, default=dict)262263264class SocialPost(Base):265    """Publication de médias sociaux (Reddit via index Google, Mastodon, Lemmy…)266    scorée par le moteur de sentiment — signal « Pouls social », poids nul dans267    le forecast, anomalies de volume détectées."""268    __tablename__ = "social_posts"269    __table_args__ = (UniqueConstraint("url", name="uq_social_url"),270                      Index("ix_social_created", "created_at"),)271    id: Mapped[int] = mapped_column(primary_key=True)272    fetched_at: Mapped[datetime] = mapped_column(DateTime, default=utcnow)273    created_at: Mapped[datetime | None] = mapped_column(DateTime, nullable=True)274    platform: Mapped[str] = mapped_column(String(20), index=True)  # reddit/mastodon/lemmy275    community: Mapped[str | None] = mapped_column(String(80), nullable=True)276    url: Mapped[str] = mapped_column(Text)277    author_hash: Mapped[str | None] = mapped_column(String(16), nullable=True)278    text: Mapped[str] = mapped_column(Text)279    text_hash: Mapped[str] = mapped_column(String(16), index=True)  # anti-doublon280    engagement: Mapped[float] = mapped_column(Float, default=0.0)   # votes/favs/boosts281    # {party: {"sentiment": s, "stance": st, "method": m}}282    scores: Mapped[dict] = mapped_column(JSON, default=dict)283    query: Mapped[str | None] = mapped_column(String(120), nullable=True)284    # §33 : SÉPARATION STRICTE — public_discussion (citoyens) vs official_content285    # (partis/candidats) vs media_content. Les sentiments ne se mélangent jamais :286    # le « sentiment » d'un message officiel n'est pas de l'opinion publique.287    content_class: Mapped[str] = mapped_column(String(20), default="public_discussion")288289290class ApifyRun(Base):291    """Observabilité des acteurs Apify (règle §34) : chaque run est journalisé;292    un échec d'acteur n'empêche jamais la publication du forecast."""293    __tablename__ = "apify_runs"294    id: Mapped[int] = mapped_column(primary_key=True)295    at: Mapped[datetime] = mapped_column(DateTime, default=utcnow, index=True)296    actor_id: Mapped[str] = mapped_column(String(40))297    actor_name: Mapped[str] = mapped_column(String(60), default="qc-social-pulse")298    status: Mapped[str] = mapped_column(String(16))   # ok / error / timeout299    items_collected: Mapped[int] = mapped_column(Integer, default=0)300    items_valid: Mapped[int] = mapped_column(Integer, default=0)301    items_stored: Mapped[int] = mapped_column(Integer, default=0)302    runtime_s: Mapped[float | None] = mapped_column(Float, nullable=True)303    error: Mapped[str | None] = mapped_column(Text, nullable=True)304    detail: Mapped[dict] = mapped_column(JSON, default=dict)305306307class DataSource(Base):308    __tablename__ = "data_sources"309    id: Mapped[int] = mapped_column(primary_key=True)310    name: Mapped[str] = mapped_column(String(80), unique=True)311    url: Mapped[str] = mapped_column(Text)312    kind: Mapped[str] = mapped_column(String(30))313    last_fetch: Mapped[datetime | None] = mapped_column(DateTime, nullable=True)314    last_status: Mapped[str | None] = mapped_column(String(200), nullable=True)315316317class ModelVersion(Base):318    __tablename__ = "model_versions"319    id: Mapped[int] = mapped_column(primary_key=True)320    version: Mapped[str] = mapped_column(String(20), unique=True)321    released: Mapped[datetime] = mapped_column(DateTime, default=utcnow)322    changelog: Mapped[str | None] = mapped_column(Text, nullable=True)323324325class PipelineLog(Base):326    __tablename__ = "pipeline_logs"327    id: Mapped[int] = mapped_column(primary_key=True)328    at: Mapped[datetime] = mapped_column(DateTime, default=utcnow, index=True)329    step: Mapped[str] = mapped_column(String(40))330    status: Mapped[str] = mapped_column(String(12))  # ok / error / skip331    message: Mapped[str | None] = mapped_column(Text, nullable=True)332