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

-- 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:

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