Concepts

Data model & export

Everything TripLibre knows is in one SQLite file. The export bundle is that same data as CSV, JSON and GeoJSON, with stable IDs that join across every table.

Conventions

Field	Convention
`*_ts`	UTC Unix seconds (REAL). Exports add a matching `*_utc` ISO string.
`tz`	IANA time zone of the device when recorded. Local dates and hours use each record's own `tz`, so travel across zones stays correct.
Coordinates	WGS84 decimal degrees
Distances	Metres, great-circle (haversine, R = 6 371 008.8 m)
IDs	Lowercase UUID strings for visits, trips, places…; integers for raw points
`route_polyline`	Google encoded polyline, precision 1e-5, simplified with Douglas–Peucker (8 m)
Deletes	Soft: `deleted = 1`. `audit_log` keeps every old → new value.

The database is at Library/Application Support/HHT/travel.sqlite (WAL mode). The schema version is in PRAGMA user_version; migrations are append-only.

Core tables

Table	One row per	Key columns
`raw_location_points`	GPS fix	`ts, lat, lon, h_accuracy, speed, source, collector_mode, tz`
`motion_activities`	CoreMotion episode	`ts, activity, confidence`
`places`	Place	`id, name, lat, lon, radius_m, category, code, merged_into`
`visits`	Stay at a place	`id, place_id, arrival_ts, departure_ts, purpose, user_status, deleted`
`trips`	Movement between visits	`id, origin_visit_id, destination_visit_id, departure_ts, arrival_ts, distance_m, mode_auto, mode_user, purpose, route_polyline`
`trip_segments`	Mode segment inside a trip	`trip_id, seq, start_ts, end_ts, mode_auto, mode_user, distance_m`
`flights`	Flight	`trip_id, date, origin_code, destination_code, airline, flight_number, distance_m`
`stamps`	One-tap self-report (schema v3)	`id, ts, lat, lon` (coarse, ≈ 110 m), `uncertainty_m, fix_accuracy_m, fix_age_s, visit_id` (set by inference; NULL = unmatched), `note, deleted`
`life_phases`	Named date range	`name, kind, start_date, end_date`
`audit_log`	Correction	`ts, entity_type, entity_id, action, field, old_value, new_value`

The final mode of a trip is COALESCE(mode_user, mode_auto, 'unknown'). The full column-by-column reference is in docs/data-model.md.

Codes

Modes: walk run bicycle ebike scooter car car_driver car_passenger taxi bus subway light_rail commuter_rail intercity_rail coach shuttle ferry airplane other unknown

Purposes: home work school meal shopping recreation social personal_business medical travel transfer escort other

Place categories: home work_school restaurant cafe grocery shopping recreation entertainment airport rail_station transit_station hotel friend_family medical other stadium event_venue museum library convenience_store gas_station bar gym pharmacy bank civic religious parking

Views

v_trips: non-deleted trips plus mode_final, purpose_final, duration_s, and final origin/destination places.
v_visits: non-deleted visits plus place name, category and duration_s.

The export bundle

Settings › Create export writes hht-export-YYYYMMDD-HHMMSS/. Every column, with units, missing-value rules and synthetic examples, is in the Data dictionary.

File	Contents
`trips.csv`	The canonical HHTS-style trip table: `person_day_id`, `trip_seq`, OD places, UTC and local times, `travel_time_min`, `distance_m`, `mode`, `mode_group`, `segment_modes`, `purpose`, provenance
`trip_segments.csv`	Mode segments with their own times, distances and polylines
`visits.csv`, `places.csv`	Stays and places (with visit counts and first/last visit)
`flights.csv`, `life_phases.csv`, `audit_log.csv`	As in the database
`stamps.csv`	Self-reported stops (coarse location only); `visit_id` is the visit inference attached each one to, empty if unmatched. Loaded as `d.stamps` in Python
`raw_location_points.csv`, `motion_activities.csv`	Only if Include raw GPS points is on
`data.json`	Everything above in one JSON document
`places.geojson`, `trips.geojson`, `trip_ends.geojson`	For QGIS, kepler.gl, Mapbox…
`travel.sqlite`	A complete snapshot of the database, including raw points, whatever the range and raw-points settings. Delete it before sharing a limited export (see Privacy)
`README.txt`	What each file is

Analysing in Python

cd analysis
python3 -m venv .venv && source .venv/bin/activate
pip install -r requirements.txt
python explore.py ~/Downloads/hht-export-20260927-120000

from hht import load, summary, mode_share

d = load("~/Downloads/hht-export-20260927-120000")  # export folder, or a travel.sqlite file
d.trips.head()                       # one row per trip
summary(d.phase("Fall 2026"))        # trips/day, km/day, radius of gyration, entropy…
mode_share(d.trips, by="distance_m") # or "trips", "travel_time_min"

The full Python API is on the Developer API page.

Analysing with SQL

Open travel.sqlite in DuckDB, R (DBI), QGIS or the sqlite3 CLI and use the views:

-- mode share by trips for the last 30 days
SELECT mode_final, COUNT(*) AS trips, ROUND(SUM(distance_m) / 1000, 1) AS km
FROM v_trips
WHERE departure_ts >= strftime('%s', 'now', '-30 days')
GROUP BY mode_final
ORDER BY trips DESC;

✓

audit_log is a ready-made labelled dataset: every set_mode row is a ground-truth mode for a trip, which is useful for evaluating or training a better mode classifier.

Metric definitions

Metric	Definition
Person-day	Local date with ≥ 1 trip or visit. Per-day rates divide by person-days, not calendar days.
Mode share	By trips, distance or time, on the final mode; also by group (active / car / transit / long-distance / other).
Radius of gyration	√(Σ wᵢ d(pᵢ, c)² / Σ wᵢ) over visited places, dwell- and visit-weighted.
Activity space	Convex hull area of visited places.
Destination / mode entropy	Shannon entropy (bits) of visits per place / trips per mode.
Time away from home	24 h − time at `home` places, over days with a home visit.
Home-based tour	Trips from leaving home to next arriving home; complex tour = ≥ 2 stops.
Repeat OD share	Share of trips whose origin→destination pair already occurred earlier in the period.

All definitions: docs/analytics.md.