The purchase-order allocation platform, taken apart.

00The one-pager

Everything below in a single screen. If a conversation only has five minutes, this is the whole project.

01The two-minute answer

Said out loud, in this order. Problem → ownership → hardest part → performance → outcome.

02Scope & ownership

The single most common way this answer goes wrong is claiming the whole stack. Be precise about the boundary — it reads as seniority, not modesty.

Facts to have ready

03How it was broken down

The work was sequenced so that each milestone produced something observable, and so that the risky parts — the distributed boundaries — were hardened before scale testing rather than after.

04The business problem

The client pushed purchase-order inventory data to a GCS location. The system had to convert that raw PO data into store-level allocations and write final output files back to GCS. Allocation is hard because it is not an even split: it has to respect product-level inventory, store-level demand, pack structures, allocation multiples, PO type and allocation strategy simultaneously.

The shape of the data is what drives the architecture. A PO arrives with a few hundred product-level rows. Once those rows are exploded across eligible stores, the working set grows by three orders of magnitude.

300 PO rows × 600 stores      = 180,000 store-level candidate rows

10–12 POs active on one worker:
180,000 × 10                  = ~1.8M rows of intermediate processing

100 concurrent POs system-wide = 150–200M row-level computations

And the requirement was never “process the file eventually”. It was to pick up validated POs automatically and have allocation results available inside the operating SLA.

05Input model

Each client file held at most ~1,000 rows, spread across multiple purchase orders. A logical PO was identified by po_id + receipt_id, and one PO could carry 100+ rows with different product and pack attributes.

06Requirements

Functional

• Ingestion. Client pushes raw PO files to GCS.
• Validation. PO-level validation before allocation processing begins.
• Automatic picking. Validated POs are picked up with no manual intervention.
• PO type separation. Distinct handling for Receipt, Split and Prior Distro.
• Chunked dispatch. Bounded work per call — 20 POs per Process invocation.
• Preprocessing. Explode to store level, then transform, validate, filter, aggregate.
• Optimisation. Call the existing Gurobi service for constrained allocation paths.
• Post-processing. Consume solver output, transform, persist.
• Delivery. Generate the final output and upload to GCS.
• Recovery. Survive retries, crashes, duplicate tasks and lost responses.

Non-functional, and what each one forced

07High-level system design

GCS is the integration point for client files and solver input/output. Google Workflows orchestrates picking, one workflow per PO type. Cloud Tasks gives retryable async execution per PO. PostgreSQL holds durable lifecycle and idempotency state. Polars does the transformation work, DuckDB and COPY do the persistence, and the shared Gurobi service handles the constrained allocation paths.

08Lifecycle & state

Retries are only unambiguous if the database can answer “has this already happened?”. The key objects are po_master, the preprocessing idempotency table, the allocation result tables, and the finalize outbox.

allocation_code is the central business identifier. It ties PO, receipt and batch together, and it is the same value used for idempotency, tracing, solver input, solver output, post-processing and finalisation. One identifier for one logical operation, all the way through.

09Stage walkthrough

Workflows and the Init API

Three Google Workflows, one per PO type. Each generates a batch_id, calls Init to assign that batch to eligible POs, then calls Process repeatedly until the batch is exhausted.

The fix was to move identity generation to the caller, so a retry is the same logical operation.

Workflow:
  batch_id = current_timestamp_bigint
  call Init(batch_id)

Init API:
  UPDATE po_master
     SET batch_id = :batch_id
   WHERE processed_status = -100
     AND batch_id = -1
     AND po_type = :po_type;

-- Init(batch_id) is now safe to retry: the identity is stable.

Process API and Cloud Task dispatch

Process fetches the next set of unique POs for the batch — 20 at a time — assigns each an allocation_code, creates a Cloud Task per PO, updates status, and returns progress so the Workflow knows whether to loop again.

110 unique POs in one batch:

  call 1 → 20 POs → all_processed = false
  call 2 → 20 POs → all_processed = false
  ...
  call 6 → 10 POs → all_processed = true

Why 20. Bounded work per call keeps each HTTP request inside its timeout, controls the rate of Cloud Task creation, keeps progress observable, and lets the Workflow drive the loop. Dispatching an arbitrarily large batch in one call is the version of this that pages someone at 2am.

10Locks vs. durable state

