Content is user-generated and unverified.

2 Medium-Sized Project Designs

1. E-Learning Platform

Database Schema

Core User Management

sql
-- Users table with role-based access
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    role VARCHAR(20) DEFAULT 'student', -- student, instructor, admin
    avatar_url VARCHAR(500),
    bio TEXT,
    date_of_birth DATE,
    phone VARCHAR(20),
    address JSONB,
    is_verified BOOLEAN DEFAULT FALSE,
    verification_token VARCHAR(255),
    reset_token VARCHAR(255),
    reset_token_expires TIMESTAMP,
    last_login TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- User profiles and preferences
CREATE TABLE user_profiles (
    user_id INTEGER PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
    timezone VARCHAR(50) DEFAULT 'UTC',
    language VARCHAR(5) DEFAULT 'en',
    notification_preferences JSONB DEFAULT '{"email": true, "push": false}',
    learning_preferences JSONB,
    social_links JSONB,
    achievements JSONB DEFAULT '[]',
    total_learning_hours INTEGER DEFAULT 0,
    skill_level VARCHAR(20) DEFAULT 'beginner'
);

Course Management

sql
-- Categories for organizing courses
CREATE TABLE categories (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    slug VARCHAR(100) UNIQUE NOT NULL,
    description TEXT,
    icon VARCHAR(50),
    color VARCHAR(7) DEFAULT '#6366f1',
    parent_id INTEGER REFERENCES categories(id),
    is_active BOOLEAN DEFAULT TRUE,
    sort_order INTEGER DEFAULT 0
);

