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-Manyrelationship exists betweenusersandposts. 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
postsrecord. 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
contentfield in thepoststable is treated as raw text and is rendered into HTML on the frontend usingreact-markdown.