# `carlos db` Unit 1 Implementation Plan — the door, the client, `pull` and `query`

> **For agentic workers:** REQUIRED SUB-SKILL: Use superpowers:subagent-driven-development (recommended) or superpowers:executing-plans to implement this plan task-by-task. Steps use checkbox (`- [ ]`) syntax for tracking.

**Goal:** Let an account member pull a restored copy of their app's replicated SQLite to their own machine and query it read-only, with no AWS credentials and no box access.

**Architecture:** The console gains an in-region, read-only S3 client on the data bucket and serves LTX files **by coordinate** (level + TXID range), never by key. The CLI implements litestream's `ReplicaClient` over that door with the three mutating methods stubbed to error, restores in-process into a 0700 temp directory, and opens the result read-only. The console never executes tenant SQL and never holds a restored database.

**Tech Stack:** Go; litestream v0.5.14 as a library (`lease.Restore`, `lss3.ReplicaClient`); `modernc.org/sqlite` (pure Go, no cgo); the existing `internal/console` CLI-API gate.

**Spec:** `docs/superpowers/specs/2026-09-02-carlos-db-design.md` — read it alongside this plan. §6 is the door, §12 is why the safety claim holds, §10 is the failure matrix every task draws its tests from.

## Global Constraints

- **Option A is decided** (spec §6): the console reads the region data bucket directly. Do **not** add an edges-file bucket lookup, and do **not** mirror through the deployment bucket.
- **In-region, non-pooled, activator-replicated instances only.** Fleet-homed (`region == "fleet/<name>"`), cross-region, and `Pool != ""` are residency refusals made **before any S3 call**.
- **`PrincipalUser` only.** Every service principal — legacy app token and every named service credential including `admin` — gets `http.NotFound`, never 403 (spec §6, "a 403 would be an oracle").
- **The client must not implement `ReplicaClientV3`.** `Restore` type-asserts for it (`replica.go:612`) and would take the v0.3 path. A test asserts the negative.
- **Never set `RestoreOptions.Follow`.** It writes a `-txid` sidecar and keeps polling.
- **Run `lease.CheckDB` after every restore** rather than relying on `opt.IntegrityCheck`.
- **The restore retry wraps the whole `Restore`**, never an individual read: `ResumableReader` treats `ErrNotExist` as fatal (`resumable_reader.go:72-75`), so a vanished planned file needs a fresh plan.
- **`list` answers levels 0–9 inclusive**, returning an **empty list** (never 404) for levels the activator never writes. `SnapshotLevel = 9`.
- **Listings ascend by `min_txid`.** `size`, `min_txid`, `max_txid` are mandatory on every entry.
- **Store errors are logged, not returned** (`apischedule.go:145-147`) — they carry bucket names and request ids.
- **No user-facing string ships without the `copy-review` gate.** Task 7 is where copy is finalised; earlier tasks may use placeholder wording in code but must not be merged to `main` with it.
- **Every command needs a `docs/site/cli.md` heading** or `internal/docsite`'s `TestCLICommandsAreDocumented` fails.
- Gate before commit: `go build ./...`, `go vet ./...`, `gofmt -l .` clean, `go test ./... -count=1`.

---

## File Structure

**Create:**
- `internal/dbdoor/coords.go` — the wire types shared by console and CLI (`LTXFile`, the list/get path builders). Leaf package, no console or CLI imports, so both sides agree on one shape.
- `internal/dbdoor/coords_test.go`
- `internal/dbdoor/client.go` — the CLI's read-only `litestream.ReplicaClient` over HTTP.
- `internal/dbdoor/client_test.go`
- `internal/dbdoor/restore.go` — restore into a 0700 temp dir, retry, `CheckDB`.
- `internal/dbdoor/restore_test.go`
- `internal/console/apidb.go` — resolution, refusals, and the two handlers.
- `internal/console/apidb_test.go`
- `db.go` — the `carlos db` CLI verb (`pull`, `query`).
- `db_test.go`

**Modify:**
- `internal/lease/endpoint.go` — export a read-only replica-client constructor (currently `newReplicaClient`, unexported).
- `internal/console/authcli.go` — two route registrations.
- `cmd/console/main.go` — read `CARLOS_HIBERNATE_BUCKET`/`_REGION`/`_PREFIX` and `CARLOS_REGION`.
- `internal/console/server.go` — carry the hibernate config and the S3 client on `Config`/`Server`.
- `apimode.go` — session methods for the two door calls.
- `main.go` — `case "db"` and the usage entry.
- `docs/site/cli.md` — the `carlos db` heading.

