System Design Interview Experience : Designing a Medicine Delivery Platform (SDE-I)

I recently sat through a system design round at an early-stage startup and I'm still waiting to hear back. I could wait for the result before writing this, but honestly, the conversation is fresh right now and a few of the questions caught me off guard in a good way. So here's the whole thing, as close to how it actually went as I can remember.

If you're preparing for a backend or system design interview, I think you'll find something useful here. And if you've already had a round like this, I'd love to hear how yours went.

Last reviewed: October 25, 2026. The SQL in this post uses ON CONFLICT and SKIP LOCKED, which need PostgreSQL 9.5 or newer, plus PostGIS for the nearby-pharmacy search.

Quick details

• Role: Founding Engineer at an early-stage startup (the discussion was at SDE-I level)
• Round: System design
• Pay: Equity-based. The salary part isn't finalized yet.
• Result: Still waiting

What Surprised Me Most

I walked in expecting to draw boxes and arrows for an hour. That's not what happened.

The interviewer barely cared about the boxes. Again and again, the question was some version of: "Okay, but what happens when this part fails?" We spent most of the hour on state, concurrency and edge cases. If you take one thing from this post, let it be that: how the boxes behave when things go wrong matters more than how many boxes you draw.

The Question

Design a scalable medicine delivery platform.

They gave me the flow up front:

Patient uploads        AI reads the         User adds          System finds nearby
a prescription   -->   prescription   -->   medicines    -->   pharmacies that have
                       and lists the        to cart            those medicines in stock
                       medicines                                        |
                                                                        v
                       Order goes to   <--   User pays      <-----------+
                       the pharmacy

Before drawing anything, I took a few minutes to agree on requirements. We settled on these:

• Scale: thousands of users at the same time, with more cities later
• Speed: finding nearby pharmacies should feel instant
• Correctness: inventory and payments must be strictly consistent, with no overselling and no double charging
• Uptime: the ordering flow should stay available

Then I said something out loud that ended up helping me for the rest of the interview: "correctness for money and stock" and "always available" can pull against each other, so I'd treat different parts of the system differently. That one sentence gave me a reason for almost every choice that followed.

The Big Picture

I split the system into small services behind an API Gateway:

              +-------------+
  Client ---> | API Gateway |   (auth + rate limiting)
              +------+------+
                     |
     +-------+-------+-------+--------+
     |       |       |       |        |
   User    Cart    Order  Payment    Geo
  Service Service Service Service  Service
             |       |       |        |
           Redis  Postgres Postgres  Postgres + PostGIS

Nothing fancy. The interesting parts came when the interviewer started poking at it.

Question 1: "How would you scale this for multiple cities?"

What I told them: I kept the application servers stateless. That's a simple idea: a server doesn't remember anything about a user between requests. Whatever needs remembering lives in Redis or the database. Because of that, I can run many copies of a service behind a load balancer and add more when traffic spikes.

I chose horizontal scaling (more machines) over vertical scaling (one bigger machine). A bigger machine hits a ceiling eventually and if it goes down, everything goes down with it.

For multiple cities, I said I'd split data by city, since someone in one city never needs pharmacies from another. I'll be honest: I gave this as a direction and didn't go deep, because the interviewer moved on. I wish I'd said more. More on that at the end.

Question 2: "How do you find pharmacies within 5 km without scanning the whole database?"

What I told them: My first point was that I wouldn't calculate the distance to every pharmacy on every search. With 50,000 pharmacies, that's a lot of wasted math for one request.

Instead, I'd use a spatial index and I offered two options:

• PostGIS, a PostgreSQL extension that understands locations and can index them
• Geohashing, which turns a latitude and longitude into a short string. Places close to each other usually share the same starting characters.

With a spatial index, the database narrows things down to a small area first, then checks exact distance for just those few candidates. That's what makes millisecond responses possible. In PostGIS, it looks roughly like this:

-- location is stored as geography(Point, 4326)
CREATE INDEX idx_pharmacies_location
    ON pharmacies USING GIST (location);

