# Vehicle Knowledge Database

SQLite one-stop index for vehicle records stored under `knowledge/personal-history/vehicle-*`.

## Files

- `vehicles.sqlite` — searchable vehicle/service database.

Source images/PDFs stay in the per-vehicle source folders; the database stores text, line items, observations, follow-ups, and source paths.

## Main tables

- `vehicles` — one row per vehicle.
- `service_records` — one row per service/invoice.
- `service_line_items` — invoice/service parts, labour, consumables and fluids.
- `service_work_notes` — work performed, observations and follow-up items.
- `inspection_values` — measurable details like brake %, battery CCA, coolant %, tyre pressures.
- `source_documents` — paths to source photos/PDFs.

## Helpful views

- `vehicle_service_summary`
- `open_followups`

## Useful queries

```sql
-- All vehicles
SELECT display_name, registration, vin, colour, build_date
FROM vehicles
ORDER BY display_name;

-- Service history summary
SELECT *
FROM vehicle_service_summary
ORDER BY service_date DESC;

-- Open follow-ups / things to fix
SELECT display_name, registration, service_date, note
FROM open_followups
ORDER BY service_date DESC, display_name;

-- All costs by vehicle
SELECT v.display_name, v.registration, ROUND(SUM(sr.total), 2) AS total_spend
FROM vehicles v
JOIN service_records sr ON sr.vehicle_id = v.id
GROUP BY v.id
ORDER BY total_spend DESC;

-- Search line items
SELECT v.display_name, v.registration, sr.service_date, li.item_code, li.description, li.quantity, li.total
FROM service_line_items li
JOIN service_records sr ON sr.id = li.service_record_id
JOIN vehicles v ON v.id = sr.vehicle_id
WHERE li.description LIKE '%oil%'
ORDER BY sr.service_date DESC;

-- Brake/battery/tyre inspection values
SELECT v.display_name, v.registration, sr.service_date, iv.system, iv.metric, iv.value, iv.notes
FROM inspection_values iv
JOIN service_records sr ON sr.id = iv.service_record_id
JOIN vehicles v ON v.id = sr.vehicle_id
ORDER BY sr.service_date DESC, iv.system, iv.metric;
```

## Import convention

When adding future vehicle documents:

1. Keep original images/PDFs under the relevant per-vehicle `_source-documents/` folder.
2. Write/update the Markdown service note.
3. Import/update `vehicles.sqlite` with vehicle, service, line-item, follow-up and inspection rows.
4. Do not delete source files; they remain the source of truth.