Fleet management database design: schema, SQL and ER diagram

A sound fleet management database design is built around one table and one rule. vehicles holds every unit, and nothing about a vehicle's current state is stored on its row. Who is driving it comes from vehicle_assignments, a history of periods. Its mileage comes from odometer_readings, an append-only log that fuel, inspections and work orders all point at. Whether it may leave the yard comes from open defects, open work_orders and expired documents. Status, current driver, odometer and next service due are all views. Get that right and the rest of the schema (fuel transactions, maintenance schedules, inspections) falls into place around it.

The tables at a glance

Two small lookup tables, checklist_items (what an inspection checks, and how serious a failure is) and required_document_types (which documents a vehicle or driver must hold), complete the schema.

Download the fleet management sample database

Download the PostgreSQL schema and sample data (.sql) · Download the ER diagram (.svg)

Run the SQL once in an empty practice database with PostgreSQL 16 or later:

psql -v ON_ERROR_STOP=1 -d your_practice_database -f fleet-example.sql

It creates a separate fleet_example schema with all fourteen tables, views and triggers, and seeds a three-vehicle fleet dated relative to the day you run it. It then deliberately breaks six rules (a second driver on an assigned van, a backwards odometer, an edit to the mileage log, dispatching a van with a failed tire check, certifying a repair before its work order closes, and assigning a driver with an expired licence) and prints rejected as expected for each. The final query is a fleet board: V-101 assigned to Dana Okafor at 43,410 miles with an oil service due soon, V-102 assigned to Sam Reyes with one minor defect open, and V-103 out of service with lapsed insurance and an overdue annual inspection. Running it again stops at the existing schema.

The file is the core custody and readiness model, not a whole product. Before production, add authentication, per-organisation separation, role-scoped reads (a driver should see their own vehicle, not the fleet) and an audit trail for every status-changing action. Append-only triggers stop ordinary updates and deletes; database owners and superusers can still bypass them, so your application must not connect as one.

How to design a fleet management system database, step by step

1. Write down the questions the yard asks every morning. Which vehicles are road-ready? Who is driving each one? What is overdue? What did each cost per mile last quarter? Who had the van on the day of the speeding ticket? Every one has a time dimension, so no single editable field can answer it.
2. Split things from events. Things exist: vehicles, people, documents, maintenance rules. Events happen to them: an assignment starts, a reading is taken, fuel is bought, an inspection fails, a repair is completed. Things become master tables. Events become rows that are added, not edited.
3. Make mileage a log. Every distance, due date and cost-per-mile figure depends on the odometer, so the odometer gets its own append-only table, and every event that observes a reading points at a row in it.
4. Derive state; do not store it. Status, current driver, current mileage and next service due are views over the events. A stored status column is a second source of truth that someone will forget to update.
5. Put the safety rules in the database. No overlapping assignments, no backwards odometer, no dispatch with an open safety-critical defect, no "repaired" without a completed work order. If those live only in the application, an import script or an admin screen will get round them.
6. Test with a real sequence. Seed an assignment history, fill-ups, a failed inspection and its repair, then check the board against answers worked out by hand, as the download does.

The vehicle register, and what it must not contain

The vehicle is the centre of the product. In our census of 60 fleet management products, the vehicle and asset register is evidenced in 40, behind only fuel management and reporting at 41 each. A fleet product is, before anything else, a list of vehicles you can open.

create table vehicles (
  id             bigint generated always as identity primary key,
  unit_number    text     not null unique,
  vin            text     not null unique check (vin ~ '^[A-HJ-NPR-Z0-9]{17}$'),
  make           text     not null,
  model          text     not null,
  model_year     smallint not null check (model_year between 1980 and 2100),
  fuel_type      text     not null check (fuel_type in ('gasoline','diesel','electric','hybrid')),
  tank_gallons   numeric(6,2) check (tank_gallons > 0),
  in_service_on  date     not null,
  retired_on     date     check (retired_on >= in_service_on)
);

Three columns are missing on purpose, and they are the three every tutorial schema includes:

• No status. "Available", "assigned" and "out of service" are consequences of other records: an open assignment, an open safety defect, a vehicle in the workshop, a lapsed insurance policy. Store status and you need code in five places to keep it in step, and the day one of them is missed a van with failed brakes reads available.
• No driver_id. A vehicle has a driver during a period. A single column can only say who has it now, and it overwrites who had it last week.
• No odometer or current_mileage. Mileage is observed at moments, by different people, and sometimes wrongly. It belongs in a log.

