docs/wiki/synthesis/directus-schema-draft.md

title, type, created, updated, sources, tags

title	type	created	updated	sources	tags
Directus Schema — Working Draft	synthesis	2026-05-01	2026-05-01	rally-albania-regulations-2025	directus schema business-plane draft penalties geofences

Directus Schema — Working Draft

Status: working agreement, not final. Captured during the 2026-05-01 schema discussion. Open for additions and revisions as the domain shape clarifies.

The TRM business-plane schema, as worked through so far. Pseudo multi-tenant under organizations. Two layers: an org-level catalog of durable resources (users, teams, vehicles, devices) and a per-event participation layer (entries + their crew/devices). All event-scoped state hangs off entries; entries is the unit of timing.

Reference rulebook: rally-albania-regulations-2025 is the canonical real-world reference. Section numbers cited in this doc as Rally Albania §X.Y map to that source. Where the schema needs a federation-specific shape (start-order rules, penalty taxonomy, class catalog), that source is the ground truth.

Tenancy model

organizations is the root. Users, teams, vehicles, and devices are all m2m with orgs — a single device can be loaned across orgs, a privateer can race for two clubs, a team can compete under multiple federations. Events, by contrast, are scoped to a single org (one FK).

Org-level catalog

Durable resources that exist independently of any particular event.

organizations

Tenant root. Every business-plane row ultimately traces back here.

users

Directus users, augmented with TRM-specific profile fields. M2M with orgs via organization_users.

organization_users (junction)

Field	Notes
organization_id	FK
user_id	FK
role	enum: owner / admin / race-director / marshal / participant

The role column drives Directus permission policies — a user's effective permissions in a given org come from this row.

teams

Durable rosters. M2M with orgs via organization_teams. A team's "presence" in an event is derived from entries.team_id — no separate team↔event join needed.

organization_teams (junction)

organization_id, team_id.

team_members (junction)

team_id, user_id. Durable team roster. Who actually races for the team in a given event is the subset that has entries with that team_id.

vehicles

M2M with orgs via organization_vehicles. No ownership FKs — ownership is a real-world fact that doesn't affect timing or tracking, and which user/team a vehicle "belongs to" is fluid (factory loaner, rented, privateer, swapped between teams). Anyone with org-level entry-creation permission can register any org vehicle into an entry.

organization_vehicles (junction)

organization_id, vehicle_id.

devices

GPS hardware (Teltonika today, future vendors possible). M2M with orgs via organization_devices. The imei is the canonical identifier the tcp-ingestion service uses; the Directus row carries vendor/model/firmware metadata and the org bindings.

organization_devices (junction)

organization_id, device_id.

Event-level participation

The per-race shape. Built around entries as the unit of timing.

events

Field	Notes
organization_id	FK, single-org per event
discipline	enum: rally / regatta / trail-run / hike / … (extensible)
name, starts_at, ends_at, …	descriptive

discipline drives validation: which crew roles are valid, whether vehicles are required, which device mount points the UI offers.

classes

Per-event class taxonomy. event_id FK, plus name, code (e.g. T1, T3, SSV), sort_order. Each event defines its own class set, so a rally's T1/T2/T3 doesn't pollute a regatta's classes.

entries

The unit of timing — what gets a bib, gets timed, gets a result.

Field	Notes
event_id	FK
vehicle_id?	nullable — null for foot/hike events
team_id?	nullable — null for lone racers
class_id	FK to classes
bib	per-event identifier shown to spectators
status	enum: registered / started / finished / dnf / dns / dsq / withdrawn

Status semantics:

• registered — entered, not yet started.
• started — currently competing. Live map only renders entries with this status.
• finished — completed within the rules. Eligible for ranked results.
• dns (Did Not Start) — registered but never crossed the start line.
• dnf (Did Not Finish) — started but failed to complete (mechanical, retired, timeout). Shows on results board but unranked.
• dsq (Disqualified) — penalized off the result by officials.
• withdrawn — pulled themselves out before the event started; doesn't count toward field size.

