All work

Decision Systems

Slow Seller Decision Engine

A recommendation per style, and a spreadsheet it cannot damage

2025Internal production

At a glance

Credit

Directed by
Denandro YusufManager and supervisor · Technical & product direction · Requirements direction · Decision-logic review · QA and rollout direction

Run the demo synthetic data

Problem
Seasonal keep-or-drop calls ran on hand-assembled inputs and a brittle script.
What I did
I turned the merchandising flowchart into requirements and a fixed decision order, then reviewed the logic.
Outcome
Reproducible recommendations from live Shopify sales, unable to overwrite a formula or a colleague's edit.

Explore

The component graph, from the source repository. Follow the arrows: left to right, and down where two steps share a column. The burgundy bars are where decisions are made; the numbers on the lines are the key flows, written out under the graph.

Slow Seller Decision Engine: architectureData flows through 12 components: Shopify Admin GraphQL (External: Products, variants, orders, timezone); Shopify client (Ingest: OAuth refresh · cost throttling · retry); Google Sheets API (External: Service-account batch read/write); Sheets client (Ingest: Columns resolved by header text); Run pipeline (Service: 7 stages; reads all before writing); Eligibility + matching (Decision logic: Style + colour index, SKU fallback); Sales aggregation (Decision logic: Net units, refunds, store-timezone days); Run store (Store: Single lock, preview cache, fingerprint); API routes (Service: Zod validation · SSE stream); Decision engine (Decision logic: Pure — no I/O, no clock); Dashboard (Interface: Live stages, results, apply confirmation); Write plan + guards (Decision logic: Allowlist · formula guard · idempotency). Connections: Shopify Admin GraphQL to Shopify client; Google Sheets API to Sheets client; Shopify client to Run pipeline; Sheets client to Run pipeline; Run pipeline to Eligibility + matching; Eligibility + matching to Sales aggregation; Sales aggregation to Decision engine (units per style+colour); Decision engine to Write plan + guards (recommendation); Write plan + guards to Sheets client (minimal ranges); Sheets client to Google Sheets API (batch update); Run pipeline to Run store (stages + fingerprint); API routes to Run pipeline (preview / apply); Run store to API routes (SSE state); API routes to Dashboard.Shopify Admin GraphQL — Products, variants, orders, timezoneShopify AdminGraphQLProducts, variants,orders, timezoneShopify client — OAuth refresh · cost throttling · retryShopify clientOAuth refresh · costthrottling · retryGoogle Sheets API — Service-account batch read/writeGoogle Sheets APIService-account batchread/writeSheets client — Columns resolved by header textSheets clientColumns resolved byheader textRun pipeline — 7 stages; reads all before writingRun pipeline7 stages; reads allbefore writingEligibility + matching — Style + colour index, SKU fallbackEligibility +matchingStyle + colour index,SKU fallbackSales aggregation — Net units, refunds, store-timezone daysSales aggregationNet units, refunds,store-timezone daysRun store — Single lock, preview cache, fingerprintRun storeSingle lock, previewcache, fingerprintAPI routes — Zod validation · SSE streamAPI routesZod validation · SSEstreamDecision engine — Pure — no I/O, no clockDecision enginePure — no I/O, noclockDashboard — Live stages, results, apply confirmationDashboardLive stages, results,apply confirmationWrite plan + guards — Allowlist · formula guard · idempotencyWrite plan + guardsAllowlist · formulaguard · idempotency1234567

Key flows

  1. Sales aggregation to Decision engine: units per style+colour
  2. Decision engine to Write plan + guards: recommendation
  3. Write plan + guards to Sheets client: minimal ranges
  4. Sheets client to Google Sheets API: batch update
  5. Run pipeline to Run store: stages + fingerprint
  6. API routes to Run pipeline: preview / apply
  7. Run store to API routes: SSE state

Go deeper

The full account

Problem
Merchandisers decided each season what to keep, sell out, mark down or drop — but the inputs were assembled by hand and by a brittle Apps Script. Four sales columns carried end dates from four different days, status grouping was a hard-coded list that broke whenever someone typed a new wording, and two engine inputs were placeholders rather than real data.
What was built
A Next.js app that runs a seven-stage sync — read the sheet, load Shopify products and orders, match style and colour, aggregate two rolling windows, run the engine, write back — streaming progress over Server-Sent Events.
Role
Manager and supervisor, not the primary engineer — see the credits for who built it. Turned the merchandising team's flowchart into system requirements, set the decision order and the rule that thresholds are numbers nobody edits casually, reviewed the decision logic and the write plan before release, and directed QA and the rollout to merchandising.
What changed
The same decision, now sourced from live Shopify data, reproducible, and structurally unable to overwrite a formula, a header, or a colleague's edit.

