<!--
hoody-sqlite Subskill (http)
Auto-generated by Hoody Skills Generator
Generated: 2026-05-06T18:26:17.309Z
Model: mimo-v2.5-pro
Mode: http


Tokens: 7641

DO NOT EDIT MANUALLY - Changes will be overwritten on next generation
-->

# hoody-sqlite Subskill

## Overview

**hoody-sqlite** provides portable SQLite databases accessible from anywhere via HTTP. It combines full SQL query capabilities with a built-in key-value store, enabling both structured data operations and simple state management through a unified API.

### When to Use

- **Application state storage**: Store configuration, session data, or feature flags
- **Lightweight data persistence**: When you need a database without provisioning infrastructure
- **Key-value caching**: Simple get/set operations with TTL, atomic increments, and array manipulation
- **Data versioning**: Built-in history, snapshots, and rollback capabilities for any stored data
- **Shareable queries**: Execute SQL via URL-encoded GET requests for easy sharing and debugging

### How It Fits Hoody Philosophy

hoody-sqlite embodies "portable databases accessible from anywhere" by:
- Providing SQL over HTTP with no client library requirements
- Offering key-value shortcuts for common patterns without writing SQL
- Supporting database creation and management via API
- Enabling time-travel queries and data rollback for safety
- Running as a containerized service with automatic routing

### Service URL Pattern

```
https://{projectId}-{containerId}-sqlite-{serviceId}.{node}.containers.hoody.icu
```

All endpoints use the `/api/v1/sqlite/` prefix. The `db` query parameter identifies which database to operate on.

---

## Common Workflows

### 1. Create a Database and Execute SQL

```
# Step 1: Create a new database
curl -s -X POST \
  "https://{BASE_URL}/api/v1/sqlite/db/create?path=myapp.db"

# Step 2: Create a table and insert data via transaction
curl -s -X POST \
  "https://{BASE_URL}/api/v1/sqlite/db?db=myapp.db" \
  -H "Content-Type: application/json" \
  -d '{
    "statements": [
      "CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, name TEXT, email TEXT)",
      "INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com')",
      "INSERT INTO users (name, email) VALUES ('Bob', 'bob@example.com')"
    ]
  }'

# Step 3: Query the data
curl -s \
  "https://{BASE_URL}/api/v1/sqlite/query?db=myapp.db&sql=SELECT%20*%20FROM%20users"
```

### 2. Key-Value Store: Basic CRUD

```
# Store a value
curl -s -X PUT \
  "https://{BASE_URL}/api/v1/sqlite/kv/config.theme?db=myapp.db" \
  -H "Content-Type: application/json" \
  -d '{"value": "dark"}'

# Retrieve a value
curl -s \
  "https://{BASE_URL}/api/v1/sqlite/kv/config.theme?db=myapp.db"

# Check if key exists (HEAD request)
curl -s -I \
  "https://{BASE_URL}/api/v1/sqlite/kv/config.theme?db=myapp.db"

# Delete a key
curl -s -X DELETE \
  "https://{BASE_URL}/api/v1/sqlite/kv/config.theme?db=myapp.db"

# List all keys with optional prefix filter
curl -s \
  "https://{BASE_URL}/api/v1/sqlite/kv?db=myapp.db&prefix=config"
```

### 3. Atomic Numeric Operations

```
# Set initial counter value
curl -s -X PUT \
  "https://{BASE_URL}/api/v1/sqlite/kv/visitors?db=myapp.db" \
  -H "Content-Type: application/json" \
  -d '{"value": 0}'

# Increment atomically
curl -s -X POST \
  "https://{BASE_URL}/api/v1/sqlite/kv/visitors/incr?db=myapp.db"

# Decrement atomically
curl -s -X POST \
  "https://{BASE_URL}/api/v1/sqlite/kv/visitors/decr?db=myapp.db"

# Increment by specific amount (via JSON body)
curl -s -X POST \
  "https://{BASE_URL}/api/v1/sqlite/kv/visitors/incr?db=myapp.db" \
  -H "Content-Type: application/json" \
  -d '{"amount": 10}'
```

### 4. Array Operations

