# Fuel transactions → actionable insight

**Implementation spec · on-prem · v1**
Scope: the fuel-card transaction feed only. Consumer: the *Fuel & telematics* section of the
daily yard brief (`yard-brief-daily.html`).
Sample profiled for this document: `fuelTransactions.csv`, 100 rows, 2026-07-13 → 2026-08-06.

---

## 0. The question, and the answer

> The real dataset has millions of rows. Way too many for AI context.

Correct, and it will never fit — so don't try. **The LLM is not the analytics engine. It is the
final 4 kilobytes.**

Everything that requires arithmetic over many rows — sums, medians, baselines, thresholds,
ranking, deduplication — is done in SQL on-prem, where the data already lives. What reaches the
model is a bounded **insight packet**: one small JSON document per yard per day, containing only
already-computed facts and the handful of exceptions that cleared a threshold.

```
raw feed          ~3,000,000 rows × 79 cols     ~2.1 GB
  ↓ ingest + conform (SQL)
silver txns       ~3,000,000 rows × 30 cols     ~120 MB columnar
  ↓ aggregate (SQL)
baselines         ~40,000 rows                  ~4 MB
  ↓ detect (SQL)
flags             3–8 rows per yard per day
  ↓ pack (app code)
insight packet    1 JSON document               ~4 KB      ← the only thing the LLM sees
  ↓ voice (LLM)  ↓ validate (code)
brief section     3 sentences + a table
```

That is a reduction of roughly **500,000 : 1**. The model's job is language: turning
`{detector: same_day_double_fill, unit: CHA19, combined_gal: 117.6, ceiling: 92.0}`
into *"Pull the receipt on CHA19 — two fills in one hour, 117.6 gallons against a 92-gallon
working ceiling."* It does not decide what is anomalous, and it never sees a transaction row.

**Consequence for cost and latency:** the pipeline runs nightly in minutes on one box. The LLM
call is ~1.5 K input tokens per yard. Nineteen yards is one cheap batch, not a data problem.

---

## 1. What the feed actually is

An API-paged export from the fuel-card provider, flattened to CSV. Three things are true of it
that change the design, all verified against the sample.

### 1.1 It is not RFC-4180 compliant — a naive parser silently corrupts 42 columns

The header declares **79** fields. Every data row contains **80**, because `ClientName` holds
`MAXIM CRANE WORKS, L.P.` with an **unquoted comma**.

```
header index 36 = ClientName
row fields 35..38: ['07/20/2026', 'MAXIM CRANE WORKS', ' L.P.', '0272852515']
```

`pandas.read_csv` / `csv.DictReader` will not error. They shift every column from index 37
onward by one, so `HistoricalDriverCity` reads `0272812566` and `HistoricalDriverAddressLine1`
reads a ZIP. The data looks plausible and is wrong.

**Rule:** field-count assertion at ingest, before parsing. Row-level repair, not file-level
tolerance:

```python
FIELD_COUNT = 79
CLIENT_NAME_IX = 36

def parse_line(line):
    f = next(csv.reader([line]))
    if len(f) == FIELD_COUNT + 1:
        # known defect: unquoted comma inside ClientName
        f = f[:CLIENT_NAME_IX] + [f[CLIENT_NAME_IX] + "," + f[CLIENT_NAME_IX+1]] \
            + f[CLIENT_NAME_IX+2:]
    if len(f) != FIELD_COUNT:
        raise QuarantineRow(f"field count {len(f)}")
    return dict(zip(HEADER, f))
```

Validate the repair with an independent cross-check, not by eye. Driver county / city / state /
ZIP must be mutually consistent: `CAMPBELL / WILDER / KY / 41076` ✓. If the repair is wrong,
this check fails loudly.

### 1.2 Half the columns carry no information

| Category | Count | Action |
|---|---:|---|
| 100 % null in sample | 13 | Drop. `BreakdownLevel4,5,6,7,8,9,10,11,12`, `VIN`, `DriverTelephone`, `ChargedByMiddleName`, `status.errorId` |
| Exactly one distinct value | 23 | Drop or move to config. `EngineType='***Unknown***'`, `Make='Unknown'`, `Model='Unknown'`, `ModelYear='Unknown'`, `GrossVehicleWeight=0.0`, `VehicleType`/`VehicleClassDescription`/`ProductClassDescription='DIVERSIFIED SERVICES'`, `POSEntryMethodCode='Pay at Pump'`, `FuelChargeType='Fuel'`, `FuelTransactionType='Retail'`, `PurgedUnitIndicator='N'`, `VehicleStatus='ACTIVE'`, `ClientName`, `Client#`, `status.*`, `result.*`, `loadDateTime` |
| Exact duplicates of another column | 6 | Keep one. `ClientAssetID`≡`HistoricalDriverLastName`; `Identifier`≡`HistoricalDriverEmployeeID`; `HistoricalBreakdownName`≡`ClientBreakdownName`; `HistoricalBreakdown`≡`BL1-BL2-BL3`; `SupplierDetails`≡city+state+zip; `PurchaseDay` derivable from `PurchaseDate` |
| **Load-bearing** | **37** | 30 of them reach silver — §2.2 |

