# 📚 CLI Commands for Database and SurrealDB Operations

## Quick Summary

**We did NOT explicitly create CLI commands to switch between SQLite and SurrealDB.**

Instead, we implemented **configuration-driven database selection** which means:
- The CLI automatically uses whatever database is configured in `configs/database.toml`
- Users switch databases by changing configuration files, not by CLI flags
- This is more flexible and doesn't require CLI code changes

---

## How Database Selection Works

### Current Implementation (Configuration-Driven)

```bash
# Step 1: Choose your database by copying config
cp configs/database-default.toml configs/database.toml      # Use SQLite
# OR
cp configs/database-surrealdb.toml configs/database.toml     # Use SurrealDB

# Step 2: Run CLI normally (no special flags needed)
workspace create
workspace status
workspace checklist show
```

The CLI automatically loads the database from `configs/database.toml`:

```toml
# configs/database.toml

[database]
engine = "sqlite"  # or "surrealdb"

# If sqlite:
[sqlite]
path = "~/.local/share/core/workspace.db"

# If surrealdb:
[surrealdb]
url = "mem://"  # or "file://", "ws://localhost:8000"
```

---

## All Available CLI Commands

### **1. Project Management**

```bash
# Initialize a new project
workspace init [PATH] --type <TYPE>

# Create project in database from TOML config
workspace create

# Migrate existing project
workspace migrate <PATH> [--no-backup]

# Show project status
workspace status

# Manage projects
workspace project <SUBCOMMAND>
  - list      List all projects
  - get       Get specific project
  - create    Create new project
  - update    Update project
  - delete    Delete project
```

---

### **2. Phase Management**

```bash
# Show current phase
workspace phase current

# Transition to new phase
workspace phase transition <PHASE> [--check-checklist]

# Phases: creation → devel → update → review ↔ status → publish → archive
```

---

### **3. Checklist Operations**

```bash
# Show checklist (for current or specific phase)
workspace checklist show [PHASE]

# Mark item complete
workspace checklist complete <ITEM_ID>

# Add item to checklist
workspace checklist add <DESCRIPTION>

# Show available task types
workspace checklist types
```

---

### **4. Security Management**

```bash
# Set security profile
workspace security profile <PROFILE_TYPE>

# Profiles: webapi, clitool, dataprocessing, iot, ml, opensourcelib, enterprise, desktop, mobile, microservice

# Assess security posture
workspace security assess [--detailed]

# Run security audit
workspace audit [--audit-vulns] [--sbom-filter] [--detailed]
```

---

### **5. Tool Management**

```bash
# List all tools
workspace tool list

# Enable a tool
workspace tool enable <TOOL>

# Disable a tool
workspace tool disable <TOOL>

# Show tool status
workspace tool status

# List Rust tool templates
workspace tool list-rust

# Add Rust tool template
workspace tool add-rust-template <TOOL>
```

---

### **6. Configuration Management**

```bash
# Show configuration
workspace config show

# Validate configuration
workspace config validate
```

**NOTE**: Database is configured via the config file, not CLI commands.

---

### **7. Database Migration** ⭐ (Most Relevant)

```bash
# Migrate from local project DB to global database
workspace migrate-db <PATH>

# Scan for local databases
workspace migrate-db --scan <ROOT>

# Show migration status
workspace migrate-db --status

# Execute SQL migration file
workspace migrate-db --sql-file <FILE>

# Run all migration files in directory
workspace migrate-db --run-all <ROOT>
```

**This handler supports both SQLite and SurrealDB databases through the Database trait!**

---

### **8. Import/Export**

```bash
# Export project data
workspace export [--format <FORMAT>] [--output <FILE>] [--project <PROJECT>]

# Formats: json, csv, toml, yaml, markdown

# Import project data
workspace import <FILE> [--format <FORMAT>] [--overwrite] [--dry-run]
```

---

### **9. Search**

```bash
# Search projects with filters
workspace search <QUERY> [--phase <PHASE>] [--type <TYPE>] [--sort <FIELD>] [--order asc|desc]
```

---

### **10. Batch Operations**

```bash
# Run batch operations on multiple projects
workspace batch <FILE>
```

---

### **11. Software Bill of Materials (SBOM)**

