# 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)

1. Immutable raw snapshot: `sqlite3 .backup` on M4M64b → drop `users` → `VACUUM` → SHA-256 → zstd → scp → verify (done 2026-11-01; `data/raw/`, read-only, **never committed**).
2. `src/ucml/ingest`: read-only DuckDB/Polars extraction to Parquet in `data/interim/` (vehicles, price_log, sync_log, dealers), with a manifest (row counts, min/max timestamps, SHA-256 of the source).
3. All downstream transformations versioned in `src/ucml/*` with tests; exclusion log in `data/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/raw` and `data/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 `users` table was dropped before the data left the production node. Kijiji private-seller listings may contain phone numbers in free text: descriptions are never exported from `data/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 in `data/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`.