How to build your own threaded comment section using Supabase and Postgres functions

I'm assuming you are fairly familiar with Postgres so I won't dive too far into explaining what each bit of SQL does, but if there's anything you're unfamiliar with, the Postgres docs are extremely well written and will help if you look things up.

Table Schemas and Policies

First let's create a table to store user data:

create table profiles (
  user_id uuid not null primary key references auth.users,
  username citext unique check (length(username) between 3 and 60),
  avatar_url text check (avatar_url ~ '^https?://\S+$'),
);

Now the table to store comments:

create table comments (
  comment_id integer primary key generated always as identity,
  user_id uuid default auth.uid() references profiles,
  parent_id integer references comments,
  slug text not null,
  body text not null check (length(body) between 1 and 2000),
  created_at timestamptz not null default now()
);

Here, slug is used to identify what a comment belongs to.

This is a good start, but there's several more things we can do to make this nicer.

Indexing

There are many comments for each slug which makes them a good candidate for indexing, especially since the most common query is likely to be listing comments for each slug.

Since we want nested comments, it will also help to index for finding a list of child comments.

-- for fast look-up by each comment's slug
create index comments_slug_idx on comments (slug);
-- optimize finding by parent comment
create index comments_parent_id_idx on comments (parent_id);

If you wanted to show comments for each user, you might also add an index on user_id, and if you wanted to optimize showing a list of newest comments, you can add an ordered index:

create index comments_user_id_idx on comments (user_id);
create index comments_created_at_idx on comments (created_at desc);

Profile Triggers

When setting up OAuth for Supabase, after a user logs in it will only insert into the auth.users table with a big json blob from the provider. We can extract some data from this and insert it into our profiles table using a trigger:

create function public.create_profile() returns trigger as $$
begin
  insert into public.profiles (user_id, username, avatar_url)
  values (
    new.id,
    new.raw_user_meta_data ->> 'preferred_username',
    new.raw_user_meta_data ->> 'avatar_url'
  );
  return new;
end;
$$ language plpgsql security definer;

create trigger
  create_profile_on_signup
  after insert on auth.users
  for each row
  execute procedure public.create_profile();

Supabase endpoints

When creating a table on Supabase it leverages PostgREST to automatically create REST endpoints for performing select insert update and delete operations.

To list all comments we would perform an HTTP request (for example here using curl):

terminal

export SUPABASE_API='slredvwiommnlttxaegc.supabase.co'
export SUPABASE_KEY='your secret key here'
curl "https://${SUPABASE_API}/rest/v1/comments" -H "apikey: $SUPABASE_KEY"

We can filter by slug by using a query string:

terminal

curl "https://${SUPABASE_API}/rest/v1/comments?slug=eq.hello-world" -H "apikey: $SUPABASE_KEY"

But filtering by slug doesn't give us a nice nested structure we can feed to the UI...

Recursive Functions

It would be perfectly fine to use this as-is, but when requesting the comments list, we only get a table of comments and they aren't threaded and we would have to match up comments to their parent, which is a fair amount of work. Instead, we can write a Postgres function that will recursively find all child comments and return a nested json structure.

create function comment_tree(
  comment_id int
) returns json as $$
  select json_build_object(
    'comment_id', c.comment_id,
    'user', json_build_object(
      'username', u.username,
      'avatar_url', u.avatar_url,
      'user_id', u.user_id
    ),
    'body', body,
    'created_at', c.created_at,
    'children', children
  )
  from comments c
    left join profiles u using(user_id),
    lateral (
      select
        coalesce(json_agg(
          comment_tree(comments.comment_id)
          order by created_at asc
        ), '[]') as children
      from comments
      where parent_id = c.comment_id
    ) as get_children
  where
    c.comment_id = comment_tree.comment_id
$$ language sql stable;

Though this function only returns the children of a given comment. We also need a function that will return comments for a given slug:

create function threaded_comments(
  slug text
) returns json as $$
  select
    coalesce(json_agg(
      comment_tree(comment_id)
      order by c.created_at desc
    ), '[]') as comments
  from comments c
  where c.parent_id is null
    and c.slug = threaded_comments.slug
$$ language sql stable;

Stats

