irongit
Sign inRegister
huncholanehuncholane/irongitPublic

Git hosting and a container registry in one Rust binary (axum + Astro)

irongit/backend/migrations/0001_init.sql
288 lines12 KBSQL
RawDownloadHistory
1-- irongit schema. Postgres holds identity, permissions and metadata; git
2-- objects live on the server's disk and large blobs (LFS, image layers, CLI
3-- releases, avatars, backups) live in R2.
4
5create extension if not exists citext;
6create extension if not exists pg_trgm;
7
8-- Users and organizations share one namespace, so /{name} is unambiguous.
9create table accounts (
10 id bigserial primary key,
11 kind text not null check (kind in ('user', 'org')),
12 name citext not null unique,
13 display_name text not null default '',
14 bio text not null default '',
15 location text not null default '',
16 website text not null default '',
17 -- R2 key of an uploaded avatar; null means the generated identicon.
18 avatar_key text,
19 -- Per-account override of the default storage quota, in bytes.
20 quota_bytes bigint,
21 created_at timestamptz not null default now(),
22 updated_at timestamptz not null default now()
23);
24create index accounts_name_trgm on accounts using gin (name gin_trgm_ops);
25
26create table users (
27 account_id bigint primary key references accounts (id) on delete cascade,
28 -- Null for accounts that only sign in with Google.
29 password_hash text,
30 is_admin boolean not null default false,
31 suspended_at timestamptz,
32 -- Whether other people see counts from private repos on the heatmap.
33 show_private_contributions boolean not null default false,
34 last_login_at timestamptz
35);
36
37create table emails (
38 id bigserial primary key,
39 user_id bigint not null references users (account_id) on delete cascade,
40 email citext not null unique,
41 is_primary boolean not null default false,
42 verified_at timestamptz,
43 verify_token_hash text,
44 verify_sent_at timestamptz,
45 created_at timestamptz not null default now()
46);
47create unique index emails_one_primary on emails (user_id) where is_primary;
48create index emails_user on emails (user_id);
49
50-- External sign-in (Google). `subject` is the provider's stable user id.
51create table oauth_identities (
52 provider text not null check (provider in ('google')),
53 subject text not null,
54 user_id bigint not null references users (account_id) on delete cascade,
55 email citext,
56 created_at timestamptz not null default now(),
57 last_used_at timestamptz,
58 primary key (provider, subject),
59 unique (provider, user_id)
60);
61
62create table org_members (
63 org_id bigint not null references accounts (id) on delete cascade,
64 user_id bigint not null references users (account_id) on delete cascade,
65 role text not null check (role in ('owner', 'member')),
66 created_at timestamptz not null default now(),
67 primary key (org_id, user_id)
68);
69create index org_members_user on org_members (user_id);
70
71create table sessions (
72 id_hash text primary key,
73 user_id bigint not null references users (account_id) on delete cascade,
74 ip text,
75 user_agent text,
76 created_at timestamptz not null default now(),
77 last_seen_at timestamptz not null default now(),
78 expires_at timestamptz not null
79);
80create index sessions_user on sessions (user_id);
81
82-- Personal access tokens: the only credential git, docker and the CLI accept.
83create table access_tokens (
84 id bigserial primary key,
85 user_id bigint not null references users (account_id) on delete cascade,
86 name text not null,
87 token_hash text not null unique,
88 token_prefix text not null,
89 scopes text[] not null default '{repo,packages,user}',
90 last_used_at timestamptz,
91 expires_at timestamptz,
92 created_at timestamptz not null default now()
93);
94create index access_tokens_user on access_tokens (user_id);
95
96create table ssh_keys (
97 id bigserial primary key,
98 user_id bigint not null references users (account_id) on delete cascade,
99 title text not null,
100 public_key text not null,
101 fingerprint text not null unique,
102 last_used_at timestamptz,
103 created_at timestamptz not null default now()
104);
105create index ssh_keys_user on ssh_keys (user_id);
106
107-- `ig login`: the CLI holds device_code, the person types user_code in a browser.
108create table device_codes (
109 device_code_hash text primary key,
110 user_code text not null unique,
111 client_name text not null default '',
112 user_id bigint references users (account_id) on delete cascade,
113 approved_at timestamptz,
114 denied_at timestamptz,
115 consumed_at timestamptz,
116 expires_at timestamptz not null,
117 created_at timestamptz not null default now()
118);
119
120create table repos (
121 id bigserial primary key,
122 owner_id bigint not null references accounts (id) on delete cascade,
123 name citext not null,
124 description text not null default '',
125 visibility text not null check (visibility in ('public', 'private')),
126 default_branch text not null default 'main',
127 size_bytes bigint not null default 0,
128 is_empty boolean not null default true,
129 archived boolean not null default false,
130 created_at timestamptz not null default now(),
131 updated_at timestamptz not null default now(),
132 pushed_at timestamptz,
133 unique (owner_id, name)
134);
135create index repos_name_trgm on repos using gin (name gin_trgm_ops);
136create index repos_public_pushed on repos (pushed_at desc nulls last) where visibility = 'public';
137
138create table repo_collaborators (
139 repo_id bigint not null references repos (id) on delete cascade,
140 user_id bigint not null references users (account_id) on delete cascade,
141 permission text not null check (permission in ('read', 'write', 'admin')),
142 created_at timestamptz not null default now(),
143 primary key (repo_id, user_id)
144);
145create index repo_collaborators_user on repo_collaborators (user_id);
146
147create table push_events (
148 id bigserial primary key,
149 repo_id bigint not null references repos (id) on delete cascade,
150 pusher_id bigint references users (account_id) on delete set null,
151 ref_name text not null,
152 before_sha text not null,
153 after_sha text not null,
154 commit_count integer not null,
155 head_message text not null default '',
156 via text not null check (via in ('http', 'ssh', 'web')),
157 created_at timestamptz not null default now()
158);
159create index push_events_pusher on push_events (pusher_id, created_at desc);
160create index push_events_repo on push_events (repo_id, created_at desc);
161
162-- One row per commit that landed on a repo's default branch and whose author
163-- email is verified by a user. Idempotent, so force pushes never double count.
164create table contribution_commits (
165 repo_id bigint not null references repos (id) on delete cascade,
166 sha text not null,
167 user_id bigint not null references users (account_id) on delete cascade,
168 day date not null,
169 primary key (repo_id, sha)
170);
171create index contribution_commits_user_day on contribution_commits (user_id, day);
172
173-- Git LFS. Objects are stored once in R2 by oid; repo links gate access.
174create table lfs_objects (
175 oid text primary key,
176 size bigint not null,
177 created_at timestamptz not null default now()
178);
179
180create table repo_lfs_objects (
181 repo_id bigint not null references repos (id) on delete cascade,
182 oid text not null references lfs_objects (oid) on delete cascade,
183 created_at timestamptz not null default now(),
184 primary key (repo_id, oid)
185);
186create index repo_lfs_objects_oid on repo_lfs_objects (oid);
187
188-- Container images. Visibility is independent of any linked repo.
189create table packages (
190 id bigserial primary key,
191 owner_id bigint not null references accounts (id) on delete cascade,
192 -- Image path below the owner, lowercase: "api" or "tools/builder".
193 name text not null,
194 visibility text not null default 'private' check (visibility in ('public', 'private')),
195 repo_id bigint references repos (id) on delete set null,
196 description text not null default '',
197 pull_count bigint not null default 0,
198 created_at timestamptz not null default now(),
199 updated_at timestamptz not null default now(),
200 unique (owner_id, name)
201);
202
203-- Layers and configs, stored once in R2 under their digest.
204create table blobs (
205 digest text primary key,
206 size bigint not null,
207 created_at timestamptz not null default now()
208);
209
210-- Which packages may see a blob. Every HEAD, GET and mount checks this, so a
211-- digest from someone's private image is useless without access to it.
212create table package_blobs (
213 package_id bigint not null references packages (id) on delete cascade,
214 digest text not null references blobs (digest) on delete cascade,
215 created_at timestamptz not null default now(),
216 primary key (package_id, digest)
217);
218create index package_blobs_digest on package_blobs (digest);
219
220create table manifests (
221 id bigserial primary key,
222 package_id bigint not null references packages (id) on delete cascade,
223 digest text not null,
224 media_type text not null,
225 content bytea not null,
226 -- Sum of the config and layer sizes, for display.
227 total_size bigint not null default 0,
228 created_at timestamptz not null default now(),
229 unique (package_id, digest)
230);
231
232create table manifest_references (
233 manifest_id bigint not null references manifests (id) on delete cascade,
234 digest text not null,
235 kind text not null check (kind in ('blob', 'manifest')),
236 primary key (manifest_id, digest)
237);
238create index manifest_references_digest on manifest_references (digest);
239
240create table tags (
241 package_id bigint not null references packages (id) on delete cascade,
242 name text not null,
243 manifest_id bigint not null references manifests (id) on delete cascade,
244 updated_at timestamptz not null default now(),
245 primary key (package_id, name)
246);
247create index tags_manifest on tags (manifest_id);
248
249-- In-progress pushes, staged on local disk under DATA_DIR/uploads/{id}.
250create table blob_uploads (
251 id uuid primary key,
252 package_id bigint not null references packages (id) on delete cascade,
253 user_id bigint references users (account_id) on delete set null,
254 size bigint not null default 0,
255 created_at timestamptz not null default now(),
256 updated_at timestamptz not null default now()
257);
258
259create table cli_releases (
260 id bigserial primary key,
261 version text not null unique,
262 target text not null default 'x86_64-unknown-linux-musl',
263 r2_key text not null,
264 sha256 text not null,
265 size bigint not null,
266 created_at timestamptz not null default now()
267);
268
269create table backup_runs (
270 id bigserial primary key,
271 started_at timestamptz not null default now(),
272 finished_at timestamptz,
273 status text not null default 'running' check (status in ('running', 'ok', 'failed')),
274 repo_count integer not null default 0,
275 bytes bigint not null default 0,
276 error text
277);
278
279create table audit_log (
280 id bigserial primary key,
281 actor_id bigint references users (account_id) on delete set null,
282 action text not null,
283 target text not null default '',
284 meta jsonb not null default '{}',
285 ip text,
286 created_at timestamptz not null default now()
287);
288create index audit_log_created on audit_log (created_at desc);