Why `internal/dbdoor` rather than putting the client in the root package: the console (server) and the CLI (client) must agree byte-for-byte on the wire shape, and a leaf package is the only way both can import it without `internal/console` and `main` importing each other. This mirrors `internal/dbverify`'s own reason for existing.

---

### Task 1: Export a read-only replica client from `internal/lease`

The console needs `lss3.ReplicaClient` construction — endpoint override, path style, region — that `internal/lease` already gets right. Duplicating it is how the two drift.

**Files:**
- Modify: `internal/lease/endpoint.go:120-131`
- Test: `internal/lease/endpoint_test.go`

**Interfaces:**
- Produces: `func ReadReplicaClient(bucket, prefix, region string) *lss3.ReplicaClient`

- [ ] **Step 1: Write the failing test**

```go
// internal/lease/endpoint_test.go
func TestReadReplicaClientCarriesBucketPrefixRegion(t *testing.T) {
	c := ReadReplicaClient("data-bucket", "instances/a.example.com", "eu-west-1")
	if c.Bucket != "data-bucket" {
		t.Errorf("Bucket = %q, want data-bucket", c.Bucket)
	}
	if c.Path != "instances/a.example.com" {
		t.Errorf("Path = %q, want instances/a.example.com", c.Path)
	}
	if c.Region != "eu-west-1" {
		t.Errorf("Region = %q, want eu-west-1", c.Region)
	}
}
```

- [ ] **Step 2: Run test to verify it fails**

Run: `go test ./internal/lease/ -run TestReadReplicaClient`
Expected: FAIL — `undefined: ReadReplicaClient`

- [ ] **Step 3: Write minimal implementation**

```go
// internal/lease/endpoint.go — beneath newReplicaClient

// ReadReplicaClient is newReplicaClient for READERS outside this package —
// the console's db door (spec 2026-09-02 §6). Identical construction on
// purpose: a second hand-rolled client is how the endpoint override and the
// path-style flag drift apart between the writer and the reader of the same
// bucket.
//
// It returns the same type the writer uses, so nothing here can enforce
// read-only by construction; that guarantee lives in internal/dbdoor's
// client, which has no write methods at all.
func ReadReplicaClient(bucket, prefix, region string) *lss3.ReplicaClient {
	return newReplicaClient(bucket, prefix, region)
}
```

- [ ] **Step 4: Run test to verify it passes**

Run: `go test ./internal/lease/ -run TestReadReplicaClient`
Expected: PASS

- [ ] **Step 5: Commit**

```bash
git add internal/lease/endpoint.go internal/lease/endpoint_test.go
git commit -m "lease: export ReadReplicaClient for the db door"
```

---

### Task 2: The wire shape

One package both sides import, so the door and the client cannot disagree about coordinates.

**Files:**
- Create: `internal/dbdoor/coords.go`
- Test: `internal/dbdoor/coords_test.go`

**Interfaces:**
- Produces:
  - `const MaxLevel = 9`
  - `type LTXFile struct { Level int; MinTXID, MaxTXID uint64; Size int64; CreatedAt time.Time }`
  - `type ListResponse struct { Files []LTXFile }`
  - `func ListPath(acct, app, host string, level int) string`
  - `func GetPath(acct, app, host string, level int, minTXID, maxTXID uint64) string`
  - `func RangeHeader(offset, size int64) string`

- [ ] **Step 1: Write the failing test**

```go
// internal/dbdoor/coords_test.go
package dbdoor

import "testing"

// Range semantics must match litestream's S3 client exactly
// (s3/replica_client.go:647-652), or a resumed read returns the wrong bytes.
func TestRangeHeaderMatchesTheS3Client(t *testing.T) {
	cases := []struct {
		name           string
		offset, size   int64
		want           string
	}{
		{"whole file from an offset", 0, 0, "bytes=0-"},
		{"open-ended from an offset", 512, 0, "bytes=512-"},
		{"sized read", 0, 100, "bytes=0-99"},
		{"sized read at an offset", 512, 100, "bytes=512-611"},
	}
	for _, tc := range cases {
		t.Run(tc.name, func(t *testing.T) {
			if got := RangeHeader(tc.offset, tc.size); got != tc.want {
				t.Errorf("RangeHeader(%d, %d) = %q, want %q", tc.offset, tc.size, got, tc.want)
			}
		})
	}
}

// Coordinates, never keys: the caller names a level and a TXID range, and
// the server decides what object that is. lock.json has no coordinate.
func TestGetPathIsCoordinateAddressed(t *testing.T) {
	got := GetPath("bab", "titogo", "a.bab.oncarlos.com", 3, 16, 32)
	for _, want := range []string{"bab", "titogo", "a.bab.oncarlos.com", "3", "16", "32"} {
		if !strings.Contains(got, want) {
			t.Fatalf("GetPath = %q, missing %q", got, want)
		}
	}
	if strings.Contains(got, ".ltx") || strings.Contains(got, "lock") {
		t.Fatalf("GetPath = %q leaks the key layout", got)
	}
}
```

