table-setup.md

PosgreSQl table setup

Points to remember:-

• There are 7 tables in public schema.
• Make sure your RLS policy is Disabled for all tables.
• You have to create both enums and tables.
• Running the provided codes may create problem in case sensitiveness. Make sure you correct those cases.
• To run the SQL code go to Supabase Dashboard -> YourProject -> SQL Editor
• Make sure you create the ENUM first then the Tables sequentially.
• If your SQL code does not work then please try to create by seeing the images.

Table Name	No. of Columns
Profile	7
Server	7
Channel	7
Member	6
Conversation	4
Message	8
DirectMessage	8

Enums -->

Enum name	Values
ChannelType	[ TEXT, AUDIO, VIDEO ]
MemberRole	[ ADMIN, MODERATOR, GUEST ]

Code to create the enums ->

CREATE TYPE channeltype AS ENUM ('TEXT', 'AUDIO', 'VIDEO');
CREATE TYPE memberrole AS ENUM ('MODERATOR', 'GUEST', 'ADMIN');

1. Table - 'Profile':

Column Name	Data Type	Description
id	text	Primary key
name	text	Username of the user
userId	text
imgUrl	text	profile image of the user
email	text	Email of the user
createdAt	timestamp	Time of creation
updatedAt	timestamp	Last updated time

SQL Code to Create Table

CREATE TABLE
  public.Profile (
    id TEXT PRIMARY KEY DEFAULT gen_random_uuid(),
    userId TEXT,
    name TEXT,
    imgUrl TEXT,
    email TEXT,
    createdAt TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updatedAt TIMESTAMP WITH TIME ZONE DEFAULT now()
  );

2. Table - 'Server':

Column Name	Data Type	Description
id	text	Primary key
name	text	Name of the server
imgUrl	text	Thumbnail
profileId	text	Owner of the server- It is a forign key of Profile(id)
inviteCode	text	Invite link for the server
createdAt	timestamp	Time of creation
updatedAt	timestamp	Last updated time

SQL code to create table

CREATE TABLE
  public.Server (
    id TEXT PRIMARY KEY DEFAULT gen_random_uuid(),
    name TEXT,
    imgUrl TEXT,
    inviteCode TEXT,
    profileId TEXT,
    createdAt TIMESTAMP WITHOUT TIME ZONE default current_timestamp,
    updatedAt TIMESTAMP WITHOUT TIME ZONE default now(),
    FOREIGN KEY (profileId) REFERENCES public.Profile (id)
  );

3. Table - 'Channel':

Column Name	Data Type	Description
id	text	Primary key
name	text	Name of the channel
type	channelType	It is a enum of type channel type
serverId	text	Server id- It is a forign key of Server(id)
profileId	text	Profile id of admin - It is a forign key
createdAt	timestamp	Time of creation
updatedAt	timestamp	Last updated time

SQL code to create table

CREATE TABLE
  Channel (
    id TEXT PRIMARY KEY DEFAULT gen_random_uuid(),
    name text,
    type channeltype,
    serverId text,
    profileId text,
    createdAt TIMESTAMP WITHOUT TIME ZONE default current_timestamp,
    updatedAt TIMESTAMP WITHOUT TIME ZONE default now(),
    FOREIGN KEY (profileId) REFERENCES public.Profile (id),
    FOREIGN KEY (serverId) REFERENCES public.Server (id)
  );

3. Table - 'Member':

Column Name	Data Type	Description
id	text	Primary key
role	memberrole	It is a enum of type member role
serverId	text	Server id- It is a forign key of Server(id)
profileId	text	Profile id of admin - It is a forign key
createdAt	timestamp	Time of creation
updatedAt	timestamp	Last updated time

SQL code to create table

CREATE TABLE
  Member (
    id TEXT PRIMARY KEY DEFAULT gen_random_uuid(),
    role memberrole,
    serverId text,
    profileId text,
    createdAt TIMESTAMP WITHOUT TIME ZONE default current_timestamp,
    updatedAt TIMESTAMP WITHOUT TIME ZONE default now(),
    FOREIGN KEY (profileId) REFERENCES public.Profile (id),
    FOREIGN KEY (serverId) REFERENCES public.Server (id)
  );

3. Table - 'Message':

Column Name	Data Type	Description
id	text	Primary key
content	text	Content of the message
fileUrl	text	Attachment url (nullable)
memberId	text	Member id - It is a foreign key for Member(id)
channelId	text	Channel id - It is a forign key for Channel(id)
deleted	boolean	A boolean value to check if message is deleted
createdAt	timestamp	Time of creation
updatedAt	timestamp	Last updated time

SQL code to create table

CREATE TABLE
  Message (
    id TEXT PRIMARY KEY DEFAULT gen_random_uuid(),
    CONTENT text,
    fileUrl TEXT,
    memberId TEXT ,
    channelId TEXT,
    deleted BOOLEAN,
    createdAt TIMESTAMP WITHOUT TIME ZONE default current_timestamp,
    updatedAt TIMESTAMP WITHOUT TIME ZONE default now(),
    FOREIGN KEY (channelId) REFERENCES public.Channel (id),
    FOREIGN KEY (memberId) REFERENCES public.Member (id)
  );

4. Table - 'Conversation':

Column Name	Data Type	Description
id	text	Primary key
memberOneId	text	Sender Member Id - Foreign key for Member(id)
memberTwoId	text	Receiver Member Id - Foreign key for Member(id)
createdAt	timestamp	Time of creation

SQL code to create Table

CREATE TABLE
  Conversation (
    id TEXT PRIMARY KEY DEFAULT gen_random_uuid(),
    memberOneId TEXT ,
    memberTwoId TEXT,
    createdAt TIMESTAMP WITHOUT TIME ZONE default current_timestamp,
      FOREIGN KEY (memberTwoId) REFERENCES public.Member (id),
      FOREIGN KEY (memberOneId) REFERENCES public.Member (id)
  );

5. Table - 'DirectMessage':

Column Name	Data Type	Description
id	text	Primary key
content	text	Content of the message
fileUrl	text	Attachment url (nullable)
memberId	text	Sender Id - foreign keys for Member(id)
conversationId	text	Converastion Id - Foreign key for Conversation(id)
deleted	boolean	A boolean value to check if message is deleted
createdAt	timestamp	Time of creation
updatedAt	timestamp	Last updated time

CREATE TABLE
  DirectMessage (
    id TEXT PRIMARY KEY DEFAULT gen_random_uuid(),
    CONTENT TEXT,
    fileUrl TEXT,
    memberId TEXT,
    conversationId TEXT,
    deleted BOOLEAN,
    createdAt TIMESTAMP WITHOUT TIME ZONE default current_timestamp,
    updatedAt TIMESTAMP WITHOUT TIME ZONE default now(),
    FOREIGN KEY (memberId) REFERENCES Member (id),
    FOREIGN KEY (conversationId) REFERENCES Conversation (id)
  );