Database — your tables, your migrations

One PostgreSQL database, two owners:

Schema reaches the database as ordered SQL files committed to your repo, applied on every deploy before any app service starts. Engine migrations run first, then yours.

Related: contract.md § 8 (the ownership rule), operations.md § Database (backups, SSL, prod access).

1. Declare tables

Create db/schema.ts in your brain root. The path is convention — nothing to configure; the engine probes for it.

import { pgTable, text, timestamp, uuid } from '@omnislash-ai/infra/drizzle';

export const customers = pgTable('customers', {
  id: uuid('id').primaryKey().defaultRandom(),
  name: text('name').notNull(),
  email: text('email'),
  createdAt: timestamp('created_at', { withTimezone: true }).defaultNow().notNull(),
});

Import pgTable and the column types from @omnislash-ai/infra/drizzle, not from drizzle-orm/pg-core. The engine re-exports every pg-core primitive from that one subpath, and it is the only import that is guaranteed to resolve to the same drizzle instance the engine runtime uses. A direct drizzle-orm/pg-core import can resolve to a second physical copy of drizzle — your tables then belong to a different registry than the client that queries them.

Rules:

• Bare pgTable() only. No pgSchema('omnislash') — that is the engine’s schema, and writing there is not yours to do.

• db/schema.ts exports YOUR tables and nothing else. Never re-export an engine table from it. Your migration line is generated from that module alone; an engine table (or an engine enum) leaking into it makes your migrations try to re-create engine objects, and the two lines start fighting over the same DDL.

• Importing an engine table for a foreign key is fine — the import just must not be re-exported:

  import { agents } from '@omnislash-ai/infra/drizzle';
  import { pgTable, text, uuid } from '@omnislash-ai/infra/drizzle';
  
  export const tickets = pgTable('tickets', {
    id: uuid('id').primaryKey().defaultRandom(),
    agentId: text('agent_id').references(() => agents.id),
    subject: text('subject').notNull(),
  });

  Engine migrations always run before yours, so omnislash.agents exists by the time this FK is created. A column referencing an engine id is text, whatever your own keys are: engine ids are opaque text (ses_…, conv_…, older rows a uuid), and a foreign key needs both sides of one type. Never parse an id’s shape — its prefix is a display hint, not a validity check. A view of yours that reads an engine agent or conversation id column (omnislash.agents.id, conversation_events.agent_id, …) blocks the engine migration that moved those columns from uuid to text (f2_ids): Postgres refuses to retype a column a view reads. That migration names every such view and stops; drop them, deploy, and recreate them.

• Table names must not collide with engine table names. Different PG schemas make a collision legal, but it makes every raw query ambiguous — don’t.

Query them from brain code with the shared client:

import { db, eq } from '@omnislash-ai/infra/drizzle';
import { customers } from '../db/schema.ts';

const rows = await db.select().from(customers).where(eq(customers.name, 'test'));

The query operators come from the same subpath, for the same reason as pgTable — and because your brain has no drizzle-orm dependency to import them from: the sql tag, filters (eq, ne, gt, gte, lt, lte, and, or, not, inArray, notInArray, isNull, isNotNull, between, like, ilike, exists, the array* operators, …), aggregates (count, countDistinct, sum, avg, min, max), ordering (asc, desc), getTableColumns, and the SQL / InferSelectModel / InferInsertModel types.

2. Generate a migration

omnislash db generate --name add_customers

This diffs db/schema.ts against the last committed snapshot — no database is involved, so it works offline and never touches production — and writes three files under db/migrations/:

db/migrations/
├── 0000_add_customers.sql          ← the DDL that will run
└── meta/
    ├── 0000_snapshot.json          ← the schema AFTER this migration
    └── _journal.json               ← the ordered index

Read the SQL before committing it. It is the thing that will run against your production data; nothing downstream will second-guess it.

Commit all three files together. The snapshot is the anchor the next diff is computed from — commit the .sql alone and the following generate re-emits everything you just wrote.

generate re-prints, under a loud [!] This migration DESTROYS data, every statement that drops a column, table, type or schema. If you did not intend a drop, stop and read § 4.

3. Apply it

