Building a Cost-Per-Kilometre Tire Tracking System for a Small Fleet

Most small fleets in Calgary track tire spend the same way: a folder of invoices, a line in the accounting software labelled "Tires & Repairs," and a general feeling that the plumbing vans cost more than the sales cars. That feeling is usually right and almost never actionable. It cannot tell you whether unit 14 burns through rubber because of its route, its load, its driver, its geometry, or simply because someone put the cheapest available casing on the steer axle two Novembers ago.

The fix is not a bigger software purchase. For a fleet of five to forty vehicles, the fix is a data model. Once you track tires as individually identified assets rather than as an expense category, cost per kilometre falls out of the arithmetic, remaining life becomes a projection instead of a guess, and outlier vehicles announce themselves without anyone squinting at a spreadsheet.

This is a write-up of how I would build that system today, aimed at the developer or operations person who has been handed the problem and a budget of approximately zero. Everything here works in a spreadsheet first and migrates cleanly to Postgres or SQLite when the spreadsheet stops being fun. I will show schema, queries, a worked calculation, and the failure modes that eat these projects.

Why Vehicle-Level Tire Spend Tells You Almost Nothing

Start with the naive model, because almost every fleet already has it. You have vehicles, and you have invoices attached to vehicles. Roll it up by year and you get something like "unit 14: $3,840 in tires." Divide by kilometres driven and you have a number that looks like cost per kilometre.

It is not. It is an accounting artifact with three structural problems.

The first is timing skew. Tires are purchased in lumps and consumed continuously. A vehicle that got four new casings in March and nothing since looks catastrophically expensive in a Q1 report and free in Q3. Unless your reporting window happens to align with the tire's service life, you are measuring purchase timing rather than consumption.

The second is the mixing problem. A single invoice for a truck might contain two steer positions, a repair on a drive position, a valve stem, a disposal levy, and a rotation. Rolled into one number, you lose the ability to say that the steers are the expensive part. On many light commercial units, the front positions wear at nearly double the rate of the rears, and the whole story lives in that split.

The third is the movement problem. Tires move. They get rotated across positions, swapped between vehicles when a unit goes down, pulled and stored over a seasonal change, and remounted the next year on a different truck entirely. Vehicle-level cost assignment silently attributes wear to whichever unit happened to be holding the tire when the invoice was cut.

Every one of these problems disappears when the tire itself becomes the unit of record.

The Core Insight: A Tire Is An Asset, Not A Line Item

Treat each physical tire as a serialized asset with a birth, a service history, an accumulated distance, and a death. That is the whole idea. Everything else in this article is bookkeeping in service of it.

An asset-oriented model gives you three things that expense-oriented models cannot. You get true consumption: a tire that cost $410 and delivered 104,000 kilometres has a defensible cost per kilometre no matter which fiscal quarters that span crossed. You get position-level attribution: because you know where the tire sat and for how long, you can compare wear rates by axle and corner. And you get portability: when a casing moves from unit 9 to unit 22, its history moves with it, and both vehicles get charged only for the distance they actually consumed.

The objection I hear most often is that this is too much work for a fleet of twelve vans. It is less work than it sounds like, because the data entry burden is roughly one short form per service event, and service events are rare. A twelve-van fleet generates maybe eighty tire-touching events a year. That is a manageable log. What is not manageable is reconstructing four years of history from invoices after your insurer or your accountant asks a pointed question.

Entities You Actually Need

Six entities carry the entire model. Resist the urge to add more before you have a year of clean data.

Vehicles

A vehicle is the platform, not the tire owner. You need an internal unit number, a VIN, the year/make/model, the axle configuration, the OE tire size, and — importantly — a route class. Route class is the field that most fleets skip and later wish they had: something like city_stop_start, mixed, highway_long, or gravel_access. It costs nothing to record and it is the single most useful explanatory variable when a vehicle looks like an outlier.

Include a in_service_date and a nullable out_of_service_date. Fleets churn vehicles, and a null-terminated interval saves you from deleting rows that historical costs depend on.

Wheel Positions

Positions are a small controlled vocabulary, not free text. Free text positions will destroy your reporting within six weeks; someone will type "LF," someone else "FL," someone else "front driver." Pick a convention and enforce it in a dropdown or a foreign key.

I use axle-ordinal notation: A1L, A1R, A2LO, A2LI, A2RI, A2RO, plus SPARE. Axle one is the front. O and I denote outer and inner on duals. This scales from a Corolla to a tandem-axle unit without changing the scheme, and it sorts sensibly.

