syntaxis/docs/databases/surrealdb/surrealdb-migration.md

SurrealDB Migration Guide for syntaxis

Executive Summary

Current System: SQLite with sqlx (sync schema in code) Proposed System: SurrealDB (multi-model NoSQL + graph database)

Migration Complexity: MEDIUM - Doable but requires careful abstraction Estimated Effort: 40-60 hours (depends on feature set) Risk Level: MEDIUM - Good separation of concerns exists

---

Current Architecture Analysis

Database Layer (syntaxis-core/src/persistence.rs)

Current Design:

pub struct PersistenceLayer {
    pool: SqlitePool,  // ← Tightly coupled to SQLite
}

impl PersistenceLayer {
    pub async fn new(db_path: &str) -> Result<Self> { ... }
    async fn run_migrations(pool: &SqlitePool) -> Result<()> { ... }

    // Query methods (200+ lines of direct SQLx queries)
    pub async fn create_project(&self, ...) -> Result<Project> { ... }
    pub async fn get_project(&self, id: &str) -> Result<Project> { ... }
    // ... 50+ more query methods
}

Coupling Points:

1. ✗ SqlitePool directly in struct (SQLx-specific)
2. ✗ Raw SQL strings in run_migrations() (SQLite dialect)
3. ✗ sqlx::query! macros throughout (compile-time SQLite checking)
4. ✗ Hard-coded migration logic (SQLite PRAGMA, CREATE TABLE IF NOT EXISTS)

Good News:

• ✅ Single responsibility (only persistence)
• ✅ Result return types (error abstraction)
• ✅ No leakage into business logic layers
• ✅ 1500 line file (manageable, not monolithic)

---

Migration Strategy

Phase 1: Create Database Trait (Low Risk)

Goal: Abstract database operations behind a trait

// syntaxis-core/src/persistence/db_trait.rs
#[async_trait]
pub trait Database: Send + Sync + Clone {
    // Project operations
    async fn create_project(&self, req: &CreateProjectRequest) -> Result<Project>;
    async fn get_project(&self, id: &str) -> Result<Project>;
    async fn list_projects(&self) -> Result<Vec<Project>>;
    async fn update_project(&self, id: &str, req: &UpdateProjectRequest) -> Result<Project>;
    async fn delete_project(&self, id: &str) -> Result<()>;

    // Checklist operations
    async fn create_checklist_item(&self, item: &ChecklistItem) -> Result<ChecklistItem>;
    async fn list_checklist_items(&self, project_id: &str) -> Result<Vec<ChecklistItem>>;
    async fn update_checklist_item(&self, id: &str, completed: bool) -> Result<ChecklistItem>;

    // Phase operations
    async fn record_phase_transition(&self, transition: &PhaseTransition) -> Result<()>;
    async fn get_phase_history(&self, project_id: &str) -> Result<Vec<PhaseTransition>>;

    // ... more operations
}

Benefits:

• Allows SQLite and SurrealDB implementations to coexist
• Can test with mock implementations
• No immediate breaking changes
• Incremental migration possible

Phase 2: Implement SQLite Adapter

// syntaxis-core/src/persistence/sqlite_impl.rs
pub struct SqliteDatabase {
    pool: SqlitePool,
}

#[async_trait]
impl Database for SqliteDatabase {
    async fn create_project(&self, req: &CreateProjectRequest) -> Result<Project> {
        // Current implementation moved here
    }

    // ... all other methods
}

Outcome: Existing code works unchanged, but now isolated in adapter

Phase 3: Implement SurrealDB Adapter

// syntaxis-core/src/persistence/surrealdb_impl.rs
pub struct SurrealDatabase {
    db: Surreal<Client>,  // SurrealDB connection
}

#[async_trait]
impl Database for SurrealDatabase {
    async fn create_project(&self, req: &CreateProjectRequest) -> Result<Project> {
        // SurrealDB implementation
        let project = Project::new(req);

        // SurrealDB: Graph-native queries
        self.db.create::<Project>("projects", Some(&project.id))
            .content(&project)
            .await?;

        Ok(project)
    }

    async fn list_projects(&self) -> Result<Vec<Project>> {
        // SurrealDB: Natural graph queries
        let projects: Vec<Project> = self.db
            .query("SELECT * FROM projects WHERE archived = false")
            .await?
            .take(0)?;

        Ok(projects)
    }