- [ ] **Step 2: Run test to verify it fails**

Run: `go test ./internal/dbdoor/ -run 'TestRangeHeader|TestGetPath'`
Expected: FAIL — `undefined: RangeHeader`, `undefined: GetPath`

- [ ] **Step 3: Write minimal implementation**

```go
// internal/dbdoor/coords.go

// Package dbdoor is the wire shape of the `carlos db` replica door, shared
// by the console that serves it and the CLI that reads it (spec
// 2026-09-02 §6).
//
// It is a LEAF package for the reason internal/dbverify is one: the writer
// (internal/console) and the reader (the CLI's main package) must agree on
// one shape, and neither can import the other.
//
// Files are addressed by COORDINATE — level plus TXID range — never by key.
// That is not decoration: lock.json lives under the same prefix as the LTX
// files and is not a member's to fetch, and a coordinate has no way to name
// it. See spec §6 "The door".
package dbdoor

import (
	"fmt"
	"net/url"
	"time"
)

// MaxLevel is the highest level a list may name. litestream's
// CalcRestorePlan asks for SnapshotLevel (9) first, then 8 down to 0
// (replica.go:1452, 1476-1485). The activator writes only 0-3 and 9, so
// most of that range is legitimately EMPTY — and empty is an empty list,
// never a 404, or the restore stops at the first gap.
const MaxLevel = 9

// LTXFile is one replica file as the door reports it. Size is MANDATORY:
// Restore rejects Size < ltx.HeaderSize (replica.go:630) and
// ResumableReader uses it to detect a premature EOF
// (resumable_reader.go:96).
type LTXFile struct {
	Level     int       `json:"level"`
	MinTXID   uint64    `json:"min_txid"`
	MaxTXID   uint64    `json:"max_txid"`
	Size      int64     `json:"size"`
	CreatedAt time.Time `json:"created_at"`
}

// ListResponse is ordered by MinTXID ASCENDING — restoreLevelCursor assumes
// filename order, which S3 gives because the names are zero-padded hex.
type ListResponse struct {
	Files []LTXFile `json:"files"`
}

func base(acct, app, host string) string {
	return fmt.Sprintf("/api/cli/apps/%s/%s/db/%s",
		url.PathEscape(acct), url.PathEscape(app), url.PathEscape(host))
}

func ListPath(acct, app, host string, level int) string {
	return fmt.Sprintf("%s/ltx/%d", base(acct, app, host), level)
}

func GetPath(acct, app, host string, level int, minTXID, maxTXID uint64) string {
	return fmt.Sprintf("%s/ltx/%d/%d-%d", base(acct, app, host), level, minTXID, maxTXID)
}

// RangeHeader mirrors litestream's own S3 client byte-for-byte
// (s3/replica_client.go:647-652): open-ended when size is 0, inclusive
// end otherwise.
func RangeHeader(offset, size int64) string {
	if size == 0 {
		return fmt.Sprintf("bytes=%d-", offset)
	}
	return fmt.Sprintf("bytes=%d-%d", offset, offset+size-1)
}
```

Add `"strings"` to the test's imports.

- [ ] **Step 4: Run test to verify it passes**

Run: `go test ./internal/dbdoor/`
Expected: PASS

- [ ] **Step 5: Commit**

```bash
git add internal/dbdoor/coords.go internal/dbdoor/coords_test.go
git commit -m "dbdoor: the coordinate wire shape the door and client share"
```

---

### Task 3: Resolution and the residency refusals

The half of the door that makes **no S3 call**. Written and tested first because every refusal in the spec's matrix happens here, and none of them should ever reach a bucket.

**Files:**
- Create: `internal/console/apidb.go`
- Test: `internal/console/apidb_test.go`

**Interfaces:**
- Consumes: `provision.Record` (fields `Host`, `App`, `Account`, `Pool`, `Region`), `provision.ValidHostShape`, `activator.HibernatePrefix`.
- Produces: `func (s *Server) resolveReplica(rec provision.Record, host string) (prefix string, err error)` and `var ErrResidency, ErrPooled, ErrBadHost error`.

- [ ] **Step 1: Write the failing test**