Tire Instances And The DOT Code Problem

This is the important table and the one that generates arguments. A tire instance is one physical casing. It needs a stable internal identifier that you assign — a sequential fleet tire number is perfect, T-0417 style — because you cannot rely on the manufacturer's markings alone to be unique.

The DOT code stamped on the sidewall encodes the plant, the size code, an optional manufacturer code, and a four-digit week/year date code. Two tires bought on the same day from the same batch will frequently share an identical full DOT string. It is a batch identifier, not a serial number. Record it anyway — the date code tells you the casing's actual age, which matters for storage decisions and for spotting old stock — but do not make it your primary key. If you want a refresher on what those sidewall markings actually encode, the breakdown of what the markings on a tire sidewall mean is a reasonable place to send a new technician.

Fields worth carrying on the instance: internal tire id, brand, model line, size, load index and speed rating, season type (summer, winter, all_season, all_weather), DOT date code, new tread depth in 32nds, acquisition date, acquisition cost, current status, and a nullable retirement date and reason.

The load index deserves a column of its own rather than being buried in a size string, because it is the field that gets violated during a hurried replacement. If a van is spec'd for a 109-load-index tire and someone fits a 102, you want a query that finds that, not a memory that fails. The reference on how load index and speed rating work together is worth linking in your internal documentation for whoever is approving purchases.

Service Events

A service event is anything that touches rubber: mount, dismount, rotate, repair, inspect, pressure adjust, seasonal changeover, or scrap. One event per visit, with child rows for each tire affected.

Header fields: event id, date, vehicle id, vehicle odometer at event, vendor, invoice number, event type, labour cost, other cost, and free-text notes. Line fields: event id, tire id, position, action, tread depth measured, pressure found, pressure set, and per-tire cost.

The header/line split matters. A single visit that replaces two tires and rotates the other two is one event with four lines, and the labour on the header can be allocated across the lines however you choose.

Odometer Readings

You need a separate odometer table, not just the readings captured at service events. Service events are too sparse to interpolate accurately, and if your telematics or fuel card system already emits odometer values weekly, ingest them. Three columns will do: vehicle id, reading date, odometer.

If you have no telematics, capture the odometer at every fuel fill or every oil change. Even monthly readings are enough to build a usable distance curve.

Tread Measurements

Tread depth belongs in its own table rather than only on service lines, because the highest-value measurements are the ones taken during routine checks when nothing else happened. Columns: measurement date, tire id, position at measurement, and depth. If you measure at three points across the face — outer shoulder, centre, inner shoulder — store three rows or three columns. The spread between them is your irregular-wear detector and it is worth the extra keystrokes.

A Spreadsheet Schema That Will Carry You To About Fifteen Vehicles

I am not going to pretend a small operation should stand up Postgres on day one. A shared workbook with six tabs and strict column discipline is genuinely sufficient for a long time, and it has the enormous advantage that the person doing data entry already knows how to use it.

Here is the tab layout I would hand someone:

TAB: vehicles
  unit_no | vin | year | make | model | axle_config | oe_size | route_class | in_service | out_of_service

TAB: tires
  tire_id | brand | model_line | size | load_index | speed_rating | season
          | dot_code | new_depth_32 | acquired_on | acquired_cost | status | retired_on | retire_reason

TAB: mounts
  mount_id | tire_id | unit_no | position | mounted_on | mounted_odo
           | removed_on | removed_odo | removal_reason

TAB: events
  event_id | event_date | unit_no | odo | vendor | invoice_no | event_type | labour_cost | other_cost | notes

TAB: event_lines
  event_id | tire_id | position | action | depth_32 | psi_found | psi_set | line_cost

TAB: odometer
  unit_no | read_date | odo | source

Three rules make this workbook survive contact with reality. Use data validation dropdowns for position, event_type, action, and season — every free-text vocabulary field will drift otherwise. Never delete a row; set a status instead. And never reuse a tire_id, even after the tire is scrapped, because your historical cost queries will silently double-count.

The mounts tab is the one people leave out, and it is the one that makes everything else work. I will come back to it.

Moving To SQL Without Rewriting Your Brain

When the workbook gets slow or two people start editing simultaneously, the migration is mechanical: each tab becomes a table. SQLite is more than adequate for this volume — a forty-vehicle fleet running ten years generates perhaps forty thousand rows total.

