member-console/docs/database-management.md

Database Management

This project uses pressly/goose for database migrations and sqlc for type-safe SQL code generation. Requires PostgreSQL 18+ (for native uuidv7()).

Per-Module Migrations

Each module owns its schema via an embedded migrations/ directory:

internal/db/migrations/              # Shared foundation (legacy table cleanup)
internal/identity/migrations/        # persons, users
internal/organization/migrations/    # organizations, org_members, workspaces, roles
internal/billing/migrations/         # products
internal/entitlements/migrations/    # grants, pools, provisions, entitlement sets
internal/cooperative/migrations/     # cooperative settings
internal/audit/migrations/           # audit log
internal/integration/migrations/     # webhook_events, outbox
internal/fedwiki/migrations/         # sites
internal/stripe/migrations/          # stripe provider_configs

Migrations are assembled at startup into a global sequence by internal/db/migrations.go. Each module gets a fixed numeric namespace based on its position in the dependency-ordered source list:

Module	Index	Version range	Example
db	0	1001–1999	01001_db_init.sql
identity	1	2001–2999	02001_identity_init.sql
organization	2	3001–3999	03001_organization_init.sql
billing	3	4001–4999	04001_billing_init.sql
entitlements	4	5001–5999	05001_entitlements_init.sql
cooperative	5	6001–6999	06001_cooperative_init.sql
audit	6	7001–7999	07001_audit_init.sql
integration	7	8001–8999	08001_integration_init.sql
fedwiki	8	9001–9999	09001_fedwiki_init.sql
stripe	9	10001–10999	10001_stripe_init.sql

Adding a migration to any module does not change any other module's version numbers. The module registration order is defined in cmd/start.go.

New modules must be appended to the end of the source list in both cmd/start.go and cmd/migrate.go — inserting in the middle shifts all subsequent modules' version namespaces, which breaks existing databases.

Goose runs with WithAllowMissing() so that new migrations added to earlier modules (e.g., integration at 8002) can be applied even when later modules (e.g., fedwiki at 9001) are already recorded. This is required for the per-module namespace design to work correctly.

Migrations run automatically on startup. The CLI also provides migrate up, migrate down, and migrate status commands.

Creating New Migrations

# Install goose CLI tool
go install github.com/pressly/goose/v3/cmd/goose@latest

# Create a new migration in the appropriate module directory
cd internal/entitlements/migrations
goose create your_migration_name sql

Always Use StatementBegin / StatementEnd

Every migration with more than one SQL statement must wrap both Up and Down sections in -- +goose StatementBegin / -- +goose StatementEnd. Without these directives, goose v3 splits statements and runs them individually, which can silently skip failed statements while still marking the migration as applied.

Migrations Run Without search_path

The ConnectAndMigrate function runs migrations on a separate database connection that does not include the custom search_path (identity,organization,...,billing,...). This is intentional — a pgx/PostgreSQL 18 interaction causes multi-statement DDL sent via the simple query protocol to silently lose tables when module schemas appear in the connection-level search_path. After migrations complete, the application connection uses the full search_path as normal.

-- +goose Up
-- +goose StatementBegin

CREATE TABLE billing.example (...);
CREATE INDEX idx_example ON billing.example(...);
GRANT ALL ON ALL TABLES IN SCHEMA billing TO billing_owner;

-- +goose StatementEnd

-- +goose Down
-- +goose StatementBegin

DROP TABLE IF EXISTS billing.example;

-- +goose StatementEnd

This ensures PostgreSQL receives the entire block as a single unit, so failures are atomic — either all statements succeed or none are applied.

sqlc Code Generation

Each module has its own sqlc.yaml that generates type-safe Go code from SQL queries.

# Regenerate sqlc code after schema or query changes
cd internal/entitlements && sqlc generate

internal/db/sqlc_schemas.sql

This file is not a goose migration — it is a sqlc-only helper that declares all PostgreSQL schemas (CREATE SCHEMA IF NOT EXISTS ...) so that sqlc can resolve schema-qualified table names (e.g., billing.accounts) across modules. It must be listed first in every module's sqlc.yaml schema paths.

Naming Convention

Table names must not repeat the schema name:

• Use billing.accounts, not billing.billing_accounts
• Use identity.persons, not identity.identity_persons

Generated Go type names carry no schema prefix — the package itself provides the namespace:

• billing.Account, not billing.BillingAccount
• identity.Person, not identity.IdentityPerson

This follows Go's own convention: http.Request not http.HttpRequest.

Standard sqlc.yaml Template

Every module follows this pattern:

schema:
  - "../db/sqlc_schemas.sql"   # always first — declares all schemas for cross-module resolution
  - "migrations/"              # this module's own migrations
  # add upstream module migrations only when FK resolution requires them
  # e.g. - "../identity/migrations/"

rename: Block Placement

The rename: block goes inside gen.go:, after emit_empty_slices and before overrides:. Keys use the {schema}_{singular_table} format:

gen:
  go:
    package: "billing"
    emit_empty_slices: true
    rename:
      billing_account: Account    # billing.accounts → Account
      billing_price: Price
    overrides:
      - db_type: "uuid"
        go_type: "string"