Data model

	Server
Status	✅
Code	db/schema.rb (version 2026_10_27_000100), app/models/, app/services/track_file.rb, app/services/account_erasure.rb, app/jobs/{account_erasure,stale_device_cleanup,support_data_retention,trip_link_cleanup,unattached_blob_cleanup}_job.rb, config/recurring.yml

Verified against code: 2026-10-26

Purpose

What the server stores about users, devices, trips and tracks, and what each column means. PostgreSQL with the pgcrypto extension. Positions are plain decimal latitude / longitude columns; PostGIS is not used. A track's points are not rows but one file per track in object storage (Track file, ADR 017). Most tables have UUID keys; the high-volume tables (track_events, track_segments) use bigint keys.

Relationships

users ─┬─< devices ─┬─< device_registrations
       │            ├─< track_segments >─ tracks (nullable)
       │            └─< tracks (nullable device)
       ├─< boats ──< trips (nullable boat)
       ├─< trips ─┬─< tracks ─┬── points_file (Active Storage: the track file)
       │          │           ├─< track_events
       │          │           └── track_map_lines (one)
       │          ├─< trip_bridges (from_track, to_track)
       │          ├─< trip_days
       │          ├─< trip_links (public / crew)
       │          ├── preview_image (Active Storage, while a link works)
       │          └─< crew_memberships >─ users (the crew)
       ├─< passkeys, magic_tokens, connections
       └─< beta_applicants, support_requests ──< replies (nullable user)

Deletion

Deleted	Effect
User	Sailor bio, public profile images (avatar, header and their originals), picked profile trips, reserved handles, devices (with registrations), boats, trips (with their links and crew memberships), tracks (with their track files and events), segments, the user's own crew memberships on other trips, passkeys, magic tokens, connections, and the beta applications and support requests (with replies) linked by user_id are deleted (foreign keys cascade; has_many … dependent:).
Device	Its registrations and all its raw segments are deleted. Its tracks stay and lose the device link (device_id null).
Trip	Its tracks (with their track files, events and map lines), bridges, days, links, crew memberships and description are deleted; it disappears from the crew's logbooks.
Support request	Its replies are deleted (foreign key cascade).
Track	Points, events and the map line are deleted; segments stay and lose the link; bridges touching it are deleted.
Boat	Trips lose the boat.
Description editor	A trip's description_edited_by_id is set to null when that user's account is deleted; the description stays with its trip, shown as "Last edited ".

Account erasure (AccountErasure)

Used by AccountErasureJob (account page and admin, auth.md, admin.md) and bin/rails "users:erase[EMAIL]". In one transaction:

1. user.destroy! with the chain above; the track files and preview images are purged from Active Storage after the commit.
2. Beta applications and support requests (with replies) whose email equals the account's email or pending new address, in any case: rows sent before the account existed have no user_id.
3. One log line with the user id, the time and the counts of trips, tracks and devices; no email or name.

test/services/account_erasure_test.rb checks that afterwards no table with a user_id, email or unconfirmed_email column has a row of the user.

beta_applicants.user_id and support_requests.user_id are set when the row is saved and an account with that email exists, and on a beta invitation.

Cleanup jobs

Recurring jobs (config/recurring.yml, production):

Job	When	Deletes
StaleDeviceCleanupJob	Daily 4:30	Devices without a user (never paired) created more than 30 days ago, with their registrations
SupportDataRetentionJob	Daily 4:45	Support requests (with replies) and beta applications created more than 12 months ago
TripLinkCleanupJob	Daily 5:00	Public and crew links that ended (expired or revoked) more than 30 days ago (sharing.md)
UnattachedBlobCleanupJob	Daily 4:00	Attachments whose record no longer exists (rows deleted in plain SQL or by a cascade), with their files; then uploaded files never attached, after a day

Recording tables

tracks

One recording (SLTB) or one FIT file.