36 of 79 columns are empty or constant before you write a single transform. Do the triage first:
every downstream query, index and review gets cheaper, and nobody wastes an afternoon deciding
what `CurrentPersonAssetFlag = 'CMN'` means.

Note the vehicle attributes are all `Unknown` — **this feed cannot tell you what the asset is.**
Make, model, year, GVW, engine and VIN must come from the asset master, joined on `Unit#`.

### 1.3 Three fields are not what their names promise

| Field | Documented meaning | Reality in the sample |
|---|---|---|
| `PurchaseTime` | Time of purchase | **Minute is always `:01`** (100/100 rows). Precision is *hourly*, not to the minute. |
| `UnitPrice` | Price per gallon | Rounded to 2 dp; `UnitPrice × Quantity ≠ ProductPriceAmount` in **75/100** rows, by up to $0.13. Derived, not authoritative. |
| `FuelOdometer` | Odometer at fill | 6/100 rows are `1.0`; 10/100 are exact multiples of 1000 (hand-keyed). Range 1 → 532,430. |

**Rules:** never advertise sub-hour timing. Recompute `price_per_gal = amount / gallons` and
treat `ProductPriceAmount` + `Quantity` as the source of truth. Gate every odometer reading
through a validity test before using it for anything.

> This corrects a line in the current brief mockup: *"Truck 12 · two fills, 40 min apart"* is
> **not derivable** from this feed. The honest phrasing is *"two fills in the same hour."*

---

## 2. Layer 1–2: ingest and conform

### 2.1 Bronze — land it verbatim, once

```sql
CREATE TABLE bronze.fuel_txn_raw (
  src_file        VARCHAR      NOT NULL,
  src_line_no     INTEGER      NOT NULL,
  ingested_at     TIMESTAMP    NOT NULL DEFAULT current_timestamp,
  payload         JSON         NOT NULL,   -- the 79 repaired fields, as-is
  PRIMARY KEY (src_file, src_line_no)
);

CREATE TABLE bronze.fuel_txn_quarantine (
  src_file VARCHAR, src_line_no INTEGER, ingested_at TIMESTAMP,
  reason   VARCHAR, raw_line VARCHAR
);
```

Bronze is append-only and never edited. Reprocessing is always "re-derive silver from bronze",
which means a detector bug is a re-run, not a data-recovery incident.

**Idempotency.** `ChargeReferenceNumber` is unique across the sample (100/100) and is the
provider's transaction reference — use it as the natural key, with `SupplierInvoice#` as a
secondary. The feed re-sends corrections, so silver is an **upsert on `txn_id`**, not an insert.

### 2.2 Silver — the 30 columns that matter

