Persistence

Every service that stores data owns one SQLite database file, used through kit/sqlstore (ADR-0019: SQLite First, rqlite Later, ADR-0016: Durable Orchestration in the Database). The driver is modernc.org/sqlite (pure Go, no cgo).

Rules

• Reads use DB.Query / DB.QueryRow.

• Writes use DB.Batch only. A batch is a list of statements executed atomically, with no Go code running between them. Never open an interactive transaction: rqlite, the planned high-availability backend, cannot execute them.

• Optimistic concurrency: every mutable row has a version column. Updates are guarded with sqlstore.ExecOne:

  err := db.Batch(ctx,
      sqlstore.ExecOne(
          `UPDATE tickets SET state = ?, version = version + 1
           WHERE id = ? AND version = ?`, next, id, expectedVersion),
      sqlstore.Exec(
          `INSERT INTO events (entity_id, kind) VALUES (?, ?)`, id, "state_changed"),
  )
  if errors.Is(err, sqlstore.ErrConflict) {
      // someone else changed the ticket: re-read and decide again
  }
  

  If the guarded statement does not change exactly one row, the whole batch is rolled back and ErrConflict is returned.

How the guard works

After each ExecOne statement the batch runs INSERT OR REPLACE INTO ballet_guard (id, ok) VALUES (1, changes() = 1). ballet_guard.ok has a CHECK (ok = 1) constraint named ballet_conflict. When the guarded statement changed no row (or more than one), the constraint fails, which aborts the batch in SQLite and in rqlite alike; sqlstore maps that error to ErrConflict. The check is pure SQL, so it works identically when the batch is sent to rqlite.

Migrations

Each service embeds its migrations and applies them at startup:

//go:embed migrations/*.sql
var migrations embed.FS

sub, _ := fs.Sub(migrations, "migrations")
err := db.Migrate(ctx, sub)

• Files are named NNNN_description.sql (0001_tickets.sql) and applied in version order, each atomically with its schema_migrations row.

• Never edit an applied migration; add a new one.

• A migration may contain several statements.

Vector functions

sqlstore registers Go implementations of the sqlite-vec functions Ballet uses, with identical names and semantics:

Function	Meaning
vec_f32(x)	JSON array text ('[0.1, 0.2]') → vector blob (little-endian float32); blobs pass through
vec_distance_cosine(a, b)	1 - cosine similarity: 0 same direction, 1 orthogonal, 2 opposite

From Go, encode vectors with sqlstore.EncodeFloat32. Similarity search is an exact scan ordered by distance (no approximate index), see ADR-0021: Hybrid Vector Search over All Content.

Tests

Tests run against real databases:

db := sqlstoretest.New(t, migrationsFS) // temp file, migrated, closed after the test

Concurrency-sensitive code is tested with several goroutines and -race.