A container registry that uses the AT Protocol for manifest storage and S3 for blob storage.
1-- ATCR AppView Database Schema
2-- This file contains the complete base schema for fresh database installations.
3-- Migrations (in migrations/*.yaml) handle changes to existing databases.
4
5CREATE TABLE IF NOT EXISTS schema_migrations (
6 version INTEGER PRIMARY KEY,
7 applied_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
8);
9
10CREATE TABLE IF NOT EXISTS users (
11 did TEXT PRIMARY KEY,
12 handle TEXT NOT NULL,
13 pds_endpoint TEXT NOT NULL,
14 avatar TEXT,
15 default_hold_did TEXT,
16 last_seen TIMESTAMP NOT NULL,
17 UNIQUE(handle)
18);
19CREATE INDEX IF NOT EXISTS idx_users_handle ON users(handle);
20
21CREATE TABLE IF NOT EXISTS manifests (
22 id INTEGER PRIMARY KEY AUTOINCREMENT,
23 did TEXT NOT NULL,
24 repository TEXT NOT NULL,
25 digest TEXT NOT NULL,
26 hold_endpoint TEXT NOT NULL, -- Stored as DID (e.g., did:web:hold.example.com)
27 schema_version INTEGER NOT NULL,
28 media_type TEXT NOT NULL,
29 config_digest TEXT,
30 config_size INTEGER,
31 artifact_type TEXT NOT NULL DEFAULT 'container-image', -- container-image, helm-chart, unknown
32 created_at TIMESTAMP NOT NULL,
33 UNIQUE(did, repository, digest),
34 FOREIGN KEY(did) REFERENCES users(did) ON DELETE CASCADE
35);
36CREATE INDEX IF NOT EXISTS idx_manifests_did_repo ON manifests(did, repository);
37CREATE INDEX IF NOT EXISTS idx_manifests_created_at ON manifests(created_at DESC);
38CREATE INDEX IF NOT EXISTS idx_manifests_digest ON manifests(digest);
39CREATE INDEX IF NOT EXISTS idx_manifests_artifact_type ON manifests(artifact_type);
40
41CREATE TABLE IF NOT EXISTS repository_annotations (
42 did TEXT NOT NULL,
43 repository TEXT NOT NULL,
44 key TEXT NOT NULL,
45 value TEXT NOT NULL,
46 updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
47 PRIMARY KEY(did, repository, key),
48 FOREIGN KEY(did) REFERENCES users(did) ON DELETE CASCADE
49);
50CREATE INDEX IF NOT EXISTS idx_repository_annotations_did_repo ON repository_annotations(did, repository);
51CREATE INDEX IF NOT EXISTS idx_repository_annotations_key ON repository_annotations(key);
52
53CREATE TABLE IF NOT EXISTS layers (
54 manifest_id INTEGER NOT NULL,
55 digest TEXT NOT NULL,
56 size INTEGER NOT NULL,
57 media_type TEXT NOT NULL,
58 layer_index INTEGER NOT NULL,
59 annotations TEXT,
60 PRIMARY KEY(manifest_id, layer_index),
61 FOREIGN KEY(manifest_id) REFERENCES manifests(id) ON DELETE CASCADE
62);
63CREATE INDEX IF NOT EXISTS idx_layers_digest ON layers(digest);
64
65CREATE TABLE IF NOT EXISTS manifest_references (
66 manifest_id INTEGER NOT NULL,
67 digest TEXT NOT NULL,
68 media_type TEXT NOT NULL,
69 size INTEGER NOT NULL,
70 platform_architecture TEXT,
71 platform_os TEXT,
72 platform_variant TEXT,
73 platform_os_version TEXT,
74 is_attestation BOOLEAN DEFAULT FALSE,
75 reference_index INTEGER NOT NULL,
76 PRIMARY KEY(manifest_id, reference_index),
77 FOREIGN KEY(manifest_id) REFERENCES manifests(id) ON DELETE CASCADE
78);
79CREATE INDEX IF NOT EXISTS idx_manifest_references_digest ON manifest_references(digest);
80
81CREATE TABLE IF NOT EXISTS tags (
82 id INTEGER PRIMARY KEY AUTOINCREMENT,
83 did TEXT NOT NULL,
84 repository TEXT NOT NULL,
85 tag TEXT NOT NULL,
86 digest TEXT NOT NULL,
87 created_at TIMESTAMP NOT NULL,
88 UNIQUE(did, repository, tag),
89 FOREIGN KEY(did) REFERENCES users(did) ON DELETE CASCADE
90);
91CREATE INDEX IF NOT EXISTS idx_tags_did_repo ON tags(did, repository);
92
93CREATE TABLE IF NOT EXISTS oauth_sessions (
94 session_key TEXT PRIMARY KEY,
95 account_did TEXT NOT NULL,
96 session_id TEXT NOT NULL,
97 session_data TEXT NOT NULL,
98 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
99 updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
100 UNIQUE(account_did, session_id)
101);
102CREATE INDEX IF NOT EXISTS idx_oauth_sessions_did ON oauth_sessions(account_did);
103CREATE INDEX IF NOT EXISTS idx_oauth_sessions_updated ON oauth_sessions(updated_at DESC);
104
105CREATE TABLE IF NOT EXISTS oauth_auth_requests (
106 state TEXT PRIMARY KEY,
107 request_data TEXT NOT NULL,
108 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
109);
110CREATE INDEX IF NOT EXISTS idx_oauth_auth_requests_created ON oauth_auth_requests(created_at);
111
112CREATE TABLE IF NOT EXISTS ui_sessions (
113 id TEXT PRIMARY KEY,
114 did TEXT NOT NULL,
115 handle TEXT NOT NULL,
116 pds_endpoint TEXT NOT NULL,
117 oauth_session_id TEXT,
118 expires_at TIMESTAMP NOT NULL,
119 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
120 FOREIGN KEY(did) REFERENCES users(did) ON DELETE CASCADE
121);
122CREATE INDEX IF NOT EXISTS idx_ui_sessions_did ON ui_sessions(did);
123CREATE INDEX IF NOT EXISTS idx_ui_sessions_expires ON ui_sessions(expires_at);
124
125CREATE TABLE IF NOT EXISTS devices (
126 id TEXT PRIMARY KEY,
127 did TEXT NOT NULL,
128 handle TEXT NOT NULL,
129 name TEXT NOT NULL,
130 secret_hash TEXT NOT NULL UNIQUE,
131 ip_address TEXT,
132 location TEXT,
133 user_agent TEXT,
134 created_at TIMESTAMP NOT NULL,
135 last_used TIMESTAMP,
136 FOREIGN KEY(did) REFERENCES users(did) ON DELETE CASCADE
137);
138CREATE INDEX IF NOT EXISTS idx_devices_did ON devices(did);
139CREATE INDEX IF NOT EXISTS idx_devices_hash ON devices(secret_hash);
140
141CREATE TABLE IF NOT EXISTS pending_device_auth (
142 device_code TEXT PRIMARY KEY,
143 user_code TEXT NOT NULL UNIQUE,
144 device_name TEXT NOT NULL,
145 ip_address TEXT,
146 user_agent TEXT,
147 expires_at TIMESTAMP NOT NULL,
148 approved_did TEXT,
149 approved_at TIMESTAMP,
150 device_secret TEXT,
151 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
152);
153CREATE INDEX IF NOT EXISTS idx_pending_device_auth_user_code ON pending_device_auth(user_code);
154CREATE INDEX IF NOT EXISTS idx_pending_device_auth_expires ON pending_device_auth(expires_at);
155
156CREATE TABLE IF NOT EXISTS repository_stats (
157 did TEXT NOT NULL,
158 repository TEXT NOT NULL,
159 pull_count INTEGER NOT NULL DEFAULT 0,
160 last_pull TIMESTAMP,
161 push_count INTEGER NOT NULL DEFAULT 0,
162 last_push TIMESTAMP,
163 PRIMARY KEY(did, repository),
164 FOREIGN KEY(did) REFERENCES users(did) ON DELETE CASCADE
165);
166CREATE INDEX IF NOT EXISTS idx_repository_stats_did ON repository_stats(did);
167CREATE INDEX IF NOT EXISTS idx_repository_stats_pull_count ON repository_stats(pull_count DESC);
168
169CREATE TABLE IF NOT EXISTS stars (
170 starrer_did TEXT NOT NULL,
171 owner_did TEXT NOT NULL,
172 repository TEXT NOT NULL,
173 created_at TIMESTAMP NOT NULL,
174 PRIMARY KEY(starrer_did, owner_did, repository),
175 FOREIGN KEY(starrer_did) REFERENCES users(did) ON DELETE CASCADE,
176 FOREIGN KEY(owner_did) REFERENCES users(did) ON DELETE CASCADE
177);
178CREATE INDEX IF NOT EXISTS idx_stars_owner_repo ON stars(owner_did, repository);
179CREATE INDEX IF NOT EXISTS idx_stars_starrer ON stars(starrer_did);
180
181CREATE TABLE IF NOT EXISTS hold_captain_records (
182 hold_did TEXT PRIMARY KEY,
183 owner_did TEXT NOT NULL,
184 public BOOLEAN NOT NULL,
185 allow_all_crew BOOLEAN NOT NULL,
186 deployed_at TEXT,
187 region TEXT,
188 successor TEXT,
189 updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
190);
191CREATE INDEX IF NOT EXISTS idx_hold_captain_updated ON hold_captain_records(updated_at);
192
193CREATE TABLE IF NOT EXISTS hold_crew_approvals (
194 hold_did TEXT NOT NULL,
195 user_did TEXT NOT NULL,
196 approved_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
197 expires_at TIMESTAMP NOT NULL,
198 PRIMARY KEY(hold_did, user_did)
199);
200CREATE INDEX IF NOT EXISTS idx_crew_approvals_expires ON hold_crew_approvals(expires_at);
201
202CREATE TABLE IF NOT EXISTS hold_crew_denials (
203 hold_did TEXT NOT NULL,
204 user_did TEXT NOT NULL,
205 denial_count INTEGER NOT NULL DEFAULT 1,
206 next_retry_at TIMESTAMP NOT NULL,
207 last_denied_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
208 PRIMARY KEY(hold_did, user_did)
209);
210CREATE INDEX IF NOT EXISTS idx_crew_denials_retry ON hold_crew_denials(next_retry_at);
211
212-- Cached hold crew memberships from Jetstream
213-- Enables reverse lookup: "which holds is user X a member of?"
214CREATE TABLE IF NOT EXISTS hold_crew_members (
215 hold_did TEXT NOT NULL,
216 member_did TEXT NOT NULL,
217 rkey TEXT NOT NULL,
218 role TEXT,
219 permissions TEXT, -- JSON array
220 tier TEXT,
221 added_at TEXT,
222 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
223 updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
224 PRIMARY KEY (hold_did, member_did)
225);
226CREATE INDEX IF NOT EXISTS idx_hold_crew_member ON hold_crew_members(member_did);
227CREATE INDEX IF NOT EXISTS idx_hold_crew_hold ON hold_crew_members(hold_did);
228CREATE INDEX IF NOT EXISTS idx_hold_crew_rkey ON hold_crew_members(hold_did, rkey);
229
230CREATE TABLE IF NOT EXISTS repo_pages (
231 did TEXT NOT NULL,
232 repository TEXT NOT NULL,
233 description TEXT,
234 avatar_cid TEXT,
235 created_at TIMESTAMP NOT NULL,
236 updated_at TIMESTAMP NOT NULL,
237 PRIMARY KEY(did, repository),
238 FOREIGN KEY(did) REFERENCES users(did) ON DELETE CASCADE
239);
240CREATE INDEX IF NOT EXISTS idx_repo_pages_did ON repo_pages(did);
241
242CREATE TABLE IF NOT EXISTS crypto_keys (
243 name TEXT PRIMARY KEY,
244 key_data BLOB NOT NULL,
245 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
246);
247
248CREATE TABLE IF NOT EXISTS webhooks (
249 id TEXT PRIMARY KEY,
250 user_did TEXT NOT NULL,
251 url TEXT NOT NULL,
252 secret TEXT,
253 triggers INTEGER NOT NULL DEFAULT 1,
254 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
255 FOREIGN KEY(user_did) REFERENCES users(did) ON DELETE CASCADE
256);
257CREATE INDEX IF NOT EXISTS idx_webhooks_user ON webhooks(user_did);
258
259CREATE TABLE IF NOT EXISTS scans (
260 hold_did TEXT NOT NULL,
261 manifest_digest TEXT NOT NULL,
262 user_did TEXT NOT NULL,
263 repository TEXT NOT NULL,
264 critical INTEGER NOT NULL DEFAULT 0,
265 high INTEGER NOT NULL DEFAULT 0,
266 medium INTEGER NOT NULL DEFAULT 0,
267 low INTEGER NOT NULL DEFAULT 0,
268 total INTEGER NOT NULL DEFAULT 0,
269 scanner_version TEXT,
270 scanned_at TIMESTAMP NOT NULL,
271 PRIMARY KEY(hold_did, manifest_digest)
272);
273CREATE INDEX IF NOT EXISTS idx_scans_user ON scans(user_did);
274
275CREATE TABLE IF NOT EXISTS labels (
276 id INTEGER PRIMARY KEY AUTOINCREMENT,
277 src TEXT NOT NULL,
278 uri TEXT NOT NULL,
279 val TEXT NOT NULL,
280 neg BOOLEAN NOT NULL DEFAULT 0,
281 cts TIMESTAMP NOT NULL,
282 subject_did TEXT NOT NULL,
283 subject_repo TEXT NOT NULL DEFAULT '',
284 seq INTEGER NOT NULL DEFAULT 0,
285 UNIQUE(src, uri, val, neg)
286);
287CREATE INDEX IF NOT EXISTS idx_labels_subject ON labels(subject_did, subject_repo);
288CREATE INDEX IF NOT EXISTS idx_labels_val ON labels(val);