PO Automation System

Goals

• Parse PO PDFs automatically regardless of customer format
• Resolve DST files from FileMaker automatically where possible
• Human reviews staged data before anything touches FileMaker
• One-click push from Sheets to FileMaker once approved
• Auto-reply to customer after push
• Never silently miss an email

Key Design Decisions

• Self-hosted n8n on EC2 to consolidate all tools on one server
• Phased rollout — FileMaker writes only after parsing accuracy is validated
• Dedicated Gmail account for orders (not main business account)
• Dedicated FileMaker API user with limited write access only
• DST picker popup shows embroidery design images from FM container fields

EC2 Upgrade Procedure

1. EC2 Console → Actions → Image → Create Image
2. Stop the EC2 instance
3. Actions → Instance Settings → Change Instance Type → t3.medium
4. Start the instance
5. SSH in → pm2 list to confirm services running
6. npm install -g n8n → pm2 start n8n --name n8n

Downtime: ~3 minutes. Existing services auto-restart via PM2.

Inbound — Email to Sheets

Email arrives in orders Gmail
  → n8n: fetch email + all PDF attachments
  → OpenAI classifies email + PDF:
       "PO"            → process (Esquire in VENDOR section)
       "Supply Order"  → log to Supply Orders tab (Esquire in SHIP-TO, or Sanmar/Alphabroder/S&S vendor)
       "Question"      → log to Questions tab
       "Revision"      → log to Needs Review tab for manual handling
       "Needs Review"  → confidence <80% → flag to Needs Review tab
  → For Embroidery POs:
       Extract structured data (PO#, dates, ship-to, DST items)
       Detect "Repeat order XXXX" or "same as previous"
         YES → query FileMaker for previous Dst_ref rows
       Build Instructions field (¶-separated) from PO keywords + Customer Master
         (Split Shipping, Folded & Bagged, Bagged By Name, Digitized, Sew Out,
          Personalization, Seam Seal, 3rd Party, Pick-Up, UPS Ship, RUSH)
         If RUSH → also set Description_2 = "Hard due date: YYYY-MM-DD"
       Detect Wilcom worksheet attachment
         YES → extract design name directly
       Resolve DST files (see Section 5)
  → Write to Google Sheets (PO Queue + DST Items tabs)
  → Apply Gmail label: n8n/PO-processed
  → Send daily digest at 8am

Outbound — Sheets to FileMaker

Human reviews PO Queue row
  → Resolves any flagged DST Items
  → Clicks [Send to FileMaker] button

n8n webhook fires:
  Step 1 → Single POST to Order Details layout
             portalData includes all Ship_To rows + all Dst_ref rows
             → FileMaker creates Order + related records atomically
  Step 2 → Send Gmail reply to customer
  Step 3 → Mark Sheets row "Sent to FileMaker ✓"

If Step 1 fails:
  → No partial records created (atomic FM operation)
  → Mark row "Error: [FM error message]"

Tables Involved

Key Field Rules

ThirdPartyAcct Rule

If Customer Master QB::ThirdPartyAcct <> "" (looked up from Google Sheets Customer Master tab):

Instructions Field

Instructions is a ¶-separated string built by n8n from detected conditions. Instructions Display = Substitute(Instructions; "¶"; "    ")

PDF Classification Rule

DST File Resolution

All Tabs at a Glance

Tab 1 — PO Queue Primary

Tab 2 — DST Items Primary

Tab 3 — Ship To Items Multi-shipment orders only

Only used when a PO has more than one delivery address. Single-shipment orders (the majority) keep ship-to fields inline in Tab 1.

Tabs 4–8 — Reference & Safety Logs

Live at tools.esquireembroidery.com/dst-picker/. All backend logic (search, image proxy, write-back) runs on the existing EC2 portal server (filemaker-node-uploader/server.js) — no n8n webhook involved for the picker itself.

URL Pattern

https://tools.esquireembroidery.com/dst-picker/?po_row_id=1779637260959&search_term=Bobcat&po_number=56089

po_row_id + search_term together identify the exact DST Items row to write back to (matched by value, not a literal row number — more robust against row insertions/reordering).

Card Preview (one per candidate)

Company Search & Preferred Logos

An optional company field (autocomplete against Customer Master, GET /dst-companies?q=) narrows and re-sorts results: candidates already linked to that company are badged "This company," and staff can pin a specific design as the go-to pick for a company + keyword combo (e.g. "Wagner" → a specific design), stored in /home/ec2-user/dst-preferences.json. Preferred picks always sort first and show a "★ Preferred" badge.

Thread Color Swatches

Numeric thread codes (e.g. 1801) are resolved to actual color swatches via a chart parsed from an uploaded Excel file (uploads/Color Chart.xlsx → build-thread-colors.js → thread-color-chart.json, keyed by code). Codes with no chart match fall back to a plain text chip. Rebuild after uploading a new chart via the "Rebuild Color Chart" button on /dst-sync/.

Image Proxy

Zero Results

Not Yet Wired

Gmail Labels Applied After Every Email

How to See Labels in Gmail

Labels appear in the left sidebar of Gmail. Because the label names use /, Gmail nests them automatically as a group:

▼ n8n
    PO-processed
    Question
    Needs-Review
    Supply-Order

Click any label name to see all emails tagged with it — works like a folder.

Finding Emails With No Label (Safety Audit)

To catch any email n8n never touched, type this in the Gmail search bar:

has:nouserlabels in:inbox

Any result here = n8n was down or failed silently when that email arrived.

Read / Unread Behaviour

Inbox
  [unread]  New email just arrived       ← n8n hasn't run yet (normal, <5 min)
  [read]    PO from BluErth         🏷 n8n/PO-processed
  [read]    Question from DT Logos  🏷 n8n/Question
  [read]    Sanmar supply order     🏷 n8n/Supply-Order

If you see unread emails sitting for more than 5–10 minutes during business hours → SSH into the server and run pm2 list to check n8n is running.

Daily Digest (8am)

n8n sends a summary email to the team each morning: counts + subject lines of all emails processed the previous day.

Confidence Thresholds

• ≥80% PO confidence → PO Queue tab
• ≥80% Question confidence → Questions tab
• <80% either way → Needs Review tab
• "Esquire Embroidery" in SHIP-TO → always Supply Orders

The Phase 2 n8n workflow is split into independent sub-workflows for easier testing, debugging, and replacement. Each sub-workflow starts with a "When called by another workflow" trigger and can be tested in isolation with sample data.

Architecture Overview

Gmail Trigger (SW0 — Main Orchestrator)
  → Edit Fields                   → messageId, emailSubject, emailText, emailFrom, received_date
  → SW2: PDF Handler              → hasPdf + pdfData (URL-safe base64) + filename
  → Code: Merge + Convert Base64  → standard base64 (replace -/_ with +//)
  → IF: hasPdf?
      true  → HTTP Request (OpenAI /v1/responses, PDF inline)
      false → HTTP Request (OpenAI /v1/responses, text only)
  → Code3: Parse + Classify       → category + confidence (confidence <80 → Needs Review)
  → Switch: Route by Category
      PO             → SW3 → Code (merge) → SW4 → SW5
      Question       → Sheets Append → SW5
      Supply Order   → Sheets Append → SW5
      Revision       → Sheets Append → SW5
      Needs Review   → Sheets Append → SW5

SW6 (Notify) and SW7 (Daily Digest) not yet built. SW7 runs on its own schedule — not called by the main orchestrator.

SW0 — Main Orchestrator

SW1 — Classification (inlined in SW0)

SW2 — PDF Handler

SW3 — Extract PO Data

SW4 — Write to Sheets Updated 2026-06-13

SW5 — Label Manager Complete ✓

Customer Master Sync Complete ✓

Standalone n8n workflow. Run on demand to refresh company and personnel lookup data from FileMaker dev server into Google Sheets.

SW6 — Notify

SW7 — Daily Digest (Standalone)

Step 1 — Gmail Labels (orders@esqembroidery.com)

Log into orders@esqembroidery.com and create the following labels. Because the names use /, Gmail nests them automatically under an n8n group.

Step 2 — Gmail Filter (orders@esqembroidery.com)

Create a filter that auto-labels all incoming emails with n8n/pending:

1. Gmail → Settings → Filters and Blocked Addresses → Create a new filter
2. In the To field enter: orders@esqembroidery.com
3. Click Create filter
4. Check Apply the label → select n8n/pending
5. Check Also apply filter to matching conversations (optional — applies to existing inbox)
6. Click Create filter

Step 3 — Google Service Account

The production n8n instance needs a Service Account credential to read Google Sheets reliably (no OAuth expiry, no re-verification required). This replaces the OAuth2 Google Sheets credential used during testing.

1. Go to console.cloud.google.com
2. Select the correct Google Cloud project
3. Left menu → IAM & Admin → Service Accounts
4. Click + Create Service Account
5. Name it n8n-sheets-reader → click Create and Continue
6. Skip optional role steps → click Done
7. Click the new service account in the list → Keys tab → Add Key → Create new key → JSON → Create
8. A JSON key file downloads automatically — keep it safe

Share the Google Sheet with the service account:

1. Open the PO Automation Google Sheet
2. Click Share
3. Paste the service account email (looks like n8n-sheets-reader@your-project.iam.gserviceaccount.com)
4. Set permission to Editor (n8n needs to append rows)
5. Uncheck "Notify people" → click Share

Add the credential in n8n:

1. Open any workflow that has a Google Sheets node (e.g. SW4)
2. Click on a Google Sheets node
3. Click the Credential dropdown → Create new credential
4. Search for Google API
5. Paste the entire contents of the downloaded JSON key file
6. Save — name it Google Sheets Service Account
7. Update all Google Sheets nodes in SW4, Customer Master Sync, and any other workflow that reads/writes Sheets to use this new credential

Step 4 — Switch n8n Gmail Trigger to Production Inbox

In SW0 (Main Orchestrator):

1. Open the Gmail Trigger node
2. Change the connected Gmail credential from chi@esqembroidery.com → orders@esqembroidery.com
3. Confirm the label filter is still set to n8n/pending
4. Save and re-activate SW0

Step 5 — Switch FileMaker to Production Server

During testing, all FM API calls point to the dev server (54.172.214.224). Switch to production before go-live:

Update the FM URL in: Customer Master Sync (FM Login node), and any future FM write nodes added in Phase 4.

Step 6 — Run Customer Master Sync

After switching to the production FM server, run the Customer Master Sync workflow manually once to populate the Google Sheets Customer Master and Personnel tabs with production data.

1. Open the Customer Master Sync FM → Sheets workflow in n8n
2. Click Execute Workflow
3. Verify Tab 4 (Customer Master) and Tab 9 (Personnel) are populated

Step 7 — DST File Master Layout Setup (Order_managementDB)

The DST File Master sync (Phase 3) reads from a dedicated FileMaker layout built specifically for the n8n Data API integration. This layout must be recreated in the production Order_managementDB file before the DST sync can be switched over — it does not exist there by default.

• Database: Order_managementDB
• Layout name: @DST file n8n (must be unique — no other layout may share this exact name)
• Base table: DST file

Required fields on the layout (confirmed working against dev, 2026-07-18):

Account / privilege setup:

1. Confirm the n8n-reader account exists under File → Manage → Security → Accounts in production Order_managementDB (accounts are per-file — the dev account does not carry over)
2. Confirm the account is Active, has a permanent password set (not "must change password at next login" — the Data API cannot complete a forced password change)
3. Confirm the assigned Privilege Set has: View only access to records/layouts/value lists, and Access via FileMaker Data API enabled
4. Grant read access to the @DST file n8n layout specifically

Step 8 — Final Verification