Tripsy stores all your trip data in a local SQLite file that iCloud syncs to your Mac. You don't need to export anything — the file is already on your disk.

find ~/Library -maxdepth 3 -name "Tripsy.sqlite" 2>/dev/null
# → ~/Library/Group Containers/group.app.tripsy.ios/Tripsy.sqlite

DB="$HOME/Library/Group Containers/group.app.tripsy.ios/Tripsy.sqlite"
sqlite3 "$DB"

Three files form the database:

sqlite3 reads the WAL automatically when you open the main file. You only need to interact with the WAL explicitly when writing — see Pre-write checklist.

Schema overview

Tripsy uses Apple's CoreData. Every table is prefixed Z_ and every column starts with Z. ZGENERALACTIVITY is the hub of the itinerary — one row per timeline item, each pointing to exactly one content table.

Core tables:

Supporting tables:

Junction tables (Z_1EMAILS, Z_4HOSTINGS, Z_3TRANSPORTATIONS) handle many-to-many relationships between emails and itinerary items.

ZGENERALACTIVITY foreign keys:

ZGENERALACTIVITY
  ZTRIP           → ZTRIP.Z_PK
  ZACTIVITY       → ZACTIVITY.Z_PK       (null if not an activity)
  ZHOSTING        → ZHOSTING.Z_PK        (null if not a hotel)
  ZTRANSPORTATION → ZTRANSPORTATION.Z_PK (null if not transport)
  ZDATE           → timeline sort key (CoreData timestamp)

Exactly one of ZACTIVITY, ZHOSTING, ZTRANSPORTATION is non-null per row.

Timestamps

CoreData stores timestamps as seconds since January 1, 2001 — not the Unix epoch (January 1, 1970). The offset is 978,307,200 seconds.

Reading:

datetime(ZSTARTS + 978307200, 'unixepoch')           -- UTC
datetime(ZSTARTS + 978307200, 'unixepoch', '+8 hours') -- MYT / SGT
datetime(ZSTARTS + 978307200, 'unixepoch', '+9 hours') -- JST

Writing — convert local time to CoreData:

import datetime

apple_epoch = datetime.datetime(2001, 1, 1, tzinfo=datetime.timezone.utc)

def to_coredata(year, month, day, hour, minute=0, utc_offset=8):
    """Pass local time. utc_offset = hours ahead of UTC (8 for MYT/SGT, 9 for JST)."""
    dt = (datetime.datetime(year, month, day, hour, minute,
                            tzinfo=datetime.timezone.utc)
          - datetime.timedelta(hours=utc_offset))
    return int((dt - apple_epoch).total_seconds())

to_coredata(2026, 3, 18, 10, 0, utc_offset=8)  # 10:00 AM MYT → 795492000
to_coredata(2026, 3, 18, 10, 0, utc_offset=9)  # 10:00 AM JST → 795488400

Always verify before inserting:

SELECT datetime(795492000 + 978307200, 'unixepoch', '+8 hours');
-- → 2026-03-18 10:00:00  ✓

Reading data

All trips

SELECT
  ZNAME,
  date(ZSTARTS + 978307200, 'unixepoch') AS start_date,
  date(ZENDS   + 978307200, 'unixepoch') AS end_date,
  ZPLANNEDNUMBEROFDAYS AS planned_days
FROM ZTRIP
ORDER BY ZSTARTS DESC;

Full itinerary for a trip

SELECT
  date(ga.ZDATE + 978307200, 'unixepoch') AS date,
  time(ga.ZDATE + 978307200, 'unixepoch') AS time,
  CASE
    WHEN ga.ZACTIVITY       IS NOT NULL THEN a.ZINTERNALTYPE
    WHEN ga.ZTRANSPORTATION IS NOT NULL THEN tr.ZINTERNALTYPE
    ELSE 'hotel'
  END AS type,
  COALESCE(a.ZNAME, h.ZNAME, tr.ZNAME) AS name,
  COALESCE(
    a.ZADDRESS,
    h.ZADDRESS,
    tr.ZDEPARTUREDESCRIPTION || ' → ' || tr.ZARRIVALDESCRIPTION
  ) AS detail
