Required Institutions on Programs, Remove from Users

Problem

institution_id is nullable on both programs and users. This creates ambiguity: - Programs can exist without an institution (no multi-tenancy boundary) - Users have institution_id but it gates nothing — authorization uses user_program_memberships, and SSO routing uses email domain lookup - Two independent paths connect users to institutions (direct column vs. program memberships), which can disagree

Design

Two changes:

1. Make institution_id NOT NULL on programs with ON DELETE CASCADE
2. Drop institution_id from users entirely

What changes

Migration (001_initial_schema.sql): - programs.institution_id: nullable → NOT NULL, ON DELETE SET NULL → ON DELETE CASCADE - users: drop institution_id column and idx_users_institution_id index

Proto (proto/program/v1/program.proto): - Add institution_id field to Program message - Add institution_id field to CreateProgramRequest

Program service (internal/features/program/): - CreateProgram validates and passes institution_id - models.go maps InstitutionID to proto field

User feature (internal/features/user/): - Drop GetUserWithInstitution query - Drop UpdateUserInstitution query - Remove institution_id param from CreateUser query

SSO handlers (internal/sso/): - Replace user.InstitutionID == institutionID check with email domain verification against institution_domains table - Remove institution_id from SSO user creation

Seed (cmd/seed/main.go): - Create a default institution for seed programs - Stop stamping institution_id on seed users

Institution management: Institutions continue to be created manually in the database. No RPC service or UI.

What doesn’t change

• users table keeps no institution_id — user-to-institution is derived from email domain or program memberships
• No new InstitutionService proto
• No frontend changes needed beyond institution_id appearing in Program responses

Invariants established

• Every program belongs to exactly one institution
• Deleting an institution cascades to its programs (and their child entities)
• SSO institution verification uses email domain (single source of truth) instead of a stored column that can drift

Trade-offs

• Tests and seed data need to create an institution before creating programs (small boilerplate increase)
• No direct “home institution” column on users — must derive from email domain or program memberships (acceptable since the column was unused for authorization)