```
# Store an array
curl -s -X PUT \
  "https://{BASE_URL}/api/v1/sqlite/kv/tags?db=myapp.db" \
  -H "Content-Type: application/json" \
  -d '{"value": ["sqlite", "hoody"]}'

# Push to array
curl -s -X POST \
  "https://{BASE_URL}/api/v1/sqlite/kv/tags/push?db=myapp.db" \
  -H "Content-Type: application/json" \
  -d '{"value": "database"}'

# Pop from array
curl -s -X POST \
  "https://{BASE_URL}/api/v1/sqlite/kv/tags/pop?db=myapp.db"

# Remove by value
curl -s -X POST \
  "https://{BASE_URL}/api/v1/sqlite/kv/tags/remove?db=myapp.db" \
  -H "Content-Type: application/json" \
  -d '{"value": "hoody"}'
```

### 5. Batch Operations

```
# Batch set multiple keys
curl -s -X POST \
  "https://{BASE_URL}/api/v1/sqlite/kv/batch/set?db=myapp.db" \
  -H "Content-Type: application/json" \
  -d '{
    "items": [
      {"key": "user:1:name", "value": "Alice"},
      {"key": "user:1:email", "value": "alice@example.com"},
      {"key": "user:2:name", "value": "Bob"}
    ]
  }'

# Batch get multiple keys
curl -s -X POST \
  "https://{BASE_URL}/api/v1/sqlite/kv/batch/get?db=myapp.db" \
  -H "Content-Type: application/json" \
  -d '{
    "keys": ["user:1:name", "user:1:email", "user:2:name"]
  }'

# Batch delete multiple keys
curl -s -X POST \
  "https://{BASE_URL}/api/v1/sqlite/kv/batch/delete?db=myapp.db" \
  -H "Content-Type: application/json" \
  -d '{
    "keys": ["user:1:name", "user:1:email"]
  }'
```

### 6. Health Check and Monitoring

```
# Full health check
curl -s \
  "https://{BASE_URL}/api/v1/sqlite/health"

# Cache-specific health (lightweight for dashboards)
curl -s \
  "https://{BASE_URL}/api/v1/sqlite/health/cache"
```

---

## Advanced Operations

### 1. Time-Travel and Snapshots

```
# Get current KV table snapshot
curl -s \
  "https://{BASE_URL}/api/v1/sqlite/kv/snapshot?db=myapp.db&timestamp=2025-01-15T10:00:00Z"

# Get snapshot of a specific key at an operation number
curl -s \
  "https://{BASE_URL}/api/v1/sqlite/kv/config.theme/snapshot?db=myapp.db&op_number=42"

# Compare KV state between two timestamps
curl -s \
  "https://{BASE_URL}/api/v1/sqlite/kv/diff?db=myapp.db&from=2025-01-15T09:00:00Z&to=2025-01-15T10:00:00Z"

# View operation history for a specific key
curl -s \
  "https://{BASE_URL}/api/v1/sqlite/kv/config.theme/history?db=myapp.db"
```

### 2. Rollback Operations

```
# Rollback a single key to previous state
curl -s -X POST \
  "https://{BASE_URL}/api/v1/sqlite/kv/config.theme/rollback?db=myapp.db" \
  -H "Content-Type: application/json" \
  -d '{"steps": 1}'

# Rollback entire KV table to a timestamp
curl -s -X POST \
  "https://{BASE_URL}/api/v1/sqlite/kv/rollback?db=myapp.db&to_timestamp=2025-01-15T09:00:00Z" \
  -H "Content-Type: application/json" \
  -d '{"confirm": true}'
```

### 3. Query History Management

```
# View query history
curl -s \
  "https://{BASE_URL}/api/v1/sqlite/history?db=myapp.db&limit=50"

# Get history statistics
curl -s \
  "https://{BASE_URL}/api/v1/sqlite/history/stats?db=myapp.db"

# Delete a specific history entry
curl -s -X DELETE \
  "https://{BASE_URL}/api/v1/sqlite/history/42?db=myapp.db"

# Clear all history
curl -s -X DELETE \
  "https://{BASE_URL}/api/v1/sqlite/history?db=myapp.db"
```

### 4. JSON Path Operations

hoody-sqlite supports JSON path extraction for nested values:

