AI Inventory & Supplier Price Sync is an embedded Shopify app on React Router 7 with a Postgres database through Prisma and a BullMQ worker that runs scheduled and manual syncs. Each run goes fetch, parse, map, match, compute, anomaly check and apply. Everything before the anomaly check is read-only: it produces a list of planned before-and-after changes, and only an approved list is written to Shopify.
Key design decisions
- Real feed sources. HTTP(S) URLs with Basic, Bearer or API-key auth, FTP/FTPS via
basic-ftp, SFTP with a password or PEM key via ssh2-sftp-client, and paginated REST APIs. File patterns pick the newest matching file. Credentials are encrypted with AES-256-GCM, and private network addresses are blocked in production.
- Streaming parsers. CSV is parsed as a stream and gzip is inflated as a stream, so large files never sit in memory as text. XML, ZIP and JSON/JSON Lines are supported, along with delimiter sniffing, encodings and header-row offsets.
- A safe formula engine. Margin rules such as
(cost * 1.4) + 2 run through a tokenizer and parser in a shared core used across DEV3's apps, with no eval. Rules are scoped by supplier, collection, tag, vendor or product type and add .99/.95 rounding, minimum margin, MAP and never-below-cost floors, plus a per-run cap on price decreases.
- Currency conversion. Supplier amounts are converted with European Central Bank daily reference rates, cached and refreshed every 12 hours. Manual rates set by the merchant take precedence.
- Ten deterministic guards, then an AI second opinion. Guards check price and cost drops, price increases, zero-outs, row-count changes, unknown and missing SKUs, invalid rows, cost above retail, negative values and empty feeds. The Google Gemini API (gemini-2.5-flash) then reviews aggregated statistics and at most 15 outliers, never the full feed, and returns a verdict and a 0–100 risk score through a JSON schema. Feed text is wrapped as untrusted data.
- Hold and approve. A tripped guard, or an AI hold at or above the risk threshold, marks the run
HELD, writes nothing, emails the merchant and pauses that supplier's schedule. Approval applies exactly the stored plan.
- Safe writes. Stock goes through
inventorySetQuantities with changeFromQuantity and deterministic @idempotent keys, in batches of 250. A stale-quantity error triggers a live re-read and one retry. Prices go through productVariantsBulkUpdate per product with partial updates allowed.
Architecture
The admin UI covers a five-step supplier wizard with a sample preview, price rules with a test calculator, sync history with before-and-after changes and CSV export, and settings for buffers and thresholds. A variant index, refreshed with bulk operations and product webhooks, matches feed rows by SKU or barcode. Shared DEV3 packages provide the Shopify GraphQL client with throttling retries, the AI client, billing and the job queue. Shop data is deleted on shop/redact, and the app stores no customer data.