Data model

Openhub keeps two kinds of state:

The two are linked by the repository ID and by branch names and commit SHAs stored in the database. Invariants lists the rules that keep them consistent.

Source files:

• migrations/0001_initial.sql holds the schema.
• src/db.rs holds the connection setup, row structs and every query.
• src/git.rs holds the repository operations.

Database connection

db::connect opens the database with these settings:

Pending migrations are applied before the servers start.

Conventions

• Timestamps are Unix seconds (INTEGER) from db::now(). They are shown in UTC.
• Booleans are INTEGER 0 or 1, mapped to bool by sqlx.
• Names compare case-insensitively. users.username, users.email and repositories.name use COLLATE NOCASE. Lookups use the column's collation, so /Alice/Project finds alice/project. Uniqueness is case-insensitive too.
• Row structs (User, Repo, SshKey, PullRequest, PrEvent) derive FromRow. Queries use runtime sqlx::query_as, so building needs no database.
• Shared column lists. USER_COLS, REPO_SELECT and PULL_SELECT are constants in db.rs. Every query for that struct uses them, so each one returns the same columns.

Tables

users

sessions

ssh_keys

repositories

collaborators

A row grants write access (push, merge, delete branches). There is only one collaborator level.

pull_requests

Pull requests explains how these columns change over a pull request's life.

pr_events

The conversation timeline: comments, plus records of state changes.

The pull request's own description is stored in pull_requests.body, not as an event.

Relationships

users ─┬─< sessions               (cascade)
       ├─< ssh_keys               (cascade)
       ├─< repositories ─┬─< collaborators >─ users   (cascade both ways)
       │                 └─< pull_requests ─< pr_events   (cascade)
       ├─< pull_requests.author_id, .merged_by          (no cascade)
       └─< pr_events.author_id                          (no cascade)

Deleting rows

• Deleting a repository (db::delete_repo) cascades to its collaborators, pull requests and events. The handler then removes repos/<id>.git. If that fails, it logs a warning and the directory is left behind.
• Deleting a user isn't supported in the interface. Because foreign keys are enforced, DELETE FROM users fails for anyone who authored a pull request or event, or merged a pull request, in a repository they don't own. A future "delete user" feature must reassign or nullify those references first. For pull requests, author_id is NOT NULL, so that needs a placeholder user or a migration.

Repositories on disk

Each repository is a bare git repository at Config::repo_path(id), which is <data>/repos/<id>.git. File paths are never built from owner or repository names.

git::init_bare creates it when a repository is created:

• HEAD points to refs/heads/main.
• receive.hideRefs is set to refs/pull.
• If the creator ticked "Add a README", git::initial_commit writes a single commit containing README.md.

The objects that web merges create (merge, squash and rebased commits) are written to the same object database. If the final ref update fails, they are left unreferenced, and a later git gc removes them. Openhub never runs git gc itself (see Operations).

Invariants

These must stay true. Code that changes either store must preserve them.

1. Every repositories row has a directory. repo::create inserts the row, then initialises repos/<id>.git. If initialisation fails, it deletes the row and the directory.
2. IDs are never reused for a different directory. SQLite INTEGER PRIMARY KEY can reuse the highest ID after a delete. Deleting a repository removes its directory, so a reused ID starts from an empty path. If directory removal failed, a later repository could pick up the stale directory. Remove leftovers by hand if the log warns about it.
3. default_branch names HEAD's target whenever that branch exists.
   • The settings page moves HEAD with git::set_head before saving.
   • After a push, sync::after_push calls git::ensure_head. If the default branch is missing, HEAD moves to main, master or the first branch alphabetically, and the column is updated to match.
4. Each pull request's refs/pull/<n>/head points at its head_sha. Every code path that writes head_sha also calls git::set_pull_ref.
5. At most one open pull request exists per (repository, base, head), enforced by checks in pulls::create and in reopening (pulls::set_state). There is no database constraint, so new code paths must check too.
6. Closed and merged pull requests have a frozen base_sha and head_sha, so their commits and diff render the same forever (see Pull requests).

Changing the schema

1. Add a new file such as migrations/0002_add_repo_topics.sql. Never edit an existing migration: sqlx stores a checksum of each applied migration, and the server refuses to start if one changes.
2. Update the column list constant (USER_COLS, REPO_SELECT or PULL_SELECT) and the row struct in src/db.rs.
3. Add query functions to src/db.rs. Keep SQL out of handlers.
4. Prefer ALTER TABLE … ADD COLUMN with a default, so existing rows stay valid. SQLite can't drop or alter constraints in place, so changing one needs the create-copy-rename pattern inside the migration.
5. Run the smoke test against a fresh data directory, and against a copy of an existing one, to check the migration applies to both.

Migrations only go forward. Rolling back means restoring a backup taken before the upgrade.

Inspecting the database

sqlite3 -readonly data/openhub.db
sqlite> .tables
sqlite> SELECT id, username, is_admin FROM users;
sqlite> SELECT r.id, u.username || '/' || r.name, r.is_private FROM repositories r JOIN users u ON u.id = r.owner_id;
sqlite> SELECT number, state, base_branch, head_branch, substr(head_sha, 1, 7) FROM pull_requests WHERE repo_id = 1;

Operations has recipes for changing data by hand.

When you change this

• New table or column: update this page, the migration, src/db.rs, and any template that should show it.
• New ref namespace: add it to the refs table above. If clients must not push to it, add it to receive.hideRefs in git::configure. Existing repositories only got the setting when they were created, so they need a migration step at startup.