Schema Migrations and Seed Data

Why Migrations Exist

A production backend needs a memory of how its schema changed. Without that memory, each environment becomes a rumor about which tables, indexes, defaults, and backfills it currently contains. Versioned migrations solve a simple but essential problem: they turn database evolution into ordered, reviewable, repeatable source code instead of a sequence of shell history accidents.

The core lesson for a beginner is broader than 'use a migration tool.' A migration is the operational unit of change between two valid database states. That means the file should be small enough to reason about, named honestly enough to review quickly, and sequenced carefully enough that deploys can move application code and schema code without tearing the system in half.

Organizing Migrations per Service

Meridian is a monorepo with separate Go modules under go.work. That strongly suggests one migration history per data-owning service, not one global migrations folder pretending every service shares the same bounded context. identity-service should evolve tenant, user, and membership tables on its own cadence. community-service should own buildings, units, residents, announcements, and complaints. billing-service should own invoices and payments. Ownership in code and ownership in schema history should align.

This layout does not require three different database servers. It only requires three different histories. Multiple services may share one PostgreSQL instance in local development and still keep separate migration directories and separate logical ownership. 'Shared infrastructure' and 'shared schema history' are different questions.

Writing Safe Schema Changes

A migration file should not try to prove cleverness. Its job is to move the database from one valid state to another with as little risk as possible. That is why experienced backend engineers prefer expand-and-contract changes over dramatic all-at-once rewrites. Add the new column nullable. Backfill it explicitly. Deploy code that understands both shapes if necessary. Tighten the constraint later. The extra step exists because production deploys are partial and asynchronous.

-- 000014_add_invoice_reference_number_nullable.sql
alter table invoices
    add column reference_number text;

create unique index idx_invoices_tenant_reference_number
    on invoices (tenant_id, reference_number)
    where reference_number is not null;

-- 000015_backfill_invoice_reference_numbers.sql
update invoices
set reference_number = 'INV-' || to_char(created_at, 'YYYYMMDD') || '-' || id::text
where reference_number is null;

-- 000016_make_invoice_reference_number_not_null.sql
alter table invoices
    alter column reference_number set not null;

The same discipline applies to destructive changes. Dropping a column that some old binary still reads is not bold engineering; it is coordinated failure. Safe schema evolution assumes that multiple versions of application code may be alive briefly. Migrations should therefore be written for rolling deployment reality, not for single-process fantasy.

Seed Data as an Engineering Tool

Seed data is not decoration for demos. Good seed data proves that multi-tenant assumptions, uniqueness constraints, foreign keys, and list queries behave under realistic variation. A database containing one tenant, one building, one resident, and one invoice teaches almost nothing. A database containing multiple HOA tenants, overlapping statuses, due dates, units, and membership roles begins to expose the exact classes of bugs that will otherwise survive to integration testing.

insert into tenants (id, slug, name) values
    ('11111111-1111-1111-1111-111111111111', 'sunny-vale', 'Sunny Vale HOA'),
    ('22222222-2222-2222-2222-222222222222', 'oak-meadows', 'Oak Meadows HOA');

insert into users (id, email, full_name) values
    ('aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa', 'president@sunnyvale.test', 'Anita Sharma'),
    ('bbbbbbbb-bbbb-bbbb-bbbb-bbbbbbbbbbbb', 'manager@oakmeadows.test', 'Ben Thomas');

insert into memberships (id, tenant_id, user_id, role) values
    ('m1111111-1111-1111-1111-111111111111', '11111111-1111-1111-1111-111111111111', 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa', 'tenant_admin'),
    ('m2222222-2222-2222-2222-222222222222', '22222222-2222-2222-2222-222222222222', 'bbbbbbbb-bbbb-bbbb-bbbb-bbbbbbbbbbbb', 'community_manager');

insert into buildings (id, tenant_id, name, address_line_1) values
    ('33333333-3333-3333-3333-333333333333', '11111111-1111-1111-1111-111111111111', 'Tower A', '12 Palm Street'),
    ('44444444-4444-4444-4444-444444444444', '22222222-2222-2222-2222-222222222222', 'Maple Court', '48 Garden Road');

Seed data should also be disposable. Local development often benefits from a reset workflow that recreates the schema, reapplies migrations, and reloads a known fixture set. Tests, by contrast, should usually create the exact rows they need instead of inheriting a giant global seed and hoping nobody edits it. 'Convenient for a developer's laptop' and 'appropriate for automated tests' are related but different concerns.

Current Repository Status

• Keep one migration history per data-owning service.
• Prefer small, reversible, expand-and-contract schema changes over dramatic rewrites.
• Use seed data to prove tenant boundaries and real lifecycle states, not just to make screenshots look populated.
• Treat migration files as operational source code, because that is exactly what they are.