How to Build Recommendations engine with V0

1// Paste this prompt into V0's AI chat:
2// Build a recommendations engine. Enable pgvector extension in Supabase and create schema:
3// 1. items: id (uuid PK), name (text), description (text), category (text), metadata (jsonb), embedding (vector(1536)), created_at (timestamptz)
4// 2. user_interactions: id (uuid PK), user_id (uuid FK), item_id (uuid FK), interaction_type (text check view/click/purchase/rating), rating (integer check 1-5 nullable), created_at (timestamptz) with index on (user_id, interaction_type)
5// 3. user_preferences: id (uuid PK), user_id (uuid FK unique), preferred_categories (text[]), interaction_weights (jsonb), updated_at (timestamptz)
6// 4. recommendations_cache: id (uuid PK), user_id (uuid FK), item_ids (uuid[]), algorithm (text), score (numeric[]), generated_at (timestamptz), expires_at (timestamptz)
7// Create IVFFlat index: CREATE INDEX ON items USING ivfflat (embedding vector_cosine_ops) WITH (lists = 100);
8// RLS: users can read all items, read/write their own interactions and preferences.
9// Generate SQL migration and TypeScript types.

1import { NextRequest, NextResponse } from 'next/server'
2import { createClient } from '@supabase/supabase-js'
3
4const supabase = createClient(
5  process.env.SUPABASE_URL!,
6  process.env.SUPABASE_SERVICE_ROLE_KEY!
7)
8
9export async function POST(req: NextRequest) {
10  const { item_id } = await req.json()
11
12  const { data: item } = await supabase
13    .from('items')
14    .select('name, description, category, metadata')
15    .eq('id', item_id)
16    .single()
17
18  if (!item) {
19    return NextResponse.json({ error: 'Item not found' }, { status: 404 })
20  }
21
22  const textToEmbed = `${item.name}. ${item.description}. Category: ${item.category}.`
23
24  const embeddingRes = await fetch('https://api.openai.com/v1/embeddings', {
25    method: 'POST',
26    headers: {
27      Authorization: `Bearer ${process.env.OPENAI_API_KEY}`,
28      'Content-Type': 'application/json',
29    },
30    body: JSON.stringify({
31      model: 'text-embedding-3-small',
32      input: textToEmbed,
33    }),
34  })
35
36  const embeddingData = await embeddingRes.json()
37  const embedding = embeddingData.data[0].embedding
38
39  await supabase
40    .from('items')
41    .update({ embedding })
42    .eq('id', item_id)
43
44  return NextResponse.json({ success: true, dimensions: embedding.length })
45}

1import { NextRequest, NextResponse } from 'next/server'
2import { createClient } from '@supabase/supabase-js'
3
4export const runtime = 'edge'
5
6export async function GET(req: NextRequest) {
7  const supabase = createClient(
8    process.env.SUPABASE_URL!,
9    process.env.SUPABASE_SERVICE_ROLE_KEY!
10  )
11
12  const userId = req.nextUrl.searchParams.get('user_id')
13  const limit = parseInt(req.nextUrl.searchParams.get('limit') ?? '10')
14
15  if (!userId) {
16    return NextResponse.json({ error: 'user_id required' }, { status: 400 })
17  }
18
19  // Check cache first
20  const { data: cached } = await supabase
21    .from('recommendations_cache')
22    .select('item_ids, algorithm')
23    .eq('user_id', userId)
24    .gt('expires_at', new Date().toISOString())
25    .order('generated_at', { ascending: false })
26    .limit(1)
27    .single()
28
29  if (cached) {
30    const { data: items } = await supabase
31      .from('items')
32      .select('id, name, description, category, metadata')
33      .in('id', cached.item_ids.slice(0, limit))
34
35    return NextResponse.json({ items, algorithm: cached.algorithm, cached: true })
36  }
37
38  // Get user's interaction history
39  const { data: interactions } = await supabase
40    .from('user_interactions')
41    .select('item_id, interaction_type, rating')
42    .eq('user_id', userId)
43    .order('created_at', { ascending: false })
44    .limit(50)
45
46  const interactedIds = interactions?.map((i) => i.item_id) ?? []
47
48  let algorithm = 'content-based'
49  let recommendedIds: string[] = []
50
51  if (interactedIds.length >= 5) {
52    // Collaborative: find similar users and recommend their items
53    algorithm = 'collaborative'
54    const { data: recs } = await supabase.rpc('collaborative_recommendations', {
55      target_user_id: userId,
56      exclude_ids: interactedIds,
57      rec_limit: limit,
58    })
59    recommendedIds = recs?.map((r: { item_id: string }) => r.item_id) ?? []
60  }
61
62  if (recommendedIds.length < limit) {
63    // Content-based fallback: find similar items to user's favorites
64    const topItemId = interactedIds[0] ?? null
65    if (topItemId) {
66      const { data: similar } = await supabase.rpc('similar_items', {
67        target_item_id: topItemId,
68        exclude_ids: [...interactedIds, ...recommendedIds],
69        match_count: limit - recommendedIds.length,
70      })
71      recommendedIds.push(...(similar?.map((s: { id: string }) => s.id) ?? []))
72      algorithm = interactedIds.length >= 5 ? 'hybrid' : 'content-based'
73    }
74  }
75
76  // Cache results
77  if (recommendedIds.length > 0) {
78    await supabase.from('recommendations_cache').insert({
79      user_id: userId,
80      item_ids: recommendedIds,
81      algorithm,
82      expires_at: new Date(Date.now() + 3600000).toISOString(),
83    })
84  }
85
86  const { data: items } = await supabase
87    .from('items')
88    .select('id, name, description, category, metadata')
89    .in('id', recommendedIds)
90
91  return NextResponse.json({ items, algorithm, cached: false })
92}

1'use server'
2
3import { createClient } from '@/lib/supabase/server'
4import { revalidatePath } from 'next/cache'
5
6export async function trackInteraction(input: {
7  user_id: string
8  item_id: string
9  interaction_type: 'view' | 'click' | 'purchase' | 'rating'
10  rating?: number
11}) {
12  const supabase = await createClient()
13
14  await supabase.from('user_interactions').insert({
15    user_id: input.user_id,
16    item_id: input.item_id,
17    interaction_type: input.interaction_type,
18    rating: input.rating ?? null,
19  })
20
21  // Invalidate recommendations cache for this user
22  await supabase
23    .from('recommendations_cache')
24    .delete()
25    .eq('user_id', input.user_id)
26
27  revalidatePath('/')
28}

1// Paste this prompt into V0's AI chat:
2// Create Supabase SQL migration with these functions:
3// 1. similar_items(target_item_id uuid, exclude_ids uuid[], match_count int):
4//    SELECT id, name, description, category, 1 - (embedding <=> (SELECT embedding FROM items WHERE id = target_item_id)) as similarity
5//    FROM items
6//    WHERE id != target_item_id AND id != ALL(exclude_ids) AND embedding IS NOT NULL
7//    ORDER BY embedding <=> (SELECT embedding FROM items WHERE id = target_item_id)
8//    LIMIT match_count;
9// 2. collaborative_recommendations(target_user_id uuid, exclude_ids uuid[], rec_limit int):
10//    Find users who interacted with same items as target user,
11//    then return items those similar users interacted with but target user has not,
12//    ranked by number of similar-user interactions.
13// 3. Ensure the IVFFlat index exists on items.embedding column for fast vector search.
14// Also create the API route at app/api/recommendations/user/[userId]/route.ts that returns cached or fresh recommendations.