Accessing / Exporting Apple's Reminders Data on macOS

Some notes on accessing / exporting Apple's Reminders data on macOS.

General
Unsorted
See Also

General

https://github.com/keith/reminders-cli
- A simple CLI for interacting with macOS reminders

Unsorted

After some investigation it seems that Tags themselves aren't exposed in the API for the EKReminders class

When you say 'tags', what specifically are you referring to? Do you mean the specific 'hashtags' tags assigned to a reminder? Or are you referring to something else?

The rules for smart lists can have many different aspects, not just tags, so even if they aren't currently accessible, the others may be:

Originally posted by @0xdevalias in keith/reminders-cli#72 (comment)

It looks like the underlying sqlite database backing the Reminders app is stored at:

/Users/devalias/Library/Reminders/Container_v1/Stores/Data-SOME-UUID-TYPE-THING.sqlite

Within that I can see the tags information in the ZREMCDHASHTAGLABEL table.

The ZREMCDOBJECT table seems to contain a lot of data, possibly related to the reminders themselves? The ZNAME1 column seems to include some of the tags as well.

The ZREMCDREMINDER table seems to have a lot of the actual reminder data, and seemingly foreign keys to link to some of the other tables.

While I wouldn't personally risk writing to this DB for fear of corrupting it or similar; it might be possible to extract some relevant details from it in a 'read only' mode, that could then be used to filter the reminders returned from the official API's 'in app'.

Originally posted by @0xdevalias in keith/reminders-cli#72 (comment)

In /Users/devalias/Library/Reminders/Container_v1/Stores/Data-SOME-UUID-TYPE-THING.sqlite:

In the ZREMCDHASHTAGLABEL table:

Z_ENT: seems to be 3 for all of the hashtag entries

ZNAME / ZCANONICALNAME: seem to contain the text of my hashtags

In the ZREMCDREMINDER table:

Z_PK: ?reminder primary key?

Z_ENT: seems to be 32 for all of the reminder entries

ZCOMPLETED: 1 for completed, 0 for not completed

ZFLAGGED: ?probably 1 if flagged, otherwise 0?

ZPRIORITY: ?reminder priority?

ZLIST: ?ID of list the reminder relates to?

ZTITLE: Main reminder text

ZNOTES: Reminder notes

etc

In the ZREMCDOBJECT table, columns such as the following look potentially useful:

ZCKIDENTIFIER: ?some sort of UUID?

ZREMINDERIDENTIFIER: ?some sort of UUID?

ZREMINDER3: ?might contain the ID of the reminder the row relates to?

ZHASHTAGLABEL: seems to contain the PK of the hashtag from ZREMCDHASHTAGLABEL

ZDATECOMPONENTSDATA: ?stuff related to the reminder date?

Z_ENT:

30: seems to correlate to the smart lists

Z_FOK_PARENTLIST1: ?Foreign key for the associated parent list?

ZBADGEEMBLEM1: Contains data like: {"Emoji" : "🎥"} that I assigned to the smartlists

ZNAME3: This seems to contain the name I assigned to the smartlists

ZSMARTLISTTYPE: seems to contain things like:

com.apple.reminders.smartlist.today

com.apple.reminders.smartlist.assigned

com.apple.reminders.smartlist.custom

com.apple.reminders.smartlist.flagged

ZSORTINGSTYLE1: eg. manual

ZFILTEREDDATA: json data relating to the smartlist config; eg:

{"hashtags":{"hashtags":["health"]}}

{"date":{"relativeRange":["inNext","1","week"]}}

{"hashtags":{"hashtags":{"include":["to-watch","youtube-aaa","youtube-bbb","youtube-ccc","youtube-ddd"],"exclude":[],"operation":"or"}},"date":{"any":""},"operation":"and"}

26: ???

ZLISTID: seems to contain things like:

com.apple.reminders.smartlist.today

Or sometimes a UUID (eg. in my data: BA189B19-F050-43FD-A76F-115415ED91A2 / 5C703F5C-7ED3-4AC3-B064-7F9C1E01AA95)

25: seems to correlate to normal lists

ZBADGEEMBLEM seems to be the image I assigned to the normal lists (eg. {"Emoji" : "💲"})

ZNAME2 seems to be the name of the normal list

ZSORTINGSTYLE: eg. manual

24: seems to (at least partially?) correlate to hashtags for reminders

ZNAME1: seems to contain the text of a hashtag for the reminder

22: ???

21: ???

