# How to Build an Inventory System with Lovable

- Tool: How to Build with Lovable
- Difficulty: Intermediate
- Compatibility: Lovable Pro or higher
- Last updated: April 2026

## TL;DR

Build a real-time inventory management system in Lovable where stock movements automatically update product levels via a Postgres trigger, low-stock alerts fire through Supabase Realtime, and bulk stock adjustments execute atomically via an Edge Function — complete with a searchable product catalog, movement history, and dashboard charts.

## Before you start

- Lovable Pro account for Edge Function generation
- Supabase project with SUPABASE_URL and SUPABASE_SERVICE_ROLE_KEY in Cloud tab → Secrets
- A list of your product names and SKUs ready to seed the products table
- Understanding of the movement types your business uses (received, sold, adjusted, damaged)
- Optional: an existing orders or sales table to reference from stock movements

## Step-by-step guide

### 1. Create the inventory schema with stock trigger

Ask Lovable to create the tables and the trigger that keeps stock_level in sync with every movement. The trigger is the foundation — all other features rely on it being correct.

```
Create an inventory management schema in Supabase.

Tables:
- products: id, sku (text unique), name, description, category, unit_cost (decimal), current_stock (int default 0), reorder_point (int default 10), reorder_quantity (int default 50), location (text), is_active (bool default true), created_at, updated_at
- stock_movements: id, product_id (references products), quantity_change (int, positive = stock in, negative = stock out), movement_type ('received' | 'sold' | 'adjusted' | 'damaged' | 'returned'), reference_id (text, order/PO number), notes (text), created_by (references auth.users), created_at
- low_stock_alerts: id, product_id (references products), current_stock (int), reorder_point (int), resolved_at (timestamptz), created_at

Create a Postgres trigger function update_stock_after_movement() that fires AFTER INSERT on stock_movements:
1. UPDATE products SET current_stock = current_stock + NEW.quantity_change, updated_at = now() WHERE id = NEW.product_id
2. After update, check if new current_stock < reorder_point. If so, and if no unresolved alert exists for this product, INSERT into low_stock_alerts.

RLS:
- products: authenticated users can SELECT. Admin role required for INSERT/UPDATE/DELETE.
- stock_movements: authenticated users SELECT and INSERT their own rows.
- low_stock_alerts: authenticated users SELECT, service role INSERT/UPDATE.

Add a constraint: CHECK(current_stock >= 0) on products to prevent negative stock.
```

> Pro tip: The CHECK(current_stock >= 0) constraint will cause the trigger to fail — and roll back the movement — if a sale would result in negative stock. This is a feature: it enforces that you can only sell what you have. To allow backorders, remove the constraint and add a separate backorder_stock column.

**Expected result:** Tables are created. Inserting a stock_movement with quantity_change=-5 for a product updates current_stock by -5 and inserts a low_stock_alert if stock is now below reorder_point. The TypeScript types are generated.

### 2. Set up Realtime low-stock alerts

Ask Lovable to subscribe to the low_stock_alerts table and display a live alert banner. This requires enabling Realtime on the table in Supabase and subscribing on the client.

```
Add real-time low-stock alerts to the inventory dashboard.

1. In the Supabase dashboard, enable Realtime for the low_stock_alerts table (Supabase will handle this when I ask you to set up the subscription).

2. Create a custom hook src/hooks/useLowStockAlerts.ts:
   - Subscribe to INSERT events on the low_stock_alerts table using supabase.channel('low-stock').on('postgres_changes', { event: 'INSERT', schema: 'public', table: 'low_stock_alerts' }, callback).subscribe()
   - Maintain a state array of unresolved alerts (where resolved_at is null)
   - On initial load, fetch existing unresolved alerts
   - When a new alert arrives via Realtime, add it to state
   - Export a dismissAlert(id) function that updates resolved_at = now() and removes from state

3. In the main dashboard layout, render an AlertBanner component:
   - If lowStockAlerts.length > 0, show a yellow Alert (shadcn/ui) at the top
   - Alert content: 'X products are low on stock' with a link to the low-stock filter view
   - Each individual alert can be dismissed (calls dismissAlert)
   - Animate in with a smooth slide-down using CSS transition
```