On deploy — automatic. The deploy stops every engine service, then a one-shot db-migrate service applies both lines, then the new version starts (operations.md § Brain CI/CD). Nothing of the previous version runs while your migration does, so a drop or a rename in one migration is fine — no code is left reading the old name. A failing migration exits non-zero and the deploy stops with the services still stopped: there is no rollback once migrations have started, because the previous version may not read what they already changed. Fix forward and deploy again.

Locks, and downtime. Your migration runs inside the deploy’s downtime, so every second it takes is a second your brain is down. A migration still waits at most migrations.lockTimeoutMs (engine default 5 s) for a table something else holds — the deploy stops the engine, not a backup’s pg_dump, an admin session or a service of your own — and retries up to migrations.lockAttempts times (default 10) before the deploy stops; raise either in omnislash.config.ts if needed. On a large table keep the work short: CREATE INDEX CONCURRENTLY (§ 4), a constraint added NOT VALID and validated in a later migration.

By hand — when your database is external (BYOI) and you want to migrate ahead of a deploy, or locally:

export DATABASE_URL='postgresql://…'
omnislash db migrate     # apply pending migrations: engine line, then yours
omnislash db status      # what is applied, what is pending, per line

One exception: the very first run on a database that predates migrations must go through a deploy. That run adopts (§ below) and adoption introspects the live schema through drizzle-kit, whose introspection the engine has to patch to bind query parameters at all. The patch ships inside the engine image but cannot ship inside an npm package, so running that first migrate from your own project answers cannot run the drift check from this project. Every run after it is ordinary SQL and works from anywhere. If you would rather not wait for a deploy, reconcile the database by hand and record the line with omnislash db baseline --force, which introspects nothing.

status is read-only and creates nothing. Typical output:

engine  …/@omnislash-ai/infra/migrations
  [✓] 0000_baseline
brain   …/my-brain/db/migrations
  [✓] 0000_add_customers
  [ ] 0001_add_orders  (pending)

Engine schema changes arrive with the engine version. omnislash upgrade bumps the pin, the new engine migrations ride along inside @omnislash-ai/infra, and the next deploy applies them. You never generate or edit engine migrations. When one needs something from your brain (a view or foreign key of yours over a column it changes, a data fix), the upgrade task says so and walks the agent through it (upgrading.md).

Your first deploy on a database whose tables are already live (one that predates migrations) is adopted, not replayed: the engine proves the live schema already carries everything the shipped one declares and stamps the journal without executing anything. You will see no drift — adopting in the deploy’s log (the GitHub Actions Deploy step — the migration runs as a throwaway docker compose run --rm db-migrate, so there is no container left to docker compose logs afterwards). If it instead reports blocking differences, the database is missing something the build declares — reconcile it before deploying, or, if you have already handled the difference by hand, record the line with omnislash db baseline --force (it stamps and runs nothing).

Tables your agents created along the way — db_execute writes to public and needs no approval — do not block adoption, and they do not trigger it either. Adoption keys on the tables your schema declares, so an agent-created table neither fails the proof nor makes the engine mistake a never-migrated database for a push-built one. They are listed in the logs as objects the schema does not declare and left exactly where they are; the engine never drops something you did not ask it to drop.

So if your brain already has its tables live, generate the baseline on its own — run generate with db/schema.ts exactly as it is today, commit that 0000, deploy it, and only then add the new table in a second migration. Bundling the new table into the baseline makes it read as blocking drift (it is not live yet) and the deploy stops. If your brain is declaring its first table ever, none of this applies — the migration just runs.

4. Renames

The generator cannot tell a rename from a drop-and-add — the two look identical when diffing two schema snapshots — and it resolves the ambiguity as drop + add, which loses the data. It never prompts (a prompt inside a deploy container would hang the deploy forever).

So: rename in db/schema.ts, run generate, then hand-edit the SQL before committing.

-- what the generator wrote
ALTER TABLE "customers" DROP COLUMN "name";--> statement-breakpoint
ALTER TABLE "customers" ADD COLUMN "full_name" text NOT NULL;

-- what you commit
ALTER TABLE "customers" RENAME COLUMN "name" TO "full_name";

This is safe for the schema: the snapshot is derived from your TypeScript, not from the SQL, so both forms describe the same end state and the next generate diffs cleanly.