18: ?URL related?

Looks like ZUTI (eg. public.url), ZURL, etc; may be related to this..

ZURL: reminder URL field

17: ?attachment related?

Looks like ZUTI (eg. public.jpeg), ZFILENAME (eg. 24B9CB35-CC7F-45F0-B52F-8BED9C2F2769-732-00055908B19E5135.jpeg), ZSHA512SUM, etc; may be related to this..

10: ?location related?

Looks like ZLATITUDE / ZLONGITUDE / ZADDRESS / ZLOCATIONUID / ZTITLE etc are related to this

9: ?reminder date/time related?

Looks like ZALARM / ZDATECOMPONENTSDATA / etc may be related to this

7: ???

Looks like ZREMINDER / ZTRIGGER / Z8TRIGGER / etc may be related to this

6: ?settings/flags related?

I only seem to have a single entry for this type, and it seems to correlate with fields like ZDAALLOWSCALENDARADDDELETEMODIFY / ZDASUPPORTSSHAREDCALENDARS / etc (seemingly at least 10 fields like this seem to correlate with it), as well as ZCKUSERRECORDNAME, ZNAME (iCloud), ZPERSONID (PRIMARY-CLOUDKIT)

That seems to be enough basic info to figure out resolving both this issue, and maybe also #72

Would just need to figure out how to match up the ID/data that the API is currently able to provide, with an ID that can be looked up in the sqlite database; and then extract the associated hashtags/etc.

I was thinking that maybe externalId from reminders show Reminders --sort creation-date --sort-order ascending --format json might have worked.. but it only seems to show up in ZREMCDOBJECT within some fields with LOTS of other IDs in them, so doesn't seem ideal; though ZREMCDREMINDER seems to have a single row match with the ID being in ZCKIDENTIFIER / ZDACALENDARITEMUNIQUEIDENTIFIER, so maybe we can do it that way in like 2 steps..

Originally posted by @0xdevalias in keith/reminders-cli#74 (comment)

Working with the above rough notes (and remembering how SQL joins work), I eventually came up with this, that would seem to extract all of the relevant entries from ZREMCDOBJECT based on the single entry in ZREMINDER that matches the externalId in the data from a command like follows:
⇒ reminders show Reminders --sort creation-date --sort-order ascending --format json | jq '[limit(1;.[])]'
[
  {
    "dueDate": "2023-08-13T14:00:00Z",
    "externalId": "EAA1A308-368E-4F90-B9D5-6E6A12AE7E6D",
    "isCompleted": false,
    "list": "Reminders",
    "priority": 0,
    "title": "Foo Bar Baz"
  }
]
SELECT ZO.*
FROM ZREMCDOBJECT ZO
JOIN (
    SELECT Z_PK
    FROM ZREMCDREMINDER
    WHERE ZCKIDENTIFIER = 'EAA1A308-368E-4F90-B9D5-6E6A12AE7E6D'
    OR ZDACALENDARITEMUNIQUEIDENTIFIER = 'EAA1A308-368E-4F90-B9D5-6E6A12AE7E6D'
) AS ZR ON ZO.ZREMINDER = ZR.Z_PK 
    OR ZO.ZREMINDER1 = ZR.Z_PK 
    OR ZO.ZREMINDER2 = ZR.Z_PK 
    OR ZO.ZREMINDER3 = ZR.Z_PK 
    OR ZO.ZREMINDER4 = ZR.Z_PK 
    OR ZO.ZREMINDER5 = ZR.Z_PK
ORDER BY ZO.Z_ENT;
This gives me 3 rows:

1 row with Z_ENT 18 that contains the URL in ZURL

2 rows with Z_ENT 24 that each contain one of the 2 associated hashtags in ZNAME1
⇒ EXTERNAL_ID='EAA1A308-368E-4F90-B9D5-6E6A12AE7E6D' &&
sqlite3 -readonly -json /Users/devalias/Library/Reminders/Container_v1/Stores/Data-5070B790-D66D-40F7-8F4A-EC8E0FA88F3A.sqlite "
  SELECT ZO.*
  FROM ZREMCDOBJECT ZO
  JOIN (
      SELECT Z_PK
      FROM ZREMCDREMINDER
      WHERE ZCKIDENTIFIER = '$EXTERNAL_ID'
      OR ZDACALENDARITEMUNIQUEIDENTIFIER = '$EXTERNAL_ID'
  ) AS ZR ON ZO.ZREMINDER = ZR.Z_PK
      OR ZO.ZREMINDER1 = ZR.Z_PK
      OR ZO.ZREMINDER2 = ZR.Z_PK
      OR ZO.ZREMINDER3 = ZR.Z_PK
      OR ZO.ZREMINDER4 = ZR.Z_PK
      OR ZO.ZREMINDER5 = ZR.Z_PK
  ORDER BY ZO.Z_ENT;
