---
name: zotero
description: Search, browse, and read papers in the local Zotero library via its SQLite database and file storage
---

# Zotero Local Library

Use this skill when the user wants to search, browse, or read papers and references from their Zotero library.

## Database Location

```
/.../Zotero/zotero.sqlite
```

**IMPORTANT**: Always open the database in read-only mode to avoid conflicts with a running Zotero instance:

```bash
sqlite3 "file:///.../Zotero/zotero.sqlite?mode=ro"
```

Or equivalently, use the `-readonly` flag (available in newer sqlite3 builds). If your sqlite3 doesn't support URI mode, the standard `sqlite3 /.../Zotero/zotero.sqlite` works but avoid writes.

## PDF Storage

PDFs are stored at `/.../Zotero/storage/<KEY>/<filename>.pdf`, where `<KEY>` is the `items.key` for the attachment item. The `itemAttachments.path` field stores the filename prefixed with `storage:`.

To get the full path for a PDF:
```sql
SELECT '/.../Zotero/storage/' || i.key || '/' || REPLACE(ia.path, 'storage:', '')
FROM itemAttachments ia
JOIN items i ON ia.itemID = i.itemID
WHERE ia.contentType = 'application/pdf'
  AND ia.parentItemID = <parent_item_id>;
```

Once you have the path, use the Read tool to read the PDF directly.

## Core Queries

### Search items by title or abstract

```sql
SELECT i.itemID, idv_title.value AS title, idv_abs.value AS abstract
FROM items i
JOIN itemTypes it ON i.itemTypeID = it.itemTypeID
JOIN itemData id_title ON i.itemID = id_title.itemID
JOIN fields f_title ON id_title.fieldID = f_title.fieldID AND f_title.fieldName = 'title'
JOIN itemDataValues idv_title ON id_title.valueID = idv_title.valueID
LEFT JOIN itemData id_abs ON i.itemID = id_abs.itemID
LEFT JOIN fields f_abs ON id_abs.fieldID = f_abs.fieldID AND f_abs.fieldName = 'abstractNote'
LEFT JOIN itemDataValues idv_abs ON id_abs.valueID = idv_abs.valueID
WHERE idv_title.value LIKE '%search term%'
  AND it.typeName NOT IN ('attachment', 'annotation', 'note')
ORDER BY i.dateModified DESC;
```

### Get full metadata for an item

```sql
SELECT f.fieldName, idv.value
FROM itemData id
JOIN fields f ON id.fieldID = f.fieldID
JOIN itemDataValues idv ON id.valueID = idv.valueID
WHERE id.itemID = <item_id>;
```

### Get authors/creators for an item

```sql
SELECT c.firstName, c.lastName, ct.creatorType, ic.orderIndex
FROM itemCreators ic
JOIN creators c ON ic.creatorID = c.creatorID
JOIN creatorTypes ct ON ic.creatorTypeID = ct.creatorTypeID
WHERE ic.itemID = <item_id>
ORDER BY ic.orderIndex;
```

### Get all metadata + authors for a paper (combined)

```sql
SELECT 'metadata' AS source, f.fieldName AS field, idv.value AS value, NULL AS orderIdx
FROM itemData id
JOIN fields f ON id.fieldID = f.fieldID
JOIN itemDataValues idv ON id.valueID = idv.valueID
WHERE id.itemID = <item_id>
UNION ALL
SELECT 'creator', ct.creatorType, c.lastName || ', ' || c.firstName, ic.orderIndex
FROM itemCreators ic
JOIN creators c ON ic.creatorID = c.creatorID
JOIN creatorTypes ct ON ic.creatorTypeID = ct.creatorTypeID
WHERE ic.itemID = <item_id>
ORDER BY source, orderIdx;
```

### Find the PDF attachment for an item

```sql
SELECT '/.../Zotero/storage/' || i.key || '/' || REPLACE(ia.path, 'storage:', '') AS pdf_path
FROM itemAttachments ia
JOIN items i ON ia.itemID = i.itemID
WHERE ia.parentItemID = <item_id>
  AND ia.contentType = 'application/pdf';
```

### Search by author

```sql
SELECT DISTINCT i.itemID, idv.value AS title
FROM items i
JOIN itemCreators ic ON i.itemID = ic.itemID
JOIN creators c ON ic.creatorID = c.creatorID
JOIN itemData id ON i.itemID = id.itemID
JOIN fields f ON id.fieldID = f.fieldID AND f.fieldName = 'title'
JOIN itemDataValues idv ON id.valueID = idv.valueID
WHERE c.lastName LIKE '%AuthorName%'
ORDER BY i.dateModified DESC;
```

### Search by tag

```sql
SELECT i.itemID, idv.value AS title
FROM items i
JOIN itemTags it ON i.itemID = it.itemID
JOIN tags t ON it.tagID = t.tagID
JOIN itemData id ON i.itemID = id.itemID
JOIN fields f ON id.fieldID = f.fieldID AND f.fieldName = 'title'
JOIN itemDataValues idv ON id.valueID = idv.valueID
WHERE t.name LIKE '%tag name%'
ORDER BY i.dateModified DESC;
```

### List all tags (with counts)

```sql
SELECT t.name, COUNT(it.itemID) AS cnt
FROM tags t
JOIN itemTags it ON t.tagID = it.tagID
GROUP BY t.tagID
ORDER BY cnt DESC;
```

## Collections

Collections are hierarchical (parent-child). Items can belong to multiple collections.

