Create API Endpoint to Add or Remove PM Users from a Project via Tables Integration

[JackHaeg]

Overview

Create an API endpoint that allows Tables to add or remove a PM user to/from a specific VRMS project (triggered by the submission of the Project Onboarding / Offboarding Google Forms). The API should validate the user and project relationship and return a clear status message describing the result.

Action Items

• Research current VRMS user, project, and role models to understand how PM/admin assignments are represented in the database.
• Define a new authenticated API endpoint (or endpoints) that supports:
  • Validating whether a user exists in VRMS (based on GitHub username and associated metadata such as email/name).
  • Checking whether the user is currently associated with a given project.
  • Adding the user to the project with a PM/admin role (onboarding).
  • Removing the user from the project with a PM/admin role (offboarding).
• Define the request payload to accept data from Tables, including:
  • User Email Address (required)
  • Project name (required)
  • Action type (add or remove)
• Implement response handling so the API returns only one of the following messages (CONFIRM message to be passed to GitHub Log & what format this should be sent in):
  • "not a valid user" (for both onboarding or offboarding when the user cannot be validated)
  • "no [project] admin assignment" (user is valid but was not assigned to the project when removal is requested)
  • "removed [project] admin assignment" (user was assigned and successfully removed)
  • "Added [project] admin assignment" (user was not assigned and was successfully added)
• Add appropriate authorization checks to ensure only trusted integrations (e.g., Tables) can call this endpoint.
• Document the endpoint, including request/response examples, for future maintainers.

Resources/Instructions

• Existing VRMS API patterns and authentication middleware (review current API routes in the backend codebase)
• (To ADD) GitHub Log integration documentation if required

Examples of API responses - Provided by Tables Team

The API responses we currently get from GitHub and Google Drive are quite different from each other, so here's a quick breakdown to help inform the VRMS API design:

GitHub returns standard HTTP status codes alongside a JSON body. Reference: https://docs.github.com/en/rest/issues/comments?apiVersion=2022-11-28

Google Drive works differently — since we use Google Apps Script's built-in Drive wrapper, there is no visible HTTP code or JSON body. The wrapper handles everything internally and converts any errors into a JavaScript exception, which we catch like this:

try {
  Drive.Permissions.insert(properties, driveId, { 
    sendNotificationEmails: true, 
    supportsAllDrives: true 
  });
  // if we get here, it worked silently ✅

} catch (error) {
  // GAS converted the HTTP error into this
  Logger.log(error.message); 
  // e.g. "GoogleJsonResponseException: API call to drive.permissions.insert 
  //       failed with error: The caller does not have permission"
  
  throw new Error(`Failed to add ${email} to Drive ${driveId}: ${error.message}`);
}

1Password — we are currently building a custom REST API that wraps the 1Password CLI. It follows the same pattern as GitHub, returning both an HTTP status code and a JSON body:
Success — HTTP 200 + body:

{
  "success": true,
  "message": "Granted user@example.com access to vault abc123",
  "permissions": ["allow_viewing", "allow_editing"]
}

Failure — HTTP 400/500 + body:

{
  "success": false,
  "message": "User not found in 1Password"
} 

currently the status codes are

| Status Code | Description |
|-------------|-------------|
| 200         | Success — vault access granted, revoked, or data returned |
| 400         | Bad Request — missing required fields (e.g. email or vaultId) |
| 401         | Unauthorized — invalid or missing API key |
| 500         | Server Error — CLI failure or session issue |Note: the API is still expanding so this may evolve, but will continue to follow the same pattern.

based on the above, we'd suggest returning JSON with at least a success boolean and a human-readable message string. Any additional data (like a confirmed user ID or role) can live in optional data fields. This keeps it consistent with what our GAS client already knows how to parse.
As for HTTP status codes, it is optional and up to your team's preference — as long as the JSON body contains a clear success boolean and message, our GAS client can work with it either way.

Screenshots of Project Onboarding / Offboarding Google Forms

[JackHaeg]

Response from Wesley L from Tables team re: API responses from GitHub and google Drive:

Slack response

Hi, [@jack Haeger](https://hackforla.slack.com/team/U059005TN1L) [@trillium Smith](https://hackforla.slack.com/team/U010G9864Q1)

The API responses we currently get from GitHub and Google Drive are quite different from each other, so here's a quick breakdown to help inform the VRMS API design:

GitHub returns standard HTTP status codes alongside a JSON body. Reference: https://docs.github.com/en/rest/issues/comments?apiVersion=2022-11-28

Google Drive works differently — since we use Google Apps Script's built-in Drive wrapper, there is no visible HTTP code or JSON body. The wrapper handles everything internally and converts any errors into a JavaScript exception, which we catch like this:
try {
Drive.Permissions.insert(properties, driveId, {
sendNotificationEmails: true,
supportsAllDrives: true
});
// if we get here, it worked silently ✅

} catch (error) {
// GAS converted the HTTP error into this
Logger.log(error.message);
// e.g. "GoogleJsonResponseException: API call to drive.permissions.insert
// failed with error: The caller does not have permission"

throw new Error(Failed to add ${email} to Drive ${driveId}: ${error.message});
}1Password — we are currently building a custom REST API that wraps the 1Password CLI. It follows the same pattern as GitHub, returning both an HTTP status code and a JSON body:
Success — HTTP 200 + body:
{
"success": true,
"message": "Granted user@example.com access to vault abc123",
"permissions": ["allow_viewing", "allow_editing"]
}Failure — HTTP 400/500 + body:
{
"success": false,
"message": "User not found in 1Password"
} currently the status codes are

Status Code	Description
200	Success — vault access granted, revoked, or data returned
400	Bad Request — missing required fields (e.g. email or vaultId)
401	Unauthorized — invalid or missing API key
500	Server Error — CLI failure or session issue

based on the above, we'd suggest returning JSON with at least a success boolean and a human-readable message string. Any additional data (like a confirmed user ID or role) can live in optional data fields. This keeps it consistent with what our GAS client already knows how to parse.
As for HTTP status codes, it is optional and up to your team's preference — as long as the JSON body contains a clear success boolean and message, our GAS client can work with it either way.