How I Built an Inventory Management System Using Next.js and Supabase

The message came in on a Tuesday afternoon. A client who runs a mid-sized hardware shop sent me a screenshot of his "inventory system" — six Excel files, three of them named final_stock_v2_REAL.xlsx. He asked one question: "Can you make this stop?"

I said yes before I fully thought it through. Here's everything that happened after.

Why I didn't just recommend existing software

My first instinct was to point him at Zoho Inventory or a similar off-the-shelf tool. But two things killed that idea. First, per-user pricing adds up fast when the shop has staff on multiple counters. Second, he had weird-but-reasonable requirements, like tracking which supplier each batch came from and printing barcode labels in a specific format his old printer supported.

Custom software made sense here. And with the stack I already use daily — Next.js and Supabase — I estimated I could ship a working version in about three weeks. It took five. It always takes longer.

The stack, and why

• Next.js (App Router) for the frontend and server actions
• Supabase for the Postgres database, auth, and realtime updates
• Vercel for hosting

Supabase was the important choice. Inventory is fundamentally a database problem, and Supabase gives you a real Postgres database with row level security, database functions, and realtime subscriptions. For an app where two cashiers might sell the same item at the same second, those features aren't nice-to-haves.

Designing the schema (the part I got wrong first)

My first schema looked like what most tutorials show:

create table products (
  id uuid primary key default gen_random_uuid(),
  name text not null,
  sku text unique not null,
  quantity integer not null default 0,
  price numeric(10,2) not null
);

See the problem? The quantity column. Storing the current stock as a single number feels natural, but it throws away history. When the client asked "why does the system say 3 units but the shelf has 5?", I had no way to answer. There was no record of how the number became 3.

The fix was switching to a movements-based design:

create table stock_movements (
  id uuid primary key default gen_random_uuid(),
  product_id uuid references products(id) not null,
  change integer not null, -- positive = stock in, negative = sale
  reason text not null,    -- 'purchase', 'sale', 'adjustment', 'return'
  created_by uuid references auth.users(id),
  created_at timestamptz default now()
);

Now current stock is just the sum of movements for a product. Every unit is accounted for, and disputes became queries instead of arguments.

The race condition that almost ruined launch week

Two days into testing, the client's staff found a bug I should have predicted. Two cashiers sold the last unit of the same item within a second of each other. Both sales went through. Stock went to -1.

My original code read the quantity in JavaScript, checked if it was enough, then wrote the sale. Between the read and the write, the other cashier's transaction slipped in. Classic race condition.

The fix was moving the logic into the database with a Postgres function, so the check and the write happen atomically:

create or replace function record_sale(p_product_id uuid, p_qty integer)
returns void as $$
declare
  current_stock integer;
begin
  select coalesce(sum(change), 0) into current_stock
  from stock_movements
  where product_id = p_product_id
  for update; -- locks relevant rows

  if current_stock < p_qty then
    raise exception 'Insufficient stock: % available', current_stock;
  end if;

  insert into stock_movements (product_id, change, reason, created_by)
  values (p_product_id, -p_qty, 'sale', auth.uid());
end;
$$ language plpgsql security definer;

From Next.js, I call it with a single RPC:

const { error } = await supabase.rpc('record_sale', {
  p_product_id: productId,
  p_qty: quantity,
});

Negative stock never happened again. Lesson learned: anything involving money or quantities should be enforced in the database, not in JavaScript.

Row level security in five minutes

Since staff accounts log into the same system, I used Supabase RLS to control access. Cashiers can insert sales but can't delete movements. Only the owner role can do adjustments.

alter table stock_movements enable row level security;

create policy "staff can record sales"
on stock_movements for insert
to authenticated
with check (reason = 'sale');

The owner gets broader policies. It took maybe an hour to set up and it means even if someone pokes at the API directly, the database itself refuses anything they're not allowed to do.

The frontend: server components did the heavy lifting

The product list, stock levels, and reports are all React Server Components. They query Supabase on the server, render HTML, and ship almost no JavaScript. Pages load fast even on the shop's mediocre connection.

The interactive parts — the sell form, the stock-in form — are client components that call server actions. And for the dashboard screen that sits open on the manager's monitor all day, I added a Supabase realtime subscription so stock numbers update live when any counter makes a sale:

supabase
  .channel('stock-changes')
  .on('postgres_changes',
    { event: 'INSERT', schema: 'public', table: 'stock_movements' },
    () => router.refresh()
  )
  .subscribe();

Watching the numbers tick down across two screens during testing was genuinely satisfying.

Mistakes I'd avoid next time

Storing quantity as a column. Covered above, but it's the big one. Store movements, compute totals.

Building reports too early. I spent the first week on a fancy analytics page. The client barely opens it. The daily-sales list — which took two hours to build — is what he checks every night.

Not asking about the printer on day one. The barcode label printing turned into a mini-project of its own. Hardware requirements should be part of the first conversation, not week four.

Skipping seed data. Testing an inventory system with 5 fake products hides problems that appear at 500. I eventually wrote a script to generate a thousand products with random movements, and it immediately exposed two slow queries that needed indexes.

What this actually cost

Supabase free tier handled everything during development. The shop now runs on the Pro plan, plus Vercel's free tier is still enough for their traffic. Compared to per-user SaaS pricing for a team of six, the client breaks even within months — and he owns the system.

Final thoughts

If you're comfortable with Next.js, an inventory system is one of the best real-world projects you can build. It forces you to think about data integrity, concurrency, and access control — the exact things tutorials skip. Start with the movements table, put the critical logic in Postgres functions, and let the database do what databases are good at.

And if a client ever sends you a file named final_stock_v2_REAL.xlsx, you know what you're getting into.