How to Build a Inventory System with Lovable

1Create an inventory management schema in Supabase.
2
3Tables:
4- 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
5- 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
6- low_stock_alerts: id, product_id (references products), current_stock (int), reorder_point (int), resolved_at (timestamptz), created_at
7
8Create a Postgres trigger function update_stock_after_movement() that fires AFTER INSERT on stock_movements:
91. UPDATE products SET current_stock = current_stock + NEW.quantity_change, updated_at = now() WHERE id = NEW.product_id
102. 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.
11
12RLS:
13- products: authenticated users can SELECT. Admin role required for INSERT/UPDATE/DELETE.
14- stock_movements: authenticated users SELECT and INSERT their own rows.
15- low_stock_alerts: authenticated users SELECT, service role INSERT/UPDATE.
16
17Add a constraint: CHECK(current_stock >= 0) on products to prevent negative stock.

1Add real-time low-stock alerts to the inventory dashboard.
2
31. 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).
4
52. Create a custom hook src/hooks/useLowStockAlerts.ts:
6   - 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()
7   - Maintain a state array of unresolved alerts (where resolved_at is null)
8   - On initial load, fetch existing unresolved alerts
9   - When a new alert arrives via Realtime, add it to state
10   - Export a dismissAlert(id) function that updates resolved_at = now() and removes from state
11
123. In the main dashboard layout, render an AlertBanner component:
13   - If lowStockAlerts.length > 0, show a yellow Alert (shadcn/ui) at the top
14   - Alert content: 'X products are low on stock' with a link to the low-stock filter view
15   - Each individual alert can be dismissed (calls dismissAlert)
16   - Animate in with a smooth slide-down using CSS transition

1// supabase/functions/bulk-adjust-stock/index.ts
2import { serve } from 'https://deno.land/std@0.168.0/http/server.ts'
3import { createClient } from 'https://esm.sh/@supabase/supabase-js@2'
4
5const corsHeaders = {
6  'Access-Control-Allow-Origin': '*',
7  'Access-Control-Allow-Headers': 'authorization, x-client-info, apikey, content-type',
8  'Content-Type': 'application/json',
9}
10
11type Adjustment = {
12  product_id: string
13  quantity_change: number
14  notes?: string
15}
16
17serve(async (req: Request) => {
18  if (req.method === 'OPTIONS') return new Response('ok', { headers: corsHeaders })
19
20  try {
21    const supabase = createClient(
22      Deno.env.get('SUPABASE_URL') ?? '',
23      Deno.env.get('SUPABASE_SERVICE_ROLE_KEY') ?? ''
24    )
25
26    const { adjustments, reference_id, created_by } = await req.json() as {
27      adjustments: Adjustment[]
28      reference_id: string
29      created_by: string
30    }
31
32    if (!adjustments?.length || !reference_id) {
33      return new Response(JSON.stringify({ error: 'adjustments and reference_id required' }),
34        { status: 400, headers: corsHeaders })
35    }
36
37    const movements = adjustments.map((adj) => ({
38      product_id: adj.product_id,
39      quantity_change: adj.quantity_change,
40      movement_type: 'adjusted',
41      reference_id,
42      notes: adj.notes ?? 'Bulk stocktake adjustment',
43      created_by,
44    }))
45
46    // Supabase inserts in batch — trigger fires per row, all in one transaction
47    const { data, error } = await supabase
48      .from('stock_movements')
49      .insert(movements)
50      .select()
51
52    if (error) throw error
53
54    return new Response(JSON.stringify({ success: true, inserted: data.length }), { headers: corsHeaders })
55  } catch (err) {
56    const message = err instanceof Error ? err.message : 'Internal error'
57    return new Response(JSON.stringify({ error: message }), { status: 500, headers: corsHeaders })
58  }
59})

1Build two pages:
2
31. Products page (src/pages/Products.tsx):
4   - 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)
5   - Search Input above table filtering by SKU or name
6   - '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
7   - '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()
8   - Low-stock products row highlighted with amber background
9
102. Movements page (src/pages/Movements.tsx):
11   - DataTable of stock_movements joined with product name
12   - Columns: Date, Product Name, SKU, Type Badge (color-coded), Quantity Change (green for positive, red for negative), Reference ID, Notes, Created By
13   - Filter bar: product Select, movement_type multi-select, date range Popover with Calendar
14   - Export CSV Button that downloads filtered results

1Build a dashboard home page at src/pages/Dashboard.tsx.
2
3Layout:
4- 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)
5- Below cards: two-column layout
6  - 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.
7  - Right: recent movements feed — last 10 stock_movements as a simple list showing product name, type Badge, quantity change, and relative time
8- Add a 'Quick Add Movement' floating action Button (bottom-right) that opens the Log Movement Dialog without navigating away
9
10All counts and aggregations are Supabase queries run in parallel with Promise.all on component mount. Show Skeleton loading placeholders while data is fetching.