CREATE TABLE vehicle (
  unit_no        TEXT PRIMARY KEY,
  vin            TEXT UNIQUE,
  model_year     INTEGER,
  make           TEXT,
  model          TEXT,
  axle_config    TEXT,          -- '4x2', '4x4-dually', etc.
  oe_size        TEXT,
  route_class    TEXT NOT NULL, -- city_stop_start | mixed | highway_long | gravel_access
  in_service     DATE NOT NULL,
  out_of_service DATE
);

CREATE TABLE tire (
  tire_id      TEXT PRIMARY KEY,      -- 'T-0417'
  brand        TEXT NOT NULL,
  model_line   TEXT,
  size_code    TEXT NOT NULL,
  load_index   INTEGER,
  speed_rating TEXT,
  season       TEXT NOT NULL,
  dot_code     TEXT,
  dot_week     INTEGER,
  dot_year     INTEGER,
  new_depth_32 REAL NOT NULL,
  acquired_on  DATE NOT NULL,
  acquired_cost NUMERIC(10,2) NOT NULL,
  status       TEXT NOT NULL DEFAULT 'in_stock',
  retired_on   DATE,
  retire_reason TEXT
);

Splitting dot_week and dot_year out of the raw dot_code string is a small kindness to your future self. Casing age drives storage and retirement policy, and parsing a four-character substring in every query gets old.

Modelling Mounts: The Table That Makes Rotations Survivable

Here is the crux. A tire's relationship to a vehicle and a position is not a property of the tire; it is an interval. The moment you model it as an interval, rotations, swaps, and seasonal storage stop being special cases.

CREATE TABLE mount (
  mount_id       INTEGER PRIMARY KEY,
  tire_id        TEXT NOT NULL REFERENCES tire(tire_id),
  unit_no        TEXT NOT NULL REFERENCES vehicle(unit_no),
  position       TEXT NOT NULL,
  mounted_on     DATE NOT NULL,
  mounted_odo    INTEGER NOT NULL,
  removed_on     DATE,
  removed_odo    INTEGER,
  removal_reason TEXT,
  CHECK (removed_odo IS NULL OR removed_odo >= mounted_odo)
);

CREATE INDEX idx_mount_open ON mount(tire_id) WHERE removed_on IS NULL;

An open mount row — removed_on IS NULL — means the tire is currently on a vehicle. A tire with no open mount row is either in storage, in stock, or scrapped, and its status column says which.

A rotation is now trivially expressed: close every affected mount row at the current odometer, then open a new row for each tire at its new position with the same odometer and date. No history is lost. The tire accumulated 41,200 kilometres at A1L and then began accumulating at A2R, and both facts survive.

A seasonal changeover is the same operation with an intermediate state. Close the mount rows in October when the winters go on, set the summer tires' status to in_storage, and open fresh mount rows in April when they come back. The kilometres the summers did not travel over winter simply are not recorded, because the tire was not mounted. That is correct behaviour, and it is the reason a naive "kilometres since purchase" calculation is so wrong in a climate with two tire sets per vehicle.

Anyone running a fleet through an Alberta winter is effectively managing double the casing inventory, and the handling logistics around a twice-yearly seasonal tire changeover are a real operational cost that belongs in the model as labour on the event header.

Computing Distance Travelled By A Tire, Not By A Truck

With mount intervals in place, a tire's lifetime distance is the sum of the odometer deltas over its mount intervals. For closed intervals that is arithmetic. For the open interval you need the vehicle's current odometer.

WITH latest_odo AS (
  SELECT unit_no, MAX(odo) AS current_odo
  FROM odometer
  GROUP BY unit_no
),
spans AS (
  SELECT m.tire_id,
         m.unit_no,
         m.position,
         COALESCE(m.removed_odo, lo.current_odo) - m.mounted_odo AS span_km
  FROM mount m
  JOIN latest_odo lo ON lo.unit_no = m.unit_no
)
SELECT tire_id, SUM(span_km) AS lifetime_km
FROM spans
GROUP BY tire_id
ORDER BY lifetime_km DESC;

Two subtleties bite people here.

Odometer units must be normalized at ingest. If half your units report miles from an OBD dongle and half report kilometres from the dash, you will produce a beautiful report that is off by a factor of 1.609 for a third of the fleet. Store a canonical unit in the odometer table and convert on the way in, never on the way out.

