04Internal Tools
Removing the developer from a routine data request
The business is made-to-order, but sometimes has matching finished stock sitting in spreadsheets maintained outside Shopify. Planners were manually cross-checking each order line against three separate Google Sheets.
The result was a developer-in-the-loop process for a routine request — the kind of dependency that looks small until you count how often it happens.
A stateless container behind Google sign-in. Identity is terminated at the proxy, the app holds no durable storage beyond a short-lived preview cache, and every external dependency is allowed to fail without taking the export with it.
A single fully-nested query for orders, line items and fulfillment data exceeds Shopify's 1000-point per-query cost cap. So the fetch runs in two passes: orders plus line items, then per-order fulfillment.
The HTTP client reads the throttle status from the cost extension on every response and paces against the real bucket, rather than guessing at a safe delay.
A dedicated resolver assigns each unit to a location using the actual fulfillment location first, then the assigned fulfillment-order location — never inventory levels, which answer a different question.
A split line item fans out into multiple rows, each tagged with how the warehouse was determined: fulfilled, assigned, or Unknown. Planners can see how much to trust each row instead of receiving a confident guess.
A separate module reads three stock workbooks via a read-only service account and indexes them on normalized style, colour and size — deliberately never on style ID, because season prefixes differ between systems.
It emits Yes / Partial / No / Review per line, and fails soft: if the sheets are unreachable, the export still ships without the stock column rather than failing entirely.
Exports are generated in memory. Nothing customer-related is written to disk or to a database; structured logs carry order references, counts and statuses only. The GraphQL documents deliberately select no customer name, email, address, phone or payment field.
Shopify exposes the actual fulfillment location, the assigned fulfillment-order location, and inventory levels — and they disagree. Picking an order of preference and then shipping the provenance alongside the answer turned an ambiguous field lookup into information a planner can reason about.
The stock workbooks and Shopify do not agree on style IDs, because season prefixes differ. Indexing on normalized style, colour and size instead is less elegant and considerably more correct.
The deployment runbook records the incident and the grep freshness check added to prevent it. Writing down the pitfall that actually happened, in the runbook, is worth more than a process document nobody reads.
Replaces a developer running local scripts on request, and the manual cross-check of each order line against three stock spreadsheets. Line-item fan-out, warehouse attribution, split-shipment expansion, timezone conversion and the stock recommendation are all computed rather than hand-assembled.
Eleven numbered deliverable documents covering requirements analysis, architecture, data mapping, warehouse logic, GraphQL cost math, security controls, deployment, a deployment checklist, a non-technical end-user guide, and a prioritized roadmap that names the infrastructure each idea would add. The README carries an explicit 'caveats to verify before production' list. The operational timezone was deliberately reversed at one point, and the reason is recorded.
Objective size signals taken from the repository. No impact metrics are claimed that the source does not prove.
Internal service behind company Google sign-in.