unit_number is the fleet's own name ("V-101"); vin is the manufacturer's. Keep both unique. The VIN check rejects I, O and Q, which never appear in a 17-character VIN, and catches most typed-in mistakes on import. Retire vehicles with retired_on rather than deleting them: a deleted vehicle orphans years of fuel, repair and assignment history.

Drivers and vehicles: assignment history, not a driver column

Assigning a driver to a vehicle is evidenced in 14 of the 60 products we counted, and it is the table most schemas get wrong, because the obvious design (vehicles.driver_id) answers only the question nobody asks in a dispute. The question that matters is who had this vehicle at 4:12 pm on the 3rd?, and a column that holds today's answer cannot give it.

create table vehicle_assignments (
  id           bigint generated always as identity primary key,
  vehicle_id   bigint      not null references vehicles(id),
  driver_id    bigint      not null references users(id),
  starts_at    timestamptz not null,
  ends_at      timestamptz check (ends_at > starts_at),
  assigned_by  bigint      not null references users(id),
  during       tstzrange generated always as (tstzrange(starts_at, ends_at, '[)')) stored,
  constraint one_driver_per_vehicle_at_a_time
    exclude using gist (vehicle_id with =, during with &&),
  constraint one_vehicle_per_driver_at_a_time
    exclude using gist (driver_id with =, during with &&)
);

An assignment is a period. ends_at is null while it is current, and ending one sets the timestamp; nothing is overwritten. The generated during column turns the pair into a PostgreSQL range, and the two exclusion constraints are the load-bearing part: the database itself refuses two overlapping assignments for the same vehicle, or for the same driver. An application-level "check then insert" fails the first time two dispatchers click at once; an exclusion constraint does not. Mixing = and && in one constraint needs the btree_gist extension, which ships with PostgreSQL (create extension btree_gist;). If your drivers legitimately hold two vehicles at once, drop the second constraint, not the first.

Pool vehicles booked for a day and permanent assignments that last a year are the same table with different period lengths, so "who had it?" is one query whatever kind of assignment it was.

The odometer log that everything derives from

Odometer and engine-hour readings are evidenced in 24 of the 60 products, but they matter more than that suggests, because nearly everything downstream is computed from them: fuel economy, cost per mile, utilisation, and half of every maintenance due date.

create table odometer_readings (
  id           bigint generated always as identity primary key,
  vehicle_id   bigint      not null references vehicles(id),
  miles        integer     not null check (miles >= 0),
  read_at      timestamptz not null,
  source       text        not null
               check (source in ('manual','fuel','inspection','work_order','telematics')),
  recorded_by  bigint      references users(id),
  recorded_at  timestamptz not null default now(),
  check (source = 'telematics' or recorded_by is not null),
  unique (id, vehicle_id)
);
create index odometer_vehicle_time on odometer_readings (vehicle_id, read_at);

create table odometer_voids (
  reading_id  bigint primary key references odometer_readings(id),
  reason      text        not null,
  voided_by   bigint      not null references users(id),
  voided_at   timestamptz not null default now()
);

create view valid_odometer_readings as
select r.* from odometer_readings r
where not exists (select 1 from odometer_voids v where v.reading_id = r.id);

create view vehicle_odometer as
select distinct on (vehicle_id) vehicle_id, miles, read_at
from valid_odometer_readings
order by vehicle_id, read_at desc, id desc;

Three decisions are worth defending.

Fuel, inspections and work orders point at a reading; they do not carry their own copy. A fuel row with an odometer integer column is a second, unvalidated mileage log. When each of them references odometer_readings instead, there is exactly one place mileage can be wrong, and one place to fix it. The odd-looking unique (id, vehicle_id) exists so other tables can reference (odometer_reading_id, vehicle_id) as a composite foreign key, which makes it impossible for a fuel transaction on V-101 to cite a reading taken on V-102.

Readings never run backwards. A trigger rejects any reading that does not fit between its neighbours in time, and it checks against neighbours rather than "the latest", because readings arrive late: Friday's pump receipt is often entered on Monday.

