Code Wiki

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)
Email	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:

1. Check col D (rc_ref) → if populated → "RC"
2. Check col C (mp_ref) → if populated → "MP"
3. Check col E (broker_ref) → if populated → "Other Broker"
4. 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

1. Pace Indicator — Today's pickup revenue vs MTD μ£/Day
2. Today's Snapshot — New bookings, revenue, vehicles on hire, deposits
3. New Bookings by Branch
4. Tomorrow's Activity — Pickups, Returns, Net
5. Revenue Pipeline — Tomorrow's pickups by branch
6. Unpaid Bookings — 7 days (conditional)
7. Alerts — Data quality issues
8. MTD Summary + MTD by Branch
9. YTD Summary
10. Fleet — Unique vehicles, idle count
11. 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)