" | jq 'length'
3
If there were other associated rows, they would obviously have been included as well.

This would allow for a much more robust/complete amount of reminders data to be shown/exported; without needing to wait for the official API's to be updated to support it (and then if/when the API's are updated, these methods could then use the official API)

Originally posted by @0xdevalias in keith/reminders-cli#74 (comment)

Following on from my above research, looking at the sqlite DB that contains the reminders data:

..snip..

Based on that, it should be possible to get a list of all of the normal lists from the ZREMCDOBJECT table like follows:

-- SELECT *
SELECT
  Z_PK,
  Z_ENT,
  ZBADGEEMBLEM,
  ZNAME2,
  ZSORTINGSTYLE
FROM ZREMCDOBJECT
WHERE Z_ENT = '25'

Which for my data, returns 9 entries:

7 active lists
1 (recently) deleted list
1 folder

⇒ sqlite3 -readonly -json /Users/devalias/Library/Reminders/Container_v1/Stores/Data-5070B790-D66D-40F7-8F4A-EC8E0FA88F3A.sqlite "
  SELECT
    Z_PK,
    Z_ENT,
    ZBADGEEMBLEM,
    ZNAME2,
    ZSORTINGSTYLE
  FROM ZREMCDOBJECT
  WHERE Z_ENT = '25'
"
[{"Z_PK":8,"Z_ENT":25,"ZBADGEEMBLEM":null,"ZNAME2":"Reminders","ZSORTINGSTYLE":"manual"},
{"Z_PK":2936,"Z_ENT":25,"ZBADGEEMBLEM":"{\"Emoji\" : \"💲\"}","ZNAME2":"To Buy","ZSORTINGSTYLE":"manual"},
{"Z_PK":2965,"Z_ENT":25,"ZBADGEEMBLEM":"{\"Emoji\" : \"🎓\"}","ZNAME2":"Learning","ZSORTINGSTYLE":"manual"},
{"Z_PK":2966,"Z_ENT":25,"ZBADGEEMBLEM":null,"ZNAME2":"Adulting","ZSORTINGSTYLE":"manual"},
{"Z_PK":2967,"Z_ENT":25,"ZBADGEEMBLEM":null,"ZNAME2":"Quality of Life","ZSORTINGSTYLE":"manual"},
{"Z_PK":3001,"Z_ENT":25,"ZBADGEEMBLEM":null,"ZNAME2":"People","ZSORTINGSTYLE":"manual"},
{"Z_PK":5060,"Z_ENT":25,"ZBADGEEMBLEM":"{\"Emoji\" : \"🛠️\"}","ZNAME2":"Projects","ZSORTINGSTYLE":"manual"},
{"Z_PK":7176,"Z_ENT":25,"ZBADGEEMBLEM":null,"ZNAME2":"Smart Lists","ZSORTINGSTYLE":"manual"},
{"Z_PK":9740,"Z_ENT":25,"ZBADGEEMBLEM":"{\"Emoji\" : \"☀️\"}","ZNAME2":"Daily","ZSORTINGSTYLE":"manual"}]

And then I can access my smart lists as follows:

-- SELECT *
SELECT
  Z_PK,
  Z_ENT,
  ZBADGEEMBLEM1,
  ZNAME3,
  ZSORTINGSTYLE1,
  ZSMARTLISTTYPE,
  ZFILTERDATA
FROM ZREMCDOBJECT
WHERE Z_ENT = '30'

Which for my data, returns 10 entries:

1 com.apple.reminders.smartlist.today
1 com.apple.reminders.smartlist.flagged
1 com.apple.reminders.smartlist.assigned
7 com.apple.reminders.smartlist.custom

⇒ sqlite3 -readonly -json /Users/devalias/Library/Reminders/Container_v1/Stores/Data-5070B790-D66D-40F7-8F4A-EC8E0FA88F3A.sqlite "
  SELECT
    Z_PK,
    Z_ENT,
    ZBADGEEMBLEM1,
    ZNAME3,
    ZSORTINGSTYLE1,
    ZSMARTLISTTYPE,
    ZFILTERDATA
  FROM ZREMCDOBJECT
  WHERE Z_ENT = '30'
