Zotero Local Database Schema Documentation

Zotero_schema.md

Exploring the Zotero Local Database Schema

On my (Windows) machine, Zotero's local database was located at C:\Users\chris\Zotero\zotero.sqlite. I was able to dump the schema using the sqlite3 command line tool:

sqlite3 zotero.sqlite ".schema" > zotero_schema.sql

Here is the SQL schema dump:

-- Field and type management tables
CREATE TABLE fieldFormats (
    fieldFormatID INTEGER PRIMARY KEY,
    regex TEXT,
    isInteger INT
);

CREATE TABLE charsets (
    charsetID INTEGER PRIMARY KEY,
    charset TEXT UNIQUE
);
CREATE INDEX charsets_charset ON charsets(charset);

CREATE TABLE fileTypes (
    fileTypeID INTEGER PRIMARY KEY,
    fileType TEXT UNIQUE
);
CREATE INDEX fileTypes_fileType ON fileTypes(fileType);

CREATE TABLE fileTypeMimeTypes (
    fileTypeID INT,
    mimeType TEXT,
    PRIMARY KEY (fileTypeID, mimeType),
    FOREIGN KEY (fileTypeID) REFERENCES fileTypes(fileTypeID)
);
CREATE INDEX fileTypeMimeTypes_mimeType ON fileTypeMimeTypes(mimeType);

CREATE TABLE syncObjectTypes (
    syncObjectTypeID INTEGER PRIMARY KEY,
    name TEXT
);
CREATE INDEX syncObjectTypes_name ON syncObjectTypes(name);

-- Item type and field definitions
CREATE TABLE itemTypes (
    itemTypeID INTEGER PRIMARY KEY,
    typeName TEXT,
    templateItemTypeID INT,
    display INT DEFAULT 1
);

CREATE TABLE itemTypesCombined (
    itemTypeID INT NOT NULL,
    typeName TEXT NOT NULL,
    display INT DEFAULT 1 NOT NULL,
    custom INT NOT NULL,
    PRIMARY KEY (itemTypeID)
);

CREATE TABLE fields (
    fieldID INTEGER PRIMARY KEY,
    fieldName TEXT,
    fieldFormatID INT,
    FOREIGN KEY (fieldFormatID) REFERENCES fieldFormats(fieldFormatID)
);

CREATE TABLE fieldsCombined (
    fieldID INT NOT NULL,
    fieldName TEXT NOT NULL,
    label TEXT,
    fieldFormatID INT,
    custom INT NOT NULL,
    PRIMARY KEY (fieldID)
);

CREATE TABLE itemTypeFields (
    itemTypeID INT,
    fieldID INT,
    hide INT,
    orderIndex INT,
    PRIMARY KEY (itemTypeID, orderIndex),
    UNIQUE (itemTypeID, fieldID),
    FOREIGN KEY (itemTypeID) REFERENCES itemTypes(itemTypeID),
    FOREIGN KEY (fieldID) REFERENCES fields(fieldID)
);
CREATE INDEX itemTypeFields_fieldID ON itemTypeFields(fieldID);

CREATE TABLE itemTypeFieldsCombined (
    itemTypeID INT NOT NULL,
    fieldID INT NOT NULL,
    hide INT,
    orderIndex INT NOT NULL,
    PRIMARY KEY (itemTypeID, orderIndex),
    UNIQUE (itemTypeID, fieldID)
);
CREATE INDEX itemTypeFieldsCombined_fieldID ON itemTypeFieldsCombined(fieldID);

CREATE TABLE baseFieldMappings (
    itemTypeID INT,
    baseFieldID INT,
    fieldID INT,
    PRIMARY KEY (itemTypeID, baseFieldID, fieldID),
    FOREIGN KEY (itemTypeID) REFERENCES itemTypes(itemTypeID),
    FOREIGN KEY (baseFieldID) REFERENCES fields(fieldID),
    FOREIGN KEY (fieldID) REFERENCES fields(fieldID)
);
CREATE INDEX baseFieldMappings_baseFieldID ON baseFieldMappings(baseFieldID);
CREATE INDEX baseFieldMappings_fieldID ON baseFieldMappings(fieldID);

CREATE TABLE baseFieldMappingsCombined (
    itemTypeID INT,
    baseFieldID INT,
    fieldID INT,
    PRIMARY KEY (itemTypeID, baseFieldID, fieldID)
);
CREATE INDEX baseFieldMappingsCombined_baseFieldID ON baseFieldMappingsCombined(baseFieldID);
CREATE INDEX baseFieldMappingsCombined_fieldID ON baseFieldMappingsCombined(fieldID);

