Schema Migrations & Seed Data

Why Manual SQL Is Not a Strategy

The worst time to discover that your team has no migration discipline is after the second environment exists. As long as one developer is hand-running SQL against a local container, it feels manageable. Then staging appears, then CI needs a database, then another engineer joins, and suddenly 'run these statements in order' becomes institutional folklore. Fieldwork needs a repeatable schema history from the first migration, because the whole course builds on the data layer staying explicit and reviewable.

I chose goose because it is boring, SQL-first, and does not pretend your schema should be expressed through an ORM's model layer. The rejected alternative was tool-specific DSL migrations or application-startup auto-migrate behavior. Both hide too much. Auto-migrate is especially dangerous for a course project like this because it erases the exact moment a schema decision happened and makes rollback conversations vague. I want migrations to be artifacts we can read, review, and reverse.

How goose Fits This Repository

Fieldwork keeps migrations in cmd/migrate — a second binary, sibling to cmd/api, inside the same go.mod. This is exactly the payoff Module 2 flagged when it put main.go under cmd/api/ instead of the repo root: a second binary just needed a home, and it got one without any restructuring. It doesn't need its own Go module or a go.work file — multiple binaries can live happily under one module as long as they don't need independent versioning, and migrate and the API are always deployed from the same commit anyway. Schema changes are repository-level infrastructure, not the API service's startup concern, so migrate runs goose against a DSN from the environment and becomes the one approved path for up, down, and status operations. That keeps deployment order explicit: migrate first, then roll services that depend on the new shape.

A dedicated tool also avoids a bad habit: teaching the API server to migrate on startup because it already has a database connection. That shortcut couples schema evolution to application rollout and makes scaling harder. If three API pods start at once, only one should be performing DDL. A separate migration step avoids inventing a leader-election problem just to save one command.

Writing Migrations We Can Live With

Reversible does not mean every migration is perfectly reversible forever, but it does mean we design changes with rollback in mind instead of treating down scripts as a ceremonial afterthought. Fieldwork's migrations follow three rules. First, schema changes are small and topic-focused. Second, destructive changes are staged instead of combined with data rewrites in one opaque file. Third, names are honest about intent so a reviewer can infer the risk from the filename before reading the SQL.

-- +goose Up
create table tasks (
    id uuid primary key,
    tenant_id uuid not null references tenants(id) on delete cascade,
    workspace_id uuid not null references workspaces(id) on delete cascade,
    project_id uuid not null references projects(id) on delete cascade,
    title text not null,
    description text not null default '',
    status text not null check (status in ('todo', 'in_progress', 'done', 'canceled')),
    priority text not null check (priority in ('low', 'medium', 'high', 'urgent')),
    assignee_user_id uuid references users(id),
    created_by_user_id uuid not null references users(id),
    due_at timestamptz,
    position bigint not null default 0,
    created_at timestamptz not null default now(),
    updated_at timestamptz not null default now(),
    completed_at timestamptz
);

create index idx_tasks_tenant_workspace_project_created
    on tasks (tenant_id, workspace_id, project_id, created_at desc, id desc);

-- +goose Down
drop index if exists idx_tasks_tenant_workspace_project_created;
drop table if exists tasks;

When a change needs backfilling, I prefer an expand-and-contract sequence over a dramatic one-shot rewrite. Add the nullable column. Backfill it in a separate migration or data job. Deploy code that writes both shapes if necessary. Then enforce not-null or remove the old column later. That sequencing is less exciting than a perfect all-at-once migration, but it is much friendlier to production rollouts and staging verification.

Seed Data Should Look Like Real Tenants

An empty database is useful exactly once: when checking that migrations succeed. After that, it is a terrible development environment. Fieldwork's local seed data creates two tenants with different workspace shapes, overlapping users, realistic project states, and tasks in multiple statuses. That matters because multi-tenant bugs often disappear in a database containing only one tenant and three rows. Seed data should force the real query paths early.

insert into users (id, email, full_name) values
    ('8bb1dcf8-7d6a-44eb-a91e-a0c2d5c1a101', 'alex@northstar.test', 'Alex Rao'),
    ('8bb1dcf8-7d6a-44eb-a91e-a0c2d5c1a102', 'mika@northstar.test', 'Mika Patel'),
    ('8bb1dcf8-7d6a-44eb-a91e-a0c2d5c1a103', 'riley@orbit.test', 'Riley Chen');

insert into tenants (id, slug, name, plan) values
    ('f31d0d59-7e7f-4f41-95e8-8efca57ec001', 'northstar', 'Northstar Labs', 'enterprise'),
    ('f31d0d59-7e7f-4f41-95e8-8efca57ec002', 'orbitops', 'OrbitOps', 'team');

insert into workspaces (id, tenant_id, slug, name) values
    ('2f6c4d95-a770-4f7c-95c7-8014a7581001', 'f31d0d59-7e7f-4f41-95e8-8efca57ec001', 'platform', 'Platform'),
    ('2f6c4d95-a770-4f7c-95c7-8014a7581002', 'f31d0d59-7e7f-4f41-95e8-8efca57ec001', 'delivery', 'Delivery'),
    ('2f6c4d95-a770-4f7c-95c7-8014a7581003', 'f31d0d59-7e7f-4f41-95e8-8efca57ec002', 'ops', 'Operations');

insert into memberships (id, tenant_id, workspace_id, user_id, role) values
    ('9e8404fb-a12b-49f5-afd2-04460fc71001', 'f31d0d59-7e7f-4f41-95e8-8efca57ec001', '2f6c4d95-a770-4f7c-95c7-8014a7581001', '8bb1dcf8-7d6a-44eb-a91e-a0c2d5c1a101', 'owner'),
    ('9e8404fb-a12b-49f5-afd2-04460fc71002', 'f31d0d59-7e7f-4f41-95e8-8efca57ec001', '2f6c4d95-a770-4f7c-95c7-8014a7581002', '8bb1dcf8-7d6a-44eb-a91e-a0c2d5c1a102', 'admin'),
    ('9e8404fb-a12b-49f5-afd2-04460fc71003', 'f31d0d59-7e7f-4f41-95e8-8efca57ec002', '2f6c4d95-a770-4f7c-95c7-8014a7581003', '8bb1dcf8-7d6a-44eb-a91e-a0c2d5c1a103', 'owner');

I keep seeds idempotent or easy to reset, not precious. In local development the usual pattern is recreate the database, run migrations, then apply seeds. In tests, most suites should own their own fixture creation instead of depending on global seed state. Seed data exists to make local manual flows believable, not to become a hidden dependency for every automated test.

The Decision

• Use goose for versioned, reviewable SQL migrations.
• Run migrations from a dedicated tool, not from application startup.
• Prefer small reversible migrations and expand-contract changes for risky evolutions.
• Seed multiple tenants and workspace shapes so local development exercises tenant-aware paths.

Fieldwork treats schema history as part of the product, not as setup trivia. goose gives the project explicit versioned migrations, and realistic seed data makes local development reflect the system's actual multi-tenant shape. The rejected shortcuts — manual SQL, auto-migrate at boot, and one-tenant demo data — all save minutes up front while quietly teaching the wrong habits for the repository this course is building.