create function check_odometer_monotonic() returns trigger language plpgsql as $$
declare prev_miles integer; next_miles integer;
begin
  perform 1 from vehicles where id = new.vehicle_id for update;  -- serialize per vehicle
  select max(miles) into prev_miles from valid_odometer_readings
   where vehicle_id = new.vehicle_id and read_at <= new.read_at;
  select min(miles) into next_miles from valid_odometer_readings
   where vehicle_id = new.vehicle_id and read_at > new.read_at;
  if new.miles < prev_miles or new.miles > next_miles then
    raise exception 'odometer % mi does not fit between neighbouring readings (% .. %)',
      new.miles, coalesce(prev_miles::text, 'none'), coalesce(next_miles::text, 'none');
  end if;
  return new;
end $$;

create trigger odometer_monotonic before insert on odometer_readings
  for each row execute function check_odometer_monotonic();

A wrong reading is voided, not edited. Somebody will type 101,500 for 91,950. Editing it would erase the evidence of the mistake and of every figure it distorted. Instead a row in odometer_voids cancels it with a reason and a name, and every view reads valid_odometer_readings. An append-only trigger (raise exception on update or delete) protects both tables. Not covered here: a replaced or rolled-over odometer needs a recorded offset event, and engine-hour equipment needs a meter_type column with the same rules per meter.

Fuel transactions

Fuel management is the most evidenced capability in the census, at 41 of 60 products, and it is the one generic builds fake hardest: a fuel entry with no gallons and no odometer reading cannot produce consumption at all.

create table fuel_transactions (
  id                    bigint generated always as identity primary key,
  vehicle_id            bigint       not null references vehicles(id),
  driver_id             bigint       references users(id),
  fueled_at             timestamptz  not null,
  gallons               numeric(7,3) not null check (gallons > 0),
  total_cents           integer      not null check (total_cents >= 0),
  is_full_fill          boolean      not null default true,
  odometer_reading_id   bigint       not null,
  vendor                text,
  card_transaction_ref  text         unique,
  foreign key (odometer_reading_id, vehicle_id)
    references odometer_readings (id, vehicle_id)
);

Gallons are numeric, not float, because volumes are fractional and floating-point rounding accumulates in exactly the totals a fuel-tax return or a card reconciliation adds up. Money is integer cents, as everywhere. card_transaction_ref is unique so that importing the same fuel-card statement twice inserts nothing the second time. is_full_fill matters because fuel economy is only measurable between two full tanks: miles driven since the last full fill, divided by the gallons it took to fill up again.

select v.unit_number, f.fueled_at::date as filled_on, o.miles, f.gallons,
       o.miles - lag(o.miles) over w as miles_driven,
       round((o.miles - lag(o.miles) over w) / f.gallons, 1) as mpg
from fuel_transactions f
join vehicles v on v.id = f.vehicle_id
join valid_odometer_readings o on o.id = f.odometer_reading_id
where f.is_full_fill
window w as (partition by f.vehicle_id order by f.fueled_at)
order by v.unit_number, f.fueled_at;

On the sample data that returns 18.5, 17.5 and 18.4 mpg for V-101, and nothing for the first fill, because one fill-up measures nothing. That empty cell is correct, and the most common bug in fleet software is filling it with something. If partial fills are common in your fleet, sum the gallons of every fill since the previous full one instead of using the last fill alone.

Maintenance schedules versus work orders

Preventive maintenance schedules are evidenced in 40 of 60 products; work orders, the mechanism that actually returns a vehicle to service, in only 18. Most schemas merge the two, and that merge causes the bug almost every maintenance spreadsheet has: a last_done field on the schedule that is only correct if someone remembers to update it.

Split them. The schedule is the rule: oil and filter every 5,000 miles or 180 days. The work order is the event: what was done, on which date, at which reading, for how much. The last time a service was done is not a column anywhere. It is the latest completed work order for that schedule.

create table maintenance_schedules (
  id              bigint generated always as identity primary key,
  vehicle_id      bigint  not null references vehicles(id),
  service_type    text    not null,
  interval_miles  integer check (interval_miles > 0),
  interval_days   integer check (interval_days > 0),
  baseline_miles  integer not null default 0,
  baseline_on     date    not null,
  is_active       boolean not null default true,
  check (interval_miles is not null or interval_days is not null),
  unique (vehicle_id, service_type),
  unique (id, vehicle_id)
);