```sql
CREATE TABLE silver.fuel_txn (
  -- identity
  txn_id                VARCHAR PRIMARY KEY,   -- ChargeReferenceNumber
  supplier_invoice_no   VARCHAR,               -- SupplierInvoice#

  -- when  (HOUR precision only — see 1.3)
  purchased_at_hour     TIMESTAMP NOT NULL,    -- PurchaseDate + PurchaseTime, minute forced to 0
  purchased_date        DATE      NOT NULL,
  purchased_hour        SMALLINT  NOT NULL,
  purchased_dow         SMALLINT  NOT NULL,
  processed_date        DATE,                  -- Processdate; lag = detection latency
  settlement_lag_days   SMALLINT,              -- processed_date - purchased_date

  -- asset & org
  unit_tag              VARCHAR NOT NULL,      -- Unit#  e.g. 'CRL55' — yard-prefixed asset tag
  asset_id              VARCHAR,               -- Identifier e.g. '53004975'
  region_code           VARCHAR,               -- BreakdownLevel1  CORPRT|CENREG|MWTREG|SEAREG…
  district_code         VARCHAR,               -- BreakdownLevel2
  yard_code             VARCHAR NOT NULL,      -- BreakdownLevel3  CRL|JAC|IND|MEM|CHA…
  yard_name             VARCHAR,               -- ClientBreakdownName
  cost_center           VARCHAR,               -- HistoricalBreakdown  'CORPRT-000-CRL'

  -- card & driver
  card_last5            VARCHAR,               -- CoBrandCard#
  card_no               VARCHAR,               -- HistoricalServiceCard#
  account_no            VARCHAR,               -- CoBrandAccount#
  driver_first          VARCHAR,
  driver_last           VARCHAR,
  driver_home_city      VARCHAR,
  driver_home_state     VARCHAR,
  driver_home_county    VARCHAR,

  -- merchant
  merchant_name         VARCHAR,               -- SupplierName  'PILOT 1025'
  merchant_site_id      VARCHAR,               -- SiteId
  merchant_city         VARCHAR,
  merchant_state        VARCHAR,
  merchant_zip          VARCHAR,

  -- product & money  (authoritative: gallons + amount)
  product_desc          VARCHAR,               -- PREMIUM DIESEL | UNLEADED REGULAR GASOLINE | ETHANOL BLEND
  product_category      VARCHAR,               -- Diesel | Regular | Ethanol
  gallons               DECIMAL(9,3) NOT NULL, -- Quantity
  amount_usd            DECIMAL(11,2) NOT NULL,-- ProductPriceAmount
  price_per_gal         DECIMAL(7,4) NOT NULL,  -- amount_usd / gallons   (DERIVED)
  price_per_gal_feed    DECIMAL(7,4),          -- UnitPrice, kept only for reconciliation

  -- odometer, gated
  odometer_raw          DECIMAL(11,1),
  odometer_is_valid     BOOLEAN NOT NULL,

  -- lineage
  src_file              VARCHAR, src_line_no INTEGER, loaded_at TIMESTAMP
);

CREATE INDEX ix_ft_unit_date ON silver.fuel_txn (unit_tag, purchased_date);
CREATE INDEX ix_ft_yard_date ON silver.fuel_txn (yard_code, purchased_date);
CREATE INDEX ix_ft_mkt       ON silver.fuel_txn (merchant_state, product_category, purchased_date);
```

Partition by `purchased_date` (monthly). Every detector query is scoped to a rolling window, so
partition pruning is what keeps this on one box.

**Derivation rules — all deterministic, all in SQL:**

```sql
INSERT INTO silver.fuel_txn
SELECT
  payload->>'ChargeReferenceNumber',
  payload->>'SupplierInvoice#',
  -- hour precision: parse then truncate. Do not preserve the fake :01.
  date_trunc('hour', strptime(
      (payload->>'PurchaseDate') || ' ' || (payload->>'PurchaseTime'),
      '%m/%d/%Y %I:%M %p')),
  ...
  CAST(payload->>'Quantity'           AS DECIMAL(9,3))  AS gallons,
  CAST(payload->>'ProductPriceAmount' AS DECIMAL(11,2)) AS amount_usd,
  CAST(payload->>'ProductPriceAmount' AS DECIMAL(11,2))
    / NULLIF(CAST(payload->>'Quantity' AS DECIMAL(9,3)), 0)              AS price_per_gal,
  CAST(payload->>'FuelOdometer' AS DECIMAL(11,1))                        AS odometer_raw,
  -- odometer validity gate
  (CAST(payload->>'FuelOdometer' AS DECIMAL) > 100
   AND CAST(payload->>'FuelOdometer' AS DECIMAL) < 2000000
   AND CAST(payload->>'FuelOdometer' AS DECIMAL) % 1000 <> 0)            AS odometer_is_valid
FROM bronze.fuel_txn_raw;
```

### 2.3 Reference tables you must build — the feed does not contain them

These four gaps are the difference between "a chart of gallons" and "an action a manager takes."

| Table | Grain | Why | Source |
|---|---|---|---|
| `ref.asset` | `unit_tag` | Feed says Make/Model/Year = `Unknown`. Needed to interpret gallons at all. | Asset master / ERP |
| `ref.asset_tank` | `unit_tag` | **Tank capacity.** Without it, "84 gal on a 40 gal tank" is unprovable. | Asset master, or spec sheet by model |
| `ref.yard` | `yard_code` | Yard name, address, lat/lon, manager, timezone, bulk-fuel contract price | Ops config |
| `ref.card_assignment` | `card_no`, effective-dated | Which card belongs to which unit and driver *at a point in time* | Card admin |

In the sample, `Unit#`, `Identifier` and `CoBrandCard#` each have exactly 42 distinct values over
42 units — card-to-unit is currently 1:1. Build `card_assignment` as effective-dated anyway; the
day that stops being 1:1 is the day a reassignment silently misattributes spend, and an
effective-dated table catches it while a snapshot does not.

**Until `ref.asset_tank` exists, detector D2 uses a statistical ceiling (§4.2) and its output
must be labelled "review", not "act".** State that in the packet's `caveats` array so the
narrative can't overclaim.

---

## 3. Layer 3: baselines

A detector is only as good as what it compares against. Compute baselines as tables, refreshed
nightly, so the detector pass is a cheap join instead of a window function over history.

