How to Build an Inventory App With AI

To build an inventory app with AI you need to get one design decision right before you write a single prompt: stock levels must be derived from a log of movements, never stored as a number you edit. Ask an AI builder for an inventory app and you will almost always get the opposite, a products table with a quantity column that gets overwritten on every sale. That app works for two weeks and then drifts, and no amount of prompting fixes it afterwards because the history it needs was never recorded.

This walkthrough builds the ledger version instead. It takes an afternoon, and it assumes you have already got as far as building an app with AI once before.

What you are actually building

Three tables, not one:

• products: the catalogue. SKU, name, unit, reorder point, supplier. Never holds a quantity.

• stock_movements: the ledger. One row per event, with a signed quantity. A delivery of 40 is +40. A sale of 3 is -3. A stocktake correction of minus two units is -2 with a reason.

• locations: where stock physically sits, even if you only ever have one row called "shop".

Current stock is a query, not a column:

select p.sku, p.name, coalesce(sum(m.qty), 0) as on_hand
from products p
left join stock_movements m on m.product_id = p.id
group by p.sku, p.name;

That single decision buys you an audit trail, working stocktakes, the ability to answer "what did we have on 3 August", and immunity to the double-decrement bug that happens when two sales land at once.

Step 1: prompt for the schema, not the app

Start with the data model on its own. AI builders are far better at schema design when you do not simultaneously ask for screens.

Design a Postgres schema for a small inventory system with three tables:
products, locations, and stock_movements.

Hard requirements:
- Stock on hand is NEVER stored. It is always derived by summing
  stock_movements.qty for a product and location.
- stock_movements.qty is a signed integer. Positive for receipts,
  negative for issues. Include movement_type (receipt, sale,
  adjustment, transfer_in, transfer_out, return) and a reason field
  that is required for adjustments.
- Every movement row is immutable. Corrections are new rows, never
  updates or deletes.
- Include a created_at and a created_by on every movement.

Return the SQL and a one-paragraph explanation of why on_hand is
not a column.

That last line matters. Asking for the justification makes the model commit to the pattern rather than quietly adding a convenience column three prompts later. It is the same technique as making an agent state its plan before it writes anything: an explanation you can disagree with beats code you have to reverse engineer.

Step 2: the four screens, in order

Build in this sequence. Each one is a separate prompt against the finished schema.

1. Receive stock. Pick a product, enter quantity and supplier reference, write a receipt movement. This is the simplest write path, so it is where you find out whether your builder wired the database correctly.

2. Record a sale or issue. Same form, negative quantity, sale type. Add a check that refuses to write a movement that would take on-hand below zero, and make the error message name the current on-hand figure.

3. Stock list. The derived on-hand query above, with a low-stock flag comparing on-hand against reorder_point. Sort by shortfall, not alphabetically. The list exists to tell you what to order.

4. Stocktake. Count physically, enter the counted number, and let the app write the difference as an adjustment movement with a required reason. Never let the user type the new total directly into a quantity field.

Screen four is the one AI builders get wrong most often. The instinct is to set the value. The correct behaviour is to record the delta, because next month you will want to know that shrinkage was eleven units in aisle three and not simply that the number changed.

Step 3: the reorder logic

Reorder point is not a guess and it is not a round number. Use:

reorder_point = (average daily usage x lead time in days) + safety stock

Pull average daily usage from your own movement history once you have four weeks of it. Before then, use the supplier's minimum order quantity as a placeholder and mark it as provisional in the UI so nobody trusts it.

If you already keep sales in a spreadsheet, the fastest way to seed all of this is to import it rather than retype it, and adding CSV import to an app you built with AI is a well-trodden path. Map the spreadsheet's per-row quantity to a single opening receipt movement per SKU, dated the day you cut over.

Step 4: the tests worth writing

Four, and they take ten minutes:

• Two concurrent sales of the last unit. One must fail.

• An adjustment with no reason. Must be rejected.

• A movement dated in the future. Decide your rule and enforce it.

• On-hand for a product with 500 movements. Should return in well under a second, and if it does not, add a multicolumn index on (product_id, location_id) before adding a cache.

Ask your builder to write these as automated tests rather than clicking through them. Guidance on getting AI to write tests that actually test something applies directly, because the default output for an inventory app tends to be four tests that all check the happy path.

Step 5: units and pack sizes, the second thing that breaks

You buy in cases and sell in singles. If the ledger stores "3" without saying three of what, the app is wrong the first time a supplier changes pack size.

Store every movement in a single base unit per product, decided once and never changed. A case of 24 becomes a receipt of +24 singles, with the purchase pack recorded separately for the purchase order, not for the stock maths. Add two columns to products:

base_unit        text not null      -- 'each', 'kg', 'litre'
purchase_pack    integer not null   -- how many base units per case

Then make the receiving screen do the multiplication and show its working, so the person entering "4 cases" sees "96 each" before they confirm. Getting this backwards, storing cases in the ledger and converting on read, means every historical row is wrong the day the supplier ships 20-packs instead of 24s.

Fractional units are the same problem with a sharper edge. If you sell by weight, make the ledger column numeric with a fixed scale rather than an integer, and decide your rounding rule before you have data. The reasoning is the same as handling money in an app you built with AI: pick the unit, pick the precision, enforce both at the database.

A second location adds one more rule and no new tables. Transfers write two movements, a transfer_out at the source and a transfer_in at the destination, in one transaction. If your builder offers to write it as a single row with a from and a to column, decline. Two signed rows keep the sum-per-location query working unchanged, which is the whole reason the schema looks like this.

Step 6: what to leave out of version one

Barcode scanning, multi-currency costing, batch and expiry tracking, and supplier purchase orders are all real requirements for somebody, and none of them belong in the first build. The ledger schema above supports every one of them later without a migration, which is the entire point of getting it right first.

If you have never taken a build like this from local to a real URL, deploying an app built with AI covers the part after the code works. And if you would rather buy than build, there is an existing rundown of AI tools for small business inventory management worth reading before you spend the afternoon.

FAQ

Can an AI app builder really produce a working inventory system?

Yes, for a single location and a few hundred SKUs, comfortably. The constraint is not capability, it is that the default schema it reaches for does not survive contact with real stock movements.

Why not just store the quantity and update it?

Because you lose history, concurrent updates race, and stocktakes become guesswork. A summed ledger costs one extra table and removes all three problems.

How many products before this approach needs a cache?

Sum-on-read stays fast into the low hundreds of thousands of movement rows with a proper index. Beyond that, add a materialised snapshot per product and keep the ledger as the source of truth.

What if two people receive the same delivery twice?

The ledger shows both receipts with timestamps and users, so you reverse one with a negative adjustment and a reason. With a quantity column you would never have found it.