395

Stack Exchange releases "data dumps" of all its publicly available content roughly every three months via archive.org, and also makes that information queryable over the Internet at the Stack Exchange Data Explorer (SEDE) (updated weekly, on Sunday). Over time, as new features and other bits of data are introduced to Stack Exchange sites, the "anatomy" of the data dump is likely to change.

Return to FAQ index

0

2 Answers 2

360

About this list:

  • foreign key fields are formatted as links to their parent table
  • italic table names are found in both the Data Dump on Archive.org as well as in the SEDE
  • if a column is optional or nullable, when it is NULL, it won't appear as an attribute in data dump / <row ...>.

Posts / PostsWithDeleted

You find in Posts all non-deleted posts. PostsWithDeleted includes rows with deleted posts while sharing the same columns with Posts but for deleted posts only a few fields populated which are marked with a 1 below.

Note that starting with the July 2025 data dump, the Posts.xml file will contain 2 artificially generated posts with Id values of 1000000001 & 1000000010. These do not exist in the SEDE database, and can be excluded.

  • Id1

  • PostTypeId1 (listed in the PostTypes table)
    1 = Question
    2 = Answer
    3 = Orphaned tag wiki
    4 = Tag wiki excerpt
    5 = Tag wiki
    6 = Moderator nomination
    7 = "Wiki placeholder" (Appears to include auxiliary site content like the help center introduction, election description, and the tour page's introduction, ask, and don't ask sections)
    8 = Privilege wiki
    9 = Article
    10 = HelpArticle
    12 = Collection
    13 = ModeratorQuestionnaireResponse
    14 = Announcement
    15 = CollectiveDiscussion
    17 = CollectiveCollection

  • AcceptedAnswerId (only present if PostTypeId = 1)

  • ParentId 1 (only present if PostTypeId = 2)

  • CreationDate1

  • DeletionDate1 (only non-null for the SEDE PostsWithDeleted table. Deleted posts are not present on Posts. Column not present on data dump.)

  • Score1 (generally non-zero for only Questions, Answers, and Moderator Nominations)

  • ViewCount (nullable)

  • Body (as rendered HTML, not Markdown)

  • OwnerUserId (only present if user has not been deleted; always -1 for tag wiki entries, i.e. the community user owns them)

  • OwnerDisplayName (nullable)

  • LastEditorUserId (nullable)

  • LastEditorDisplayName (nullable)

  • LastEditDate (e.g. 2009年03月05日T22:28:34.823) - the date and time of the most recent edit to the post (nullable)

  • LastActivityDate (e.g. 2009年03月11日T12:51:01.480) - datetime of the post's most recent activity

  • Title - question title (PostTypeId = 1), or on Stack Overflow, the tag name for some tag wikis and excerpts (PostTypeId = 4/5)

  • Tags1 - question tags (PostTypeId = 1), or on Stack Overflow, the subject tag of some tag wikis and excerpts (PostTypeId = 4/5)

  • AnswerCount - the number of undeleted answers (only present if PostTypeId = 1)

  • CommentCount (nullable)

  • FavoriteCount (nullable)

  • ClosedDate1 (present only if the post is closed)

  • CommunityOwnedDate (present only if post is community wiki'd)

  • ContentLicense1

Users

Comments

  • Id
  • PostId
  • Score
  • Text (Comment body)
  • CreationDate
  • UserDisplayName
  • UserId (Optional. Absent if user has been deleted)
  • ContentLicense

Badges

  • Id

  • UserId

  • Name (Name of the badge)

  • Date (e.g. 2008年09月15日T08:55:03.923)

  • Class
    1 = Gold
    2 = Silver
    3 = Bronze

  • TagBased = True if badge is for a tag, otherwise it is a named badge

CloseAsOffTopicReasonTypes

  • Id
  • IsUniversal
  • InputTitle
  • MarkdownInputGuidance shown while flagging/voting
  • MarkdownPostOwnerGuidance shown when closed to OP
  • MarkdownPublicGuidance shown when closed to priviledged users
  • MarkdownConcensusDescription (sic) (nullable) shown above the public or post owner guidance.
  • CreationDate
  • CreationModeratorId
  • ApprovalDate
  • ApprovalModeratorId
  • DeactivationDate
  • DeactivationModeratorId

PendingFlags

Despite the name, this table in fact contains close-related flags and votes.

  • Id

  • FlagTypeId (listed in the FlagTypes table)
    13 = canned flag for closure
    14 = vote to close
    15 = vote to reopen

  • PostId

  • CreationDate

  • CloseReasonTypeId (listed in the CloseReasonTypes table)

  • CloseAsOffTopicReasonTypeId, if CloseReasonTypeId = 102 (off-topic) (listed in the CloseAsOffTopicReasonTypes table)

  • DuplicateOfQuestionId, if CloseReasonTypeId is 1 or 101 (old duplicate or current duplicate)

  • BelongsOnBaseHostAddress, for votes to close and migrate

PostFeedback

Collects up and down votes from anonymous visitor and/or unregistered users. See here

  • Id

  • PostId

  • IsAnonymous

  • VoteTypeId (listed in the VoteTypes table)
    2 = UpMod
    3 = DownMod

  • CreationDate

PostHistory

(Note that the history of deleted posts is scrubbed from this table in SEDE.)

  • Id
  • PostHistoryTypeId (listed in the PostHistoryTypes table)
    1 = Initial Title - initial title (questions only)
    2 = Initial Body - initial post raw body text
    3 = Initial Tags - initial list of tags (questions only)
    4 = Edit Title - modified title (questions only)
    5 = Edit Body - modified post body (raw markdown)
    6 = Edit Tags - modified list of tags (questions only)
    7 = Rollback Title - reverted title (questions only)
    8 = Rollback Body - reverted body (raw markdown)
    9 = Rollback Tags - reverted list of tags (questions only)
    10 = Post Closed - post voted to be closed
    11 = Post Reopened - post voted to be reopened
    12 = Post Deleted - post voted to be removed
    13 = Post Undeleted - post voted to be restored
    14 = Post Locked - post locked by moderator
    15 = Post Unlocked - post unlocked by moderator
    16 = Community Owned - post now community owned
    17 = Post Migrated - post migrated - now replaced by 35/36 (away/here)
    18 = Question Merged - question merged with deleted question
    19 = Question Protected - question was protected by a moderator.
    20 = Question Unprotected - question was unprotected by a moderator.
    21 = Post Disassociated - OwnerUserId removed from post by admin
    22 = Question Unmerged - answers/votes restored to previously merged question
    24 = Suggested Edit Applied
    25 = Post Tweeted
    31 = Comment discussion moved to chat
    33 = Post notice added - comment contains foreign key to PostNotices
    34 = Post notice removed - comment contains foreign key to PostNotices
    35 = Post migrated away - replaces id 17
    36 = Post migrated here - replaces id 17
    37 = Post merge source
    38 = Post merge destination
    50 = Bumped by Community User
    52 = Question became hot network question (main) / Hot Meta question (meta)
    53 = Question removed from hot network/meta questions by a moderator
    66 = Created from Ask Wizard

Additionally, in older dumps (all guesses, all seem no longer present in the wild):
23 = Unknown dev related event
26 = Vote nullification by dev (ERM?)
27 = Post unmigrated/hidden moderator migration?
28 = Unknown suggestion event
29 = Unknown moderator event (possibly de-wikification?)
30 = Unknown event (too rare to guess)

  • PostId

  • RevisionGUID: At times more than one type of history record can be recorded by a single action. All of these will be grouped using the same RevisionGUID

  • CreationDate (e.g. 2009年03月05日T22:28:34.823)

  • UserId

  • UserDisplayName: populated if a user has been removed and no longer referenced by user Id; also happens to the author of a migrated post

  • Comment: This field will contain the comment made by the user who edited a post.

    • If PostHistoryTypeId = 10, this field contains the CloseReasonId of the close reason (listed in CloseReasonTypes):
      Old close reasons:
      1 = Exact Duplicate
      2 = Off-topic
      3 = Subjective and argumentative
      4 = Not a real question
      7 = Too localized
      10 = General reference
      20 = Noise or pointless (Meta sites only)
      Current close reasons:
      101 = Duplicate
      102 = Off-topic
      103 = Unclear what you're asking
      104 = Too broad
      105 = Primarily opinion-based

    • If PostHistoryTypeId in (33,34) this field contains the PostNoticeId of the PostNotice

  • Text: A raw version of the new value for a given revision
    - If PostHistoryTypeId in (10,11,12,13,14,15,19,20,35) this column will contain a JSON encoded string with all users who have voted for the PostHistoryTypeId
    - If it is a duplicate close vote, the JSON string will contain an array of original questions as OriginalQuestionIds
    - If PostHistoryTypeId = 17 this column will contain migration details of either from <url> or to <url>

  • ContentLicense

PostLinks

  • Id primary key
  • CreationDate when the link was created
  • PostId id of source post
  • RelatedPostId id of target/related post
  • LinkTypeId type of link
    1 = Linked (PostId contains a link to RelatedPostId)
    3 = Duplicate (PostId is a duplicate of RelatedPostId)

RelatedQuestions

  • PostId
  • RelatedPostId
  • Position
  • Score

PostNotices

  • Id
  • PostId
  • PostNoticeTypeId
    1 = Citation needed
    2 = Current event
    3 = Insufficient explanation
    10 = Current answers are outdated
    11 = Draw attention
    12 = Improve details
    13 = Authoritative reference needed
    14 = Canonical answer required
    15 = Reward existing answer
    20 = Content dispute
    21 = Offtopic comments
    22 = Historical significance
    23 = Wiki Answer
    24 = Policy Lock (SO Collectives)
    25 = Recommended Answer (SO Collectives)
    26 = Posted by Recognized Member/Admin (SO Collectives)
    27 = Endorsed Edit (SO Collectives)
    28 = Obsolete (SO Collectives)
    1000 = Redditted (SO)
    9001 = DMCA Takedown
  • CreationDate
  • DeletionDate
  • ExpiryDate
  • Body (when present contains the custom text shown with the notice)
  • OwnerUserId
  • DeletionUserId

PostNoticeTypes

  • Id
  • ClassId
    0 = DMCA or Redditted
    1 = Historical lock
    2 = Bounty
    4 = Moderator notice (lock)
  • Name
  • Body (contains the default notice text)
  • IsHidden
  • Predefined
  • PostNoticeDurationId
    -1 = No duration specified
    1 = 7 days (bounty)

PostTags

ReviewRejectionReasons

Canned rejection reasons for suggested edits. See Show all review rejection reasons

  • Id
  • Name
  • Description
  • PostTypeId (for reasons that apply to Wiki (5) or Excerpt (6) post types only, otherwise null)

ReviewTaskResults

  • Id

  • ReviewTaskId

  • ReviewTaskResultTypeId (listed in ReviewTaskResultTypes)
    1 = Skip
    2 = Approve (suggested edits)
    3 = Reject (suggested edits)
    4 = Delete (low quality)
    5 = Edit (first posts, late answers, low quality)
    6 = Close (close, low quality)
    7 = Looks OK (low quality)
    8 = Do Not Close (close)
    9 = Recommend Deletion (low quality answer)
    10 = Recommend Close (low quality question)
    11 = Other Action (first posts), previously "I'm Done"
    12 = Reopen (reopen)
    13 = Leave Closed (reopen)
    14 = Edit and Reopen (reopen)
    15 = Excellent (community evaluation)
    16 = Satisfactory (community evaluation)
    17 = Needs Improvement (community evaluation)
    18 = No Action Needed (first posts, late answers)

  • CreationDate date only (2018年07月31日 00:00:00); time data looks to be purposefully removed to protect user privacy

  • RejectionReasonId (for suggested edits; listed in ReviewRejectionReasons)

  • Comment

ReviewTasks

  • Id

  • ReviewTaskTypeId (listed in ReviewTaskTypes)
    1 = Suggested Edit
    2 = Close Votes
    3 = Low Quality Posts
    4 = First Post
    5 = Late Answer
    6 = Reopen Vote
    7 = Community Evaluation
    8 = Link Validation
    9 = Flagged Posts
    10 = Triage
    11 = Helper
    12 = First Questions
    13 = First Answers

  • CreationDate date only (2018年07月31日 00:00:00)

  • DeletionDate date only (2018年07月31日 00:00:00)

  • ReviewTaskStateId (listed in ReviewTaskStates)
    1 = Active
    2 = Completed
    3 = Invalidated

  • PostId

  • SuggestedEditId (for suggested edits, which have their own numbering for historical reasons)

  • CompletedByReviewTaskId id associated to the ReviewTaskResult that stores the outcome of a completed review.

SuggestedEdits

If both approval and rejection date are null then this edit is still in review (and its corresponding entry in ReviewTasks will have an active state as well).

  • Id
  • PostId
  • CreationDate
  • ApprovalDate - NULL if not approved (yet).
  • RejectionDate - NULL if not rejected (yet).
  • OwnerUserId
  • Comment
  • Text
  • Title
  • Tags
  • RevisionGUID

SuggestedEditVotes

Tags

  • Id
  • TagName
  • Count
  • ExcerptPostId (nullable) Id of Post that holds the excerpt text of the tag
  • WikiPostId (nullable) Id of Post that holds the wiki text of the tag
  • IsModeratorOnly
  • IsRequired

TagSynonyms

Votes

  • Id

  • PostId

  • VoteTypeId (listed in the VoteTypes table - not all of these can actually appear in Data Explorer or the data dump, and some types - like the reactions, 17 & 22-28 - are currently only implemented in Stack Overflow for Teams)
    -1 = InformModerator
    0 = UndoMod
    1 = AcceptedByOriginator
    2 = UpMod (AKA upvote)
    3 = DownMod (AKA downvote)
    4 = Offensive
    5 = Favorite (AKA bookmark; UserId will also be populated) feature removed after October 2022 / replaced by Saves
    6 = Close (effective 2013年06月25日: Close votes are only stored in table: PostHistory)
    7 = Reopen
    8 = BountyStart (UserId and BountyAmount will also be populated)
    9 = BountyClose (BountyAmount will also be populated)
    10 = Deletion
    11 = Undeletion
    12 = Spam
    15 = ModeratorReview (i.e., a moderator looking at a flagged post)
    16 = ApproveEditSuggestion
    17 = Reaction1 (Teams: celebrate)
    18 = Helpful
    19 = ThankYou (see Thank You reaction test)
    20 = WellWritten
    21 = Follow
    22 = Reaction2 (Teams: smile)
    23 = Reaction3 (Teams: mind blown)
    24 = Reaction4 (Teams: clap)
    25 = Reaction5 (Teams: heart)
    26 = Reaction6 (Teams: fire)
    27 = Reaction7 (Teams: trophy)
    28 = Reaction8 (Teams: wave)
    29 = Outdated (see Outdated Answers project)
    30 = NotOutdated
    31 = PreVote
    32 = CollectiveDiscussionUpvote
    33 = CollectiveDiscussionDownvote (no longer used)
    35 = privateAiAnswerCorrect (see Answer Bot experiment)
    36 = privateAiAnswerIncorrect
    37 = privateAiAnswerPartiallyCorrect

  • UserId (present only if VoteTypeId in (5,8); -1 if user is deleted)

  • CreationDate Date only (2018年07月31日 00:00:00 time data is purposefully removed to protect user privacy)

  • BountyAmount (present only if VoteTypeId in (8,9))

xxxTypes

Not listed here:

  • xxxTypes tables which list (Id, Name) pairs for Posts.PostTypeId, Votes.VoteTypeId, etc. See Show all types for an up-to-date list of all types.

All Tables/Columns/Type

Find the exact T-SQL datatype and length/precision of each specific column in this query:

List all Fields in all Tables on SEDE

Views / Stored Procedures

To support queries across multiple databases (aka sites) the following views and stored procedure exist in each database. See Create a view that provides a list of databases for usage scenarios.

sede_databases

  • database_id
  • database_name
  • site_name
  • tiny_name
  • long_name
  • site_type main_site or meta_site
  • site_url
  • sede_url
  • api_site_parameter
  • initialized Datetime when we started populating that database.
  • made_available Datetime when the database came online and was ready for queries
  • processing_time The duration between initialized and made_available, in hh:mm:ss.fff format
  • questions Total number of questions in this site at the time of the current refresh
  • answers Total number of answers in this site at the time of the current refresh
  • latest_post The timestamp of the last non-deleted post captured in this refresh
  • notes This will be non-NULL when a database is in transition

sede_databases_markdown

  • rn row number, for sorting by "long name"
  • content markdown with headers

sede_tables

  • database_id from sys.databases
  • database_name from sys.databases
  • table_name from sys.tables
  • latest_date The latest timestamp (e.g. CreationDate) for tables that have such a column.
  • row_count Number of rows in the table, according to sys.partitions. Note: For Posts, this is PostsWithDeleted; if you want non-deleted total number of Posts, use answers + questions from dbo.sede_databases
  • initialized Timestamp of initial table creation
  • made_available Timestamp of final operation against table.
  • processing_time Duration of initialized -> made_available, in {hh:mm:ss.fff} format.

sede_sites

  • site_name
  • site_url
  • database_name
  • long_name
  • site_id

sede_users

  • account_id
  • site_id
  • user_id
  • reputation
  • question_count
  • answer_count

sede_ineachdb

note: this is a stored procedure!

See this answer for usage details.

Parameter Data Type Default Description
@SQLCommand nvarchar(4000) The SQL statement to run
@IncludeMainSites bit 1 Include all non-meta sites
@IncludeMetaSites bit 1 Include all meta sites
@IncludeMainMeta bit 1 Include StackExchange.Meta
@CollectResultsForMe bit 1 For standard SELECT queries, this will attempt to put each database's results into a #temp table
@ErrorOnSkippedSites bit 0 Set this to 1 if you want execution to halt in the event any site is missing due to transition change

Timestamps

All timestamps are UTC, default format: yyyy-MM-dd hh:mm:ss.fff (stored with three digits of millisecond precision as implemented by SQL Server, making the "end" of a day 23:59:59.997).

Example of conversion current time to PST (including DST) using At Time Zone:

SELECT GetDate() At Time Zone 'UTC' At Time Zone 'Pacific Standard Time';

The time zones do not have great names, as they imply that you would have to use 'Pacific Daylight Time' and know something about DST. To list available time zones: SELECT * FROM sys.time_zone_info

ContentLicense

For tables with a ContentLicense column, the string value here indicates that the content is licensed under one of the following three Creative Commons versions, depending on when the content was submitted or last edited (more details):

ContentLicense Value Date Start Date End Link
CC BY-SA 4.0 2018年05月02日 - /licenses/by-sa/4.0/
CC BY-SA 3.0 2011年04月08日 2018年05月01日 /licenses/by-sa/3.0/
CC BY-SA 2.5 - 2011年04月07日 /licenses/by-sa/2.5/
18
  • For PostNoticeTypes, there appears to be two classIds other than the mentioned 1, 2, & 4. Stack Overflow additionally as 8 and 0, it seems most (all?) other sites have just the 8. Is there an official name for this class of notices? Commented Apr 2, 2021 at 22:42
  • 1
    @zcoop98 I've run a check and you're right, only 1 site has 0 as classid. I checked but can't find info on what the name of those classid's is. Feel free to open a question to get that name. Here is the answer meta.stackexchange.com/a/278150 that announces the addition of those tables. Surprisingly enough I was the requestor to get those tables added in the first place. Commented Apr 22, 2021 at 16:51
  • Why is there UserName in Post and Comment? Is it to avoid a join? Are the username entries in posts and comments updated if user updates the username? Commented Jun 1, 2021 at 8:09
  • 1
    @chaosifier Username is populated when a User is deleted. So once the Userid or OwnerUserId would be set to null the Usename field gets populated with the last known name (although most of the time the username is reset to default first) Commented Jun 1, 2021 at 8:17
  • How can you tell if a vote is for a comment or for a post? It seems like the schema only handles votes for posts. Commented Jun 10, 2021 at 19:23
  • 2
    @AlexJ Comments only have upvotes and those are kept in the field ` Score` . There is no separate table for CommentVotes. So the votes(votetypeid in (2,3)) table has the rows for up and downvotes of posts alone. Commented Jun 10, 2021 at 19:41
  • @rene Interesting, what keeps a user from making multiple upvotes on a given comment? How is the vote on a comment related back to the user that makes it? Commented Jun 10, 2021 at 20:05
  • 1
    @AlexJ the schema of the production database is more extensive Commented Jun 10, 2021 at 20:09
  • I'm looking for Users.Title. Where is it?! Commented Apr 27, 2022 at 9:25
  • 1
    @BenyaminJafari see How can I query users with a specific Job title? Commented Apr 27, 2022 at 9:28
  • What does AcceptedAnswerId mean? I wish there was documentation specifically telling about it. Commented Nov 20, 2022 at 15:59
  • 1
    @desert_ranger see Which Stack Overflow table saves the data for accepted answers? Commented Nov 20, 2022 at 19:11
  • Is there an access to Twitter / Github usernames? I don't see such columns but it's a public information... Commented Oct 25, 2023 at 8:13
  • 1
    @IvanKleshnin please see meta.stackexchange.com/a/335060/158100 tl;dr: they are not in the schema. Commented Oct 25, 2023 at 17:32
  • 1
    CollectiveCollection is actually 17. It would also help to add information on the new id types. Also, the 8 reactions are here Commented Aug 16, 2024 at 0:42
62

The Schema Visualized SEDE ERD The Entity Relationship Diagram (ERD) above was constructed using the data found in the information_schema.columns table. (Thank you to ashleedawg for paring the table down to its bare essentials).

All the table, column and key information were compiled into a SQL script using python3. The results were cross validated with the accepted answer above, and the few idiosyncratic foreign key formations left over were hardcoded in at the end.)