```sql
-- Per-unit fill profile. 180 days, so a unit with weekly fills has ~25 observations.
CREATE OR REPLACE TABLE gold.unit_fill_baseline AS
SELECT
  unit_tag,
  product_category,
  count(*)                                                    AS n_fills,
  median(gallons)                                             AS gal_med,
  quantile_cont(gallons, 0.95)                                AS gal_p95,
  max(gallons)                                                AS gal_max,
  median(price_per_gal)                                       AS ppg_med,
  count(DISTINCT purchased_date)                              AS active_days,
  count(*) * 1.0 / NULLIF(count(DISTINCT purchased_date),0)   AS fills_per_active_day
FROM silver.fuel_txn
WHERE purchased_date >= current_date - INTERVAL 180 DAY
GROUP BY 1,2;

-- Local market price. Bucket must be large enough to have a trustworthy median.
CREATE OR REPLACE TABLE gold.market_price_baseline AS
SELECT
  merchant_state, product_category, date_trunc('week', purchased_date) AS wk,
  count(*)                     AS n,
  median(price_per_gal)        AS ppg_med,
  quantile_cont(price_per_gal, 0.90) AS ppg_p90
FROM silver.fuel_txn
WHERE purchased_date >= current_date - INTERVAL 90 DAY
GROUP BY 1,2,3;

-- Yard roll-up: this is what feeds the KPI tile and the peer comparison.
CREATE OR REPLACE TABLE gold.yard_fuel_daily AS
SELECT
  yard_code, purchased_date,
  count(*) AS txns, sum(gallons) AS gallons, sum(amount_usd) AS spend,
  sum(amount_usd) / NULLIF(sum(gallons),0) AS ppg
FROM silver.fuel_txn
GROUP BY 1,2;
```

### 3.1 The comparison trap: never average $/gal across product categories

The sample's cheapest yard by blended price per gallon is `NAS` at **$3.460/gal** — which looks
like a benchmark until you look at the rows. It is a *single ethanol fill*. `BHM` at $3.578 is
four ethanol fills. Neither yard bought a drop of diesel in the window.

Ranked on **diesel only**, the honest picture is different:

| rank | yard | n | $/gal |
|---:|---|---:|---:|
| 1 | FRE | 2 | 4.691 → *n too low, suppress* |
| 6 | IND | 13 | 5.194 |
| 7 | JAC | 16 | 5.234 |
| 11 | **CRL** | **34** | **5.332** |
| 14 | CHA | 4 | 5.452 |
| 15 | STO | 1 | 6.699 → *n too low, suppress* |

Two rules fall out of this, and they apply to every ratio in the brief:

1. **Comparison keys must include product category.** A blended $/gal is fine as a spend number
   and worthless as a benchmark. Ship `price_per_gal_blended` for the KPI tile and
   `by_product[*].price_per_gal` for anything comparative.
2. **Suppress buckets below a minimum n.** A yard with one fill will top or bottom any league
   table. Require `n >= 10` at yard-week grain before a peer rank is published; below that,
   report the number without the rank.

An LLM handed a blended table will faithfully write *"NAS is buying fuel 34 % cheaper than you"*
and it will be nonsense. The guardrail belongs in SQL, not the prompt.

**Why the baseline tables are the answer to the context problem.** `gold.market_price_baseline`
over the full history is ~40,000 rows and answers "is $5.42/gal high for Texas diesel this week"
in a single lookup. The 100-row sample reaches n ≥ 3 in only **9 of 40** state-week buckets — far
too thin to set a threshold. The millions of rows are not a burden here; they are precisely what
makes the median trustworthy. They just never need to travel.

---

## 4. Layer 4: detectors

Each detector is one SQL statement writing to `gold.fuel_flag`. Every flag carries the evidence
that produced it, so the narrative layer never has to compute anything and a manager can always
drill to rows.

```sql
CREATE TABLE gold.fuel_flag (
  flag_id        VARCHAR PRIMARY KEY,   -- deterministic: detector|entity|date
  run_date       DATE NOT NULL,
  detector       VARCHAR NOT NULL,
  severity       VARCHAR NOT NULL,      -- act_today | watch | fyi
  yard_code      VARCHAR NOT NULL,
  unit_tag       VARCHAR,
  driver_name    VARCHAR,
  occurred_at    TIMESTAMP,
  exposure_usd   DECIMAL(11,2),         -- the number that decides ranking
  evidence       JSON NOT NULL,
  txn_ids        JSON NOT NULL,         -- drill-down
  fallback_text  VARCHAR NOT NULL       -- deterministic sentence; see §6
);
```

`flag_id` is deterministic so a re-run is an upsert and a flag can be suppressed permanently.