```bash
# Generate SBOM in configured format(s)
workspace sbom generate

# Generate and display dependency tree
workspace sbom tree
```

---

### **12. Plugins**

```bash
# List plugins
workspace plugin list

# Install plugin
workspace plugin install <PATH>

# Uninstall plugin
workspace plugin uninstall <NAME>

# Enable plugin
workspace plugin enable <NAME>

# Disable plugin
workspace plugin disable <NAME>

# Show plugin info
workspace plugin info <NAME>
```

---

## Global Flags

Available for all commands:

```bash
# Verbose output
workspace --verbose <COMMAND>
# or
workspace -v <COMMAND>

# Auto-detect project (optional, usually automatic)
workspace --project <NAME> <COMMAND>
# or
workspace -p <NAME> <COMMAND>

# Use remote API instead of local database
workspace --remote <COMMAND>

# Specify API server base URL (default: http://localhost:3000)
workspace --api-url <URL> <COMMAND>
```

---

## Database-Specific Usage

### **With SQLite (Default)**

```bash
# Copy SQLite config
cp configs/database-default.toml configs/database.toml

# Run CLI normally - uses SQLite automatically
workspace create
workspace status
workspace checklist show
```

### **With SurrealDB In-Memory**

```bash
# Copy SurrealDB config
cp configs/database-surrealdb.toml configs/database.toml

# Edit config to use in-memory mode
# [surrealdb]
# url = "mem://"

# Run CLI normally - uses SurrealDB in-memory
workspace create
workspace status
workspace checklist show
```

### **With SurrealDB Server Mode**

```bash
# Copy SurrealDB config
cp configs/database-surrealdb.toml configs/database.toml

# Edit config for server mode
# [surrealdb]
# url = "ws://localhost:8000"
# username = "admin"
# password = "password"

# Start SurrealDB server (in another terminal)
surreal start --bind 127.0.0.1:8000 memory

# Run CLI normally - uses SurrealDB server
workspace create
workspace status
workspace checklist show
```

---

## How CLI Uses Databases

### **Code Architecture**

```
syntaxis-cli/src/main.rs
    ↓ (loads config)
configs/database.toml
    ↓ (reads engine type)
┌─────────────────────────────────────────┐
│ syntaxis-core/src/persistence/         │
│ ├─ mod.rs (Database trait)              │
│ ├─ sqlite_impl.rs (SqliteDatabase)      │
│ └─ surrealdb_impl.rs (SurrealDatabase)  │
└─────────────────────────────────────────┘
    ↓ (CLI handlers use Database trait)
handlers/
    ├─ create.rs (calls db.create_project())
    ├─ checklist.rs (calls db.get_checklist_items())
    ├─ security.rs (calls db.create_security_assessment())
    ├─ migrate_db.rs (calls db.list_projects() + db.create_project())
    └─ ... (all use Database trait)
```

### **Database Trait Methods Used**

In `migrate_db.rs`, for example:

```rust
let local_db = SqliteDatabase::new(local_db_path).await?;
let global_db = SqliteDatabase::new(&global_db_path_str).await?;

// These methods work with BOTH SQLite and SurrealDB:
let projects = local_db.list_projects().await?;    // Database trait
global_db.create_project(project).await?;          // Database trait
```

All handlers use this pattern - they don't care if it's SQLite or SurrealDB!

---

## Configuration vs CLI Commands

### **Why No Database CLI Flags?**

We intentionally did NOT add CLI flags like:

```bash
# ❌ These DON'T exist:
workspace --database sqlite create
workspace --database surrealdb create
workspace --db-url ws://localhost:8000 create
```

**Reasons:**

1. **Configuration-Driven**: Better to have single config file than multiple CLI flags
2. **Consistency**: All tools use same database, easier to manage
3. **Simplicity**: Users copy config file, not memorize flags
4. **Flexibility**: Can change database without code changes

### **What We Created Instead**

```
configs/database-default.toml       ← SQLite config template
configs/database-surrealdb.toml     ← SurrealDB config template

// Switch databases:
cp configs/database-{default|surrealdb}.toml configs/database.toml

// All CLI commands then use the selected database
```

---

## Database Configuration File

### **Configuration**

The database configuration is loaded from `configs/database.toml`.

### **SQLite Example**

