Database Design

Modeling an Organization as a Row, Not Yet as a Go Struct

It is tempting to open a Go file and let a struct's fields dictate the database schema. This lesson deliberately does the opposite: it starts from what an organization actually is in the HOA domain — a legal society with a unique identifying name, a status that changes over its lifetime, and contact details support staff need to reach it — and only then, in the Models lesson, transcribes that design into Go. Schema decided this way tends to survive framework changes; schema copied from a struct tends to carry the struct's accidents (like a convenient but meaningless field order) into permanent storage.

Choosing CHECK Constraints Over a Native Postgres ENUM

Postgres supports a native ENUM type, but altering one later — adding a fourth status, renaming a value — requires ALTER TYPE ... ADD VALUE, which cannot run inside a transaction in older Postgres versions and can complicate rollbacks. A TEXT column with a CHECK constraint listing the allowed values is slightly more verbose to declare, but every future change to the allowed set is an ordinary, transactional ALTER TABLE ... DROP CONSTRAINT / ADD CONSTRAINT — the same migration machinery golang-migrate already manages for every other schema change in this course.

Indexes Decided by the Queries We Already Know We'll Run

Two access patterns are certain before a single handler exists: looking up one organization by its slug (the public-facing identifier), and listing organizations filtered by status for an internal admin view. That gives exactly two indexes to design now — a unique index on slug, and a plain index on status — rather than guessing at indexes speculatively for queries that may never exist.

• UNIQUE INDEX on slug — enforces the business rule (no two organizations share a slug) and makes GetBySlug lookups fast; the same index does both jobs.
• INDEX on status — supports filtering an organization list by lifecycle state without a full table scan, which matters once this table has more than a handful of rows.
• No index on created_at or updated_at yet — no query in this phase's scope orders or filters by them; adding an unused index only slows down every write for no read benefit.

Review the Model as Relationships and Invariants, Not SQL

Testing the Constraint's Logic Before It's Committed to a Migration

Nothing prevents testing the exact boolean logic a CHECK constraint will enforce, directly in psql, before writing the migration that makes it permanent. This catches a typo in the allowed value list — an easy mistake with three string literals — while it is still a one-line experiment instead of a committed migration that needs a follow-up migration to fix.

 valid_example | invalid_example
---------------+-----------------
 t             | f