> ## Documentation Index
> Fetch the complete documentation index at: https://docs.whilst.io/llms.txt
> Use this file to discover all available pages before exploring further.

# Database Migrations

> Create and run database schema changes

Whilst uses Neon PostgreSQL. When running migrations, always use the **direct** (non-pooled) connection string — the pooled endpoint doesn't support DDL operations reliably.

## Running Migrations

```bash theme={null}
# Run all pending migrations (local)
pnpm db:migrate

# Against Neon (use the direct URL, not the pooled one)
export POSTGRES_URL="postgresql://user:pass@ep-xxx.us-east-1.aws.neon.tech/whilst?sslmode=require"
pnpm db:migrate

# Or from the web-app directory
cd apps/web-app
pnpm migrate

# Check migration status
pnpm migrate:status

# Dry run (preview without executing)
pnpm migrate:dry-run

# Rollback last batch
pnpm migrate:rollback
```

<Tip>
  Use Neon branches for testing migrations safely before applying to production.
</Tip>

## Creating Migrations

```bash theme={null}
cd apps/web-app
pnpm migrate:create add_user_preferences
```

This creates a new file in `db/migrations/` with the next sequential number.

## Migration File Template

```sql theme={null}
-- Migration 003: Add user preferences
-- Date: 2025-11-17
-- Description: Add table for storing user preferences
-- Dependencies: Requires users table

-- MIGRATION CODE
CREATE TABLE IF NOT EXISTS user_preferences (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  key VARCHAR(100) NOT NULL,
  value JSONB NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  UNIQUE(user_id, key)
);

CREATE INDEX IF NOT EXISTS idx_user_preferences_user_id
  ON user_preferences(user_id);

-- ROLLBACK SCRIPT (for reference)
/*
DROP TABLE IF EXISTS user_preferences CASCADE;
*/
```

## Best Practices

<Warning>
  Never modify the database without a migration. All schema changes must be tracked.
</Warning>

1. **Always use idempotent operations** — `CREATE TABLE IF NOT EXISTS`, `ADD COLUMN IF NOT EXISTS`
2. **Include rollback instructions** — Document how to reverse the change
3. **Test locally first** — Run dry-run, then apply, then verify idempotency by running again
4. **Keep migrations small** — One migration per feature/change
5. **Test your app after** — Run `pnpm dev` to verify nothing breaks