JEISA-WEBAPP/n8n

JEISA Automation: Customer PO → Invoice → Stock

End-to-end pipeline that watches a JEI mailbox for incoming customer purchase orders, uploads them to PocketBase, creates QuickBooks invoices, emails them to customers, and decrements stock.

┌─────────────────┐    ┌─────────────────┐    ┌──────────────────┐    ┌─────────────────┐
│  Customer email │───▶│   n8N workflow  │───▶│  PocketBase      │    │  QuickBooks     │
│  (PO PDF)       │    │  01: email→PB   │    │  • customer_pos  │    │  Online         │
└─────────────────┘    └─────────────────┘    │  • parsed_lines  │    └────────┬────────┘
                                              └──────────────────┘             │
┌─────────────────┐    ┌─────────────────┐    ┌──────────────────┐             │
│  Stock list     │◀───│   n8N workflow  │◀───│  status:parsed   │◀────────────┘
│  decremented    │    │  02: invoice+   │    └──────────────────┘   (status changes
└─────────────────┘    │     stock       │                             to "parsed")
                       └─────────────────┘

The AI agent you task can do all of this through the PocketBase MCP server in .cursor/mcp.json — you ask it to "create a customer PO" or "show me all pending invoices" and it talks to the same database.

---

What's in this repo

Path	What
pocketbase/pb_migrations/1786200001_created_customer_pos.js	New collection: incoming POs from customers
pocketbase/pb_migrations/1786200002_created_customer_po_lines.js	New collection: parsed line items per PO
pocketbase/pb_migrations/1786200003_created_invoices.js	New collection: invoices we issue (with QB id)
pocketbase/pb_migrations/1786200004_created_invoice_items.js	New collection: line items per invoice
pocketbase/pb_migrations/1786200005_created_email_inbox.js	New collection: every email the workflow sees
pocketbase/pb_migrations/1786200006_created_workflow_runs.js	New collection: every n8N execution
.cursor/mcp.json	Cursor MCP config — pocketbase-mcp-bridge
.mcp.json	Same config, for Claude Desktop (~/Library/Application Support/Claude/claude_desktop_config.json)
n8n/01-customer-po-to-parsed.json	n8N workflow: email → upload PDF → parse lines
n8n/02-parsed-po-to-invoice.json	n8N workflow: parsed → QuickBooks invoice → send → decrement stock
.env.example	Template — copy to .env, fill in credentials

All 6 migrations have been validated against the local PocketBase 0.30 binary in this repo (pocketbase/pocketbase).

---

Deploy: PocketBase

1. Copy the new migration files to your PB server's pb_migrations/ dir. The next time your PB instance starts, it will apply them in order. (Tested locally: collections customer_pos, customer_po_lines, invoices, invoice_items, email_inbox, workflow_runs were created.)

2. Verify by hitting GET /api/collections with an admin token — you should see all six new collections listed.

3. No data backfill is needed — these are fresh tables.

4. Existing customers collection (pbc_108570809) is referenced by customer_pos.customer and invoices.customer. Make sure every customer has a valid email filled in — that's how the workflow matches incoming POs to customers.

---

Deploy: MCP server for the AI agent

The MCP server (pocketbase-mcp-bridge) gives an AI agent (Claude, Cursor, etc.) full access to read/write PocketBase. This is what you use to "task a agent to create purchase orders for me."

1. Open .cursor/mcp.json and fill in the REPLACE_ME_* placeholders with the values from .env (POCKETBASE_URL, POCKETBASE_ADMIN_EMAIL, POCKETBASE_ADMIN_PASSWORD).

2. Restart Cursor. The MCP server will auto-install via npx -y pocketbase-mcp-bridge and authenticate as the superuser.

3. Open a new chat and ask the agent to "list the customer_pos records with status=new." It should call list_records and return the rows.

4. To create a PO via the agent, say: "Create a customer PO for Isherwood Mining, PO number ISO-001, 10 × Drill Bit at R150 each." The agent will call create_record on the customer_pos collection with the right shape.

Available tools (55 total, see github.com/nestebe/pocketbase-mcp for the full list):

• list_records, get_record, create_record, update_record, delete_record, get_first_record
• list_collections, get_collection, create_collection, update_collection, delete_collection
• auth_with_password, list_auth_methods
• list_logs, get_settings, list_backups
• send_raw_request — call any endpoint (e.g. PB hooks, custom routes)

---

Deploy: n8N workflows

1. Install n8N (if you haven't already):

   npm install -g n8n
   # or run via Docker: docker run -it --rm -p 5678:5678 n8nio/n8n
   

2. Import the workflows: in the n8N UI, Workflows → Import from File and select:

   • n8n/01-customer-po-to-parsed.json
   • n8n/02-parsed-po-to-invoice.json

3. Create credentials for each node that has a REPLACE_ME_*:

   • IMAP credential → your orders@jeisa.co.za mailbox
   • PocketBase API credential → URL + admin email/password from .env
   • QuickBooks OAuth2 API credential → connect your QBO account

4. Activate the workflows in the n8N UI (toggle "Active" in the top right of each).

---

End-to-end test

1. Send a test email from your personal account to orders@jeisa.co.za. Subject: "PO test — please ignore." Attach a fake PDF with one or two lines like:

   AB-123  Test Widget           10   100.00   1000.00
   

2. Workflow 01 should fire within ~30s:

   • A customer_pos record is created
   • A customer_pos_line record is created
   • status becomes parsed

3. Workflow 02 should fire within ~2 minutes:

   • A QuickBooks invoice is created (sandbox)
   • An invoices record is created with status=sent
   • The stock_items quantity is decremented (only if the SKU matches a stock row — wire up more SKUs in the stock_items collection)
   • customer_pos.status becomes invoiced

4. Check workflow_runs and email_inbox in PB for the audit trail.

---

Customizing

Want to	Edit
Add fields to the email parser (e.g. delivery date)	n8n/01-…json → "Function: Parse lines from PDF text"
Switch to AI-powered PDF parsing (for scanned POs)	Add an "OpenAI" node between "Extract PDF text" and "Function: Parse lines"
Email the invoice from JEI's SMTP (not QB)	Replace "QuickBooks: Send Invoice by email" with the Send Email node
Use a different QuickBooks Item ID for SKUs	"Function: Build QB invoice payload" → ItemRef.value
Only auto-invoice certain customers	Add an auto_invoice boolean to customers, then add a filter in workflow 02
Sync QuickBooks payments back	Add a third workflow that polls QB Invoice.Status every hour and updates invoices.status

---

Important: confirm before making this live

Before going live, you'll want a human approval step between workflow 01 and 02 — the current design auto-invoices everything that looks like a PO. Add a status: "parsed_pending_review" and require a human to flip it to "parsed" to make this safer. The simplest way:

1. In 01-…json, change the final node to set status = "parsed_pending_review".
2. In 02-…json, change the filter from status = "parsed" to status = "parsed_pending_review".
3. Add a tiny approval UI in the React app (a button on the customer_pos row that PATCHes the status to "parsed").

This stops random emails from triggering invoices.