```go
// internal/console/apidb_test.go
package console

import (
	"errors"
	"testing"

	"github.com/carlosframework/platform/internal/provision"
)

// Every one of these is refused BEFORE any S3 call. A door that reached the
// bucket and mapped AccessDenied would leak both latency and the fact that
// a bucket was tried.
func TestResolveReplicaRefusesBeforeTouchingS3(t *testing.T) {
	s := &Server{}
	s.cfg.Region = "eu-west-1"
	s.cfg.HibernatePrefix = "instances"

	cases := []struct {
		name string
		rec  provision.Record
		host string
		want error
	}{
		{
			name: "another region",
			rec:  provision.Record{Host: "a.bab.oncarlos.com", Region: "us-east-1"},
			host: "a.bab.oncarlos.com",
			want: ErrResidency,
		},
		{
			name: "fleet-homed",
			rec:  provision.Record{Host: "a.bab.oncarlos.com", Region: "fleet/acme"},
			host: "a.bab.oncarlos.com",
			want: ErrResidency,
		},
		{
			name: "pooled",
			rec:  provision.Record{Host: "a.bab.oncarlos.com", Region: "eu-west-1", Pool: "shared"},
			host: "a.bab.oncarlos.com",
			want: ErrPooled,
		},
		{
			name: "hostile host",
			rec:  provision.Record{Host: "../../etc", Region: "eu-west-1"},
			host: "../../etc",
			want: ErrBadHost,
		},
	}
	for _, tc := range cases {
		t.Run(tc.name, func(t *testing.T) {
			_, err := s.resolveReplica(tc.rec, tc.host)
			if !errors.Is(err, tc.want) {
				t.Fatalf("resolveReplica = %v, want %v", err, tc.want)
			}
		})
	}
}

// The happy path is an in-region, non-pooled instance, and the prefix is
// the activator's own layout — not a string this package invents.
func TestResolveReplicaBuildsTheActivatorPrefix(t *testing.T) {
	s := &Server{}
	s.cfg.Region = "eu-west-1"
	s.cfg.HibernatePrefix = "instances"
	got, err := s.resolveReplica(
		provision.Record{Host: "a.bab.oncarlos.com", Region: "eu-west-1"},
		"a.bab.oncarlos.com")
	if err != nil {
		t.Fatal(err)
	}
	if got != "instances/a.bab.oncarlos.com" {
		t.Fatalf("prefix = %q, want instances/a.bab.oncarlos.com", got)
	}
}

// A single-region deployment leaves both empty, and "" == "" must be a
// match rather than a refusal — otherwise the door never works anywhere
// that has not set CARLOS_REGION.
func TestResolveReplicaTreatsEmptyRegionsAsAMatch(t *testing.T) {
	s := &Server{}
	s.cfg.HibernatePrefix = "instances"
	if _, err := s.resolveReplica(
		provision.Record{Host: "a.bab.oncarlos.com"}, "a.bab.oncarlos.com"); err != nil {
		t.Fatalf("single-region deployment refused: %v", err)
	}
}
```

- [ ] **Step 2: Run test to verify it fails**

Run: `go test ./internal/console/ -run TestResolveReplica`
Expected: FAIL — `undefined: ErrResidency`, `s.resolveReplica undefined`, `s.cfg.Region undefined`

- [ ] **Step 3: Write minimal implementation**

Add to `internal/console/server.go`'s `Config` struct:

```go
	// The region data bucket the db door reads (spec 2026-09-02 §6,
	// Option A). Read-only, in-region, instances/* only. Empty disables
	// the door rather than failing it open.
	HibernateBucket string
	HibernateRegion string
	HibernatePrefix string
	// Region is this console's own placement, compared against an
	// instance record's before any S3 call. Empty in a single-region
	// deployment, where every record's is empty too.
	Region string
```

Then `internal/console/apidb.go`:

```go
// The `carlos db` replica door (spec 2026-09-02 §6). This file is the half
// that makes no S3 call: resolving an instance record to one replica
// prefix, and refusing everything that is not this console's to read.
//
// Every refusal here happens BEFORE the bucket is touched. That is
// deliberate — a door that tried and mapped AccessDenied would leak the
// attempt, the latency, and eventually a bucket name into a log a member
// can provoke.

package console

import (
	"errors"
	"fmt"

	"github.com/carlosframework/platform/internal/activator"
	"github.com/carlosframework/platform/internal/provision"
)

var (
	// ErrResidency: the data is in another region's bucket, or on a
	// customer's own fleet box. The instance-move design makes those reads
	// impossible at IAM by design, so this is the design working rather
	// than a fault to retry.
	ErrResidency = errors.New("db: the instance's data is outside this region")
	// ErrPooled: a pooled record's data lives on the pool box, the same
	// class movespass.go:150-153 refuses.
	ErrPooled = errors.New("db: pooled instances have no replica of their own")
	// ErrBadHost: path.Join cleans "../x" to "x" rather than refusing it,
	// so a host becomes a key segment only after ValidHostShape.
	ErrBadHost = errors.New("db: invalid host")
)

func (s *Server) resolveReplica(rec provision.Record, host string) (string, error) {
	if !provision.ValidHostShape(host) {
		return "", fmt.Errorf("%w: %q", ErrBadHost, host)
	}
	if rec.Pool != "" {
		return "", fmt.Errorf("%w: %s is in pool %q", ErrPooled, host, rec.Pool)
	}
	if rec.Region != s.cfg.Region {
		return "", fmt.Errorf("%w: %s is homed in %q, this console serves %q",
			ErrResidency, host, rec.Region, s.cfg.Region)
	}
	return activator.HibernatePrefix(s.cfg.HibernatePrefix, host), nil
}
```

