Skip to content

Latest commit

 

History

History
415 lines (400 loc) · 12.3 KB

database_erd.md

File metadata and controls

415 lines (400 loc) · 12.3 KB

Database Entity-Relationship Diagrams (ERD)

This document provides a highly accurate ERD generated directly from the deep analysis of the codebase schemas. It is split into our two primary databases: PostgreSQL (Relational) and MongoDB (Document), followed by Cross-Database Linkages.

PostgreSQL (Drizzle ORM)

The Postgres database acts as the single source of truth for users, social connections, stats, leaderboards, votes, and structured hierarchy.

erDiagram
    %% Tables
    users {
        uuid id PK
        text clerkId
        text username
        text fullName
        text email
        text avatarUrl
        text status
        text role
        text githubUsername
        text linkedinUsername
        text leetcodeUsername
        timestamp createdAt
        timestamp updatedAt
    }
    user_stats {
        uuid userId PK,FK
        bigint totalPoints
        bigint arenaPoints
        integer totalSolved
        integer easySolved
        integer mediumSolved
        integer hardSolved
        integer arenaGames
        integer currentStreak
        integer bestStreak
        date lastSolveDate
        jsonb languageCounts
    }
    user_activity {
        uuid userId PK,FK
        date date PK
        integer pointsEarned
        integer arenaPointsEarned
        integer submissions
        integer matches
    }
    user_solved_problems {
        uuid userId PK,FK
        text problemId PK "References Mongo"
        timestamp solvedAt
    }
    user_solved_languages {
        uuid userId PK,FK
        text problemId PK
        text languageId PK
        timestamp solvedAt
    }
    user_academy_exercises {
        uuid userId PK,FK
        text trackSlug PK
        text exerciseSlug PK
        timestamp solvedAt
    }
    follows {
        uuid followerId PK,FK
        uuid followingId PK,FK
        timestamp createdAt
    }
    contests {
        uuid id PK
        integer clistId
        text title
        text description
        text platform
        timestamp startTime
        timestamp endTime
        integer duration
        text href
        integer resourceId
        text icon
        text status
        timestamp createdAt
        timestamp updatedAt
    }
    categories {
        uuid id PK
        uuid parentId FK
        text name
        text slug
        text description
        integer order
        timestamp createdAt
    }
    category_problems {
        uuid categoryId PK,FK
        text problemId PK
        integer order
    }
    solutions {
        uuid id PK
        uuid userId FK
        text problemId "References Mongo"
        text problemTitle
        text problemSlug
        text title
        text content
        text language
        integer upvotes
        integer downvotes
        timestamp createdAt
        timestamp updatedAt
    }
    solution_votes {
        uuid userId PK,FK
        uuid solutionId PK,FK
        integer voteType
        timestamp createdAt
    }
    votes {
        uuid userId PK,FK
        text targetType PK "project | solution | roadmap | resource"
        text targetId PK "References Mongo or Postgres target"
        integer voteType "1 for upvote, -1 for downvote"
        timestamp createdAt
        timestamp updatedAt
    }
    workspaces {
        uuid id PK
        uuid userId FK
        text name
        boolean isDefault
        timestamp createdAt
        timestamp updatedAt
    }
    diagrams {
        uuid id PK
        uuid workspaceId FK
        text title
        jsonb documentState
        timestamp createdAt
        timestamp updatedAt
    }
    chat_threads {
        uuid id PK
        uuid diagramId FK
        text title
        timestamp createdAt
        timestamp updatedAt
    }
    chat_messages {
        uuid id PK
        uuid threadId FK
        text role
        text content
        timestamp createdAt
    }
    bug_reports {
        uuid id PK
        text title
        text description
        text type
        jsonb images
        text status
        timestamp createdAt
        timestamp updatedAt
    }

    %% Relationships
    users ||--o| user_stats : "has"
    users ||--o{ user_activity : "logs"
    users ||--o{ user_solved_problems : "solves"
    users ||--o{ user_solved_languages : "uses"
    users ||--o{ user_academy_exercises : "completes"
    users ||--o{ follows : "follower_id"
    users ||--o{ follows : "following_id"
    users ||--o{ solutions : "writes"
    users ||--o{ solution_votes : "casts"
    users ||--o{ votes : "casts"
    users ||--o{ workspaces : "owns"
    
    solutions ||--o{ solution_votes : "receives"
    workspaces ||--o{ diagrams : "contains"
    diagrams ||--o{ chat_threads : "has"
    chat_threads ||--o{ chat_messages : "contains"
    categories ||--o| categories : "parent_id"
    categories ||--o{ category_problems : "categorizes"
Loading

MongoDB (Mongoose)

MongoDB handles unstructured, heavy, graph-based, or heavily nested data (Problems, Academy, Roadmaps, Projects, Resources). Since MongoDB is schema-less by nature, this diagram represents the Mongoose application-level schema definitions.

erDiagram
    Problem {
        String title
        String problem_id "Unique"
        String difficulty
        String problem_slug "Unique"
        Array topics
        String description
        Array examples
        Array constraints
        Array follow_ups
        Array hints
        Object code_snippets
        String problem_type "function|class"
        Object function_signature
        Object class_signature
        Object judging_policy
        String solutions
        Boolean is_premium
        Date createdAt
        Date updatedAt
    }
    ProblemTest {
        String problem_id "Index"
        String type "public|hidden|stress"
        Array cases "Array of TestCases"
        Date createdAt
        Date updatedAt
    }
    Submission {
        String problemId "Index"
        String problemTitle
        String userId "Index"
        String languageId
        String sourceCode
        String status "PENDING|ACCEPTED|etc"
        Number time
        Number memory
        Mixed details
        Date createdAt
        Date updatedAt
    }
    ArenaRoom {
        String room_id "Unique"
        String status
        Array users
        Array problems
        Date created_at
        Date expires_at
    }
    ArenaMatch {
        String match_id "Unique"
        String room_id "Index"
        String status
        Array players
        Array problems
        Object scoreboard
        Date started_at
        Date finished_at
    }
    ArenaSubmission {
        String submission_id
        String match_id
        String user_id
        String problem_id
        String status
        Date submitted_at
    }
    Company {
        String name
        String slug "Unique"
        Array problem_ids
    }
    SystemDesignTopic {
        String topic_id "Unique"
        String slug "Unique"
        String title
        Number order
        String content
        Date createdAt
        Date updatedAt
    }
    AcademyTrack {
        String slug "Unique (Track ID)"
        Mixed data "JSON Config"
        Date createdAt
        Date updatedAt
    }
    AcademyConfig {
        String slug "Unique (Track ID)"
        Mixed data "JSON Config"
        Date createdAt
        Date updatedAt
    }
    AcademyConcept {
        String trackSlug "Index"
        String conceptSlug "Index"
        Mixed data "JSON Config"
        Date createdAt
        Date updatedAt
    }
    AcademyExercise {
        String trackSlug "Index"
        String exerciseSlug "Index"
        Mixed data "JSON Config"
        Date createdAt
        Date updatedAt
    }
    RoadmapCategory {
        String categoryId "Unique"
        String title
        Number count
        Number order
        Array sections "Array of RoadmapCategorySection"
        Date createdAt
        Date updatedAt
    }
    Roadmap {
        String slug "Unique"
        Object title "card, page"
        String description
        String type "role|skill"
        Array categories
        Number nodesCount
        Array nodes "React Flow nodes"
        Array edges "React Flow edges"
        Object dimensions "width, height"
        Array relatedRoadmaps
        Object seo
        String status "draft|published"
        Date createdAt
        Date updatedAt
    }
    RoadmapTopic {
        String roadmapSlug "Index"
        String nodeId "Index"
        String topicSlug
        String title
        String description
        Array resources "Array of ResourceItem"
        String content
        Date createdAt
        Date updatedAt
    }
    RoadmapProject {
        String slug "Unique"
        String title
        String description
        String difficulty "beginner|intermediate|advanced"
        Array roadmapIds "Index"
        Array topics
        Array skills
        Number order
        String type
        Object content "overview, markdown, previewImage, requirements, checklist, techStack"
        String origin "official|community"
        String status "pending|approved|rejected|archived|updated"
        Object submittedBy "userId, username, fullName, avatarUrl"
        Object review "reviewedBy, rejectionReason, adminNotes"
        Object stats "startedCount, completedCount, upvotesCount"
        Date createdAt
        Date updatedAt
    }
    RoadmapResource {
        String title
        String description
        String url
        String thumbnailUrl
        String type "YouTube Playlist|Video|Documentation|Course|Article"
        String author
        Array roadmapIds "Index"
        Number order
        String origin "official|community"
        String status "pending|approved|rejected|archived"
        Object submittedBy "userId, username, fullName, avatarUrl"
        Object review "reviewedBy, rejectionReason, adminNotes"
        Object stats "upvotesCount, viewsCount"
        Date createdAt
        Date updatedAt
    }

    %% Problem & Arena Relationships
    Problem ||--o| ProblemTest : "problem_id"
    Problem ||--o{ Submission : "problemId"
    Problem ||--o{ ArenaRoom : "problems"
    ArenaRoom ||--o{ ArenaMatch : "room_id"
    ArenaMatch ||--o{ ArenaSubmission : "match_id"
    Company ||--o{ Problem : "problem_ids"

    %% Academy Relationships
    AcademyTrack ||--o| AcademyConfig : "slug"
    AcademyTrack ||--o{ AcademyConcept : "trackSlug"
    AcademyTrack ||--o{ AcademyExercise : "trackSlug"

    %% Roadmaps, Projects & Resources Relationships
    RoadmapCategory ||--o{ Roadmap : "categorizes"
    Roadmap ||--o{ RoadmapTopic : "roadmapSlug"
    Roadmap ||--o{ RoadmapProject : "roadmapIds"
    Roadmap ||--o{ RoadmapResource : "roadmapIds"
Loading

Cross-Database Linkages (PostgreSQL ⇄ MongoDB)

While PostgreSQL and MongoDB operate as decoupled databases, logical foreign references span between them:

Source (PostgreSQL) Reference Field Target (MongoDB) Target Key Purpose
user_solved_problems problemId Problem problem_id Tracks which user solved which algorithm problem
user_solved_languages problemId Problem problem_id Tracks languages used per solved problem
user_academy_exercises trackSlug, exerciseSlug AcademyExercise trackSlug, exerciseSlug User completion progress in Academy tracks
category_problems problemId Problem problem_id Custom curator categories grouping algorithm problems
solutions problemId Problem problem_id User community solutions attached to a problem
votes targetId (targetType='roadmap') Roadmap slug User upvotes/downvotes on developer roadmaps
votes targetId (targetType='project') RoadmapProject slug User upvotes/downvotes on developer projects
votes targetId (targetType='resource') RoadmapResource _id User upvotes/downvotes on community resources
votes targetId (targetType='solution') solutions (Postgres) id User upvotes/downvotes on solutions
RoadmapProject submittedBy.userId users (Postgres) id Identifies author of community submitted projects
RoadmapResource submittedBy.userId users (Postgres) id Identifies author of community submitted resources
Submission userId users (Postgres) id Links code run submissions back to the user profile
ArenaMatch players[].user_id users (Postgres) id Links battle match participants back to user records