Skip to content
<Hyun/>
All projects

SB Trading

Production · Purchasing workflow

From sales files to an approved order.

Edit the order sheet and see how quantities, versioned approval and vendor output stay connected.

Interactive explanation · not the live service

Loading interactive explanation…

Watch the original recordingsWeb · Recording notes included

Use the left and right arrow keys to switch slides.

Web

Recorded from a local build with synthetic products, vendors, stores and accounts.

A sample buyer starts an automatic proposal, reviews store targets while it calculates, then edits a quantity, approves and creates the vendor Excel.

1 / 6
Explore the architectureSystem diagrams, design decisions and detailed flows

System overview

  1. POS Excel exportsmaster + weekly sales
    Step 1, upload
    React SPAbuyers, approvers, stores
    RENDER · ONE DOCKER SERVICEFastAPI APIauth, roles, versions
    uploads + sha256
    RENDER · ONE DOCKER SERVICEPersistent diskuploads, Excel exports
  2. FastAPI API
    enqueue, edits
    RENDER · ONE DOCKER SERVICESQLite (WAL)jobs, orders, audit log
  3. RENDER · ONE DOCKER SERVICECalculation workersseparate processes
    Step 2, claim, lease
    SQLite (WAL)
  4. Persistent disk
    inputs
    Calculation workers
    Step 3, vendor xlsx
    Persistent disk

Other connections

  • FastAPI APItoBigCommerce APIorders, refunds
  • Calculation workerstoBigCommerce APIcarts
  • Calculation workerstoGmail
A React SPA calls a FastAPI service that queues work in SQLite. Separate worker processes claim the jobs, read and write files on a persistent disk, and call BigCommerce and Gmail, all in one Docker service on Render. The purple path: (1) uploads are stored with a SHA-256 fingerprint, (2) a worker claims the queued calculation, (3) workers write the vendor Excel files.
View the diagram as text

Parts

  • POS Excel exports (master + weekly sales): External service
  • React SPA (buyers, approvers, stores): Client / UI
  • FastAPI API (auth, roles, versions): Owned service
  • SQLite (WAL) (jobs, orders, audit log): Data store / broker
  • Persistent disk (uploads, Excel exports): Data store / broker
  • Calculation workers (separate processes): Background job
  • BigCommerce API (orders, refunds, carts): External service
  • Gmail (report mail): External service

Connections

  • Step 1: POS Excel exports → React SPA: upload
  • React SPA → FastAPI API
  • FastAPI API → Persistent disk: uploads + sha256
  • FastAPI API → SQLite (WAL): enqueue, edits
  • Step 2: Calculation workers → SQLite (WAL): claim, lease
  • Persistent disk → Calculation workers: inputs
  • Step 3: Calculation workers → Persistent disk: vendor xlsx
  • FastAPI API → BigCommerce API: orders, refunds
  • Calculation workers → BigCommerce API: carts
  • Calculation workers → Gmail

Job and worker lifecycle

  1. Create requestPOST → 202
    request id
    queuedrules + input digests
    atomic claim
    WORKER PROCESSrunningone worker owns it
    commit
    completedorder or xlsx + audit

Other connections

  • Supervisortorunningspawn
  • Heartbeattorunningrenew
  • runningtofailederror / expiry
  • failedtoExplicit retryuser retries
  • Explicit retrytoqueuednew job
A background job is created idempotently, claimed by exactly one worker, kept alive by a lease, and either completed or failed visibly. The purple path is a job that succeeds. Retries happen only when a person asks for one.
View the diagram as text

Parts

  • Create request (POST → 202): Client / UI
  • Supervisor (spawns, respawns): Owned service
  • queued (rules + input digests): Data store / broker
  • Heartbeat (lease +90 s / 10 s): Background job
  • running (one worker owns it): Background job
  • completed (order or xlsx + audit): Data store / broker
  • failed (error or lease expired): Data store / broker
  • Explicit retry (new job, same snapshot): Client / UI

Connections

  • Create request → queued: request id
  • queued → running: atomic claim
  • running → completed: commit
  • Supervisor → running: spawn
  • Heartbeat → running: renew
  • running → failed: error / expiry
  • failed → Explicit retry: user retries
  • Explicit retry → queued: new job

From uploaded inputs to an approved vendor Excel

  1. Upload inputs

    A buyer uploads the master workbook and picks the weekly POS exports from a folder. The API streams each file to disk with size and decompression limits, checks the required sheet and columns, and stores its SHA-256 digest with an audit entry.

  2. Queue a calculation

    The browser posts a job with a request ID it generated, and that ID becomes the job ID, so a double click returns the same job. The API copies the current rules and the input digests into the job and answers 202 right away.

  3. Worker claims and computes

    A worker claims the oldest queued job and holds a lease that a heartbeat renews. It re-verifies each input's digest, recalculates the master from the latest POS files and runs the rules engine, committing the job and the new draft order in one transaction while the UI polls and other screens stay usable.

  4. Review with history

    Buyers adjust quantities. Each save names the order version it expects, so a concurrent edit gets a 409 and a comparison screen, and the audit log records every changed line as old and new values.

  5. Role-based approval

    A buyer submits the reviewed order, and only an approver, admin or developer can approve it, for the exact version they reviewed. Store allocations are approved per store.

  6. Export the vendor Excel

    Exporting queues another job tied to the order version. The worker fills the vendor's template, neutralizes text that looks like a formula and writes the file atomically, then checks in the same transaction that the order is still approved at that version before marking it exported.

The project & my contribution

Problem
Purchasing for one warehouse and four stores ran on a monthly Excel workbook of ordering formulas.
Solution
A web workspace that turns the workbook rules and weekly POS exports into replenishment orders, store allocations and vendor order sheets, with review and approval.
Result
Replaced the monthly Excel workflow, with output checked against the workbook it replaced.
  • 83,412 formula cells reproduced
  • 5,949 rows, 100% match
  • 695 pytest + 79 Playwright tests
Explore featuresLive site(opens in a new tab)

Live site: Staff sign-in required; the interface is in Korean.

The source code is private. I'm happy to walk through it on request.

How it's built

  • React + TypeScript front end, FastAPI and SQLite, shipped as one Docker service on Render
  • Calculations run in separate workers that claim jobs with a renewable 90-second lease
  • A crashed job is marked failed and retried only as a new job, so orders are never duplicated

Highlights

  • Each job snapshots its rules and input file hashes, so past results stay explainable
  • Optimistic versioning on every edit and approval; conflicts get a 409 and changes are audited
  • Recalculates the workbook's formulas from fresh POS files and shows differences from Excel
  • BigCommerce lookups and refunds; external writes are never retried automatically