create table work_orders (
  id                   bigint generated always as identity primary key,
  number               text        not null unique,
  vehicle_id           bigint      not null references vehicles(id),
  schedule_id          bigint,
  status               text        not null default 'open'
                       check (status in ('open','in_progress','completed','cancelled')),
  takes_vehicle_down   boolean     not null default true,
  opened_at            timestamptz not null default now(),
  opened_by            bigint      not null references users(id),
  completed_at         timestamptz,
  completed_by         bigint      references users(id),
  odometer_reading_id  bigint,
  labor_cents          integer     not null default 0 check (labor_cents >= 0),
  parts_cents          integer     not null default 0 check (parts_cents >= 0),
  vendor               text,
  check ((status = 'completed') = (completed_at is not null)),
  check (status <> 'completed' or (completed_by is not null and odometer_reading_id is not null)),
  foreign key (schedule_id, vehicle_id) references maintenance_schedules (id, vehicle_id),
  foreign key (odometer_reading_id, vehicle_id) references odometer_readings (id, vehicle_id),
  unique (id, vehicle_id)
);

baseline_miles and baseline_on say where the clock starts before the first completed job. The check constraints refuse a "completed" work order without a date, a person and an odometer reading, because a service with no mileage cannot advance a mileage-based schedule.

Note what unique (vehicle_id, service_type) is on: the schedule, not the work order. Rules are unique per vehicle; the events that satisfy them repeat forever. This is not a theoretical mistake. One of the four builds in our fleet build-off could record routine maintenance only once: after the first oil change, a second was refused anywhere in the fleet, forever.

"Whichever comes first" is then a view:

create view maintenance_due as
with last_done as (
  select distinct on (w.schedule_id) w.schedule_id, o.miles, w.completed_at::date as done_on
  from work_orders w
  join odometer_readings o on o.id = w.odometer_reading_id
  where w.status = 'completed' and w.schedule_id is not null
  order by w.schedule_id, w.completed_at desc
), due as (
  select s.id as schedule_id, s.vehicle_id, s.service_type,
         coalesce(l.miles, s.baseline_miles) + s.interval_miles as due_miles,
         coalesce(l.done_on, s.baseline_on) + s.interval_days  as due_on
  from maintenance_schedules s
  left join last_done l on l.schedule_id = s.id
  where s.is_active
)
select d.*,
       d.due_miles - o.miles      as miles_left,
       d.due_on - current_date    as days_left,
       case when d.due_miles - o.miles <= 0 or d.due_on - current_date <= 0 then 'overdue'
            when d.due_miles - o.miles <= 500 or d.due_on - current_date <= 14 then 'due_soon'
            else 'ok' end         as state
from due d
left join vehicle_odometer o on o.vehicle_id = d.vehicle_id;

Either limit trips the state; a null interval simply never does. The 500-mile and 14-day warning thresholds are constants here; in a product they belong per schedule or per fleet. Completing a work order is the only action that moves a due date, which is exactly the property the spreadsheet lacked.

Inspections, defects and the dispatch gate

Digital inspections are evidenced in 31 of 60 products. Defect management, the link from a failed inspection to a repair, in only 14. That gap is where the most dangerous fleet software lives: an inspection form that records "tires: fail" and then does nothing, so the next driver takes the van anyway.

In the US, a driver vehicle inspection report (DVIR) has legal weight. Under 49 CFR 396.11, drivers of commercial motor vehicles prepare a written report at the end of each day's work listing any defect found (none is required if no defect is found); before the vehicle is operated again, the carrier must certify on the report that each defect was repaired or that repair is unnecessary; and the reports are kept for three months. Exemptions and rules vary, so check what applies to your operation, but the data shape that regulation describes is a good one for any fleet: a failed item becomes a defect, and the defect stays open until a named person certifies it.

create table inspections (
  id                   bigint generated always as identity primary key,
  vehicle_id           bigint      not null references vehicles(id),
  driver_id            bigint      not null references users(id),
  kind                 text        not null check (kind in ('pre_trip','post_trip')),
  inspected_at         timestamptz not null,
  odometer_reading_id  bigint      not null,
  foreign key (odometer_reading_id, vehicle_id) references odometer_readings (id, vehicle_id)
);

create table inspection_items (
  id             bigint generated always as identity primary key,
  inspection_id  bigint not null references inspections(id),
  item_code      text   not null references checklist_items(code),
  result         text   not null check (result in ('pass','fail','na')),
  note           text,
  check (result <> 'fail' or note is not null),
  unique (inspection_id, item_code)
);

