# ⚡ CLI Quick Reference: Database Commands

## TL;DR - No Special Database CLI Commands

**We did NOT create CLI flags like `--database sqlite` or `--surrealdb`**

Instead: **Use configuration files to switch databases**

---

## Quickest Setup

```bash
# 1️⃣ CHOOSE YOUR DATABASE BY COPYING CONFIG

# For SQLite (default):
cp configs/database-default.toml configs/database.toml

# OR for SurrealDB:
cp configs/database-surrealdb.toml configs/database.toml

# 2️⃣ RUN CLI COMMANDS NORMALLY (no special flags!)

workspace create                    # Create project
workspace status                    # Show status
workspace checklist show            # Show checklist
workspace config show               # Show configuration
```

That's it! No other steps needed.

---

## Available CLI Commands

### **Project Operations**
```bash
workspace init [PATH] --type TYPE        # Initialize project
workspace create                         # Create from config
workspace migrate PATH                   # Migrate project
workspace status                         # Show status
```

### **Checklist Management**
```bash
workspace checklist show [PHASE]         # Show checklist
workspace checklist complete ITEM_ID     # Mark complete
workspace checklist add DESCRIPTION      # Add item
workspace checklist types                # Show task types
```

### **Security**
```bash
workspace security profile TYPE          # Set security profile
workspace security assess [--detailed]   # Assess security
workspace audit [--audit-vulns]          # Run audit
```

### **Phase Management**
```bash
workspace phase current                  # Show current phase
workspace phase transition PHASE         # Move to phase
```

### **Database Operations** ⭐
```bash
workspace migrate-db PATH                # Migrate DB to global
workspace migrate-db --scan ROOT         # Scan for databases
workspace migrate-db --status            # Show migration status
```

### **Configuration**
```bash
workspace config show                    # Show configuration
workspace config validate                # Validate configuration
```

### **Tools**
```bash
workspace tool list                      # List tools
workspace tool enable TOOL               # Enable tool
workspace tool disable TOOL              # Disable tool
workspace tool status                    # Show tool status
```

### **Import/Export**
```bash
workspace export [--format FORMAT]       # Export data
workspace import FILE [--format FORMAT]  # Import data
```

### **Other**
```bash
workspace search QUERY [filters]         # Search projects
workspace sbom generate                  # Generate SBOM
workspace plugin install PATH            # Install plugin
workspace batch FILE                     # Batch operations
```

---

## Global Flags

Available with any command:

```bash
workspace --verbose COMMAND              # Verbose output
workspace -v COMMAND                     # Shorthand

workspace --project NAME COMMAND         # Specify project
workspace -p NAME COMMAND                # Shorthand

workspace --remote COMMAND               # Use remote API

workspace --api-url URL COMMAND          # API server URL
```

---

## Database Configuration Files

### **SQLite Config** (Recommended Default)
```toml
# File: configs/database-default.toml

[database]
engine = "sqlite"

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

### **SurrealDB Config** (Alternative)
```toml
# File: configs/database-surrealdb.toml

[database]
engine = "surrealdb"

[surrealdb]
# Choose one mode:
url = "mem://"                           # In-memory (fast, no server)
# url = "file:///tmp/surrealdb.db"     # File-based (persistent)
# url = "ws://localhost:8000"          # Server mode (remote)

namespace = "syntaxis"
database = "projects"
max_connections = 10
```

---

## How To Switch Databases

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

# 2. If using server mode, start server:
surreal start --bind 127.0.0.1:8000 memory

# 3. Run CLI (uses SurrealDB automatically)
workspace create
```

### **SurrealDB → SQLite**
```bash
# 1. Copy SQLite config
cp configs/database-default.toml configs/database.toml

# 2. Run CLI (uses SQLite automatically)
workspace create
```

---

## Database Migration Command

Most relevant for databases:

```bash
# Migrate from local to global database
workspace migrate-db PATH

# Example:
workspace migrate-db ~/.local/share/core/old.db

# What it does:
# 1. Opens local database (at PATH)
# 2. Loads all projects from local database
# 3. Saves all projects to global database
# 4. Creates backup of local database
# 5. Reports success
```

This command works with **BOTH SQLite and SurrealDB!**

---

## Configuration Discovery

CLI automatically finds config in this order:

