# Social Media Platform - Database Architecture Documentation

**Version:** 2.0
**Database:** PostgreSQL 14+
**Date:** January 2026
**Status:** Ready for Review

---

## Table of Contents

1. [Overview](#overview)
2. [Architecture Principles](#architecture-principles)
3. [Naming Conventions](#naming-conventions)
4. [Database Schema](#database-schema)
- [System & Configuration](#1-system--configuration-sys_)
- [Users & Authentication](#2-users--authentication-usr_)
- [Content Creators](#3-content-creators-crt_)
- [File Storage](#4-file-storage-fs_)
- [Posts](#5-posts-cnt_)
- [Reels](#6-reels-cnt_)
- [Stories](#7-stories-cnt_)
- [Hashtags](#8-hashtags-cnt_)
- [Social Features](#9-social-features-usr_)
5. [Entity Relationships](#entity-relationships)
6. [Key Design Decisions](#key-design-decisions)
7. [Security & Access Control](#security--access-control)
8. [Scalability Considerations](#scalability-considerations)
9. [Data Integrity](#data-integrity)
10. [Future Extensions](#future-extensions)

---

## Overview

This database schema supports a comprehensive social media platform with three distinct user roles: **Users**, **Content Creators**, and **Admins**. The platform enables content creation (posts, reels, and stories), user engagement (likes, comments, follows), hashtag discovery, and comprehensive admin moderation capabilities.

### Key Features

- ✅ Role-based access control (RBAC) with granular permissions
- ✅ Individual and company content creator profiles
- ✅ Mixed-content posts (text, images, videos, or combinations)
- ✅ Instagram-style reels with video content
- ✅ 24-hour ephemeral stories
- ✅ Hashtag system for content discovery
- ✅ Follow/following social graph
- ✅ Engagement tracking (likes, comments, views)
- ✅ Storage-agnostic file system (MinIO, S3, local)
- ✅ Admin moderation and audit logging
- ✅ Soft deletes for content recovery

### External Dependencies

- **Analytics**: Firebase/PostHog (view tracking, watch time, engagement metrics)
- **File Storage**: MinIO (primary), S3, or local filesystem

---

## Architecture Principles

### 1. **Separation of Concerns**
Tables are grouped by functional domain using prefixes:
- `sys_` → System configuration and security
- `usr_` → User accounts and social features
- `crt_` → Content creator profiles
- `cnt_` → Content and interactions
- `fs_` → File storage

### 2. **Referential Integrity**
All relationships use foreign keys with explicit cascade behaviors. No polymorphic relationships—each content type has dedicated tables.

### 3. **Performance Optimization**
- Denormalized engagement counts (likes, comments, views) on content tables
- Denormalized follower/following counts on user profiles
- Strategic indexes on frequently queried columns
- Soft deletes to preserve data integrity

### 4. **Audit Trail**
- `created_at` and `updated_at` timestamps on all tables
- Admin action logging in `sys_audit_log`
- Creator status history tracking

### 5. **Extensibility**
- JSONB columns for flexible metadata
- Storage-agnostic file system design
- Permission-based RBAC for easy role expansion

---

## Naming Conventions

### Tables
- **Prefix-based grouping**: `<domain>_<entity_name>`
- **snake_case**: All lowercase with underscores
- **Plural or singular**: Context-dependent (e.g., `usr_users`, `fs_files`)

### Columns
- **snake_case**: All lowercase with underscores
- **Descriptive names**: `creator_id`, `video_file_id`, `like_count`
- **Timestamp suffix**: `created_at`, `updated_at`, `deleted_at`, `expires_at`

### Indexes
- **Pattern**: `idx_<table>_<column(s)>`
- **Example**: `idx_cnt_posts_creator`

### Foreign Keys
- **Pattern**: `fk_<table>_<reference>`
- **Example**: `fk_usr_users_profile_picture`

---

## Database Schema

### 1. System & Configuration (`sys_`)

#### `sys_parameters`
**Purpose**: Global system configuration key-value store.

| Column | Type | Constraints | Description |
|--------|------|-------------|-------------|
| `id` | `SERIAL` | `PRIMARY KEY` | Auto-increment ID |
| `key` | `VARCHAR(100)` | `UNIQUE NOT NULL` | Configuration key |
| `value` | `TEXT` | `NOT NULL` | Configuration value |
| `description` | `TEXT` | `NULL` | Human-readable description |
| `created_at` | `TIMESTAMP` | `DEFAULT NOW()` | Creation timestamp |
| `updated_at` | `TIMESTAMP` | `DEFAULT NOW()` | Auto-updated on change |

**Example Data**:

key: "max_file_size_mb", value: "500"
key: "story_duration_hours", value: "24"
key: "max_hashtags_per_post", value: "30"

---

#### `sys_permissions`
**Purpose**: Defines granular permissions for RBAC system.

| Column | Type | Constraints | Description |
|--------|------|-------------|-------------|
| `id` | `SERIAL` | `PRIMARY KEY` | Permission ID |
| `name` | `VARCHAR(100)` | `UNIQUE NOT NULL` | Permission name (e.g., "create_post") |
| `resource` | `VARCHAR(50)` | `NOT NULL` | Resource type (e.g., "posts", "users") |
| `action` | `VARCHAR(50)` | `NOT NULL` | Action type (e.g., "create", "moderate") |
| `description` | `TEXT` | `NULL` | Permission description |
| `created_at` | `TIMESTAMP` | `DEFAULT NOW()` | Creation timestamp |

**Index**: `idx_sys_permissions_resource` on `(resource, action)`

**Example Permissions**:
- `view_content` (resource: content, action: read)
- `create_story` (resource: stories, action: create)
- `moderate_users` (resource: users, action: moderate)

---

#### `sys_role_permissions`
**Purpose**: Maps permissions to user roles (user, content_creator, admin).

| Column | Type | Constraints | Description |
|--------|------|-------------|-------------|
| `role` | `VARCHAR(20)` | `CHECK IN (...)` | Role: user, content_creator, admin |
| `permission_id` | `INTEGER` | `FK → sys_permissions(id)` | Permission reference |
| `created_at` | `TIMESTAMP` | `DEFAULT NOW()` | Mapping creation time |

**Primary Key**: `(role, permission_id)`

**Permission Inheritance**:
- **User**: View, like, comment on content, follow users
- **Content Creator**: User permissions + create/edit posts/reels/stories
- **Admin**: All permissions (full platform access)

---

#### `sys_audit_log`
**Purpose**: Tracks admin actions and critical system operations.

| Column | Type | Constraints | Description |
|--------|------|-------------|-------------|
| `id` | `BIGSERIAL` | `PRIMARY KEY` | Log entry ID |
| `user_id` | `BIGINT` | `FK → usr_users(id)` | User who performed action |
| `action` | `VARCHAR(100)` | `NOT NULL` | Action name (e.g., "ban_user") |
| `resource_type` | `VARCHAR(50)` | `NOT NULL` | Resource affected (e.g., "creators") |
| `resource_id` | `BIGINT` | `NULL` | ID of affected resource |
| `changes` | `JSONB` | `NULL` | Before/after values |
| `ip_address` | `INET` | `NULL` | User's IP address |
| `user_agent` | `TEXT` | `NULL` | Browser/client info |
| `created_at` | `TIMESTAMP` | `DEFAULT NOW()` | Action timestamp |

**Indexes**:
- `idx_sys_audit_log_user` on `(user_id, created_at DESC)`
- `idx_sys_audit_log_resource` on `(resource_type, resource_id, created_at DESC)`

---

### 2. Users & Authentication (`usr_`)

#### `usr_users`
**Purpose**: Core user accounts for all platform members.

| Column | Type | Constraints | Description |
|--------|------|-------------|-------------|
| `id` | `BIGSERIAL` | `PRIMARY KEY` | User ID |
| `username` | `VARCHAR(50)` | `UNIQUE NOT NULL` | Unique username |
| `email` | `VARCHAR(255)` | `UNIQUE NOT NULL` | Email address |
| `password_hash` | `VARCHAR(255)` | `NOT NULL` | Hashed password |
| `role` | `VARCHAR(20)` | `CHECK IN (...)` | Role: user, content_creator, admin |
| `is_active` | `BOOLEAN` | `DEFAULT TRUE` | Account active status |
| `profile_picture_id` | `BIGINT` | `FK → fs_files(id)` | Profile picture reference |
| `bio` | `TEXT` | `NULL` | User biography |
| `follower_count` | `INTEGER` | `DEFAULT 0` | Total followers (denormalized) |
| `following_count` | `INTEGER` | `DEFAULT 0` | Total following (denormalized) |
| `last_login_at` | `TIMESTAMP` | `NULL` | Last login timestamp |
| `last_login_ip` | `INET` | `NULL` | Last login IP address |
| `created_at` | `TIMESTAMP` | `DEFAULT NOW()` | Account creation date |
| `updated_at` | `TIMESTAMP` | `DEFAULT NOW()` | Last update timestamp |
| `deleted_at` | `TIMESTAMP` | `NULL` | Soft delete timestamp |
| `deleted_by` | `BIGINT` | `FK → usr_users(id)` | Admin who deleted account |

**Indexes**:
- `idx_usr_users_email` on `(email)` where `deleted_at IS NULL`
- `idx_usr_users_username` on `(username)` where `deleted_at IS NULL`
- `idx_usr_users_role` on `(role)` where `is_active = TRUE`
- `idx_usr_users_followers` on `(follower_count DESC)` where `deleted_at IS NULL`

---

### 3. Content Creators (`crt_`)

#### `crt_creators`
**Purpose**: Extended profile for users who create content.

| Column | Type | Constraints | Description |
|--------|------|-------------|-------------|
| `id` | `BIGSERIAL` | `PRIMARY KEY` | Creator ID |
| `user_id` | `BIGINT` | `UNIQUE FK → usr_users(id)` | Associated user account |
| `creator_type` | `VARCHAR(20)` | `CHECK IN (...)` | Type: individual or company |
| `display_name` | `VARCHAR(100)` | `NOT NULL` | Public display name |
| `verified` | `BOOLEAN` | `DEFAULT FALSE` | Verification badge status |
| `status` | `VARCHAR(20)` | `CHECK IN (...)` | Status: active, suspended, banned, under_review |
| `status_changed_at` | `TIMESTAMP` | `NULL` | Last status change time |
| `status_changed_by` | `BIGINT` | `FK → usr_users(id)` | Admin who changed status |
| `created_at` | `TIMESTAMP` | `DEFAULT NOW()` | Creator profile creation |
| `updated_at` | `TIMESTAMP` | `DEFAULT NOW()` | Last update timestamp |

**Indexes**:
- `idx_crt_creators_user` on `(user_id)`
- `idx_crt_creators_type` on `(creator_type)`
- `idx_crt_creators_status` on `(status)` where `status != 'banned'`

---

#### `crt_companies`
**Purpose**: Company-specific information for corporate creators.

| Column | Type | Constraints | Description |
|--------|------|-------------|-------------|
| `id` | `BIGSERIAL` | `PRIMARY KEY` | Company ID |
| `creator_id` | `BIGINT` | `UNIQUE FK → crt_creators(id)` | Associated creator profile |
| `company_name` | `VARCHAR(255)` | `NOT NULL` | Legal company name |
| `registration_number` | `VARCHAR(100)` | `NULL` | Business registration number |
| `industry` | `VARCHAR(100)` | `NULL` | Industry/sector |
| `website` | `VARCHAR(255)` | `NULL` | Company website URL |
| `contact_email` | `VARCHAR(255)` | `NULL` | Business contact email |
| `contact_phone` | `VARCHAR(50)` | `NULL` | Business phone number |
| `address` | `TEXT` | `NULL` | Business address |
| `created_at` | `TIMESTAMP` | `DEFAULT NOW()` | Record creation time |
| `updated_at` | `TIMESTAMP` | `DEFAULT NOW()` | Last update timestamp |

---

#### `crt_creator_status_history`
**Purpose**: Audit trail for creator moderation actions.

| Column | Type | Constraints | Description |
|--------|------|-------------|-------------|
| `id` | `BIGSERIAL` | `PRIMARY KEY` | History entry ID |
| `creator_id` | `BIGINT` | `FK → crt_creators(id)` | Creator being moderated |
| `status` | `VARCHAR(20)` | `CHECK IN (...)` | New status applied |
| `reason` | `TEXT` | `NULL` | Reason for status change |
| `actioned_by` | `BIGINT` | `FK → usr_users(id)` | Admin who made the change |
| `created_at` | `TIMESTAMP` | `DEFAULT NOW()` | Change timestamp |

**Index**: `idx_crt_creator_status_history_creator` on `(creator_id, created_at DESC)`

---

### 4. File Storage (`fs_`)

#### `fs_files`
**Purpose**: Storage-agnostic file metadata for all uploaded media.

| Column | Type | Constraints | Description |
|--------|------|-------------|-------------|
| `id` | `BIGSERIAL` | `PRIMARY KEY` | File ID |
| `uploader_id` | `BIGINT` | `FK → usr_users(id)` | User who uploaded file |
| `storage_provider` | `VARCHAR(50)` | `NOT NULL` | Provider: minio, s3, local |
| `bucket_name` | `VARCHAR(255)` | `NOT NULL` | Bucket/container name |
| `storage_key` | `VARCHAR(500)` | `NOT NULL` | Full path in storage |
| `file_name` | `VARCHAR(255)` | `NOT NULL` | Original file name |
| `mime_type` | `VARCHAR(100)` | `NOT NULL` | MIME type (e.g., "image/jpeg") |
| `file_size_bytes` | `BIGINT` | `NOT NULL` | File size in bytes |
| `file_hash` | `VARCHAR(64)` | `NOT NULL` | SHA-256 hash (deduplication) |
| `width` | `INTEGER` | `NULL` | Image/video width in pixels |
| `height` | `INTEGER` | `NULL` | Image/video height in pixels |
| `duration_seconds` | `INTEGER` | `NULL` | Video duration |
| `metadata` | `JSONB` | `NULL` | Additional metadata (EXIF, etc.) |
| `is_public` | `BOOLEAN` | `DEFAULT FALSE` | Public access flag |
| `upload_status` | `VARCHAR(20)` | `CHECK IN (...)` | Status: pending, processing, completed, failed |
| `created_at` | `TIMESTAMP` | `DEFAULT NOW()` | Upload timestamp |
| `updated_at` | `TIMESTAMP` | `DEFAULT NOW()` | Last update timestamp |
| `deleted_at` | `TIMESTAMP` | `NULL` | Soft delete timestamp |

**Indexes**:
- `idx_fs_files_uploader` on `(uploader_id)`
- `idx_fs_files_hash` on `(file_hash)` where `deleted_at IS NULL`
- `idx_fs_files_provider` on `(storage_provider, bucket_name)`

---

### 5. Posts (`cnt_`)

#### `cnt_posts`
**Purpose**: User-generated posts with text and/or media content.

| Column | Type | Constraints | Description |
|--------|------|-------------|-------------|
| `id` | `BIGSERIAL` | `PRIMARY KEY` | Post ID |
| `creator_id` | `BIGINT` | `FK → crt_creators(id)` | Post author |
| `content_text` | `TEXT` | `NULL` | Post text content |
| `is_published` | `BOOLEAN` | `DEFAULT TRUE` | Published status |
| `view_count` | `BIGINT` | `DEFAULT 0` | Total views (from analytics) |
| `like_count` | `INTEGER` | `DEFAULT 0` | Total likes (auto-updated) |
| `comment_count` | `INTEGER` | `DEFAULT 0` | Total comments (auto-updated) |
| `created_at` | `TIMESTAMP` | `DEFAULT NOW()` | Post creation time |
| `updated_at` | `TIMESTAMP` | `DEFAULT NOW()` | Last edit time |
| `deleted_at` | `TIMESTAMP` | `NULL` | Soft delete timestamp |

**Indexes**:
- `idx_cnt_posts_creator` on `(creator_id, created_at DESC)`
- `idx_cnt_posts_created` on `(created_at DESC)` where `deleted_at IS NULL`
- `idx_cnt_posts_published` on `(is_published, created_at DESC)` where `deleted_at IS NULL`

---

#### `cnt_post_media`
**Purpose**: Junction table linking posts to multiple media files.

| Column | Type | Constraints | Description |
|--------|------|-------------|-------------|
| `id` | `BIGSERIAL` | `PRIMARY KEY` | Junction ID |
| `post_id` | `BIGINT` | `FK → cnt_posts(id)` | Post reference |
| `file_id` | `BIGINT` | `FK → fs_files(id)` | File reference |
| `display_order` | `INTEGER` | `DEFAULT 0` | Order of media in post |
| `created_at` | `TIMESTAMP` | `DEFAULT NOW()` | Link creation time |

**Indexes**:
- `idx_cnt_post_media_unique` (UNIQUE) on `(post_id, file_id)`
- `idx_cnt_post_media_post` on `(post_id, display_order)`

---

#### `cnt_post_likes`
**Purpose**: Tracks user likes on posts.

| Column | Type | Constraints | Description |
|--------|------|-------------|-------------|
| `id` | `BIGSERIAL` | `PRIMARY KEY` | Like ID |
| `user_id` | `BIGINT` | `FK → usr_users(id)` | User who liked |
| `post_id` | `BIGINT` | `FK → cnt_posts(id)` | Post being liked |
| `created_at` | `TIMESTAMP` | `DEFAULT NOW()` | Like timestamp |

**Indexes**:
- `idx_cnt_post_likes_unique` (UNIQUE) on `(user_id, post_id)`
- `idx_cnt_post_likes_post` on `(post_id, created_at DESC)`

---

#### `cnt_post_comments`
**Purpose**: User comments on posts with threading support.

| Column | Type | Constraints | Description |
|--------|------|-------------|-------------|
| `id` | `BIGSERIAL` | `PRIMARY KEY` | Comment ID |
| `user_id` | `BIGINT` | `FK → usr_users(id)` | Commenter |
| `post_id` | `BIGINT` | `FK → cnt_posts(id)` | Post being commented on |
| `parent_comment_id` | `BIGINT` | `FK → cnt_post_comments(id)` | Parent comment (for replies) |
| `content` | `TEXT` | `NOT NULL` | Comment text |
| `created_at` | `TIMESTAMP` | `DEFAULT NOW()` | Comment timestamp |
| `updated_at` | `TIMESTAMP` | `DEFAULT NOW()` | Edit timestamp |
| `deleted_at` | `TIMESTAMP` | `NULL` | Soft delete timestamp |

**Indexes**:
- `idx_cnt_post_comments_post` on `(post_id, created_at DESC)` where `deleted_at IS NULL`
- `idx_cnt_post_comments_parent` on `(parent_comment_id)` where `parent_comment_id IS NOT NULL`

---

### 6. Reels (`cnt_`)

#### `cnt_reels`
**Purpose**: Short-form video content (Instagram-style reels).

| Column | Type | Constraints | Description |
|--------|------|-------------|-------------|
| `id` | `BIGSERIAL` | `PRIMARY KEY` | Reel ID |
| `creator_id` | `BIGINT` | `FK → crt_creators(id)` | Reel author |
| `title` | `VARCHAR(255)` | `NULL` | Reel title |
| `description` | `TEXT` | `NULL` | Reel description |
| `video_file_id` | `BIGINT` | `FK → fs_files(id)` | Video file reference |
| `thumbnail_file_id` | `BIGINT` | `FK → fs_files(id)` | Thumbnail image reference |
| `duration_seconds` | `INTEGER` | `NOT NULL` | Video duration |
| `is_published` | `BOOLEAN` | `DEFAULT TRUE` | Published status |
| `view_count` | `BIGINT` | `DEFAULT 0` | Total views (from analytics) |
| `like_count` | `INTEGER` | `DEFAULT 0` | Total likes (auto-updated) |
| `comment_count` | `INTEGER` | `DEFAULT 0` | Total comments (auto-updated) |
| `created_at` | `TIMESTAMP` | `DEFAULT NOW()` | Reel creation time |
| `updated_at` | `TIMESTAMP` | `DEFAULT NOW()` | Last edit time |
| `deleted_at` | `TIMESTAMP` | `NULL` | Soft delete timestamp |

**Indexes**:
- `idx_cnt_reels_creator` on `(creator_id, created_at DESC)`
- `idx_cnt_reels_created` on `(created_at DESC)` where `deleted_at IS NULL`
- `idx_cnt_reels_published` on `(is_published, created_at DESC)` where `deleted_at IS NULL`

---

#### `cnt_reel_likes`
**Purpose**: Tracks user likes on reels.

| Column | Type | Constraints | Description |
|--------|------|-------------|-------------|
| `id` | `BIGSERIAL` | `PRIMARY KEY` | Like ID |
| `user_id` | `BIGINT` | `FK → usr_users(id)` | User who liked |
| `reel_id` | `BIGINT` | `FK → cnt_reels(id)` | Reel being liked |
| `created_at` | `TIMESTAMP` | `DEFAULT NOW()` | Like timestamp |

**Indexes**:
- `idx_cnt_reel_likes_unique` (UNIQUE) on `(user_id, reel_id)`
- `idx_cnt_reel_likes_reel` on `(reel_id, created_at DESC)`

---

#### `cnt_reel_comments`
**Purpose**: User comments on reels with threading support.

| Column | Type | Constraints | Description |
|--------|------|-------------|-------------|
| `id` | `BIGSERIAL` | `PRIMARY KEY` | Comment ID |
| `user_id` | `BIGINT` | `FK → usr_users(id)` | Commenter |
| `reel_id` | `BIGINT` | `FK → cnt_reels(id)` | Reel being commented on |
| `parent_comment_id` | `BIGINT` | `FK → cnt_reel_comments(id)` | Parent comment (for replies) |
| `content` | `TEXT` | `NOT NULL` | Comment text |
| `created_at` | `TIMESTAMP` | `DEFAULT NOW()` | Comment timestamp |
| `updated_at` | `TIMESTAMP` | `DEFAULT NOW()` | Edit timestamp |
| `deleted_at` | `TIMESTAMP` | `NULL` | Soft delete timestamp |

**Indexes**:
- `idx_cnt_reel_comments_reel` on `(reel_id, created_at DESC)` where `deleted_at IS NULL`
- `idx_cnt_reel_comments_parent` on `(parent_comment_id)` where `parent_comment_id IS NOT NULL`

---

### 7. Stories (`cnt_`)

#### `cnt_stories`
**Purpose**: 24-hour ephemeral content (Instagram-style stories).

| Column | Type | Constraints | Description |
|--------|------|-------------|-------------|
| `id` | `BIGSERIAL` | `PRIMARY KEY` | Story ID |
| `creator_id` | `BIGINT` | `FK → crt_creators(id)` | Story author |
| `media_file_id` | `BIGINT` | `FK → fs_files(id)` | Media file (image or video) |
| `content_text` | `TEXT` | `NULL` | Optional text overlay |
| `view_count` | `BIGINT` | `DEFAULT 0` | Total views (from analytics) |
| `is_published` | `BOOLEAN` | `DEFAULT TRUE` | Published status |
| `created_at` | `TIMESTAMP` | `DEFAULT NOW()` | Story creation time |
| `expires_at` | `TIMESTAMP` | `NOT NULL` | Expiration time (24h from creation) |
| `deleted_at` | `TIMESTAMP` | `NULL` | Soft delete timestamp |

**Indexes**:
- `idx_cnt_stories_creator` on `(creator_id, created_at DESC)`
- `idx_cnt_stories_expires` on `(expires_at)` where `deleted_at IS NULL`
- `idx_cnt_stories_active` on `(created_at DESC)` where `expires_at > NOW() AND deleted_at IS NULL`

**Cleanup Strategy**:
- Background job periodically deletes expired stories (`WHERE expires_at < NOW()`)
- Expired stories can be archived before deletion for analytics

---

#### `cnt_story_views`
**Purpose**: Tracks who viewed each story (for creator insights).

| Column | Type | Constraints | Description |
|--------|------|-------------|-------------|
| `id` | `BIGSERIAL` | `PRIMARY KEY` | View ID |
| `story_id` | `BIGINT` | `FK → cnt_stories(id)` | Story being viewed |
| `viewer_id` | `BIGINT` | `FK → usr_users(id)` | User who viewed |
| `created_at` | `TIMESTAMP` | `DEFAULT NOW()` | View timestamp |

**Indexes**:
- `idx_cnt_story_views_unique` (UNIQUE) on `(story_id, viewer_id)`
- `idx_cnt_story_views_story` on `(story_id, created_at DESC)`
- `idx_cnt_story_views_viewer` on `(viewer_id, created_at DESC)`

**Note**: Each user can view a story multiple times, but only the first view is recorded.

---

### 8. Hashtags (`cnt_`)

#### `cnt_hashtags`
**Purpose**: Master list of all hashtags used on the platform.

| Column | Type | Constraints | Description |
|--------|------|-------------|-------------|
| `id` | `BIGSERIAL` | `PRIMARY KEY` | Hashtag ID |
| `tag` | `VARCHAR(100)` | `UNIQUE NOT NULL` | Hashtag text (without #) |
| `usage_count` | `BIGINT` | `DEFAULT 0` | Total times used (denormalized) |
| `created_at` | `TIMESTAMP` | `DEFAULT NOW()` | First usage timestamp |
| `updated_at` | `TIMESTAMP` | `DEFAULT NOW()` | Last usage timestamp |

**Indexes**:
- `idx_cnt_hashtags_tag` (UNIQUE) on `(LOWER(tag))`
- `idx_cnt_hashtags_usage` on `(usage_count DESC)`

**Normalization**: Tags stored in lowercase for consistency (e.g., "travel", not "Travel" or "TRAVEL")

---

#### `cnt_post_hashtags`
**Purpose**: Junction table linking posts to hashtags.

| Column | Type | Constraints | Description |
|--------|------|-------------|-------------|
| `id` | `BIGSERIAL` | `PRIMARY KEY` | Junction ID |
| `post_id` | `BIGINT` | `FK → cnt_posts(id)` | Post reference |
| `hashtag_id` | `BIGINT` | `FK → cnt_hashtags(id)` | Hashtag reference |
| `created_at` | `TIMESTAMP` | `DEFAULT NOW()` | Link creation time |

**Indexes**:
- `idx_cnt_post_hashtags_unique` (UNIQUE) on `(post_id, hashtag_id)`
- `idx_cnt_post_hashtags_post` on `(post_id)`
- `idx_cnt_post_hashtags_hashtag` on `(hashtag_id, created_at DESC)`

---

#### `cnt_reel_hashtags`
**Purpose**: Junction table linking reels to hashtags.

| Column | Type | Constraints | Description |
|--------|------|-------------|-------------|
| `id` | `BIGSERIAL` | `PRIMARY KEY` | Junction ID |
| `reel_id` | `BIGINT` | `FK → cnt_reels(id)` | Reel reference |
| `hashtag_id` | `BIGINT` | `FK → cnt_hashtags(id)` | Hashtag reference |
| `created_at` | `TIMESTAMP` | `DEFAULT NOW()` | Link creation time |

**Indexes**:
- `idx_cnt_reel_hashtags_unique` (UNIQUE) on `(reel_id, hashtag_id)`
- `idx_cnt_reel_hashtags_reel` on `(reel_id)`
- `idx_cnt_reel_hashtags_hashtag` on `(hashtag_id, created_at DESC)`

---

### 9. Social Features (`usr_`)

#### `usr_follows`
**Purpose**: Tracks follower/following relationships between users.

| Column | Type | Constraints | Description |
|--------|------|-------------|-------------|
| `id` | `BIGSERIAL` | `PRIMARY KEY` | Follow ID |
| `follower_id` | `BIGINT` | `FK → usr_users(id)` | User who is following |
| `following_id` | `BIGINT` | `FK → usr_users(id)` | User being followed |
| `created_at` | `TIMESTAMP` | `DEFAULT NOW()` | Follow timestamp |

**Indexes**:
- `idx_usr_follows_unique` (UNIQUE) on `(follower_id, following_id)`
- `idx_usr_follows_follower` on `(follower_id, created_at DESC)`
- `idx_usr_follows_following` on `(following_id, created_at DESC)`

**Constraints**:
- Check constraint: `follower_id != following_id` (cannot follow yourself)

**Behavior**:
- Insert triggers increment `usr_users.following_count` (for follower) and `usr_users.follower_count` (for following)
- Delete triggers decrement both counts

**Use Cases**:
- Get followers: `SELECT follower_id FROM usr_follows WHERE following_id = ?`
- Get following: `SELECT following_id FROM usr_follows WHERE follower_id = ?`
- Check if following: `SELECT 1 FROM usr_follows WHERE follower_id = ? AND following_id = ?`

---

## Entity Relationships

### Relationship Diagram

usr_users (1)
    ↓
    ├──> (0-1) crt_creators
    │         ↓
    │         ├──> (0-1) crt_companies [if creator_type='company']
    │         │
    │         ├──> (0-N) cnt_posts
    │         │         ↓
    │         │         ├──> (0-N) cnt_post_media ──> (N) fs_files
    │         │         ├──> (0-N) cnt_post_likes
    │         │         ├──> (0-N) cnt_post_comments
    │         │         └──> (0-N) cnt_post_hashtags ──> (N) cnt_hashtags
    │         │
    │         ├──> (0-N) cnt_reels
    │         │         ↓
    │         │         ├──> (1) fs_files [video]
    │         │         ├──> (0-1) fs_files [thumbnail]
    │         │         ├──> (0-N) cnt_reel_likes
    │         │         ├──> (0-N) cnt_reel_comments
    │         │         └──> (0-N) cnt_reel_hashtags ──> (N) cnt_hashtags
    │         │
    │         └──> (0-N) cnt_stories
    │                   ↓
    │                   ├──> (1) fs_files [media]
    │                   └──> (0-N) cnt_story_views
    │
    ├──> (0-N) usr_follows [as follower]
    ├──> (0-N) usr_follows [as following]
    ├──> (0-N) cnt_post_likes
    ├──> (0-N) cnt_post_comments
    ├──> (0-N) cnt_reel_likes
    ├──> (0-N) cnt_reel_comments
    ├──> (0-N) cnt_story_views
    ├──> (0-N) fs_files [uploads]
    └──> (0-1) fs_files [profile_picture]

sys_permissions (N) ←──> (N) sys_role_permissions ──> (3) roles

### Key Relationships

| Parent | Child | Type | Cascade Behavior |
|--------|-------|------|------------------|
| `usr_users` | `crt_creators` | 1:1 | `ON DELETE CASCADE` |
| `usr_users` | `usr_follows` (follower) | 1:N | `ON DELETE CASCADE` |
| `usr_users` | `usr_follows` (following) | 1:N | `ON DELETE CASCADE` |
| `crt_creators` | `crt_companies` | 1:0..1 | `ON DELETE CASCADE` |
| `crt_creators` | `cnt_posts` | 1:N | `ON DELETE CASCADE` |
| `crt_creators` | `cnt_reels` | 1:N | `ON DELETE CASCADE` |
| `crt_creators` | `cnt_stories` | 1:N | `ON DELETE CASCADE` |
| `cnt_posts` | `cnt_post_hashtags` | N:M | `ON DELETE CASCADE` |
| `cnt_reels` | `cnt_reel_hashtags` | N:M | `ON DELETE CASCADE` |
| `cnt_hashtags` | `cnt_post_hashtags` | 1:N | `ON DELETE CASCADE` |
| `cnt_hashtags` | `cnt_reel_hashtags` | 1:N | `ON DELETE CASCADE` |
| `cnt_stories` | `cnt_story_views` | 1:N | `ON DELETE CASCADE` |

---

## Key Design Decisions

### 1. **Stories as Separate Table (Not Polymorphic)**
**Decision**: Dedicated `cnt_stories` table instead of merging with posts/reels.

**Rationale**:
- **Different lifecycle**: Stories expire after 24 hours (auto-cleanup required)
- **Different features**: No likes/comments, only views
- **Performance**: Separate table allows efficient expiration queries
- **Simpler queries**: No type filtering needed

**Cleanup**: Background job runs hourly to delete expired stories:

DELETE FROM cnt_stories WHERE expires_at < NOW() - INTERVAL '7 days';

---

### 2. **Hashtag Normalization**
**Decision**: Master hashtag table with junction tables for posts/reels.

**Rationale**:
- **Consistency**: Single source of truth for each hashtag
- **Trending calculation**: `usage_count` enables trending hashtags
- **Search optimization**: Index on hashtag name for fast lookups
- **Typo prevention**: Application normalizes to lowercase before insert

**Discovery Features**:
- Search by hashtag: `SELECT * FROM cnt_posts WHERE id IN (SELECT post_id FROM cnt_post_hashtags WHERE hashtag_id = ?)`
- Trending hashtags: `SELECT * FROM cnt_hashtags ORDER BY usage_count DESC LIMIT 50`
- Related hashtags: Query co-occurrence in same posts

---

### 3. **Follow Relationship as Self-Referencing Table**
**Decision**: Single `usr_follows` table with `follower_id` and `following_id`.

**Rationale**:
- **Bidirectional queries**: Easy to query both followers and following
- **Denormalized counts**: `follower_count` and `following_count` in `usr_users` for fast profile display
- **No redundancy**: Single row represents the relationship
- **Mutual follows**: Requires two rows (A→B and B→A)

**Performance**: Triggers automatically maintain counts on insert/delete.

---

### 4. **Story Views Tracking**
**Decision**: Separate `cnt_story_views` table instead of using analytics.

**Rationale**:
- **Creator insights**: Creators need to see who viewed their story
- **Privacy consideration**: Unlike posts/reels, story views are visible to creator
- **Real-time**: Immediate feedback without analytics delay
- **Ephemeral**: Auto-deleted when story expires

**Alternative considered**: Store in analytics (Firebase/PostHog)
- **Rejected**: Cannot query individual viewers from external analytics

---

### 5. **Hashtag Usage Count Denormalization**
**Decision**: Store `usage_count` in `cnt_hashtags` table.

**Rationale**:
- **Trending discovery**: Fast query for trending hashtags without COUNT aggregation
- **Caching friendly**: Single value instead of expensive count query
- **Real-time updates**: Triggers update on hashtag link insert/delete

**Update mechanism**:

-- On insert to cnt_post_hashtags
UPDATE cnt_hashtags SET usage_count = usage_count + 1 WHERE id = NEW.hashtag_id;

---

### 6. **Follower/Following Count Denormalization**
**Decision**: Store counts in `usr_users` instead of counting on-demand.

**Rationale**:
- **Profile performance**: Display counts without JOIN or COUNT query
- **Popular on social platforms**: Users expect instant count display
- **Sort by popularity**: Easy to find top users by followers
- **Consistency**: Triggers ensure accuracy

**Trade-off**: Slight write overhead (acceptable for read-heavy social platform)

---

### 7. **Stories Expiration Strategy**
**Decision**: `expires_at` timestamp with background cleanup job.

**Rationale**:
- **Automatic expiration**: No manual intervention needed
- **Grace period**: Keep for 7 days after expiration (analytics/compliance)
- **Performance**: Index on `expires_at` for efficient cleanup
- **Flexibility**: Can extend expiration for premium users

**Implementation**:

-- Cron job runs every hour
DELETE FROM cnt_stories 
WHERE expires_at < NOW() - INTERVAL '7 days'
  AND deleted_at IS NULL;

---

## Security & Access Control

### Role-Based Access Control (RBAC)

#### Permission Model
1. **Permissions defined in** `sys_permissions`:
- Resource (e.g., "posts", "stories", "users")
- Action (e.g., "create", "moderate")
- Name (e.g., "create_story", "follow_user")

2. **Roles mapped in** `sys_role_permissions`:
- Each role assigned specific permissions
- Inheritance: content_creator includes all user permissions
- Admin has all permissions

3. **Enforcement**:
- Application checks `sys_role_permissions` before allowing actions
- Database enforces via foreign key constraints
- Admin actions logged in `sys_audit_log`

#### Permission Examples

| Role | Can Do | Cannot Do |
|------|--------|-----------|
| **User** | View content, like, comment, follow users | Create posts/reels/stories, moderate |
| **Content Creator** | Everything User can + create/edit own posts/reels/stories | Moderate others' content, ban users |
| **Admin** | Everything + moderate all content, ban users, view analytics | (No restrictions) |

---

### Social Graph Privacy

#### Follow Relationships
- **Public by default**: Anyone can see followers/following lists
- **Application-level privacy**: Can implement private accounts
- **Block feature**: Application prevents follow if user blocked

#### Story Views
- **Creator visibility**: Creator can see who viewed their stories
- **Viewer privacy**: Other users cannot see who viewed stories
- **Anonymous views**: Optional feature to hide viewer from creator

---

### Data Ownership

#### Creator → Content
- Creators can only edit/delete **their own** posts, reels, and stories
- Enforced by application checking `creator_id` against current user
- Admins can moderate any content (logged in `sys_audit_log`)

#### User → Engagement
- Users can only delete **their own** likes, comments, and follows
- Edit comment: Check `user_id` matches current user
- Admins can delete any comment (moderation)

---

## Scalability Considerations

### Indexed Queries
All frequently queried patterns have dedicated indexes:
- User lookup by email/username
- Follower/following relationships (bidirectional)
- Hashtag search and trending
- Active stories (not expired)
- Content by creator ordered by date
- Post/reel by hashtag with time sorting

### Write Optimization
- **Engagement counts**: Triggers update in single transaction
- **Hashtag usage**: Triggers update hashtag counts
- **Follow counts**: Triggers update user counts
- **Story cleanup**: Scheduled batch deletion

### Read Optimization
- **Denormalized counts**: No COUNT(*) on large tables
- **Filtered indexes**: Exclude deleted/expired records
- **Hashtag caching**: Cache trending hashtags (1 hour TTL)
- **Follow status**: Cache follow relationships (5 min TTL)

### Hot Hashtags Strategy
**Problem**: Popular hashtags (e.g., #love) could have millions of posts.

**Solution**:
1. **Pagination**: Always use cursor-based pagination on hashtag queries
2. **Time filters**: Default to "last 7 days" for popular hashtags
3. **Relevance scoring**: Use external search (Elasticsearch) for ranking
4. **Caching**: Cache first page of trending hashtags

---

### Story Scaling
**Problem**: High write volume (millions of stories/day), most expire.

**Solution**:
1. **Partitioning**: Partition `cnt_stories` by `created_at` (daily)
2. **Auto-drop old partitions**: Drop partitions older than 7 days
3. **Separate analytics**: Send story views to Firebase/PostHog
4. **CDN delivery**: Serve story media from CDN (not database)

---

### Follower Feed Generation
**Problem**: Generate feed for user following 1000+ accounts.

**Solution**:
1. **Fan-out on write**: Pre-compute feed for active users (Redis)
2. **Hybrid approach**: Mix cached feed + recent posts query
3. **Pagination**: Never load entire feed, always paginate
4. **Background job**: Async feed computation for large follower counts

**Query example** (simple approach):

SELECT p.* FROM cnt_posts p
JOIN usr_follows f ON f.following_id = p.creator_id
WHERE f.follower_id = ?
  AND p.deleted_at IS NULL
  AND p.is_published = TRUE
ORDER BY p.created_at DESC
LIMIT 20;

---

## Data Integrity

### Foreign Key Constraints
All relationships enforced at database level:
- ✅ Cannot follow deleted user (CASCADE delete)
- ✅ Cannot tag post with non-existent hashtag
- ✅ Cannot view deleted story (CASCADE delete)
- ✅ Cannot create story without valid media file

### Unique Constraints
Prevent duplicate data:
- ✅ User cannot follow same person twice
- ✅ User cannot like same post twice
- ✅ User cannot view same story twice (for tracking)
- ✅ Post cannot use same hashtag twice
- ✅ Hashtag text must be unique (case-insensitive)

### Check Constraints
Validate data at insert/update:
- ✅ Cannot follow yourself (`follower_id != following_id`)
- ✅ Story `expires_at` must be after `created_at`
- ✅ Hashtag tag cannot be empty string
- ✅ Usage counts cannot be negative

### Cascading Deletes Strategy

| When Deleted | What Happens | Why |
|--------------|--------------|-----|
| User account | All follows CASCADE deleted | Clean up relationships |
| Creator profile | All posts/reels/stories CASCADE deleted | Remove all content |
| Post | All likes/comments/hashtags CASCADE deleted | Clean up engagement |
| Story (expired) | All views CASCADE deleted | No orphaned data |
| Hashtag | All post/reel links CASCADE deleted | Remove broken references |

---

## Future Extensions

### Potential Features (Not Currently Implemented)

#### 1. **Saved Posts/Collections**

CREATE TABLE usr_saved_posts (
  id BIGSERIAL PRIMARY KEY,
  user_id BIGINT REFERENCES usr_users(id) ON DELETE CASCADE,
  post_id BIGINT REFERENCES cnt_posts(id) ON DELETE CASCADE,
  collection_name VARCHAR(100), -- Optional grouping
  created_at TIMESTAMP DEFAULT NOW(),
  UNIQUE (user_id, post_id)
);

CREATE TABLE usr_saved_reels (
  id BIGSERIAL PRIMARY KEY,
  user_id BIGINT REFERENCES usr_users(id) ON DELETE CASCADE,
  reel_id BIGINT REFERENCES cnt_reels(id) ON DELETE CASCADE,
  collection_name VARCHAR(100),
  created_at TIMESTAMP DEFAULT NOW(),
  UNIQUE (user_id, reel_id)
);

---

#### 2. **Direct Messaging**

CREATE TABLE msg_conversations (
  id BIGSERIAL PRIMARY KEY,
  is_group BOOLEAN DEFAULT FALSE,
  name VARCHAR(255), -- For group chats
  created_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE msg_participants (
  conversation_id BIGINT REFERENCES msg_conversations(id) ON DELETE CASCADE,
  user_id BIGINT REFERENCES usr_users(id) ON DELETE CASCADE,
  joined_at TIMESTAMP DEFAULT NOW(),
  last_read_at TIMESTAMP,
  PRIMARY KEY (conversation_id, user_id)
);

CREATE TABLE msg_messages (
  id BIGSERIAL PRIMARY KEY,
  conversation_id BIGINT REFERENCES msg_conversations(id) ON DELETE CASCADE,
  sender_id BIGINT REFERENCES usr_users(id) ON DELETE SET NULL,
  content TEXT,
  media_file_id BIGINT REFERENCES fs_files(id) ON DELETE SET NULL,
  created_at TIMESTAMP DEFAULT NOW(),
  deleted_at TIMESTAMP
);

---

#### 3. **User Blocking**

CREATE TABLE usr_blocks (
  id BIGSERIAL PRIMARY KEY,
  blocker_id BIGINT REFERENCES usr_users(id) ON DELETE CASCADE,
  blocked_id BIGINT REFERENCES usr_users(id) ON DELETE CASCADE,
  created_at TIMESTAMP DEFAULT NOW(),
  UNIQUE (blocker_id, blocked_id),
  CHECK (blocker_id != blocked_id)
);

**Enforcement**: Application prevents:
- Blocked users from seeing blocker's content
- Blocked users from following/messaging blocker
- Blocker from seeing blocked user's content

---

#### 4. **Notifications**

CREATE TABLE usr_notifications (
  id BIGSERIAL PRIMARY KEY,
  user_id BIGINT REFERENCES usr_users(id) ON DELETE CASCADE,
  actor_id BIGINT REFERENCES usr_users(id) ON DELETE CASCADE,
  notification_type VARCHAR(50) NOT NULL, -- 'follow', 'like', 'comment', 'mention'
  resource_type VARCHAR(50), -- 'post', 'reel', 'comment'
  resource_id BIGINT,
  is_read BOOLEAN DEFAULT FALSE,
  created_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_usr_notifications_user ON usr_notifications(user_id, is_read, created_at DESC);

---

#### 5. **Content Sharing (Cross-platform)**

CREATE TABLE cnt_shares (
  id BIGSERIAL PRIMARY KEY,
  user_id BIGINT REFERENCES usr_users(id) ON DELETE CASCADE,
  content_type VARCHAR(20) CHECK (content_type IN ('post', 'reel', 'story')),
  content_id BIGINT NOT NULL,
  platform VARCHAR(50), -- 'facebook', 'twitter', 'whatsapp', 'internal'
  created_at TIMESTAMP DEFAULT NOW()
);

---

#### 6. **Mentions in Content**

CREATE TABLE cnt_post_mentions (
  id BIGSERIAL PRIMARY KEY,
  post_id BIGINT REFERENCES cnt_posts(id) ON DELETE CASCADE,
  mentioned_user_id BIGINT REFERENCES usr_users(id) ON DELETE CASCADE,
  created_at TIMESTAMP DEFAULT NOW(),
  UNIQUE (post_id, mentioned_user_id)
);

CREATE TABLE cnt_reel_mentions (
  id BIGSERIAL PRIMARY KEY,
  reel_id BIGINT REFERENCES cnt_reels(id) ON DELETE CASCADE,
  mentioned_user_id BIGINT REFERENCES usr_users(id) ON DELETE CASCADE,
  created_at TIMESTAMP DEFAULT NOW(),
  UNIQUE (reel_id, mentioned_user_id)
);

---

#### 7. **Story Replies**

CREATE TABLE cnt_story_replies (
  id BIGSERIAL PRIMARY KEY,
  story_id BIGINT REFERENCES cnt_stories(id) ON DELETE CASCADE,
  user_id BIGINT REFERENCES usr_users(id) ON DELETE CASCADE,
  content TEXT NOT NULL,
  created_at TIMESTAMP DEFAULT NOW()
);

**Note**: Replies could also trigger direct message conversation.

---

#### 8. **Hashtag Following**

CREATE TABLE usr_followed_hashtags (
  id BIGSERIAL PRIMARY KEY,
  user_id BIGINT REFERENCES usr_users(id) ON DELETE CASCADE,
  hashtag_id BIGINT REFERENCES cnt_hashtags(id) ON DELETE CASCADE,
  created_at TIMESTAMP DEFAULT NOW(),
  UNIQUE (user_id, hashtag_id)
);

**Use Case**: Users can follow hashtags to see posts/reels in their feed.

---

## Appendix: Quick Reference

### Table Count: **25 Tables**

| Prefix | Count | Tables |
|--------|-------|--------|
| `sys_` | 4 | parameters, permissions, role_permissions, audit_log |
| `usr_` | 2 | users, follows |
| `crt_` | 3 | creators, companies, creator_status_history |
| `fs_` | 1 | files |
| `cnt_` | 15 | posts, post_media, post_likes, post_comments, post_hashtags, reels, reel_likes, reel_comments, reel_hashtags, stories, story_views, hashtags |

### New Features Summary

| Feature | Tables Added | Key Capabilities |
|---------|--------------|------------------|
| **Followers/Following** | `usr_follows` | Follow users, follower counts, social graph |
| **Hashtags** | `cnt_hashtags`, `cnt_post_hashtags`, `cnt_reel_hashtags` | Tag content, discover by hashtag, trending tags |
| **Stories** | `cnt_stories`, `cnt_story_views` | 24-hour ephemeral content, view tracking |

### Index Count: **68 Indexes**

### Foreign Key Count: **38 Relationships**

### Supported Content Types
- **Posts**: Text + multiple images/videos
- **Reels**: Short-form video (max 3 minutes)
- **Stories**: Single image/video (expires in 24 hours)

### Default Configuration
- Max file size: 500 MB
- Max post media: 10 files
- Max reel duration: 180 seconds
- Story duration: 24 hours
- Max hashtags per post: 30

---

## Review Checklist

### Core Features
- [x] Does the schema satisfy all three user roles?
- [x] Can companies register as content creators?
- [x] Can posts contain text only, media only, or both?
- [x] Can posts have multiple media files?
- [x] Are reels properly structured with video + thumbnail?
- [x] Can users like and comment on content?
- [x] Is the file storage system provider-agnostic?
- [x] Can admins moderate creators and content?
- [x] Are all admin actions logged?

### New Features
- [x] Can users follow/unfollow other users?
- [x] Are follower/following counts maintained?
- [x] Can posts and reels be tagged with hashtags?
- [x] Can users discover content by hashtag?
- [x] Are trending hashtags trackable?
- [x] Can creators post 24-hour stories?
- [x] Can creators see who viewed their stories?
- [x] Do stories automatically expire?

### Technical
- [x] Is the schema scalable for high traffic?
- [x] Are foreign keys and constraints properly enforced?
- [x] Is soft delete implemented where needed?
- [x] Are indexes optimized for common queries?
- [x] Are denormalized counts auto-updated via triggers?

---

**Document Version:** 2.0
**Last Updated:** January 29, 2026
**Changes from v1.0**: Added followers/following, hashtags, and stories features
**Prepared For:** Architecture Review