Project Overview
Last updated: 25 February 2026
What This Is
An automated business intelligence system for Fairview Vehicles (FV), a commercial vehicle hire business with multiple UK branches. The system eliminates manual reporting by:
- Downloading booking data from SharePoint (Excel spreadsheet) via Microsoft Graph API
- Computing analytics across 11 analytical sections (including merged Surprice franchise data)
- Serving a live web dashboard at
https://metrics.fvapp.co - Generating a formatted Excel workbook (8 sheets) and HTML email
- Sending the email with the Excel attached via Resend API
- Running automatically on a DigitalOcean droplet via cron (5 runs daily)
Business Context
Fairview Vehicles operates from multiple UK branches hiring out commercial vehicles (vans: SWB, LWB, XLWB, Lutons, Tippers, Dropsides). Bookings come through several channels — direct, and via brokers (RC, MP, and other brokers).
The source data is a single Excel spreadsheet (Daily Hire.xlsx) maintained on SharePoint, with 36 columns per booking covering dates, customer info, vehicle details, and 8 revenue streams.
System Components
| Component | Technology | Purpose |
|---|---|---|
| Reporting Agent | Python 3.9+ | Data pipeline, analytics, Excel/email generation |
| Web Dashboard | FastAPI + Jinja2 + Tailwind + Chart.js | Live interactive dashboard |
| Data Source | SharePoint (Excel) via Graph API | Booking data |
| Email Delivery | Resend API ([email protected]) | Daily report distribution |
| Infrastructure | DigitalOcean droplet, nginx, sslh, systemd | Hosting and automation |
| Auth | JWT + SQLite + bcrypt | User management |
Dashboard Sections (11)
- Snapshot — Pace indicator, today's bookings/revenue by branch, vehicles on-hire, deposits, alerts
- Forward Activity — Tomorrow's pickups/returns by branch, revenue pipeline, unpaid 7-day alert
- MTD by Branch — Bookings, revenue, paid/total days, μ£/booking, MoM + YoY comparison
- YTD by Branch — Bookings, revenue, paid/total days, μ vehicles OH, μ£/paid day, YoY
- Revenue Trend — MTD daily line chart, YTD weekly bars, KPIs (best/worst day, streak)
- Forward Bookings — 7/30/90 day pipeline by vehicle group (count + revenue), DH + Surprice merged with tooltips
- Forward Bookings Utilisation — 28-day heatmap: booked vs available per group, colour-coded %, overbooking alerts, weekend/BH shading
- Booking Buildup — Hire days by usage month attributed to booking month, 12-month forward heatmap with "12+ months prior" bucket
- Revenue Composition — 8 revenue streams, MTD + YTD
- Booking Channels — RC, MP, Other Broker, Direct: bookings, revenue, μ£/booking, MTD + YTD
- Fleet Utilisation — Unique vehicles, paid/total days, μ days/vehicle, top 10, idle list
Surprice Franchise Module
A parallel pipeline processes bookings from the Surprice franchise (a separate worksheet in the same Excel workbook). Surprice data is:
- Merged into the DH Forward Bookings section (combined totals with per-source tooltips)
- Merged into the Forward Bookings Utilisation heatmap
- Served as a standalone Surprice dashboard at
/surpricewith 7 sections: Snapshot, MTD, YTD, Brokers, Vehicle Groups, Forward Bookings, Revenue Trend
Operational Model
| Time | Action |
|---|---|
| 8 AM, 12 PM, 3 PM, 6 PM | Download from SharePoint + dashboard JSON update (no email) |
| 11 PM | Full run: download + dashboard + email + Excel + archive to SharePoint |
Admin users can also trigger a manual refresh from the dashboard header.
Key Operational Features
- Archive System: Each 11 PM run archives a timestamped copy (
Daily_Hire_YYYYMMDD_HHMMSS.xlsx) to SharePoint. Auto-cleanup deletes archives older than 7 days. - Admin Refresh: Header button (admin only) triggers
run_daily_hire.py --dashboardas subprocess, polls status every 3 seconds, auto-reloads on completion. - Atomic Writes: Dashboard JSON files use temp-file-then-rename to prevent serving partial data.
- Comprehensive Logging: Every run produces timestamped log files in the agent's logs directory.
Label Convention
All averages use μ (mu) notation throughout: μ£/Booking, μ£/Day, μ£/Paid Day, μ Days/Booking, μ Vehicles OH.
Architecture & File Structure
Droplet File Layout
Droplet: 138.68.143.29 (DigitalOcean, Ubuntu 24.04, LON1 region)
/opt/fv-agents/
├── daily_hire_agent/
│ ├── config.py # Settings, column mapping, location mapping, channels
│ ├── data_loader.py # Excel → pandas DataFrame pipeline + load_vehicle_counts()
│ ├── email_builder.py # HTML email generation
│ ├── email_sender.py # Resend API (sender: [email protected])
│ ├── excel_builder.py # openpyxl workbook generation (8 sheets)
│ ├── run_daily_hire.py # Runner: download, archive, pipeline, output
│ ├── sections/
│ │ ├── daily_snapshot.py # Pace, new bookings, tomorrow activity, pipeline, unpaid
│ │ ├── mtd_branch.py # MTD by branch with MoM + YoY
│ │ ├── ytd_branch.py # YTD by branch with YoY
│ │ ├── vehicle_groups.py # Performance by vehicle group
│ │ ├── revenue_composition.py # 8 revenue streams breakdown
│ │ ├── booking_channels.py # RC, MP, Other Broker, Direct
│ │ ├── fleet_utilisation.py # Fleet usage, idle vehicles, top performers
│ │ ├── forward_bookings.py # Forward pipeline (7/30/90d) + utilisation heatmap
│ │ ├── booking_buildup.py # Booking buildup heatmap (hire days by usage/booking month)
│ │ └── revenue_trend.py # Daily/weekly revenue trend with comparisons
│ ├── surprice/
│ │ ├── surprice_config.py # Surprice column mapping, location mapping, sheet config
│ │ ├── surprice_loader.py # Surprice worksheet → DataFrame + VC name lookup
│ │ ├── surprice_exporter.py # Surprice section dicts → JSON files
│ │ └── surprice_sections/
│ │ ├── snapshot.py # Surprice daily snapshot (pace, on-hire, tomorrow)
│ │ ├── mtd_branch.py # Surprice MTD by branch
│ │ ├── ytd_branch.py # Surprice YTD by branch
│ │ ├── brokers.py # Surprice broker performance
│ │ ├── vehicle_groups.py # Surprice group performance (FV codes)
│ │ ├── forward.py # Surprice forward bookings (stacked bar segments)
│ │ └── trend.py # Surprice revenue trend
│ ├── dashboard/
│ │ ├── app.py # FastAPI — all routes including admin refresh
│ │ ├── auth.py # JWT authentication (24h tokens)
│ │ ├── config.py # Dashboard-specific config (env vars)
│ │ ├── user_db.py # SQLite user management (bcrypt, CRUD, roles)
│ │ ├── dashboard_exporter.py # Converts section dicts → JSON files (atomic writes)
│ │ ├── templates/
│ │ │ ├── login.html # Login page
│ │ │ ├── dashboard.html # Main dashboard (4-tab layout, 11 sections, Chart.js)
│ │ │ ├── admin.html # User management panel
│ │ │ ├── wiki.html # Code wiki (this page, admin only)
│ │ │ ├── user-wiki.html # User guide (all users)
│ │ │ └── surprice.html # Surprice dashboard
│ │ └── static/
│ ├── output/ # Generated Excel reports
│ └── logs/ # Per-run log files
├── onedrive_client/ # Shared Graph API client for SharePoint
├── data/
│ ├── daily_hire/ # Downloaded Excel file
│ └── dashboard/ # JSON files + users.db
├── logs/ # Cron-level logs
├── venv/ # Python virtual environment
├── run_scorecard.py # Courier Scorecard runner
└── run_market_intel.py # CV Market Research runner
Infrastructure Stack
| Component | Configuration |
|---|---|
| sslh | Listens on 0.0.0.0:443, multiplexes SSH + HTTPS traffic |
| SSH | 127.0.0.1:22 (accessed via sslh — port 22 blocked by local network) |
| nginx | Port 80 (→ HTTPS redirect), 127.0.0.1:8443 (SSL termination, via sslh) |
| uvicorn | 127.0.0.1:8050 (FastAPI app, nginx proxies to here) |
| SSL | Let's Encrypt via certbot, auto-renewal |
| systemd | fv-dashboard.service — auto-start on boot, security hardening |
| Domain | metrics.fvapp.co → droplet IP |
Traffic Flow
User → metrics.fvapp.co:443
→ sslh (port 443, detects HTTPS vs SSH)
→ nginx (127.0.0.1:8443, SSL termination)
→ uvicorn (127.0.0.1:8050, FastAPI)
Pipeline Architecture
1. SharePoint Download (Linux)
└─ Graph API → /opt/fv-agents/data/daily_hire/Daily Hire.xlsx
2. Archive (11 PM runs only)
└─ Upload timestamped copy to SharePoint archive folder
└─ Delete archives older than 7 days
3. Data Loading (data_loader.py)
└─ openpyxl read_only mode → raw rows
└─ Filter: pickup_date >= 2024
└─ Map locations → branches
└─ Determine channels from reference columns
└─ Type-cast all fields → pandas DataFrame
└─ Load vehicle counts from "Vehicle Counts" sheet
4. Section Computation (11 DH modules)
└─ Each returns a dict of computed metrics
5. Surprice Integration (dashboard mode only)
└─ Load Surprice worksheet → separate DataFrame
└─ Merge Surprice into forward bookings (DH + SP counts/revenue)
└─ Merge Surprice into utilisation heatmap (DH + SP active bookings)
└─ Run 7 Surprice-specific section modules
6. Output Generation
├─ Dashboard JSON (atomic writes to /opt/fv-agents/data/dashboard/)
│ ├─ DH sections (11 JSON files)
│ └─ Surprice sections (7 JSON files)
├─ Excel Workbook (8-sheet .xlsx)
└─ HTML Email (only with --send) → Resend API
Runner Script — Command-Line Arguments
| Flag | Description |
|---|---|
--send | Send email (default: dry run) |
--back N | Report date = N days ago |
--skip-download | Skip SharePoint download (Linux only, use cached file) |
--to EMAIL | Override recipient email |
--cron | Flag for cron-triggered runs |
--dashboard | Export dashboard JSON data |
Cron Schedule
# Full run: download + email + dashboard (11 PM UK)
0 23 * * * /opt/fv-agents/venv/bin/python /opt/fv-agents/daily_hire_agent/run_daily_hire.py --send --dashboard --cron
# Daytime refreshes: download + dashboard only (8 AM, 12 PM, 3 PM, 6 PM)
0 8,12,15,18 * * * /opt/fv-agents/venv/bin/python /opt/fv-agents/daily_hire_agent/run_daily_hire.py --dashboard --cron
# Scorecard Agent — Thursday 3 PM, Friday 12 PM backup
0 11 * * 4 /opt/fv-agents/venv/bin/python /opt/fv-agents/run_scorecard.py thursday
0 11 * * 5 /opt/fv-agents/venv/bin/python /opt/fv-agents/run_scorecard.py friday
# Market Intel Agent — Monday 3 PM
# (currently placeholder)
Technology Stack
| Layer | Technology |
|---|---|
| Language | Python 3.9+ |
| Web Framework | FastAPI + Jinja2 |
| Frontend | Vanilla JS + Tailwind CSS (CDN) + Chart.js |
| Data Processing | pandas, numpy |
| Excel | openpyxl |
| Bank Holidays | holidays (GB/ENG subdiv) |
| Authentication | PyJWT + bcrypt |
| User Storage | SQLite (custom user_db.py) |
| Resend API (HTTP, no SMTP) | |
| SharePoint | Microsoft Graph API (custom onedrive_client.py) |
| Web Server | nginx (reverse proxy + SSL) |
| App Server | uvicorn |
| Process Manager | systemd |
| Scheduler | cron |
| SSL | Let's Encrypt (certbot) |
| Protocol Mux | sslh (SSH + HTTPS on port 443) |
| Version Control | Git (GitHub, main branch) — local via GitHub Desktop |
Data Model & Business Logic
Source Data
File: Daily Hire.xlsx, sheet "Daily Hire"
Location: SharePoint site FleetStorage, path Fairview Hire Management/Daily Hire.xlsx
Format: 36 columns (A–AJ), one row per booking
Complete Column Specification
| Col | Letter | Field Name | Type | Description |
|---|---|---|---|---|
| 0 | A | booking_date | date | Date the booking was created |
| 1 | B | transaction_id | string | SumUp / Stripe transaction ID |
| 2 | C | mp_ref | string | MP broker reference |
| 3 | D | rc_ref | string | RC broker reference |
| 4 | E | broker_ref | string | Other broker reference |
| 5 | F | prohire_ref | string | ProHire platform reference |
| 6 | G | coastr_ref | string | Coastr platform reference |
| 7 | H | sage_inv | string | Sage invoice number |
| 8 | I | comments_1 | string | Comments field 1 |
| 9 | J | customer_name | string | Customer name |
| 10 | K | deposit_block | string | Deposit block |
| 11 | L | phone | string | Phone number |
| 12 | M | group | string | Vehicle group code (SWB, LWB, Luton, etc.) |
| 13 | N | registration | string | Vehicle registration plate |
| 14 | O | make_model | string | Make & Model |
| 15 | P | pickup_date | date | PRIMARY KEY — pick-up date (revenue attribution) |
| 16 | Q | pickup_time | time | Pick-up time |
| 17 | R | pickup_location | string | Raw pick-up location (mapped to branch) |
| 18 | S | dropoff_date | date | Drop-off date |
| 19 | T | dropoff_time | time | Drop-off time |
| 20 | U | dropoff_location | string | Drop-off location |
| 21 | V | rental_days | int | Rental days (only populated for paid bookings) |
| 22 | W | hire_charge | float | Hire charge incl VAT |
| 23 | X | insurance | float | Insurance |
| 24 | Y | additional_hours | float | Additional hours / days charge |
| 25 | Z | additional_rental | float | Additional rental collected @ FV |
| 26 | AA | additional_driver | float | Additional driver charge |
| 27 | AB | cdw_collected | float | CDW / Standard / Premium collected @ FV |
| 28 | AC | deposit_to_collect | float | Deposit to be collected @ FV |
| 29 | AD | damage_charge | float | Damage charge |
| 30 | AE | additional_charges | float | Additional charges |
| 31 | AF | paid_to_us | float | TOTAL REVENUE — Paid to us |
| 32 | AG | deposit_returned | date | Deposit returned date |
| 33 | AH | supplier_payment | float | Supplier payment received |
| 34 | AI | payment_platform | string | Payment platform (not currently used) |
| 35 | AJ | comments_2 | string | Comments field 2 |
Derived Fields
Computed by data_loader.py, not stored in the source spreadsheet:
| Field | Derivation | Purpose |
|---|---|---|
branch | Mapped from pickup_location via LOCATION_MAP | Simplified branch name |
channel | Determined from reference columns (RC → D, MP → C, Other Broker → E, else Direct) | Booking channel |
calc_rental_days | (dropoff_date - pickup_date).days.clip(lower=1) | Calculated duration for every booking |
is_paid | rental_days > 0 (Col V has a value) | Whether booking is paid |
Location → Branch Mapping
| Raw Location Pattern | Branch |
|---|---|
"final rentals heathrow" | Final Rentals Heathrow |
"final rentals battersea" | Final Rentals Battersea |
"fairview bray" | Bray |
"bray" | Bray |
"london battersea" | Battersea |
"battersea" | Battersea |
"barking" | Barking |
"kensington" | Kensington |
"heathrow" | Heathrow |
"egham" | Egham |
"milton keynes" | Milton Keynes |
Channel Detection
First populated reference field wins:
- Check col D (
rc_ref) → if populated → "RC" - Check col C (
mp_ref) → if populated → "MP" - Check col E (
broker_ref) → if populated → "Other Broker" - Default → "Direct"
Revenue Streams (8)
| Stream | Source Column |
|---|---|
| Hire Charge (incl VAT) | hire_charge (W) |
| Insurance | insurance (X) |
| Additional Hours/Days | additional_hours (Y) |
| Additional Rental | additional_rental (Z) |
| Additional Driver | additional_driver (AA) |
| CDW / Standard / Premium | cdw_collected (AB) |
| Damage Charges | damage_charge (AD) |
| Additional Charges | additional_charges (AE) |
Total revenue = paid_to_us (Col AF), which should equal the sum of these 8 streams.
Key Business Logic Decisions
| Decision | Detail |
|---|---|
| Paid vs Total Rental Days | rental_days (Col V) = spreadsheet value, only for paid bookings. calc_rental_days = calculated from dates, every booking. is_paid = rental_days > 0. |
| Same-Day Hires | calc_rental_days clips at minimum 1 day. ~190 same-day bookings exist. |
| Deposit Filter | Outstanding deposits only shown for 2025+ bookings where pickup_date ≤ report_date. |
| Report Date Logic | Before 8 PM → report on yesterday. After 8 PM → report on today. Overridable via --back N. |
| Data Start Year | 2024 loaded for historical YoY comparisons. |
Data Loading Pipeline
1. Copy file to temp location (avoids OneDrive/Excel lock conflicts)
2. Read with openpyxl in read_only mode, skip header row
3. For each row:
a. Pad to 36 columns if short
b. Parse pickup_date — skip if None or year < 2024
c. Map pickup_location → branch
d. Determine channel from reference columns
e. Build record dict with type-cast values
4. Create DataFrame
5. Convert date columns to pandas datetime
6. Compute calc_rental_days and is_paid
7. Return (DataFrame, unknown_locations list)
Vehicle Counts Sheet
A second sheet in the same workbook provides fleet inventory data used for the utilisation heatmap.
Sheet: "Vehicle Counts" | Loaded by: data_loader.load_vehicle_counts()
| Col | Letter | Field | Type | Description |
|---|---|---|---|---|
| 8 | I | Group Name | string | Descriptive label (e.g., "COMPACT CROSSOVER AUTO") |
| 9 | J | Group ID | string | JOIN KEY — FV group code (e.g., DGAV, EDAV), matches DH Col M and Surprice Col Q |
| 10 | K | Total Vehicles | int | Number of vehicles available in this group |
Returns: dict mapping group_id → {"group_name": str, "total": int}. Returns empty dict if the sheet is missing.
Surprice Data Model
Sheet: "Surprice" | Loaded by: surprice_loader.load_surprice_data()
Format: 29 columns (A–AC), one row per Surprice booking
| Col | Letter | Field Name | Type | Description |
|---|---|---|---|---|
| 0 | A | booking_date | date | Booking confirmation date |
| 1 | B | coastr_ref | string | Coastr reference |
| 4 | E | broker | string | Broker name |
| 6 | G | customer_name | string | Customer |
| 9 | J | location | string | Location (may contain "/" separator) |
| 10 | K | pickup_date | date | Primary date — revenue attribution |
| 11 | L | return_date | date | Return date |
| 12 | M | rental_days | int | Number of days |
| 15 | P | sp_vehicle_group | string | Surprice's own group code (e.g., CA2, C2) |
| 16 | Q | fv_group_code | string | FV GROUP CODE — unified fleet code, join key to DH + Vehicle Counts |
| 17 | R | fv_group_category | string | Descriptive name (fallback if Q not in VC) |
| 18 | S | registration | string | Vehicle registration plate |
| 22 | W | hire_charge | float | Hire charges |
| 27 | AB | paid_to_us | float | TOTAL REVENUE |
Surprice Location Mapping
| Raw Location Pattern | Branch |
|---|---|
"surprice heathrow" | Surprice Heathrow |
"london battersea" | London Battersea |
"london kensington" | London Kensington |
Note: Surprice branches are kept separate from DH branches — they are not mapped to the same names.
Dashboard & API
Overview
URL: https://metrics.fvapp.co | Auth: JWT tokens (24h expiry) | Roles: admin (user management + refresh), viewer (dashboard only)
The dashboard serves pre-computed JSON data files generated by the reporting pipeline. It does not query the database or Excel file directly.
API Routes — Public
| Method | Path | Description |
|---|---|---|
GET | /health | Health check (has_data, user_count) |
GET | /login | Login page (HTML) |
API Routes — Authentication
| Method | Path | Auth | Description |
|---|---|---|---|
POST | /auth/login | None | Username/password → JWT token |
GET | /auth/check | Bearer | Validate token, return user info |
POST | /auth/change-password | Bearer | Change own password |
API Routes — Dashboard
| Method | Path | Auth | Description |
|---|---|---|---|
GET | / | Bearer | Dashboard page (HTML) |
GET | /api/meta | Bearer | Report metadata |
GET | /api/data/{section} | Bearer | Section data as JSON |
Valid sections: snapshot, mtd, ytd, groups, revenue, channels, fleet, forward, trend, utilisation, buildup
API Routes — Pages (Auth Required)
| Method | Path | Access | Description |
|---|---|---|---|
GET | /guide | All users | User wiki / dashboard guide |
GET | /wiki | Admin only | Code wiki (this page) |
GET | /surprice | All users | Surprice franchise dashboard |
API Routes — Admin
| Method | Path | Description |
|---|---|---|
GET | /admin | Admin panel page (HTML) |
GET | /api/admin/users | List all users |
POST | /api/admin/users | Create user |
POST | /api/admin/users/{username}/reset-password | Reset password |
POST | /api/admin/users/{username}/toggle-active | Toggle active/inactive |
POST | /api/admin/users/{username}/role | Change role |
POST | /api/admin/refresh | Trigger pipeline refresh |
GET | /api/admin/refresh-status | Check refresh status |
Safety Guards
- Cannot deactivate your own account
- Cannot deactivate the last admin
- Cannot demote the last admin
- Cannot remove your own admin role
- Password minimum 6 characters
- Refresh blocks concurrent runs (409 if already running)
Authentication System
JWT Tokens: Signed with FV_DASH_SECRET env var. 24-hour expiry. Payload: sub (username), role, name (display_name), exp.
User DB: SQLite at /opt/fv-agents/data/dashboard/users.db. Passwords hashed with bcrypt.
Dashboard JSON Files
Generated by dashboard_exporter.py with atomic writes (temp file + rename).
| Source Type | JSON Type |
|---|---|
| pandas DataFrame | List of dicts |
| numpy int64/float64 | Python int/float |
| datetime.date / pd.Timestamp | ISO string |
| NaN / Inf | null |
| pd.NaT | null |
Email Output
| Setting | Value |
|---|---|
| API | Resend (HTTP POST) |
| From | FV Daily Hire Dashboard <[email protected]> |
| To | [email protected] (configurable) |
| Domain | fvapp.co (verified with Resend) |
Email Sections
- Pace Indicator — Today's pickup revenue vs MTD μ£/Day
- Today's Snapshot — New bookings, revenue, vehicles on hire, deposits
- New Bookings by Branch
- Tomorrow's Activity — Pickups, Returns, Net
- Revenue Pipeline — Tomorrow's pickups by branch
- Unpaid Bookings — 7 days (conditional)
- Alerts — Data quality issues
- MTD Summary + MTD by Branch
- YTD Summary
- Fleet — Unique vehicles, idle count
- Forward Bookings — Top 5 by count, top 5 by revenue
Excel Attachment (8 Sheets)
| Sheet | Content |
|---|---|
| Daily Snapshot | Full snapshot with pace, bookings, tomorrow, pipeline, unpaid, on-hire, deposits, alerts |
| MTD by Branch | Branch metrics + vs Prior Month + vs Same Month LY |
| YTD by Branch | Branch metrics + vs Prior Year |
| Vehicle Groups | Group performance + prior year comparison |
| Revenue Composition | 8 revenue streams, MTD + YTD |
| Booking Channels | RC, MP, Other Broker, Direct: MTD + YTD |
| Fleet Utilisation | Unique vehicles, top 10, idle list, μ days/vehicle |
| Forward Bookings | 7/30/90 day pipeline by group (count + revenue) |
Operations & Deployment
Droplet Details
| Property | Value |
|---|---|
| Provider | DigitalOcean |
| IP | 138.68.143.29 |
| OS | Ubuntu 24.04 |
| Region | LON1 |
| SSH | ssh -p 443 [email protected] |
Deployment Process
All code changes flow through GitHub as the single source of truth. The droplet and local Mac both sync through it.
Droplet ←→ GitHub (main) ←→ Mac (via GitHub Desktop)
Standard Deployment (New Features / Updates)
# 1. Place updated files in local repo
# /Users/mpatel/Documents/GitHub/daily_hire_agent/
# 2. In GitHub Desktop: review changes, write commit message, Push to origin
# 3. SSH into droplet and pull
ssh -p 443 [email protected]
cd /opt/fv-agents/daily_hire_agent
git pull origin main
# 4. Restart dashboard service (if .py or template files changed)
systemctl restart fv-dashboard
Hotfix Deployment (Direct on Droplet)
# 1. SSH in and make the edit
ssh -p 443 [email protected]
cd /opt/fv-agents/daily_hire_agent
nano <file>
# 2. Commit and push from droplet
git add .
git commit -m "hotfix: description of change"
git push origin main
# 3. Restart if needed
systemctl restart fv-dashboard
# 4. Sync locally: GitHub Desktop → Fetch origin → Pull origin
Common Droplet Commands
# Check cron logs
tail -50 /opt/fv-agents/logs/cron_daily_hire.log
# Check latest agent log
ls -lt /opt/fv-agents/daily_hire_agent/logs/ | head -5
# Manual runs
cd /opt/fv-agents/daily_hire_agent
/opt/fv-agents/venv/bin/python run_daily_hire.py # Dry run
/opt/fv-agents/venv/bin/python run_daily_hire.py --send # Live send
/opt/fv-agents/venv/bin/python run_daily_hire.py --back 3 --send # 3 days ago
/opt/fv-agents/venv/bin/python run_daily_hire.py --dashboard # JSON only
# Service management
systemctl status fv-dashboard
systemctl restart fv-dashboard
journalctl -u fv-dashboard -n 50
# Git operations
cd /opt/fv-agents/daily_hire_agent
git pull origin main
git status
git log --oneline -5
# Troubleshooting
timedatectl # Check timezone
df -h # Disk space
certbot certificates # SSL check
Storage Rules
| What | Where |
|---|---|
| Source data | /opt/fv-agents/data/daily_hire/ (overwritten each run) |
| Dashboard JSON | /opt/fv-agents/data/dashboard/ (overwritten each run) |
| User DB | /opt/fv-agents/data/dashboard/users.db (persistent) |
| Archives | SharePoint: DirectorsStorage / FV SDH Daily Hire Archive/ |
| Agent logs | /opt/fv-agents/daily_hire_agent/logs/ (accumulates) |
| Excel output | /opt/fv-agents/daily_hire_agent/output/ (overwritten) |
Key Dates & Credentials
| Item | Detail |
|---|---|
| Azure client secret expiry | February 2028 |
| Azure app | FV Agents OneDrive Access (Greythorn Services tenant) |
| JWT secret | Set in fv-dashboard.service as FV_DASH_SECRET |
| Resend API key | Set via RESEND_API_KEY env var |
| SSL certificates | Let's Encrypt, auto-renewal via certbot |
Email Configuration
| Setting | Value |
|---|---|
| Provider | Resend API (HTTP POST, no SMTP) |
| From | [email protected] |
| Default To | [email protected] |
| Subject | FV Daily Hire Report — DD Mon YYYY |
Code Reference
File Inventory
| File | Location | Lines | Purpose |
|---|---|---|---|
| Core Pipeline | |||
run_daily_hire.py | daily_hire_agent/ | ~450 | Runner: download, archive, DH pipeline, Surprice merge, output |
config.py | daily_hire_agent/ | ~208 | Settings, column mapping, location mapping, channels |
data_loader.py | daily_hire_agent/ | ~280 | Excel → pandas DataFrame pipeline + load_vehicle_counts() |
email_builder.py | daily_hire_agent/ | ~484 | HTML email generation |
email_sender.py | daily_hire_agent/ | ~123 | Resend API integration |
excel_builder.py | daily_hire_agent/ | ~1043 | openpyxl workbook (8 sheets) |
| DH Section Modules | |||
daily_snapshot.py | sections/ | ~253 | Pace, new bookings, tomorrow activity, pipeline, unpaid |
mtd_branch.py | sections/ | ~180 | MTD by branch with MoM + YoY |
ytd_branch.py | sections/ | ~170 | YTD by branch with YoY |
vehicle_groups.py | sections/ | ~150 | Group performance with prior year |
revenue_composition.py | sections/ | ~140 | 8 revenue streams, MTD/YTD |
booking_channels.py | sections/ | ~140 | Channel analysis, MTD/YTD |
fleet_utilisation.py | sections/ | ~160 | Fleet usage, idle list, top 10 |
forward_bookings.py | sections/ | ~300 | Forward pipeline (7/30/90d) + utilisation heatmap + DH/Surprice merge |
booking_buildup.py | sections/ | ~150 | Booking buildup heatmap — hire days by usage month / booking month |
revenue_trend.py | sections/ | ~175 | Daily/weekly revenue trend |
| Surprice Module | |||
surprice_config.py | surprice/ | ~80 | Column mapping (29 cols), location mapping, sheet config |
surprice_loader.py | surprice/ | ~150 | Surprice worksheet → DataFrame, VC name resolution |
surprice_exporter.py | surprice/ | ~120 | Surprice section dicts → JSON files (atomic writes) |
snapshot.py | surprice_sections/ | ~180 | Surprice daily snapshot (pace, on-hire, deposits, alerts) |
mtd_branch.py | surprice_sections/ | ~120 | Surprice MTD by branch |
ytd_branch.py | surprice_sections/ | ~120 | Surprice YTD by branch |
brokers.py | surprice_sections/ | ~100 | Surprice broker performance |
vehicle_groups.py | surprice_sections/ | ~130 | Surprice group performance (FV codes via VC lookup) |
forward.py | surprice_sections/ | ~120 | Surprice forward bookings (stacked bar segments) |
trend.py | surprice_sections/ | ~140 | Surprice revenue trend |
| Dashboard | |||
app.py | dashboard/ | ~380 | FastAPI — all routes, refresh logic, wiki/guide pages |
auth.py | dashboard/ | ~60 | JWT creation/verification |
config.py | dashboard/ | ~20 | Dashboard env var config |
user_db.py | dashboard/ | ~150 | SQLite user CRUD with bcrypt |
dashboard_exporter.py | dashboard/ | ~328 | Section dicts → JSON files (atomic writes) |
| Templates | |||
dashboard.html | templates/ | ~1700 | Main dashboard (11 sections in 4-tab layout with collapsible accordions, Chart.js, utilisation + buildup heatmaps) |
surprice.html | templates/ | ~700 | Surprice franchise dashboard (7 sections) |
login.html | templates/ | ~100 | Login page |
admin.html | templates/ | ~200 | Admin user management panel |
wiki.html | templates/ | — | Code wiki (this page, admin only) |
user-wiki.html | templates/ | — | User guide (all authenticated users) |
Section Module Pattern
Each section module follows the same pattern:
def compute_<section_name>(df: pd.DataFrame, report_date: date) -> dict:
"""Compute <section> metrics. Returns dict with all computed data."""
# Filter data
# Compute metrics
# Return structured dict
Section outputs consumed by:
dashboard_exporter.py→ JSON files (all 11 sections)excel_builder.py→ Excel workbook (7 sections — trend not in Excel)email_builder.py→ HTML email (snapshot, mtd, ytd, fleet, forward)
Key Function Signatures
# data_loader.py
load_data(filepath: Path) -> (pd.DataFrame, list)
get_data_summary(df: pd.DataFrame) -> dict
load_vehicle_counts(filepath: Path) -> dict # {group_id: {group_name, total}}
# forward_bookings.py
compute_forward_bookings(df, report_date, surprice_df=None) -> dict
compute_forward_utilisation(df, report_date, vehicle_counts, num_days=28,
surprice_df=None) -> dict
# booking_buildup.py
compute_booking_buildup(df, report_date) -> dict
# surprice_loader.py
load_surprice_data(filepath: Path) -> (pd.DataFrame, list)
# excel_builder.py
build_dashboard(snapshot_data, mtd_data, ytd_data, groups_data,
revenue_data, channels_data, fleet_data, output_path,
report_date=None, forward_data=None) -> Path
# email_builder.py
build_email_body(snapshot_data, mtd_data, ytd_data, fleet_data,
report_date, run_source="", forward_data=None,
utilisation_data=None) -> str
# email_sender.py
send_report(html_body, attachment_path, report_date,
recipient=None, dry_run=False) -> dict
# dashboard_exporter.py
export_dashboard_data(snapshot, mtd, ytd, groups, revenue, channels,
fleet, forward, report_date, output_dir,
run_source="manual", trend=None,
utilisation=None, buildup=None) -> Path
Forward Bookings Utilisation — Technical Detail
compute_forward_utilisation() generates a day-by-day grid for the next N days (28 for dashboard, 14 for email).
For each vehicle group on each day, it counts active bookings where pickup_date <= date AND dropoff_date >= date. When Surprice data is present, DH and Surprice counts are tracked separately (accessible via dh_booked and sp_booked keys) with the headline booked value being the combined total.
Weekend and bank holiday flags are computed using the holidays Python library (country=GB, subdiv=ENG). Each day's entry includes is_weekend and is_bank_holiday boolean flags used by the frontend for column shading.
Booking Buildup — Technical Detail
compute_booking_buildup() allocates hire days to calendar months using date-overlap calculation rather than day-by-day iteration. For each booking, the overlap with each usage month is computed as max(pickup, month_start) to min(dropoff, month_end), and the resulting day count is attributed to the month the booking was created.
Usage months span the current month + 11 forward (12 rows). Booking month columns run from 11 months before the first usage month through to the current month. Any booking created more than 12 months before its usage month is grouped into a single "earlier" bucket, shown as a distinct amber-tinted column.
The output JSON structure:
{
"usage_months": ["Feb 2026", "Mar 2026", ...], // 12 entries
"booking_months": ["Feb 2025", "Mar 2025", ...], // up to current month
"grid": {
"Feb 2026": {"earlier": 3, "Feb 2025": 0, "Mar 2025": 5, ...},
...
}
}
Number Formatting Conventions
| Format | Excel | Email/Dashboard |
|---|---|---|
| GBP | £#,##0.00 | £X,XXX.XX |
| Integers | #,##0 | X,XXX |
| Percentages | +X.X% / −X.X% with colour | |
| Averages | μ prefix (μ£/Booking, μ£/Day, etc.) | |
Change Indicators
- ▲ +X.X% — Positive (green)
- ▼ −X.X% — Negative (red)
- — 0.0% — Zero (grey)
Error Handling
- All exceptions logged with full traceback
- Non-fatal errors (archive, dashboard export) caught and logged but don't stop the pipeline
- Fatal errors →
sys.exit(1)
Build History & Future Work
Build Phases
| Phase | Work Completed |
|---|---|
| 1. Planning | Data analysis of 6,044 records, specification of 7 analytical sections, architecture decisions |
| 2. Phases 1–3 | Data pipeline, core metrics computation, analytics section modules |
| 3. Phases 4–5 | Excel builder (multi-sheet with explainers), email builder (HTML), Resend API integration |
| 4. Validation | Revenue attribution corrected to pickup_date. Deposit filter refinement (2025+ only) |
| 5. Dual Rental Days | Paid vs total rental day tracking. μ notation standardisation |
| 6. Snapshot Expansion | 5 new snapshot sub-sections: pace, new bookings, tomorrow's activity, revenue pipeline, unpaid alert |
| 7. Web Dashboard | FastAPI app, 8-section HTML dashboard with Tailwind + Chart.js, JSON export, droplet deployment |
| 8. Infrastructure | metrics.fvapp.co, HTTPS, sslh, multi-user JWT auth, admin panel |
| 9. Revenue Trend | Daily/weekly revenue charts, KPIs (best/worst day, streak, weekly average) |
| 10. Operations | Archive system, admin refresh button, 5 daily cron runs |
| 11. Surprice Module | Surprice worksheet loader, 7 Surprice-specific sections, standalone Surprice dashboard at /surprice |
| 12. Forward Utilisation | Vehicle Counts loader, 28-day utilisation heatmap, DH + Surprice merge, overbooking alerts, weekend/BH shading |
| 13. Documentation | User Wiki (/guide) and Code Wiki (/wiki) with full system documentation |
| 14. GitHub Workflow | Migrated from SCP deployment to GitHub-based workflow. Corrected run_daily_hire.py path from orphaned location to git-managed directory |
| 15. Booking Buildup | Hire-day heatmap by usage month × booking month. Date-overlap allocation, 12-month forward view, "12+ months prior" bucket. Tap-to-select tooltips on both buildup and utilisation heatmaps (mobile + desktop) |
| 16. Tab Navigation | Dashboard restructured from single scrolling page to 4-tab layout (Today, Performance, Forward, Breakdown). Each tab contains collapsible accordion sections with expand/collapse all controls. KPI cards and pace indicator remain always visible. Chart.js resize triggered on tab switch and accordion open |
Planned Dashboard Sections
| Section | Description | Data Source |
|---|---|---|
| Rental Duration Distribution | Bucket bookings by duration: 1d, 2–3d, 4–7d, 1–2w, 2–4w, 28+d | calc_rental_days |
| Day-of-Week Heatmap | Pickups and returns by weekday (staffing decisions) | pickup_date, dropoff_date |
| Top Customers | Repeat customer concentration, VIP identification | customer_name, paid_to_us |
Other Planned Work
- Data Entry/Edit Screen — Web-based UI replacing manual Excel editing. Would write to a database with the existing agent reading from it via a new
db_loader.py. - Deposit Ageing Breakdown — Segment outstanding deposits by age: 0–7 days, 8–14 days, 15–30 days, 30+ days.
Key Design Decisions
| Decision | Rationale |
|---|---|
| pickup_date for revenue | More accurate than booking_date — represents when the hire actually occurs |
| Dual rental day tracking | Col V only populated for paid bookings; agent calculates from dates for all |
| Same-day clip to 1 | 190 bookings have pickup = dropoff; 0 days would distort averages |
| Resend API over SMTP | SMTP port blocked by network; Resend HTTP API works reliably |
| SharePoint archiving | Droplet storage is limited; SharePoint has ample space |
| JSON files (not live queries) | Fast, simple, no DB connection, atomic updates prevent partial reads |
| sslh on port 443 | Port 22 blocked; sslh multiplexes SSH + HTTPS on single open port |
| 7-day archive retention | Balances traceability with storage costs |
| μ notation | Compact, unambiguous, consistent across all outputs |
| FV group code as join key | Column Q in Surprice / Column M in DH / Column J in Vehicle Counts — unified fleet identity |
| Non-fatal Surprice loading | If Surprice data fails, DH pipeline continues unaffected — graceful degradation |
| GitHub as single source of truth | All code flows through GitHub main branch — no direct SCP to droplet |
Known Constraints
| Constraint | Impact |
|---|---|
| Port 22 blocked locally | Must use ssh -p 443 via sslh |
| No SMTP | Email via Resend HTTP API only |
| Limited droplet storage | No data archiving on droplet |
| OneDrive sync delays | May cause stale reads; temp-file copy mitigates lock issues |
| Single Excel source | No concurrent editing protection; last-write-wins |
| Free-text vehicle groups | No standardisation — used as-is from spreadsheet |
| No failure alerting | Cron failures only visible in log files (no email/Slack notification yet) |