How to Build a Recommendations Engine with Replit

1// Build a recommendations engine with Express and PostgreSQL using Drizzle ORM.
2//
3// Tables:
4// 1. items: id serial primary key, name text not null, category text,
5//    tags text[] (PostgreSQL array), metadata jsonb, created_at timestamp default now()
6// 2. users: id serial primary key, user_id text not null unique,
7//    preferences jsonb, created_at timestamp default now()
8// 3. interactions: id serial primary key, user_id integer references users not null,
9//    item_id integer references items not null, type text not null
10//    (enum: view/click/purchase/rating/wishlist), value numeric (1-5 for ratings),
11//    weight numeric default 1.0, created_at timestamp default now()
12// 4. similarity_scores: id serial primary key, item_a_id integer references items not null,
13//    item_b_id integer references items not null, score numeric not null (0 to 1),
14//    algorithm text default 'cosine', calculated_at timestamp default now(),
15//    unique(item_a_id, item_b_id, algorithm)
16// 5. recommendation_cache: id serial primary key, user_id integer references users not null unique,
17//    recommendations jsonb not null (array of {item_id, score, reason}),
18//    calculated_at timestamp default now()
19//
20// Routes:
21// POST /api/interactions (record interaction — no auth required, just user_id in body)
22// GET /api/recommendations/:userId (get personalized recommendations, uses cache)
23// GET /api/items/:id/similar (get similar items from similarity_scores)
24// GET /api/popular (globally popular items fallback)
25// POST /api/admin/recalculate (trigger similarity matrix rebuild — requires ADMIN_KEY header)
26//
27// Use Replit Auth for admin routes. Bind to 0.0.0.0:3000.

1const express = require('express');
2const { db } = require('../db');
3const { users, interactions } = require('../schema');
4const { eq } = require('drizzle-orm');
5const { withDbRetry } = require('../lib/retryDb');
6
7const router = express.Router();
8
9// Interaction weights — higher = stronger signal
10const WEIGHTS = {
11  view:     0.5,
12  click:    1.0,
13  wishlist: 1.5,
14  rating:   2.0,
15  purchase: 3.0,
16};
17
18router.post('/api/interactions', express.json(), async (req, res) => {
19  const { userId, itemId, type, value } = req.body;
20
21  if (!userId || !itemId || !type) {
22    return res.status(400).json({ error: 'userId, itemId, and type are required' });
23  }
24  if (!WEIGHTS[type]) {
25    return res.status(400).json({ error: `Invalid type. Must be: ${Object.keys(WEIGHTS).join(', ')}` });
26  }
27
28  // Upsert the user record (create if first interaction)
29  await withDbRetry(() =>
30    db.insert(users)
31      .values({ userId: String(userId) })
32      .onConflictDoNothing()
33  );
34
35  // Get internal user ID
36  const [user] = await db.select().from(users)
37    .where(eq(users.userId, String(userId))).limit(1);
38
39  const weight = WEIGHTS[type];
40
41  await withDbRetry(() =>
42    db.insert(interactions).values({
43      userId: user.id,
44      itemId: parseInt(itemId),
45      type,
46      value: value ? parseFloat(value) : null,
47      weight,
48    })
49  );
50
51  return res.json({ recorded: true });
52});
53
54module.exports = router;

1// server/jobs/calculateSimilarity.js — run as a Scheduled Deployment nightly
2const { db } = require('../db');
3const { similarityScores } = require('../schema');
4const { sql } = require('drizzle-orm');
5
6async function calculateSimilarityMatrix() {
7  console.log('[similarity] Starting similarity matrix calculation...');
8
9  // Calculate cosine similarity between all item pairs
10  // that share at least 3 common users (reduces noise)
11  await db.execute(sql`
12    INSERT INTO similarity_scores (item_a_id, item_b_id, score, algorithm, calculated_at)
13    SELECT
14      a.item_id AS item_a_id,
15      b.item_id AS item_b_id,
16      -- Cosine similarity: SUM(wa*wb) / (SQRT(SUM(wa^2)) * SQRT(SUM(wb^2)))
17      SUM(a.weight * b.weight) /
18        (SQRT(SUM(a.weight * a.weight)) * SQRT(SUM(b.weight * b.weight))) AS score,
19      'cosine' AS algorithm,
20      NOW() AS calculated_at
21    FROM interactions a
22    JOIN interactions b
23      ON a.user_id = b.user_id
24      AND a.item_id < b.item_id  -- only compute each pair once
25    GROUP BY a.item_id, b.item_id
26    HAVING COUNT(DISTINCT a.user_id) >= 2  -- at least 2 shared users
27      AND SUM(a.weight * a.weight) > 0
28      AND SUM(b.weight * b.weight) > 0
29    ON CONFLICT (item_a_id, item_b_id, algorithm)
30    DO UPDATE SET
31      score = EXCLUDED.score,
32      calculated_at = EXCLUDED.calculated_at
33  `);
34
35  // Update recommendation cache for all active users
36  const usersResult = await db.execute(sql`SELECT id, user_id FROM users LIMIT 1000`);
37  for (const user of usersResult.rows) {
38    await buildUserRecommendations(user.id, user.user_id);
39  }
40
41  console.log('[similarity] Done.');
42}
43
44async function buildUserRecommendations(internalUserId, externalUserId) {
45  // Get items the user has already interacted with
46  const userItems = await db.execute({
47    sql: 'SELECT DISTINCT item_id FROM interactions WHERE user_id = $1',
48    params: [internalUserId],
49  });
50  const seen = new Set(userItems.rows.map(r => r.item_id));
51
52  if (seen.size === 0) return; // Skip cold-start users
53
54  // Find similar items the user hasn't seen yet
55  const recs = await db.execute({
56    sql: `
57      SELECT
58        CASE WHEN ss.item_a_id = ANY($1::int[]) THEN ss.item_b_id ELSE ss.item_a_id END AS rec_item_id,
59        MAX(ss.score) AS score,
60        MIN(CASE WHEN ss.item_a_id = ANY($1::int[]) THEN ss.item_a_id ELSE ss.item_b_id END) AS because_item_id
61      FROM similarity_scores ss
62      WHERE (ss.item_a_id = ANY($1::int[]) OR ss.item_b_id = ANY($1::int[]))
63        AND NOT (ss.item_a_id = ANY($1::int[]) AND ss.item_b_id = ANY($1::int[]))
64      GROUP BY rec_item_id
65      ORDER BY score DESC
66      LIMIT 20
67    `,
68    params: [Array.from(seen)],
69  });
70
71  const recommendations = recs.rows.map(r => ({
72    item_id: r.rec_item_id,
73    score: parseFloat(r.score),
74    because_item_id: r.because_item_id,
75  }));
76
77  await db.execute({
78    sql: `
79      INSERT INTO recommendation_cache (user_id, recommendations, calculated_at)
80      VALUES ($1, $2, NOW())
81      ON CONFLICT (user_id) DO UPDATE SET
82        recommendations = EXCLUDED.recommendations,
83        calculated_at = EXCLUDED.calculated_at
84    `,
85    params: [internalUserId, JSON.stringify(recommendations)],
86  });
87}
88
89calculateSimilarityMatrix()
90  .then(() => process.exit(0))
91  .catch(err => { console.error('[similarity] Error:', err); process.exit(1); });

