Legal Product Portfolio Org Chart — How Does This Work? Back to Org Chart
🗺️

Overview

What this is and how it stays up to date

The Legal PP Org Chart is a self-updating web application hosted on AWS. The roster data lives in a Microsoft Excel spreadsheet in OneDrive. Every night an automated pipeline reads the spreadsheet, converts it to JSON, and pushes it to AWS where the org chart serves it to your browser — with no manual intervention required.

If the live data feed fails for any reason, the chart falls back to a baked-in copy of the last known good roster so it always renders something useful.

🏗️

Architecture

How all the pieces connect

┌──────────────────────────────────────────────────────────────┐ │ DATA SOURCE │ └──────────────────────────────────────────────────────────────┘ Excel XLSX _Legal_PP_Master_Staffing_Roster.xlsx Dave Minker's OneDrive → Documents → Legal Data lives in named Table: ActiveRosterTable │ Power Automate — nightly 2am weekdays (or manual trigger) ▼ ┌──────────────────────────────────────────────────────────────┐ │ AUTOMATION LAYER │ └──────────────────────────────────────────────────────────────┘ Office Script "RosterExport" (Automate tab inside the XLSX) Reads ActiveRosterTable, validates columns, converts serial dates, returns JSON payload Power Automate "Legal PP Org Chart — Nightly Roster Refresh" Checks ok=true, sends alert emails on error, POSTs validated JSON to AWS API Gateway │ HTTP POST x-api-secret header ▼ ┌──────────────────────────────────────────────────────────────┐ │ AWS LAYER │ └──────────────────────────────────────────────────────────────┘ API Gateway LegalPPOrgChartRosterAPI → POST /roster │ ▼ Lambda legalPPOrgChartRosterJSONWriter (Node.js) 1. Validates auth + payload 2. Writes roster.json → S3 3. Reads index.html from S3, rebuilds fallback roster between FALLBACK_ROSTER_START / END markers 4. Writes updated index.html → S3 5. Invalidates /roster.json + /index.html in CloudFront │ ▼ S3 Bucket (private — Block Public Access ON) index.html the org chart application roster.json the live roster data │ CloudFront OAC — signed AWS internal request ▼ CloudFront E22GASJYKRDOUR d3g5nwlolqxpjp.cloudfront.net HTTPS, cache auto-invalidated on every roster write │ ▼ Browser Loads index.html, fetches roster.json (same origin) Falls back to baked-in roster if fetch fails
🔄

Data Flow — Step by Step

What happens on every nightly run

  • 1
    Roster owner updates the Excel spreadsheet
    Changes are made in _Legal_PP_Master_Staffing_Roster.xlsx in Dave Minker's OneDrive (Documents → Legal). Data must be within the named Excel Table ActiveRosterTable — rows outside the table are not picked up by the script.
  • 2
    Power Automate triggers (nightly or manually)
    The flow runs automatically at 2am on weekdays. To trigger manually: make.powerautomate.com → My flows → "Legal PP Org Chart — Nightly Roster Refresh" → Run. Use manual triggers whenever the roster changes and you need the chart updated immediately.
  • 3
    Office Script reads and validates the roster
    The RosterExport script (saved in the Automate tab of the XLSX) reads ActiveRosterTable, confirms required columns exist, converts Excel serial date numbers to readable dates, flags duplicates and blank rows, and returns a JSON payload with an ok field indicating success or failure.
  • 4
    Power Automate checks for errors and warnings
    If ok is false the flow sends a failure alert email and stops — the live chart is not updated. If there are warnings (e.g. a duplicate name was skipped) an advisory email is sent but the flow continues with the valid rows.
  • 5
    JSON payload is POSTed to AWS API Gateway
    Power Automate sends an HTTP POST to the API Gateway /roster endpoint with the roster JSON in the body and the x-api-secret header for authentication. If the POST fails, a failure alert email is sent.
  • 6
    Lambda validates, writes to S3, updates fallback, invalidates cache
    The Lambda function checks the secret header, validates the payload, writes roster.json to S3, reads index.html from S3 and replaces the roster data between the FALLBACK_ROSTER_START and FALLBACK_ROSTER_END comment markers, writes the updated HTML back to S3, then sends a CloudFront invalidation for both files.
  • 7
    CloudFront serves updated files within ~30 seconds
    The CloudFront invalidation clears the cached copies of both files. Within 30–60 seconds any browser loading the org chart URL receives the updated versions from S3 via CloudFront.
  • 8
    Browser loads the chart and fetches live roster
    When someone opens the org chart URL, their browser loads index.html then fetches roster.json from the same CloudFront origin (no CORS issues). If the fetch succeeds the chart renders from live data and shows the generatedAt timestamp in the header. If it fails for any reason the chart silently falls back to the baked-in roster and shows "(fallback)" in the header.
