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. Have that function update `schema_version` to its target version inside
17//!    the same transaction as its schema changes, so the version write and the
18//!    schema change commit or roll back together
19//! 4. Add the migration to the match statement in `run_migration`
20//! 5. Document what the migration does
21
22use crate::Result;
23use crate::backend::errors::BackendError;
24
25use super::{SqlxBackend, SqlxResultExt};
26
27/// Current schema version.
28///
29/// Increment this when making schema changes that require migration.
30/// Version 0 is fully unstable and should not be used in production.
31pub const SCHEMA_VERSION: i64 = 0;
32
33const CREATE_STORE_STATE_TABLES: &[&str] = &[
34    "CREATE TABLE IF NOT EXISTS store_state_namespaces (
35        namespace_id TEXT PRIMARY KEY NOT NULL,
36        database_id TEXT NOT NULL,
37        store_name TEXT NOT NULL,
38        lifecycle BIGINT NOT NULL,
39        status BIGINT NOT NULL,
40        scope_user_uuid TEXT NOT NULL,
41        projection_name TEXT NOT NULL,
42        projection_version BIGINT NOT NULL,
43        source_key BYTEA NOT NULL,
44        created_revision BIGINT,
45        UNIQUE (database_id, store_name, lifecycle, status, scope_user_uuid,
46                projection_name, projection_version, source_key)
47    )",
48    "CREATE TABLE IF NOT EXISTS store_state_records (
49        namespace_id TEXT NOT NULL,
50        record_key BYTEA NOT NULL,
51        record_value BYTEA,
52        PRIMARY KEY (namespace_id, record_key),
53        -- Dropping a namespace drops its records. SQLite enforces this because
54        -- sqlx sets `PRAGMA foreign_keys = ON` on every connection it opens;
55        -- it is off in a bare sqlite3 session, which makes this look inert.
56        FOREIGN KEY (namespace_id) REFERENCES store_state_namespaces(namespace_id)
57            ON DELETE CASCADE
58    )",
59];
60
61/// SQL statements to create the schema tables.
62///
63/// Each statement uses portable SQL that works on both SQLite and PostgreSQL.
64pub const CREATE_TABLES: &[&str] = &[
65    // Schema version tracking
66    // BIGINT (64-bit) used for portability between SQLite and PostgreSQL
67    "CREATE TABLE IF NOT EXISTS schema_version (
68        version BIGINT PRIMARY KEY
69    )",
70    // Core entry storage
71    // Entries are content-addressable via hash of entry content
72    "CREATE TABLE IF NOT EXISTS entries (
73        id TEXT PRIMARY KEY NOT NULL,
74        tree_id TEXT NOT NULL,
75        is_root BIGINT NOT NULL DEFAULT 0,
76        verification_status BIGINT NOT NULL DEFAULT 0,
77        height BIGINT NOT NULL DEFAULT 0,
78        entry_cbor BYTEA NOT NULL
79    )",
80    // Tree parent relationships (main tree DAG edges)
81    // Each entry can have multiple parents for merge commits
82    "CREATE TABLE IF NOT EXISTS tree_parents (
83        child_id TEXT NOT NULL,
84        parent_id TEXT NOT NULL,
85        PRIMARY KEY (child_id, parent_id)
86    )",
87    // Subtrees - denormalized subtree data for efficient queries
88    // Replaces store_memberships with additional columns for height and data.
89    // `data` is the opaque payload bytes for each store (format chosen by the store).
90    "CREATE TABLE IF NOT EXISTS subtrees (
91        tree_id TEXT NOT NULL,
92        entry_id TEXT NOT NULL,
93        store_name TEXT NOT NULL,
94        height BIGINT NOT NULL,
95        data BLOB,
96        PRIMARY KEY (entry_id, store_name)
97    )",
98    // Store parent relationships (per-store DAG edges)
99    // Parents within a specific store context
100    "CREATE TABLE IF NOT EXISTS store_parents (
101        child_id TEXT NOT NULL,
102        parent_id TEXT NOT NULL,
103        store_name TEXT NOT NULL,
104        PRIMARY KEY (child_id, parent_id, store_name)
105    )",
106    // Tips cache - maintained incrementally
107    // Tips are entries with no children in their tree/store context
108    // store_name uses empty string for tree-level tips (PostgreSQL disallows NULL in PK)
109    "CREATE TABLE IF NOT EXISTS tips (
110        entry_id TEXT NOT NULL,
111        tree_id TEXT NOT NULL,
112        store_name TEXT NOT NULL DEFAULT '',
113        PRIMARY KEY (entry_id, tree_id, store_name)
114    )",
115    // Instance metadata (singleton row pattern)
116    // Contains device key and system database IDs.
117    // Uses singleton=1 constraint to ensure only one row exists.
118    "CREATE TABLE IF NOT EXISTS instance_metadata (
119        singleton BIGINT PRIMARY KEY DEFAULT 1 CHECK (singleton = 1),
120        data TEXT NOT NULL
121    )",
122    // Instance secrets (singleton row pattern)
123    // Contains device signing key. Stored separately from metadata.
124    "CREATE TABLE IF NOT EXISTS instance_secrets (
125        singleton BIGINT PRIMARY KEY DEFAULT 1 CHECK (singleton = 1),
126        data TEXT NOT NULL
127    )",
128];
129
130/// SQL statements to create indexes.
131pub const CREATE_INDEXES: &[&str] = &[
132    // Entry lookups and filtering
133    "CREATE INDEX IF NOT EXISTS idx_entries_tree_id ON entries(tree_id)",
134    "CREATE INDEX IF NOT EXISTS idx_entries_tree_height ON entries(tree_id, height DESC, id)",
135    "CREATE INDEX IF NOT EXISTS idx_entries_verification ON entries(verification_status)",
136    "CREATE INDEX IF NOT EXISTS idx_entries_is_root ON entries(is_root)",
137    // Parent relationship traversal
138    "CREATE INDEX IF NOT EXISTS idx_tree_parents_parent ON tree_parents(parent_id)",
139    "CREATE INDEX IF NOT EXISTS idx_tree_parents_child ON tree_parents(child_id)",
140    // Store-specific queries
141    "CREATE INDEX IF NOT EXISTS idx_subtrees_tree_store_height ON subtrees(tree_id, store_name, height DESC, entry_id)",
142    "CREATE INDEX IF NOT EXISTS idx_subtrees_store_height ON subtrees(store_name, height DESC, entry_id)",
143    "CREATE INDEX IF NOT EXISTS idx_store_parents_parent ON store_parents(store_name, parent_id)",
144    "CREATE INDEX IF NOT EXISTS idx_store_parents_child ON store_parents(store_name, child_id)",
145    // Tip lookups
146    "CREATE INDEX IF NOT EXISTS idx_tips_tree_store ON tips(tree_id, store_name)",
147];
148
149/// Initialize the database schema.
150///
151/// Creates tables and indexes if they don't exist, and handles migrations
152/// if the schema version has changed.
153pub async fn initialize(backend: &SqlxBackend) -> Result<()> {
154    let pool = backend.pool();
155
156    // Create tables, adapting dialect-specific types
157    let blob_type = if backend.is_sqlite() { "BLOB" } else { "BYTEA" };
158    for statement in CREATE_TABLES {
159        let statement = statement.replace("BLOB", blob_type);
160        sqlx::query(&statement)
161            .execute(pool)
162            .await
163            .sql_context("Schema creation failed")?;
164    }
165
166    // Check current schema version
167    let row: Option<(i64,)> = sqlx::query_as("SELECT version FROM schema_version")
168        .fetch_optional(pool)
169        .await
170        .sql_context("Failed to check schema version")?;
171
172    initialize_store_state_tables(backend).await?;
173
174    if row.is_none() {
175        sqlx::query("INSERT INTO schema_version (version) VALUES ($1)")
176            .bind(SCHEMA_VERSION)
177            .execute(pool)
178            .await
179            .sql_context("Failed to initialize schema version")?;
180    } else if let Some((current_version,)) = row
181        && current_version < SCHEMA_VERSION
182    {
183        // Run migrations
184        migrate(backend, current_version, SCHEMA_VERSION).await?;
185    }
186
187    // Create indexes
188    for statement in CREATE_INDEXES {
189        sqlx::query(statement)
190            .execute(pool)
191            .await
192            .sql_context("Index creation failed")?;
193    }
194
195    Ok(())
196}
197
198async fn initialize_store_state_tables(backend: &SqlxBackend) -> Result<()> {
199    let mut tx = backend
200        .pool()
201        .begin()
202        .await
203        .sql_context("Failed to begin schema initialization")?;
204    let blob_type = if backend.is_sqlite() { "BLOB" } else { "BYTEA" };
205    for statement in CREATE_STORE_STATE_TABLES {
206        sqlx::query(&statement.replace("BYTEA", blob_type))
207            .execute(&mut *tx)
208            .await
209            .sql_context("Failed to create Store-state tables")?;
210    }
211    tx.commit()
212        .await
213        .sql_context("Failed to commit Store-state table initialization")
214}
215
216/// Run migrations sequentially from one schema version to another.
217///
218/// Migrations are run one step at a time, each advancing the schema by a single
219/// version. This function only tracks the step it is on; persisting the new
220/// `schema_version` is the responsibility of each migration function, which
221/// writes it in the same transaction as its schema changes. A failed step
222/// therefore leaves the recorded version at the last successfully committed
223/// migration.
224async fn migrate(backend: &SqlxBackend, from: i64, to: i64) -> Result<()> {
225    tracing::info!(from, to, "Starting SQL schema migration");
226
227    let mut current = from;
228    while current < to {
229        let next = current + 1;
230        tracing::info!(from = current, to = next, "Running migration");
231
232        run_migration(backend, current, next).await?;
233
234        tracing::info!(version = next, "Migration completed");
235        current = next;
236    }
237
238    tracing::info!(from, to, "All migrations completed successfully");
239    Ok(())
240}
241
242/// Execute a single migration step.
243///
244/// Each migration is a separate async function that handles the schema change.
245/// Add new migrations here as match arms.
246///
247/// # Adding a New Migration
248///
249/// When incrementing `SCHEMA_VERSION`, add a match arm here:
250///
251/// ```ignore
252/// match (from, to) {
253///     (1, 2) => migrate_v1_to_v2(backend).await,
254///     // ... existing migrations ...
255///     _ => { /* error handling */ }
256/// }
257/// ```
258///
259/// The migration function is responsible for persisting the new
260/// `schema_version` itself, inside the same transaction as its schema changes.
261async fn run_migration(backend: &SqlxBackend, from: i64, to: i64) -> Result<()> {
262    let _ = backend;
263
264    Err(BackendError::SqlxError {
265        reason: format!(
266            "Unknown migration path: v{from} to v{to}. \
267             This likely means SCHEMA_VERSION was incremented without adding a migration."
268        ),
269        source: None,
270    }
271    .into())
272}
eidetica/backend/database/sql/schema.rs

eidetica/backend/database/sql/
schema.rs
