MyBlog Schema Design and Implementation

Next.jsSupabasePostgresSQL

Published Apr 22, 2025

The schema design and implementation of my blog project.

1. Enumerated Types (ENUM Types)

First, define the enumerated types that will be used in the project to enhance data consistency and readability. Run in Supabase SQL editor:

2. Database Table Structure (Table Schema)

Here are the core database table designs:

Table: profiles (User Information)

Associated with auth.users, stores public information and application-specific data.

Column Name	Data Type	Constraints	Description
`id`	`uuid`	`PRIMARY KEYREFERENCES auth.users(id) ON DELETE CASCADE`	User ID (from`auth.users`)
`username`	`text`	`UNIQUENOT NULL`	Username (ensure uniqueness)
`email`	`text`	`UNIQUENOT NULL`	User email (synced from`auth.users`)
`avatar_url`	`text`	ㅤ	User avatar URL (from OAuth or user upload)
`role`	`public.user_role`	`NOT NULLDEFAULT 'Normal'::public.user_role`	User role (`AdminPlusNormal`)
`created_at`	`timestamptz`	`NOT NULLDEFAULT now()`	Record creation time
`updated_at`	`timestamptz`	`NOT NULLDEFAULT now()`	Record last update time (automatically updated via trigger)

Table: posts (Blog Posts)

Column Name	Data Type	Constraints	Description
`id`	`bigint`	`PRIMARY KEYGENERATED BY DEFAULT AS IDENTITY`	Post ID
`title`	`text`	`NOT NULL`	Post title
`slug`	`text`	`UNIQUENOT NULL`	URL Slug (generated at application layer and ensure uniqueness)
`content`	`text`	ㅤ	Post content (Markdown/HTML)
`excerpt`	`text`	ㅤ	Post excerpt/summary
`cover_image_url`	`text`	ㅤ	Cover image URL (Supabase Storage)
`published_at`	`timestamptz`	`DEFAULT now()`	Publication time
`last_modified_at`	`timestamptz`	`DEFAULT now()`	Last modification time
`visibility`	`public.post_visibility`	`NOT NULLDEFAULT 'Public'::public.post_visibility`	Post visibility (`PublicPlusAdmin`)
`created_at`	`timestamptz`	`NOT NULLDEFAULT now()`	Record creation time
`updated_at`	`timestamptz`	`NOT NULLDEFAULT now()`	Record last update time (automatically updated via trigger)

Table: tags (Tags)

Column Name	Data Type	Constraints	Description
`id`	`bigint`	`PRIMARY KEYGENERATED BY DEFAULT AS IDENTITY`	Tag ID
`name`	`text`	`UNIQUENOT NULL`	Tag name
`created_at`	`timestamptz`	`NOT NULLDEFAULT now()`	Record creation time
`color`	`text`	ㅤ	Used for rendering tag color in application layer

Table: post_tags (Post-Tag Association Table)

Column Name	Data Type	Constraints	Description
`post_id`	`bigint`	`REFERENCES posts(id) ON DELETE CASCADENOT NULL`	Associated post ID
`tag_id`	`bigint`	`REFERENCES tags(id) ON DELETE CASCADENOT NULL`	Associated tag ID
ㅤ	ㅤ	`PRIMARY KEY (post_id, tag_id)`	Composite primary key, ensures uniqueness

Table: projects (Project List)

Column Name	Data Type	Constraints	Description
`id`	`bigint`	`PRIMARY KEYGENERATED BY DEFAULT AS IDENTITY`	Project ID
`name`	`text`	`NOT NULL`	Project name
`description`	`text`	ㅤ	Project description
`project_url`	`text`	ㅤ	Project link
`github_url`	`text`	ㅤ	Project Github
`cover_image_url`	`text`	ㅤ	Project cover image URL
`tech_stack`	`text[]`	ㅤ	Project tech stack (array)
`created_at`	`timestamptz`	`NOT NULLDEFAULT now()`	Record creation time
`updated_at`	`timestamptz`	`NOT NULLDEFAULT now()`	Record last update time (automatically updated via trigger)

Table: guestbook_entries (Guestbook)

Column Name	Data Type	Constraints	Description
`id`	`bigint`	`PRIMARY KEYGENERATED BY DEFAULT AS IDENTITY`	Entry ID
`name`	`text`	`NOT NULL`	Guest name
`email`	`text`	`NOT NULL`	Guest email
`message`	`text`	`NOT NULL`	Message content
`is_read`	`boolean`	`NOT NULLDEFAULT false`	Read status (for management)
`created_at`	`timestamptz`	`NOT NULLDEFAULT now()`	Message time

Table: comments (Post Comments)

Column Name	Data Type	Constraints	Description
`id`	`bigint`	`PRIMARY KEYGENERATED BY DEFAULT AS IDENTITY`	Comment ID
`post_id`	`bigint`	`REFERENCES posts(id) ON DELETE CASCADENOT NULL`	Associated post ID
`user_id`	`uuid`	`REFERENCES profiles(id) ON DELETE CASCADENOT NULL`	User ID who posted the comment
`content`	`text`	`NOT NULL`	Comment content
`created_at`	`timestamptz`	`NOT NULLDEFAULT now()`	Comment creation time
`updated_at`	`timestamptz`	`NOT NULLDEFAULT now()`	Comment last update time (automatically updated via trigger)

3. Automatic Avatar Synchronization (Database Triggers)

Use the following SQL functions and triggers to automatically synchronize avatar URLs and basic information from auth.users to public.profiles after new user registration (especially through OAuth).

4. Slug Generation Strategy

Handle slug generation in the Next.js application layer (Server Action or API Route):

Library: Use libraries like slugify.

Timing: Before creating or updating posts records.

Logic:

Generate slug from title.

Query database to check if slug already exists.

If exists, append random string or number to ensure uniqueness.

Store final unique slug in posts.slug field.

Database constraint: Set UNIQUE constraint on posts.slug column.

5. Comments System Notes

comments table stores article comments.

Links to specific articles via post_id.

Links to logged-in users who posted comments via user_id (profiles table).

No support for child comments (nested comments), simplified structure.

6. Row Level Security (RLS) Policy Overview

RLS is core to Supabase security. Policies must be enabled and configured for tables requiring protection. Here is the basic approach to policy design:

profiles table:

SELECT: Users can read everyone's username and avatar_url. Users can read their own complete profile information (including email, role).
UPDATE: Users can only update their own profile information (may restrict certain fields like role). Admin can update everyone's information.
INSERT/DELETE: Usually handled by triggers, deletion may be restricted to Admin or triggered through account deletion process.

posts table:

SELECT:

Everyone can read posts with visibility = Public.
Users with role Plus or Admin can read posts with visibility = Public or Plus.
Users with role Admin can read all posts (including visibility = Admin).

INSERT, UPDATE, DELETE: Only allowed for users with role Admin.

comments table:

SELECT: Everyone can read all comments.
INSERT: Must be logged-in user (auth.uid() must have corresponding user_id in profiles).
UPDATE: Users can only update their own comments (or disallow updates). Admin can update all comments.
DELETE: Users can only delete their own comments. Admin can delete all comments.

guestbook_entries table:

SELECT: Only Admin can read all entries (protect privacy).
INSERT: Allow anyone (non-logged-in users) to insert entries.
UPDATE: Only Admin can update (is_read status).
DELETE: Only Admin can delete.

projects, tags, post_tags tables:

SELECT: Usually allow everyone to read.
INSERT, UPDATE, DELETE: Only allow Admin operations.

7. Supabase Storage

All image files ...