<!--
hoody-sqlite Subskill (http)
Auto-generated by Hoody Skills Generator
Generated: 2026-06-20T00:40:34.662Z
Model: mimo-v2.5-pro + fixer:mimo-v2.5-pro
Mode: http


Tokens: 13285

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

# hoody-sqlite Subskill

## Overview

### What It Does
hoody-sqlite provides portable SQLite databases accessible over HTTP. It combines:
- **Full SQL execution** — run any SQLite-compatible SQL through transactional POST endpoints with ACID guarantees
- **Key-Value (KV) store** — a structured shortcut layer built on SQLite with atomic counters, array operations, TTL, and compare-and-swap
- **Time-travel** — reconstruct any KV value or entire table state at any past operation number or timestamp
- **Query history** — track, audit, and inspect past SQL executions per database

### When to Use It
- Store structured data requiring SQL (joins, aggregations, indexes)
- Use a lightweight KV store with array operations and atomic increment/decrement
- Need portable databases that travel with your container project
- Want time-travel debugging for data changes and rollback capability
- Require ACID transactions for multi-step write operations

### How It Fits Into Hoody Philosophy
hoody-sqlite embodies "portable databases accessible from anywhere." Each container gets its own SQLite instance reachable over HTTP — no separate database server required. SQL over HTTP with key-value shortcuts means you get relational query power plus KV simplicity. Data lives with your project and is discoverable through Hoody's proxy routing system.

**Base URL Pattern:**
```
https://{projectId}-{containerId}-sqlite-{serviceId}.{node}.containers.hoody.com
```

Refer to the core SKILL.md for container creation and service discovery to obtain your exact base URL. All endpoint paths below are relative to this base.

---

## Common Workflows

### 1. Create a Database and Execute SQL

Create a new database, run schema and inserts, then query results.

```
BASE="https://myproject-mycontainer-sqlite-myservice.us-east-1.containers.hoody.com"

# Step 1: Create a new database file
curl -s -X POST "$BASE/api/v1/sqlite/db/create?path=myapp.db"

# Step 2: Execute schema + inserts in a single transaction
curl -s -X POST "$BASE/api/v1/sqlite/db?db=myapp.db" \
  -H "Content-Type: application/json" \
  -d '{
    "queries": [
      {"sql": "CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, name TEXT, email TEXT)"},
      {"sql": "INSERT INTO users (name, email) VALUES (?, ?)", "params": ["Alice", "alice@example.com"]},
      {"sql": "INSERT INTO users (name, email) VALUES (?, ?)", "params": ["Bob", "bob@example.com"]}
    ]
  }'

# Step 3: Query data via base64-encoded SQL (shareable via URL)
curl -s "$BASE/api/v1/sqlite/query?db=myapp.db&sql=$(echo -n 'SELECT * FROM users' | base64)"
```

**Verification:** The `/query` response should return the inserted rows. If the table doesn't exist, the transaction in step 2 rolls back entirely.

### 2. KV Store — Basic CRUD

Store, retrieve, check existence, list, and delete key-value pairs.

```
# Set a value
curl -s -X PUT "$BASE/api/v1/sqlite/kv/config.appName?db=myapp.db" \
  -H "Content-Type: application/json" \
  -d '{"value": "MyApp"}'

# Get the value back
curl -s "$BASE/api/v1/sqlite/kv/config.appName?db=myapp.db"

# Check if key exists (HEAD — returns status code only, no body)
curl -s -I "$BASE/api/v1/sqlite/kv/config.appName?db=myapp.db"

# List all keys in the KV store
curl -s "$BASE/api/v1/sqlite/kv?db=myapp.db"

# Delete the key
curl -s -X DELETE "$BASE/api/v1/sqlite/kv/config.appName?db=myapp.db"
```

**Verification:** After PUT, GET returns the value. After DELETE, HEAD returns 404.

### 3. KV Store — Atomic Counters

Atomically increment and decrement numeric values without read-modify-write races.

```
# Initialize counter
curl -s -X PUT "$BASE/api/v1/sqlite/kv/counters.visits?db=myapp.db" \
  -H "Content-Type: application/json" \
  -d '{"value": 0}'

# Increment by 1
curl -s -X POST "$BASE/api/v1/sqlite/kv/counters.visits/incr?db=myapp.db" \
  -H "Content-Type: application/json" \
  -d '{"amount": 1}'

# Decrement by 5
curl -s -X POST "$BASE/api/v1/sqlite/kv/counters.visits/decr?db=myapp.db" \
  -H "Content-Type: application/json" \
  -d '{"amount": 5}'
```

