How to Build a Recommendations Engine with Lovable

1Set up a recommendations engine database in Supabase:
2
3First, enable the vector extension: CREATE EXTENSION IF NOT EXISTS vector;
4
5Tables:
6- items: id, title, description, category (text), tags (text array), image_url, metadata (jsonb), embedding vector(1536), embedded_at (timestamptz), created_at
7- user_interactions: id, user_id, item_id, interaction_type (view|like|save|purchase|skip), weight (float: view=0.1, like=0.5, save=0.7, purchase=1.0, skip=-0.2), created_at
8- user_preferences: id (references auth.users), preference_vector vector(1536), vector_updated_at, interaction_count (int)
9- recommendation_logs: id, user_id, item_id, algorithm (content_based|collaborative|hybrid), similarity_score (float), shown_at, clicked (bool default false)
10
11RLS:
12- items: public SELECT. Service role for INSERT/UPDATE.
13- user_interactions: users can INSERT/SELECT their own rows (user_id = auth.uid())
14- user_preferences: users can SELECT their own row. Service role for INSERT/UPDATE.
15- recommendation_logs: users can INSERT their own logs. Service role for SELECT.
16
17Indexes:
18- CREATE INDEX idx_items_embedding ON items USING hnsw (embedding vector_cosine_ops) WITH (m = 16, ef_construction = 64);
19- CREATE INDEX idx_interactions_user ON user_interactions(user_id, interaction_type, created_at DESC);
20- CREATE INDEX idx_user_preferences ON user_preferences USING hnsw (preference_vector vector_cosine_ops);
21
22Create a Supabase RPC function get_similar_items(p_item_id uuid, p_limit int) that returns items ordered by embedding <=> (SELECT embedding FROM items WHERE id = p_item_id) LIMIT p_limit, excluding the input item itself.
23
24Create a RPC function get_recommendations_for_user(p_user_id uuid, p_limit int) that returns items ordered by embedding <=> (SELECT preference_vector FROM user_preferences WHERE id = p_user_id) LIMIT p_limit.

1// supabase/functions/generate-embeddings/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
11serve(async (req: Request) => {
12  if (req.method === 'OPTIONS') {
13    return new Response('ok', { headers: corsHeaders })
14  }
15
16  const supabase = createClient(
17    Deno.env.get('SUPABASE_URL') ?? '',
18    Deno.env.get('SUPABASE_SERVICE_ROLE_KEY') ?? ''
19  )
20
21  // Fetch items without embeddings (or specific IDs from request body)
22  const body = await req.json().catch(() => ({}))
23  let query = supabase
24    .from('items')
25    .select('id, title, description, tags')
26    .limit(body.limit ?? 50)
27
28  if (body.item_ids?.length) {
29    query = query.in('id', body.item_ids)
30  } else {
31    query = query.is('embedding', null)
32  }
33
34  const { data: items, error } = await query
35  if (error) return new Response(JSON.stringify({ error: error.message }), { status: 500, headers: corsHeaders })
36
37  const processed: string[] = []
38  const failed: string[] = []
39
40  for (const item of items ?? []) {
41    try {
42      const inputText = [
43        item.title,
44        item.description,
45        (item.tags ?? []).join(' '),
46      ].filter(Boolean).join('. ')
47
48      const embeddingRes = await fetch('https://api.openai.com/v1/embeddings', {
49        method: 'POST',
50        headers: {
51          'Authorization': `Bearer ${Deno.env.get('OPENAI_API_KEY')}`,
52          'Content-Type': 'application/json',
53        },
54        body: JSON.stringify({
55          model: 'text-embedding-3-small',
56          input: inputText,
57        }),
58      })
59
60      const embData = await embeddingRes.json()
61      const vector = embData.data?.[0]?.embedding
62
63      if (!vector) throw new Error('No embedding returned')
64
65      await supabase
66        .from('items')
67        .update({ embedding: vector, embedded_at: new Date().toISOString() })
68        .eq('id', item.id)
69
70      processed.push(item.id)
71    } catch {
72      failed.push(item.id)
73    }
74  }
75
76  return new Response(
77    JSON.stringify({ processed: processed.length, failed: failed.length }),
78    { headers: corsHeaders }
79  )
80})

