Database Schema & Migrations

Relevant source files

The following files were used as context for generating this wiki page:

• backend/db_migrations.py
• backend/db_manager.py
• backend/AGENTS.md
• backend/db/schema/000_extensions.sql
• tests/test_db_migrations.py
• tests/test_schema_change_gate.py

PRKS uses a relational database system to store research metadata, library structure, and synchronization state. The system currently implements a SQLite backend for local-first persistence, with an ongoing transition toward a PostgreSQL server architecture for multi-user support. The project manages schema evolution through an ordered, transactional migration system that ensures database integrity during updates.

Sources: README.md:16-18, backend/AGENTS.md:7-14, backend/db_migrations.py:12-25

Database Architecture

The system maintains a canonical relational store in prks_data.db. SQLite handles local storage while the PRKSDatabase class in backend/db_manager.py provides the primary interface for data operations.

Key Database Components

The following diagram illustrates the relationship between the management layer and the physical storage:

The system distinguishes between canonical research data and rebuildable derived indexes. Canonical state is backed up, while derived indexes like PDF text search are rebuilt after restoration.

Sources: backend/db_manager.py:461-485, backend/AGENTS.md:100-112, README.md:139-142

Schema Migration System

PRKS automatically upgrades older databases at startup. The migration system is version-ordered and transactional. If a migration fails, the system rolls back the transaction to preserve the previous valid state.

Migration Logic

The migration runner compares the current version in the schema_version table against LATEST_SCHEMA_VERSION. It then applies every missing Migration object in sequence.

Component	Description
schema_version table	Stores a single integer representing the current database revision.
LATEST_SCHEMA_VERSION	A constant in backend/db_migrations.py defining the current target version.
Migration Dataclass	Holds target_version, name, and an apply function for a specific update.
MIGRATIONS Tuple	The ordered registry of all available migration steps.

Sources: backend/db_migrations.py:12-25, backend/db_migrations.py:33-40, backend/db_migrations.py:1000-1025, backend/db_manager.py:487-493

Migration Safety Rules

1. Refuse Downgrades: PRKS refuses to open databases created by a newer version of the application.
2. Transactional Integrity: Each migration step must commit successfully before the version number increments.
3. No Filesystem Mutation: Migrations may only modify database state; they must not touch managed filesystem data like PDFs.
4. Schema Signature: The system validates the database structure using application_schema_signature to detect unauthorized manual drift.

Sources: backend/db_migrations.py:1040-1060, backend/AGENTS.md:300-320, tests/test_db_migrations.py:320-335

Entity Relationship Model

The schema organizes data into several core entity families. Primary keys are generated using a distributed-safe format (e.g., W- for Works, P- for Persons) to facilitate synchronization.

Core Tables Summary

Table	Purpose
works	Canonical metadata for research items (PDFs, Videos, Notes).
persons	Profile data for authors, editors, and researchers.
roles	Links Persons to Works with specific roles (e.g., Author, Translator).
folders	Hierarchical library organization.
tags	Flat vocabulary for categorizing Works and Folders.
sync_entity_revisions	Tracking table for local-first synchronization and conflict resolution.

Sources: backend/db_manager.py:302-328, backend/db_manager.py:1550-1570, backend/db_migrations.py:1300-1350

Validation & Change Gates

The project uses static analysis and runtime checks to prevent incorrect schema changes. Developers must follow a specific workflow for schema updates:

1. Increment LATEST_SCHEMA_VERSION.
2. Add an ordered migration function in backend/db_migrations.py.
3. Update the baseline backend/db_schema.sql to match the final state.

scripts/check_schema_change.py enforces these rules during the development process, ensuring that every table upserted with ON CONFLICT is registered in _CURRENT_TABLE_PKS.

Sources: backend/AGENTS.md:300-315, tests/test_schema_change_gate.py:20-50

Summary

The PRKS database system provides a robust foundation for a local-first research library. It uses a SQLite core managed by a transactional migration runner that ensures schema consistency across updates. As the project moves toward PostgreSQL, the architecture maintains strict separation between canonical metadata and rebuildable derived data, while using a rigorous change-gate process to ensure schema integrity.