Note: a `fleet/<name>` region is caught by the same `rec.Region != s.cfg.Region` compare, because a console's own region is never `fleet/…`. Keep the dedicated test case anyway — it documents the intent, and would catch a future change that special-cased the compare.

- [ ] **Step 4: Run test to verify it passes**

Run: `go test ./internal/console/ -run TestResolveReplica`
Expected: PASS

- [ ] **Step 5: Verify `ValidHostShape` is exported**

Run: `grep -n "func ValidHostShape" internal/provision/provision.go`
Expected: it exists and is exported. If it is not, export it in this task and note the rename in the commit.

- [ ] **Step 6: Commit**

```bash
git add internal/console/apidb.go internal/console/apidb_test.go internal/console/server.go
git commit -m "console: resolve an instance to one replica prefix, refusing before S3"
```

---

### Task 4: The door handlers

**Files:**
- Modify: `internal/console/apidb.go`
- Modify: `internal/console/authcli.go`
- Test: `internal/console/apidb_test.go`

**Interfaces:**
- Consumes: Task 1's `lease.ReadReplicaClient`, Task 2's `dbdoor.LTXFile`/`ListResponse`/`MaxLevel`, Task 3's `resolveReplica`.
- Produces: `POST`-free routes `GET /api/cli/apps/{account}/{app}/db/{host}/ltx/{level}` and `GET …/ltx/{level}/{min}-{max}`.

- [ ] **Step 1: Write the failing test**

```go
// internal/console/apidb_test.go — append

// A level the activator never writes is EMPTY, not missing. A 404 here
// stops CalcRestorePlan at the first gap and the restore fails.
func TestAPIDBListAnswersEmptyForUnwrittenLevels(t *testing.T) {
	fx, token := dbFixture(t)
	for level := 0; level <= dbdoor.MaxLevel; level++ {
		status, out := get(t, fx.ts.URL+dbdoor.ListPath("bab", "titogo", dbTestHost, level), token)
		if status != http.StatusOK {
			t.Fatalf("level %d = %d %s, want 200 with an empty list", level, status, out)
		}
	}
}

// Coordinate addressing exists to make lock.json unreachable. This is the
// test that keeps the door from decaying into an object proxy.
func TestAPIDBServesNoKeyOutsideItsCoordinates(t *testing.T) {
	fx, token := dbFixture(t)
	for _, bad := range []string{
		"/api/cli/apps/bab/titogo/db/" + dbTestHost + "/ltx/0/../../lock.json",
		"/api/cli/apps/bab/titogo/db/" + dbTestHost + "/lock.json",
		"/api/cli/apps/bab/titogo/db/" + dbTestHost + "/ltx/10",
	} {
		status, _ := get(t, fx.ts.URL+bad, token)
		if status == http.StatusOK {
			t.Fatalf("%s = 200, want a refusal", bad)
		}
	}
}

// Not a 403: a 403 tells any token holder that a db door exists for this
// app. Service principals get the same answer as a stranger.
func TestAPIDBRefusesServicePrincipalsWithA404(t *testing.T) {
	fx, svcToken := dbServiceFixture(t)
	status, _ := get(t, fx.ts.URL+dbdoor.ListPath("bab", "titogo", dbTestHost, 0), svcToken)
	if status != http.StatusNotFound {
		t.Fatalf("service principal = %d, want 404", status)
	}
}
```

Write `dbFixture`, `dbServiceFixture`, `dbTestHost` and `get` alongside, modelled on `scheduleFixture` and `postBody` in `apischedule_test.go`. The fixture must seed an `apps/<sqid>/<app>/instances/<host>.json` record with an empty region, and point `cfg.HibernateBucket` at a `release.NewDirStore`-backed fake or leave it empty so the empty-list path is exercised without S3.

- [ ] **Step 2: Run test to verify it fails**

Run: `go test ./internal/console/ -run TestAPIDB`
Expected: FAIL — routes are 404 for every case, including the empty-list one that should be 200.