### 4.1 D1 — Same-day repeat fill *(strongest signal in the sample)*

Two dispenses on one unit on one day, together exceeding what that unit ever takes in one fill.
Catches tank-topping into a second container, card sharing, and off-book dispensing.

```sql
WITH day_roll AS (
  SELECT unit_tag, purchased_date, yard_code,
         count(*) AS fills, sum(gallons) AS gal, sum(amount_usd) AS amt,
         min(purchased_hour) AS h_first, max(purchased_hour) AS h_last,
         list(gallons) AS gal_list, list(txn_id) AS txns
  FROM silver.fuel_txn
  WHERE purchased_date >= current_date - INTERVAL 7 DAY
  GROUP BY 1,2,3
  HAVING count(*) > 1
)
SELECT d.*, b.gal_p95,
       CASE WHEN d.h_last = d.h_first THEN 'act_today' ELSE 'watch' END AS severity,
       -- exposure = the gallons above the unit's own ceiling, priced
       (d.gal - b.gal_p95) * (d.amt / d.gal) AS exposure_usd
FROM day_roll d
JOIN gold.unit_fill_baseline b USING (unit_tag)
WHERE b.n_fills >= 8
  AND d.gal > b.gal_p95 * 1.15;
```

Sample result — 4 unit-days had repeat fills, one of them inside a single hour:

| unit | date | hours | gallons | combined |
|---|---|---|---|---|
| **CHA19** | 2026-07-20 | 07, **07** | 78.5 + 39.1 | **117.6** |
| JAC38 | 2026-08-06 | 12, 16 | 41.7 + 117.5 | 159.2 |
| FRE29 | 2026-07-14 | 09, 13 | 100.2 + 53.7 | 153.9 |
| MEM5 | 2026-07-15 | 14, 15 | 38.1 + 46.9 | 85.0 |

CHA19 is `act_today` — same hour bucket, 117.6 gal for $646.77 at PILOT SITE 071, Port Wentworth GA,
driver R. Mathews. The others are `watch`: a crane support truck legitimately fuels twice on a long
move. Severity is set by the *hour collision*, not by the repeat alone.

### 4.2 D2 — Fill exceeds working capacity

The tank-capacity table does not exist yet, so use the unit's own trailing distribution as the
ceiling and require enough history to trust it.

```sql
SELECT t.txn_id, t.yard_code, t.unit_tag, t.gallons, b.gal_p95,
       (t.gallons - b.gal_p95) * t.price_per_gal AS exposure_usd,
       'watch' AS severity                       -- never act_today on a statistical ceiling
FROM silver.fuel_txn t
JOIN gold.unit_fill_baseline b USING (unit_tag, product_category)
WHERE t.purchased_date >= current_date - INTERVAL 7 DAY
  AND b.n_fills >= 8
  AND t.gallons > b.gal_p95 * 1.25;
```

Sample distribution — min 10.4, median 82.3, p95 132.1, max 145.0 gal. Only **14 of 42** units
have ≥ 3 fills in 25 days, which is why the lookback is 180 days and the `n_fills >= 8` guard is
not optional. When `ref.asset_tank` lands, swap `gal_p95` for `tank_gal * 1.05` and promote the
severity to `act_today`.

### 4.3 D3 — Price outlier vs local market

```sql
SELECT t.txn_id, t.yard_code, t.unit_tag, t.merchant_name, t.merchant_state,
       t.price_per_gal, m.ppg_med,
       (t.price_per_gal - m.ppg_med) * t.gallons AS exposure_usd,
       'watch' AS severity
FROM silver.fuel_txn t
JOIN gold.market_price_baseline m
  ON m.merchant_state = t.merchant_state
 AND m.product_category = t.product_category
 AND m.wk = date_trunc('week', t.purchased_date)
WHERE t.purchased_date >= current_date - INTERVAL 7 DAY
  AND m.n >= 30                                  -- do not benchmark against a thin bucket
  AND t.price_per_gal > m.ppg_med * 1.08         -- one-sided: cheap fuel is not a problem
  AND t.gallons >= 25;                           -- and the dollars must be worth a phone call
```

Sample: **1 of 88** diesel fills sits > 10 % off its state-week median — and it is *below*
(FRE29 at ONE 9 1291, TX: $4.489 vs $4.999). One-sided comparison keeps savings out of the alert
queue.

### 4.4 D4 — Isolated out-of-corridor fill

**The obvious version of this rule is wrong.** Comparing merchant state to driver home state fires
on **52 of 100** rows in the sample — this is a road-crane fleet, and out-of-state fuel is the
job, not an exception. A detector with a 52 % base rate destroys trust in the whole brief.

The defensible version: flag a fill whose state matches neither the unit's previous nor next fill
— an isolated detour rather than a trip.

