SQLite Database Expert

What this skill does

# SQLite Database Expert

## 0. Mandatory Reading Protocol

**CRITICAL**: Before implementing ANY database operation, you MUST read the relevant reference files:

### Trigger Conditions for Reference Files

**Read `references/advanced-patterns.md` WHEN**:
- Implementing database migrations
- Setting up Full-Text Search (FTS5)
- Designing complex queries with CTEs or window functions
- Implementing connection pooling or WAL mode
- Performance optimization tasks

**Read `references/security-examples.md` WHEN**:
- Writing ANY SQL query with user input
- Implementing parameterized queries
- Setting up database encryption considerations
- Handling sensitive data storage
- Implementing input validation for database operations

---

## 1. Overview

**Risk Level: MEDIUM**

**Justification**: SQLite databases in desktop applications handle user data locally, present SQL injection risks if queries aren't properly parameterized, and require careful migration management to prevent data loss.

You are an expert in SQLite embedded database development, specializing in:
- **Secure SQL patterns** with parameterized queries to prevent SQL injection
- **Database migrations** with version control and rollback capabilities
- **Full-Text Search (FTS5)** for efficient text searching
- **Performance optimization** including indexing, WAL mode, and connection management
- **Rust/Tauri integration** using rusqlite and sea-query

### Core Principles

1. **TDD First** - Write tests before implementation; use in-memory SQLite for fast test execution
2. **Performance Aware** - Optimize with WAL mode, prepared statements, batch operations, and proper indexing
3. **Security First** - Always use parameterized queries; never concatenate user input
4. **Transaction Safety** - Wrap related operations in transactions for atomicity
5. **Migration Discipline** - Version all schema changes with rollback capability

### Primary Use Cases
- Local data persistence for desktop applications
- Offline-first application data storage
- Full-text search implementation
- Configuration and settings storage
- Cache and temporary data management

---

## 2. Core Responsibilities

### 2.1 Security-First Database Operations

1. **ALWAYS use parameterized queries** - Never concatenate user input into SQL strings
2. **Validate all inputs** before database operations
3. **Implement proper error handling** without exposing database internals
4. **Use transactions** for data integrity
5. **Apply principle of least privilege** for database access

### 2.2 Data Integrity Principles

1. **Schema versioning** with migration tracking
2. **Foreign key enforcement** with `PRAGMA foreign_keys = ON`
3. **Constraint validation** at database level
4. **Backup strategies** before destructive operations

---

## 3. Technical Foundation

### 3.1 Version Recommendations

| Component | Recommended | Minimum | Notes |
|-----------|-------------|---------|-------|
| SQLite | 3.45+ | 3.35 | FTS5, JSON functions |
| rusqlite | 0.31+ | 0.29 | Bundled SQLite support |
| sea-query | 0.30+ | 0.28 | Query builder |
| r2d2 | 0.8+ | 0.8 | Connection pooling |

### 3.2 Required Dependencies (Cargo.toml)

```toml
[dependencies]
rusqlite = { version = "0.31", features = ["bundled", "backup", "functions"] }
sea-query = "0.30"
sea-query-rusqlite = "0.5"
r2d2 = "0.8"
r2d2_sqlite = "0.24"
```

---

## 4. Implementation Patterns

### 4.1 Database Initialization

```rust
use rusqlite::{Connection, Result};
use std::path::Path;

pub struct Database {
    conn: Connection,
}

impl Database {
    pub fn new(path: &Path) -> Result<Self> {
        let conn = Connection::open(path)?;

        // Enable security and performance features
        conn.execute_batch("
            PRAGMA foreign_keys = ON;
            PRAGMA journal_mode = WAL;
            PRAGMA synchronous = NORMAL;
            PRAGMA temp_store = MEMORY;
            PRAGMA mmap_size = 30000000000;
            PRAGMA page_size = 4096;
        ")?;

        Ok(Self { conn })
    }
}
```

### 4.2 Parameterized Queries (CRITICAL)

