Case Study

Brewhaus

An AI-driven specialty coffee marketplace for home enthusiasts.

The Problem

Home coffee enthusiasts struggle to find beans matched to their taste. Brewhaus profiles flavor with AI and recommends roasts that fit each palate — turning discovery into an interactive experience.

My Role

Built solo with AI-assisted development — SSR storefront, vector search, AI integration, and Dockerized deployment.

Official Website ↗

Highlights

Next.js 15 SSR storefront with gamification and subscription billing
pgvector semantic search powering AI-driven flavor profiling
Claude AI integration for personalized recommendations
Containerized with Docker and served via Cloudflare Tunnels

Stack

Next.js 15TypeScriptPostgreSQLpgvectorClaude AIDocker

Constraints

Self-hosted on a home server — no cloud bill, but also no auto-scaling.
Solo build, so the stack had to favour boring, well-documented pieces over novelty.
AI cost must stay bounded — recommendations had to use embeddings, not a per-request LLM call.

System Architecture

Edge

Cloudflare Tunnel

Application

Next.js 15 SSR

Subscriptions

Gamification

Intelligence

Claude AI

Flavor Profiler

Data

PostgreSQL

pgvector (semantic search)

Runtime

Docker

Key Trade-offs

The decisions worth defending — what I chose, what I turned down, and why.

Recommendation engine

Chose

pgvector similarity on cached embeddings

Rejected

LLM call per recommendation

Vector search is millisecond-fast and effectively free once embeddings are computed; LLMs would have added cost and latency per page view.

Public ingress

Chose

Cloudflare Tunnel

Rejected

Public VPS with open ports

No exposed origin IP, free TLS, and DDoS protection inherited from Cloudflare — much smaller attack surface for a self-hosted box.

Rendering model

Chose

Next.js 15 SSR

Rejected

Pure client-rendered SPA

Product pages need to be indexable and shareable; SSR gives fast first paint and clean OG cards without a separate prerender step.

What I'd Do Differently

An honest retrospective — the stuff I'd change with more time, more users, or a second pass.

1Move embedding generation to a background queue — synchronous calls during admin product writes briefly blocked the UI.
2Add a staging container that mirrors prod data shape so AI tuning doesn't happen against the live DB.
3Track recommendation quality with a click-through metric instead of relying on subjective spot-checks.

Technical Deep-Dive

Architecture, specifications, and implementation details.

03 · Database Schema

Engine: PostgreSQL 16 with pgvector extension
ORM: Prisma 5
Container image: pgvector/pgvector:pg16

#Extensions

-- Enable vector similarity search
CREATE EXTENSION IF NOT EXISTS vector;

-- Enable UUID generation
CREATE EXTENSION IF NOT EXISTS "pgcrypto";

#Entity Relationship Overview

users ──────────┬── accounts          (OAuth providers)
                ├── sessions          (active sessions)
                ├── orders ──────────── order_items ── products
                ├── subscription
                ├── taste_profile     (AI embedding)
                ├── reviews ─────────── products
                ├── xp_events
                └── user_badges ──────── badges

products ───────┬── order_items
                ├── cart_items
                └── reviews

#Tables

##users

Core user account. Stores gamification state (XP, level).

