Deployment & Architecture · 17_fleetfuel

FleetFuel — How it runs

From the RustFS fuel bucket to the FleetOps Fuel Log tab — the data flow, where each piece runs on the Coolify VPS, and the runbook to deploy it.

See also: plan.html (implementation plan) · host: twala.rahamafresh.com

Status (2026-06-12): ingestion is live in the prod DB — migrations 01–03 applied, ~1,900 rows ingested, reporting.v_fuel_fills = 1,888 rows / 1,775 matched (94%). Read-side is pushed, awaiting promotion: FleetOps tab on branch staging (auto-deploys staging); dashboard_api on feat/staging-fleetops-architecture (PR #17) — promote to staging then main to light up the API.

1Solution flow

Five stages. The WhatsApp fuel feed is exported to object storage by n8n; FleetFuel pulls it, normalizes and stores it, the API reads it back, and the SPA renders it.

owned by fleetfuel / data-plane read-side (existing repos, edited) data flow

The middle three stages all live in the same shared tracksolid_db. FleetFuel only owns the fuel schema + the two reporting.* views; the API and SPA are existing repos that gained a fuel surface. This is the same module shape as fleettickets.

2Where it runs (Coolify VPS)

Host twala.rahamafresh.com runs everything under Coolify. The tracksolid stack (Coolify app bo3nov2ija7g8wn9b1g2paxs) and the RustFS server are co-located:

Container	Role
`timescale_db-…`	Postgres 16 + TimescaleDB — the shared `tracksolid_db` (target of the migration + ingest)
`dashboard_api-…` / `dashboard_api_staging`	FastAPI read-API (`fleetapi.rahamafresh.com` / staging)
`ingest_worker-…`, `webhook_receiver-…`	Tracksolid telematics ingestion (poll + push)
`db_backup-…`	Nightly DB dump → RustFS (already holds `RUSTFS_*` env)
`rustfs-…`	The S3-compatible object store serving the `fuel` bucket
`forgejo-runner`	CI runner for `repo.rahamafresh.com` (auto-deploys on push)

FleetFuel deploys as a new Coolify resource in the same project so it shares the internal Docker network and can reach timescale_db:5432 and the RustFS endpoint.

3Deployment runbook

3a · First-time: schema + backfill

One-off, to create the fuel schema and load the full history.

# on the VPS, in the fleetfuel checkout (pulled from repo.rahamafresh.com)
uv sync                              # installs psycopg2-binary + boto3
cp .env.example .env && $EDITOR .env  # DATABASE_URL + RUSTFS_* (see §4)

python run_migrations.py             # creates fuel.* + reporting.v_fuel_fills/efficiency (idempotent)
python import_fuel.py --snapshot     # DRY-RUN: parse + log counts, writes nothing
python import_fuel.py --snapshot --apply   # full reconcile from fuel_records/latest.json

run_migrations.py applies the whole set in order (ledger fuel.schema_migrations): 01 base schema · 02 one-device-per-fill join fix (a plate can map to several devices rows) · 03 standardize all fuel timestamps to Africa/Nairobi (EAT) so record_datetime::date buckets by the Kenyan day. All idempotent.

3b · Recurring: the ingest cron

A scheduled container that keeps the DB current. The --snapshot full-reconcile is idempotent and self-healing (picks up edits + soft-deletes), so a simple hourly run is safe:

# hourly, matching the n8n export cadence
python run_migrations.py && python import_fuel.py --snapshot --apply

Lower-latency alternative: import_fuel.py --changes --apply processes only new fuel_records/changes/*.json since the watermark in fuel.ingest_state.

3c · Read-side: API + SPA edit

Commit + push the dashboard_api_rev.py change (new /analytics/fuel-fills endpoints) on the tracksolid repo → Coolify auto-deploys via the Forgejo webhook.
Commit + push the 15_fleetops/src/index.html change (Fuel Log tab) → Coolify rebuilds the SPA image.
Promotion path per repo: feature → staging → main.

Order matters: the migration (3a) must run before the API deploy hits /analytics/fuel-fills. The extended /analytics/filters is savepoint-guarded, so deploying the API before the migration won’t break the existing Logistics dropdowns — the fuel dropdowns just stay empty until the view exists.

4Configuration

Env var	Value / source
`DATABASE_URL`	`postgresql://tracksolid_owner:<pw>@timescale_db:5432/tracksolid_db` (internal Docker host)
`RUSTFS_ENDPOINT`	`https://s3.rahamafresh.com`
`RUSTFS_ACCESS_KEY` / `RUSTFS_SECRET_KEY`	RustFS keypair (same store the `db_backup` service uses)
`RUSTFS_REGION`	`us-east-1`
`FUEL_BUCKET`	`fuel`

Secret hygiene: the real .env is gitignored and never committed. The RustFS key used during development was shared in plaintext and should be rotated.

5Verification

Schema: \dt fuel.* shows fuel.records / fuel.ingest_state; \dv reporting.v_fuel_* shows the two views.
Data: SELECT count(*), count(*) FILTER (WHERE deleted_at IS NULL) FROM fuel.records; (≈1922 / ≈1888 at first load).
Join health: SELECT count(*) FILTER (WHERE vehicle_number IS NOT NULL) FROM reporting.v_fuel_fills; — the plate-match rate against tracksolid.devices.
API: curl "https://fleetapi.rahamafresh.com/analytics/fuel-fills?period=90d" → totals / rows / by_department / trend populated; /analytics/filters includes departments.
SPA: open FleetOps → Fuel Log tab → KPI strip, spend/litres chart, per-vehicle & recent tables render.

6Rollback

Read-side: revert the two commits (dashboard_api + fleetops); Coolify redeploys the prior image.
Data: the fuel schema is additive and isolated — nothing else reads it, so it can be left in place or dropped (DROP SCHEMA fuel CASCADE; + DROP VIEW reporting.v_fuel_fills, reporting.v_fuel_efficiency;) with no impact on the existing Logistics/Tickets surfaces.
Cron: pause/stop the FleetFuel Coolify resource — ingestion halts, existing rows remain.