Data Flows
How data moves between ShipHero, N8N, the Cloudflare worker, and Supabase.
This page shows how data moves through the system. It names the main integrations, the scheduled jobs, and the calculations behind the reports.
Where the data lands
This page follows the data in motion. To see the tables it lands in, with columns, keys, and the triggers that keep derived values in sync, read the Database reference. The Glossary links each domain term to its table.
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 → SupabaseShipHero 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 → SupabaseWhen 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 → portalA 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
Replenishment requests
Replen Report → request (draft PO tables) → brand's magic link → draft POsNC snapshots one account's Replen Report rows into a replenishment request and can adjust each line's Requested or remove lines before the brand answers. The brand answers through a magic link with no login, working from Requested, and NC turns the answer into one Case SKU draft per warehouse. Those drafts then go to ShipHero like any other, below.
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 → SupabaseRetrigger 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
Four jobs run on a schedule:
- SKU sync runs daily at 07:00 UTC. It pulls new SKUs into the review queue. If an account fails, the others still sync and the internal Slack channel gets a message.
- 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.
- Sign-in history cleanup runs daily at 03:15 UTC in the database. It deletes auth_events rows older than 12 months.
The shipment reconcile job posts a PDF report to Slack. It flags missing shipments, duplicates, and quantity drift, comparing only the labels ShipHero marks as valid.
Account and user jobs (N8N)
Portal → create account → N8N webhook → Supabase
Portal → invite user → N8N webhook → emailCreating 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 creates the user and sends the invitation email, which tells them to sign in with a code. See Invite Users to Client Portal.
Sign-in and new-sign-in emails
Portal login → Supabase Auth → email with code (via Resend)
Sign-in → auth.sessions → auth_events → 5s later → /api/security/new-device → Resend → userThe login page asks Supabase Auth for a code, and Supabase emails it through Resend. Entering the code creates a session.
Each new session writes a sign_in row to
auth_events. If the account has
never signed in from that internet address, a database trigger waits 5 seconds
(so middleware can add the city and country), then posts the sign-in to the web
app. The app emails the user the device, location, and time. See
Automation.
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, on production and on staging.
- Code: GitHub stores the code.
- Database: Supabase runs PostgreSQL.
- Edge jobs: Cloudflare Workers run the report and email jobs.