Skip to main content

Writing Plugin Migrations

Your plugin's tables come from migrations — SQL files in your own folder that run at deploy time. They're the authoritative schema for your plugin, and getting a few conventions right keeps them safe to apply and re-apply.

Location and ordering​

Plugin migrations live at plugins/{entity_slug}/migrations/NNN_description.sql. Ordering is deterministic:

  • All core migrations run first, then plugin migrations.
  • Across plugins, migrations run in alphabetical order of entity slug (enforced by the shared apply-plugin-migrations.sh sort). (This is migration ordering only — unrelated to the runtime plugin-loader discovery order.)
  • Within your plugin, migrations run in alphanumeric filename order.

A hard rule falls out of this: your migrations must have no dependency on another plugin's tables. Each plugin is self-contained.

Idempotency​

Write migrations to be safe to apply more than once — CREATE TABLE IF NOT EXISTS, ALTER TABLE … ADD COLUMN IF NOT EXISTS, enum guards, and so on. This protects against double-application and simplifies recovery. You don't touch any CI/CD YAML: the shared script discovers plugins/*/migrations/*.sql automatically, in both CI (fresh schema → test DB) and deploy (incremental against the live DB).

Tables vs. seed data — the separation that matters​

This is the one people miss. Migrations create tables only — your entity object table, join table, domain tables, indexes, constraints, views, triggers. They do not seed your entity row or your workspace statuses.

Those are seeded by the plugin loader at API startup, from your manifest — idempotently, every boot. So your statuses live in exactly one place (the manifest), and the loader can validate that the tables your migrations created actually exist. Don't INSERT entity or status rows in a migration; declare them in the manifest and let the loader handle them.

A first-migration checklist​

Study plugins/default_plugin/migrations/001_initial.sql as a complete example. The shape:

  1. Wrap in BEGIN / COMMIT.
  2. SET search_path TO circus, public.
  3. Create any enum types (with IF NOT EXISTS guards).
  4. Create tables (IF NOT EXISTS).
  5. Add FK constraints via ALTER TABLE (useful for circular references, e.g. entity ↔ workspace pointers).
  6. Create indexes.
  7. Create helper views.
  8. Create updated_at triggers (reusing the core's set_updated_at() function).

Follow that, keep it idempotent, and your schema will apply cleanly on a fresh database and on an existing one alike.