amadan

oficina / sheets Public

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

Plain git — no account needed to clone.

Download

Download this file

Sheets Core Implementation Plan

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

Goal: A headless, sealed spreadsheet engine — storage, formula evaluation, number formatting and XLSX round-tripping — with every trust gate live from the first commit.

Architecture: One Go binary on the CARLOS platform. Cells are sparse rows keyed by stable row/column identity, sealed at rest with a DEK from platform config. An append-only event log carries input events (never derived values). A formula engine — lexer, parser, evaluator, precedent graph with dirty-set propagation — owns all calculation; the client will later paint strings this engine produces. excelize is confined to XLSX I/O.

Tech Stack: Go 1.26, github.com/carlosframework/rastrillo v0.20.0 (GORM + chi + cgo-free SQLite via modernc), github.com/xuri/excelize/v2 v2.11.0, golang.org/x/crypto for HKDF. No CGO. No JS in this plan.

Spec: docs/superpowers/specs/2026-08-30-sheets-design.md

Framework pin: v0.20.0, tagged 2026-08-30 and verified on the module proxy. It carries ThemeCSS, ThemeNames, Layout and LayoutNames, which v0.19.0 did not — none of which this plan needs, but pinning the current release avoids a second bump the week after. Pair and the colour allocation entry point are not in it; they remain specified and unwritten, so the §10.3 shim stands. Re-copy static/tokens.css deliberately on adoption — it is frozen at scaffold time while partials upgrade with the module.

Scope boundary: This plan deliberately excludes everything the design system touches — auth screens, the grid surface, chrome, sharing UI, live sync. Design-system questions are paused pending rastrillo's own settling (Paul, 2026-08-30). Those become plan 2. Every task here is testable without a browser.

Global Constraints

