## Keyboard shortcuts

Press `←` or `→` to navigate between chapters

Press `S` or `/` to search in the book

Press `?` to show this help

Press `Esc` to hide this help

# ADR-004: SurrealDB como Database Único

Status: Accepted | Implemented Date: 2024-11-01 Deciders: Backend Architecture Team Technical Story: Selecting unified multi-model database for relational, graph, and document workloads

## Decision

Usar SurrealDB 2.3 como base de datos única (no PostgreSQL + Neo4j, no MongoDB puro).

## Rationale

1. Multi-Model en una sola DB: Relational (SQL), graph (queries), document (JSON) sin múltiples conexiones
2. Multi-Tenancy Nativa: SurrealDB scopes permiten aislamiento a nivel de database sin lógica en aplicación
3. WebSocket Connection: Soporte nativo de conexiones bidireccionales (vs REST)
4. SurrealQL: Sintaxis SQL-like + graph traversal en una sola query language
5. VAPORA Requirements: Almacena projects (relational), agent relationships (graph), execution history (document)

## Alternatives Considered

### ❌ PostgreSQL + Neo4j (Two Database Approach)

- Pros: Maduro, comunidad grande, especializados
- Cons: Sincronización entre dos DBs, dos conexiones, transacciones distribuidas complejas

### ❌ MongoDB Puro (Document Only)

- Pros: Flexible, escalable
- Cons: Sin soporte graph nativo, requiere aplicación para traversal, sin SQL

### ✅ SurrealDB (CHOSEN)

- Unifica relational + graph + document
- Multi-tenancy built-in
- WebSocket para real-time

## Trade-offs

Pros:

- ✅ Una sola DB para todos los modelos de datos
- ✅ Scopes para isolamiento de tenants (no en aplicación)
- ✅ Transactions ACID
- ✅ SurrealQL es SQL + graph en una query
- ✅ WebSocket bidireccional

Cons:

- ⚠️ Ecosistema más pequeño que PostgreSQL
- ⚠️ Drivers/herramientas menos maduras
- ⚠️ Soporte de clusters más limitado (vs Postgres)

## Implementation

Database Connection:

    #![allow(unused)]
    fn main() {
    // crates/vapora-backend/src/main.rs:48-59
    let db = surrealdb::Surreal::new::<surrealdb::engine::remote::ws::Ws>(
        &config.database.url
    ).await?;

    db.signin(surrealdb::opt::auth::Root {
        username: "root",
        password: "root",
    }).await?;

    db.use_ns("vapora").use_db("main").await?;
    }

Scope-Based Multi-Tenancy:

    #![allow(unused)]
    fn main() {
    // All queries use scope for tenant isolation
    db.query("SELECT * FROM projects WHERE tenant_id = $tenant_id")
        .bind(("tenant_id", tenant_id))
        .await?
    }

Key Files:

- `/crates/vapora-backend/src/main.rs:45-59` (connection setup)
- `/crates/vapora-backend/src/services/` (query implementations)
- `/crates/vapora-shared/src/models.rs` (Project, Task, Agent models with tenant_id)

## Verification

    # Connect to SurrealDB
    surreal sql --conn ws://localhost:8000 --user root --pass root

    # Verify namespace and database exist
    USE ns vapora db main;
    INFO FOR DATABASE;

    # Test multi-tenant query
    SELECT * FROM projects WHERE tenant_id = 'workspace:123';

    # Test graph traversal
    SELECT
        *,
        ->assigned_to->agents AS assigned_agents
    FROM tasks
    WHERE project_id = 'project:123';

    # Run backend tests with SurrealDB
    cargo test -p vapora-backend -- --nocapture

Expected Output:

- SurrealDB connects via WebSocket
- Projects table exists and is queryable
- Graph relationships (->assigned_to) resolve
- Multi-tenant queries filter correctly
- 79+ backend tests pass

## Consequences

### Data Model Changes

- All tables must include `tenant_id` field for scoping
- Relations use SurrealDB's `->` edge syntax for graph queries
- No foreign key constraints (SurrealDB uses references instead)

### Query Patterns

- Services layer queries must include tenant_id filter (defense-in-depth)
- SurrealQL instead of raw SQL learning curve for team
- Graph traversal enables efficient knowledge graph queries

### Scaling Considerations

- Horizontal scaling requires clustering (vs Postgres replication)
- Backup/recovery different from traditional databases (see ADR-020)

## References

- SurrealDB Documentation
- `/crates/vapora-backend/src/services/` (query patterns)
- `/crates/vapora-shared/src/models.rs` (model definitions with tenant_id)
- ADR-025 (Multi-Tenancy with Scopes)

Related ADRs: ADR-001 (Workspace), ADR-025 (Multi-Tenancy)