**Expected result:** When a stock_movement causes stock to drop below reorder_point, the low-stock alert appears in the dashboard within 1-2 seconds without a page refresh. Dismissing an alert marks it resolved in the database.

### 3. Build the bulk adjustment Edge Function

Stocktakes and bulk corrections need to update many products at once atomically. Build an Edge Function that wraps a batch of movements in a single Postgres transaction.

```
// supabase/functions/bulk-adjust-stock/index.ts
import { serve } from 'https://deno.land/std@0.168.0/http/server.ts'
import { createClient } from 'https://esm.sh/@supabase/supabase-js@2'

const corsHeaders = {
  'Access-Control-Allow-Origin': '*',
  'Access-Control-Allow-Headers': 'authorization, x-client-info, apikey, content-type',
  'Content-Type': 'application/json',
}

type Adjustment = {
  product_id: string
  quantity_change: number
  notes?: string
}

serve(async (req: Request) => {
  if (req.method === 'OPTIONS') return new Response('ok', { headers: corsHeaders })

  try {
    const supabase = createClient(
      Deno.env.get('SUPABASE_URL') ?? '',
      Deno.env.get('SUPABASE_SERVICE_ROLE_KEY') ?? ''
    )

    const { adjustments, reference_id, created_by } = await req.json() as {
      adjustments: Adjustment[]
      reference_id: string
      created_by: string
    }

    if (!adjustments?.length || !reference_id) {
      return new Response(JSON.stringify({ error: 'adjustments and reference_id required' }),
        { status: 400, headers: corsHeaders })
    }

    const movements = adjustments.map((adj) => ({
      product_id: adj.product_id,
      quantity_change: adj.quantity_change,
      movement_type: 'adjusted',
      reference_id,
      notes: adj.notes ?? 'Bulk stocktake adjustment',
      created_by,
    }))

    // Supabase inserts in batch — trigger fires per row, all in one transaction
    const { data, error } = await supabase
      .from('stock_movements')
      .insert(movements)
      .select()

    if (error) throw error

    return new Response(JSON.stringify({ success: true, inserted: data.length }), { headers: corsHeaders })
  } catch (err) {
    const message = err instanceof Error ? err.message : 'Internal error'
    return new Response(JSON.stringify({ error: message }), { status: 500, headers: corsHeaders })
  }
})
```

> Pro tip: Supabase batch INSERTs are not wrapped in a single transaction by default. For true atomicity, create a Postgres function bulk_insert_movements(movements jsonb) using LANGUAGE plpgsql that loops through the array and calls INSERT in a single function body. Functions in Postgres run in an implicit transaction.

**Expected result:** The Edge Function accepts a JSON array of adjustments and inserts all stock_movements in one operation. If any row fails the CHECK constraint, none are committed.

### 4. Build the product catalog and movement history

Ask Lovable to create the two main pages: the product catalog where staff can add products and log single movements, and the movement history with filters.

```
Build two pages:

1. Products page (src/pages/Products.tsx):
   - DataTable of all active products. Columns: SKU (monospace), Name, Category, Current Stock (red text if below reorder_point), Reorder Point, Location, Actions (Log Movement button)
   - Search Input above table filtering by SKU or name
   - 'Add Product' Button opening a Sheet with a form (react-hook-form + zod): SKU, name, description, category Select (Electronics/Clothing/Food/Other), unit_cost, reorder_point, reorder_quantity, location
   - 'Log Movement' button opens a Dialog: movement type Select (received/sold/adjusted/damaged/returned), quantity Input (positive number), reference_id Input, notes Textarea. Submitting calls supabase.from('stock_movements').insert()
   - Low-stock products row highlighted with amber background

2. Movements page (src/pages/Movements.tsx):
   - DataTable of stock_movements joined with product name
   - Columns: Date, Product Name, SKU, Type Badge (color-coded), Quantity Change (green for positive, red for negative), Reference ID, Notes, Created By
   - Filter bar: product Select, movement_type multi-select, date range Popover with Calendar
   - Export CSV Button that downloads filtered results
```