### List all top-level collections

```sql
SELECT collectionID, collectionName
FROM collections
WHERE parentCollectionID IS NULL
ORDER BY collectionName;
```

### List sub-collections of a collection

```sql
SELECT collectionID, collectionName
FROM collections
WHERE parentCollectionID = <collection_id>
ORDER BY collectionName;
```

### List items in a collection

```sql
SELECT i.itemID, idv.value AS title
FROM collectionItems ci
JOIN items i ON ci.itemID = i.itemID
JOIN itemTypes it ON i.itemTypeID = it.itemTypeID
JOIN itemData id ON i.itemID = id.itemID
JOIN fields f ON id.fieldID = f.fieldID AND f.fieldName = 'title'
JOIN itemDataValues idv ON id.valueID = idv.valueID
WHERE ci.collectionID = <collection_id>
  AND it.typeName NOT IN ('attachment', 'annotation', 'note')
ORDER BY idv.value;
```

### Find a collection by name

```sql
SELECT collectionID, collectionName, parentCollectionID
FROM collections
WHERE collectionName LIKE '%search term%';
```

### Full collection tree (recursive)

```sql
WITH RECURSIVE tree AS (
  SELECT collectionID, collectionName, parentCollectionID, 0 AS depth,
         collectionName AS path
  FROM collections WHERE parentCollectionID IS NULL
  UNION ALL
  SELECT c.collectionID, c.collectionName, c.parentCollectionID, t.depth + 1,
         t.path || ' > ' || c.collectionName
  FROM collections c JOIN tree t ON c.parentCollectionID = t.collectionID
)
SELECT depth, path, collectionID FROM tree ORDER BY path;
```

## Full-Text Search

Zotero maintains a word-level full-text index of PDFs. This is useful for finding which papers mention a specific term.

### Search full-text content by word

```sql
SELECT DISTINCT ia.parentItemID AS itemID, idv.value AS title
FROM fulltextItemWords fiw
JOIN fulltextWords fw ON fiw.wordID = fw.wordID
JOIN itemAttachments ia ON fiw.itemID = ia.itemID
JOIN itemData id ON ia.parentItemID = id.itemID
JOIN fields f ON id.fieldID = f.fieldID AND f.fieldName = 'title'
JOIN itemDataValues idv ON id.valueID = idv.valueID
WHERE fw.word = 'algorithm'
ORDER BY title;
```

Note: The full-text index stores individual words (lowercased). For multi-word searches, intersect results:

```sql
SELECT ia.parentItemID AS itemID, idv.value AS title
FROM fulltextItemWords fiw1
JOIN fulltextWords fw1 ON fiw1.wordID = fw1.wordID AND fw1.word = 'neural'
JOIN fulltextItemWords fiw2 ON fiw1.itemID = fiw2.itemID
JOIN fulltextWords fw2 ON fiw2.wordID = fw2.wordID AND fw2.word = 'network'
JOIN itemAttachments ia ON fiw1.itemID = ia.itemID
JOIN itemData id ON ia.parentItemID = id.itemID
JOIN fields f ON id.fieldID = f.fieldID AND f.fieldName = 'title'
JOIN itemDataValues idv ON id.valueID = idv.valueID
ORDER BY title;
```

## Notes and Annotations

### Get notes attached to an item

```sql
SELECT n.title, n.note
FROM itemNotes n
WHERE n.parentItemID = <item_id>;
```

Notes are stored as HTML.

### Get annotations (highlights, comments) on a PDF

```sql
SELECT ia.type, ia.text, ia.comment, ia.color, ia.pageLabel
FROM itemAnnotations ia
WHERE ia.parentItemID = <attachment_item_id>
ORDER BY ia.sortIndex;
```

Annotation types: 1 = highlight, 2 = note, 3 = image.
The `parentItemID` here is the attachment itemID (not the parent paper itemID). To get annotations for a paper, first find its PDF attachment itemID.

### Get all annotations for a paper by its itemID

```sql
SELECT ann.type, ann.text, ann.comment, ann.color, ann.pageLabel
FROM itemAttachments att
JOIN itemAnnotations ann ON ann.parentItemID = att.itemID
WHERE att.parentItemID = <item_id>
ORDER BY ann.sortIndex;
```

## Common Field Names

| fieldName | Description |
|-----------|------------|
| title | Item title |
| abstractNote | Abstract |
| date | Publication date |
| DOI | Digital Object Identifier |
| url | URL |
| publicationTitle | Journal/conference name |
| volume | Volume number |
| issue | Issue number |
| pages | Page range |
| language | Language |
| ISSN | ISSN |
| ISBN | ISBN |
| extra | Extra/misc field |
| journalAbbreviation | Abbreviated journal name |

## Item Types

Common types: `journalArticle`, `conferencePaper`, `preprint`, `book`, `bookSection`, `webpage`, `blogPost`, `report`, `newspaperArticle`, `thesis`, `manuscript`.

Internal-only types (usually excluded from searches): `attachment`, `annotation`, `note`.

## Workflow Tips

1. **Finding a paper**: Search by title, author, or tag. Then get its itemID.
2. **Reading a paper**: Use the itemID to find the PDF path, then use the Read tool to read it.
3. **Getting context**: Check annotations and notes on the paper for the user's highlights and comments.
4. **Exploring a topic**: Use collection browsing or full-text search to find related papers.
5. **Citation info**: Get the full metadata + creators query to build a citation.