9943932200
Self-contained ingestion module (mirrors fleettickets) for the WhatsApp fuel-record feed in the rustfs `fuel` bucket: - import_fuel.py — snapshot/changes/file modes, raw-jsonb upsert on id - migrations/01_fuel_schema.sql — fuel schema, plate/fuel-type/department normalizers, trigger-derived columns, reporting.v_fuel_fills + v_fuel_efficiency, grafana_ro grants - s3util.py / shared.py / run_migrations.py — rustfs client + DB helpers - docs/plan.html — implementation plan Co-Authored-By: Claude Opus 4.8 <noreply@anthropic.com>
223 lines
17 KiB
HTML
223 lines
17 KiB
HTML
<!DOCTYPE html>
|
||
<html lang="en">
|
||
<head>
|
||
<meta charset="utf-8">
|
||
<meta name="viewport" content="width=device-width, initial-scale=1">
|
||
<title>FleetFuel — Implementation Plan</title>
|
||
<style>
|
||
:root{
|
||
--bg:#15110c; --panel:#1d1812; --panel2:#241d15; --ink:#f3ebdd; --muted:#b8a98f;
|
||
--line:#3a2f22; --accent:#e8913c; --accent2:#f0b454; --green:#7fb069; --code:#120e09;
|
||
}
|
||
*{box-sizing:border-box}
|
||
html{scroll-behavior:smooth}
|
||
body{
|
||
margin:0; background:var(--bg); color:var(--ink);
|
||
font:16px/1.65 -apple-system,BlinkMacSystemFont,"Segoe UI",Roboto,Helvetica,Arial,sans-serif;
|
||
-webkit-font-smoothing:antialiased;
|
||
}
|
||
.wrap{max-width:920px; margin:0 auto; padding:48px 24px 96px}
|
||
header.hero{
|
||
border:1px solid var(--line); border-radius:16px; padding:32px 32px 28px;
|
||
background:linear-gradient(135deg,#221a11,#1a140d);
|
||
box-shadow:0 10px 40px rgba(0,0,0,.35);
|
||
}
|
||
.kicker{color:var(--accent2); font-weight:700; letter-spacing:.14em; text-transform:uppercase; font-size:12px}
|
||
h1{margin:.3em 0 .15em; font-size:34px; letter-spacing:-.02em}
|
||
.sub{color:var(--muted); font-size:16px; margin:0}
|
||
.pipeline{display:flex; flex-wrap:wrap; gap:8px; margin-top:20px}
|
||
.pipeline span{
|
||
background:var(--panel2); border:1px solid var(--line); border-radius:999px;
|
||
padding:6px 14px; font-size:13px; color:var(--ink); white-space:nowrap;
|
||
}
|
||
.pipeline .arrow{border:none; background:none; color:var(--accent); padding:6px 0}
|
||
h2{
|
||
margin:46px 0 14px; font-size:23px; padding-bottom:8px; border-bottom:1px solid var(--line);
|
||
display:flex; align-items:center; gap:12px;
|
||
}
|
||
h2 .num{
|
||
flex:0 0 auto; width:30px; height:30px; border-radius:8px; background:var(--accent);
|
||
color:#1a120a; font-size:15px; font-weight:800; display:grid; place-items:center;
|
||
}
|
||
h3{margin:26px 0 8px; font-size:17px; color:var(--accent2)}
|
||
p{margin:.6em 0}
|
||
code{
|
||
background:var(--code); color:#f0c992; padding:.12em .42em; border-radius:5px;
|
||
font:13.5px/1.5 ui-monospace,SFMono-Regular,Menlo,Consolas,monospace;
|
||
}
|
||
pre{
|
||
background:var(--code); border:1px solid var(--line); border-radius:10px; padding:16px;
|
||
overflow:auto; font:13px/1.6 ui-monospace,SFMono-Regular,Menlo,Consolas,monospace; color:#d8c7a6;
|
||
}
|
||
pre code{background:none; padding:0; color:inherit}
|
||
ul{margin:.5em 0; padding-left:1.3em}
|
||
li{margin:.3em 0}
|
||
a{color:var(--accent2)}
|
||
.note{
|
||
border-left:3px solid var(--accent); background:var(--panel2); padding:12px 16px;
|
||
border-radius:0 10px 10px 0; margin:18px 0; color:var(--ink);
|
||
}
|
||
.note.warn{border-left-color:#e0683c}
|
||
table{width:100%; border-collapse:collapse; margin:14px 0; font-size:14px}
|
||
th,td{text-align:left; padding:10px 12px; border-bottom:1px solid var(--line); vertical-align:top}
|
||
th{color:var(--accent2); font-size:12px; text-transform:uppercase; letter-spacing:.06em}
|
||
td code{font-size:12.5px}
|
||
.tag{display:inline-block; font-size:11px; font-weight:700; padding:2px 8px; border-radius:6px}
|
||
.tag.new{background:rgba(127,176,105,.18); color:var(--green)}
|
||
.tag.edit{background:rgba(232,145,60,.18); color:var(--accent2)}
|
||
footer{margin-top:60px; color:var(--muted); font-size:13px; text-align:center; border-top:1px solid var(--line); padding-top:20px}
|
||
.pill{color:var(--muted); font-size:13px}
|
||
</style>
|
||
</head>
|
||
<body>
|
||
<div class="wrap">
|
||
|
||
<header class="hero">
|
||
<div class="kicker">Implementation Plan · 17_fleetfuel</div>
|
||
<h1>FleetFuel</h1>
|
||
<p class="sub">Ingest the RustFS <code>fuel</code> bucket → shared database → a new <strong>Fuel Log</strong> tab in FleetOps.</p>
|
||
<div class="pipeline">
|
||
<span>RustFS <code>fuel</code> bucket</span><span class="arrow">→</span>
|
||
<span><code>17_fleetfuel</code> ingestion</span><span class="arrow">→</span>
|
||
<span><code>fuel</code> schema · tracksolid_db</span><span class="arrow">→</span>
|
||
<span><code>dashboard_api</code></span><span class="arrow">→</span>
|
||
<span>FleetOps “Fuel Log” tab</span>
|
||
</div>
|
||
</header>
|
||
|
||
<h2><span class="num">·</span>Context</h2>
|
||
<p>FleetOps (<code>15_fleetops</code>) is the fleet <strong>operations</strong> analytics SPA. It already has a
|
||
trip-<em>derived</em> fuel panel (<code>GET /analytics/fuel</code>), but that data is effectively empty — estimated
|
||
fuel needs <code>devices.fuel_100km</code> (NULL fleet-wide) and actual <code>oils</code> is sparse.</p>
|
||
<p>A real fuel-spend feed now lands in the RustFS <code>fuel</code> bucket: WhatsApp fuel-update messages,
|
||
extracted by an n8n CDC job from <code>logistics_department.fuel_records</code> — <strong>1,922 rows</strong> (Feb–Jun 2026)
|
||
of <em>actual</em> fills (litres, KES amount, odometer, fuel type, driver, department), keyed by number plate.</p>
|
||
<p><strong>Goal (full vertical):</strong> a new <code>17_fleetfuel</code> module pulls the bucket into the shared
|
||
<code>tracksolid_db</code> under its own <code>fuel</code> schema, exposes it via <code>dashboard_api</code>, and adds a
|
||
<strong>new “Fuel Log” tab</strong> to FleetOps. The existing trip-derived panel stays as-is — the two coexist.
|
||
This mirrors the proven <code>16_fleettickets</code> module pattern exactly.</p>
|
||
<div class="note warn"><strong>Credential note:</strong> the <code>RUSTFS_*</code> keys were pasted in chat. They go
|
||
<strong>only</strong> in a gitignored <code>.env</code> (never committed), and the shared secret should be
|
||
<strong>rotated</strong> after this work, since plaintext-in-chat counts as exposed.</div>
|
||
|
||
<h2><span class="num">·</span>The data (confirmed by reading the bucket)</h2>
|
||
<p>Bucket layout (<code>s3.rahamafresh.com</code>, path-style, region <code>us-east-1</code>):</p>
|
||
<ul>
|
||
<li><code>fuel_records/latest.json</code> — full snapshot, envelope <code>{ metadata, records[] }</code>, ~1.7 MB / 1922 rows.</li>
|
||
<li><code>fuel_records/changes/<ISO-ts>.json</code> — hourly CDC deltas (same envelope, includes soft-deletes).</li>
|
||
<li><code>.csv</code> siblings exist, but we ingest the <strong>JSON</strong> (richer, typed).</li>
|
||
</ul>
|
||
<p>Record shape (stable PK <code>id</code>):</p>
|
||
<pre><code>id, record_datetime, department, driver, car, liters, amount, fuel_type, odometer,
|
||
sender_name, sender_phone, raw_message, source, source_instance, source_message_id,
|
||
source_event_timestamp, message_fingerprint, deleted_at, deleted_by, delete_reason,
|
||
delete_source, created_at</code></pre>
|
||
<p>The data is <strong>messy</strong> (WhatsApp-sourced) → normalization is essential:</p>
|
||
<ul>
|
||
<li><code>car</code>: <code>KCA 542Q</code> vs <code>KCA542Q</code>, plus junk (<code>ANY VEH</code>). 162 distinct.</li>
|
||
<li><code>fuel_type</code>: <code>PETROL/DIESEL</code> + typos (<code>DISIEL</code>, <code>DISEL</code>, <code>PETRO</code>, <code>/PETROL</code>, <code>VPOWER</code>, null).</li>
|
||
<li><code>department</code>: ~30 case/spelling variants of ~12 real departments (<code>OSP/osp/Osp</code>, <code>ROLL-OUT/ROLLOUT</code>).</li>
|
||
<li><code>deleted_at</code> set on 34 rows (soft-deleted — must be excluded from reporting).</li>
|
||
</ul>
|
||
|
||
<h2><span class="num">·</span>Pattern mirrored: <code>16_fleettickets</code></h2>
|
||
<p>Self-contained Python module → reads a RustFS bucket → upserts raw-jsonb rows into a namespaced schema in the
|
||
shared <code>tracksolid_db</code> → idempotent migrations with a <code>schema_migrations</code> ledger → a
|
||
<code>reporting.*</code> view consumed by <code>dashboard_api</code> → surfaced as a FleetOps tab. Reuse:
|
||
<code>shared.py</code> (DB ctx-mgr + <code>clean</code>), <code>run_migrations.py</code> (ledger runner), the
|
||
dry-run/<code>--apply</code> CLI convention, and the <code>.env</code>/<code>pyproject</code>/<code>README</code> layout.</p>
|
||
|
||
<h2><span class="num">A</span>Ingestion repo — <code>17_fleetfuel</code> <span class="tag new">new</span></h2>
|
||
<p>Created in <code>/Users/kianiadee/Downloads/projects/17_fleetfuel</code>, files mirroring fleettickets:</p>
|
||
<ul>
|
||
<li><strong><code>pyproject.toml</code></strong> — <code>psycopg2-binary</code>, <code>boto3</code> (the <code>aws</code> CLI isn’t available, and the CDC <code>changes/</code> listing needs <code>list_objects_v2</code> pagination, so boto3 beats CLI-subprocess). ruff dev dep.</li>
|
||
<li><strong><code>shared.py</code></strong> — copy verbatim (<code>get_conn</code>, <code>get_logger</code>, <code>clean</code>); rename logger ns to <code>fleetfuel</code>.</li>
|
||
<li><strong><code>run_migrations.py</code></strong> — copy; swap ledger to <code>fuel.schema_migrations</code>.</li>
|
||
<li><strong><code>.env.example</code></strong> — <code>DATABASE_URL</code>, <code>RUSTFS_ENDPOINT/ACCESS_KEY/SECRET_KEY/REGION</code>, <code>FUEL_BUCKET=fuel</code>.</li>
|
||
<li><strong><code>.gitignore</code></strong> — <code>.env</code>, <code>__pycache__</code>, <code>.venv</code>.</li>
|
||
<li><strong><code>README.md</code></strong> — what it owns vs. not (DB schema = ours; read-API = dashboard_api; frontend = fleetops).</li>
|
||
<li><strong><code>migrations/01_fuel_schema.sql</code></strong> — see Part B.</li>
|
||
<li><strong><code>import_fuel.py</code></strong> — the loader (below).</li>
|
||
<li><strong><code>s3util.py</code></strong> (optional) — thin boto3 client factory (<code>endpoint_url</code> + path-style addressing).</li>
|
||
</ul>
|
||
<h3><code>import_fuel.py</code></h3>
|
||
<ul>
|
||
<li>boto3 S3 client from <code>RUSTFS_*</code> env.</li>
|
||
<li><strong>Default <code>--snapshot</code></strong>: GET <code>fuel_records/latest.json</code>, upsert all <code>records</code> on <code>id</code>. At 1922 rows / hourly cadence this full reconcile is trivial and <strong>self-healing</strong> (picks up edits + soft-deletes) → simplest correct design.</li>
|
||
<li><strong><code>--changes</code></strong> (optional, lower-latency): list <code>fuel_records/changes/</code>, process files newer than a watermark in <code>fuel.ingest_state</code>.</li>
|
||
<li><strong><code>--file <path></code></strong>: local JSON for dev/testing.</li>
|
||
<li><strong><code>--apply</code></strong> writes; default is a dry-run logging parsed/valid/skipped counts.</li>
|
||
<li>Upsert via <code>execute_values</code>: <code>INSERT … ON CONFLICT (id) DO UPDATE SET raw=EXCLUDED.raw, updated_at=now()</code>. Derived/normalized columns populated by a DB trigger reading <code>raw</code>. Scrub JSON <code>NaN</code> → null first.</li>
|
||
</ul>
|
||
|
||
<h2><span class="num">B</span><code>migrations/01_fuel_schema.sql</code> — the <code>fuel</code> schema <span class="tag new">new</span></h2>
|
||
<p>Idempotent, lives in shared <code>tracksolid_db</code>:</p>
|
||
<ul>
|
||
<li><code>CREATE SCHEMA IF NOT EXISTS fuel;</code> + <code>reporting;</code></li>
|
||
<li><strong>Normalizer functions</strong> (IMMUTABLE, single source of truth):
|
||
<ul>
|
||
<li><code>fuel.norm_plate(text)</code> → upper, strip non-alphanumeric (<code>KCA 542Q</code>→<code>KCA542Q</code>); null out junk.</li>
|
||
<li><code>fuel.canon_fuel_type(text)</code> → map typos to <code>PETROL / DIESEL / VPOWER / OTHER / NULL</code>.</li>
|
||
<li><code>fuel.canon_department(text)</code> → upper + collapse-ws + variant map to canonical set.</li>
|
||
</ul>
|
||
</li>
|
||
<li><strong><code>fuel.records</code></strong> — raw-first + derived columns populated by a <code>BEFORE INSERT/UPDATE</code> trigger from <code>raw</code>: <code>id, raw, record_datetime, plate, car_raw, liters, amount, fuel_type, department, driver, odometer, deleted_at, message_fingerprint, ingested_at, updated_at</code>. Indexes on <code>plate</code>, <code>record_datetime</code>, <code>department</code>, partial <code>WHERE deleted_at IS NULL</code>.</li>
|
||
<li><strong><code>fuel.ingest_state</code></strong> — watermark for <code>--changes</code> mode.</li>
|
||
<li><strong><code>reporting.v_fuel_fills</code></strong> — read view: <code>fuel.records</code> (<code>deleted_at IS NULL</code>) LEFT JOIN <code>tracksolid.devices d ON fuel.norm_plate(d.vehicle_number) = r.plate</code>, exposing <code>fuel_date, plate, vehicle_number, cost_centre, assigned_city, imei, department, driver, liters, amount, fuel_type, odometer</code>. Same filter contract as <code>reporting.v_daily_summary</code>.</li>
|
||
<li><strong><code>reporting.v_fuel_efficiency</code></strong> (optional, high-value) — per-<code>plate</code> window over <code>record_datetime</code>: <code>km = odometer − lag(odometer)</code>, <code>km_per_litre = km / liters</code>, with defensive bounds.</li>
|
||
<li><strong>Grants</strong> — <code>USAGE</code> + <code>SELECT</code> on the views to <code>dashboard_ro</code> (mirror tracksolid migration 18).</li>
|
||
</ul>
|
||
|
||
<h2><span class="num">C</span><code>dashboard_api</code> read endpoints <span class="tag edit">edit</span></h2>
|
||
<p>In <code>dashboard_api_rev.py</code>, add endpoints reusing <code>_analytics_window</code> + <code>_dim_filters</code> + <code>RealDictCursor</code>:</p>
|
||
<ul>
|
||
<li><strong><code>GET /analytics/fuel-fills</code></strong> — <code>period/start/end</code> + dims + optional <code>department</code>, <code>fuel_type</code>. Returns: <code>totals</code> (litres, spend_kes, fills, avg_price_per_litre, vehicles_fuelled), <code>rows</code> (per-vehicle), <code>by_department</code>, <code>trend</code> (daily litres + spend), <code>data_status</code> (unmatched-plate count).</li>
|
||
<li><strong><code>GET /analytics/fuel-fills/recent</code></strong> — recent N fills for the detail table.</li>
|
||
<li><strong>Extend <code>GET /analytics/filters</code></strong> to also return <code>departments</code> and <code>fuel_types</code>.</li>
|
||
</ul>
|
||
<p>No business logic in the API — it only selects from the <code>reporting.*</code> views.</p>
|
||
|
||
<h2><span class="num">D</span>FleetOps SPA — new “Fuel Log” tab <span class="tag edit">edit</span></h2>
|
||
<p>In <code>15_fleetops/src/index.html</code> (single-file SPA, inline JS + Chart.js). Leave the existing fuel panel untouched; add a “Fuel Log” tab:</p>
|
||
<ul>
|
||
<li>KPI strip: total litres, total KES spend, fills, avg KES/litre, vehicles fuelled.</li>
|
||
<li>Trend chart (spend + litres) — reuse the utilisation panel’s Chart.js setup.</li>
|
||
<li>Per-vehicle table (litres, spend, fills, last odometer, km/l) + by-department breakdown.</li>
|
||
<li>Recent-fills detail table from <code>/analytics/fuel-fills/recent</code>.</li>
|
||
<li>Wire to the shared filter state + new department / fuel-type dropdowns; calls go through the existing <code>API_BASE</code> mechanism.</li>
|
||
</ul>
|
||
|
||
<h2><span class="num">E</span>Git & deploy</h2>
|
||
<ul>
|
||
<li><code>git init</code> in <code>17_fleetfuel</code>, add <code>repo.rahamafresh.com/kianiadee/fleetfuel.git</code> as origin, push (repo is currently empty).</li>
|
||
<li>Deploy like fleettickets: a Coolify container/cron in the stack runs <code>run_migrations.py</code> then <code>import_fuel.py --snapshot --apply</code> hourly (matching the CDC cadence).</li>
|
||
<li><code>dashboard_api</code> + <code>fleetops</code> ride their existing Coolify pipelines (feature → <code>staging</code> → <code>main</code>).</li>
|
||
</ul>
|
||
|
||
<h2><span class="num">·</span>Critical files</h2>
|
||
<table>
|
||
<thead><tr><th>File</th><th>Action</th></tr></thead>
|
||
<tbody>
|
||
<tr><td><code>17_fleetfuel/import_fuel.py</code>, <code>shared.py</code>, <code>run_migrations.py</code>, <code>pyproject.toml</code>, <code>.env.example</code>, <code>README.md</code></td><td><span class="tag new">new</span> mirror <code>16_fleettickets/*</code></td></tr>
|
||
<tr><td><code>17_fleetfuel/migrations/01_fuel_schema.sql</code></td><td><span class="tag new">new</span> <code>fuel</code> schema + normalizers + <code>reporting.v_fuel_fills</code></td></tr>
|
||
<tr><td><code>tracksolid_timescale_grafana_prod/dashboard_api_rev.py</code></td><td><span class="tag edit">edit</span> add <code>/analytics/fuel-fills[/recent]</code>, extend <code>/analytics/filters</code></td></tr>
|
||
<tr><td><code>15_fleetops/src/index.html</code></td><td><span class="tag edit">edit</span> add “Fuel Log” tab</td></tr>
|
||
</tbody>
|
||
</table>
|
||
<p class="pill">Reuse: <code>16_fleettickets/shared.py</code>, <code>run_migrations.py</code>, the <code>_scrub_nan</code>/<code>upsert</code> shape; <code>dashboard_api_rev.py:444</code> <code>_dim_filters</code>, <code>_analytics_window</code>; the stdlib SigV4 reader already proven this session (fallback if boto3 is undesirable).</p>
|
||
|
||
<h2><span class="num">✓</span>Verification (end-to-end)</h2>
|
||
<ol>
|
||
<li><strong>Bucket read</strong> — already proven this session (listed 14 objects, parsed <code>latest.json</code> = 1922 rows).</li>
|
||
<li><strong>Ingestion dry-run</strong> — <code>python import_fuel.py --snapshot</code> (no <code>--apply</code>): logs parsed/valid/skipped, no DB writes.</li>
|
||
<li><strong>Migrate + apply</strong> — <code>python run_migrations.py</code> then <code>import_fuel.py --snapshot --apply</code>. Spot-check: <code>SELECT count(*), count(*) FILTER (WHERE deleted_at IS NULL) FROM fuel.records;</code> (≈1922 / ≈1888) and plate-match rate in <code>reporting.v_fuel_fills</code>.</li>
|
||
<li><strong>API</strong> — <code>curl "$API/analytics/fuel-fills?period=90d"</code> → totals/rows/by_department/trend non-empty; <code>/analytics/filters</code> includes <code>departments</code>.</li>
|
||
<li><strong>Frontend</strong> — build & run fleetops locally, open the Fuel Log tab, confirm KPIs/chart/tables render and filters drive the queries.</li>
|
||
<li><strong><code>/verify</code></strong> the fleetops change once wired.</li>
|
||
</ol>
|
||
|
||
<footer>FleetFuel implementation plan · generated 2026-06-11 · sibling of FleetOps / FleetTickets</footer>
|
||
|
||
</div>
|
||
</body>
|
||
</html>
|