Database Management CLI

Purpose & relationship to scaffolding (spec 18)

Spec 18's twilldb new/init only writes files — it never opens a database. Management is the opposite: every command here talks to a real database. That single difference drives the whole design (a transport, the single-writer lease, the engine linked into the binary). The two halves share one binary and one help surface; the scaffolder stays dependency-free, and the management half is additive behind a cargo feature.

Design principles

• MUST reach durable state only through the engine's public API (the twill-engine crate's Connection: exec/query/prepare/catalog/branch/stats) or over the pgwire wire — never by opening a file, socket, or cloud SDK directly. No new engine or storage-seam surface is required; management is a consumer of what Phases 1–6 already expose.
• MUST choose its transport by the connection-string scheme, exactly like the engine chooses its backend: file:///s3:// → embedded (link the engine); postgres:// → a pgwire client. Unknown schemes are rejected, never defaulted.
• MUST respect the single-writer lease (see below) — the CLI is a writer when it opens a database embedded, so it must not be used to mutate a database a server/app currently holds; manage a live deployment over postgres:// instead.
• MUST be additive and feature-gated: the management commands link twill-engine behind a manage cargo feature, so a default build remains the lean, dependency-free scaffolder (the same wall twill-bench's custom-profile feature uses).
• SHOULD be safe-by-default: read-only commands (sql SELECT, tables, describe, stats, gen types) run anywhere; destructive commands (db reset, migrate up on a non-empty db) confirm or require an explicit flag.
• MUST NOT add a GUI, a cloud/login surface, or a hosted control plane — there is no Twill cloud; link/login/studio are explicitly out of scope.

Transport model & the single-writer caveat

Two transports, chosen by URL scheme:

Command surface

The full proposed surface, with the engine capability each rides and a feasibility note. Milestone assignment is in Phased delivery.

Migrations design

A directory of ordered, immutable SQL files plus a tracking table in the database. The model is intentionally close to Supabase / dbmate / golang-migrate so it's familiar.

Layout

migrations/
  20260627120000_init.sql
  20260627123000_add_notes_index.sql
  ...

Tracking table

Created on first migrate up if absent. Names are underscored to stay out of the user's namespace:

CREATE TABLE _twilldb_migrations (
  version    TEXT PRIMARY KEY,   -- the file's timestamp prefix
  name       TEXT NOT NULL,
  checksum   TEXT NOT NULL,      -- hash of the file at apply time (drift detection)
  applied_at TEXT NOT NULL       -- ISO-8601
)

Apply semantics

• MUST apply pending versions (those not in the tracking table) in ascending order, one file at a time, recording each in _twilldb_migrations on success.
• MUST split a file into statements and run them in order. DML-only files run inside one transaction; a file containing DDL runs its DDL in autocommit (see the constraint below) — so a file is the unit of ordering, not always of atomicity.
• MUST stop on the first failing statement and report which migration/statement failed; already-applied earlier migrations remain applied.
• SHOULD detect drift: if an already-applied file's checksum no longer matches, migrate up/status warns (a previously-applied migration was edited — a footgun in every migration tool).

Type generation

gen types reflects the live schema through catalog() (the same TableSchema — columns, types, PK, FK, unique sets — the server already uses for PostgREST reflection) and emits TypeScript for the Bun client: a row interface per table, with the engine's value types mapped to TS (INTEGER/REAL/TEXT/BLOB → number/number/string/Uint8Array, vector(N) → number[], nullable columns → | null). The output is a single .ts file the app imports, keeping db.query<Row>() calls statically typed against the real schema. This is the most @twilldb/bun-aligned command and a strong reason to do it early.

Binary shape & dependencies

One binary, two halves. The scaffolder (spec 18) stays dependency-free. Management is gated behind a manage cargo feature that pulls in twill-engine (and, when postgres:// support lands, a minimal pgwire client — reuse the bench driver's pgclient rather than a new dependency). A default cargo install twilldb-cli compiles no management code; --features manage (or the release build) includes it. The engine itself carries no heavy third-party dependencies (it hand-rolls its SQL parser, WAL codec, and base64), so even the management build stays auditable. The Homebrew/release builds ship the full-featured binary; the lean build remains available for embedders who only want the scaffolder.

Phased delivery

1. Milestone 1 — inspect & migrate (embedded) (implemented): sql, shell, tables/describe, migrate new/up/status, gen types, seed, stats — all over the embedded (file:///s3://) transport, behind the manage feature. Covers "create schema, check data, run migrations." Each command ships with a test (and migration apply/idempotency/drift is covered end-to-end on file://).
2. Milestone 2 — branches & serve (implemented): branch create/list/delete (branches are durable on the base and addressed as <url>#branch=<id>), migrate up --branch (preview-and-swap — applies pending migrations to a zero-copy fork, leaving the base untouched until you re-run migrate up on it to promote), db reset (drop → re-migrate → seed, --force on a non-empty database), schema dump (reconstructed CREATE TABLE DDL from catalog()), and serve (the engine behind a Postgres-wire listener, composing twill-server in-process). The branch commands are thin consumers of the engine's additive branch API (Connection::create_branch/list_branches/delete_branch/open_branch) over the Phase-4 storage seam; no seam or C-ABI change. Each command ships with a test (branch isolation and migrate-preview verified end-to-end on file://).
3. Milestone 3 — live deployments (implemented): the postgres:// pgwire transport across the read/inspect/migrate commands (sql, shell, tables/describe, migrate up/status, seed, gen types, schema dump, db reset, stats), so the same surface manages a running engine-server — the server stays the sole writer and the CLI is just a wire client, the single-writer-safe path for a live database. The transport is a thin Conn enum (embedded vs. wire) the commands are written against, reusing twill-bench's hand-rolled, dependency-free pgclient (no new third-party dependency). The SQL-execution commands run unchanged; the inspect commands need the catalog, which the engine exposes only through the Rust catalog() API, so the server reflects it as the plain-text twill.catalog / twill.relationships surface — the same #53 mechanism behind SHOW twill.stats — which the CLI reads back and reassembles into the engine's catalog shape so the renderers stay transport-agnostic. That reflection lives entirely in the server (composition glue); no engine or storage-seam surface changed. Branching and serve are storage-seam / server-lifecycle operations with no wire form, so they stay embedded-only (a postgres:// URL is refused with guidance). Each command path ships with a test driving it against an in-process listener.
4. Deferred: schema diff (needs a differ), data dump/restore round-trips, a TUI. No GUI/cloud, ever.

Deliberate scope boundaries

• OUT — GUI/studio, cloud login/link, hosted control plane (no Twill cloud).
• OUT — any new engine or storage-seam surface; management consumes the existing Connection API only.
• DEFERRED — transactional, all-or-nothing migrations (blocked by autocommit-only DDL; branch-and-swap is the v1 answer).
• DEFERRED — schema diff / declarative schema; v1 migrations are imperative ordered SQL.

Related specifications