```toml
[database]
engine = "sqlite"

[sqlite]
path = "~/.local/share/core/workspace.db"
max_connections = 5
```

### **SurrealDB Example**

```toml
[database]
engine = "surrealdb"

[surrealdb]
url = "mem://"  # or "file://", "ws://localhost:8000"
namespace = "syntaxis"
database = "projects"
max_connections = 10
```

---

## How To Use SurrealDB With CLI

### **Step-by-Step**

#### **Option 1: In-Memory (Development)**

```bash
# 1. Copy SurrealDB config
cp configs/database-surrealdb.toml configs/database.toml

# 2. Ensure config has:
# [surrealdb]
# url = "mem://"

# 3. Run CLI (no server needed)
workspace create
workspace status
```

#### **Option 2: File-Based (Local Development)**

```bash
# 1. Copy SurrealDB config
cp configs/database-surrealdb.toml configs/database.toml

# 2. Set in config:
# [surrealdb]
# url = "file:///tmp/surrealdb.db"

# 3. Run CLI
workspace create
workspace status
```

#### **Option 3: Server Mode (Testing/Production)**

```bash
# 1. Copy SurrealDB config
cp configs/database-surrealdb.toml configs/database.toml

# 2. Set in config:
# [surrealdb]
# url = "ws://localhost:8000"
# username = "admin"
# password = "password"

# 3. Start SurrealDB server (another terminal)
surreal start --bind 127.0.0.1:8000 memory

# 4. Run CLI
workspace create
workspace status
```

---

## Summary of Database Commands

| What | How | Where |
|------|-----|-------|
| **Switch to SQLite** | `cp configs/database-default.toml configs/database.toml` | Configuration |
| **Switch to SurrealDB** | `cp configs/database-surrealdb.toml configs/database.toml` | Configuration |
| **Migrate databases** | `workspace migrate-db <PATH>` | CLI command |
| **Show config** | `workspace config show` | CLI command |
| **Validate config** | `workspace config validate` | CLI command |

---

## What Was Modified For SurrealDB

### **Handlers Using Database Trait**

1. **create.rs**
   - Creates projects (works with SQLite or SurrealDB)
   - ✅ Uses `Database::create_project()`

2. **checklist.rs**
   - Manages checklist items (works with both)
   - ✅ Uses `Database::get_checklist_items_by_phase()`
   - ✅ Uses `Database::complete_checklist_item()`

3. **security.rs**
   - Security assessments (works with both)
   - ✅ Uses `Database::create_security_assessment()`

4. **migrate_db.rs** ⭐ **KEY FILE**
   - Migrates databases (works with both)
   - ✅ Uses `Database::list_projects()`
   - ✅ Uses `Database::create_project()`

5. **export.rs**, **project.rs**, **init.rs**
   - Data export and project management
   - ✅ All use Database trait methods

---

## Key Insight

**The beauty of our implementation:**

```
CLI Code (handlers/)
    ↓
Database Trait (abstraction)
    ↓
┌────────────────────────────────────────┐
│ SqliteDatabase    SurrealDatabase      │
│ (SQLite backend)  (SurrealDB backend)  │
└────────────────────────────────────────┘

Same CLI code works with BOTH databases!
No CLI flags needed - configuration file handles it.
```

---

## Configuration Priority

When CLI runs, it loads database config in this order:

1. Check `configs/database.toml` (primary)
2. Use `[database]` engine setting
3. Load backend-specific config:
   - If `sqlite`: use `[sqlite]` section
   - If `surrealdb`: use `[surrealdb]` section
4. Initialize appropriate database driver
5. Run CLI command with selected database

---

## Conclusion

**CLI Database Commands Summary:**

| Command | Purpose | Database Support |
|---------|---------|------------------|
| `workspace migrate-db` | Migrate databases | ✅ Both |
| `workspace config show` | Show configuration | ✅ Both |
| `workspace config validate` | Validate configuration | ✅ Both |
| `workspace create` | Create project | ✅ Both |
| `workspace status` | Show status | ✅ Both |
| `workspace checklist` | Manage checklist | ✅ Both |
| `workspace security` | Security operations | ✅ Both |
| All other commands | Various operations | ✅ Both |

**Database is selected via configuration file, not CLI commands.**

This is **cleaner, simpler, and more flexible** than adding CLI flags!