create table defects (
  id                  bigint generated always as identity primary key,
  vehicle_id          bigint      not null references vehicles(id),
  inspection_item_id  bigint      unique references inspection_items(id),
  reported_by         bigint      references users(id),
  reported_at         timestamptz not null default now(),
  severity            text        not null check (severity in ('safety_critical','minor')),
  description         text        not null,
  work_order_id       bigint,
  resolution          text        check (resolution in ('repaired','no_repair_needed')),
  resolved_at         timestamptz,
  resolved_by         bigint      references users(id),
  check (inspection_item_id is not null or reported_by is not null),
  check (num_nulls(resolution, resolved_at, resolved_by) in (0, 3)),
  check (resolution is distinct from 'repaired' or work_order_id is not null),
  foreign key (work_order_id, vehicle_id) references work_orders (id, vehicle_id)
);

One row per checklist item, not one wide row with a column per item, so the checklist can change without a migration and every failure is individually addressable. checklist_items holds each item's code, label and fail_severity, so "brakes" failing is safety-critical and "wipers" failing is minor by policy, not by the mood of the driver filling in the form.

A failed item raises its defect in the same transaction, so there is no window in which the failure exists and the defect does not:

create function raise_defect_from_failed_item() returns trigger language plpgsql as $$
begin
  insert into defects (vehicle_id, inspection_item_id, reported_at, severity, description)
  select i.vehicle_id, new.id, i.inspected_at, c.fail_severity, c.label || ': ' || new.note
  from inspections i
  join checklist_items c on c.code = new.item_code
  where i.id = new.inspection_id;
  return new;
end $$;

create trigger failed_item_raises_defect after insert on inspection_items
  for each row when (new.result = 'fail')
  execute function raise_defect_from_failed_item();

Defects can also be reported outside an inspection (a driver radios in a warning light), hence reported_by as the alternative to inspection_item_id. Resolution is the certification: the three columns are set together or not at all, "repaired" requires a work order, and a trigger in the download refuses "repaired" until that work order is completed and freezes a defect once certified. Inspections and their items are append-only, because a signed inspection that can be edited afterwards is not evidence.

Documents and compliance with expiry dates

Documents, registration, permits and renewals are evidenced in 12 of 60 products, and driver licence and qualification in only 5, yet both turn into hard requirements the moment you need to prove a vehicle was legal to run or a driver was eligible to drive it.

create table documents (
  id          bigint generated always as identity primary key,
  vehicle_id  bigint references vehicles(id),
  user_id     bigint references users(id),
  doc_type    text   not null check (doc_type in ('registration','insurance','permit',
                                                  'driver_license','medical_card')),
  reference   text   not null,
  issued_on   date   not null,
  expires_on  date   check (expires_on >= issued_on),
  check (num_nonnulls(vehicle_id, user_id) = 1)
);

create table required_document_types (
  subject   text not null check (subject in ('vehicle','driver')),
  doc_type  text not null,
  primary key (subject, doc_type)
);

One table holds vehicle and driver documents, with exactly one subject per row. A renewal is a new row, never an update of expires_on, so you can still show the policy that was in force on the day of an accident. Which documents are mandatory is data in required_document_types, not code, because it differs by jurisdiction and by kind of operation. A licence expiring is then the same kind of event as a failed brake check: it blocks something, and the block is derived.

Vehicle status is a view

With the events in place, status stops being a field anyone types:

create view vehicle_readiness as
select v.id as vehicle_id,
  array_remove(array[
    case when exists (select 1 from defects d
                      where d.vehicle_id = v.id and d.resolved_at is null
                        and d.severity = 'safety_critical')
         then 'open safety-critical defect' end,
    case when exists (select 1 from work_orders w
                      where w.vehicle_id = v.id and w.status in ('open','in_progress')
                        and w.takes_vehicle_down)
         then 'in the workshop' end
  ], null)
  || array(select r.doc_type || ' expired or missing'
           from required_document_types r
           where r.subject = 'vehicle'
             and not exists (select 1 from documents d
                             where d.vehicle_id = v.id and d.doc_type = r.doc_type
                               and (d.expires_on is null or d.expires_on >= current_date))
           order by r.doc_type) as blockers
from vehicles v;

