Tags in Safari History db

I noticed an interesting feature in Safari's History database today. Safari tries to interpret every page you visit into a few words, represented as a Tag. But importantly, not all pages are tagged. The exact mechanism of how it arrives at a tag name is also uncertain but it piqued my interest as a case I was recently working on had some curious Tag entries in it!

This does not seem to be a new feature, as I've seen this in databases going back to at least 2021. 

Artifact location

In the Safari History db located at ~/Library/Safari/History.db

There are 2 tables of interest here: history_tags and history_items_to_tags. As the name suggests, the latter has info to join the tags to the actual history_items table entries while the former (history_tags) has the tag details.

Figure 1 - Tables holding tag data

This is what my own Safari history_tags table look like: 

Figure 2 - history_tags table content

The modification_timestamp represents the last time a page (assigned that tag) was visited. The value is of type NSDate (aka Cocoa timestamp) having epoch of 2001-01-01.

The Tag name is in the title column. Now notice that identifier column that always starts with a 'Q'. This is because Safari links every tag to a wikidata item (as shown below).

Figure 3 - The identifier field

Quick query to pull out this information along with the full history data from the database:

SELECT history_visits.title, url, 

  datetime(visit_time + 978307200, 'unixepoch') as visit_time, 

  history_tags.title as tag_title,  

  history_tags.identifier as tag_identifier, 

  datetime(history_tags.modification_timestamp  + 978307200, 'unixepoch') as tag_mod_date

FROM history_visits 

  LEFT JOIN history_items on history_visits.history_item = history_items.id

  LEFT JOIN history_items_to_tags ON history_items_to_tags.history_item=history_items.id

  LEFT JOIN history_tags on  history_items_to_tags.tag_id=history_tags.id

ORDER BY visit_time

Figure 4 - Output showing full history with tag details

Remember, not all pages have tags, and in the ones that do, the tags may not always be accurate. In my database about 98% of the time, the tag represented something related to the page (not always the central theme but definitely related). Below is an example of what a page was tagged as, but its a completely different story, both are related to planes though.

Figure 5 - Sample data from history

 

Forensic fun facts and usage

While in my own experiments, clearing the Safari history does seem to remove the corresponding entries from this table as well, I do also have some test systems where the history_tags table has old data having thousands of entries, with several having an item_count of 0 (as can be seen below). 

Figure 6 - history_tags with no associated history items due to deletion

While the above may not be the smoking gun you are looking for, it can provide some good insight of what themes the end user was browsing and when. 

On the case that inspired deeper inspection of this table, the tag was "APT" (Q230724), which got me excited only to find out that it refers to Advanced Package Tool, as in Homebrew or Linux 'apt'. But the website we were interested in was a phishing site impersonating the real Homebrew site, and it was properly tagged as APT, while the real Homebrew site got no tags!

mac_apt has been updated to add parsing of tags. Tags without item counts are shown as type "TAGGED". 

Figure 7 - mac_apt Safari output