SELECT p.id, p.name
FROM pharmacies p
JOIN pharmacy_inventory i ON i.pharmacy_id = p.id
WHERE p.is_active = true
  AND i.medicine_id = :medicine_id
  AND i.quantity >= :qty
  AND ST_DWithin(
        p.location,
        ST_SetSRID(ST_MakePoint(:lng, :lat), 4326)::geography,
        5000   -- meters
      );

The PostGIS docs say that for the geography type, ST_DWithin takes its distance in meters and that it's designed for index-assisted searches like this one.

A small catch if you go the geohash route: two points can be very close in real life but sit on opposite sides of a cell border, so they end up with different prefixes. You have to query the neighboring cells too. PostGIS handles that for you, but it ties you to Postgres.

"Why not MongoDB?" This flowed straight into the database question. I said I'd use PostgreSQL for orders and payments, because this is money and medical stock and I want ACID transactions. In plain words, that means either the whole operation happens (reduce stock, create order, record payment) or none of it does.

I'd use Redis for carts and sessions, since they change constantly and losing one isn't a disaster. I wasn't saying NoSQL is bad. I was saying that for these two services, strong guarantees are worth more than flexibility.

Question 3: "User A orders, we reserve the stock, but their payment hangs. User B wants the same medicine. How do you prevent deadlocks?"

This was my favorite part of the interview.

First, a small note I made to the interviewer. This isn't really a deadlock in the textbook sense, where two transactions wait on each other forever. It's a resource being held for too long. Saying that out loud made my answer much cleaner.

What I told them: We can never hold stock forever. So when User A goes to payment:

1. The order moves to PENDING and the stock is deducted (reserved).
2. The order gets a strict expiry of 10 minutes.
3. If the payment isn't confirmed in that time, the order becomes CANCELLED and the stock goes back, so User B can buy it.

            +---------+    payment ok     +------+
 create --> | PENDING | ----------------> | PAID |
            +----+----+                   +------+
                 |
       timeout or payment failed
                 v
          +-----------+
          | CANCELLED |  --> stock goes back to inventory
          +-----------+

The stock deduction has to be a single atomic statement, so two people can't grab the last strip at the same moment:

UPDATE pharmacy_inventory
SET quantity = quantity - :qty
WHERE pharmacy_id = :pharmacy_id
  AND medicine_id = :medicine_id
  AND quantity >= :qty;
-- 0 rows affected means not enough stock. Reject the order.

The quantity >= :qty check inside the same statement is the whole trick. The database checks and updates together, so overselling can't sneak in between the two steps.

For triggering the 10-minute expiry, I mentioned a Redis TTL event or a cron job. One more thing worth saying: I brought up this inventory-timeout problem myself, before the interviewer asked about it. That felt like the moment the interview turned from a quiz into a conversation.

What I'd say now about the expiry

If I were answering again, I'd make a scheduled cleanup job the main mechanism, not a Redis TTL event. Redis keyspace notifications travel over Pub/Sub, which the Redis docs describe as "fire and forget": if your listener disconnects, the events sent while it was away are gone. The docs also say an expired event is generated when Redis actually deletes the key, not at the exact moment the TTL hits zero. So it can't be your only safety net. A repeating query is boring, but it never forgets.

There's another catch. The cleanup job and a late payment webhook can reach the same order at the same moment. So every status change has to be conditional (WHERE status = 'PENDING') and the cleanup job should skip rows another transaction is already handling:

BEGIN;

WITH expired AS (
  SELECT id FROM orders
  WHERE status = 'PENDING' AND expires_at < now()
  ORDER BY expires_at
  LIMIT 100
  FOR UPDATE SKIP LOCKED          -- skip orders a webhook is handling right now
), cancelled AS (
  UPDATE orders o
  SET status = 'CANCELLED'
  FROM expired e
  WHERE o.id = e.id AND o.status = 'PENDING'   -- conditional transition
  RETURNING o.id
)
-- give the stock back for the orders we just cancelled
-- (aggregate first: several cancelled orders can hit the same inventory row)
UPDATE pharmacy_inventory inv
SET quantity = inv.quantity + r.qty
FROM (
  SELECT oi.pharmacy_id, oi.medicine_id, SUM(oi.qty) AS qty
  FROM order_items oi
  JOIN cancelled c ON c.id = oi.order_id
  GROUP BY oi.pharmacy_id, oi.medicine_id
) r
WHERE inv.pharmacy_id = r.pharmacy_id
  AND inv.medicine_id = r.medicine_id;