Odometer rollovers and replacements happen. A cluster replacement resets the displayed value, and your delta goes negative. The CHECK constraint above catches the mount rows; for the odometer table, add a nightly validation that flags any reading lower than the previous one for the same unit, and keep an odo_offset column on the vehicle so you can correct forward without rewriting history.

The Cost Per Kilometre Calculation, Worked End To End

Let us do one by hand, because the arithmetic is where the design decisions become visible. Every dollar figure below is an illustrative placeholder chosen to make the math legible — not a quote, not a price list, just numbers for a worked example.

Take tire instance T-0417, a 245/70R17 all-season fitted to a three-quarter-ton service truck on a mixed route.

Costs accumulated over its life:

Component	Amount	Note
Casing purchase	$412.00	capitalised at acquisition
Initial mount and balance	$28.00	allocated from event header
Environmental levy	$6.25	per-tire, at purchase
Rotation labour share	$30.00	two rotations, $60 vehicle labour each, 4 tires
Puncture repair	$38.00	one tread-area repair at 61,000 km
Total capitalised cost	$514.25

Distance:

The tire was mounted new at vehicle odometer 68,240 and pulled at 172,910. It never left the vehicle. Lifetime distance is 172,910 − 68,240 = 104,670 km.

Cost per kilometre:

CPK = 514.25 / 104,670
    = $0.004913 per km
    ≈ 0.49 cents per kilometre

That is the number. Multiply by four for a per-vehicle tire consumption rate of roughly 1.96 cents per kilometre on that axle set, which for a unit running 45,000 km a year works out to about $884 annually in tire consumption — a figure you can put in a budget with a straight face, because it is derived from actual consumption rather than purchase timing.

There is a second metric worth computing alongside it: cost per 32nd of tread consumed. The tire went on at 16/32" and came off at 5/32", so it delivered 11/32" of usable rubber for $514.25, or $46.75 per 32nd. This metric is useful when you are comparing two different tire models across vehicles with different annual distances, because it normalizes away route length and isolates the value of the rubber itself.

Expressed in SQL against the schema above, with per-tire cost aggregated from event lines:

SELECT t.tire_id,
       t.brand,
       t.size_code,
       t.acquired_cost + COALESCE(SUM(el.line_cost), 0) AS total_cost,
       k.lifetime_km,
       ROUND((t.acquired_cost + COALESCE(SUM(el.line_cost), 0)) / NULLIF(k.lifetime_km, 0), 5) AS cpk
FROM tire t
JOIN v_tire_km k USING (tire_id)
LEFT JOIN event_line el ON el.tire_id = t.tire_id
GROUP BY t.tire_id, t.brand, t.size_code, k.lifetime_km
HAVING k.lifetime_km > 10000
ORDER BY cpk DESC
LIMIT 25;

The HAVING filter is not decoration. A tire with 900 kilometres on it will show an absurd cost per kilometre and will sit at the top of every unfiltered report forever, training everyone to ignore the report.

Projecting Remaining Life From Tread Depth

Cost per kilometre is retrospective. The forward-looking number your operations lead actually wants is "when do I need to buy rubber for unit 14?"

The projection is a two-point linear fit and it is accurate enough to plan purchasing. Take the tire's new depth, its most recent measured depth, and the distance it has travelled between those points. That gives a wear rate in kilometres per 32nd. Multiply the remaining usable tread by that rate.

The word "usable" is doing work. Legal minimum is 2/32", but no sane fleet runs to legal minimum, particularly in a city that gets meaningful snow between October and April. Set a fleet pull point — 5/32" or 6/32" for units that run through winter, 4/32" for a summer-only trailer — and project against that, not against the legal floor.

def remaining_km(new_depth_32, current_depth_32, km_since_new, pull_point_32):
    """Linear remaining-life projection. Returns km, or None if not yet measurable."""
    consumed = new_depth_32 - current_depth_32
    if consumed <= 0.5 or km_since_new < 5000:
        return None  # too early to fit a rate
    km_per_32 = km_since_new / consumed
    usable_left = current_depth_32 - pull_point_32
    if usable_left <= 0:
        return 0
    return round(km_per_32 * usable_left)

Applying it to a sibling of our worked example: a tire measuring 8/32" with a pull point of 5/32" and an observed rate of 9,515 km per 32nd has roughly 28,500 kilometres left. If the unit runs 3,800 km a month, that is a purchase you plan for seven months out, not a surprise on a Friday afternoon.

