oficina / sheets Public

branch:

Clone

git clone https://amadan.net/oficina/sheets

Plain git — no account needed to clone.

Download

Download this file

1	# Sheets Core Implementation Plan
2
3	> For agentic workers: REQUIRED SUB-SKILL: Use
4	> superpowers:subagent-driven-development (recommended) or
5	> superpowers:executing-plans to implement this plan task-by-task. Steps use
6	> checkbox (`- [ ]`) syntax for tracking.
7
8	Goal: A headless, sealed spreadsheet engine — storage, formula
9	evaluation, number formatting and XLSX round-tripping — with every trust
10	gate live from the first commit.
11
12	Architecture: One Go binary on the CARLOS platform. Cells are sparse
13	rows keyed by stable row/column identity, sealed at rest with a DEK from
14	platform config. An append-only event log carries input events (never
15	derived values). A formula engine — lexer, parser, evaluator, precedent
16	graph with dirty-set propagation — owns all calculation; the client will
17	later paint strings this engine produces. `excelize` is confined to XLSX
18	I/O.
19
20	Tech Stack: Go 1.26, `github.com/carlosframework/rastrillo` v0.20.0
21	(GORM + chi + cgo-free SQLite via modernc), `github.com/xuri/excelize/v2`
22	v2.11.0, `golang.org/x/crypto` for HKDF. No CGO. No JS in this plan.
23
24	Spec: `docs/superpowers/specs/2026-08-30-sheets-design.md`
25
26	Framework pin: `v0.20.0`, tagged 2026-08-30 and verified on the module
27	proxy. It carries `ThemeCSS`, `ThemeNames`, `Layout` and `LayoutNames`,
28	which `v0.19.0` did not — none of which this plan needs, but pinning the
29	current release avoids a second bump the week after. `Pair` and the colour
30	allocation entry point are not in it; they remain specified and
31	unwritten, so the §10.3 shim stands. Re-copy `static/tokens.css`
32	deliberately on adoption — it is frozen at scaffold time while partials
33	upgrade with the module.
34
35	Scope boundary: This plan deliberately excludes everything the design
36	system touches — auth screens, the grid surface, chrome, sharing UI, live
37	sync. Design-system questions are paused pending rastrillo's own settling
38	(Paul, 2026-08-30). Those become plan 2. Every task here is testable without
39	a browser.
40
41	## Global Constraints
42
43	Copied verbatim from the spec. Every task's requirements include these.
44
45	- The one rule: *"A sheet's contents are sealed at rest. The bucket never
46	holds a plaintext cell."* A plaintext value reaching the SQLite file is a
47	bug, not a preference.
48	- `CGO_ENABLED=0` throughout. A cgo SQLite driver sneaking in is a bug.
49	Never import `glebarez/*` or `gorm.io/driver/sqlite`; use rastrillo's `db`.
50	- Migrations are additive-only. New code over an old DB is always safe.
51	Never delete data to update.
52	- Cell values are IEEE doubles end to end. Currency is a number format,
53	not a type. The family's "a float never touches an amount" rule binds
54	app-owned amounts (billing, quotas) only.
55	- **No cell content, comment text, sheet title or tab name in any log line,
56	error string, job status message or metric label.** Errors carry
57	coordinates and codes. `carlos logs` is platform-held storage we cannot
58	grep.
59	- Never `scope.Owned` or `scope.OwnedBy` on a team-shared table. Both are
60	single-column equality; our rows are team-shared, and `Owned` would return
61	only the creator's rows — failing as a plausible empty list rather than an
62	error.
63	- 200,000 cells per tab, hard, with the reasoning in a comment beside the
64	constant. An honest error, never a slow death.
65	- **Anything nondeterministic in a fixture path makes the contract
66	unfalsifiable**, and the first person to hit it removes the check rather
67	than the nondeterminism. Pin clocks; make exclusion lists files somebody
68	edits, not filters somebody widens.
69	- **A suite of gates that all live inside one layer is a suite that agrees
70	with itself.** At least one gate crosses from computed value to painted
71	output.
72
73	---
74
75	## File Structure
76
77	```
78	cmd/sheets/main.go Thin dispatch. Platform contract only.
79	internal/sheets/
80	app.go App struct, wiring, Resolve/Serve/Run.
81	models.go GORM models. Schema lives here, nowhere else.
82	seal/
83	seal.go DEK, HKDF purpose keys, AES-GCM. ~150 lines.
84	vectors_test.go Golden envelope vectors.
85	store/
86	store.go Open/save cells, rows, cols, formats.
87	events.go Append-only log: input events, recalc markers.
88	handle.go In-memory sheet handle, eviction, dirty markers.
89	calc/
90	token.go Lexer. ~200 lines.
91	ast.go AST node types. ~120 lines.
92	parse.go Parser. ~250 lines.
93	ref.go A1 ↔ stable id, and the translation pass.
94	graph.go Precedent graph, dirty set, topological recalc.
95	eval.go Evaluator core.
96	fn_math.go Function library, split by family.
97	fn_text.go
98	fn_logic.go
99	fn_lookup.go
100	fn_date.go
101	errors.go Excel error values.
102	corpus_test.go Reads the committed golden corpus.
103	format/
104	format.go Number format → display string. Go formats.
105	xlsx/
106	import.go excelize → our model.
107	export.go our model → excelize.
108	roundtrip_test.go Golden corpus.
109	tools/gencorpus/main.go LibreOffice-driven corpus generator. Run rarely.
110	testdata/corpus/
111	formulas.json Committed. Hash-pinned.
112	volatile-excluded.txt A file somebody edits. Not a filter.
113	xlsx/*.xlsx Round-trip fixtures.
114	```
115
116	Rationale for the splits: `calc/` is the largest subsystem and is divided by
117	compilation stage rather than by feature, so a change to parsing never
118	touches evaluation. The function library is split by family because it is
119	the only part expected to grow to thousands of lines. `seal/` is isolated so
120	the plaintext gate has one place to audit.
121
122	---
123
124	### Task 1: Skeleton and the platform contract
125
126	Files:
127	- Create: `go.mod`, `cmd/sheets/main.go`, `internal/sheets/app.go`
128	- Create: `internal/sheets/app_test.go`
129
130	Interfaces:
131	- Consumes: nothing.
132	- Produces: `sheets.New(db gorm.DB) App` and `(*App).Router() http.Handler`.
133	Every later task mounts on this.
134
135	- [ ] Step 1: Initialise the module
136
137	```bash
138	cd /home/paulca/amadan.net/oficina/sheets # or wherever the checkout is
139	go mod init amadan.net/oficina/sheets
140	go get github.com/carlosframework/rastrillo@v0.20.0
141	```
142
143	- [ ] Step 2: Write the failing test
144
145	Create `internal/sheets/app_test.go`:
146
147	```go
148	package sheets
149
150	import (
151	"net/http"
152	"net/http/httptest"
153	"testing"
154	)
155
156	func TestHealthz(t *testing.T) {
157	app := New(nil)
158	req := httptest.NewRequest(http.MethodGet, "/healthz", nil)
159	rec := httptest.NewRecorder()
160	app.Router().ServeHTTP(rec, req)
161	if rec.Code != http.StatusOK {
162	t.Fatalf("healthz = %d, want 200", rec.Code)
163	}
164	}
165	```
166
167	- [ ] Step 3: Run it and watch it fail
168
169	Run: `go test ./internal/sheets/ -run TestHealthz -v`
170	Expected: FAIL — `undefined: New`.
171
172	- [ ] Step 4: Write the minimal implementation
173
174	Create `internal/sheets/app.go`:
175
176	```go
177	// Package sheets is the Sheets instance app: one process, one SQLite file,
178	// one team.
179	package sheets
180
181	import (
182	"net/http"
183
184	"github.com/go-chi/chi/v5"
185	"gorm.io/gorm"
186	)
187
188	// App holds the wiring. One per process.
189	type App struct {
190	db *gorm.DB
191	}
192
193	// New builds an App. db may be nil in tests that do not touch storage.
194	func New(db gorm.DB) App { return &App{db: db} }
195
196	// Router returns the mounted handler.
197	func (a *App) Router() http.Handler {
198	r := chi.NewRouter()
199	r.Get("/healthz", func(w http.ResponseWriter, _ *http.Request) {
200	w.WriteHeader(http.StatusOK)
201	})
202	return r
203	}
204	```
205
206	- [ ] Step 5: Run it and watch it pass
207
208	Run: `go test ./internal/sheets/ -run TestHealthz -v`
209	Expected: PASS.
210
211	- [ ] Step 6: Write `cmd/sheets/main.go`
212
213	Thin dispatch only — the platform contract is rastrillo's job. There is no
214	`$PORT`; the contract is `<bin> --socket <path> --db <path>`.
215
216	```go
217	// Command sheets is the Sheets instance binary.
218	package main
219
220	import (
221	"log"
222
223	"github.com/carlosframework/rastrillo"
224	"amadan.net/oficina/sheets/internal/sheets"
225	)
226
227	func main() {
228	cfg, err := rastrillo.Resolve()
229	if err != nil {
230	log.Fatal(err)
231	}
232	app := sheets.New(cfg.DB)
233	if err := rastrillo.Run(cfg, app.Router()); err != nil {
234	log.Fatal(err)
235	}
236	}
237	```
238
239	If `rastrillo.Resolve`/`Run` have a different shape at v0.19.0, read
240	`$(go env GOMODCACHE)/github.com/carlosframework/rastrillo@v0.20.0/SKILL.md`
241	and follow it — SKILL.md wins over this plan.
242
243	- [ ] Step 7: Verify the whole build, cgo-free
244
245	```bash
246	CGO_ENABLED=0 go build ./... && go vet ./... && go test ./...
247	```
248	Expected: all pass.
249
250	- [ ] Step 8: Commit
251
252	```bash
253	git add go.mod go.sum cmd/ internal/
254	git commit -m "Skeleton: the platform contract and a health check"
255	```
256
257	---
258
259	### Task 2: Sealing, and the plaintext gate
260
261	The gate goes in with the mechanism, not after it. Once this task lands,
262	every later task is checked by it.
263
264	Files:
265	- Create: `internal/sheets/seal/seal.go`, `internal/sheets/seal/seal_test.go`
266	- Create: `internal/sheets/seal/vectors_test.go`
267	- Create: `internal/sheets/seal/testdata/vectors.json`
268
269	Interfaces:
270	- Consumes: nothing.
271	- Produces:
272	- `seal.New(dek []byte) (*Sealer, error)` — DEK must be 32 bytes.
273	- `(*Sealer).Seal(purpose string, plaintext []byte) ([]byte, error)`
274	- `(*Sealer).Open(purpose string, ciphertext []byte) ([]byte, error)`
275	Purposes used across the codebase: `"cell.input"`, `"cell.cached"`,
276	`"sheet.title"`, `"tab.name"`, `"event.payload"`.
277
278	- [ ] Step 1: Write the failing round-trip test
279
280	Create `internal/sheets/seal/seal_test.go`:
281
282	```go
283	package seal
284
285	import (
286	"bytes"
287	"testing"
288	)
289
290	func testDEK() []byte {
291	d := make([]byte, 32)
292	for i := range d {
293	d[i] = byte(i)
294	}
295	return d
296	}
297
298	func TestSealOpenRoundTrip(t *testing.T) {
299	s, err := New(testDEK())
300	if err != nil {
301	t.Fatal(err)
302	}
303	want := []byte("=SUM(A1:A10)")
304	ct, err := s.Seal("cell.input", want)
305	if err != nil {
306	t.Fatal(err)
307	}
308	if bytes.Contains(ct, want) {
309	t.Fatal("ciphertext contains the plaintext")
310	}
311	got, err := s.Open("cell.input", ct)
312	if err != nil {
313	t.Fatal(err)
314	}
315	if !bytes.Equal(got, want) {
316	t.Fatalf("Open = %q, want %q", got, want)
317	}
318	}
319
320	// A value sealed under one purpose must not open under another. Purpose
321	// separation is what stops a cached value being swapped for an input.
322	func TestPurposeSeparation(t *testing.T) {
323	s, _ := New(testDEK())
324	ct, _ := s.Seal("cell.input", []byte("secret"))
325	if _, err := s.Open("cell.cached", ct); err == nil {
326	t.Fatal("opened under the wrong purpose")
327	}
328	}
329
330	func TestRejectsShortDEK(t *testing.T) {
331	if _, err := New([]byte("too short")); err == nil {
332	t.Fatal("accepted a short DEK")
333	}
334	}
335	```
336
337	- [ ] Step 2: Run and watch it fail
338
339	Run: `go test ./internal/sheets/seal/ -v`
340	Expected: FAIL — `undefined: New`.
341
342	- [ ] Step 3: Implement
343
344	Create `internal/sheets/seal/seal.go`:
345
346	```go
347	// Package seal is the app's only encryption surface.
348	//
349	// The one rule: a sheet's contents are sealed at rest, and the bucket never
350	// holds a plaintext cell. The DEK arrives as platform config and is never
351	// written to the database it decrypts.
352	//
353	// Purpose keys are derived with HKDF so that a value sealed as a cell input
354	// cannot be opened as a cached value. That is not paranoia: the two are
355	// adjacent columns of the same row, and a bug that swapped them would be
356	// invisible without this.
357	package seal
358
359	import (
360	"crypto/aes"
361	"crypto/cipher"
362	"crypto/rand"
363	"errors"
364	"io"
365
366	"golang.org/x/crypto/hkdf"
367	)
368
369	// Sealer holds the data encryption key. One per process.
370	type Sealer struct{ dek []byte }
371
372	// New returns a Sealer. The DEK must be exactly 32 bytes.
373	func New(dek []byte) (*Sealer, error) {
374	if len(dek) != 32 {
375	return nil, errors.New("seal: DEK must be 32 bytes")
376	}
377	c := make([]byte, 32)
378	copy(c, dek)
379	return &Sealer{dek: c}, nil
380	}
381
382	// aeadFor derives the purpose key and returns its AEAD.
383	func (s *Sealer) aeadFor(purpose string) (cipher.AEAD, error) {
384	key := make([]byte, 32)
385	r := hkdf.New(sha256New, s.dek, nil, []byte("oficina/sheets:"+purpose))
386	if _, err := io.ReadFull(r, key); err != nil {
387	return nil, err
388	}
389	block, err := aes.NewCipher(key)
390	if err != nil {
391	return nil, err
392	}
393	return cipher.NewGCM(block)
394	}
395
396	// Seal encrypts plaintext under purpose. The nonce is prepended.
397	func (s *Sealer) Seal(purpose string, plaintext []byte) ([]byte, error) {
398	aead, err := s.aeadFor(purpose)
399	if err != nil {
400	return nil, err
401	}
402	nonce := make([]byte, aead.NonceSize())
403	if _, err := rand.Read(nonce); err != nil {
404	return nil, err
405	}
406	return aead.Seal(nonce, nonce, plaintext, nil), nil
407	}
408
409	// Open decrypts a value sealed under the same purpose.
410	func (s *Sealer) Open(purpose string, ct []byte) ([]byte, error) {
411	aead, err := s.aeadFor(purpose)
412	if err != nil {
413	return nil, err
414	}
415	if len(ct) < aead.NonceSize() {
416	return nil, errors.New("seal: ciphertext too short")
417	}
418	nonce, body := ct[:aead.NonceSize()], ct[aead.NonceSize():]
419	return aead.Open(nil, nonce, body, nil)
420	}
421	```
422
423	Add the hash import at the top of the file:
424
425	```go
426	import "crypto/sha256"
427
428	func sha256New() hash.Hash { return sha256.New() }
429	```
430
431	with `"hash"` added to the import block. (Written as a named function rather
432	than inline so the HKDF call reads as one line.)
433
434	- [ ] Step 4: Run and watch it pass
435
436	```bash
437	go get golang.org/x/crypto
438	go test ./internal/sheets/seal/ -v
439	```
440	Expected: PASS.
441
442	- [ ] Step 5: Write the golden vectors test
443
444	Vectors pin the envelope so a later refactor cannot silently change the
445	format and orphan every sealed row. Because GCM nonces are random, the
446	vector fixes the derived key, not a whole ciphertext.
447
448	Create `internal/sheets/seal/vectors_test.go`:
449
450	```go
451	package seal
452
453	import (
454	"encoding/hex"
455	"testing"
456	)
457
458	// Pins the HKDF derivation. If this changes, every sealed row in every
459	// deployed instance becomes unreadable — so it changes only with a
460	// migration, never as a refactor.
461	func TestPurposeKeyVectors(t *testing.T) {
462	s, _ := New(testDEK())
463	for _, tc := range []struct{ purpose, want string }{
464	{"cell.input", ""}, // fill from the first run, then never edit
465	{"cell.cached", ""},
466	} {
467	got := hex.EncodeToString(s.purposeKey(tc.purpose))
468	if tc.want == "" {
469	t.Logf("RECORD %s = %s", tc.purpose, got)
470	continue
471	}
472	if got != tc.want {
473	t.Fatalf("%s key = %s, want %s", tc.purpose, got, tc.want)
474	}
475	}
476	}
477	```
478
479	Add the helper to `seal.go`:
480
481	```go
482	// purposeKey exposes the derived key for the golden-vector test. It is not
483	// used in production paths.
484	func (s *Sealer) purposeKey(purpose string) []byte {
485	key := make([]byte, 32)
486	r := hkdf.New(sha256New, s.dek, nil, []byte("oficina/sheets:"+purpose))
487	io.ReadFull(r, key)
488	return key
489	}
490	```
491
492	- [ ] Step 6: Record the vectors, then freeze them
493
494	Run: `go test ./internal/sheets/seal/ -run TestPurposeKeyVectors -v`
495	Copy each logged `RECORD` value into the `want` field. Re-run; expect PASS.
496
497	- [ ] Step 7: Write the plaintext gate
498
499	This is the gate the whole spec rests on. It greps the raw SQLite file and
500	its WAL, because Litestream ships WAL frames continuously and a value
501	written plainly reaches the bucket before any delete.
502
503	Create `internal/sheets/seal/gate_test.go`:
504
505	```go
506	package seal_test
507
508	import (
509	"bytes"
510	"os"
511	"path/filepath"
512	"testing"
513	)
514
515	// The canary strings a test seeds into a database before this gate runs.
516	// Any of them appearing in raw bytes is a plaintext leak.
517	var canaries = [][]byte{
518	[]byte("CANARY-CELL-VALUE-8f3a"),
519	[]byte("CANARY-SHEET-TITLE-2b71"),
520	[]byte("CANARY-TAB-NAME-c40d"),
521	}
522
523	// AssertNoPlaintext greps a database file, its WAL and its SHM for canaries.
524	// Exported for use by store and handle tests, which are the ones that
525	// actually write rows.
526	func AssertNoPlaintext(t *testing.T, dbPath string) {
527	t.Helper()
528	for _, suffix := range []string{"", "-wal", "-shm"} {
529	p := dbPath + suffix
530	b, err := os.ReadFile(p)
531	if os.IsNotExist(err) {
532	continue
533	}
534	if err != nil {
535	t.Fatalf("read %s: %v", filepath.Base(p), err)
536	}
537	for _, c := range canaries {
538	if bytes.Contains(b, c) {
539	t.Fatalf("plaintext %q found in %s", c, filepath.Base(p))
540	}
541	}
542	}
543	}
544	```
545
546	Move `AssertNoPlaintext` and `canaries` into a non-`_test` file
547	`internal/sheets/seal/gate.go` under a `//go:build !prod` tag if the
548	executor prefers; either is fine, but it must be callable from
549	`internal/sheets/store`.
550
551	- [ ] Step 8: Commit
552
553	```bash
554	git add internal/sheets/seal/
555	git commit -m "Sealing, purpose keys, golden vectors, and the plaintext gate"
556	```
557
558	---
559
560	### Task 3: The storage schema and stable identity
561
562	Files:
563	- Create: `internal/sheets/models.go`, `internal/sheets/models_test.go`
564	- Create: `internal/sheets/calc/ref.go`, `internal/sheets/calc/ref_test.go`
565
566	Interfaces:
567	- Consumes: `seal.Sealer` (task 2).
568	- Produces:
569	- Models `Sheet`, `Tab`, `Row`, `Col`, `Cell`, `Format`.
570	- `calc.ColLabel(pos int) string` — 0→"A", 25→"Z", 26→"AA".
571	- `calc.ParseColLabel(s string) (int, error)` — the inverse.
572	- `calc.A1(rowPos, colPos int) string` — 0,0 → "A1".
573
574	- [ ] Step 1: Write the failing A1 test
575
576	A1 is a view, derived at render time. Positions live on `Row`/`Col`
577	records; `Cell` is keyed by stable ids. Getting the label maths wrong is the
578	kind of bug that surfaces at column 702, so it is tested at the boundaries.
579
580	Create `internal/sheets/calc/ref_test.go`:
581
582	```go
583	package calc
584
585	import "testing"
586
587	func TestColLabel(t *testing.T) {
588	for _, tc := range []struct {
589	pos int
590	want string
591	}{
592	{0, "A"}, {1, "B"}, {25, "Z"},
593	{26, "AA"}, {27, "AB"}, {51, "AZ"},
594	{52, "BA"}, {701, "ZZ"}, {702, "AAA"},
595	} {
596	if got := ColLabel(tc.pos); got != tc.want {
597	t.Errorf("ColLabel(%d) = %q, want %q", tc.pos, got, tc.want)
598	}
599	}
600	}
601
602	func TestParseColLabelRoundTrip(t *testing.T) {
603	for pos := 0; pos < 3000; pos++ {
604	got, err := ParseColLabel(ColLabel(pos))
605	if err != nil {
606	t.Fatalf("pos %d: %v", pos, err)
607	}
608	if got != pos {
609	t.Fatalf("round trip %d -> %q -> %d", pos, ColLabel(pos), got)
610	}
611	}
612	}
613
614	func TestA1(t *testing.T) {
615	if got := A1(0, 0); got != "A1" {
616	t.Errorf("A1(0,0) = %q, want A1", got)
617	}
618	if got := A1(6, 1); got != "B7" {
619	t.Errorf("A1(6,1) = %q, want B7", got)
620	}
621	}
622	```
623
624	- [ ] Step 2: Run and watch it fail
625
626	Run: `go test ./internal/sheets/calc/ -run 'TestColLabel\|TestParseColLabel\|TestA1' -v`
627	Expected: FAIL — undefined.
628
629	- [ ] Step 3: Implement
630
631	Create `internal/sheets/calc/ref.go`:
632
633	```go
634	// Package calc owns everything about formulas: references, lexing, parsing,
635	// evaluation and the dependency graph.
636	package calc
637
638	import (
639	"errors"
640	"strings"
641	)
642
643	// ColLabel renders a zero-based column position as a spreadsheet label.
644	// This is bijective base-26, not ordinary base-26: there is no zero digit,
645	// which is why 25 is "Z" and 26 is "AA" rather than "BA".
646	func ColLabel(pos int) string {
647	if pos < 0 {
648	return ""
649	}
650	var sb strings.Builder
651	pos++
652	for pos > 0 {
653	pos--
654	sb.WriteByte(byte('A' + pos%26))
655	pos /= 26
656	}
657	b := []byte(sb.String())
658	for i, j := 0, len(b)-1; i < j; i, j = i+1, j-1 {
659	b[i], b[j] = b[j], b[i]
660	}
661	return string(b)
662	}
663
664	// ParseColLabel is ColLabel's inverse.
665	func ParseColLabel(s string) (int, error) {
666	if s == "" {
667	return 0, errors.New("calc: empty column label")
668	}
669	n := 0
670	for i := 0; i < len(s); i++ {
671	c := s[i]
672	if c >= 'a' && c <= 'z' {
673	c -= 'a' - 'A'
674	}
675	if c < 'A' \|\| c > 'Z' {
676	return 0, errors.New("calc: bad column label")
677	}
678	n = n*26 + int(c-'A') + 1
679	}
680	return n - 1, nil
681	}
682
683	// A1 renders zero-based positions as an A1 reference. Positions, not ids:
684	// stable ids are the key, A1 is the view.
685	func A1(rowPos, colPos int) string {
686	return ColLabel(colPos) + itoa(rowPos+1)
687	}
688
689	func itoa(n int) string {
690	if n == 0 {
691	return "0"
692	}
693	var b [20]byte
694	i := len(b)
695	for n > 0 {
696	i--
697	b[i] = byte('0' + n%10)
698	n /= 10
699	}
700	return string(b[i:])
701	}
702	```
703
704	- [ ] Step 4: Run and watch it pass
705
706	Run: `go test ./internal/sheets/calc/ -v`
707	Expected: PASS.
708
709	- [ ] Step 5: Write the models
710
711	Create `internal/sheets/models.go`:
712
713	```go
714	package sheets
715
716	import "time"
717
718	// Sheet is one spreadsheet document.
719	type Sheet struct {
720	ID uint64 `gorm:"primaryKey"`
721	TitleSealed []byte // seal purpose "sheet.title"
722	CreatedBy uint64
723	CreatedAt time.Time
724	UpdatedAt time.Time
725	DeletedAt *time.Time `gorm:"index"`
726	}
727
728	// Tab is one sheet within a document — the tabs along the bottom.
729	type Tab struct {
730	ID uint64 `gorm:"primaryKey"`
731	SheetID uint64 `gorm:"index"`
732	NameSealed []byte // seal purpose "tab.name"
733	Position int
734	FrozenRows int
735	FrozenCols int
736	}
737
738	// Row and Col carry stable identity. Position is a property of the record,
739	// never part of a key — so inserting a row above a comment or a range does
740	// not move what they point at. See spec section 4.2 for what this does and
741	// does not buy: it removes reference rewriting for insert and delete only.
742	type Row struct {
743	ID uint64 `gorm:"primaryKey"`
744	TabID uint64 `gorm:"index:idx_row_tab_pos"`
745	Position int `gorm:"index:idx_row_tab_pos"`
746	Height int
747	}
748
749	type Col struct {
750	ID uint64 `gorm:"primaryKey"`
751	TabID uint64 `gorm:"index:idx_col_tab_pos"`
752	Position int `gorm:"index:idx_col_tab_pos"`
753	Width int
754	}
755
756	// Cell is sparse: only non-empty cells exist.
757	//
758	// CacheVersion records the InputVersion its CachedSealed was derived from.
759	// A cache whose CacheVersion trails its input is stale and must not be
760	// served — which is how a crash mid-recalc is detected rather than assumed
761	// away. See spec section 4.4.
762	type Cell struct {
763	TabID uint64 `gorm:"primaryKey"`
764	RowID uint64 `gorm:"primaryKey"`
765	ColID uint64 `gorm:"primaryKey"`
766	InputSealed []byte // seal purpose "cell.input"
767	CachedSealed []byte // seal purpose "cell.cached"
768	InputVersion uint64
769	CacheVersion uint64
770	FormatID *uint64
771	UpdatedBy uint64
772	UpdatedAt time.Time
773	}
774
775	// Format is interned. A column of 5,000 identically styled cells is one row
776	// here, not 5,000 — which is the difference between a format change being
777	// cheap and being a table rewrite.
778	type Format struct {
779	ID uint64 `gorm:"primaryKey"`
780	Digest string `gorm:"uniqueIndex;size:64"`
781	NumberFmt string
782	Bold bool
783	Italic bool
784	FillIntentH *float64 // OKLCH hue; nil means no fill
785	FillIntentC *float64 // OKLCH chroma
786	FillHex string // retained from XLSX import for lossless re-export
787	Align string
788	}
789	```
790
791	- [ ] Step 6: Write the schema test
792
793	Create `internal/sheets/models_test.go`:
794
795	```go
796	package sheets
797
798	import "testing"
799
800	// The models must carry no column that could hold a readable cell value.
801	// This is a schema-level restatement of the one rule: there is nothing to
802	// accidentally populate.
803	func TestNoPlaintextValueColumns(t *testing.T) {
804	forbidden := []string{"Title", "Name", "Input", "Cached", "Value"}
805	// Sealed fields end in "Sealed"; a bare name would be a plaintext column.
806	for _, f := range []string{"TitleSealed", "NameSealed", "InputSealed", "CachedSealed"} {
807	if !hasSuffix(f, "Sealed") {
808	t.Fatalf("%s does not end in Sealed", f)
809	}
810	}
811	_ = forbidden
812	}
813
814	func hasSuffix(s, suf string) bool {
815	return len(s) >= len(suf) && s[len(s)-len(suf):] == suf
816	}
817	```
818
819	Executor note: this test is deliberately weak — it documents intent. The
820	real enforcement is the byte-level gate from task 2, invoked in task 4.
821
822	- [ ] Step 7: Verify and commit
823
824	```bash
825	CGO_ENABLED=0 go build ./... && go test ./...
826	git add internal/sheets/models.go internal/sheets/models_test.go internal/sheets/calc/
827	git commit -m "Storage schema, stable row and column identity, A1 as a view"
828	```
829
830	---
831
832	### Task 4: The event log, and the gate against real rows
833
834	Files:
835	- Create: `internal/sheets/store/events.go`, `internal/sheets/store/events_test.go`
836
837	Interfaces:
838	- Consumes: models (task 3), `seal.Sealer` (task 2).
839	- Produces:
840	- `store.Event` model.
841	- `store.AppendInput(tx gorm.DB, s seal.Sealer, e InputEvent) error`
842	- `store.AppendRecalc(tx *gorm.DB, triggerID uint64, derived int) error`
843
844	- [ ] Step 1: Write the failing test, including the plaintext gate
845
846	This is the first task that writes real rows, so it is where the gate stops
847	being theoretical.
848
849	Create `internal/sheets/store/events_test.go`:
850
851	```go
852	package store_test
853
854	import (
855	"path/filepath"
856	"testing"
857
858	"amadan.net/oficina/sheets/internal/sheets/seal"
859	"amadan.net/oficina/sheets/internal/sheets/store"
860	)
861
862	func TestInputEventIsSealedOnDisk(t *testing.T) {
863	dir := t.TempDir()
864	dbPath := filepath.Join(dir, "test.db")
865	db := openTestDB(t, dbPath) // helper defined below
866	s, _ := seal.New(make32())
867
868	err := store.AppendInput(db, s, store.InputEvent{
869	TabID: 1, RowID: 1, ColID: 1, ActorID: 7,
870	OldRaw: "",
871	NewRaw: "CANARY-CELL-VALUE-8f3a",
872	})
873	if err != nil {
874	t.Fatal(err)
875	}
876	checkpoint(t, db) // force a WAL checkpoint so bytes hit the file
877
878	seal.AssertNoPlaintext(t, dbPath)
879	}
880
881	// A recalc marker records that a pass happened and how wide it was. It must
882	// never carry derived values: logging them makes the log grow with the
883	// fan-out of every edit rather than with the number of edits, in a file
884	// Litestream ships continuously. See spec section 4.3.
885	func TestRecalcMarkerHoldsNoValues(t *testing.T) {
886	dir := t.TempDir()
887	db := openTestDB(t, filepath.Join(dir, "test.db"))
888	if err := store.AppendRecalc(db, 42, 1200); err != nil {
889	t.Fatal(err)
890	}
891	var e store.Event
892	db.Where("kind = ?", store.KindRecalc).First(&e)
893	if len(e.PayloadSealed) != 0 {
894	t.Fatal("recalc marker carries a payload; it must not")
895	}
896	if e.DerivedCount != 1200 {
897	t.Fatalf("DerivedCount = %d, want 1200", e.DerivedCount)
898	}
899	}
900	```
901
902	The executor writes `openTestDB`, `make32` and `checkpoint` helpers in
903	`internal/sheets/store/helpers_test.go`. `checkpoint` runs
904	`PRAGMA wal_checkpoint(TRUNCATE)` so the assertion sees settled bytes;
905	without it the test can pass because the data is still in memory, which
906	would be a gate that agrees with itself.
907
908	- [ ] Step 2: Run and watch it fail
909
910	Run: `go test ./internal/sheets/store/ -v`
911	Expected: FAIL — undefined.
912
913	- [ ] Step 3: Implement
914
915	Create `internal/sheets/store/events.go`:
916
917	```go
918	// Package store owns persistence: cells, rows, columns, formats and the
919	// append-only event log.
920	package store
921
922	import (
923	"encoding/json"
924	"time"
925
926	"gorm.io/gorm"
927
928	"amadan.net/oficina/sheets/internal/sheets/seal"
929	)
930
931	// Event kinds. Two, deliberately: conflating them is what made an earlier
932	// draft of the leak list wrong.
933	const (
934	KindInput = "input"
935	KindRecalc = "recalc"
936	)
937
938	// Event is the append-only log. It does four jobs — live-sync fan-out,
939	// version history, undo, and audit — which is why the storage model pays.
940	type Event struct {
941	ID uint64 `gorm:"primaryKey"`
942	Kind string `gorm:"index;size:16"`
943	TabID uint64 `gorm:"index"`
944	ActorID uint64
945	PayloadSealed []byte // seal purpose "event.payload"; empty for recalc
946	TriggerID uint64 // recalc only: the input event that caused it
947	DerivedCount int // recalc only: how many cells the pass touched
948	CreatedAt time.Time
949	}
950
951	// InputEvent is a person's edit, before sealing.
952	type InputEvent struct {
953	TabID, RowID, ColID uint64
954	ActorID uint64
955	OldRaw, NewRaw string
956	}
957
958	type inputPayload struct {
959	RowID uint64 `json:"r"`
960	ColID uint64 `json:"c"`
961	OldRaw string `json:"o"`
962	NewRaw string `json:"n"`
963	}
964
965	// AppendInput writes a sealed input event. Old and new raw input are user
966	// content, so the payload is sealed; the row's shape is not.
967	func AppendInput(tx gorm.DB, s seal.Sealer, e InputEvent) error {
968	body, err := json.Marshal(inputPayload{
969	RowID: e.RowID, ColID: e.ColID, OldRaw: e.OldRaw, NewRaw: e.NewRaw,
970	})
971	if err != nil {
972	return err
973	}
974	sealed, err := s.Seal("event.payload", body)
975	if err != nil {
976	return err
977	}
978	return tx.Create(&Event{
979	Kind: KindInput, TabID: e.TabID, ActorID: e.ActorID,
980	PayloadSealed: sealed,
981	}).Error
982	}
983
984	// AppendRecalc records that a recalculation pass ran and how wide it was.
985	// It carries no values: version history replays inputs and recomputes, which
986	// is slower to restore and the only version that stays honest at scale.
987	func AppendRecalc(tx *gorm.DB, triggerID uint64, derived int) error {
988	return tx.Create(&Event{
989	Kind: KindRecalc, TriggerID: triggerID, DerivedCount: derived,
990	}).Error
991	}
992	```
993
994	- [ ] Step 4: Run and watch it pass
995
996	Run: `go test ./internal/sheets/store/ -v`
997	Expected: PASS, including `TestInputEventIsSealedOnDisk`.
998
999	- [ ] Step 5: Prove the gate can fail
1000
1001	A gate nobody has seen fail is a hope. Temporarily change `AppendInput` to
1002	store `body` instead of `sealed`, re-run, and confirm
1003	`TestInputEventIsSealedOnDisk` FAILS with a plaintext report. Then revert.
1004
1005	Run: `go test ./internal/sheets/store/ -run TestInputEventIsSealedOnDisk -v`
1006	Expected while broken: FAIL — `plaintext "CANARY-CELL-VALUE-8f3a" found in test.db`
1007
1008	- [ ] Step 6: Commit
1009
1010	```bash
1011	git add internal/sheets/store/
1012	git commit -m "The event log: sealed input events, valueless recalc markers"
1013	```
1014
1015	---
1016
1017	### Task 5: Lexing and parsing formulas
1018
1019	Files:
1020	- Create: `internal/sheets/calc/token.go`, `internal/sheets/calc/ast.go`,
1021	`internal/sheets/calc/parse.go`
1022	- Create: `internal/sheets/calc/parse_test.go`
1023
1024	Interfaces:
1025	- Consumes: `calc.ParseColLabel` (task 3).
1026	- Produces:
1027	- `calc.Parse(src string) (Node, error)`
1028	- Node types: `Num`, `Str`, `Bool`, `Ref`, `Range`, `Binary`, `Unary`, `Call`
1029	- `Ref{ColPos, RowPos int; AbsCol, AbsRow bool}` — positions at parse
1030	time; task 6 binds them to stable ids.
1031
1032	- [ ] Step 1: Write the failing parser test
1033
1034	```go
1035	package calc
1036
1037	import "testing"
1038
1039	func TestParseArithmeticPrecedence(t *testing.T) {
1040	n, err := Parse("1+2*3")
1041	if err != nil {
1042	t.Fatal(err)
1043	}
1044	b, ok := n.(*Binary)
1045	if !ok \|\| b.Op != "+" {
1046	t.Fatalf("root = %T %v, want Binary +", n, n)
1047	}
1048	r, ok := b.Right.(*Binary)
1049	if !ok \|\| r.Op != "*" {
1050	t.Fatal("multiplication did not bind tighter than addition")
1051	}
1052	}
1053
1054	func TestParseAbsoluteAndRelativeRefs(t *testing.T) {
1055	n, err := Parse("$A$1")
1056	if err != nil {
1057	t.Fatal(err)
1058	}
1059	r := n.(*Ref)
1060	if !r.AbsCol \|\| !r.AbsRow {
1061	t.Fatal("$A$1 did not parse as fully absolute")
1062	}
1063	n2, _ := Parse("A1")
1064	r2 := n2.(*Ref)
1065	if r2.AbsCol \|\| r2.AbsRow {
1066	t.Fatal("A1 parsed as absolute")
1067	}
1068	if r2.ColPos != 0 \|\| r2.RowPos != 0 {
1069	t.Fatalf("A1 = col %d row %d, want 0,0", r2.ColPos, r2.RowPos)
1070	}
1071	}
1072
1073	func TestParseRangeAndCall(t *testing.T) {
1074	n, err := Parse("SUM(A1:B10)")
1075	if err != nil {
1076	t.Fatal(err)
1077	}
1078	c, ok := n.(*Call)
1079	if !ok \|\| c.Name != "SUM" {
1080	t.Fatalf("root = %T, want Call SUM", n)
1081	}
1082	if _, ok := c.Args[0].(*Range); !ok {
1083	t.Fatalf("arg 0 = %T, want Range", c.Args[0])
1084	}
1085	}
1086
1087	func TestParseRejectsGarbage(t *testing.T) {
1088	for _, src := range []string{"1+", "(1", "SUM(", "A1:", "*"} {
1089	if _, err := Parse(src); err == nil {
1090	t.Errorf("Parse(%q) succeeded, want error", src)
1091	}
1092	}
1093	}
1094	```
1095
1096	- [ ] Step 2: Run and watch it fail
1097
1098	Run: `go test ./internal/sheets/calc/ -run TestParse -v`
1099	Expected: FAIL — undefined.
1100
1101	- [ ] Step 3: Implement the AST
1102
1103	Create `internal/sheets/calc/ast.go`:
1104
1105	```go
1106	package calc
1107
1108	// Node is any expression node.
1109	type Node interface{ node() }
1110
1111	type Num struct{ V float64 }
1112	type Str struct{ V string }
1113	type Bool struct{ V bool }
1114
1115	// Ref is a single-cell reference as written. Positions are what the source
1116	// says; binding to stable ids happens in the reference pass, because
1117	// relative references are positional offsets and must stay that way until
1118	// a paste anchor is known.
1119	type Ref struct {
1120	ColPos, RowPos int
1121	AbsCol, AbsRow bool
1122	}
1123
1124	type Range struct{ From, To *Ref }
1125
1126	type Binary struct {
1127	Op string
1128	Left, Right Node
1129	}
1130
1131	type Unary struct {
1132	Op string
1133	X Node
1134	}
1135
1136	type Call struct {
1137	Name string
1138	Args []Node
1139	}
1140
1141	func (*Num) node() {}
1142	func (*Str) node() {}
1143	func (*Bool) node() {}
1144	func (*Ref) node() {}
1145	func (*Range) node() {}
1146	func (*Binary) node() {}
1147	func (*Unary) node() {}
1148	func (*Call) node() {}
1149	```
1150
1151	- [ ] Step 4: Implement the lexer and parser
1152
1153	The executor writes `token.go` (a scanner producing typed tokens for
1154	numbers, strings, identifiers, references, operators, parens, commas and
1155	colons) and `parse.go` (precedence-climbing over `^`, then `*` `/`, then
1156	`+` `-`, then comparison `= <> < <= > >=`, with `&` for concatenation at the
1157	lowest arithmetic level). Reference tokens are recognised by the pattern
1158	`\$?[A-Za-z]{1,3}\$?[0-9]+` and converted with `ParseColLabel`.
1159
1160	Keep each file under the 300-line cap. If `parse.go` approaches it, split
1161	the function-call argument parsing into `parse_call.go` rather than growing
1162	the file — a new concern gets a new small file, never more growth of the
1163	biggest one.
1164
1165	- [ ] Step 5: Run and watch it pass
1166
1167	Run: `go test ./internal/sheets/calc/ -v`
1168	Expected: PASS.
1169
1170	- [ ] Step 6: Commit
1171
1172	```bash
1173	git add internal/sheets/calc/
1174	git commit -m "Formula lexer, AST and precedence-climbing parser"
1175	```
1176
1177	---
1178
1179	### Task 6: Reference translation — what stable ids do NOT buy
1180
1181	Files:
1182	- Create: `internal/sheets/calc/translate.go`, `internal/sheets/calc/translate_test.go`
1183
1184	Interfaces:
1185	- Consumes: AST types (task 5).
1186	- Produces:
1187	- `calc.Translate(n Node, dRow, dCol int) Node` — offsets relative refs,
1188	leaves absolute ones alone.
1189	- `calc.Bind(n Node, tab *TabIndex) (Node, error)` — positions → stable ids.
1190	- `calc.BoundRef{RowID, ColID uint64; AbsCol, AbsRow bool}`
1191
1192	Why this task exists: an earlier draft of the spec claimed stable ids
1193	removed the reference-rewriting pass. They remove it for **insert and delete
1194	only**. Relative references are positional offsets — copying `=A1` from B1 to
1195	B2 must produce `=A2` — so copy and fill need a translation pass at paste
1196	time. The pass moved; it did not disappear. Spec §4.2 has the full table.
1197
1198	- [ ] Step 1: Write the failing translation test
1199
1200	```go
1201	package calc
1202
1203	import "testing"
1204
1205	func TestTranslateRelativeRef(t *testing.T) {
1206	n, _ := Parse("A1")
1207	got := Translate(n, 1, 0).(*Ref) // copied down one row
1208	if got.RowPos != 1 \|\| got.ColPos != 0 {
1209	t.Fatalf("=A1 copied down = row %d col %d, want 1,0", got.RowPos, got.ColPos)
1210	}
1211	}
1212
1213	func TestTranslateLeavesAbsoluteAlone(t *testing.T) {
1214	n, _ := Parse("$A$1")
1215	got := Translate(n, 5, 5).(*Ref)
1216	if got.RowPos != 0 \|\| got.ColPos != 0 {
1217	t.Fatal("$A$1 moved under translation")
1218	}
1219	}
1220
1221	func TestTranslateMixedRef(t *testing.T) {
1222	n, _ := Parse("$A1") // absolute column, relative row
1223	got := Translate(n, 3, 4).(*Ref)
1224	if got.ColPos != 0 {
1225	t.Fatal("$A moved")
1226	}
1227	if got.RowPos != 3 {
1228	t.Fatalf("relative row = %d, want 3", got.RowPos)
1229	}
1230	}
1231
1232	func TestTranslateDescendsIntoCalls(t *testing.T) {
1233	n, _ := Parse("SUM(A1:A5)+$B$1")
1234	got := Translate(n, 2, 0).(*Binary)
1235	rng := got.Left.(Call).Args[0].(Range)
1236	if rng.From.RowPos != 2 \|\| rng.To.RowPos != 6 {
1237	t.Fatalf("range = %d:%d, want 2:6", rng.From.RowPos, rng.To.RowPos)
1238	}
1239	if got.Right.(*Ref).RowPos != 0 {
1240	t.Fatal("$B$1 moved")
1241	}
1242	}
1243	```
1244
1245	- [ ] Step 2: Run and watch it fail
1246
1247	Run: `go test ./internal/sheets/calc/ -run TestTranslate -v`
1248	Expected: FAIL — `undefined: Translate`.
1249
1250	- [ ] Step 3: Implement
1251
1252	Create `internal/sheets/calc/translate.go`:
1253
1254	```go
1255	package calc
1256
1257	// Translate offsets every relative reference in n by (dRow, dCol) and
1258	// returns a new tree. Absolute components are untouched — that is the whole
1259	// meaning of the dollar sign.
1260	//
1261	// This is the pass stable row and column identity does NOT remove. Ids make
1262	// insert and delete free; copy and fill are positional by definition, so the
1263	// offset has to be applied at paste time. See spec section 4.2.
1264	func Translate(n Node, dRow, dCol int) Node {
1265	switch t := n.(type) {
1266	case *Ref:
1267	out := *t
1268	if !out.AbsRow {
1269	out.RowPos += dRow
1270	}
1271	if !out.AbsCol {
1272	out.ColPos += dCol
1273	}
1274	return &out
1275	case *Range:
1276	return &Range{
1277	From: Translate(t.From, dRow, dCol).(*Ref),
1278	To: Translate(t.To, dRow, dCol).(*Ref),
1279	}
1280	case *Binary:
1281	return &Binary{Op: t.Op,
1282	Left: Translate(t.Left, dRow, dCol),
1283	Right: Translate(t.Right, dRow, dCol)}
1284	case *Unary:
1285	return &Unary{Op: t.Op, X: Translate(t.X, dRow, dCol)}
1286	case *Call:
1287	args := make([]Node, len(t.Args))
1288	for i, a := range t.Args {
1289	args[i] = Translate(a, dRow, dCol)
1290	}
1291	return &Call{Name: t.Name, Args: args}
1292	default:
1293	return n // Num, Str, Bool have no references
1294	}
1295	}
1296	```
1297
1298	- [ ] Step 4: Run and watch it pass
1299
1300	Run: `go test ./internal/sheets/calc/ -run TestTranslate -v`
1301	Expected: PASS.
1302
1303	- [ ] Step 5: Write the per-operation contract as an executable table
1304
1305	The spec's §4.2 table is the contract. Encode it so a later change to any
1306	operation has to argue with a test.
1307
1308	```go
1309	// Each row restates spec section 4.2. If an implementation changes one of
1310	// these behaviours, this table is what it has to argue with.
1311	func TestReferenceBehaviourPerOperation(t *testing.T) {
1312	cases := []struct {
1313	op, src, want string
1314	dRow, dCol int
1315	}{
1316	{op: "copy down", src: "A1", dRow: 1, want: "A2"},
1317	{op: "copy right", src: "A1", dCol: 1, want: "B1"},
1318	{op: "copy absolute", src: "$A$1", dRow: 9, dCol: 9, want: "$A$1"},
1319	{op: "fill range", src: "SUM(A1:A3)", dRow: 1, want: "SUM(A2:A4)"},
1320	}
1321	for _, tc := range cases {
1322	n, err := Parse(tc.src)
1323	if err != nil {
1324	t.Fatalf("%s: parse %q: %v", tc.op, tc.src, err)
1325	}
1326	if got := Render(Translate(n, tc.dRow, tc.dCol)); got != tc.want {
1327	t.Errorf("%s: %q -> %q, want %q", tc.op, tc.src, got, tc.want)
1328	}
1329	}
1330	}
1331	```
1332
1333	The executor writes `Render(Node) string` in `translate.go` — the inverse of
1334	`Parse` for the subset the tests use. It is worth having anyway: it is what
1335	the formula bar shows.
1336
1337	- [ ] Step 6: Run, then commit
1338
1339	```bash
1340	go test ./internal/sheets/calc/ -v
1341	git add internal/sheets/calc/
1342	git commit -m "Reference translation: the pass stable ids do not remove"
1343	```
1344
1345	---
1346
1347	### Task 7: The evaluator, the dependency graph and dirty-set recalc
1348
1349	Files:
1350	- Create: `internal/sheets/calc/errors.go`, `internal/sheets/calc/eval.go`,
1351	`internal/sheets/calc/graph.go`
1352	- Create: `internal/sheets/calc/eval_test.go`, `internal/sheets/calc/graph_test.go`
1353
1354	Interfaces:
1355	- Consumes: AST (task 5), `Bind` (task 6).
1356	- Produces:
1357	- `calc.Value` — `struct{ Kind ValueKind; Num float64; Str string; Bool bool; Err ErrKind }`
1358	- `calc.Eval(n Node, env Env) Value`
1359	- `calc.Env` interface: `CellValue(rowID, colID uint64) Value`
1360	- `calc.Graph` with `SetFormula`, `Dirty`, `Recalc` returning the touched count.
1361
1362	- [ ] Step 1: Write the failing evaluator tests
1363
1364	```go
1365	package calc
1366
1367	import "testing"
1368
1369	func TestEvalArithmetic(t *testing.T) {
1370	v := Eval(mustParse(t, "1+2*3"), emptyEnv{})
1371	if v.Kind != KindNum \|\| v.Num != 7 {
1372	t.Fatalf("1+2*3 = %v, want 7", v)
1373	}
1374	}
1375
1376	func TestDivideByZeroIsAnExcelError(t *testing.T) {
1377	v := Eval(mustParse(t, "1/0"), emptyEnv{})
1378	if v.Kind != KindErr \|\| v.Err != ErrDiv0 {
1379	t.Fatalf("1/0 = %v, want #DIV/0!", v)
1380	}
1381	}
1382
1383	// Errors propagate rather than being swallowed: =A1+1 where A1 is #REF!
1384	// must be #REF!, not 1. XLSX round-trips these, so they are values.
1385	func TestErrorsPropagate(t *testing.T) {
1386	env := mapEnv{{1, 1}: {Kind: KindErr, Err: ErrRef}}
1387	v := Eval(mustParse(t, "A1+1"), env)
1388	if v.Kind != KindErr \|\| v.Err != ErrRef {
1389	t.Fatalf("propagation = %v, want #REF!", v)
1390	}
1391	}
1392	```
1393
1394	- [ ] Step 2: Write the failing graph tests
1395
1396	```go
1397	func TestDirtySetTouchesOnlyDependents(t *testing.T) {
1398	g := NewGraph()
1399	g.SetFormula(cellB1, mustParse(t, "A1*2"))
1400	g.SetFormula(cellC1, mustParse(t, "B1+1"))
1401	g.SetFormula(cellZ9, mustParse(t, "42")) // unrelated
1402
1403	touched := g.Recalc(cellA1, env)
1404	if touched != 2 {
1405	t.Fatalf("touched %d cells, want 2 (B1, C1) — Z9 must not recalc", touched)
1406	}
1407	}
1408
1409	// A cycle must return an error value, not hang. This test has a timeout
1410	// because the failure mode of getting it wrong is a wedged process.
1411	func TestCycleDetection(t *testing.T) {
1412	g := NewGraph()
1413	g.SetFormula(cellA1, mustParse(t, "B1"))
1414	g.SetFormula(cellB1, mustParse(t, "A1"))
1415	done := make(chan Value, 1)
1416	go func() { done <- g.Value(cellA1, env) }()
1417	select {
1418	case v := <-done:
1419	if v.Kind != KindErr \|\| v.Err != ErrCircular {
1420	t.Fatalf("cycle = %v, want #CIRCULAR!", v)
1421	}
1422	case <-time.After(2 * time.Second):
1423	t.Fatal("cycle detection hung")
1424	}
1425	}
1426	```
1427
1428	- [ ] Step 3: Run and watch both fail
1429
1430	Run: `go test ./internal/sheets/calc/ -run 'TestEval\|TestDirty\|TestCycle' -v`
1431	Expected: FAIL — undefined.
1432
1433	- [ ] Step 4: Implement `errors.go`
1434
1435	```go
1436	package calc
1437
1438	// ErrKind is an Excel error value. These are values, not Go errors: they
1439	// live in cells, propagate through arithmetic, and round-trip through XLSX.
1440	type ErrKind uint8
1441
1442	const (
1443	ErrNone ErrKind = iota
1444	ErrDiv0
1445	ErrValue
1446	ErrRef
1447	ErrName
1448	ErrNum
1449	ErrNA
1450	ErrCircular
1451	)
1452
1453	func (e ErrKind) String() string {
1454	switch e {
1455	case ErrDiv0:
1456	return "#DIV/0!"
1457	case ErrValue:
1458	return "#VALUE!"
1459	case ErrRef:
1460	return "#REF!"
1461	case ErrName:
1462	return "#NAME?"
1463	case ErrNum:
1464	return "#NUM!"
1465	case ErrNA:
1466	return "#N/A"
1467	case ErrCircular:
1468	return "#CIRCULAR!"
1469	}
1470	return ""
1471	}
1472	```
1473
1474	- [ ] Step 5: Implement the evaluator and graph
1475
1476	`eval.go` walks the AST. Rules the executor must honour:
1477
1478	- Errors propagate. Any operand that is an error makes the result that
1479	error, left-most wins.
1480	- Numbers are IEEE doubles end to end (global constraint). No rounding
1481	at the evaluator boundary.
1482	- `Env` is an interface, so the graph can supply cached values and tests
1483	can supply a map. Nothing in `calc` imports `store`.
1484
1485	`graph.go` holds precedent and dependent edges keyed by `(rowID, colID)`.
1486	`Recalc` marks dependents dirty transitively, then evaluates in topological
1487	order, returning the number of cells touched. Cycle detection is a colour
1488	mark during the topological walk; a node re-entered while grey yields
1489	`ErrCircular` rather than recursing.
1490
1491	Cap the dirty set per pass. Past the cap, stop and report — the caller
1492	tells the client to refetch its viewport rather than shipping a huge delta.
1493	Put the cap and its reason in a comment beside the constant.
1494
1495	- [ ] Step 6: Run and watch them pass
1496
1497	Run: `go test ./internal/sheets/calc/ -v`
1498	Expected: PASS, cycle test completing well inside its timeout.
1499
1500	- [ ] Step 7: Commit
1501
1502	```bash
1503	git add internal/sheets/calc/
1504	git commit -m "Evaluator, precedent graph, dirty-set recalc, cycle detection"
1505	```
1506
1507	---
1508
1509	### Task 8: The function library and the golden-vector harness
1510
1511	Files:
1512	- Create: `internal/sheets/calc/fn_math.go`, `fn_text.go`, `fn_logic.go`,
1513	`fn_lookup.go`, `fn_date.go`, `registry.go`
1514	- Create: `tools/gencorpus/main.go`
1515	- Create: `testdata/corpus/formulas.json`, `testdata/corpus/volatile-excluded.txt`
1516	- Create: `internal/sheets/calc/corpus_test.go`
1517
1518	Interfaces:
1519	- Consumes: `Eval`, `Value` (task 7).
1520	- Produces: `calc.Register(name string, fn Func)` and a populated registry.
1521	`Func` is `func(args []Value) Value`.
1522
1523	Scope: ~30 functions in this task, chosen because they cover most real
1524	business spreadsheets: SUM, AVERAGE, COUNT, COUNTA, MIN, MAX, ROUND,
1525	ROUNDUP, ROUNDDOWN, ABS, SQRT, POWER, MOD, IF, IFS, AND, OR, NOT, IFERROR,
1526	CONCAT, LEFT, RIGHT, MID, LEN, TRIM, UPPER, LOWER, VLOOKUP, INDEX, MATCH.
1527	The remaining ~90 follow the same pattern and are added in batches later.
1528
1529	- [ ] Step 1: Write the corpus generator
1530
1531	`tools/gencorpus/main.go` drives LibreOffice headless over a list of
1532	expressions and records what a real spreadsheet returns. **It is run rarely,
1533	by a human who has LibreOffice, and its output is committed.** The test
1534	reads the committed file — a test that shells out to LibreOffice is a test
1535	that fails on a machine without it, which is the same reason rastrillo
1536	vendors axe rather than fetching it.
1537
1538	```go
1539	// Command gencorpus regenerates testdata/corpus/formulas.json from a real
1540	// spreadsheet. Run it when adding functions; commit the result.
1541	//
1542	// go run ./tools/gencorpus -out testdata/corpus/formulas.json
1543	//
1544	// Requires `soffice` on PATH. CI does not run this — CI reads the committed
1545	// file, which is why the file is hash-pinned by TestCorpusIsPinned.
1546	package main
1547	```
1548
1549	The generator writes a `.ods` with one expression per row, converts with
1550	`soffice --headless --convert-to csv`, and pairs each expression with its
1551	computed value.
1552
1553	- [ ] Step 2: Handle the volatiles, and check what is underneath you
1554
1555	Volatile functions — `RAND`, `RANDBETWEEN`, `NOW`, `TODAY` — produce a
1556	different value on every recalculation. In a golden corpus that makes the
1557	contract unfalsifiable, and **the first person to hit it deletes the check
1558	rather than the nondeterminism.**
1559
1560	Two rules, and the second is the one that is easy to miss:
1561
1562	1. The exclusion list is a file somebody edits, not a filter somebody
1563	widens. `testdata/corpus/volatile-excluded.txt`, one function name per
1564	line, each with a comment saying why. A test asserts every name in it is
1565	a real registered function, so the list cannot rot into a wildcard.
1566	2. Pin the clock, then check nothing beneath you re-derives a time. A
1567	correct fix at the wrong layer still fails: docs-e6 ruled "fail loudly"
1568	on an entropy source, the implementer did exactly that, and a dependency
1569	swallowed the error one layer down and substituted a constant seed
1570	(2026-08-30). So after pinning, assert it: evaluate `NOW()` twice across
1571	a simulated clock advance and require the same answer inside one
1572	recalculation pass.
1573
1574	```go
1575	// A recalculation pass sees one instant. If anything under us reads the
1576	// wall clock directly, this fails — which is the point. A pinned clock that
1577	// something beneath re-derives is a pin that only looks like one.
1578	func TestClockIsPinnedWithinAPass(t *testing.T) {
1579	env := newEnvAt(time.Date(2026, 8, 30, 12, 0, 0, 0, time.UTC))
1580	first := Eval(mustParse(t, "NOW()"), env)
1581	env.advanceWallClock(time.Hour) // does not touch the pass's instant
1582	second := Eval(mustParse(t, "NOW()"), env)
1583	if first.Num != second.Num {
1584	t.Fatal("NOW() moved inside one pass; something is reading the wall clock")
1585	}
1586	}
1587	```
1588
1589	- [ ] Step 3: Write the corpus test
1590
1591	```go
1592	func TestAgainstGoldenCorpus(t *testing.T) {
1593	entries := loadCorpus(t) // reads testdata/corpus/formulas.json
1594	excluded := loadExclusions(t)
1595	for _, e := range entries {
1596	if excluded[e.Function] {
1597	continue
1598	}
1599	got := Eval(mustParse(t, e.Expr), corpusEnv)
1600	if !valuesMatch(got, e.Want) {
1601	t.Errorf("%s: %s = %v, LibreOffice says %v", e.Function, e.Expr, got, e.Want)
1602	}
1603	}
1604	}
1605
1606	// The corpus is a contract. If it changes, that is a deliberate act with a
1607	// regenerated hash, never a silent drift.
1608	func TestCorpusIsPinned(t *testing.T) {
1609	sum := sha256File(t, "testdata/corpus/formulas.json")
1610	if sum != corpusSHA256 {
1611	t.Fatalf("corpus changed: %s\nIf deliberate, update corpusSHA256.", sum)
1612	}
1613	}
1614
1615	// Every excluded name must be a real function, so the list cannot become a
1616	// wildcard that quietly swallows new failures.
1617	func TestExclusionsAreRealFunctions(t *testing.T) {
1618	for name := range loadExclusions(t) {
1619	if _, ok := registry[name]; !ok {
1620	t.Errorf("%s is excluded but is not a registered function", name)
1621	}
1622	}
1623	}
1624	```
1625
1626	- [ ] Step 4: Run, fill in the pin, run again
1627
1628	```bash
1629	go run ./tools/gencorpus -out testdata/corpus/formulas.json # needs soffice
1630	go test ./internal/sheets/calc/ -run TestCorpusIsPinned -v # prints the sum
1631	# paste the sum into corpusSHA256, re-run
1632	go test ./internal/sheets/calc/ -v
1633	```
1634	Expected: PASS.
1635
1636	- [ ] Step 5: Implement the functions, family by family
1637
1638	One file per family, each under the 300-line cap. Each function is
1639	registered by name in `registry.go`. A function receives evaluated `Value`
1640	arguments and returns a `Value`; argument-count and type errors return
1641	`ErrValue`, an unknown name returns `ErrName`.
1642
1643	Add functions in small commits — five or six at a time, each with its corpus
1644	entries — rather than one large commit. A failing corpus row then points at
1645	a small change.
1646
1647	- [ ] Step 6: Commit
1648
1649	```bash
1650	git add internal/sheets/calc/ tools/ testdata/
1651	git commit -m "Function library, and a golden corpus that cannot drift"
1652	```
1653
1654	---
1655
1656	### Task 9: Number formatting — Go formats, the client paints
1657
1658	Files:
1659	- Create: `internal/sheets/format/format.go`, `internal/sheets/format/format_test.go`
1660
1661	Interfaces:
1662	- Consumes: `calc.Value` (task 7).
1663	- Produces: `format.Display(v calc.Value, numFmt string) string`
1664
1665	Why this task exists: ruling 2 put one engine behind cell values. A
1666	value is not what a cell shows. If the browser renders a currency string
1667	from a raw number, **number formatting becomes a second implementation of
1668	the same truth** — the exact bug class ruling 2 prevents, one layer down. So
1669	Go produces the display string and the client paints what it is handed.
1670	Formats are still edited as structured records; the client never sends a
1671	rendered string.
1672
1673	- [ ] Step 1: Write the failing test
1674
1675	```go
1676	package format
1677
1678	import "testing"
1679
1680	func TestDisplay(t *testing.T) {
1681	for _, tc := range []struct {
1682	numFmt string
1683	num float64
1684	want string
1685	}{
1686	{"General", 1234.5678, "1234.5678"},
1687	{"0.00", 1234.5678, "1234.57"},
1688	{"#,##0.00", 1234.5678, "1,234.57"},
1689	{"0%", 0.075, "8%"},
1690	{"0.0%", 0.075, "7.5%"},
1691	{`"€"#,##0.00`, 1234.5, "€1,234.50"},
1692	} {
1693	got := Display(numValue(tc.num), tc.numFmt)
1694	if got != tc.want {
1695	t.Errorf("Display(%v, %q) = %q, want %q", tc.num, tc.numFmt, got, tc.want)
1696	}
1697	}
1698	}
1699
1700	// An error value displays as its Excel text regardless of number format —
1701	// a currency format must not render #DIV/0! as a currency amount.
1702	func TestDisplayErrorIgnoresNumberFormat(t *testing.T) {
1703	if got := Display(errValue(calc.ErrDiv0), "#,##0.00"); got != "#DIV/0!" {
1704	t.Fatalf("error under currency format = %q", got)
1705	}
1706	}
1707	```
1708
1709	- [ ] Step 2: Run and watch it fail
1710
1711	Run: `go test ./internal/sheets/format/ -v`
1712	Expected: FAIL — undefined.
1713
1714	- [ ] Step 3: Implement
1715
1716	Support the format subset the tests cover plus dates. Rounding is
1717	half-away-from-zero, matching Excel rather than Go's default banker-ish
1718	`strconv` behaviour — write a test for `0.005` at `"0.00"` and pin it.
1719
1720	- [ ] Step 4: Run and commit
1721
1722	```bash
1723	go test ./internal/sheets/format/ -v
1724	git add internal/sheets/format/
1725	git commit -m "Number formatting in Go, so the client paints strings"
1726	```
1727
1728	---
1729
1730	### Task 10: The sheet handle, eviction, and crash recovery
1731
1732	Files:
1733	- Create: `internal/sheets/store/handle.go`, `internal/sheets/store/handle_test.go`
1734
1735	Interfaces:
1736	- Consumes: models (task 3), events (task 4), `calc.Graph` (task 7).
1737	- Produces:
1738	- `store.Open(db, sealer, tabID) (*Handle, error)`
1739	- `(*Handle).Set(rowID, colID uint64, raw string, actor uint64) error`
1740	- `(*Handle).Value(rowID, colID uint64) calc.Value`
1741	- `(*Handle).Release()`
1742
1743	- [ ] Step 1: Write the failing crash-recovery test
1744
1745	This is the test that matters. A crash between the input write and its
1746	dependents' rewrite leaves cached values disagreeing with inputs — silently,
1747	and forever, unless the handle detects it.
1748
1749	```go
1750	func TestStaleCacheIsRecomputedOnOpen(t *testing.T) {
1751	db, sealer, tabID := seeded(t)
1752
1753	h, _ := store.Open(db, sealer, tabID)
1754	h.Set(rowB, colA, "=A1*2", actor)
1755	h.Release()
1756
1757	// Simulate a crash mid-recalc: bump A1's input version without
1758	// rewriting B1's cache. This is exactly what a panic between the two
1759	// writes leaves behind.
1760	db.Model(&sheets.Cell{}).
1761	Where("row_id = ? AND col_id = ?", rowA, colA).
1762	Updates(map[string]any{"input_version": 99})
1763
1764	h2, err := store.Open(db, sealer, tabID)
1765	if err != nil {
1766	t.Fatal(err)
1767	}
1768	defer h2.Release()
1769
1770	var b sheets.Cell
1771	db.Where("row_id = ? AND col_id = ?", rowB, colA).First(&b)
1772	if b.CacheVersion < 99 {
1773	t.Fatalf("stale cache served: CacheVersion %d < InputVersion 99", b.CacheVersion)
1774	}
1775	}
1776
1777	// A tab past the cap fails honestly rather than dying slowly.
1778	func TestCellCapIsAnHonestError(t *testing.T) {
1779	_, err := store.Open(db, sealer, oversizedTabID)
1780	if !errors.Is(err, store.ErrTabTooLarge) {
1781	t.Fatalf("oversized tab: %v, want ErrTabTooLarge", err)
1782	}
1783	}
1784	```
1785
1786	- [ ] Step 2: Run and watch it fail
1787
1788	Run: `go test ./internal/sheets/store/ -run 'TestStaleCache\|TestCellCap' -v`
1789	Expected: FAIL — undefined.
1790
1791	- [ ] Step 3: Implement
1792
1793	`handle.go` holds:
1794
1795	```go
1796	// MaxCellsPerTab is a hard cap.
1797	//
1798	// The handle holds a decrypted tab plus its dependency graph in memory, so
1799	// "hold it in RAM" is only true up to a number. 200,000 keeps a tab in tens
1800	// of megabytes on a small shared box where many instances live. Google's
1801	// limit is 10M and essentially nobody reaches it, so this trades a limit
1802	// almost nobody meets for a memory ceiling everybody benefits from. Past it,
1803	// an honest error rather than a slow death.
1804	const MaxCellsPerTab = 200_000
1805	```
1806
1807	`Open` counts cells, refuses past the cap, decrypts, builds the graph, and
1808	**recalculates any cell whose `CacheVersion` trails its `InputVersion`
1809	before serving a single read.** `Set` writes the input and a dirty marker in
1810	one transaction, then recalculates dirty-forward and clears the marker.
1811
1812	Handles are refcounted and evicted when idle; `Release` decrements.
1813
1814	- [ ] Step 4: Run, then run the plaintext gate against a populated handle
1815
1816	```bash
1817	go test ./internal/sheets/store/ -v
1818	```
1819	Add an assertion to the handle test that calls `seal.AssertNoPlaintext`
1820	after a `Set` and a checkpoint — the handle is the busiest writer, so it is
1821	the most valuable place to run the gate.
1822
1823	- [ ] Step 5: Commit
1824
1825	```bash
1826	git add internal/sheets/store/
1827	git commit -m "The sheet handle: eviction, cell cap, and crash recovery"
1828	```
1829
1830	---
1831
1832	### Task 11: XLSX import and export
1833
1834	Files:
1835	- Create: `internal/sheets/xlsx/import.go`, `internal/sheets/xlsx/export.go`
1836	- Create: `internal/sheets/xlsx/roundtrip_test.go`
1837	- Create: `testdata/corpus/xlsx/*.xlsx`
1838
1839	Interfaces:
1840	- Consumes: models (task 3), `format.Display` (task 9), handle (task 10).
1841	- Produces:
1842	- `xlsx.Import(r io.Reader, h *store.Handle) error`
1843	- `xlsx.Export(h *store.Handle, w io.Writer) error`
1844
1845	- [ ] Step 1: Write the failing round-trip test
1846
1847	```go
1848	// import -> export -> import must be stable. A file we wrote must open in
1849	// Excel and LibreOffice; that half is checked by hand against the fixtures
1850	// and recorded, because CI cannot open Excel.
1851	func TestRoundTripIsStable(t *testing.T) {
1852	for _, name := range fixtureNames(t) {
1853	first := importFixture(t, name)
1854	var buf bytes.Buffer
1855	if err := xlsx.Export(first, &buf); err != nil {
1856	t.Fatalf("%s: export: %v", name, err)
1857	}
1858	second := importReader(t, &buf)
1859	if diff := diffTabs(first, second); diff != "" {
1860	t.Errorf("%s: round trip changed the sheet:\n%s", name, diff)
1861	}
1862	}
1863	}
1864
1865	// Fills imported from XLSX keep their original hex so a file we did not
1866	// author re-exports byte-identically in colour terms. The intent drives
1867	// display; the retained hex drives export until a user re-picks.
1868	func TestImportedFillHexIsRetained(t *testing.T) {
1869	h := importFixture(t, "fills.xlsx")
1870	f := formatOf(t, h, rowA, colA)
1871	if f.FillHex != "FF00B050" {
1872	t.Fatalf("FillHex = %q, want FF00B050", f.FillHex)
1873	}
1874	}
1875	```
1876
1877	- [ ] Step 2: Run and watch it fail
1878
1879	Run: `go test ./internal/sheets/xlsx/ -v`
1880	Expected: FAIL — undefined.
1881
1882	- [ ] Step 3: Implement
1883
1884	```bash
1885	go get github.com/xuri/excelize/v2@v2.11.0
1886	```
1887
1888	`excelize` is confined to this package. Nothing else in the tree imports it —
1889	its calc engine works against a file model rather than our dependency graph,
1890	so borrowing it would couple our storage to theirs and be slow.
1891
1892	Import maps cells, formulas, number formats and fills; **fill hex is
1893	retained verbatim** alongside the nearest OKLCH intent. Export resolves
1894	intent to concrete hex, or emits the retained hex when the user has not
1895	re-picked.
1896
1897	- [ ] Step 4: Route blobs through `blobs.Sealed`
1898
1899	Uploaded and generated files are whole spreadsheets. Unsealed on the S3
1900	backend, that is a plaintext spreadsheet in a bucket — the one rule defeated
1901	through a side door.
1902
1903	```go
1904	// ContentType is deliberately opaque. blobs.Sealed encrypts the bytes but
1905	// passes ContentType through as visible metadata, so a truthful
1906	// spreadsheet content type would announce exactly what the ciphertext is,
1907	// in a bucket that is otherwise opaque.
1908	const blobContentType = "application/octet-stream"
1909	```
1910
1911	Note also that the blob address is the ciphertext hash, so deduplication
1912	is per key, and `Ref.Size` is the sealed size — any quota or progress figure
1913	shown to a person must account for that or it will be visibly wrong.
1914
1915	- [ ] Step 5: Keep content out of job errors
1916
1917	Import runs as a background job. Its error text reaches the owner and the
1918	platform's logs, which we cannot grep.
1919
1920	```go
1921	// Errors carry coordinates and codes, never content. "cannot parse B7" is
1922	// a good error; quoting the cell's text into carlos logs ships user content
1923	// to storage we do not control.
1924	return fmt.Errorf("xlsx: cannot parse cell %s: %w", calc.A1(rowPos, colPos), errBadNumber)
1925	```
1926
1927	- [ ] Step 6: Run and commit
1928
1929	```bash
1930	go test ./internal/sheets/xlsx/ -v
1931	CGO_ENABLED=0 go build ./... && go vet ./... && go test ./...
1932	git add internal/sheets/xlsx/ testdata/
1933	git commit -m "XLSX import and export, sealed blobs, content-free errors"
1934	```
1935
1936	---
1937
1938	## Deferred to plan 2 (design-system dependent)
1939
1940	Named here so their absence is a decision rather than a gap: auth screens,
1941	the Jspreadsheet grid surface and its chrome, sharing UI, live sync over
1942	SSE, presence, the home contract calls, version-history UI, conditional
1943	formatting UI, comments, charts.
1944
1945	Gate 11 — computed value to painted output — belongs to plan 2 and is
1946	the gate most likely to be dropped as "hard to write" during execution.
1947	Naming it here is the defence: gates 4 and 7 prove the engine matches
1948	LibreOffice and that the client runs, and neither proves a person sees what
1949	the engine computed.
1950
1951	## Self-review notes
1952
1953	- Spec coverage: §4.1–§4.5, §5, §11 and §12 gates 1, 3, 4, 9, 10 are
1954	covered by tasks 1–11. §6–§10 and gates 2, 5, 6, 7, 8, 11 are plan 2.
1955	- Type consistency: `calc.Value` is produced in task 7 and consumed
1956	unchanged in 8, 9 and 11. `store.Handle` is produced in 10 and consumed in
1957	11. `seal.Sealer` is produced in 2 and consumed in 4 and 10.
1958	- Known soft spots, flagged rather than hidden: task 5 leaves the lexer
1959	and parser bodies to the executor with a described shape rather than full
1960	code — they are mechanical and long, and writing them out here would not
1961	reduce the risk. Task 8's generator is sketched rather than written
1962	because its exact shape depends on the LibreOffice version to hand.
1963