Skip to content

Instantly share code, notes, and snippets.

@sonipranjal
Created April 21, 2023 17:37
Show Gist options
  • Save sonipranjal/8015583e89ecb75ef7361605991d7b6d to your computer and use it in GitHub Desktop.
Save sonipranjal/8015583e89ecb75ef7361605991d7b6d to your computer and use it in GitHub Desktop.
this is the db for ultimate twitter clone
-- Create a table for public profiles
create table profiles (
id uuid references auth.users on delete cascade not null primary key,
updated_at timestamp with time zone default timezone('utc'::text, now()) not null,
username text unique,
full_name text,
constraint username_length check (char_length(username) >= 3)
);
-- Set up Row Level Security (RLS)
-- See https://supabase.com/docs/guides/auth/row-level-security for more details.
alter table profiles
enable row level security;
create policy "Public profiles are viewable by everyone." on profiles
for select using (true);
create policy "Users can insert their own profile." on profiles
for insert with check (auth.uid() = id);
create policy "Users can update own profile." on profiles
for update using (auth.uid() = id);
-- This trigger automatically creates a profile entry when a new user signs up via Supabase Auth.
-- See https://supabase.com/docs/guides/auth/managing-user-data#using-triggers for more details.
create function public.handle_new_user()
returns trigger as $$
begin
insert into public.profiles (id, username)
values (new.id, new.raw_user_meta_data->>'username');
return new;
end;
$$ language plpgsql security definer;
create trigger on_auth_user_created
after insert on auth.users
for each row execute procedure public.handle_new_user();
CREATE TABLE tweets (
id UUID PRIMARY KEY,
text text not null,
profile_id UUID not null,
created_at timestamp with time zone default timezone('utc'::text, now()) not null,
updated_at timestamp with time zone default timezone('utc'::text, now()) not null,
FOREIGN KEY (profile_id) REFERENCES profiles(id) ON DELETE CASCADE
);
CREATE TABLE hashtags (
id UUID PRIMARY KEY,
name text not null
);
CREATE TABLE tweet_hashtag (
tweet_id UUID not null,
hashtag_id UUID not null,
PRIMARY KEY (tweet_id, hashtag_id),
FOREIGN KEY (tweet_id) REFERENCES tweets(id) ON DELETE CASCADE,
FOREIGN KEY (hashtag_id) REFERENCES hashtags(id) ON DELETE CASCADE
);
CREATE TABLE replies (
id UUID PRIMARY KEY,
text text not null,
user_id UUID not null,
tweet_id UUID,
reply_id UUID,
FOREIGN KEY (user_id) REFERENCES profiles(id) ON DELETE CASCADE,
FOREIGN KEY (tweet_id) REFERENCES tweets(id) ON DELETE CASCADE,
FOREIGN KEY (reply_id) REFERENCES replies(id) ON DELETE CASCADE
);
CREATE TABLE "likes" (
id UUID PRIMARY KEY,
user_id UUID not null,
tweet_id UUID not null,
created_at timestamp with time zone default timezone('utc'::text, now()) not null,
CONSTRAINT like_unique UNIQUE (user_id, tweet_id),
FOREIGN KEY (user_id) REFERENCES profiles(id) ON DELETE CASCADE,
FOREIGN KEY (tweet_id) REFERENCES tweets(id) ON DELETE CASCADE
);
CREATE TABLE bookmarks (
id UUID PRIMARY KEY,
user_id UUID,
tweet_id UUID,
created_at timestamp with time zone default timezone('utc'::text, now()) not null,
CONSTRAINT bookmark_unique UNIQUE (user_id, tweet_id),
FOREIGN KEY (user_id) REFERENCES profiles(id) ON DELETE CASCADE,
FOREIGN KEY (tweet_id) REFERENCES tweets(id) ON DELETE CASCADE
);
@syntaxlexx
Copy link

syntaxlexx commented Aug 14, 2023

For consistency naming, the tweets table profile_id column should be renamed to user_id column to reflect the pattern on the other tables.

CREATE TABLE tweets (
  id UUID PRIMARY KEY,
  text text not null,
  user_id UUID not null,
  created_at timestamp with time zone default timezone('utc'::text, now()) not null,
  updated_at timestamp with time zone default timezone('utc'::text, now()) not null,
  FOREIGN KEY (user_id) REFERENCES profiles(id) ON DELETE CASCADE
);

@sonipranjal
Copy link
Author

Yes!

@Smnthjm08
Copy link

we can use prisma right??

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment