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)

```rust
#[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

```toml
# .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:**
```sql
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:**
```surql
SELECT * FROM project:123 -> transitioned_to -> *
```

### 2. Real-time Dashboard Updates

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

```rust
// 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
```surql
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
```surql
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