# Device Reconciliation — 162 vs 182 (2026-04-27)

Phase 0.2 of the Business Analytics redesign. Resolves the gap between
`20260427_FSG_Vehicles_mitieng.csv` (162 rows) and `tracksolid.devices`
(~182 rows at last check).

## How to populate this report

1. Pull this branch onto the Coolify host (or rebuild containers so the
   ingest container has `audit_device_reconciliation.py`).

2. Run inside the ingest container so it has `DATABASE_URL`:
   ```bash
   ING=$(docker ps --filter name=ingest_movement --format "{{.Names}}" | head -1)
   docker cp 20260427_FSG_Vehicles_mitieng.csv "$ING":/app/
   docker exec "$ING" python audit_device_reconciliation.py \
     --csv 20260427_FSG_Vehicles_mitieng.csv \
     --out /tmp/260427_audit_output.txt
   docker cp "$ING":/tmp/260427_audit_output.txt ./
   ```

3. Paste the audit output into the **Audit output** section below.

4. Mark the chosen disposition for each IMEI in the **Disposition** section.

## Audit output

First run: 2026-04-27 23:35 UTC against `tracksolid_db` on stage. Full output in
`260427_audit_output.txt`. Headline numbers:

| Metric | Value |
|---|---|
| CSV rows | 162 |
| `tracksolid.devices` rows | 172 |
| Delta (DB − CSV) | +10 |
| In CSV but not in DB | 0 |
| In DB but not in CSV | 10 |
| Devices both sides, DB metadata still NULL on ≥1 field | 162 (resolved by importer run) |

After running `import_drivers_csv.py --only-null --apply` (2026-04-27): 154
devices updated, 8 already complete, 0 inserted. Coverage now: `assigned_city`
152/172, `cost_centre` 150/172, `vehicle_brand` 2/172, `fuel_100km` 3/172.
`assigned_route` / `vehicle_category` / `depot_address` remain 0/172 (CSV
provided no values for these — Phase 1 follow-up).

The 10 in-DB-not-in-CSV IMEIs are listed in `260427_audit_output.txt`. They
sit in `(blank)` or `fireside` accounts and surface in Grafana as
`assigned_city = 'unassigned'` thanks to the existing COALESCE in
`07_analytics_views.sql`.

## Disposition

For each IMEI in the "in DB but not in CSV" list, choose one and record why:

| IMEI | Account | Last seen | Disposition | Notes |
|---|---|---|---|---|
|  |  |  |  |  |

**Disposition options:**

- **prune** — Delete from `tracksolid.devices`. Use when the unit is a stale
  test/decommissioned device that should never have synced. Capture the SQL
  before running:
  ```sql
  DELETE FROM tracksolid.devices WHERE imei = '<imei>';
  ```
  *Caveat:* foreign keys from `position_history`, `trips`, `alarms` must be
  considered first — these will block the delete if there's any history.
  Usually safer to leave-as-NULL.

- **leave-as-NULL** — Keep the row; metadata fields stay NULL. The device
  was auto-synced from a Tracksolid account that the CSV doesn't cover
  (likely `Fireside@HQ` rows that were left out of this Mitieng export).
  Grafana views already use `COALESCE(d.assigned_city, d.city, 'unassigned')`
  so these surface as "unassigned" but don't break dashboards.

- **addendum** — Add to a follow-up CSV and re-run the importer with
  `--apply`. Use when the device is legitimate fleet metadata was just
  missing from the export.

## Decision

<!-- Fill in once dispositions are chosen. -->

- Total devices reviewed: ___
- Pruned: ___
- Left-as-NULL: ___
- Added via addendum: ___

After action, re-run `audit_device_reconciliation.py` and confirm the
delta is what you expect.