Often when showing a listing of things with comments, we want to display how many comments it has. To get a count of comments on each slug, we can use this function which returns a json object for easy lookup:

create function comments_stats() returns json as $$
  select
    coalesce(json_object(
      array_agg(slug),
      array_agg(count::text)
    ), '{}')
  from (
    select
      slug,
      count(slug)
    from comments
    group by slug
  ) as get_counts
$$ language sql stable;

React UI

First, here's the TypeScript structure of our threaded comments:

interface Comment {
  comment_id: number
  user: {
    username: string
    avatar_url: string
    user_id: string
  }
  body: string
  created_at: string
  children: Comment[]
}

Rendering our comments in React might look something like this:

function CommentCard({
  comment_id,
  user,
  body,
  created_at,
  children,
}: Comment) {
  return (
    <li>
      <div>
        <div>
          <img src={user.avatar_url} alt={user.username} />
          <span>{user.username}</span>
          <time>{new Date(created_at).toLocaleString()}</time>
        </div>
        <div>{body}</div>
      </div>
      {children.length > 0 && (
        <ul>
          {children.map(c => (
            <CommentCard key={c.comment_id} {...c} />
          ))}
        </ul>
      )}
    </li>
  )
}

function CommentList({ slug }: { slug: string }) {
  const { data: comments, error, status } = useCommentsBySlug(slug)
  if (error || comments && !Array.isArray(comments)) {
    return 'There was an error fetching comments.'
  }
  if (status === 'loading') {
    return 'Loading...'
  }
  if (comments == null) {
    return null
  }
  if (comments.length < 1) {
    return 'No comments... yet?'
  }
  return (
    <ul>
      {comments.map(c => (
        <CommentCard key={c.comment_id} {...c} />
      ))}
    </ul>
  )
}

Though we still need to write the hook that retrieves the list of comments in our component. This can be done with useState and useEffect hooks:

import { useState, useEffect } from 'react'

function useCommentsBySlug(slug: string): {
  data: null | Comment[]
  loading: boolean
  error: null | Error
} {
  const [comments, setComments] = useState<null | Comment[]>(null)
  const [loading, setLoading] = useState<boolean>(false)
  const [error, setError] = useState<null | Error>(null)
  useEffect(() => {
    setLoading(true)
    fetch(`https://${process.env.REACT_APP_SUPABASE_API}/rest/v1/threaded_comments?slug=${slug}`)
      .then(res => res.json())
      .then(res => {
        setComments(res)
        setLoading(false)
        setError(null)
      })
      .catch(err => {
        console.error(err)
        setComments(null)
        setLoading(false)
        setError(err)
      })
  }, [slug])
  return { data: comments, loading, error }
}

Though I think it's much simpler and less error-prone to do this with React Query and the Supabase library:

import { useQuery } from '@tanstack/react-query'
import { createClient } from '@supabase/supabase-js'

const supabase = createClient(REACT_APP_SUPABASE_API, REACT_APP_SUPABASE_KEY)

function useCommentsBySlug(slug: string) {
  return useQuery({
    queryKey: ['comments', slug],
    queryFn: async () => {
      const { data, error } = await supabase.rpc<Comment>('threaded_comments', {
        slug,
      })
      if (error) throw error
      return data
    },
  })
}

Real-time Updates

If you want to enable real-time updates for comments, you'll have to enable logical replication on that table by running the following SQL in Supabase:

begin;
  drop publication if exists supabase_realtime;
  create publication supabase_realtime;
commit;
alter publication supabase_realtime add table comments;

Now we can use the supabase library to subscribe to updates, and then use React Query to invalidate the comments whenever the comments table is updated:

import { useEffect } from 'react'
import { createClient } from '@supabase/supabase-js'
import { useQuery, useQueryClient } from '@tanstack/react-query'

function useRealTimeComments(slug: string) {
  const queryClient = useQueryClient()
  useEffect(() => {
    const subscription = supabase
      .from(`comments:slug=eq.${slug}`)
      .on('*', _payload => {
        queryClient.invalidateQueries({ queryKey: ['comments', slug] })
      })
      .subscribe()
    return () => {
      subscription.unsubscribe()
    }
  }, [slug])
}

