# Persistence Layer Quick Start Guide

This guide shows how to use the new trait-based database abstraction layer.

---

## Using SQLite Backend

### 1. Create Database Instance

```rust
use workspace_core::SqliteDatabase;

#[tokio::main]
async fn main() -> Result<(), Box<dyn std::error::Error>> {
    // Create SQLite database
    let db = SqliteDatabase::new("workspace.db").await?;

    // Or use in-memory database (for testing)
    let db = SqliteDatabase::new_memory().await?;

    Ok(())
}
```

### 2. Create a Project

```rust
use workspace_core::{SqliteDatabase, DbProject};
use chrono::Utc;
use uuid::Uuid;

let db = SqliteDatabase::new("workspace.db").await?;

let project = DbProject {
    id: Uuid::new_v4().to_string(),
    name: "My Project".to_string(),
    version: "0.1.0".to_string(),
    description: "A new project".to_string(),
    project_type: "MultiLang".to_string(),
    current_phase: "Creation".to_string(),
    created_at: Utc::now().to_rfc3339(),
    updated_at: Utc::now().to_rfc3339(),
};

db.create_project(&project).await?;
```

### 3. Retrieve Projects

```rust
// Get all projects
let projects = db.list_projects().await?;
println!("Found {} projects", projects.len());

// Get specific project
if let Some(project) = db.get_project("project-id").await? {
    println!("Project: {}", project.name);
}
```

### 4. Add Checklist Items

```rust
use workspace_core::DbChecklistItem;

let item = DbChecklistItem {
    id: Uuid::new_v4().to_string(),
    project_id: project.id.clone(),
    phase: "Creation".to_string(),
    task_id: "task-1".to_string(),
    description: "Complete project setup".to_string(),
    completed: false,
    completed_at: None,
    created_at: Utc::now().to_rfc3339(),
    task_type: "Setup".to_string(),
    task_priority: "High".to_string(),
    task_due: Some("2025-12-31".to_string()),
    task_estimation: Some("2d".to_string()),
    task_deps: "[]".to_string(),
    task_note: Some("Remember to test").to_string(),
    task_name: Some("Setup".to_string()),
};

db.create_checklist_item(&item).await?;
```

### 5. Track Phase Transitions

```rust
use workspace_core::DbPhaseTransition;

let transition = DbPhaseTransition {
    id: Uuid::new_v4().to_string(),
    project_id: project.id.clone(),
    from_phase: "Creation".to_string(),
    to_phase: "Development".to_string(),
    timestamp: Utc::now().to_rfc3339(),
    reason: Some("Project setup complete".to_string()),
};

db.record_phase_transition(&transition).await?;
```

---

## Using Configuration Files

### 1. Create Configuration File

**`configs/database.toml`**
```toml
engine = "sqlite"

[sqlite]
path = "~/.local/share/core/workspace.db"
max_connections = 5
timeout_secs = 30
wal_mode = true
pragma_synchronous = "NORMAL"
pragma_cache_size = 2000
```

### 2. Load Configuration

```rust
use workspace_core::DatabaseConfig;
use std::path::Path;

let config = DatabaseConfig::load_from_file("configs/database.toml")?;

// Validate configuration
config.validate()?;

// Check which backend is configured
if config.is_sqlite() {
    println!("Using SQLite backend");
}
```

### 3. Runtime Selection (Future)

```rust
use workspace_core::{DatabaseConfig, Database, SqliteDatabase};

let config = DatabaseConfig::load_from_file("configs/database.toml")?;

// Create appropriate backend based on configuration
let db: Box<dyn Database> = match config.engine.as_str() {
    "sqlite" => {
        let sqlite_config = config.sqlite.ok_or("No SQLite config")?;
        let db = SqliteDatabase::new(&sqlite_config.path).await?;
        Box::new(db)
    }
    "surrealdb" => {
        // Once SurrealDB is implemented:
        // let db = SurrealDatabase::new(...).await?;
        // Box::new(db)
        return Err("SurrealDB not yet implemented".into());
    }
    _ => return Err("Unknown database engine".into()),
};

// Use database transparently
db.list_projects().await?;
```

---

## Error Handling

### Pattern 1: Propagate with `?`

```rust
async fn create_project(db: &dyn Database, name: &str) -> Result<()> {
    let project = DbProject {
        id: Uuid::new_v4().to_string(),
        name: name.to_string(),
        // ... other fields ...
    };

    db.create_project(&project).await?; // Propagates error
    Ok(())
}
```

### Pattern 2: Match Errors

```rust
match db.get_project("nonexistent").await {
    Ok(Some(project)) => println!("Found: {}", project.name),
    Ok(None) => println!("Project not found"),
    Err(e) => eprintln!("Error: {}", e),
}
```

### Pattern 3: Check Error Type