-- Creator management
CREATE TABLE creatorTypes (
    creatorTypeID INTEGER PRIMARY KEY,
    creatorType TEXT
);

CREATE TABLE itemTypeCreatorTypes (
    itemTypeID INT,
    creatorTypeID INT,
    primaryField INT,
    PRIMARY KEY (itemTypeID, creatorTypeID),
    FOREIGN KEY (itemTypeID) REFERENCES itemTypes(itemTypeID),
    FOREIGN KEY (creatorTypeID) REFERENCES creatorTypes(creatorTypeID)
);
CREATE INDEX itemTypeCreatorTypes_creatorTypeID ON itemTypeCreatorTypes(creatorTypeID);

-- System configuration
CREATE TABLE version (
    schema TEXT PRIMARY KEY,
    version INT NOT NULL
);
CREATE INDEX schema ON version(schema);

CREATE TABLE settings (
    setting TEXT,
    key TEXT,
    value,
    PRIMARY KEY (setting, key)
);

CREATE TABLE syncedSettings (
    setting TEXT NOT NULL,
    libraryID INT NOT NULL,
    value NOT NULL,
    version INT NOT NULL DEFAULT 0,
    synced INT NOT NULL DEFAULT 0,
    PRIMARY KEY (setting, libraryID),
    FOREIGN KEY (libraryID) REFERENCES libraries(libraryID) ON DELETE CASCADE
);

-- Core item management
CREATE TABLE items (
    itemID INTEGER PRIMARY KEY,
    itemTypeID INT NOT NULL,
    dateAdded TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    dateModified TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    clientDateModified TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    libraryID INT NOT NULL,
    key TEXT NOT NULL,
    version INT NOT NULL DEFAULT 0,
    synced INT NOT NULL DEFAULT 0,
    UNIQUE (libraryID, key),
    FOREIGN KEY (libraryID) REFERENCES libraries(libraryID) ON DELETE CASCADE
);
CREATE INDEX items_synced ON items(synced);

CREATE TABLE itemDataValues (
    valueID INTEGER PRIMARY KEY,
    value UNIQUE
);

CREATE TABLE itemData (
    itemID INT,
    fieldID INT,
    valueID,
    PRIMARY KEY (itemID, fieldID),
    FOREIGN KEY (itemID) REFERENCES items(itemID) ON DELETE CASCADE,
    FOREIGN KEY (fieldID) REFERENCES fieldsCombined(fieldID),
    FOREIGN KEY (valueID) REFERENCES itemDataValues(valueID)
);
CREATE INDEX itemData_fieldID ON itemData(fieldID);
CREATE INDEX itemData_valueID ON itemData(valueID);

-- Item content types
CREATE TABLE itemNotes (
    itemID INTEGER PRIMARY KEY,
    parentItemID INT,
    note TEXT,
    title TEXT,
    FOREIGN KEY (itemID) REFERENCES items(itemID) ON DELETE CASCADE,
    FOREIGN KEY (parentItemID) REFERENCES items(itemID) ON DELETE CASCADE
);
CREATE INDEX itemNotes_parentItemID ON itemNotes(parentItemID);

CREATE TABLE itemAttachments (
    itemID INTEGER PRIMARY KEY,
    parentItemID INT,
    linkMode INT,
    contentType TEXT,
    charsetID INT,
    path TEXT,
    syncState INT DEFAULT 0,
    storageModTime INT,
    storageHash TEXT,
    lastProcessedModificationTime INT,
    FOREIGN KEY (itemID) REFERENCES items(itemID) ON DELETE CASCADE,
    FOREIGN KEY (parentItemID) REFERENCES items(itemID) ON DELETE CASCADE,
    FOREIGN KEY (charsetID) REFERENCES charsets(charsetID) ON DELETE SET NULL
);
CREATE INDEX itemAttachments_parentItemID ON itemAttachments(parentItemID);
CREATE INDEX itemAttachments_charsetID ON itemAttachments(charsetID);
CREATE INDEX itemAttachments_contentType ON itemAttachments(contentType);
CREATE INDEX itemAttachments_syncState ON itemAttachments(syncState);
CREATE INDEX itemAttachments_lastProcessedModificationTime ON itemAttachments(lastProcessedModificationTime);

