`show-lists` doesn't include custom 'smart lists'

[0xdevalias]

Overview

When using the show-lists subcommand in reminders-cli, the output does not include custom 'smart lists' from macOS Reminders.

Steps to Reproduce

1. Ensure there are custom 'smart lists' in the macOS Reminders app.
2. Open the terminal.
3. Run the command: reminders show-lists.
4. Observe that the output includes regular lists but excludes 'smart lists'.

Expected Behavior

The show-lists subcommand should display all lists available in the macOS Reminders app, including 'smart lists'.

Actual Behavior

The show-lists subcommand only displays regular lists and omits 'smart lists' from the output.

Additional Information

• This behavior is consistent regardless of the number or types of lists present in the macOS Reminders app.
• Regular lists are displayed as expected.

Environment

• reminders-cli version: 2.4.0
• macOS version: Ventura 13.5

[naafiyan]

This seems to be a limitation of the Apple Reminders API itself not including Smart Lists.

[0xdevalias]

This seems to be a limitation of the Apple Reminders API itself not including Smart Lists.

I haven't verified this, but ChatGPT seems to suggest that they may need to be manually implemented as filters:

Full ChatGPT Response

To access smart lists using the Apple Reminders API, you'll need to interact with the API through Apple's frameworks, such as EventKit for iOS and macOS. Smart lists in Apple Reminders are special lists like "Today", "Scheduled", "All", etc., that automatically categorize reminders based on certain criteria.

Here's a general approach to accessing smart lists:

1. Import EventKit: First, you need to import the EventKit framework into your project. This framework provides the necessary classes and methods to interact with calendar and reminders data.

2. Request Access: Request access to the user's reminders. You need to ensure your app has the appropriate permissions to access reminder data.

3. Fetch Reminders: Once you have access, you can fetch reminders. However, Apple's EventKit does not provide a direct way to access smart lists by their usual names like "Today" or "Scheduled". Instead, you have to manually filter the reminders based on the criteria that define these smart lists. For example, for the "Today" smart list, you would filter reminders whose due dates are today.

Here's a basic outline of how you might do this in Swift (for iOS/macOS):

import EventKit

let eventStore = EKEventStore()

// Request access to reminders
eventStore.requestAccess(to: .reminder) { (granted, error) in
    if granted && error == nil {
        // Fetch all reminders
        let predicate = eventStore.predicateForReminders(in: nil)
        eventStore.fetchReminders(matching: predicate) { (reminders) in
            // Filter reminders based on your criteria for a smart list
            let todayReminders = reminders?.filter {
                guard let dueDate = $0.dueDateComponents?.date else { return false }
                return Calendar.current.isDateInToday(dueDate)
            }

            // Do something with the filtered reminders
        }
    } else {
        // Handle the error or the case where access is not granted
    }
}

In this code snippet, we:

• Import EventKit.
• Request access to reminders.
• Fetch all reminders and filter them to match the "Today" smart list criteria.

Remember that this is just a basic example. You'll need to tailor your filtering logic to match the specific criteria of the smart list you're interested in. Additionally, always handle permissions and potential errors appropriately in a production environment.

For a more comprehensive solution, you might consider creating custom functions or classes to encapsulate the logic for different smart lists.

[naafiyan]

After some investigation it seems that Tags themselves aren't exposed in the API for the EKReminders class. The ChatGPT example uses the very specific case of Today, Tomorrow and other Scheduled events to "tag" them by their due dates. Guess we'll just have to wait for Apple's Reminders API to catch up to the features

[0xdevalias]

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

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

• Tags
• Date
• Time
• Priority
• Flag
• Location
• List

That said, I also don't think there is a way to access the underlying rules that make up a smart list currently, so it would sort of be a manual process to implement these with various filters/etc (perhaps being able to create/save 'filter aliases' in this app would be useful for that?)

[0xdevalias]

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

[0xdevalias]

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

---

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 #74 (comment)

---

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}]

---

Edit: Collated/cross-posted the above on the following gist for future reference: https://gist.github.com/0xdevalias/ccc2b083ff58b52aa701462f2cfb3cc8#accessing--exporting-apples-reminders-data-on-macos

Edit 2: Found this cool blog post laying out a lot of the specifics of how the internals of Apple CoreData based SQLite databases are laid out: https://fatbobman.com/en/posts/tables_and_fields_of_coredata/

