The Submittal Tracker
Last reviewed
IntermediateWhat you'll learn
~40 min- Merge submittal data from multiple project CSVs into a unified tracking database
- Implement submittal lifecycle states with transition validation
- Flag overdue submittals with escalation tiers based on days past due
- Generate weekly status reports with project-level and portfolio-level rollups
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.
Every procurement manager has The Spreadsheet. It started as a simple tracker. Then someone added conditional formatting. Then someone else added a pivot table. Then it got emailed around and now there are four versions and none of them agree. This tool kills The Spreadsheet. One source of truth, one command, always current. It’s like switching from driving to every store in District 18 hoping they put something out, to having the group chat ping you the second 203 drops. Less windshield time, better results.
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.
Multiple CSV inputs → merged database → state machine lifecycle tracking → filtered views + automated alerts. This pattern works for any multi-project tracking scenario: permit applications, inspection schedules, material deliveries, punch list items. Anywhere you’re tracking items through defined stages across multiple workstreams.
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
Before you start
If your agent is still open from the previous lesson, exit that session first (/exit or Ctrl+D). Create a clean folder for this lesson and open it:
mkdir -p ~/ofci-playbook/03-submittal-tracker && cd ~/ofci-playbook/03-submittal-trackerIn Windows PowerShell:
mkdir ~/ofci-playbook/03-submittal-tracker; cd ~/ofci-playbook/03-submittal-trackerOr open the folder directly in your desktop agent app. Start your agent the way Workbench Setup showed.
Switch to plan mode
Switch to Plan mode inside your session (Shift+Tab, /plan, or the mode picker) so the agent confirms your submittal lifecycle states and overdue rules before generating code.
The conversation
Say what you want
Ask for the multi-project submittal tracker in plain English:
I manage equipment submittals across three data center builds. Help me build a tool called submittal-tracker that merges project CSVs, enforces lifecycle states, flags overdue items, and writes weekly reports. Ask me what you need first.Answer its questions
Here are the questions your agent will likely ask and concise answers that lock in the domain rules:
| If it asks | You might say |
|---|---|
| Which projects and sample CSVs should it load? | Create sample CSVs in data/ for SV9, CH2, and DEN3 with 8 to 12 submittals each. |
| What are the valid submittal states and transitions? | pending -> submitted -> in-review -> approved, approved-as-noted, revise-and-resubmit, or rejected; revise/rejected -> resubmitted -> in-review. |
| How should overdue escalation tiers work? | Calendar days past due: Tier 1 is 1-7 days, Tier 2 is 8-14 days, Tier 3 is 15+ days. |
| Which states can be flagged as overdue? | Only active states: submitted, in-review, revise-and-resubmit, and resubmitted. Never flag pending, approved, or approved-as-noted. |
| Which subcommands should the CLI support? | status, overdue, report, update <id> --status <new>, and import <csv-file> --project <code>, with an optional --project filter. |
ℹReference notes to hand the agent (or check against)click to expand
Use these CSV columns across data/sv9-submittals.csv, data/ch2-submittals.csv, and data/den3-submittals.csv:
submittal_id, project, equipment_type, vendor, description, status, date_submitted, date_due, reviewer, days_overdue, notes
Sample status mixes to test every branch:
- SV9 (Santa Clara): 1 approved, 2 approved-as-noted, 2 in-review, 2 submitted, 2 pending, 1 revise-and-resubmit
- CH2 (Chicago): 2 approved-as-noted, 1 approved, 2 in-review, 1 submitted, 3 pending, 1 resubmitted
- DEN3 (Denver): 1 approved, 1 approved-as-noted, 2 in-review, 3 submitted, 2 pending, 1 rejected, 1 resubmitted
Both approved and approved-as-noted are terminal success states (approved-as-noted passes with minor comments that do not require resubmission). revise-and-resubmit requires vendor action before fabrication can begin. For packages, use commander, csv-parse, cli-table3, and chalk@4 (chalk 5 and later are ES-module-only and break require()).
Read the plan
Check the agent’s plan for four things before you let it build:
- Multi-file CSV merging: Loads every project CSV from
--data-dir(default./data) and preserves the project code for--projectfiltering. - Strict state transition validation: Rejects illegal jumps (such as
pendingdirectly toapproved) with a clear message showing valid next states, and writes valid updates back to the CSV. - Active-only overdue tiers: Counts calendar days past
date_dueonly forsubmitted,in-review,revise-and-resubmit, andresubmitted, grouping them into Tier 1 (1-7 days, yellow), Tier 2 (8-14 days, orange), and Tier 3 (15+ days, red). - Weekly Markdown report: Writes
output/weekly-report-<date>.mdwith portfolio status counts, Tier 2 and Tier 3 overdue lists, per-project tables, and follow-up action items.
If the plan checks overdue dates on every row regardless of status, correct it first:
Only flag submitted, in-review, revise-and-resubmit, and resubmitted items as overdue. Skip pending, approved, and approved-as-noted items.Approve the plan once all four items are in place, and ask the agent to save a Git checkpoint after the initial build works.
What you get
Fire it up
Ask your agent to install what is needed, run the portfolio status and overdue checks, and generate the weekly report:
Install what the tool needs, run the status, overdue, and report commands across SV9, CH2, and DEN3, and show me the weekly report.If you prefer running the commands in a terminal yourself:
cd submittal-trackernpm installnode src/index.js statusnode src/index.js overduenode src/index.js reportThe status command shows 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
| Problem | Follow-up prompt |
|---|---|
| CSV files not loading from data directory | The tool can't find the CSV files. Make sure it looks inside the --data-dir folder (defaulting to ./data) and loads every .csv file in that directory automatically. |
| State transitions not being validated | The update command accepts any status change without checking if it's allowed. Reject invalid jumps like pending straight to approved and print the valid next states. |
| Overdue calculation counting weekends | Days overdue should count total calendar days rather than business days, since vendor deliveries and submittal review cycles continue across weekends. |
When Things Go Wrong
Use the Symptom → Evidence → Request pattern: describe what you see, paste the error, then ask for a fix.
How it works (the 2-minute version)
Alright, here’s what’s happening under the hood — no computer science degree required.
- CSV loader — grabs every project CSV from the data folder and mushes them into one big list of submittal objects. Each one keeps a
projectfield so you can filter later. Think of it as dumping three project binders into one sorted pile. - 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.
- 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.”
- 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, you can extend it with short follow-up requests in the same session.
Add email notification drafts
Add a --notify flag to the overdue command that writes draft follow-up emails to output/notifications/ for each overdue submittal: polite for Tier 1, firm escalation for Tier 2, and urgent meeting request for Tier 3.Add submittal aging histogram
Add a stats subcommand that prints a histogram of submittal ages in 7-day buckets, highlights buckets exceeding typical review timelines, and shows average review cycle 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.
You run the overdue command and see that submittal DEN3-007 (Eaton switchgear shop drawings) is 16 days past due in 'in-review' status. The lifecycle state machine lets in-review move to approved, approved-as-noted, revise-and-resubmit, or rejected. What should you do?
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.