Database schema documentation for the public data dump and SEDE

Question

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.

• What is the current structure of the public data dump?
• Is there a diagram of the schema?

Return to FAQ index

Answer 1

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

---

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 ¹ below.

• Id¹

• PostTypeId¹ (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}

• AcceptedAnswerId (only present if PostTypeId = 1)

• ParentId¹ (only present if PostTypeId = 2)

• CreationDate¹

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

• Score¹ (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-05T22:28:34.823) - the date and time of the most recent edit to the post (nullable)

• LastActivityDate (e.g. 2009-03-11T12: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)

• Tags¹ - 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)

• ClosedDate¹ (present only if the post is closed)

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

• ContentLicense¹

---

Users

• Id
• Reputation
• CreationDate
• DisplayName
• LastAccessDate (Datetime user last loaded a page; updated every 30 min at most)
• WebsiteUrl
• Location
• AboutMe
• Views (Number of times the profile is viewed)
• UpVotes (How many upvotes the user has cast)
• DownVotes
• ProfileImageUrl
• EmailHash (now always blank)
• AccountId (User's Stack Exchange Network profile ID)

---

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-15T08: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-05T22:28:34.823)

• UserId

• UserDisplayName: populated if a user has been removed and no longer referenced by user Id

• 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
  <sub>- 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></sub>

• 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)}

---

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}
• CreationDate
• DeletionDate
• ExpiryDate
• Body (when present contains the custom text shown with the notice)
• OwnerUserId
• DeletionUserId

---

PostNoticeTypes

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

---

PostTags

• PostId
• TagId

---

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

• Id
• SuggestedEditId
• UserId
• VoteTypeId (listed in the VoteTypes table)
  _{2 = Approve (technically UpMod)
  3 = Reject (technically DownMod)}
• CreationDate
• TargetUserId
• TargetRepChange

---

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

• Id
• SourceTagName (nullable)
• TargetTagName (nullable)
• CreationDate
• OwnerUserId (nullable)
• AutoRenameCount
• LastAutoRename (nullable)
• Score
• ApprovedByUserId (nullable)
• ApprovalDate (nullable)

---

Votes

• Id
• PostId
• VoteTypeId (listed in the VoteTypes table)
  _{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}
• 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

---

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}

Answer 2

The Schema Visualized 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/