The SQLite library and migrations
All user data lives in one SQLite file, opened by openDb(path) in core/src/db.ts. This page covers where the file is, what is in it, how sync changes are recorded, and how to change the schema.
Where the library is
core/src/library-location.ts is the single rule. The app, the connector host (read-only, it never adopts a legacy path) and the Word add-in (word/addin/Settings.cs mirrors it in C#) all use it. Nothing in the rule names a user. The order in resolveLibrary() is:
ZN_DB, thenZN_LIBRARY(environment).- The saved setting:
<config>\zotero-next-gpui\library.json,{"path": "<library.sqlite>"}. - The legacy location
<home>\zotero-next\data\library.sqlite, if it exists. It is adopted: written to the saved setting, so the library keeps opening from there. - The default
<data>\zotero-next\library.sqlite, if it exists. - Nothing found:
pathis null and the app shows the first-run screen (gpui-ui/src/firstrun/). A new library goes to the default path, orlibrary-2.sqlite,library-3.sqliteand so on beside it when the name is taken; an existing file is never overwritten.
isRealLibrary(path) is true for the saved, legacy and default paths. The in-process core refuses them unless ZN_ALLOW_REAL_DB=1, so a development run does not open your real library by accident. Use a copy:
bun -e 'import {Database} from "bun:sqlite"; new Database("src.sqlite",{readonly:true}).run("VACUUM INTO \"copy.sqlite\"")'Files next to the database file:
| File or folder | Purpose |
|---|---|
storage/<KEY>/ |
Imported attachment files (for example a PDF saved by Find Full Text) |
translators/ |
A translators folder written by update-translators.ts; see Translators |
library.backup-v<from>-to-v<to>-<time>.sqlite |
Pre-upgrade backups (below) |
library.backup-gpui-<date>.sqlite |
One backup made by the packaged app before it first writes to a library (skipped if one exists) |
connector-host-state.json |
Connector-host state: the last chosen collection and in-flight save sessions |
The app’s own small state files (library.json, ui-state.json, startup.json, prefetch-hint.json) and logs live in <config>\zotero-next-gpui\, not beside the library.
<config> and <data> come from core/src/platform-dirs.ts:
<config> (settings, logs) |
<data> (default library) |
|
|---|---|---|
| Windows | %APPDATA% |
%APPDATA% |
| macOS | ~/Library/Application Support |
~/Library/Application Support |
| Linux | $XDG_CONFIG_HOME (~/.config) |
$XDG_DATA_HOME (~/.local/share) |
An explicit APPDATA in the environment wins on every OS, so tests and isolated runs can point both at a scratch folder.
Connection settings
openDb runs the migrations (below), then sets synchronous = NORMAL, busy_timeout = 5000, foreign_keys = ON, cache_size = -65536, temp_store = MEMORY and mmap_size = 268435456. The journal mode is WAL (the migration runner sets it before it starts). WAL plus the busy timeout is what lets several programs share the file: the app, the MCP stdio server (core/scripts/mcp-stdio.ts), the connector host and the read-only Word add-in. Read-only connections, such as the prefetch worker’s, never migrate and set query_only.
Schema
core/schema.sql defines the base tables. Items are stored as Zotero Web API JSON in items.data; the other columns are copies for querying. Keys are Zotero’s 8-character keys.
| Table | Content |
|---|---|
meta |
Name/value rows: schema_version, zotero_import_at, venue_version |
items |
One row per item (regular, attachment, note); parent_key, title, creators_summary, year, publication, data, dates, version, deleted (1 = trash) |
collections, collection_items |
Collection tree and membership |
item_tags |
Tags (type 0 manual, 1 automatic) |
attachments |
One row per attachment item: link mode, content type, path, URL |
annotations, view_state |
Reader annotations and per-attachment view state, in the reader’s own JSON shape |
fulltext |
Extracted text per attachment |
search |
FTS5 index (porter, unicode61) over title, creators, abstract, publication, tags, notes and full text; rebuilt by core/src/search.ts |
libraries |
Migration 4. One row per library: My Library is id 1; groups have type = 'group', a group_id, editable/files_editable flags, per-library sync versions and removed. items, collections and sync_outbox carry library_id (default 1). Keys stay globally unique; a pull that finds a key already used by another library skips that object and counts a collision. A trigger makes child rows inherit the parent’s library, and assertWritable in core/src/libraries.ts refuses writes to read-only libraries (403) |
quick_search |
FTS5 trigram index (migration 3) over title, creators, year and citation key of top-level items, for the toolbar search box. rowid = items.rowid. Triggers on items and bbt_keys keep it current; terms under 3 characters fall back to LIKE. rebuildSearchIndex rebuilds it |
Further schema files are applied by the baseline migration: core/src/sync/schema.sql (sync state and outbox), core/src/websync/schema.sql (zotero.org sync) and core/src/bbt/schema.sql (Better BibTeX). core/src/searches/schema.sql (saved searches) and the duplicates tables are applied lazily by their modules.
The sync outbox and triggers
core/src/sync/schema.sql creates triggers (after insert, update and delete, as applicable) on items, collections, collection_items, item_tags, attachments and annotations that record every local change in sync_outbox. A row is (seq, type, key, op, at) with type one of item, collection, annotation and op one of upsert, trash, restore, delete. Rows are deduplicated per (type, key, op): an upsert moves the entry to a fresh seq, so a change made while a push is in flight survives the push’s “delete everything up to the snapshot” cleanup.
The triggers are skipped while sync_flags holds a suppress row. Sync sets it inside its own transaction while it applies remote changes, and any migration or bulk job that rewrites data the user did not change must do the same, or every row would be queued for upload. Other sync tables: sync_state, sync_base (last known modification time per key) and sync_conflicts.
Exactly one sync route runs per library; sync_state records which (zotero-local, zotero-org or off).
Migrations
The code is core/src/migrations/: index.ts (the runner), list.ts (the ordered list), types.ts, baseline.ts (migration 1) and one file per later migration (venues.ts is migration 2, quick-search.ts is migration 3). meta.schema_version holds the id of the last migration applied. SCHEMA_HEAD is the id of the last migration in the list.
What migrate(db, path) does:
- Reads
schema_version(0 means a new, empty file). If it is newer thanSCHEMA_HEAD, it throwsLibraryTooNewErrorand changes nothing; the app must be updated to open that library. - If the library is already current, it returns without touching the file.
- Otherwise, for an existing library that has items, it first makes a backup with
VACUUM INTO library.backup-v<from>-to-v<to>-<time>.sqlitenext to the database and keeps the newest three such files. Other backups are never touched. If the backup cannot be made, nothing is upgraded. - Then, in one
BEGIN IMMEDIATEtransaction with foreign keys switched off, it runs every pending migration in order, recordingschema_versionafter each, then runsPRAGMA integrity_checkandPRAGMA foreign_key_check(new violations count) and commits. - Any throw or failed check rolls everything back and raises
MigrationError, whose message names the failing migration and the backup. The file is unchanged.
Only openDb migrates. Another process may migrate while you wait for the write lock; the runner re-reads the version after taking it.
How to add a migration
- Create
core/src/migrations/<name>.tsexporting aMigration:{ id, name, up(db) }. Theidis the next number after the last entry inMIGRATIONS(1, 2, 3 with no gaps; the runner rejects a gap).nameis a short kebab-case label used in error messages. - Append it to
MIGRATIONSincore/src/migrations/list.ts. - In
up(db)use plaindb.run,db.execanddb.query. Do notBEGIN,COMMITor calldb.transaction(); the runner owns the transaction. To change a table’s shape, create the new table, copy, drop the old one and rename it; foreign keys are off during the run and checked afterwards. - Do not change tables or indexes in the
*.sqlfiles to make the change. They are the version 1 baseline; express the change as a migration so existing libraries get it too. - If a rule that fills a derived column changes (for example
core/src/venue.ts), add a migration that recomputes the column.recomputeVenuesinvenues.tsis the model. - Never edit, reorder or delete a migration that has been released.
- Add a test (below).
A hypothetical migration that adds an index (the name and id are illustrative):
import type { Database } from "bun:sqlite";import type { Migration } from "./types";
export const attachmentUrlIndex: Migration = { id: 3, name: "attachment-url-index", up(db: Database) { db.run("CREATE INDEX IF NOT EXISTS attachments_url ON attachments(url)"); },};import { baseline } from "./baseline";import { venues2 } from "./venues";import { attachmentUrlIndex } from "./attachmentUrlIndex";import type { Migration } from "./types";
export const MIGRATIONS: readonly Migration[] = [baseline, venues2, attachmentUrlIndex];A migration that rewrites data follows venues.ts: hold the suppress flag so the outbox stays empty, change the rows, then release it.
up(db: Database) { db.run("INSERT OR IGNORE INTO sync_flags(name) VALUES('suppress')"); // ... UPDATE the derived column for every affected row ... db.run("DELETE FROM sync_flags WHERE name = 'suppress'");},Make the data step safe to run twice (for example by checking a meta row, as migration 2 does with venue_version), because a library from an older build may have run the change before versioning existed.
Tests
core/test/migrations.test.ts covers the runner: the list is 1..n, a fresh library is created at the head with no backup, an old library is upgraded with a backup, only the last three backups are kept, a throwing migration or a new foreign-key violation rolls back, a newer library is refused untouched, and read-only connections never migrate. Add a case there whenever the migration rewrites data: build an old-format library (the file has a makeV1Library() helper that sets schema_version to 1), open it with openDb, and assert the rows. To test a runner behaviour with your own steps, call migrate(db, path, { migrations: [...] }); MigrateOptions also takes now (a clock for backup names) and keepBackups. Run it with cd core && bun test test/migrations.test.ts.
If your migration changes what the importer, sync or API read, also run the full core suite; many tests create libraries through openDb, so they exercise every migration.