Skip to main content
Version: v5.5.0

Database and Migrations

OpenReader keeps accounts, settings, document records and reading progress in SQL. File contents and generated audio live in object storage.

SQLite or Postgres​

DatabaseWhen it is usedGood for
SQLite (default)POSTGRES_URL is unset. The file is docstore/sqlite3.db; set SQLITE_DB_PATH to move it.A single server, Docker Compose, the desktop app.
PostgresPOSTGRES_URL is set.Production, several app instances, Vercel.

What it stores​

DataTables
Accounts and sessions (generated by Better Auth)user, session, account, verification
Documents, folders and their settingsdocuments, user_folders, document_settings, document_blob_leases
Reading progress and bookmarksuser_document_progress, user_document_bookmarks
Preferences and onboardinguser_preferences, user_onboarding
Previews (status and references, not images)document_previews
Admin providers and settingsadmin_providers, admin_settings
Compute limits and usagecompute_limit_admissions, compute_limit_buckets, compute_limit_events
Scheduled tasksscheduled_tasks
Storage cleanup queuestorage_cleanups
Audiobook requests and audio usageaudiobook_requests, document_audio_usage
OAuth apps and grants (generated by Better Auth)oauth_client, oauth_client_resource, oauth_client_assertion, oauth_consent, oauth_access_token, oauth_refresh_token, oauth_resource, device_code, jwks

Document files, generated audio, audiobooks and preview images are not in the database. Playback state belongs to the compute worker, which never connects to the database.

Settings, progress and bookmarks sync between devices when the app loads or saves them. There is no live push between open tabs.

Migrations​

Migrations run automatically every time OpenReader starts, through Docker, pnpm start, pnpm dev or the desktop app. Startup runs two steps:

  1. Database migrations (pnpm migrate) for whichever database is configured.
  2. v4 storage cleanup (pnpm migrate-decommission), which deletes storage folders retired in v5. It does nothing once they are gone.

The startup log shows Running database migrations... and Running v4 legacy storage decommission....

To skip either step, set RUN_DRIZZLE_MIGRATIONS=false or RUN_V4_DECOMMISSION=false. Then run them yourself before serving traffic.

:::warning Vercel and other custom deployments Deployments without the bootstrap entrypoint, including Vercel, do not run migrations on start. Run pnpm migrate against the production database before deploying a new version. See Vercel + External Worker. :::

Upgrading from v4 runs irreversible migrations and deletes old audio. Follow Upgrade from v4.

Schema history​

Migrations apply in order to both SQLite and Postgres. A v4.4 database keeps its users, documents, progress, folders, preferences, providers and settings when upgraded.

MigrationWhat it does
0000Baseline documents, preferences, progress, auth, previews and legacy audiobooks.
0001–0003The pre-v5 TTS segment cache.
0004Encrypted shared providers, admin settings and admin users.
0005–0009Document settings and transitional PDF state, later removed.
0010Cleanup cascades for user data.
0011Scheduled tasks and document blob leases.
0012Folders, onboarding and recently opened documents.
0013–0014Transitional playback sessions used during v5 development.
0015Removes the pre-v5 TTS cache, transitional playback sessions and legacy audiobook tables.
0016Records which identity provider issued each sign-in account.
0017–0019Compute admissions, usage metering and the maintained limit policy.
0020User approval status and admin grant source; converts the old sign-up switch to signupPolicy.
0021Bookmarks, document author and language, and reading positions saved as playback positions.
0022Removes the sign-in account issuer added in 0016.
0023Removes Speech SDK providers, and clears the default provider if it was one.
0024Default model options on admin providers.
0025Removes DeepInfra provider setups, and clears the default provider if it was one. Existing audio is untouched.
0026Queue for retrying storage cleanup. Document settings and reading progress are now deleted with their document.
0027Public API: OAuth and signing-key tables, audiobook requests, audio usage and the guest retention notice. Drops device and IP keys from compute admissions and clears stored session IPs.

To create new migrations when changing the schema, see Local Development.