PostgreSQL Facade
Relevant source files
The following files were used as context for generating this wiki page:
The PostgreSQL Facade manages the transition of the PRKS canonical relational store from SQLite to PostgreSQL. It provides a PostgresLibrary class composed of mixins that port methods from the legacy SQLite implementation to PostgreSQL. This system ensures architectural continuity while enabling authenticated multi-user and multi-library operations.
Sources: AGENTS.md:144-149, backend/AGENTS.md:7-14
Architecture and Implementation
The facade acts as a transition layer between the application logic and the PostgreSQL database. The core component, PostgresLibrary, is a modular monolith composed of lane-specific mixins. Each mixin contains methods that are registered as "ported" when they are ready for production use.
Methods not yet ported to PostgreSQL raise a NotPortedError. This design allows developers to migrate domain boundaries incrementally without a full system rewrite.
Sources: AGENTS.md:144-149, backend/AGENTS.md:162-166
Transition Workflow
The migration follows a structured lane-based approach. Each lane edits only the SQL baseline files it owns and regenerates the schema snapshot.
The diagram shows the logical flow of a method transition from the SQLite implementation to the PostgreSQL Facade.
Sources: AGENTS.md:144-149, backend/AGENTS.md:162-166
Database Schema and Extensions
The PostgreSQL implementation utilizes specialized schemas and extensions to maintain parity with the Python-based text processing used in the SQLite version.
Schemas and Extensions
The system uses the following schemas:
ext: Reserved for PostgreSQL extensions.prks: Contains the primary application logic and tables.derived: Holds rebuildable projections and search indexes.
Standard extensions include pg_trgm for trigram matching and unaccent for diacritic folding.
Sources: backend/db/schema/000_extensions.sql:1-12
Text Identity and Search Normalization
The facade maintains strict text identity rules to ensure that name lookups are consistent between Python and SQL. This is handled by the prks.identity_key function, which performs full case folding, whitespace collapsing, and NFC normalization.
| Function | Logic | Purpose |
|---|---|---|
prks.identity_key | casefold(collapse_whitespace(trim(NFC(s)))) | Determines if two names are "the same". |
prks.search_norm | unaccent(casefold(normalize(s, NFC))) | Normalizes text for search indexing. |
Sources: backend/db/schema/000_extensions.sql:14-43
Integration Mixins
The PostgresLibrary is built by combining several specialized mixin classes. Each mixin handles a specific domain of the library's functionality.
Works Mixin
The WorksPostgresMixin (from backend/pg_facade/works.py) manages the persistence of research material records. It ensures that managed PDF identities and metadata are correctly mapped to the PostgreSQL schema.
Organization Mixin
The OrganizationPostgresMixin (from backend/pg_facade/organization.py) handles structured research relationships, including Folders, Playlists, and Saved Views.
The class diagram represents the composition of the PostgresLibrary through domain-specific mixins.
Sources: AGENTS.md:144-149, backend/pg_facade/works.py, backend/pg_facade/organization.py
Operation and Security
The PostgreSQL Facade enforces strict access control through a library-scoping mechanism. Every command or query executed through the facade must respect the following invariants:
- Library ID Enforcement: Commands use
SET LOCAL prks.library_idto restrict row access within a transaction. - Actor and Capability: The system enforces actor permissions and capability checks before executing database operations.
- Epoch Fencing: A library maintenance lock and epoch fence prevent concurrent modifications during sensitive operations.
Sources: AGENTS.md:162-178, backend/AGENTS.md:162-171
Conclusion
The PostgreSQL Facade provides the necessary infrastructure for PRKS to move from a single-user SQLite application to a multi-user PostgreSQL platform. By utilizing a mixin-based architecture and strict text normalization, it maintains functional parity with legacy systems while preparing the backend for long-term scalability.