This file contains Unicode characters that might be confused with other characters. If you think that this is intentional, you can safely ignore this warning. Use the Escape button to reveal them.
Mirrored from
/mnt/DATA/git/pikebacker/docs/08_data_model.md. Origin:repository. Active tasks and changing state belong in Gitea issues; this wiki page is durable project context.
Data Model
The authoritative DDL is in schemas/database.sql. This document explains the domain model.
Core entities
User
Stores account, preferences, privacy settings, and default bikepacking profile.
Trip
A planned multi-day journey.
Fields:
- id,
- owner_id,
- title,
- start/end coordinates,
- date range,
- profile,
- status,
- created_at,
- updated_at.
Route
A route generated for a trip.
Fields:
- id,
- trip_id,
- provider,
- geometry,
- distance,
- ascent/descent,
- surface breakdown,
- access confidence,
- warnings.
Route segment
A normalized route chunk with scoring attributes.
Fields:
- geometry,
- distance,
- grade,
- surface,
- smoothness,
- highway class,
- access tags,
- risk scores,
- confidence.
Stage
A daily ride slice.
Fields:
- day_index,
- route_start_m,
- route_end_m,
- distance,
- ascent,
- estimated_time,
- endpoint,
- sleep candidates,
- warnings,
- Plan A/B.
POI
A point of interest for logistics.
Categories:
- water,
- food,
- sleep,
- repair,
- bailout,
- charging,
- emergency.
Service gap
A computed gap between relevant POIs along the route.
Fields:
- type,
- start_m,
- end_m,
- distance,
- severity,
- explanation.
Local report
A typed, dated user/local report.
Fields:
- report type,
- geometry/point,
- route segment optional,
- payload,
- trust score,
- expiry date,
- moderation status.
Rule card
A localized advisory card about riding/camping/access/fire/protected areas.
Fields:
- jurisdiction,
- rule type,
- summary,
- status,
- confidence,
- source URL,
- last reviewed,
- geometry.
Offline pack
A generated set of data for a trip.
Fields:
- trip,
- bbox,
- corridor buffer,
- included layers,
- version metadata,
- file references,
- status.
Important modeling choices
Route distance index
Store positions along a route as meters_from_start. This allows POIs, warnings, reports, and stage boundaries to attach to the route even when coordinates are simplified.
Confidence levels
Use a normalized enum:
known_good,high,medium,low,unknown,restricted,conflicting.
Warning severity
Use:
info,notice,warning,critical.
Report expiration
Most local reports should expire unless reconfirmed:
- water status: 30–90 days,
- closure: 7–60 days depending source,
- surface condition: 14–90 days,
- access/legal: reviewed manually,
- sleep hints: 90–365 days.