function useCommentsBySlug(slug: string) {
  useRealTimeComments(slug)
  return useQuery({
    queryKey: ['comments', slug],
    queryFn: async () => {
      const { data, error } = await supabase.rpc<Comment>('threaded_comments', {
        slug,
      })
      if (error) throw error
      return data
    },
  })
}

Authentication

So far I've glossed over the fact that in order to create a comment you have to sign up for an account.

Let's start with a sign-in hook, using the supabase.auth.signIn function.

import { useEffect, useState } from 'react'
import { createClient } from '@supabase/supabase-js'
import { useQuery, useQueryClient, useMutation } from '@tanstack/react-query'
import type { Session, UserCredentials } from '@supabase/supabase-js'

export function useSignIn() {
  return useMutation({
    mutationKey: ['signin'],
    mutationFn: async ({
      scopes,
      captchaToken,
      shouldCreateUser,
      redirectTo,
      ...creds
    }: UserCredentials & {
      redirectTo?: `/${string}`
      shouldCreateUser?: boolean
      scopes?: string
      captchaToken?: string
    }) => {
      const { session, user, error } = await supabase.auth.signIn(creds, {
        scopes,
        captchaToken,
        shouldCreateUser,
        redirectTo: (process.env.NEXT_PUBLIC_URL ?? '').concat(redirectTo ?? '/'),
      })
      if (error) throw error
      return { user, session }
    },
  })
}

In order to properly use the data from our sign in hook, we also need to be able to respond to changes to the auth session:

function useAuth(): Session | null {
  const [session, setSession] = useState<Session | null>(null)
  useEffect(() => {
    setSession(supabase.auth.session())

    const { data: listener } = supabase.auth.onAuthStateChange((_event, session) => {
      setSession(session)
    })
    return () => {
      listener?.unsubscribe()
    }
  }, [])
  return session
}

Now we can finally make hooks that create and delete comments:

function useCreateComment() {
  const queryClient = useQueryClient()
  return useMutation({
    mutationKey: ['new_comment'],
    mutationFn: async (values: { slug: string; body: string; parent_id: number | null }) => {
      const { data, error } = await supabase.from('comments').insert(values)
      if (error) throw error
      return data
    },
  })
}

function useDeleteComment() {
  const queryClient = useQueryClient()
  return useMutation({
    mutationKey: ['delete_comment'],
    mutationFn: async (comment_id: number) => {
      const { data, error } = await supabase.from('comments').delete().eq('comment_id', comment_id)
      if (error) throw error
      return data
    },
    onSuccess(_data, variables) {
      queryClient.invalidateQueries()
    },
  })
}

function useLogout() {
  return useMutation({
    mutationKey: ['logout'],
    mutationFn: async () => {
      await supabase.auth.signOut()
    }
  })
}

Now we have hooks we need to integrate them into our components.

First, let's create the comment form, but only show it when session is set, otherwise it shows a "sign in with github" button.

function CommentSection({ slug }: { slug: string }) {
  const { data: comments, error, status } = useCommentsBySlug(slug)
  const [replyId, setReplyId] = useState<null | number>(null)
  const session = useAuth()
  const signin = useSignIn()
  const newcomment = useCreateComment()
  const logout = useLogout()

  return (
    <>
      {session ? (
        <ReplyForm replyId={replyId} clearReplyId={() => setReplyId(null)} />
      ) : (
        <div>
          <button
            onClick={() => {
              signin.mutate({ provider: 'github', redirectTo: `/${slug}` })
            }}
          >
            sign in with github
          </button>
        </div>
      )}
      <CommentList
        status={status}
        error={error}
        comments={comments}
        sessionUserId={session?.user?.id ?? null}
        setReplyId={setReplyId}
      />
    </>
  )
}

