Supabase Project: Build a Blog CMS with Next.js

-- Enable extensions
create extension if not exists "uuid-ossp";

-- Profiles table extending auth.users
create table profiles (
  id uuid references auth.users on delete cascade primary key,
  username text unique not null,
  full_name text,
  avatar_url text,
  bio text,
  role text default 'reader' check (role in ('reader', 'author', 'admin')),
  created_at timestamp with time zone default now(),
  updated_at timestamp with time zone default now()
);

-- Categories
create table categories (
  id uuid default uuid_generate_v4() primary key,
  name text unique not null,
  slug text unique not null,
  description text,
  created_at timestamp with time zone default now()
);

-- Posts
create table posts (
  id uuid default uuid_generate_v4() primary key,
  title text not null,
  slug text unique not null,
  excerpt text,
  content text not null,
  featured_image text,
  author_id uuid references profiles(id) on delete cascade not null,
  category_id uuid references categories(id) on delete set null,
  status text default 'draft' check (status in ('draft', 'published', 'archived')),
  published_at timestamp with time zone,
  view_count int default 0,
  reading_time_minutes int,
  meta_description text,
  meta_keywords text[],
  created_at timestamp with time zone default now(),
  updated_at timestamp with time zone default now()
);

-- Tags
create table tags (
  id uuid default uuid_generate_v4() primary key,
  name text unique not null,
  slug text unique not null,
  created_at timestamp with time zone default now()
);

-- Post tags junction table
create table post_tags (
  post_id uuid references posts(id) on delete cascade,
  tag_id uuid references tags(id) on delete cascade,
  primary key (post_id, tag_id)
);

-- Comments
create table comments (
  id uuid default uuid_generate_v4() primary key,
  post_id uuid references posts(id) on delete cascade not null,
  author_id uuid references profiles(id) on delete cascade not null,
  parent_id uuid references comments(id) on delete cascade,
  content text not null,
  status text default 'pending' check (status in ('pending', 'approved', 'rejected', 'spam')),
  created_at timestamp with time zone default now(),
  updated_at timestamp with time zone default now()
);

-- Post likes
create table post_likes (
  post_id uuid references posts(id) on delete cascade,
  user_id uuid references profiles(id) on delete cascade,
  created_at timestamp with time zone default now(),
  primary key (post_id, user_id)
);

-- Reading history
create table reading_history (
  id uuid default uuid_generate_v4() primary key,
  user_id uuid references profiles(id) on delete cascade not null,
  post_id uuid references posts(id) on delete cascade not null,
  progress int default 0, -- percentage read
  completed boolean default false,
  last_read_at timestamp with time zone default now(),
  unique(user_id, post_id)
);

-- Indexes for performance
create index idx_posts_author on posts(author_id);
create index idx_posts_category on posts(category_id);
create index idx_posts_status on posts(status);
create index idx_posts_published_at on posts(published_at desc);
create index idx_posts_slug on posts(slug);
create index idx_comments_post on comments(post_id);
create index idx_comments_author on comments(author_id);
create index idx_post_tags_post on post_tags(post_id);
create index idx_post_tags_tag on post_tags(tag_id);

-- Full-text search index
alter table posts add column search_vector tsvector;

create index idx_posts_search on posts using gin(search_vector);

-- Update search vector on insert/update
create or replace function posts_search_update()
returns trigger as $$
begin
  new.search_vector :=
    setweight(to_tsvector('english', coalesce(new.title, '')), 'A') ||
    setweight(to_tsvector('english', coalesce(new.excerpt, '')), 'B') ||
    setweight(to_tsvector('english', coalesce(new.content, '')), 'C');
  return new;
end;
$$ language plpgsql;

create trigger posts_search_update_trigger
  before insert or update on posts
  for each row
  execute function posts_search_update();

-- Update timestamps
create or replace function update_updated_at()
returns trigger as $$
begin
  new.updated_at = now();
  return new;
end;
$$ language plpgsql;

create trigger posts_updated_at
  before update on posts
  for each row
  execute function update_updated_at();

create trigger profiles_updated_at
  before update on profiles
  for each row
  execute function update_updated_at();

create trigger comments_updated_at
  before update on comments
  for each row
  execute function update_updated_at();