- [ ] **Step 3: Write minimal implementation**

Append to `internal/console/apidb.go`:

```go
// handleAPIDBList answers one level's listing. An out-of-range level is a
// 404 (it is not a coordinate); a level with nothing in it is 200 and an
// empty list (it is a coordinate that happens to be empty).
func (s *Server) handleAPIDBList(w http.ResponseWriter, r *http.Request) {
	prefix, acct, app, host, ok := s.dbGate(w, r)
	if !ok {
		return
	}
	level, err := strconv.Atoi(r.PathValue("level"))
	if err != nil || level < 0 || level > dbdoor.MaxLevel {
		http.NotFound(w, r)
		return
	}
	client := lease.ReadReplicaClient(s.cfg.HibernateBucket, prefix, s.cfg.HibernateRegion)
	itr, err := client.LTXFiles(r.Context(), level, 0, false)
	if err != nil {
		log.Printf("console: api db list %s/%s %s level %d: %v", acct, app, host, level, err)
		jsonError(w, http.StatusBadGateway, "could not read the replica; try again")
		return
	}
	defer itr.Close()
	out := dbdoor.ListResponse{Files: []dbdoor.LTXFile{}}
	for itr.Next() {
		f := itr.Item()
		out.Files = append(out.Files, dbdoor.LTXFile{
			Level: level, MinTXID: uint64(f.MinTXID), MaxTXID: uint64(f.MaxTXID),
			Size: f.Size, CreatedAt: f.CreatedAt,
		})
	}
	sort.Slice(out.Files, func(i, j int) bool { return out.Files[i].MinTXID < out.Files[j].MinTXID })
	writeJSON(w, http.StatusOK, out)
}
```

and the `get` handler, which opens by coordinate and streams (F8: never a presigned redirect), honouring an inbound `Range` via `dbdoor.RangeHeader`'s inverse. `dbGate` wraps `apiScheduleApp(w, r, false, …)`, reads the instance record, calls `resolveReplica`, maps `ErrResidency`/`ErrPooled` to a 409 with the residency sentence and `ErrBadHost` to `http.NotFound`, and returns a 503 when `HibernateBucket` is empty.

Register in `authcli.go` beside the schedule routes, but with `svcrole.Deny` — or, if no such helper exists, a `PrincipalUser`-only check inside `dbGate`; the test in Step 1 is the arbiter.

- [ ] **Step 4: Run test to verify it passes**

Run: `go test ./internal/console/ -run TestAPIDB`
Expected: PASS

- [ ] **Step 5: Commit**

```bash
git add internal/console/apidb.go internal/console/apidb_test.go internal/console/authcli.go
git commit -m "console: serve replica LTX files by coordinate, members only"
```

---

### Task 5: The read-only replica client

The spec's central safety claim lives here (§6, §12). Two tests, not one: the mutating methods must **error**, and they must **never be called** during a real restore.

**Files:**
- Create: `internal/dbdoor/client.go`
- Test: `internal/dbdoor/client_test.go`

**Interfaces:**
- Produces: `func NewClient(base, acct, app, host string, do func(*http.Request) (*http.Response, error)) *Client`, satisfying `litestream.ReplicaClient`.

- [ ] **Step 1: Write the failing test**

```go
// internal/dbdoor/client_test.go
package dbdoor

import (
	"context"
	"testing"

	"github.com/benbjohnson/litestream"
)

// The claim the whole design rests on: this client cannot corrupt a
// replica, because it has no code that writes to one.
func TestClientMutatingMethodsAlwaysError(t *testing.T) {
	c := NewClient("http://console.invalid", "bab", "titogo", "a.example.com", nil)
	ctx := context.Background()
	if _, err := c.WriteLTXFile(ctx, 0, 1, 2, nil); err == nil {
		t.Error("WriteLTXFile returned nil — this client must never write")
	}
	if err := c.DeleteLTXFiles(ctx, nil); err == nil {
		t.Error("DeleteLTXFiles returned nil — this client must never delete")
	}
	if err := c.DeleteAll(ctx); err == nil {
		t.Error("DeleteAll returned nil — this client must never delete")
	}
}

// Restore type-asserts for ReplicaClientV3 (replica.go:612) and would take
// the v0.3 path if this satisfied it. It must not.
func TestClientIsNotAV3ReplicaClient(t *testing.T) {
	var c any = NewClient("http://console.invalid", "bab", "titogo", "a.example.com", nil)
	if _, ok := c.(litestream.ReplicaClientV3); ok {
		t.Fatal("client satisfies ReplicaClientV3 — Restore would take the v0.3 path")
	}
}

var _ litestream.ReplicaClient = (*Client)(nil)
```

- [ ] **Step 2: Run test to verify it fails**

