[CHORE] Design ERD and Schema for Supabase Integration

[dmp100]

Task Description

현재 Fake Data 구조를 기반으로 Supabase(PostgreSQL)용 데이터베이스 스키마와 ERD를 설계한다. 이후 Spring Boot 백엔드와 통합 시 재사용 가능한 확장형 구조로 설계한다.

Based on the current fake data, design the database schema and ERD for Supabase (PostgreSQL). The schema should be scalable and reusable for later integration with the Spring Boot backend.

Todo List

• Extract entities from Fake Data (Post, User, Tag, etc.)

• Define relationships (1:N, N:M) and constraints

• Design schema fields considering scalability (timestamps, foreign keys)

• Create ERD using PlantUML or Draw.io

• Implement tables and relationships in Supabase Dashboard

• Document schema structure for later API integration

Development Scope

Server (Spring boots)

[coderabbitai]

📝 CodeRabbit Plan Mode

Generate an implementation plan and prompts that you can use with your favorite coding agent.

✅ Successfully generated implementation plan.

💡 Use @coderabbitai <feedback> to replan

Examples

• Example 1
• Example 2

---

🔗 Similar Issues

Related Issues

• [FEAT] Implement Fake Data for Web UI Prototype #9

👤 Suggested Assignees

• dmp100

---

🧪 Issue enrichment is currently in open beta.

You can configure auto-planning by selecting labels in the issue_enrichment configuration.

To disable automatic issue enrichment, add the following to your .coderabbit.yaml:

issue_enrichment:
  auto_enrich:
    enabled: false

💬 Have feedback or questions? Drop into our discord!

[coderabbitai]

Coding Plan

Approach

Ticket Summary

Design and implement a PostgreSQL database schema for Supabase that defines Post, User, and Tag entities with their relationships. Create an ERD and documentation to support future Spring Boot backend integration. Since no fake data structure currently exists in the codebase, establish the foundational entity definitions based on standard blog/social platform patterns.

Observations

Codebase Summary

This is a Kotlin Multiplatform project with a Spring Boot server backend currently configured for H2 in-memory database with JPA/Hibernate and Flyway migration tools. The project has basic Spring Security setup but no domain models, entities, or database tables have been implemented yet. The server is prepared for PostgreSQL migration with Flyway dependencies already configured, though the PostgreSQL driver is currently commented out in favor of H2 for local development.

Assumptions

Assumption 1: Entity field definitions and relationships

Options Considered:

• Create minimal entities with only essential fields (id, name, timestamps)
• Use rich entity models based on standard blog/social platform patterns with comprehensive fields

Chosen Option: Use standard blog/social platform patterns with comprehensive but focused fields

Rationale: This approach provides a realistic foundation that developers expect from similar systems (user profiles, post content with metadata, tagging system) while remaining practical for initial implementation. The web search results confirm these patterns are well-established and include fields like user profiles (username, email, bio), post metadata (title, content, status, counts), and tag systems (name, slug).

Assumption 2: Schema implementation strategy across environments

Options Considered:

• Implement only in Supabase Dashboard, leaving local development without schema
• Create Flyway migrations that work for both H2 and PostgreSQL, maintaining parity across environments
• Switch entirely to PostgreSQL for local development

Chosen Option: Create PostgreSQL-compatible Flyway migrations that work with both H2 (in PostgreSQL compatibility mode) and Supabase

Rationale: This maintains the existing H2-based local development workflow while ensuring schema parity between environments, making it easier for developers to work locally before deploying to Supabase. The project already has Flyway configured, so this aligns with existing infrastructure.

💡 User Tips

Regenerate the plan with different choices with @coderabbitai <feedback>.

Implementation Steps

Phase 1: Define Entity Structure and Relationships

Establish the foundational entity definitions that will serve as the canonical data model across all system layers.

Task 1: Define Core Entity Fields

Document the entity structures for User, Post, Tag, and PostTag with their fields, types, and purposes.

