Writing

iOS · 8 min read

Inspecting Tripsy's SQLite Database on macOS

Read-only queries against the local Core Data store I inspected in March 2026, and why direct writes were not a reliable integration.

In this article7 sections

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 needBest route todayDirect SQLite write?Main risk
Add or update itinerary dataOfficial Tripsy MCP or CLINoLowest-risk supported workflow
Inspect, query, or export local datasqlite3 against a backupNoMisreading CoreData timestamps or relationships
Repair data the API cannot reachAsk Tripsy supportAvoidWAL corruption, broken relationships, or sync overwrite
Understand Tripsy’s local schemaSchema and query sections belowNoQuery 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:

TablePurpose
ZTRIPOne row per trip
ZGENERALACTIVITYItinerary timeline — links each item to a trip
ZACTIVITYPlaces: restaurants, cafes, parks, museums, events
ZHOSTINGHotels and accommodations
ZTRANSPORTATIONFlights, trains, car rentals, road trips

Supporting tables:

TablePurpose
ZGEOCODEDLOCATIONCached geocoding results
ZEMAILForwarded booking confirmation emails
ZTRIPGUESTTravel companions
ZTRIPEXPENSETrip expenses
ZDOCUMENTAttached documents
ZCUSTOMCATEGORYUser-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 tableLinks ZEMAIL to
Z_1EMAILSZACTIVITY
Z_4HOSTINGSZHOSTING
Z_3TRANSPORTATIONSZTRANSPORTATION
-- 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?

What was missing?

Thanks for the feedback.