You Can Access Your Own Data on Mac

Your Mac quietly maintains an impressive collection of SQLite databases that chronicle your digital life. App usage patterns, browsing history, bluetooth device connections, podcast listening habits, message metadata—it’s all there, sitting in various ~/Library subdirectories. Wonderfully, given the “ecosystem” aspect of Apple, much of your phone’s data is there too—just much more accessible.

Let’s explore what’s available and how to access it.

Local Databases

App Usage: knowledgeC.db

Location: ~/Library/Application Support/Knowledge/knowledgeC.db

This is Apple’s Screen Time database, and it’s remarkably comprehensive. Despite the cryptic CoreData schema with tables like ZOBJECT, ZSOURCE, and ZSTRUCTUREDMETADATA, it contains:

• App usage sessions — Every time you open an app, with start/end timestamps
• Bluetooth connections — Device name, address, connection duration
• Notifications — Which apps pinged you and when
• Display state — When your screen was on or off
• Siri intents — Your interactions with Siri and Shortcuts

The schema is… not friendly. Here’s a taste of what ZOBJECT looks like:

CREATE TABLE ZOBJECT (
    Z_PK INTEGER PRIMARY KEY,
    Z_ENT INTEGER,
    Z_OPT INTEGER,
    ZSTARTDATE TIMESTAMP,          -- Apple epoch (2001-01-01)
    ZENDDATE TIMESTAMP,
    ZSTREAMNAME TEXT,              -- '/app/usage', '/bluetooth/isConnected', etc.
    ZVALUESTRING TEXT,             -- Often the bundle ID
    ZVALUEINTEGER INTEGER,
    ZSTRUCTUREDMETADATA INTEGER,   -- FK to structured data
    ZSOURCE INTEGER,               -- FK to source info
    -- ... about 20 more columns
);

The ZSTREAMNAME column is the key to understanding what’s what. Common stream names include:

• /app/usage — App usage sessions
• /bluetooth/isConnected — Bluetooth connection events
• /notification/usage — Notification events
• /display/isBacklit — Screen state
• /app/intents — Siri intents

Important notes:

• Timestamps use Apple’s epoch (2001-01-01), not Unix. Add 978307200 to convert to Unix time.
• Requires Full Disk Access to read directly—more on that below.
• Data syncs across devices via iCloud if you have Screen Time enabled.

Once you extract and visualize the data, you can see patterns emerge. Here’s my screen time over the past month:

Chrome History

Location: ~/Library/Application Support/Google/Chrome/Default/History

If you use Chrome, this one’s straightforward:

CREATE TABLE urls (
    id INTEGER PRIMARY KEY,
    url LONGVARCHAR,
    title LONGVARCHAR,
    visit_count INTEGER DEFAULT 0,
    typed_count INTEGER DEFAULT 0,       -- Times you typed this URL directly
    last_visit_time INTEGER NOT NULL,    -- WebKit timestamp
    hidden INTEGER DEFAULT 0
);

There’s also a visits table with individual visit events linked to URLs. Chrome’s timestamps are WebKit format (microseconds since 1601-01-01), so you’ll need to convert: (webkit_time / 1000000) - 11644473600.

Aggregating by domain gives a fun picture:

Note: Chrome locks this database while running. You’ll want to copy it before querying, or close Chrome first.

Apple Podcasts Database

Location: ~/Library/Group Containers/243LU875E5.groups.com.apple.podcasts/Documents/MTLibrary.sqlite

If you listen to podcasts through Apple’s app, this database tracks everything:

-- Shows you're subscribed to
SELECT ZTITLE, ZAUTHOR, ZFEEDURL FROM ZMTPODCAST;

-- Episode listening history
SELECT
    ZTITLE,           -- Episode title
    ZDURATION,        -- Total duration
    ZPLAYEDCOUNT,     -- Times played
    ZLASTDATEPLAYED   -- When you last listened
FROM ZMTEPISODE
WHERE ZPLAYEDCOUNT > 0;

This is useful for answering questions like “how many hours of podcasts did I listen to this month?” or “what’s my most-replayed episode?”

Messages Database

Location: ~/Library/Messages/chat.db

Your iMessage and SMS history, complete with:

• Message content and timestamps
• Read/delivered receipts
• Chat participants
• Attachment metadata

This one’s particularly sensitive (it has your actual message text), but can be useful for things like analyzing response times or conversation patterns.

The sent vs received ratio and temporal patterns can reveal interesting things about communication habits. Are you more of a sender or receiver? When are your conversations most active?

Note: Definitely requires Full Disk Access, and you probably want to be thoughtful about what you do with these data.

What Can You Do With This?

Once you’ve got access, the queries get interesting. Here’s what I found in my own data:

Top apps by usage sessions:

com.google.Chrome         3274 sessions
com.tinyspeck.slackmacgap 1489 sessions
com.microsoft.VSCode      1159 sessions
com.apple.MobileSMS        931 sessions
com.microsoft.teams2       763 sessions

Bluetooth devices I’ve connected to:

Tevon's AirPods #5    513 connections
MOMENTUM 4            270 connections
Audi_MMI_0304          83 connections

Some practical uses:

• Personal analytics — Build your own Screen Time alternative with custom queries
• Data export — Back up your messages or browsing history in a portable format
• Pattern discovery — When do you actually work? Which apps dominate your day?
• Personal Assistant — Make Siri what it actually should be… Useful and personal
• Forensics — Useful if you ever need to reconstruct a timeline of your activity

Technical Details

The Full Disk Access Requirement

Here’s the catch: most of these databases live in protected directories. To access them programmatically, you need to:

1. Open System Settings then Privacy & Security then Full Disk Access
2. Add your terminal app (Terminal.app, iTerm, VS Code’s terminal, etc.)
3. Restart the terminal after granting access

Without this, you’ll get permission errors even though the files technically exist.

DONT MODIFY THE DATABASES: copy the databases to an unprotected location (like your Desktop) before querying them:

This also avoids locking issues with databases that are actively being written to.

Gotchas

A few things I learned while exploring these databases:

1. Copy before querying — Live databases can be locked or in an inconsistent state. Always work with copies.

2. Watch the timestamps — You’ll encounter at least three different epoch formats:

• Unix (1970-01-01)
• Apple (2001-01-01)
• WebKit (1601-01-01, in microseconds)

3. CoreData schemas are verbose — Apple’s databases use CoreData, which generates schemas with columns like Z_PK, Z_ENT, Z_OPT. The actual data columns have prefixes like ZVALUESTRING or Z_DKBLUETOOTHMETADATAKEY__NAME. Use .schema and .tables liberally.

4. Some data is on your phone, not your Mac — Screen Time data syncs, but if you’re looking for iOS-specific events, you might need to look at an iOS backup instead.

5. Watch out for data quality issues — The bluetooth connection data in particular can be unreliable. Here’s an example where the reported connection time spiked to impossible values:

387 hours of bluetooth connection time in a single day? That’s obviously a data quality issue—likely a device that didn’t properly report disconnection. When building analyses on these data, you’ll want to add sanity checks and outlier filtering.

Wrapping Up

Your Mac maintains a detailed record of your digital behavior across multiple SQLite databases. With Full Disk Access and some SQL knowledge, you can query these data to understand your own patterns, export them for backup, or build custom tools on top of them.

The visualizations throughout this post were generated by querying these databases and plotting the results. There’s something satisfying about having complete access to your own data—no third-party service required, no privacy concerns, just raw SQLite files waiting to be explored.

Please, please create a copy of these databases before querying them. Play with them at your own risk.