​
ADR-0017: Supervisor Team Scope Resolved by Scanning the Shared Member Table

​
Status

Accepted

​
Tags

fieldforce, teams, supervisor, better-auth, adr-0006

​
Decision

Fieldforce supervisor scope (which tasks a supervisor can see) is resolved by reading the supervisor’s team_ids from the shared member table (a JSONB column after the prerequisite migration), then querying all members in the org and filtering to those sharing at least one team ID. This runs in the Go HTTP handler on every list request for a supervisor-role caller. No team data is replicated into the Go schema.

​
Why

Teams are owned by the Bun backend. The member table is part of the Better Auth schema — read-only from Go (ADR-0006). There is no teams join table accessible from Go. Replicating team membership into a Go-owned table requires a sync mechanism between Bun and Go that does not exist and violates the bounded context boundary. Rejected alternatives:
  • Replicate teams into a Go-owned table: Violates ADR-0006. Requires ongoing synchronization with no existing infrastructure. Rejected.
  • Dedicated teams API endpoint in Go: Phase 1 scope. Revisit in Phase 4 if org sizes exceed thousands of members. Rejected for Phase 1.

​
How it works

GET /fieldforce/tasks (supervisor role)
  ↓
resolveAllowedUserIDs(ctx, db, orgID, supervisorUserID)
  1. SELECT team_ids FROM member WHERE id = supervisorUserID AND organization_id = orgID
     → supervisor.teamIDs = ["team_a", "team_b"]
  2. SELECT id FROM member WHERE organization_id = orgID
     → filter rows where team_ids @> ANY(supervisor.teamIDs)  (GIN index used)
     → allowedUserIDs = ["uid1", "uid2", "uid3"]
  3. Inject into TaskFilter.AllowedUserIDs
  4. Repository applies: AND EXISTS (SELECT 1 FROM ff_task_assignments WHERE task_id = ff_tasks.id AND user_id = ANY(?))
Index (added in prerequisite migration): 
CREATE INDEX CONCURRENTLY idx_member_team_ids ON member USING GIN (team_ids jsonb_path_ops);
team_ids is JSONB after the prerequisite migration (Task 0 in Phase 1). The @> containment operator uses the GIN index for efficient lookup. For orgs with hundreds of members, the O(n) scan is one indexed query — acceptable.

​
Known limitations

  • Scale threshold: At > 1,000 members per org, the full-member scan may degrade beyond 200ms P95. At > 200ms P95 on supervisor list requests, revisit with a Go-side teams cache or a dedicated teams service.
  • Stale team membership: If a member is added to or removed from a team in Bun, the change is reflected on the next Go request — no cache invalidation needed because Go always reads the current member table.

​
Rules for agents

  • Never write to the member table from Go — SELECT only (ADR-0006)
  • resolveAllowedUserIDs must return nil (no filtering) for non-supervisor roles — nil means “see all org tasks”
  • TaskFilter.AllowedUserIDs non-nil means “restrict to this set” — the repository applies AND user_id = ANY(?) on the assignments join
  • Do not cache the result of resolveAllowedUserIDs in application memory — team membership changes must be reflected immediately

​
Bad pattern (do not generate)

// Replicating team data into a Go-owned table — violates ADR-0006
type GoTeamMembership struct { TeamID, UserID string } // wrong

// Returning an empty slice instead of nil for non-supervisor — causes empty results
if role != "supervisor" {
    return []string{} // wrong — return nil to skip the filter
}

​
Good pattern

// Read-only scan of shared member table
func resolveAllowedUserIDs(ctx context.Context, db *gorm.DB, orgID, supervisorID string) ([]string, error) {
    var sup struct{ TeamIDs []string }
    if err := db.Table("member").
        Select("team_ids").
        Where("id = ? AND organization_id = ?", supervisorID, orgID).
        Scan(&sup).Error; err != nil {
        return nil, err
    }
    if len(sup.TeamIDs) == 0 {
        return []string{}, nil // supervisor has no teams — sees nothing
    }
    var members []struct{ ID string }
    db.Table("member").
        Select("id").
        Where("organization_id = ? AND team_ids @> ANY(?)", orgID, pq.Array(sup.TeamIDs)).
        Scan(&members)
    // collect and return member IDs
}

// Nil means no filter (manager/admin sees all)
if role == "supervisor" {
    filter.AllowedUserIDs, _ = resolveAllowedUserIDs(ctx, db, orgID, userID)
}
// filter.AllowedUserIDs == nil → no extra WHERE clause in repository