sqlite/CLAUDE.md

# sqlite

Pure-Go SQLite client with launcher lifecycle, health check, unit-of-work, and structured error mapping.

## Purpose

Provides a `database/sql`-backed SQLite client that integrates with the launcher and health
modules. Designed for single-process, embedded database use cases where CGO-free builds and
cross-compilation matter. Serialises writes through a mutex to prevent `SQLITE_BUSY` errors.

## Tier & Dependencies

**Tier 3 (infrastructure)** — depends on:
- `code.nochebuena.dev/go/health` (Tier 2)
- `code.nochebuena.dev/go/launcher` (Tier 2)
- `code.nochebuena.dev/go/logz` (Tier 1)
- `code.nochebuena.dev/go/xerrors` (Tier 0)
- `modernc.org/sqlite` (pure-Go SQLite driver, no CGO)

## Key Design Decisions

- **Pure-Go driver**: `modernc.org/sqlite` is used instead of `mattn/go-sqlite3`. No CGO,
  no system library required. Cross-compilation works with `CGO_ENABLED=0`. See ADR-001.
- **Write mutex**: `writeMu sync.Mutex` in `sqliteComponent` is acquired by `UnitOfWork.Do`
  before every write transaction. Eliminates `SQLITE_BUSY` under concurrent goroutines. See ADR-002.
- **Foreign key enforcement**: Enabled in both the default DSN Pragmas (`_fk=true`) and via
  an explicit `PRAGMA foreign_keys = ON` in `OnInit`. Startup fails if the PRAGMA cannot be
  set. See ADR-003.
- **Honest Tx contract**: `Tx.Commit()` and `Tx.Rollback()` accept no `ctx` argument,
  matching the `database/sql` limitation. The interface documents this explicitly.
- **Context injection for UoW**: `GetExecutor(ctx)` checks for a `ctxTxKey{}` value in the
  context. When inside `UnitOfWork.Do`, the context carries the active transaction, so
  repositories call `client.GetExecutor(ctx)` and automatically participate in the transaction
  without knowing about it.
- **WAL + busy timeout defaults**: `?_journal=WAL&_timeout=5000&_fk=true` are the default
  Pragmas. Callers can override via `Config.Pragmas`, but the `OnInit` FK PRAGMA always runs.
- **Health check**: `Priority()` returns `health.LevelCritical`. A failed ping prevents the
  service from being marked healthy.

## Patterns

**Lifecycle registration:**
```go
db := sqlite.New(logger, cfg)
lc.Append(db) // registers OnInit / OnStart / OnStop
```

**Repository pattern using GetExecutor:**
```go
func (r *repo) FindByID(ctx context.Context, id int) (*Thing, error) {
    exec := r.db.GetExecutor(ctx) // returns Tx if inside UoW, pool otherwise
    row := exec.QueryRowContext(ctx, "SELECT ... WHERE id = ?", id)
    ...
}
```

**Unit of Work:**
```go
uow := sqlite.NewUnitOfWork(logger, db)
err := uow.Do(ctx, func(ctx context.Context) error {
    // all calls to db.GetExecutor(ctx) return the same Tx
    return repo.Save(ctx, thing)
})
```

**Error handling:**
```go
if err := db.HandleError(err); err != nil {
    // err is a *xerrors.Err with code ErrNotFound, ErrAlreadyExists, ErrInvalidInput, or ErrInternal
}
```

## What to Avoid

- Do not set `MaxOpenConns > 1` without understanding the implications. SQLite allows only one
  writer at a time; a larger pool increases `SQLITE_BUSY` risk for callers that bypass `UnitOfWork`.
- Do not use `Begin`/`Commit`/`Rollback` directly for concurrent writes. Use `UnitOfWork.Do`
  to get write-mutex protection.
- Do not rely on `Tx.Commit(ctx)` — the `Tx` interface intentionally has no ctx on Commit
  and Rollback, matching `database/sql` behaviour.
- Do not wrap `HandleError` output with additional error types that would obscure the
  `*xerrors.Err` for callers using `errors.As`.

## Testing Notes

- All tests use `Config{Path: ":memory:"}`. No filesystem or teardown needed.
- `compliance_test.go` (package `sqlite_test`) asserts that `New(...)` satisfies the
  `Component` interface at compile time.
- `TestUnitOfWork_WriteMutex` spawns 5 concurrent goroutines to verify serialisation.
- `TestHandleError_ForeignKey` and `TestHandleError_UniqueConstraint` exercise real SQLite
  error code mapping; they require `_fk=true` in Pragmas (set in `newMemDB`).