Reading and Writing Tripsy's SQLite Database on macOS

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 its iCloud-synced SQLite database was already on my Mac. I worked out the schema so an agent could update it without guessing at CoreData’s conventions.

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, 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.

---

What I observed when writing to the database

These are unsupported experiments on the app version I used, not a guaranteed sync contract. Use the official CLI for edits now. The SQL below documents the experiment; do not run it against your only copy. Quit the app and preserve the complete database directory, including WAL files, before inspecting a copy. Read-only queries can still disclose private itineraries and guest details.

Direct SQLite inserts appear in Tripsy when you next launch it — CoreData reads the local store on startup. In my tests, new rows survived sync and direct updates to existing rows reverted.

I did not find CoreData history tracking tables in the store. That observation alone does not establish the app’s sync implementation. Changing ZUPDATEDAT did not prevent my edits from reverting. Once a row has been pushed to CloudKit, Tripsy treats the cloud copy as authoritative and overwrites any direct UPDATE on the next open.

Quit the app before copying or writing Always quit Tripsy before writing. Concurrent direct writes can conflict with the app’s persistence and sync behavior. A successful SQL statement does not establish that the app will retain it.

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

UPDATE gets reverted on any row that CloudKit has already seen. I tried deleting and reinserting with a fresh ZINTERNALIDENTIFIER. That can lose relationships or trigger cloud deletions; it is not a general repair method. Use the app or CLI to fix existing records.

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. Appending v2 to the identifier is enough: ExampleRecord2024v2.

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. Set to 0 for all manual inserts. Sequential values risk 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. An illustrative value is ExampleRecord2024xQm7; it is not an identifier from my store. Derive it from the item name, location, and a few random characters — one per row.

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, whether 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.

Never reuse a tombstoned identifier This is the most confusing failure mode. The row appears in Tripsy on first launch, then disappears after a second open/close. No error is shown. The fix: always generate a brand-new identifier when reinserting a previously deleted row. Never reuse one.

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 needs 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>,
  40.6413, -73.7781,      -- departure airport lat/lng (JFK)
  51.4775, -0.4613,       -- arrival airport lat/lng (LHR)
  5540000,                -- approximate 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.

Flights need real coordinates Both ZDEPARTURELATITUDE/ZDEPARTURELONGITUDE and ZARRIVALLATITUDE/ZARRIVALLONGITUDE must be set to real coordinates. If either pair is 0.0 or NULL, Tripsy reverse-geocodes the coordinates and displays whatever city happens to be nearest. The flight shows as “Anacortes to Anacortes” or some other wrong city entirely unrelated to the route. Airport lat/lng is easy to copy from any other flight in the same database that stops at that airport. Query SELECT ZDEPARTURELATITUDE, ZDEPARTURELONGITUDE FROM ZTRANSPORTATION WHERE ZDEPARTUREDESCRIPTION = 'SIN' LIMIT 1.

Inserting a hotel stay

BEGIN TRANSACTION;

INSERT INTO ZHOSTING (
  Z_PK, Z_ENT, Z_OPT,
  ZHIDDEN, ZID, ZOWNERID,
  ZCREATEDAT, ZUPDATEDAT,
  ZSTARTS, ZENDS,
  ZLATITUDE, ZLONGITUDE,
  ZNAME, ZADDRESS,
  ZTIMEZONE, ZINTERNALIDENTIFIER, ZOWNERNAME
) VALUES (
  <next_pk>, 8, 1,
  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. They don’t reach 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 it to CloudKit, and syncs to iPhone. It also locks in the data — once Tripsy has pushed the record, the cloud copy matches and nothing reverts it.

SQLite writes do not become CloudKit writes Rows that are never touched through the Tripsy UI are never pushed to CloudKit. They stay Mac-only indefinitely. More importantly: if you insert a row with wrong data and Tripsy pushes that incorrect record to CloudKit before you can fix it, a subsequent UPDATE in SQLite will be reverted. Delete and re-insert with a new identifier instead.

---

Keep itinerary examples private

I removed the worked itinerary from this post because it exposed travel dates, lodging details, and account identifiers. The placeholder inserts above explain the relationships without publishing a real trip. Give an assistant only the rows it needs, and remove booking references, guest emails, and owner identifiers before sharing query output.

---

Failures I observed in the experiment

The rows I checked survived subsequent CloudKit sync — as long as each one was opened through the Tripsy UI afterward. The ones I skipped are still Mac-only.