**Expected result:** Both pages render correctly. Logging a movement from the product page inserts a stock_movements row, the trigger fires, and the current_stock in the product table updates. The movements history reflects the new entry.

### 5. Build the dashboard overview

The main dashboard page gives managers a quick health view of the entire inventory. Ask Lovable to build the summary cards and charts.

```
Build a dashboard home page at src/pages/Dashboard.tsx.

Layout:
- Row of four stat Cards: Total Products (count), Total Stock Value (SUM of current_stock * unit_cost formatted as currency), Low Stock Items (count WHERE current_stock < reorder_point), Movements Today (count WHERE created_at > today)
- Below cards: two-column layout
  - Left: Recharts BarChart of the 10 products with the lowest stock as a percentage of reorder_point. X-axis = product name (truncated), Y-axis = current_stock. Bars colored red if below reorder_point.
  - Right: recent movements feed — last 10 stock_movements as a simple list showing product name, type Badge, quantity change, and relative time
- Add a 'Quick Add Movement' floating action Button (bottom-right) that opens the Log Movement Dialog without navigating away

All counts and aggregations are Supabase queries run in parallel with Promise.all on component mount. Show Skeleton loading placeholders while data is fetching.
```

**Expected result:** The dashboard shows live stock health across all cards and charts. The bar chart immediately reflects recent movements. The floating action button opens the movement dialog from any view on the dashboard.

## Complete code example

File: `src/hooks/useLowStockAlerts.ts`

```typescript
import { useEffect, useState } from 'react'
import { supabase } from '@/integrations/supabase/client'

type LowStockAlert = {
  id: string
  product_id: string
  current_stock: number
  reorder_point: number
  created_at: string
  products: { name: string; sku: string } | null
}

export function useLowStockAlerts() {
  const [alerts, setAlerts] = useState<LowStockAlert[]>([])
  const [loading, setLoading] = useState(true)

  useEffect(() => {
    const fetchAlerts = async () => {
      const { data } = await supabase
        .from('low_stock_alerts')
        .select('*, products(name, sku)')
        .is('resolved_at', null)
        .order('created_at', { ascending: false })
      setAlerts(data ?? [])
      setLoading(false)
    }

    fetchAlerts()

    const channel = supabase
      .channel('low-stock-alerts')
      .on(
        'postgres_changes',
        { event: 'INSERT', schema: 'public', table: 'low_stock_alerts' },
        async (payload) => {
          const { data } = await supabase
            .from('low_stock_alerts')
            .select('*, products(name, sku)')
            .eq('id', payload.new.id)
            .single()
          if (data) setAlerts((prev) => [data, ...prev])
        }
      )
      .subscribe()

    return () => { supabase.removeChannel(channel) }
  }, [])

  const dismissAlert = async (id: string) => {
    await supabase
      .from('low_stock_alerts')
      .update({ resolved_at: new Date().toISOString() })
      .eq('id', id)
    setAlerts((prev) => prev.filter((a) => a.id !== id))
  }

  return { alerts, loading, dismissAlert }
}
```

## Common mistakes

- **Updating current_stock directly instead of going through stock_movements** — Direct updates to the stock column bypass the movement ledger, destroying traceability. You lose the audit trail and can no longer reconcile discrepancies. Fix: Treat current_stock as a read-only computed value. All stock changes must be INSERTs into stock_movements. Deny direct UPDATE on products.current_stock by removing it from the RLS UPDATE policy columns list.
- **Forgetting to enable Realtime for the low_stock_alerts table** — Supabase Realtime must be enabled per-table. Without it, the channel subscription silently receives no events and the alert banner never updates. Fix: In your Supabase project, go to Database → Replication and add the low_stock_alerts table to the publication. Ask Lovable to include this in the setup prompt or enable it manually in the Supabase dashboard.
- **Not handling the case where a sale creates negative stock** — The CHECK(current_stock >= 0) constraint will reject the stock_movement INSERT with a database error. If this error is not handled, the UI shows a confusing generic error instead of 'Insufficient stock'. Fix: In the Log Movement form, check available stock before submitting. In the bulk adjustment Edge Function, catch the constraint violation error and return a user-friendly message: 'Product [name] would go negative. Movement rejected.'
- **Using the service role key in client-side code** — The service role key bypasses RLS. Including it in the Vite bundle exposes it to any user who opens browser DevTools, giving them full database access. Fix: The service role key is only for Edge Functions (accessed via Deno.env.get). All client-side Supabase queries use the anon key. The anon key is safe to expose because RLS policies control what data is accessible.

