Add option for tags

[theuberuser]

Tags were introduced in the Reminders app in macOS Monterey and can be extremely useful when managing various types of tasks. I think adding this to the reminders CLI would make it all the more powerful than it already is, especially when used with scripts and automation.

Usage examples:

$ reminders add work -t projectX

$ reminders add work -t teamZ

$ reminders add work -t jira_FOOBAR-123456

$ reminders add errands -t groceries

$ reminders add home -t chores

NOTE: Adding support for multiple tags would be ideal but not required

These are just some basic examples but you can probably see how useful this could be.

Also, yes, I did see that there was one comment about this already on #59, but, I felt it was worth explicitly calling it out in a separate issue, so as not to get lost. If you'd rather work on this in the already open issue for enhancements, then please feel free to delete this one.

[keith]

AFAICT tags aren't in the public API https://developer.apple.com/documentation/eventkit/ekevent

[0xdevalias]

Semi-related:

---

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

---

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 see that we can look up what the Z_ENT's represent within the Z_PRIMARYKEY table to see that what 'class'/'superclass' it corresponds to (eg. REMCDObject). Using that method, we can confirm that Z_ENT 30 is REMCDSmartList, 24 is REMCDHashtag, 18 is REMCDURLAttachment, 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

[0xdevalias]

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)

---

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

[cromulus]

For what it's worth, interacting directly with OS X application sqlite dbs is fraught with peril/pain: the schemas change fairly often, encodings, formats, etc of rows change. From my experience trying to export notes, it's been challenging to say the least: I've had to remove functionality because of changes to the underlying sqlite dataset.

[keith]

fwiw it's possible to use the private APIs that reminders.app uses instead for this kinda thing, which i imagine might be a bit safer long term. i have a private codebase unrelated to this repo that does that successfully to handle multiple URLs which isn't exposed in the public API

[0xdevalias]

fwiw it's possible to use the private APIs that reminders.app uses instead for this kinda thing

@keith Oh? Interesting.. that's not a path I've looked into before. Curious, do you have any particularly useful blog / gist / etc references that go into the process and/or the findings more that you'd be happy to share? Would be interested to read more about it.

[keith]

the gist is you copy the runtime headers from the framework that contains REMStore, and then you can poke around at those private methods at least to fetch data. I didn't try to do any edits through that, but unsurprisingly the reminders you get from fetching there have matching IDs with the ones you get from the public APIs.

[0xdevalias]

the gist is you copy the runtime headers from the framework that contains REMStore, and then you can poke around at those private methods at least to fetch data.

@keith Interesting, thanks! Have had ChatGPT expand on that a bit (Ref), and got myself a whole new pile of sideproject reading/learning to do! (Refs: 1, 2, 3, 4, 5, 6, 7, 8, etc)

---

Edit: Captured these references in my gist here, and will likely expand on them in future: https://gist.github.com/0xdevalias/ccc2b083ff58b52aa701462f2cfb3cc8#macos-private-frameworks