Reference

Data dictionary

What you get when you export from TripLibre: each file, each column, and the rules for times, units, IDs and missing values. Written for researchers receiving an export. It describes schema version 5, the current one.

For the database internals (every table and migration), see docs/data-model.md. For a shorter overview, see Data model & export. All examples on this page come from the synthetic test fixture that CI exports on every run, not from a real person.

Concepts

Conventions

Missing values

• In CSV, an empty field means NULL. In JSON it is null. There are no sentinel values like -1.
• departure_ts empty: the visit is still going on. Its duration_min then runs to the moment of export.
• mode_user empty: the user never set the mode. mode then equals mode_auto, or unknown.
• purpose empty: no explicit purpose and the destination has no category that implies one.
• Place name empty: an unnamed place. In trips.csv the name column then shows its coordinates, like Place 39.9540, -75.2009.
• Raw sensor fields (altitude, speed, course…) are empty when iOS reported them as invalid.
• A day with no rows is not evidence of no travel. Collection may have been off. See what the sensors miss.

Deleted and merged records

• CSV and GeoJSON files contain only active records: soft-deleted rows (deleted = 1) are left out.
• data.json and travel.sqlite contain everything, including deleted rows. Filter on deleted = 0, or use the v_trips / v_visits views.
• A merged place stays as a tombstone with merged_into set to the place that replaced it.

The export folder

Settings › Create export writes hht-export-YYYYMMDD-HHMMSS/ (the phone's local time) to Files › On My iPhone › TripLibre › Exports:

trips.csv

One row per non-deleted trip, ordered by departure. Example values are from the synthetic fixture.

Other CSV files

visits.csv

visit_id, place_id, place_name, place_category, place_category_custom, arrival_ts, arrival_utc, departure_ts, departure_utc, tz, duration_min, lat, lon, purpose, auto_confidence, source, user_status, point_count, notes. lat/lon is the observed centroid of the stay; point_count the number of fixes in it.

trip_segments.csv

segment_id, trip_id, seq, start_ts, start_utc, end_ts, end_utc, mode, mode_auto, mode_confidence, mode_user, distance_m, route_polyline. seq starts at 0. Segments of a trip are contiguous and don't overlap. A segment with mode_user set and no mode_auto was drawn by hand.

places.csv

place_id, name, category, category_custom, code, lat, lon, radius_m, address, city, region, country, favorite, source, merged_into, notes, created_ts, created_utc, visit_count, first_visit_ts, first_visit_utc, last_visit_ts, last_visit_utc. Address fields are filled only if the user turned on address lookup. code is an IATA or station code.

stamps.csv

stamp_id, ts, time_utc, tz, lat, lon, uncertainty_m, fix_accuracy_m, fix_age_s, visit_id, note. lat/lon are rounded to 3 decimals and empty if there was no fix in the last 10 minutes. visit_id is the visit inference attached the stamp to, empty if none.

expenses.csv

expense_id, ts, time_utc, tz, amount, currency, category, trip_id, visit_id, note. One row per cost the user entered (schema v6). currency is an ISO 4217 code; amounts in different currencies are never converted. amount 0 means the user said it was free (a pass, a lift), which differs from no row at all. category is fare, taxi, fuel, parking, flight, lodging, food, admission, shopping or other. A cost is on a trip_id, a visit_id, or neither (a cost on that day, dated by ts); an id is empty unless it points at an exported trip or visit. ts is the trip's departure, when the stay began (or when it was entered, during an ongoing stay), or the time the user chose.

raw_location_points.csv

id, ts, time_utc, lat, lon, h_accuracy, altitude, v_accuracy, speed, speed_accuracy, course, source, collector_mode, tz, inserted_at. source is gps, significant_change, region_exit, visit_arrival, visit_departure, stationary_marker or imported_gpx. collector_mode (moving / stationary) says whether a silence afterwards was deliberate.

motion_activities.csv

id, ts, time_utc, activity, confidence. activity is stationary, walking, running, cycling, automotive or unknown; confidence 0 (low) to 2 (high). Each row lasts until the next one.

audit_log.csv

id, ts, time_utc, entity_type, entity_id, action, field, old_value, new_value. One row per correction. entity_type: visit, trip, trip_segment, place, flight, stamp. action includes set_mode, set_purpose, set_place, set_times, merge, split, delete, confirm, create. Values are stored as text and may contain place names or IDs.

flights.csv and life_phases.csv

Flights: flight_id (repeated as id), trip_id, date, airport codes and coordinates, times, airline, flight number, aircraft, seat, cabin, distance_m, booking_reference, notes, source. Life phases: id, name, kind, start_date, end_date (inclusive; empty = ongoing), notes.

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. The classifier only produces a subset; see known limitations.

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. Codes are never renamed between versions.

JSON, GeoJSON and SQLite

data.json

One object: format ("hht-personal-travel-diary"), schema_version, exported_at, range (null for everything), then one array per table: places, visits, trips, trip_segments, flights, journeys, journey_members, life_phases, stamps, expenses, audit_log. Rows are the database columns as stored: times are Unix seconds, and trips.purpose is the explicit purpose only (the CSV's purpose is the resolved one).

{"format": "hht-personal-travel-diary", "schema_version": 5, "range": null,
 "trips": [{"id": "25da27b4-…", "departure_ts": 1789473615, "arrival_ts": 1789475265,
            "mode_auto": "walk", "mode_user": "bicycle", "purpose": null, "deleted": 0, …}], …}

GeoJSON

• places.geojson: a Point per active place, with place_id, name, category, visit_count, total_dwell_h and first/last visit.
• trips.geojson: a LineString per trip with a route, with trip_id, person_day_id, trip_seq, mode, purpose, departure_local, arrival_local, distance_m, origin, destination.
• trip_ends.geojson: two Points per trip (end = origin / destination).

travel.sqlite

A full copy of the app's database. Open it with sqlite3, DuckDB, R (DBI), pandas or QGIS. The views v_trips and v_visits leave out deleted rows and add mode_final, purpose_final and duration_s.

Loading in Python

from hht import load

d = load("hht-export-20260915-210000")   # export folder, or a travel.sqlite file
d.trips       # trips.csv, times parsed as UTC
d.segments    # trip_segments.csv
d.visits, d.places, d.flights, d.life_phases
d.stamps      # self-reported stops
d.expenses    # costs entered by the user
d.points      # raw GPS, None if not exported
d.motion      # CoreMotion episodes

analysis/hht.py reads both formats into the same columns. make export-check verifies on every CI run that the CSV and SQLite versions of one export agree. More helpers are on the Developer API page.

Schema versioning

• The database schema version is stored in SQLite's PRAGMA user_version, shown in Settings › Database, and written to data.json and README.txt in every export.
• Migrations only ever add (Schema.swift). Columns and codes are not renamed or removed, so code written for an older export keeps working.
• History: v1 initial schema · v2 places can be deleted · v3 stamps · v4 more place categories · v5 custom place types · v6 costs.
• A formal, machine-readable schema file for exports is Planned.