FROM ZGENERALACTIVITY ga
LEFT JOIN ZACTIVITY       a  ON ga.ZACTIVITY       = a.Z_PK
LEFT JOIN ZHOSTING        h  ON ga.ZHOSTING        = h.Z_PK
LEFT JOIN ZTRANSPORTATION tr ON ga.ZTRANSPORTATION = tr.Z_PK
WHERE ga.ZTRIP = (SELECT Z_PK FROM ZTRIP WHERE ZNAME LIKE '%Japan%')
ORDER BY ga.ZDATE;

Flights

SELECT
  t.ZNAME AS trip,
  datetime(tr.ZDEPARTURE + 978307200, 'unixepoch') AS departs,
  tr.ZDEPARTUREDESCRIPTION AS from_airport,
  tr.ZARRIVALDESCRIPTION   AS to_airport,
  tr.ZTRANSPORTNUMBER      AS flight_number,
  tr.ZSEATCLASS AS cabin,
  tr.ZSEATNUMBER AS seat
FROM ZGENERALACTIVITY ga
JOIN ZTRANSPORTATION tr ON ga.ZTRANSPORTATION = tr.Z_PK
JOIN ZTRIP t            ON ga.ZTRIP = t.Z_PK
WHERE tr.ZINTERNALTYPE = 'airplane'
ORDER BY tr.ZDEPARTURE DESC;

ZDEPARTUREDESCRIPTION and ZARRIVALDESCRIPTION hold IATA airport codes for flights (SFO, LHR) and station names for trains. ZACTUALTRANSPORTNUMBER holds the operated-by carrier when different from the scheduled number.

Hotels

SELECT
  t.ZNAME AS trip,
  h.ZNAME AS hotel,
  date(h.ZSTARTS + 978307200, 'unixepoch') AS check_in,
  date(h.ZENDS   + 978307200, 'unixepoch') AS check_out,
  h.ZRESERVATIONCODE AS confirmation,
  h.ZROOMTYPE AS room_type
FROM ZGENERALACTIVITY ga
JOIN ZHOSTING h ON ga.ZHOSTING = h.Z_PK
JOIN ZTRIP    t ON ga.ZTRIP    = t.Z_PK
ORDER BY h.ZSTARTS DESC;

ZHOSTING.ZNOTES stores the full booking text — room details, cancellation policy — as parsed from the confirmation email. ZROOMNUMBER and ZROOMTYPE are populated only when entered manually in the app.

Parsed booking emails

Emails forwarded to Tripsy are stored in ZEMAIL. Junction tables link each email to its itinerary item:

-- Emails linked to hotel bookings for a trip
SELECT e.ZSUBJECT, datetime(e.ZDATE + 978307200, 'unixepoch') AS received
FROM ZEMAIL e
JOIN Z_4HOSTINGS jeh ON jeh.Z_4EMAILS2 = e.Z_PK
WHERE jeh.Z_8HOSTINGS1 IN (
  SELECT ga.ZHOSTING FROM ZGENERALACTIVITY ga
  WHERE ga.ZTRIP = (SELECT Z_PK FROM ZTRIP WHERE ZNAME LIKE '%Japan%')
    AND ga.ZHOSTING IS NOT NULL
);

Multi-currency

Each item stores its own price and currency independently. No conversion happens at query time.

SELECT
  COALESCE(a.ZINTERNALTYPE, tr.ZINTERNALTYPE, 'hotel') AS type,
  COALESCE(a.ZNAME, h.ZNAME,
    tr.ZDEPARTUREDESCRIPTION || '→' || tr.ZARRIVALDESCRIPTION) AS name,
  COALESCE(a.ZPRICE,    h.ZPRICE,    tr.ZPRICE)    AS price,
  COALESCE(a.ZCURRENCY, h.ZCURRENCY, tr.ZCURRENCY) AS currency