"

[{"Z_PK":18,"Z_ENT":30,"ZBADGEEMBLEM1":null,"ZNAME3":null,"ZSORTINGSTYLE1":"manual","ZSMARTLISTTYPE":"com.apple.reminders.smartlist.today","ZFILTERDATA":null},
{"Z_PK":2932,"Z_ENT":30,"ZBADGEEMBLEM1":"{\"Emoji\" : \"🏥\"}","ZNAME3":"Health","ZSORTINGSTYLE1":"manual","ZSMARTLISTTYPE":"com.apple.reminders.smartlist.custom","ZFILTERDATA":"{\"hashtags\":{\"hashtags\":[\"health\"]}}"},
{"Z_PK":3125,"Z_ENT":30,"ZBADGEEMBLEM1":"{\"Emoji\" : \"⏱\"}","ZNAME3":"This Week","ZSORTINGSTYLE1":null,"ZSMARTLISTTYPE":"com.apple.reminders.smartlist.custom","ZFILTERDATA":"{\"date\":{\"relativeRange\":[\"inNext\",\"1\",\"week\"]}}"},
{"Z_PK":4293,"Z_ENT":30,"ZBADGEEMBLEM1":"{\"Emoji\" : \"🎫\"}","ZNAME3":"Events","ZSORTINGSTYLE1":null,"ZSMARTLISTTYPE":"com.apple.reminders.smartlist.custom","ZFILTERDATA":"{\"hashtags\":{\"hashtags\":[\"event\"]}}"},
{"Z_PK":7157,"Z_ENT":30,"ZBADGEEMBLEM1":"{\"Emoji\" : \"📚\"}","ZNAME3":"To Read","ZSORTINGSTYLE1":"manual","ZSMARTLISTTYPE":"com.apple.reminders.smartlist.custom","ZFILTERDATA":"{\"hashtags\":{\"hashtags\":[\"to-read\"]}}"},
{"Z_PK":7177,"Z_ENT":30,"ZBADGEEMBLEM1":"{\"Emoji\" : \"🎥\"}","ZNAME3":"To Watch","ZSORTINGSTYLE1":null,"ZSMARTLISTTYPE":"com.apple.reminders.smartlist.custom","ZFILTERDATA":"{\"hashtags\":{\"hashtags\":{\"include\":[\"to-watch\",\"youtube-aitrepreneur\",\"youtube-crypto-crew-university\",\"youtube-daveshapiro\",\"youtube-mattvidpro\"],\"exclude\":[],\"operation\":\"or\"}},\"date\":{\"any\":\"\"},\"operation\":\"and\"}"},
{"Z_PK":14539,"Z_ENT":30,"ZBADGEEMBLEM1":"{\"Emoji\" : \"💾\"}","ZNAME3":"Backup","ZSORTINGSTYLE1":"manual","ZSMARTLISTTYPE":"com.apple.reminders.smartlist.custom","ZFILTERDATA":"{\"hashtags\":{\"hashtags\":[\"backup\"]}}"},
{"Z_PK":25188,"Z_ENT":30,"ZBADGEEMBLEM1":"{\"Emoji\" : \"🎥\"}","ZNAME3":"To Watch - Dave Shapiro","ZSORTINGSTYLE1":null,"ZSMARTLISTTYPE":"com.apple.reminders.smartlist.custom","ZFILTERDATA":"{\"hashtags\":{\"hashtags\":{\"exclude\":[],\"operation\":\"or\",\"include\":[\"youtube-daveshapiro\"]}},\"operation\":\"and\"}"},
{"Z_PK":25191,"Z_ENT":30,"ZBADGEEMBLEM1":null,"ZNAME3":null,"ZSORTINGSTYLE1":null,"ZSMARTLISTTYPE":"com.apple.reminders.smartlist.flagged","ZFILTERDATA":null},
{"Z_PK":25192,"Z_ENT":30,"ZBADGEEMBLEM1":null,"ZNAME3":null,"ZSORTINGSTYLE1":null,"ZSMARTLISTTYPE":"com.apple.reminders.smartlist.assigned","ZFILTERDATA":null}]

Originally posted by @0xdevalias in keith/reminders-cli#72 (comment)

duraki/_accessing-apple-reminders-data-macos.md

Accessing / Exporting Apple's Reminders Data on macOS

Table of Contents

General

Unsorted

See Also