-- Main courses table
CREATE TABLE courses (
    id SERIAL PRIMARY KEY,
    instructor_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
    category_id INTEGER REFERENCES categories(id),
    title VARCHAR(255) NOT NULL,
    slug VARCHAR(255) UNIQUE NOT NULL,
    description TEXT,
    short_description VARCHAR(500),
    thumbnail_url VARCHAR(500),
    trailer_video_url VARCHAR(500),
    level VARCHAR(20) DEFAULT 'beginner', -- beginner, intermediate, advanced
    language VARCHAR(5) DEFAULT 'en',
    price DECIMAL(10,2) DEFAULT 0,
    discount_price DECIMAL(10,2),
    duration_hours INTEGER,
    status VARCHAR(20) DEFAULT 'draft', -- draft, published, archived
    requirements TEXT[],
    learning_outcomes TEXT[],
    tags TEXT[],
    rating DECIMAL(3,2) DEFAULT 0,
    total_students INTEGER DEFAULT 0,
    total_reviews INTEGER DEFAULT 0,
    is_featured BOOLEAN DEFAULT FALSE,
    published_at TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Course sections/modules
CREATE TABLE course_sections (
    id SERIAL PRIMARY KEY,
    course_id INTEGER REFERENCES courses(id) ON DELETE CASCADE,
    title VARCHAR(255) NOT NULL,
    description TEXT,
    sort_order INTEGER NOT NULL,
    is_preview BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Individual lessons within sections
CREATE TABLE lessons (
    id SERIAL PRIMARY KEY,
    section_id INTEGER REFERENCES course_sections(id) ON DELETE CASCADE,
    title VARCHAR(255) NOT NULL,
    slug VARCHAR(255) NOT NULL,
    description TEXT,
    content_type VARCHAR(20) NOT NULL, -- video, text, quiz, assignment
    content_url VARCHAR(500),
    content_text TEXT,
    duration_minutes INTEGER,
    sort_order INTEGER NOT NULL,
    is_preview BOOLEAN DEFAULT FALSE,
    resources JSONB DEFAULT '[]',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(section_id, slug)
);

Learning Progress & Interaction

sql
-- Student enrollments
CREATE TABLE enrollments (
    id SERIAL PRIMARY KEY,
    student_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
    course_id INTEGER REFERENCES courses(id) ON DELETE CASCADE,
    enrollment_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    completion_date TIMESTAMP,
    progress_percentage DECIMAL(5,2) DEFAULT 0,
    last_accessed TIMESTAMP,
    certificate_issued BOOLEAN DEFAULT FALSE,
    payment_status VARCHAR(20) DEFAULT 'pending',
    payment_amount DECIMAL(10,2),
    UNIQUE(student_id, course_id)
);

-- Lesson progress tracking
CREATE TABLE lesson_progress (
    id SERIAL PRIMARY KEY,
    student_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
    lesson_id INTEGER REFERENCES lessons(id) ON DELETE CASCADE,
    status VARCHAR(20) DEFAULT 'not_started', -- not_started, in_progress, completed
    time_spent_minutes INTEGER DEFAULT 0,
    completion_percentage DECIMAL(5,2) DEFAULT 0,
    last_position_seconds INTEGER DEFAULT 0,
    completed_at TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(student_id, lesson_id)
);

-- Quiz system
CREATE TABLE quizzes (
    id SERIAL PRIMARY KEY,
    lesson_id INTEGER REFERENCES lessons(id) ON DELETE CASCADE,
    title VARCHAR(255) NOT NULL,
    description TEXT,
    time_limit_minutes INTEGER,
    passing_score DECIMAL(5,2) DEFAULT 70,
    max_attempts INTEGER DEFAULT 3,
    questions JSONB NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Quiz attempts and results
CREATE TABLE quiz_attempts (
    id SERIAL PRIMARY KEY,
    student_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
    quiz_id INTEGER REFERENCES quizzes(id) ON DELETE CASCADE,
    attempt_number INTEGER NOT NULL,
    answers JSONB NOT NULL,
    score DECIMAL(5,2) NOT NULL,
    time_taken_minutes INTEGER,
    passed BOOLEAN DEFAULT FALSE,
    started_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    completed_at TIMESTAMP
);

-- Course reviews and ratings
CREATE TABLE reviews (
    id SERIAL PRIMARY KEY,
    student_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
    course_id INTEGER REFERENCES courses(id) ON DELETE CASCADE,
    rating INTEGER CHECK (rating >= 1 AND rating <= 5),
    title VARCHAR(255),
    comment TEXT,
    is_anonymous BOOLEAN DEFAULT FALSE,
    helpful_votes INTEGER DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(student_id, course_id)
);

-- Discussion forums
CREATE TABLE discussions (
    id SERIAL PRIMARY KEY,
    course_id INTEGER REFERENCES courses(id) ON DELETE CASCADE,
    lesson_id INTEGER REFERENCES lessons(id) ON DELETE CASCADE,
    user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
    title VARCHAR(255) NOT NULL,
    content TEXT NOT NULL,
    parent_id INTEGER REFERENCES discussions(id), -- for replies
    upvotes INTEGER DEFAULT 0,
    is_resolved BOOLEAN DEFAULT FALSE,
    is_pinned BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Payment & Certificates

sql
-- Payment transactions
CREATE TABLE payments (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
    course_id INTEGER REFERENCES courses(id) ON DELETE CASCADE,
    amount DECIMAL(10,2) NOT NULL,
    currency VARCHAR(3) DEFAULT 'USD',
    payment_method VARCHAR(50),
    payment_gateway VARCHAR(50),
    transaction_id VARCHAR(255),
    status VARCHAR(20) DEFAULT 'pending',
    gateway_response JSONB,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Certificates
CREATE TABLE certificates (
    id SERIAL PRIMARY KEY,
    student_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
    course_id INTEGER REFERENCES courses(id) ON DELETE CASCADE,
    certificate_id VARCHAR(50) UNIQUE NOT NULL,
    issued_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    certificate_url VARCHAR(500),
    verification_code VARCHAR(20) UNIQUE NOT NULL
);

API Endpoints

Authentication & User Management

  • POST /api/auth/register - User registration
  • POST /api/auth/login - User login
  • POST /api/auth/forgot-password - Forgot password
  • POST /api/auth/reset-password - Reset password
  • GET /api/auth/verify/:token - Verify email
  • GET /api/users/profile - Get user profile
  • PUT /api/users/profile - Update profile
  • POST /api/users/avatar - Upload avatar

Course Management

  • GET /api/courses - Browse courses with filters
  • GET /api/courses/:id - Get course details
  • POST /api/courses - Create course (instructor)
  • PUT /api/courses/:id - Update course
  • POST /api/courses/:id/sections - Add section
  • POST /api/sections/:id/lessons - Add lesson
  • GET /api/categories - Get course categories

Learning & Progress

  • POST /api/courses/:id/enroll - Enroll in course
  • GET /api/enrollments - Get user enrollments
  • GET /api/courses/:id/progress - Get course progress
  • POST /api/lessons/:id/progress - Update lesson progress
  • GET /api/dashboard - Learning dashboard data

Interactive Features

  • POST /api/courses/:id/reviews - Add course review
  • GET /api/courses/:id/reviews - Get course reviews
  • POST /api/discussions - Create discussion thread
  • GET /api/courses/:id/discussions - Get course discussions
  • POST /api/discussions/:id/replies - Reply to discussion

Quizzes & Assessments

  • GET /api/lessons/:id/quiz - Get lesson quiz
  • POST /api/quizzes/:id/attempt - Submit quiz attempt
  • GET /api/quizzes/:id/results - Get quiz results

Payments & Certificates

  • POST /api/payments/create - Create payment intent
  • POST /api/payments/verify - Verify payment
  • GET /api/certificates - Get user certificates
  • GET /api/certificates/:id/verify - Verify certificate

UI Design

Dashboard Layout

┌─────────────────────────────────────────────────────┐
│ EduPlatform    [🔍 Search courses...] 👤 Profile    │
├─────────────────────────────────────────────────────┤
│ Dashboard                                           │
├─────────────────────────────────────────────────────┤
│ Welcome back, John! 👋                             │
│ Continue your learning journey                      │
├─────────────────────────────────────────────────────┤
│ 📚 Continue Learning                                │
│ ┌───────────┐ ┌───────────┐ ┌───────────┐         │
│ │📱React    │ │🐍Python   │ │🎨Design   │         │
│ │Bootcamp   │ │for Data   │ │Thinking   │         │
│ │████████░░ │ │██████░░░░ │ │███░░░░░░░ │         │
│ │80% • 4h   │ │60% • 2h   │ │30% • 1h   │         │
│ └───────────┘ └───────────┘ └───────────┘         │
├─────────────────────────────────────────────────────┤
│ 🏆 Your Achievements                                │
│ • Completed 3 courses this month                    │
│ • 15-day learning streak! 🔥                       │
│ • Earned "JavaScript Master" badge                  │
├─────────────────────────────────────────────────────┤
│ 📊 Learning Stats                                   │
│ Total Hours: 87  |  Courses: 12  |  Certificates: 5│
└─────────────────────────────────────────────────────┘

Course Detail Page

┌─────────────────────────────────────────────────────┐
│ ← Back to Courses                                   │
├─────────────────────────────────────────────────────┤
│ ┌──────────────┐  Complete React Developer Course  │
│ │  📺 Preview  │  Master React from Zero to Hero   │
│ │   Video      │  ⭐ 4.8 (2,847 reviews)          │
│ │              │  👨‍💻 By John Smith               │
│ │              │  🕐 42 hours • 🎓 156,423 students│
│ └──────────────┘  💰 $89.99 [🛒 Enroll Now]       │
├─────────────────────────────────────────────────────┤
│ [📖 Overview] [📋 Curriculum] [⭐ Reviews] [💬 Q&A] │
├─────────────────────────────────────────────────────┤
│ What you'll learn:                                  │
│ ✓ Build modern React applications                   │
│ ✓ Master React Hooks and Context API               │
│ ✓ State management with Redux                       │
│ ✓ Deploy apps to production                         │
├─────────────────────────────────────────────────────┤
│ Course Content (12 sections • 89 lectures)         │
│ ▼ 1. Getting Started (5 lectures • 45min)         │
│   ▶️ Introduction to React                  4:32   │
│   ▶️ Setting up Development Environment     8:15   │
│   🔒 Your First React Component            6:43   │
│ ▼ 2. JSX and Components (8 lectures • 1h 20min)  │
│   ▶️ Understanding JSX                      9:21   │
│   🔒 Props and State                       12:45   │
└─────────────────────────────────────────────────────┘

Learning Interface

┌─────────────────────────────────────────────────────┐
│ Complete React Course > Components > State Management│
├─────────────────────────────────────────────────────┤
│ ┌─────────────────────────────────────────────────┐ │
│ │            📺 Video Player                      │ │
│ │                                                 │ │
│ │  ▶️  [Timeline: ████████████░░░░░░░░░]  15:32   │ │
│ │                                                 │ │
│ │  [⏪] [⏸️] [⏩]  [🔊] [⚙️] [📺] [📝]            │ │
│ └─────────────────────────────────────────────────┘ │
├─────────────────────────────────────────────────────┤
│ Understanding React State                           │
│ Learn how to manage component state effectively     │
│                                                     │
│ 📚 Resources:                                       │
│ • Code examples.zip                                 │
│ • State management cheat sheet                      │
│ • Practice exercises                                │
├─────────────────────────────────────────────────────┤
│ [← Previous: Props] [✓ Mark Complete] [Next: Hooks →]│
└─────────────────────────────────────────────────────┘

Instructor Dashboard

┌─────────────────────────────────────────────────────┐
│ Instructor Dashboard               [+ Create Course] │
├─────────────────────────────────────────────────────┤
│ 📊 Overview                                         │
│ Total Students: 2,847  |  Revenue: $45,230         │
│ Avg Rating: 4.8  |  Total Reviews: 1,234          │
├─────────────────────────────────────────────────────┤
│ 📈 Analytics (Last 30 days)                        │
│ New Enrollments: ████████████████ 423              │
│ Course Completions: █████████░░░ 156               │
│ Revenue Growth: +23% 📈                            │
├─────────────────────────────────────────────────────┤
│ 📚 My Courses                                       │
│ React Course        👥 1,247  ⭐ 4.8  💰 $1,890   │
│ Python Bootcamp     👥 856   ⭐ 4.6  💰 $2,140    │
│ Web Design Basics   👥 743   ⭐ 4.9  💰 $1,200    │
├─────────────────────────────────────────────────────┤
│ 💬 Recent Q&A (3 pending)                          │
│ • "How to handle async operations?" - Sarah         │
│ • "Best practices for state management?" - Mike     │
└─────────────────────────────────────────────────────┘

2. Social Media Management Platform

Database Schema

User & Account Management

sql
-- Main users table
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    company_name VARCHAR(255),
    role VARCHAR(20) DEFAULT 'user', -- user, admin, manager
    avatar_url VARCHAR(500),
    timezone VARCHAR(50) DEFAULT 'UTC',
    subscription_plan VARCHAR(20) DEFAULT 'free',
    subscription_expires TIMESTAMP,
    is_active BOOLEAN DEFAULT TRUE,
    last_login TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Social media accounts connected by users
CREATE TABLE social_accounts (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
    platform VARCHAR(50) NOT NULL, -- facebook, twitter, instagram, linkedin, tiktok
    account_id VARCHAR(255) NOT NULL,
    username VARCHAR(255) NOT NULL,
    display_name VARCHAR(255),
    profile_image_url VARCHAR(500),
    follower_count INTEGER DEFAULT 0,
    following_count INTEGER DEFAULT 0,
    access_token TEXT,
    refresh_token TEXT,
    token_expires_at TIMESTAMP,
    account_data JSONB,
    is_active BOOLEAN DEFAULT TRUE,
    connected_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_sync TIMESTAMP,
    UNIQUE(user_id, platform, account_id)
);

-- Team management for agencies
CREATE TABLE teams (
    id SERIAL PRIMARY KEY,
    owner_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    invite_code VARCHAR(50) UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE team_members (
    id SERIAL PRIMARY KEY,
    team_id INTEGER REFERENCES teams(id) ON DELETE CASCADE,
    user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
    role VARCHAR(20) DEFAULT 'member', -- owner, admin, member, viewer
    permissions JSONB DEFAULT '[]',
    joined_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(team_id, user_id)
);

Content Management

sql
-- Media library for storing images, videos, etc.
CREATE TABLE media_library (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
    filename VARCHAR(255) NOT NULL,
    original_name VARCHAR(255) NOT NULL,
    file_type VARCHAR(50) NOT NULL,
    file_size INTEGER NOT NULL,
    file_url VARCHAR(500) NOT NULL,
    thumbnail_url VARCHAR(500),
    alt_text VARCHAR(255),
    tags TEXT[],
    folder VARCHAR(255),
    metadata JSONB,
    uploaded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Content templates for reusable posts
CREATE TABLE content_templates (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    content TEXT NOT NULL,
    media_urls TEXT[],
    hashtags TEXT[],
    platforms TEXT[],
    category VARCHAR(100),
    is_public BOOLEAN DEFAULT FALSE,
    usage_count INTEGER DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Main posts table for scheduled/published content
CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
    content TEXT NOT NULL,
    media_urls TEXT[],
    hashtags TEXT[],
    status VARCHAR(20) DEFAULT 'draft', -- draft, scheduled, published, failed
    post_type VARCHAR(20) DEFAULT 'post', -- post, story, reel, carousel
    platforms TEXT[] NOT NULL,
    scheduled_at TIMESTAMP,
    published_at TIMESTAMP,
    auto_publish BOOLEAN DEFAULT TRUE,
    campaign_id INTEGER,
    metadata JSONB,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Platform-specific post data
CREATE TABLE post_publications (
    id SERIAL PRIMARY KEY,
    post_id INTEGER REFERENCES posts(id) ON DELETE CASCADE,
    social_account_id INTEGER REFERENCES social_accounts(id) ON DELETE CASCADE,
    platform_post_id VARCHAR(255),
    status VARCHAR(20) DEFAULT 'pending', -- pending, published, failed
    error_message TEXT,
    engagement_data JSONB,
    published_at TIMESTAMP,
    last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Analytics & Engagement

sql
-- Analytics data collection
CREATE TABLE analytics_data (
    id SERIAL PRIMARY KEY,
    social_account_id INTEGER REFERENCES social_accounts(id) ON DELETE CASCADE,
    post_id INTEGER REFERENCES posts(id),
    date DATE NOT NULL,
    metric_type VARCHAR(50) NOT NULL, -- followers, likes, shares, comments, impressions, reach
    value INTEGER NOT NULL,
    platform_data JSONB,
    collected_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(social_account_id, post_id, date, metric_type)
);

-- Engagement tracking
CREATE TABLE engagements (
    id SERIAL PRIMARY KEY,
    post_publication_id INTEGER REFERENCES post_publications(id) ON DELETE CASCADE,
    engagement_type VARCHAR(50) NOT NULL, -- like, comment, share, save, click
    engagement_count INTEGER DEFAULT 0,
    engagement_rate DECIMAL(5,2),
    recorded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Comments and messages monitoring
CREATE TABLE social_interactions (
    id SERIAL PRIMARY KEY,
    social_account_id INTEGER REFERENCES social_accounts(id) ON DELETE CASCADE,
    platform VARCHAR(50) NOT NULL,
    interaction_type VARCHAR(50) NOT NULL, -- comment, mention, direct_message
    platform_id VARCHAR(255) NOT NULL,
    author_username VARCHAR(255),
    author_name VARCHAR(255),
    content TEXT,
    is_reply BOOLEAN DEFAULT FALSE,
    parent_id VARCHAR(255),
    sentiment VARCHAR(20), -- positive, negative, neutral
    is_responded BOOLEAN DEFAULT FALSE,
    response_text TEXT,
    responded_at TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Campaign & Automation

sql
-- Marketing campaigns
CREATE TABLE campaigns (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    status VARCHAR(20) DEFAULT 'active', -- active, paused, completed
    start_date DATE,
    end_date DATE,
    budget DECIMAL(10,2),
    goals JSONB,
    target_audience JSONB,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Hashtag research and tracking
CREATE TABLE hashtag_analytics (
    id SERIAL PRIMARY KEY,
    hashtag VARCHAR(255) NOT NULL,
    platform VARCHAR(50) NOT NULL,
    usage_count INTEGER DEFAULT 0,
    engagement_rate DECIMAL(5,2),
    trend_score INTEGER DEFAULT 0,
    category VARCHAR(100),
    last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(hashtag, platform)
);

-- Content calendar and scheduling
CREATE TABLE content_calendar (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
    title VARCHAR(255) NOT NULL,
    description TEXT,
    scheduled_date DATE NOT NULL,
    time_slot VARCHAR(20),
    platforms TEXT[],
    content_type VARCHAR(50),
    status VARCHAR(20) DEFAULT 'planned',
    assigned_to INTEGER REFERENCES users(id),
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Automation rules
CREATE TABLE automation_rules (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
    name VARCHAR(255) NOT NULL,
    trigger_type VARCHAR(50) NOT NULL, -- keyword_mention, new_follower, scheduled_time
    trigger_conditions JSONB NOT NULL,
    action_type VARCHAR(50) NOT NULL, -- auto_reply, follow_back, repost
    action_config JSONB NOT NULL,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

API Endpoints

Authentication & User Management

  • POST /api/auth/register - User registration
  • POST /api/auth/login - User login with 2FA
  • GET /api/users/profile - Get user profile
  • PUT /api/users/profile - Update profile
  • POST /api/users/subscription - Update subscription

Social Account Management

  • GET /api/social-accounts - Get connected accounts
  • POST /api/social-accounts/connect - Connect social account
  • DELETE /api/social-accounts/:id - Disconnect account
  • POST /api/social-accounts/:id/refresh - Refresh tokens
  • GET /api/social-accounts/:id/profile - Get account profile data

Content Creation & Management

  • GET /api/posts - Get posts with filters
  • POST /api/posts - Create new post
  • PUT /api/posts/:id - Update post
  • POST /api/posts/:id/schedule - Schedule post
  • POST /api/posts/:id/publish - Publish immediately
  • GET /api/posts/:id/preview - Preview post for platforms

Media Library

  • GET /api/media - Get media library files
  • POST /api/media/upload - Upload media files
  • DELETE /api/media/:id - Delete media file
  • PUT /api/media/:id/metadata - Update file metadata
  • POST /api/media/bulk-tag - Bulk tag media files

Analytics & Reporting

  • GET /api/analytics/overview - Dashboard overview
  • GET /api/analytics/posts/:id - Post performance
  • GET /api/analytics/accounts/:id - Account analytics
  • GET /api/analytics/engagement - Engagement metrics
  • GET /api/analytics/audience - Audience insights
  • GET /api/reports/generate - Generate custom reports

Team Management

  • GET /api/teams - Get user teams
  • POST /api/teams - Create team
  • POST /api/teams/:id/invite - Invite team member
  • PUT /api/teams/:id/members/:userId/role - Update member role
  • GET /api/teams/:id/activity - Team activity log

Campaign Management

  • GET /api/campaigns - Get campaigns
  • POST /api/campaigns - Create campaign
  • GET /api/campaigns/:id/performance - Campaign analytics
  • PUT /api/campaigns/:id/status - Update campaign status

Automation & Scheduling

  • GET /api/calendar - Get content calendar
  • POST /api/calendar/events - Add calendar event
  • GET /api/automation/rules - Get automation rules
  • POST /api/automation/rules - Create automation rule
  • PUT /api/automation/rules/:id/toggle - Enable/disable rule

UI Design

Main Dashboard

┌─────────────────────────────────────────────────────────────┐
│ SocialHub      [📅 Calendar] [📊 Analytics] 👤 Profile     │
├─────────────────────────────────────────────────────────────┤
│ Dashboard                                [+ Create Post]     │
├─────────────────────────────────────────────────────────────┤
│ 📱 Connected Accounts                                       │
│ [📘 Facebook: @mybrand (12.5K)] [📷 Instagram: @mybrand    │
│ [🐦 Twitter: @mybrand (8.2K)]   (18.3K)]                  │
│ [💼 LinkedIn: My Brand (2.1K)]  [🎵 TikTok: @mybrand      │
│                                  (5.7K)]                   │
├─────────────────────────────────────────────────────────────┤
│ 📊 Today's Overview                                         │
│ ┌──────────┐ ┌──────────┐ ┌──────────┐ ┌──────────┐       │
│ │Followers │ │Posts     │ │Engagement│ │Reach     │       │
│ │+127      │ │3 Pending │ │4.2%      │ │23.5K     │       │
│ │📈 +2.3%  │ │1 Failed  │ │📈 +0.8%  │ │📈 +12%   │       │
│ └──────────┘ └──────────┘ └──────────┘ └──────────┘       │
├─────────────────────────────────────────────────────────────┤
│ 🗓️ Upcoming Posts                                          │
│ • Product Launch Announcement - Today 2:00 PM              │
│   📘 Facebook, 📷 Instagram, 🐦 Twitter                   │
│ • Weekly Tips Tuesday - Tomorrow 9:00 AM                   │
│   📷 Instagram Story, 🎵 TikTok                           │
│ • Customer Testimonial - Friday 1:00 PM                    │
│   💼 LinkedIn, 📘 Facebook                                │
├─────────────────────────────────────────────────────────────┤
│ 📈 Recent Performance                                       │
│ [Week] [Month] [Quarter]                                    │
│ Engagement Trend: ████████████████████▲                    │
│ Best performing: "Summer Sale Announcement" (1.2K likes)    │
└─────────────────────────────────────────────────────────────┘

Content Creation Interface

┌─────────────────────────────────────────────────────────────┐
│ Create New Post                          [Save Draft] [→]   │
├─────────────────────────────────────────────────────────────┤
│
Content is user-generated and unverified.
    2 Medium-Sized Project Designs | Claude