Smart Inventory & Auto-Merging Catalog API
By (author)Hafsa Yousuf
Read Me
Smart Inventory & Auto-Merging Catalog API (Next.js 14 + Supabase SSR)
Features
-
Smart Catalog Search: Real-time grocery/ingredient catalog search with dynamic category-based filtering.
-
Fallbacks to JSON Seed: The catalog search gracefully falls back to a 180+ standard item dataset if Supabase connectivity is offline or unconfigured.
-
Auto-Merge Quantity Logic: Intelligent duplicate handling that aggregates quantities for matching item names (case-insensitive) and units instead of creating messy duplicate records.
-
Full Pantry CRUD: Inline quantity drafting, fast updates on-blur, instant deletion actions, and organized category grouping badges.
-
Row-Level Security (RLS): Secure pantry entries isolated and scoped exclusively to authenticated users (
user_id = auth.uid()).
Auto-Merging Mechanism
POST request is sent to /api/inventory with a payload of items, the server-side route handles processing in three clear steps:-
Query & Match: For each incoming item, the backend queries
public.inventory_itemsfiltered byuser_id, comparingitem_name(case-insensitive) andunit. -
Quantity Aggregation (If Match Found): If an entry exists, it updates the record by setting
quantity = existing_quantity + new_quantity. -
Dynamic Insertion (If No Match Found): If no matching entry exists, it executes an insert query to create a clean new row.
Directory Structure
Installation Steps
Step 1: Execute SQL Setup
Run the SQL queries in supabase/schema.sql inside your Supabase project's SQL editor. This creates:
-
public.catalog_itemstable with a public read-only RLS policy. -
public.inventory_itemstable with RLS scoped toauth.uid() = user_id.
Step 2: Seed the Catalog (Optional but Recommended)
To populate your catalog_items table with the prebuilt dataset:
-
Copy the JSON array from
lib/catalog-seed.json. -
Execute the following SQL query in your Supabase SQL Editor:
with seed_items as (
select * from jsonb_to_recordset($json$
[PASTE JSON SEED CONTENT HERE]
$json$::jsonb) as item(name text, category text, default_unit text)
)
insert into public.catalog_items (name, category, default_unit)
select lower(trim(name)), trim(category), trim(default_unit)
from seed_items
on conflict (name) do update
set category = excluded.category,
default_unit = excluded.default_unit;
Step 3: Environment Variables
Add the following keys to your local .env.local file:
NEXT_PUBLIC_SUPABASE_URL=your_supabase_project_url
NEXT_PUBLIC_SUPABASE_ANON_KEY=your_supabase_anon_key
Step 4: Import & Mount Components
Mount <InventoryPanel /> inside any authenticated dashboard page layout:
import { InventoryPanel } from '@/components/inventory/InventoryPanel';
export default function Dashboard() {
function handleSelectedAction(selectedIds: string[]) {
console.log("Selected IDs to process:", selectedIds);
}
return (
);
}
