TypeScript 50.6%
JavaScript 46.1%
SQL 2.1%
Shell 0.6%
CSS 0.5%
1-- Toveli — base canonique (reconstruite chaque nuit par scripts/sync.ts)2-- Séparation : données source (source_record) / entités canonisées (property, listing)3-- / historique (price_event) / résultats de modèles (valuation) / marché (market_metric).4PRAGMA journal_mode = OFF;5PRAGMA synchronous = OFF;67CREATE TABLE meta (key TEXT PRIMARY KEY, value TEXT);89CREATE TABLE source (10 id TEXT PRIMARY KEY, -- 'immo-ka' | 'lou-ka' | 'vrai-prix'11 label TEXT NOT NULL,12 kind TEXT NOT NULL, -- 'listings-sale' | 'listings-rent' | 'valuation' | 'transactions'13 node TEXT, path TEXT,14 snapshot_at TEXT, -- ISO : prise de l'instantané15 rows INTEGER,16 licence TEXT, notes TEXT17);1819CREATE TABLE property (20 id TEXT PRIMARY KEY, -- identifiant interne stable (hash de la clé canonique)21 key_kind TEXT NOT NULL, -- 'role' (id_provinc MAMH) | 'address' | 'coord'22 key_value TEXT NOT NULL,23 address TEXT, unit TEXT, city TEXT, region TEXT, postal_code TEXT,24 lat REAL, lng REAL,25 category TEXT, -- maison | condo | plex | terrain | commercial | chalet | autre | logement26 bedrooms REAL, bathrooms REAL, area_sqft REAL, lot_sqft REAL, year_built INTEGER,27 role_value REAL, role_land REAL, role_building REAL, role_unit_id TEXT,28 n_listings INTEGER DEFAULT 0, n_sale INTEGER DEFAULT 0, n_rent INTEGER DEFAULT 0,29 min_price REAL, max_price REAL,30 attr_quality REAL, -- 0..1 : complétude des attributs31 dedup_confidence REAL, -- 0..1 : confiance du rapprochement des annonces32 updated_at TEXT33);34CREATE INDEX idx_property_geo ON property(lat, lng);35CREATE INDEX idx_property_city ON property(city);36CREATE INDEX idx_property_key ON property(key_kind, key_value);3738CREATE TABLE listing (39 uid TEXT PRIMARY KEY, -- '<source_id>:<connector>:<external_id>'40 property_id TEXT NOT NULL REFERENCES property(id),41 source_id TEXT NOT NULL, -- immo-ka | lou-ka42 connector TEXT NOT NULL, -- remax_quebec, duproprio, kangalou…43 external_id TEXT,44 market TEXT NOT NULL, -- 'sale' | 'rent'45 url TEXT,46 status TEXT, -- a-vendre | a-louer47 title TEXT, description TEXT,48 price REAL, price_label TEXT,49 address TEXT, unit TEXT, city TEXT, region TEXT, sector TEXT,50 lat REAL, lng REAL,51 category TEXT, type_label TEXT, -- catégorie canonique + libellé source (Maison, 4½…)52 bedrooms REAL, bathrooms REAL, powder_rooms REAL,53 area_sqft REAL, lot_sqft REAL, year_built INTEGER,54 features TEXT, -- JSON []55 details TEXT, -- JSON {}56 images TEXT, -- JSON [] (URL hotlinkées, crédit = connecteur source)57 image_count INTEGER,58 agency TEXT, broker TEXT, -- affichés seulement si fournis par la source59 mls TEXT,60 availability TEXT, furnished INTEGER, pets TEXT,61 first_seen TEXT, last_seen TEXT, updated_at TEXT,62 quality_score REAL,63 dup_of TEXT, -- uid canonique si doublon intra-source conservé pour trace64 provenance TEXT -- JSON : {source_uid, collected_at, terms}65);66CREATE INDEX idx_listing_property ON listing(property_id);67CREATE INDEX idx_listing_market_price ON listing(market, price);68CREATE INDEX idx_listing_city ON listing(city);69CREATE INDEX idx_listing_geo ON listing(lat, lng);70CREATE INDEX idx_listing_cat ON listing(market, category);71CREATE INDEX idx_listing_first_seen ON listing(first_seen);7273CREATE TABLE price_event (74 listing_uid TEXT NOT NULL REFERENCES listing(uid),75 ts TEXT NOT NULL,76 price REAL,77 kind TEXT DEFAULT 'price' -- price | first_seen78);79CREATE INDEX idx_price_event_uid ON price_event(listing_uid, ts);8081CREATE TABLE valuation (82 listing_uid TEXT PRIMARY KEY REFERENCES listing(uid),83 property_id TEXT,84 model TEXT NOT NULL, -- 'vrai-prix-hybride' | 'lou-ka-fairvalue'85 model_version TEXT,86 estimate REAL, low REAL, high REAL,87 confidence_pct REAL, confidence TEXT,88 method_detail TEXT, -- JSON : composantes (model_est, comps_est, cost_est, role_est, n_comps, match_m…)89 ratio REAL, -- estimate / prix demandé90 diff REAL, -- prix demandé − estimate91 verdict TEXT, -- sous | aligne | sur | na92 evaluated_at TEXT93);94CREATE INDEX idx_valuation_property ON valuation(property_id);9596CREATE TABLE neighbourhood_score (97 coord_key TEXT PRIMARY KEY,98 lat REAL, lng REAL,99 walk REAL, transit REAL, bike REAL, calm REAL, services REAL, global REAL,100 version TEXT, computed_at TEXT, source_id TEXT DEFAULT 'lou-ka'101);102103CREATE TABLE sale_transaction (104 id TEXT PRIMARY KEY,105 date TEXT, amount REAL, street TEXT, city TEXT,106 lat REAL, lng REAL, property_type TEXT, year_built INTEGER,107 floor_area REAL, land_area REAL, role_unit_id TEXT, role_value REAL108);109CREATE INDEX idx_tx_geo ON sale_transaction(lat, lng);110CREATE INDEX idx_tx_date ON sale_transaction(date);111CREATE INDEX idx_tx_role ON sale_transaction(role_unit_id);112113CREATE TABLE role_unit ( -- rôle d'évaluation MAMH (Données Québec, licence ouverte) + estimations Vrai-Prix pré-calculées114 id TEXT PRIMARY KEY,115 address TEXT, unit TEXT, city TEXT, code_mun TEXT,116 lat REAL, lng REAL,117 type_prop TEXT, cubf INTEGER, cubf_label TEXT,118 year_built INTEGER, floor_area_m2 REAL, land_area_m2 REAL, n_units INTEGER, n_floors INTEGER,119 role_land REAL, role_building REAL, role_value REAL, role_market_date TEXT,120 est REAL, p10 REAL, p90 REAL121);122CREATE INDEX idx_role_unit_geo ON role_unit(lat, lng);123CREATE INDEX idx_role_unit_city ON role_unit(city);124125CREATE TABLE market_index (month TEXT, type_prop TEXT, ppm2 REAL, idx REAL, n INTEGER, PRIMARY KEY (month, type_prop));126127CREATE TABLE market_metric (128 scope TEXT NOT NULL, -- 'province' | 'region' | 'city'129 scope_value TEXT NOT NULL,130 market TEXT NOT NULL, -- sale | rent131 category TEXT NOT NULL, -- 'all' | maison | condo | plex | logement…132 n INTEGER, median_price REAL, p25_price REAL, p75_price REAL,133 median_ppsf REAL, median_days INTEGER, new_30d INTEGER,134 median_ratio REAL, -- estimation / prix demandé (si valuation)135 computed_at TEXT,136 PRIMARY KEY (scope, scope_value, market, category)137);138139CREATE TABLE place ( -- autocomplétion : municipalités, régions140 kind TEXT NOT NULL, -- city | region141 name TEXT NOT NULL,142 region TEXT,143 lat REAL, lng REAL,144 n_sale INTEGER DEFAULT 0, n_rent INTEGER DEFAULT 0,145 PRIMARY KEY (kind, name)146);147