-- Enable RLS on all tables
alter table profiles enable row level security;
alter table posts enable row level security;
alter table categories enable row level security;
alter table tags enable row level security;
alter table post_tags enable row level security;
alter table comments enable row level security;
alter table post_likes enable row level security;
alter table reading_history enable row level security;

-- Profiles policies
create policy "Public profiles are viewable by everyone"
  on profiles for select
  using (true);

create policy "Users can update own profile"
  on profiles for update
  using (auth.uid() = id);

-- Posts policies
create policy "Published posts are viewable by everyone"
  on posts for select
  using (status = 'published' or auth.uid() = author_id);

create policy "Authors can create posts"
  on posts for insert
  with check (
    auth.uid() = author_id and
    exists (
      select 1 from profiles
      where id = auth.uid()
      and role in ('author', 'admin')
    )
  );

create policy "Authors can update own posts"
  on posts for update
  using (auth.uid() = author_id);

create policy "Authors can delete own posts"
  on posts for delete
  using (auth.uid() = author_id);

create policy "Admins can do everything with posts"
  on posts for all
  using (
    exists (
      select 1 from profiles
      where id = auth.uid()
      and role = 'admin'
    )
  );

-- Categories policies
create policy "Categories are viewable by everyone"
  on categories for select
  using (true);

create policy "Only admins can manage categories"
  on categories for all
  using (
    exists (
      select 1 from profiles
      where id = auth.uid()
      and role = 'admin'
    )
  );

-- Tags policies (similar to categories)
create policy "Tags are viewable by everyone"
  on tags for select
  using (true);

create policy "Authors can create tags"
  on tags for insert
  with check (
    exists (
      select 1 from profiles
      where id = auth.uid()
      and role in ('author', 'admin')
    )
  );

-- Post tags policies
create policy "Post tags are viewable by everyone"
  on post_tags for select
  using (true);

create policy "Authors can manage tags for their posts"
  on post_tags for all
  using (
    exists (
      select 1 from posts
      where id = post_id
      and author_id = auth.uid()
    )
  );

-- Comments policies
create policy "Approved comments are viewable by everyone"
  on comments for select
  using (
    status = 'approved' or
    author_id = auth.uid() or
    exists (
      select 1 from profiles
      where id = auth.uid()
      and role in ('author', 'admin')
    )
  );

create policy "Authenticated users can create comments"
  on comments for insert
  with check (auth.uid() = author_id);

create policy "Users can update own comments"
  on comments for update
  using (auth.uid() = author_id);

create policy "Users can delete own comments"
  on comments for delete
  using (auth.uid() = author_id);

create policy "Admins can moderate all comments"
  on comments for all
  using (
    exists (
      select 1 from profiles
      where id = auth.uid()
      and role = 'admin'
    )
  );

-- Post likes policies
create policy "Post likes are viewable by everyone"
  on post_likes for select
  using (true);

create policy "Users can manage own likes"
  on post_likes for all
  using (auth.uid() = user_id);

-- Reading history policies
create policy "Users can manage own reading history"
  on reading_history for all
  using (auth.uid() = user_id);

# Create Next.js project
npx create-next-app@latest blog-cms --typescript --tailwind --app
cd blog-cms

# Install dependencies
npm install @supabase/supabase-js @supabase/ssr
npm install react-markdown remark-gfm
npm install slugify
npm install @tiptap/react @tiptap/starter-kit # Rich text editor
npm install date-fns
npm install zustand # State management

# Project structure
blog-cms/
  app/
    (auth)/
      login/
        page.tsx
      register/
        page.tsx
    (admin)/
      dashboard/
        page.tsx
      posts/
        page.tsx
        new/
          page.tsx
        [slug]/
          edit/
            page.tsx
      comments/
        page.tsx
    (public)/
      page.tsx              # Homepage
      blog/
        [slug]/
          page.tsx          # Single post
        category/
          [slug]/
            page.tsx
        tag/
          [slug]/
            page.tsx
      search/
        page.tsx
    api/
      posts/
        route.ts
      comments/
        route.ts
  components/
    PostCard.tsx
    PostEditor.tsx
    CommentSection.tsx
    SearchBar.tsx
    AdminSidebar.tsx
  lib/
    supabase/
      client.ts
      server.ts
      middleware.ts
    utils.ts
  types/
    database.types.ts
  .env.local
  next.config.js

