Required Institutions Implementation Plan

For Claude: REQUIRED SUB-SKILL: Use superpowers:executing-plans to implement this plan task-by-task.

Goal: Make institution_id NOT NULL on programs (with CASCADE delete) and remove institution_id from users entirely.

Architecture: Two schema changes propagate through generated code, services, tests, and seed data. The SSO institution-mismatch check switches from reading user.institution_id to verifying the user’s email domain against institution_domains.

Tech Stack: Go, sqlc, PostgreSQL, Connect RPC, Protocol Buffers

Task 1: Update migration — programs.institution_id NOT NULL, drop from users

Files: - Modify: internal/db/migrations/001_initial_schema.sql

Step 1: Edit the programs table

Change line 58 from:

institution_id UUID REFERENCES institutions(id) ON DELETE SET NULL,

to:

institution_id UUID NOT NULL REFERENCES institutions(id) ON DELETE CASCADE,

Step 2: Edit the users table

Remove line 89:

institution_id UUID REFERENCES institutions(id) ON DELETE SET NULL,

Remove line 96:

CREATE INDEX idx_users_institution_id ON users(institution_id);

Step 3: Commit

git add internal/db/migrations/001_initial_schema.sql
git commit -m "schema: make institution_id NOT NULL on programs, drop from users"

Task 2: Update SQL queries

Files: - Modify: internal/features/program/queries.sql - Modify: internal/features/user/queries.sql - Modify: internal/features/institution/queries.sql

Step 1: Program queries — no changes needed

CreateProgram already accepts institution_id as $3. UpdateProgramInstitution still makes sense. No changes to internal/features/program/queries.sql.

Step 2: User queries — remove institution_id

In internal/features/user/queries.sql:

Remove the GetUserWithInstitution query (lines 43-52):

-- name: GetUserWithInstitution :one
SELECT
    u.*,
    i.id as institution_id,
    ...

Remove the UpdateUserInstitution query (lines 65-69):

-- name: UpdateUserInstitution :one
UPDATE users
SET institution_id = $2, updated_at = NOW()
WHERE id = $1
RETURNING *;

Remove institution_id from CreateUser query. Change line 55 from:

INSERT INTO users (email, name, password_hash, institution_id, is_platform_admin)
VALUES ($1, $2, $3, $4, $5)

to:

INSERT INTO users (email, name, password_hash, is_platform_admin)
VALUES ($1, $2, $3, $4)

Step 3: Institution queries — add email domain verification query

Add to internal/features/institution/queries.sql:

-- name: GetInstitutionByEmailDomain :one
-- Verifies an email's domain belongs to a specific institution.
-- Used by SSO handlers to replace user.institution_id check.
SELECT i.* FROM institutions i
JOIN institution_domains d ON d.institution_id = i.id
WHERE LOWER(d.domain) = $1 AND i.id = $2;

Step 4: Commit

git add internal/features/program/queries.sql internal/features/user/queries.sql internal/features/institution/queries.sql
git commit -m "queries: remove institution_id from users, add domain verification query"

Task 3: Update proto and regenerate code

Files: - Modify: proto/program/v1/program.proto - Run: make generate

Step 1: Add institution_id to Program proto

In proto/program/v1/program.proto, add institution_id to the Program message:

message Program {
  string id = 1;
  string name = 2;
  string slug = 3;
  google.protobuf.Timestamp created_at = 4;
  google.protobuf.Timestamp updated_at = 5;
  string timezone = 6;
  string institution_id = 7;
}

Add institution_id to CreateProgramRequest:

message CreateProgramRequest {
  string name = 1;
  string slug = 2;
  string institution_id = 3;
}

Step 2: Regenerate all code

Run: make generate

This regenerates: - gen/ — protobuf Go code - internal/db/sqlc/ — sqlc Go code

After regeneration, sqlc.Program.InstitutionID changes from pgtype.UUID to uuid.UUID (non-nullable), and sqlc.User no longer has InstitutionID. sqlc.CreateUserParams no longer has InstitutionID.

Step 3: Commit

git add proto/ gen/ internal/db/sqlc/
git commit -m "proto: add institution_id to Program, regenerate code"

Task 4: Update program service and models

Files: - Modify: internal/features/program/service.go - Modify: internal/features/program/models.go

Step 1: Update CreateProgram handler

In internal/features/program/service.go, add institution_id validation and pass it to the query.

Add validation after the slug check:

if strings.TrimSpace(req.Msg.InstitutionId) == "" {
    return nil, apperrors.InvalidArgumentError("institution_id", "Institution ID is required.")
}

