How to Build a Inventory Tracking Platform with Lovable

1Create an inventory tracking app with Supabase. Set up these tables:
2
3- categories: id, org_id, name, description, created_at
4
5- products: id, org_id, sku (unique), name, description, category_id, unit_cost (numeric), selling_price (numeric), reorder_point (integer default 10), reorder_quantity (integer default 50), image_url (text nullable), is_active (boolean default true), created_at, updated_at
6
7- inventory_movements: id, org_id, product_id, movement_type ('receive'|'sale'|'adjustment'|'return'|'transfer'), quantity (integer, positive for stock in, negative for stock out), unit_cost (numeric nullable), reference_id (text nullable, e.g. order ID), notes (text nullable), created_by (uuid references auth.users), created_at (timestamptz default now())
8
9- inventory_alerts: id, org_id, product_id, alert_type ('low_stock'|'out_of_stock'), current_stock (integer), reorder_point (integer), resolved_at (nullable timestamptz), created_at
10
11Create a Postgres view called current_stock:
12SELECT product_id, SUM(quantity) as stock_level FROM inventory_movements GROUP BY product_id
13
14Create a trigger: after INSERT on inventory_movements, compute the new stock for product_id. If stock <= reorder_point and no unresolved alert exists, insert into inventory_alerts.
15
16Enable RLS on all tables with org_id isolation. Seed with 20 sample products across 4 categories and 50 movements.

1import { useState } from 'react'
2import { useQuery } from '@tanstack/react-query'
3import { supabase } from '@/integrations/supabase/client'
4import { DataTable } from '@/components/ui/data-table'
5import { Input } from '@/components/ui/input'
6import { Select, SelectTrigger, SelectValue, SelectContent, SelectItem } from '@/components/ui/select'
7import { Badge } from '@/components/ui/badge'
8import { Button } from '@/components/ui/button'
9import { Plus } from 'lucide-react'
10import type { ColumnDef } from '@tanstack/react-table'
11
12type ProductRow = {
13  id: string; sku: string; name: string; category_id: string
14  unit_cost: number; selling_price: number; reorder_point: number; is_active: boolean
15  current_stock: number | null
16  categories: { name: string } | null
17}
18
19export function ProductCatalog() {
20  const [search, setSearch] = useState('')
21  const [category, setCategory] = useState('all')
22
23  const { data: products = [] } = useQuery<ProductRow[]>({
24    queryKey: ['products', search, category],
25    queryFn: async () => {
26      let q = supabase
27        .from('products')
28        .select('*, categories(name), current_stock:current_stock(stock_level)')
29        .eq('is_active', true)
30        .order('name')
31      if (search) q = q.ilike('name', `%${search}%`)
32      if (category !== 'all') q = q.eq('category_id', category)
33      const { data, error } = await q
34      if (error) throw error
35      return data as ProductRow[]
36    },
37  })
38
39  const columns: ColumnDef<ProductRow>[] = [
40    { accessorKey: 'sku', header: 'SKU', cell: ({ getValue }) => <code className="text-xs">{getValue() as string}</code> },
41    { accessorKey: 'name', header: 'Product' },
42    { accessorFn: (r) => r.categories?.name ?? '—', header: 'Category' },
43    { accessorKey: 'current_stock', header: 'In Stock', cell: ({ getValue, row }) => {
44      const stock = (getValue() as number) ?? 0
45      const low = stock <= row.original.reorder_point
46      return <Badge variant={stock === 0 ? 'destructive' : low ? 'secondary' : 'default'}>{stock}</Badge>
47    }},
48    { accessorKey: 'selling_price', header: 'Price', cell: ({ getValue }) => `$${(getValue() as number).toFixed(2)}` },
49  ]
50
51  return (
52    <div className="space-y-3">
53      <div className="flex items-center gap-2">
54        <Input placeholder="Search products..." value={search} onChange={(e) => setSearch(e.target.value)} className="max-w-xs" />
55        <Select value={category} onValueChange={setCategory}>
56          <SelectTrigger className="w-40"><SelectValue /></SelectTrigger>
57          <SelectContent>
58            <SelectItem value="all">All Categories</SelectItem>
59          </SelectContent>
60        </Select>
61        <Button size="sm" className="ml-auto"><Plus className="mr-1 h-3 w-3" /> Add Product</Button>
62      </div>
63      <DataTable columns={columns} data={products} />
64    </div>
65  )
66}

1Build a StockAdjustmentDialog component at src/components/inventory/StockAdjustmentDialog.tsx.
2
3Requirements:
4- Props: product (id, name, current stock level), onClose callback
5- Show a shadcn/ui Dialog titled 'Adjust Stock — [product name]'
6- Form fields using react-hook-form + zod:
7  - movement_type: Select with options: Receive (+), Sale (-), Adjustment (+/-), Return (+)
8  - quantity: Input type number, min 1. For 'sale' and negative 'adjustment', the value will be negated before insert.
9  - unit_cost: Input type number, optional, shown only for 'receive' type
10  - reference_id: Input text, optional, placeholder 'PO-123 or Order-456'
11  - notes: Textarea optional
12- Show a preview line: 'Current stock: 42 → New stock: 47' that updates as the user types
13- On submit: insert into inventory_movements. Quantity should be positive for receive/return, negative for sale.
14- Show a success toast and close the Dialog on completion
15- Invalidate the ['products'] React Query cache on success

1Build a StockTrendChart component at src/components/inventory/StockTrendChart.tsx.
2
3Requirements:
4- Props: productId (string), productName (string), reorderPoint (number)
5- Call supabase.rpc('get_stock_history', { p_product_id: productId, p_days: 30 })
6- The function returns rows of { day: date, stock_level: number }
7- Render using recharts LineChart:
8  - XAxis dataKey='day' with date format 'MMM d'
9  - YAxis with domain [0, 'auto']
10  - Line for stock_level in indigo, dot=false, strokeWidth=2
11  - A ReferenceLine at y=reorderPoint with label='Reorder' stroke='#f59e0b' strokeDasharray='4 4'
12  - Tooltip showing the date and stock level
13  - ResponsiveContainer width='100%' height={200}
14- Render inside a shadcn/ui Card with the product name as CardTitle
15- Show a Skeleton while loading
16- If stock_level crosses below reorderPoint, shade that region in a light amber color using Recharts ReferenceArea

1Build a LowStockAlertsPanel component at src/components/inventory/LowStockAlertsPanel.tsx.
2
3Requirements:
4- Fetch unresolved alerts from inventory_alerts (resolved_at IS NULL) joined with products (name, sku)
5- Subscribe to Supabase Realtime INSERT events on inventory_alerts to add new alerts in real time
6- Render as a shadcn/ui Sheet that opens from a Bell icon Button in the app header
7- The Bell icon shows a red Badge with the unresolved alert count (hide Badge if count is 0)
8- Inside the Sheet:
9  - Title: 'Inventory Alerts'
10  - Group alerts by alert_type: 'Out of Stock' (stock = 0) at the top, 'Low Stock' below
11  - Each alert row: product name, SKU, current stock, reorder point, created_at relative time
12  - 'Order Now' Button per row: opens the StockAdjustmentDialog pre-filled with movement_type='receive' for that product
13  - 'Dismiss' Button: sets resolved_at = now() on the alert row and removes it from the list
14- Show empty state illustration when no unresolved alerts exist