Database Schema

Blogify utilizes a hybrid data architecture. While the backend service uses MongoDB for high-performance NoSQL storage, the core application state, user authentication, and relational data are managed through Supabase (PostgreSQL).

This section details the structure of the primary data entities used across the platform.

PostgreSQL (Supabase) Schema

The application uses PostgreSQL to maintain relational integrity between authors and their content. Below are the primary tables and their respective structures.

users Table

Stores public profile information linked to Supabase Authentication.

| Column | Type | Description | | :--- | :--- | :--- | | id | uuid | Primary Key. Matches the auth.uid(). | | name | text | Display name of the user. | | email | text | Unique email address. | | profile_picture | text (Optional) | URL to the user's avatar in storage. | | created_at | timestamp | Default set to now(). |

posts Table

Contains the markdown content and metadata for all blog entries.

| Column | Type | Description | | :--- | :--- | :--- | | id | uuid | Primary Key. Unique identifier for the post. | | user_id | uuid | Foreign Key. References users.id. | | title | text | The headline of the post. | | content | text | The body of the post (Markdown format). | | tags | text[] | Array of strings representing post categories. | | image_url | text (Optional) | URL to the featured header image. | | created_at | timestamp | Timestamp of initial publication. | | updated_at | timestamp | Updated automatically on post modification. |

MongoDB Collections

The FastAPI backend leverages MongoDB for document-based storage, primarily used for data aggregation and features requiring flexible schemas.

blogs Collection

In the MongoDB instance, data is stored in a JSON-like format for fast retrieval during feed generation.

{
  "_id": "ObjectId",
  "title": "String",
  "content": "String (Markdown)",
  "author_id": "String (UUID)",
  "tags": ["String"],
  "image_url": "String",
  "created_at": "ISODate",
  "updated_at": "ISODate"
}

Storage Schema

Blogify uses Supabase Storage to handle static assets such as profile pictures and blog images.

images Bucket

• Public Access: Read access is public for image rendering.
• Upload Policy: Authenticated users can upload to specific paths.
• Path Convention: ${Date.now()}-${RandomHash}.${extension}.

Data Flow & Relationships

• User-to-Post: A One-to-Many relationship exists between users and posts. Deleting a user account is typically configured to trigger a cascade or anonymization of posts via Row Level Security (RLS) policies.
• Tagging: Tags are stored as an array of strings within the posts record. This allows for efficient filtering without the overhead of a dedicated many-to-many junction table, optimized for the platform's current scale.
• Markdown: The content field in the posts table is treated as raw text and is rendered into HTML on the frontend using react-markdown.