carlos / platform Public

branch:

Clone

git clone https://amadan.net/carlos/platform

Plain git — no account needed to clone.

Download

Download this file

1	# `carlos db` Unit 1 Implementation Plan — the door, the client, `pull` and `query`
2
3	> 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.
4
5	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.
6
7	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.
8
9	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.
10
11	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.
12
13	## Global Constraints
14
15	- 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.
16	- 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.
17	- `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").
18	- 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.
19	- Never set `RestoreOptions.Follow`. It writes a `-txid` sidecar and keeps polling.
20	- Run `lease.CheckDB` after every restore rather than relying on `opt.IntegrityCheck`.
21	- 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.
22	- `list` answers levels 0–9 inclusive, returning an empty list (never 404) for levels the activator never writes. `SnapshotLevel = 9`.
23	- Listings ascend by `min_txid`. `size`, `min_txid`, `max_txid` are mandatory on every entry.
24	- Store errors are logged, not returned (`apischedule.go:145-147`) — they carry bucket names and request ids.
25	- 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.
26	- Every command needs a `docs/site/cli.md` heading or `internal/docsite`'s `TestCLICommandsAreDocumented` fails.
27	- Gate before commit: `go build ./...`, `go vet ./...`, `gofmt -l .` clean, `go test ./... -count=1`.
28
29	---
30
31	## File Structure
32
33	Create:
34	- `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.
35	- `internal/dbdoor/coords_test.go`
36	- `internal/dbdoor/client.go` — the CLI's read-only `litestream.ReplicaClient` over HTTP.
37	- `internal/dbdoor/client_test.go`
38	- `internal/dbdoor/restore.go` — restore into a 0700 temp dir, retry, `CheckDB`.
39	- `internal/dbdoor/restore_test.go`
40	- `internal/console/apidb.go` — resolution, refusals, and the two handlers.
41	- `internal/console/apidb_test.go`
42	- `db.go` — the `carlos db` CLI verb (`pull`, `query`).
43	- `db_test.go`
44
45	Modify:
46	- `internal/lease/endpoint.go` — export a read-only replica-client constructor (currently `newReplicaClient`, unexported).
47	- `internal/console/authcli.go` — two route registrations.
48	- `cmd/console/main.go` — read `CARLOS_HIBERNATE_BUCKET`/`_REGION`/`_PREFIX` and `CARLOS_REGION`.
49	- `internal/console/server.go` — carry the hibernate config and the S3 client on `Config`/`Server`.
50	- `apimode.go` — session methods for the two door calls.
51	- `main.go` — `case "db"` and the usage entry.
52	- `docs/site/cli.md` — the `carlos db` heading.
53
54	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.
55
56	---
57
58	### Task 1: Export a read-only replica client from `internal/lease`
59
60	The console needs `lss3.ReplicaClient` construction — endpoint override, path style, region — that `internal/lease` already gets right. Duplicating it is how the two drift.
61
62	Files:
63	- Modify: `internal/lease/endpoint.go:120-131`
64	- Test: `internal/lease/endpoint_test.go`
65
66	Interfaces:
67	- Produces: `func ReadReplicaClient(bucket, prefix, region string) *lss3.ReplicaClient`
68
69	- [ ] Step 1: Write the failing test
70
71	```go
72	// internal/lease/endpoint_test.go
73	func TestReadReplicaClientCarriesBucketPrefixRegion(t *testing.T) {
74	c := ReadReplicaClient("data-bucket", "instances/a.example.com", "eu-west-1")
75	if c.Bucket != "data-bucket" {
76	t.Errorf("Bucket = %q, want data-bucket", c.Bucket)
77	}
78	if c.Path != "instances/a.example.com" {
79	t.Errorf("Path = %q, want instances/a.example.com", c.Path)
80	}
81	if c.Region != "eu-west-1" {
82	t.Errorf("Region = %q, want eu-west-1", c.Region)
83	}
84	}
85	```
86
87	- [ ] Step 2: Run test to verify it fails
88
89	Run: `go test ./internal/lease/ -run TestReadReplicaClient`
90	Expected: FAIL — `undefined: ReadReplicaClient`
91
92	- [ ] Step 3: Write minimal implementation
93
94	```go
95	// internal/lease/endpoint.go — beneath newReplicaClient
96
97	// ReadReplicaClient is newReplicaClient for READERS outside this package —
98	// the console's db door (spec 2026-09-02 §6). Identical construction on
99	// purpose: a second hand-rolled client is how the endpoint override and the
100	// path-style flag drift apart between the writer and the reader of the same
101	// bucket.
102	//
103	// It returns the same type the writer uses, so nothing here can enforce
104	// read-only by construction; that guarantee lives in internal/dbdoor's
105	// client, which has no write methods at all.
106	func ReadReplicaClient(bucket, prefix, region string) *lss3.ReplicaClient {
107	return newReplicaClient(bucket, prefix, region)
108	}
109	```
110
111	- [ ] Step 4: Run test to verify it passes
112
113	Run: `go test ./internal/lease/ -run TestReadReplicaClient`
114	Expected: PASS
115
116	- [ ] Step 5: Commit
117
118	```bash
119	git add internal/lease/endpoint.go internal/lease/endpoint_test.go
120	git commit -m "lease: export ReadReplicaClient for the db door"
121	```
122
123	---
124
125	### Task 2: The wire shape
126
127	One package both sides import, so the door and the client cannot disagree about coordinates.
128
129	Files:
130	- Create: `internal/dbdoor/coords.go`
131	- Test: `internal/dbdoor/coords_test.go`
132
133	Interfaces:
134	- Produces:
135	- `const MaxLevel = 9`
136	- `type LTXFile struct { Level int; MinTXID, MaxTXID uint64; Size int64; CreatedAt time.Time }`
137	- `type ListResponse struct { Files []LTXFile }`
138	- `func ListPath(acct, app, host string, level int) string`
139	- `func GetPath(acct, app, host string, level int, minTXID, maxTXID uint64) string`
140	- `func RangeHeader(offset, size int64) string`
141
142	- [ ] Step 1: Write the failing test
143
144	```go
145	// internal/dbdoor/coords_test.go
146	package dbdoor
147
148	import "testing"
149
150	// Range semantics must match litestream's S3 client exactly
151	// (s3/replica_client.go:647-652), or a resumed read returns the wrong bytes.
152	func TestRangeHeaderMatchesTheS3Client(t *testing.T) {
153	cases := []struct {
154	name string
155	offset, size int64
156	want string
157	}{
158	{"whole file from an offset", 0, 0, "bytes=0-"},
159	{"open-ended from an offset", 512, 0, "bytes=512-"},
160	{"sized read", 0, 100, "bytes=0-99"},
161	{"sized read at an offset", 512, 100, "bytes=512-611"},
162	}
163	for _, tc := range cases {
164	t.Run(tc.name, func(t *testing.T) {
165	if got := RangeHeader(tc.offset, tc.size); got != tc.want {
166	t.Errorf("RangeHeader(%d, %d) = %q, want %q", tc.offset, tc.size, got, tc.want)
167	}
168	})
169	}
170	}
171
172	// Coordinates, never keys: the caller names a level and a TXID range, and
173	// the server decides what object that is. lock.json has no coordinate.
174	func TestGetPathIsCoordinateAddressed(t *testing.T) {
175	got := GetPath("bab", "titogo", "a.bab.oncarlos.com", 3, 16, 32)
176	for _, want := range []string{"bab", "titogo", "a.bab.oncarlos.com", "3", "16", "32"} {
177	if !strings.Contains(got, want) {
178	t.Fatalf("GetPath = %q, missing %q", got, want)
179	}
180	}
181	if strings.Contains(got, ".ltx") \|\| strings.Contains(got, "lock") {
182	t.Fatalf("GetPath = %q leaks the key layout", got)
183	}
184	}
185	```
186
187	- [ ] Step 2: Run test to verify it fails
188
189	Run: `go test ./internal/dbdoor/ -run 'TestRangeHeader\|TestGetPath'`
190	Expected: FAIL — `undefined: RangeHeader`, `undefined: GetPath`
191
192	- [ ] Step 3: Write minimal implementation
193
194	```go
195	// internal/dbdoor/coords.go
196
197	// Package dbdoor is the wire shape of the `carlos db` replica door, shared
198	// by the console that serves it and the CLI that reads it (spec
199	// 2026-09-02 §6).
200	//
201	// It is a LEAF package for the reason internal/dbverify is one: the writer
202	// (internal/console) and the reader (the CLI's main package) must agree on
203	// one shape, and neither can import the other.
204	//
205	// Files are addressed by COORDINATE — level plus TXID range — never by key.
206	// That is not decoration: lock.json lives under the same prefix as the LTX
207	// files and is not a member's to fetch, and a coordinate has no way to name
208	// it. See spec §6 "The door".
209	package dbdoor
210
211	import (
212	"fmt"
213	"net/url"
214	"time"
215	)
216
217	// MaxLevel is the highest level a list may name. litestream's
218	// CalcRestorePlan asks for SnapshotLevel (9) first, then 8 down to 0
219	// (replica.go:1452, 1476-1485). The activator writes only 0-3 and 9, so
220	// most of that range is legitimately EMPTY — and empty is an empty list,
221	// never a 404, or the restore stops at the first gap.
222	const MaxLevel = 9
223
224	// LTXFile is one replica file as the door reports it. Size is MANDATORY:
225	// Restore rejects Size < ltx.HeaderSize (replica.go:630) and
226	// ResumableReader uses it to detect a premature EOF
227	// (resumable_reader.go:96).
228	type LTXFile struct {
229	Level int `json:"level"`
230	MinTXID uint64 `json:"min_txid"`
231	MaxTXID uint64 `json:"max_txid"`
232	Size int64 `json:"size"`
233	CreatedAt time.Time `json:"created_at"`
234	}
235
236	// ListResponse is ordered by MinTXID ASCENDING — restoreLevelCursor assumes
237	// filename order, which S3 gives because the names are zero-padded hex.
238	type ListResponse struct {
239	Files []LTXFile `json:"files"`
240	}
241
242	func base(acct, app, host string) string {
243	return fmt.Sprintf("/api/cli/apps/%s/%s/db/%s",
244	url.PathEscape(acct), url.PathEscape(app), url.PathEscape(host))
245	}
246
247	func ListPath(acct, app, host string, level int) string {
248	return fmt.Sprintf("%s/ltx/%d", base(acct, app, host), level)
249	}
250
251	func GetPath(acct, app, host string, level int, minTXID, maxTXID uint64) string {
252	return fmt.Sprintf("%s/ltx/%d/%d-%d", base(acct, app, host), level, minTXID, maxTXID)
253	}
254
255	// RangeHeader mirrors litestream's own S3 client byte-for-byte
256	// (s3/replica_client.go:647-652): open-ended when size is 0, inclusive
257	// end otherwise.
258	func RangeHeader(offset, size int64) string {
259	if size == 0 {
260	return fmt.Sprintf("bytes=%d-", offset)
261	}
262	return fmt.Sprintf("bytes=%d-%d", offset, offset+size-1)
263	}
264	```
265
266	Add `"strings"` to the test's imports.
267
268	- [ ] Step 4: Run test to verify it passes
269
270	Run: `go test ./internal/dbdoor/`
271	Expected: PASS
272
273	- [ ] Step 5: Commit
274
275	```bash
276	git add internal/dbdoor/coords.go internal/dbdoor/coords_test.go
277	git commit -m "dbdoor: the coordinate wire shape the door and client share"
278	```
279
280	---
281
282	### Task 3: Resolution and the residency refusals
283
284	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.
285
286	Files:
287	- Create: `internal/console/apidb.go`
288	- Test: `internal/console/apidb_test.go`
289
290	Interfaces:
291	- Consumes: `provision.Record` (fields `Host`, `App`, `Account`, `Pool`, `Region`), `provision.ValidHostShape`, `activator.HibernatePrefix`.
292	- Produces: `func (s *Server) resolveReplica(rec provision.Record, host string) (prefix string, err error)` and `var ErrResidency, ErrPooled, ErrBadHost error`.
293
294	- [ ] Step 1: Write the failing test
295
296	```go
297	// internal/console/apidb_test.go
298	package console
299
300	import (
301	"errors"
302	"testing"
303
304	"github.com/carlosframework/platform/internal/provision"
305	)
306
307	// Every one of these is refused BEFORE any S3 call. A door that reached the
308	// bucket and mapped AccessDenied would leak both latency and the fact that
309	// a bucket was tried.
310	func TestResolveReplicaRefusesBeforeTouchingS3(t *testing.T) {
311	s := &Server{}
312	s.cfg.Region = "eu-west-1"
313	s.cfg.HibernatePrefix = "instances"
314
315	cases := []struct {
316	name string
317	rec provision.Record
318	host string
319	want error
320	}{
321	{
322	name: "another region",
323	rec: provision.Record{Host: "a.bab.oncarlos.com", Region: "us-east-1"},
324	host: "a.bab.oncarlos.com",
325	want: ErrResidency,
326	},
327	{
328	name: "fleet-homed",
329	rec: provision.Record{Host: "a.bab.oncarlos.com", Region: "fleet/acme"},
330	host: "a.bab.oncarlos.com",
331	want: ErrResidency,
332	},
333	{
334	name: "pooled",
335	rec: provision.Record{Host: "a.bab.oncarlos.com", Region: "eu-west-1", Pool: "shared"},
336	host: "a.bab.oncarlos.com",
337	want: ErrPooled,
338	},
339	{
340	name: "hostile host",
341	rec: provision.Record{Host: "../../etc", Region: "eu-west-1"},
342	host: "../../etc",
343	want: ErrBadHost,
344	},
345	}
346	for _, tc := range cases {
347	t.Run(tc.name, func(t *testing.T) {
348	_, err := s.resolveReplica(tc.rec, tc.host)
349	if !errors.Is(err, tc.want) {
350	t.Fatalf("resolveReplica = %v, want %v", err, tc.want)
351	}
352	})
353	}
354	}
355
356	// The happy path is an in-region, non-pooled instance, and the prefix is
357	// the activator's own layout — not a string this package invents.
358	func TestResolveReplicaBuildsTheActivatorPrefix(t *testing.T) {
359	s := &Server{}
360	s.cfg.Region = "eu-west-1"
361	s.cfg.HibernatePrefix = "instances"
362	got, err := s.resolveReplica(
363	provision.Record{Host: "a.bab.oncarlos.com", Region: "eu-west-1"},
364	"a.bab.oncarlos.com")
365	if err != nil {
366	t.Fatal(err)
367	}
368	if got != "instances/a.bab.oncarlos.com" {
369	t.Fatalf("prefix = %q, want instances/a.bab.oncarlos.com", got)
370	}
371	}
372
373	// A single-region deployment leaves both empty, and "" == "" must be a
374	// match rather than a refusal — otherwise the door never works anywhere
375	// that has not set CARLOS_REGION.
376	func TestResolveReplicaTreatsEmptyRegionsAsAMatch(t *testing.T) {
377	s := &Server{}
378	s.cfg.HibernatePrefix = "instances"
379	if _, err := s.resolveReplica(
380	provision.Record{Host: "a.bab.oncarlos.com"}, "a.bab.oncarlos.com"); err != nil {
381	t.Fatalf("single-region deployment refused: %v", err)
382	}
383	}
384	```
385
386	- [ ] Step 2: Run test to verify it fails
387
388	Run: `go test ./internal/console/ -run TestResolveReplica`
389	Expected: FAIL — `undefined: ErrResidency`, `s.resolveReplica undefined`, `s.cfg.Region undefined`
390
391	- [ ] Step 3: Write minimal implementation
392
393	Add to `internal/console/server.go`'s `Config` struct:
394
395	```go
396	// The region data bucket the db door reads (spec 2026-09-02 §6,
397	// Option A). Read-only, in-region, instances/* only. Empty disables
398	// the door rather than failing it open.
399	HibernateBucket string
400	HibernateRegion string
401	HibernatePrefix string
402	// Region is this console's own placement, compared against an
403	// instance record's before any S3 call. Empty in a single-region
404	// deployment, where every record's is empty too.
405	Region string
406	```
407
408	Then `internal/console/apidb.go`:
409
410	```go
411	// The `carlos db` replica door (spec 2026-09-02 §6). This file is the half
412	// that makes no S3 call: resolving an instance record to one replica
413	// prefix, and refusing everything that is not this console's to read.
414	//
415	// Every refusal here happens BEFORE the bucket is touched. That is
416	// deliberate — a door that tried and mapped AccessDenied would leak the
417	// attempt, the latency, and eventually a bucket name into a log a member
418	// can provoke.
419
420	package console
421
422	import (
423	"errors"
424	"fmt"
425
426	"github.com/carlosframework/platform/internal/activator"
427	"github.com/carlosframework/platform/internal/provision"
428	)
429
430	var (
431	// ErrResidency: the data is in another region's bucket, or on a
432	// customer's own fleet box. The instance-move design makes those reads
433	// impossible at IAM by design, so this is the design working rather
434	// than a fault to retry.
435	ErrResidency = errors.New("db: the instance's data is outside this region")
436	// ErrPooled: a pooled record's data lives on the pool box, the same
437	// class movespass.go:150-153 refuses.
438	ErrPooled = errors.New("db: pooled instances have no replica of their own")
439	// ErrBadHost: path.Join cleans "../x" to "x" rather than refusing it,
440	// so a host becomes a key segment only after ValidHostShape.
441	ErrBadHost = errors.New("db: invalid host")
442	)
443
444	func (s *Server) resolveReplica(rec provision.Record, host string) (string, error) {
445	if !provision.ValidHostShape(host) {
446	return "", fmt.Errorf("%w: %q", ErrBadHost, host)
447	}
448	if rec.Pool != "" {
449	return "", fmt.Errorf("%w: %s is in pool %q", ErrPooled, host, rec.Pool)
450	}
451	if rec.Region != s.cfg.Region {
452	return "", fmt.Errorf("%w: %s is homed in %q, this console serves %q",
453	ErrResidency, host, rec.Region, s.cfg.Region)
454	}
455	return activator.HibernatePrefix(s.cfg.HibernatePrefix, host), nil
456	}
457	```
458
459	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.
460
461	- [ ] Step 4: Run test to verify it passes
462
463	Run: `go test ./internal/console/ -run TestResolveReplica`
464	Expected: PASS
465
466	- [ ] Step 5: Verify `ValidHostShape` is exported
467
468	Run: `grep -n "func ValidHostShape" internal/provision/provision.go`
469	Expected: it exists and is exported. If it is not, export it in this task and note the rename in the commit.
470
471	- [ ] Step 6: Commit
472
473	```bash
474	git add internal/console/apidb.go internal/console/apidb_test.go internal/console/server.go
475	git commit -m "console: resolve an instance to one replica prefix, refusing before S3"
476	```
477
478	---
479
480	### Task 4: The door handlers
481
482	Files:
483	- Modify: `internal/console/apidb.go`
484	- Modify: `internal/console/authcli.go`
485	- Test: `internal/console/apidb_test.go`
486
487	Interfaces:
488	- Consumes: Task 1's `lease.ReadReplicaClient`, Task 2's `dbdoor.LTXFile`/`ListResponse`/`MaxLevel`, Task 3's `resolveReplica`.
489	- Produces: `POST`-free routes `GET /api/cli/apps/{account}/{app}/db/{host}/ltx/{level}` and `GET …/ltx/{level}/{min}-{max}`.
490
491	- [ ] Step 1: Write the failing test
492
493	```go
494	// internal/console/apidb_test.go — append
495
496	// A level the activator never writes is EMPTY, not missing. A 404 here
497	// stops CalcRestorePlan at the first gap and the restore fails.
498	func TestAPIDBListAnswersEmptyForUnwrittenLevels(t *testing.T) {
499	fx, token := dbFixture(t)
500	for level := 0; level <= dbdoor.MaxLevel; level++ {
501	status, out := get(t, fx.ts.URL+dbdoor.ListPath("bab", "titogo", dbTestHost, level), token)
502	if status != http.StatusOK {
503	t.Fatalf("level %d = %d %s, want 200 with an empty list", level, status, out)
504	}
505	}
506	}
507
508	// Coordinate addressing exists to make lock.json unreachable. This is the
509	// test that keeps the door from decaying into an object proxy.
510	func TestAPIDBServesNoKeyOutsideItsCoordinates(t *testing.T) {
511	fx, token := dbFixture(t)
512	for _, bad := range []string{
513	"/api/cli/apps/bab/titogo/db/" + dbTestHost + "/ltx/0/../../lock.json",
514	"/api/cli/apps/bab/titogo/db/" + dbTestHost + "/lock.json",
515	"/api/cli/apps/bab/titogo/db/" + dbTestHost + "/ltx/10",
516	} {
517	status, _ := get(t, fx.ts.URL+bad, token)
518	if status == http.StatusOK {
519	t.Fatalf("%s = 200, want a refusal", bad)
520	}
521	}
522	}
523
524	// Not a 403: a 403 tells any token holder that a db door exists for this
525	// app. Service principals get the same answer as a stranger.
526	func TestAPIDBRefusesServicePrincipalsWithA404(t *testing.T) {
527	fx, svcToken := dbServiceFixture(t)
528	status, _ := get(t, fx.ts.URL+dbdoor.ListPath("bab", "titogo", dbTestHost, 0), svcToken)
529	if status != http.StatusNotFound {
530	t.Fatalf("service principal = %d, want 404", status)
531	}
532	}
533	```
534
535	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.
536
537	- [ ] Step 2: Run test to verify it fails
538
539	Run: `go test ./internal/console/ -run TestAPIDB`
540	Expected: FAIL — routes are 404 for every case, including the empty-list one that should be 200.
541
542	- [ ] Step 3: Write minimal implementation
543
544	Append to `internal/console/apidb.go`:
545
546	```go
547	// handleAPIDBList answers one level's listing. An out-of-range level is a
548	// 404 (it is not a coordinate); a level with nothing in it is 200 and an
549	// empty list (it is a coordinate that happens to be empty).
550	func (s Server) handleAPIDBList(w http.ResponseWriter, r http.Request) {
551	prefix, acct, app, host, ok := s.dbGate(w, r)
552	if !ok {
553	return
554	}
555	level, err := strconv.Atoi(r.PathValue("level"))
556	if err != nil \|\| level < 0 \|\| level > dbdoor.MaxLevel {
557	http.NotFound(w, r)
558	return
559	}
560	client := lease.ReadReplicaClient(s.cfg.HibernateBucket, prefix, s.cfg.HibernateRegion)
561	itr, err := client.LTXFiles(r.Context(), level, 0, false)
562	if err != nil {
563	log.Printf("console: api db list %s/%s %s level %d: %v", acct, app, host, level, err)
564	jsonError(w, http.StatusBadGateway, "could not read the replica; try again")
565	return
566	}
567	defer itr.Close()
568	out := dbdoor.ListResponse{Files: []dbdoor.LTXFile{}}
569	for itr.Next() {
570	f := itr.Item()
571	out.Files = append(out.Files, dbdoor.LTXFile{
572	Level: level, MinTXID: uint64(f.MinTXID), MaxTXID: uint64(f.MaxTXID),
573	Size: f.Size, CreatedAt: f.CreatedAt,
574	})
575	}
576	sort.Slice(out.Files, func(i, j int) bool { return out.Files[i].MinTXID < out.Files[j].MinTXID })
577	writeJSON(w, http.StatusOK, out)
578	}
579	```
580
581	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.
582
583	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.
584
585	- [ ] Step 4: Run test to verify it passes
586
587	Run: `go test ./internal/console/ -run TestAPIDB`
588	Expected: PASS
589
590	- [ ] Step 5: Commit
591
592	```bash
593	git add internal/console/apidb.go internal/console/apidb_test.go internal/console/authcli.go
594	git commit -m "console: serve replica LTX files by coordinate, members only"
595	```
596
597	---
598
599	### Task 5: The read-only replica client
600
601	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.
602
603	Files:
604	- Create: `internal/dbdoor/client.go`
605	- Test: `internal/dbdoor/client_test.go`
606
607	Interfaces:
608	- Produces: `func NewClient(base, acct, app, host string, do func(http.Request) (http.Response, error)) *Client`, satisfying `litestream.ReplicaClient`.
609
610	- [ ] Step 1: Write the failing test
611
612	```go
613	// internal/dbdoor/client_test.go
614	package dbdoor
615
616	import (
617	"context"
618	"testing"
619
620	"github.com/benbjohnson/litestream"
621	)
622
623	// The claim the whole design rests on: this client cannot corrupt a
624	// replica, because it has no code that writes to one.
625	func TestClientMutatingMethodsAlwaysError(t *testing.T) {
626	c := NewClient("http://console.invalid", "bab", "titogo", "a.example.com", nil)
627	ctx := context.Background()
628	if _, err := c.WriteLTXFile(ctx, 0, 1, 2, nil); err == nil {
629	t.Error("WriteLTXFile returned nil — this client must never write")
630	}
631	if err := c.DeleteLTXFiles(ctx, nil); err == nil {
632	t.Error("DeleteLTXFiles returned nil — this client must never delete")
633	}
634	if err := c.DeleteAll(ctx); err == nil {
635	t.Error("DeleteAll returned nil — this client must never delete")
636	}
637	}
638
639	// Restore type-asserts for ReplicaClientV3 (replica.go:612) and would take
640	// the v0.3 path if this satisfied it. It must not.
641	func TestClientIsNotAV3ReplicaClient(t *testing.T) {
642	var c any = NewClient("http://console.invalid", "bab", "titogo", "a.example.com", nil)
643	if _, ok := c.(litestream.ReplicaClientV3); ok {
644	t.Fatal("client satisfies ReplicaClientV3 — Restore would take the v0.3 path")
645	}
646	}
647
648	var _ litestream.ReplicaClient = (*Client)(nil)
649	```
650
651	- [ ] Step 2: Run test to verify it fails
652
653	Run: `go test ./internal/dbdoor/ -run TestClient`
654	Expected: FAIL — `undefined: NewClient`
655
656	- [ ] Step 3: Write minimal implementation
657
658	`Client` holds the base URL, the identifiers, and a `do` func (defaulting to `http.DefaultClient.Do`, injected in tests). Implement:
659
660	- `Type() string` → `"carlos-db"`
661	- `Init(ctx) error` → `nil` (idempotent no-op; the HTTP client needs no setup)
662	- `SetLogger(*slog.Logger)` → store it
663	- `LTXFiles(ctx, level, seek, useMetadata)` → GET `ListPath`, decode `ListResponse`, filter `>= seek` locally (seek is not on the wire), return an `ltx.FileIterator`
664	- `OpenLTXFile(ctx, level, min, max, offset, size)` → GET `GetPath` with `Range: RangeHeader(offset, size)`; a 404 maps to `os.ErrNotExist`
665	- `WriteLTXFile`, `DeleteLTXFiles`, `DeleteAll` → `return errReadOnly` unconditionally
666
667	```go
668	// errReadOnly is not a guard that can be switched off: these three methods
669	// have no other body. The two-replicators hazard internal/caps and
670	// internal/lease both warn about is unreachable from this client by
671	// construction, and a future edit that wanted to reach it would have to add
672	// the capability first.
673	var errReadOnly = errors.New("dbdoor: this client is read-only — it restores replicas and never writes them")
674	```
675
676	- [ ] Step 4: Run test to verify it passes
677
678	Run: `go test ./internal/dbdoor/ -run TestClient`
679	Expected: PASS
680
681	- [ ] Step 5: Add the call-counting test — the other half
682
683	```go
684	// The methods erroring is not enough: a restore that CALLED one would be
685	// relying on an error path. This asserts a full restore never reaches them,
686	// which is what catches a litestream upgrade changing the restore path.
687	func TestRestoreNeverCallsAMutatingMethod(t *testing.T) {
688	// Serve a real replica from a DirStore-backed fake console (build one
689	// by replicating a small SQLite through lease.ReplicateWithLease into a
690	// temp dir, then serving its files over httptest).
691	// Wrap Client in a counter that increments per method name.
692	// Restore through it, then assert:
693	if counts["WriteLTXFile"]+counts["DeleteLTXFiles"]+counts["DeleteAll"] != 0 {
694	t.Fatalf("restore called a mutating method: %v", counts)
695	}
696	}
697	```
698
699	- [ ] Step 6: Run it, then commit
700
701	```bash
702	go test ./internal/dbdoor/
703	git add internal/dbdoor/client.go internal/dbdoor/client_test.go
704	git commit -m "dbdoor: a replica client with no write methods"
705	```
706
707	---
708
709	### Task 6: Restore into a private directory
710
711	Files:
712	- Create: `internal/dbdoor/restore.go`
713	- Test: `internal/dbdoor/restore_test.go`
714
715	Interfaces:
716	- Produces: `func RestoreTo(ctx context.Context, c *Client, dir string) (path string, err error)`
717
718	- [ ] Step 1: Write the failing test
719
720	```go
721	// litestream creates <out>.tmp itself with os.Create, at the process umask,
722	// so a 0600 file cannot be pre-created for it. The containing directory is
723	// the only thing that can be private — and it must also confine the -wal
724	// and -shm a read-only open still needs.
725	func TestRestoreToUsesAPrivateDirectory(t *testing.T) {
726	dir := t.TempDir()
727	// ... restore a known replica ...
728	fi, err := os.Stat(filepath.Dir(path))
729	if err != nil {
730	t.Fatal(err)
731	}
732	if perm := fi.Mode().Perm(); perm != 0o700 {
733	t.Fatalf("restore directory is %04o, want 0700", perm)
734	}
735	}
736
737	// A planned file can vanish under the restore when the writer's L0
738	// retention fires. ResumableReader treats ErrNotExist as FATAL and does not
739	// retry, so the retry has to wrap the whole Restore — a fresh plan is the
740	// only recovery.
741	func TestRestoreToRetriesTheWholeRestoreOnce(t *testing.T) {
742	// A client that returns os.ErrNotExist for the first OpenLTXFile and
743	// serves normally afterwards.
744	// Assert: RestoreTo succeeds, and the client saw two CalcRestorePlan
745	// listing passes rather than one.
746	}
747	```
748
749	- [ ] Step 2: Run to verify it fails
750
751	Run: `go test ./internal/dbdoor/ -run TestRestoreTo`
752	Expected: FAIL — `undefined: RestoreTo`
753
754	- [ ] Step 3: Implement
755
756	`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.
757
758	- [ ] Step 4: Run to verify it passes, then commit
759
760	```bash
761	go test ./internal/dbdoor/
762	git add internal/dbdoor/restore.go internal/dbdoor/restore_test.go
763	git commit -m "dbdoor: restore into a 0700 directory, retrying the whole plan once"
764	```
765
766	---
767
768	### Task 7: `carlos db pull` and `carlos db query`
769
770	Files:
771	- Create: `db.go`, `db_test.go`
772	- Modify: `apimode.go`, `main.go`, `docs/site/cli.md`
773
774	- [ ] Step 1: Write the failing tests
775
776	```go
777	// A copy is harmless to write to. Read-only exists so a caller cannot
778	// BELIEVE it changed production: an agent that sees "1 row affected"
779	// concludes it fixed the bug. The error is the truth.
780	func TestQueryRefusesAWriteWithSQLitesOwnError(t *testing.T) {
781	// open a restored fixture through the same helper db.go uses
782	err := runQuery(path, "UPDATE users SET name='x'")
783	if err == nil \|\| !strings.Contains(err.Error(), "attempt to write a readonly database") {
784	t.Fatalf("write error = %v, want SQLite's readonly refusal", err)
785	}
786	}
787
788	func TestQueryLeavesTheFileByteIdentical(t testing.T) { / sha256 before and after */ }
789
790	func TestPullRefusesAnExistingOutAndLands0600(t testing.T) { / both halves */ }
791
792	func TestPullCopiesAcrossFilesystems(t *testing.T) {
793	// os.Rename fails across devices; the temp dir and --out routinely
794	// differ. Assert a copy path, not a rename.
795	}
796	```
797
798	- [ ] Step 2: Run to verify they fail, then implement
799
800	`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.
801
802	- [ ] Step 3: Add the `cli.md` heading
803
804	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.
805
806	- [ ] Step 4: Run the copy-review gate
807
808	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.
809
810	- [ ] Step 5: Full gate, then commit
811
812	```bash
813	go build ./... && go vet ./... && gofmt -l . && go test ./... -count=1
814	git add db.go db_test.go apimode.go main.go docs/site/cli.md
815	git commit -m "carlos db: pull and query a restored replica, read-only"
816	```
817
818	---
819
820	## Self-Review
821
822	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.
823
824	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.
825
826	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.
827
828	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.
829