The ERD was then reverse engineered using dbschema.

Please find the script that wrote the SQL and other related files in my GitHub repository.

The interactive HTML version of the ERD is posted here: https://sedeschema.github.io/

10
  • 1
    It could use a key. For example, what are the dashed lines for? Commented Jan 2, 2021 at 10:29
  • 3
    for further information about ERD key relationship representations (and the start of a path down into the UML structure and format hole) please see here: dba.stackexchange.com/questions/76532/… Commented Jan 3, 2021 at 15:13
  • @leerssej It is not explained in the provided reference what the dashed/solid line styles stand for. At least I couldn't find this. Commented Jul 4, 2023 at 0:26
  • @paperskilltrees Please see the third sentence in the first response. Link here: dba.stackexchange.com/a/76547/109673 Commented Jul 7, 2023 at 22:03
  • @leerssej Thanks, however the linked answer does not explicitly mention a line being solid or dashed: "ER Diagrams were originally used only to represent the ER model. The ER model does not use foreign keys to represent relationships. It uses lines between boxes. The lines have some kind of indicator for cardinality at either end or both ends. Sometimes, a relationship will be indicated separately by a diamond." What is confusing to me is whether dashing conveys additional information to symbols at the ends of a line or not. ... [1/n] Commented Jul 7, 2023 at 23:32
  • You seem to imply that dashing is an indicator of cardinality and should be considered together with the end symbols. This agrees with dbSchema documentation, although it does not show exactly the same notation you used in the picture (you have "P", "Z", or "1" at the ends; the docs show Crow's foot and notches). ... [2/3] Commented Jul 7, 2023 at 23:32
  • On the other hand, this shows that dashed (non-solid) line indicates something on top of those symbols; something related to identifying or non-identifying relationships. This variety of notations and possibilities make the topic confusing for a non-expert, and it would be great to know at least something with certainty. [3/3] Commented Jul 7, 2023 at 23:32
  • 2
    @paperskilltrees Ah! Now I understand your question: what is the difference between the dashed lines and the solid lines? Thank you for the clarification! Maybe this stackoverflow.com/a/16697896/5088194 or this can help stackoverflow.com/a/16676282 Commented Jul 10, 2023 at 3:28
  • The description of PostNoticeTypes.ClassId=1 as a historical lock seems misleading. It's used for "Needs citation", "Current event", and "Needs detailed answers". They do not stop new answers from being posted. Commented Apr 17 at 18:56
  • AhHa! 💡Thank you for the background @Stevoisiak Commented Apr 23 at 13:31

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.