Two honest caveats. Wear is not perfectly linear — the last few 32nds often go faster as the tread blocks lose stiffness and the contact patch changes. And a single measurement pair is noisy, because tread depth gauges vary by hand and by measurement point. Once you have three or more measurements per tire, fit a least-squares slope instead of a two-point rate, and treat the two-point version as a bootstrap.

Wear Rate Is A Better Alarm Than Tread Depth

Absolute tread depth answers "is this tire near the end." Wear rate answers "is something wrong." Those are different questions and the second one saves more money.

A tire wearing at 9,500 km per 32nd is behaving normally on a mixed route. The same casing on the same vehicle suddenly wearing at 4,000 km per 32nd is telling you something changed: pressure has dropped, the load went up, the route changed, or the geometry moved. Rate is a leading indicator; depth is a lagging one.

The most valuable variant is cross-face wear rate. If you are recording outer, centre, and inner depths, compute the spread:

WITH profile AS (
  SELECT tire_id,
         position_at_measure AS pos,
         measured_on,
         depth_outer  AS d_out,
         depth_centre AS d_mid,
         depth_inner  AS d_in,
         ABS(depth_outer - depth_inner)                     AS spread,
         depth_centre - (depth_outer + depth_inner) / 2.0   AS crown_bias
  FROM tread_measurement
  WHERE measured_on >= DATE('now', '-90 days')
)
SELECT tire_id, pos, measured_on, d_out, d_mid, d_in,
       ROUND(spread, 1)     AS shoulder_spread,
       ROUND(crown_bias, 1) AS crown_bias
FROM profile
WHERE spread >= 2.0 OR ABS(crown_bias) >= 1.5
ORDER BY shoulder_spread DESC;

A persistent shoulder spread of 2/32" or more usually means a geometry problem — toe or camber out of specification — or a chronically under-inflated tire. A positive crown bias means the centre is proportionally higher and the shoulders are going away, which points at under-inflation; a negative one, centre wearing fastest, points at over-inflation.

This is the point where I need to be precise about scope, because it is a place where fleets waste money. A tire operation can measure irregular wear, document it, and tell you it is happening. Correcting a geometry fault is mechanical work that belongs with an alignment specialist. The data model's job is to produce the evidence and the timestamp, not to fix the truck. If you want the plain-language version of what wear patterns indicate for the people doing your yard walks, the tire safety fundamentals overview is a decent hand-out.

Vibration complaints are worth logging as a separate event type too, because they are frequently a balance issue rather than a wear issue and the distinction matters for cost attribution — a repeated wheel balancing service on the same position is itself a signal worth querying for.

Finding The Bad Actor: Vehicle, Position, Route, Or Driver

Once you have a wear rate per tire, per position, per vehicle, per route class, you can start ranking. This is where the model pays for itself.

The question is always comparative. Nobody knows what a "good" km-per-32nd figure is in the abstract — it depends on the tire compound, the load, the pavement, and the season. But you know what your own fleet does, and outliers within your own data are meaningful.

WITH rates AS (
  SELECT m.unit_no,
         v.route_class,
         SUBSTR(m.position, 1, 2) AS axle,
         w.km_per_32
  FROM wear_rate w
  JOIN mount m   ON m.mount_id = w.mount_id
  JOIN vehicle v ON v.unit_no  = m.unit_no
  WHERE w.km_per_32 IS NOT NULL
),
class_norm AS (
  SELECT route_class, axle, AVG(km_per_32) AS class_avg
  FROM rates GROUP BY route_class, axle
)
SELECT r.unit_no,
       r.route_class,
       r.axle,
       ROUND(AVG(r.km_per_32))                       AS unit_rate,
       ROUND(c.class_avg)                            AS peer_rate,
       ROUND(100.0 * AVG(r.km_per_32) / c.class_avg) AS pct_of_peer
FROM rates r
JOIN class_norm c ON c.route_class = r.route_class AND c.axle = r.axle
GROUP BY r.unit_no, r.route_class, r.axle, c.class_avg
HAVING pct_of_peer < 75
ORDER BY pct_of_peer;

Normalizing within route class and axle is the trick. Comparing a downtown courier van's front tires to a highway unit's rears produces noise; comparing that van to the other three downtown vans produces a finding.

When a unit shows up at 60% of peer, the diagnostic ladder is short and ordered by cost. Check pressure history first, because it is free and it is the answer more than half the time. Check load — has that unit been carrying something it was not carrying last year? Check route — did dispatch quietly change its territory? Then, and only then, suspect geometry or suspension, which is when you send it to a mechanical provider.