Run: `go test ./internal/dbdoor/ -run TestClient`
Expected: FAIL — `undefined: NewClient`

- [ ] **Step 3: Write minimal implementation**

`Client` holds the base URL, the identifiers, and a `do` func (defaulting to `http.DefaultClient.Do`, injected in tests). Implement:

- `Type() string` → `"carlos-db"`
- `Init(ctx) error` → `nil` (idempotent no-op; the HTTP client needs no setup)
- `SetLogger(*slog.Logger)` → store it
- `LTXFiles(ctx, level, seek, useMetadata)` → GET `ListPath`, decode `ListResponse`, filter `>= seek` **locally** (seek is not on the wire), return an `ltx.FileIterator`
- `OpenLTXFile(ctx, level, min, max, offset, size)` → GET `GetPath` with `Range: RangeHeader(offset, size)`; a 404 maps to `os.ErrNotExist`
- `WriteLTXFile`, `DeleteLTXFiles`, `DeleteAll` → `return errReadOnly` unconditionally

```go
// errReadOnly is not a guard that can be switched off: these three methods
// have no other body. The two-replicators hazard internal/caps and
// internal/lease both warn about is unreachable from this client by
// construction, and a future edit that wanted to reach it would have to add
// the capability first.
var errReadOnly = errors.New("dbdoor: this client is read-only — it restores replicas and never writes them")
```

- [ ] **Step 4: Run test to verify it passes**

Run: `go test ./internal/dbdoor/ -run TestClient`
Expected: PASS

- [ ] **Step 5: Add the call-counting test — the other half**

```go
// The methods erroring is not enough: a restore that CALLED one would be
// relying on an error path. This asserts a full restore never reaches them,
// which is what catches a litestream upgrade changing the restore path.
func TestRestoreNeverCallsAMutatingMethod(t *testing.T) {
	// Serve a real replica from a DirStore-backed fake console (build one
	// by replicating a small SQLite through lease.ReplicateWithLease into a
	// temp dir, then serving its files over httptest).
	// Wrap Client in a counter that increments per method name.
	// Restore through it, then assert:
	if counts["WriteLTXFile"]+counts["DeleteLTXFiles"]+counts["DeleteAll"] != 0 {
		t.Fatalf("restore called a mutating method: %v", counts)
	}
}
```

- [ ] **Step 6: Run it, then commit**

```bash
go test ./internal/dbdoor/
git add internal/dbdoor/client.go internal/dbdoor/client_test.go
git commit -m "dbdoor: a replica client with no write methods"
```

---

### Task 6: Restore into a private directory

**Files:**
- Create: `internal/dbdoor/restore.go`
- Test: `internal/dbdoor/restore_test.go`

**Interfaces:**
- Produces: `func RestoreTo(ctx context.Context, c *Client, dir string) (path string, err error)`

- [ ] **Step 1: Write the failing test**

```go
// litestream creates <out>.tmp itself with os.Create, at the process umask,
// so a 0600 file cannot be pre-created for it. The containing directory is
// the only thing that can be private — and it must also confine the -wal
// and -shm a read-only open still needs.
func TestRestoreToUsesAPrivateDirectory(t *testing.T) {
	dir := t.TempDir()
	// ... restore a known replica ...
	fi, err := os.Stat(filepath.Dir(path))
	if err != nil {
		t.Fatal(err)
	}
	if perm := fi.Mode().Perm(); perm != 0o700 {
		t.Fatalf("restore directory is %04o, want 0700", perm)
	}
}

// A planned file can vanish under the restore when the writer's L0
// retention fires. ResumableReader treats ErrNotExist as FATAL and does not
// retry, so the retry has to wrap the whole Restore — a fresh plan is the
// only recovery.
func TestRestoreToRetriesTheWholeRestoreOnce(t *testing.T) {
	// A client that returns os.ErrNotExist for the first OpenLTXFile and
	// serves normally afterwards.
	// Assert: RestoreTo succeeds, and the client saw two CalcRestorePlan
	// listing passes rather than one.
}
```

- [ ] **Step 2: Run to verify it fails**

Run: `go test ./internal/dbdoor/ -run TestRestoreTo`
Expected: FAIL — `undefined: RestoreTo`

- [ ] **Step 3: Implement**

`RestoreTo` creates `os.MkdirTemp(dir, "carlos-db-")`, chmods it 0700, builds `litestream.NewReplicaWithClient(nil, c)`, sets `RestoreOptions{OutputPath: …}` — **never `Follow`** — calls `Restore`, retries the whole call once on `os.ErrNotExist`, then runs `lease.CheckDB` on the result and returns the path.

- [ ] **Step 4: Run to verify it passes, then commit**

