Python 67%
TypeScript 18.2%
CSS 14.4%
1# -----------------------------------------------------------------------------2# Immo-Ka — Agrégateur de maisons à vendre (province de Québec)3# Auteur : Simon-Pierre Boucher — contact@spboucher.ai4# mortgage/store.py : persistance des taux hypothécaires (data/mortgage.db).5# Historisation par périodes de validité : une ligne « courante » par produit6# (valid_to IS NULL) ; un changement de taux ferme la ligne et en ouvre une7# nouvelle. Aucune donnée n'est jamais écrasée — l'historique complet se8# reconstruit par produit. Santé des providers dans provider_runs.9# -----------------------------------------------------------------------------10from __future__ import annotations1112import hashlib13import json14import sqlite315import statistics16import time17from pathlib import Path1819DB_PATH = Path(__file__).resolve().parent.parent.parent / "data" / "mortgage.db"2021# Un taux « courant » plus vieux que STALE_H heures est signalé périmé.22STALE_H = 2423# Rejet des sauts aberrants : variation > JUMP_MAX points en < JUMP_WINDOW_H h.24JUMP_MAX = 2.525JUMP_WINDOW_H = 482627_SCHEMA = """28CREATE TABLE IF NOT EXISTS rate_observations (29 id INTEGER PRIMARY KEY AUTOINCREMENT,30 provider TEXT NOT NULL,31 institution TEXT NOT NULL,32 product_key TEXT NOT NULL, -- empreinte identité produit33 product_name TEXT,34 rate_type TEXT NOT NULL, -- fixed|variable|adjustable|other35 term_months INTEGER NOT NULL,36 kind TEXT NOT NULL, -- posted|special37 rate REAL NOT NULL, -- taux contractuel en % (ex. 4.19)38 apr REAL,39 insured_status TEXT DEFAULT 'unknown', -- insured|insurable|uninsured|unknown40 purpose TEXT DEFAULT 'purchase', -- purchase|renewal|refinance|unknown41 amortization_max_years INTEGER,42 conditions TEXT,43 source_url TEXT,44 confidence REAL DEFAULT 1.0,45 raw TEXT, -- JSON : observation brute (audit)46 valid_from REAL NOT NULL,47 valid_to REAL, -- NULL = taux courant48 last_checked REAL NOT NULL49);50CREATE INDEX IF NOT EXISTS idx_rateobs_current51 ON rate_observations(provider, product_key, valid_to);52CREATE INDEX IF NOT EXISTS idx_rateobs_lookup53 ON rate_observations(rate_type, term_months, valid_to);5455CREATE TABLE IF NOT EXISTS provider_runs (56 id INTEGER PRIMARY KEY AUTOINCREMENT,57 provider TEXT NOT NULL,58 ts REAL NOT NULL,59 ok INTEGER,60 status TEXT, -- success|http_error|parser_error|validation_error|empty61 products INTEGER,62 changed INTEGER,63 rejected INTEGER,64 duration_ms INTEGER,65 message TEXT66);67CREATE INDEX IF NOT EXISTS idx_provider_runs ON provider_runs(provider, ts);6869-- Préparé pour les notifications futures (« alerte-moi si le 5 ans fixe70-- passe sous 3,99 % » / « si le paiement de cette propriété passe sous X $ »).71CREATE TABLE IF NOT EXISTS rate_alerts (72 id INTEGER PRIMARY KEY AUTOINCREMENT,73 created REAL NOT NULL,74 kind TEXT NOT NULL, -- rate_below|payment_below75 params TEXT, -- JSON : {rate_type, term_months, uid, down…}76 threshold REAL NOT NULL,77 contact TEXT,78 active INTEGER DEFAULT 1,79 fired_at REAL80);81"""8283_SCHEMA_READY = False848586def connect() -> sqlite3.Connection:87 global _SCHEMA_READY88 DB_PATH.parent.mkdir(parents=True, exist_ok=True)89 con = sqlite3.connect(DB_PATH, timeout=60)90 con.row_factory = sqlite3.Row91 con.execute("PRAGMA journal_mode=WAL")92 con.execute("PRAGMA synchronous=NORMAL")93 con.execute("PRAGMA busy_timeout=120000")94 if not _SCHEMA_READY:95 con.executescript(_SCHEMA)96 con.commit()97 _SCHEMA_READY = True98 return con99100101def product_key(p: dict) -> str:102 """Identité stable d'un produit : institution + type + terme + nature du103 taux + assurabilité + objet + nom. Deux produits incompatibles ne104 partagent jamais la même clé (règle : ne jamais comparer l'incomparable)."""105 ident = "|".join([106 p["provider"], p["rate_type"], str(p["term_months"]), p["kind"],107 p.get("insured_status") or "unknown", p.get("purpose") or "purchase",108 (p.get("product_name") or "").strip().lower(),109 ])110 return hashlib.sha1(ident.encode()).hexdigest()[:16]111112113def record_observations(con: sqlite3.Connection, provider: str,114 products: list[dict]) -> dict:115 """Enregistre une passe de collecte validée.116117 Pour chaque produit : si le taux courant est identique → simple mise à118 jour de last_checked ; s'il a changé → fermeture de la période et119 insertion d'une nouvelle ligne. Rejette les sauts aberrants (> JUMP_MAX120 points en < JUMP_WINDOW_H h) sans écraser la bonne donnée précédente.121 Retourne {seen, changed, rejected, rejected_details}."""122 now = time.time()123 changed = 0124 rejected: list[str] = []125 for p in products:126 pk = product_key(p)127 cur = con.execute(128 "SELECT id, rate, last_checked FROM rate_observations "129 "WHERE provider=? AND product_key=? AND valid_to IS NULL",130 (provider, pk)).fetchone()131 if cur is not None:132 if abs(cur["rate"] - p["rate"]) < 1e-9:133 con.execute(134 "UPDATE rate_observations SET last_checked=?, apr=?, "135 "conditions=?, source_url=? WHERE id=?",136 (now, p.get("apr"), p.get("conditions"),137 p.get("source_url"), cur["id"]))138 continue139 # Garde-fou anti-aberration : ne jamais écraser une donnée saine140 # par un saut manifestement impossible.141 age_h = (now - (cur["last_checked"] or now)) / 3600.0142 if abs(cur["rate"] - p["rate"]) > JUMP_MAX and age_h < JUMP_WINDOW_H:143 rejected.append(144 f"{p.get('product_name') or pk}: {cur['rate']} -> "145 f"{p['rate']} (saut aberrant)")146 continue147 con.execute("UPDATE rate_observations SET valid_to=? WHERE id=?",148 (now, cur["id"]))149 changed += 1150 con.execute(151 "INSERT INTO rate_observations (provider, institution, "152 "product_key, product_name, rate_type, term_months, kind, rate, "153 "apr, insured_status, purpose, amortization_max_years, "154 "conditions, source_url, confidence, raw, valid_from, "155 "valid_to, last_checked) "156 "VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,NULL,?)",157 (provider, p["institution"], pk, p.get("product_name"),158 p["rate_type"], p["term_months"], p["kind"], p["rate"],159 p.get("apr"), p.get("insured_status") or "unknown",160 p.get("purpose") or "purchase", p.get("amortization_max_years"),161 p.get("conditions"), p.get("source_url"),162 p.get("confidence", 1.0),163 json.dumps(p.get("raw"), ensure_ascii=False) if p.get("raw") else None,164 now, now))165 con.commit()166 return {"seen": len(products), "changed": changed,167 "rejected": len(rejected), "rejected_details": rejected}168169170def _row(r: sqlite3.Row) -> dict:171 d = dict(r)172 d.pop("raw", None)173 now = time.time()174 d["age_minutes"] = round((now - (d.get("last_checked") or now)) / 60)175 d["stale"] = d["age_minutes"] > STALE_H * 60176 return d177178179def current_rates(con: sqlite3.Connection, rate_type: str | None = None,180 term_months: int | None = None, kind: str | None = None,181 insured_status: str | None = None,182 purpose: str | None = None,183 provider: str | None = None) -> list[dict]:184 """Taux courants (dernière donnée valide par produit), filtrables."""185 q = ("SELECT * FROM rate_observations WHERE valid_to IS NULL")186 args: list = []187 for col, val in (("rate_type", rate_type), ("term_months", term_months),188 ("kind", kind), ("provider", provider),189 ("purpose", purpose)):190 if val is not None:191 q += f" AND {col}=?"192 args.append(val)193 if insured_status is not None:194 q += " AND insured_status IN (?, 'unknown')"195 args.append(insured_status)196 q += " ORDER BY provider, term_months, rate"197 return [_row(r) for r in con.execute(q, args).fetchall()]198199200def best_rate(con: sqlite3.Connection, rate_type: str = "fixed",201 term_months: int = 60, insured_status: str | None = None,202 purpose: str = "purchase") -> dict | None:203 """Meilleur taux courant pour un produit comparable (privilégie les taux204 « special », sinon posted). Retourne la ligne complète + contexte."""205 rows = current_rates(con, rate_type=rate_type, term_months=term_months,206 insured_status=insured_status, purpose=purpose)207 if not rows:208 return None209 # Un seul candidat par institution : special prioritaire, sinon posted.210 by_inst: dict[str, dict] = {}211 for r in rows:212 cur = by_inst.get(r["provider"])213 if cur is None:214 by_inst[r["provider"]] = r215 elif r["kind"] == "special" and cur["kind"] == "posted":216 by_inst[r["provider"]] = r217 elif r["kind"] == cur["kind"] and r["rate"] < cur["rate"]:218 by_inst[r["provider"]] = r219 candidates = sorted(by_inst.values(), key=lambda r: r["rate"])220 best = candidates[0]221 rates = [r["rate"] for r in candidates]222 return {223 **best,224 "median_rate": round(statistics.median(rates), 2) if rates else None,225 "institutions_count": len(candidates),226 "per_institution": candidates,227 }228229230def history(con: sqlite3.Connection, rate_type: str, term_months: int,231 provider: str | None = None, kind: str | None = None,232 days: int = 365) -> list[dict]:233 """Historique : périodes de validité (valid_from/valid_to) par produit."""234 since = time.time() - days * 86400235 q = ("SELECT provider, institution, product_name, kind, rate, "236 "insured_status, valid_from, valid_to, last_checked, source_url "237 "FROM rate_observations WHERE rate_type=? AND term_months=? "238 "AND (valid_to IS NULL OR valid_to >= ?)")239 args: list = [rate_type, term_months, since]240 if provider:241 q += " AND provider=?"242 args.append(provider)243 if kind:244 q += " AND kind=?"245 args.append(kind)246 q += " ORDER BY provider, valid_from"247 return [dict(r) for r in con.execute(q, args).fetchall()]248249250def rate_at(con: sqlite3.Connection, rate_type: str, term_months: int,251 ts: float, kind: str = "special") -> float | None:252 """Meilleur taux observé (toutes institutions) à un instant donné."""253 rows = con.execute(254 "SELECT MIN(rate) AS r FROM rate_observations "255 "WHERE rate_type=? AND term_months=? AND kind=? AND valid_from<=? "256 "AND (valid_to IS NULL OR valid_to>?) AND last_checked>=?",257 (rate_type, term_months, kind, ts, ts, ts - 14 * 86400)).fetchone()258 if rows is None or rows["r"] is None:259 # repli : posted si aucun special à cette date260 rows = con.execute(261 "SELECT MIN(rate) AS r FROM rate_observations "262 "WHERE rate_type=? AND term_months=? AND valid_from<=? "263 "AND (valid_to IS NULL OR valid_to>?)",264 (rate_type, term_months, ts, ts)).fetchone()265 return rows["r"] if rows else None266267268def market_stats(con: sqlite3.Connection, rate_type: str = "fixed",269 term_months: int = 60) -> dict | None:270 """Métriques Mortgage Intelligence : meilleur, médian, spread,271 variations 7/30/90 jours, plus bas observé 6 mois."""272 best = best_rate(con, rate_type=rate_type, term_months=term_months)273 if best is None:274 return None275 now = time.time()276 out = {277 "rate_type": rate_type,278 "term_months": term_months,279 "best": best["rate"],280 "best_provider": best["provider"],281 "best_institution": best["institution"],282 "best_kind": best["kind"],283 "median": best["median_rate"],284 "spread": (round(best["median_rate"] - best["rate"], 2)285 if best["median_rate"] is not None else None),286 "institutions_count": best["institutions_count"],287 }288 for label, days in (("var_7d", 7), ("var_30d", 30), ("var_90d", 90)):289 past = rate_at(con, rate_type, term_months, now - days * 86400)290 out[label] = round(best["rate"] - past, 2) if past is not None else None291 low = con.execute(292 "SELECT MIN(rate) AS r FROM rate_observations "293 "WHERE rate_type=? AND term_months=? AND kind='special' "294 "AND last_checked >= ?",295 (rate_type, term_months, now - 182 * 86400)).fetchone()296 out["lowest_6m"] = low["r"] if low and low["r"] is not None else None297 return out298299300def log_run(con: sqlite3.Connection, provider: str, ok: bool, status: str,301 products: int = 0, changed: int = 0, rejected: int = 0,302 duration_ms: int = 0, message: str = "") -> None:303 con.execute(304 "INSERT INTO provider_runs (provider, ts, ok, status, products, "305 "changed, rejected, duration_ms, message) VALUES (?,?,?,?,?,?,?,?,?)",306 (provider, time.time(), 1 if ok else 0, status, products, changed,307 rejected, duration_ms, message[:500]))308 con.commit()309310311def provider_health(con: sqlite3.Connection) -> list[dict]:312 """Dernier état de chaque provider : OK / WARNING / ERROR + fraîcheur."""313 rows = con.execute(314 "SELECT p.* FROM provider_runs p JOIN (SELECT provider, MAX(ts) AS m "315 "FROM provider_runs GROUP BY provider) x "316 "ON p.provider=x.provider AND p.ts=x.m ORDER BY p.provider").fetchall()317 now = time.time()318 out = []319 for r in rows:320 d = dict(r)321 n_current = con.execute(322 "SELECT COUNT(*) AS n, MAX(last_checked) AS mc "323 "FROM rate_observations WHERE provider=? AND valid_to IS NULL",324 (r["provider"],)).fetchone()325 age_min = round((now - r["ts"]) / 60)326 if r["ok"] and n_current["n"] > 0:327 level = "WARNING" if age_min > STALE_H * 60 or r["rejected"] else "OK"328 elif n_current["n"] > 0:329 level = "WARNING" # échec récent mais données valides conservées330 else:331 level = "ERROR"332 d.update({"level": level, "age_minutes": age_min,333 "current_products": n_current["n"],334 "last_data_at": n_current["mc"]})335 out.append(d)336 return out337