# Safe Schema Migration

The schema (created with `execute_sql`) and your handler code must agree on column
names and nullability. A mismatch is a top cause of HTTP 500s — e.g. code that inserts
an `image_key` into a table whose only column is `image_url TEXT NOT NULL`: every
insert violates the NOT NULL constraint and the request 500s.

## Golden rules

1. **Change schema BEFORE deploying code that depends on it.** Run `execute_sql` first,
   then `deploy`. Otherwise the new code runs against the old schema and 500s.
2. **Adding a column to a table that already has rows? Make it nullable or give it a
   DEFAULT.** A bare `ADD COLUMN ... NOT NULL` (no default) fails on a non-empty table.
3. **Name columns for what they hold.** If you store the key from `uploadFile`, call it
   `image_key`, not `image_url`. See onvibe://docs/uploads.
4. **Keep statements idempotent** so re-running is safe.

## Patterns

### Add a column (safe on populated tables)

```sql
ALTER TABLE photos ADD COLUMN IF NOT EXISTS image_key TEXT;        -- nullable: always safe
ALTER TABLE photos ADD COLUMN IF NOT EXISTS status TEXT NOT NULL DEFAULT 'pending';  -- NOT NULL needs a DEFAULT
```

### Rename a column without downtime

Do it in two deploys so old and new code both work in between:

```sql
-- 1. add the new column, backfill, keep the old one
ALTER TABLE photos ADD COLUMN IF NOT EXISTS image_key TEXT;
UPDATE photos SET image_key = image_url WHERE image_key IS NULL;
-- 2. deploy code that reads/writes image_key
-- 3. once nothing uses it, drop the old column:
ALTER TABLE photos DROP COLUMN IF EXISTS image_url;
```

### Backfill, then tighten

Only add a NOT NULL constraint after every row has a value:

```sql
ALTER TABLE photos ADD COLUMN IF NOT EXISTS image_key TEXT;
UPDATE photos SET image_key = '...' WHERE image_key IS NULL;
ALTER TABLE photos ALTER COLUMN image_key SET NOT NULL;
```

## Inspect the live schema when unsure

Use `query_sql` (read-only) to see what actually exists before changing code:

```sql
SELECT column_name, data_type, is_nullable
FROM information_schema.columns
WHERE table_name = 'photos'
ORDER BY ordinal_position;
```

If a deploy starts returning 500s right after a schema or code change, this mismatch is
the first thing to check — confirm column names and nullability match your handler, then
run get_app_logs for the exact constraint error.

## Versioned migrations (recommended over ad-hoc execute_sql)

Instead of running schema changes by hand with `execute_sql`, store them as files and let
the platform apply them once, in order, and track what's been applied.

1. Write each change as a file at `migrations/<version>.sql`. Use a sortable,
   timestamp-based version so lexicographic order = chronological order, e.g.
   `migrations/20260101120000_add_image_key.sql`.
2. Keep migrations **forward-only and additive** (CREATE TABLE, ADD COLUMN ... with a DEFAULT
   or nullable). Don't edit an already-applied migration — add a new one.
3. Call `apply_migrations({ project_id })`. It applies only the pending ones, each inside a
   transaction, and records them in a `schema_migrations` table inside your database. Safe
   to re-run: applied migrations are skipped. When there are pending migrations, it first saves
   a restore point (code + database snapshot), returned as `restore_point` — pass that label to
   `rollback_to_version` if a migration corrupts data. The restore point is auto-removed after a
   few days, so revert promptly if needed.

```sql
-- migrations/20260101120000_add_image_key.sql
ALTER TABLE photos ADD COLUMN IF NOT EXISTS image_key TEXT;
```

## Iterating on a production app safely (drafts)

When an app already has real user data, don't experiment on it directly. Work in a **draft**:

1. `create_draft({ source_project_id })` → a private copy with its own URL and a snapshot of
   the app's code + database. Drafts don't count against your project limit, and you can draft
   even a locked app.
2. Iterate in the draft: `edit_file`/`deploy` for code, add `migrations/<version>.sql` files
   and `apply_migrations` for schema, `upload_asset` for assets. Test on the draft URL.
3. `apply_changes({ draft_project_id })` → pushes the draft's **code, pending migrations and
   app assets** to the main app and deletes the draft. The main app's **database rows and user
   uploads are preserved** — only the schema is evolved via your migrations. A restore-point
   version (code + database) is created first, so `rollback_to_version` can undo it. If the
   main app changed since the draft was created, apply_changes aborts (pass `force: true` to
   override). To discard a draft instead, `delete_project` it.