CREATE TABLE itemAnnotations (
    itemID INTEGER PRIMARY KEY,
    parentItemID INT NOT NULL,
    type INTEGER NOT NULL,
    authorName TEXT,
    text TEXT,
    comment TEXT,
    color TEXT,
    pageLabel TEXT,
    sortIndex TEXT NOT NULL,
    position TEXT NOT NULL,
    isExternal INT NOT NULL,
    FOREIGN KEY (itemID) REFERENCES items(itemID) ON DELETE CASCADE,
    FOREIGN KEY (parentItemID) REFERENCES itemAttachments(itemID)
);
CREATE INDEX itemAnnotations_parentItemID ON itemAnnotations(parentItemID);

-- Tagging system
CREATE TABLE tags (
    tagID INTEGER PRIMARY KEY,
    name TEXT NOT NULL UNIQUE
);

CREATE TABLE itemTags (
    itemID INT NOT NULL,
    tagID INT NOT NULL,
    type INT NOT NULL,
    PRIMARY KEY (itemID, tagID),
    FOREIGN KEY (itemID) REFERENCES items(itemID) ON DELETE CASCADE,
    FOREIGN KEY (tagID) REFERENCES tags(tagID) ON DELETE CASCADE
);
CREATE INDEX itemTags_tagID ON itemTags(tagID);

-- Relationship management
CREATE TABLE relationPredicates (
    predicateID INTEGER PRIMARY KEY,
    predicate TEXT UNIQUE
);

CREATE TABLE itemRelations (
    itemID INT NOT NULL,
    predicateID INT NOT NULL,
    object TEXT NOT NULL,
    PRIMARY KEY (itemID, predicateID, object),
    FOREIGN KEY (itemID) REFERENCES items(itemID) ON DELETE CASCADE,
    FOREIGN KEY (predicateID) REFERENCES relationPredicates(predicateID) ON DELETE CASCADE
);
CREATE INDEX itemRelations_predicateID ON itemRelations(predicateID);
CREATE INDEX itemRelations_object ON itemRelations(object);

-- Creator system
CREATE TABLE creators (
    creatorID INTEGER PRIMARY KEY,
    firstName TEXT,
    lastName TEXT,
    fieldMode INT,
    UNIQUE (lastName, firstName, fieldMode)
);

CREATE TABLE itemCreators (
    itemID INT NOT NULL,
    creatorID INT NOT NULL,
    creatorTypeID INT NOT NULL DEFAULT 1,
    orderIndex INT NOT NULL DEFAULT 0,
    PRIMARY KEY (itemID, creatorID, creatorTypeID, orderIndex),
    UNIQUE (itemID, orderIndex),
    FOREIGN KEY (itemID) REFERENCES items(itemID) ON DELETE CASCADE,
    FOREIGN KEY (creatorID) REFERENCES creators(creatorID) ON DELETE CASCADE,
    FOREIGN KEY (creatorTypeID) REFERENCES creatorTypes(creatorTypeID)
);
CREATE INDEX itemCreators_creatorTypeID ON itemCreators(creatorTypeID);

-- Collection management
CREATE TABLE collections (
    collectionID INTEGER PRIMARY KEY,
    collectionName TEXT NOT NULL,
    parentCollectionID INT DEFAULT NULL,
    clientDateModified TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    libraryID INT NOT NULL,
    key TEXT NOT NULL,
    version INT NOT NULL DEFAULT 0,
    synced INT NOT NULL DEFAULT 0,
    UNIQUE (libraryID, key),
    FOREIGN KEY (libraryID) REFERENCES libraries(libraryID) ON DELETE CASCADE,
    FOREIGN KEY (parentCollectionID) REFERENCES collections(collectionID) ON DELETE CASCADE
);
CREATE INDEX collections_synced ON collections(synced);

CREATE TABLE collectionItems (
    collectionID INT NOT NULL,
    itemID INT NOT NULL,
    orderIndex INT NOT NULL DEFAULT 0,
    PRIMARY KEY (collectionID, itemID),
    FOREIGN KEY (collectionID) REFERENCES collections(collectionID) ON DELETE CASCADE,
    FOREIGN KEY (itemID) REFERENCES items(itemID) ON DELETE CASCADE
);
CREATE INDEX collectionItems_itemID ON collectionItems(itemID);