• Define User entity: id (UUID/BIGINT), username (VARCHAR, unique), email (VARCHAR, unique), display_name (VARCHAR), bio (TEXT), profile_image_url (VARCHAR), created_at (TIMESTAMPTZ), updated_at (TIMESTAMPTZ)
• Define Post entity: id (UUID/BIGINT), user_id (FK to users), title (VARCHAR), slug (VARCHAR, unique), content (TEXT), excerpt (VARCHAR), status (ENUM: draft/published/archived), views_count (INT), likes_count (INT), comments_count (INT), created_at (TIMESTAMPTZ), updated_at (TIMESTAMPTZ), published_at (TIMESTAMPTZ nullable)
• Define Tag entity: id (UUID/BIGINT), name (VARCHAR, unique), slug (VARCHAR, unique), description (TEXT nullable), created_at (TIMESTAMPTZ)
• Define PostTag join entity: post_id (FK to posts), tag_id (FK to tags), created_at (TIMESTAMPTZ), composite primary key (post_id, tag_id)

Task 2: Document Entity Relationships and Constraints

Define the relationships between entities and their cardinality.

• Document User → Post relationship as one-to-many (one user authors many posts, enforced via posts.user_id foreign key)
• Document Post ↔ Tag relationship as many-to-many (implemented via post_tags junction table with composite primary key)
• Specify referential integrity constraints: CASCADE on delete for post_tags when post is deleted, RESTRICT on delete for posts when user is deleted (preserve post history)
• Define unique constraints on username, email, post slug, tag name/slug to prevent duplicates

🤖 Prompt for AI agents

Define the foundational data model for User, Post, Tag, and PostTag entities
with their relationships.

Document all entity field definitions:
- User entity with profile fields (id, username, email, display_name, bio,
profile_image_url, timestamps)
- Post entity with content and metadata fields (id, user_id FK, title, slug,
content, excerpt, status enum, count fields, timestamps)
- Tag entity with categorization fields (id, name, slug, description,
created_at)
- PostTag junction table with composite key (post_id FK, tag_id FK, created_at)

Document entity relationships and constraints:
- User to Post: one-to-many relationship via user_id foreign key
- Post to Tag: many-to-many via post_tags junction table
- Set referential integrity: CASCADE delete for post_tags when post deleted,
RESTRICT for posts when user deleted
- Add unique constraints on username, email, post slug, tag name/slug

Phase 2: Design PostgreSQL Schema with Best Practices

Create detailed PostgreSQL schema design incorporating Supabase-specific features and scalability considerations.

Task 1: Design Table Structures with PostgreSQL Types

Define the complete SQL schema using appropriate PostgreSQL data types and constraints.

• Use BIGSERIAL or UUID for primary keys (recommend BIGSERIAL for simpler integration and better performance with B-tree indexes)
• Use TIMESTAMPTZ for all timestamp fields to handle timezone-aware dates
• Use VARCHAR with appropriate length constraints (username 50, email 255, post title 255, slug 255, tag name 100)
• Use TEXT for long content fields (post content, post excerpt, user bio, tag description)
• Use INTEGER for count fields (views_count, likes_count, comments_count) with DEFAULT 0 and NOT NULL
• Define ENUM type for post status: CREATE TYPE post_status AS ENUM ('draft', 'published', 'archived')

Task 2: Define Indexes and Performance Optimizations

Plan indexes to optimize common query patterns while balancing write performance.