Column	Meaning
user_id, trip_id, device_id	Owner; trip (nullable, always one of the owner's trips: validated); device (nullable, e.g. web import)
uploaded_by_id	Who uploaded the file in the web upload (the owner or the trip's skipper); null for watch tracks. Set to null when that account is deleted (on_delete: nullify). Lets the skipper delete their own uploads (sharing.md § Roles)
source	watch_sync (SLTB), direct_sync (API FIT upload), import (web upload), garmin_activity_api (unused)
external_activity_id	Web import: upload:<user_id>:<start unix>. Unique with source.
trip_token	Token the track arrived with
device_name	Model name at import, e.g. "fēnix 8 Pro 47mm" (default "Garmin Watch")
sport	Always sailing
start_time, end_time	Recording start (SLTB: track_id) and last kept point
trim_start_at, trim_end_at	Trim IN / OUT as timestamps; nullable; OUT must be after IN
distance_nm, distance_source	Distance and where it came from: device, computed, device_scaled
engine_distance_nm, sailing_distance_nm, engine_hours, sailing_hours	Split (ingestion.md)
max_speed_knots, avg_speed_knots	Speeds
storm_warning	A storm warning event exists
start_name, end_name	Departure and destination names
start_name_inherited	Start name came from the previous track or was generated
start_name_manual, end_name_manual	Typed by the user; never regenerated
wind, wind_fetched_at	JSON: the hourly model wind along the track as the wind endpoint returns it (times, cells with speed and direction), and when it was fetched; null until TrackWindJob ran (ingestion.md § Wind)
state_notes	JSON list [{"at": ISO, "note": "…"}]: the skipper's notes on the sail/engine changes from the track editor (at most 200 characters each); a note belongs to the change within 60 s of its time. Default [].
sync_status	SLTB only: receiving, complete, incomplete; null for FIT
expected_segments	SLTB: FINAL.seq + 1
device_totals	JSON: the SLTB END record (reason saved / recovered, timer_s, distance_m, …) or {"reason":"fit","distance_m":…}
processed_at, recompute_requested_at	Last recompute; set when a trim asked for one (shows "recalculating" until processed_at is newer)
is_private	Default true; not used by the logbook

Indexes: unique (device_id, start_time) where device_id is set; unique (source, external_activity_id) where set.

Track file

A track's points: one file per track in Active Storage (tracks.points_file, content type application/vnd.sailever.track, production bucket sailever-files), written on ingest and rewritten by every SLTB segment and every sail/engine edit (ADR 017, app/services/track_file.rb). Every source is on the 5 s grid (ingestion.md). The track editor reads the same file in the browser (app/javascript/track_view/track_file.js).

Format, gzip-compressed; after decompression little-endian:

Part	Content
Header, 16 bytes	SVTP, u8 version 1, u8 interval 5 (s), u16 flags 0, u32 number of points n, u32 time of the first point (epoch s)
i32[n]	Seconds since the previous point (first: 0)
i32[n] i32[n]	Latitude and longitude in 1e-7°, as differences to the previous point (first: the value)
u16[n]	SOG in 0.01 kn (SLTB: derived by the server; FIT/GPX: from the file)
u16[n]	COG in 0.1°
u16[n]	Pressure in 0.1 hPa
u8[n]	Vessel state: 0 sail, 1 engine, 2 motorsail, 3 mooring, 4 anchor, 5 buoy, 6 shoreline, 7 adrift (vessel-state.md)
u8[n]	Engine on (1): state engine / motorsail, or the FIT engine field

0xFFFF in a u16 column means no value. Points outside the trim stay in the file and are ignored by every calculation (Track#effective_points). Each read is cached per server process by the file's key; a changed file is a new file.

track_events

Derived, rebuilt with every recompute (ingestion.md).

Column	Meaning
kind	vessel_state_change, engine_on, engine_off, storm_warning, anchor_drift_alert (unused)
occurred_at, latitude, longitude, baro_pressure_hpa	Where and when
details	JSON: from, to, location_name, island_name, feature_type, engine_on, drop_hpa, window_hours

track_map_lines

Derived, rebuilt with every metrics computation (ingestion.md). Its own table, so lists that load tracks don't load their lines.

Column	Meaning
track_id	Unique; deleted with the track
points	The simplified line, packed by MapLines::Codec: BER varints (version 1, number of lines, then per line: point count, engine of the first point, the engine switch indexes as steps, then latitude and longitude in 1e-5° as zigzag steps from the previous point). About 3 bytes per point.

track_segments

Every uploaded SLTB segment. The raw payload is the archive of a watch track: the track can be rebuilt from it at any time.

Column	Meaning
device_id, user_id	Uploader at upload time
track_id	Set by the ingest job; null before, after a discard, or when the track was deleted
trip_token	From the envelope (≤ 64)
track_started_at, seq	Identity with device_id (unique idx_segments_identity)
final, format_version	From the envelope (format_version always 1)
payload, byte_size	Raw SLTB bytes (header + body); emptied on discard
point_count	Decoded points
device_model, app_version	From the envelope
status	received, decoded, quarantined, discarded
error, processed_at	Quarantine reason; last processing

Kept indefinitely (about 7 KB per day of sailing) until the device or the user is deleted.

Trip tables

trips

Column	Meaning
user_id, boat_id	Owner; boat (nullable, always one of the owner's boats: validated)
title	Required. Generated as Sailing Trip <date> or from the upload dialog.
trip_token	Unique per user when set (idx_trips_user_token). Watch-generated (20 hex) or a UUID for web uploads.
sailing_area	Free text; the spelling the user already uses wins (case-insensitive)
skipper	The owner was skipper ("I was the skipper"); setting it clears a crew skipper (sharing.md § Roles)
time_zone	IANA zone for days; set from the first point
description_edited_by_id, description_edited_at	Who last changed the description text (trip.md) and when; the foreign key to users nulls when that account is deleted
bridge_gaps	Bridges between all tracks on / off
total_distance_nm, engine_distance_nm, sailing_distance_nm, bridged_distance_nm, engine_hours, sailing_hours	Sums (ingestion.md)
import_notified_at	When the new-trip email went out (emails.md)
pending_uploads	Files of a web upload still being imported
map_line_card	The card's map line, packed like track_map_lines.points, one line per track (ingestion.md). Null until the first recompute or the backfill.
charter_company	Ignored column (moved to boats)
visibility	Ignored column, dropped in the next migration: visibility levels are gone, trips are shared by link (sharing.md)

trip_links

A link to a trip that works for 30 days (sharing.md).

Column	Meaning
trip_id	Trip (cascade)
kind	public (the read-only page /t/<token>) or crew (joining at /crew/<token>)
token	22 base58 characters, unique; the only thing in the URL. Stored in clear so the owner can copy the link again.
expires_at	Creation + 30 days
revoked_at	Set by "Stop sharing" (public) or by a new crew link (crew)

Indexes: token (unique), (trip_id, kind, expires_at), and index_trip_links_one_crew_link (unique trip_id where kind = 'crew' AND revoked_at IS NULL).

crew_memberships

trip_id (cascade), user_id (the crew member, cascade), role (crew or skipper, default crew, check constraint crew_memberships_role; sharing.md § Roles), created_at (joined). Unique (trip_id, user_id); at most one skipper per trip (index_crew_memberships_one_skipper, unique trip_id where role = 'skipper'); the owner can't be crew of their own trip.

trip_bridges

Derived (ingestion.md): from_track_id, to_track_id (unique pair), distance_nm, propulsion (unknown, engine, sail; always unknown today), duration_seconds (never set).

trip_days

Derived (ingestion.md), unique (trip_id, date).

Column	Meaning
date, day_number	Local date; 1-based count of days with points
started_at, ended_at	First and last kept point of the day
start_name, end_name	Route of the day
distance_nm, sailing_distance_nm, engine_distance_nm, bridged_distance_nm, sailing_hours, engine_hours	Day totals
night_distance_nm, night_hours	Miles and underway hours in nautical night (ingestion.md); NULL until the day is rebuilt
night_stay, night_stay_override	anchor, buoy, mooring, shoreline, at_sea, unknown; the override is set by the user and survives rebuilds
night_name, night_latitude, night_longitude	Where the night was spent
track_ids	UUID array of tracks touching the day
bounds	[[south, west], [north, east]]

profile_trips

user_id, trip_id (unique pair, both cascade), created_at: a trip the user picked for their public skipper profile. Removed when the user leaves the trip's crew.

profile_handle_reservations

handle (unique), user_id (cascade), released_at: a handle given up, reserved for its old owner until released_at (30 days, profile.md).

boats

name (required), boat_model, home_port, own_boat, charter_company (cleared for own boats), kind (sailing_yacht default, catamaran, motorboat), length_m (length overall, optional, 2–100, one decimal).

Device tables

devices

One installed watch app, unique (platform, device_uid). Registration and pairing: device-api.md.

Column	Meaning
platform	garmin, apple_watch, wear_os
device_uid	Install identifier from the watch (≤ 128)
device_model	Raw model (Garmin part number, Build.MODEL)
display_name, display_name_manual	Name shown; typed by the user when manual
app_version, last_registered_app_version	Latest reported version
user_id, paired_at	Owner and pairing time; null until paired
revoked_at	Removed or unpaired
token_hash, token_rotated_at	SHA-256 of the device token (unique); when it was issued
pairing_code_hash, poll_token_hash, pairing_expires_at	Open pairing request
last_seen_at	Last registration or authenticated request

A device is paired when user_id and paired_at are set and revoked_at is null.

device_registrations

One row per device and app version (unique (device_id, app_version)): platform, app_version, device_model, registered_at. Feeds the admin device statistics.

Other tables

Table	Holds
users	Email (unique), unconfirmed_email, names, locale, webauthn_id, privacy_consent_version and privacy_consented_at (web consent, auth.md), terms_version and terms_accepted_at (auth.md), show_first_name_publicly (default false, sharing.md), admin (default false: may enter /admin, admin.md), public skipper profile: profile_handle (unique), profile_public, profile_indexable (both default false), profile_handle_changed_at, avatar_crop / header_crop ({x, y, w, h} fractions); attachments avatar, avatar_original, header_image, header_original (profile.md)
passkeys, magic_tokens	Sign-in (auth.md)
connections	Provider OAuth tokens (garmin, apple, android, huawei); unused
action_text_rich_texts	Rich text with uuid record_id: User#sailor_bio and Trip#description (name, HTML body). Unique per record and name; deleted with the record (has_rich_text). No attachments are ever stored.
active_storage_*	Track files (tracks.points_file), link preview images (trips.preview_image, sharing.md) and pending upload blobs
support_requests, replies, beta_applicants, system_settings	Admin and support (admin.md). support_requests and beta_applicants have a nullable user_id (cascade); beta_applicants.privacy_consented_at and terms_accepted_at are the times of the form's privacy and terms checkboxes
deploys	One row per server deploy for the admin changelog: version (the deployed commit, unique), deployed_at, entry_ids (ids from config/changelog.yml). No user data
osm_places, osm_shores, osm_coastline, osm_imports	OpenStreetMap places for the place names, no user data; built by each import, not in schema.rb or the backup (ingestion.md § OSM places)

Archives

Channel	Archive	Rebuild
SLTB	track_segments.payload	Admin Reprocess per quarantined segment; the segment's time window in the track file is replaced

Known deviations

• Deleting a device keeps its tracks but deletes their raw segments, so those tracks can no longer be rebuilt.