syntaxis/docs/databases/surrealdb/surrealdb-quick-reference.md

SurrealDB Integration - Quick Reference

TL;DR

Question: What happens if we want to use SurrealDB instead of SQLite?

Answer:

• ✅ Possible - Good separation of concerns exists
• ⚠️ Medium Effort - ~40-50 hours, manageable
• 🎯 Recommended Approach - Trait-based abstraction (supports both)
• 📅 Timeline - Can be done in next sprint if prioritized

---

Architecture Overview

Current (SQLite only):
┌─────────────────────────────┐
│  syntaxis-cli              │
│  syntaxis-tui              │
│  syntaxis-api              │
│  syntaxis-dashboard        │
└──────────────┬──────────────┘
               │
         ┌─────▼────────┐
         │ PersistenceLayer
         │ (SQLite pool)
         └─────┬────────┘
               │
            workspace.db

Proposed (Both support):
┌─────────────────────────────┐
│  syntaxis-cli              │
│  syntaxis-tui              │
│  syntaxis-api              │
│  syntaxis-dashboard        │
└──────────────┬──────────────┘
               │
         ┌─────▼──────────────────┐
         │ Database Trait         │
         │ (abstraction layer)    │
         └─────┬──────────┬───────┘
               │          │
        ┌──────▼─┐    ┌──▼──────────┐
        │ SQLite │    │ SurrealDB   │
        │ Impl   │    │ Impl        │
        └──────┬─┘    └──┬──────────┘
               │         │
          workspace.db   surrealdb://

---

Key Implementation Details

1. Database Trait (Abstraction Layer)

#[async_trait]
pub trait Database: Send + Sync + Clone {
    async fn create_project(...) -> Result<Project>;
    async fn get_project(id: &str) -> Result<Project>;
    async fn list_projects() -> Result<Vec<Project>>;
    async fn record_phase_transition(...) -> Result<()>;
    // ... 50+ more methods
}

Benefits:

• Both SQLite and SurrealDB implement same interface
• Application code doesn't change
• Swap backends at runtime via config
• Testable with mock implementations

2. Configuration-Driven Selection

# .env.development - SQLite (fast, no setup)
[database]
engine = "sqlite"
sqlite_path = "data/workspace.db"

# .env.production - SurrealDB (cloud-ready)
[database]
engine = "surrealdb"
[database.surrealdb]
url = "ws://surrealdb-server:8000"
namespace = "syntaxis"
database = "projects"

3. Implementation Modules

syntaxis-core/src/persistence/
├── mod.rs              # Database trait definition
├── sqlite_impl.rs      # SQLite implementation (current code)
├── surrealdb_impl.rs   # SurrealDB implementation (new)
└── error.rs            # Unified error handling

---

SurrealDB Advantages for syntaxis

1. Graph-Native Phase Transitions

Problem with SQLite:

SELECT pt.* FROM phase_transitions pt
JOIN phases p1 ON p1.id = pt.from_phase
JOIN phases p2 ON p2.id = pt.to_phase
WHERE pt.project_id = ? ORDER BY timestamp DESC;

Solution with SurrealDB:

SELECT * FROM project:123 -> transitioned_to -> *

2. Real-time Dashboard Updates

SQLite: Poll database every N seconds (wasteful) SurrealDB: WebSocket subscription (instant push)

// SurrealDB - Real-time
let mut stream = db.query("LIVE SELECT * FROM projects").await?;
while let Some(change) = stream.next().await {
    send_to_dashboard(change).await;  // Instant!
}

3. Built-in Audit Trail

SQLite: Manual activity_logs table SurrealDB: Built-in versioning

SELECT * FROM projects VERSION AT <d'2025-01-14T10:00:00Z'>;
SELECT * FROM projects DIFF FROM <date1> TO <date2>;

4. Flexible Document Schema

SQLite: JSON strings to parse SurrealDB: Native document objects

UPDATE toolconfig:cargo-clippy
SET config.clippy_level = 'strict';  // No parsing!

---

Implementation Roadmap

Phase	Duration	Effort	Tasks
Phase 1	1 week	Low	Define Database trait, tests
Phase 2	1 week	Medium	Refactor SQLite to adapter
Phase 3	2 weeks	High	Implement SurrealDB adapter
Phase 4	1 week	Medium	Integration & testing
Total	5 weeks	Medium	40-50 hours

---

Current Status vs Future State

Current (SQLite)

✅ Single file database
✅ Zero configuration
✅ Embedded in application
✅ Suitable for single user/CLI
❌ No real-time updates
❌ Poor graph query support
❌ Manual audit trails

With SurrealDB Support

✅ Keep SQLite as default
✅ Add SurrealDB for advanced use
✅ Configuration-driven selection
✅ Real-time dashboard updates
✅ Natural graph queries
✅ Built-in versioning
✅ Zero breaking changes

---

Risk Assessment

Risk	Impact	Probability	Mitigation
Breaking changes	High	Low	Trait pattern prevents breakage
Data migration	High	Medium	Create migration tool before switching
SurrealDB learning curve	Medium	Medium	Comprehensive docs + examples
Operational complexity	Medium	Medium	Keep SQLite as simple default

---

Recommendation

✅ YES, proceed with SurrealDB integration because:

1. Clean separation of concerns (single persistence module)
2. Natural fit for graph-based domain (phases, tasks, dependencies)
3. Trait-based approach enables zero breaking changes
4. Future features easier (real-time, versioning, graph queries)
5. Can be done incrementally without disrupting current work

Best Approach:

1. Phase 1: Define Database trait
2. Phase 2: Move SQLite to adapter
3. Phase 3: Implement SurrealDB adapter
4. Phase 4: Configuration-driven selection
5. Result: Users can choose which database fits their needs

---

Files Created

1. SURREALDB_MIGRATION.md - Comprehensive migration guide
2. SURREALDB_EXAMPLE.rs - Complete implementation example
3. SURREALDB_COMPARISON.md - SQLite vs SurrealDB comparison
4. SURREALDB_QUICK_REFERENCE.md - This file

---

Next Steps

1. Review migration documents
2. Review example implementation
3. Decide if proceeding with Phase 1
4. Estimate timeline for your team
5. Create feature branch for implementation

---

Questions?

See detailed documentation in:

• SURREALDB_MIGRATION.md - Full technical details
• SURREALDB_EXAMPLE.rs - Working code examples
• SURREALDB_COMPARISON.md - Feature comparison