Restructure the db

[ShinichiShi]

currently using the follwing structure in fb

Restructure the db in following manner (suggestion open)

1️⃣ Users Table

Field	Type	Notes
id (PK)	UUID	Supabase auth.users.id FK
username	VARCHAR(50)	Unique
email	VARCHAR(100)	Unique
bio	TEXT	Optional
profile_pic	TEXT (URL)	Optional
created_at	TIMESTAMP	Default: now()

---

2️⃣ Posts Table

Field	Type	Notes
id (PK)	UUID	Unique
user_id (FK)	UUID	References users(id)
content	TEXT	Post body or caption
media_url	TEXT	Optional, media link (image/video)
created_at	TIMESTAMP	Default: now()
updated_at	TIMESTAMP	On update

---

3️⃣ Comments Table

Field	Type	Notes
id (PK)	UUID	Unique
post_id (FK)	UUID	References posts(id)
user_id (FK)	UUID	References users(id)
content	TEXT	Comment body
created_at	TIMESTAMP	Default: now()

---

4️⃣ Reactions Table (Likes/Dislikes)

Field	Type	Notes
id (PK)	UUID	Unique
post_id (FK)	UUID	References posts(id)
user_id (FK)	UUID	References users(id)
reaction_type	ENUM	e.g., LIKE, DISLIKE
created_at	TIMESTAMP	Default: now()

---

5️⃣ Followers Table

Field	Type	Notes
id (PK)	UUID	Unique
follower_id (FK)	UUID	User following
followed_id (FK)	UUID	User being followed
created_at	TIMESTAMP	Default: now()

6️⃣ Notifications Table

Field	Type	Notes
id (PK)	SERIAL	Primary key, auto-increment
user_id (FK)	UUID	References users(id), ON DELETE CASCADE
type	VARCHAR(50)	e.g., 'like', 'comment', 'follow'
actor_id (FK)	UUID	The user who performed the action, references users(id) ON DELETE CASCADE
entity_id	UUID	Can refer to post_id, comment_id, etc. (nullable)
entity_type	VARCHAR(50)	e.g., 'post', 'comment', etc.
message	TEXT	Optional: custom message or summary
created_at	TIMESTAMP	Default: CURRENT_TIMESTAMP
is_read	BOOLEAN	Default: FALSE

gg