outside-process-tracker

Daily tracker for parts sent to outside vendors — heat treat, plating, anodize, NDT, paint, passivate, brazing, welding. Reads an OutsideOps Sheet the shop maintains, sends throttled follow-up emails to vendors whose parts are past their promised return date, flags upcoming returns so receiving can be ready, and emails a single morning digest to the owner. Cross-platform — uses the python runtime InTouch auto-creates on every OS.

Use case

Every machine shop in the world has this whiteboard:

J0512  Acme bracket Rev C  — heat treat @ Bodycote — out 4/28 — back 5/8
J0518  Beta housing Rev B  — anodize @ MSI         — out 5/1  — back 5/10
J0521  Gamma plate Rev A   — FPI @ Acuren NDT      — out 5/3  — back 5/9

Then someone notices Tuesday afternoon: "Wait, the J0512 was supposed to be back today." Then someone has to call Bodycote. Then someone forgets to call back when Bodycote says "tomorrow." Then it's Friday and the customer's pissed.

This is that whiteboard, except it never forgets and it sends the call for you.

Setup

1. Credentials (Credentials view): - google-workspace — Google Workspace service-account JSON
2. Runtime environment (Runtime Environments view): - python — auto-created on every OS install
3. OutsideOps Sheet (one-time): - Create a Sheet with a tab named OutsideOps and 14 columns: A ref_id E process I promised_return M follow_up_count B part_number F vendor_name J actual_return N notes C rev G vendor_email K status D qty H sent_date L last_followup_date - Row 1 is the header; data starts at row 2. - You maintain columns A–I and J/N: when shipping parts out, append a row with ref_id, part_number, rev, qty, process, vendor_name, vendor_email, sent_date, promised_return. When parts come back, fill actual_return (J) and set status (K) to returned or complete. - The workflow maintains columns K (overdue transitions), L, and M: writes overdue to K and stamps L + increments M each time it chases. - Copy the spreadsheet ID from the URL.
4. Edit workflow.yaml — replace the three placeholders: - <<OUTSIDE_OPS_SHEET_ID>> — OutsideOps spreadsheet ID - <<SHOP_NAME>> — your shop's name for the vendor-email signature - <<OWNER_PUBLISHER>> — user name for the owner digest
5. Schedule the workflow once daily, 7:30 AM (before shop-status-digest).

Status flow

(empty) or "out"     ← parts in flight at the vendor, nothing to do
     │
     │  past promised_return + every 3 days
     ▼
"overdue"            ← workflow has sent a follow-up email
     │
     │  shop receives parts, fills actual_return, sets status
     ▼
"returned"           ← parts physically back, awaiting inspection
     │
     │  shop closes the row out
     ▼
"complete"           ← ignored by the workflow

Pipeline

1. process — runtimeenv (python). Reads OutsideOps!A2:N, classifies each open row, sends throttled follow-up emails to vendors of overdue parts, writes back updated state to columns K/L/M, and prints a formatted digest of: rows chased today, rows still overdue (within throttle), expected returns today, expected returns in next 2 days, and rows that should have been chased but lack a vendor email.
2. notify-owner — message. Sends the digest to your owner user.

Follow-up throttle

Configurable in the script via FOLLOWUP_THROTTLE_DAYS (default 3). A row that's been overdue for 14 days gets follow-up emails on days 1, 4, 7, 10, 13 — five emails total, not fourteen. Prevents you from looking like a robot to a vendor whose plating tank is genuinely down.

Sample digest

OUTSIDE PROCESSING — Monday, May 11, 2026

OVERDUE (chased today, 2):
  - RFQ-20260428-091014  Acme bracket  @ Bodycote  — 3 days late  (follow-up #1)
  - RFQ-20260424-130022  Beta housing  @ MSI       — 7 days late  (follow-up #3)

OVERDUE (within 3-day throttle, 1):
  - RFQ-20260420-080000  Old plate     @ MetalCorp — 18 days late  (last chased recently, #6)

EXPECTED BACK TODAY (1):
  - RFQ-20260505-110000  Gamma plate Rev A  (10 ea, FPI)  from Acuren NDT

EXPECTED BACK IN NEXT 2 DAYS (1):
  - RFQ-20260509-130000  Delta block Rev B  (4 ea, heat-treat)  from Bodycote  (tomorrow)

Customization

• Throttle — change FOLLOWUP_THROTTLE_DAYS in the script. Common values: 2 (aggressive), 3 (default), 5 (patient).
• Lookahead — change UPCOMING_HORIZON_DAYS for how far out to flag expected returns.
• Vendor email template — edit the body = ... block. Keep it short and professional; vendor receiving clerks read 50 of these a day.
• CC the buyer or owner — add --cc [email protected] to the gog gmail send call.
• Different status vocabulary — change the if status in ("returned", "complete") check to match the words your shop uses (done, closed, received-inspected, etc.).
• Separate digests for different processes — duplicate the workflow, point each copy at a different Sheet tab (HeatTreat, Plating, etc.), or filter by the process column in Python before composing the digest.
• Send the receiving-dock digest separately — split notify-owner into two message tasks: one to the owner (full digest), one to the receiving dock (only EXPECTED BACK sections).

Companion workflows

• rfq-triage — front of the lifecycle; the ref_id you write into OutsideOps usually traces back to a row in QuoteLog
• coc-generator — back of the lifecycle; runs after outside processes are done and the part ships to the customer
• shop-status-digest — could be extended to count OVERDUE rows in its morning digest (one more SQL-ish pull); good follow-on enhancement once this workflow has been in service for a week

Run all five together and the small-shop lifecycle is fully observable from the morning coffee email.

Verified

gog sheets get/update, gog gmail send flag/command syntax all verified against the bundled gog v0.19.0 (2026-06-01); re-verify output shapes on first run. Cross-platform Python runtime confirmed in Server.kt. Throttle math and overdue-window arithmetic are pure-Python datetime.

Source

See workflow.yaml.