Course outline

01 · Phase 1 — Project Foundation16/16

02 · Phase 2 — Organization Service20/20

03 · Phase 3 — User Service10/10

04 · Phase 4 — Authentication Service10/10

05 · Phase 5 — API Gateway9/9

06 · Phase 6 — Flat Service6/6

07 · Phase 7 — Resident Service7/7

08 · Phase 8 — Maintenance Service7/7

09 · Phase 9 — Payment Service7/7

10 · Phase 10 — Complaint Service7/7

11 · Phase 11 — Notice Service7/7

12 · Phase 12 — Visitor Service6/6

13 · Phase 13 — Staff Service6/6

14 · Phase 14 — Notification Service7/7

15 · Phase 15 — Redis Integration6/6

16 · Phase 16 — Kafka Integration8/8

17 · Phase 17 — Observability7/7

18 · Phase 18 — Performance Engineering6/6

19 · Phase 19 — Production Deployment11/11

20 · Phase 20 — Production Readiness8/8

Stage 7 · Master

Phase 2 — Organization Service

Migrations

Turn the schema design into a real, reversible golang-migrate migration, and extend the Phase 1 Makefile to run it.

14 min readHOA Backend Engineering CourseMaster

Why golang-migrate, Not an ORM's Auto-Migrate

An ORM's auto-migrate compares Go structs against the live database and generates DDL to reconcile them — convenient in a demo, risky in a service where a struct field rename should never silently trigger a column rename against production data. golang-migrate instead requires an explicit, hand-written, reviewable pair of SQL files for every change — one .up.sql, one .down.sql — tracked in a schema_migrations table it manages automatically, which makes re-running the same migration twice a safe no-op instead of a duplicate-object error.

Terminal — services/organization

$mkdir -p migrations

$go install -tags 'pgx5' github.com/golang-migrate/migrate/v4/cmd/migrate@v4.19.1

// A one-time local tool install, not a module dependency of the service itself. The pgx5 build tag links in golang-migrate's database/pgx/v5 driver — the same pgx/v5 module the service itself uses to talk to Postgres, rather than the CLI's default lib/pq-based driver.

$touch migrations/00001_create_organizations.up.sql migrations/00001_create_organizations.down.sql

// golang-migrate pairs one shared version prefix with two separate files, rather than a single file split by an inline Up/Down marker.

The Up/Down Contract as a File Pair

Create services/organization/migrations/00001_create_organizations.up.sql before adding the code

$mkdir -p services/organization/migrations && touch services/organization/migrations/00001_create_organizations.up.sql

// touch preserves an existing file, so the same command is safe when revisiting the lesson.

services/organization/migrations/00001_create_organizations.up.sql

CREATE EXTENSION IF NOT EXISTS pgcrypto;

CREATE TABLE organizations (
    id                   uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    name                 text NOT NULL,
    slug                 text NOT NULL,
    registration_number  text NOT NULL,
    contact_email        text NOT NULL,
    contact_phone        text,
    address_line1        text NOT NULL,
    address_line2        text,
    city                 text NOT NULL,
    state                text NOT NULL,
    postal_code          text NOT NULL,
    country              text NOT NULL,
    status               text NOT NULL DEFAULT 'active'
                         CHECK (status IN ('active', 'suspended', 'archived')),
    created_at           timestamptz NOT NULL DEFAULT now(),
    updated_at           timestamptz NOT NULL DEFAULT now()
);

CREATE UNIQUE INDEX organizations_slug_key ON organizations (slug);
CREATE INDEX organizations_status_idx ON organizations (status);

gen_random_uuid() comes from the pgcrypto extension, enabled defensively with IF NOT EXISTS since this is the first migration to need it. The matching .down.sql, shown next, drops exactly what this file created, in dependency order.

Create services/organization/migrations/00001_create_organizations.down.sql before adding the code

$mkdir -p services/organization/migrations && touch services/organization/migrations/00001_create_organizations.down.sql

// touch preserves an existing file, so the same command is safe when revisiting the lesson.

services/organization/migrations/00001_create_organizations.down.sql

DROP TABLE IF EXISTS organizations;

Indexes disappear automatically with their table, so only the table itself needs an explicit drop. golang-migrate identifies this file as version 00001's rollback purely by its shared numeric prefix and its .down.sql suffix — the filename is the entire contract; nothing inside the file marks it as a Down step.

pgx5:// is not postgres://

golang-migrate's pgx/v5 driver registers itself under the pgx5:// URL scheme, not postgres:// or postgresql://. ORGANIZATION_DATABASE_URL stays a postgres:// DSN everywhere pgxpool.New reads it — the app never changes — but any -database argument passed to the migrate binary needs that same connection string with its scheme swapped to pgx5://. The Makefile target below does that swap with a small piece of shell parameter expansion so the underlying env var never has to exist in two forms.

Extending the Phase 1 Makefile for Migration Commands

Create Makefile before adding the code

$touch Makefile

// touch preserves an existing file, so the same command is safe when revisiting the lesson.

Makefile (modify — repository root, adds three new targets)

migrate-up:
	migrate -path services/organization/migrations -database "pgx5://$${ORGANIZATION_DATABASE_URL#postgres://}" up