FROM ZGENERALACTIVITY ga
LEFT JOIN ZACTIVITY       a  ON ga.ZACTIVITY       = a.Z_PK
LEFT JOIN ZHOSTING        h  ON ga.ZHOSTING        = h.Z_PK
LEFT JOIN ZTRANSPORTATION tr ON ga.ZTRANSPORTATION = tr.Z_PK
WHERE ga.ZTRIP = (SELECT Z_PK FROM ZTRIP WHERE ZNAME LIKE '%Japan%')
  AND COALESCE(a.ZPRICE, h.ZPRICE, tr.ZPRICE) > 0;

Travel companions

SELECT ZNAME, ZEMAIL FROM ZTRIPGUEST;

ZGUESTSIDS on activities, hostings, and transportation stores which guests are included, as a comma-separated string of integer IDs matching ZTRIPGUEST.ZID.

Geocoded locations

SELECT ZCOUNTRYNAME, ZCOUNTRYCODE, COUNT(*) AS lookups
FROM ZGEOCODEDLOCATION
GROUP BY ZCOUNTRYCODE
ORDER BY lookups DESC;

ZLATITUDE and ZLONGITUDE are also stored directly on ZACTIVITY and ZHOSTING rows — every place with coordinates is queryable for GeoJSON export without touching ZGEOCODEDLOCATION.

Writing to the database

Direct SQLite inserts appear in Tripsy on next launch. CoreData reads the local store on startup, and inserts are not overridden by CloudKit sync. Tripsy does not use NSPersistentCloudKitContainer — it has custom CloudKit sync code, confirmed by no CoreData history tracking tables in the store and an empty CloudSync.plist. Direct SQLite writes bypass that layer.

Pre-write checklist

# 1. Quit Tripsy
osascript -e 'tell application "Tripsy" to quit'

# 2. Confirm it's closed (must return no output)
pgrep -x "Tripsy"

# 3. Back up the database directory
cp -r ~/Library/Group\ Containers/group.app.tripsy.ios \
      ~/Desktop/Tripsy_backup_$(date +%Y-%m-%d)

# 4. After all inserts, merge the WAL
sqlite3 "$DB" "PRAGMA wal_checkpoint(TRUNCATE);"
# → 0|0|0  means clean (busy_writers | log_frames | checkpointed_frames)

CoreData field reference

Every inserted row needs these fields set correctly. Wrong values are silently ignored by Tripsy.

Z_ENT — entity type number, required on every row:

Z_OPT — optimistic locking counter. Set to 1 on new rows. Tripsy increments it on each save through the UI. Do not set it high — Tripsy uses this to detect stale writes.

ZID — internal integer, purpose unclear. Use 0 for all manual inserts. Generating realistic-looking sequential values risks collisions with Tripsy's own counter.

ZOWNERID — must match the owner of the trip. Query it from any existing row in the same trip rather than guessing:

SELECT DISTINCT ZOWNERID FROM ZACTIVITY a
JOIN ZGENERALACTIVITY ga ON ga.ZACTIVITY = a.Z_PK
WHERE ga.ZTRIP = <your_trip_z_pk>;

ZTIMEZONE — must be a valid IANA timezone name matching the physical location of the activity. Both Malaysia and Singapore are UTC+8, but they use different identifiers:

ZINTERNALIDENTIFIER — unique identifier, ~20 alphanumeric characters, no hyphens, underscores, or spaces. Existing values look like z2wxvvERr5khQPOfUMnQ. Generate one per row using a mix of the item name, location, and a few random characters.

Primary key management

Z_PRIMARYKEY tracks the current maximum PK per entity. Query it immediately before inserting and update it after.

-- Read current max before inserting
SELECT Z_NAME, Z_MAX FROM Z_PRIMARYKEY
WHERE Z_NAME IN ('Activity', 'Transportation', 'GeneralActivity', 'Hosting');

-- Update after inserting
UPDATE Z_PRIMARYKEY SET Z_MAX = <new_max> WHERE Z_NAME = 'Activity';

