Stage 7 · Master
Phase 3 — User Service
Relationships
Model the many-to-many relationship between users and organizations as its own aggregate with a role and a lifecycle, and give it a repository that survives concurrent invitations.
Membership Is a Fact With Its Own History
A membership row answers one question: does this user currently have this role in this organization, and how did that come to be? It is not a join table in the denormalization sense — it carries its own status lifecycle (invited, active, suspended) that changes independently of both the user and the organization it connects. A board term ending changes a membership's role. A resident who never accepts an invite leaves a membership stuck at invited. Neither event touches the user's name or the organization's plan.
For our platform this matters immediately: the same person can be a board_member in Maple Ridge HOA and a resident in Cedar Grove HOA at the same time, with two independent membership rows, two independent role histories, and no risk that revoking board access in one community touches the other.
The memberships Table
CREATE TABLE memberships (
id uuid PRIMARY KEY,
user_id uuid NOT NULL REFERENCES users(id),
organization_id uuid NOT NULL,
role text NOT NULL
CHECK (role IN ('org_admin', 'board_member', 'staff', 'resident')),
status text NOT NULL DEFAULT 'invited'
CHECK (status IN ('invited', 'active', 'suspended')),
created_at timestamptz NOT NULL DEFAULT now(),
updated_at timestamptz NOT NULL DEFAULT now(),
UNIQUE (user_id, organization_id)
);
CREATE INDEX memberships_organization_id_idx ON memberships (organization_id);
organization_id has no foreign key: organizations live in a different service's database, so this is a value reference, not a physical constraint. The UNIQUE(user_id, organization_id) is the one constraint we can and must enforce locally — a person cannot hold two simultaneous memberships in the same community.
DROP TABLE memberships;
The index disappears with its table, so the down migration only needs to drop memberships itself.
organization_id is trusted data, not verified data, at the database layer. When Membership Service creates a membership it must call Organization Service (or read a cached, event-driven projection) to confirm the organization actually exists — the UNIQUE constraint alone would happily accept a membership pointing at an organization_id nobody has ever created.
A Repository That Handles the Race Condition
Two board members can invite the same resident within milliseconds of each other. The UNIQUE(user_id, organization_id) constraint is what actually prevents a duplicate membership — the repository's job is to turn that constraint violation into a domain error the service layer can react to sensibly, instead of a raw PostgreSQL error code leaking upward.
package membership
import (
"errors"
"time"
"github.com/google/uuid"
)
var ErrAlreadyMember = errors.New("user already has a membership in this organization")
type Role string
const (
RoleOrgAdmin Role = "org_admin"
RoleBoardMember Role = "board_member"
RoleStaff Role = "staff"
RoleResident Role = "resident"
)
type Status string
const (
StatusInvited Status = "invited"
StatusActive Status = "active"
StatusSuspended Status = "suspended"
)
type Membership struct {
ID uuid.UUID
UserID uuid.UUID
OrganizationID uuid.UUID
Role Role
Status Status
CreatedAt time.Time
UpdatedAt time.Time
}package membership
import (
"context"
"errors"
"github.com/google/uuid"
"github.com/jackc/pgx/v5"
"github.com/jackc/pgx/v5/pgconn"
"github.com/jackc/pgx/v5/pgxpool"
)
type Repository struct {
pool *pgxpool.Pool
}
func NewRepository(pool *pgxpool.Pool) *Repository {
return &Repository{pool: pool}
}
func (r *Repository) Create(ctx context.Context, m Membership) error {
_, err := r.pool.Exec(ctx, `
INSERT INTO memberships (id, user_id, organization_id, role, status)
VALUES ($1, $2, $3, $4, $5)`,
m.ID, m.UserID, m.OrganizationID, m.Role, m.Status,
)
var pgErr *pgconn.PgError
if errors.As(err, &pgErr) && pgErr.Code == "23505" {
return ErrAlreadyMember
}
return err
}
func (r *Repository) ByUserAndOrg(ctx context.Context, userID, orgID uuid.UUID) (Membership, error) {
var m Membership
err := r.pool.QueryRow(ctx, `
SELECT id, user_id, organization_id, role, status, created_at, updated_at
FROM memberships WHERE user_id = $1 AND organization_id = $2`,
userID, orgID,
).Scan(&m.ID, &m.UserID, &m.OrganizationID, &m.Role, &m.Status, &m.CreatedAt, &m.UpdatedAt)
if errors.Is(err, pgx.ErrNoRows) {
return Membership{}, nil
}
return m, err
}Postgres error code 23505 is 'unique_violation'. Mapping it to ErrAlreadyMember here — once, in the repository — means the handler layer never has to know a PgError type exists.
A Membership Lifecycle That Matches Board Reality
- invited: created by an org_admin or board_member; the invited person cannot yet act in the organization.
- active: the person accepted and can now be authorized for organization-scoped actions.
- suspended: access is paused without losing role history — a common HOA scenario during a dues dispute.
Applied exercise
Handle the double-invite race
Two board members each click 'Invite' on the same resident within the same second, from two different browser tabs.
- Trace both requests through Create and identify exactly where the second one fails.
- Decide what HTTP status and error code the second request's caller should see.
- Write one sentence explaining why retrying the second request with the same payload is safe.
Deliverable
A short trace document naming the failing statement, the returned error, and the safety argument.
Completion checks
- The trace references the UNIQUE(user_id, organization_id) constraint by name.
- The safety argument does not claim the operation is idempotent in the classic sense — it explains why a duplicate attempt is merely rejected, not harmful.
Why is organization_id stored as a plain uuid column with no foreign key constraint?
Mark this lesson complete to store local progress and unlock a cleaner resume path the next time you visit.