# .env.local
NEXT_PUBLIC_SUPABASE_URL=your-project-url
NEXT_PUBLIC_SUPABASE_ANON_KEY=your-anon-key
SUPABASE_SERVICE_ROLE_KEY=your-service-role-key

// lib/supabase/client.ts
import { createBrowserClient } from '@supabase/ssr'
import { Database } from '@/types/database.types'

export function createClient() {
  return createBrowserClient<Database>(
    process.env.NEXT_PUBLIC_SUPABASE_URL!,
    process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!
  )
}

// lib/supabase/server.ts
import { createServerClient, type CookieOptions } from '@supabase/ssr'
import { cookies } from 'next/headers'
import { Database } from '@/types/database.types'

export function createServerSupabaseClient() {
  const cookieStore = cookies()

  return createServerClient<Database>(
    process.env.NEXT_PUBLIC_SUPABASE_URL!,
    process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!,
    {
      cookies: {
        get(name: string) {
          return cookieStore.get(name)?.value
        },
        set(name: string, value: string, options: CookieOptions) {
          cookieStore.set({ name, value, ...options })
        },
        remove(name: string, options: CookieOptions) {
          cookieStore.set({ name, value: '', ...options })
        },
      },
    }
  )
}

// lib/supabase/middleware.ts
import { createServerClient, type CookieOptions } from '@supabase/ssr'
import { NextResponse, type NextRequest } from 'next/server'

export async function updateSession(request: NextRequest) {
  let response = NextResponse.next({
    request: {
      headers: request.headers,
    },
  })

  const supabase = createServerClient(
    process.env.NEXT_PUBLIC_SUPABASE_URL!,
    process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!,
    {
      cookies: {
        get(name: string) {
          return request.cookies.get(name)?.value
        },
        set(name: string, value: string, options: CookieOptions) {
          request.cookies.set({
            name,
            value,
            ...options,
          })
          response = NextResponse.next({
            request: {
              headers: request.headers,
            },
          })
          response.cookies.set({
            name,
            value,
            ...options,
          })
        },
        remove(name: string, options: CookieOptions) {
          request.cookies.set({
            name,
            value: '',
            ...options,
          })
          response = NextResponse.next({
            request: {
              headers: request.headers,
            },
          })
          response.cookies.set({
            name,
            value: '',
            ...options,
          })
        },
      },
    }
  )

  await supabase.auth.getUser()

  return response
}

// middleware.ts
import { updateSession } from '@/lib/supabase/middleware'
import { NextResponse } from 'next/server'
import type { NextRequest } from 'next/server'

export async function middleware(request: NextRequest) {
  const response = await updateSession(request)

  // Protect admin routes
  if (request.nextUrl.pathname.startsWith('/dashboard')) {
    const supabase = createClient()
    const { data: { user } } = await supabase.auth.getUser()

    if (!user) {
      return NextResponse.redirect(new URL('/login', request.url))
    }

    // Check if user has author/admin role
    const { data: profile } = await supabase
      .from('profiles')
      .select('role')
      .eq('id', user.id)
      .single()

    if (!profile || !['author', 'admin'].includes(profile.role)) {
      return NextResponse.redirect(new URL('/', request.url))
    }
  }

  return response
}

export const config = {
  matcher: [
    '/((?!_next/static|_next/image|favicon.ico|.*\\.(?:svg|png|jpg|jpeg|gif|webp)$).*)',
  ],
}

// app/(admin)/posts/new/page.tsx
'use client'

import { useState } from 'react'
import { useRouter } from 'next/navigation'
import { createClient } from '@/lib/supabase/client'
import slugify from 'slugify'
import PostEditor from '@/components/PostEditor'