CloudKit tombstones

When Tripsy deletes a row — either internally or via CloudKit sync — it stores a tombstone for that ZINTERNALIDENTIFIER. Reinserting any row with a tombstoned identifier causes Tripsy to silently delete it again on next sync.

Sequence to diagnose a tombstoned insert:

1. You insert a row, verify it in SQLite, checkpoint the WAL
2. Open Tripsy — row appears ✓
3. Close and reopen Tripsy — row is gone
4. Z_MAX has advanced past your PK, but no row exists there

Activity types

ZINTERNALTYPE controls how Tripsy displays and categorizes each item.

ZACTIVITY types:

ZTRANSPORTATION types:

Inserting an activity

Every insert requires two rows: one in the content table and one in ZGENERALACTIVITY linking it to the trip.

BEGIN TRANSACTION;

INSERT INTO ZACTIVITY (
  Z_PK, Z_ENT, Z_OPT,
  ZCHECKED, ZHIDDEN, ZID, ZOWNERID,
  ZCREATEDAT, ZUPDATEDAT,
  ZSTARTS, ZENDS,
  ZLATITUDE, ZLONGITUDE,
  ZINTERNALTYPE, ZNAME,
  ZADDRESS, ZTIMEZONE,
  ZINTERNALIDENTIFIER, ZOWNERNAME,
  ZISALLDAY
) VALUES (
  <next_pk>, 1, 1,
  0, 0, 0, <ZOWNERID>,
  <now_coredata>, <now_coredata>,
  <start_coredata>, <end_coredata>,
  48.8738, 2.3320,
  'general', 'Conference Day 1',
  '11 Rue Richer, 75009 Paris, France', 'Europe/Paris',
  'ConfDay1Paris2024xQm7Rk', 'Traveler Name',
  0
);

INSERT INTO ZGENERALACTIVITY (
  Z_PK, Z_ENT, Z_OPT,
  ZACTIVITY, ZHOSTING, ZTRANSPORTATION, ZTRIP,
  ZDATE, ZINTERNALTYPE
) VALUES (
  <next_ga_pk>, 6, 1,
  <activity_pk>, NULL, NULL, <trip_z_pk>,
  <start_coredata>, 'general'
);

UPDATE Z_PRIMARYKEY SET Z_MAX = <next_pk>    WHERE Z_NAME = 'Activity';
UPDATE Z_PRIMARYKEY SET Z_MAX = <next_ga_pk> WHERE Z_NAME = 'GeneralActivity';

COMMIT;

ZGENERALACTIVITY.ZDATE is the sort key for the itinerary timeline — set it to the activity's start time. Two activities can share the same ZDATE; Tripsy allows overlapping events.

Inserting a flight

BEGIN TRANSACTION;

INSERT INTO ZTRANSPORTATION (
  Z_PK, Z_ENT, Z_OPT,
  ZHIDDEN, ZID, ZOWNERID, ZRECEIVEUPDATES,
  ZDEPARTURE, ZARRIVAL,
  ZCREATEDAT, ZUPDATEDAT,
  ZDEPARTURELATITUDE, ZDEPARTURELONGITUDE,
  ZARRIVALLATITUDE,   ZARRIVALLONGITUDE,
  ZDISTANCEINMETERS,
  ZDEPARTUREDESCRIPTION, ZARRIVALDESCRIPTION,
  ZDEPARTURETIMEZONE,    ZARRIVALTIMEZONE,
  ZINTERNALTYPE,
  ZTRANSPORTNUMBER, ZCOMPANY, ZCURRENCY,
  ZRESERVATIONCODE, ZOWNERNAME,
  ZINTERNALIDENTIFIER
) VALUES (
  <next_pk>, 9, 1,
  0, 0, <ZOWNERID>, 0,
  <departure_coredata>, <arrival_coredata>,
  <now_coredata>, <now_coredata>,
  37.6189, -122.3748,     -- departure airport lat/lng (SFO)
  51.4775, -0.4613,       -- arrival airport lat/lng (LHR)
  8767000,                -- distance in meters
  'JFK', 'LHR',
  'America/New_York', 'Europe/London',
  'airplane',
  'BA178', 'British Airways', 'USD',
  'ABC123', 'Traveler Name',
  'JFKLHRba178xMp7Qr2024'
);

