TypeScript 50.6%
JavaScript 46.1%
SQL 2.1%
Shell 0.6%
CSS 0.5%
1// Toveli — synchronisation nocturne : extraits minces (lecture seule) des trois sources → data/toveli.db2//3// acquisition autorisée → validation → normalisation → rapprochement (propriété) → enrichissement4// (valuations, scores) → indexation (FTS) → publication (bascule atomique + pm2 restart)5//6// Sources (toutes en LECTURE SEULE, instantané par requête sqlite3 ATTACH sur le nœud source) :7// • Vrai-Prix (M3U96a) data/immoka.db → annonces à vendre (copie Immo-Ka de la nuit) + vp_eval (estimations)8// • Vrai-Prix (M3U96a) data/vraiprix.db → rôle MAMH (units, licence ouverte Données Québec), transactions, market_index9// • Lou-Ka (M4M64a) data/louka.db → logements à louer canoniques + fairvalue + kascores + price_log10//11// Usage : pnpm sync (complet) SKIP_FETCH=1 pnpm sync (réutilise data/src/*.db)12// NO_RESTART=1 pnpm sync TOVELI_SSH_KEY="" pour utiliser l'agent SSH (laptop)13import Database from "better-sqlite3";14import { execFileSync, spawnSync } from "node:child_process";15import { existsSync, mkdirSync, readFileSync, renameSync, rmSync, statSync, writeFileSync } from "node:fs";16import { homedir } from "node:os";17import path from "node:path";18import {19 addressKey, coordKey, epochToISO, normRegion, rentCategory, saleCategory, sha1Short, displayCity, normCity,20} from "../src/lib/normalize";2122const ROOT = path.resolve(path.dirname(new URL(import.meta.url).pathname), "..");23const DATA = path.join(ROOT, "data");24const SRC = path.join(DATA, "src");25const LIVE = path.join(DATA, "toveli.db");26const NEW = path.join(DATA, "toveli.db.new");27const LOG = path.join(DATA, "sync.log");28mkdirSync(SRC, { recursive: true });2930const env = process.env;31const VP_SSH = env.TOVELI_VP_SSH ?? "simon-pierreboucher@192.168.2.80"; // M3U96a (LAN)32const LK_SSH = env.TOVELI_LK_SSH ?? "simon-pierreboucher@192.168.2.70"; // M4M64a (LAN)33const VP_DIR = env.TOVELI_VP_DIR ?? "/Users/simon-pierreboucher/apps/vrai-prix/data";34const LK_DB = env.TOVELI_LK_DB ?? "/Users/simon-pierreboucher/apps/lou-ka/data/louka.db";35const SSH_KEY = env.TOVELI_SSH_KEY === undefined ? path.join(homedir(), ".ssh/ka_guardian_ed25519") : env.TOVELI_SSH_KEY;36const PM2_APP = env.PM2_APP ?? "toveli";37const T0 = Date.now();3839function log(msg: string) {40 const line = `[${new Date().toISOString().slice(0, 19).replace("T", " ")}] ${msg}`;41 console.log(line);42 try { writeFileSync(LOG, line + "\n", { flag: "a" }); } catch { /* ignore */ }43}44function die(msg: string): never { log(`ÉCHEC : ${msg}`); process.exit(1); }4546function sshArgs(target: string): string[] {47 const a = ["-o", "BatchMode=yes", "-o", "ConnectTimeout=20", "-o", "StrictHostKeyChecking=accept-new"];48 if (SSH_KEY) a.push("-i", SSH_KEY, "-o", "IdentitiesOnly=yes", "-o", "ControlMaster=no", "-o", "ControlPath=none");49 a.push(target);50 return a;51}52function sshRun(target: string, cmd: string, stdin?: string): string {53 const r = spawnSync("ssh", [...sshArgs(target), cmd], { input: stdin, encoding: "utf8", maxBuffer: 64 * 1024 * 1024 });54 if (r.status !== 0) throw new Error(`ssh ${target} « ${cmd.slice(0, 60)} » : ${r.stderr || r.stdout}`);55 return r.stdout;56}57function rsyncBack(target: string, remote: string, local: string) {58 const e = ["ssh", ...sshArgs(target).slice(0, -1)].join(" ");59 const r = spawnSync("rsync", ["-a", "--inplace", "--no-whole-file", "-e", e, `${target}:${remote}`, local], { encoding: "utf8" });60 if (r.status !== 0) throw new Error(`rsync ${remote} : ${r.stderr}`);61}6263/** Exécute un script sqlite3 sur le nœud source : la base source est ouverte telle quelle (aucune écriture), la sortie va dans un fichier /tmp. */64function remoteExtract(target: string, srcDb: string, outName: string, sql: string) {65 const remoteOut = `/tmp/toveli-${outName}.db`;66 const local = path.join(SRC, `${outName}.db`);67 log(` ↳ extraction ${outName} sur ${target.split("@").pop()}…`);68 const script = `.bail on\nATTACH '${remoteOut}' AS out;\n${sql}\nDETACH out;\n`;69 sshRun(target, `rm -f ${remoteOut} && /usr/bin/sqlite3 '${srcDb}'`, script);70 const size = sshRun(target, `stat -f %z ${remoteOut}`).trim();71 log(` ↳ ${outName} : ${(Number(size) / 1e6).toFixed(0)} Mo, rapatriement…`);72 rsyncBack(target, remoteOut, local);73 sshRun(target, `rm -f ${remoteOut}`);74 return local;75}7677const LIVE_SALE = "status='a-vendre' AND published=1 AND active=1 AND dup_hidden=0 AND price>0";78const LIVE_RENT = "active=1 AND published=1 AND dup_of IS NULL AND province='QC' AND price>0";7980function fetchAll() {81 log("1/6 extraction des instantanés (lecture seule)");82 remoteExtract(VP_SSH, `${VP_DIR}/immoka.db`, "immoka", `83 CREATE TABLE out.listings AS SELECT uid, source, external_id, url, title, address, sector, city, region, property_type, price, price_label,84 bedrooms, bathrooms, powder_rooms, area_sqft, lot_sqft, year_built, mls, status, agency, description, features, details, images,85 lat, lng, first_seen, last_seen, updated_at, quality_score FROM listings WHERE ${LIVE_SALE};86 CREATE TABLE out.vp_eval AS SELECT * FROM vp_eval;87 CREATE TABLE out.price_log AS SELECT p.uid, p.ts, p.price FROM price_log p WHERE p.uid IN (SELECT uid FROM out.listings);88 `);89 remoteExtract(VP_SSH, `${VP_DIR}/vraiprix.db`, "vraiprix", `90 CREATE TABLE out.units AS SELECT id_provinc, adresse, apt, municipalite, code_mun, lat, lng, cubf, cubf_libelle, type_prop, annee_construction,91 aire_etages_m2, superficie_terrain_m2, nb_etages, nb_logements, valeur_terrain, valeur_batiment, valeur_role, dat_cond_marche, est_2026, p10, p9092 FROM units WHERE lat IS NOT NULL;93 CREATE TABLE out.transactions AS SELECT * FROM transactions WHERE amount >= 50000 AND lat IS NOT NULL;94 CREATE TABLE out.market_index AS SELECT * FROM market_index;95 `);96 rsyncBack(VP_SSH, `${VP_DIR}/marche-stats.json`, path.join(SRC, "vp-marche-stats.json"));97 remoteExtract(LK_SSH, LK_DB, "louka", `98 CREATE TABLE out.listings AS SELECT uid, source, external_id, url, title, address, sector, city, unit_type, price, price_label, availability,99 availability_date, description, amenities, images, images_ok, lat, lng, first_seen, last_seen, updated_at, area_sqft, pets, furnished, details,100 bedrooms, bathrooms, completeness, building_key, coord_key FROM listings WHERE ${LIVE_RENT};101 CREATE TABLE out.fairvalue AS SELECT f.* FROM fairvalue f WHERE f.uid IN (SELECT uid FROM out.listings);102 CREATE TABLE out.kascores AS SELECT coord_key, lat, lng, walk, transit, bike, calme, services, global, version, computed_at FROM kascores;103 CREATE TABLE out.price_log AS SELECT p.uid, p.ts, p.price FROM price_log p WHERE p.uid IN (SELECT uid FROM out.listings);104 `);105}106107// ─── construction ───────────────────────────────────────────────────────────108function num(v: unknown): number | null { if (v == null || v === "") return null; const n = Number(v); return Number.isFinite(n) ? n : null; }109function jsonArr(v: unknown): string[] { if (!v) return []; try { const a = JSON.parse(String(v)); return Array.isArray(a) ? a.filter((x) => typeof x === "string") : []; } catch { return []; } }110function median(a: number[]): number | null { if (!a.length) return null; const s = [...a].sort((x, y) => x - y); const m = Math.floor(s.length / 2); return s.length % 2 ? s[m] : (s[m - 1] + s[m]) / 2; }111function quantile(a: number[], q: number): number | null { if (!a.length) return null; const s = [...a].sort((x, y) => x - y); return s[Math.min(s.length - 1, Math.floor(q * s.length))]; }112113function build() {114 log("2/6 construction de toveli.db.new");115 rmSync(NEW, { force: true });116 const db = new Database(NEW);117 db.pragma("foreign_keys = OFF");118 db.exec(readFileSync(path.join(ROOT, "scripts/schema.sql"), "utf8"));119 db.exec(`ATTACH '${path.join(SRC, "immoka.db")}' AS ik; ATTACH '${path.join(SRC, "vraiprix.db")}' AS vp; ATTACH '${path.join(SRC, "louka.db")}' AS lk;`);120 // Connexion de lecture séparée : better-sqlite3 interdit d'écrire pendant qu'un itérateur est ouvert sur la même connexion.121 const rd = new Database(":memory:");122 rd.exec(`ATTACH '${path.join(SRC, "immoka.db")}' AS ik; ATTACH '${path.join(SRC, "vraiprix.db")}' AS vp; ATTACH '${path.join(SRC, "louka.db")}' AS lk;`);123 const now = new Date().toISOString();124125 // Sources + provenance126 const nSale = (db.prepare("SELECT count(*) c FROM ik.listings").get() as { c: number }).c;127 const nRent = (db.prepare("SELECT count(*) c FROM lk.listings").get() as { c: number }).c;128 const nTx = (db.prepare("SELECT count(*) c FROM vp.transactions").get() as { c: number }).c;129 const nUnits = (db.prepare("SELECT count(*) c FROM vp.units").get() as { c: number }).c;130 if (nSale < 10000) die(`instantané Immo-Ka suspect (${nSale} annonces vivantes)`);131 if (nRent < 5000) die(`instantané Lou-Ka suspect (${nRent} logements vivants)`);132 const insSrc = db.prepare("INSERT INTO source VALUES (?,?,?,?,?,?,?,?,?)");133 insSrc.run("immo-ka", "Immo-Ka", "listings-sale", "M4M64b→M3U96a", "~/apps/vrai-prix/data/immoka.db", now, nSale, "Agrégateur Groupe Ka ; données factuelles publiques ; photos hotlinkées, droits du site source", "Copie nocturne Vrai-Prix de la base Immo-Ka (03:30) + vp_eval");134 insSrc.run("lou-ka", "Lou-Ka", "listings-rent", "M4M64a", "~/apps/lou-ka/data/louka.db", now, nRent, "Agrégateur Groupe Ka ; données factuelles publiques ; photos hotlinkées, droits du site source", "Logements canoniques actifs (dup_of IS NULL), fairvalue fv-logement-1.0, KA Scores");135 insSrc.run("vrai-prix", "Vrai-Prix", "valuation", "M3U96a", "~/apps/vrai-prix/data/vraiprix.db", now, nTx, "Rôle d'évaluation MAMH (Données Québec, licence ouverte, attribution requise) ; transactions : flux qub.ca — usage interne au moteur, non republié brut", `units ${nUnits}, transactions ${nTx}, market_index`);136137 // Rôle, transactions, indice138 log(" ↳ rôle MAMH + transactions + indice");139 db.exec(`INSERT INTO role_unit SELECT id_provinc, adresse, apt, municipalite, code_mun, lat, lng, cubf, cubf_libelle, type_prop, annee_construction,140 aire_etages_m2, superficie_terrain_m2, nb_logements, nb_etages, valeur_terrain, valeur_batiment, valeur_role, dat_cond_marche, est_2026, p10, p90 FROM vp.units;`);141 db.exec(`INSERT INTO sale_transaction SELECT id, date, amount, street, city, lat, lng, property_type, year_built, floor_area, land_area, id_provinc, valeur_role FROM vp.transactions;`);142 db.exec(`INSERT INTO market_index SELECT month, type_prop, ppm2, idx, n FROM vp.market_index;`);143144 // Scores de quartier145 db.exec(`INSERT OR IGNORE INTO neighbourhood_score SELECT coord_key, lat, lng, walk, transit, bike, calme, services, global, version,146 datetime(computed_at, 'unixepoch'), 'lou-ka' FROM lk.kascores;`);147148 // Annonces → propriétés149 log(" ↳ annonces à vendre (Immo-Ka) → propriétés");150 const insProp = db.prepare(`INSERT OR IGNORE INTO property (id, key_kind, key_value, address, unit, city, region, lat, lng, category, bedrooms, bathrooms,151 area_sqft, lot_sqft, year_built, role_value, role_land, role_building, role_unit_id, dedup_confidence, updated_at)152 VALUES (@id,@key_kind,@key_value,@address,@unit,@city,@region,@lat,@lng,@category,@bedrooms,@bathrooms,@area_sqft,@lot_sqft,@year_built,@role_value,@role_land,@role_building,@role_unit_id,@dedup_confidence,@updated_at)`);153 const insListing = db.prepare(`INSERT OR REPLACE INTO listing (uid, property_id, source_id, connector, external_id, market, url, status, title, description, price, price_label,154 address, unit, city, region, sector, lat, lng, category, type_label, bedrooms, bathrooms, powder_rooms, area_sqft, lot_sqft, year_built, features, details, images,155 image_count, agency, broker, mls, availability, furnished, pets, first_seen, last_seen, updated_at, quality_score, dup_of, provenance)156 VALUES (@uid,@property_id,@source_id,@connector,@external_id,@market,@url,@status,@title,@description,@price,@price_label,@address,@unit,@city,@region,@sector,@lat,@lng,157 @category,@type_label,@bedrooms,@bathrooms,@powder_rooms,@area_sqft,@lot_sqft,@year_built,@features,@details,@images,@image_count,@agency,@broker,@mls,@availability,158 @furnished,@pets,@first_seen,@last_seen,@updated_at,@quality_score,@dup_of,@provenance)`);159 const insVal = db.prepare(`INSERT OR REPLACE INTO valuation (listing_uid, property_id, model, model_version, estimate, low, high, confidence_pct, confidence, method_detail, ratio, diff, verdict, evaluated_at)160 VALUES (@listing_uid,@property_id,@model,@model_version,@estimate,@low,@high,@confidence_pct,@confidence,@method_detail,@ratio,@diff,@verdict,@evaluated_at)`);161 const getUnit = db.prepare("SELECT id, role_value, role_land, role_building FROM role_unit WHERE id = ?");162 const getEval = rd.prepare("SELECT * FROM ik.vp_eval WHERE uid = ?");163164 type Row = Record<string, unknown>;165 const verdictOf = (ratio: number | null) => ratio == null ? "na" : ratio > 1.05 ? "sous" : ratio < 0.95 ? "sur" : "aligne";166167 const txSale = db.transaction(() => {168 let i = 0;169 for (const r of rd.prepare("SELECT * FROM ik.listings").iterate() as Iterable<Row>) {170 const lat = num(r.lat), lng = num(r.lng);171 const city = displayCity(r.city as string);172 const region = normRegion(r.region as string, city);173 const cat = saleCategory(r.property_type as string, r.title as string);174 const ev = getEval.get(r.uid) as Row | undefined;175 // identité : rôle (jumelage spatial ≤ 40 m) > adresse normalisée > cellule de coordonnées176 let keyKind = "coord", keyValue: string | null = null, conf = 0.4, unit: Row | undefined;177 if (ev?.unit_id && num(ev.match_m) != null && (num(ev.match_m) as number) <= 40) {178 unit = getUnit.get(ev.unit_id) as Row | undefined;179 if (unit) { keyKind = "role"; keyValue = String(ev.unit_id); conf = 0.9; }180 }181 if (!keyValue) { const ak = addressKey(r.address as string, city); if (ak) { keyKind = "address"; keyValue = ak; conf = 0.7; } }182 if (!keyValue) { if (lat == null || lng == null) continue; keyValue = coordKey(lat, lng) + "|" + cat; }183 const pid = sha1Short(`${keyKind}:${keyValue}`);184 const images = jsonArr(r.images);185 const details = r.details ? String(r.details) : null;186 insProp.run({187 id: pid, key_kind: keyKind, key_value: keyValue, address: r.address ?? null, unit: null, city, region, lat, lng, category: cat,188 bedrooms: num(r.bedrooms), bathrooms: num(r.bathrooms), area_sqft: num(r.area_sqft), lot_sqft: num(r.lot_sqft), year_built: num(r.year_built),189 role_value: unit ? num(unit.role_value) : num(ev?.valeur_role), role_land: unit ? num(unit.role_land) : null, role_building: unit ? num(unit.role_building) : null,190 role_unit_id: unit ? String(unit.id) : null, dedup_confidence: conf, updated_at: now,191 });192 const uid = `immo-ka:${r.uid}`;193 insListing.run({194 uid, property_id: pid, source_id: "immo-ka", connector: r.source, external_id: r.external_id, market: "sale", url: r.url ?? null, status: "a-vendre",195 title: r.title ?? null, description: r.description ?? null, price: num(r.price), price_label: r.price_label ?? null, address: r.address ?? null, unit: null,196 city, region, sector: r.sector ?? null, lat, lng, category: cat, type_label: r.property_type || null, bedrooms: num(r.bedrooms), bathrooms: num(r.bathrooms),197 powder_rooms: num(r.powder_rooms), area_sqft: num(r.area_sqft), lot_sqft: num(r.lot_sqft), year_built: num(r.year_built),198 features: r.features ? String(r.features) : "[]", details, images: JSON.stringify(images), image_count: images.length,199 agency: r.agency ?? null, broker: null, mls: (r.mls as string) || null, availability: null, furnished: null, pets: null,200 first_seen: epochToISO(r.first_seen as number), last_seen: epochToISO(r.last_seen as number), updated_at: epochToISO(r.updated_at as number),201 quality_score: num(r.quality_score), dup_of: null,202 provenance: JSON.stringify({ source_uid: r.uid, source: "immo-ka", connector: r.source, collected_at: epochToISO(r.last_seen as number), origin_url: r.url ?? null, media: "hotlink-source" }),203 });204 if (ev && num(ev.est)) {205 const est = num(ev.est) as number, price = num(r.price) as number;206 const ratio = price > 0 ? est / price : null;207 insVal.run({208 listing_uid: uid, property_id: pid, model: "vrai-prix-hybride", model_version: "toveli-estim-2026.10 (hédonique 2026-08 + comparables ajustés)", estimate: est, low: num(ev.low), high: num(ev.high),209 confidence_pct: num(ev.confidence_pct), confidence: ev.confidence ?? null,210 method_detail: JSON.stringify({ model_est: num(ev.model_est), comps_est: num(ev.comps_est), model_weight: num(ev.model_weight), n_comps: num(ev.n_comps), cost_est: num(ev.cost_est), role_est: num(ev.role_est), ens_est: num(ev.ens_est), valeur_role: num(ev.valeur_role), type_prop: ev.type_prop, match_m: num(ev.match_m), unit_id: ev.unit_id }),211 ratio, diff: price - est, verdict: verdictOf(ratio), evaluated_at: ev.evaluated_at ?? null,212 });213 }214 if (++i % 20000 === 0) log(` ${i} annonces…`);215 }216 db.exec(`INSERT INTO price_event SELECT 'immo-ka:'||p.uid, strftime('%Y-%m-%dT%H:%M:%SZ', p.ts, 'unixepoch'), p.price, 'price' FROM ik.price_log p JOIN listing l ON l.uid = 'immo-ka:'||p.uid WHERE p.price IS NOT NULL;`);217 });218 txSale();219220 log(" ↳ logements à louer (Lou-Ka) → immeubles/propriétés");221 const getFv = rd.prepare("SELECT * FROM lk.fairvalue WHERE uid = ?");222 const txRent = db.transaction(() => {223 let i = 0;224 for (const r of rd.prepare("SELECT * FROM lk.listings").iterate() as Iterable<Row>) {225 const lat = num(r.lat), lng = num(r.lng);226 const city = displayCity(r.city as string);227 const region = normRegion(null, city);228 const cat = rentCategory(r.unit_type as string);229 let keyKind = "address", keyValue: string | null = addressKey(r.address as string, city), conf = 0.7;230 if (!keyValue && r.building_key) { keyValue = String(r.building_key); conf = 0.6; }231 if (!keyValue) { if (lat == null || lng == null) continue; keyKind = "coord"; keyValue = coordKey(lat, lng) + "|immeuble"; conf = 0.4; }232 const pid = sha1Short(`${keyKind}:${keyValue}`);233 const images = jsonArr(r.images_ok).length ? jsonArr(r.images_ok) : jsonArr(r.images);234 insProp.run({235 id: pid, key_kind: keyKind, key_value: keyValue, address: r.address ?? null, unit: null, city, region, lat, lng, category: cat === "logement" || cat === "chambre" ? "immeuble" : cat,236 bedrooms: null, bathrooms: null, area_sqft: null, lot_sqft: null, year_built: null, role_value: null, role_land: null, role_building: null, role_unit_id: null,237 dedup_confidence: conf, updated_at: now,238 });239 const uid = `lou-ka:${r.uid}`;240 const details = r.details ? String(r.details) : null;241 insListing.run({242 uid, property_id: pid, source_id: "lou-ka", connector: r.source, external_id: r.external_id, market: "rent", url: r.url ?? null, status: "a-louer",243 title: r.title ?? null, description: r.description ?? null, price: num(r.price), price_label: r.price_label ?? null, address: r.address ?? null, unit: null,244 city, region, sector: r.sector ?? null, lat, lng, category: cat, type_label: (r.unit_type as string) || null, bedrooms: num(r.bedrooms), bathrooms: num(r.bathrooms),245 powder_rooms: null, area_sqft: num(r.area_sqft), lot_sqft: null, year_built: null, features: r.amenities ? String(r.amenities) : "[]", details,246 images: JSON.stringify(images), image_count: images.length, agency: null, broker: null, mls: null,247 availability: (r.availability_date as string) || (r.availability as string) || null, furnished: num(r.furnished), pets: (r.pets as string) || null,248 first_seen: epochToISO(r.first_seen as number), last_seen: epochToISO(r.last_seen as number), updated_at: epochToISO(r.updated_at as number),249 quality_score: num(r.completeness), dup_of: null,250 provenance: JSON.stringify({ source_uid: r.uid, source: "lou-ka", connector: r.source, collected_at: epochToISO(r.last_seen as number), origin_url: r.url ?? null, media: "hotlink-source" }),251 });252 const fv = getFv.get(r.uid) as Row | undefined;253 if (fv && num(fv.fv)) {254 const est = num(fv.fv) as number, price = num(r.price) as number;255 const ratio = price > 0 ? est / price : null;256 insVal.run({257 listing_uid: uid, property_id: pid, model: "lou-ka-fairvalue", model_version: (fv.model_version as string) ?? "fv-logement-1.0", estimate: est, low: num(fv.fv_low), high: num(fv.fv_high),258 confidence_pct: null, confidence: (fv.confidence as string) ?? null,259 method_detail: JSON.stringify({ method: fv.method, comps: num(fv.comps), segment: fv.segment, deviation: num(fv.deviation), source_verdict: fv.verdict }),260 ratio, diff: price - est, verdict: verdictOf(ratio), evaluated_at: epochToISO(fv.computed_at as number),261 });262 }263 if (++i % 10000 === 0) log(` ${i} logements…`);264 }265 db.exec(`INSERT INTO price_event SELECT 'lou-ka:'||p.uid, strftime('%Y-%m-%dT%H:%M:%SZ', p.ts, 'unixepoch'), p.price, 'price' FROM lk.price_log p JOIN listing l ON l.uid = 'lou-ka:'||p.uid WHERE p.price IS NOT NULL;`);266 });267 txRent();268269 log("3/6 agrégats par propriété, lieux et métriques de marché");270 {271 type Agg = { property_id: string; n: number; n_sale: number; n_rent: number; min_price: number | null; max_price: number | null; bedrooms: number | null; bathrooms: number | null; area_sqft: number | null; year_built: number | null };272 const aggs = db.prepare(`SELECT property_id, count(*) n, sum(market='sale') n_sale, sum(market='rent') n_rent, min(price) min_price, max(price) max_price,273 max(bedrooms) bedrooms, max(bathrooms) bathrooms, max(area_sqft) area_sqft, max(year_built) year_built FROM listing GROUP BY property_id`).all() as Agg[];274 const up = db.prepare(`UPDATE property SET n_listings=@n, n_sale=@n_sale, n_rent=@n_rent, min_price=@min_price, max_price=@max_price,275 bedrooms=COALESCE(bedrooms,@bedrooms), bathrooms=COALESCE(bathrooms,@bathrooms), area_sqft=COALESCE(area_sqft,@area_sqft), year_built=COALESCE(year_built,@year_built),276 attr_quality = ((lat IS NOT NULL) + (COALESCE(bedrooms,@bedrooms) IS NOT NULL) + (COALESCE(bathrooms,@bathrooms) IS NOT NULL) + (COALESCE(area_sqft,@area_sqft) IS NOT NULL) + (COALESCE(year_built,@year_built) IS NOT NULL) + (role_value IS NOT NULL)) / 6.0277 WHERE id=@property_id`);278 db.transaction(() => { for (const a of aggs) up.run(a); })();279 log(` ${aggs.length} propriétés agrégées`);280 }281282 db.exec(`INSERT INTO place (kind, name, region, lat, lng, n_sale, n_rent)283 SELECT 'city', city, max(region), avg(lat), avg(lng), sum(market='sale'), sum(market='rent') FROM listing WHERE city <> '' AND lat IS NOT NULL GROUP BY city HAVING count(*) >= 3;284 INSERT INTO place (kind, name, region, lat, lng, n_sale, n_rent)285 SELECT 'region', region, region, avg(lat), avg(lng), sum(market='sale'), sum(market='rent') FROM listing WHERE region IS NOT NULL AND lat IS NOT NULL GROUP BY region;`);286287 // Métriques de marché : province / région / ville × marché × catégorie288 const insMetric = db.prepare(`INSERT OR REPLACE INTO market_metric VALUES (@scope,@scope_value,@market,@category,@n,@median_price,@p25_price,@p75_price,@median_ppsf,@median_days,@new_30d,@median_ratio,@computed_at)`);289 const rows = db.prepare(`SELECT l.market, l.category, l.region, l.city, l.price, l.area_sqft, l.first_seen, v.ratio FROM listing l LEFT JOIN valuation v ON v.listing_uid = l.uid WHERE l.price > 0`).all() as Row[];290 const groups = new Map<string, { prices: number[]; ppsf: number[]; days: number[]; new30: number; ratios: number[] }>();291 const nowMs = Date.now();292 const add = (k: string, r: Row) => {293 let g = groups.get(k); if (!g) { g = { prices: [], ppsf: [], days: [], new30: 0, ratios: [] }; groups.set(k, g); }294 const p = r.price as number; g.prices.push(p);295 const a = num(r.area_sqft); if (a && a > 100 && (r.market === "sale")) g.ppsf.push(p / a);296 const fs = r.first_seen ? Date.parse(r.first_seen as string) : NaN; if (!Number.isNaN(fs)) { const d = (nowMs - fs) / 86400000; g.days.push(d); if (d <= 30) g.new30++; }297 const ra = num(r.ratio); if (ra) g.ratios.push(ra);298 };299 for (const r of rows) {300 const m = r.market as string, c = r.category as string;301 for (const cat of [c, "all"]) {302 add(`province|Québec|${m}|${cat}`, r);303 if (r.region) add(`region|${r.region}|${m}|${cat}`, r);304 if (r.city) add(`city|${r.city}|${m}|${cat}`, r);305 }306 }307 const txM = db.transaction(() => {308 for (const [k, g] of groups) {309 if (g.prices.length < 5) continue;310 const [scope, scope_value, market, category] = k.split("|");311 insMetric.run({ scope, scope_value, market, category, n: g.prices.length, median_price: median(g.prices), p25_price: quantile(g.prices, 0.25), p75_price: quantile(g.prices, 0.75),312 median_ppsf: g.ppsf.length >= 5 ? median(g.ppsf) : null, median_days: g.days.length ? Math.round(median(g.days) as number) : null, new_30d: g.new30,313 median_ratio: g.ratios.length >= 10 ? median(g.ratios) : null, computed_at: now });314 }315 });316 txM();317318 log("4/6 index plein texte");319 db.exec(`CREATE VIRTUAL TABLE listing_fts USING fts5(uid UNINDEXED, address, city, title, tokenize="unicode61 remove_diacritics 2");320 INSERT INTO listing_fts SELECT uid, address, city, title FROM listing;321 CREATE VIRTUAL TABLE role_unit_fts USING fts5(id UNINDEXED, address, city, tokenize="unicode61 remove_diacritics 2");322 INSERT INTO role_unit_fts SELECT id, address, city FROM role_unit WHERE address IS NOT NULL;`);323324 const stats = {325 built_at: now, listings_sale: nSale, listings_rent: nRent, properties: (db.prepare("SELECT count(*) c FROM property").get() as { c: number }).c,326 valuations: (db.prepare("SELECT count(*) c FROM valuation").get() as { c: number }).c, transactions: nTx, role_units: nUnits,327 multi_listing_properties: (db.prepare("SELECT count(*) c FROM property WHERE n_listings > 1").get() as { c: number }).c,328 };329 const insMeta = db.prepare("INSERT OR REPLACE INTO meta VALUES (?,?)");330 for (const [k, v] of Object.entries(stats)) insMeta.run(k, String(v));331 insMeta.run("vp_marche_stats", existsSync(path.join(SRC, "vp-marche-stats.json")) ? readFileSync(path.join(SRC, "vp-marche-stats.json"), "utf8") : "{}");332 rd.close();333 db.exec("DETACH ik; DETACH vp; DETACH lk;");334 db.exec("PRAGMA journal_mode = WAL;");335 db.exec("ANALYZE; VACUUM;");336 db.close();337 log(` ${JSON.stringify(stats)}`);338 return stats;339}340341function publish() {342 log("5/6 bascule atomique");343 const sz = statSync(NEW).size;344 if (sz < 50e6) die(`toveli.db.new trop petite (${sz} o)`);345 for (const suf of ["-wal", "-shm"]) rmSync(LIVE + suf, { force: true });346 renameSync(NEW, LIVE);347 log("6/6 redémarrage PM2");348 if (env.NO_RESTART !== "1") {349 try { execFileSync("pm2", ["restart", PM2_APP, "--update-env"], { stdio: "ignore", env: { ...env, PATH: `/opt/homebrew/bin:${env.PATH}` } }); }350 catch { log("avertissement : pm2 restart a échoué (pm2 absent ou app non lancée)"); }351 }352}353354(function main() {355 log(`=== toveli sync — ${new Date().toISOString()} ===`);356 if (env.SKIP_FETCH !== "1") fetchAll(); else log("1/6 extraction sautée (SKIP_FETCH=1)");357 for (const f of ["immoka.db", "vraiprix.db", "louka.db"]) if (!existsSync(path.join(SRC, f))) die(`${f} absent dans data/src`);358 build();359 publish();360 log(`=== terminé en ${((Date.now() - T0) / 1000).toFixed(0)} s ===`);361})();362