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.
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"
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"
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 |