```sql
WITH seq AS (
  SELECT txn_id, yard_code, unit_tag, purchased_at_hour, merchant_state,
         merchant_city, gallons, amount_usd,
         lag(merchant_state)  OVER w AS prev_state,
         lead(merchant_state) OVER w AS next_state
  FROM silver.fuel_txn
  WHERE purchased_date >= current_date - INTERVAL 30 DAY
  WINDOW w AS (PARTITION BY unit_tag ORDER BY purchased_at_hour)
)
SELECT *, amount_usd AS exposure_usd, 'fyi' AS severity
FROM seq
WHERE prev_state IS NOT NULL AND next_state IS NOT NULL
  AND merchant_state <> prev_state AND merchant_state <> next_state;
```

Ship it at `fyi` only. Real off-route detection needs GPS, and **there is no GPS in this feed** —
that belongs to the telematics source, which is a separate integration.

### 4.5 D5 — Product mismatch (misfuel)

```sql
WITH modal AS (
  SELECT unit_tag, product_category,
         row_number() OVER (PARTITION BY unit_tag ORDER BY count(*) DESC) AS rk
  FROM silver.fuel_txn
  WHERE purchased_date >= current_date - INTERVAL 180 DAY
  GROUP BY 1,2
)
SELECT t.txn_id, t.yard_code, t.unit_tag, t.product_category AS got,
       m.product_category AS expected, t.amount_usd AS exposure_usd, 'act_today' AS severity
FROM silver.fuel_txn t
JOIN modal m ON m.unit_tag = t.unit_tag AND m.rk = 1
WHERE t.purchased_date >= current_date - INTERVAL 7 DAY
  AND t.product_category <> m.product_category;
```

Sample mix is 88 Diesel / 7 Regular / 5 Ethanol, and **zero units take more than one category** —
so the base rate is ~0 and any hit is high signal. Cheap to run, keep it. Gasoline in a diesel
tank is a five-figure repair, which is why this one is `act_today` despite a low prior.

### 4.6 D6 — Implied fuel economy out of band

Requires two consecutive *valid* odometer readings on the same unit.

```sql
WITH chain AS (
  SELECT unit_tag, txn_id, yard_code, purchased_at_hour, gallons, odometer_raw,
         lag(odometer_raw) OVER w AS prev_odo,
         lag(purchased_at_hour) OVER w AS prev_at
  FROM silver.fuel_txn
  WHERE odometer_is_valid
  WINDOW w AS (PARTITION BY unit_tag ORDER BY purchased_at_hour)
)
SELECT *, (odometer_raw - prev_odo) / NULLIF(gallons,0) AS implied_mpg,
       'watch' AS severity
FROM chain
WHERE prev_odo IS NOT NULL
  AND odometer_raw > prev_odo
  AND odometer_raw - prev_odo BETWEEN 20 AND 2000
  AND ((odometer_raw - prev_odo) / NULLIF(gallons,0) < 2.0
    OR (odometer_raw - prev_odo) / NULLIF(gallons,0) > 12.0);
```

The `odometer_is_valid` gate drops 16 % of the sample (6 rows at `1.0`, 10 hand-keyed round
thousands). Report `odometer_valid_pct` in the packet so the narrative can hedge honestly — and
so somebody eventually fixes the pump prompt.

### 4.7 Calibration discipline

A detector ships only with a measured base rate. Run it over 90 days of history first:

| Detector | Sample fire rate | Ship at | Gate |
|---|---:|---|---|
| D1 same-day repeat, same hour | 1 / 100 | `act_today` | `n_fills >= 8` |
| D1 same-day repeat, diff hour | 3 / 100 | `watch` | as above |
| D2 over working capacity | 0 / 100 | `watch` | `n_fills >= 8`; promote when tank table lands |
| D3 price outlier | 0 / 100 one-sided | `watch` | market bucket `n >= 30` |
| D4 isolated corridor break | needs ordering | `fyi` | never `act_today` without GPS |
| D5 misfuel | 0 / 100 | `act_today` | — |
| D6 MPG out of band | needs odo chain | `watch` | valid odometer pair |
| ~~home-state mismatch~~ | **52 / 100** | **rejected** | base rate too high to be information |

**Budget: at most 3 fuel flags reach the brief per yard per day**, ranked by `exposure_usd`. The
brief has room for three lines. A detector that cannot earn a top-three slot on exposure is a
dashboard row, not an alert.

---

## 5. Layer 5: the insight packet

One document per yard per day. This is the entire interface between the data platform and the
LLM.

