ProjectTeamMember schema is the right join table but is bypassed by redundant fields on User and Project

[trillium]

Overview

The codebase tracks project membership and manager status in four separate places simultaneously. ProjectTeamMember was designed as the canonical join table for this relationship, but the app never fully committed to it — resulting in redundant, potentially out-of-sync fields on both User and Project that are used in its place.

Action Items

Research and document the current inconsistency

The four representations of the same two facts ("is a member" / "is a manager"):

Field	Model	Tracks
User.projects[]	User	basic project membership
User.managedProjects[]	User	manager status (denormalized on user)
Project.managedByUsers[]	Project	manager status (denormalized on project)
ProjectTeamMember.vrmsProjectAdmin	ProjectTeamMember	manager status (join table, currently unused)

The updateManagedProjects controller keeps User.managedProjects and Project.managedByUsers in sync with each other, but it never touches ProjectTeamMember.vrmsProjectAdmin. Any code reading vrmsProjectAdmin may see stale data.

Evaluate consolidating onto ProjectTeamMember as the single source of truth

ProjectTeamMember already has everything needed:

ProjectTeamMember {
  userId,
  projectId,
  teamMemberStatus,  // Active / Inactive
  vrmsProjectAdmin,  // Boolean — manager flag (already exists, currently bypassed)
  roleOnProject,     // Developer, PM, UX, etc.
}

If the app read/wrote through this model exclusively:

• "Is user a member?" → ProjectTeamMember.findOne({ userId, projectId })
• "Is user a manager?" → ProjectTeamMember.findOne({ userId, projectId, vrmsProjectAdmin: true })
• "All of a user's projects with manager status?" → ProjectTeamMember.find({ userId }) — one query returns both facts per row

This would allow removing User.managedProjects, Project.managedByUsers, and User.projects as redundant fields.

If consolidation is approved, the migration would involve:

1. Backfill — for every entry in User.managedProjects, find or create the corresponding ProjectTeamMember record and set vrmsProjectAdmin: true
2. Update all write paths — the updateManagedProjects controller writes to ProjectTeamMember.vrmsProjectAdmin instead of the denormalized fields
3. Update all read paths — anything reading User.managedProjects or Project.managedByUsers queries ProjectTeamMember instead
4. Drop the redundant fields once all paths are migrated

Note: Issue #2149 (manager toggle feature) requires reading from ProjectTeamMember to know a user's non-manager memberships. That ticket should write to vrmsProjectAdmin to move toward the clean model rather than adding another consumer of the redundant fields.

Resources/Instructions

• backend/models/projectTeamMember.model.js — join table schema
• backend/models/user.model.js — projects and managedProjects fields
• backend/models/project.model.js — managedByUsers field
• backend/controllers/user.controller.js — updateManagedProjects (the current write path)
• Related: As a manager, I want a Toggle Feature so that I can Enable Manager Access #2149 (manager toggle feature that surfaces this inconsistency)

[trillium]

Migration Approach: Expand / Contract (Two-Phase)

Rather than a single cutover, this migration runs in two independent phases, each safely reversible.

Migration 1 — Expand (backfill + dual-write)

Goal: populate ProjectTeamMember without breaking anything that reads the old fields.

1. For every User document, iterate user.projects and user.managedProjects
2. For each project in user.projects, find or create the ProjectTeamMember record for that (userId, projectId) pair
3. If the project also appears in user.managedProjects, set vrmsProjectAdmin: true — otherwise false
4. Update the write path in updateManagedProjects controller to write to both old fields (User.managedProjects, Project.managedByUsers) AND ProjectTeamMember.vrmsProjectAdmin simultaneously

App still reads from old fields after this step — nothing breaks. ProjectTeamMember is now being kept in sync going forward.

Verify before proceeding: spot-check that ProjectTeamMember.vrmsProjectAdmin is consistent with User.managedProjects across a sample of documents.

Migration 2 — Contract (cut over + drop)

Goal: switch reads to ProjectTeamMember, remove the redundant fields.

1. Update all read paths — anything reading User.managedProjects, Project.managedByUsers, or User.projects now queries ProjectTeamMember instead
2. Update the write path to drop the old side — updateManagedProjects writes only to ProjectTeamMember.vrmsProjectAdmin
3. Remove managedProjects and projects from User schema, managedByUsers from Project schema
4. Run a cleanup script to $unset those fields from existing MongoDB documents (schema removal alone does not purge stored data)

Why two phases?

A single cutover — backfill + switch reads simultaneously — leaves the app in an inconsistent state if the script fails mid-run with no safe rollback.

• Migration 1 failure: old fields still intact, app unaffected, re-run the script
• Migration 2 failure: ProjectTeamMember is already the source of truth, old fields still present as fallback, revert the read-path code change

Each phase is independently reversible.