1// supabase/functions/update-user-preferences/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 = { 'Content-Type': 'application/json' }
6
7serve(async (req: Request) => {
8  const body = await req.json().catch(() => ({}))
9  // Process a specific user or all users with recent interactions
10  const userId = body.user_id as string | undefined
11
12  const supabase = createClient(
13    Deno.env.get('SUPABASE_URL') ?? '',
14    Deno.env.get('SUPABASE_SERVICE_ROLE_KEY') ?? ''
15  )
16
17  // Get users to update
18  let userIds: string[] = []
19  if (userId) {
20    userIds = [userId]
21  } else {
22    const since = new Date(Date.now() - 24 * 60 * 60 * 1000).toISOString()
23    const { data } = await supabase
24      .from('user_interactions')
25      .select('user_id')
26      .gte('created_at', since)
27      .not('interaction_type', 'eq', 'skip')
28    userIds = [...new Set((data ?? []).map(r => r.user_id))]
29  }
30
31  const results = { updated: 0, skipped: 0 }
32
33  for (const uid of userIds) {
34    const { data: interactions } = await supabase
35      .from('user_interactions')
36      .select('weight, created_at, items(embedding)')
37      .eq('user_id', uid)
38      .gt('weight', 0)
39      .not('items.embedding', 'is', null)
40      .order('created_at', { ascending: false })
41      .limit(100)
42
43    if (!interactions?.length) { results.skipped++; continue }
44
45    // Weighted average with recency decay
46    const now = Date.now()
47    const VECTOR_DIM = 1536
48    const sumVector = new Array(VECTOR_DIM).fill(0)
49    let totalWeight = 0
50
51    for (const interaction of interactions) {
52      const embedding = (interaction.items as any)?.embedding as number[] | null
53      if (!embedding) continue
54
55      const ageMs = now - new Date(interaction.created_at).getTime()
56      const ageDays = ageMs / (1000 * 60 * 60 * 24)
57      // Exponential decay: interactions from 30 days ago get half the weight
58      const recencyWeight = Math.exp(-ageDays * Math.log(2) / 30)
59      const totalW = interaction.weight * recencyWeight
60
61      for (let i = 0; i < VECTOR_DIM; i++) {
62        sumVector[i] += embedding[i] * totalW
63      }
64      totalWeight += totalW
65    }
66
67    if (totalWeight === 0) { results.skipped++; continue }
68
69    const preferenceVector = sumVector.map(v => v / totalWeight)
70
71    await supabase.from('user_preferences').upsert({
72      id: uid,
73      preference_vector: preferenceVector,
74      vector_updated_at: new Date().toISOString(),
75      interaction_count: interactions.length,
76    }, { onConflict: 'id' })
77
78    results.updated++
79  }
80
81  return new Response(JSON.stringify(results), { headers: corsHeaders })
82})

1Build two pages:
2
31. For You feed at src/pages/ForYouFeed.tsx:
4- Call the get_recommendations_for_user(user.id, 20) Supabase RPC function
5- Render results as a responsive grid of Cards (2 cols mobile, 3 cols tablet, 4 cols desktop)
6- Each Card shows: item image, title, category Badge, a 'Match' Badge showing the similarity score as a percentage (e.g. similarity score 0.89 = '89% Match')
7- Below the match badge, show a reason label based on the item category and user's top interaction category (e.g. 'Because you liked: [category]')
8- Add a Skeleton grid that shows while loading
9- Add an 'Explore' Tab alongside 'For You' that shows random items from categories the user has not interacted with
10- Log a recommendation_logs INSERT whenever an item Card is shown (shown_at = now, algorithm = 'collaborative')
11- Log clicked = true when a Card is clicked
12
132. Related items section for item detail pages:
14- After the main item content, add a 'Similar Items' section
15- Call get_similar_items(item.id, 6) and render as a horizontal ScrollArea of Cards
16- Each card shows image, title, and a 'Similarity: X%' muted text
17- Show 'No similar items yet' if embeddings have not been generated for this item yet

1Build two things:
2
31. Prompt Lovable to create the pg_cron schedule:
4Set up a pg_cron job named 'nightly-preference-update' that runs every night at 3am UTC. It should call the Supabase Edge Function update-user-preferences using the net.http_post function from pg_net extension. Pass no body (process all users with recent interactions). Also set up a second cron job 'embed-new-items' that runs every hour and calls the generate-embeddings Edge Function to process any items with a NULL embedding column.
5
62. Analytics dashboard at src/pages/RecommendationAnalytics.tsx (admin only):
7- Total recommendations shown today (count from recommendation_logs where shown_at >= today)
8- Click-through rate: (clicked = true) / total as a percentage, shown as a large stat Card
9- Coverage: percentage of items table that has a non-null embedding
10- User preference coverage: percentage of auth.users that have a row in user_preferences
11- A Recharts LineChart showing daily CTR over the last 30 days
12- A BarChart showing CTR by algorithm (content_based vs collaborative)
13- Top 10 most recommended items (by count) as a DataTable with recommendation count and CTR columns