Database Guide

1-- Posts table
2create table public.posts (
3  id uuid default gen_random_uuid() primary key,
4  user_id uuid references public.profiles(id) on delete cascade not null,
5  content text not null,
6  created_at timestamp with time zone default timezone('utc'::text, now()) not null,
7  updated_at timestamp with time zone default timezone('utc'::text, now()) not null
8);
9
10-- Comments table
11create table public.comments (
12  id uuid default gen_random_uuid() primary key,
13  post_id uuid references public.posts(id) on delete cascade not null,
14  user_id uuid references public.profiles(id) on delete cascade not null,
15  content text not null,
16  created_at timestamp with time zone default timezone('utc'::text, now()) not null,
17  updated_at timestamp with time zone default timezone('utc'::text, now()) not null
18);
19
20-- Likes table
21create table public.likes (
22  id uuid default gen_random_uuid() primary key,
23  user_id uuid references public.profiles(id) on delete cascade not null,
24  post_id uuid references public.posts(id) on delete cascade not null,
25  created_at timestamp with time zone default timezone('utc'::text, now()) not null,
26  -- Ensure a user can only like a post once
27  unique(user_id, post_id)
28);
29
30-- Enable RLS
31alter table public.posts enable row level security;
32alter table public.comments enable row level security;
33alter table public.likes enable row level security;
34
35-- Update triggers
36create trigger handle_updated_at before update on public.posts
37  for each row execute procedure moddatetime (updated_at);
38
39create trigger handle_updated_at before update on public.comments
40  for each row execute procedure moddatetime (updated_at);

1// Example type definitions
2export type Tables = {
3  profiles: {
4    Row: {
5      id: string
6      username: string
7      full_name: string | null
8      avatar_url: string | null
9      is_admin: boolean
10      created_at: string
11      updated_at: string
12    }
13    Insert: {
14      id: string
15      username: string
16      full_name?: string | null
17      avatar_url?: string | null
18      is_admin?: boolean
19    }
20  }
21  posts: {
22    Row: {
23      id: string
24      user_id: string
25      content: string
26      created_at: string
27      updated_at: string
28    }
29    Insert: {
30      id?: string
31      user_id: string
32      content: string
33    }
34  }
35  comments: {
36    Row: {
37      id: string
38      post_id: string
39      user_id: string
40      content: string
41      created_at: string
42      updated_at: string
43    }
44    Insert: {
45      id?: string
46      post_id: string
47      user_id: string
48      content: string
49    }
50  }
51  likes: {
52    Row: {
53      id: string
54      user_id: string
55      post_id: string
56      created_at: string
57    }
58    Insert: {
59      id?: string
60      user_id: string
61      post_id: string
62    }
63  }
64}

1-- Posts policies
2create policy "Anyone can view posts"
3  on public.posts for select
4  using ( true );
5
6create policy "Authenticated users can create posts"
7  on public.posts for insert
8  with check ( auth.role() = 'authenticated' );
9
10create policy "Users can update their own posts"
11  on public.posts for update
12  using ( auth.uid() = user_id );
13
14create policy "Users can delete their own posts"
15  on public.posts for delete
16  using ( auth.uid() = user_id );
17
18-- Comments policies
19create policy "Anyone can view comments"
20  on public.comments for select
21  using ( true );
22
23create policy "Authenticated users can create comments"
24  on public.comments for insert
25  with check ( auth.role() = 'authenticated' );
26
27create policy "Users can update their own comments"
28  on public.comments for update
29  using ( auth.uid() = user_id );
30
31create policy "Users can delete their own comments"
32  on public.comments for delete
33  using ( auth.uid() = user_id );
34
35-- Likes policies
36create policy "Anyone can view likes"
37  on public.likes for select
38  using ( true );
39
40create policy "Authenticated users can create likes"
41  on public.likes for insert
42  with check ( auth.role() = 'authenticated' );
43
44create policy "Users can delete their own likes"
45  on public.likes for delete
46  using ( auth.uid() = user_id );

1import { createClientComponentClient } from '@supabase/auth-helpers-nextjs'
2import type { Database } from '@/lib/database.types'
3
4export async function getPosts() {
5  const supabase = createClientComponentClient<Database>()
6  
7  const { data: posts, error } = await supabase
8    .from('posts')
9    .select(`
10      id,
11      content,
12      created_at,
13      profiles (
14        id,
15        username,
16        avatar_url
17      ),
18      likes (
19        id,
20        user_id
21      ),
22      comments (
23        id,
24        content,
25        created_at,
26        profiles (
27          id,
28          username,
29          avatar_url
30        )
31      )
32    `)
33    .order('created_at', { ascending: false })
34
35  if (error) {
36    console.error('Error fetching posts:', error)
37    return []
38  }
39
40  return posts
41}
42
43export async function createPost(content: string) {
44  const supabase = createClientComponentClient<Database>()
45  
46  const { data: { session } } = await supabase.auth.getSession()
47  if (!session) return null
48
49  const { data, error } = await supabase
50    .from('posts')
51    .insert({
52      user_id: session.user.id,
53      content,
54    })
55    .select()
56    .single()
57
58  if (error) {
59    console.error('Error creating post:', error)
60    return null
61  }
62
63  return data
64}
65
66export async function toggleLike(postId: string) {
67  const supabase = createClientComponentClient<Database>()
68  
69  const { data: { session } } = await supabase.auth.getSession()
70  if (!session) return null
71
72  const { data: existingLike } = await supabase
73    .from('likes')
74    .select()
75    .match({ user_id: session.user.id, post_id: postId })
76    .single()
77
78  if (existingLike) {
79    const { error } = await supabase
80      .from('likes')
81      .delete()
82      .match({ id: existingLike.id })
83
84    if (error) {
85      console.error('Error removing like:', error)
86      return null
87    }
88
89    return null
90  }
91
92  const { data, error } = await supabase
93    .from('likes')
94    .insert({
95      user_id: session.user.id,
96      post_id: postId,
97    })
98    .select()
99    .single()
100
101  if (error) {
102    console.error('Error adding like:', error)
103    return null
104  }
105
106  return data
107}