```bash
go test ./internal/dbdoor/
git add internal/dbdoor/restore.go internal/dbdoor/restore_test.go
git commit -m "dbdoor: restore into a 0700 directory, retrying the whole plan once"
```

---

### Task 7: `carlos db pull` and `carlos db query`

**Files:**
- Create: `db.go`, `db_test.go`
- Modify: `apimode.go`, `main.go`, `docs/site/cli.md`

- [ ] **Step 1: Write the failing tests**

```go
// A copy is harmless to write to. Read-only exists so a caller cannot
// BELIEVE it changed production: an agent that sees "1 row affected"
// concludes it fixed the bug. The error is the truth.
func TestQueryRefusesAWriteWithSQLitesOwnError(t *testing.T) {
	// open a restored fixture through the same helper db.go uses
	err := runQuery(path, "UPDATE users SET name='x'")
	if err == nil || !strings.Contains(err.Error(), "attempt to write a readonly database") {
		t.Fatalf("write error = %v, want SQLite's readonly refusal", err)
	}
}

func TestQueryLeavesTheFileByteIdentical(t *testing.T) { /* sha256 before and after */ }

func TestPullRefusesAnExistingOutAndLands0600(t *testing.T) { /* both halves */ }

func TestPullCopiesAcrossFilesystems(t *testing.T) {
	// os.Rename fails across devices; the temp dir and --out routinely
	// differ. Assert a copy path, not a rename.
}
```

- [ ] **Step 2: Run to verify they fail, then implement**

`db.go` adds `runDB(args)` dispatching `pull` and `query`; `apimode.go` gains a session method returning a configured `*dbdoor.Client`; `main.go` gains `case "db": runDB(args)` and a usage entry.

- [ ] **Step 3: Add the `cli.md` heading**

Required — `internal/docsite`'s `TestCLICommandsAreDocumented` fails without it. The prose is where "this is a copy, not your live database" gets said to the person most likely to assume otherwise.

- [ ] **Step 4: Run the copy-review gate**

**Do not merge without this.** Extract every user-facing string added in Tasks 3–7 — the residency refusal, the pooled refusal, the handover line, the read-only refusal, the `cli.md` prose — and run the `copy-review` skill. Apply the user's edits verbatim. Requirements R1–R6 in spec §5 are what the copy has to satisfy; R2 (age as a number carrying its bound, never an adjective, never defaulting to `stream`) lands in Unit 2 but the handover line's "this is a copy" half is Unit 1's.

- [ ] **Step 5: Full gate, then commit**

```bash
go build ./... && go vet ./... && gofmt -l . && go test ./... -count=1
git add db.go db_test.go apimode.go main.go docs/site/cli.md
git commit -m "carlos db: pull and query a restored replica, read-only"
```

---

## Self-Review

**Spec coverage.** §6 configuration → Task 3 (`Config` fields) + Task 4. §6 resolution and refusals → Task 3. §6 door and wire specifics 1–5 → Tasks 2 and 4. §6 principal rule → Task 4. §6 read-only client → Task 5. §7 one-database → implicit: no `--db` flag exists anywhere in this plan. §12 conditions (i) no V3, (ii) no `Follow`, (iii) `CheckDB` → Task 5 Step 1 and Task 6 Step 3, and all three are in Global Constraints. §4 private directory and 0600 `--out` → Tasks 6 and 7. §4 read-only enforcement → Task 7.

**Deliberately not in this plan** (Unit 2 and Unit 3 in spec §13): `ls` and every freshness field — `lease_held`, `sync_class` with its mandatory `unknown`, LTX-header age, the `dbverify` verdict, and the `--json` field set — plus `shell`. Wire specific 6 (`lease_held`) is Unit 2's, because nothing in `pull`/`query` reads it. Two follow-on plans, written once Unit 1 lands and its shapes are real rather than predicted.

**Placeholders.** Tasks 4, 5 Step 5, 6 and 7 describe some test bodies rather than spelling them out — specifically the ones that need a real replica fixture. That fixture does not exist yet and its shape depends on Task 4's fixture helpers. **Before executing Task 4, write the fixture helper first and fill those bodies in.** Flagging rather than pretending: a test body invented here against a fixture that does not exist would be worse than a named gap.

**Type consistency.** `dbdoor.LTXFile` fields are used identically in Tasks 2, 4 and 5. `lease.ReadReplicaClient(bucket, prefix, region)` is defined in Task 1 and called in Task 4 with that order. `resolveReplica(rec, host) (string, error)` is defined in Task 3 and consumed by `dbGate` in Task 4. `NewClient(base, acct, app, host, do)` is defined in Task 5 and consumed in Tasks 6 and 7.