CREATE TABLE users (
  id              TEXT PRIMARY KEY DEFAULT gen_random_uuid()::text,
  name            TEXT,
  email           TEXT UNIQUE NOT NULL,
  email_verified  TIMESTAMPTZ,
  image           TEXT,
  xp              INTEGER NOT NULL DEFAULT 0,
  level           TEXT NOT NULL DEFAULT 'BRONZE',
                  -- BRONZE | SILVER | GOLD | PLATINUM | BLACK
  created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

##products

Full product catalog. Key: embedding column for pgvector similarity search.

CREATE TABLE products (
  id            TEXT PRIMARY KEY DEFAULT gen_random_uuid()::text,
  name          TEXT NOT NULL,
  slug          TEXT UNIQUE NOT NULL,
  description   TEXT NOT NULL,
  price         FLOAT NOT NULL,
  category      TEXT NOT NULL, -- BEANS | EQUIPMENT | ACCESSORIES
  origin        TEXT,
  country       TEXT,
  region        TEXT,
  process       TEXT,
  roast_level   TEXT,          -- LIGHT | MEDIUM_LIGHT | MEDIUM | MEDIUM_DARK | DARK
  altitude      TEXT,
  harvest       TEXT,
  varietal      TEXT,
  cupping_score FLOAT,
  flavor_notes  TEXT[],
  image_url     TEXT,
  in_stock      BOOLEAN NOT NULL DEFAULT TRUE,
  stock_count   INTEGER NOT NULL DEFAULT 100,
  xp_reward     INTEGER NOT NULL DEFAULT 30,
  badge         TEXT,
  featured      BOOLEAN NOT NULL DEFAULT FALSE,
  embedding     vector(1536),  -- OpenAI text-embedding-3-small dimension
  created_at    TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at    TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Index for fast cosine similarity search
CREATE INDEX ON products USING ivfflat (embedding vector_cosine_ops)
  WITH (lists = 100);

-- Standard indexes
CREATE INDEX ON products (category);
CREATE INDEX ON products (featured, in_stock);
CREATE INDEX ON products (slug);

##taste_profiles

User's AI-derived taste profile with vector embedding for product matching.

CREATE TABLE taste_profiles (
  id            TEXT PRIMARY KEY DEFAULT gen_random_uuid()::text,
  user_id       TEXT UNIQUE NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  answers       JSONB NOT NULL,          -- Raw quiz Q&A
  flavor_notes  TEXT[],
  roast_pref    TEXT,
  brew_method   TEXT,
  embedding     vector(1536),            -- Profile embedding for similarity
  updated_at    TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Index for profile-to-product matching
CREATE INDEX ON taste_profiles USING ivfflat (embedding vector_cosine_ops)
  WITH (lists = 100);

##orders & order_items

CREATE TABLE orders (
  id                TEXT PRIMARY KEY DEFAULT gen_random_uuid()::text,
  user_id           TEXT NOT NULL REFERENCES users(id),
  status            TEXT NOT NULL DEFAULT 'PENDING',
                    -- PENDING | PROCESSING | SHIPPED | DELIVERED | CANCELLED
  total             FLOAT NOT NULL,
  stripe_payment_id TEXT,
  xp_awarded        INTEGER NOT NULL DEFAULT 0,
  created_at        TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at        TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE order_items (
  id         TEXT PRIMARY KEY DEFAULT gen_random_uuid()::text,
  order_id   TEXT NOT NULL REFERENCES orders(id),
  product_id TEXT NOT NULL REFERENCES products(id),
  quantity   INTEGER NOT NULL,
  price      FLOAT NOT NULL
);

##subscriptions

CREATE TABLE subscriptions (
  id                      TEXT PRIMARY KEY DEFAULT gen_random_uuid()::text,
  user_id                 TEXT UNIQUE NOT NULL REFERENCES users(id),
  plan                    TEXT NOT NULL,  -- STARTER | CONNOISSEUR | COLLECTIVE
  status                  TEXT NOT NULL DEFAULT 'ACTIVE',
                          -- ACTIVE | PAUSED | CANCELLED | PAST_DUE
  stripe_subscription_id  TEXT UNIQUE NOT NULL,
  stripe_customer_id      TEXT NOT NULL,
  current_period_start    TIMESTAMPTZ NOT NULL,
  current_period_end      TIMESTAMPTZ NOT NULL,
  cancel_at_period_end    BOOLEAN NOT NULL DEFAULT FALSE,
  created_at              TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at              TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

##gamification — xp_events, badges, user_badges

CREATE TABLE xp_events (
  id         TEXT PRIMARY KEY DEFAULT gen_random_uuid()::text,
  user_id    TEXT NOT NULL REFERENCES users(id),
  amount     INTEGER NOT NULL,
  reason     TEXT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE badges (
  id           TEXT PRIMARY KEY DEFAULT gen_random_uuid()::text,
  name         TEXT UNIQUE NOT NULL,
  description  TEXT NOT NULL,
  icon         TEXT NOT NULL,
  xp_required  INTEGER NOT NULL DEFAULT 0
);

CREATE TABLE user_badges (
  id        TEXT PRIMARY KEY DEFAULT gen_random_uuid()::text,
  user_id   TEXT NOT NULL REFERENCES users(id),
  badge_id  TEXT NOT NULL REFERENCES badges(id),
  earned_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  UNIQUE(user_id, badge_id)
);

##reviews & cart_items

CREATE TABLE reviews (
  id         TEXT PRIMARY KEY DEFAULT gen_random_uuid()::text,
  user_id    TEXT NOT NULL REFERENCES users(id),
  product_id TEXT NOT NULL REFERENCES products(id),
  rating     INTEGER NOT NULL CHECK (rating BETWEEN 1 AND 5),
  body       TEXT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  UNIQUE(user_id, product_id)
);

CREATE TABLE cart_items (
  id         TEXT PRIMARY KEY DEFAULT gen_random_uuid()::text,
  session_id TEXT NOT NULL,
  product_id TEXT NOT NULL REFERENCES products(id),
  quantity   INTEGER NOT NULL DEFAULT 1,
  UNIQUE(session_id, product_id)
);

#Key Queries

##Cosine Similarity Product Search

-- Find products most similar to a taste profile embedding
SELECT
  p.id,
  p.name,
  p.price,
  p.origin,
  1 - (p.embedding <=> $1::vector) AS similarity_score
FROM products p
WHERE
  p.in_stock = TRUE
  AND p.category = 'BEANS'
  AND p.embedding IS NOT NULL
ORDER BY p.embedding <=> $1::vector
LIMIT 20;

##XP Leaderboard

SELECT
  u.name,
  u.xp,
  u.level,
  COUNT(ub.id) AS badge_count
FROM users u
LEFT JOIN user_badges ub ON ub.user_id = u.id
GROUP BY u.id
ORDER BY u.xp DESC
LIMIT 50;

##User Dashboard Summary

SELECT
  u.xp,
  u.level,
  COUNT(DISTINCT o.id)  AS order_count,
  COUNT(DISTINCT r.id)  AS review_count,
  COUNT(DISTINCT ub.id) AS badge_count,
  s.plan                AS subscription_plan
FROM users u
LEFT JOIN orders       o  ON o.user_id = u.id AND o.status = 'DELIVERED'
LEFT JOIN reviews      r  ON r.user_id = u.id
LEFT JOIN user_badges  ub ON ub.user_id = u.id
LEFT JOIN subscriptions s ON s.user_id = u.id AND s.status = 'ACTIVE'
WHERE u.id = $1
GROUP BY u.id, u.xp, u.level, s.plan;

#Migration Strategy

Prisma manages all migrations:

# Create new migration
npx prisma migrate dev --name "add_feature_x"

# Apply to production
npx prisma migrate deploy

# Reset (dev only — destroys data)
npx prisma migrate reset

#Backup

See scripts/backup.sh for automated daily backups.

# Manual backup
docker compose exec postgres pg_dump \
  -U brewhaus brewhaus_prod \
  | gzip > backup-$(date +%Y%m%d).sql.gz

Next: 04 — API Specification →

~ End of Document ~