Data Model & Storage
SQLite entities, relationships, and how state is rehydrated.
PlanToCode uses SQLite for all local state. This document describes the schema, entity relationships, and how state is restored when the app restarts.
SQLite Configuration
The database uses WAL mode for concurrent read/write access. The file is stored in the Tauri app data directory (~/.local/share/plantocode on Linux, ~/Library/Application Support/plantocode on macOS).
Schema migrations are consolidated in consolidated_schema.sql. The app checks schema version on startup and runs any pending migrations.
Core Entities
- sessions: Project context with task description, file selections, model preferences, search settings, video/merge prompts, history indexes
- background_jobs: LLM-backed operations with prompt, response, tokens, cost, is_finalized flag, error_message
- terminal_sessions: PTY sessions with output log, status, process info
- task_description_history: Version history for task descriptions
- file_selection_history: Version history for file selections
- project_system_prompts: Per-project prompt overrides
- key_value_store: App settings and configuration
- error_logs: Client-side error tracking
- migrations: Tracks applied database migrations with timestamps
- db_diagnostic_logs: Records database diagnostic issues and errors
- app_settings: Application configuration key-value pairs with descriptions
Entity relationship diagram
Visual representation of the SQLite schema and relationships.
Schema details
sessions table
id TEXT PRIMARY KEY
name, project_directory, project_hash
task_description, search_term, model_used
search_selected_files_only INTEGER (0/1)
video_analysis_prompt, merge_instructions
included_files, force_excluded_files (JSON)
task_history_version, file_history_version
task_history_current_index, file_history_current_index
created_at, updated_at (Unix timestamps)
background_jobs table
id TEXT PRIMARY KEY, session_id FK
task_type, status (with CHECK constraint)
prompt TEXT, response TEXT, error_message TEXT
tokens_sent, tokens_received, cache_*_tokens
model_used, actual_cost REAL
metadata JSON, system_prompt_template
is_finalized INTEGER (0/1 flag)
start_time, end_time, server_request_id
terminal_sessions table
id TEXT PRIMARY KEY, job_id FK (optional)
session_id TEXT UNIQUE
status: idle, starting, initializing, running, completed, failed, agent_requires_attention, recovering, disconnected, stuck, restored
process_pid, exit_code
working_directory, environment_vars JSON
output_log TEXT (accumulated PTY output)
last_output_at, started_at, ended_at
migrations table
id INTEGER PRIMARY KEY AUTOINCREMENT
name TEXT NOT NULL
applied_at INTEGER (Unix timestamp)
db_diagnostic_logs table
id INTEGER PRIMARY KEY AUTOINCREMENT
timestamp INTEGER, error_type TEXT
error_message TEXT, additional_info TEXT
app_settings table
key TEXT PRIMARY KEY
value TEXT NOT NULL, description TEXT
created_at, updated_at (Unix timestamps)
Entity Relationships
Entities are linked through foreign keys with cascade delete rules:
- sessions → background_jobs: One-to-many, cascade delete
- background_jobs → terminal_sessions: Optional one-to-one link via job_id
- sessions → task_description_history: One-to-many for version tracking
- sessions → file_selection_history: One-to-many for version tracking
Repository Layer
All database access goes through typed repositories in desktop/src-tauri/src/db_utils/:
- background_job_repository/: Modular with base.rs, worker.rs, metadata.rs, cleanup.rs
- session_repository.rs: Session CRUD with history management
- terminal_repository.rs: Terminal session persistence and output logging
- settings_repository.rs: Key-value settings storage
State Rehydration
When the app starts, state is restored from SQLite:
Active session is loaded with task description, file selections, and model preferences. Recent sessions are available in the session picker.
Data Retention
Old data is cleaned up based on configurable retention periods:
Sessions and jobs can be exported for backup before cleanup.
Key source files
- desktop/src-tauri/migrations/consolidated_schema.sql - Full schema
- desktop/src-tauri/src/db_utils/mod.rs - Repository exports
- desktop/src-tauri/src/db_utils/background_job_repository/ - Job CRUD (directory with base.rs, worker.rs, metadata.rs, cleanup.rs)
- desktop/src-tauri/src/db_utils/session_repository.rs - Session management
- desktop/src-tauri/src/db_utils/terminal_repository.rs - Terminal persistence
- desktop/src-tauri/src/db_utils/settings_repository.rs - App settings
Explore job processing
See how background jobs use this data model.