Building a secure comment moderation system with Next.js 14 and Supabase

Remember Web 2.0, when comment sections were just SQL injection vulnerabilities waiting to happen and the only moderation was the honor system? Like trickle-down economics, we all knew it wasn't working, but we kept pretending it would somehow fix itself. Fast forward to 2024, and here I am, building yet another comment system. Not because the world needs another one, but because I refuse to embed a third-party solution that's probably mining cryptocurrency in my users' browsers.

Besides, what better way to prove database competency than building something that could absolutely destroy my blog if I get it wrong?

The Database: Where Dreams Meet Reality

-- Users are handled by Supabase auth
CREATE TABLE comments (
  id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
  user_id UUID REFERENCES auth.users(id),
  post_slug TEXT NOT NULL,
  content TEXT NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  parent_id UUID REFERENCES comments(id),
  is_deleted BOOLEAN DEFAULT false,
  status TEXT DEFAULT 'pending'
);

-- Because managing moderators like its 2005 is not going to cut it
CREATE TABLE moderators (
  id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
  user_id UUID REFERENCES auth.users(id) UNIQUE,
  email TEXT UNIQUE NOT NULL
);

Notice how we're using uuid_generate_v4() instead of serial IDs? Because nothing says "I'm up-to-date with database design in 2024" quite like non-sequential primary keys. Also, it makes those "guess the next ID" attacks about as effective as the UN.

Row Level Security: Because Trust Issues Are Healthy

Remember when we used to just SELECT * FROM comments and call it a day? Those were simpler times. Now we have Row Level Security (RLS), because apparently, we don't trust anyone anymore (and rightfully so):

-- Allow anyone to read approved comments
CREATE POLICY "Read comments" ON public.comments
FOR SELECT USING (
  status = 'approved' OR
  auth.uid() IN (SELECT user_id FROM public.moderators)
);

-- Let authenticated users post comments
CREATE POLICY "Insert comments" ON public.comments
FOR INSERT WITH CHECK (
  auth.role() = 'authenticated'
);

-- Only moderators can update status
CREATE POLICY "Update comments" ON public.comments
FOR UPDATE USING (
  auth.uid() IN (SELECT user_id FROM public.moderators)
);

These policies are like bouncers at a club, except they actually check IDs and can't be bribed with crypto.

Data Relationships: It's Complicated

The relationship between users and comments is like any healthy relationship - well-defined and with clear boundaries:

interface Comment {
  id: string
  user_id: string // Foreign key to auth.users
  post_slug: string
  content: string
  created_at: string
  parent_id?: string // Self-referential for threaded comments
  is_deleted: boolean
  status: 'pending' | 'approved' | 'rejected'
  users?: {
    raw_user_meta_data: {
      name?: string
    }
  }
}

Query Optimization: Because We Care About Performance (Sometimes)

When fetching comments, we could just grab everything and let the client sort it out. But unlike major tech companies, we actually care about performance. This query is optimized to only fetch what we need, when we need it:

const { data, error } = await supabase
  .from('comments')
  .select('*, users(raw_user_meta_data)')
  .eq('status', 'pending')
  .order('created_at', { ascending: false })

The Moderation Flow: A Tale of Three States

Here's the full journey from submission to verdict:

First, a comment lands in our database, starting its journey in a pending state:

interface Comment {
  // ... other fields
  status: 'pending' | 'approved' | 'rejected'
  is_deleted: boolean
}

// When a comment is first submitted
const { data, error } = await supabase
  .from('comments')
  .insert([
    {
      user_id: user.id,
      post_slug: postSlug,
      content: newComment,
      status: 'pending', // Everyone starts in purgatory
      is_deleted: false,
    },
  ])
  .select()

As soon as the comment hits the database, our notification system springs into action faster than a tech CEO backpedaling on a bad take:

const postmarkClient = new postmark.ServerClient(POSTMARK_API_TOKEN)

// Notify the moderator (me) that there's work to be done
await postmarkClient.sendEmail({
  From: 'yourfromemailaddress',
  To: MODERATOR_EMAIL,
  Subject: 'New comment pending moderation',
  TextBody: `A new comment is pending moderation:
    Post: ${postSlug}
    Post URL: ${postUrl}
    Content: ${content}
    Moderation link: ${moderationUrl}`,
  MessageStream: 'outbound',
})

Meanwhile, in the public view, the comment exists in a quantum state - both there and not there, like my motivation on Monday mornings:

// Public view only sees approved comments
const { data: visibleComments } = await supabase
  .from('comments')
  .select('*, users(raw_user_meta_data)')
  .eq('status', 'approved')
  .order('created_at', { ascending: false })

The moderation interface shows a different story - all pending comments awaiting judgment:

const fetchPendingComments = useCallback(async () => {
  const { data, error } = await supabase
    .from('comments')
    .select('*, users(raw_user_meta_data)')
    .eq('status', 'pending')
    .order('created_at', { ascending: false })

  if (error) {
    setStatusMessage(`Failed to fetch comments. Error: ${error.message}`)
  } else {
    setPendingComments(data || [])
  }
}, [supabase])

When judgment time comes, the moderateComment function executes the verdict:

async function moderateComment(commentId: string, action: 'approved' | 'rejected') {
  try {
    const { data, error } = await supabase
      .from('comments')
      .update({ status: action })
      .eq('id', commentId)
      .select()

    if (error) throw error

    // Remove from pending list immediately for that sweet, sweet UX
    setPendingComments((prevComments) => prevComments.filter((comment) => comment.id !== commentId))

    setStatusMessage(`Comment ${action} successfully`)

    // Refresh the list in case we missed anything
    await fetchPendingComments()
  } catch (error) {
    console.error('Error moderating comment:', error)
    setStatusMessage(`Failed to ${action} comment. Error: ${error.message}`)
  }
}

Finally, the circle of life completes - approved comments appear on the blog, while rejected ones vanish into the void (or more accurately, stay in the database with a 'rejected' status because we're not barbarians who DELETE data):

// In the blog view
const { data: blogComments } = await supabase
  .from('comments')
  .select(
    `
    *,
    users:user_id (
      raw_user_meta_data
    )
  `
  )
  .eq('post_slug', postSlug)
  .eq('status', 'approved')
  .order('created_at', { ascending: true })

The entire flow runs smoother than a well-oiled machine, assuming your definition of "well-oiled machine" includes getting emails at 3 AM because someone decided that was the perfect time to test your comment system's SQL injection protection. Our moderation system might not be reinventing the wheel, but it's certainly making sure that wheel is properly aligned, balanced, and not accepting any DROP TABLE comments without proper authorization.

TLDR

What started as a simple comment system evolved into an interesting study of database design and security patterns. The implementation showcases several key database considerations:

Table relationships that maintain referential integrity without overcomplicating the schema
Row Level Security policies that actually make sense (a rare sight in the wild)
A moderation system that doesn't require me to be logged into seventeen different services just to approve a comment
Authentication that's secure enough for a personal blog

Key technical decisions included:

Using Supabase's built-in auth system for commenters while implementing a separate admin authentication flow
Implementing RLS policies that allow public reads of approved comments while restricting moderation actions
Structuring the database to handle soft deletes and comment status changes without breaking existing relationships

The end result is a system that's both technically sound and practically useful. The comments table won't win any awards for most complex schema, but it does prove that you can build a secure, efficient database system without sacrificing your principles or your sanity. You can see this system in action below this post.