```
1. configs/database.toml                    ← PRIMARY (created by copying)
2. .project/database.toml
3. .vapora/database.toml
4. .coder/database.toml

Uses first one found ✅
```

---

## Real-World Examples

### **Example 1: Local Development with SurrealDB In-Memory**

```bash
# Step 1: Use SurrealDB config
cp configs/database-surrealdb.toml configs/database.toml

# Step 2: Ensure config has url = "mem://"

# Step 3: Run CLI (no server needed!)
workspace create              # Create project
workspace checklist show      # View checklist
workspace status              # Show status
workspace security assess     # Security assessment
```

### **Example 2: Production with SurrealDB Server**

```bash
# Step 1: Use SurrealDB config
cp configs/database-surrealdb.toml configs/database.toml

# Step 2: Update config:
# url = "ws://surrealdb.example.com:8000"
# username = "app-user"
# password = "secure-password"

# Step 3: Run CLI against remote server
workspace create              # Connects to remote SurrealDB
workspace status              # Fetches from remote
```

### **Example 3: Team Using SQLite**

```bash
# Step 1: Use SQLite config (already default)
cp configs/database-default.toml configs/database.toml

# Step 2: Share database file via Git LFS or cloud storage

# Step 3: All team members run same commands
workspace create              # Uses local SQLite
workspace checklist show      # Instant (no network)
```

---

## What Commands Support Databases?

| Command | Works with SQLite? | Works with SurrealDB? |
|---------|-------------------|----------------------|
| `workspace create` | ✅ Yes | ✅ Yes |
| `workspace status` | ✅ Yes | ✅ Yes |
| `workspace checklist` | ✅ Yes | ✅ Yes |
| `workspace security` | ✅ Yes | ✅ Yes |
| `workspace migrate-db` | ✅ Yes | ✅ Yes |
| `workspace export` | ✅ Yes | ✅ Yes |
| `workspace search` | ✅ Yes | ✅ Yes |
| All other commands | ✅ Yes | ✅ Yes |

**Every CLI command works with both databases!**

---

## Troubleshooting

### **"Database not found"**
```bash
# Make sure config exists:
ls -la configs/database.toml

# If missing, copy it:
cp configs/database-default.toml configs/database.toml
```

### **"SurrealDB server connection failed"**
```bash
# Make sure server is running:
surreal start --bind 127.0.0.1:8000 memory

# Check config URL:
workspace config show
```

### **"Wrong database being used"**
```bash
# Check which config is active:
workspace config show

# Copy the right config:
cp configs/database-{default|surrealdb}.toml configs/database.toml
```

---

## Summary

| Question | Answer |
|----------|--------|
| **Any `--database` flag?** | ❌ No - use config files |
| **How to switch databases?** | Copy different config file |
| **CLI code changes needed?** | ❌ No - all commands work both ways |
| **Server needed for SQLite?** | ❌ No - local file |
| **Server needed for SurrealDB in-memory?** | ❌ No - embedded |
| **Server needed for SurrealDB server mode?** | ✅ Yes - one command |
| **Which database is default?** | SQLite (in database-default.toml) |

---

## Files To Know

```
configs/database-default.toml          ← SQLite config template
configs/database-surrealdb.toml        ← SurrealDB config template
configs/database.toml                  ← Active config (created by copying)

syntaxis-core/src/persistence/
    ├─ mod.rs                          ← Database trait
    ├─ sqlite_impl.rs                  ← SQLite implementation
    └─ surrealdb_impl.rs               ← SurrealDB implementation (800+ lines)

syntaxis-cli/src/handlers/
    ├─ migrate_db.rs                   ← Database migration
    ├─ config.rs                       ← Configuration management
    ├─ create.rs                       ← Project creation
    ├─ checklist.rs                    ← Checklist operations
    ├─ security.rs                     ← Security operations
    └─ ... (all use Database trait)
```

---

## Key Insight

**The architecture is beautiful:**

```
One set of CLI commands
         ↓
Database trait (abstraction)
         ↓
    ┌──────────┬──────────┐
    ↓          ↓
 SQLite    SurrealDB

Same CLI code, different backends!
Database is selected via config, not code!
```

This is why no special CLI flags were needed - the design handles it elegantly through configuration.

---

**That's it! You're ready to use either SQLite or SurrealDB with the CLI! 🚀**