The Submittal Tracker

What you’re building

You’ve got three active data center builds — SV9 in Santa Clara, CH2 in Chicago, and DEN3 in Denver. Across all three projects, you’re juggling somewhere between 60 and 100 submittals. The switchgear shop drawings for SV9 were due two weeks ago. The chiller O&M manuals for CH2 are “in review” but you have no idea who’s sitting on them. The generator test reports for DEN3 got kicked back once and you think the resubmittal came in, but honestly you’d have to go look.

All of this currently lives in an Excel file that was last updated… you’re not totally sure. Maybe last Tuesday? Probably last Tuesday. The PM cornered you in a meeting yesterday to ask about it and you had to hit him with the classic “let me check and get back to you.” We’ve all said it. We all hate saying it.

What if instead of that little moment of shame, you had one command that pulled every submittal across every project into a single view, told you exactly what’s overdue and by how many days, and spit out a weekly status report you could forward to the PM in 30 seconds? That’s what we’re building.

By the end of this lesson you’ll have a Node.js CLI tool that turns your multi-project submittal chaos into something that actually makes sense. It reads CSVs from each project, tracks lifecycle states (pending → submitted → in review → approved/rejected → resubmitted), flags overdue items with escalation tiers so you know who to yell at first, and generates weekly status reports at both the project and portfolio level. The kind of report that makes PMs think you have your shit together — because you will.

Here’s what it does

• Input: One CSV per project with columns for submittal ID, equipment type, vendor, status, date submitted, date due, reviewer, and notes — basically the columns you already have in The Spreadsheet, minus the sixteen hidden columns nobody remembers adding
• Lifecycle states: pending → submitted → in-review → approved / approved-as-noted / revise-and-resubmit / rejected → resubmitted → in-review (loop) — because nothing ever gets approved the first time
• Overdue detection: Compares due dates against today; flags items 1-7 days late (yellow), 8-14 days (orange), 15+ days (red). Three levels of “you need to call someone”
• Status commands: status (full portfolio), overdue (overdue only), project <code> (single project), report (formatted weekly report)
• Weekly report: Markdown document with summary stats, overdue items by escalation tier, project-level breakdowns, and action items — the kind of thing that takes 30 minutes to build manually and 30 seconds with this tool

The prompt

Build a Node.js CLI tool called submittal-tracker that manages submittal
lifecycles across multiple data center construction projects.

PROJECT STRUCTURE:
submittal-tracker/
  package.json
  src/
    index.js          (CLI entry point using Commander.js)
    csv-loader.js     (multi-file CSV loading and merging)
    lifecycle.js      (submittal state machine and transition rules)
    overdue.js        (overdue detection with escalation tiers)
    reporter.js       (weekly status report generator)
    formatter.js      (Markdown and terminal output formatting)
  data/
    sv9-submittals.csv   (sample data — Santa Clara project)
    ch2-submittals.csv   (sample data — Chicago project)
    den3-submittals.csv  (sample data — Denver project)

REQUIREMENTS:

1. CLI INTERFACE (src/index.js)
   - Subcommands:
     node src/index.js status [--project <code>]     (show current status of all/filtered submittals)
     node src/index.js overdue [--project <code>]     (show only overdue items, sorted by days overdue)
     node src/index.js report [--project <code>]      (generate weekly status report as Markdown)
     node src/index.js update <id> --status <new>     (transition a submittal to new state)
     node src/index.js import <csv-file> --project <code>  (import/refresh data from CSV)
   - Options:
     --data-dir <path> (directory containing project CSVs, default: ./data)
     --output <path> (output directory for reports, default: ./output)