A row lock only protects concurrent transactions while the transaction is open. A SELECT … FOR UPDATE with no state change is not enough: after COMMIT the lock is gone and a later request sails through. The useful pattern is lock, validate, and transition state — atomically.

In this system there was no normal concurrent Workflow execution for the same batch_id. The real problem was never two workers racing — it was retry after partial completion. That is why idempotent downstream processing mattered more than SKIP LOCKED.

11Idempotency & leases

A duplicate request into Preprocessing could be a retry of the same Cloud Task, or a genuinely separate task created for the same PO. Task ID and retry count identify the transport attempt, not the work — so the key had to be the business operation: allocation_code.

CREATE TABLE preprocessing_idempotency (
    allocation_code   TEXT PRIMARY KEY,
    status            TEXT NOT NULL,   -- IN_PROGRESS / COMPLETED / FAILED
    owner_id          TEXT,
    lease_expires_at  TIMESTAMP,
    created_at        TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at        TIMESTAMP NOT NULL DEFAULT NOW()
);

Three statements carry the whole protocol — claim, take over, complete.

-- 1. first claim: insert if absent
INSERT INTO preprocessing_idempotency (
    allocation_code, status, owner_id, lease_expires_at
)
VALUES (:allocation_code, 'IN_PROGRESS', :owner_id, NOW() + INTERVAL 'N minutes')
ON CONFLICT DO NOTHING;

-- 2. takeover: only if the previous owner looks dead
UPDATE preprocessing_idempotency
   SET owner_id = :new_owner,
       lease_expires_at = NOW() + INTERVAL 'N minutes',
       updated_at = NOW()
 WHERE allocation_code = :allocation_code
   AND status = 'IN_PROGRESS'
   AND lease_expires_at < NOW();

-- 3. complete: only if this worker still owns the operation
UPDATE preprocessing_idempotency
   SET status = 'COMPLETED',
       updated_at = NOW()
 WHERE allocation_code = :allocation_code
   AND owner_id = :owner_id;

If the row is already COMPLETED, the API returns success immediately. If it is IN_PROGRESS with a live lease, the request does not duplicate work. If the lease has expired, a retry can atomically take ownership.

Why not allocation_code + retry_count

Retry count changes on every attempt. Put it in the key and every retry becomes a different key — which is exactly not having idempotency. Retry count is a metric; it is not part of identity.

Edge cases this closes

Postgres vs. Redis for the lock

12Finalisation & the outbox

Finalize reads the persisted allocation result, generates the client-facing output, uploads it to GCS, and marks the allocation complete. Same shape of problem as before: the upload can succeed and the process can die before the DB is updated.

Post-processing transaction:
  BEGIN;
    persist allocation result;
    insert outbox event FINAL_UPLOAD(allocation_code);
  COMMIT;

Finalize worker:
  claim outbox event;
  generate deterministic file path from allocation_code;
  upload to GCS;
  mark outbox event COMPLETED;
  mark allocation COMPLETED;

13The failure matrix

This table is the project. Every row is a crash window someone wrote down before it happened in production.

14Preprocessing & why Polars

Once ownership was acquired, Preprocessing fetched the PO data, expanded it to store level, joined store and demand data, applied filters and validations, computed derived columns, and prepared solver input.

One PO goes from hundreds of product rows to hundreds of thousands of store-level rows. With several POs on a worker, that is around two million rows of intermediate data at once — and the work is transformation-heavy: filters, joins, group-bys, validations, ranking and business rules across 15–18 combinations of PO type and allocation method.

Polars fits that shape: a Rust columnar engine with vectorised operations, parallel execution and memory-efficient representation. Python describes the computation; the heavy lifting happens in native code.

15Gurobi & the optimisation

Some allocation strategies are optimisation problems, not calculations. The system has to choose the best distribution among enormously many valid ones while satisfying inventory, capacity, pack, multiple, demand and business constraints simultaneously.

Decision variable
  x[p, s, store] = units of product p / size s allocated to a store

Inventory constraint
  Σ over stores  x[p, s, store]  ≤  available_inventory[p, s]

Store capacity constraint
  Σ over products x[p, s, store] ≤  store_capacity[store]

Objective
  maximise Σ demand_score[p, s, store] × x[p, s, store]

