Use the supported path Tripsy released an official MCP server and CLI in May 2026. This post records unsupported experiments; the API is now the right default.
This started because I needed to add missing flights from a KAYAK itinerary. Tripsy had no public CLI at the time, but a local SQLite store containing itinerary records was already on my Mac. I explored the schema to understand the records. My later write experiments showed why that knowledge was not enough for a reliable integration.
| What you need | Best route today | Direct SQLite write? | Main risk |
|---|---|---|---|
| Add or update itinerary data | Official Tripsy MCP or CLI | No | Lowest-risk supported workflow |
| Inspect, query, or export local data | sqlite3 against a backup | No | Misreading CoreData timestamps or relationships |
| Repair data the API cannot reach | Ask Tripsy support | Avoid | WAL corruption, broken relationships, or sync overwrite |
| Understand Tripsy’s local schema | Schema and query sections below | No | Query output can reveal private travel details |
Inspect a copy with an agent
Ask an agent to explain queries against a backup, not to generate and run writes against your live itinerary. Review which rows it can read: travel dates, guest emails, and booking references remain private even in a read-only session.
The app directory I inspected was ~/Library/Group Containers/group.app.tripsy.ios/. Quit Tripsy, copy that complete directory to a private backup location, and open the copy read-only:
DB="/path/to/private-backup/Tripsy.sqlite"
sqlite3 -readonly "$DB"
Keep Tripsy.sqlite, Tripsy.sqlite-wal, and Tripsy.sqlite-shm together. The schema below is the one I inspected in March 2026; app upgrades may change it.
Schema overview
Tripsy uses Apple’s CoreData. The CoreData entity tables and columns start with Z; the application’s names and relationships below are observations from this store. ZGENERALACTIVITY is the hub of the itinerary — one row per timeline item, linked to the content tables shown below in the records I inspected.
Core tables:
| Table | Purpose |
|---|---|
ZTRIP | One row per trip |
ZGENERALACTIVITY | Itinerary timeline — links each item to a trip |
ZACTIVITY | Places: restaurants, cafes, parks, museums, events |
ZHOSTING | Hotels and accommodations |
ZTRANSPORTATION | Flights, trains, car rentals, road trips |
Supporting tables:
| Table | Purpose |
|---|---|
ZGEOCODEDLOCATION | Cached geocoding results |
ZEMAIL | Forwarded booking confirmation emails |
ZTRIPGUEST | Travel companions |
ZTRIPEXPENSE | Trip expenses |
ZDOCUMENT | Attached documents |
ZCUSTOMCATEGORY | User-defined categories |
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)
In the itinerary rows I inspected, one of ZACTIVITY, ZHOSTING, or ZTRANSPORTATION identified the content record. I have not verified this as an invariant for every activity type or app version.
Timestamps
The date fields inspected here use 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
Understanding a timestamp — fixed-offset example:
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
Verify the conversion:
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. For two instants exactly 48 hours apart, the stored values differ by 2 × 86400 = 172800. Local calendar dates alone do not establish that interval across a daylight-saving transition.
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. Treat ZACTUALTRANSPORTNUMBER as an observed field name, not a documented carrier-mapping contract.
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. I have not established every path that can populate ZROOMNUMBER and ZROOMTYPE.
Parsed booking emails
Emails forwarded to Tripsy are stored in ZEMAIL. Junction tables link each email to its itinerary item:
| Junction table | Links ZEMAIL to |
|---|---|
Z_1EMAILS | ZACTIVITY |
Z_4HOSTINGS | ZHOSTING |
Z_3TRANSPORTATIONS | ZTRANSPORTATION |
-- 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, ZCOUNTRYNAME
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.
What the write experiment established
In my March 2026 tests, some inserted rows appeared in Tripsy, while direct updates to existing rows reverted. Some items appeared on my iPhone after I opened and saved them in the Mac app. Those observations did not establish a general sync contract.
I previously attributed these results to specific CloudKit conflict rules, tombstones, and server-side versions. I did not verify those mechanisms. A disappearing row cannot tell me, by itself, why the app rejected it. A successful save also cannot guarantee that later sync will preserve it.
Apple documents Core Data’s SQLite format as private and advises against manipulating its stores through the native SQLite API. Knowing the visible columns does not establish the invariants the app needs when it saves or syncs.
I have therefore removed the direct-write and delete/reinsert recipes from this guide. In particular, I cannot substantiate that setting ZID to zero is safe, that appending v2 repairs a record, or that saving through the UI makes an inserted row permanent. Use Tripsy’s supported interface for edits and support for records it cannot repair.
Reading dates carefully
The queries above return UTC unless they explicitly apply an offset. A hotel’s UTC date can differ from its local check-in date. For locations with daylight saving time, a fixed offset is not a year-round timezone rule; convert using the record’s IANA timezone and the date being inspected.
Keep itinerary examples private
Travel dates, lodging details, booking references, guest emails, and owner identifiers remain private even in query output. Share only the fields needed to explain a result. The schema here is a dated observation, not a supported Tripsy API.
Was this useful?
Thanks for the feedback.