export default function NewPostPage() {
  const [title, setTitle] = useState('')
  const [content, setContent] = useState('')
  const [excerpt, setExcerpt] = useState('')
  const [categoryId, setCategoryId] = useState('')
  const [tags, setTags] = useState<string[]>([])
  const [featuredImage, setFeaturedImage] = useState<File | null>(null)
  const [status, setStatus] = useState<'draft' | 'published'>('draft')
  const [loading, setLoading] = useState(false)
  const router = useRouter()
  const supabase = createClient()

  async function handleSubmit(e: React.FormEvent) {
    e.preventDefault()
    setLoading(true)

    try {
      const { data: { user } } = await supabase.auth.getUser()
      if (!user) throw new Error('Not authenticated')

      // Upload featured image if provided
      let featuredImageUrl = null
      if (featuredImage) {
        const fileExt = featuredImage.name.split('.').pop()
        const fileName = `${Date.now()}.${fileExt}`
        const { data, error } = await supabase.storage
          .from('blog-images')
          .upload(`featured/${fileName}`, featuredImage)

        if (error) throw error

        const { data: { publicUrl } } = supabase.storage
          .from('blog-images')
          .getPublicUrl(data.path)

        featuredImageUrl = publicUrl
      }

      // Create post
      const slug = slugify(title, { lower: true, strict: true })
      const readingTime = Math.ceil(content.split(' ').length / 200)

      const { data: post, error: postError } = await supabase
        .from('posts')
        .insert({
          title,
          slug,
          content,
          excerpt,
          featured_image: featuredImageUrl,
          author_id: user.id,
          category_id: categoryId || null,
          status,
          reading_time_minutes: readingTime,
          published_at: status === 'published' ? new Date().toISOString() : null,
        })
        .select()
        .single()

      if (postError) throw postError

      // Add tags
      if (tags.length > 0) {
        const tagIds = await Promise.all(
          tags.map(async (tagName) => {
            const tagSlug = slugify(tagName, { lower: true, strict: true })
            const { data } = await supabase
              .from('tags')
              .upsert({ name: tagName, slug: tagSlug })
              .select()
              .single()
            return data?.id
          })
        )

        await supabase
          .from('post_tags')
          .insert(tagIds.map((tag_id) => ({ post_id: post.id, tag_id })))
      }

      router.push('/dashboard/posts')
    } catch (error) {
      console.error('Error creating post:', error)
      alert('Failed to create post')
    } finally {
      setLoading(false)
    }
  }

  return (
    <div className="max-w-4xl mx-auto p-6">
      <h1 className="text-3xl font-bold mb-6">Create New Post</h1>
      
      <form onSubmit={handleSubmit} className="space-y-6">
        <div>
          <label className="block text-sm font-medium mb-2">Title</label>
          <input
            type="text"
            value={title}
            onChange={(e) => setTitle(e.target.value)}
            required
            className="w-full px-4 py-2 border rounded-lg"
          />
        </div>

        <div>
          <label className="block text-sm font-medium mb-2">Excerpt</label>
          <textarea
            value={excerpt}
            onChange={(e) => setExcerpt(e.target.value)}
            rows={3}
            className="w-full px-4 py-2 border rounded-lg"
          />
        </div>

        <div>
          <label className="block text-sm font-medium mb-2">Content</label>
          <PostEditor value={content} onChange={setContent} />
        </div>

        <div>
          <label className="block text-sm font-medium mb-2">Featured Image</label>
          <input
            type="file"
            accept="image/*"
            onChange={(e) => setFeaturedImage(e.target.files?.[0] || null)}
            className="w-full"
          />
        </div>

        <div className="flex gap-4">
          <button
            type="button"
            onClick={() => setStatus('draft')}
            className="px-6 py-2 border rounded-lg"
          >
            Save as Draft
          </button>
          <button
            type="submit"
            disabled={loading}
            className="px-6 py-2 bg-blue-500 text-white rounded-lg disabled:opacity-50"
          >
            {loading ? 'Publishing...' : 'Publish'}
          </button>
        </div>
      </form>
    </div>
  )
}

// components/PostEditor.tsx
import { useEditor, EditorContent } from '@tiptap/react'
import StarterKit from '@tiptap/starter-kit'

interface PostEditorProps {
  value: string
  onChange: (value: string) => void
}