```rust
use workspace_core::error::LifecycleError;

match db.create_project(&project).await {
    Ok(p) => println!("Created: {}", p.id),
    Err(LifecycleError::Database(msg)) => eprintln!("DB error: {}", msg),
    Err(e) => eprintln!("Other error: {}", e),
}
```

---

## Testing

### In-Memory Database

```rust
#[tokio::test]
async fn test_project_creation() {
    let db = SqliteDatabase::new_memory().await.unwrap();

    let project = DbProject {
        id: "test-1".to_string(),
        name: "Test".to_string(),
        // ... other fields ...
    };

    let created = db.create_project(&project).await.unwrap();
    assert_eq!(created.name, "Test");
}
```

### Temporary File Database

```rust
use tempfile::tempdir;

#[tokio::test]
async fn test_persistence() {
    let dir = tempdir().unwrap();
    let db_path = dir.path().join("test.db").to_string_lossy().to_string();

    let db = SqliteDatabase::new(&db_path).await.unwrap();

    // Your test code here
}
```

---

## Best Practices

### 1. Use Type Alias for Result

```rust
use workspace_core::error::Result;

async fn my_function() -> Result<String> {
    // Function body
    Ok("success".to_string())
}
```

### 2. Always Validate IDs

```rust
if let Some(project) = db.get_project(&project_id).await? {
    // Process project
} else {
    eprintln!("Project {} not found", project_id);
}
```

### 3. Batch Operations When Possible

```rust
// Good: create multiple items in a loop
for item in items {
    db.create_checklist_item(&item).await?;
}
```

### 4. Use Configuration for Paths

```rust
// Good
let path = config.sqlite.unwrap().path;
let db = SqliteDatabase::new(&path).await?;

// Bad
let db = SqliteDatabase::new("workspace.db").await?; // hardcoded
```

### 5. Always Handle Connection Errors

```rust
match db.ping().await {
    Ok(()) => println!("Database is healthy"),
    Err(e) => eprintln!("Database is unavailable: {}", e),
}
```

---

## Common Operations

### List All Projects for a Phase

```rust
let projects = db.list_projects().await?;
for project in projects {
    if project.current_phase == "Development" {
        println!("{}: {}", project.id, project.name);
    }
}
```

### Get Project Completion Percentage

```rust
let completion = db.get_completion_percentage(&project_id, "Development").await?;
println!("Phase completion: {:.1}%", completion);
```

### Record Security Assessment

```rust
use workspace_core::DbSecurityAssessment;

let assessment = DbSecurityAssessment {
    id: Uuid::new_v4().to_string(),
    project_id: project.id.clone(),
    profile: "OWASP".to_string(),
    risk_level: "Medium".to_string(),
    passed_rules: 45,
    failed_rules: 5,
    critical_issues: 0,
    assessment_date: Utc::now().to_rfc3339(),
};

db.create_security_assessment(&assessment).await?;
```

### Get Phase History

```rust
let history = db.get_phase_history(&project_id).await?;
for transition in history {
    println!(
        "{}: {} → {} (reason: {:?})",
        transition.timestamp,
        transition.from_phase,
        transition.to_phase,
        transition.reason
    );
}
```

---

## Troubleshooting

### Issue: "Database is locked"
**Solution**: SQLite doesn't handle high concurrency well. For production:
1. Reduce connection pool size
2. Use timeout configurations
3. Switch to SurrealDB (when available)

### Issue: "File not found"
**Solution**: Ensure directory exists:
```rust
let path = Path::new("~/.local/share/core/workspace.db");
if let Some(parent) = path.parent() {
    std::fs::create_dir_all(parent)?;
}
```

### Issue: "Connection timeout"
**Solution**: Increase timeout in configuration:
```toml
[sqlite]
timeout_secs = 60  # Increase from default 30
```

---

## Next Steps

1. **Review** `SURREALDB_IMPLEMENTATION_PROGRESS.md` for architecture details
2. **Check** `src/persistence/mod.rs` for full Database trait documentation
3. **Explore** `src/persistence/config.rs` for configuration options
4. **Study** `src/persistence/sqlite_impl.rs` for implementation patterns
5. **Run** `cargo test -p syntaxis-core --lib persistence` to verify setup

---

## References

- [Database Trait Documentation](src/persistence/mod.rs)
- [Configuration Guide](src/persistence/config.rs)
- [SQLite Implementation](src/persistence/sqlite_impl.rs)
- [Error Types](src/persistence/error.rs)
- [Migration Infrastructure](src/persistence/migration.rs)
- [Implementation Progress](SURREALDB_IMPLEMENTATION_PROGRESS.md)
- [Original Plan](SURREALDB_IMPLEMENTATION_FINAL_PLAN.md)

---

**Last Updated**: November 15, 2025
**Status**: Ready for Use
**Support**: See SURREALDB_IMPLEMENTATION_PROGRESS.md for more details