Team Allocation Data Structure and Query Patterns

Overview

Team allocation tracks which team members are assigned to specific slots in a project on a monthly basis. This document describes the data structure, storage approach, and query patterns.

Data Structure

Type Definitions

The team allocation data is defined in /apps/web/src/types/project.ts:

/**
 * Team allocation assignment
 */
export interface TeamAllocationAssignment {
  role: string;              // Role name (e.g., "senior-engineer", "engineer", "product-owner")
  slotIndex: number;          // Zero-based index of the slot within the role
  memberId: string | null;    // Email or ID of assigned team member (null if unassigned)
}

/**
 * Monthly team allocation
 */
export interface MonthlyTeamAllocation {
  month: MonthString;         // Format: "YYYY-MM" (e.g., "2025-10")
  assignments: TeamAllocationAssignment[];
}

Storage Location

Team allocations are stored as part of the Project document in Firestore:

Collection: projects
Document ID: projectKey (e.g., "PROJ")
Field: teamAllocations (array of MonthlyTeamAllocation)

Example Data Structure

{
  projectKey: "PROJ",
  name: "Example Project",
  // ... other project fields
  teamAllocations: [
    {
      month: "2025-10",
      assignments: [
        { role: "senior-engineer", slotIndex: 0, memberId: "sarah.chen@company.com" },
        { role: "senior-engineer", slotIndex: 1, memberId: "mike.johnson@company.com" },
        { role: "engineer", slotIndex: 0, memberId: "james.wilson@company.com" },
        { role: "engineer", slotIndex: 1, memberId: "david.kim@company.com" },
        { role: "engineer", slotIndex: 2, memberId: "emily.rodriguez@company.com" },
        { role: "product-owner", slotIndex: 0, memberId: "anna.martinez@company.com" },
      ]
    },
    {
      month: "2025-11",
      assignments: [
        { role: "senior-engineer", slotIndex: 0, memberId: "sarah.chen@company.com" },
        { role: "senior-engineer", slotIndex: 1, memberId: null }, // Unassigned
        // ... more assignments
      ]
    }
  ]
}

Relationship to Budget Allocations

Team allocations are based on budget allocations defined in the Finance tab:

Budget defines slots: project.budget.versions[].monthlyAllocations[] defines how many slots exist per role per month
Team assigns members: project.teamAllocations[] assigns specific team members to those slots

Example Flow

Finance tab sets: October 2025 needs 2 senior-engineer slots, 3 engineer slots, 1 product-owner slot
Team tab shows: 6 total slots for October 2025
User assigns: Sarah Chen to senior-engineer slot 0, Mike Johnson to senior-engineer slot 1, etc.

Query Patterns

Reading Team Allocations

Pattern: Read entire project document (team allocations are embedded)

// API Route: GET /api/projects/[projectKey]
const projectDoc = await db.collection('projects').doc(projectKey).get();
const project = projectDoc.data();
const teamAllocations = project.teamAllocations || [];

Query Characteristics:

Single document read (efficient)
No additional queries needed
All months loaded at once (typically small dataset)

Updating Team Allocations

Pattern: Update specific month's assignments or entire array

// Option 1: Update entire teamAllocations array
await projectRef.update({
  teamAllocations: updatedTeamAllocations,
  updatedAt: dateUtils.current(),
});

// Option 2: Update specific month (requires reading, modifying, writing)
const project = await projectRef.get();
const teamAllocations = project.data().teamAllocations || [];
const monthIndex = teamAllocations.findIndex(ta => ta.month === targetMonth);
if (monthIndex >= 0) {
  teamAllocations[monthIndex] = updatedMonthAllocation;
} else {
  teamAllocations.push(updatedMonthAllocation);
}
await projectRef.update({ teamAllocations, updatedAt: dateUtils.current() });

Querying by Month

Pattern: Filter in-memory (since data is embedded)

function getTeamAllocationsForMonth(
  project: Project,
  month: MonthString
): TeamAllocationAssignment[] {
  const monthAllocation = project.teamAllocations?.find(ta => ta.month === month);
  return monthAllocation?.assignments || [];
}

Querying by Member

Pattern: Filter assignments across all months

function getMemberAssignments(
  project: Project,
  memberId: string
): Array<{ month: MonthString; assignment: TeamAllocationAssignment }> {
  const results: Array<{ month: MonthString; assignment: TeamAllocationAssignment }> = [];
  
  project.teamAllocations?.forEach(monthAlloc => {
    monthAlloc.assignments.forEach(assignment => {
      if (assignment.memberId === memberId) {
        results.push({ month: monthAlloc.month, assignment });
      }
    });
  });
  
  return results;
}

Data Size Considerations

Typical Project

Months: 3-12 months per project
Slots per month: 5-20 slots
Total assignments: 15-240 assignments per project
Data size: ~5-50 KB per project

Firestore Limits

Document size limit: 1 MB
Array size: No hard limit, but best practice is < 10,000 items
Our usage: Well within limits (typically < 500 assignments)

Optimization Strategies

Embedded storage: Keep allocations in project document (fits well within size limits)
Lazy loading: Only load months that are being viewed (not needed for embedded storage)
Indexing: No special indexes needed (queries are in-memory filters)

API Endpoints

Current State

✅ GET /api/projects/[projectKey] - Returns project with teamAllocations
❌ PUT /api/projects/[projectKey] - Updates project but doesn't handle teamAllocations yet
❌ PATCH /api/projects/[projectKey]/team-allocations - Not yet implemented

Recommended Implementation

// PATCH /api/projects/[projectKey]/team-allocations
// Update team allocations for a specific month
{
  month: "2025-10",
  assignments: [
    { role: "senior-engineer", slotIndex: 0, memberId: "sarah.chen@company.com" },
    // ... more assignments
  ]
}

Or update entire array:

// PUT /api/projects/[projectKey]/team-allocations
// Replace entire teamAllocations array
{
  teamAllocations: [
    { month: "2025-10", assignments: [...] },
    { month: "2025-11", assignments: [...] },
  ]
}

Migration Considerations

Existing Projects

Projects without teamAllocations should default to empty array: teamAllocations: []
Migration script already handles this (see /apps/web/src/lib/firestore/migrations.ts)

Backward Compatibility

Legacy resourceAllocation field can coexist with teamAllocations
New UI uses teamAllocations, legacy UI can continue using resourceAllocation

Best Practices

Always validate: Ensure memberId exists in people collection before assignment
Validate slot indices: Ensure slotIndex doesn't exceed budget allocation count for that role/month
Sort months: Keep teamAllocations array sorted by month for easier querying
Atomic updates: Update entire month's allocations in one operation to avoid partial states
Audit trail: Consider adding assignedBy and assignedAt fields for tracking (future enhancement)

Future Enhancements

Sub-month granularity: Support weekly or bi-weekly allocations
Partial allocations: Support percentage-based allocations (e.g., 50% of a slot)
Allocation history: Track changes over time
Conflicts detection: Warn when member is over-allocated across projects
Bulk operations: Assign same member to multiple months at once

Overview​

Data Structure​

Type Definitions​

Storage Location​

Example Data Structure​

Relationship to Budget Allocations​

Example Flow​

Query Patterns​

Reading Team Allocations​

Updating Team Allocations​

Querying by Month​

Querying by Member​

Data Size Considerations​

Typical Project​

Firestore Limits​

Optimization Strategies​

API Endpoints​

Current State​

Recommended Implementation​

Migration Considerations​

Existing Projects​

Backward Compatibility​

Best Practices​

Future Enhancements​