```
# Store nested JSON
curl -s -X PUT \
  "https://{BASE_URL}/api/v1/sqlite/kv/user.settings?db=myapp.db" \
  -H "Content-Type: application/json" \
  -d '{"value": {"theme": "dark", "notifications": {"email": true, "sms": false}}}'

# Retrieve nested value via JSON path
curl -s \
  "https://{BASE_URL}/api/v1/sqlite/kv/user.settings?db=myapp.db&path=notifications.email"

# Increment a nested numeric value
curl -s -X POST \
  "https://{BASE_URL}/api/v1/sqlite/kv/user.settings/incr?db=myapp.db" \
  -H "Content-Type: application/json" \
  -d '{"path": "login_count"}'
```

### 5. Error Recovery Pattern

```
# If a transaction fails, check health first
curl -s \
  "https://{BASE_URL}/api/v1/sqlite/health"

# Review recent history to understand what happened
curl -s \
  "https://{BASE_URL}/api/v1/sqlite/history?db=myapp.db&limit=10"

# If KV data is corrupted, rollback to last known good state
curl -s \
  "https://{BASE_URL}/api/v1/sqlite/kv/diff?db=myapp.db&from=2025-01-15T08:00:00Z&to=2025-01-15T09:00:00Z"

# Then rollback to before the corruption
curl -s -X POST \
  "https://{BASE_URL}/api/v1/sqlite/kv/rollback?db=myapp.db&to_timestamp=2025-01-15T08:00:00Z" \
  -H "Content-Type: application/json" \
  -d '{"confirm": true}'
```

### Performance Considerations

- **Batch operations**: Use `/kv/batch/*` endpoints for multiple keys (max 100 per request)
- **Pagination**: Use `limit` and `offset` parameters when listing keys
- **Cache monitoring**: Poll `/health/cache` for lightweight cache pressure metrics
- **History cleanup**: Regularly clear old history entries to maintain performance
- **Connection reuse**: HTTP keep-alive is recommended for multiple sequential operations

---

## Quick Reference

### Most Common Endpoints

| Operation | Method | Path |
|-----------|--------|------|
| Execute SQL | POST | `/api/v1/sqlite/db?db={db}` |
| Create DB | POST | `/api/v1/sqlite/db/create?path={path}` |
| Query via URL | GET | `/api/v1/sqlite/query?db={db}&sql={sql}` |
| Get KV value | GET | `/api/v1/sqlite/kv/{key}?db={db}` |
| Set KV value | PUT | `/api/v1/sqlite/kv/{key}?db={db}` |
| Delete KV key | DELETE | `/api/v1/sqlite/kv/{key}?db={db}` |
| List keys | GET | `/api/v1/sqlite/kv?db={db}` |
| Increment | POST | `/api/v1/sqlite/kv/{key}/incr?db={db}` |
| Health check | GET | `/api/v1/sqlite/health` |

### Essential Parameters

| Parameter | Description | Example |
|-----------|-------------|---------|
| `db` | Database identifier (required for most endpoints) | `myapp.db` |
| `path` | Database file path (for creation) | `myapp.db` |
| `key` | KV store key name | `config.theme` |
| `prefix` | Filter keys by prefix | `config` |
| `limit` | Max results to return | `50` |
| `offset` | Skip N results | `100` |
| `timestamp` | ISO 8601 timestamp for snapshots | `2025-01-15T10:00:00Z` |
| `op_number` | Operation number for key snapshots | `42` |

### Typical Response Formats

**Success (KV Get)**:
```
{
  "key": "config.theme",
  "value": "dark",
  "updated_at": "2025-01-15T10:30:00Z"
}
```

**Success (SQL Query)**:
```
{
  "rows": [
    {"id": 1, "name": "Alice", "email": "alice@example.com"},
    {"id": 2, "name": "Bob", "email": "bob@example.com"}
  ],
  "columns": ["id", "name", "email"],
  "rows_affected": 0
}
```

**Error**:
```
{
  "error": "Key not found",
  "code": "KV_KEY_NOT_FOUND"
}
```

**Health Check**:
```
{
  "status": "ok",
  "service": "hoody-sqlite",
  "built": "2025-01-15T08:00:00Z",
  "started": "2025-01-15T09:00:00Z",
  "pid": 12345,
  "memory": {"rss": 52428800, "heap_used": 20971520},
  "fds": 24
}
```

### Documentation Endpoints

```
# Get OpenAPI spec (YAML)
curl -s \
  "https://{BASE_URL}/api/v1/sqlite/openapi.yaml"

# Get OpenAPI spec (JSON - redirects to YAML)
curl -s \
  "https://{BASE_URL}/api/v1/sqlite/openapi.json"
```