COMMIT;

That SUM ... GROUP BY step looks like a small detail, but it's the kind of bug that quietly costs you money. In an UPDATE ... FROM, if several joined rows match one target row, Postgres applies only one of them. Picture two expired orders in the same batch that reserved 3 and 2 from the same inventory row. Without aggregating, you'd get back only 3 (or 2) instead of 5 and the stock would leak a little every time. A unique key on order_items doesn't prevent this, because those two items belong to different orders. The join has to be aggregated per inventory row.

And if a webhook is holding the lock on an order, the cleanup job simply skips it this round and picks it up on the next run if it's still PENDING and expired.

Question 4: "What if the payment fails multiple times or the user clicks Pay twice?"

What I told them: Two ideas.

First, keep orders and payments separate. One order can have many payment attempts, so it's a one-to-many relationship. Every attempt, including failed ones, gets its own row. That gives you a proper history and support teams can see exactly what happened.

Second, make the operation idempotent. That's a fancy word for "doing it twice has the same effect as doing it once." In the room, I gave the idempotency-key idea and talked about the unique constraint on the gateway transaction id, so a repeated webhook couldn't process twice.

That's about where I stopped in the interview. Everything from here to the end of this question is the fuller design I'd give now, after thinking it through again. I'm keeping it in this post because this is the part that's easiest to get wrong.

Two different duplicate problems

They look similar, but they need different protection:

1. The user double-clicks Pay. Two requests reach your server. Without protection, you could create two payment attempts and the gateway could create two different transactions.
2. The gateway sends the same webhook twice. Stripe's docs say an endpoint might receive the same event more than once and Razorpay's docs say the same and suggest using the x-razorpay-event-id header to spot duplicates.

A unique constraint on gateway_txn_id only helps with the second problem. For the first, the constraint has to exist before the gateway is called, so it needs to live on something we create ourselves. Here's the schema I'd use:

CREATE TABLE payments (
  id               BIGSERIAL PRIMARY KEY,
  order_id         BIGINT      NOT NULL REFERENCES orders(id),
  idempotency_key  TEXT        NOT NULL,
  gateway_txn_id   TEXT,                    -- NULL until the gateway answers
  status           TEXT        NOT NULL,    -- INITIATED | SUCCESS | FAILED
  claimed_at       TIMESTAMPTZ,             -- used by the reconciler (see below)
  created_at       TIMESTAMPTZ NOT NULL DEFAULT now(),
  CONSTRAINT uq_payments_idempotency_key UNIQUE (idempotency_key),
  CONSTRAINT uq_payments_gateway_txn     UNIQUE (gateway_txn_id)
);

-- At most ONE in-flight attempt per order
CREATE UNIQUE INDEX uq_one_active_payment_per_order
  ON payments (order_id)
  WHERE status = 'INITIATED';