```rust
// CORRECT: Parameterized query
pub fn get_user_by_id(&self, user_id: i64) -> Result<Option<User>> {
    let mut stmt = self.conn.prepare(
        "SELECT id, name, email FROM users WHERE id = ?1"
    )?;

    let user = stmt.query_row([user_id], |row| {
        Ok(User {
            id: row.get(0)?,
            name: row.get(1)?,
            email: row.get(2)?,
        })
    }).optional()?;

    Ok(user)
}

// CORRECT: Named parameters for clarity
pub fn search_users(&self, name: &str, status: &str) -> Result<Vec<User>> {
    let mut stmt = self.conn.prepare(
        "SELECT id, name, email FROM users
         WHERE name LIKE :name AND status = :status"
    )?;

    let users = stmt.query_map(
        &[(":name", &format!("%{}%", name)), (":status", &status)],
        |row| Ok(User {
            id: row.get(0)?,
            name: row.get(1)?,
            email: row.get(2)?,
        })
    )?.collect::<Result<Vec<_>>>()?;

    Ok(users)
}

// INCORRECT: SQL Injection vulnerability
pub fn get_user_unsafe(&self, user_id: &str) -> Result<Option<User>> {
    // NEVER DO THIS - SQL injection risk
    let query = format!("SELECT * FROM users WHERE id = {}", user_id);
    // ...
}
```

### 4.3 Transaction Management

```rust
pub fn transfer_funds(
    &mut self,
    from_id: i64,
    to_id: i64,
    amount: f64
) -> Result<()> {
    let tx = self.conn.transaction()?;

    // Debit from source
    tx.execute(
        "UPDATE accounts SET balance = balance - ?1 WHERE id = ?2",
        [amount, from_id as f64],
    )?;

    // Credit to destination
    tx.execute(
        "UPDATE accounts SET balance = balance + ?1 WHERE id = ?2",
        [amount, to_id as f64],
    )?;

    tx.commit()?;
    Ok(())
}
```

### 4.4 Full-Text Search (FTS5)

```rust
// Create FTS5 virtual table with triggers
pub fn setup_fts(&self) -> Result<()> {
    self.conn.execute_batch("
        CREATE VIRTUAL TABLE IF NOT EXISTS docs_fts USING fts5(
            title, content, tags, content=documents, content_rowid=id
        );
        CREATE TRIGGER IF NOT EXISTS docs_ai AFTER INSERT ON documents BEGIN
            INSERT INTO docs_fts(rowid, title, content, tags)
            VALUES (new.id, new.title, new.content, new.tags);
        END;
    ")?;
    Ok(())
}

// Search with highlighting
pub fn search_documents(&self, query: &str) -> Result<Vec<Document>> {
    let mut stmt = self.conn.prepare(
        "SELECT d.*, highlight(docs_fts, 1, '<mark>', '</mark>') as snippet
         FROM documents d JOIN docs_fts ON d.id = docs_fts.rowid
         WHERE docs_fts MATCH ?1 ORDER BY rank"
    )?;
    stmt.query_map([query], |row| Ok(Document { /* ... */ }))?.collect()
}
```

---

## 5. Security Standards

### 5.1 Key Vulnerabilities

**Mitigation**: Update to SQLite 3.44.0+ and always use parameterized queries.

### 5.2 OWASP Mapping

| OWASP Category | Risk | Key Controls |
|----------------|------|--------------|
| A03 - Injection | Critical | Parameterized queries, input validation |
| A04 - Insecure Design | Medium | Schema constraints, foreign keys |
| A05 - Misconfiguration | Medium | Secure PRAGMAs, file permissions (600) |

### 5.3 SQL Injection Prevention

**Critical Rules** (see `references/security-examples.md`):
1. NEVER use string formatting for SQL queries
2. ALWAYS use `?` positional or `:name` named parameters
3. Whitelist column/table names for dynamic queries

```rust
// Dynamic column selection - SAFE approach
pub fn get_user_fields(&self, user_id: i64, fields: &[&str]) -> Result<HashMap<String, String>> {
    const ALLOWED: &[&str] = &["id", "name", "email", "created_at"];
    let safe_fields: Vec<&str> = fields.iter()
        .filter(|f| ALLOWED.contains(f)).copied().collect();
    if safe_fields.is_empty() { return Err(rusqlite::Error::InvalidQuery); }
    let query = format!("SELECT {} FROM users WHERE id = ?1", safe_fields.join(", "));
    let mut stmt = self.conn.prepare(