There is a fourth possibility that people avoid discussing: driver behaviour. Aggressive cornering and hard braking show up as shoulder scrub and flat-spotting respectively. If you have telematics with harsh-event scoring, joining that against wear rate by driver assignment is a five-minute query and an uncomfortable meeting. Do it anyway — it is usually a training conversation, not a discipline one.

Seasonal Swaps Double Your Inventory And Your Bookkeeping

In a climate with a real winter, every vehicle in the fleet has two sets of tires and only one of them is on the truck at any moment. That doubles your asset count, adds a storage problem, and creates the single most common data-quality failure I see: tires that go into storage and never come back out of the system.

Model storage explicitly. A tire's status should move in_stock → mounted → in_storage → mounted → scrapped, and every transition should be an event with a date. If you dismount forty winter tires in April and never record where they went, next October you will discover that eleven of them cannot be located and you will buy new ones. That is a five-figure error in a twenty-vehicle fleet.

Add a storage_location field — rack, bay, pallet, or third-party — and a set_id that groups four tires that travel together. Set-level grouping matters because winter sets are usually mounted on dedicated wheels and move as a unit. Trying to track them purely as four independent instances creates more work than it saves at changeover time.

Spring in Calgary brings a second seasonal effect the data will show you if you let it. Once the streets thaw and the accumulated winter gravel is still down, tread and sidewall damage events spike for four to six weeks. If you log repair events with a cause code, the annual pattern is unmistakable, and it is an argument for scheduling changeovers early rather than running summer rubber through the worst of the grit. Puncture volume in that window is high enough that having a plan for tire puncture repair versus replacement written down beforehand keeps the decision consistent instead of ad hoc.

Repairability is a rules question, not a judgement question, and it should live in your documentation: tread-area punctures within the repairable zone and under a diameter limit can be properly patch-plugged from the inside; sidewall and shoulder damage cannot. Encoding that rule means your event log records a defensible decision rather than whoever was at the counter that day.

What To Log At Every Service Event

If you get nothing else from this article, get the event checklist. Data you do not capture at the moment of service is gone forever, and reconstructing it is impossible.

At every event, for every tire touched, record:

• Vehicle odometer. Not approximate. Read it. This is the single most important field and it is the one most often left blank.
• Position before and after. For rotations, both. For mounts, just after. For dismounts, just before.
• Tread depth measured. Three points across the face if you have thirty extra seconds; one centre reading if you do not.
• Pressure found and pressure set. "Found" is diagnostic gold. A tire that is consistently found 12 psi low is a slow leak or a valve issue, and you will never know unless someone writes down the found value before correcting it.
• Action code. From a controlled list: mount_new, remount, rotate, repair, inspect, dismount_store, dismount_scrap, pressure_adjust.
• Cause code on removal. Why did it come off? worn_out, irregular_wear, sidewall_damage, puncture_unrepairable, seasonal, vehicle_sold, warranty. Without this field, your retirement analysis is meaningless.
• Cost, split into casing, labour, and consumables.
• Technician or vendor identifier.

Cold pressure matters and it is worth a sentence of technician training. A tire measured after an hour on the highway in July will read several psi higher than the same tire in a cold bay, and in a Calgary January the swing between a heated bay and an overnight yard is large enough to look like a leak in your data. Record whether the reading was cold, and prefer cold readings for trend analysis.

For fleets that cannot easily bring vehicles in during working hours, the pressure and depth capture can happen wherever the vehicle sleeps — on-site mobile tire service exists precisely because pulling a unit out of rotation for a check costs more in downtime than the check itself.

Handling The Messy Cases

Clean models break on reality. Here are the four cases that will hit you and how I would handle each.

Mid-life transfers between vehicles. A tire comes off unit 9 with 62,000 km on it and goes onto unit 22. The mount interval model handles this natively: close one row, open another. Cost attribution needs a decision, though. My preference is to allocate the tire's total cost across its whole life proportionally by distance, so unit 9 gets charged for the kilometres it consumed and unit 22 for its share. The alternative — charging the acquiring vehicle nothing because the tire was already "paid for" — makes unit 22 look artificially cheap.

Partial-set replacement. Two tires get replaced, two stay. This is normal and the model handles it fine, but your per-vehicle reporting needs to be built on tire instances rather than on "sets," or the mixed ages will confuse every summary you write.

