Why not merge issues and pull requests?

Because a PR carries branches, a merge state and reviews that an issue never has, and an issue carries milestones and assignees that a PR uses differently. Merging produces a wide table where the meaning of half the columns depends on a kind flag. The shared number sequence is the one thing that gets harder when you split them.

How is the per-repository issue number kept unique?

Not by this schema. number is scoped to repo_id, so it needs a unique constraint on (repo_id, number) and a way to allocate the next value that both issues and pull requests agree on — usually a counter on the repository row, updated in the same transaction.

Where are comments on issues and PRs?

They are not in this template. Adding them means deciding whether one comments table points at both — with two nullable columns, the same shape as the repository owner — or whether each gets its own. One table is usual, because a comment renders identically either way.

Can I drop the branches table?

If you always ask Git, yes. It exists so that listing branches and their heads is a query rather than a set of ref lookups, which matters on a page that shows every branch of a large repository. Treat it as a cache that a push updates.

Why does actions_runs store the commit sha rather than a commit id?

Because a run can outlive the commit row, and because the sha is what identifies the code that ran. Pointing at commits would mean a run cannot be recorded for a commit the host has not indexed yet.

Browse Templates/GitHub

GitHub

Comprehensive database schema for a GitHub-like code hosting platform with user accounts, organizations, repositories, branches, commits, pull requests, code reviews, issues, labels, milestones, releases, stars, forks, and Actions workflows.

PostgreSQL16 tablesWeb App

developer-toolsgitcollaborationopen-sourceci-cd

About this schema

A repository can belong to a person or to an organization, and this schema keeps both pointers on the row — owner_id and org_id — with one of them set. That is the shape almost every code host converges on, because a repository transferred from a user to an org should keep its identity, its stars and its issues.

Issues and pull requests are separate tables that both carry a number scoped to the repository. They look mergeable, but a pull request has branches, a merge state and reviews, while an issue has assignees and milestones. Sharing the number sequence between them is the part that has to be enforced outside the schema.

Git itself is not modelled. commits, branches and releases record what the host needs to show — who, when, how many lines — while the object graph stays in the repository on disk. That boundary is deliberate: a database that tries to hold every commit's tree becomes a slow copy of something Git already does well.

Key relationships

repositories → users / organizations: owner_id and org_id, one of the two. Transferring ownership is an update on this row rather than a new repository, so stars and issues follow.
repositories → repositories: forked_from_id is a self-reference. A fork is a repository in its own right — it has its own stars and issues — that remembers where it came from.
pull_requests → pr_reviews: Reviews are rows with their own state, so a pull request can be approved by one reviewer and have changes requested by another at the same time.
issues → labels via issue_labels: Labels are per repository, not global, so two projects can both have a 'bug' label with different colours and neither can rename the other's.
actions_workflows → actions_runs: The workflow is the definition, the run is one execution with its own status, conclusion and commit. Deleting a workflow file should not erase the history of what it did.

Design decisions

Issues and pull requests are separate tables

They share a number sequence and a comment stream, which tempts merging them. But a pull request has head and base branches, a draft flag, a merge time and reviews attached; an issue has none of those and has milestones and assignees instead. One table means half the columns are null on every row and every query filters on a kind. The price of splitting is that the shared number has to be allocated outside the schema, and that comments have to point at one or the other.

A repository keeps two owner columns

owner_id and org_id with exactly one set is a nullable pair, which is not the tidiest possible model — a polymorphic owner table would be stricter. It is used because ownership transfer between the two is common and must not change the repository's identity, and because almost every query filters by one or the other, not by an abstract owner.

Commits are records, not the object graph

commits stores sha, message, additions, deletions and files_changed — what a listing page shows. It does not store trees, blobs or parents, because reconstructing history from a relational table is slower than asking Git, and the data would be a second copy that can disagree with the repository.

Branches are rows, with the head denormalized

branches.commit_sha is the branch head copied out of Git so that a repository page can list branches and their last commit without walking refs. It is a cache and will lag a push; the repository stays the source of truth.

Tables in this template

usersUsers

10 cols

organizationsOrganizations

9 cols

org_membersOrg Members

6 cols

repositoriesRepositories

14 cols

branchesBranches

8 cols

commitsCommits

11 cols

pull_requestsPull Requests

14 cols

pr_reviewsPR Reviews

8 cols

issuesIssues

12 cols

labelsLabels

7 cols

issue_labelsIssue Labels

5 cols

milestonesMilestones

9 cols

starsStars

5 cols

releasesReleases

10 cols

actions_workflowsActions Workflows

9 cols

actions_runsActions Runs

10 cols

Frequently asked

Why not merge issues and pull requests?: Because a PR carries branches, a merge state and reviews that an issue never has, and an issue carries milestones and assignees that a PR uses differently. Merging produces a wide table where the meaning of half the columns depends on a kind flag. The shared number sequence is the one thing that gets harder when you split them.
How is the per-repository issue number kept unique?: Not by this schema. number is scoped to repo_id, so it needs a unique constraint on (repo_id, number) and a way to allocate the next value that both issues and pull requests agree on — usually a counter on the repository row, updated in the same transaction.
Where are comments on issues and PRs?: They are not in this template. Adding them means deciding whether one comments table points at both — with two nullable columns, the same shape as the repository owner — or whether each gets its own. One table is usual, because a comment renders identically either way.
Can I drop the branches table?: If you always ask Git, yes. It exists so that listing branches and their heads is a query rather than a set of ref lookups, which matters on a page that shows every branch of a large repository. Treat it as a cache that a push updates.
Why does actions_runs store the commit sha rather than a commit id?: Because a run can outlive the commit row, and because the sha is what identifies the code that ran. Pointing at commits would mean a run cannot be recorded for a commit the host has not indexed yet.

Related Templates

View all templates →

GitHub

About this schema

Key relationships

Design decisions

Issues and pull requests are separate tables

A repository keeps two owner columns

Commits are records, not the object graph

Branches are rows, with the head denormalized

Tables in this template

Frequently asked

Related Templates

Netflix

Spotify

Discord

Slack