Data Flows

This page shows how data moves through the system. It names the main integrations, the scheduled jobs, and the calculations behind the reports.

The main players

Five systems pass data to each other:

• ShipHero: the fulfillment system of record. It owns orders, shipments, SKUs, and purchase orders.
• Supabase: the app's PostgreSQL database. It stores every record the portal reads.
• N8N: a workflow tool. It runs jobs that create accounts, invite users, and re-process purchase orders.
• Cloudflare Worker: an edge service. It builds reports and sends email.
• The portal: this Next.js web app.

Data coming in from ShipHero

Shipments

ShipHero shipments webhook → Cloudflare worker → Supabase

ShipHero pushes each shipment to the worker. The worker stores it as a Shipments row and flags shipments whose cost passes their threshold. Raw payloads that still need processing wait in shipments_queue.

The worker that receives these webhooks is the ShipHero Handler. Its full processing pipeline, quantity verification, and zone calculation are documented in that section.

An N8N workflow also stamps each shipment's shipping zone. It calls the app's zone calculator through a protected endpoint.

Purchase order updates

ShipHero purchase order update → N8N → Supabase

When a purchase order changes in ShipHero, N8N updates Supabase. This path also flags line items that arrive over or under the ordered quantity.

If a line item carries a SKU code nobody has classified yet, the code goes to the review queue and the line item is saved with no SKU attached. It counts toward nothing until someone classifies the code. Receiving and closing the PO are unaffected. See the Purchase Order Update Handler.

New SKUs

New SKU in ShipHero → SKU sync → review queue → portal
PO line item with an unclassified code → review queue → portal

A daily cron pulls new SKUs from ShipHero at 07:00 UTC. You can also sync one account on demand. A purchase order importing an unclassified code feeds the same queue.

New SKUs land in a review queue (sku_pending_queue), not the live tables. A person classifies each one before it becomes real. Classifying a code also links any purchase order line items that were held waiting on it. Each run and its per-SKU results are recorded in the SKU Sync tables. Read the SKU Sync guide for the steps.

Data going out to ShipHero

Draft purchase orders

Portal → draft PO → push to ShipHero (GraphQL)

You build a draft purchase order in the portal. Push to ShipHero sends a GraphQL request that creates the real order.

Each call signs with the account's own ShipHero access token.

Retrigger and re-import

Portal → purchase_order_updates_queue → N8N → Supabase

Retrigger and re-import add a job to a queue table (purchase_order_updates_queue). An N8N workflow reads the queue and re-processes the order.

Scheduled jobs

Three jobs run on a schedule:

• SKU sync runs daily at 07:00 UTC. It pulls new SKUs into the review queue.
• Shipment reconcile runs daily at 12:00 UTC. It checks that the portal holds every shipment ShipHero shipped.
• On-hand quantity sync: N8N updates inventory quantities on a schedule.

The shipment reconcile job posts a PDF report to Slack. It flags missing shipments, duplicates, quantity drift, and voided labels.

Account and user jobs (N8N)

Portal → create account → N8N webhook → Supabase
Portal → invite user → N8N webhook → email

Creating an account sends its details to an N8N webhook. The webhook builds the account, roles, and invites.

Inviting a new user also runs through N8N. The webhook sends the invitation email.

Calculations behind the reports

The portal computes several figures in code, not in the database.

Live inbound

The Replen Report, Inventory Snapshot, and Forecast share one inbound engine. It counts stock on the way in from pending purchase orders.

It reads each pending line item and subtracts what already arrived. Case line items multiply by their units per case to reach base units.

Reorder point

The database computes each SKU's reorder point. It uses the five settings you control on the Reorder Point Settings screen, stored on the Inventory row.

The app then overrides inbound with the live figure. It recomputes the amount to reorder and rounds it up to the SKU's order multiple.

Shipping zones

The zone calculator measures the distance between the warehouse ZIP and the customer ZIP. It maps that distance to a zone from 1 to 8 (via the calculate_zone_from_distance function), and stores the result in the shipping_zone column on Shipments.

Zone 9 covers US territories and military addresses.

Reports and the audit tool

The Cloudflare worker builds CSV, PDF, and spreadsheet reports. It emails them through Resend.

A separate audit tool runs at audit.nationalcommerce.com. It produces an emailed report.

Where the system runs

• Hosting: Vercel runs the web app.
• Code: GitHub stores the code.
• Database: Supabase runs PostgreSQL.
• Edge jobs: Cloudflare Workers run the report and email jobs.