Cubby schema and migrations

A cubby’s schema is a directory of numbered SQL migrations. The same rules apply whether a workflow’s cubby steps or a code agent’s ctx.cubby uses it. What a cubby is and how each kind of agent reads and writes it is on Cubbies.

Where migrations live

Declared by	Location	Published with
One agent or workflow	`cubbies: [{ alias, migrations: "./migrations/<alias>" }]` in `cef.config.ts`	`cef push` (inside the agent’s manifest)
The Agent Service	`cubbies/<alias>/*.sql` in the service project	`cef cubby push --bucket <id>`. See Cubbies for its options.
The Agent Service, in ROC	Cubbies → New cubby (an alias and its first migration), then Add migration per cubby	ROC

Aliases are 1 to 64 letters, digits, -, or _. Avoid memory: the Memory Bank is ctx.memory, and a cubby with that name only causes confusion (cef build warns about it). Do not use runs: it is the workflow runner’s own state.

When migrations run

Declared	Migrations run
In an agent’s or workflow’s `cubbies`	When a vault connects it. A failing migration fails the connect with `CUBBY_PROVISION_FAILED`, and nothing is left behind.
On the Agent Service	Publishing creates no database. The platform creates a vault’s copy the first time an agent or step touches the cubby, and applies every pending migration then.
Nowhere	An alias nobody declared is created empty on first use.

Migration rules

Files are named NNN-description.sql. The leading number is the migration’s version; a file that does not start with digits fails the build.

cubbies/crm/
├── 001-init.sql
├── 002-crm-notes.sql
└── 003-crm-contacts-email-index.sql

The platform records the highest applied version in a schema_migrations table in each cubby. On each touch it runs, in order, only the files whose version is greater than that.

Never edit an applied migration. The platform tracks migrations by version number, not by content, so an edited file is silently skipped in every vault that already applied it. Its new SQL runs only in vaults that have never seen that version, and your vaults drift apart.

Make every change a new, higher-numbered file. A file numbered at or below the highest applied version is skipped too, so do not fill gaps or renumber.

Create new tables only in new files. Add a table with a new migration, never by extending 001-init.sql.

Prefix table names. Every agent in the service shares the cubby, and two agents that both create messages collide. Prefix tables with the agent or feature they belong to: crm_contacts, sensor_readings.

Each migration runs in a transaction with its version record. A migration that fails rolls back and is retried on the next touch.

Change a table

SQLite cannot alter a CHECK constraint or drop most constraints in place. Rebuild the table in a new migration:

-- 004-crm-contacts-status.sql
CREATE TABLE crm_contacts_new (
  id         TEXT PRIMARY KEY,
  email      TEXT NOT NULL,
  status     TEXT NOT NULL CHECK (status IN ('new', 'active', 'won', 'lost')),
  updated_at TEXT NOT NULL
);
INSERT INTO crm_contacts_new SELECT id, email, status, updated_at FROM crm_contacts;
DROP TABLE crm_contacts;
ALTER TABLE crm_contacts_new RENAME TO crm_contacts;

Adding a nullable column needs only ALTER TABLE … ADD COLUMN.

Table conventions

Text keys. Use TEXT PRIMARY KEY with an id you control, such as the event id or a natural key, so redelivered events upsert instead of duplicating.
References without FOREIGN KEY. Store the referenced id in a TEXT column and join in your code. This avoids cascade surprises when a migration rebuilds a table.
JSON for evolving shapes. Store nested documents as JSON in a TEXT column suffixed _json, and read them with SQLite’s JSON functions.
Closed states. Enforce a state machine with CHECK (status IN (…)); widen it with a rebuild.
Idempotent writes. INSERT … ON CONFLICT DO NOTHING, ON CONFLICT DO UPDATE, or INSERT OR IGNORE.

Vector search

The cubby loads the sqlite-vec extension. Store vectors as BLOB, convert JSON-array text with vec_f32(?), and rank in SQL:

-- 005-crm-note-embeddings.sql
CREATE TABLE crm_note_embeddings (
  note_id   TEXT PRIMARY KEY,
  embedding BLOB NOT NULL,
  model     TEXT NOT NULL,
  dims      INTEGER NOT NULL
);

await ctx.cubby("crm").exec(
  "INSERT INTO crm_note_embeddings(note_id, embedding, model, dims) VALUES (?, vec_f32(?), ?, ?) ON CONFLICT(note_id) DO UPDATE SET embedding = excluded.embedding",
  [noteId, JSON.stringify(vector), "my-embedder", vector.length],
);
const nearest = await ctx.cubby("crm").query(
  "SELECT note_id FROM crm_note_embeddings ORDER BY vec_distance_cosine(embedding, vec_f32(?)) LIMIT 5",
  [JSON.stringify(queryVector)],
);

Pass vectors as JSON text, not as typed arrays.