create view vehicle_status as
select v.id as vehicle_id, v.unit_number,
  case when v.retired_on is not null      then 'retired'
       when cardinality(r.blockers) > 0   then 'out_of_service'
       when a.driver_id is not null       then 'assigned'
       else 'available' end as status,
  a.driver_id, r.blockers
from vehicles v
join vehicle_readiness r on r.vehicle_id = v.id
left join vehicle_assignments a on a.vehicle_id = v.id and a.during @> now();

blockers is a list, not a flag, so the board can say why a vehicle is off the road. Certifying a repair returns the van to service without anyone touching a status, and lapsed insurance takes one out of service the morning it lapses, with no nightly job.

The same view gates dispatch. A trigger on vehicle_assignments locks the vehicle row, reads its blockers, and refuses the assignment if there are any; it also refuses a driver who is not an active driver or whose required documents do not cover the start date:

create function check_assignment_readiness() returns trigger language plpgsql as $$
declare b text[];
begin
  perform 1 from vehicles where id = new.vehicle_id for update;
  select blockers into b from vehicle_readiness where vehicle_id = new.vehicle_id;
  if cardinality(b) > 0 then
    raise exception 'vehicle % is not road-ready: %', new.vehicle_id, array_to_string(b, ', ');
  end if;
  if not exists (select 1 from users u
                 where u.id = new.driver_id and u.role = 'driver' and u.is_active) then
    raise exception 'user % is not an active driver', new.driver_id;
  end if;
  if exists (select 1 from required_document_types r
             where r.subject = 'driver'
               and not exists (select 1 from documents d
                               where d.user_id = new.driver_id and d.doc_type = r.doc_type
                                 and (d.expires_on is null or d.expires_on >= new.starts_at::date))) then
    raise exception 'driver % has an expired or missing required document', new.driver_id;
  end if;
  return new;
end $$;

create trigger assignment_readiness before insert on vehicle_assignments
  for each row execute function check_assignment_readiness();

This is the rule that the inspection exists to serve. An open safety-critical defect stops the next assignment, in the database, whichever screen or script tries to create it.

The constraints that are load-bearing

• No table stores what can be derived. Status, current driver, current mileage, last service date and next due date are views. If you enforce one thing at review time, enforce this.
• Assignments cannot overlap, per vehicle and per driver, by exclusion constraint rather than application check.
• The odometer log is append-only and monotonic, and corrections are voids with a reason.
• Composite foreign keys (odometer_reading_id, vehicle_id) stop any event borrowing another vehicle's mileage; the same trick ties work orders and defects to their own vehicle.
• A failed checklist item raises a defect in the same transaction, and a safety-critical defect blocks dispatch until it is certified, with "repaired" requiring a completed work order.
• Every status and type is a check constraint or a lookup table, and money is integer cents, gallons numeric.
• Nothing is deleted. Vehicles retire, assignments end, documents are superseded, readings are voided. The history is the product.

Example queries

Each of these runs unchanged against the downloadable file.

Who was driving a vehicle, and when. The full custody history for one unit:

select v.unit_number, u.full_name as driver,
       a.starts_at::date as from_date, a.ends_at::date as to_date
from vehicle_assignments a
join vehicles v on v.id = a.vehicle_id
join users u on u.id = a.driver_id
where v.unit_number = 'V-101'
order by a.starts_at;

For "who had it at a given moment", replace the filter with a.during @> timestamptz '…'. On the sample data, V-101 thirty days ago was Lee Moreno's, and since ten days ago it is Dana Okafor's. A vehicles.driver_id column would know only about Dana.

What is overdue or due soon:

select v.unit_number, m.service_type, m.due_miles, m.miles_left,
       m.due_on, m.days_left, m.state
from maintenance_due m
join vehicles v on v.id = m.vehicle_id
where m.state <> 'ok'
order by case m.state when 'overdue' then 0 else 1 end, m.days_left;

The sample returns V-103's annual inspection 15 days overdue, V-101's oil service 490 miles away, and V-103's oil service 50 miles away.

Cost per mile, over a period. Spend is fuel plus completed work orders; distance is miles actually driven in the period:

