Root store hands out entity-scoped stores; the scope rides the handle, not the args

byob-storage.7 interfacesstorage

Problem: byob-storage.1 gestures at a two-tier store for multi-tenant CLIs and says "skip it if you're single-tenant". This decision is the full shape, for when you are multi-entity — one binary managing many projects/tenants/sites, every data row carrying the entity's foreign key. The naive alternatives both age badly: threading entityID through every method turns a 20-method interface into a 20-method interface with a stutter argument, and leaving it out of the signature means every SQL statement hand-writes the same WHERE entity_id = ? — until one forgets it and a query silently reads across partitions.

Idea: two tiers over one *sql.DB:

RootStore owns the entity table and nothing else: EnsureEntity(ctx, key..., name) (int64, error) (an upsert returning the id — idempotent, so command code never does get-or-create dances), ListEntities(ctx), ForEntity(id) Store, Close().
Store (the scoped tier) is the whole domain interface. The implementing struct carries entityID as a field, set once by ForEntity, and stamps it into every query. Scope is captured in the handle; method signatures never mention it, and no query can forget it because the field is right there in the one file that writes SQL.

Both tiers share the underlying *sql.DB pool — ForEntity is a cheap struct literal, not a second connection.

The Factory (byob-factory-di.1) exposes both as lazy closures: a RootDB for commands that enumerate or manage entities, and an EntityDB that resolves the current entity (from a --project/ --tenant flag or the config's default), calls EnsureEntity, and returns the scoped handle. Most commands only ever see EntityDB and stay oblivious to the partitioning.

The handle generalizes: further read-scoping (pin to a snapshot, pin to a config version) becomes WithX(...) Store methods that return a copy of the struct with one more field set — the same capture-in-the-handle move, composable and immutable.

Tradeoffs: two interfaces to fake in tests (the root fake is four func fields; see byob-testing.5). Cross-entity queries don't fit the scoped tier — they become RootStore methods or a loop over ListEntities, which is the honest shape for them anyway. And the entity FK must be in every table from the first migration; bolting it on later is a data migration, not a refactor.

Design

// internal/storage/store.go
type RootStore struct{ db *sql.DB }

// If entities are defined by a config file, widen the unique key to
// (slug, config_id) and reject an empty config ID — two configs that
// define the same slug must not share a row (see byob-config.4).
func (r *RootStore) EnsureEntity(ctx context.Context, slug, name string) (int64, error) {
    _, err := r.db.ExecContext(ctx,
        `INSERT INTO entities (slug, name) VALUES (?, ?)
         ON CONFLICT(slug) DO UPDATE SET name = excluded.name`, slug, name)
    if err != nil { return 0, fmt.Errorf("ensure entity: %w", err) }
    var id int64
    err = r.db.QueryRowContext(ctx,
        `SELECT id FROM entities WHERE slug = ?`, slug).Scan(&id)
    return id, err
}

// ForEntity returns an entity-scoped Store sharing the pool.
func (r *RootStore) ForEntity(id int64) Store {
    return &scopedStore{db: r.db, entityID: id}
}

type scopedStore struct {
    db         *sql.DB
    entityID   int64
    snapshotID int64 // 0 = unpinned; set by WithSnapshot
}

func (s *scopedStore) ListItems(ctx context.Context) ([]Item, error) {
    rows, err := s.db.QueryContext(ctx,
        `SELECT id, name FROM items WHERE entity_id = ? ORDER BY id`, s.entityID)
    // ... every query in this file binds s.entityID; nothing to forget elsewhere
}

// Further pins compose the same way — copy the handle, set a field:
func (s *scopedStore) WithSnapshot(id int64) Store {
    s2 := *s
    s2.snapshotID = id
    return &s2
}

// Factory wiring — commands depend on the tier they need:
f.RootDB = sync.OnceValues(func() (*storage.RootStore, error) { ... })
f.EntityDB = func() (storage.Store, error) {
    ctx := context.Background() // factory closures have no caller ctx
    ent, err := f.CurrentEntity() // flag override or config default
    if err != nil { return nil, err }
    root, err := f.RootDB()
    if err != nil { return nil, err }
    id, err := root.EnsureEntity(ctx, ent.Slug, ent.Name)
    if err != nil { return nil, err }
    return root.ForEntity(id), nil
}