eidetica/backend/database/sql/
schema.rs

1//! SQL schema definitions and migrations.
2//!
3//! This module contains the database schema used by SQL backends.
4//! The schema is designed to be portable between SQLite and Postgres.
5//!
6//! # Migration System
7//!
8//! The migration system uses code-based migrations rather than SQL files to handle
9//! dialect differences between SQLite and PostgreSQL. Each migration is a function
10//! that receives the backend and can execute database-specific SQL as needed.
11//!
12//! ## Adding a New Migration
13//!
14//! 1. Increment `SCHEMA_VERSION`
15//! 2. Add a new `migrate_vN_to_vM` async function
16//! 3. Add the migration to the match statement in `run_migration`
17//! 4. Document what the migration does
18
19use crate::Result;
20use crate::backend::errors::BackendError;
21
22use super::{SqlxBackend, SqlxResultExt};
23
24/// Current schema version.
25///
26/// Increment this when making schema changes that require migration.
27pub const SCHEMA_VERSION: i64 = 1;
28
29/// SQL statements to create the schema tables.
30///
31/// Each statement uses portable SQL that works on both SQLite and PostgreSQL.
32pub const CREATE_TABLES: &[&str] = &[
33    // Schema version tracking
34    // BIGINT (64-bit) used for portability between SQLite and PostgreSQL
35    "CREATE TABLE IF NOT EXISTS schema_version (
36        version BIGINT PRIMARY KEY
37    )",
38    // Core entry storage
39    // Entries are content-addressable via hash of entry content
40    "CREATE TABLE IF NOT EXISTS entries (
41        id TEXT PRIMARY KEY NOT NULL,
42        tree_id TEXT NOT NULL,
43        is_root BIGINT NOT NULL DEFAULT 0,
44        verification_status BIGINT NOT NULL DEFAULT 0,
45        height BIGINT NOT NULL DEFAULT 0,
46        entry_json TEXT NOT NULL
47    )",
48    // Tree parent relationships (main tree DAG edges)
49    // Each entry can have multiple parents for merge commits
50    "CREATE TABLE IF NOT EXISTS tree_parents (
51        child_id TEXT NOT NULL,
52        parent_id TEXT NOT NULL,
53        PRIMARY KEY (child_id, parent_id)
54    )",
55    // Subtrees - denormalized subtree data for efficient queries
56    // Replaces store_memberships with additional columns for height and data
57    "CREATE TABLE IF NOT EXISTS subtrees (
58        tree_id TEXT NOT NULL,
59        entry_id TEXT NOT NULL,
60        store_name TEXT NOT NULL,
61        height BIGINT NOT NULL,
62        data TEXT,
63        PRIMARY KEY (entry_id, store_name)
64    )",
65    // Store parent relationships (per-store DAG edges)
66    // Parents within a specific store context
67    "CREATE TABLE IF NOT EXISTS store_parents (
68        child_id TEXT NOT NULL,
69        parent_id TEXT NOT NULL,
70        store_name TEXT NOT NULL,
71        PRIMARY KEY (child_id, parent_id, store_name)
72    )",
73    // Tips cache - maintained incrementally
74    // Tips are entries with no children in their tree/store context
75    // store_name uses empty string for tree-level tips (PostgreSQL disallows NULL in PK)
76    "CREATE TABLE IF NOT EXISTS tips (
77        entry_id TEXT NOT NULL,
78        tree_id TEXT NOT NULL,
79        store_name TEXT NOT NULL DEFAULT '',
80        PRIMARY KEY (entry_id, tree_id, store_name)
81    )",
82    // Instance metadata (singleton row pattern)
83    // Contains device key and system database IDs.
84    // Uses singleton=1 constraint to ensure only one row exists.
85    "CREATE TABLE IF NOT EXISTS instance_metadata (
86        singleton BIGINT PRIMARY KEY DEFAULT 1 CHECK (singleton = 1),
87        data TEXT NOT NULL
88    )",
89    // CRDT state cache
90    "CREATE TABLE IF NOT EXISTS crdt_cache (
91        entry_id TEXT NOT NULL,
92        store_name TEXT NOT NULL,
93        state TEXT NOT NULL,
94        PRIMARY KEY (entry_id, store_name)
95    )",
96];
97
98/// SQL statements to create indexes.
99pub const CREATE_INDEXES: &[&str] = &[
100    // Entry lookups and filtering
101    "CREATE INDEX IF NOT EXISTS idx_entries_tree_id ON entries(tree_id)",
102    "CREATE INDEX IF NOT EXISTS idx_entries_tree_height ON entries(tree_id, height DESC, id)",
103    "CREATE INDEX IF NOT EXISTS idx_entries_verification ON entries(verification_status)",
104    "CREATE INDEX IF NOT EXISTS idx_entries_is_root ON entries(is_root)",
105    // Parent relationship traversal
106    "CREATE INDEX IF NOT EXISTS idx_tree_parents_parent ON tree_parents(parent_id)",
107    "CREATE INDEX IF NOT EXISTS idx_tree_parents_child ON tree_parents(child_id)",
108    // Store-specific queries
109    "CREATE INDEX IF NOT EXISTS idx_subtrees_tree_store_height ON subtrees(tree_id, store_name, height DESC, entry_id)",
110    "CREATE INDEX IF NOT EXISTS idx_subtrees_store_height ON subtrees(store_name, height DESC, entry_id)",
111    "CREATE INDEX IF NOT EXISTS idx_store_parents_parent ON store_parents(store_name, parent_id)",
112    "CREATE INDEX IF NOT EXISTS idx_store_parents_child ON store_parents(store_name, child_id)",
113    // Tip lookups
114    "CREATE INDEX IF NOT EXISTS idx_tips_tree_store ON tips(tree_id, store_name)",
115];
116
117/// Initialize the database schema.
118///
119/// Creates tables and indexes if they don't exist, and handles migrations
120/// if the schema version has changed.
121pub async fn initialize(backend: &SqlxBackend) -> Result<()> {
122    let pool = backend.pool();
123
124    // Create tables
125    for statement in CREATE_TABLES {
126        sqlx::query(statement)
127            .execute(pool)
128            .await
129            .sql_context("Schema creation failed")?;
130    }
131
132    // Check current schema version
133    let row: Option<(i64,)> = sqlx::query_as("SELECT version FROM schema_version")
134        .fetch_optional(pool)
135        .await
136        .sql_context("Failed to check schema version")?;
137
138    if row.is_none() {
139        // First initialization
140        sqlx::query("INSERT INTO schema_version (version) VALUES ($1)")
141            .bind(SCHEMA_VERSION)
142            .execute(pool)
143            .await
144            .sql_context("Failed to initialize schema version")?;
145    } else if let Some((current_version,)) = row
146        && current_version < SCHEMA_VERSION
147    {
148        // Run migrations
149        migrate(backend, current_version, SCHEMA_VERSION).await?;
150    }
151
152    // Create indexes
153    for statement in CREATE_INDEXES {
154        sqlx::query(statement)
155            .execute(pool)
156            .await
157            .sql_context("Index creation failed")?;
158    }
159
160    Ok(())
161}
162
163/// Run migrations sequentially from one schema version to another.
164///
165/// Migrations are run one at a time, incrementing the version after each.
166/// This allows for proper error handling and rollback semantics.
167async fn migrate(backend: &SqlxBackend, from: i64, to: i64) -> Result<()> {
168    tracing::info!(from, to, "Starting SQL schema migration");
169
170    let mut current = from;
171    while current < to {
172        let next = current + 1;
173        tracing::info!(from = current, to = next, "Running migration");
174
175        run_migration(backend, current, next).await?;
176
177        // Update schema version after successful migration
178        sqlx::query("UPDATE schema_version SET version = $1")
179            .bind(next)
180            .execute(backend.pool())
181            .await
182            .sql_context("Failed to update schema version")?;
183
184        tracing::info!(version = next, "Migration completed");
185        current = next;
186    }
187
188    tracing::info!(from, to, "All migrations completed successfully");
189    Ok(())
190}
191
192/// Execute a single migration step.
193///
194/// Each migration is a separate async function that handles the schema change.
195/// Add new migrations here as match arms.
196///
197/// # Adding a New Migration
198///
199/// When incrementing `SCHEMA_VERSION`, add a match arm here:
200///
201/// ```ignore
202/// match from {
203///     1 => migrate_v1_to_v2(backend).await,
204///     // ... existing migrations ...
205///     _ => { /* error handling */ }
206/// }
207/// ```
208async fn run_migration(backend: &SqlxBackend, from: i64, to: i64) -> Result<()> {
209    // When adding the first migration, replace this with:
210    //
211    // match from {
212    //     1 => migrate_v1_to_v2(backend).await,
213    //     _ => Err(BackendError::SqlxError { ... }.into()),
214    // }
215    //
216    // For now, since there are no migrations yet, any attempt to migrate is an error.
217
218    // Suppress unused variable warning until migrations are added
219    let _ = backend;
220
221    Err(BackendError::SqlxError {
222        reason: format!(
223            "Unknown migration path: v{from} to v{to}. \
224             This likely means SCHEMA_VERSION was incremented without adding a migration."
225        ),
226        source: None,
227    }
228    .into())
229}
eidetica/backend/database/sql/schema.rs

eidetica/backend/database/sql/
schema.rs