**Verification:** GET the key after each operation to confirm the counter changed atomically.

### 4. KV Store — Array Operations

Push, pop, and remove elements from arrays stored as KV values.

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

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

# Pop last element
curl -s -X POST "$BASE/api/v1/sqlite/kv/tags.recent/pop?db=myapp.db"

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

**Verification:** After push, the array has 3 elements. After pop, it has 2. After remove by value, only matching elements remain.

### 5. Batch KV Operations

Read, write, or delete up to 100 keys in a single transaction.

```
# Set multiple keys atomically
curl -s -X POST "$BASE/api/v1/sqlite/kv/batch/set?db=myapp.db" \
  -H "Content-Type: application/json" \
  -d '{
    "items": [
      {"key": "user.1", "value": {"name": "Alice"}},
      {"key": "user.2", "value": {"name": "Bob"}},
      {"key": "user.3", "value": {"name": "Charlie"}}
    ]
  }'

# Get multiple keys in one request
curl -s -X POST "$BASE/api/v1/sqlite/kv/batch/get?db=myapp.db" \
  -H "Content-Type: application/json" \
  -d '{"keys": ["user.1", "user.2", "user.3"]}'

# Delete multiple keys atomically
curl -s -X POST "$BASE/api/v1/sqlite/kv/batch/delete?db=myapp.db" \
  -H "Content-Type: application/json" \
  -d '{"keys": ["user.2", "user.3"]}'
```

**Verification:** After batch/set, batch/get returns all three. After batch/delete, batch/get returns only user.1.

### 6. Health Monitoring

```
# Full health — service identity, memory, fds, cache stats
curl -s "$BASE/api/v1/sqlite/health"

# Cache-only — lightweight endpoint for dashboard polling
curl -s "$BASE/api/v1/sqlite/health/cache"
```

**Verification:** Health returns status object with service name, pid, memory counters, and cache stats.

---

## Advanced Operations

### Time-Travel — Snapshots and History

Reconstruct past states of individual keys or the entire KV table.

```
# Get a key's value at a specific operation number
curl -s "$BASE/api/v1/sqlite/kv/config.appName/snapshot?db=myapp.db&op_number=5"

# View full operation history for a specific key
curl -s "$BASE/api/v1/sqlite/kv/config.appName/history?db=myapp.db"

# Snapshot the entire KV table at a past timestamp
curl -s "$BASE/api/v1/sqlite/kv/snapshot?db=myapp.db&timestamp=2025-01-15T10:00:00Z"

# Diff: compare KV state between two timestamps
curl -s "$BASE/api/v1/sqlite/kv/diff?db=myapp.db&from=2025-01-15T09:00:00Z&to=2025-01-15T12:00:00Z"
```

**Verification:** Snapshots return the value as it was at that point. Diff shows created, modified, and deleted keys.

### Rollback Operations

Undo changes at the key level or roll back the entire KV table.

```
# Rollback a single key — undo last 2 operations
curl -s -X POST "$BASE/api/v1/sqlite/kv/config.appName/rollback?db=myapp.db" \
  -H "Content-Type: application/json" \
  -d '{"steps": 2}'

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

**Verification:** After rollback, GET the key or list all keys to confirm the state matches the target point.

### Query History Management

Track, audit, and clean up SQL execution history.

```
# View query history for a database
curl -s "$BASE/api/v1/sqlite/history?db=myapp.db"

# Aggregated stats about query history
curl -s "$BASE/api/v1/sqlite/history/stats?db=myapp.db"

# Delete a specific history entry by index
curl -s -X DELETE "$BASE/api/v1/sqlite/history/42?db=myapp.db"

# Clear all history entries for a database
curl -s -X DELETE "$BASE/api/v1/sqlite/history?db=myapp.db"
```

**Verification:** History stats show execution counts and timing. After clear, history list returns empty.

### Maintenance Operations

Operations that cannot run inside the transactional `/db` endpoint.

```
# WAL checkpoint truncation — reclaim WAL file space
curl -s -X POST "$BASE/api/v1/sqlite/maintenance?db=myapp.db" \
  -H "Content-Type: application/json" \
  -d '{"operation": "wal_checkpoint_truncate"}'

