WP36 — Data access and schema audit (Phase 0)
Date of audit: 2026-11-01 (snapshot taken 2026-11-01 ≈ 02:50 America/Toronto, last sync in snapshot 2026-11-01 06:49:42 UTC). Auditor: lead research agent (Claude Code), on behalf of S.-P. Boucher (UQO). Status of the gate: PASSED for H1, H2, H5 and the cross-sectional/dispersion parts of H4; CONDITIONAL for H3 (association only) and H6 (construction + internal validation only; no external forecasting test possible — see §7).
1. Source inventory (evidence-backed)
| Item | Finding | Evidence |
|---|---|---|
| Platform | Auto-Ka (www.auto-ka.com), independent Quebec used-vehicle aggregator built and operated by the author (Groupe KA). | M4M64b:~/auto-ka/README.md, mld status (node M4M64b, port 8095, PM2 auto-ka-web + auto-ka-sync). |
| Authorized access path | Owner-approved, read-only copy of the production SQLite database (sqlite3 .backup), taken on the production node, personal-account table dropped before transfer. No scraping of Auto-Ka, no use of the public HTTP API, no bypass of robots.txt (Disallow: /api/). |
data/raw/autoka-snapshot-20261101.db (1.35 GB, mode 0444), SHA-256 in data/raw/autoka-snapshot-20261101.db.sha256 (verified after transfer). |
| Documented API | FastAPI endpoints /api/vehicles, /api/vehicles/{uid} (includes price history), /api/stats/*, /api/sources, /api/dealers — documented in README but disallowed to crawlers; not used for this paper (the DB copy is strictly richer). |
README §"API principale". |
| Upstream data-generation process | 143 registered sources (131 dealer websites "direct", 11 portals, 1 marketplace = Kijiji private sellers) crawled by 31 connector modules; each cycle re-reads every source, normalises each listing to one schema, hashes content, logs price changes, and marks a listing inactive after MISS_GRACE = 2 consecutive absences; removals are suspended when a drift alert fires (volume ≤ 25 % of the 5-sync median, or ≥ 80 % null prices). VIN-based cross-source de-duplication elects a canonical listing. |
autoka/db.py lines 150–310, autoka/dedup.py, data/sources.json. |
| Crawl cadence | Full cycle every ≈ 3.3 h on average (585 cycles between 2026-08-12 12:30 UTC and 2026-11-01 06:49 UTC; min 0.64 h, max 13.4 h; no gap > 24 h for any source). ≈ 7 observations per listing-day. | sync_log (83 531 rows). |
| Model APIs | None required. The paper uses no LLM/external model API; no credential verification needed. | — |
2. Schema (tables kept in the research snapshot)
| Table | Rows | Role / key fields |
|---|---|---|
vehicles |
273 265 | One row per listing uid = source:external_id. Fields: make, model, trim, year, price, price_label, mileage_km, transmission, fuel, drivetrain, body_type, colours, engine, doors, seats, vin, stock_number, dealer_name, city, region, lat/lng, description, features (JSON), details (JSON), images (JSON), carfax_url, content_hash, first_seen, last_seen, updated_at, miss_count, active, kind (auto/moto/scooter), dup_of, dup_sources. |
price_log |
428 484 | (uid, ts, price) — one row at first observation and one row per price change (not per observation). price = NULL = price withdrawn from display. |
sync_log |
83 531 | One row per (source, cycle): found, added, updated, removed, ok, message, stats JSON (null-price rate, null-km rate, missed, alert). |
dealers |
138 | Geocoded registry of direct dealer sources (address, phone, website, city, lat/lng). |
recalls |
109 186 | Transport Canada recalls keyed by make/model/year (public data). |
vin_decode |
4 655 | Decoded VIN attributes (partial coverage). |
detail_cache |
77 039 | Opaque per-connector payload cache; not used. |
users |
— | Dropped from the research snapshot (personal accounts). |
Timestamps are Unix epoch seconds (UTC). first_seen = first observation; last_seen = last observation; for inactive rows updated_at = time the listing was declared inactive (two consecutive misses after last_seen).
3. Temporal coverage and panel structure
| Quantity | Value |
|---|---|
| Observation window | 2026-08-12 12:32 UTC → 2026-11-01 06:49 UTC (81 days, 585 full crawl cycles) |
| Listings ever observed | 273 265 (auto 271 992; moto 1 218; scooter 55) |
| Active at snapshot | 62 821 (auto 62 052) ; inactive (exited) 210 444 |
| New listings per month (first_seen) | Aug 99 859 (incl. 46 k stock on day 1, left-censored) ; Sep 85 452 ; Oct 87 355 |
| Price observations | 428 484 rows for 270 909 uids; 61 962 uids with ≥ 2 price points, 26 576 with ≥ 3 |
| Monthly price-log rows | Aug 124 379 ; Sep 147 476 ; Oct 155 766 |
| Listing duration (inactive, all sources) | p10 0.74 d, p25 0.93 d, p50 2.6 d, p75 13.9 d, p90 35.1 d — the low quantiles are driven by sampled portals (see §5) |
A longitudinal listing panel therefore exists, but it is short (11.5 weeks) and starts with a left-censored stock. Listing-level dynamics (H2, H5) are feasible; market-level time series (H6) have at most 81 daily / 11 weekly points.
4. Field coverage (null rates, all 273 265 listings)
| Field | Null/empty | Field | Null/empty |
|---|---|---|---|
| price | 1.7 % | make / model | 0.0 % / 0.0 % |
| mileage_km | 0.4 % | year | 0.1 % |
| trim | 5.6 % | VIN | 33.3 % (AutoTrader 100 %, Kijiji 79 %, Otogo 18 %, direct dealers ≈ 0 %) |
| body_type | 56.3 % | fuel | 35.1 % |
| transmission | 32.2 % | drivetrain | 37.7 % |
| region | 9.4 % | city | 1.7 % |
| lat/lng | 4.8 % | dealer_name | 0.0 % |
| stock_number | 57.8 % | exterior colour / engine | 56 % / 57 % |
Price integrity: 2 164 listings at price ≤ 1 $ (placeholders), 29 between 1 and 500 $, 1 632 between 500 and 2 000 $, 506 above 250 000 $, 5 above 1 M$ (max 26.5 M$ — data error). price_label empty for 6 739. Mileage ≤ 10 km: 1 588 (new/demo or placeholder); > 500 000 km: 48. Year < 1990: 131; year = 2027: 784 (new-model-year stock). Hedonic-eligible core (auto, canonical, 2005 ≤ year ≤ 2026, 1 000 ≤ km ≤ 400 000, 2 000 ≤ price ≤ 250 000, make & model present): 242 508 of 259 133 canonical auto listings.
5. Source-level quality (decisive for the dynamic analyses)
Measured from sync_log and vehicles; "reactivation share" = (sum of removed events − inactive uids) / removed events, i.e. the share of exit events that were later reversed (flicker).
| Source (type) | Listings | Mean found / cycle | Exits with duration < 1 d | Reactivation share | Verdict |
|---|---|---|---|---|---|
| otogo (portal, full inventory, VIN ≈ 82 %, exact GPS, 538 dealers) | 79 045 | 24 719 | 30 % | 0.44 | Keep for levels and dynamics with flicker-robust exit rule; uid stable per dealer listing (only 1.7 % of VINs have > 1 otogo uid, mostly genuine dealer-to-dealer transfers) |
| autousagee (AMVOQ portal, full inventory, 391 dealers) | 28 858 | 11 973 | 5 % | 0.04 | Keep |
| direct dealer websites (136 sources, 50 223 listings, 17 801 active) | 50 223 | — | 4.8 % | mostly ≤ 0.1 | Core dynamic sample (exceptions below) |
| hgregoire, automobileendirect, megacentre, clubautozone (banners) | 11 038 | — | low | 0.10–0.21 | Keep |
| autotrader (portal, sliding window capped at 2 000 of > 4 000) | 65 526 | 1 853 | 11 % (mean duration 2.2 d) | 0.12 | Exclude from dynamics (sampled, no VIN); eligible for cross-sectional hedonics only with a flag |
| cargurus (portal, capped at 500) | 37 501 | 507 | 78 % | 0.17 | Exclude from dynamics; cross-section only |
| kijiji (private sellers, no VIN 79 %, trim missing 55 %) | 11 874 | 3 041 | 15 % | 0.04 | Separate "private-seller" segment; excluded from dealer analyses |
| leprixdugros (banner) | 6 124 | 1 019 | 11 % | 0.94 | Connector flicker: exclude from exit/markdown hazards |
| albioccasion (rate-limited 429) | 2 057 | 853 | — | 0.52 | Exclude from dynamics |
| baiecomeaugm, septilesgm, stjeromeautodepot, excelmoto (404 / connector failures 214–271 cycles) | < 500 each | — | — | 0.85 / 0.39 / — | Exclude |
| classeauto:-98 | 1 uid | — | — | — | Identity collision (36 194 price rows, 48 distinct prices): exclude uid |
Other anomalies: 3 451 uids show A→B→A price oscillation (e.g. otogo:1694844 alternating 37 840 / 40 794 $ twice a day for weeks — two feed variants of the same listing); 11 479 of 121 239 price changes occur < 24 h after the previous one. A "sustained price path" rule is therefore required before any markdown analysis (see research/analysis_plan.md).
Among non-portal sources, price changes are 100 532 cuts vs 11 447 rises (mean cut 3.4 %, mean rise 15.0 % — rises are dominated by placeholder/oscillation artefacts).
6. Geographic and dealer coverage
Active canonical listings by administrative region (2026-11-01): Montérégie 8 744, Capitale-Nationale 5 442, Laurentides 4 999, Montréal 4 511, Chaudière-Appalaches 3 605, Laval 3 036, Lanaudière 2 965, Mauricie 2 060, Estrie 1 965, Centre-du-Québec 1 652, Bas-Saint-Laurent 1 504, Outaouais 1 342, Abitibi-Témiscamingue 1 265, Saguenay–Lac-Saint-Jean 1 027, Côte-Nord 799, Gaspésie–Îles-de-la-Madeleine 291; missing region 4 754 (to be recovered from lat/lng). 138 geocoded direct dealers; otogo adds 538 dealer names with exact GPS; 16 308 direct-dealer VINs are also observed on otogo (cross-source consistency checks for exits).
Representativeness: the direct-dealer registry over-weights Capitale-Nationale, Bas-Saint-Laurent, Chaudière-Appalaches and Côte-Nord relative to population (registry built by the operator, not sampled). External benchmarks: see reports/external_benchmarks_inventory.md.
7. Feasibility matrix (H1–H6)
| Hypothesis | Feasible? | Sample | Limits |
|---|---|---|---|
| H1 nonlinear depreciation (hedonic asking prices) | Yes | 242 k canonical listings; first-observed price per listing (or listing-month panel) | Asking prices, not transactions; composition; dealer effects collinear with region for single-site dealers |
| H2 markdown hazard | Yes | Dynamic sample (direct dealers + otogo + autousagee + banners), listing-day risk set, flicker-robust price paths | Left-censoring of the day-1 stock (46 k); 81-day window → right-censoring heavy for long spells |
| H3 competitor response | Association only | Local competitor sets (same make/model/year ± 1, radius 25/50 km) from GPS | No exogenous variation; reflection problem; common shocks → report as conditional association with event-study diagnostics |
| H4 regional segmentation | Yes (cross-section + within-spec dispersion) | Hedonic residuals by region with model-trim-year cohorts | Dealer FE absorb region for single-location dealers → use dealer-group/chain FE variants |
| H5 listing exit & relative price | Yes, exit ≠ sale | Same as H2; exit = final absence ≥ 7 d | No transaction data; validation limited to cross-source consistency (direct site vs otogo) and relisting detection |
| H6 AMLI | Construction + internal validation only | Daily/weekly market-level panel, 81 days | No out-of-sample forecasting test of external economic variables is possible with ≤ 3 monthly points; stated as a documented limitation |
8. Recommended extraction method (adopted)
- Immutable raw snapshot:
sqlite3 .backupon M4M64b → dropusers→VACUUM→ SHA-256 → zstd → scp → verify (done 2026-11-01;data/raw/, read-only, never committed). src/ucml/ingest: read-only DuckDB/Polars extraction to Parquet indata/interim/(vehicles, price_log, sync_log, dealers), with a manifest (row counts, min/max timestamps, SHA-256 of the source).- All downstream transformations versioned in
src/ucml/*with tests; exclusion log indata/processed/exclusions.csv.
9. Data governance
- Public vs restricted fields. Make/model/trim/year/price/mileage/attributes/region/city/dealer name are public asking-price information published by dealers. VIN, stock number, listing URL, images, free-text descriptions and exact coordinates are treated as restricted: kept only in
data/rawanddata/interim(not committed, not released), used solely for linkage (VIN de-duplication, relisting detection, dealer geography). Released/processed tables carry anonymised dealer IDs (d####) and listing IDs (l#######), region/city and 1 km-rounded coordinates. - Personal information. The
userstable was dropped before the data left the production node. Kijiji private-seller listings may contain phone numbers in free text: descriptions are never exported fromdata/interim; the private-seller segment is analysed only through structured fields. - Retention. Raw snapshot retained for the life of the paper on the author's encrypted laptop and the production node; processed Parquet retained with the repository; nothing is pushed to any third-party service.
- Attribution. Data source cited as "Auto-Ka (www.auto-ka.com), operator's database snapshot of 2026-11-01"; dealer sites are the ultimate sources (listed in
data/sources.json, mirrored indata/processed/sources_registry.csv). - Permitted use. Owner-authorised academic research use by the platform operator; no redistribution of listing-level raw data.
- Access control. Snapshot mode 0444, directory excluded by
.gitignore.