Warranty and pro-rata credits. A tire that fails early and returns a credit should not be recorded as a discount on the replacement. Record the credit against the failed instance as a negative cost line, which makes its lifetime cost lower and its cost per kilometre accurate, and keeps the replacement's cost basis clean.

Casings that leave and come back. Retread or repair-and-return workflows create a gap in the timeline. Keep the same tire_id, add a status of out_for_service, and record the retread as a cost line with its own new tread depth. The instance's cumulative distance keeps climbing, which is exactly what you want for a casing-life analysis.

Data Quality Rules That Save You Later

Enforce these as constraints or as a nightly validation job. Every one of them represents a real error I have seen destroy a dataset.

1. No two open mount rows for the same tire. A tire cannot be on two vehicles.
2. No two open mount rows for the same vehicle and position. A position cannot hold two tires.
3. Removal odometer must be greater than or equal to mount odometer.
4. Measured tread depth must never exceed the tire's new depth. This catches transposed digits and wrong-tire entries.
5. Tread depth for a given tire must be non-increasing over time. Rubber does not grow back; a rising value means a mis-keyed tire id.
6. Every event with event_type = 'rotation' must have an even number of lines and no net change in the set of tires mounted.
7. Odometer readings must be monotonically non-decreasing per vehicle, modulo a recorded cluster-replacement offset.
8. Every mounted tire's load index must be greater than or equal to the vehicle's minimum specification.

Rule 5 in particular catches more errors than any other single check, and it costs six lines of SQL.

Reporting: Four Numbers Worth Putting On A Dashboard

Resist building twenty reports. Four numbers, reviewed monthly, cover the decisions a small fleet actually makes.

Fleet tire cost per kilometre, trailing twelve months. Total tire consumption dollars divided by total fleet distance. This is your headline and its trend matters more than its level.

Cost per kilometre by unit, ranked. The bottom five are your investigation queue. Normalize by route class before you rank or you will chase the wrong vehicles.

Projected replacements in the next ninety days. Driven off the remaining-life projection, this is a purchasing document. It converts tire buying from reactive to scheduled, which is worth real money in a market where sizing availability fluctuates.

Removal reason mix. What fraction of tires are coming off worn out versus coming off damaged versus coming off for irregular wear? A healthy fleet retires most casings for wear. If more than a quarter are coming off for irregular wear, you have a mechanical problem somewhere in the fleet, not a tire problem.

That fourth number is the one that changes behaviour, because it reframes tire spend as a symptom rather than a cost centre.

When A Spreadsheet Is Genuinely Enough

I want to be honest about the threshold, because the reflex to build software is strong and often wrong.

Under about fifteen vehicles with a single person doing data entry, a well-disciplined workbook is not a compromise. It is the correct tool. It is inspectable, it is backed up by whatever cloud drive you already pay for, it needs no deployment, and the person entering data can see their mistakes immediately. The projection math fits comfortably in a formula column.

You should move to a database when one of four things becomes true. Concurrent editing starts causing lost updates. The row count makes lookups slow enough to be annoying — typically past ten thousand event lines. You need to join against telematics or fuel data that already lives in a database. Or you need programmatic validation, because the human validation stopped happening.

There is a middle path worth mentioning: keep the spreadsheet as the entry surface and sync it nightly into SQLite for querying and reporting. A forty-line Python script using openpyxl and sqlite3 gets you there, and it preserves the entry ergonomics people actually like while giving you real query power. I have shipped that arrangement more than once and never regretted it.

Instrumenting Without Annoying Your Technicians

The system dies at the data-entry layer. Every fleet tracking project I have watched fail, failed because the person holding the tread gauge decided it was not worth the trouble.

Design the capture around a two-minute form, not a database screen. Vehicle number, odometer, four positions, four depths, four pressures, and a notes field. Pre-fill everything you can from last time. Let them photograph the invoice rather than transcribing it, and do the transcription centrally.

Give something back, too. If the yard lead can see a list of which units need attention next month, generated from their own measurements, they will keep measuring. If the data disappears into a spreadsheet nobody reads, they will stop within a quarter. That is not a technology problem and no amount of schema design fixes it.

One practical note: standardize the gauge. Digital depth gauges are inexpensive and they remove the inter-reader variance that makes wear-rate fits noisy. Buy four, mark them, and keep one in each service vehicle.

Fleets that outsource the measurement side to their tire provider can get this for free as part of routine service, and it is worth asking whether your provider will hand back structured data rather than a paper invoice. A provider that already runs fleet tire programs will usually have position and depth data in their system already; the question is whether you have asked for it in a form you can ingest.