In practice many variables must be integers — you cannot ship 3.7 units — and pack constraints plus allocation multiples push the problem from pure LP toward mixed-integer linear programming. Gurobi handles both, using presolve, simplex/barrier for the LP relaxation and branch-and-cut for the integer part.

Why not greedy

Greedy optimises the next local decision, which is fine for the simpler methods. But once packs, multiples, inventory limits, demand and capacity interact, filling the highest-demand store first can break a pack constraint or foreclose a better global distribution. A solver is the difference between a locally sensible answer and a globally good one.

1690 seconds → under 4

When the solver finished, it wrote output to GCS and called Post-processing, which read the output, transformed it into the application's allocation-result format, validated and coerced it, and persisted it. For a single PO that could be 200K rows or more — and the naive path was eating most of the SLA budget on its own.

the old path

rows = transformed_output_rows
for batch in chunks(rows, 1000):
    INSERT INTO allocation_result_main (...) VALUES (...), (...), ...;

# 200K rows ÷ 1,000 = ~200 round trips, each paying:
#   SQL + parameter construction, network latency, parse, plan, execute,
#   WAL generation, index maintenance, transaction bookkeeping,
#   plus Python loop overhead on top.

the new path

1. Read and transform the solver output.
2. Use DuckDB for in-engine cleansing and schema-aware type coercion.
3. Bulk-load into a PostgreSQL temporary table with COPY.
4. Run any set-based validation or normalisation in SQL.
5. INSERT INTO main SELECT … FROM temp — one set-based operation.
6. Commit.

The win is not a faster insert. It is a different unit of work: hundreds of application-driven statements became one bulk ingestion plus one set-based move. DuckDB earns its place before the COPY by making the data schema-aligned on arrival, so Postgres is not re-validating and re-coercing row by row. The temp table earns its place by giving a staging area where dedup and normalisation happen in SQL before the final table is touched.

17Scale & capacity validation

A single PO passing is not evidence. The dimension that matters is intermediate row expansion multiplied by concurrency — so the load tests were built around concurrent POs, not throughput of one.

Load tests sustained 100 concurrent POs and roughly 150–200M row-level computations inside the SLA.

18Technology decision record

19Alternatives seriously considered

The four decisions most likely to be challenged, and the honest version of each argument — including what the rejected option would actually have been good at.

• Kafka instead of Cloud Tasks. Kafka is the better answer for a continuous event stream with multiple consumers and replay. This was discrete, per-PO, retryable HTTP work with a natural completion — Cloud Tasks gave that with far less operational surface. Trade-off accepted: weaker replay story, which the outbox and durable state cover instead.
• Redis for locking. Genuinely faster for high-frequency short-lived locks. Rejected because the state being protected already lived in Postgres, and adding Redis would have introduced a second consistency boundary while still needing durable completion state somewhere. Trade-off: more lock traffic on the primary database, acceptable at this concurrency.
• Pandas instead of Polars. Cheaper in team familiarity and ecosystem. Rejected on the concurrency profile, not on single-PO size. Trade-off accepted: a ramp-up cost on a newer library.
• SKIP LOCKED queue semantics. The natural instinct for a picking API. Rejected because the Workflow already serialised picking per batch — the actual threat was retry-after-partial-completion, which a lock does nothing about. Solving the wrong problem elegantly is still solving the wrong problem.
• Doing more work in SQL. Would have kept computation next to the data. Rejected because the business logic branches across 15–18 paths with derived calculations — far easier to structure, unit test and extend in application code with a dataframe engine.

20Cross-functional work & estimation

Four groups touched this: the ingestion team upstream, the optimisation team who owned the solver service, PM and EM on scope and dates, and the allocation team building the workflow. Most of the coordination cost was in contracts, not code.

• Ingestion. Agreeing what “validated” means and which statuses the allocation flow is allowed to pick — the -120 / -100 split is that agreement written into the schema.
• Optimisation team. A service contract: what store-level input looks like, how allocation_code, configuration and GCS paths are passed, what the callback guarantees, and what happens on failure. This is the boundary where most integration bugs would otherwise live.
• PM. Translating “fast enough” into a number. The 15-minute SLA is what made the persistence rewrite fundable rather than a nice-to-have.
• EM. Sequencing — specifically the argument for spending a milestone on correctness hardening before scale testing, on the grounds that load-testing an incorrect system just produces confident wrong numbers.

How the estimates were built