📦

Asset Inventory

Where every piece of this system lives

Asset Platform Location / Name Purpose
Master Staffing Roster
Excel XLSX
 OneDrive _Legal_PP_Master_Staffing_Roster.xlsx
Dave Minker's OneDrive → Documents → Legal
 Source of truth. Contains named Table ActiveRosterTable.
Office Script
TypeScript
 Excel Online RosterExport
Automate tab inside the XLSX
 Reads ActiveRosterTable, validates, converts dates, returns JSON. Edit via Excel Online → Automate → RosterExport.
Power Automate Flow
Cloud flow
 Power Automate Legal PP Org Chart — Nightly Roster Refresh
make.powerautomate.com — Dave Minker's personal space
 Orchestrates the nightly pipeline. Triggers script, checks errors, POSTs to API Gateway, sends alert emails.
API Gateway
HTTP API
 AWS LegalPPOrgChartRosterAPI
AWS Console → API Gateway → us-east-1 → BCG-LegalInnov-Sbox-POC
 Receives POST /roster from Power Automate. Triggers Lambda. Protected by x-api-secret header.
Lambda Function
Node.js
 AWS legalPPOrgChartRosterJSONWriter
AWS Console → Lambda → us-east-1 → BCG-LegalInnov-Sbox-POC
 Validates payload, writes roster.json, rebuilds index.html fallback, invalidates CloudFront.
IAM Policy
Scoped
 AWS LegalPPOrgChartLambdaPolicy
AWS Console → IAM → Policies → BCG-LegalInnov-Sbox-POC
 Grants Lambda write access to roster.json and index.html only, and CreateInvalidation on this CloudFront distribution.
S3 Bucket
Private
 AWS legal-pp-org-chart-poc-574643567791-us-east-1-an
AWS Console → S3 → us-east-1 → BCG-LegalInnov-Sbox-POC
 Hosts index.html and roster.json. Fully private — accessible only via CloudFront OAC.
Origin Access Control
OAC
 AWS LegalPPOrgChartOAC
AWS Console → CloudFront → Origin access → BCG-LegalInnov-Sbox-POC
 Allows CloudFront to read from the private S3 bucket without making the bucket public. Not affected by BCG security controls that remove public bucket policies.
CloudFront Distribution
CDN
 AWS E22GASJYKRDOUR
d3g5nwlolqxpjp.cloudfront.net — BCG-LegalInnov-Sbox-POC
 Serves index.html and roster.json over HTTPS. Auto-invalidated by Lambda after each roster update.
Org Chart HTML
index.html
 Claude + S3 index.html in S3 bucket
Also maintained in Claude.ai conversation history
 The org chart application. Contains FALLBACK_ROSTER_START / FALLBACK_ROSTER_END markers for Lambda to update the baked-in fallback roster.
🔑

Credentials & Configuration

All secrets and config values needed to operate or rebuild the system — store these securely

Item Where Used How to Find / Rotate
API secret value
API_SECRET Lambda env var
 Power Automate HTTP header; Lambda auth check Lambda → legalPPOrgChartRosterJSONWriter → Configuration → Environment variables. To rotate: change in Lambda AND update the x-api-secret header in the Power Automate HTTP step simultaneously.
API Gateway Invoke URL
 Power Automate HTTP POST URI (+ /roster) API Gateway → LegalPPOrgChartRosterAPI → main page → Invoke URL (BCG-LegalInnov-Sbox-POC).
S3 Bucket Name
BUCKET_NAME Lambda env var
 Lambda reads/writes index.html and roster.json legal-pp-org-chart-poc-574643567791-us-east-1-an — Lambda → legalPPOrgChartRosterJSONWriter → Configuration → Environment variables.
CloudFront Distribution ID
CLOUDFRONT_DISTRIBUTION_ID Lambda env var
 Lambda sends cache invalidations CloudFront console → distributions list → BCG-LegalInnov-Sbox-POC. Current value: E22GASJYKRDOUR.
AWS Account ID
 IAM policy ARNs, bucket policy 574643567791 — BCG-LegalInnov-Sbox-POC. Visible in the top-right corner of the AWS console.