function ReplyForm({ replyId, clearReplyId }: {
  replyId: null | number
  clearReplyId: () => void
}) {
  const formRef = useRef<HTMLFormElement | null>(null)
  return (
    <form
      ref={formRef}
      onReset={clearReplyId}
      onSubmit={ev => {
        ev.preventDefault()
        const values = Object.fromEntries(new FormData(ev.currentTarget))
        if (typeof values.comment !== 'string' || typeof slug !== 'string') return
        newcomment.mutate(
          {
            slug,
            body: values.comment.trim().replace(/\n\n+/, '\n\n'),
            parent_id: values.parent_id,
          },
          {
            onSuccess() {
              formRef.current?.reset()
            },
          }
        )
      }}
    >
      <div>
        <label>
          <div>
            {replyId ? `responding to #${replyId}:` : 'add your comment:'}
          </div>
          <textarea
            rows={3}
            name="comment"
            placeholder="Say something nice!"
            disabled={newcomment.isLoading}
          />
        </label>
      </div>
      <div>
        <button type="submit" disabled={newcomment.isLoading}>
          {newcomment.isLoading ? 'sending...' : 'post'}
        </button>
        <button type="reset">clear</button>
        <button
          onClick={ev => {
            if (ev.shiftKey || confirm('Are you sure you want to log out?')) {
              logout.mutate()
            }
          }}
        >
          log out
        </button>
      </div>
      {replyId && <input type="hidden" name="parent_id" value={replyId} />}
    </form>
  )
}

function CommentList({ comments, status, error, sessionUserId, replyId, setReplyId }: {
  comments: Array<Comment>
  status: "loading" | "error" | "success",
  error: unknown
  sessionUserId: string | null
  replyId: number | null
  setReplyId: (reply: number) => void
}) {
  if (status === 'loading') return 'Loading...'
  if (comments == null) return null
  if (error || !Array.isArray(comments)) {
    return 'There was an error fetching comments.'
  }
  if (comments.length < 1) return 'No comments... yet?'

  return (
    <ul>
      {comments.map(c => (
        <CommentCard
          key={c.comment_id}
          replyId={replyId}
          setReplyId={setReplyId}
          sessionUserId={sessionUserId}
          comment={c}
        />
      ))}
    </ul>
  )
}

Now that a CommentCard has access to the session's user id we can determine when to show a reply button, and let users delete their previous comments.

function CommentCard({
  comment,
  setReplyId,
  sessionUserId,
}: {
  comment: Comment
  setReplyId: (id: number) => void
  sessionUserId: string | null
}) {
  const deleteComment = useDeleteComment()
  return (
    <li>
      <img src={comment.user.avatar_url} alt={comment.user.username} />
      <div>{comment.user.username}</div>
      <div><time>{new Date(comment.created_at).toLocaleString()}</time></div>
      <div>{comment.body}</div>
      {sessionUserId && (
        <div>
          <button onClick={() => setReplyId(comment.comment_id)}>reply</button>
          {sessionUserId === comment.user.user_id && (
            <button onClick={ev => {
              if (ev.shiftKey || confirm('Are you sure you want to delete your comment?')) {
                deleteComment.mutate(comment.comment_id)
              }
            }}>
            delete
            </button>
          )}
        </div>
      )}
      {comment.children.length > 0 && (
        <ul>
          {comment.children.map(c => (
            <CommentCard
              key={c.comment_id}
              comment={c}
              setReplyId={setReplyId}
              sessionUserId={sessionUserId}
            />
          ))}
        </ul>
      )}
    </li>
  )
}

Authorization

The astute reader may notice that any user currently can delete or edit another user's posts by issuing the appropriate request.

Fortunately Postgres Row-Level-Security features will let us write declarative policies to enforce that a user can only delete or edit their own content, while allowing all other users to still read them.

alter table profiles enable row level security;
create policy select_all_profiles
  on profiles for select
  using (true);
create policy insert_own_profile
  on profiles for insert
  with check (user_id = auth.uid());
create policy update_own_profile
  on profiles for update
  using (user_id = auth.uid());
create policy delete_own_profile
  on profiles for delete
  using (user_id = auth.uid());

alter table comments enable row level security;
create policy select_all_comments
  on comments for select
  using (true);
create policy insert_own_comment
  on comments for insert
  with check (user_id = auth.uid());
create policy update_own_comment
  on comments for update
  using (user_id = auth.uid());
create policy delete_own_comment
  on comments for delete
  using (user_id = auth.uid());

And that's a working comment system!

You'll want to implement your own styling, and decide how to implement a UI for editing comments.