# 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.