Add a Database Migration

This guide covers creating, naming, running, and testing SQL migration files for the Crawbl orchestrator database.

Migration File Location

All migration files live in:

crawbl-backend/migrations/orchestrator/

Step 1

Choose the next sequence

List the existing migrations and pick the next zero-padded 6-digit prefix.

ls migrations/orchestrator/

If the latest file is 000003_add_agents.up.sql, the next migration starts with 000004.

Step 2

Create the up and down files

Create both files together so the rollback path exists from the start.

{sequence}_{description}.up.sql
{sequence}_{description}.down.sql

touch migrations/orchestrator/000004_add_integrations.up.sql
touch migrations/orchestrator/000004_add_integrations.down.sql

Step 3

Write the up migration

The up file contains the forward schema change.

CREATE TABLE integrations (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    workspace_id UUID NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
    provider TEXT NOT NULL,
    status TEXT NOT NULL DEFAULT 'pending',
    config JSONB,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX idx_integrations_workspace_id ON integrations(workspace_id);

Keep one logical change per migration, add foreign keys explicitly, index lookup columns, use TIMESTAMPTZ for timestamps, and use UUID keys with gen_random_uuid().

Step 4

Write the down migration

The down file should reverse the up file exactly.

DROP TABLE IF EXISTS integrations;

For column changes, use ALTER TABLE ... DROP COLUMN. For indexes, use DROP INDEX.

Step 5

Run it locally

Apply pending migrations against local Postgres.

./crawbl dev migrate

If it fails, fix the SQL and re-run. The migrator skips migrations that already ran.

Step 6

Verify the schema

Confirm the table or columns exist after the migration.

psql -h localhost -U crawbl -d crawbl -c "\d orchestrator.integrations"

Running Migrations in the Cluster

In the dev cluster, migrations run automatically during orchestrator startup. The orchestrator container executes pending migrations before accepting traffic.

To run migrations manually against the cluster database:

# Port-forward the cluster database
kubectl port-forward -n backend svc/backend-postgresql 5432:5432

# Run migrations
crawbl dev migrate

Rolling Back a Migration

To roll back the latest migration, apply the corresponding down file manually:

psql -h localhost -U crawbl -d crawbl -f migrations/orchestrator/000004_add_integrations.down.sql

There is no automated rollback command. Always test your down migration before merging.

Testing Migrations

Step 1

Apply locally

./crawbl dev migrate should succeed cleanly.

Step 2

Check idempotency

Run ./crawbl dev migrate twice. The second run should be a no-op.

Step 3

Test the down path

Apply the down migration, then re-apply the up migration.

Step 4

Run E2E

The E2E suite exercises the full schema, so a passing run is a good compatibility check.

./crawbl dev migrate
./crawbl test e2e

Common Patterns

Adding a column

-- up
ALTER TABLE workspaces ADD COLUMN description TEXT;

-- down
ALTER TABLE workspaces DROP COLUMN description;

Adding an index

-- up
CREATE INDEX idx_messages_conversation_id ON messages(conversation_id);

-- down
DROP INDEX IF EXISTS idx_messages_conversation_id;

Adding an enum-style constraint

-- up
ALTER TABLE integrations
    ADD CONSTRAINT chk_integrations_status
    CHECK (status IN ('pending', 'active', 'revoked'));

-- down
ALTER TABLE integrations DROP CONSTRAINT chk_integrations_status;

What's next: Coding Patterns

Migration File Location​