    async fn record_phase_transition(&self, transition: &PhaseTransition) -> Result<()> {
        // SurrealDB: Graph edge creation
        self.db.query("
            LET $project = type::thing('projects', $project_id);
            LET $from = type::thing('phases', $from_phase);
            LET $to = type::thing('phases', $to_phase);

            RELATE $project->transitioned_to->$to
                SET reason = $reason, timestamp = $timestamp,
                    from_phase = $from_phase
        ")
        .bind(("project_id", &transition.project_id))
        .bind(("from_phase", &transition.from_phase))
        .bind(("to_phase", &transition.to_phase))
        .bind(("reason", &transition.reason))
        .bind(("timestamp", &transition.timestamp))
        .await?;

        Ok(())
    }
}

SurrealDB Advantages:

• Graph Queries: Phase transitions become edges (natural relationships)
• Relations: Checklist items → Projects → Phases (built-in)
• Multi-model: Document + Graph + SQL-like syntax
• Type Safety: Serde integration for documents
• Async Native: Built for tokio runtime

Phase 4: Configuration-Driven Selection

# configs/default.toml
[database]
engine = "sqlite"  # or "surrealdb"
sqlite_path = "workspace.db"

[database.surrealdb]
url = "ws://localhost:8000"  # WebSocket for local, TCP for remote
namespace = "syntaxis"
database = "projects"
username = "root"
password = "root"

// syntaxis-core/src/persistence/mod.rs
pub enum DatabaseEngine {
    Sqlite(SqliteDatabase),
    SurrealDB(SurrealDatabase),
}

impl DatabaseEngine {
    pub async fn from_config(config: &DatabaseConfig) -> Result<Self> {
        match config.engine.as_str() {
            "sqlite" => {
                let db = SqliteDatabase::new(&config.sqlite_path).await?;
                Ok(DatabaseEngine::Sqlite(db))
            }
            "surrealdb" => {
                let db = SurrealDatabase::new(&config.surrealdb_url).await?;
                Ok(DatabaseEngine::SurrealDB(db))
            }
            _ => Err(LifecycleError::Config(format!("Unknown database engine: {}", config.engine))),
        }
    }
}

// All callers use trait, not specific implementation
pub async fn use_database(engine: DatabaseEngine) -> Result<()> {
    let projects = engine.list_projects().await?;  // Works with both!
    Ok(())
}

---

SurrealDB Benefits for syntaxis

1. Graph-Native Phase Transitions

Current (SQLite):

-- Store transitions in table, query with JOINs
SELECT pt.*, p.name FROM phase_transitions pt
JOIN projects p ON pt.project_id = p.id
WHERE pt.project_id = ? ORDER BY timestamp DESC

SurrealDB (Graph):

-- Query graph edges directly
SELECT * FROM project:12345 -> transitioned_to -> *

2. Dynamic Relationships (Checklist Dependencies)

Current (SQLite):

-- Store JSON array of task_deps, parse manually
SELECT task_deps FROM checklist_items WHERE id = ?
-- Then manually fetch related tasks

SurrealDB (Relations):

-- Query relationships directly
SELECT * FROM task:123 -> depends_on -> *
SELECT * -> depends_on -> * FROM task:123  -- Recursive

3. Flexible Schema for Tool Configurations

Current (SQLite):

-- config_json stored as string, must parse
CREATE TABLE tool_configurations (
    id TEXT PRIMARY KEY,
    config_json TEXT,  -- {"rust_version": "1.75", "lint_level": "pedantic"}
    ...
)

SurrealDB (Document):

-- Native document storage
CREATE toolconfig:123 CONTENT {
    tool_name: 'cargo-clippy',
    enabled: true,
    rust_version: '1.75',
    lint_level: 'pedantic',
    lint_rules: ['all', 'pedantic'],
    updated_at: <d'2025-01-15T10:00:00Z'>
};

4. Audit Trail (Built-in Time-Travel)

Current (SQLite):

-- Activity log in separate table
CREATE TABLE activity_logs (
    id TEXT,
    action TEXT,
    timestamp TEXT,
    ...
)

SurrealDB (Versioning):

-- SurrealDB versions automatically
SELECT * FROM projects VERSION AT <d'2025-01-14T10:00:00Z'>;
SELECT * FROM projects DIFF FROM <d'2025-01-14T10:00:00Z'>;

5. Real-time Notifications (WebSocket)

Current (SQLite):

• Poll database at intervals
• Manual change tracking

SurrealDB:

• Built-in WebSocket subscriptions
• Push notifications on changes
• Perfect for TUI/Dashboard live updates

// Subscribe to project changes
let mut stream = db.query("LIVE SELECT * FROM projects")
    .await?
    .take(0)?;

while let Some(change) = stream.next().await {
    println!("Project changed: {:?}", change);
}

---

Implementation Roadmap

Step 1: Add SurrealDB Dependency (1 hour)

# Cargo.toml
[workspace.dependencies]
sqlx = { version = "0.8", features = ["runtime-tokio-native-tls", "sqlite", "macros"] }
surrealdb = { version = "2.0", features = ["ws"] }  # Add this

Step 2: Create Trait Layer (4-6 hours)

• Define Database trait in syntaxis-core/src/persistence/mod.rs
• Move 50+ methods signatures to trait
• Create error wrapper for SurrealDB errors

Step 3: Refactor SQLite to Adapter (6-8 hours)

• Move SQLite implementation to sqlite_impl.rs
• Implement Database trait for SqliteDatabase
• Test all existing functionality

Step 4: Implement SurrealDB Adapter (12-16 hours)

• Create schema (tables/types in SurrealDB)
• Implement Database trait for SurrealDatabase
• Handle type conversions (Serde)

Step 5: Update Binaries (4-6 hours)

• syntaxis-api: Accept database config, use trait
• syntaxis-cli: Load database from config
• syntaxis-tui: Switch databases at runtime
• Tests: Update to use trait

Step 6: Testing & Validation (8-10 hours)

• Unit tests for both adapters
• Integration tests
• Performance benchmarks (SQLite vs SurrealDB)
• E2E tests with TUI/API

---

Database Schema Mapping

SQLite Tables → SurrealDB Collections

SQLite                      SurrealDB
projects               →     projects:{id}
checklist_items        →     checklist_items:{id}
phase_transitions      →     phase_transitions:{id}
security_assessments   →     assessments:{id}
tool_configurations    →     tool_configs:{id}
activity_logs          →     activities:{id}
backup_history         →     backups:{id}
phase_history          →     phase_history:{id}

Foreign Keys           →     Graph Relations (RELATE)
Indices                →     SurrealDB DEFINE INDEX

Example Schema Definition

-- Create collections with schema
DEFINE TABLE projects SCHEMAFULL
    AS SELECT id, name, version, description, project_type, current_phase, created_at, updated_at
    FROM projects;

DEFINE FIELD projects.id AS string ASSERT $before IS NONE;
DEFINE FIELD projects.name AS string;
DEFINE FIELD projects.version AS string;
DEFINE FIELD projects.current_phase AS enum<Creation,Development,Testing,Publishing,Archived>;

-- Create graph relation for phase transitions
DEFINE TABLE transitioned_to SCHEMALESS
    AS SELECT * FROM transitioned_to;

-- Create indices
DEFINE INDEX idx_projects_created ON TABLE projects COLUMNS created_at DESC;
DEFINE INDEX idx_checklist_project ON TABLE checklist_items COLUMNS project_id;

---

Configuration Example

# .env.development - Use SQLite
[database]
engine = "sqlite"
sqlite_path = "data/workspace.db"

# .env.production - Use SurrealDB
[database]
engine = "surrealdb"

[database.surrealdb]
url = "ws://surrealdb-server:8000"
namespace = "syntaxis"
database = "projects"
username = "${SURREALDB_USER}"  # From env var
password = "${SURREALDB_PASS}"

---

Risk Assessment & Mitigation

Risk	Impact	Probability	Mitigation
Breaking Changes	High	Medium	Trait pattern allows gradual migration, keep SQLite as fallback
Data Migration	High	High	Create migration script: SQLite → SurrealDB before switching
SurrealDB Performance	Medium	Low	Benchmark both; can tune indexes and caching
Test Coverage	Medium	Medium	Mock implementation of Database trait for tests
Operator Complexity	Medium	High	Keep SQLite as simple default, SurrealDB for advanced features

---

Recommendation

✅ YES, migrate to SurrealDB because:

1. Clean separation exists - Single persistence module, no leakage
2. Natural fit for domain - Graph relationships (phases, tasks, dependencies) are core
3. Future features easier - Real-time subscriptions, versioning, complex queries
4. Abstraction pattern - Trait-based approach enables both systems to coexist
5. Not urgent - Can be done incrementally without disrupting current work

Best Approach:

1. Keep SQLite as default (current behavior)
2. Implement SurrealDB as optional feature
3. Configuration-driven selection (TOML)
4. Tests work with both backends
5. Gradual migration path for users

Timeline: Phase 1-3 in 2-3 sprints, full deployment by Q2 2025

---

Code References

• Current Persistence Layer: core/crates/syntaxis-core/src/persistence.rs (1492 lines)
• Database Dependencies: Cargo.toml lines 72-74
• Config Loading: syntaxis-core/src/config.rs
• Error Handling: syntaxis-core/src/error.rs

---

Further Reading

• SurrealDB Docs
• SurrealDB Rust Client
• Graph Database Patterns
• Trait-based Abstraction in Rust