1const express = require('express');
2const { db } = require('../db');
3const { users, recommendationCache, items, interactions } = require('../schema');
4const { eq, sql, desc } = require('drizzle-orm');
5
6const router = express.Router();
7
8router.get('/api/recommendations/:userId', async (req, res) => {
9  const { userId } = req.params;
10
11  // Find internal user record
12  const [user] = await db.select().from(users)
13    .where(eq(users.userId, userId)).limit(1);
14
15  if (!user) {
16    // New user — return popular items (cold start)
17    return servePopular(res);
18  }
19
20  // Check cache freshness (accept if calculated in last 24 hours)
21  const [cache] = await db.select().from(recommendationCache)
22    .where(eq(recommendationCache.userId, user.id)).limit(1);
23
24  if (cache && new Date() - new Date(cache.calculatedAt) < 24 * 60 * 60 * 1000) {
25    return res.json({
26      userId,
27      source: 'cache',
28      recommendations: cache.recommendations,
29    });
30  }
31
32  // Cache stale or missing — return popular as fallback until next nightly run
33  return servePopular(res);
34});
35
36async function servePopular(res) {
37  const popular = await db.execute(sql`
38    SELECT item_id, COUNT(*) AS interaction_count, COUNT(DISTINCT user_id) AS user_count
39    FROM interactions
40    GROUP BY item_id
41    ORDER BY user_count DESC, interaction_count DESC
42    LIMIT 10
43  `);
44
45  return res.json({
46    source: 'popular',
47    recommendations: popular.rows.map(r => ({
48      item_id: r.item_id,
49      score: null,
50      reason: 'Popular with other users',
51    })),
52  });
53}
54
55router.get('/api/items/:id/similar', async (req, res) => {
56  const itemId = parseInt(req.params.id);
57  const similar = await db.execute({
58    sql: `
59      SELECT
60        CASE WHEN item_a_id = $1 THEN item_b_id ELSE item_a_id END AS similar_item_id,
61        score
62      FROM similarity_scores
63      WHERE item_a_id = $1 OR item_b_id = $1
64      ORDER BY score DESC
65      LIMIT 8
66    `,
67    params: [itemId],
68  });
69  return res.json({ itemId, similar: similar.rows });
70});
71
72module.exports = router;

1// Ask Agent to build the React frontend with this prompt:
2// Build a React recommendations UI with two components:
3//
4// 1. RecommendationsWidget component:
5//    - Accept userId prop
6//    - Fetch GET /api/recommendations/:userId on mount
7//    - Display a horizontal scrollable row of item cards
8//    - Each card shows: item image (from metadata.image_url), item name, category badge,
9//      match score as percentage (score * 100, rounded)
10//    - If recommendations have because_item_id, show a tooltip on hover:
11//      'Because you interacted with [item name]'
12//    - Show 'Popular picks' heading if source='popular' (cold start state)
13//
14// 2. Admin Dashboard page at /admin/recommendations:
15//    - Require ADMIN_KEY header (store in localStorage)
16//    - Fetch GET /api/admin/recalculate stats: count of items, interactions, similarity pairs
17//    - Show three stat cards: Total Items, Total Interactions, Similarity Pairs Computed
18//    - Show a 'Recalculate Now' button that calls POST /api/admin/recalculate
19//    - Show a heatmap-style similarity matrix: sample 20 random items,
20//      show a 20x20 grid where each cell color = similarity score (white=0, blue=1)
21//    - Show recent calculation timestamp
22//
23// Also add a GET /api/items route that returns items with their recommendation stats
24// (interaction_count, unique_users from joining interactions).