Copied verbatim from the spec. Every task's requirements include these.

  • The one rule: "A sheet's contents are sealed at rest. The bucket never holds a plaintext cell." A plaintext value reaching the SQLite file is a bug, not a preference.
  • CGO_ENABLED=0 throughout. A cgo SQLite driver sneaking in is a bug. Never import glebarez/* or gorm.io/driver/sqlite; use rastrillo's db.
  • Migrations are additive-only. New code over an old DB is always safe. Never delete data to update.
  • Cell values are IEEE doubles end to end. Currency is a number format, not a type. The family's "a float never touches an amount" rule binds app-owned amounts (billing, quotas) only.
  • No cell content, comment text, sheet title or tab name in any log line, error string, job status message or metric label. Errors carry coordinates and codes. carlos logs is platform-held storage we cannot grep.
  • Never scope.Owned or scope.OwnedBy on a team-shared table. Both are single-column equality; our rows are team-shared, and Owned would return only the creator's rows — failing as a plausible empty list rather than an error.
  • 200,000 cells per tab, hard, with the reasoning in a comment beside the constant. An honest error, never a slow death.
  • Anything nondeterministic in a fixture path makes the contract unfalsifiable, and the first person to hit it removes the check rather than the nondeterminism. Pin clocks; make exclusion lists files somebody edits, not filters somebody widens.
  • A suite of gates that all live inside one layer is a suite that agrees with itself. At least one gate crosses from computed value to painted output.

---

File Structure

cmd/sheets/main.go            Thin dispatch. Platform contract only.
internal/sheets/
  app.go                      App struct, wiring, Resolve/Serve/Run.
  models.go                   GORM models. Schema lives here, nowhere else.
  seal/
    seal.go                   DEK, HKDF purpose keys, AES-GCM. ~150 lines.
    vectors_test.go           Golden envelope vectors.
  store/
    store.go                  Open/save cells, rows, cols, formats.
    events.go                 Append-only log: input events, recalc markers.
    handle.go                 In-memory sheet handle, eviction, dirty markers.
  calc/
    token.go                  Lexer. ~200 lines.
    ast.go                    AST node types. ~120 lines.
    parse.go                  Parser. ~250 lines.
    ref.go                    A1 ↔ stable id, and the translation pass.
    graph.go                  Precedent graph, dirty set, topological recalc.
    eval.go                   Evaluator core.
    fn_math.go                Function library, split by family.
    fn_text.go
    fn_logic.go
    fn_lookup.go
    fn_date.go
    errors.go                 Excel error values.
    corpus_test.go            Reads the committed golden corpus.
  format/
    format.go                 Number format → display string. Go formats.
  xlsx/
    import.go                 excelize → our model.
    export.go                 our model → excelize.
    roundtrip_test.go         Golden corpus.
tools/gencorpus/main.go       LibreOffice-driven corpus generator. Run rarely.
testdata/corpus/
  formulas.json               Committed. Hash-pinned.
  volatile-excluded.txt       A file somebody edits. Not a filter.
  xlsx/*.xlsx                 Round-trip fixtures.

Rationale for the splits: calc/ is the largest subsystem and is divided by compilation stage rather than by feature, so a change to parsing never touches evaluation. The function library is split by family because it is the only part expected to grow to thousands of lines. seal/ is isolated so the plaintext gate has one place to audit.

---

Task 1: Skeleton and the platform contract

Files:

  • Create: go.mod, cmd/sheets/main.go, internal/sheets/app.go
  • Create: internal/sheets/app_test.go

Interfaces:

  • Consumes: nothing.
  • Produces: sheets.New(db *gorm.DB) *App and (*App).Router() http.Handler. Every later task mounts on this.
  • [ ] Step 1: Initialise the module
cd /home/paulca/amadan.net/oficina/sheets   # or wherever the checkout is
go mod init amadan.net/oficina/sheets
go get github.com/carlosframework/rastrillo@v0.20.0
  • [ ] Step 2: Write the failing test

Create internal/sheets/app_test.go:

package sheets

import (
	"net/http"
	"net/http/httptest"
	"testing"
)

func TestHealthz(t *testing.T) {
	app := New(nil)
	req := httptest.NewRequest(http.MethodGet, "/healthz", nil)
	rec := httptest.NewRecorder()
	app.Router().ServeHTTP(rec, req)
	if rec.Code != http.StatusOK {
		t.Fatalf("healthz = %d, want 200", rec.Code)
	}
}
  • [ ] Step 3: Run it and watch it fail

Run: go test ./internal/sheets/ -run TestHealthz -v Expected: FAIL — undefined: New.

  • [ ] Step 4: Write the minimal implementation

Create internal/sheets/app.go:

// Package sheets is the Sheets instance app: one process, one SQLite file,
// one team.
package sheets

import (
	"net/http"

	"github.com/go-chi/chi/v5"
	"gorm.io/gorm"
)

// App holds the wiring. One per process.
type App struct {
	db *gorm.DB
}

// New builds an App. db may be nil in tests that do not touch storage.
func New(db *gorm.DB) *App { return &App{db: db} }

// Router returns the mounted handler.
func (a *App) Router() http.Handler {
	r := chi.NewRouter()
	r.Get("/healthz", func(w http.ResponseWriter, _ *http.Request) {
		w.WriteHeader(http.StatusOK)
	})
	return r
}
  • [ ] Step 5: Run it and watch it pass

Run: go test ./internal/sheets/ -run TestHealthz -v Expected: PASS.

  • [ ] Step 6: Write cmd/sheets/main.go

Thin dispatch only — the platform contract is rastrillo's job. There is no $PORT; the contract is <bin> --socket <path> --db <path>.

// Command sheets is the Sheets instance binary.
package main

import (
	"log"

	"github.com/carlosframework/rastrillo"
	"amadan.net/oficina/sheets/internal/sheets"
)

func main() {
	cfg, err := rastrillo.Resolve()
	if err != nil {
		log.Fatal(err)
	}
	app := sheets.New(cfg.DB)
	if err := rastrillo.Run(cfg, app.Router()); err != nil {
		log.Fatal(err)
	}
}

If rastrillo.Resolve/Run have a different shape at v0.19.0, read $(go env GOMODCACHE)/github.com/carlosframework/rastrillo@v0.20.0/SKILL.md and follow it — SKILL.md wins over this plan.

  • [ ] Step 7: Verify the whole build, cgo-free
CGO_ENABLED=0 go build ./... && go vet ./... && go test ./...

Expected: all pass.

  • [ ] Step 8: Commit
git add go.mod go.sum cmd/ internal/
git commit -m "Skeleton: the platform contract and a health check"

---

Task 2: Sealing, and the plaintext gate

The gate goes in with the mechanism, not after it. Once this task lands, every later task is checked by it.

Files:

  • Create: internal/sheets/seal/seal.go, internal/sheets/seal/seal_test.go
  • Create: internal/sheets/seal/vectors_test.go
  • Create: internal/sheets/seal/testdata/vectors.json

Interfaces:

  • Consumes: nothing.
  • Produces: - seal.New(dek []byte) (*Sealer, error) — DEK must be 32 bytes. - (*Sealer).Seal(purpose string, plaintext []byte) ([]byte, error) - (*Sealer).Open(purpose string, ciphertext []byte) ([]byte, error) Purposes used across the codebase: "cell.input", "cell.cached", "sheet.title", "tab.name", "event.payload".
  • [ ] Step 1: Write the failing round-trip test

Create internal/sheets/seal/seal_test.go:

package seal

import (
	"bytes"
	"testing"
)

func testDEK() []byte {
	d := make([]byte, 32)
	for i := range d {
		d[i] = byte(i)
	}
	return d
}

func TestSealOpenRoundTrip(t *testing.T) {
	s, err := New(testDEK())
	if err != nil {
		t.Fatal(err)
	}
	want := []byte("=SUM(A1:A10)")
	ct, err := s.Seal("cell.input", want)
	if err != nil {
		t.Fatal(err)
	}
	if bytes.Contains(ct, want) {
		t.Fatal("ciphertext contains the plaintext")
	}
	got, err := s.Open("cell.input", ct)
	if err != nil {
		t.Fatal(err)
	}
	if !bytes.Equal(got, want) {
		t.Fatalf("Open = %q, want %q", got, want)
	}
}

// A value sealed under one purpose must not open under another. Purpose
// separation is what stops a cached value being swapped for an input.
func TestPurposeSeparation(t *testing.T) {
	s, _ := New(testDEK())
	ct, _ := s.Seal("cell.input", []byte("secret"))
	if _, err := s.Open("cell.cached", ct); err == nil {
		t.Fatal("opened under the wrong purpose")
	}
}

func TestRejectsShortDEK(t *testing.T) {
	if _, err := New([]byte("too short")); err == nil {
		t.Fatal("accepted a short DEK")
	}
}
  • [ ] Step 2: Run and watch it fail

Run: go test ./internal/sheets/seal/ -v Expected: FAIL — undefined: New.

  • [ ] Step 3: Implement

Create internal/sheets/seal/seal.go:

// Package seal is the app's only encryption surface.
//
// The one rule: a sheet's contents are sealed at rest, and the bucket never
// holds a plaintext cell. The DEK arrives as platform config and is never
// written to the database it decrypts.
//
// Purpose keys are derived with HKDF so that a value sealed as a cell input
// cannot be opened as a cached value. That is not paranoia: the two are
// adjacent columns of the same row, and a bug that swapped them would be
// invisible without this.
package seal

import (
	"crypto/aes"
	"crypto/cipher"
	"crypto/rand"
	"errors"
	"io"

	"golang.org/x/crypto/hkdf"
)

// Sealer holds the data encryption key. One per process.
type Sealer struct{ dek []byte }

// New returns a Sealer. The DEK must be exactly 32 bytes.
func New(dek []byte) (*Sealer, error) {
	if len(dek) != 32 {
		return nil, errors.New("seal: DEK must be 32 bytes")
	}
	c := make([]byte, 32)
	copy(c, dek)
	return &Sealer{dek: c}, nil
}

// aeadFor derives the purpose key and returns its AEAD.
func (s *Sealer) aeadFor(purpose string) (cipher.AEAD, error) {
	key := make([]byte, 32)
	r := hkdf.New(sha256New, s.dek, nil, []byte("oficina/sheets:"+purpose))
	if _, err := io.ReadFull(r, key); err != nil {
		return nil, err
	}
	block, err := aes.NewCipher(key)
	if err != nil {
		return nil, err
	}
	return cipher.NewGCM(block)
}

// Seal encrypts plaintext under purpose. The nonce is prepended.
func (s *Sealer) Seal(purpose string, plaintext []byte) ([]byte, error) {
	aead, err := s.aeadFor(purpose)
	if err != nil {
		return nil, err
	}
	nonce := make([]byte, aead.NonceSize())
	if _, err := rand.Read(nonce); err != nil {
		return nil, err
	}
	return aead.Seal(nonce, nonce, plaintext, nil), nil
}

// Open decrypts a value sealed under the same purpose.
func (s *Sealer) Open(purpose string, ct []byte) ([]byte, error) {
	aead, err := s.aeadFor(purpose)
	if err != nil {
		return nil, err
	}
	if len(ct) < aead.NonceSize() {
		return nil, errors.New("seal: ciphertext too short")
	}
	nonce, body := ct[:aead.NonceSize()], ct[aead.NonceSize():]
	return aead.Open(nil, nonce, body, nil)
}

Add the hash import at the top of the file:

import "crypto/sha256"

func sha256New() hash.Hash { return sha256.New() }

with "hash" added to the import block. (Written as a named function rather than inline so the HKDF call reads as one line.)

  • [ ] Step 4: Run and watch it pass
go get golang.org/x/crypto
go test ./internal/sheets/seal/ -v

Expected: PASS.

  • [ ] Step 5: Write the golden vectors test

Vectors pin the envelope so a later refactor cannot silently change the format and orphan every sealed row. Because GCM nonces are random, the vector fixes the derived key, not a whole ciphertext.

Create internal/sheets/seal/vectors_test.go:

package seal

import (
	"encoding/hex"
	"testing"
)

// Pins the HKDF derivation. If this changes, every sealed row in every
// deployed instance becomes unreadable — so it changes only with a
// migration, never as a refactor.
func TestPurposeKeyVectors(t *testing.T) {
	s, _ := New(testDEK())
	for _, tc := range []struct{ purpose, want string }{
		{"cell.input", ""},   // fill from the first run, then never edit
		{"cell.cached", ""},
	} {
		got := hex.EncodeToString(s.purposeKey(tc.purpose))
		if tc.want == "" {
			t.Logf("RECORD %s = %s", tc.purpose, got)
			continue
		}
		if got != tc.want {
			t.Fatalf("%s key = %s, want %s", tc.purpose, got, tc.want)
		}
	}
}

Add the helper to seal.go:

// purposeKey exposes the derived key for the golden-vector test. It is not
// used in production paths.
func (s *Sealer) purposeKey(purpose string) []byte {
	key := make([]byte, 32)
	r := hkdf.New(sha256New, s.dek, nil, []byte("oficina/sheets:"+purpose))
	io.ReadFull(r, key)
	return key
}
  • [ ] Step 6: Record the vectors, then freeze them

Run: go test ./internal/sheets/seal/ -run TestPurposeKeyVectors -v Copy each logged RECORD value into the want field. Re-run; expect PASS.

  • [ ] Step 7: Write the plaintext gate

This is the gate the whole spec rests on. It greps the raw SQLite file and its WAL, because Litestream ships WAL frames continuously and a value written plainly reaches the bucket before any delete.

Create internal/sheets/seal/gate_test.go:

package seal_test

import (
	"bytes"
	"os"
	"path/filepath"
	"testing"
)

// The canary strings a test seeds into a database before this gate runs.
// Any of them appearing in raw bytes is a plaintext leak.
var canaries = [][]byte{
	[]byte("CANARY-CELL-VALUE-8f3a"),
	[]byte("CANARY-SHEET-TITLE-2b71"),
	[]byte("CANARY-TAB-NAME-c40d"),
}

// AssertNoPlaintext greps a database file, its WAL and its SHM for canaries.
// Exported for use by store and handle tests, which are the ones that
// actually write rows.
func AssertNoPlaintext(t *testing.T, dbPath string) {
	t.Helper()
	for _, suffix := range []string{"", "-wal", "-shm"} {
		p := dbPath + suffix
		b, err := os.ReadFile(p)
		if os.IsNotExist(err) {
			continue
		}
		if err != nil {
			t.Fatalf("read %s: %v", filepath.Base(p), err)
		}
		for _, c := range canaries {
			if bytes.Contains(b, c) {
				t.Fatalf("plaintext %q found in %s", c, filepath.Base(p))
			}
		}
	}
}

Move AssertNoPlaintext and canaries into a non-_test file internal/sheets/seal/gate.go under a //go:build !prod tag if the executor prefers; either is fine, but it must be callable from internal/sheets/store.

  • [ ] Step 8: Commit
git add internal/sheets/seal/
git commit -m "Sealing, purpose keys, golden vectors, and the plaintext gate"

---

Task 3: The storage schema and stable identity

Files:

  • Create: internal/sheets/models.go, internal/sheets/models_test.go
  • Create: internal/sheets/calc/ref.go, internal/sheets/calc/ref_test.go

Interfaces:

  • Consumes: seal.Sealer (task 2).
  • Produces: - Models Sheet, Tab, Row, Col, Cell, Format. - calc.ColLabel(pos int) string — 0→"A", 25→"Z", 26→"AA". - calc.ParseColLabel(s string) (int, error) — the inverse. - calc.A1(rowPos, colPos int) string — 0,0 → "A1".
  • [ ] Step 1: Write the failing A1 test

A1 is a view, derived at render time. Positions live on Row/Col records; Cell is keyed by stable ids. Getting the label maths wrong is the kind of bug that surfaces at column 702, so it is tested at the boundaries.

Create internal/sheets/calc/ref_test.go:

package calc

import "testing"

func TestColLabel(t *testing.T) {
	for _, tc := range []struct {
		pos  int
		want string
	}{
		{0, "A"}, {1, "B"}, {25, "Z"},
		{26, "AA"}, {27, "AB"}, {51, "AZ"},
		{52, "BA"}, {701, "ZZ"}, {702, "AAA"},
	} {
		if got := ColLabel(tc.pos); got != tc.want {
			t.Errorf("ColLabel(%d) = %q, want %q", tc.pos, got, tc.want)
		}
	}
}

func TestParseColLabelRoundTrip(t *testing.T) {
	for pos := 0; pos < 3000; pos++ {
		got, err := ParseColLabel(ColLabel(pos))
		if err != nil {
			t.Fatalf("pos %d: %v", pos, err)
		}
		if got != pos {
			t.Fatalf("round trip %d -> %q -> %d", pos, ColLabel(pos), got)
		}
	}
}

func TestA1(t *testing.T) {
	if got := A1(0, 0); got != "A1" {
		t.Errorf("A1(0,0) = %q, want A1", got)
	}
	if got := A1(6, 1); got != "B7" {
		t.Errorf("A1(6,1) = %q, want B7", got)
	}
}
  • [ ] Step 2: Run and watch it fail

Run: go test ./internal/sheets/calc/ -run 'TestColLabel|TestParseColLabel|TestA1' -v Expected: FAIL — undefined.

  • [ ] Step 3: Implement

Create internal/sheets/calc/ref.go:

// Package calc owns everything about formulas: references, lexing, parsing,
// evaluation and the dependency graph.
package calc

import (
	"errors"
	"strings"
)

// ColLabel renders a zero-based column position as a spreadsheet label.
// This is bijective base-26, not ordinary base-26: there is no zero digit,
// which is why 25 is "Z" and 26 is "AA" rather than "BA".
func ColLabel(pos int) string {
	if pos < 0 {
		return ""
	}
	var sb strings.Builder
	pos++
	for pos > 0 {
		pos--
		sb.WriteByte(byte('A' + pos%26))
		pos /= 26
	}
	b := []byte(sb.String())
	for i, j := 0, len(b)-1; i < j; i, j = i+1, j-1 {
		b[i], b[j] = b[j], b[i]
	}
	return string(b)
}

// ParseColLabel is ColLabel's inverse.
func ParseColLabel(s string) (int, error) {
	if s == "" {
		return 0, errors.New("calc: empty column label")
	}
	n := 0
	for i := 0; i < len(s); i++ {
		c := s[i]
		if c >= 'a' && c <= 'z' {
			c -= 'a' - 'A'
		}
		if c < 'A' || c > 'Z' {
			return 0, errors.New("calc: bad column label")
		}
		n = n*26 + int(c-'A') + 1
	}
	return n - 1, nil
}

// A1 renders zero-based positions as an A1 reference. Positions, not ids:
// stable ids are the key, A1 is the view.
func A1(rowPos, colPos int) string {
	return ColLabel(colPos) + itoa(rowPos+1)
}

func itoa(n int) string {
	if n == 0 {
		return "0"
	}
	var b [20]byte
	i := len(b)
	for n > 0 {
		i--
		b[i] = byte('0' + n%10)
		n /= 10
	}
	return string(b[i:])
}
  • [ ] Step 4: Run and watch it pass

Run: go test ./internal/sheets/calc/ -v Expected: PASS.

  • [ ] Step 5: Write the models

Create internal/sheets/models.go:

package sheets

import "time"

// Sheet is one spreadsheet document.
type Sheet struct {
	ID          uint64 `gorm:"primaryKey"`
	TitleSealed []byte // seal purpose "sheet.title"
	CreatedBy   uint64
	CreatedAt   time.Time
	UpdatedAt   time.Time
	DeletedAt   *time.Time `gorm:"index"`
}

// Tab is one sheet within a document — the tabs along the bottom.
type Tab struct {
	ID         uint64 `gorm:"primaryKey"`
	SheetID    uint64 `gorm:"index"`
	NameSealed []byte // seal purpose "tab.name"
	Position   int
	FrozenRows int
	FrozenCols int
}

// Row and Col carry stable identity. Position is a property of the record,
// never part of a key — so inserting a row above a comment or a range does
// not move what they point at. See spec section 4.2 for what this does and
// does not buy: it removes reference rewriting for insert and delete only.
type Row struct {
	ID       uint64 `gorm:"primaryKey"`
	TabID    uint64 `gorm:"index:idx_row_tab_pos"`
	Position int    `gorm:"index:idx_row_tab_pos"`
	Height   int
}

type Col struct {
	ID       uint64 `gorm:"primaryKey"`
	TabID    uint64 `gorm:"index:idx_col_tab_pos"`
	Position int    `gorm:"index:idx_col_tab_pos"`
	Width    int
}

// Cell is sparse: only non-empty cells exist.
//
// CacheVersion records the InputVersion its CachedSealed was derived from.
// A cache whose CacheVersion trails its input is stale and must not be
// served — which is how a crash mid-recalc is detected rather than assumed
// away. See spec section 4.4.
type Cell struct {
	TabID        uint64 `gorm:"primaryKey"`
	RowID        uint64 `gorm:"primaryKey"`
	ColID        uint64 `gorm:"primaryKey"`
	InputSealed  []byte // seal purpose "cell.input"
	CachedSealed []byte // seal purpose "cell.cached"
	InputVersion uint64
	CacheVersion uint64
	FormatID     *uint64
	UpdatedBy    uint64
	UpdatedAt    time.Time
}

// Format is interned. A column of 5,000 identically styled cells is one row
// here, not 5,000 — which is the difference between a format change being
// cheap and being a table rewrite.
type Format struct {
	ID          uint64 `gorm:"primaryKey"`
	Digest      string `gorm:"uniqueIndex;size:64"`
	NumberFmt   string
	Bold        bool
	Italic      bool
	FillIntentH *float64 // OKLCH hue; nil means no fill
	FillIntentC *float64 // OKLCH chroma
	FillHex     string   // retained from XLSX import for lossless re-export
	Align       string
}
  • [ ] Step 6: Write the schema test

Create internal/sheets/models_test.go:

package sheets

import "testing"

// The models must carry no column that could hold a readable cell value.
// This is a schema-level restatement of the one rule: there is nothing to
// accidentally populate.
func TestNoPlaintextValueColumns(t *testing.T) {
	forbidden := []string{"Title", "Name", "Input", "Cached", "Value"}
	// Sealed fields end in "Sealed"; a bare name would be a plaintext column.
	for _, f := range []string{"TitleSealed", "NameSealed", "InputSealed", "CachedSealed"} {
		if !hasSuffix(f, "Sealed") {
			t.Fatalf("%s does not end in Sealed", f)
		}
	}
	_ = forbidden
}

func hasSuffix(s, suf string) bool {
	return len(s) >= len(suf) && s[len(s)-len(suf):] == suf
}

Executor note: this test is deliberately weak — it documents intent. The real enforcement is the byte-level gate from task 2, invoked in task 4.

  • [ ] Step 7: Verify and commit
CGO_ENABLED=0 go build ./... && go test ./...
git add internal/sheets/models.go internal/sheets/models_test.go internal/sheets/calc/
git commit -m "Storage schema, stable row and column identity, A1 as a view"

---

Task 4: The event log, and the gate against real rows

Files:

  • Create: internal/sheets/store/events.go, internal/sheets/store/events_test.go

Interfaces:

  • Consumes: models (task 3), seal.Sealer (task 2).
  • Produces: - store.Event model. - store.AppendInput(tx *gorm.DB, s *seal.Sealer, e InputEvent) error - store.AppendRecalc(tx *gorm.DB, triggerID uint64, derived int) error
  • [ ] Step 1: Write the failing test, including the plaintext gate

This is the first task that writes real rows, so it is where the gate stops being theoretical.

Create internal/sheets/store/events_test.go:

package store_test

import (
	"path/filepath"
	"testing"

	"amadan.net/oficina/sheets/internal/sheets/seal"
	"amadan.net/oficina/sheets/internal/sheets/store"
)

func TestInputEventIsSealedOnDisk(t *testing.T) {
	dir := t.TempDir()
	dbPath := filepath.Join(dir, "test.db")
	db := openTestDB(t, dbPath) // helper defined below
	s, _ := seal.New(make32())

	err := store.AppendInput(db, s, store.InputEvent{
		TabID: 1, RowID: 1, ColID: 1, ActorID: 7,
		OldRaw: "",
		NewRaw: "CANARY-CELL-VALUE-8f3a",
	})
	if err != nil {
		t.Fatal(err)
	}
	checkpoint(t, db) // force a WAL checkpoint so bytes hit the file

	seal.AssertNoPlaintext(t, dbPath)
}

// A recalc marker records that a pass happened and how wide it was. It must
// never carry derived values: logging them makes the log grow with the
// fan-out of every edit rather than with the number of edits, in a file
// Litestream ships continuously. See spec section 4.3.
func TestRecalcMarkerHoldsNoValues(t *testing.T) {
	dir := t.TempDir()
	db := openTestDB(t, filepath.Join(dir, "test.db"))
	if err := store.AppendRecalc(db, 42, 1200); err != nil {
		t.Fatal(err)
	}
	var e store.Event
	db.Where("kind = ?", store.KindRecalc).First(&e)
	if len(e.PayloadSealed) != 0 {
		t.Fatal("recalc marker carries a payload; it must not")
	}
	if e.DerivedCount != 1200 {
		t.Fatalf("DerivedCount = %d, want 1200", e.DerivedCount)
	}
}

The executor writes openTestDB, make32 and checkpoint helpers in internal/sheets/store/helpers_test.go. checkpoint runs PRAGMA wal_checkpoint(TRUNCATE) so the assertion sees settled bytes; without it the test can pass because the data is still in memory, which would be a gate that agrees with itself.

  • [ ] Step 2: Run and watch it fail

Run: go test ./internal/sheets/store/ -v Expected: FAIL — undefined.

  • [ ] Step 3: Implement

Create internal/sheets/store/events.go:

// Package store owns persistence: cells, rows, columns, formats and the
// append-only event log.
package store

import (
	"encoding/json"
	"time"

	"gorm.io/gorm"

	"amadan.net/oficina/sheets/internal/sheets/seal"
)

// Event kinds. Two, deliberately: conflating them is what made an earlier
// draft of the leak list wrong.
const (
	KindInput  = "input"
	KindRecalc = "recalc"
)

// Event is the append-only log. It does four jobs — live-sync fan-out,
// version history, undo, and audit — which is why the storage model pays.
type Event struct {
	ID            uint64 `gorm:"primaryKey"`
	Kind          string `gorm:"index;size:16"`
	TabID         uint64 `gorm:"index"`
	ActorID       uint64
	PayloadSealed []byte // seal purpose "event.payload"; empty for recalc
	TriggerID     uint64 // recalc only: the input event that caused it
	DerivedCount  int    // recalc only: how many cells the pass touched
	CreatedAt     time.Time
}

// InputEvent is a person's edit, before sealing.
type InputEvent struct {
	TabID, RowID, ColID uint64
	ActorID             uint64
	OldRaw, NewRaw      string
}

type inputPayload struct {
	RowID  uint64 `json:"r"`
	ColID  uint64 `json:"c"`
	OldRaw string `json:"o"`
	NewRaw string `json:"n"`
}

// AppendInput writes a sealed input event. Old and new raw input are user
// content, so the payload is sealed; the row's shape is not.
func AppendInput(tx *gorm.DB, s *seal.Sealer, e InputEvent) error {
	body, err := json.Marshal(inputPayload{
		RowID: e.RowID, ColID: e.ColID, OldRaw: e.OldRaw, NewRaw: e.NewRaw,
	})
	if err != nil {
		return err
	}
	sealed, err := s.Seal("event.payload", body)
	if err != nil {
		return err
	}
	return tx.Create(&Event{
		Kind: KindInput, TabID: e.TabID, ActorID: e.ActorID,
		PayloadSealed: sealed,
	}).Error
}

// AppendRecalc records that a recalculation pass ran and how wide it was.
// It carries no values: version history replays inputs and recomputes, which
// is slower to restore and the only version that stays honest at scale.
func AppendRecalc(tx *gorm.DB, triggerID uint64, derived int) error {
	return tx.Create(&Event{
		Kind: KindRecalc, TriggerID: triggerID, DerivedCount: derived,
	}).Error
}
  • [ ] Step 4: Run and watch it pass

Run: go test ./internal/sheets/store/ -v Expected: PASS, including TestInputEventIsSealedOnDisk.

  • [ ] Step 5: Prove the gate can fail

A gate nobody has seen fail is a hope. Temporarily change AppendInput to store body instead of sealed, re-run, and confirm TestInputEventIsSealedOnDisk FAILS with a plaintext report. Then revert.

Run: go test ./internal/sheets/store/ -run TestInputEventIsSealedOnDisk -v Expected while broken: FAIL — plaintext "CANARY-CELL-VALUE-8f3a" found in test.db

  • [ ] Step 6: Commit
git add internal/sheets/store/
git commit -m "The event log: sealed input events, valueless recalc markers"

---

Task 5: Lexing and parsing formulas

Files:

  • Create: internal/sheets/calc/token.go, internal/sheets/calc/ast.go, internal/sheets/calc/parse.go
  • Create: internal/sheets/calc/parse_test.go

Interfaces:

  • Consumes: calc.ParseColLabel (task 3).
  • Produces: - calc.Parse(src string) (Node, error) - Node types: Num, Str, Bool, Ref, Range, Binary, Unary, Call - Ref{ColPos, RowPos int; AbsCol, AbsRow bool} — positions at parse time; task 6 binds them to stable ids.
  • [ ] Step 1: Write the failing parser test
package calc

import "testing"

func TestParseArithmeticPrecedence(t *testing.T) {
	n, err := Parse("1+2*3")
	if err != nil {
		t.Fatal(err)
	}
	b, ok := n.(*Binary)
	if !ok || b.Op != "+" {
		t.Fatalf("root = %T %v, want Binary +", n, n)
	}
	r, ok := b.Right.(*Binary)
	if !ok || r.Op != "*" {
		t.Fatal("multiplication did not bind tighter than addition")
	}
}

func TestParseAbsoluteAndRelativeRefs(t *testing.T) {
	n, err := Parse("$A$1")
	if err != nil {
		t.Fatal(err)
	}
	r := n.(*Ref)
	if !r.AbsCol || !r.AbsRow {
		t.Fatal("$A$1 did not parse as fully absolute")
	}
	n2, _ := Parse("A1")
	r2 := n2.(*Ref)
	if r2.AbsCol || r2.AbsRow {
		t.Fatal("A1 parsed as absolute")
	}
	if r2.ColPos != 0 || r2.RowPos != 0 {
		t.Fatalf("A1 = col %d row %d, want 0,0", r2.ColPos, r2.RowPos)
	}
}

func TestParseRangeAndCall(t *testing.T) {
	n, err := Parse("SUM(A1:B10)")
	if err != nil {
		t.Fatal(err)
	}
	c, ok := n.(*Call)
	if !ok || c.Name != "SUM" {
		t.Fatalf("root = %T, want Call SUM", n)
	}
	if _, ok := c.Args[0].(*Range); !ok {
		t.Fatalf("arg 0 = %T, want Range", c.Args[0])
	}
}

func TestParseRejectsGarbage(t *testing.T) {
	for _, src := range []string{"1+", "(1", "SUM(", "A1:", "*"} {
		if _, err := Parse(src); err == nil {
			t.Errorf("Parse(%q) succeeded, want error", src)
		}
	}
}
  • [ ] Step 2: Run and watch it fail

Run: go test ./internal/sheets/calc/ -run TestParse -v Expected: FAIL — undefined.

  • [ ] Step 3: Implement the AST

Create internal/sheets/calc/ast.go:

package calc

// Node is any expression node.
type Node interface{ node() }

type Num struct{ V float64 }
type Str struct{ V string }
type Bool struct{ V bool }

// Ref is a single-cell reference as written. Positions are what the source
// says; binding to stable ids happens in the reference pass, because
// relative references are positional offsets and must stay that way until
// a paste anchor is known.
type Ref struct {
	ColPos, RowPos int
	AbsCol, AbsRow bool
}

type Range struct{ From, To *Ref }

type Binary struct {
	Op          string
	Left, Right Node
}

type Unary struct {
	Op string
	X  Node
}

type Call struct {
	Name string
	Args []Node
}

func (*Num) node()    {}
func (*Str) node()    {}
func (*Bool) node()   {}
func (*Ref) node()    {}
func (*Range) node()  {}
func (*Binary) node() {}
func (*Unary) node()  {}
func (*Call) node()   {}
  • [ ] Step 4: Implement the lexer and parser

The executor writes token.go (a scanner producing typed tokens for numbers, strings, identifiers, references, operators, parens, commas and colons) and parse.go (precedence-climbing over ^, then * /, then + -, then comparison = <> < <= > >=, with & for concatenation at the lowest arithmetic level). Reference tokens are recognised by the pattern \$?[A-Za-z]{1,3}\$?[0-9]+ and converted with ParseColLabel.

Keep each file under the 300-line cap. If parse.go approaches it, split the function-call argument parsing into parse_call.go rather than growing the file — a new concern gets a new small file, never more growth of the biggest one.

  • [ ] Step 5: Run and watch it pass

Run: go test ./internal/sheets/calc/ -v Expected: PASS.

  • [ ] Step 6: Commit
git add internal/sheets/calc/
git commit -m "Formula lexer, AST and precedence-climbing parser"

---

Task 6: Reference translation — what stable ids do NOT buy

Files:

  • Create: internal/sheets/calc/translate.go, internal/sheets/calc/translate_test.go

Interfaces:

  • Consumes: AST types (task 5).
  • Produces: - calc.Translate(n Node, dRow, dCol int) Node — offsets relative refs, leaves absolute ones alone. - calc.Bind(n Node, tab *TabIndex) (Node, error) — positions → stable ids. - calc.BoundRef{RowID, ColID uint64; AbsCol, AbsRow bool}

Why this task exists: an earlier draft of the spec claimed stable ids removed the reference-rewriting pass. They remove it for insert and delete only. Relative references are positional offsets — copying =A1 from B1 to B2 must produce =A2 — so copy and fill need a translation pass at paste time. The pass moved; it did not disappear. Spec §4.2 has the full table.

  • [ ] Step 1: Write the failing translation test
package calc

import "testing"

func TestTranslateRelativeRef(t *testing.T) {
	n, _ := Parse("A1")
	got := Translate(n, 1, 0).(*Ref) // copied down one row
	if got.RowPos != 1 || got.ColPos != 0 {
		t.Fatalf("=A1 copied down = row %d col %d, want 1,0", got.RowPos, got.ColPos)
	}
}

func TestTranslateLeavesAbsoluteAlone(t *testing.T) {
	n, _ := Parse("$A$1")
	got := Translate(n, 5, 5).(*Ref)
	if got.RowPos != 0 || got.ColPos != 0 {
		t.Fatal("$A$1 moved under translation")
	}
}

func TestTranslateMixedRef(t *testing.T) {
	n, _ := Parse("$A1") // absolute column, relative row
	got := Translate(n, 3, 4).(*Ref)
	if got.ColPos != 0 {
		t.Fatal("$A moved")
	}
	if got.RowPos != 3 {
		t.Fatalf("relative row = %d, want 3", got.RowPos)
	}
}

func TestTranslateDescendsIntoCalls(t *testing.T) {
	n, _ := Parse("SUM(A1:A5)+$B$1")
	got := Translate(n, 2, 0).(*Binary)
	rng := got.Left.(*Call).Args[0].(*Range)
	if rng.From.RowPos != 2 || rng.To.RowPos != 6 {
		t.Fatalf("range = %d:%d, want 2:6", rng.From.RowPos, rng.To.RowPos)
	}
	if got.Right.(*Ref).RowPos != 0 {
		t.Fatal("$B$1 moved")
	}
}
  • [ ] Step 2: Run and watch it fail

Run: go test ./internal/sheets/calc/ -run TestTranslate -v Expected: FAIL — undefined: Translate.

  • [ ] Step 3: Implement

Create internal/sheets/calc/translate.go:

package calc

// Translate offsets every relative reference in n by (dRow, dCol) and
// returns a new tree. Absolute components are untouched — that is the whole
// meaning of the dollar sign.
//
// This is the pass stable row and column identity does NOT remove. Ids make
// insert and delete free; copy and fill are positional by definition, so the
// offset has to be applied at paste time. See spec section 4.2.
func Translate(n Node, dRow, dCol int) Node {
	switch t := n.(type) {
	case *Ref:
		out := *t
		if !out.AbsRow {
			out.RowPos += dRow
		}
		if !out.AbsCol {
			out.ColPos += dCol
		}
		return &out
	case *Range:
		return &Range{
			From: Translate(t.From, dRow, dCol).(*Ref),
			To:   Translate(t.To, dRow, dCol).(*Ref),
		}
	case *Binary:
		return &Binary{Op: t.Op,
			Left:  Translate(t.Left, dRow, dCol),
			Right: Translate(t.Right, dRow, dCol)}
	case *Unary:
		return &Unary{Op: t.Op, X: Translate(t.X, dRow, dCol)}
	case *Call:
		args := make([]Node, len(t.Args))
		for i, a := range t.Args {
			args[i] = Translate(a, dRow, dCol)
		}
		return &Call{Name: t.Name, Args: args}
	default:
		return n // Num, Str, Bool have no references
	}
}
  • [ ] Step 4: Run and watch it pass

Run: go test ./internal/sheets/calc/ -run TestTranslate -v Expected: PASS.

  • [ ] Step 5: Write the per-operation contract as an executable table

The spec's §4.2 table is the contract. Encode it so a later change to any operation has to argue with a test.

// Each row restates spec section 4.2. If an implementation changes one of
// these behaviours, this table is what it has to argue with.
func TestReferenceBehaviourPerOperation(t *testing.T) {
	cases := []struct {
		op, src, want string
		dRow, dCol    int
	}{
		{op: "copy down", src: "A1", dRow: 1, want: "A2"},
		{op: "copy right", src: "A1", dCol: 1, want: "B1"},
		{op: "copy absolute", src: "$A$1", dRow: 9, dCol: 9, want: "$A$1"},
		{op: "fill range", src: "SUM(A1:A3)", dRow: 1, want: "SUM(A2:A4)"},
	}
	for _, tc := range cases {
		n, err := Parse(tc.src)
		if err != nil {
			t.Fatalf("%s: parse %q: %v", tc.op, tc.src, err)
		}
		if got := Render(Translate(n, tc.dRow, tc.dCol)); got != tc.want {
			t.Errorf("%s: %q -> %q, want %q", tc.op, tc.src, got, tc.want)
		}
	}
}

The executor writes Render(Node) string in translate.go — the inverse of Parse for the subset the tests use. It is worth having anyway: it is what the formula bar shows.

  • [ ] Step 6: Run, then commit
go test ./internal/sheets/calc/ -v
git add internal/sheets/calc/
git commit -m "Reference translation: the pass stable ids do not remove"

---

Task 7: The evaluator, the dependency graph and dirty-set recalc

Files:

  • Create: internal/sheets/calc/errors.go, internal/sheets/calc/eval.go, internal/sheets/calc/graph.go
  • Create: internal/sheets/calc/eval_test.go, internal/sheets/calc/graph_test.go

Interfaces:

  • Consumes: AST (task 5), Bind (task 6).
  • Produces: - calc.Value — struct{ Kind ValueKind; Num float64; Str string; Bool bool; Err ErrKind } - calc.Eval(n Node, env Env) Value - calc.Env interface: CellValue(rowID, colID uint64) Value - calc.Graph with SetFormula, Dirty, Recalc returning the touched count.
  • [ ] Step 1: Write the failing evaluator tests
package calc

import "testing"

func TestEvalArithmetic(t *testing.T) {
	v := Eval(mustParse(t, "1+2*3"), emptyEnv{})
	if v.Kind != KindNum || v.Num != 7 {
		t.Fatalf("1+2*3 = %v, want 7", v)
	}
}

func TestDivideByZeroIsAnExcelError(t *testing.T) {
	v := Eval(mustParse(t, "1/0"), emptyEnv{})
	if v.Kind != KindErr || v.Err != ErrDiv0 {
		t.Fatalf("1/0 = %v, want #DIV/0!", v)
	}
}

// Errors propagate rather than being swallowed: =A1+1 where A1 is #REF!
// must be #REF!, not 1. XLSX round-trips these, so they are values.
func TestErrorsPropagate(t *testing.T) {
	env := mapEnv{{1, 1}: {Kind: KindErr, Err: ErrRef}}
	v := Eval(mustParse(t, "A1+1"), env)
	if v.Kind != KindErr || v.Err != ErrRef {
		t.Fatalf("propagation = %v, want #REF!", v)
	}
}
  • [ ] Step 2: Write the failing graph tests
func TestDirtySetTouchesOnlyDependents(t *testing.T) {
	g := NewGraph()
	g.SetFormula(cellB1, mustParse(t, "A1*2"))
	g.SetFormula(cellC1, mustParse(t, "B1+1"))
	g.SetFormula(cellZ9, mustParse(t, "42")) // unrelated

	touched := g.Recalc(cellA1, env)
	if touched != 2 {
		t.Fatalf("touched %d cells, want 2 (B1, C1) — Z9 must not recalc", touched)
	}
}

// A cycle must return an error value, not hang. This test has a timeout
// because the failure mode of getting it wrong is a wedged process.
func TestCycleDetection(t *testing.T) {
	g := NewGraph()
	g.SetFormula(cellA1, mustParse(t, "B1"))
	g.SetFormula(cellB1, mustParse(t, "A1"))
	done := make(chan Value, 1)
	go func() { done <- g.Value(cellA1, env) }()
	select {
	case v := <-done:
		if v.Kind != KindErr || v.Err != ErrCircular {
			t.Fatalf("cycle = %v, want #CIRCULAR!", v)
		}
	case <-time.After(2 * time.Second):
		t.Fatal("cycle detection hung")
	}
}
  • [ ] Step 3: Run and watch both fail

Run: go test ./internal/sheets/calc/ -run 'TestEval|TestDirty|TestCycle' -v Expected: FAIL — undefined.

  • [ ] Step 4: Implement errors.go
package calc

// ErrKind is an Excel error value. These are values, not Go errors: they
// live in cells, propagate through arithmetic, and round-trip through XLSX.
type ErrKind uint8

const (
	ErrNone ErrKind = iota
	ErrDiv0
	ErrValue
	ErrRef
	ErrName
	ErrNum
	ErrNA
	ErrCircular
)

func (e ErrKind) String() string {
	switch e {
	case ErrDiv0:
		return "#DIV/0!"
	case ErrValue:
		return "#VALUE!"
	case ErrRef:
		return "#REF!"
	case ErrName:
		return "#NAME?"
	case ErrNum:
		return "#NUM!"
	case ErrNA:
		return "#N/A"
	case ErrCircular:
		return "#CIRCULAR!"
	}
	return ""
}
  • [ ] Step 5: Implement the evaluator and graph

eval.go walks the AST. Rules the executor must honour:

  • Errors propagate. Any operand that is an error makes the result that error, left-most wins.
  • Numbers are IEEE doubles end to end (global constraint). No rounding at the evaluator boundary.
  • Env is an interface, so the graph can supply cached values and tests can supply a map. Nothing in calc imports store.

graph.go holds precedent and dependent edges keyed by (rowID, colID). Recalc marks dependents dirty transitively, then evaluates in topological order, returning the number of cells touched. Cycle detection is a colour mark during the topological walk; a node re-entered while grey yields ErrCircular rather than recursing.

Cap the dirty set per pass. Past the cap, stop and report — the caller tells the client to refetch its viewport rather than shipping a huge delta. Put the cap and its reason in a comment beside the constant.

  • [ ] Step 6: Run and watch them pass

Run: go test ./internal/sheets/calc/ -v Expected: PASS, cycle test completing well inside its timeout.

  • [ ] Step 7: Commit
git add internal/sheets/calc/
git commit -m "Evaluator, precedent graph, dirty-set recalc, cycle detection"

---

Task 8: The function library and the golden-vector harness

Files:

  • Create: internal/sheets/calc/fn_math.go, fn_text.go, fn_logic.go, fn_lookup.go, fn_date.go, registry.go
  • Create: tools/gencorpus/main.go
  • Create: testdata/corpus/formulas.json, testdata/corpus/volatile-excluded.txt
  • Create: internal/sheets/calc/corpus_test.go

Interfaces:

  • Consumes: Eval, Value (task 7).
  • Produces: calc.Register(name string, fn Func) and a populated registry. Func is func(args []Value) Value.

Scope: ~30 functions in this task, chosen because they cover most real business spreadsheets: SUM, AVERAGE, COUNT, COUNTA, MIN, MAX, ROUND, ROUNDUP, ROUNDDOWN, ABS, SQRT, POWER, MOD, IF, IFS, AND, OR, NOT, IFERROR, CONCAT, LEFT, RIGHT, MID, LEN, TRIM, UPPER, LOWER, VLOOKUP, INDEX, MATCH. The remaining ~90 follow the same pattern and are added in batches later.

  • [ ] Step 1: Write the corpus generator

tools/gencorpus/main.go drives LibreOffice headless over a list of expressions and records what a real spreadsheet returns. It is run rarely, by a human who has LibreOffice, and its output is committed. The test reads the committed file — a test that shells out to LibreOffice is a test that fails on a machine without it, which is the same reason rastrillo vendors axe rather than fetching it.

// Command gencorpus regenerates testdata/corpus/formulas.json from a real
// spreadsheet. Run it when adding functions; commit the result.
//
//	go run ./tools/gencorpus -out testdata/corpus/formulas.json
//
// Requires `soffice` on PATH. CI does not run this — CI reads the committed
// file, which is why the file is hash-pinned by TestCorpusIsPinned.
package main

The generator writes a .ods with one expression per row, converts with soffice --headless --convert-to csv, and pairs each expression with its computed value.

  • [ ] Step 2: Handle the volatiles, and check what is underneath you

Volatile functions — RAND, RANDBETWEEN, NOW, TODAY — produce a different value on every recalculation. In a golden corpus that makes the contract unfalsifiable, and the first person to hit it deletes the check rather than the nondeterminism.

Two rules, and the second is the one that is easy to miss:

  1. The exclusion list is a file somebody edits, not a filter somebody widens. testdata/corpus/volatile-excluded.txt, one function name per line, each with a comment saying why. A test asserts every name in it is a real registered function, so the list cannot rot into a wildcard.
  2. Pin the clock, then check nothing beneath you re-derives a time. A correct fix at the wrong layer still fails: docs-e6 ruled "fail loudly" on an entropy source, the implementer did exactly that, and a dependency swallowed the error one layer down and substituted a constant seed (2026-08-30). So after pinning, assert it: evaluate NOW() twice across a simulated clock advance and require the same answer inside one recalculation pass.
// A recalculation pass sees one instant. If anything under us reads the
// wall clock directly, this fails — which is the point. A pinned clock that
// something beneath re-derives is a pin that only looks like one.
func TestClockIsPinnedWithinAPass(t *testing.T) {
	env := newEnvAt(time.Date(2026, 8, 30, 12, 0, 0, 0, time.UTC))
	first := Eval(mustParse(t, "NOW()"), env)
	env.advanceWallClock(time.Hour) // does not touch the pass's instant
	second := Eval(mustParse(t, "NOW()"), env)
	if first.Num != second.Num {
		t.Fatal("NOW() moved inside one pass; something is reading the wall clock")
	}
}
  • [ ] Step 3: Write the corpus test
func TestAgainstGoldenCorpus(t *testing.T) {
	entries := loadCorpus(t) // reads testdata/corpus/formulas.json
	excluded := loadExclusions(t)
	for _, e := range entries {
		if excluded[e.Function] {
			continue
		}
		got := Eval(mustParse(t, e.Expr), corpusEnv)
		if !valuesMatch(got, e.Want) {
			t.Errorf("%s: %s = %v, LibreOffice says %v", e.Function, e.Expr, got, e.Want)
		}
	}
}

// The corpus is a contract. If it changes, that is a deliberate act with a
// regenerated hash, never a silent drift.
func TestCorpusIsPinned(t *testing.T) {
	sum := sha256File(t, "testdata/corpus/formulas.json")
	if sum != corpusSHA256 {
		t.Fatalf("corpus changed: %s\nIf deliberate, update corpusSHA256.", sum)
	}
}

// Every excluded name must be a real function, so the list cannot become a
// wildcard that quietly swallows new failures.
func TestExclusionsAreRealFunctions(t *testing.T) {
	for name := range loadExclusions(t) {
		if _, ok := registry[name]; !ok {
			t.Errorf("%s is excluded but is not a registered function", name)
		}
	}
}
  • [ ] Step 4: Run, fill in the pin, run again
go run ./tools/gencorpus -out testdata/corpus/formulas.json   # needs soffice
go test ./internal/sheets/calc/ -run TestCorpusIsPinned -v    # prints the sum
# paste the sum into corpusSHA256, re-run
go test ./internal/sheets/calc/ -v

Expected: PASS.

  • [ ] Step 5: Implement the functions, family by family

One file per family, each under the 300-line cap. Each function is registered by name in registry.go. A function receives evaluated Value arguments and returns a Value; argument-count and type errors return ErrValue, an unknown name returns ErrName.

Add functions in small commits — five or six at a time, each with its corpus entries — rather than one large commit. A failing corpus row then points at a small change.

  • [ ] Step 6: Commit
git add internal/sheets/calc/ tools/ testdata/
git commit -m "Function library, and a golden corpus that cannot drift"

---

Task 9: Number formatting — Go formats, the client paints

Files:

  • Create: internal/sheets/format/format.go, internal/sheets/format/format_test.go

Interfaces:

  • Consumes: calc.Value (task 7).
  • Produces: format.Display(v calc.Value, numFmt string) string

Why this task exists: ruling 2 put one engine behind cell values. A value is not what a cell shows. If the browser renders a currency string from a raw number, number formatting becomes a second implementation of the same truth — the exact bug class ruling 2 prevents, one layer down. So Go produces the display string and the client paints what it is handed. Formats are still edited as structured records; the client never sends a rendered string.

  • [ ] Step 1: Write the failing test
package format

import "testing"

func TestDisplay(t *testing.T) {
	for _, tc := range []struct {
		numFmt string
		num    float64
		want   string
	}{
		{"General", 1234.5678, "1234.5678"},
		{"0.00", 1234.5678, "1234.57"},
		{"#,##0.00", 1234.5678, "1,234.57"},
		{"0%", 0.075, "8%"},
		{"0.0%", 0.075, "7.5%"},
		{`"€"#,##0.00`, 1234.5, "€1,234.50"},
	} {
		got := Display(numValue(tc.num), tc.numFmt)
		if got != tc.want {
			t.Errorf("Display(%v, %q) = %q, want %q", tc.num, tc.numFmt, got, tc.want)
		}
	}
}

// An error value displays as its Excel text regardless of number format —
// a currency format must not render #DIV/0! as a currency amount.
func TestDisplayErrorIgnoresNumberFormat(t *testing.T) {
	if got := Display(errValue(calc.ErrDiv0), "#,##0.00"); got != "#DIV/0!" {
		t.Fatalf("error under currency format = %q", got)
	}
}
  • [ ] Step 2: Run and watch it fail

Run: go test ./internal/sheets/format/ -v Expected: FAIL — undefined.

  • [ ] Step 3: Implement

Support the format subset the tests cover plus dates. Rounding is half-away-from-zero, matching Excel rather than Go's default banker-ish strconv behaviour — write a test for 0.005 at "0.00" and pin it.

  • [ ] Step 4: Run and commit
go test ./internal/sheets/format/ -v
git add internal/sheets/format/
git commit -m "Number formatting in Go, so the client paints strings"

---

Task 10: The sheet handle, eviction, and crash recovery

Files:

  • Create: internal/sheets/store/handle.go, internal/sheets/store/handle_test.go

Interfaces:

  • Consumes: models (task 3), events (task 4), calc.Graph (task 7).
  • Produces: - store.Open(db, sealer, tabID) (*Handle, error) - (*Handle).Set(rowID, colID uint64, raw string, actor uint64) error - (*Handle).Value(rowID, colID uint64) calc.Value - (*Handle).Release()
  • [ ] Step 1: Write the failing crash-recovery test

This is the test that matters. A crash between the input write and its dependents' rewrite leaves cached values disagreeing with inputs — silently, and forever, unless the handle detects it.

func TestStaleCacheIsRecomputedOnOpen(t *testing.T) {
	db, sealer, tabID := seeded(t)

	h, _ := store.Open(db, sealer, tabID)
	h.Set(rowB, colA, "=A1*2", actor)
	h.Release()

	// Simulate a crash mid-recalc: bump A1's input version without
	// rewriting B1's cache. This is exactly what a panic between the two
	// writes leaves behind.
	db.Model(&sheets.Cell{}).
		Where("row_id = ? AND col_id = ?", rowA, colA).
		Updates(map[string]any{"input_version": 99})

	h2, err := store.Open(db, sealer, tabID)
	if err != nil {
		t.Fatal(err)
	}
	defer h2.Release()

	var b sheets.Cell
	db.Where("row_id = ? AND col_id = ?", rowB, colA).First(&b)
	if b.CacheVersion < 99 {
		t.Fatalf("stale cache served: CacheVersion %d < InputVersion 99", b.CacheVersion)
	}
}

// A tab past the cap fails honestly rather than dying slowly.
func TestCellCapIsAnHonestError(t *testing.T) {
	_, err := store.Open(db, sealer, oversizedTabID)
	if !errors.Is(err, store.ErrTabTooLarge) {
		t.Fatalf("oversized tab: %v, want ErrTabTooLarge", err)
	}
}
  • [ ] Step 2: Run and watch it fail

Run: go test ./internal/sheets/store/ -run 'TestStaleCache|TestCellCap' -v Expected: FAIL — undefined.

  • [ ] Step 3: Implement

handle.go holds:

// MaxCellsPerTab is a hard cap.
//
// The handle holds a decrypted tab plus its dependency graph in memory, so
// "hold it in RAM" is only true up to a number. 200,000 keeps a tab in tens
// of megabytes on a small shared box where many instances live. Google's
// limit is 10M and essentially nobody reaches it, so this trades a limit
// almost nobody meets for a memory ceiling everybody benefits from. Past it,
// an honest error rather than a slow death.
const MaxCellsPerTab = 200_000

Open counts cells, refuses past the cap, decrypts, builds the graph, and recalculates any cell whose CacheVersion trails its InputVersion before serving a single read. Set writes the input and a dirty marker in one transaction, then recalculates dirty-forward and clears the marker.

Handles are refcounted and evicted when idle; Release decrements.

  • [ ] Step 4: Run, then run the plaintext gate against a populated handle
go test ./internal/sheets/store/ -v

Add an assertion to the handle test that calls seal.AssertNoPlaintext after a Set and a checkpoint — the handle is the busiest writer, so it is the most valuable place to run the gate.

  • [ ] Step 5: Commit
git add internal/sheets/store/
git commit -m "The sheet handle: eviction, cell cap, and crash recovery"

---

Task 11: XLSX import and export

Files:

  • Create: internal/sheets/xlsx/import.go, internal/sheets/xlsx/export.go
  • Create: internal/sheets/xlsx/roundtrip_test.go
  • Create: testdata/corpus/xlsx/*.xlsx

Interfaces:

  • Consumes: models (task 3), format.Display (task 9), handle (task 10).
  • Produces: - xlsx.Import(r io.Reader, h *store.Handle) error - xlsx.Export(h *store.Handle, w io.Writer) error
  • [ ] Step 1: Write the failing round-trip test
// import -> export -> import must be stable. A file we wrote must open in
// Excel and LibreOffice; that half is checked by hand against the fixtures
// and recorded, because CI cannot open Excel.
func TestRoundTripIsStable(t *testing.T) {
	for _, name := range fixtureNames(t) {
		first := importFixture(t, name)
		var buf bytes.Buffer
		if err := xlsx.Export(first, &buf); err != nil {
			t.Fatalf("%s: export: %v", name, err)
		}
		second := importReader(t, &buf)
		if diff := diffTabs(first, second); diff != "" {
			t.Errorf("%s: round trip changed the sheet:\n%s", name, diff)
		}
	}
}

// Fills imported from XLSX keep their original hex so a file we did not
// author re-exports byte-identically in colour terms. The intent drives
// display; the retained hex drives export until a user re-picks.
func TestImportedFillHexIsRetained(t *testing.T) {
	h := importFixture(t, "fills.xlsx")
	f := formatOf(t, h, rowA, colA)
	if f.FillHex != "FF00B050" {
		t.Fatalf("FillHex = %q, want FF00B050", f.FillHex)
	}
}
  • [ ] Step 2: Run and watch it fail

Run: go test ./internal/sheets/xlsx/ -v Expected: FAIL — undefined.

  • [ ] Step 3: Implement
go get github.com/xuri/excelize/v2@v2.11.0

excelize is confined to this package. Nothing else in the tree imports it — its calc engine works against a file model rather than our dependency graph, so borrowing it would couple our storage to theirs and be slow.

Import maps cells, formulas, number formats and fills; fill hex is retained verbatim alongside the nearest OKLCH intent. Export resolves intent to concrete hex, or emits the retained hex when the user has not re-picked.

  • [ ] Step 4: Route blobs through blobs.Sealed

Uploaded and generated files are whole spreadsheets. Unsealed on the S3 backend, that is a plaintext spreadsheet in a bucket — the one rule defeated through a side door.

// ContentType is deliberately opaque. blobs.Sealed encrypts the bytes but
// passes ContentType through as visible metadata, so a truthful
// spreadsheet content type would announce exactly what the ciphertext is,
// in a bucket that is otherwise opaque.
const blobContentType = "application/octet-stream"

Note also that the blob address is the ciphertext hash, so deduplication is per key, and Ref.Size is the sealed size — any quota or progress figure shown to a person must account for that or it will be visibly wrong.

  • [ ] Step 5: Keep content out of job errors

Import runs as a background job. Its error text reaches the owner and the platform's logs, which we cannot grep.

// Errors carry coordinates and codes, never content. "cannot parse B7" is
// a good error; quoting the cell's text into carlos logs ships user content
// to storage we do not control.
return fmt.Errorf("xlsx: cannot parse cell %s: %w", calc.A1(rowPos, colPos), errBadNumber)
  • [ ] Step 6: Run and commit
go test ./internal/sheets/xlsx/ -v
CGO_ENABLED=0 go build ./... && go vet ./... && go test ./...
git add internal/sheets/xlsx/ testdata/
git commit -m "XLSX import and export, sealed blobs, content-free errors"

---

Deferred to plan 2 (design-system dependent)

Named here so their absence is a decision rather than a gap: auth screens, the Jspreadsheet grid surface and its chrome, sharing UI, live sync over SSE, presence, the home contract calls, version-history UI, conditional formatting UI, comments, charts.

Gate 11 — computed value to painted output — belongs to plan 2 and is the gate most likely to be dropped as "hard to write" during execution. Naming it here is the defence: gates 4 and 7 prove the engine matches LibreOffice and that the client runs, and neither proves a person sees what the engine computed.

Self-review notes

  • Spec coverage: §4.1–§4.5, §5, §11 and §12 gates 1, 3, 4, 9, 10 are covered by tasks 1–11. §6–§10 and gates 2, 5, 6, 7, 8, 11 are plan 2.
  • Type consistency: calc.Value is produced in task 7 and consumed unchanged in 8, 9 and 11. store.Handle is produced in 10 and consumed in 11. seal.Sealer is produced in 2 and consumed in 4 and 10.
  • Known soft spots, flagged rather than hidden: task 5 leaves the lexer and parser bodies to the executor with a described shape rather than full code — they are mechanical and long, and writing them out here would not reduce the risk. Task 8's generator is sketched rather than written because its exact shape depends on the LibreOffice version to hand.