export default function PostEditor({ value, onChange }: PostEditorProps) {
  const editor = useEditor({
    extensions: [StarterKit],
    content: value,
    onUpdate: ({ editor }) => {
      onChange(editor.getHTML())
    },
  })

  return (
    <div className="border rounded-lg">
      <div className="border-b p-2 flex gap-2">
        <button
          onClick={() => editor?.chain().focus().toggleBold().run()}
          className="px-3 py-1 rounded hover:bg-gray-100"
        >
          <strong>B</strong>
        </button>
        <button
          onClick={() => editor?.chain().focus().toggleItalic().run()}
          className="px-3 py-1 rounded hover:bg-gray-100"
        >
          <em>I</em>
        </button>
        <button
          onClick={() => editor?.chain().focus().toggleHeading({ level: 2 }).run()}
          className="px-3 py-1 rounded hover:bg-gray-100"
        >
          H2
        </button>
      </div>
      <EditorContent editor={editor} className="prose max-w-none p-4" />
    </div>
  )
}

// app/(public)/blog/[slug]/page.tsx
import { createServerSupabaseClient } from '@/lib/supabase/server'
import { notFound } from 'next/navigation'
import ReactMarkdown from 'react-markdown'
import CommentSection from '@/components/CommentSection'
import { format } from 'date-fns'

interface PageProps {
  params: { slug: string }
}

export async function generateStaticParams() {
  const supabase = createServerSupabaseClient()
  const { data: posts } = await supabase
    .from('posts')
    .select('slug')
    .eq('status', 'published')

  return posts?.map((post) => ({ slug: post.slug })) || []
}

export default async function BlogPostPage({ params }: PageProps) {
  const supabase = createServerSupabaseClient()

  // Fetch post with author and category
  const { data: post, error } = await supabase
    .from('posts')
    .select(`
      *,
      author:profiles(*),
      category:categories(*),
      tags:post_tags(tag:tags(*))
    `)
    .eq('slug', params.slug)
    .eq('status', 'published')
    .single()

  if (error || !post) {
    notFound()
  }

  // Increment view count
  await supabase.rpc('increment_post_views', { post_id: post.id })

  // Fetch related posts
  const { data: relatedPosts } = await supabase
    .from('posts')
    .select('id, title, slug, excerpt, featured_image')
    .eq('category_id', post.category_id)
    .neq('id', post.id)
    .eq('status', 'published')
    .limit(3)

  return (
    <article className="max-w-4xl mx-auto px-6 py-12">
      {/* Featured Image */}
      {post.featured_image && (
        <img
          src={post.featured_image}
          alt={post.title}
          className="w-full h-96 object-cover rounded-lg mb-8"
        />
      )}

      {/* Post Header */}
      <header className="mb-8">
        <h1 className="text-4xl font-bold mb-4">{post.title}</h1>
        
        <div className="flex items-center gap-4 text-gray-600">
          <img
            src={post.author.avatar_url || '/default-avatar.png'}
            alt={post.author.full_name}
            className="w-10 h-10 rounded-full"
          />
          <div>
            <p className="font-medium">{post.author.full_name}</p>
            <p className="text-sm">
              {format(new Date(post.published_at), 'MMMM d, yyyy')} · {post.reading_time_minutes} min read
            </p>
          </div>
        </div>

        {/* Category and Tags */}
        <div className="flex gap-2 mt-4">
          {post.category && (
            <span className="px-3 py-1 bg-blue-100 text-blue-800 rounded-full text-sm">
              {post.category.name}
            </span>
          )}
          {post.tags?.map((pt: any) => (
            <span
              key={pt.tag.id}
              className="px-3 py-1 bg-gray-100 text-gray-800 rounded-full text-sm"
            >
              {pt.tag.name}
            </span>
          ))}
        </div>
      </header>

      {/* Post Content */}
      <div className="prose prose-lg max-w-none mb-12">
        <ReactMarkdown>{post.content}</ReactMarkdown>
      </div>

      {/* Comments */}
      <CommentSection postId={post.id} />

      {/* Related Posts */}
      {relatedPosts && relatedPosts.length > 0 && (
        <section className="mt-12">
          <h2 className="text-2xl font-bold mb-6">Related Posts</h2>
          <div className="grid grid-cols-1 md:grid-cols-3 gap-6">
            {relatedPosts.map((relatedPost) => (
              <a
                key={relatedPost.id}
                href={`/blog/${relatedPost.slug}`}
                className="group"
              >
                {relatedPost.featured_image && (
                  <img
                    src={relatedPost.featured_image}
                    alt={relatedPost.title}
                    className="w-full h-48 object-cover rounded-lg mb-3"
                  />
                )}
                <h3 className="font-semibold group-hover:text-blue-600">
                  {relatedPost.title}
                </h3>
                <p className="text-sm text-gray-600 mt-2">
                  {relatedPost.excerpt}
                </p>
              </a>
            ))}
          </div>
        </section>
      )}
    </article>
  )
}