Estimated per milestone rather than per ticket, with the two genuinely unknown areas — solver integration behaviour and persistence throughput — explicitly time-boxed as spikes before committing dates. The 15–18 processing combinations were estimated as one framework plus N strategies, so the cost curve was flat per additional method instead of linear in unknowns.

21Mentorship

The question is never “did you help people”. It is whether someone's scope grew because of you, and whether you can name it. Structure each story the same way: where they started, what the gap was, what you actually did, what they owned afterwards.

The one story to prepare properly

22Self-initiated work

Vendor-driven and reactive work does not demonstrate much. Two things here were not asked for by anyone.

Inside this project — the reliability and performance rework

Nobody filed a ticket saying “batch IDs generated server-side will strand POs on retry”. That came from sitting down and enumerating what happens if each API dies between its side effect and its response. The same applies to the persistence rewrite: the requirement was an SLA, not a technique — profiling the stages and finding that one step was spending 90 seconds was self-directed, and the DuckDB/COPY design was a proposal, not an assignment.

Outside it — mtptrace

An org-wide OpenTelemetry library, started because debugging across FastAPI microservices was guesswork and the commercial APM bill was real. Runtime module discovery, wrapt-based auto-instrumentation, AST analysis, build-time caching, configurable sampling, error-preserving span filters, tenant-aware tracing, trace–log correlation and automatic PostgreSQL query visibility — packaged so a team gets tracing by adding an import. It replaced the commercial APM with Grafana Tempo and cut trace-storage cost by roughly $21,000 a year.

That is the cleaner “innovation and initiative” answer, because it was invented rather than assigned, adopted beyond one team, and had a number attached.

23Learning curve

Three things this project actually taught, stated as principles rather than war stories.

• Identity before side effect. If the server invents the identifier after it starts mutating state, a lost response is unrecoverable. Generate it at the caller. This one rule would have prevented the original Init bug and generalises to every retryable API.
• Idempotency belongs to the business operation. Task IDs, retry counts and message IDs describe transport. Key on the thing the business considers one operation, or duplicates find a way through.
• The bottleneck is rarely the interesting code. The expensive part of this pipeline was not the optimisation or the transformations — it was writing rows to a database in the obvious way. Profile before optimising anything you find intellectually appealing.

Ramp-up cost worth naming honestly: Polars was new to the team, and reasoning about lease durations versus worst-case processing time took iteration to get right — a lease too short causes duplicate work, too long causes stuck POs.

24Outcome & metrics

25The hardest part

Two candidate answers. Lead with the first — it is a reasoning story, not a tuning story — and keep the second as the follow-up when they ask about performance.

1. Correctness across five systems that each promise only at-least-once

The difficulty was not any single mechanism; it was recognising that Workflow, Cloud Tasks, Postgres, GCS and the solver service cannot be made to agree atomically, and that every gap between a side effect and its acknowledgement is a distinct failure mode with a distinct fix. Enumerating those windows produced the failure matrix; the matrix produced the design. Caller-generated identity, a business-level idempotency key, an owner-and-lease protocol with atomic takeover, and an outbox with deterministic destinations are four different answers to four different questions — treating them as one “make it idempotent” task is how these systems end up subtly broken.

The subtlest single decision was the lease. It converts “a worker is processing” from an assertion into a claim with an expiry, which is the only version of that statement that survives a process being killed.

2. Persistence, once it turned out to be the bottleneck

Harder in a different way: the code was correct, readable and obviously slow only once measured. The fix required stepping outside “make the insert faster” and changing the unit of work entirely — bulk load into staging, then one set-based move.

26Question drill

Read the question, answer it out loud, then open the card and compare.

Scope, ownership, leadership

Technical

27What not to say

28Gaps to fill before the interview

Six facts this write-up cannot supply. Each one is a question a hiring manager will ask, and a blank is more damaging than a modest number.

• Project duration. Months from design to production, split by phase.
• Team composition. Backend, ingestion, optimisation, PM, EM, QA — and who you led.
• Your own code. Named modules, APIs and functions you personally wrote or rewrote.
• Business impact. Go-live outcome, automation rate, SLA adherence, manual effort removed, incidents avoided.
• PM/EM story. One concrete instance of turning ambiguity into scope and a technical plan.
• Mentorship story. One engineer: gap, coaching, and what they owned afterwards.