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 : 8 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 JSON3435from src.config import REQUIRED_NODE3637# BIGSERIAL sur PostgreSQL ; INTEGER auto-incrémenté en dev SQLite.38BigIntPK = BigInteger().with_variant(Integer(), "sqlite")39# JSONB sur PostgreSQL ; JSON générique en dev SQLite.40JSONPayload = JSONB().with_variant(JSON(), "sqlite")414243class Base(DeclarativeBase):44    """Base déclarative commune à tous les modèles API-KA."""454647class DataRecordMixin:48    """Colonnes communes aux 8 tables de données des services KA."""4950    id: Mapped[int] = mapped_column(BigIntPK, primary_key=True, autoincrement=True)51    payload: Mapped[dict[str, Any]] = mapped_column(JSONPayload, nullable=False)52    source: Mapped[str] = mapped_column(Text, nullable=False)53    collected_at: Mapped[datetime.datetime] = mapped_column(54        DateTime(timezone=True), nullable=False, server_default=func.now()55    )56    date_key: Mapped[datetime.date] = mapped_column(Date, nullable=False)57    checksum: Mapped[str] = mapped_column(Text, nullable=False)5859    @declared_attr.directive60    def __table_args__(cls) -> tuple:  # noqa: N805 — API declared_attr de SQLAlchemy61        table = cls.__tablename__62        return (63            Index(f"ix_{table}_date_key", "date_key"),64            Index(f"ix_{table}_source_date_key", "source", "date_key"),65            UniqueConstraint(66                "source",67                "date_key",68                "checksum",69                name=f"uq_{table}_source_date_checksum",70            ),71        )727374class LoukaData(DataRecordMixin, Base):75    """Données quotidiennes du service lou-ka."""7677    __tablename__ = "louka_data"787980class ImmokaData(DataRecordMixin, Base):81    """Données quotidiennes du service immo-ka."""8283    __tablename__ = "immoka_data"848586class FoodkaData(DataRecordMixin, Base):87    """Données quotidiennes du service food-ka."""8889    __tablename__ = "foodka_data"909192class AutokaData(DataRecordMixin, Base):93    """Données quotidiennes du service auto-ka."""9495    __tablename__ = "autoka_data"969798class FabrikaData(DataRecordMixin, Base):99    """Données quotidiennes du service fabri-ka."""100101    __tablename__ = "fabrika_data"102103104class RestokaData(DataRecordMixin, Base):105    """Données quotidiennes du service resto-ka."""106107    __tablename__ = "restoka_data"108109110class SortikaData(DataRecordMixin, Base):111    """Données quotidiennes du service sorti-ka."""112113    __tablename__ = "sortika_data"114115116class CreakaData(DataRecordMixin, Base):117    """Données quotidiennes du service crea-ka."""118119    __tablename__ = "creaka_data"120121122DATA_MODELS: dict[str, type[Base]] = {123    "louka": LoukaData,124    "immoka": ImmokaData,125    "foodka": FoodkaData,126    "autoka": AutokaData,127    "fabrika": FabrikaData,128    "restoka": RestokaData,129    "sortika": SortikaData,130    "creaka": CreakaData,131}132133134class CollectionRun(Base):135    """Journal de chaque exécution de collecte (succès, échec ou relance)."""136137    __tablename__ = "collection_runs"138    __table_args__ = (139        Index("ix_collection_runs_service_date_key", "service", "date_key"),140        Index("ix_collection_runs_status", "status"),141    )142143    id: Mapped[int] = mapped_column(BigIntPK, primary_key=True, autoincrement=True)144    service: Mapped[str] = mapped_column(Text, nullable=False)145    date_key: Mapped[datetime.date] = mapped_column(Date, nullable=False)146    status: Mapped[str] = mapped_column(147        Text, nullable=False148    )  # success / failed / retried149    records_count: Mapped[int] = mapped_column(Integer, nullable=False, default=0)150    duration_seconds: Mapped[float] = mapped_column(Float, nullable=False, default=0.0)151    error_message: Mapped[str | None] = mapped_column(Text, nullable=True)152    node: Mapped[str] = mapped_column(Text, nullable=False, default=REQUIRED_NODE)153    started_at: Mapped[datetime.datetime] = mapped_column(154        DateTime(timezone=True), nullable=False155    )156    finished_at: Mapped[datetime.datetime] = mapped_column(157        DateTime(timezone=True), nullable=False158    )159160161class ApiRequest(Base):162    """Journal léger des requêtes HTTP servies par l'API (alimenté par le163    middleware de logging, purge automatique > 90 jours).164165    Sert exclusivement la page /stats : appels par endpoint/jour, latences166    moyennes et p95, taux d'erreur. Le chemin est normalisé (paramètres de167    route repliés) pour garder une cardinalité bornée.168    """169170    __tablename__ = "api_requests"171    __table_args__ = (172        Index("ix_api_requests_ts", "ts"),173        Index("ix_api_requests_endpoint_ts", "endpoint", "ts"),174    )175176    id: Mapped[int] = mapped_column(BigIntPK, primary_key=True, autoincrement=True)177    ts: Mapped[datetime.datetime] = mapped_column(178        DateTime(timezone=True), nullable=False179    )180    method: Mapped[str] = mapped_column(Text, nullable=False, default="GET")181    endpoint: Mapped[str] = mapped_column(Text, nullable=False)182    status: Mapped[int] = mapped_column(Integer, nullable=False)183    duration_ms: Mapped[float] = mapped_column(Float, nullable=False, default=0.0)184