Parse the UUID:

institutionID, parseErr := id.ParseUUID(req.Msg.InstitutionId, "institution ID")
if parseErr != nil {
    return nil, parseErr
}

Update the CreateProgramParams to pass it:

org, err := queries.CreateProgram(ctx, sqlc.CreateProgramParams{
    Name:          req.Msg.Name,
    Slug:          req.Msg.Slug,
    InstitutionID: institutionID,
})

Step 2: Update models.go

In internal/features/program/models.go, add InstitutionId to both converter functions.

In dbOrgToProto:

func dbOrgToProto(org sqlc.Program) *programv1.Program {
    return &programv1.Program{
        Id:            org.ID.String(),
        Name:          org.Name,
        Slug:          org.Slug,
        CreatedAt:     timestamppb.New(org.CreatedAt.Time),
        UpdatedAt:     timestamppb.New(org.UpdatedAt.Time),
        Timezone:      org.Timezone,
        InstitutionId: org.InstitutionID.String(),
    }
}

In dbOrgWithStatsToProto, add InstitutionId: row.InstitutionID.String() to the Program struct.

Step 3: Commit

git add internal/features/program/
git commit -m "feat: require institution_id when creating programs"

Task 5: Update testutil and test helper

Files: - Modify: internal/testutil/testutil.go

Step 1: Add CreateInstitution helper

Add a helper function to internal/testutil/testutil.go:

// CreateInstitution creates a test institution.
// Returns the created institution for use in test setup.
func CreateInstitution(ctx context.Context, t *testing.T, queries *sqlc.Queries, name, slug string) sqlc.Institution {
    t.Helper()
    institution, err := queries.CreateInstitution(ctx, sqlc.CreateInstitutionParams{
        Name: name,
        Slug: slug,
    })
    if err != nil {
        t.Fatalf("CreateInstitution: failed to create institution: %v", err)
    }
    return institution
}

Step 2: Remove InstitutionID from CreateUserWithProgram

In CreateUserWithProgram, remove InstitutionID: pgtype.UUID{} from the CreateUserParams. After codegen, this field no longer exists on the struct.

Step 3: Commit

git add internal/testutil/testutil.go
git commit -m "test: add CreateInstitution helper, remove institution_id from user creation"

Task 6: Update all test files — CreateProgram calls need institution

Every test file that calls queries.CreateProgram(ctx, sqlc.CreateProgramParams{...}) with InstitutionID: pgtype.UUID{} must:

  1. Create an institution first using testutil.CreateInstitution (or queries.CreateInstitution directly)
  2. Pass InstitutionID: institution.ID instead of pgtype.UUID{}

Every test file that calls queries.CreateUser(ctx, sqlc.CreateUserParams{...}) with InstitutionID: pgtype.UUID{} must remove the InstitutionID field (it no longer exists on the struct).

Files to modify (every file from the grep results):

Programs — need institution + remove from user creation: - internal/features/program/service_test.go (13 occurrences) - internal/features/user/service_test.go (10 occurrences) - internal/features/user/integration_test.go - internal/features/user/flows_test.go - internal/features/user/getcurrentuser_test.go - internal/features/membership/service_test.go - internal/features/resident/service_test.go - internal/features/rotation/service_test.go - internal/features/rotation/queries_test.go - internal/features/rotationschedule/service_test.go - internal/features/rotationscheduleblock/service_test.go - internal/features/rotationschedulepreference/service_test.go - internal/features/rotationscheduleconstraint/service_test.go - internal/features/rotationschedulesolver/snapshot_test.go - internal/features/liverotationschedule/service_test.go - internal/features/tag/service_test.go - internal/features/site/service_test.go - internal/features/shift/service_test.go - internal/features/calltype/service_test.go - internal/features/callschedule/service_test.go - internal/features/callshifttemplate/service_test.go - internal/features/callshifttemplaterel/service_test.go - internal/features/call/postgres/template_repository_test.go - internal/features/call/postgres/shift_repository_test.go - internal/auth/interceptor_test.go - internal/authz/authz_test.go

Pattern for each test file:

Most test files have a setup section that creates a program. The pattern change is:

Before:

org, err := queries.CreateProgram(ctx, sqlc.CreateProgramParams{
    Name:          "Test Org",
    Slug:          "test-org",
    InstitutionID: pgtype.UUID{},
})

After:

institution := testutil.CreateInstitution(ctx, t, queries, "Test Institution", "test-inst")
org, err := queries.CreateProgram(ctx, sqlc.CreateProgramParams{
    Name:          "Test Org",
    Slug:          "test-org",
    InstitutionID: institution.ID,
})