• Create index on posts.user_id (for user's posts queries)
• Create index on posts.status (for filtering published posts)
• Create index on posts.created_at (for time-based ordering and pagination)
• Create composite index on post_tags(post_id, tag_id) via primary key
• Create index on post_tags.tag_id (for reverse lookup: posts by tag)
• Create unique indexes on username, email, post slug, tag name, tag slug
• Document that foreign key indexes are essential for PostgreSQL as they're not auto-created

Task 3: Design Supabase RLS Policies

Plan Row Level Security policies to control data access based on authentication state.

• Enable RLS on all tables in public schema
• For users table: SELECT policy allowing auth.uid() = id OR publicly visible fields only; UPDATE policy allowing auth.uid() = id
• For posts table: SELECT policy allowing status='published' OR (status IN ('draft','archived') AND user_id = auth.uid()); INSERT/UPDATE/DELETE policies requiring user_id = auth.uid()
• For tags table: SELECT policy allowing all authenticated users; INSERT/UPDATE/DELETE restricted to admin role (via custom claims)
• For post_tags table: policies matching parent post's permissions (join to posts table in policy)
• Document that service_role bypass should only be used for administrative tasks

Task 4: Create ERD Documentation

Produce a visual representation of the schema using PlantUML.

• Create PlantUML diagram in docs/schema/erd.puml showing all four tables (users, posts, tags, post_tags)
• Include entity attributes with types, primary keys, foreign keys, and cardinality symbols
• Show relationships with proper notation: User --{ Post (one-to-many), Post }--{ Tag via post_tags (many-to-many)
• Add notes for key constraints (unique indexes, enum values, RLS considerations)
• Generate PNG/SVG output for easy viewing

🤖 Prompt for AI agents

Design the complete PostgreSQL schema with Supabase best practices for all
entities.

Define table structures with PostgreSQL types:
- Use BIGSERIAL for primary keys
- Use TIMESTAMPTZ for all timestamp fields
- Apply VARCHAR length constraints (username 50, email 255, title/slug 255, tag
name 100)
- Use TEXT for long content fields
- Use INTEGER with DEFAULT 0 for count fields
- Create post_status ENUM type with values: draft, published, archived

Plan indexing strategy:
- Index posts.user_id, posts.status, posts.created_at
- Composite primary key index on post_tags(post_id, tag_id)
- Index post_tags.tag_id for reverse lookups
- Unique indexes on username, email, post slug, tag name, tag slug

Design RLS policies for Supabase:
- Users table: self-access for authenticated users, public read for visible
fields
- Posts table: public read for published, owner-only for drafts/archived
- Tags table: public read, admin-only write
- PostTags table: inherit permissions from parent post

Create ERD documentation:
- Generate PlantUML diagram in docs/schema/erd.puml with all tables and
relationships
- Show User to Post one-to-many and Post to Tag many-to-many relationships
- Include all attributes, keys, and constraints

Phase 3: Implement Schema in Development and Supabase

Execute the schema design in both local development environment and production Supabase instance.

Task 1: Create Flyway Migration for Local Development

Implement the schema as a versioned Flyway migration script for H2/PostgreSQL compatibility.

• Create server/src/main/resources/db/migration/ directory
• Create V1__initial_schema.sql with PostgreSQL-compatible DDL
• Include CREATE TYPE for post_status enum
• Include CREATE TABLE statements for users, posts, tags, post_tags with all constraints
• Include CREATE INDEX statements for all planned indexes
• Add comments explaining Supabase-specific features that may differ in H2 (RLS, auth functions)
• Test migration against H2 database to verify syntax compatibility

Task 2: Execute Schema Creation in Supabase Dashboard

Deploy the schema to the production Supabase instance.

• Access Supabase Dashboard SQL Editor
• Execute the DDL statements from the Flyway migration (tables, indexes, enums)
• Verify table creation and relationships in Supabase Table Editor
• Test foreign key constraints by attempting invalid inserts
• Verify that RLS is enabled on all tables (default state)

Task 3: Implement RLS Policies in Supabase

Set up Row Level Security policies for access control.

• Create RLS policies for users table (self-access and public read)
• Create RLS policies for posts table (published public read, draft owner-only)
• Create RLS policies for tags table (public read, admin write)
• Create RLS policies for post_tags table (inherit from posts permissions)
• Test policies by making authenticated and unauthenticated requests via Supabase client
• Document any deviations from the planned policies based on testing

🤖 Prompt for AI agents

Implement the database schema in both local development and Supabase production.

Create Flyway migration:
- Create V1__initial_schema.sql in server/src/main/resources/db/migration/
- Add CREATE TYPE statement for post_status enum
- Add CREATE TABLE statements for users, posts, tags, post_tags with all
constraints and foreign keys
- Add CREATE INDEX statements for all planned indexes
- Include comments about Supabase-specific features
- Ensure H2 compatibility

Deploy to Supabase:
- Execute DDL in Supabase SQL Editor
- Verify table creation and foreign key relationships
- Confirm RLS is enabled on all tables

Implement RLS policies:
- Users: self-access UPDATE, public/self SELECT
- Posts: public SELECT for published, owner-only for drafts, owner-only
INSERT/UPDATE/DELETE
- Tags: public read, admin-only write
- PostTags: inherit post permissions
- Test policies with authenticated and unauthenticated requests

Phase 4: Document Schema for API Integration

Create comprehensive documentation to guide future Spring Boot backend development.

Task 1: Create Schema Documentation

Document the complete schema structure with design rationale.

• Create docs/schema/DATABASE_SCHEMA.md describing all tables, fields, types, constraints
• Document the reasoning behind key design decisions (BIGSERIAL vs UUID, TIMESTAMPTZ usage, soft delete approach if implemented)
• Document Supabase-specific features (RLS policies, auth.uid() integration patterns)
• Include migration strategy: Flyway for local H2, direct SQL for Supabase, eventual PostgreSQL driver configuration
• Add section on indexing strategy and performance considerations

Task 2: Provide JPA Entity Mapping Guidance

Document how to implement Spring Boot JPA entities that map to this schema.

• Describe recommended JPA entity structure matching each table
• Document annotation patterns (@entity, @table, @column, @manytoone, @manytomany)
• Explain how to handle ENUM types (use Java enum with @Enumerated)
• Provide guidance on timestamp handling (use Instant or LocalDateTime with appropriate converters)
• Document the post_tags junction table strategy (@jointable annotation pattern)
• Note that entities should be created in server/src/main/kotlin/org/patchnote/patchnote/entity/

Task 3: Document Supabase Connection Configuration

Provide configuration templates for connecting Spring Boot to Supabase.

• Document PostgreSQL JDBC URL format for Supabase connection pooler
• Provide application.yml template with Supabase connection settings (URL, credentials, pool configuration)
• Document environment variable patterns for sensitive credentials (SUPABASE_DB_URL, SUPABASE_DB_PASSWORD)
• Explain the migration path from H2 (comment out H2, uncomment PostgreSQL driver in build.gradle.kts, update datasource configuration)
• Document Flyway configuration to run migrations against Supabase (note that Supabase schema should already exist from manual creation)

🤖 Prompt for AI agents

Create comprehensive documentation for Spring Boot API integration with the
database schema.

Create schema documentation:
- Generate docs/schema/DATABASE_SCHEMA.md with complete table descriptions
- Document all fields, types, constraints, and relationships
- Explain design decisions (BIGSERIAL choice, TIMESTAMPTZ usage, referential
integrity approach)
- Document Supabase RLS policies and auth.uid() patterns
- Describe migration strategy for local H2 and Supabase deployment
- Include indexing strategy and performance considerations

Provide JPA mapping guidance:
- Document JPA entity structure for each table
- Provide annotation examples (@Entity, @Table, @Column, @ManyToOne,
@ManyToMany, @JoinTable)
- Explain ENUM handling with @Enumerated
- Cover timestamp mapping with Instant/LocalDateTime
- Document junction table mapping pattern for post_tags
- Specify entity package location:
server/src/main/kotlin/org/patchnote/patchnote/entity/

Document Supabase connection:
- Provide PostgreSQL JDBC URL format for Supabase
- Create application.yml template with connection settings
- Document environment variable patterns for credentials
- Explain migration from H2 to PostgreSQL driver
- Configure Flyway for Supabase migrations

---

🚀 Next Steps

🤖 All AI agent prompts combined

Task: 1

Define the foundational data model for User, Post, Tag, and PostTag entities
with their relationships.

Document all entity field definitions:
- User entity with profile fields (id, username, email, display_name, bio,
profile_image_url, timestamps)
- Post entity with content and metadata fields (id, user_id FK, title, slug,
content, excerpt, status enum, count fields, timestamps)
- Tag entity with categorization fields (id, name, slug, description,
created_at)
- PostTag junction table with composite key (post_id FK, tag_id FK, created_at)

Document entity relationships and constraints:
- User to Post: one-to-many relationship via user_id foreign key
- Post to Tag: many-to-many via post_tags junction table
- Set referential integrity: CASCADE delete for post_tags when post deleted,
RESTRICT for posts when user deleted
- Add unique constraints on username, email, post slug, tag name/slug
===============================================================================

Task: 2

Design the complete PostgreSQL schema with Supabase best practices for all
entities.

Define table structures with PostgreSQL types:
- Use BIGSERIAL for primary keys
- Use TIMESTAMPTZ for all timestamp fields
- Apply VARCHAR length constraints (username 50, email 255, title/slug 255, tag
name 100)
- Use TEXT for long content fields
- Use INTEGER with DEFAULT 0 for count fields
- Create post_status ENUM type with values: draft, published, archived

Plan indexing strategy:
- Index posts.user_id, posts.status, posts.created_at
- Composite primary key index on post_tags(post_id, tag_id)
- Index post_tags.tag_id for reverse lookups
- Unique indexes on username, email, post slug, tag name, tag slug

Design RLS policies for Supabase:
- Users table: self-access for authenticated users, public read for visible
fields
- Posts table: public read for published, owner-only for drafts/archived
- Tags table: public read, admin-only write
- PostTags table: inherit permissions from parent post

Create ERD documentation:
- Generate PlantUML diagram in docs/schema/erd.puml with all tables and
relationships
- Show User to Post one-to-many and Post to Tag many-to-many relationships
- Include all attributes, keys, and constraints
===============================================================================

Task: 3

Implement the database schema in both local development and Supabase production.

Create Flyway migration:
- Create V1__initial_schema.sql in server/src/main/resources/db/migration/
- Add CREATE TYPE statement for post_status enum
- Add CREATE TABLE statements for users, posts, tags, post_tags with all
constraints and foreign keys
- Add CREATE INDEX statements for all planned indexes
- Include comments about Supabase-specific features
- Ensure H2 compatibility

Deploy to Supabase:
- Execute DDL in Supabase SQL Editor
- Verify table creation and foreign key relationships
- Confirm RLS is enabled on all tables

Implement RLS policies:
- Users: self-access UPDATE, public/self SELECT
- Posts: public SELECT for published, owner-only for drafts, owner-only
INSERT/UPDATE/DELETE
- Tags: public read, admin-only write
- PostTags: inherit post permissions
- Test policies with authenticated and unauthenticated requests
===============================================================================

Task: 4

Create comprehensive documentation for Spring Boot API integration with the
database schema.

Create schema documentation:
- Generate docs/schema/DATABASE_SCHEMA.md with complete table descriptions
- Document all fields, types, constraints, and relationships
- Explain design decisions (BIGSERIAL choice, TIMESTAMPTZ usage, referential
integrity approach)
- Document Supabase RLS policies and auth.uid() patterns
- Describe migration strategy for local H2 and Supabase deployment
- Include indexing strategy and performance considerations

Provide JPA mapping guidance:
- Document JPA entity structure for each table
- Provide annotation examples (@Entity, @Table, @Column, @ManyToOne,
@ManyToMany, @JoinTable)
- Explain ENUM handling with @Enumerated
- Cover timestamp mapping with Instant/LocalDateTime
- Document junction table mapping pattern for post_tags
- Specify entity package location:
server/src/main/kotlin/org/patchnote/patchnote/entity/

Document Supabase connection:
- Provide PostgreSQL JDBC URL format for Supabase
- Create application.yml template with connection settings
- Document environment variable patterns for credentials
- Explain migration from H2 to PostgreSQL driver
- Configure Flyway for Supabase migrations

💡 Iterate on the plan with: @coderabbitai <feedback>

Example Feedback
- @coderabbitai You can skip phase 3. Add a simple unit test case for phase 2.
- @coderabbitai For assumption 1 go ahead with option 3 and replan.

---

💬 Have feedback or questions? Drop into our discord!