Rebuilding from scratch? The full step-by-step build guide — including S3 setup, CloudFront OAC migration, Lambda code, Power Automate flow spec, and Office Script — is preserved in the Claude.ai conversation where this system was built. Search Claude conversation history for "Legal PP Org Chart."
⚡

How to Update the Roster Right Now

Without waiting for the 2am nightly run

  • 1
    Update the spreadsheet
    Make your changes in _Legal_PP_Master_Staffing_Roster.xlsx (OneDrive → Documents → Legal). Save the file.
  • 2
    Trigger the Power Automate flow manually
    Go to make.powerautomate.com → My flows (Dave Minker's personal space) → "Legal PP Org Chart — Nightly Roster Refresh" → click Run. No parameters needed.
  • 3
    Wait 30–60 seconds
    The flow takes ~30 seconds to complete. Lambda then sends a CloudFront invalidation which takes another 15–30 seconds to propagate globally.
  • 4
    Hard refresh the org chart
    Press Ctrl+Shift+R (Windows) or Cmd+Shift+R (Mac) to bypass browser cache. The "Last updated" timestamp in the header should reflect the new run time.
How to tell if live data is loading: the "Last updated" text shows a date and time (e.g. "Last updated: Jul 23, 2026 at 2:00 AM"). If it shows "(fallback)" the live fetch failed — check roster.json directly. If that loads correctly, the issue is browser cache; do a hard refresh.
🚨

Alert Emails — What They Mean

What to do when something goes wrong

Subject What happened What to do
⚠️ …FAILED — action required
 Office Script returned ok: false. A required column is missing or renamed, or ActiveRosterTable can't be found. Check the XLSX — confirm ActiveRosterTable exists and column headers match the COLUMN_MAP in the RosterExport script. Re-run the flow once fixed.
ℹ️ …warnings to review
 Script ran successfully but found issues like duplicate names or blank rows. Chart was updated with the valid rows. Review the warnings in the email body. Usually a duplicate name that needs disambiguation in the XLSX (e.g. "Abhinav Jain (int)" vs "Abhinav Jain").
⚠️ …FAILED — API error
 Office Script succeeded but the HTTP POST to AWS failed. Chart was NOT updated. Check Lambda → CloudWatch logs in AWS for the error. Common causes: Lambda crashed, S3 permissions changed, or the API secret changed without updating Power Automate.
🔧

Common Maintenance Tasks

What to do when things need changing

Task What to do
Add or remove a person Update ActiveRosterTable in the XLSX → trigger Power Automate manually.
Rename a column in the XLSX Update COLUMN_MAP at the top of the RosterExport Office Script to match the new header. This is the only place column names are defined.
Rename the Excel Table Update TABLE_NAME at the top of the RosterExport Office Script.
Change the alert email address Update the "To" field in each email action in the Power Automate flow.
Rotate the API secret Change API_SECRET in Lambda env vars AND update the x-api-secret header in the Power Automate HTTP step simultaneously — do both at once or the flow will break in the gap.
Chart shows fallback unexpectedly Check roster.json directly. If it 403s, the S3 bucket policy was cleared — re-apply the CloudFront OAC bucket policy: S3 → your bucket → Permissions → Bucket policy.
Add or change a squad / colour Update the SQUADS object and SQUAD_LABELS map at the top of index.html. Upload to S3 and invalidate /index.html in CloudFront.
Add a new field to the chart Requires coordinated changes to three places: index.html (display/filter), the Office Script COLUMN_MAP (export), and the Parse JSON schema in Power Automate (pass-through). Do all three together.
Update index.html with new features Share the current index.html and describe the change in Claude.ai (same conversation or a new one). Claude understands the full codebase and can regenerate. Upload the result to S3 — Lambda will update the fallback on the next flow run.
🤖

How This Was Built

Where to find the full history if you need to rebuild or extend

This org chart and its entire supporting infrastructure — the Office Script, Power Automate flow spec, Lambda function, AWS architecture, and this documentation page — were designed and built collaboratively with Claude (Anthropic) in a single extended conversation in Claude.ai.

The full conversation is preserved in Claude.ai and contains the complete decision history, including dead ends explored (SharePoint hosting, CORS issues, the vibe-sandbox platform limitations) and the reasoning behind every architectural choice. If you need to make significant changes, picking up that conversation is the fastest way to get context — search Claude conversation history for "Legal PP Org Chart."