Edit 3: Based on that blog post, we can correlate the Z_ENT 30 with the Z_PRIMARYKEY table to see that it represents a REMCDSmartList, which has a superclass of REMCDObject (which explains why it's in that table). We can also see that Z_ENT 25 is a REMCDList (also with a superclass of REMCDObject). Etc

Edit 4: I wrote a basic python script to extract the CoreData models + hierarchy from the Z_PRIMARYKEY table and display them as an indented markdown list: https://gist.github.com/0xdevalias/ccc2b083ff58b52aa701462f2cfb3cc8#file-extract-coredata-model-hierarchy-py

Reminders SQLite CoreData Models + Associated tables

⇒ ./extract-coredata-model-hierarchy.py /Users/devalias/Library/Reminders/Container_v1/Stores/Data-5070B790-D66D-40F7-8F4A-EC8E0FA88F3A.sqlite
- 1: REMCDAccountListData (Table: ZREMCDACCOUNTLISTDATA)
- 2: REMCDChangeTrackingState (Table: ZREMCDCHANGETRACKINGSTATE)
- 3: REMCDHashtagLabel (Table: ZREMCDHASHTAGLABEL)
- 4: REMCDMigrationState (Table: ZREMCDMIGRATIONSTATE)
- 5: REMCDObject (Table: ZREMCDOBJECT)
  - 6: REMCDAccount
  - 7: REMCDAlarm
  - 8: REMCDAlarmTrigger
    - 9: REMCDAlarmDateTrigger
    - 10: REMCDAlarmLocationTrigger
    - 11: REMCDAlarmTimeIntervalTrigger
    - 12: REMCDAlarmVehicleTrigger
  - 13: REMCDAssignment
  - 14: REMCDAttachment
    - 15: REMCDFileAttachment
      - 16: REMCDAudioAttachment
      - 17: REMCDImageAttachment
    - 18: REMCDURLAttachment
  - 19: REMCDAuxiliaryChangeInfo
    - 20: REMCDAuxiliaryReminderChangeInfo
      - 21: REMCDAuxiliaryReminderChangeDeleteInfo
      - 22: REMCDAuxiliaryReminderChangeMoveInfo
  - 23: REMCDCalDAVNotification
  - 24: REMCDHashtag
  - 25: REMCDList
  - 26: REMCDManualSortHint_v1
  - 27: REMCDRecurrenceRule
  - 28: REMCDSharedToMeReminderPlaceholder
  - 29: REMCDSharee
  - 30: REMCDSmartList
- 31: REMCDPublicTemplate (Table: ZREMCDPUBLICTEMPLATE)
- 32: REMCDReminder (Table: ZREMCDREMINDER)
- 33: REMCDReplicaManager (Table: ZREMCDREPLICAMANAGER)
- 34: REMCDSavedAttachment (Table: ZREMCDSAVEDATTACHMENT)
- 35: REMCDSavedReminder (Table: ZREMCDSAVEDREMINDER)
- 36: REMCDTemplate (Table: ZREMCDTEMPLATE)
- 37: REMCDTemplateOperationQueueItem (Table: ZREMCDTEMPLATEOPERATIONQUEUEITEM)
- 38: REMCKCloudState (Table: ZREMCKCLOUDSTATE)
- 39: REMCKServerChangeToken (Table: ZREMCKSERVERCHANGETOKEN)
- 40: REMCKSharedEntitySyncActivity (Table: ZREMCKSHAREDENTITYSYNCACTIVITY)
- 41: REMCKSharedObjectOwnerName (Table: ZREMCKSHAREDOBJECTOWNERNAME)
- 16001: CHANGE
- 16002: TRANSACTION
- 16003: TRANSACTIONSTRING

[cromulus]

From prior adventures in accessing the underlying sqlite db for apple apps (notes), it is, in my humble opinion, a huge headache.

Much, much better off using standard API's that are stable, documented, and thus far less brittle.

[0xdevalias]

Much, much better off using standard API's that are stable, documented, and thus far less brittle.

@cromulus When they exist; I'd agree with you 100%. But when they don't, or are limited and don't provide all of the things you'd like access to; that's what the above was exploring.

[cromulus]

Yuuup. I feel you. That's why I was doing the same sort of archaeology in the notes sqlitedb.