Below, `totals`, `flags[0].evidence` and `data_quality` are **measured from the sample** for
`yard_code = 'CRL'` and `unit_tag = 'CHA19'`. `vs_prior_30d`, `unit_p95_single_fill` and the
quarantine count are **illustrative** — the 25-day sample has no prior period, and CHA19 has only
3 fills in it, below D2's `n_fills >= 8` gate. In production both come from the 180-day baseline.

```json
{
  "schema": "yard.fuel.insight.v1",
  "generated_at": "2026-08-07T05:12:00-05:00",
  "yard":   { "code": "CRL", "name": "Crawler Crane Division",
              "cost_center": "CORPRT-000-CRL", "manager": "R. Alvarez" },
  "window": { "from": "2026-07-13", "to": "2026-08-06", "days": 25 },

  "totals": {
    "txns": 38, "gallons": 3696, "spend_usd": 19485.08,
    "price_per_gal_blended": 5.272,
    "by_product": {
      "Diesel":  { "txns": 34, "gallons": 3567, "price_per_gal": 5.332 },
      "Regular": { "txns":  4, "gallons":  129, "price_per_gal": 3.600 }
    },
    "vs_prior_30d": { "gallons_pct": 8.4, "price_per_gal_delta": 0.09 },
    "peer": { "basis": "Diesel only", "rank": 11, "of": 15,
              "best": { "code": "IND", "name": "Indianapolis", "price_per_gal": 5.194 },
              "gap_usd_per_gal": 0.138, "gap_usd_per_month": 589.46 }
  },

  "flags": [
    {
      "flag_id": "D1|CHA19|2026-07-20",
      "detector": "same_day_repeat_fill",
      "severity": "act_today",
      "unit_tag": "CHA19", "driver_name": "R. Mathews",
      "merchant": "PILOT SITE 071, Port Wentworth GA",
      "occurred_at": "2026-07-20T07:00:00",
      "time_precision": "hour",
      "evidence": { "fills": 2, "gallons": [78.5, 39.1], "combined_gallons": 117.6,
                    "unit_p95_single_fill": 92.0, "same_hour": true,
                    "amount_usd": 646.77, "price_per_gal": 5.499 },
      "exposure_usd": 140.77,
      "owner": "yard_manager", "deadline_local": "16:30",
      "fallback_text": "CHA19 took two fills in the 07:00 hour on Jul 20 — 117.6 gal against a 92 gal working ceiling.",
      "drill_url": "/fuel/txn?unit=CHA19&date=2026-07-20"
    }
  ],

  "flag_summary": { "surfaced": 3, "total_open": 7, "suppressed": 2,
                    "exposure_usd": 217.35 },

  "data_quality": { "rows_fetched": 11310, "rows_loaded": 11284, "quarantined": 26,
                    "odometer_valid_pct": 84.0, "field_count_repairs": 11284 },

  "caveats": [
    "Fill time precision is hourly; the feed's minute field is always :01.",
    "No tank-capacity reference table yet — capacity ceilings are statistical (unit p95).",
    "No GPS in this feed; corridor flags are state-sequence heuristics, not route deviation."
  ]
}
```

Design rules for the packet, all of which exist to keep the LLM honest:

1. **Every number the narrative may use is in the packet.** If it isn't here, it cannot be said.
2. **No raw rows.** `txn_ids` stay server-side behind `drill_url`.
3. **Pre-rounded.** The model must never do arithmetic. `gap_usd_per_month` is computed, not implied.
4. **`fallback_text` is written by the detector**, deterministically, from a template. It is
   correct but flat. It is also the safety net (§6).
5. **`caveats` are machine-generated from the data-quality layer**, not hand-written prose, so
   they cannot drift out of sync with the pipeline.
6. **Hard cap ~6 KB.** If flags overflow, drop by lowest exposure and increment `suppressed`.

---

## 6. Layer 6: the LLM step, and how it is constrained

The model gets the packet and one instruction: **voice these facts, add nothing.**

```
You are writing the Fuel & telematics section of a daily yard brief for a yard manager
reading on a phone at 5:30 AM. Input is a JSON packet of already-computed facts.

Produce exactly:
  1. Three pill labels: gallons, variance vs 30-day average, price per gallon.
  2. One table row per flag: short description, dollar exposure.
  3. One closing sentence, max 30 words, naming the decision and its deadline.

Rules:
  - Use ONLY numbers present in the packet. Never compute, infer, or round differently.
  - Never claim precision the packet denies. If time_precision is "hour", say "in the same
    hour", never "minutes apart".
  - Respect severity: act_today items get the imperative; watch items get "review".
  - Do not use the words "anomaly", "insight", "leverage", or "optimize".
  - If flags is empty, say what is normal and stop. Do not manufacture concern.
```

### 6.1 Output validation — the part that makes this safe to automate

Deterministic post-check on every generation. This is not optional; it is what lets a nightly
job send unreviewed prose to nineteen managers.