CREATE TABLE collectionRelations (
    collectionID INT NOT NULL,
    predicateID INT NOT NULL,
    object TEXT NOT NULL,
    PRIMARY KEY (collectionID, predicateID, object),
    FOREIGN KEY (collectionID) REFERENCES collections(collectionID) ON DELETE CASCADE,
    FOREIGN KEY (predicateID) REFERENCES relationPredicates(predicateID) ON DELETE CASCADE
);
CREATE INDEX collectionRelations_predicateID ON collectionRelations(predicateID);
CREATE INDEX collectionRelations_object ON collectionRelations(object);

-- Feed system
CREATE TABLE feeds (
    libraryID INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    url TEXT NOT NULL UNIQUE,
    lastUpdate TIMESTAMP,
    lastCheck TIMESTAMP,
    lastCheckError TEXT,
    cleanupReadAfter INT,
    cleanupUnreadAfter INT,
    refreshInterval INT,
    FOREIGN KEY (libraryID) REFERENCES libraries(libraryID) ON DELETE CASCADE
);

CREATE TABLE feedItems (
    itemID INTEGER PRIMARY KEY,
    guid TEXT NOT NULL UNIQUE,
    readTime TIMESTAMP,
    translatedTime TIMESTAMP,
    FOREIGN KEY (itemID) REFERENCES items(itemID) ON DELETE CASCADE
);

-- Saved searches
CREATE TABLE savedSearches (
    savedSearchID INTEGER PRIMARY KEY,
    savedSearchName TEXT NOT NULL,
    clientDateModified TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    libraryID INT NOT NULL,
    key TEXT NOT NULL,
    version INT NOT NULL DEFAULT 0,
    synced INT NOT NULL DEFAULT 0,
    UNIQUE (libraryID, key),
    FOREIGN KEY (libraryID) REFERENCES libraries(libraryID) ON DELETE CASCADE
);
CREATE INDEX savedSearches_synced ON savedSearches(synced);

CREATE TABLE savedSearchConditions (
    savedSearchID INT NOT NULL,
    searchConditionID INT NOT NULL,
    condition TEXT NOT NULL,
    operator TEXT,
    value TEXT,
    required NONE,
    PRIMARY KEY (savedSearchID, searchConditionID),
    FOREIGN KEY (savedSearchID) REFERENCES savedSearches(savedSearchID) ON DELETE CASCADE
);

-- Deletion tracking
CREATE TABLE deletedCollections (
    collectionID INTEGER PRIMARY KEY,
    dateDeleted DEFAULT CURRENT_TIMESTAMP NOT NULL,
    FOREIGN KEY (collectionID) REFERENCES collections(collectionID) ON DELETE CASCADE
);
CREATE INDEX deletedCollections_dateDeleted ON deletedCollections(dateDeleted);

CREATE TABLE deletedItems (
    itemID INTEGER PRIMARY KEY,
    dateDeleted DEFAULT CURRENT_TIMESTAMP NOT NULL,
    FOREIGN KEY (itemID) REFERENCES items(itemID) ON DELETE CASCADE
);
CREATE INDEX deletedItems_dateDeleted ON deletedItems(dateDeleted);

CREATE TABLE deletedSearches (
    savedSearchID INTEGER PRIMARY KEY,
    dateDeleted DEFAULT CURRENT_TIMESTAMP NOT NULL,
    FOREIGN KEY (savedSearchID) REFERENCES savedSearches(savedSearchID) ON DELETE CASCADE
);
CREATE INDEX deletedSearches_dateDeleted ON deletedItems(dateDeleted);

-- Library and user management
CREATE TABLE libraries (
    libraryID INTEGER PRIMARY KEY,
    type TEXT NOT NULL,
    editable INT NOT NULL,
    filesEditable INT NOT NULL,
    version INT NOT NULL DEFAULT 0,
    storageVersion INT NOT NULL DEFAULT 0,
    lastSync INT NOT NULL DEFAULT 0,
    archived INT NOT NULL DEFAULT 0
);