-- Increment view count function
create or replace function increment_post_views(post_id uuid)
returns void as $$
begin
  update posts
  set view_count = view_count + 1
  where id = post_id;
end;
$$ language plpgsql security definer;

// components/CommentSection.tsx
'use client'

import { useState, useEffect } from 'react'
import { createClient } from '@/lib/supabase/client'
import { formatDistanceToNow } from 'date-fns'

interface Comment {
  id: string
  content: string
  created_at: string
  author: {
    full_name: string
    avatar_url: string
  }
}

interface CommentSectionProps {
  postId: string
}

export default function CommentSection({ postId }: CommentSectionProps) {
  const [comments, setComments] = useState<Comment[]>([])
  const [newComment, setNewComment] = useState('')
  const [loading, setLoading] = useState(false)
  const supabase = createClient()

  useEffect(() => {
    loadComments()

    // Subscribe to new comments
    const channel = supabase
      .channel('comments')
      .on(
        'postgres_changes',
        {
          event: 'INSERT',
          schema: 'public',
          table: 'comments',
          filter: `post_id=eq.${postId}`,
        },
        (payload) => {
          loadComments() // Reload to get author info
        }
      )
      .subscribe()

    return () => {
      supabase.removeChannel(channel)
    }
  }, [postId])

  async function loadComments() {
    const { data } = await supabase
      .from('comments')
      .select(`
        *,
        author:profiles(full_name, avatar_url)
      `)
      .eq('post_id', postId)
      .eq('status', 'approved')
      .order('created_at', { ascending: false })

    if (data) setComments(data)
  }

  async function handleSubmit(e: React.FormEvent) {
    e.preventDefault()
    if (!newComment.trim()) return

    setLoading(true)
    try {
      const { data: { user } } = await supabase.auth.getUser()
      if (!user) {
        alert('Please sign in to comment')
        return
      }

      await supabase.from('comments').insert({
        post_id: postId,
        author_id: user.id,
        content: newComment,
        status: 'approved', // Auto-approve or set to 'pending'
      })

      setNewComment('')
    } catch (error) {
      console.error('Error posting comment:', error)
      alert('Failed to post comment')
    } finally {
      setLoading(false)
    }
  }

  return (
    <div className="mt-12">
      <h2 className="text-2xl font-bold mb-6">Comments ({comments.length})</h2>

      {/* Comment Form */}
      <form onSubmit={handleSubmit} className="mb-8">
        <textarea
          value={newComment}
          onChange={(e) => setNewComment(e.target.value)}
          placeholder="Write a comment..."
          className="w-full px-4 py-2 border rounded-lg"
          rows={4}
        />
        <button
          type="submit"
          disabled={loading}
          className="mt-2 px-6 py-2 bg-blue-500 text-white rounded-lg disabled:opacity-50"
        >
          {loading ? 'Posting...' : 'Post Comment'}
        </button>
      </form>

      {/* Comments List */}
      <div className="space-y-6">
        {comments.map((comment) => (
          <div key={comment.id} className="flex gap-4">
            <img
              src={comment.author.avatar_url || '/default-avatar.png'}
              alt={comment.author.full_name}
              className="w-10 h-10 rounded-full"
            />
            <div className="flex-1">
              <div className="bg-gray-50 rounded-lg p-4">
                <p className="font-medium mb-1">{comment.author.full_name}</p>
                <p className="text-gray-700">{comment.content}</p>
              </div>
              <p className="text-sm text-gray-500 mt-2">
                {formatDistanceToNow(new Date(comment.created_at), { addSuffix: true })}
              </p>
            </div>
          </div>
        ))}
      </div>
    </div>
  )
}