Skip to content

Development environment & database change management

Purpose

This document explains how our development environment is structured and why we must use migration files for all database changes. It's written for anyone who needs to understand the system — including future-Glenn who has forgotten how it all works.

For how to actually deploy changes to production, see Deploying changes to production.

The two environments

We have two completely separate database environments:

Production — the live system. Both the BMS (bms.matthewscleaningco.com.au) and the client portal (portal2.matthewscleaningco.com.au) run against this database. This is the real database with real data.

  • Supabase project ref: jjroiufvngjhbvjfcrbv
  • Both apps deployed on Vercel, auto-deploy when code is pushed to the master branch on GitHub

Dev branch — a sandboxed copy of the database used for development and testing. Claude Code works here. It has the same schema (table structure) as production, but no real data — only test/seed data. Both the BMS and client portal share this database, just as they share the production database.

  • Supabase dev branch ref: euhornpsmtwosgcpxuda
  • Claude Code connects to this via MCP (configured in .mcp.json)
  • Created 20 March 2026 as a persistent Supabase branch

The dev branch is completely isolated from production. Nothing Claude Code does on the dev branch affects the live system. You can break things, experiment, and iterate safely without any risk to real data or real users.

How changes flow to production

There are two types of changes: code changes (React components, pages, styles) and database changes (new tables, new columns, seed data, constraints).

Code changes flow automatically. When code is pushed to the master branch on GitHub, Vercel detects the push and deploys the updated app to production. No manual step required.

Database changes and Edge Functions do not flow automatically. These require a manual deployment step — see Deploying changes to production for the full workflow.

Why migration files matter

This is the most important concept in this document.

What is a migration file?

A migration file is a SQL script that describes a database change. For example: "add a column called accepted_consumable_ids to the contracts table" or "create a new products table with these columns." Each migration file has a timestamp in its filename so they're applied in the correct order.

They live in supabase/migrations/ in the repo and are committed to Git like any other code file.

Why not just make changes directly in Supabase?

You can make database changes directly in the Supabase Dashboard — using the SQL Editor, the Table Editor, or the GUI. It works. The change takes effect immediately. So why bother with migration files?

Because direct changes are invisible to the rest of the system. Here's what goes wrong:

Problem 1: Dev branch resets lose your changes. Supabase dev branches can be deleted and recreated (we've already done this once — the original dev branch became unhealthy and had to be replaced). When you create a new dev branch, it's built from the baseline migration plus all subsequent migration files. If a change was made directly in the dashboard instead of via a migration file, it won't exist on the new branch. The change is simply gone.

Problem 2: Production and dev get out of sync. If you make a change directly on prod but don't also make it on dev (or vice versa), the two environments drift apart. Code that works on dev breaks on prod, or vice versa. Migration files are the single source of truth that keeps both environments in sync.

Problem 3: You can't reproduce the database. If you ever need to set up a new environment — a staging server, a new dev branch, a fresh Supabase project — the only way to build the database from scratch is by running the migration files in order. If changes were made outside of migration files, they're lost. You'd have to remember what you changed and manually redo it, which is error-prone and unrealistic over months of development.

Problem 4: No audit trail. Migration files are committed to Git. You can see exactly what changed, when, and why (from the commit message). Direct dashboard changes leave no trace — you won't remember in three months why a column was added or a constraint was changed.

Problem 5: Claude Code can't see direct changes. CC works from the codebase. If schema changes exist on the database but not in the migration files, CC doesn't know about them. It might create conflicting migrations, or write code that assumes a schema that doesn't match reality.

The rule

All database changes must be captured in migration files. This applies to:

  • Schema changes (tables, columns, constraints, indexes, triggers, functions)
  • Reference/seed data (area types, service types, statuses, site type defaults, hourly rates, products — anything that is configuration rather than user-generated data)
  • Data fixes (correcting bad data, backfilling columns)

Never make database changes directly via the Supabase Dashboard, SQL Editor, or MCP outside of a migration file. If you need to make an emergency fix on prod, create a migration file for it afterwards so the change is captured.

The one exception: reading data (SELECT queries) is always fine in the SQL Editor or MCP. It's only writes (INSERT, UPDATE, DELETE, ALTER, CREATE, DROP) that must go through migration files.

Summary

Concept Detail
Dev branch Sandboxed database for development. CC works here. No real data. Safe to break.
Production Live database with real data. Users interact with this.
Code deploys Automatic via Vercel on push to master.
Database/Edge Function deploys Manual — see Deploying changes to production.
Migration files SQL scripts in supabase/migrations/ that describe every database change.
Why migration files? They're the single source of truth. Without them, changes get lost on branch resets, environments drift apart, and the database can't be reproduced.
The rule All database writes must be in migration files. No direct changes via the dashboard.
CC's role Creates migration files and applies them to dev automatically. Reminds Glenn to deploy to prod.