-- Webhook de-duplication (event id from the gateway)
CREATE TABLE webhook_events (
  event_id     TEXT PRIMARY KEY,
  received_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

In Postgres, a UNIQUE column allows many NULL values, so gateway_txn_id can stay empty until the gateway responds. The partial unique index is what blocks a second concurrent attempt for the same order, while still allowing a fresh attempt after the previous one moves to FAILED.

Starting a payment (this handles double clicks)

INSERT INTO payments (order_id, idempotency_key, status)
VALUES (:order_id, :idempotency_key, 'INITIATED')
ON CONFLICT (idempotency_key) DO NOTHING
RETURNING id;
-- A row came back: first request. Go ahead and call the gateway.
-- No row: same click arrived twice. Return the existing attempt, don't call the gateway again.

The backend generates the idempotency key when checkout starts and hands it to the client. A double click reuses the same key. A genuine retry after a failure starts a new checkout and gets a new key. If a different key shows up while an attempt is still INITIATED, the partial unique index rejects it. And if your gateway supports idempotent requests, pass the key along too. Stripe, for example, accepts an Idempotency-Key header on POST requests.

Handling the webhook (this handles duplicates)

BEGIN;

INSERT INTO webhook_events (event_id) VALUES (:event_id)
ON CONFLICT (event_id) DO NOTHING;
-- 0 rows inserted = already processed. COMMIT and return 200.

-- ...otherwise apply the effect (update payment, confirm order) in this same transaction.

COMMIT;

Recording the event id and applying the effect in the same transaction matters. If the process crashes halfway, both roll back together, so a retry is safe.

My rule here is simple: don't trust the frontend to stop double clicks. The last line of defense is the database, meaning the unique idempotency_key, the one-active-payment index and the unique event id.

What if an attempt gets stuck in INITIATED?

This one is a side effect of the design and I'd rather you hear it from me than discover it in production.

If the server crashes right after the insert or the gateway's response gets lost, that row stays INITIATED and blocks every new attempt on the order. The user is now stuck with an order they can't pay for.

The fix is a reconciler: a small scheduled job that finds stale attempts and asks the gateway what really happened. One thing to be careful about is how it claims rows. If you open a transaction, lock the rows and then call the gateway for each one, incoming webhooks for those rows have to wait. That's exactly the long-held-lock problem we avoided earlier. So claim the rows with a short "lease" instead, commit and only then call the gateway:

-- Step 1: claim a batch with a short lease, then commit right away
UPDATE payments SET claimed_at = now()
WHERE id IN (
  SELECT id FROM payments
  WHERE status = 'INITIATED'
    AND created_at < now() - interval '15 minutes'
    AND (claimed_at IS NULL OR claimed_at < now() - interval '5 minutes')
  ORDER BY created_at
  LIMIT 50
  FOR UPDATE SKIP LOCKED
)
RETURNING id, order_id, idempotency_key, gateway_txn_id;

Step 2 happens outside any transaction: for each claimed row, the job calls the gateway's status API. Razorpay's docs recommend an API fetch when a webhook hasn't arrived within the time your business can wait. If gateway_txn_id is still empty because the response was lost, look the payment up by your own order reference. With Stripe, you can also repeat the original create request with the same Idempotency-Key, which returns the saved result. Keys can be removed after 24 hours, so reconcile inside that window.

Step 3 is a short transaction that applies what the gateway said:

• Gateway says paid: run the same locked confirm transaction as the late-payment path (covered in the follow-up questions below), so it's safe even if the webhook shows up later.

• Gateway says failed or no such payment: mark the attempt failed, which frees the order for a new attempt:

  
  UPDATE payments SET status = 'FAILED'
  WHERE id = :id AND status = 'INITIATED';
  

• Gateway says still pending: leave it alone and check again on the next run. If your gateway lets you cancel a pending payment (Stripe PaymentIntents can be canceled, for example), cancel it before marking the attempt FAILED, so it can't succeed after you've given up. Not every gateway allows this, so check yours. If yours doesn't, a late success will land in the late-payment path or you refund it.

The lease is what keeps two reconciler instances from working on the same row. If a worker crashes after claiming, the lease expires after 5 minutes and another run picks the row up. The 15-minute and 5-minute values are just examples. Pick yours based on how long your gateway normally takes to confirm.

A Small Extra: Rate Limiting the AI Step

Reading a prescription with an AI model is slow and it costs money on every call. So I added rate limiting at the API Gateway, for example a maximum of 5 extractions per minute per user (or per IP for anonymous traffic). It protects both the bill and the service from bots.

More Follow-Up Questions (With Answers)

The four questions above are the ones I actually got. The rest are follow-ups I think are natural for this design and I prepared answers for them. Treat them as practice questions.

1. "What if the payment succeeds after the order has already expired and the stock is gone?"

This is the classic late-payment case and it has a trap in it. "Re-check inventory" must not mean a plain SELECT. If two late webhooks both read "stock available" and both confirm, you've oversold. The re-check has to be the same atomic decrement we used at order time, inside a transaction that locks the order row first:

BEGIN;

SELECT status FROM orders WHERE id = :order_id FOR UPDATE;
-- Locks the order. A second webhook (or the sweeper) waits here.

-- If status = 'PAID'     -> duplicate. Do nothing.
-- If status = 'PENDING'  -> stock is still reserved. Just confirm:
UPDATE orders SET status = 'PAID'
WHERE id = :order_id AND status = 'PENDING';

-- If status = 'CANCELLED' -> stock was released. Try to take it again, per item:
UPDATE pharmacy_inventory
SET quantity = quantity - :qty
WHERE pharmacy_id = :pharmacy_id AND medicine_id = :medicine_id AND quantity >= :qty;
-- Every item returned 1 row? Then:
UPDATE orders SET status = 'PAID'
WHERE id = :order_id AND status = 'CANCELLED';
-- Any item returned 0 rows? ROLLBACK, then refund automatically and tell the user.

COMMIT;

Because of the row lock and the conditional updates, the cleanup job and any number of late webhooks can't both win. The point is to never silently take money for an order you can't fulfil.

2. "Why not just lock the inventory row until the payment finishes?"

Because payment can take seconds or hang for minutes. Holding a database lock that long blocks other users and ties up connections. A reservation with an expiry gives you the same protection without keeping a lock open.

3. "A user adds an item to cart and the pharmacy runs out before checkout. What happens?"

The cart doesn't reserve stock. It's basically a wish list. At checkout, the atomic stock update is the real check. If it fails, we tell the user and suggest another nearby pharmacy that still has it.

4. "What if the AI reads a medicine name wrongly?"

I'd never go straight from AI output to an order. Show the extracted list to the user so they can confirm or edit it. For medicines that need a valid prescription, add a verification step before payment, such as a pharmacist check. The AI should suggest, not decide.

5. "What if the pharmacy's system is down when the order is routed to it?"

Save the order first, then send it to the pharmacy through a message queue with retries. After a number of failed retries, move it to a dead-letter queue and alert someone. If the pharmacy doesn't respond within a set time, route the order to another pharmacy that has stock. Since retries mean the same message might be delivered more than once, the receiving side has to be idempotent too.

6. "Can you cache the nearby-pharmacy results?"

Yes, but carefully. Pharmacy locations and details rarely change, so they cache well with a short expiry. Stock changes constantly, so I wouldn't trust cached stock. The atomic update at order time is the source of truth and the cache is only there to make search feel fast.

7. "How would you handle 20 cities?"

Partition data by city, so each city's pharmacies, inventory and orders can live on separate database partitions or clusters. Most queries stay inside one city, so this works well. The harder cases, like a user ordering while travelling, can be handled by routing on the user's current city.

What I'd Do Differently

Writing this down made my own gaps pretty obvious. Here's my honest list:

• Say "resource held too long," not "deadlock." Precise words show you understand the difference.
• Use a cleanup job as the main expiry mechanism. Treat Redis TTL as a nice extra, not the only safety net.
• Mention the late-payment case myself and name the race. The re-check has to be the atomic decrement, not a plain read.
• Show the double-click protection explicitly. A unique gateway_txn_id only protects against duplicate webhooks. Double clicks need a unique idempotency_key and a one-active-payment-per-order index.
• Plan for stuck attempts. A one-active-payment index needs a reconciler or one lost response can block an order forever.
• Make every status change conditional. Use WHERE status = 'PENDING' on each transition, so the cleanup job and a late webhook can't both win.
• Go deeper on multi-city partitioning. I only gave a direction. A stronger answer would have covered how the data splits and what crosses city boundaries.

What I'd Tell Someone Preparing

• Don't only draw boxes. Explain what happens between them when the network fails.
• Bring up edge cases yourself. Raising the inventory timeout before being asked made the interview feel like a discussion, not a quiz.
• Justify every database choice. "Postgres with PostGIS over MongoDB" only sounds convincing if you can explain why.
• Start simple. Add scale and complexity when the interviewer takes you there.
• Say your trade-offs out loud. Strong consistency for orders and payments versus high availability for browsing is the heart of this problem.

FAQ

If you've been asked something similar, I'd like to hear what follow-ups you got. Good luck with your prep.