Primary engineering by Kirei Kharisma Handayani. I managed and supervised the project, translated merchandising requirements into system direction, reviewed the decision logic and implementation, and directed QA and rollout.

Context

The spreadsheet was the system of record and was going to stay that way — that constraint was not negotiable, and designing around it rather than against it is most of the work.

The real risk was not a wrong recommendation. It was a script with write access to a live merchandising sheet that 1,900 product rows and several people depend on. Anyone re-running it risked destroying formulas or a colleague's work.

Architecture and the system

Read everything before writing anything. Two integration layers feed a pipeline whose stages are all pure until the final write plan, which is guarded by a column allowlist, a source fingerprint and per-cell idempotency.

A pure engine with nothing to break

The decision engine is a pure function with no I/O and no clock. Status group, two sales figures, stock, samples, release date, next-season availability and inventory in; an 'ACTION — Reason.' sentence out.

Because it has no dependencies, it is fully testable, and a threshold experiment can run without touching the sheet at all.

Guards between a decision and a cell

Columns are resolved by header text at run time rather than by position, so inserting a column upstream does not silently corrupt the write.

Every run builds a pure write plan first, then asserts that plan touches only an allowlisted set of columns and never the header row. A SHA-256 fingerprint of every source cell, a single in-process run lock, and per-cell idempotency checks sit between the plan and the API call.

A second run over unchanged data writes nothing at all. A Shopify failure mid-run leaves the spreadsheet untouched rather than half-updated. An unmatched row is flagged for human review rather than written as zero sales.

A Shopify client that respects the bucket

A hand-written Admin GraphQL client mints and refreshes its own OAuth client-credentials token — 24-hour lifetime, refreshed ten minutes early, re-minted and retried on a mid-run 401.

It paces itself against Shopify's calculated query cost and leaky bucket with exponential backoff, jitter and Retry-After handling, rather than guessing at a fixed delay.

Experiments separated from the agreed rules

A standard run applies itself. A custom-threshold run stops at a results table and requires an explicit confirmation dialog listing every setting that differs from the agreed rule set — so an experiment can never be mistaken for policy.

What was hard

Writing into a spreadsheet people are using

The sheet is live, shared and full of formulas. The answer was to make the write plan a value the code can inspect before it executes — allowlist the columns, forbid the header row, fingerprint the source, and check each cell for idempotency. A destructive write becomes something you can assert against in a test rather than something you hope does not happen.

Replacing two placeholders with real data

The old script used a sample count as a stand-in for 'quantity that can be made', and a proxy date column for 'releases again next season'. Both now read real sources — live inventory, and the next season's tab. Two placeholders quietly wrong for a long time is a good illustration of why a system beats a script.

Documenting the weakness at the top of the README

The app has no sign-in; the deployment boundary is the entire access control. That is stated prominently at the top of the README with a comparison table of four ways to add one, rather than buried. Leading with your system's biggest limitation is what makes the rest of the document trustworthy.

AI and automation

Automation

Replaces a Google Apps Script workflow and the manual steps around it: pulling Shopify unit sales for two rolling windows, keying them to the right row by style and colour, hand-typing the date range each sales column covers, and applying the slow-seller rules style by style. A one-off migration endpoint clears the legacy column the old script wrote, showing the affected cell count before confirmation.

Direction and delivery

My part was requirements and review: I translated what the merchandisers needed into system direction, reviewed the decision logic and the implementation, and directed QA and rollout. Kirei Kharisma Handayani engineered it as primary engineer. Her 1,032-line README documents the platform setup step by step, the service-account sharing step and what to ask a Workspace admin when external sharing is blocked, a troubleshooting matrix mapping every user-facing error to its fix, and a 'where to change X' table pointing every tunable to the file that owns it. Design decisions are recorded as rationale in code — 'do not reorder these thresholds without an explicit instruction — the wording is safe to edit, the numbers are not'.

Size signals

Source files
66
Lines of TypeScript
~9,400
Tests
361
Test files
14
Pipeline stages
7
README lines
1,032
Catalogue rows handled
~1,900

Counted from the source repository at its first version, before the 2.0. No impact metric is claimed that the source does not prove.

What I would tell the next person

  • When a spreadsheet is the system of record, design around that constraint. Resolving columns by header text costs an hour and survives every future column insert.
  • Make the destructive step a value you can inspect. A write plan you can assert against is safer than a write you have to trust.
  • Separate experiments from policy in the UI, not just in the docs.

Technologies and access

  • TypeScript
  • Next.js 15
  • React 19
  • Tailwind CSS 3
  • Radix UI
  • Zod
  • Shopify Admin GraphQL
  • Google Sheets API v4
  • Server-Sent Events
  • Vitest

Integrations

  • Shopify Admin GraphQL (OAuth client-credentials, pinned API version)
  • Google Sheets API v4 (service-account JWT)

Internal tool behind a network boundary. Not publicly reachable.