crossbar/docs/plans/v1/01-store.md

# v1 task 01: the SQLite store

**Branch:** `v1` (create it from `master`: `git switch master && git switch -c v1`; `git status --short` must be empty first, otherwise stop)
**Commit subject:** `Add the SQLite store for leases and accounting`

## Goal

One SQLite file holds crossbar's durable state (the lease table) and its accounting log (one row
per proxied request, lease events, poller observations), with rollup queries that answer
per-route / per-model / per-host usage. This is `PLAN.md` §7a.

## Context

The driver is `modernc.org/sqlite` (pure Go, no cgo — the arm64 static build stays a plain
`go build`), registered under the `database/sql` name `"sqlite"`. Open with WAL and a busy
timeout: `sql.Open("sqlite", "file:"+path+"?_pragma=journal_mode(WAL)&_pragma=busy_timeout(5000)")`.
Volume is a few rows per request, so nothing here is performance-sensitive; correctness of the
sums is what matters. Times are stored as Unix milliseconds (`INTEGER`) and returned as
`time.Time` in UTC.

## Files

- Copy (never edit afterwards): `go.sum` (replaces; adds the driver's lines), `internal/store/store_test.go`
- Create: `internal/store/store.go` (and `schema.go` if you want the SQL separate; both under 400 lines)
- Modify: `go.mod` (add `modernc.org/sqlite v1.59.0` to `require`), `docs/implementer-log.md`

## Interfaces

Produces, in `internal/store`, package `store`:

```go
type State string
const ( Active State = "active"; Pinned State = "pinned" )

const (
    ReasonNew = "new"; ReasonUnhealthy = "unhealthy"; ReasonIdle = "idle"
    ReasonPin = "pin"; ReasonRelease = "release"; ReasonDrain = "drain"
)

type By string
const ( ByRoute By = "route"; ByModel By = "model"; ByHost By = "host" )

type Lease struct {
    Route, FP, Model, Host string
    State                  State
    Created, LastUsed      time.Time
}
type LeaseEvent struct {
    TS                             time.Time
    Route, Model, FromHost, ToHost string
    Reason                         string
}
type Request struct {
    Route, FP, Model, Host                       string
    Started                                      time.Time
    QueuedMs, TTFBMs, TotalMs                    int64
    Status                                       int
    Streamed                                     bool
    PromptTokens, CachedTokens, CompletionTokens int64
    Err                                          string
}
type HostHealth struct {
    TS      time.Time
    Host    string
    Healthy bool
    Loaded  []string   // stored as a JSON array in one TEXT column
}
type UsageRow struct {
    Key              string `json:"key"`
    Requests         int64  `json:"requests"`
    Errors           int64  `json:"errors"`           // rows with Status >= 400
    BusyMs           int64  `json:"busy_ms"`          // sum(TotalMs)
    QueuedMs         int64  `json:"queued_ms"`
    PromptTokens     int64  `json:"prompt_tokens"`
    CachedTokens     int64  `json:"cached_tokens"`
    CompletionTokens int64  `json:"completion_tokens"`
}
func (u UsageRow) CacheHitRatio() float64            // CachedTokens / PromptTokens; 0 when PromptTokens == 0

type Store struct { /* private: *sql.DB */ }
func Open(path string) (*Store, error)               // creates tables if missing; fails if the directory does not exist
func (s *Store) Close() error
func (s *Store) JournalMode() string                 // "wal"
func (s *Store) SaveLease(l Lease) error             // INSERT OR REPLACE on (route, fp, model)
func (s *Store) DeleteLease(route, fp, model string) error
func (s *Store) ListLeases() ([]Lease, error)
func (s *Store) RecordEvent(e LeaseEvent) error
func (s *Store) RecordRequest(r Request) error
func (s *Store) RecordHostHealth(h HostHealth) error
func (s *Store) Usage(since time.Time, by By) ([]UsageRow, error)          // rows with Started >= since, grouped by `by`; a zero `since` means all time
func (s *Store) Events(since time.Time, limit int) ([]LeaseEvent, error)   // oldest first
func (s *Store) Prune(now time.Time, retention time.Duration) (int64, error)
```

Rules the tests check:

1. **Schema** (create with `IF NOT EXISTS`, so `Open` twice on one file works):
   `leases(route, fp, model, host, state, created, last_used, PRIMARY KEY(route, fp, model))`,
   `lease_events(ts, route, model, from_host, to_host, reason)`,
   `requests(id INTEGER PRIMARY KEY, route, fp, model, host, started, queued_ms, ttfb_ms, total_ms, status, streamed, prompt_tokens, cached_tokens, completion_tokens, err)`,
   `host_health(ts, host, healthy, loaded_models)`,
   `requests_daily(day, route, model, host, requests, errors, busy_ms, queued_ms, prompt_tokens, cached_tokens, completion_tokens, PRIMARY KEY(day, route, model, host))`.
2. **`Usage`** sums `requests` rows with `started >= since` **plus** `requests_daily` rows whose
   `day >= since` (day = UTC midnight of `started`), grouped by the `by` column. `Errors` counts
   `status >= 400`. A zero `since` (`time.Time{}`) means everything. Result order: by `Key`.
3. **`Prune(now, retention)`** moves every `requests` row with `started < now - retention` into
   `requests_daily` (adding into the existing day row if there is one), deletes them, and returns
   the number deleted. In one transaction.
4. Nothing here panics; every `sql` error is returned wrapped (`fmt.Errorf("store: …: %w", err)`).
5. `Loaded` in `HostHealth` is written as a JSON array; `nil` is written as `[]`.

## Steps

- [ ] **1. Branch and copy.**

```sh
git switch master && git switch -c v1
cp docs/plans/v1/_files/go.sum go.sum
mkdir -p internal/store && cp docs/plans/v1/_files/internal/store/store_test.go internal/store/
```

- [ ] **2. Add the dependency.** In `go.mod`, the `require` becomes a block with both modules:

```
require (
	github.com/BurntSushi/toml v1.6.0
	modernc.org/sqlite v1.59.0
)
```

Then `go mod download modernc.org/sqlite` (network, once) and `go mod verify`. Expected:
`all modules verified`. If `go mod tidy` wants to change `go.sum` or add `// indirect` lines to
`go.mod`, let it, and stage the result; `go.sum` must end up a superset of the given file.

- [ ] **3. See the test fail.** `go test ./internal/store/`. Expected: it does not compile.
- [ ] **4. Write `internal/store/store.go`.** `gofmt -w internal/store/`.
- [ ] **5. See the test pass.** `go test -race -count=1 ./internal/store/`. Expected: `ok`. The
  first compile of the driver takes a minute or two.
- [ ] **6. Run the gate.** `make gate`. Expected last line: `gate: ok`.
- [ ] **7. Log and commit.** Row `v1/01-store`.

```sh
git add go.mod go.sum internal/store docs/implementer-log.md
git commit
```

## Done when

- `go test -race -count=1 ./internal/store/` is `ok`; `make gate` prints `gate: ok`.
- `cmp internal/store/store_test.go docs/plans/v1/_files/internal/store/store_test.go` prints nothing.

## Stop and report if

- The module cannot be downloaded, or `go vet` rejects the driver on this Go version.