---
paths:
  - "src/db/**"
  - "src/history/**"
  - "migrations/**"
---
# Database Rules

Dual-backend persistence: PostgreSQL + libSQL/Turso. **All new persistence features must support both backends.**

See `src/db/CLAUDE.md` for full schema, dialect differences, and libSQL limitations.

## Adding a New Operation

1. Decide which sub-trait it belongs to (`ConversationStore`, `JobStore`, `SandboxStore`, `RoutineStore`, `ToolFailureStore`, `SettingsStore`, `WorkspaceStore`) or create a new one
2. Add the async method signature to that sub-trait in `src/db/mod.rs`
3. Implement in `src/db/postgres.rs` (delegate to `Store`/`Repository`)
4. Implement in `src/db/libsql/<module>.rs` (use `self.connect().await?` per operation)
5. Add migration if needed:
   - PostgreSQL: new `migrations/VN__description.sql`
   - libSQL: add `CREATE TABLE IF NOT EXISTS` to `libsql_migrations.rs`
6. Test feature isolation:
   ```bash
   cargo check                                          # postgres (default)
   cargo check --no-default-features --features libsql  # libsql only
   cargo check --all-features                           # both
   ```

## SQL Dialect Translation Checklist

When writing SQL for both backends, translate these types:

| PostgreSQL | libSQL |
|-----------|--------|
| `UUID` | `TEXT` |
| `TIMESTAMPTZ` | `TEXT` (ISO-8601, write with `fmt_ts()`, read with `get_ts()`) |
| `JSONB` | `TEXT` (JSON string) |
| `BOOLEAN` | `INTEGER` (0/1 -- use `get_i64(row, idx) != 0` to read) |
| `NUMERIC` | `TEXT` (preserves `rust_decimal` precision) |
| `TEXT[]` | `TEXT` (JSON-encoded array) |
| `VECTOR` | `BLOB` (flexible dimensions; vector index dropped, brute-force search fallback) |
| `jsonb_set(col, '{key}', val)` | `json_patch(col, '{"key": val}')` -- replaces top-level keys entirely, cannot do partial nested updates |
| `DEFAULT NOW()` | `DEFAULT (datetime('now'))` |
| `tsvector` + `ts_rank_cd` | FTS5 virtual table + sync triggers |

## Schema Translation Beyond DDL

Don't just translate `CREATE TABLE`. Also check:
- **Indexes** -- diff `CREATE INDEX` statements between backends
- **Seed data** -- check for `INSERT INTO` in migrations (e.g., `leak_detection_patterns`)
- **Triggers** -- PostgreSQL functions vs SQLite triggers (no stored procs in SQLite)

## Transaction Safety

Multi-step operations (INSERT+INSERT, UPDATE+DELETE, read-modify-write) MUST be wrapped in a transaction. Ask: "If this crashes between step N and N+1, is the database consistent?" If not, wrap in a transaction. Applies to both backends.

## libSQL Connection Model

`LibSqlBackend::connect()` creates a fresh connection per operation with `PRAGMA busy_timeout = 5000`. This is intentional -- no pool exists. Never hold connections open across `await` points. Satellite stores (`LibSqlSecretsStore`, `LibSqlWasmToolStore`) receive `Arc<LibSqlDatabase>` via `shared_db()` and call `.connect()` themselves -- never pass a live `Connection`.

## Fix the Pattern, Not the Instance

When fixing a bug in one backend's SQL, always grep for the same pattern in the other. A fix to `postgres.rs` that doesn't also fix `libsql/jobs.rs` is half a fix. Same applies to satellite stores.