Turn Apple Books Highlights into Study Material

Published November 12, 2026 • Figment editorial team

Apple Books stores every highlight and attached note as a row in a local SQLite database that iCloud syncs to your Mac. No export button is needed. A SQL query joins each annotation to its source book and returns structured rows ready for Anki, Obsidian, or any tool that accepts JSON.

What Apple Books records when you highlight

Each highlight creates a row in the ZAEANNOTATION table, inside:

~/Library/Containers/com.apple.iBooksX/Data/Documents/AEAnnotation/*.sqlite

Column	What it holds
`ZANNOTATIONASSETID`	Foreign key linking this annotation to its book
`ZANNOTATIONSELECTEDTEXT`	The exact text you highlighted
`ZANNOTATIONNOTE`	Your typed note, if any
`ZANNOTATIONSTYLE`	Annotation style as an integer; community sources observe values 0–5 including one for underline; Apple does not publish the integer-to-name mapping
`ZANNOTATIONCREATIONDATE`	Creation timestamp in Apple Core Data format (seconds since 1 January 2001)
`ZANNOTATIONMODIFICATIONDATE`	Last-edited timestamp
`ZANNOTATIONDELETED`	Soft-delete flag; `1` means removed in the app

Filter WHERE ZANNOTATIONDELETED = 0 to skip deleted annotations. Soft-deleting in the app flips the flag; the row stays in the database until the app data is cleared.

Apple timestamps use the Core Data epoch: 1 January 2001, which is 978,307,200 seconds after the Unix epoch. Add that offset before calling datetime() in SQLite or converting in code.

iCloud sync brings phone highlights to Mac

Highlights made on iPhone or iPad sync to the Mac database through iCloud. Two settings must both be enabled.

System Settings (macOS 13+): Open the Apple menu, choose System Settings, click your name, then iCloud › Drive. Turn on Sync this Mac, open Apps Syncing to iCloud Drive, and enable Books.

Books app: Open Books, choose Books › Settings › General, and select Reading Now and Collections, bookmarks, and highlights (Apple iCloud setup guide).

Once both settings are on, annotations from every signed-in Apple device appear in the same SQLite files on Mac. Sync is not instantaneous. Any workflow that reads these files should tolerate incomplete results on the first run.

Highlighting is not available for all book types (Apple Support). Content where the app disables highlighting produces no rows in ZAEANNOTATION.

Map every annotation to its title and author

Book metadata lives in a separate file:

~/Library/Containers/com.apple.iBooksX/Data/Documents/BKLibrary/*.sqlite

That database holds ZBKLIBRARYASSET with columns ZASSETID, ZTITLE, and ZAUTHOR. Because the two files are separate SQLite databases, use ATTACH DATABASE to link them in one session.

The query below maps every active highlight to its source book. This is the core artifact for a study-material workflow:

-- Open the AEAnnotation database first (e.g. in DB Browser for SQLite),
-- then run this query. File paths must match your actual Mac username.
-- Unexecuted example — confirm paths and schema before use in production.

ATTACH DATABASE
  '/Users/YOUR_USERNAME/Library/Containers/com.apple.iBooksX/Data/Documents/BKLibrary/BKLibrary-1-091020131601.sqlite'
  AS bk;

SELECT
  bk.ZBKLIBRARYASSET.ZTITLE                                                   AS book_title,
  bk.ZBKLIBRARYASSET.ZAUTHOR                                                  AS author,
  a.ZANNOTATIONSELECTEDTEXT                                                    AS highlight,
  a.ZANNOTATIONNOTE                                                            AS note,
  a.ZANNOTATIONSTYLE                                                           AS color_index,
  datetime(a.ZANNOTATIONCREATIONDATE + 978307200, 'unixepoch', 'localtime')   AS created_at
FROM  ZAEANNOTATION         AS a
JOIN  bk.ZBKLIBRARYASSET    ON bk.ZBKLIBRARYASSET.ZASSETID = a.ZANNOTATIONASSETID
WHERE a.ZANNOTATIONDELETED  = 0
ORDER BY book_title, created_at;

The BKLibrary filename includes a date string that may differ on your Mac. List the directory at ~/Library/Containers/com.apple.iBooksX/Data/Documents/BKLibrary/ to find the exact filename before connecting.

From raw rows to study material

Each row contains book title, author, highlight text, your note, the color index, and a timestamp. That structure maps directly onto several study formats.

Flashcards. A note attached to a highlight makes a natural answer side; the highlighted sentence is the prompt. Tools like Anki (via AnkiConnect) accept JSON input and can ingest these rows in bulk.

Chapter outline. Order rows by book_title then created_at. The result is a chronological record of what you marked in each book — a rough outline of your own attention.

Spaced-repetition export. Export rows to JSON, keep created_at on each object, and import into any SRS tool that accepts a standard format.

The integration-design snippet below builds that JSON from Python. It is an unexecuted design reference; file paths and schema details must be confirmed before production use:

# Integration design — unexecuted; confirm paths and schema before shipping.
import sqlite3

EPOCH_OFFSET = 978_307_200  # seconds between 1970-01-01 and 2001-01-01

def load_highlights(ae_path: str, bk_path: str) -> list[dict]:
    con = sqlite3.connect(ae_path)
    con.execute(f"ATTACH DATABASE '{bk_path}' AS bk")
    cur = con.execute("""
        SELECT
            bk.ZBKLIBRARYASSET.ZTITLE,
            bk.ZBKLIBRARYASSET.ZAUTHOR,
            a.ZANNOTATIONSELECTEDTEXT,
            a.ZANNOTATIONNOTE,
            a.ZANNOTATIONSTYLE,
            a.ZANNOTATIONCREATIONDATE + 978307200
        FROM  ZAEANNOTATION AS a
        JOIN  bk.ZBKLIBRARYASSET
              ON bk.ZBKLIBRARYASSET.ZASSETID = a.ZANNOTATIONASSETID
        WHERE a.ZANNOTATIONDELETED = 0
        ORDER BY 1, 6
    """)
    return [
        {
            "book":      r[0],
            "author":    r[1],
            "highlight": r[2],
            "note":      r[3],
            "color":     r[4],
            "ts":        r[5],
        }
        for r in cur.fetchall()
    ]

Apple's built-in export options

Before building a SQLite pipeline, consider what Apple Books already provides. You can Control-click a note or highlight and share it directly via AirDrop, Mail, Messages, or another app (Apple Support). Quote sharing is available in the United States and is limited to 200 words. For interactive books, the app converts highlights into study cards automatically — no code required (Apple Books study cards).

The SQLite approach is worth the extra step when you need bulk extraction, programmatic source-book mapping, or integration with an external tool that Apple's sharing sheet does not support natively.

Building for multiple users

The SQLite files above live on the Mac where Apple Books is signed in. For a single developer's own machine that is a local workflow. For a product serving multiple users, each user's Books data is on their own device.

Figment Apple Connect provides per-user Apple account access on a hosted Mac through a Figment OAuth flow — your app receives a Figment access token; it never receives the user's Apple password. The connection endpoint confirms service readiness:

GET https://figment.so/api/apple-connect/v1/connections/{connection_id}
Authorization: Bearer ACCESS_TOKEN

Response: {connection: {id, mode, services, ...}} where mode is sandbox or live.

There is currently no public Figment REST endpoint for Books annotations. The SQLite extraction design above is an Apple-native local workflow. For a multi-user implementation you would run that same logic on the hosted Mac as a developer-owned process, within the bounds of the connection and the Apple Books sync state. Sandbox access is free; live connections are $29/month per active Apple connection. Register your app and start with the sandbox at /apple-connect/developers.

For the full OAuth and PKCE reference, see OAuth for iCloud: Connect Apple Data to Your App. For other native Apple data sources, see Connect Apple Notes, Photos, Files, and Safari.

Annotation boundaries to know before you build

Not all books support highlights. DRM-protected titles and certain publisher-restricted formats may produce no rows in ZAEANNOTATION even when the book appears in the library.

No protected content. This workflow reads annotations — your highlighted selections and typed notes. It does not provide DRM bypass or bulk export of the underlying book text.

PDFs versus EPUBs. Both formats can live in Apple Books, but annotation table structure may differ. Test your query against the specific content types in your library before treating a schema assumption as universal.

Soft-delete is not permanent delete. Rows with ZANNOTATIONDELETED = 1 remain in the database until the app data is cleared. Verify the filter is applied if your workflow produces duplicates on subsequent runs.

Sync timing. Run any annotation pipeline only after confirming that iCloud Books sync has completed. A pipeline that starts while the database is still receiving changes may miss recent highlights.

Next step: Connect Apple Account to register your app and start with the sandbox.