Project: Related Articles Recommendation Engine

In this project, we will build a related articles recommendation engine. Given an article ID, the engine queries its embedding vector and retrieves the top 3 most similar articles in the database using Cosine distance, excluding the active article itself.

1. Table Schema Setup

Create an articles table containing article metadata and embedding vectors:

CREATE TABLE articles (
  id BIGSERIAL PRIMARY KEY,
  title TEXT NOT NULL,
  slug TEXT NOT NULL UNIQUE,
  summary TEXT NOT NULL,
  embedding VECTOR(1536)
);

2. Writing the Stored Procedure

Create a database function that retrieves recommendations based on an input article ID:

CREATE OR REPLACE FUNCTION get_related_articles (
  target_article_id INT,
  match_limit INT
)
RETURNS TABLE (
  id BIGINT,
  title TEXT,
  slug TEXT,
  similarity FLOAT
)
LANGUAGE plpgsql
AS $$
DECLARE
  target_embedding VECTOR(1536);
BEGIN
  -- 1. Fetch the embedding vector for the target article
  SELECT embedding INTO target_embedding 
  FROM articles 
  WHERE articles.id = target_article_id;

  -- 2. Return the most similar articles
  RETURN QUERY
  SELECT
    a.id,
    a.title,
    a.slug,
    (1 - (a.embedding <=> target_embedding)) AS similarity
  FROM articles a
  -- Exclude the query article itself
  WHERE a.id != target_article_id
  ORDER BY a.embedding <=> target_embedding ASC
  LIMIT match_limit;
END;
$$;

3. Implementing the React Component

Here is the React UI component displaying recommendations for an active article:

// src/components/RelatedArticles.tsx
import React, { useState, useEffect } from "react";
import { supabase } from "../lib/supabase";

interface RecommendedArticle {
  id: number;
  title: string;
  slug: string;
  similarity: number;
}

export default function RelatedArticles({ articleId }: { articleId: number }) {
  const [recommendations, setRecommendations] = useState<RecommendedArticle[]>([]);
  const [loading, setLoading] = useState(true);

  useEffect(() => {
    fetchRecommendations();
  }, [articleId]);

  async function fetchRecommendations() {
    try {
      setLoading(true);
      const { data, error } = await supabase.rpc("get_related_articles", {
        target_article_id: articleId,
        match_limit: 3, // Fetch top 3 recommendations
      });

      if (error) throw error;
      if (data) setRecommendations(data);
    } catch (err) {
      console.error("Failed to load article recommendations:", err);
    } finally {
      setLoading(false);
    }
  }

  if (loading) return <p className="text-gray-400 text-sm">Loading recommendations...</p>;
  if (recommendations.length === 0) return null;

  return (
    <div className="mt-12 border-t pt-8">
      <h3 className="text-lg font-bold text-gray-950">Recommended Articles</h3>
      <p className="text-gray-500 text-xs mt-1">AI-powered recommendations based on content similarity</p>

      <div className="grid gap-4 sm:grid-cols-3 mt-6">
        {recommendations.map((item) => (
          <a
            key={item.id}
            href={`/blog/${item.slug}`}
            className="block p-4 border rounded-xl hover:border-blue-500 transition hover:shadow-sm bg-white"
          >
            <h4 className="font-semibold text-gray-900 line-clamp-2">{item.title}</h4>
            <p className="text-blue-600 text-xs font-medium mt-2">
              {Math.round(item.similarity * 100)}% match
            </p>
          </a>
        ))}
      </div>
    </div>
  );
}