Relational Databases & Schemas
Relevant source files
The following files were used as context for generating this wiki page:
Introduction
The tldw_server project utilizes a decentralized relational database architecture primarily based on SQLite, with support for PostgreSQL in multi-user environments. The system is designed to provide strict logical isolation of user data by employing a "per-user" database strategy for content, notes, and specific application modules like Kanban boards. Centralized databases are reserved for system-wide concerns such as authentication, authorization (AuthNZ), and global evaluations.
The core philosophy of the database layer is to centralize all storage access via the core/DB_Management/ module, ensuring that API endpoints remain thin and do not execute raw SQL directly. This architecture supports high-level features including media ingestion, full-text search (FTS5), vector storage integration, and complex workflow orchestration.
Sources: Docs/Architecture.md:27-31, AGENTS.md:124-131, CLAUDE.md:125-132
Database Architecture & Layout
The system categorizes databases into two main types: Centralized and Per-User. This separation ensures scalability and privacy, particularly when the system is configured in multi_user mode.
Database Storage Structure
The root directory for user-specific data is defined by <USER_DB_BASE_DIR>, which defaults to Databases/user_databases/.
| Database Type | File/Instance Name | Scope | Purpose |
|---|---|---|---|
| AuthNZ DB | users.db (SQLite) or PostgreSQL | Centralized | Stores user credentials, permissions, and session data. |
| Media DB v2 | Media_DB_v2.db | Per-User | Stores ingested media metadata, chunks, and FTS5 indexes. |
| Notes/Chats | ChaChaNotes.db | Per-User | Stores notes, chat history, and character data. |
| Prompts | user_prompts_v2.sqlite | Per-User | Stores user-defined prompts and Prompt Studio artifacts. |
| Evaluations | evaluations.db | Centralized | Audit logs, metrics, and RAG evaluation results. |
| Kanban | kanban.db | Per-User | Boards, lists, cards, and workflow control state. |
Sources: Docs/Architecture.md:179-218, AGENTS.md:124-131, CLAUDE.md:125-132
Multi-Tenancy Logic
The mapping between a user and their data is handled through a user_id derived from the AuthNZ layer (either an API Key or JWT).
This diagram illustrates how user requests are routed to specific isolated database files based on their identity.
Sources: Docs/Architecture.md:231-235, AGENTS.md:124-131
Kanban Workflow Control Plane
A significant specialized schema within the project is the Kanban workflow engine. It introduces a "policy-enforced" control plane designed for safe autonomous orchestration. This system extends basic card management with state-machine logic, leases, and idempotency.
Workflow Schema Components
The Kanban system utilizes several specialized tables to manage state transitions and prevent race conditions during automated operations.
- board_workflow_policies: Defines the high-level rules for a specific board's workflow.
- board_workflow_statuses: Catalog of valid states (e.g., Todo, In Progress, Done).
- board_workflow_transitions: Defines valid paths between statuses.
- kanban_card_workflow_state: Tracks the current version, lease owner, and status of a card.
- kanban_card_workflow_events: Audit log of all state changes.
- kanban_card_workflow_approvals: Manages manual or automated sign-offs required for transitions.
Sources: Docs/Plans/2026-03-04-kanban-safe-orchestrator-workflow-primitives-implementation-plan.md:16-32
Safe Orchestration Contract
The database implementation for workflows enforces a strict contract to ensure that autonomous agents (orchestrators) do not overwrite each other's work.
- Lease Requirement: A card must be "claimed" (
claim_card_workflow) by an owner before it can be transitioned. - Expected Version: Every write operation must include an
expected_version(CAS - Check And Set) to prevent stale updates. - Idempotency: Requests must include an
idempotency_keyto allow safe retries.
The sequence above shows the safe interaction pattern required by the database layer for workflow transitions.
Sources: Docs/Plans/2026-03-04-kanban-safe-orchestrator-workflow-primitives-implementation-plan.md:88-100
Core Database Abstractions
The project mandates the use of specific factory functions and classes for database interactions, forbidding raw SQL in endpoint logic.
Media Database (Media_DB_v2)
The MediaDatabase class (and its factory create_media_database) handles the complex requirements of media ingestion, including storing transcripts, metadata, and chunk hierarchies.
- Key Function:
create_media_database(client_id, db_path) - Features: Implements soft deletes, versioning for content, and integration with FTS5 for full-text search across media chunks.
Sources: AGENTS.md:275-279, CLAUDE.md:276-281
Data Access Patterns
| Pattern | Implementation Detail | Source |
|---|---|---|
| Context Managers | Always used for transactions to ensure connection closure. | AGENTS.md:279 |
| Soft Deletes | Implemented in Media and Notes DBs to allow recovery. | AGENTS.md:131 |
| FTS5 Indexes | Used in Media DB v2 for high-performance text search. | AGENTS.md:154 |
| Versioning | Content and workflow state tables track version IDs for CAS. | Docs/Plans/2026-03-04-kanban-safe-orchestrator-workflow-primitives-implementation-plan.md:143 |
Conclusion
Relational databases in tldw_server are structured to support a highly modular and secure research environment. By combining centralized system databases with isolated, per-user SQLite files, the project achieves a balance of global administrative control and individual data privacy. The inclusion of advanced schema features like FTS5 for search and version-controlled workflow tables for Kanban demonstrates an architecture optimized for both human interaction and autonomous agent orchestration.