How do you implement recommendation systems using pgvector?

-- Schema for a product recommendation system
CREATE TABLE products (
    id          BIGSERIAL PRIMARY KEY,
    name        TEXT,
    category    TEXT,
    price       NUMERIC(10,2),
    in_stock    BOOLEAN DEFAULT TRUE,
    embedding   VECTOR(512)   -- content/collaborative filtering embedding
);

CREATE TABLE user_interactions (
    user_id     BIGINT,
    product_id  BIGINT REFERENCES products(id),
    interacted_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX ON products USING hnsw (embedding vector_cosine_ops);

-- Find products similar to a specific product:
SELECT p.id, p.name, p.category,
       p.embedding <-> target.embedding AS distance
FROM products p,
     (SELECT embedding FROM products WHERE id = 42) AS target
WHERE p.id != 42
  AND p.in_stock = TRUE             -- business filter
  AND p.category = 'electronics'    -- category filter
ORDER BY distance
LIMIT 10;

-- 'Users who liked X also liked' - user-based recommendations:
-- 1. Find products a user interacted with
-- 2. Average their embeddings to get user preference vector
-- 3. Find products closest to that preference vector
WITH user_prefs AS (
    SELECT avg(p.embedding) AS preference_vector
    FROM user_interactions ui
    JOIN products p ON p.id = ui.product_id
    WHERE ui.user_id = 99
)
SELECT p.id, p.name,
       p.embedding <-> (SELECT preference_vector FROM user_prefs) AS distance
FROM products p
WHERE p.in_stock = TRUE
  AND p.id NOT IN (   -- exclude already-seen products
    SELECT product_id FROM user_interactions WHERE user_id = 99
  )
ORDER BY distance
LIMIT 10;