Python 68.8%
TypeScript 18.6%
CSS 8.7%
JavaScript 3.3%
HTML 0.6%
1# -----------------------------------------------------------------------------2# Rent-Ka — Rental listings aggregator (Canada, outside Québec)3# Author: Simon-Pierre Boucher — contact@spboucher.ai4# db.py : persistance SQLite — upsert avec détection de changements,5# cycle de vie avec délai de grâce (2 syncs), détection de dérive,6# cache des pages détail, cache de géocodage.7# -----------------------------------------------------------------------------8from __future__ import annotations910import json11import sqlite312import statistics13import time14from pathlib import Path1516from .schema import Listing1718DB_PATH = Path(__file__).resolve().parent.parent / "data" / "rentka.db"1920# Nombre d'exécutions consécutives où une annonce doit être absente de la21# source avant d'être désactivée (délai de grâce contre les ratés ponctuels).22MISS_GRACE = 22324# Dérive : si une source retourne <= DRIFT_RATIO × sa médiane historique25# (médiane >= DRIFT_MIN_BASE annonces), on alerte et on suspend les retraits.26DRIFT_RATIO = 0.2527DRIFT_MIN_BASE = 828DRIFT_HISTORY = 52930_SCHEMA = """31CREATE TABLE IF NOT EXISTS listings (32 uid TEXT PRIMARY KEY,33 source TEXT NOT NULL,34 external_id TEXT NOT NULL,35 url TEXT,36 title TEXT,37 address TEXT,38 sector TEXT,39 city TEXT,40 unit_type TEXT,41 bedrooms REAL,42 bathrooms REAL,43 price REAL,44 price_label TEXT,45 availability TEXT,46 availability_date TEXT,47 area_sqft REAL,48 pets TEXT,49 furnished INTEGER,50 description TEXT,51 amenities TEXT, -- JSON (liste de textes source)52 details TEXT, -- JSON (champs structurés : inclusions, parking…)53 images TEXT, -- JSON54 lat REAL,55 lng REAL,56 geocode_failed INTEGER DEFAULT 0,57 content_hash TEXT,58 first_seen REAL,59 last_seen REAL,60 updated_at REAL,61 miss_count INTEGER DEFAULT 0,62 active INTEGER DEFAULT 163);64CREATE INDEX IF NOT EXISTS idx_listings_source ON listings(source);65CREATE INDEX IF NOT EXISTS idx_listings_city ON listings(city);66CREATE INDEX IF NOT EXISTS idx_listings_active ON listings(active);67CREATE INDEX IF NOT EXISTS idx_listings_geo ON listings(lat, lng);6869CREATE TABLE IF NOT EXISTS sync_log (70 id INTEGER PRIMARY KEY AUTOINCREMENT,71 source TEXT,72 ts REAL,73 found INTEGER,74 added INTEGER,75 updated INTEGER,76 removed INTEGER,77 ok INTEGER,78 message TEXT,79 stats TEXT -- JSON : taux de champs null, missed, alerte…80);8182CREATE TABLE IF NOT EXISTS detail_cache (83 source TEXT NOT NULL,84 external_id TEXT NOT NULL,85 key TEXT, -- hash du contenu « liste » de l'annonce86 payload TEXT, -- JSON opaque propre au connecteur87 fetched_at REAL,88 PRIMARY KEY (source, external_id)89);9091CREATE TABLE IF NOT EXISTS price_log (92 uid TEXT NOT NULL,93 ts REAL NOT NULL,94 price REAL -- prix observé (NULL = retiré de l'affichage)95);96CREATE INDEX IF NOT EXISTS idx_price_log_uid ON price_log(uid);9798CREATE TABLE IF NOT EXISTS poi_cache (99 coord_key TEXT PRIMARY KEY, -- "lat,lng" arrondi à 4 décimales (~11 m)100 lat REAL,101 lng REAL,102 pois TEXT, -- JSON : [{cat, name, dist_m}] (plus proche/catégorie)103 ts REAL104);105106CREATE TABLE IF NOT EXISTS geocode_cache (107 address TEXT PRIMARY KEY, -- adresse normalisée (clé de cache)108 lat REAL,109 lng REAL,110 provider TEXT,111 failed INTEGER DEFAULT 0,112 ts REAL113);114115CREATE TABLE IF NOT EXISTS users (116 id INTEGER PRIMARY KEY AUTOINCREMENT,117 google_sub TEXT UNIQUE, -- identifiant stable Google (OpenID « sub »)118 ka_id TEXT, -- identifiant membre « ka-0123456789 » (unique)119 email TEXT,120 name TEXT,121 picture TEXT, -- URL de l'avatar Google122 public INTEGER DEFAULT 0, -- profil public /u/{ka_id} (opt-in)123 role TEXT, -- « locataire » | « gestionnaire » | NULL124 org_source TEXT, -- source réclamée (gestionnaire)125 created_at REAL,126 last_login REAL127);128129CREATE TABLE IF NOT EXISTS favorites (130 user_id INTEGER NOT NULL,131 uid TEXT NOT NULL, -- listings.uid132 ts REAL,133 PRIMARY KEY (user_id, uid)134);135136CREATE TABLE IF NOT EXISTS image_checks (137 url TEXT PRIMARY KEY, -- URL d'image vérifiée (imgcheck.py)138 status TEXT, -- ok | dead | small | error139 width INTEGER,140 height INTEGER,141 bytes INTEGER,142 sha1 TEXT, -- hash du contenu (doublons / placeholders)143 checked_at REAL144);145146CREATE TABLE IF NOT EXISTS env_tiles (147 tile_key TEXT PRIMARY KEY, -- « ty,tx » (tuiles 0,5° — voir poi.py/environment.py)148 data TEXT, -- JSON : routes/rails/aéro/industriel/bars/cyclable/POI149 fetched_at REAL150);151152CREATE TABLE IF NOT EXISTS kascores (153 coord_key TEXT PRIMARY KEY, -- « lat,lng » arrondi à 4 décimales (immeuble)154 lat REAL, lng REAL,155 walk REAL, -- KA Walk Score 0-100 (NULL = données insuffisantes)156 transit REAL, -- KA Transit Score (NULL = non desservi/inconnu)157 bike REAL,158 calme REAL,159 services REAL,160 global REAL, -- moyenne pondérée par défaut (voir kascores.py)161 details TEXT, -- JSON : détail par score (fiche + méthodologie)162 version TEXT, -- version du barème (recalcul si changement)163 computed_at REAL164);165166CREATE TABLE IF NOT EXISTS source_profiles (167 source_id TEXT PRIMARY KEY, -- id de la source (data/sources.json)168 owner_user_id INTEGER, -- gestionnaire qui a réclamé la page169 tagline TEXT, -- accroche personnalisée170 description TEXT,171 website TEXT,172 phone TEXT,173 email TEXT,174 logo_url TEXT,175 updated_at REAL176);177178CREATE TABLE IF NOT EXISTS listing_events (179 id INTEGER PRIMARY KEY AUTOINCREMENT,180 uid TEXT NOT NULL, -- listings.uid181 ts REAL NOT NULL,182 event TEXT NOT NULL, -- description | superficie | dispo |183 -- inclusions | photos | disparition |184 -- reapparition (prix -> price_log)185 old TEXT, -- valeur avant (JSON compact)186 new TEXT -- valeur après (JSON compact)187);188CREATE INDEX IF NOT EXISTS idx_levents_uid ON listing_events(uid, ts);189190CREATE TABLE IF NOT EXISTS buildings (191 bkey TEXT PRIMARY KEY, -- clé d'adresse « civique|rue|ville »192 -- (dedup._parse_address) ou « geo:lat,lng »193 address TEXT, -- adresse d'affichage (la plus fréquente)194 city TEXT,195 lat REAL, lng REAL,196 stats TEXT, -- JSON : unités, annonces, loyers,197 -- rotation, pression, gestionnaires…198 version TEXT, -- version du calcul (building.py)199 computed_at REAL200);201202CREATE TABLE IF NOT EXISTS recycled_cache (203 uid TEXT PRIMARY KEY, -- listings.uid204 matches TEXT, -- JSON : correspondances probables + signaux205 computed_at REAL206);207208CREATE TABLE IF NOT EXISTS managers (209 id INTEGER PRIMARY KEY AUTOINCREMENT,210 source_id TEXT UNIQUE, -- id de la source (data/sources.json)211 name TEXT, -- nom canonique212 aliases TEXT, -- JSON : variantes de nom213 website TEXT,214 phone TEXT,215 -- résolution Google Maps (SerpApi) — voir rentka/managers.py216 gmaps_place_id TEXT,217 gmaps_data_id TEXT,218 gmaps_name TEXT,219 gmaps_address TEXT,220 gmaps_rating REAL,221 gmaps_reviews INTEGER, -- nombre d'avis annoncé par Google222 match_confidence REAL, -- 0..1 (association refusée si trop faible)223 match_signals TEXT, -- JSON : signaux ayant fondé l'association224 resolved_at REAL,225 resolve_failed INTEGER DEFAULT 0,226 review_stats TEXT, -- JSON : distribution, tendance, thèmes227 last_synced_at REAL228);229230CREATE TABLE IF NOT EXISTS manager_reviews (231 id INTEGER PRIMARY KEY AUTOINCREMENT,232 manager_id INTEGER NOT NULL,233 external_review_id TEXT, -- id Google (déduplication)234 source TEXT DEFAULT 'google_maps',235 rating REAL,236 text TEXT,237 published_at TEXT, -- ISO si dérivable238 relative_date_raw TEXT, -- « il y a 3 mois » (texte source)239 author_name TEXT,240 author_review_count INTEGER,241 owner_response TEXT,242 owner_response_date TEXT,243 source_url TEXT,244 fetched_at REAL,245 content_hash TEXT, -- déduplication de secours246 analysis TEXT -- JSON : topics/sentiment/severity (managers.py)247);248CREATE INDEX IF NOT EXISTS idx_mreviews_mgr ON manager_reviews(manager_id);249CREATE UNIQUE INDEX IF NOT EXISTS idx_mreviews_ext250 ON manager_reviews(manager_id, external_review_id);251"""252253# Colonnes ajoutées après la v1 — migration automatique des bases existantes.254_MIGRATIONS = {255 "listings": {256 "availability_date": "TEXT",257 "area_sqft": "REAL",258 "pets": "TEXT",259 "furnished": "INTEGER",260 "details": "TEXT",261 "geocode_failed": "INTEGER DEFAULT 0",262 "miss_count": "INTEGER DEFAULT 0",263 "dauid": "TEXT", # aire de diffusion 2021 (stats de quartier)264 "digest": "TEXT", # JSON rentka/textmine.py (description structurée)265 "dup_of": "TEXT", # uid de l'annonce canonique si doublon inter-sources266 "dup_sources": "TEXT", # JSON : autres sources où l'annonce est publiée267 "bedrooms": "REAL", # chambres fermées (convention QC : 4½ = 2)268 "bathrooms": "REAL", # salles de bain (1.5 = salle d'eau en plus)269 "completeness": "REAL", # score de complétude 0–100 (quality.py)270 "published": "INTEGER DEFAULT 1", # 0 = quarantaine (sous le seuil)271 "quality_reasons": "TEXT", # JSON : raisons de la quarantaine272 "images_ok": "TEXT", # JSON : galerie nettoyée (imgcheck.py)273 "img_audit": "TEXT", # JSON : traçabilité du contrôle images274 "coord_key": "TEXT", # clé immeuble « lat,lng » 4 déc. (jointure kascores)275 "building_key": "TEXT", # clé immeuble par adresse (buildings.bkey)276 "province": "TEXT DEFAULT 'QC'", # « QC » | « ON » (expansion Ontario 2026-08)277 },278 "sync_log": {279 "stats": "TEXT",280 },281 "users": {282 "ka_id": "TEXT",283 "public": "INTEGER DEFAULT 0",284 "role": "TEXT",285 "org_source": "TEXT",286 "display_name": "TEXT", # nom choisi (prime sur le nom Google)287 "avatar_url": "TEXT", # photo téléversée (prime sur picture)288 "bio": "TEXT",289 "city": "TEXT",290 "phone": "TEXT",291 "website": "TEXT",292 "socials": "TEXT", # JSON {instagram, facebook, x, linkedin…}293 },294}295296297def connect() -> sqlite3.Connection:298 DB_PATH.parent.mkdir(parents=True, exist_ok=True)299 con = sqlite3.connect(DB_PATH)300 con.row_factory = sqlite3.Row301 con.executescript(_SCHEMA)302 for table, cols in _MIGRATIONS.items():303 existing = {r["name"] for r in con.execute(f"PRAGMA table_info({table})")}304 for col, decl in cols.items():305 if col not in existing:306 con.execute(f"ALTER TABLE {table} ADD COLUMN {col} {decl}")307 # index sur des colonnes de migration : APRÈS les ALTER TABLE (une base308 # antérieure n'a pas encore la colonne au moment du CREATE INDEX)309 con.execute("CREATE UNIQUE INDEX IF NOT EXISTS users_ka_id ON users(ka_id)")310 con.execute("CREATE INDEX IF NOT EXISTS idx_listings_pub"311 " ON listings(active, published)")312 con.execute("CREATE INDEX IF NOT EXISTS idx_listings_coord"313 " ON listings(coord_key)")314 con.execute("CREATE INDEX IF NOT EXISTS idx_listings_bkey"315 " ON listings(building_key)")316 con.commit()317 # WAL : lectures (web) et écritures (sync, imgcheck) concurrentes sans318 # verrou global ; busy_timeout évite les « database is locked » ponctuels.319 con.execute("PRAGMA journal_mode=WAL")320 con.execute("PRAGMA busy_timeout=30000")321 return con322323324# ---------------------------------------------------------------------------325# Synchronisation d'une source326# ---------------------------------------------------------------------------327328def _drift_alert(con: sqlite3.Connection, source: str, found: int,329 null_price_rate: float) -> str | None:330 """Détecte une dérive du connecteur (chute du volume ou des prix extraits).331332 Retourne un message d'alerte, ou None si tout est normal.333 """334 hist = con.execute(335 "SELECT found, stats FROM sync_log WHERE source=? AND ok=1"336 " ORDER BY ts DESC LIMIT ?", (source, DRIFT_HISTORY)).fetchall()337 if len(hist) < 3:338 return None339 med_found = statistics.median(r["found"] for r in hist)340 if med_found >= DRIFT_MIN_BASE and found <= DRIFT_RATIO * med_found:341 return (f"dérive: {found} annonce(s) trouvée(s) contre une médiane de "342 f"{med_found:.0f} — retraits suspendus, vérifier le connecteur")343 if found >= DRIFT_MIN_BASE and null_price_rate >= 0.8:344 rates = []345 for r in hist:346 try:347 rates.append(json.loads(r["stats"] or "{}")["null_price_rate"])348 except (KeyError, ValueError, TypeError):349 continue350 if rates and statistics.median(rates) <= 0.3:351 return (f"dérive: {null_price_rate:.0%} des annonces sans prix "352 f"(habituellement {statistics.median(rates):.0%}) — "353 "le format de la source a probablement changé")354 return None355356357def _j(v) -> str:358 return json.dumps(v, ensure_ascii=False)359360361def _log_events(con: sqlite3.Connection, uid: str, ts: float,362 old: sqlite3.Row, new: dict) -> None:363 """Journalise les changements observés d'une annonce (listing_events).364365 Le prix a déjà son historique dédié (price_log) — ici : description,366 superficie, disponibilité, inclusions, photos. Valeurs compactes (pas de367 texte intégral) : l'objectif est la timeline de la fiche, pas l'archive.368 """369 ev: list[tuple[str, str | None, str | None]] = []370 old_desc = (old["description"] or "").strip()371 new_desc = (new.get("description") or "").strip()372 if old_desc != new_desc and (old_desc or new_desc):373 ev.append(("description", _j({"caracteres": len(old_desc)}),374 _j({"caracteres": len(new_desc)})))375 if (old["area_sqft"] or None) != (new.get("area_sqft") or None):376 ev.append(("superficie", _j(old["area_sqft"]), _j(new.get("area_sqft"))))377 if (old["availability_date"] or None) != (new.get("availability_date") or None):378 ev.append(("dispo", _j(old["availability_date"]),379 _j(new.get("availability_date"))))380 try:381 inc_old = (json.loads(old["details"] or "{}") or {}).get("inclusions") or {}382 inc_new = (json.loads(new.get("details") or "{}") or {}).get("inclusions") or {}383 except (ValueError, TypeError):384 inc_old = inc_new = {}385 if inc_old != inc_new:386 keys = set(inc_old) | set(inc_new)387 diff_old = {k: inc_old.get(k) for k in keys if inc_old.get(k) != inc_new.get(k)}388 diff_new = {k: inc_new.get(k) for k in keys if inc_old.get(k) != inc_new.get(k)}389 if diff_old or diff_new:390 ev.append(("inclusions", _j(diff_old), _j(diff_new)))391 try:392 imgs_old = json.loads(old["images"] or "[]")393 imgs_new = json.loads(new.get("images") or "[]")394 except (ValueError, TypeError):395 imgs_old = imgs_new = []396 if set(imgs_old) != set(imgs_new):397 ev.append(("photos", _j({"n": len(imgs_old)}), _j({"n": len(imgs_new)})))398 if ev:399 con.executemany(400 "INSERT INTO listing_events (uid, ts, event, old, new)"401 " VALUES (?,?,?,?,?)",402 [(uid, ts, e, o, n) for e, o, n in ev])403404405def sync_source(con: sqlite3.Connection, source: str,406 listings: list[Listing]) -> dict:407 """Synchronise les annonces d'une source.408409 - nouvelle annonce -> insertion410 - annonce modifiée -> mise à jour (comparaison de content_hash)411 - annonce disparue -> miss_count += 1, puis active=0 après MISS_GRACE412 exécutions consécutives (délai de grâce)413 - dérive détectée -> alerte consignée, retraits suspendus414 """415 now = time.time()416 added = updated = 0417 seen_uids = set()418419 n = len(listings)420 null_price = sum(1 for l in listings if l.price is None)421 null_addr = sum(1 for l in listings if not l.address)422 null_price_rate = round(null_price / n, 3) if n else 0.0423424 alert = _drift_alert(con, source, n, null_price_rate)425426 for lst in listings:427 seen_uids.add(lst.uid)428 h = lst.content_hash()429 row = con.execute(430 "SELECT content_hash, price, images, description, area_sqft,"431 " availability_date, details, active FROM listings WHERE uid=?",432 (lst.uid,)).fetchone()433 # score de complétude + seuil de publication (quarantaine sous le seuil)434 from .quality import evaluate435 q_score, q_pub, q_reasons = evaluate({436 "title": lst.title,437 "price": lst.price, "address": lst.address, "city": lst.city,438 "sector": lst.sector, "lat": lst.lat, "lng": lst.lng,439 "description": lst.description, "images": lst.images,440 "unit_type": lst.unit_type, "bedrooms": lst.bedrooms,441 "bathrooms": lst.bathrooms, "availability": lst.availability,442 "availability_date": lst.availability_date,443 "area_sqft": lst.area_sqft, "amenities": lst.amenities,444 "details": lst.details, "url": lst.url,445 "province": getattr(lst, "province", None) or "QC"})446 params = dict(447 uid=lst.uid, source=lst.source, external_id=lst.external_id,448 url=lst.url, title=lst.title, address=lst.address,449 sector=lst.sector, city=lst.city,450 province=(getattr(lst, "province", None) or "QC"),451 unit_type=lst.unit_type,452 bedrooms=lst.bedrooms, bathrooms=lst.bathrooms,453 price=lst.price, price_label=lst.price_label,454 availability=lst.availability,455 availability_date=lst.availability_date,456 area_sqft=lst.area_sqft, pets=lst.pets,457 furnished=(None if lst.furnished is None else int(lst.furnished)),458 description=lst.description,459 digest=(json.dumps(lst.digest, ensure_ascii=False)460 if getattr(lst, "digest", None) else None),461 amenities=json.dumps(lst.amenities, ensure_ascii=False),462 details=json.dumps(lst.details, ensure_ascii=False),463 images=json.dumps(lst.images, ensure_ascii=False),464 lat=lst.lat, lng=lst.lng, content_hash=h, now=now,465 completeness=q_score, published=int(q_pub),466 quality_reasons=(json.dumps(q_reasons, ensure_ascii=False)467 if q_reasons else None),468 )469 if row is None:470 con.execute(471 """INSERT INTO listings (uid, source, external_id, url, title,472 address, sector, city, province, unit_type, bedrooms,473 bathrooms, price, price_label,474 availability, availability_date, area_sqft, pets, furnished,475 description, digest, amenities, details, images, lat, lng,476 content_hash, first_seen, last_seen, updated_at,477 miss_count, active, completeness, published,478 quality_reasons)479 VALUES (:uid,:source,:external_id,:url,:title,:address,480 :sector,:city,:province,:unit_type,:bedrooms,:bathrooms,481 :price,:price_label,:availability,482 :availability_date,:area_sqft,:pets,:furnished,483 :description,:digest,:amenities,:details,:images,:lat,:lng,484 :content_hash,:now,:now,:now,0,1,485 :completeness,:published,:quality_reasons)""", params)486 if lst.price is not None: # prix initial = point de départ de l'historique487 con.execute("INSERT INTO price_log (uid, ts, price) VALUES (?,?,?)",488 (lst.uid, now, lst.price))489 added += 1490 elif row["content_hash"] != h:491 # COALESCE : ne jamais écraser des coordonnées géocodées par null492 con.execute(493 """UPDATE listings SET url=:url, title=:title,494 address=:address, sector=:sector, city=:city,495 province=:province,496 unit_type=:unit_type, bedrooms=:bedrooms,497 bathrooms=:bathrooms, price=:price,498 price_label=:price_label, availability=:availability,499 availability_date=:availability_date,500 area_sqft=:area_sqft, pets=:pets, furnished=:furnished,501 description=:description, digest=:digest,502 amenities=:amenities, details=:details, images=:images,503 lat=COALESCE(:lat, lat), lng=COALESCE(:lng, lng),504 content_hash=:content_hash, last_seen=:now,505 updated_at=:now, miss_count=0, active=1,506 completeness=:completeness, published=:published,507 quality_reasons=:quality_reasons508 WHERE uid=:uid""", params)509 if (row["images"] or "[]") != params["images"]:510 # galerie modifiée : invalider l'audit d'images (imgcheck.py)511 con.execute("UPDATE listings SET images_ok=NULL, img_audit=NULL"512 " WHERE uid=?", (lst.uid,))513 if lst.price != row["price"]: # changement de prix -> historique514 con.execute("INSERT INTO price_log (uid, ts, price) VALUES (?,?,?)",515 (lst.uid, now, lst.price))516 # timeline de la fiche : changements observés (hors prix)517 _log_events(con, lst.uid, now, row, params)518 if not row["active"]:519 con.execute("INSERT INTO listing_events (uid, ts, event, old, new)"520 " VALUES (?,?,?,?,?)",521 (lst.uid, now, "reapparition", None, None))522 updated += 1523 else:524 if not row["active"]:525 con.execute("INSERT INTO listing_events (uid, ts, event, old, new)"526 " VALUES (?,?,?,?,?)",527 (lst.uid, now, "reapparition", None, None))528 con.execute(529 "UPDATE listings SET last_seen=?, miss_count=0, active=1 WHERE uid=?",530 (now, lst.uid))531532 # Annonces de cette source qui n'apparaissent plus : délai de grâce,533 # puis désactivation. Suspendu si une dérive est détectée.534 removed = missed = 0535 if not alert:536 for r in con.execute(537 "SELECT uid, miss_count FROM listings WHERE source=? AND active=1",538 (source,)).fetchall():539 if r["uid"] in seen_uids:540 continue541 missed += 1542 if r["miss_count"] + 1 >= MISS_GRACE:543 con.execute(544 "UPDATE listings SET active=0, miss_count=?, updated_at=?"545 " WHERE uid=?", (r["miss_count"] + 1, now, r["uid"]))546 con.execute("INSERT INTO listing_events (uid, ts, event, old, new)"547 " VALUES (?,?,?,?,?)",548 (r["uid"], now, "disparition", None, None))549 removed += 1550 else:551 con.execute("UPDATE listings SET miss_count=miss_count+1 WHERE uid=?",552 (r["uid"],))553554 stats = {555 "null_price_rate": null_price_rate,556 "null_address_rate": round(null_addr / n, 3) if n else 0.0,557 "missed": missed,558 }559 if alert:560 stats["alert"] = alert561 con.execute(562 "INSERT INTO sync_log (source, ts, found, added, updated, removed, ok,"563 " message, stats) VALUES (?,?,?,?,?,?,1,?,?)",564 (source, now, n, added, updated, removed, alert or "ok",565 json.dumps(stats, ensure_ascii=False)))566 con.commit()567 out = {"source": source, "found": n, "added": added,568 "updated": updated, "removed": removed}569 if alert:570 out["alert"] = alert571 return out572573574def log_failure(con: sqlite3.Connection, source: str, message: str) -> None:575 con.execute(576 "INSERT INTO sync_log (source, ts, found, added, updated, removed, ok, message)"577 " VALUES (?,?,0,0,0,0,0,?)", (source, time.time(), message))578 con.commit()579580581# ---------------------------------------------------------------------------582# Cache des pages détail (« détail si nouveau/modifié »)583# ---------------------------------------------------------------------------584585def get_cached_detail(con: sqlite3.Connection, source: str,586 external_id: str, key: str) -> dict | None:587 """Payload détail mis en cache si la clé (hash liste) n'a pas changé."""588 row = con.execute(589 "SELECT key, payload FROM detail_cache WHERE source=? AND external_id=?",590 (source, external_id)).fetchone()591 if row and row["key"] == key and row["payload"]:592 try:593 return json.loads(row["payload"])594 except ValueError:595 return None596 return None597598599def put_cached_detail(con: sqlite3.Connection, source: str,600 external_id: str, key: str, payload: dict) -> None:601 con.execute(602 "INSERT INTO detail_cache (source, external_id, key, payload, fetched_at)"603 " VALUES (?,?,?,?,?)"604 " ON CONFLICT(source, external_id) DO UPDATE SET"605 " key=excluded.key, payload=excluded.payload, fetched_at=excluded.fetched_at",606 (source, external_id, key, json.dumps(payload, ensure_ascii=False),607 time.time()))608 con.commit()609