## Best practices

- Never allow direct updates to the current_stock column from the application. All stock changes flow through stock_movements so the ledger is always complete and auditable.
- Add a not-null reference_id requirement for movements of type 'sold' or 'received'. This ensures every stock change can be traced back to an order or purchase order.
- Use optimistic updates in the UI for single movements: update the local state immediately, then confirm with the database response. If the database rejects the movement (e.g. negative stock), revert and show an error.
- Index stock_movements on (product_id, created_at DESC) for fast movement history queries and on (created_at) for the dashboard's today count.
- Set up a Supabase alert or pg_cron job that recalculates current_stock from the movements ledger weekly as a consistency check. Log any discrepancies to an audit table.
- Export the bulk adjustment template as a CSV download. Staff fill in the spreadsheet and upload it. Parse the CSV in the browser and preview changes before submitting to the Edge Function.
- Use Row Level Security to restrict which users can log which movement types. Warehouse staff can log 'received' and 'damaged'. Only managers can log 'adjusted'. Implement this via a role check in the movements INSERT policy.

## Frequently asked questions

### How does the stock trigger handle concurrent movements?

Postgres processes each INSERT into stock_movements serially within the transaction. The trigger uses UPDATE products SET current_stock = current_stock + NEW.quantity_change, which is an atomic increment. If two movements for the same product arrive simultaneously, Postgres serializes them at the row lock level, so neither is lost or double-applied.

### What if I need to allow negative stock for backorders?

Remove the CHECK(current_stock >= 0) constraint from the products table. Add a separate column allow_backorder (boolean default false) per product. Modify the trigger to only enforce non-negative stock when allow_backorder is false. The UI can show negative stock in a distinct color to differentiate it from normal levels.

### How do I seed the products table with my existing inventory data?

Prepare a CSV with columns matching your products table schema. In the Supabase dashboard, go to Table Editor → products → Import Data and upload the CSV. Alternatively, ask Lovable to build a CSV import page that parses the file client-side and batches INSERT calls in groups of 100 rows.

### Can Supabase Realtime handle a high volume of stock movements?

Supabase Realtime is suitable for alert-level notifications (low-stock events are infrequent). For the movement feed in the dashboard, polling every 30 seconds is more appropriate than Realtime for high-volume systems, as Realtime connections are limited on the free tier. Use Realtime for the low_stock_alerts table and polling for movement history.

### How do I connect inventory to an order management system?

Create an Edge Function process-order that accepts an array of line items (product_id, quantity). For each item, it inserts a stock_movement with type='sold' and the order ID as reference_id. Call this function from your order checkout flow after payment is confirmed. If any product has insufficient stock, the entire function should return an error before any movements are inserted.

### How do I generate a snapshot of current inventory for accounting?

The dashboard's Export CSV button should query SELECT sku, name, current_stock, unit_cost, (current_stock * unit_cost) as total_value FROM products WHERE is_active = true ORDER BY category, name. Format it as CSV in the browser and trigger a download. Include the export timestamp in the filename for version control.

### What is the best way to track inventory that has an expiry date?

Add an expiry_date column to stock_movements (for received items) and to a separate inventory_lots table. When stock is sold, consume the oldest lots first (FIFO). Add a pg_cron job that checks daily for lots expiring within 30 days and creates low_stock_alerts with type='expiring'. The movement history then shows which lot each unit came from.

### Is there help available for building a more complex inventory system?

RapidDev builds Lovable apps with complex backend logic including multi-location inventory, supplier portals, and ERP integrations. Reach out if your inventory requirements go beyond this guide.

---

Source: https://www.rapidevelopers.com/how-to-build-lovable/inventory-system
© RapidDev — https://www.rapidevelopers.com/how-to-build-lovable/inventory-system