71 of 93 added 20 characters in body

Notes on the meaning of the layout:

  • the 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 SEDE

Posts

  • 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" (seems to only be the election description)
    8. Privilege wiki
  • AcceptedAnswerId (only present if PostTypeId is 1)

  • ParentId (only present if PostTypeId is 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
  • 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="2009-03-05T22:28:34.823" - the date and time of the most recent edit to the post (nullable)
  • LastActivityDate="2009-03-11T12:51:01.480" - the date and time of the most recent activity on the post. For a question, this could be the post being edited, a new answer was posted, a bounty was started, etc.
  • Title (nullable)
  • Tags (nullable)
  • AnswerCount (nullable)
  • CommentCount
  • FavoriteCount
  • ClosedDate (present only if the post is closed)
  • CommunityOwnedDate (present only if post is community wikied)

Users

Comments

  • Id
  • PostId
  • Score
  • Text (The text of the comment.)
  • CreationDate
  • UserDisplayName
  • UserId (Optional. Absent if user has been deleted)

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

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

  • Id
  • PostHistoryTypeId (listed in the PostHistoryTypes table)

    1. Initial Title - The first title a question is asked with.
    2. Initial Body - The first raw body text a post is submitted with.
    3. Initial Tags - The first tags a question is asked with.
    4. Edit Title - A question's title has been changed.
    5. Edit Body - A post's body has been changed, the raw text is stored here as markdown.
    6. Edit Tags - A question's tags have been changed.
    7. Rollback Title - A question's title has reverted to a previous version.
    8. Rollback Body - A post's body has reverted to a previous version - the raw text is stored here.
    9. Rollback Tags - A question's tags have reverted to a previous version.
    10. Post Closed - A post was voted to be closed.
    11. Post Reopened - A post was voted to be reopened.
    12. Post Deleted - A post was voted to be removed.
    13. Post Undeleted - A post was voted to be restored.
    14. Post Locked - A post was locked by a moderator.
    15. Post Unlocked - A post was unlocked by a moderator.
    16. Community Owned - A post has become community owned.
    17. Post Migrated - A post was migrated. superseded now with id 35 and 36 (away/here)
    18. Question Merged - A question has had another, deleted question merged into itself.
    19. Question Protected - A question was protected by a moderator.
    20. Question Unprotected - A question was unprotected by a moderator.
    21. Post Disassociated - An admin removes the OwnerUserId from a post.
    22. Question Unmerged - A previously merged question has had its answers and votes restored.
    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

    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: "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 = 33 or 34 this field contains the PostNoticeId of the PostNotice
  • Text: A raw version of the new value for a given revision
    • If PostHistoryTypeId = 10, 11, 12, 13, 14, 15, 19, 20 and 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>

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

PostsWithDeleted

Similar to Posts table but with deleted post as well. For deleted posts the extra column Deletiondate is not null. Listed here are the fields that contain info when DeletionDate is not null.

  • Id
  • PostTypeId (listed in the PostTypes table)
  • ParentId (only on when PostTypeId = 2 (answer))
  • CreationDate
  • DeletionDate
  • Score
  • Tags
  • ClosedDate

PostTags

ReviewRejectionReasons

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

  • Id
  • Name
  • Description
  • PostTypeId (for reasons that apply to specific post types only)

ReviewTaskResults

  • Id
  • ReviewTaskId
  • ReviewTaskResultTypeId (listed in ReviewTaskResultTypes)

    1. Not Sure
    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. I'm Done (first posts)
    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

  • 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

  • CreationDate

  • DeletionDate
  • 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

  • Id
  • PostId
  • CreationDate
  • ApprovalDate - NULL if not approved (yet).
  • RejectionDate - NULL if not rejected (yet). 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).
  • OwnerUserId
  • Comment
  • Text
  • Title
  • Tags
  • RevisionGUID

SuggestedEditVotes

Tags

TagSynonyms

Votes

  • Id
  • PostId
  • VoteTypeId (listed in the VoteTypes table)

    1. AcceptedByOriginator
    2. UpMod (AKA upvote)
    3. DownMod (AKA downvote)
    4. Offensive
    5. Favorite (UserId will also be populated)
    6. Close (since 2013-06-25 close votes are ONLY stored in the PostHistory table)
    7. Reopen
    8. BountyStart (UserId (-1 = user deleted) and BountyAmount will also be populated)
    9. BountyClose (BountyAmount will also be populated)
    10. Deletion
    11. Undeletion
    12. Spam
    15. ModeratorReview
    16. ApproveEditSuggestion

  • UserId (only present if VoteTypeId is 5 or 8)
  • CreationDate (time data is purposefully removed to protect user privacy)
  • BountyAmount (only present if VoteTypeId is 8 or 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.