What The Data Cannot Tell You

A short list, offered in the spirit of not overselling.

It cannot tell you which tire model is better in the abstract, because your sample size is small and your route mix is confounded. Two brands compared across eight tires each on different vehicles is an anecdote with error bars, not a finding. It takes a lot of casings to make a defensible compound comparison.

It cannot tell you whether a wear pattern is caused by alignment, worn bushings, a bent component, or load distribution. It can tell you a pattern exists, on which corner, and since when, which is exactly the information a mechanical specialist needs to work efficiently.

It cannot price your risk. A tire pulled at 5/32" instead of run to 3/32" costs more per kilometre and reduces the probability of a winter incident. The data will show you the cost side of that trade precisely and the benefit side not at all. That is a judgement, and it should be made deliberately rather than emerging by accident from a cost-minimizing report.

Finally, it cannot replace looking at the tires. Walk-around inspections catch things no gauge does: a stone drilled into a shoulder, a bulge, a valve stem weeping, a wheel with a fresh curb gouge. The database is a memory aid for a physical process, not a substitute for it.

Rolling It Out In Four Weeks

A realistic sequence for someone starting from invoices in a folder.

Week one: build the vehicle table and the position vocabulary. Walk the yard and inventory every mounted tire — brand, size, DOT date code, current depth, position. That inventory becomes your opening balance. Assign every casing a fleet tire id and write it somewhere durable; a paint pen on the sidewall near the DOT stamp works and survives a season.

Week two: backfill acquisition costs from the last two years of invoices where you can identify the tire, and mark the rest as cost_unknown rather than guessing. Unknown is a valid state; invented numbers are not, and they will poison your cost per kilometre forever.

Week three: deploy the event form and run every service visit through it. Do not backfill events — start clean from a date and let history accumulate.

Week four: build the four dashboard numbers, even if three of them are thin. Show them to whoever owns the fleet budget. The conversation that follows will tell you which fields you actually need.

You will have usable wear rates after about four months and defensible cost per kilometre figures after a full replacement cycle, which for most light fleets is somewhere between eighteen months and three years. That is a long feedback loop and it is the main reason these systems do not get built. Start anyway; the alternative is starting the same clock two years later.

Sourcing And Fitment Notes That Affect The Model

Two operational realities intersect with the data model and are worth flagging for whoever specifies replacements.

Size and rating substitutions break comparability. If unit 7 gets a different size than it ran last cycle, its wear-rate history is no longer a clean time series, and your projection should reset rather than pretending the rate carries over. Flag substitutions explicitly with a spec_deviation boolean on the mount row so the query layer can exclude them from trend fits.

Availability drives substitution more than preference does. Sizes go in and out of supply, and a unit that needs rubber this week will get whatever fits and is rated correctly. Building your purchasing off the ninety-day projection rather than off failures is the practical defence, and browsing the available tire sizes and fitments ahead of a planned replacement window is a cheaper habit than scrambling. Where your vehicles are based also affects turnaround, and a provider's service coverage area is a legitimate input into how much buffer stock your projection needs to carry.

For heavier units and dedicated work trucks, the service requirements differ enough — torque specifications, dual spacing, load rating discipline — that it is worth confirming your provider handles commercial and heavy-duty tire work before you build a scheduling assumption on top of it. The same goes for the changeover window: everyone in the city wants the same two weeks in October and the same two in April, and an online scheduling system beats a phone queue when you are moving twenty vehicles through in a fortnight.

Closing Notes For The Ops Person Holding This

The system I have described is perhaps three hundred rows of schema and query, plus a form. It is not a hard build. What makes it work is discipline in two specific places: the odometer at every event, and the removal reason on every dismount. Get those two fields right and everything else is derivable. Get them wrong and no amount of modelling recovers the information.

Start with the tire instance table and the mount intervals. Add the measurement history next. Cost allocation can wait a month; distance and wear cannot, because those clocks are already running on every vehicle in your yard right now.

And keep the scope honest. This is a measurement and forecasting system for rubber. It will make your purchasing predictable, your outliers visible, and your budget defensible. It will not diagnose a suspension fault or align a truck — it will just tell you, with a date and a corner, that something is quietly costing you a set of tires a year. Working out which local tire service provider will hand you structured position-level data instead of a paper stub is the last piece, and it is worth asking about before you need it.