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)

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:

-- 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:

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:

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.

-- 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.