profilarr/docs/backend/database.md

App Database

Source: src/lib/server/db/ (db.ts, migrations.ts, migrations/, queries/)

The app database (profilarr.db) is a SQLite file that stores all application state: Arr instances, settings, job queue, notifications, sessions, and PCD ops. It uses a singleton DatabaseManager, migration-based schema management, and raw SQL query modules with parameter binding. Kysely is not used here -- it's reserved for the PCD cache (see pcd.md).

Table of Contents

• Initialization
• Schema
• Migrations
  • Adding a Migration
• Query Layer

Initialization

The database initializes early in the server startup sequence (src/hooks.server.ts):

1. config.init() -- ensure data directories exist
2. db.initialize() -- open SQLite connection, set pragmas
3. runMigrations() -- apply pending schema changes
4. Load log settings, initialize PCD manager, start job queue

Three pragmas are set on every connection:

Pragma	Value	Purpose
foreign_keys	ON	Enforce referential integrity
journal_mode	WAL	Write-Ahead Logging for concurrency
synchronous	NORMAL	Balanced performance/safety

The manager includes HMR recovery for development: isHealthy() runs SELECT 1 to verify the connection is alive. If the check fails during initialization (common after Vite hot reloads), the manager closes and reinitializes the connection.

Transaction support is available via transaction<T>(fn), an async wrapper that auto-commits on success and auto-rollbacks on error. Raw beginTransaction(), commit(), and rollback() methods are also exposed.

Schema

src/lib/server/db/schema.sql is a reference snapshot of the current schema after all migrations have been applied. Its header reads:

DO NOT execute this file directly -- use migrations instead.

The file is useful for understanding the final table structure, reviewing database design, and onboarding new contributors. It is not used at runtime. Migrations are the source of truth for schema changes.

Migrations

Source: src/lib/server/db/migrations/ (sequentially numbered NNN_*.ts) Runner: src/lib/server/db/migrations.ts

Each migration exports an object matching the Migration interface:

Field	Required	Purpose
version	yes	Sequential number (001, 002, ...)
name	yes	Human-readable description
up	yes	SQL to apply the migration
down	no	SQL to roll back (omit if not rollbackable)
afterUp	no	Callback for data migrations after schema DDL

The runner applies pending migrations in version order. Each up block runs inside a transaction. The afterUp callback runs outside the transaction, which is useful for data migrations that need the new schema committed first.

A migrations table tracks applied versions with timestamps. The runner supports up (apply pending), down (roll back to target version), and fresh (reset and reapply all).

Adding a Migration

1. Review migrations/_template.ts as your starting point.
2. Number sequentially after the highest existing migration.
3. Write the up SQL. Provide down if the migration is rollbackable.
4. Add the import and entry in migrations.ts inside loadMigrations().
5. Test both fresh (db.fresh()) and incremental application.
6. Update schema.sql to reflect the new state.

Rule: Never modify an already-applied migration. If a migration has a bug, create a new migration to fix it.

Query Layer

Source: src/lib/server/db/queries/

Each module covers one database table and exports a queries object with typed methods. All use raw SQL with ? parameter binding and typed input/output interfaces.

Module	Domain
arrInstances	Arr instance CRUD
arrSync	Sync config and status per instance
arrCleanupSettings	Stale config cleanup settings
arrRenameSettings	Rename job settings per instance
authSettings	Session duration, API key
sessions	Session CRUD and cleanup
jobQueue	Job queue CRUD, claim, reschedule
jobRunHistory	Job execution history
pcdOps	PCD operation CRUD and filtering
pcdOpHistory	Per-compile op application results
notificationServices	Notification service config
logSettings	Log level and output settings
renameRuns	Rename job run history
upgradeRuns	Upgrade job run history
parsedReleaseCache	Parser result cache
patternMatchCache	Regex match result cache
githubCache	GitHub API response cache
tmdbSettings	TMDB API key storage
aiSettings	AI service settings
appInfo	App metadata and setup state

Query methods follow a consistent pattern: typed input interfaces for mutations, generic return types for reads, and the singleton db instance for all operations.