spb/api-ka

1# ============================================2# Projet   : API-KA3# Fichier  : src/database/models.py4# Node     : m3u96b5# Author   : Simon-Pierre Boucher6# Contact  : contact@spboucher.ai7# Date     : 2026-08-168# ============================================9"""Modèles SQLAlchemy : 9 tables de données (schéma identique) + collection_runs.1011Règle append-only : aucune suppression destructive, l'historique complet est conservé.12Déduplication par contrainte unique (source, date_key, checksum).13"""1415from __future__ import annotations1617import datetime18from typing import Any1920from sqlalchemy import (21    BigInteger,22    Date,23    DateTime,24    Float,25    Index,26    Integer,27    Text,28    UniqueConstraint,29    func,30)31from sqlalchemy.dialects.postgresql import JSONB32from sqlalchemy.orm import DeclarativeBase, Mapped, declared_attr, mapped_column33from sqlalchemy.types import JSON3435import socket3637from src.config import REQUIRED_NODE3839CURRENT_NODE = REQUIRED_NODE or socket.gethostname().split(".")[0].lower()4041# BIGSERIAL sur PostgreSQL ; INTEGER auto-incrémenté en dev SQLite.42BigIntPK = BigInteger().with_variant(Integer(), "sqlite")43# JSONB sur PostgreSQL ; JSON générique en dev SQLite.44JSONPayload = JSONB().with_variant(JSON(), "sqlite")454647class Base(DeclarativeBase):48    """Base déclarative commune à tous les modèles API-KA."""495051class DataRecordMixin:52    """Colonnes communes aux 9 tables de données des services KA."""5354    id: Mapped[int] = mapped_column(BigIntPK, primary_key=True, autoincrement=True)55    payload: Mapped[dict[str, Any]] = mapped_column(JSONPayload, nullable=False)56    source: Mapped[str] = mapped_column(Text, nullable=False)57    collected_at: Mapped[datetime.datetime] = mapped_column(58        DateTime(timezone=True), nullable=False, server_default=func.now()59    )60    date_key: Mapped[datetime.date] = mapped_column(Date, nullable=False)61    checksum: Mapped[str] = mapped_column(Text, nullable=False)6263    @declared_attr.directive64    def __table_args__(cls) -> tuple:  # noqa: N805 — API declared_attr de SQLAlchemy65        table = cls.__tablename__66        return (67            Index(f"ix_{table}_date_key", "date_key"),68            Index(f"ix_{table}_source_date_key", "source", "date_key"),69            UniqueConstraint(70                "source",71                "date_key",72                "checksum",73                name=f"uq_{table}_source_date_checksum",74            ),75        )767778class LoukaData(DataRecordMixin, Base):79    """Données quotidiennes du service lou-ka."""8081    __tablename__ = "louka_data"828384class ImmokaData(DataRecordMixin, Base):85    """Données quotidiennes du service immo-ka."""8687    __tablename__ = "immoka_data"888990class FoodkaData(DataRecordMixin, Base):91    """Données quotidiennes du service food-ka."""9293    __tablename__ = "foodka_data"949596class AutokaData(DataRecordMixin, Base):97    """Données quotidiennes du service auto-ka."""9899    __tablename__ = "autoka_data"100101102class FabrikaData(DataRecordMixin, Base):103    """Données quotidiennes du service fabri-ka."""104105    __tablename__ = "fabrika_data"106107108class RestokaData(DataRecordMixin, Base):109    """Données quotidiennes du service resto-ka."""110111    __tablename__ = "restoka_data"112113114class SortikaData(DataRecordMixin, Base):115    """Données quotidiennes du service sorti-ka."""116117    __tablename__ = "sortika_data"118119120class CreakaData(DataRecordMixin, Base):121    """Données quotidiennes du service crea-ka."""122123    __tablename__ = "creaka_data"124125126class HousekaData(DataRecordMixin, Base):127    """Données quotidiennes du service house-ka."""128129    __tablename__ = "houseka_data"130131132class RentkaData(DataRecordMixin, Base):133    """Données quotidiennes du service rent-ka."""134135    __tablename__ = "rentka_data"136137138class JobkaData(DataRecordMixin, Base):139    """Données quotidiennes du service job-ka."""140141    __tablename__ = "jobka_data"142143144DATA_MODELS: dict[str, type[Base]] = {145    "louka": LoukaData,146    "immoka": ImmokaData,147    "houseka": HousekaData,148    "foodka": FoodkaData,149    "autoka": AutokaData,150    "fabrika": FabrikaData,151    "restoka": RestokaData,152    "sortika": SortikaData,153    "creaka": CreakaData,154    "jobka": JobkaData,155    "rentka": RentkaData,156}157158159class CollectionRun(Base):160    """Journal de chaque exécution de collecte (succès, échec ou relance)."""161162    __tablename__ = "collection_runs"163    __table_args__ = (164        Index("ix_collection_runs_service_date_key", "service", "date_key"),165        Index("ix_collection_runs_status", "status"),166    )167168    id: Mapped[int] = mapped_column(BigIntPK, primary_key=True, autoincrement=True)169    service: Mapped[str] = mapped_column(Text, nullable=False)170    date_key: Mapped[datetime.date] = mapped_column(Date, nullable=False)171    status: Mapped[str] = mapped_column(172        Text, nullable=False173    )  # success / failed / retried174    records_count: Mapped[int] = mapped_column(Integer, nullable=False, default=0)175    duration_seconds: Mapped[float] = mapped_column(Float, nullable=False, default=0.0)176    error_message: Mapped[str | None] = mapped_column(Text, nullable=True)177    node: Mapped[str] = mapped_column(Text, nullable=False, default=CURRENT_NODE)178    started_at: Mapped[datetime.datetime] = mapped_column(179        DateTime(timezone=True), nullable=False180    )181    finished_at: Mapped[datetime.datetime] = mapped_column(182        DateTime(timezone=True), nullable=False183    )184185186class ApiRequest(Base):187    """Journal léger des requêtes HTTP servies par l'API (alimenté par le188    middleware de logging, purge automatique > 90 jours).189190    Sert exclusivement la page /stats : appels par endpoint/jour, latences191    moyennes et p95, taux d'erreur. Le chemin est normalisé (paramètres de192    route repliés) pour garder une cardinalité bornée.193    """194195    __tablename__ = "api_requests"196    __table_args__ = (197        Index("ix_api_requests_ts", "ts"),198        Index("ix_api_requests_endpoint_ts", "endpoint", "ts"),199    )200201    id: Mapped[int] = mapped_column(BigIntPK, primary_key=True, autoincrement=True)202    ts: Mapped[datetime.datetime] = mapped_column(203        DateTime(timezone=True), nullable=False204    )205    method: Mapped[str] = mapped_column(Text, nullable=False, default="GET")206    endpoint: Mapped[str] = mapped_column(Text, nullable=False)207    status: Mapped[int] = mapped_column(Integer, nullable=False)208    duration_ms: Mapped[float] = mapped_column(Float, nullable=False, default=0.0)209210211class ConnectorHealth(Base):212    """État courant de chaque connecteur de l'écosystème KA (supervision).213214    Une ligne par couple (service, source), upsertée toutes les 2 h par215    ``src.monitoring.connector_health``. La source virtuelle ``_app``216    représente l'état global de l'app. La ligne mémorise aussi l'état217    précédent (anti-spam des alertes : on n'alerte qu'à la transition).218    """219220    __tablename__ = "connector_health"221    __table_args__ = (222        UniqueConstraint(223            "service", "source", name="uq_connector_health_service_source"224        ),225        Index("ix_connector_health_service", "service"),226        Index("ix_connector_health_status", "status"),227    )228229    id: Mapped[int] = mapped_column(BigIntPK, primary_key=True, autoincrement=True)230    service: Mapped[str] = mapped_column(Text, nullable=False)231    source: Mapped[str] = mapped_column(Text, nullable=False)232    checked_at: Mapped[datetime.datetime] = mapped_column(233        DateTime(timezone=True), nullable=False, server_default=func.now()234    )235    status: Mapped[str] = mapped_column(236        Text, nullable=False237    )  # ok / degraded / broken / stale / retired238    last_success: Mapped[datetime.datetime | None] = mapped_column(239        DateTime(timezone=True), nullable=True240    )241    found_last: Mapped[int | None] = mapped_column(Integer, nullable=True)242    median_found: Mapped[float | None] = mapped_column(Float, nullable=True)243    consecutive_failures: Mapped[int] = mapped_column(244        Integer, nullable=False, default=0245    )246    message: Mapped[str | None] = mapped_column(Text, nullable=True)247    # Dernière apparition de la source dans une fenêtre de sync (pilote le248    # retrait ``retired`` des sources disparues du journal).249    last_seen: Mapped[datetime.datetime | None] = mapped_column(250        DateTime(timezone=True), nullable=True251    )252