I have such hook, and it works well. It increases time, but it’s negligible if agent works for 10+ mins before the final output. The hook is only on stop event, so agent is not interrupted during the session. I didn’t have luck in putting it to skill / md files, as agent just ignores it after a couple of iterations. The solution is not perfect, sometimes it compresses too much, and I have to reread both versions, but at least I don’t have to read load bearing stuff anymore
I'm working on a platform that aggregates a business's operational and financial data and builds a causal model on top, deriving target metrics like FCF or EBITDA. With that model we can forecast different scenarios — what happens if we open a new shop, raise prices, take a loan. We also generate reports: weekly overviews, anomaly detection, deviations from targets. All of this is essentially a harness for an AI agent that customizes the platform for a specific business and integrates its data sources. Large companies have dedicated teams and sophisticated tooling for this; my bet is that small and medium businesses would benefit just as much, and with recent progress in AI it's finally possible to deliver it tailored to each case at very low cost.
My primary target right now is e-commerce due to rich data streams, however if you think your business would benefit from this tool, I would be happy to connect and discuss it.
> But if a seller has 5k inventory in one location, has a spike of 2k orders, but only 1k of the orders can successfully reserve inventory, then isn't that an argument that you lost the revenue of the 2nd 1k orders that error out before the replenishment process succeeds?
They explicitly cover this in the article, saying they do reservation inline. It does increase latency for these orders, but it doesn’t result in an error
> But you can have a batch transaction whereby the transaction decreases inventory by 100 (thus touching the high-contention inventory row once) and credits each of 100 different customer cart database rows (which are not under heavy contention and can be on a different disk entirely).
They mention this as well, checkout batching increased implementation complexity.
> Arguing that "slow reservations trigger throttling and a worse buyer experience", without an actual number for what counts as "slow" to serve as an SLO and as a design target, is a cop-out inviting over-engineering.
True. The article would’ve been better if they included such numbers. However the fact that they didn’t mention this doesn’t imply they haven’t done research. I haven’t found anything related to checkout specifically, however there are in general articles, indicating that increased latency correlates with revenue drop.
If anything this article shows that concurrency is a big issue. It is such a big issue you have to write in-memory single threaded processor with custom journaling. If you have established workflows with MySQL and a team knowing how to work with it, throwing all that to do LMAX is not cost efficient. While there are domains where such approach is suitable and even required due to strict transaction ordering, Shopify case doesn’t look like one of them.
They never said they don’t shard it, however this doesn’t solve the problem they were facing. Even if they have a single store (therefore a single shard), the burst demand may be high for the item in that shop, which creates contention for “remaining item quantity” resource. Their solution spreads this contention across several rows.
> Also, I wonder why they could not have a row status (available/reserved) and UPDATE it instead of deleting the rows.
This requires a row per item unit, doesn’t it? If you have 50k units you’ll have to track status of every item, meaning 50k rows. They also mention this as a rationale to use at most 1k rows, and treat it as a buffer.
I now thought that "updating a row" might be more expensive than simply deleting because UPDATE is implemented as "mark row deleted" + "insert new version of a row" in a table which support multiple versions of a row (MVCC). So maybe using DELETE is actually faster - it just marks a row as "deleted in transaction X". Unless I forgot something.
That's how Postgres' (and perhaps others) MVCC works, yes. MySQL / InnoDB, however, updates tuples in-place [0], and uses the undo log to recreate older versions as needed.
per my reading of the article, the protection is only needed for a few seconds, while payment is being processed by the payment system.
so the row is inserted when Payment is initiated, and row is deleted when Payment succeeds
What is oversell protection?
Reserve: When payment starts, we mark items as reserved (a short hold, e.g. several minutes).
Claim: When payment succeeds, we permanently deduct quantity from the inventory ledger (source of truth).
but that system could be easily improved to reserve item when user Adds item to a cart, to prevent scenario when user adds item to a cart, goes through checkout, and after initiating payment gets "soldout error":
1. Let user add item to a cart by default (happy path)
2. Initiate async check in the background for SKU and quantity
2a. The check sums up rows for all SKUs and compares to Inventory table (very cheap check since its done to only active shopping carts)
3. After few seconds the check comes back, and we let user know that item is soldout, before/the moment user goes to Checkout.
Ok, but before inserting you must ensure that inventory is not depleted, which means you need to know the count and you need to lock the row. So you still have contention on that item. Them having a 1k buffer allows not to take a lock on a single row every time, and only do it when buffer is empty
there is no need to lock the row, since you a dealing with a shopping cart, not individual item piece. when you run aggregate functions, lock is no needed, it is actually better to run it with SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; for aggregation
the check for oversold items is extremely cheap:
with current_order as (
select $SKU1, $q2 as quantity
union
select $SKU2, $q2 as quantity
),
with carts as (
select sku, sum(quantity) as reserved
from active_carts
group by sku
),
with warehouse as (
select sku, available_units
from inventory
group by sku
)
select * from current_order
inner join carts using (sku)
inner join warehouse using (sku)
where warehouse.available_units - carts.reserved < current_order.quantity
assuming there are indexes on sku field in both, results in efficient index seek and agg over 2 tables
The item is reserved when the user decides to place an order, but before paying for it. Not when a product is added to the cart because the user can keep it there for a month and end up not buying.
You reserve the product by creating an "active_cart" entry. Your solution has a problem, that when you run the check, it might say the product is available, but before you create an "active_cart" to reserve it from thread A, another thread B reserves it and you end up reserving a product that is not available anymore. You end up with SUM(active_cart.quantity) > inventory.available_units.
That is exactly why the database has locks - to prevent this situation. With locks, thread A decrements inventory.available_units and that row is locked until the end of transaction. Other threads (if they do SELECT FOR UPDATE instead of SELECT) cannot see the old, invalid value until thread A either commits and the value is updated or rollbacks. However, locks cause performance issues and that is why shopify uses the architecture from the article - instead of 100 users fighting for the lock on the same row with available amount, each user locks only rows with units they plan to buy.
I don’t understand how this should prevent oversold. You have a check that reports empty or oversold inventory. But how does that check prevent 2 concurrent actors fighting for the last item from inserting 2 rows?
how does current design resolve concurrent actors fighting for the last item ?
there is ultimately needs to be some global mechanism resolving this conflict. Currently it is an order in which db engine processes transactions by locking rows for a transaction, whoever got the first lock, wins the last remaining items.
my design is the same, except it does not need this dance with moving rows between tables, locking them, and the cludge with replenishment process.
in the simplest form, run the sum() over active non-finished orders and compare to inventory. you get the same result: whoever got the first to run sum() and get positive answer will get the last remaining items.
but the problem as formulated, imho, is not even correctly defined.
Shopify incorrectly formulated the very problem they are trying to solve.
Trying to solve it at the payment time is too late, its better to resolve it earlier, before the checkout.
the "PAY" button should only do one thing: deduct money from cc and that's it. Resolving inventory availability must be solved way earlier, the moment user clicks Checkout, not when user clicks Pay.
So ideally, the error for oversold items should be shown to a user when he clicks Checkout, not when he click PAY
> Shopify incorrectly formulated the very problem they are trying to solve.
That’s a bold overconfident statement. Cart abandonment is real. People never clear their carts they just walk away
Shopify purposefully chooses to do it at payment time because doing it earlier results in lost sales as people “reserve” items and then walk away causing other to see out of stock and then also walk away
Whoever puts up the money first gets the item
That’s the design constraint they chose you can’t just say “their solution is wrong because they solved the wrong problem”. Each design is a different user experience and I think it’s safe to say they chose which experience they want consciously.
that's why I mentioned active carts in my post, there are ways to define active cart to get rid of abandoned carts ( ignore carts where last user action was > N seconds ago).
Ok, let's accept the design goal that whoever paid first wins. You can use the same metric (how many milliseconds ago did user click PAY) and impose a global monotonic non-decreasing counter to distribute the scarce inventory. This is how order matching engines work at stock exchanges with HFT orders (FIFO logic).
the goal is to know with 100% certainty, before sending payment request to payment processor, who will have item and who won't, and you dont need to move mountains of rows for that.
the payment processor should be just a binary answer: payment succeeded or not, but currently it combines Inventory availability check & payment processing, which is the root cause of confusion. For clarity it is better to make that stage of order processing an explicit separage stage, instead of coupling it with payment stage.
some stores split payment into two stages: Payment and Final order confirmation. at the Payment stage you can pre-authorize money at cc and do inventory availability, and at final confirmation you capture $$
i dont know about the world, by authorize.net and Stripe, which work globally and work with global credit cards, they do support separate authorize and separate capture, which seems to be part of PCI standard
Looking at your solution, if i understand it, is instead of decreasing the inventory count for each sku as orders are processed, you are comparing the current warehouse quantity against the sum of all carts to see if there's availbale quantity.
You'll have to also include the sum of all completed orders so far.
Honestly seems almost worse? Arn't you trading contention on a single counter (inventory) for a large read across all pending and completed orders? Even indexed you're ingesting a ton more data? And you'll still need a lock here as you have to ensure two orders do this check at the same time.
Naive design
- Single inventory row per warehouse sku
- All orders compete on a lock for all inventory sku rows in their order to deduct/claim their items
Shopify design
- Unroll warehouse inventory to thousands of rows per sku
- Order processing races to find sufficient unlocked rows for all items in order
- If insufficient rows are found then orders block behind slower "restock" process that creates more rows
Your design
- Warehouse inventory row is static/read-only (restocking out of scope for now that's fine).
- Order processing computes the sum of all completed orders to ensure there is sufficient quantity
- This would have to be under a lock as well, otherwise two or more racing orders will think there is quantity left.
So sounds like in your solution, you still have a single point of contention for who is computing the sum of completed orders, and while holding that lock you are doing a sum of all completed orders for each sku in your order. That sounds... worse?
Clearly this is for high concurrency cases where there are many people racing to get all the available items. It's not clear that it's in shopifys or the sellers interest to let items get sequestered in people's shopping carts, which is a spot where there isn't a strong commitment to complete the purchase. At payment time, you can be more assured that the item will actually be purchased.
Still I think their solution is a bit weird. I'd want to commit the reservation transaction with inventory decrement along with a payment key and then use a different transaction to drop the reservation when the transaction completes. If the transaction does not complete in a timely manner you probably need to query external systems anyway to resolve whether the payment actually occurred or not.
They talk about lock contention in this case, but I also wonder about latch contention since these rows are adjacent. If it's a small transaction that's not interactive, does mysql resolve it with just the latches on the needed tables?
I was curious about what Tiger Beetle does. It has two phase transfers, which appears tailor built to handle this case. But maybe Tiger Beetle isn't the right database to track all your product stocks.
> how does current design resolve concurrent actors fighting for the last item ?
It resolves with skip locked. Assuming we have only 1 item left. First query scans the buffer table, locks as many rows as needed (1 in our case), and moves rows to another table. Second query scans the table, finds no rows (even if first one hasn’t finished yet, the row is locked and ignored), checks if it can increase buffer, finds out that it’s fully sold and aborts. Db guarantees that you can’t oversold.
> my design is the same, except it does not need this dance with moving rows between tables, locking them, and the cludge with replenishment process.
I can’t evaluate whether it’s the same or not, because you still haven’t clarified when exactly you’re going to insert the row. In the article they’re inserting in the same transaction. Would you also do it in the transaction? Because if you’ll introduce a separate global mechanism to resolve conflicts, on a high level it would be the same as their approach with redis (you need to have 2 systems)
think about for a moment what that skip locked actually means, all these 1000 rows per SKU are logically equivalent to a Inventory table with a single row where available_units=1000 per SKU.
now let's think again, do we need to lock 900 rows to place order on 900 items? or can we insert a single row where order_quantity=900 ?
shopify's design relies on DB to lock rows for transaction as a way to "decrement the counter" of available units. What I am suggesting, is you can just decrement counter by updating a single row, no need to lock 900 rows. Shopify moved from one extreme (single global variable in redis) to another extreme (1000 rows in db) and forgot about the middle ground.
The dance with moving rows per each item between tables is completely unnecessary, it's like counting numbers one by one in a for loop, when you can just substract number directly.
if I were to solve the problem, I would have solved it differently, at the Checkout state, before user clicks PAY. This removes the race condition at the user UI level, before any request lands in backend/db:
1. Have a table with active shopping carts (cart_id, cart_status, sku, quantity)
2. when cart_status changes to 'Checkout' run inventory availability check
3. If inventory availability check fails, show error to user (before he clicks Pay) and suggest replacement items.
4. If inventory availability succeeds, proceed to charge cc
availability check is the SQL above: inventory-sum(active_carts.quantity)-current_order must be > 0
> if I were to solve the problem, I would have solved it differently, at the Checkout state, before user clicks PAY. This removes the race condition at the user UI level, before any request lands in backend/db:
In order to avoid races you need to insert reservation and decrement availability atomically. Your proposed approach is not atomic. For it to be atomic you will need to lock whole range, to make sure no new rows appeared between the points “check for availability” and “record reservation”. Actors will be effectively competing for the single aggregate row. This is the same as having a single inventory row with quantity field, which they rejected in the beginning of the article
> now let's think again, do we need to lock 900 rows to place order on 900 items? or can we insert a single row where order_quantity=900 ?
In the proposed schema nobody is waiting for these locks, they’re skipped by concurrent queries. In your schema actors would have to wait before they can insert without breaking invariants.
Two concurrent deductions of inventory do contend but only during the actual DB update. That is just normal DB locking for SQL isolation levels. The blog refers to explicit locking by the app, which is where skip locked comes in.
I noticed this for some companies: there are 10+ spreadsheets which should have the same structure, but different data. For example one spreadsheet per country for purposes of budgeting, modeling, or just inputs. These spreadsheets tend to break and it’s hard to update them centrally. There is of course an option with moving to a proper admin app, but it requires development and quite often results in less UX, because users are familiar with spreadsheets - they can write formulas, analyze the data, or connect with other systems.
This is why I’m writing a tool to simplify administrative work with these files, so you can quickly see where spreadsheets diverge, propagate updates, make them version controlled, and many other good things, which we have in typical app development, but still miss in spreadsheets management.