Ship the rename and the code that reads full_name in the same deploy: nothing of the previous version runs while the migration applies (§ 3). The same applies to anything the generator writes clumsily — a data backfill, a USING clause on a type change, a NOT NULL added in two steps.

Statements are separated by --> statement-breakpoint. Keep the separators when you edit.

CREATE INDEX CONCURRENTLY cannot run inside a transaction. When your diff contains one, the generator puts -- omnislash:no-transaction on the first line and the whole file runs in autocommit — which means a failure leaves it half-applied and the retry replays it from the top, so every statement in such a file must be idempotent. Prefer moving the concurrent index into a migration of its own so the rest of your change stays atomic.

5. What your agents do with these tables

Your tables are also the surface your agents work through, with three engine tools. Worth knowing when you design a table, because these are the ergonomics your agents will live with:

• db_schema — column names, nullability, defaults and exact types, spelled the way a cast or a column definition takes them: text[], numeric(10,2), character varying(255), and a type outside public schema-qualified, because that is how db_query must write it — omnislash."ExecutionStatus" for an engine enum. An enum column (or an array of one) also carries its values, the labels in the enum’s own order, so WHERE status = 'running' is written from the answer rather than guessed. Plus three facts about the relation itself: its kind (table / view / materialized / foreign / partitioned), the definition of a view or a materialized view (the stored SELECT, formatted), and a table’s constraints (PRIMARY KEY / UNIQUE / CHECK / FOREIGN KEY, each as Postgres prints it). Those three are what make the tool usable by an agent that models in SQL rather than just reading: it can check the view it just created says what it meant, and it can learn that status is already bounded by a CHECK (status IN ('open','closed')) instead of re-deriving that rule in prose every turn. It reads the Postgres catalog, not information_schema, which names every enum USER-DEFINED, every array ARRAY and does not list materialized views at all. It covers public (your tables) and omnislash (engine tables), and every entry says which schema it came from, so an agent can tell public.agents from omnislash.agents and knows which name needs qualifying. schema narrows to one, table is a LIKE filter. Each entry also carries the table’s own SQL comment as note — the semantics a column list cannot show. Engine tables use it for exactly that: omnislash.agents says it is a sliding retention window, not a journal, so an agent that reads the note stops asking it “did this happen in the last 14 days”. Put a COMMENT ON TABLE on your own tables for the same reason. Reading the engine schema is fine and often necessary; writing to it is not (below). A bare call describes two whole schemas and so is the one most likely to hit the 1000-column cap — it says so with truncated: true, and the honest read of that is “narrow it and ask again”, not “that is all the columns there are”.

• db_query — read-only SQL. Every statement reports on its own: the answer is { results: [...] }, one entry per ;-separated statement in the order they ran, each with its command — the first word of Postgres’ command tag (SELECT, UPDATE, and CREATE for any CREATE …) — and its own rowCount, plus rows and truncated exactly when the statement returns a result set (SELECT, … RETURNING, SHOW, EXPLAIN). So an UPDATE without RETURNING has no rows key at all, while a SELECT that matched nothing has rows: [] — “returns nothing” and “returned zero rows” are different facts, and the shape says which. A lone statement is simply a list of one. One 1000-row cap covers the whole call, spent in statement order; the statement it cuts says truncated: true, and its rowCount still says how many rows it really had. DDL carries no count of its own, so it reports rowCount: 0. The odd ones out are CREATE TABLE … AS, CREATE MATERIALIZED VIEW and SELECT … INTO: Postgres tags them SELECT n, so they come back as command: 'SELECT' with the rows they stored as rowCount and no rows. A batch runs as one transaction, and the tool owns that transaction, so a batch that writes its own BEGIN / COMMIT / ROLLBACK is refused outright rather than run: an embedded COMMIT would end the tool’s transaction and let everything after it run unprotected — writes through a read-only tool, and a dryRun that persists. Values go in params, bound to $1…$n and never quoted into the SQL text: anything with quotes, whatever a user typed, JSON (pass it as a string and cast, $1::jsonb). Scalars only — string, number, boolean, null — so an array or an object is always a string plus an explicit cast ($1::text[], $1::jsonb) rather than the driver’s own guess. With params the call is ONE statement: Postgres’ extended protocol carries one and answers a ;-batch with 42601 cannot insert multiple commands into a prepared statement, so write it as one statement with RETURNING or a CTE. An empty params: [] counts as none, so a batch without values still runs. A failed query comes back with everything Postgres diagnosed, not just the message: DETAIL, HINT, CONTEXT, RELATION and SQLSTATE, each on its own line and each clipped on its own at 500 characters, so a huge DETAIL can never push the HINT out — and the position the way psql prints it, the line of your SQL it falls on with a caret under the character, counted across the whole batch:

  LINE 3: SELECT nope FROM checkins
                 ^

  A long line is windowed to about 120 characters around the caret, with … where it was cut. Same on db_execute; db_schema runs the engine’s own SQL, so a position there stays a bare POSITION: n. The HINT is often literally the fix — Perhaps you meant to reference the column "h.responded_at". — so an agent that mistyped a column can correct itself from the answer instead of re-reading the whole schema. Postgres does not fill a hint for every error class; SQLSTATE is there for all of them.

