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.

How to use this

This post is a reference document. The intended workflow is to give it to Claude Code, Cursor, or any agentic coding tool at the start of a session:

"Read this post: [URL]. Then help me add my missing flights from this KAYAK itinerary."

The agent reads the schema, timestamp format, field rules, and pre-write checklist, then generates and runs the correct SQL. You review the output and open Tripsy — done in minutes instead of figuring out the CoreData format by hand.

The database is at a fixed path. Three files travel together:

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

sqlite3 reads the WAL automatically. You only interact with it 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  ✓

Off-by-one-year errors produce valid-looking dates in the wrong year and are easy to miss. Always confirm both the date and the year. Cross-check against a nearby existing row's ZSTARTS — if a known event is March 16 and your new event is March 18, their CoreData values should differ by roughly 2 × 86400 = 172800.

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 when you next launch it — CoreData reads the local store on startup. New rows survive CloudKit sync. Updates to existing rows do not.

Tripsy's CloudKit sync does not follow the standard NSPersistentCloudKitContainer pattern — there are no CoreData history tracking tables in the store. Conflict resolution uses CloudKit's server-side modifiedAt, not the local SQLite ZUPDATEDAT field. Once a row has been pushed to CloudKit, Tripsy treats the cloud copy as authoritative. Any UPDATE you write directly to SQLite is overwritten on the next Tripsy open.

This means:

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)

Fixing data in existing rows

Because UPDATE gets reverted, the only way to correct data in a row that's already been synced to CloudKit is to delete it and re-insert it as a new row with a fresh ZINTERNALIDENTIFIER.

BEGIN;

-- 1. Delete the GA entry
DELETE FROM ZGENERALACTIVITY WHERE ZTRANSPORTATION = <old_pk>;

-- 2. Delete the content row
DELETE FROM ZTRANSPORTATION WHERE Z_PK = <old_pk>;

-- 3. Re-insert with a new PK, new ZINTERNALIDENTIFIER, and correct data
INSERT INTO ZTRANSPORTATION (...) VALUES (<new_pk>, ..., 'NewIdentifierv2', ...);
INSERT INTO ZGENERALACTIVITY (...) VALUES (<new_ga_pk>, ..., <new_pk>, ...);

-- 4. Advance Z_PRIMARYKEY
UPDATE Z_PRIMARYKEY SET Z_MAX = <new_pk>    WHERE Z_NAME = 'Transportation';
UPDATE Z_PRIMARYKEY SET Z_MAX = <new_ga_pk> WHERE Z_NAME = 'GeneralActivity';

COMMIT;

When Tripsy next opens, it sends a deletion tombstone for the old identifier and registers the new row as a record CloudKit has never seen. Nothing reverts it.

Appending v2 to the identifier is enough: MU546SINPVGSep2023kTv2. The old identifier is tombstoned; the new one is clean.

CoreData field reference

Every inserted row needs these fields set correctly. A wrong Z_ENT or missing ZOWNERID causes the row to be ignored or misclassified without any error.

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. Setting it higher than 1 on a new row has no effect on whether CloudKit accepts or reverts the record — CloudKit uses its own server-side version, not Z_OPT.

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.

Z_MAX can jump by large amounts between sessions. Tripsy creates rows internally during CloudKit sync and then immediately deletes many of them, but Z_MAX still advances for each one — a jump of 20–50 in a single open/close cycle is normal. Never reuse a value you read earlier. Re-query every time.

-- 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:

Insert activities with ZINTERNALTYPE = 'general' and change the type in the Tripsy UI afterward. It's faster than looking up the exact string, and the UI type change also triggers a CloudKit push.

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

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." That 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.

Open each inserted item in Tripsy on Mac and save it. Any UI save registers the row as a local change, pushes the full record to CloudKit, and syncs it to iPhone. That touch also protects the row — once Tripsy has pushed your insert to CloudKit, the cloud copy matches and there is nothing to revert.

Diagnosing failed inserts

Every row I've inserted using this approach has survived months of CloudKit sync untouched — as long as I opened each one through the Tripsy UI afterward. The ones I skipped opening are still Mac-only.