Openhub
Sign in Sign up
samjeston / openhub Public
main
Switch branches/tags
Branches
main
Sam Jeston Working vertical 9aa3c45 · History
206 lines· 11.8 KB ·Markdown
Preview Code
Raw
1# Data model
2
3Openhub keeps two kinds of state:
4
5| Store | Holds | Source of truth for |
6| --- | --- | --- |
7| SQLite (`<data>/openhub.db`) | Users, sessions, SSH keys, repository metadata, collaborators, pull requests, pull request events | Who exists, who may do what, and pull request state |
8| Bare repositories (`<data>/repos/<id>.git`) | Every commit, tree, blob, branch and tag, plus Openhub's `refs/pull/<n>/head` refs | All code and history |
9
10The two are linked by the repository ID and by branch names and commit SHAs stored in the database. [Invariants](#invariants) lists the rules that keep them consistent.
11
12**Source files:**
13- `migrations/0001_initial.sql` holds the schema.
14- `src/db.rs` holds the connection setup, row structs and every query.
15- `src/git.rs` holds the repository operations.
16
17## Database connection
18
19`db::connect` opens the database with these settings:
20
21| Setting | Value | Why |
22| --- | --- | --- |
23| `create_if_missing` | true | A fresh data directory needs no setup |
24| `journal_mode` | WAL | Readers don't block the writer |
25| `synchronous` | NORMAL | Safe with WAL and faster than FULL |
26| `foreign_keys` | on | Cascading deletes and referential integrity |
27| `busy_timeout` | 5 s | Waits out short write locks instead of failing |
28| Pool size | 8 connections | Plenty for a single small server |
29
30Pending migrations are applied before the servers start.
31
32## Conventions
33
34- **Timestamps** are Unix seconds (`INTEGER`) from `db::now()`. They are shown in UTC.
35- **Booleans** are `INTEGER` 0 or 1, mapped to `bool` by sqlx.
36- **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.
37- **Row structs** (`User`, `Repo`, `SshKey`, `PullRequest`, `PrEvent`) derive `FromRow`. Queries use runtime `sqlx::query_as`, so building needs no database.
38- **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.
39
40## Tables
41
42### `users`
43
44| Column | Type | Notes |
45| --- | --- | --- |
46| `id` | INTEGER PK | |
47| `username` | TEXT, unique, NOCASE | Validated by `validate::username`. Appears in URLs (`/{owner}`) |
48| `email` | TEXT, unique, NOCASE | Used for signing in and as the committer email for web merges |
49| `display_name` | TEXT | Optional, up to 100 characters. `User::name()` falls back to the username |
50| `bio` | TEXT | Optional, up to 500 characters |
51| `password_hash` | TEXT | Argon2id in PHC string format (`$argon2id$v=19$…`) |
52| `is_admin` | INTEGER | Site administrator. Set for the first account created |
53| `created_at` | INTEGER | |
54
55### `sessions`
56
57| Column | Type | Notes |
58| --- | --- | --- |
59| `token_hash` | TEXT PK | Hex SHA-256 of the cookie token. The token itself is never stored |
60| `user_id` | INTEGER, FK, cascade | |
61| `created_at`, `expires_at` | INTEGER | `expires_at` is 30 days after sign-in. Expired rows are purged at each sign-in |
62
63### `ssh_keys`
64
65| Column | Type | Notes |
66| --- | --- | --- |
67| `id` | INTEGER PK | |
68| `user_id` | INTEGER, FK, cascade | |
69| `title` | TEXT | Defaults to the key comment, or failing that the key type |
70| `fingerprint` | TEXT, unique | `SHA256:…`. Unique across all users, so a key identifies exactly one account |
71| `public_key` | TEXT | Normalised OpenSSH line with the comment removed |
72| `key_type` | TEXT | For example `ssh-ed25519` |
73| `created_at` | INTEGER | |
74| `last_used_at` | INTEGER, nullable | Updated on every successful SSH authentication |
75
76### `repositories`
77
78| Column | Type | Notes |
79| --- | --- | --- |
80| `id` | INTEGER PK | Also names the directory on disk: `repos/<id>.git` |
81| `owner_id` | INTEGER, FK, cascade | |
82| `name` | TEXT, NOCASE | Validated by `validate::repo_name`. `UNIQUE (owner_id, name)` |
83| `description` | TEXT | Up to 350 characters |
84| `is_private` | INTEGER | |
85| `default_branch` | TEXT | Defaults to `main`. Kept in step with the bare repository's `HEAD` |
86| `created_at`, `updated_at` | INTEGER | `updated_at` changes when settings are saved, and with `pushed_at` |
87| `pushed_at` | INTEGER, nullable | Set after each successful push and each web merge. Orders repository lists |
88
89### `collaborators`
90
91| Column | Type | Notes |
92| --- | --- | --- |
93| `repo_id` | INTEGER, FK, cascade | Primary key together with `user_id` |
94| `user_id` | INTEGER, FK, cascade | |
95| `created_at` | INTEGER | |
96
97A row grants write access (push, merge, delete branches). There is only one collaborator level.
98
99### `pull_requests`
100
101| Column | Type | Notes |
102| --- | --- | --- |
103| `id` | INTEGER PK | Internal ID, used by `pr_events` |
104| `repo_id` | INTEGER, FK, cascade | |
105| `number` | INTEGER | Per-repository number shown in URLs. `MAX(number) + 1` inside a transaction. `UNIQUE (repo_id, number)` |
106| `title`, `body` | TEXT | Title up to 256 characters. The body is Markdown |
107| `author_id` | INTEGER, FK | No cascade (see [Deleting rows](#deleting-rows)) |
108| `base_branch`, `head_branch` | TEXT | Branch names in the same repository. There are no forks |
109| `state` | TEXT | `open`, `closed` or `merged` |
110| `head_sha` | TEXT | Last known tip of the head branch, mirrored in `refs/pull/<number>/head` |
111| `base_sha` | TEXT | Merge base of base and head. Tracked while open, frozen on close or merge |
112| `merge_commit_sha` | TEXT, nullable | New tip of the base branch after a web merge. NULL when the merge was detected after a push |
113| `merged_by` | INTEGER, FK, nullable | NULL when Openhub detected the merge on a page view |
114| `merged_at`, `closed_at` | INTEGER, nullable | `closed_at` is also set on merge |
115| `created_at`, `updated_at` | INTEGER | `updated_at` changes on every event, edit and head update |
116
117[Pull requests](pull-requests.md) explains how these columns change over a pull request's life.
118
119### `pr_events`
120
121The conversation timeline: comments, plus records of state changes.
122
123| Column | Type | Notes |
124| --- | --- | --- |
125| `id` | INTEGER PK | Used for `#event-<id>` anchors |
126| `pr_id` | INTEGER, FK, cascade | |
127| `author_id` | INTEGER, FK, nullable | NULL for events Openhub detected itself |
128| `kind` | TEXT | `comment`, `closed`, `reopened`, `merged` or `branch_deleted` |
129| `body` | TEXT | Markdown for `comment`, the branch name for `branch_deleted`, empty otherwise |
130| `created_at` | INTEGER | |
131
132The pull request's own description is stored in `pull_requests.body`, not as an event.
133
134## Relationships
135
136```
137users ─┬─< sessions (cascade)
138 ├─< ssh_keys (cascade)
139 ├─< repositories ─┬─< collaborators >─ users (cascade both ways)
140 │ └─< pull_requests ─< pr_events (cascade)
141 ├─< pull_requests.author_id, .merged_by (no cascade)
142 └─< pr_events.author_id (no cascade)
143```
144
145### Deleting rows
146
147- **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.
148- **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.
149
150## Repositories on disk
151
152Each 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.
153
154`git::init_bare` creates it when a repository is created:
155- `HEAD` points to `refs/heads/main`.
156- `receive.hideRefs` is set to `refs/pull`.
157- If the creator ticked "Add a README", `git::initial_commit` writes a single commit containing `README.md`.
158
159| Ref | Written by | Notes |
160| --- | --- | --- |
161| `HEAD` | `git init`, `git::ensure_head`, `git::set_head` | Symbolic ref to the default branch |
162| `refs/heads/*` | Pushes, web merges, `git::delete_branch` | |
163| `refs/tags/*` | Pushes | Tags can't be created in the web interface |
164| `refs/pull/<n>/head` | `git::set_pull_ref` (create, refresh, merge, reopen) | Hidden from `receive-pack`, so pushes to it are rejected. Fetchable with `git fetch origin pull/<n>/head` |
165
166The 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](operations.md#repository-maintenance)).
167
168## Invariants
169
170These must stay true. Code that changes either store must preserve them.
171
1721. **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.
1732. **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.
1743. **`default_branch` names `HEAD`'s target whenever that branch exists.**
175 - The settings page moves `HEAD` with `git::set_head` before saving.
176 - 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.
1774. **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`.
1785. **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.
1796. **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](pull-requests.md#which-commits-a-pull-request-shows)).
180
181## Changing the schema
182
1831. **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.
1842. **Update the column list constant** (`USER_COLS`, `REPO_SELECT` or `PULL_SELECT`) and the row struct in `src/db.rs`.
1853. **Add query functions** to `src/db.rs`. Keep SQL out of handlers.
1864. **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.
1875. **Run the smoke test against a fresh data directory, and against a copy of an existing one,** to check the migration applies to both.
188
189Migrations only go forward. Rolling back means restoring a backup taken before the upgrade.
190
191## Inspecting the database
192
193```sh
194sqlite3 -readonly data/openhub.db
195sqlite> .tables
196sqlite> SELECT id, username, is_admin FROM users;
197sqlite> SELECT r.id, u.username || '/' || r.name, r.is_private FROM repositories r JOIN users u ON u.id = r.owner_id;
198sqlite> SELECT number, state, base_branch, head_branch, substr(head_sha, 1, 7) FROM pull_requests WHERE repo_id = 1;
199```
200
201[Operations](operations.md#administration-without-a-ui) has recipes for changing data by hand.
202
203## When you change this
204
205- **New table or column:** update this page, the migration, `src/db.rs`, and any template that should show it.
206- **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.