A vehicle can race at most one entry per event (matches reality — same vehicle can't run two stages simultaneously). For foot races, vehicle_id is null and the entry is identified by its single crew member.

entry_crew (junction)

Field	Notes
entry_id	FK
user_id	FK
role	enum: pilot / co-pilot / navigator / mechanic / rider / runner / hiker (extensible per discipline)

One row for solo rides, four+ for big-truck rally crews. No separate crews collection — teams already provides durable group identity, and per-entry crew is the subset that showed up.

A user has two distinct role surfaces

The schema carries two role columns that look similar but mean different things. Easy to conflate; worth being explicit.

Surface	Where	Scope	What it controls
Org role	organization_users.role	Per-tenant, durable	What the user can see and do inside an org. Drives Directus policies (race-director, marshal, participant, …).
Racing role	entry_crew.role	Per-entry, per-event	What the user does on the track in this specific entry (pilot, co-pilot, navigator, mechanic, …).

The two role enums don't overlap. Same user can be race-director in Org A (admin power), participant in Org B, and show up as pilot in three different entries across both orgs — three independent dimensions.

Crew → org chain is indirect:

entry_crew.entry_id  →  entries.id
entries.event_id     →  events.id
events.organization_id →  organizations.id

The crew row itself doesn't reference org. The schema does not enforce that an entry_crew.user_id is also in organization_users for the entry's host org — guest racers happen. If a federation requires strict org membership for participation, that's a per-org permission rule on entry_crew insert, not a schema FK.

entry_devices (junction)

Field	Notes
entry_id	FK
device_id	FK
assigned_user_id?	nullable — null = vehicle-mounted (hardwired/backup), set = body-worn on that crew member

A rally Toyota with three devices: two rows with null assigned_user_id (hardwired + backup), one row with assigned_user_id set to the pilot (panic button). A solo runner: one or more rows all with assigned_user_id set to the runner.

Course definition

The spatial and procedural definition of an event. Three layers: stages (ordered containers), segments (typed sub-units of a stage), and geographical features (geofences, waypoints, SLZs) attached to segments.

stages

Field	Notes
event_id	FK
name	"Stage 1", "Day 2 Loop", etc.
sort_order	integer
role	enum: prologue / regular / epilogue. Default regular. Drives default seeding strategy and standings handling.
starts_at	base time used to compute per-entry start offsets
start_interval_seconds	gap between consecutive starts. Set per stage (Rally Albania §5.10 — decided the day before each stage).
start_order_strategy	enum: manual / previous_stage_result / previous_stage_clean_result / inverse_top_n_then_natural / inverse_of_overall. See "Start order strategies" below.
start_order_strategy_params?	JSON. Strategy-specific (e.g. { "n": 20, "source_stage_id": <prologue_stage_id> } for inverse_top_n_then_natural).
start_order_input_stage_id?	FK to another stages row when a strategy reads from a specific previous stage (prologue → stage 1, or any non-immediate predecessor). Null = use immediately preceding stage by sort_order.

A rally can have many stages; a trail run typically has one. The stage is just an ordered container — all the actual rules and geometry hang off its segments.

Start order strategies

Different federations seed each stage differently. The strategy is declarative on the stage; the processor (or a Directus operation) materializes entry_segment_starts rows when a stage is opened. Real-world cases derived from rally-albania-regulations-2025 §5.5–§5.10.

Strategy	Behavior	Real-world example
manual	Org sets each entry's start_position directly. No computation.	Tirana 24h every leg; Rally Albania prologue.
previous_stage_result	Order by ascending SS time of the input stage (penalty-adjusted excluded).	Rally Albania Stage 1 cars/SSV (input = prologue).
previous_stage_clean_result	Same as above, explicitly using clean SS time only. Penalties never affect seeding.	Rally Albania stages 2 → last (input = previous stage).
inverse_top_n_then_natural	Top n of input stage in inverse order, then n+1…last in natural order.	Rally Albania Stage 1 bikes/quads (params: n=20, source=prologue).
inverse_of_overall	Order by descending overall standings after the previous stage.	Rally Albania epilogue.

Seeding input is always clean SS time (stage_results.clean_time) — penalties roll into overall total, never the next stage's grid (Rally Albania §5.8 explicit on this).

Strategies seed per category independently. Bikes/quads share a grid; cars share another; SSVs another (Rally Albania §2.8 + §5.10). The materialization step iterates each category's entries and assigns positions within that category.

Late-arrival reseeding stays operator-driven (Rally Albania §9.8, Tirana 24h §8.7) — Race Marshals adjust individual entry_segment_starts.target_at rows on the day, not via strategy.

segments

The atomic unit of rules within a stage. Each segment has a type that drives processor and validation behavior.

Field	Notes
stage_id	FK
sort_order	integer
type	enum: liaison / special-stage / parc-ferme
entry_geofence_id	FK to geofences — where you arrive / get timed in
exit_geofence_id?	nullable — only special-stage segments have one (timed exit). Liaisons end implicitly when the next SS-start is hit; parc fermé has no exit.
target_duration_seconds?	nominal duration used to compute target arrival times for liaisons

Concrete shape of a rally stage (per Rally Albania pattern):

[Stage Start] → Liaison → [SS1 Start] → SS1 → [SS1 Finish] →
   Liaison → [SS2 Start] → SS2 → [SS2 Finish] →
   Liaison → [Parc fermé]

The […] markers are geofences shared between segments — the SS1-Start geofence is both the exit-trigger of the preceding liaison and the entry-trigger of SS1.

geofences

Reusable spatial assets. Polygons stored as PostGIS geometry.

Field	Notes
event_id	FK
name	display name
kind	enum: stage-start / ss-start / ss-finish / parc-ferme / manual-checkpoint
geometry	PostGIS polygon
manual_verification	bool — true = marshal-confirmed (CP), false = pure GPS detection
retroactive	bool, default false — whether geometry edits trigger recompute of past crossings

"Checkpoints" are not a separate collection; they are geofences with manual_verification = true. In practice these usually coincide with SS-start / SS-finish geofences (matches rally conventions).

waypoints

Auto-detected points along an SS track. Missing one = penalty.

Field	Notes
segment_id	FK — must reference a special-stage segment
location	PostGIS point
tolerance_meters	radius for "passed within" detection
sort_order	integer

speed_limit_zones

Distinct from geofences because they carry SLZ-specific evaluation parameters. Polygons where a speed cap applies.

Field	Notes
segment_id	FK
geometry	PostGIS polygon
max_speed_kmh	the cap
evaluation_window_meters?	null = whole-polygon peak; 2000 = re-evaluate every 2km of transit (per the Tirana 24h rulebook).
retroactive	bool, default false — whether geometry edits trigger recompute of past traversals

Penalty system

Penalty rules are stored as data, evaluated in code. The collection holds the numbers (per-bracket multipliers, per-miss penalties); the processor holds the math (one evaluator function per penalty type, in a registry).

penalty_formulas

The collection. Single shape covering bracketed (SLZ) and flat (CP, WP, late-start) rules; bracket-specific fields are nullable.

Field	Notes
belongs_to_type	enum: event / stage / speed_limit_zone / geofence — the scope this rule attaches to
belongs_to_id	FK to that scope
type	enum: speed_limit_offence / waypoint_missing / checkpoint_missing / early_start / late_start
input	descriptor of the variable being measured (e.g. peak_overspeed_kmh, missed_count, seconds_late)
offence_min?	bracket lower bound (km/h, seconds, etc.). Null for flat rules.
offence_max?	bracket upper bound. Null = open-ended.
operator	enum: multiplication / addition
penalty	the numeric value (sec/kmh, sec per miss, etc.)
retroactive	bool, default true — whether formula edits trigger recompute of past penalties
enabled	bool, default true

Resolution at evaluation time: most-specific scope wins. The processor queries "what's the SLZ rule for this zone?" first against speed_limit_zone, falls back to stage, falls back to event.

Bracketed rules: progressive slice-by-slice

For speed_limit_offence, multiple rows with the same (belongs_to_type, belongs_to_id, type) form a bracket table. Each bracket contributes only to the portion of the input within its range — same math as progressive income tax. For peak overspeed P:

penalty = 0
for each row in brackets ordered by offence_min:
    if P < row.offence_min: continue
    upper = min(P, row.offence_max ?? P)
    slice = upper - row.offence_min + 1
    penalty += slice * row.penalty

Worked example (Tirana 24h table, peak P = 58 km/h overspeed):

offence_min	offence_max	sec/kmh	slice	contribution
1	10	5	10	50
11	20	10	10	100
21	30	30	10	300
31	40	90	10	900
41	null	240	18	4320
			Total	5670

Flat rules: one row, one number

For waypoint_missing and checkpoint_missing, a single row carries the per-miss value:

// missed waypoint: 60 min × count
{ "belongs_to_type": "event", "belongs_to_id": "<event-id>",
  "type": "waypoint_missing", "penalty": 3600, "enabled": true }

// missed checkpoint: 120 min × count + worst_valid_time_in_category (added by evaluator)
{ "belongs_to_type": "event", "belongs_to_id": "<event-id>",
  "type": "checkpoint_missing", "penalty": 7200, "enabled": true }

The "worst valid time in your category" addition for checkpoint_missing is not stored as a number. It's a runtime aggregate the processor computes from entry_results at stage close, and the evaluator function for that type adds it as part of its semantics. If a federation publishes a variant without that addition, that's a new type (checkpoint_missing_flat) with its own evaluator — not a flag on the row.

Code side: the evaluator registry

The processor holds a registry mapping type → evaluator function. The evaluators contain no constants — all numbers come from penalty_formulas rows.

const evaluators = {
  'speed_limit_offence': (peak, rules)        => walkBrackets(peak, rules),
  'waypoint_missing':    (count, [rule])      => count * rule.penalty,
  'checkpoint_missing':  (count, [rule], ctx) => count * rule.penalty + ctx.worstValidTimeInCategory,
  'late_start':          (secondsLate, rules) => walkBrackets(secondsLate, rules),
  // …
};

The split: numbers live in the database (rows in penalty_formulas); math shape lives in code (one function per type); runtime aggregates (peak speed, missed count, worst time) are computed by the processor and passed to the evaluator. Adding a new event with a different bracket table = pure data change, no deploy. Adding a fundamentally new kind of math = small code change (one new evaluator, one registry entry).

Loading and reload

The processor queries enabled formulas in scope when an event becomes active or when it starts up:

SELECT * FROM penalty_formulas
WHERE belongs_to_id IN (
    $event_id,
    SELECT id FROM stages WHERE event_id = $event_id,
    SELECT id FROM geofences WHERE event_id = $event_id,
    SELECT id FROM speed_limit_zones WHERE event_id = $event_id
  )
  AND enabled = true;

Indexes them in memory by (belongs_to_type, belongs_to_id, type) for O(1) lookup at violation time. Reload triggers: Directus webhook on formula edit (preferred), periodic refresh as fallback, manual "reload formulas" admin action.

Retroactive recompute

retroactive lives on penalty_formulas (default true — math fixes usually apply across the field) and on geofences / speed_limit_zones (default false — physical crossings stand on their own merit). Per-edit override available so an organizer can flip the default for a specific change.

When a row is updated and retroactive = true:

1. A Directus Flow / hook enqueues a recompute job (Redis Stream recompute:requests).
2. The processor consumes the job, walks affected entry_penalties, recomputes each from snapshotted inputs against the new formula, updates the rows in place, logs recomputed_at / recomputed_reason.
3. Old values are preserved in Directus revisions for audit.

Two recompute kinds:

• Formula change — cheap. entry_penalties.inputs is snapshotted, so it's pure arithmetic. Seconds for thousands of rows.
• Geometry change — expensive. Crossing decisions themselves change; have to re-detect from raw positions in postgres-timescaledb. Stage-bounded. Defer to Phase 2.5 of the processor.

Per-entry timing and results

Written by the processor as cars drive (Phase 2 work — not part of Phase 1 of processor).

entry_segment_starts

Field	Notes
entry_id	FK
segment_id	FK
start_position	integer — seeded position within the entry's category for this stage. Output of the strategy.
target_at	stage.starts_at + (start_position - 1) × stage.start_interval_seconds, plus nominal liaison durations for sub-segment starts. Operator-overridable.
manual_override	bool, default false. Set to true when a Race Marshal hand-edits target_at (late arrival, etc.) — flags the row as not derivable from the strategy.

Materialized by the processor (or a Directus flow) at stage-open time, scoped per category. Until then the rows do not exist.

entry_crossings

The raw timeline of detected/recorded crossings. Append-only, written by the processor as positions stream in (or by marshals via Directus for manual checkpoints).

Field	Notes
entry_id	FK
geofence_id?	one of geofence_id / waypoint_id set per row
waypoint_id?
crossed_at	timestamp
source	enum: gps / manual

entry_penalties

Computed from entry_crossings + penalty_formulas by the processor.

Field	Notes
entry_id	FK
segment_id?	FK, when applicable
rule_id	FK to penalty_formulas (or to a "rule set" if grouped)
seconds	computed penalty
reason	free text + structured payload
formula_snapshot	jsonb — the rule rows used at evaluation time, so the row is self-explaining if formulas later change
inputs	jsonb — peak overspeed, missed count, etc. (whatever the evaluator consumed)
evaluated_at	timestamp
recomputed_at?	set if the row has been retroactively recalculated
recomputed_reason?	what triggered the recompute

The formula_snapshot + inputs fields make recompute a pure data transformation — no need to re-derive anything from raw GPS.

stage_results

Materialized leaderboard per stage. Recomputed when the stage closes or on protest.

Field	Notes
entry_id	FK
stage_id	FK
raw_time	seconds — net time from start to finish
penalty_seconds	sum of entry_penalties for this stage
final_time	raw_time + penalty_seconds
position	rank within class

Position flagging (cross-plane operator workflow)

Devices emit faulty data — jumpy GPS, impossible coordinates, unrealistic speeds. Track operators need to exclude such points from calculations after the fact, without deleting them (the raw record stays available for audit).

Mechanism

The positions hypertable in postgres-timescaledb carries a faulty boolean DEFAULT false column. The hypertable is exposed as a Directus collection (read + update) so operators can flip the flag through the admin UI like any other row. Position records are owned by the processor (write side, telemetry plane), but the faulty flag is exclusively a business-plane operator concern.

Permission scope: operators with role race-director (or a more specific track-operator role added to the organization_users.role enum if granularity is needed) get update access to positions.faulty, scoped via the same dynamic-filter Policy pattern — only positions whose device_id belongs to a device entered in an event of the operator's org.

Effect on the processor

All processor read paths against positions filter WHERE faulty = false:

• Peak-speed evaluation inside SLZs.
• Geofence/waypoint crossing detection.
• Replay-based recompute.

Flagged positions are still in the hypertable, still in the live broadcast (the live channel doesn't filter — operators flag historically, after the fact). They simply no longer contribute to penalties and results.

Recompute on flag change

When faulty flips on a position whose timestamp is within an already-evaluated window:

1. Directus webhook fires on positions update where faulty changed.
2. Webhook enqueues a recompute request on recompute:requests (Redis Stream).
3. The processor identifies entry_penalties whose evaluation window overlaps the position's timestamp and re-evaluates them.
4. Cost is mid-band: cheaper than full geometry replay (the window is bounded), but the inputs (peak speed, crossing detection) must be re-derived from raw positions — the snapshotted entry_penalties.inputs no longer reflect reality once a position they were derived from is excluded.

This is a third recompute kind alongside formula recompute (cheap, snapshot arithmetic) and geometry recompute (expensive, full replay).

Decisions made

• Vehicle is the racing unit, not the user. In rally the vehicle gets the time; crew is sub-relation.
• Vehicle ownership is not modeled. Vehicles belong to orgs (m2m); who "owns" the vehicle in the real world doesn't affect timing or tracking, so it's left out of the schema.
• Class belongs to the entry, not the vehicle. A vehicle could race different classes in different events; class is a per-event property.
• Teams are org-level, durable. A team's per-event roster is derived from entries, not stored separately. No crews collection.
• Classes are per-event. classes.event_id FK; entries reference class_id.
• Discipline drives validation. events.discipline decides whether vehicle is required and which crew roles/device mount points are valid.
• Org-user role lives on the junction. organization_users.role is the per-tenant role driving Directus policies.
• Permission policies via dynamic filters. Directus 11 Policies, one per logical role (race-director, marshal, participant, …). Each policy's filter reaches through the row → org → organization_users to confirm the current user has that role in that org. A user gets all applicable policies attached at once. No org-switcher UI needed.
• Stage = container, segment = atomic. Stages just order their segments. Each segment has a type (liaison / special-stage / parc-ferme) that drives processor and validation behavior.
• Checkpoints are typed geofences, not their own collection. A "checkpoint" is a geofence with manual_verification = true (and usually a kind of ss-start / ss-finish).
• Penalty rules are data, math is code. The penalty_formulas collection holds all numeric values (bracket multipliers, per-miss penalties). The processor holds one evaluator function per penalty type. Adding new events / new bracket tables = data only.
• Speed limit penalties are progressive (slice-by-slice). Each bracket contributes only to the portion of the input within its range — same as income tax. peak_overspeed × per-bracket-rate summed across brackets the peak crossed.
• retroactive defaults differ by what's edited. Formulas default true (math fixes apply across the field). Geometry defaults false (physical crossings stand on their own). Per-edit override at save time.
• entry_penalties snapshot inputs and rule rows. Recompute is pure arithmetic from snapshots, not re-derivation from raw GPS — the rare exception being geometry retroactive changes.
• Positions carry a faulty flag. Operator-controlled, default false, set after the fact through Directus when a GPS reading is unrealistic. processor filters WHERE faulty = false on every read; flagging a position triggers a windowed recompute of affected penalties. The hypertable is exposed as a Directus collection for this workflow.
• Start order is per-stage, declarative, strategy-driven. Stages carry start_order_strategy + params, materialized into entry_segment_starts at stage-open time, scoped per category. Penalties never feed the next stage's grid; only clean SS time does. Operator overrides via manual_override for late arrivals.
• Stages have a role. prologue / regular / epilogue. Drives default strategy choice and lets the standings logic exclude the prologue from overall-time totals when a federation requires that. Most rallies = one prologue + N regulars; some end with an epilogue seeded by inverse-of-overall.
• CP missing vs CP-late-past-closing are distinct event types with the same penalty formula. Rally Albania §12.4 (missing) and §12.6 (arrived after closing hour) both pay "worst valid + 120 min" but trigger on different processor signals — one from "no crossing detected within stage window," one from "crossing detected but after time_control_closed_at." Both surface as entry_penalties rows with distinct types sharing a formula row.
• Tiebreaker is reverse-stage order. Rally Albania §6.19: same total → best last-stage time wins; if still tied, walk back stage-by-stage. Pure SQL on stage_results; no schema impact.
• Crews are per-entry, not reusable. No crews collection. Each entry builds its entry_crew rows fresh; the SPA solves "register the usual Toyota crew" via a "copy crew from previous entry" action that clones rows. Saved crews would go stale fast (people swap roles, drop out); copy-from-previous covers the real case without that risk.
• Non-bracket federation rules ship as code (Option A), one PR per rule shape. When a federation publishes math that doesn't fit the bracket / flat-per-miss model (curves, multi-input aggregates, conditional rules), the response is to add a new evaluator function to the registry plus the fields it needs on penalty_formulas. Explicitly not building an in-database expression engine (Option B). Reason: expression engines bring sandboxing, validation, authoring UX, testing, versioning, and determinism obligations that dwarf the per-rule PR cost. PR-per-rule keeps the math reviewable, deterministic, testable, and versioned through normal git history. Stability over flexibility.

Open questions

• Geometry retroactivity engine — the position-replay path for geofence/SLZ geometry edits is non-trivial. Defer to Phase 2.5 of processor once Phase 2 is shipping.

Relationship to existing wiki

This schema lives in directus (the business plane). The processor writes telemetry data (positions hypertable) keyed by device_id; the join from telemetry back to "who was racing" is device_id → entry_devices → entry → everything else. See plane-separation for why the telemetry plane stays ignorant of this schema.