And remove InstitutionID: pgtype.UUID{} from all CreateUserParams structs (the field no longer exists).

For files with multiple CreateProgram calls (e.g., program/service_test.go), create the institution once in a test helper and reuse it. Since each test gets an isolated database, institution slugs won’t conflict.

Step: Run tests to verify

Run: make test-go Expected: All tests pass

Step: Commit

git add internal/
git commit -m "test: update all tests to create institution before program"

Task 7: Update SSO handlers — verify institution via email domain

Files: - Modify: internal/sso/saml_handler.go - Modify: internal/sso/oidc_handler.go - Modify: internal/sso/saml_integration_test.go

Step 1: Update SAML handler

In internal/sso/saml_handler.go, replace the institution mismatch check (around line 397-408).

Before:

// Verify user belongs to the institution that initiated SAML flow.
if !user.InstitutionID.Valid || uuid.UUID(user.InstitutionID.Bytes) != institutionID {
    slog.Warn("SAML ACS: user institution mismatch",
        slog.String("user_id", user.ID.String()),
        slog.String("expected_institution", institutionID.String()),
        slog.String("remote_addr", r.RemoteAddr),
    )
    h.redirectWithError(w, r, "institution_mismatch")
    return
}

After:

// Verify user's email domain belongs to the institution that initiated SAML flow.
// This prevents cross-institution authentication if IdPs are shared between institutions.
emailDomain := extractEmailDomain(email)
_, err = queries.GetInstitutionByEmailDomain(ctx, sqlc.GetInstitutionByEmailDomainParams{
    Lower:  emailDomain,
    ID:     institutionID,
})
if err != nil {
    slog.Warn("SAML ACS: user email domain does not match institution",
        slog.String("email_domain", emailDomain),
        slog.String("expected_institution", institutionID.String()),
        slog.String("remote_addr", r.RemoteAddr),
    )
    h.redirectWithError(w, r, "institution_mismatch")
    return
}

Add a helper function (or reuse from user package if importable):

func extractEmailDomain(email string) string {
    parts := strings.Split(email, "@")
    if len(parts) != 2 || parts[1] == "" {
        return ""
    }
    return strings.ToLower(parts[1])
}

Step 2: Update OIDC handler

Same pattern in internal/sso/oidc_handler.go around line 337-345. Replace the user.InstitutionID check with the same email domain verification.

Step 3: Update SSO tests

In internal/sso/saml_integration_test.go, remove InstitutionID from CreateUserParams calls. The test users need institution domains set up to pass the new check instead.

Lines 96-98 — change from:

InstitutionID: pgutil.UUIDFromValue(institution.ID),

to: (remove the line, and ensure the test creates an institution domain for the user’s email domain)

Lines 463-465 — same pattern for cross-institution test.

Step 4: Run tests

Run: make test-go Expected: All tests pass

Step 5: Commit

git add internal/sso/
git commit -m "refactor: SSO handlers verify institution via email domain instead of user column"

Task 8: Update seed data

Files: - Modify: cmd/seed/main.go

Step 1: Create default institution for seed programs

Add a default institution creation in the run() function before programs are created. Something like:

defaultInstitution, err := getOrCreateInstitution(ctx, qtx, "Default Institution", "default", "", "")

Note: getOrCreateInstitution already exists in the seed file. The SSO provider can be empty/nil for a non-SSO institution.

Step 2: Pass institution ID to program creation

Change all CreateProgramParams in seed from InstitutionID: pgtype.UUID{} to InstitutionID: defaultInstitution.ID.

Step 3: Remove institution_id from user creation

Remove InstitutionID: pgtype.UUID{} from all CreateUserParams in seed (the field no longer exists).

For SSO seed users, remove InstitutionID: pgutil.UUIDFromValue(institutionID) from the createSSOUser function.

Step 4: Commit

git add cmd/seed/main.go
git commit -m "seed: create default institution for programs, remove institution_id from users"

Task 9: Run full validation

Step 1: Reset local database

Run: make db-nuke

(Migration changed, pgmigrate won’t re-run it without reset.)

Step 2: Run full check

Run: make fix && make verify

Expected: All linters pass, all tests pass, type checking passes.

Step 3: Fix any remaining compilation errors

The codegen will have removed InstitutionID from User-related structs. If any file still references it, fix the compilation error.

Task 10: Create PR

git checkout -b feat/required-institutions
git push -u origin HEAD
gh pr create --title "Make institution_id required on programs, remove from users" --body "..."