• db_execute — writes, moderate risk, and by default no approval card. Same result shape and the same params as db_query, plus rolledBack. It commits unless the agent passes dryRun: true, which runs the statement for real inside a transaction and then rolls it back: honest row counts, nothing persisted, rolledBack: true in the result. dryRun is not a cheaper permission — it still opens a write transaction and still needs db:write, so if you raise that capability to hitl: true in constitution/engine-capabilities.json, rehearsals get a card too.

search_path is pinned to public for both query tools, so your tables are reachable by bare name and engine tables always need omnislash.. That is the isolation boundary — it governs what resolves unqualified, not what is knowable, which is why introspection deliberately reaches wider.

A table an agent creates through db_execute is a real table in public that your schema does not declare. The engine leaves it alone: it neither blocks adoption nor gets dropped (§ 3). If you want it to be part of your schema, declare it in db/schema.ts and generate a migration — otherwise it stays outside your migration line for good.

Such a table survives a redeploy. A deploy replaces the brain image — your code, your db/schema.ts, your constitution — and runs the two migration lines; it does not rebuild the database. The thing a redeploy wipes is an uncommitted edit to db/schema.ts made inside a running container, because db/ is image-baked and never hot-synced (contract.md § 2). That is a fact about the file, not about the tables, and the two get confused often enough to be worth stating: an agent that models its own state in Postgres is not building on sand.

Which is a whole way of working, not an edge case. An agent can own its structured state — facts in tables it creates and evolves with DDL, a small prefixed view per thing it needs to see — and the engine renders those views into its context on every turn, automatically. That is state.md; everything in this section is the surface it is built on.

What not to do

• Never edit a migration that has already been applied anywhere. Each file’s checksum is recorded when it runs; an edited file fails the next migrate with a checksum mismatch, and refuses to go further. Ship the correction as a new migration.
• Never renumber, reorder or delete committed migrations. A migration recorded in the database but missing from the build fails the run (“the database is ahead of this build”), and a new file numbered below the applied high-water mark is refused outright — it would produce a state that no fresh replay reproduces. Always generate on top of the current head.
• Never move db/schema.ts while db/migrations/ exists. That path is how the engine finds your line at all; refactoring it into db/schema/index.ts would otherwise make migrate apply the engine line, exit 0, and bring your apps up against DDL that never ran. The engine now fails loudly instead — but the fix is to keep the file (re-export from a db/schema/ folder if it has outgrown one file), not to work around the error.
• Never write to omnislash.* — not with db_execute, not with psql, not with a migration of yours. Reading it is fine and db_schema shows it to your agents on purpose; writing is what makes an engine upgrade fail to apply. Engine state is the engine’s.
• omnislash db push is not available to you. It is a drizzle-kit wrapper that ships only in the engine source tree (the npm package carries dist and migrations, not scripts), so from a brain it answers ENGINE_NOT_FOUND — by construction, not by policy. To iterate on a table shape, point DATABASE_URL at a database you are willing to throw away and run generate + migrate; delete the 0000 and regenerate until the shape is right, then commit it.
• Don’t put db/ in brainSync.additional. It is read at boot before any volume is mounted; the engine rejects it (see contract.md § 2).