pikebacker/docs/08_data_model.md

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.