74 lines
2.4 KiB
Markdown
74 lines
2.4 KiB
Markdown
# 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.
|