INSERT INTO ZGENERALACTIVITY (
  Z_PK, Z_ENT, Z_OPT,
  ZACTIVITY, ZHOSTING, ZTRANSPORTATION, ZTRIP,
  ZDATE, ZINTERNALTYPE
) VALUES (
  <next_ga_pk>, 6, 1,
  NULL, NULL, <transport_pk>, <trip_z_pk>,
  <departure_coredata>, 'general'
);

UPDATE Z_PRIMARYKEY SET Z_MAX = <next_pk>    WHERE Z_NAME = 'Transportation';
UPDATE Z_PRIMARYKEY SET Z_MAX = <next_ga_pk> WHERE Z_NAME = 'GeneralActivity';

COMMIT;

ZGENERALACTIVITY.ZDATE should match the departure timestamp. ZDEPARTURETIMEZONE and ZARRIVALTIMEZONE take IANA timezone names — use the physical timezone at each airport, not UTC.

Inserting a hotel stay

BEGIN TRANSACTION;

INSERT INTO ZHOSTING (
  Z_PK, Z_ENT, Z_OPT,
  ZCHECKED, ZHIDDEN, ZID, ZOWNERID,
  ZCREATEDAT, ZUPDATEDAT,
  ZSTARTS, ZENDS,
  ZLATITUDE, ZLONGITUDE,
  ZNAME, ZADDRESS,
  ZTIMEZONE, ZINTERNALIDENTIFIER, ZOWNERNAME
) VALUES (
  <next_pk>, 8, 1,
  0, 0, 0, <ZOWNERID>,
  <now_coredata>, <now_coredata>,
  <checkin_coredata>, <checkout_coredata>,
  35.6762, 139.6503,
  'Shinjuku Hotel', '1-2-3 Shinjuku, Tokyo 160-0022, Japan',
  'Asia/Tokyo', 'ShinjukuHotelTky8xQ25', 'Traveler Name'
);

INSERT INTO ZGENERALACTIVITY (
  Z_PK, Z_ENT, Z_OPT,
  ZACTIVITY, ZHOSTING, ZTRANSPORTATION, ZTRIP,
  ZDATE, ZINTERNALTYPE
) VALUES (
  <next_ga_pk>, 6, 1,
  NULL, <hosting_pk>, NULL, <trip_z_pk>,
  <checkin_coredata>, 'general'
);

UPDATE Z_PRIMARYKEY SET Z_MAX = <next_pk>    WHERE Z_NAME = 'Hosting';
UPDATE Z_PRIMARYKEY SET Z_MAX = <next_ga_pk> WHERE Z_NAME = 'GeneralActivity';

COMMIT;

After inserting

Checkpoint the WAL:

PRAGMA wal_checkpoint(TRUNCATE);
-- → 0|0|0 means the WAL was fully merged into the main file
-- → 0|N|N means N frames were checkpointed (also fine)
-- → 1|N|M means a writer is still active — Tripsy may still be running

"Activities beyond trip dates": If ZGENERALACTIVITY.ZDATE falls outside the trip's ZSTARTS/ZENDS range, Tripsy shows a banner when you next open the trip: "Activities beyond trip dates — Do you want to update the trip dates?" Tap "Update Trip Dates." This banner also confirms the insert was read by CoreData.

Syncing to iOS

Inserted rows appear in Tripsy on Mac immediately (CoreData reads the local store on launch). They do not appear on iPhone until Tripsy pushes them to CloudKit.

To trigger the push: open each inserted item in Tripsy on Mac and save it. Any save through the UI registers the row as a local change and pushes the full record to CloudKit, which then syncs to iPhone.

Diagnosing failed inserts