with period as (
  select now() - interval '100 days' as starts, now() as ends
), distance as (
  select r.vehicle_id, max(r.miles) - min(r.miles) as miles
  from valid_odometer_readings r, period p
  where r.read_at >= p.starts and r.read_at < p.ends
  group by r.vehicle_id
), spend as (
  select f.vehicle_id, f.total_cents as cents
  from fuel_transactions f, period p
  where f.fueled_at >= p.starts and f.fueled_at < p.ends
  union all
  select w.vehicle_id, w.labor_cents + w.parts_cents
  from work_orders w, period p
  where w.status = 'completed' and w.completed_at >= p.starts and w.completed_at < p.ends
)
select v.unit_number, d.miles,
       (coalesce(sum(s.cents), 0) / 100.0)::numeric(12,2) as spend,
       round(coalesce(sum(s.cents), 0) / 100.0 / nullif(d.miles, 0), 3) as cost_per_mile
from vehicles v
join distance d on d.vehicle_id = v.id
left join spend s on s.vehicle_id = v.id
group by v.unit_number, d.miles
order by v.unit_number;

The divisor is the difference between readings, never their sum. That sounds too obvious to say until you see it go wrong: one build in our fleet build-off divided spend by the total of every odometer in the fleet, making its cost per mile about a hundred times too small. max − min inside the window slightly undercounts; for exact boundaries, interpolate from the reading just before.

Fuel bought by someone who was not driving the vehicle, or more fuel than the tank holds, the two cheapest fuel-card checks there are:

select v.unit_number, u.full_name as driver, f.fueled_at::date as filled_on,
       f.gallons, v.tank_gallons
from fuel_transactions f
join vehicles v on v.id = f.vehicle_id
left join users u on u.id = f.driver_id
where f.gallons > v.tank_gallons
   or not exists (select 1 from vehicle_assignments a
                  where a.vehicle_id = f.vehicle_id
                    and a.driver_id = f.driver_id
                    and a.during @> f.fueled_at)
order by f.fueled_at;

On the sample it finds one fill: Dana Okafor bought 20 gallons for V-101 more than two weeks before the van was assigned to her. It may be innocent. The point is that only an assignment history can ask the question at all.

A fleet management database in Microsoft Access or Excel

Many people searching for a fleet management database want a Microsoft Access template, and for a small fleet that is a reasonable start. Access is a real relational database with enforced relationships and queries, so the tables above translate into it directly.

Where it breaks is the same place for every desktop file. Access's own published limits are a 2 GB database file and 255 concurrent users, but the practical limit arrives much earlier: drivers at a pump or in a yard need a phone form, not a shared file on an office PC. Access has nothing like PostgreSQL's exclusion constraints, so "one driver per van at a time", "odometer never runs backwards" and "a failed brake check blocks the next assignment" become form or macro logic you write and maintain yourself, and logic that lives in a form is skipped by anything that writes to the table directly. Status tends to end up as a field again, because a field is what a form shows most easily.

If you are not ready for a database at all, fleet management in Excel gives the same design as five spreadsheet tabs (vehicles, fuel, maintenance, service history and inspections) and is honest about the five places it fails. The rule carries over to all three tools: one table or tab per kind of event, and nothing on the vehicle row that can be worked out from them.

Why coding agents get this wrong

Ask a coding agent for a fleet management database and you get the tutorial schema: vehicles with status, driver_id and mileage columns, a maintenance table with a last_service date, and a live map, the most photographed screen in the category. It is a specification failure, not a reasoning failure: nothing in "build a fleet management system" says mileage is a log or that a failed inspection must block dispatch.

We have watched this directly. Four models built the same fleet spec from one byte-identical planner package, and many of the differences were in the schema. One build had thirteen of its sixteen features as the same generic table with different labels: fuel without gallons, costs without an amount, and a fuel reading with gallons and an odometer accepted and then thrown away. The same build refused every routine service after the first. Another wrote a fixed fuel-efficiency figure whenever it could not calculate one and divided cost by the sum of every odometer. And only one of the four wired a failed pre-trip item through to a linked critical defect that took the vehicle out of service and became a work order, which is the chain this whole article is about. All four run as live demos, so you can open the difference rather than take our word for it.

The screens arrive either way. The substrate is where builds differ, and it is decided in the schema before the first line of application code.

---

The inventory database design post applies the same "derive, don't store" rule to stock. If you want the build order rather than the schema, how to build a fleet management system covers it, and features of a fleet management system is the full counted inventory the figures above come from.

And if you are pointing a coding agent at this, the Fleet Management planner is the spec we hand ours: the vehicle as the core object, the readiness gate and the inspection-to-repair chain written so the agent has to build them rather than reach for the tutorial schema.