Skip to content

Module 04 / Lesson 02

Review Supabase migrations and regenerate types

Module 04 · Real-World ContextStep 02 of 04

Never trust a green checkmark or a loading spinner until you have verified the database write and security boundaries.

You click "Publish Update". A green toast notification pops up saying "Published successfully!" But when you open a private browser window, nothing has changed. The AI had simply written a fake timer (setTimeout) that showed a toast without actually persisting data to the database. Or worse: anyone on the internet could edit your company’s incidents because row-level security was missing.

Narrated lesson · 1:45
Read the narration transcript

A dashboard edit leaves the repository behind

The form worked on the first developer's machine. They added a published timestamp to their local database and built the app against that structure.

A teammate pulls the branch and sees an error about a missing column. The application changed. The repository does not yet include the matching migration.

Put the schema change in Git

Write the additive change in a reviewed migration. Decide what published at means for existing records first. A former published record with no timestamp might otherwise look like a draft.

Use the local Supabase CLI to apply migrations. If you add MCP for inspection, start with a local or development project. Hosted scoping uses project ref. Read only access still needs appropriate sample data.

Inspect locally, then rebuild the schema

I point Supabase MCP at the local development stack and confirm the address with Supabase Status. For a hosted development project, use the explicit project reference and read-only mode. Write the change in a migration file.

A reset clears local data. I confirm this stack is disposable, then rebuild it from the migration history. The new field should appear and the old test records should behave as expected in a separate upgrade fixture.

Finally, I regenerate the types from that local schema. The database decides which values exist. The generated types tell the frontend how to handle them.

Verify fresh and existing data separately

The clean case checks the migration history. It shows a new team member can start from the branch and get the expected schema.

Then apply the new migration to a previous-version fixture with old drafts and published records. Verify what both sets of rows mean. That is the evidence you need for the release discussion.

The everyday problem

The new publish path works in the developer’s preview. A teammate pulls the branch, opens the same form, and gets a database error about a missing column. The person who wrote the patch added the column in a dashboard and forgot that a second environment starts from the repository. The UI branch contains code that depends on a data change nobody else can see.

In the status app we need to record when an incident update was published. The schema already has incident updates, and null can mean a draft when the team explicitly agrees that rule. The moment that column becomes part of the contract, it belongs in the same review history as the code that uses it.

Why the agent got it wrong

An agent can inspect one database and assume every environment looks like it. That shortcut hides drift, which simply means the actual structure of one environment disagrees with the structure the team expects. Type definitions generated from a different database can also make a development build look fine while a deployment fails.

A migration is a file that records a specific schema change and can be applied in order. A clean reset tests whether all migrations produce the expected schema from a fresh database. That is one kind of evidence. The next kind tests the same new migration against data already in the old schema. Existing records can expose cases a clean seed cannot.

The tool and technique

Use the local Supabase CLI and keep proposed mutations in supabase/migrations/. If you use Supabase MCP to inspect schema, pick a local or development instance with synthetic data. The hosted configuration documents project scoping through project_ref, read-only access, and feature groups. Confirm the target and effective capabilities in your own client before using it.

Ask for the plan before writing the migration. What happens to older published records that do not yet have a timestamp? Does a missing value represent a draft, an unknown timestamp, or an earlier published record? That is an application decision. Write down the approved mapping before the agent chooses one by accident.

Use versioned migrations and scoped schema inspection

Use the Supabase CLI with a disposable local development database. Read the schema with a local tool connection if it adds value, and keep schema changes in reviewed files under supabase/migrations. A hosted connection should have both an explicitly scoped project and read-only mode during inspection.

supabase status
# Typical local MCP endpoint: http://localhost:54321/mcp
# Hosted development query parameters: project_ref=PROJECT_REF&read_only=true

supabase migration new add_incident_published_at
# Review the SQL and local seed data before applying it.
supabase db reset --local
supabase gen types typescript --local

# Expected fixture output after rebuilding the test schema
migration add_incident_published_at applied
generated schema includes published_at: string | null

On the disposable local database, apply migrations from scratch and compare generated types with the change. Confirm drafts can still have a null published_at and older updates remain readable.

On screen in the status app

The first workflow below updates a shared dashboard and leaves the repository to catch up. The reviewed alternative stages an additive migration for published_at, checks an older fixture, and regenerates types from the local schema. The shown SQL is the first step only. If the app must backfill older rows, define the historical source and verify the mapping with the owner before the application reads it as a release requirement.

In the agent task, identify the source and scope. Ask it to show the column change, how existing rows are handled, and which queries could need updating. Ask for separate evidence of a clean reset and an in-place migration. A good result describes the records it checked and any mismatch still open.

supabase/migrations/<timestamp>_incident_update_published_at.sql

Before

Add published_at in the shared dashboard. Tell the team to add it if the form fails locally.

After

-- First, additive migration. Review historical mapping separately.
alter table public.incident_updates
  add column published_at timestamptz;

# In the disposable local workspace, after confirmation:
supabase db reset
supabase gen types typescript --local

What to watch for

Running a database reset destroys data in the target database. Use it only on the disposable local instance in this walkthrough and verify the target before resetting. Read-only inspection can still expose information to the agent, so use sample data and project-scoped access. Review the migration and avoid making unrecorded changes to hosted environments.

Be careful with rollback promises. Reverting application code does not always reverse a database change or recover lost data. Favor compatible, additive steps when an old and new version might run together. When you need to make a destructive change, separate it and agree a recovery plan with the owner.

How you know it worked

From a clean local checkout, apply the migration history against a disposable database and regenerate the TypeScript definitions. Confirm the app can query the timestamp. Then test with a previous-version fixture and apply just the new migration. Check whether old draft and published records remain correctly understood by the application.

Review the migration, generated type diff, and runtime checks together. If the types changed more than you expected, trace why. Record the exact setup in the pull request so another person can repeat it. The next lesson uses the same discipline for who can see the rows the database returns.

References