migrate-down:
	migrate -path services/organization/migrations -database "pgx5://$${ORGANIZATION_DATABASE_URL#postgres://}" down 1

migrate-version:
	migrate -path services/organization/migrations -database "pgx5://$${ORGANIZATION_DATABASE_URL#postgres://}" version

$${ORGANIZATION_DATABASE_URL#postgres://} is POSIX shell parameter expansion, not a Make variable — the doubled $$ escapes Make's own $-substitution so the shell sees a literal $, then that shell strips a leading postgres:// before pgx5:// is prepended back on. migrate-down passes an explicit 1 so a single make migrate-down always rolls back exactly one version, matching the step-at-a-time behavior this course relies on; bare down would otherwise prompt for confirmation before undoing every migration. migrate-version prints the single currently-applied version number (and whether it is dirty) without changing anything — the closest golang-migrate equivalent to asking 'what's pending', since the CLI has no separate status subcommand. Appended to the existing Makefile from Phase 1 — every earlier target (tidy, build, test, vet, lint, run-organization, compose-*) stays exactly as it was.

Why Running This Migration Twice Doesn't Break Anything

golang-migrate records the single highest applied version — plus a dirty flag — in a schema_migrations table it manages automatically. There is no per-migration history row; there is exactly one row, moved forward or back as up and down run. Running make migrate-up a second time sees that 00001 is already the recorded version and applies nothing — contrast this with running the raw SQL file's CREATE TABLE statement directly through psql a second time, which fails outright because Postgres refuses to create a table that already exists.

Terminal — repository root (deliberately running the raw SQL twice to see the contrast)

$docker compose exec -T postgres psql -U hoa -d hoa_organization -c "CREATE TABLE organizations (id uuid PRIMARY KEY)"

// First run succeeds (assuming the table doesn't already exist from golang-migrate).

$docker compose exec -T postgres psql -U hoa -d hoa_organization -c "CREATE TABLE organizations (id uuid PRIMARY KEY)"

// Second run, identical command.

Expected output on the second raw CREATE TABLE

ERROR:  relation "organizations" already exists

Terminal — repository root

$docker compose exec -T postgres psql -U hoa -d hoa_organization -c "DROP TABLE IF EXISTS organizations"

// Clean up the raw demonstration table before running the real migration.

Running the Real Migration and Inspecting the Result

Terminal — repository root

$export ORGANIZATION_DATABASE_URL="******localhost:5432/hoa_organization?sslmode=disable"

$make migrate-up

$docker compose exec postgres psql -U hoa -d hoa_organization -c '\dt'

// Expect organizations and schema_migrations both listed.

$docker compose exec postgres psql -U hoa -d hoa_organization -c '\d organizations'

// Confirms columns, defaults, and the status CHECK constraint match docs/schema/organization.md exactly.

Applied exercise

Run make migrate-up a second time and confirm golang-migrate treats it as a no-op

10 minutes

The lesson claims golang-migrate's tracking table makes a repeat run safe. Confirm it directly, in contrast to the raw-SQL failure shown above.

Run make migrate-up again, immediately after the successful run above.
Record migrate's exact output for the already-applied migration (it should differ noticeably from the raw psql error you saw earlier — expect no output at all, since there's nothing pending).
Run make migrate-down followed by make migrate-up once more, and confirm \dt shows the table present again afterward.

Deliverable

A short transcript comparing migrate's repeat-run output against the raw psql duplicate-table error from this lesson.

Completion checks

The second make migrate-up exits successfully (status 0) with no pending migrations applied, unlike the raw SQL demonstration.
After migrate-down then migrate-up, \dt shows organizations present again.

Quiz & Practice

Question 1 / 1

What specifically allows `make migrate-up` to be run twice in a row safely, when running the equivalent raw CREATE TABLE statement twice fails?

Mark this lesson complete to store local progress and unlock a cleaner resume path the next time you visit.

Course outline

01 · Phase 1 — Project Foundation16/16

02 · Phase 2 — Organization Service20/20

03 · Phase 3 — User Service10/10

04 · Phase 4 — Authentication Service10/10

05 · Phase 5 — API Gateway9/9

06 · Phase 6 — Flat Service6/6

07 · Phase 7 — Resident Service7/7

08 · Phase 8 — Maintenance Service7/7

09 · Phase 9 — Payment Service7/7

10 · Phase 10 — Complaint Service7/7

11 · Phase 11 — Notice Service7/7

12 · Phase 12 — Visitor Service6/6

13 · Phase 13 — Staff Service6/6

14 · Phase 14 — Notification Service7/7

15 · Phase 15 — Redis Integration6/6

16 · Phase 16 — Kafka Integration8/8

17 · Phase 17 — Observability7/7

18 · Phase 18 — Performance Engineering6/6

19 · Phase 19 — Production Deployment11/11

20 · Phase 20 — Production Readiness8/8

Stage 7 · Master

Phase 2 — Organization Service

Migrations

Turn the schema design into a real, reversible golang-migrate migration, and extend the Phase 1 Makefile to run it.

14 min readHOA Backend Engineering CourseMaster

