ERO Reference

Schemas, payloads, and endpoint contracts. This document is the single owner of the Event Route Optimiser data model. Where another document needs a field list, it links here.

1. Storage schema

The schema maintains structural parity across Cloudflare D1 at the edge and Dexie over IndexedDB on the device, so synchronisation is a merge rather than a translation.

1.1 Festivals

Field	Type	Notes
id	TEXT	Primary key, unique identifier or slug
name	TEXT	Full festival name
url	TEXT	Origin Clashfinder URL
created_at	INTEGER	Epoch milliseconds

1.2 Stages

Field	Type	Notes
id	TEXT	Primary key, generated GUID
festival_id	TEXT	Foreign key to festivals.id
name	TEXT	Stage name

1.3 Bands

Field	Type	Notes
id	TEXT	Primary key, generated GUID
stage_id	TEXT	Foreign key to stages.id
name	TEXT	Act or band name
start_time	INTEGER	Epoch milliseconds
end_time	INTEGER	Epoch milliseconds

1.4 User routes

Local-first. Owned by the client and authoritative on conflict.

Field	Type	Notes
id	TEXT	Primary key, GUID
band_id	TEXT	Foreign key to bands.id
priority	INTEGER	Rating or precedence score
sync_status	TEXT	synced, pending, or failed
updated_at	INTEGER	Epoch milliseconds

1.5 Local override fields

The client Act schema in apps/event-route-optimiser/web/src/lib/db.ts carries four additional fields that exist only on device.

Field	Type	Default
is_local_override	boolean	false
local_start_time	string	optional
local_end_time	string	optional
local_stage_id	string	optional

A differential merge must never overwrite a row where is_local_override is true.

2. API contract

2.1 Fetch a festival

GET /api/festivals/:id

Returns nested festival, stage, and band entities in one payload, so the client can hydrate Dexie in a single pass.

2.2 Ingest a Clashfinder event

POST /api/ingest/clashfinder

Request body:

{ "festivalId": "string", "username": "string" }

Returns a success payload reporting the number of stages and acts imported or updated.

2.3 Generate a route

GET /api/route/:username

Returns a strict itinerary alternating act and transit entries.

{
  "username": "example-user",
  "itinerary": [
    { "type": "act", "band": "Concrete Age", "stage": "New Blood Stage", "startTime": 1786017600000, "endTime": 1786019400000 },
    { "type": "transit", "instruction": "Walk to Main Stage", "durationMinutes": 8 },
    { "type": "act", "band": "Lamb of God", "stage": "Main Stage", "startTime": 1786020000000, "endTime": 1786023600000 }
  ]
}

3. Clashfinder source payload

The upstream Clashfinder JSON is fetched from https://clashfinder.com/data/event/{festivalId}.json?user={username}.

3.1 Event

Field	Type
event	string, festival name
url	string, original Clashfinder URL

3.2 Day

Field	Type
id	string, for example fri
date	string, ISO date YYYY-MM-DD

3.3 Stage

Field	Type
id	string
name	string, full display name

3.4 Act

Field	Type
name	string
stage	string, matches a stage id
day	string, matches a day id
start	string, HH:MM
end	string, HH:MM
highlight	number, greater than zero means the user selected it

4. Mapping rules

Converting the source payload into the storage schema requires two transformations.

1. Time. Combine the HH:MM time string with the day YYYY-MM-DD date and convert to epoch milliseconds for start_time and end_time. Storing milliseconds on both sides is what keeps D1 and Dexie in parity.
2. Keys. Generate a GUID for any entity that arrives without a stable identifier.