Reddit Clone Database Schema database schema

What this schema models

A full Reddit clone database schema — not a core subset, the whole platform. 108 tables, 61 enums and 201 relationships covering accounts, sessions, MFA, OAuth apps, trophies and avatars; communities with rules, flair, emojis, widgets, wiki and traffic; posts, galleries, drafts, scheduling, collections and polls; nested comment trees; voting, karma and five separate ranking functions; coins, awards, premium and orders; AutoMod, mod notes, crowd control, appeals and admin actions; notifications, private messages, modmail and live chat; live threads, search, the complete advertising stack, experiments, feature flags, audit logs and rate limits. THE FOUR DECISIONS 1. The COMMUNITY is the unit, not a follow graph. There is no fan-out feed table anywhere in this schema on purpose. Home is a ranked union over the rows in subreddit_subscriptions, assembled at read time. That single absence is the biggest structural difference between Reddit and a Twitter-shaped model, and it is the reason multireddits are trivial here: a named bag of subreddits produces its own listing for free precisely because the feed is computed rather than materialised. user_follows exists, but it feeds the profile surface and never the home listing. Wiring the home feed to it is how a Reddit clone accidentally turns into Twitter. 2. RANK IS STORED, not computed at read time. posts carries hot_score, best_score, top_score, rising_score and controversial_score as five separate indexed columns, each written when a vote lands. Every Reddit sort is a DIFFERENT function of the same votes, so each one gets its own column and its own index. (subreddit_id, is_stickied, hot_score) IS the front page query — an index scan, never an ORDER BY over a time-decay formula. Only the genuinely expensive combinations, top of the year and all-time and r/all, fall back to the precomputed slices in post_rankings. 3. A COMMENT TREE IS A PATH, not a recursion. comments carries parent_id AND a materialised tree_path with depth, so a thread with ten thousand replies loads as one prefix range query on (post_id, tree_path) ordered by score — not a recursive CTE per expand. best_score is the Wilson lower bound, which is why a comment with 9 of 10 upvotes correctly outranks one with 1 of 1. 4. A VOTE IS A ROW; score is a cache. post_votes and comment_votes use composite primary keys with value constrained to +1 or -1, so a vote is idempotent, changing your mind is an UPDATE and un-voting is a DELETE. Every score, karma and coin figure in the model is a rebuildable cache over an append-only ledger: karma_events for karma, coin_transactions for coins, the vote tables for scores. DETAILS WORTH STEALING - removed_at is NOT deleted_at. A moderator removal and an author deletion are different columns with different visibility rules. That is literally the [removed] versus [deleted] distinction, and collapsing it is the single most common mistake in a Reddit clone. - A profile IS a subreddit. user_profiles.profile_subreddit_id points at a subreddits row with is_profile_subreddit set, so u/name reuses the entire posting, flair and moderation stack instead of needing a parallel one. - Displayed score is not stored score. posts keeps true upvotes and downvotes plus a display_seed, because vote fuzzing is an anti-spam feature and belongs in the model rather than being invented in the view layer. - A crosspost is a self-reference on posts, never a copy of the row. - AutoMod rules are DATA, not code. automod_rules holds the field, operator, pattern and action, so a community changes its own filtering without a deploy, and match_count tells moderators which rule is doing the work and which has never fired. - Two-tier moderation is one nullable column. reports.rule_id set means the report cites a subreddit rule and lands in that community queue; null with a site reason escalates to admins. - A ban is a row with an expiry, never a boolean, and subreddit_bans and site_suspensions are deliberately separate tables because they are issued by different people and appealed to different people. is_shadowban cannot be a status, because the user must not be told. - mod_notes is the institutional memory a mod team keeps on a user, with a label that renders as a badge. It stops a team re-litigating the same person every six months. - Crowd control collapses rather than removes, which is why comments.is_collapsed is stored state. - Money is integers in minor units beside an explicit currency. Coins are their own signed ledger with balance_after stored, so a statement renders without re-summing history and a mismatch is an alarm. - A promoted post is a real posts row with is_promoted set, so it is voted on, commented on and reported through exactly the same machinery as organic content. - Every counting table is pre-aggregated per day: post_views, post_insights_daily, subreddit_traffic_daily, ad_metrics_daily. Raw hit logs belong in an event pipeline, never in the database the site reads on every page load. - Multireddits, contest mode, comment_score_hide_minutes, approved users, per-page wiki editors, vote manipulation signals and experiment assignments are all modelled — the features most clones forget. WHAT YOU GET Clean DBML that imports straight into DB Planner, plus a laid-out board with cards colour-coded across eight zones — identity, communities, content, voting, economy, moderation, messaging and platform — sticky notes explaining every decision, and a Note on almost every table saying why it exists. It also ships six Mermaid FLOWCHARTS, cross-linked to the exact tables each step writes to: vote-to-rank, comment-tree, moderation-pipeline, feed-assembly, post-submission, and coins-and-awards. Those links and the labelled groups are things neither DBML nor Mermaid can express on their own, which is the point — you can see the write path, not just the shape. Postgres-flavoured types (uuid, timestamptz, smallint, integer minor units) that port cleanly to MySQL or SQL Server. Useful for developers actually building a Reddit clone or a community platform, students who need a realistic Reddit ER diagram for a DBMS project, and teams designing ranking, comment-tree or karma infrastructure for the first time who want to argue with a complete reference model before writing their first migration.