Why golang-migrate, Not an ORM's Auto-Migrate

Terminal — services/organization

$mkdir -p migrations

$go install -tags 'pgx5' github.com/golang-migrate/migrate/v4/cmd/migrate@v4.19.1

$touch migrations/00001_create_organizations.up.sql migrations/00001_create_organizations.down.sql

// golang-migrate pairs one shared version prefix with two separate files, rather than a single file split by an inline Up/Down marker.

The Up/Down Contract as a File Pair

Create services/organization/migrations/00001_create_organizations.up.sql before adding the code

$mkdir -p services/organization/migrations && touch services/organization/migrations/00001_create_organizations.up.sql

// touch preserves an existing file, so the same command is safe when revisiting the lesson.

services/organization/migrations/00001_create_organizations.up.sql

CREATE EXTENSION IF NOT EXISTS pgcrypto;

CREATE TABLE organizations (
    id                   uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    name                 text NOT NULL,
    slug                 text NOT NULL,
    registration_number  text NOT NULL,
    contact_email        text NOT NULL,
    contact_phone        text,
    address_line1        text NOT NULL,
    address_line2        text,
    city                 text NOT NULL,
    state                text NOT NULL,
    postal_code          text NOT NULL,
    country              text NOT NULL,
    status               text NOT NULL DEFAULT 'active'
                         CHECK (status IN ('active', 'suspended', 'archived')),
    created_at           timestamptz NOT NULL DEFAULT now(),
    updated_at           timestamptz NOT NULL DEFAULT now()
);

CREATE UNIQUE INDEX organizations_slug_key ON organizations (slug);
CREATE INDEX organizations_status_idx ON organizations (status);

Create services/organization/migrations/00001_create_organizations.down.sql before adding the code

$mkdir -p services/organization/migrations && touch services/organization/migrations/00001_create_organizations.down.sql

// touch preserves an existing file, so the same command is safe when revisiting the lesson.

services/organization/migrations/00001_create_organizations.down.sql

DROP TABLE IF EXISTS organizations;

pgx5:// is not postgres://

Extending the Phase 1 Makefile for Migration Commands

Create Makefile before adding the code

$touch Makefile

// touch preserves an existing file, so the same command is safe when revisiting the lesson.

Makefile (modify — repository root, adds three new targets)

migrate-up:
	migrate -path services/organization/migrations -database "pgx5://$${ORGANIZATION_DATABASE_URL#postgres://}" up

migrate-down:
	migrate -path services/organization/migrations -database "pgx5://$${ORGANIZATION_DATABASE_URL#postgres://}" down 1

migrate-version:
	migrate -path services/organization/migrations -database "pgx5://$${ORGANIZATION_DATABASE_URL#postgres://}" version

Why Running This Migration Twice Doesn't Break Anything

Terminal — repository root (deliberately running the raw SQL twice to see the contrast)

$docker compose exec -T postgres psql -U hoa -d hoa_organization -c "CREATE TABLE organizations (id uuid PRIMARY KEY)"

// First run succeeds (assuming the table doesn't already exist from golang-migrate).

$docker compose exec -T postgres psql -U hoa -d hoa_organization -c "CREATE TABLE organizations (id uuid PRIMARY KEY)"

// Second run, identical command.

Expected output on the second raw CREATE TABLE

ERROR:  relation "organizations" already exists

Terminal — repository root

$docker compose exec -T postgres psql -U hoa -d hoa_organization -c "DROP TABLE IF EXISTS organizations"

// Clean up the raw demonstration table before running the real migration.

Running the Real Migration and Inspecting the Result

Terminal — repository root

$export ORGANIZATION_DATABASE_URL="******localhost:5432/hoa_organization?sslmode=disable"

$make migrate-up

$docker compose exec postgres psql -U hoa -d hoa_organization -c '\dt'

// Expect organizations and schema_migrations both listed.

$docker compose exec postgres psql -U hoa -d hoa_organization -c '\d organizations'

// Confirms columns, defaults, and the status CHECK constraint match docs/schema/organization.md exactly.

Applied exercise

Run make migrate-up a second time and confirm golang-migrate treats it as a no-op

10 minutes

The lesson claims golang-migrate's tracking table makes a repeat run safe. Confirm it directly, in contrast to the raw-SQL failure shown above.

Run make migrate-up again, immediately after the successful run above.
Record migrate's exact output for the already-applied migration (it should differ noticeably from the raw psql error you saw earlier — expect no output at all, since there's nothing pending).
Run make migrate-down followed by make migrate-up once more, and confirm \dt shows the table present again afterward.

Deliverable

A short transcript comparing migrate's repeat-run output against the raw psql duplicate-table error from this lesson.

Completion checks

The second make migrate-up exits successfully (status 0) with no pending migrations applied, unlike the raw SQL demonstration.
After migrate-down then migrate-up, \dt shows organizations present again.

Quiz & Practice

Question 1 / 1

What specifically allows `make migrate-up` to be run twice in a row safely, when running the equivalent raw CREATE TABLE statement twice fails?

Mark this lesson complete to store local progress and unlock a cleaner resume path the next time you visit.