2. CSV FORMAT (data/*.csv)
   Create sample CSVs for three projects with realistic submittal data.
   Columns: submittal_id, project, equipment_type, vendor, description,
   status, date_submitted, date_due, reviewer, days_overdue, notes

   Include 8-12 submittals per project with a mix of statuses:
   - SV9: 1 approved, 2 approved-as-noted, 2 in-review, 2 submitted, 2 pending, 1 revise-and-resubmit
   - CH2: 2 approved-as-noted, 1 approved, 2 in-review, 1 submitted, 3 pending, 1 resubmitted
   - DEN3: 1 approved, 1 approved-as-noted, 2 in-review, 3 submitted, 2 pending, 1 rejected, 1 resubmitted

3. LIFECYCLE STATE MACHINE (src/lifecycle.js)
   Valid states and transitions:
   - pending → submitted (vendor sends initial submittal)
   - submitted → in-review (team picks it up for review)
   - in-review → approved (passes technical review with no comments)
   - in-review → approved-as-noted (passes with minor comments that don't require resubmission — most common outcome)
   - in-review → revise-and-resubmit (needs significant revision before approval — vendor must resubmit)
   - in-review → rejected (fundamentally non-compliant, start over)
   - revise-and-resubmit → resubmitted (vendor sends corrected version)
   - rejected → resubmitted (vendor sends new version)
   - resubmitted → in-review (team reviews corrected version)

   Both "approved" and "approved-as-noted" are terminal success states.
   "Revise-and-resubmit" requires vendor action before manufacturing can begin.

   Reject invalid transitions with clear error messages (e.g., "Cannot transition
   from 'pending' to 'approved' — submittal must be submitted and reviewed first").

4. OVERDUE DETECTION (src/overdue.js)
   Three escalation tiers based on calendar days past the due date:
   - Tier 1 (1-7 days): Yellow flag, "Overdue — follow up with vendor"
   - Tier 2 (8-14 days): Orange flag, "Critical — escalate to vendor management"
   - Tier 3 (15+ days): Red flag, "Emergency — schedule call with vendor + PM"

   Only flag items in states that should have activity: submitted, in-review,
   revise-and-resubmit, resubmitted.
   Don't flag pending items (not yet due) or completed items (approved, approved-as-noted).

5. WEEKLY STATUS REPORT (src/reporter.js)
   Generate a Markdown report containing:
   - Report header: date range, total submittals tracked, portfolio summary
   - Portfolio summary: pie chart data (count by status across all projects)
   - Overdue summary: count by escalation tier, list of Tier 2 and Tier 3 items
   - Per-project breakdown: table of submittals with status, vendor, due date
   - Action items: auto-generated list of follow-ups needed
   - Write to output/weekly-report-<date>.md

DEPENDENCIES: commander, csv-parse, chalk, cli-table3

SAMPLE RUN:
node src/index.js overdue
# Shows all overdue submittals across all projects, sorted by days overdue

node src/index.js report --project SV9
# Generates weekly report for SV9 only

What you get

Fire it up

cd submittal-tracker
npm install
node src/index.js status
node src/index.js overdue
node src/index.js report

The status command dumps a color-coded table of every submittal across all three projects. It’s the view you wish you had every time someone walks up to your desk with “quick question.” The overdue command cuts the noise and shows only the stuff that needs attention, sorted by how badly it needs attention. And report generates a Markdown weekly status report — the kind of polished output that makes people assume you spent an hour putting it together. You didn’t. You spent three seconds.

If something is off

How it works (the 2-minute version)

Alright, here’s what’s happening under the hood — no computer science degree required.

1. CSV loader — grabs every project CSV from the data folder and mushes them into one big list of submittal objects. Each one keeps a project field so you can filter later. Think of it as dumping three project binders into one sorted pile.
2. State machine — this is the part that keeps you honest. Every submittal has a current state, and the tool has a map of what’s allowed to come next. You can’t jump from “pending” to “approved” because that’s not how reality works (even though we’ve all wished it did). Try an invalid transition and it tells you exactly what your options are.
3. Overdue detection — looks at every submittal in an active state (submitted, in-review, resubmitted), checks the due date against today, and does the math on how many calendar days late it is. Then it slots each one into an escalation tier so you know the difference between “send an email” and “holy shit, get someone on the phone.”
4. Report generator — takes all that data, rolls it up into summary stats, project breakdowns, and auto-generated action items, then formats the whole thing into a Markdown document that looks like you spent your morning on it. You did not spend your morning on it.

Customize it

Once the base tool is working, these are the prompts that turn it into something genuinely dangerous. Copy-paste either one and let the AI build it out.

Add email notification drafts

Add a --notify flag to the overdue command that generates draft email templates
for each overdue submittal. Tier 1 gets a polite follow-up, Tier 2 gets a firm
escalation, Tier 3 gets an urgent meeting request. Include the vendor name,
submittal description, days overdue, and project in each draft. Write drafts to
output/notifications/.

Add submittal aging histogram

Add a "stats" subcommand that shows a histogram of submittal ages (days since
submission) grouped in 7-day buckets. Highlight the buckets that exceed typical
review timelines. Also show average review cycle time for approved submittals
(days from submitted to approved) by equipment type.

Key takeaways

• State machines keep you from lying to yourself — you can’t accidentally mark something “approved” that was never submitted. The lifecycle enforces what actually has to happen in the real world, even when you’re tempted to skip steps on a Friday afternoon.
• Three tiers of “oh no” beat a wall of red — if everything is flagged as urgent, nothing is urgent. Tier 1 means send an email. Tier 2 means make a phone call. Tier 3 means cancel your lunch. That distinction matters when you have 30 overdue items and need to figure out which five will actually blow up your schedule.
• Seeing all three projects at once is the whole point — tracking one project is table stakes. Seeing SV9, CH2, and DEN3 in the same view is how you catch that Schneider is consistently late on submittals across every project. That’s a vendor management conversation, not a project-by-project problem.
• The weekly report is your reputation — when the PM asks “where do we stand on submittals?”, you pull up a formatted report in 30 seconds instead of stammering through “let me check The Spreadsheet.” That alone is worth building this thing.

What’s next

Next up is The Lead Time Watchboard — the tool that takes everything you’re tracking here and adds the dimension that actually keeps you up at night: time. It shows procurement windows across all your projects, works backward from milestone dates to figure out when you needed to have ordered something (spoiler: sometimes the answer is “three weeks ago”), and screams at you when a window is about to close. The submittals from this tracker feed directly into those lead time calculations, because knowing a submittal is approved doesn’t help if you don’t know whether you still have time to manufacture and ship the damn thing.