```python
NUM = re.compile(r'-?\$?\d[\d,]*\.?\d*%?')

def validate(text: str, packet: dict) -> bool:
    allowed = numeric_closure(packet)          # every number + accepted roundings
    for tok in NUM.findall(text):
        if normalize(tok) not in allowed:
            return False                       # hallucinated figure
    if packet_says_hourly(packet) and re.search(r'\bminutes?\b', text):
        return False                           # overclaimed precision
    if len(text.split()) > WORD_BUDGET:
        return False
    return True

# On failure: log, alert the pipeline owner, and render fallback_text.
# The brief always ships. It just ships flat instead of well-written.
```

Track `fallback_rate` as a service metric. A rising fallback rate means the packet and the prompt
have drifted apart — usually a new detector whose evidence keys the prompt has never seen.

---

## 7. What this feed can and cannot support

Honest mapping to the fuel lines currently in the brief mockup:

| Brief line | Supported? | Needs |
|---|---|---|
| `1,842 gal yesterday` | ✅ | `sum(gallons)` |
| `+8.4% vs 30-day avg` | ✅ | `gold.yard_fuel_daily` |
| `$3.42/gal` | ✅ | `sum(amount)/sum(gallons)` — **not** `avg(UnitPrice)` |
| *"84 gal on a 40 gal tank"* | ⚠️ | `ref.asset_tank`. Until then: "117.6 gal against a 92 gal working ceiling" |
| *"two fills, 40 min apart"* | ❌ | Feed is hourly. Rephrase to "same hour". |
| *"4.1 hrs idle, 2 deliveries"* | ❌ | Telematics engine-hours + dispatch. Different source. |
| *"off-route fill, 22 mi from any job"* | ❌ | GPS + job locations. Different source. |
| Price vs local market | ✅ | `gold.market_price_baseline`, needs bucket n ≥ 30 |
| Misfuel | ✅ | modal product per unit |
| Cost per operating hour | ❌ | Telematics hour meters |

Three of nine lines in the mockup are not derivable from fuel-card data alone. Ship the six that
are, and put the other three behind the telematics integration rather than approximating them —
a brief that is wrong once is a brief nobody opens again.

---

## 8. Build order

| # | Deliverable | Done when |
|---|---|---|
| 1 | Bronze loader with field-count assertion + quarantine | Sample loads 100/100; a deliberately corrupted row lands in quarantine, not silver |
| 2 | Silver transform + geography cross-check | County/city/state/ZIP consistent on 100 % of loaded rows |
| 3 | `ref.yard`, `ref.asset`, `ref.card_assignment` | Every silver row joins to a yard; unmatched rate < 0.1 % |
| 4 | `gold.yard_fuel_daily` + the three KPI pills | Numbers reconcile to the card statement to the cent |
| 5 | Baselines + **D1 and D5 only** | 90-day backtest fire rates match §4.7 |
| 6 | `gold.fuel_flag` + packet builder | Packet ≤ 6 KB for the busiest yard |
| 7 | LLM step + validator + fallback | 200-packet dry run, fallback rate < 5 %, zero hallucinated figures |
| 8 | Suppression + precision tracking | "Not useful" click writes a suppression; weekly precision report exists |
| 9 | `ref.asset_tank`; promote D2 | D2 at `act_today` against real capacity |
| 10 | Telematics join; D4 becomes real, idle/MPG lines unlock | — |

Steps 1–7 are the whole loop end to end. Resist adding detectors before step 8 exists: without
precision tracking you cannot tell a working detector from a noisy one, and the fuel section gets
exactly three lines a day to spend.

## 9. Decisions that need a human

1. **Tank capacity** — does the asset master hold it, or must it be derived per model? Blocks D2
   at `act_today` and blocks the most quotable line in the brief.
2. **Yard attribution** — is `BreakdownLevel3` the *home* yard or the *charged* yard? In the
   sample, unit `CHA19` is coded to the Charlotte yard but fuels in Port Wentworth GA and
   St Matthews SC; `CRL50` is coded `CORPRT-000-CRL` with a Kentucky driver and fuels in
   Dunnigan CA. A crane on a three-month job charges somewhere — whose brief does its fuel
   appear in, and does the receiving yard manager have any authority over it?
3. **Exposure convention** — is D1 exposure the gallons above ceiling (used here, $214.60) or the
   full transaction ($641.20)? It changes flag ranking across all detectors.
4. **Settlement lag** — `Processdate` trails `PurchaseDate`. Does the brief report on purchase
   date (fresher, restates) or settlement date (stable, staler)?
5. **Suppression scope** — when a manager dismisses a flag, is it silenced for that unit, that
   detector, or that unit+detector pair, and for how long?