CREATE TABLE users (
    userID INTEGER PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE groups (
    groupID INTEGER PRIMARY KEY,
    libraryID INT NOT NULL UNIQUE,
    name TEXT NOT NULL,
    description TEXT NOT NULL,
    version INT NOT NULL,
    FOREIGN KEY (libraryID) REFERENCES libraries(libraryID) ON DELETE CASCADE
);

CREATE TABLE groupItems (
    itemID INTEGER PRIMARY KEY,
    createdByUserID INT,
    lastModifiedByUserID INT,
    FOREIGN KEY (itemID) REFERENCES items(itemID) ON DELETE CASCADE,
    FOREIGN KEY (createdByUserID) REFERENCES users(userID) ON DELETE SET NULL,
    FOREIGN KEY (lastModifiedByUserID) REFERENCES users(userID) ON DELETE SET NULL
);

-- Special item types
CREATE TABLE publicationsItems (
    itemID INTEGER PRIMARY KEY
);

CREATE TABLE retractedItems (
    itemID INTEGER PRIMARY KEY,
    data TEXT,
    flag INT DEFAULT 0,
    FOREIGN KEY (itemID) REFERENCES items(itemID) ON DELETE CASCADE
);

-- Full-text search
CREATE TABLE fulltextItems (
    itemID INTEGER PRIMARY KEY,
    indexedPages INT,
    totalPages INT,
    indexedChars INT,
    totalChars INT,
    version INT NOT NULL DEFAULT 0,
    synced INT NOT NULL DEFAULT 0,
    FOREIGN KEY (itemID) REFERENCES items(itemID) ON DELETE CASCADE
);
CREATE INDEX fulltextItems_synced ON fulltextItems(synced);
CREATE INDEX fulltextItems_version ON fulltextItems(version);

CREATE TABLE fulltextWords (
    wordID INTEGER PRIMARY KEY,
    word TEXT UNIQUE
);

CREATE TABLE fulltextItemWords (
    wordID INT,
    itemID INT,
    PRIMARY KEY (wordID, itemID),
    FOREIGN KEY (wordID) REFERENCES fulltextWords(wordID),
    FOREIGN KEY (itemID) REFERENCES items(itemID) ON DELETE CASCADE
);
CREATE INDEX fulltextItemWords_itemID ON fulltextItemWords(itemID);

-- Synchronization
CREATE TABLE syncCache (
    libraryID INT NOT NULL,
    key TEXT NOT NULL,
    syncObjectTypeID INT NOT NULL,
    version INT NOT NULL,
    data TEXT,
    PRIMARY KEY (libraryID, key, syncObjectTypeID, version),
    FOREIGN KEY (libraryID) REFERENCES libraries(libraryID) ON DELETE CASCADE,
    FOREIGN KEY (syncObjectTypeID) REFERENCES syncObjectTypes(syncObjectTypeID)
);

CREATE TABLE syncDeleteLog (
    syncObjectTypeID INT NOT NULL,
    libraryID INT NOT NULL,
    key TEXT NOT NULL,
    dateDeleted TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE (syncObjectTypeID, libraryID, key),
    FOREIGN KEY (syncObjectTypeID) REFERENCES syncObjectTypes(syncObjectTypeID),
    FOREIGN KEY (libraryID) REFERENCES libraries(libraryID) ON DELETE CASCADE
);

CREATE TABLE syncQueue (
    libraryID INT NOT NULL,
    key TEXT NOT NULL,
    syncObjectTypeID INT NOT NULL,
    lastCheck TIMESTAMP,
    tries INT,
    PRIMARY KEY (libraryID, key, syncObjectTypeID),
    FOREIGN KEY (libraryID) REFERENCES libraries(libraryID) ON DELETE CASCADE,
    FOREIGN KEY (syncObjectTypeID) REFERENCES syncObjectTypes(syncObjectTypeID) ON DELETE CASCADE
);

CREATE TABLE storageDeleteLog (
    libraryID INT NOT NULL,
    key TEXT NOT NULL,
    dateDeleted TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (libraryID, key),
    FOREIGN KEY (libraryID) REFERENCES libraries(libraryID) ON DELETE CASCADE
);

-- Proxy configuration
CREATE TABLE proxies (
    proxyID INTEGER PRIMARY KEY,
    multiHost INT,
    autoAssociate INT,
    scheme TEXT
);

CREATE TABLE proxyHosts (
    hostID INTEGER PRIMARY KEY,
    proxyID INTEGER,
    hostname TEXT,
    FOREIGN KEY (proxyID) REFERENCES proxies(proxyID)
);
CREATE INDEX proxyHosts_proxyID ON proxyHosts(proxyID);

-- Custom types and fields
CREATE TABLE customItemTypes (
    customItemTypeID INTEGER PRIMARY KEY,
    typeName TEXT,
    label TEXT,
    display INT DEFAULT 1,
    icon TEXT
);

CREATE TABLE customFields (
    customFieldID INTEGER PRIMARY KEY,
    fieldName TEXT,
    label TEXT
);

CREATE TABLE customItemTypeFields (
    customItemTypeID INT NOT NULL,
    fieldID INT,
    customFieldID INT,
    hide INT NOT NULL,
    orderIndex INT NOT NULL,
    PRIMARY KEY (customItemTypeID, orderIndex),
    FOREIGN KEY (customItemTypeID) REFERENCES customItemTypes(customItemTypeID),
    FOREIGN KEY (fieldID) REFERENCES fields(fieldID),
    FOREIGN KEY (customFieldID) REFERENCES customFields(customFieldID)
);
CREATE INDEX customItemTypeFields_fieldID ON customItemTypeFields(fieldID);
CREATE INDEX customItemTypeFields_customFieldID ON customItemTypeFields(customFieldID);

CREATE TABLE customBaseFieldMappings (
    customItemTypeID INT,
    baseFieldID INT,
    customFieldID INT,
    PRIMARY KEY (customItemTypeID, baseFieldID, customFieldID),
    FOREIGN KEY (customItemTypeID) REFERENCES customItemTypes(customItemTypeID),
    FOREIGN KEY (baseFieldID) REFERENCES fields(fieldID),
    FOREIGN KEY (customFieldID) REFERENCES customFields(customFieldID)
);
CREATE INDEX customBaseFieldMappings_baseFieldID ON customBaseFieldMappings(baseFieldID);
CREATE INDEX customBaseFieldMappings_customFieldID ON customBaseFieldMappings(customFieldID);

-- Translator cache
CREATE TABLE translatorCache (
    fileName TEXT PRIMARY KEY,
    metadataJSON TEXT,
    lastModifiedTime INT
);

-- Debug table
CREATE TABLE dbDebug1 (
    a INTEGER PRIMARY KEY
);

-- Data integrity triggers
CREATE TRIGGER insert_creators 
    BEFORE INSERT ON creators
    FOR EACH ROW WHEN NEW.firstName='' AND NEW.lastName=''
BEGIN
    SELECT RAISE (ABORT, 'Creator names cannot be empty');
END;

CREATE TRIGGER update_creators 
    BEFORE UPDATE ON creators
    FOR EACH ROW WHEN NEW.firstName='' AND NEW.lastName=''
BEGIN
    SELECT RAISE (ABORT, 'Creator names cannot be empty');
END;

CREATE TRIGGER fki_collections_parentCollectionID_libraryID
    BEFORE INSERT ON collections
    FOR EACH ROW 
BEGIN
    SELECT RAISE(ABORT, 'insert on table "collections" violates foreign key constraint "fki_collections_parentCollectionID_libraryID"')
    WHERE NEW.parentCollectionID IS NOT NULL AND
          NEW.libraryID != (SELECT libraryID FROM collections WHERE collectionID = NEW.parentCollectionID);
END;

CREATE TRIGGER fku_collections_parentCollectionID_libraryID
    BEFORE UPDATE ON collections
    FOR EACH ROW 
BEGIN
    SELECT RAISE(ABORT, 'update on table "collections" violates foreign key constraint "fku_collections_parentCollectionID_libraryID"')
    WHERE NEW.parentCollectionID IS NOT NULL AND
          NEW.libraryID != (SELECT libraryID FROM collections WHERE collectionID = NEW.parentCollectionID);
END;

CREATE TRIGGER fki_collectionItems_libraryID
    BEFORE INSERT ON collectionItems
    FOR EACH ROW 
BEGIN
    SELECT RAISE(ABORT, 'insert on table "collectionItems" violates foreign key constraint "fki_collectionItems_libraryID"')
    WHERE (SELECT libraryID FROM collections WHERE collectionID = NEW.collectionID) != 
          (SELECT libraryID FROM items WHERE itemID = NEW.itemID);
END;

CREATE TRIGGER fku_collectionItems_libraryID
    BEFORE UPDATE ON collectionItems
    FOR EACH ROW 
BEGIN
    SELECT RAISE(ABORT, 'update on table "collectionItems" violates foreign key constraint "fku_collectionItems_libraryID"')
    WHERE (SELECT libraryID FROM collections WHERE collectionID = NEW.collectionID) != 
          (SELECT libraryID FROM items WHERE itemID = NEW.itemID);
END;

CREATE TRIGGER fki_collectionItems_itemID_parentItemID
    BEFORE INSERT ON collectionItems
    FOR EACH ROW 
BEGIN
    SELECT RAISE(ABORT, 'insert on table "collectionItems" violates foreign key constraint "fki_collectionItems_itemID_parentItemID"')
    WHERE NEW.itemID IN (
        SELECT itemID FROM itemAttachments WHERE parentItemID IS NOT NULL 
        UNION 
        SELECT itemID FROM itemNotes WHERE parentItemID IS NOT NULL
    );
END;

CREATE TRIGGER fku_collectionItems_itemID_parentItemID
    BEFORE UPDATE OF itemID ON collectionItems
    FOR EACH ROW 
BEGIN
    SELECT RAISE(ABORT, 'update on table "collectionItems" violates foreign key constraint "fku_collectionItems_itemID_parentItemID"')
    WHERE NEW.itemID IN (
        SELECT itemID FROM itemAttachments WHERE parentItemID IS NOT NULL 
        UNION 
        SELECT itemID FROM itemNotes WHERE parentItemID IS NOT NULL
    );
END;

CREATE TRIGGER fku_itemAttachments_parentItemID_collectionItems_itemID
    BEFORE UPDATE OF parentItemID ON itemAttachments
    FOR EACH ROW WHEN OLD.parentItemID IS NULL AND NEW.parentItemID IS NOT NULL 
BEGIN
    DELETE FROM collectionItems WHERE itemID = NEW.itemID;
END;

CREATE TRIGGER fku_itemNotes_parentItemID_collectionItems_itemID
    BEFORE UPDATE OF parentItemID ON itemNotes
    FOR EACH ROW WHEN OLD.parentItemID IS NULL AND NEW.parentItemID IS NOT NULL 
BEGIN
    DELETE FROM collectionItems WHERE itemID = NEW.itemID;
END;

CREATE TRIGGER fki_tags
    BEFORE INSERT ON tags
    FOR EACH ROW 
BEGIN
    SELECT RAISE(ABORT, 'Tag cannot be blank')
    WHERE TRIM(NEW.name)='';
END;

CREATE TRIGGER fku_tags
    BEFORE UPDATE OF name ON tags
    FOR EACH ROW 
BEGIN
    SELECT RAISE(ABORT, 'Tag cannot be blank')
    WHERE TRIM(NEW.name)='';
END;

The Zotero database is organized around several core entities:

• Libraries: Each user has at least one library (their personal library) and may have access to group libraries.
• Items: These represent bibliographic entries. Each item has a type (e.g., book, journal article) and belongs to a library. The items table contains the core item metadata shared by all item types, including the item type, date added, date modified, and library ID.
• Relations: Items can be related to other items.
• Item Notes: These are user-generated notes associated with items.
• Item Attachments: These are full-text files associated with items.
• Item Annotations: These are user-generated annotations associated with attachments.
• Collections: These act like folders that can contain items and other collections (subfolders).
• Creators: Authors and contributors are stored in the creators table and linked to items via itemCreators.
• Tags: Items can be tagged, and tags are stored in the tags table and linked via itemTags.
• Item Types: These are base and custom (user-defined) item types, with mappings to both base and custom fields, field formats, and field sequencing information.
• Item Data: Values for item fields are stored using the EAV pattern in the itemData table, which links items to fields and their values.

The Mermaid diagram below visualizes the relationships between these tables.

erDiagram
    libraries {
        INTEGER libraryID PK
        TEXT type
        INTEGER editable
        INTEGER filesEditable
        INTEGER version
        INTEGER storageVersion
        INTEGER lastSync
        INTEGER archived
    }
    items {
        INTEGER itemID PK
        INTEGER itemTypeID FK
        TIMESTAMP dateAdded
        TIMESTAMP dateModified
        TIMESTAMP clientDateModified
        INTEGER libraryID FK
        TEXT key
        INTEGER version
        INTEGER synced
    }
    itemData {
        INTEGER itemID PK,FK
        INTEGER fieldID PK,FK
        INTEGER valueID FK
    }
    fieldsCombined {
        INTEGER fieldID PK
        TEXT fieldName
        TEXT label
        INTEGER fieldFormatID FK
        INTEGER custom
    }
    fieldFormats {
        INTEGER fieldFormatID PK
        TEXT regex
        INTEGER isInteger
    }
    itemCreators {
        INTEGER itemID PK,FK
        INTEGER creatorID PK,FK
        INTEGER creatorTypeID PK,FK
        INTEGER orderIndex PK
    }
    creatorTypes {
        INTEGER creatorTypeID PK
        TEXT creatorType
    }
    creators {
        INTEGER creatorID PK
        TEXT firstName
        TEXT lastName
        INTEGER fieldMode
    }
    itemAttachments {
        INTEGER itemID PK,FK
        INTEGER parentItemID FK
        INTEGER linkMode
        TEXT contentType
        INTEGER charsetID FK
        TEXT path
        INTEGER syncState
        INTEGER storageModTime
        TEXT storageHash
        INTEGER lastProcessedModificationTime
    }
    charsets {
        INTEGER charsetID PK
        TEXT charset
    }
    itemNotes {
        INTEGER itemID PK,FK
        INTEGER parentItemID FK
        TEXT note
        TEXT title
    }
    itemAnnotations {
        INTEGER itemID PK,FK
        INTEGER parentItemID FK
        INTEGER type
        TEXT authorName
        TEXT text
        TEXT comment
        TEXT color
        TEXT pageLabel
        TEXT sortIndex
        TEXT position
        INTEGER isExternal
    }
    relationPredicates {
        INTEGER predicateID PK
        TEXT predicate
    }
    itemRelations {
        INTEGER itemID PK,FK
        INTEGER predicateID PK,FK
        TEXT object PK
    }
    itemTags {
        INTEGER itemID PK,FK
        INTEGER tagID PK,FK
        INTEGER type
    }
    tags {
        INTEGER tagID PK
        TEXT name
    }
    collections {
        INTEGER collectionID PK
        TEXT collectionName
        INTEGER parentCollectionID FK
        TIMESTAMP clientDateModified
        INTEGER libraryID FK
        TEXT key
        INTEGER version
        INTEGER synced
    }
    collectionItems {
        INTEGER collectionID PK,FK
        INTEGER itemID PK,FK
        INTEGER orderIndex
    }
    itemTypes {
        INTEGER itemTypeID PK
        TEXT typeName
        INTEGER templateItemTypeID
        INTEGER display
    }
    itemTypeFields {
        INTEGER itemTypeID PK,FK
        INTEGER fieldID FK
        INTEGER hide
        INTEGER orderIndex PK
    }
    fields {
        INTEGER fieldID PK
        TEXT fieldName
        INTEGER fieldFormatID FK
    }
    itemTypeCreatorTypes {
        INTEGER itemTypeID PK,FK
        INTEGER creatorTypeID PK,FK
        INTEGER primaryField
    }
    baseFieldMappings {
        INTEGER itemTypeID PK,FK
        INTEGER baseFieldID PK,FK
        INTEGER fieldID PK,FK
    }
    itemDataValues {
        INTEGER valueID PK
        TEXT value
    }

    libraries ||--o{ items : "libraryID"
    libraries ||--o{ collections : "libraryID"
    items ||--o{ itemData : "itemID"
    items ||--o{ itemCreators : "itemID"
    items ||--o{ itemAttachments : "itemID"
    items |o--o{ itemAttachments : "parentItemID"
    items ||--o{ itemNotes : "itemID"
    items |o--o{ itemNotes : "parentItemID"
    items }o--|| itemTypes : "itemTypeID"
    items ||--o{ itemRelations : "itemID"
    items ||--o{ itemTags : "itemID"
    items ||--o{ collectionItems : "itemID"
    itemAttachments ||--o{ itemAnnotations : "parentItemID"
    itemTags }o--|| tags : "tagID"
    collectionItems }o--|| collections : "collectionID"
    collections |o--o{ collections : "parentCollectionID"
    itemAttachments }o--o| charsets : "charsetID"
    itemData }o--|| itemDataValues : "valueID"
    itemData }o--|| fieldsCombined : "fieldID"
    itemRelations }o--|| relationPredicates : "predicateID"
    itemCreators }o--|| creators : "creatorID"
    itemCreators }o--|| creatorTypes : "creatorTypeID"
    creatorTypes }o--|| itemTypeCreatorTypes : "creatorTypeID"
    itemTypeCreatorTypes }o--|| itemTypes : "itemTypeID"
    itemTypes ||--o{ itemTypeFields : "itemTypeID"
    itemTypeFields }o--|| fields : "fieldID"
    itemTypes ||--o{ baseFieldMappings : "itemTypeID"
    baseFieldMappings }o--|| fields : "baseFieldID"
    baseFieldMappings }o--|| fields : "fieldID"
    fieldsCombined }o--o| fieldFormats : "fieldFormatID"
    fields }o--o| fieldFormats : "fieldFormatID"

Loading