Tables (108)

Relationships (201)

• ad_campaigns.advertiser_id references advertisers.id many to one
• ad_metrics_daily.ad_id references ads.id many to one
• ad_targeting_rules.campaign_id references ad_campaigns.id many to one
• admin_actions.admin_id references users.id many to one
• ads.campaign_id references ad_campaigns.id many to one
• ads.creative_asset_id references media_assets.id many to one
• ads.post_id references posts.id many to one
• automod_matches.rule_id references automod_rules.id many to one
• automod_rules.created_by references users.id many to one
• automod_rules.subreddit_id references subreddits.id many to one
• award_gifts.award_id references awards.id many to one
• award_gifts.giver_id references users.id many to one
• award_gifts.receiver_id references users.id many to one
• awards.subreddit_id references subreddits.id many to one
• ban_appeals.reviewer_id references users.id many to one
• ban_appeals.subreddit_ban_id references subreddit_bans.id many to one
• ban_appeals.suspension_id references site_suspensions.id many to one
• ban_appeals.user_id references users.id many to one
• banned_domains.added_by references users.id many to one
• banned_domains.subreddit_id references subreddits.id many to one
• chat_channel_members.channel_id references chat_channels.id many to one
• chat_channel_members.last_read_message_id references chat_messages.id many to one
• chat_channel_members.user_id references users.id many to one
• chat_channels.created_by references users.id many to one
• chat_channels.subreddit_id references subreddits.id many to one
• chat_messages.author_id references users.id many to one
• chat_messages.channel_id references chat_channels.id many to one
• chat_messages.media_asset_id references media_assets.id many to one
• chat_messages.reply_to_id references chat_messages.id many to one
• chat_reactions.message_id references chat_messages.id many to one
• chat_reactions.user_id references users.id many to one
• coin_transactions.user_id references users.id many to one
• collection_posts.collection_id references collections.id many to one
• collection_posts.post_id references posts.id many to one
• collections.author_id references users.id many to one
• collections.subreddit_id references subreddits.id many to one
• comment_votes.comment_id references comments.id many to one
• comment_votes.user_id references users.id many to one
• comments.author_id references users.id many to one
• comments.parent_id references comments.id many to one
• comments.post_id references posts.id many to one
• comments.removal_reason_id references removal_reasons.id many to one
• comments.removed_by references users.id many to one
• content_revisions.editor_id references users.id many to one
• crowd_control_settings.updated_by references users.id many to one
• drafts.author_id references users.id many to one
• drafts.flair_id references flairs.id many to one
• drafts.subreddit_id references subreddits.id many to one
• email_change_requests.user_id references users.id many to one
• experiment_assignments.experiment_id references experiments.id many to one
• experiment_assignments.user_id references users.id many to one
• feature_flags.updated_by references users.id many to one
• flairs.subreddit_id references subreddits.id many to one
• hidden_posts.post_id references posts.id many to one
• hidden_posts.user_id references users.id many to one
• karma_events.subreddit_id references subreddits.id many to one
• karma_events.user_id references users.id many to one
• live_thread_contributors.live_thread_id references live_threads.id many to one
• live_thread_contributors.user_id references users.id many to one
• live_thread_updates.author_id references users.id many to one
• live_thread_updates.live_thread_id references live_threads.id many to one
• live_threads.created_by references users.id many to one
• media_assets.uploader_id references users.id many to one
• mentions.mentioned_subreddit_id references subreddits.id many to one
• mentions.mentioned_user_id references users.id many to one
• messages.parent_id references messages.id many to one
• messages.recipient_id references users.id many to one
• messages.sender_id references users.id many to one
• mfa_devices.user_id references users.id many to one
• mod_actions.automod_rule_id references automod_rules.id many to one
• mod_actions.moderator_id references users.id many to one
• mod_actions.report_id references reports.id many to one
• mod_actions.subreddit_id references subreddits.id many to one
• mod_actions.target_user_id references users.id many to one
• mod_notes.moderator_id references users.id many to one
• mod_notes.subreddit_id references subreddits.id many to one
• mod_notes.user_id references users.id many to one
• modmail_conversations.assigned_to references users.id many to one
• modmail_conversations.participant_id references users.id many to one
• modmail_conversations.subreddit_id references subreddits.id many to one
• modmail_messages.author_id references users.id many to one
• modmail_messages.conversation_id references modmail_conversations.id many to one
• multireddit_subreddits.multireddit_id references multireddits.id many to one
• multireddit_subreddits.subreddit_id references subreddits.id many to one
• multireddits.owner_id references users.id many to one
• notification_settings.user_id references users.id many to one
• notifications.actor_id references users.id many to one
• notifications.subreddit_id references subreddits.id many to one
• notifications.user_id references users.id many to one
• oauth_access_tokens.application_id references oauth_applications.id many to one
• oauth_access_tokens.user_id references users.id many to one
• oauth_applications.owner_id references users.id many to one
• order_items.order_id references orders.id many to one
• orders.payment_method_id references payment_methods.id many to one
• orders.user_id references users.id many to one
• password_resets.user_id references users.id many to one
• payment_methods.user_id references users.id many to one
• poll_options.post_id references polls.post_id many to one
• poll_votes.option_id references poll_options.id many to one
• poll_votes.user_id references users.id many to one
• post_insights_daily.post_id references posts.id many to one
• post_media.media_asset_id references media_assets.id many to one
• post_media.post_id references posts.id many to one
• post_rankings.post_id references posts.id many to one
• post_rankings.subreddit_id references subreddits.id many to one
• post_views.post_id references posts.id many to one
• post_votes.post_id references posts.id many to one
• post_votes.user_id references users.id many to one
• posts.author_id references users.id many to one
• posts.collection_id references collections.id many to one
• posts.crosspost_parent_id references posts.id many to one
• posts.flair_id references flairs.id many to one
• posts.id references polls.post_id one to one
• posts.removal_reason_id references removal_reasons.id many to one
• posts.removed_by references users.id many to one
• posts.subreddit_id references subreddits.id many to one
• premium_subscriptions.gifted_by references users.id many to one
• premium_subscriptions.user_id references users.id many to one
• push_subscriptions.device_id references user_devices.id many to one
• recovery_codes.user_id references users.id many to one
• related_subreddits.related_subreddit_id references subreddits.id many to one
• related_subreddits.subreddit_id references subreddits.id many to one
• removal_reasons.subreddit_id references subreddits.id many to one
• report_reasons.subreddit_id references subreddits.id many to one
• reports.report_reason_id references report_reasons.id many to one
• reports.reporter_id references users.id many to one
• reports.resolved_by references users.id many to one
• reports.rule_id references subreddit_rules.id many to one
• reports.subreddit_id references subreddits.id many to one
• saved_items.user_id references users.id many to one
• saved_searches.subreddit_id references subreddits.id many to one
• saved_searches.user_id references users.id many to one
• scheduled_posts.author_id references users.id many to one
• scheduled_posts.draft_id references drafts.id many to one
• scheduled_posts.published_post_id references posts.id many to one
• scheduled_posts.subreddit_id references subreddits.id many to one
• search_queries.subreddit_id references subreddits.id many to one
• search_queries.user_id references users.id many to one
• site_suspensions.issued_by references users.id many to one
• site_suspensions.lifted_by references users.id many to one
• site_suspensions.user_id references users.id many to one
• subreddit_approved_users.added_by references users.id many to one
• subreddit_approved_users.subreddit_id references subreddits.id many to one
• subreddit_approved_users.user_id references users.id many to one
• subreddit_bans.banned_by references users.id many to one
• subreddit_bans.lifted_by references users.id many to one
• subreddit_bans.rule_id references subreddit_rules.id many to one
• subreddit_bans.subreddit_id references subreddits.id many to one
• subreddit_bans.user_id references users.id many to one
• subreddit_emojis.created_by references users.id many to one
• subreddit_emojis.subreddit_id references subreddits.id many to one
• subreddit_karma.subreddit_id references subreddits.id many to one
• subreddit_karma.user_id references users.id many to one
• subreddit_moderators.invited_by references users.id many to one
• subreddit_moderators.subreddit_id references subreddits.id many to one
• subreddit_moderators.user_id references users.id many to one
• subreddit_mutes.muted_by references users.id many to one
• subreddit_mutes.subreddit_id references subreddits.id many to one
• subreddit_mutes.user_id references users.id many to one
• subreddit_rules.subreddit_id references subreddits.id many to one
• subreddit_styles.updated_by references users.id many to one
• subreddit_subscriptions.subreddit_id references subreddits.id many to one
• subreddit_subscriptions.user_id references users.id many to one
• subreddit_topics.subreddit_id references subreddits.id many to one
• subreddit_topics.topic_id references topics.id many to one
• subreddit_traffic_daily.subreddit_id references subreddits.id many to one
• subreddit_widgets.subreddit_id references subreddits.id many to one
• subreddits.created_by references users.id many to one
• subreddits.id references subreddit_styles.subreddit_id one to one
• subreddits.id references crowd_control_settings.subreddit_id one to one
• topics.parent_id references topics.id many to one
• trending_subreddits.subreddit_id references subreddits.id many to one
• user_avatar_items.asset_id references avatar_assets.id many to one
• user_avatar_items.user_id references users.id many to one
• user_blocks.blocked_id references users.id many to one
• user_blocks.blocker_id references users.id many to one
• user_devices.user_id references users.id many to one
• user_flairs.assigned_by references users.id many to one
• user_flairs.flair_id references flairs.id many to one
• user_flairs.subreddit_id references subreddits.id many to one
• user_flairs.user_id references users.id many to one
• user_follows.followee_id references users.id many to one
• user_follows.follower_id references users.id many to one
• user_profiles.profile_subreddit_id references subreddits.id many to one
• user_sessions.device_id references user_devices.id many to one
• user_sessions.user_id references users.id many to one
• user_trophies.granted_by references users.id many to one
• user_trophies.subreddit_id references subreddits.id many to one
• user_trophies.trophy_id references trophies.id many to one
• user_trophies.user_id references users.id many to one
• username_history.changed_by references users.id many to one
• username_history.user_id references users.id many to one
• users.id references user_profiles.user_id one to one
• users.id references user_preferences.user_id one to one
• users.id references coin_balances.user_id one to one
• vote_anomalies.user_id references users.id many to one
• wiki_page_editors.added_by references users.id many to one
• wiki_page_editors.user_id references users.id many to one
• wiki_page_editors.wiki_page_id references wiki_pages.id many to one
• wiki_pages.last_edited_by references users.id many to one
• wiki_pages.subreddit_id references subreddits.id many to one

How to use this template

1. Open the diagram to see the tables and how they relate.
2. Copy it into your workspace. You get your own editable copy.
3. Export it as PostgreSQL, MySQL, or MSSQL DDL, or as C# models.

Frequently asked questions

What is in the Reddit Clone Database Schema schema?

108 tables and 201 relationships, across 890 columns. The table names and the full relationship list are shown on this page.

Can I export it to PostgreSQL or MySQL?

Yes. Every DB Planner template exports as DBML, PostgreSQL, MySQL, or MSSQL DDL, and as C# models.

Is this template free?

Yes. You can open it in the browser without an account, and copy it into your own workspace with a free DB Planner account.

Can I change it after copying it?

Yes. A copy is yours: you can rename tables, add columns, and export it. Changes the author makes later do not affect your copy.