# VACUUM INTO — compact database into a new file
curl -s -X POST "$BASE/api/v1/sqlite/maintenance?db=myapp.db" \
  -H "Content-Type: application/json" \
  -d '{"operation": "vacuum_into", "dest_path": "myapp_compacted.db"}'
```

**Verification:** After vacuum_into, create a database from the dest_path and query it to confirm data integrity.

### Error Recovery Pattern

A structured approach when data issues are suspected.

```
# 1. Confirm service is responsive
curl -s "$BASE/api/v1/sqlite/health"

# 2. Review recent query history for unexpected operations
curl -s "$BASE/api/v1/sqlite/history?db=myapp.db"

# 3. Check what changed using KV diff
curl -s "$BASE/api/v1/sqlite/kv/diff?db=myapp.db&from=2025-01-15T09:00:00Z&to=2025-01-15T12:00:00Z"

# 4. Rollback to a known-good timestamp
curl -s -X POST "$BASE/api/v1/sqlite/kv/rollback?db=myapp.db&to_timestamp=2025-01-15T09:00:00Z" \
  -H "Content-Type: application/json" \
  -d '{"confirmed": true}'

# 5. Run maintenance to clean up WAL files
curl -s -X POST "$BASE/api/v1/sqlite/maintenance?db=myapp.db" \
  -H "Content-Type: application/json" \
  -d '{"operation": "wal_checkpoint_truncate"}'
```

### Performance Considerations

- **Batch over individual:** Use `/kv/batch/set`, `/kv/batch/get`, `/kv/batch/delete` (max 100 items) to reduce round trips
- **HEAD for existence checks:** `HEAD /kv/{key}` is lighter than `GET` when you only need existence status
- **Periodic maintenance:** Schedule `wal_checkpoint_truncate` for write-heavy workloads to prevent WAL file growth
- **History cleanup:** Periodically clear history with `DELETE /history` to reduce storage overhead
- **KV over SQL for simple data:** KV operations include built-in versioning, snapshots, and rollback without writing SQL
- **Use /health/cache for monitoring:** Lighter than full `/health` for frequent dashboard polling

---

## Quick Reference

### Most Common Endpoints

| Operation | Method | Path | Key Params |
|-----------|--------|------|------------|
| Create DB | POST | `/api/v1/sqlite/db/create` | `?path=` |
| Run SQL | POST | `/api/v1/sqlite/db` | `?db=` |
| Query (URL-shareable) | GET | `/api/v1/sqlite/query` | `?db=`, `?sql=` (base64) |
| Set KV | PUT | `/api/v1/sqlite/kv/{key}` | `?db=` |
| Get KV | GET | `/api/v1/sqlite/kv/{key}` | `?db=` |
| Check KV exists | HEAD | `/api/v1/sqlite/kv/{key}` | `?db=` |
| Delete KV | DELETE | `/api/v1/sqlite/kv/{key}` | `?db=` |
| Batch Set | POST | `/api/v1/sqlite/kv/batch/set` | `?db=` |
| Batch Get | POST | `/api/v1/sqlite/kv/batch/get` | `?db=` |
| Increment | POST | `/api/v1/sqlite/kv/{key}/incr` | `?db=` |
| Push Array | POST | `/api/v1/sqlite/kv/{key}/push` | `?db=` |
| Health | GET | `/api/v1/sqlite/health` | none |
| Maintenance | POST | `/api/v1/sqlite/maintenance` | `?db=` |
| OpenAPI Spec | GET | `/api/v1/sqlite/openapi.yaml` | none |

### Essential Patterns

```
# Standard curl pattern for all requests
curl -s -X METHOD "$BASE/api/v1/sqlite/...?db=mydb" \
  -H "Content-Type: application/json" \
  -d '{"..."}'
```

### Key Constraints

- All SQL executes through `POST /api/v1/sqlite/db` with ACID transaction guarantees
- Batch operations accept a maximum of 100 items per request
- Timestamps use ISO 8601 format (e.g., `2025-01-15T10:00:00Z`)
- Query history is per-database and persists across container restarts
- KV snapshots and rollback use operation numbers or ISO 8601 timestamps
- The `/api/v1/sqlite/query` endpoint requires base64-encoded SQL
- Full OpenAPI specification: `GET /api/v1/sqlite/openapi.yaml`