cuducos.me
forked from
evan.jarrett.net/at-container-registry
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 last_seen TIMESTAMP NOT NULL,
16 UNIQUE(handle)
17);
18CREATE INDEX IF NOT EXISTS idx_users_handle ON users(handle);
19
20CREATE TABLE IF NOT EXISTS manifests (
21 id INTEGER PRIMARY KEY AUTOINCREMENT,
22 did TEXT NOT NULL,
23 repository TEXT NOT NULL,
24 digest TEXT NOT NULL,
25 hold_endpoint TEXT NOT NULL, -- Stored as DID (e.g., did:web:hold.example.com)
26 schema_version INTEGER NOT NULL,
27 media_type TEXT NOT NULL,
28 config_digest TEXT,
29 config_size INTEGER,
30 artifact_type TEXT NOT NULL DEFAULT 'container-image', -- container-image, helm-chart, unknown
31 created_at TIMESTAMP NOT NULL,
32 UNIQUE(did, repository, digest),
33 FOREIGN KEY(did) REFERENCES users(did) ON DELETE CASCADE
34);
35CREATE INDEX IF NOT EXISTS idx_manifests_did_repo ON manifests(did, repository);
36CREATE INDEX IF NOT EXISTS idx_manifests_created_at ON manifests(created_at DESC);
37CREATE INDEX IF NOT EXISTS idx_manifests_digest ON manifests(digest);
38CREATE INDEX IF NOT EXISTS idx_manifests_artifact_type ON manifests(artifact_type);
39
40CREATE TABLE IF NOT EXISTS repository_annotations (
41 did TEXT NOT NULL,
42 repository TEXT NOT NULL,
43 key TEXT NOT NULL,
44 value TEXT NOT NULL,
45 updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
46 PRIMARY KEY(did, repository, key),
47 FOREIGN KEY(did) REFERENCES users(did) ON DELETE CASCADE
48);
49CREATE INDEX IF NOT EXISTS idx_repository_annotations_did_repo ON repository_annotations(did, repository);
50CREATE INDEX IF NOT EXISTS idx_repository_annotations_key ON repository_annotations(key);
51
52CREATE TABLE IF NOT EXISTS layers (
53 manifest_id INTEGER NOT NULL,
54 digest TEXT NOT NULL,
55 size INTEGER NOT NULL,
56 media_type TEXT NOT NULL,
57 layer_index INTEGER NOT NULL,
58 PRIMARY KEY(manifest_id, layer_index),
59 FOREIGN KEY(manifest_id) REFERENCES manifests(id) ON DELETE CASCADE
60);
61CREATE INDEX IF NOT EXISTS idx_layers_digest ON layers(digest);
62
63CREATE TABLE IF NOT EXISTS manifest_references (
64 manifest_id INTEGER NOT NULL,
65 digest TEXT NOT NULL,
66 media_type TEXT NOT NULL,
67 size INTEGER NOT NULL,
68 platform_architecture TEXT,
69 platform_os TEXT,
70 platform_variant TEXT,
71 platform_os_version TEXT,
72 is_attestation BOOLEAN DEFAULT FALSE,
73 reference_index INTEGER NOT NULL,
74 PRIMARY KEY(manifest_id, reference_index),
75 FOREIGN KEY(manifest_id) REFERENCES manifests(id) ON DELETE CASCADE
76);
77CREATE INDEX IF NOT EXISTS idx_manifest_references_digest ON manifest_references(digest);
78
79CREATE TABLE IF NOT EXISTS tags (
80 id INTEGER PRIMARY KEY AUTOINCREMENT,
81 did TEXT NOT NULL,
82 repository TEXT NOT NULL,
83 tag TEXT NOT NULL,
84 digest TEXT NOT NULL,
85 created_at TIMESTAMP NOT NULL,
86 UNIQUE(did, repository, tag),
87 FOREIGN KEY(did) REFERENCES users(did) ON DELETE CASCADE
88);
89CREATE INDEX IF NOT EXISTS idx_tags_did_repo ON tags(did, repository);
90
91CREATE TABLE IF NOT EXISTS oauth_sessions (
92 session_key TEXT PRIMARY KEY,
93 account_did TEXT NOT NULL,
94 session_id TEXT NOT NULL,
95 session_data TEXT NOT NULL,
96 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
97 updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
98 UNIQUE(account_did, session_id)
99);
100CREATE INDEX IF NOT EXISTS idx_oauth_sessions_did ON oauth_sessions(account_did);
101CREATE INDEX IF NOT EXISTS idx_oauth_sessions_updated ON oauth_sessions(updated_at DESC);
102
103CREATE TABLE IF NOT EXISTS oauth_auth_requests (
104 state TEXT PRIMARY KEY,
105 request_data TEXT NOT NULL,
106 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
107);
108CREATE INDEX IF NOT EXISTS idx_oauth_auth_requests_created ON oauth_auth_requests(created_at);
109
110CREATE TABLE IF NOT EXISTS ui_sessions (
111 id TEXT PRIMARY KEY,
112 did TEXT NOT NULL,
113 handle TEXT NOT NULL,
114 pds_endpoint TEXT NOT NULL,
115 oauth_session_id TEXT,
116 expires_at TIMESTAMP NOT NULL,
117 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
118 FOREIGN KEY(did) REFERENCES users(did) ON DELETE CASCADE
119);
120CREATE INDEX IF NOT EXISTS idx_ui_sessions_did ON ui_sessions(did);
121CREATE INDEX IF NOT EXISTS idx_ui_sessions_expires ON ui_sessions(expires_at);
122
123CREATE TABLE IF NOT EXISTS devices (
124 id TEXT PRIMARY KEY,
125 did TEXT NOT NULL,
126 handle TEXT NOT NULL,
127 name TEXT NOT NULL,
128 secret_hash TEXT NOT NULL UNIQUE,
129 ip_address TEXT,
130 location TEXT,
131 user_agent TEXT,
132 created_at TIMESTAMP NOT NULL,
133 last_used TIMESTAMP,
134 FOREIGN KEY(did) REFERENCES users(did) ON DELETE CASCADE
135);
136CREATE INDEX IF NOT EXISTS idx_devices_did ON devices(did);
137CREATE INDEX IF NOT EXISTS idx_devices_hash ON devices(secret_hash);
138
139CREATE TABLE IF NOT EXISTS pending_device_auth (
140 device_code TEXT PRIMARY KEY,
141 user_code TEXT NOT NULL UNIQUE,
142 device_name TEXT NOT NULL,
143 ip_address TEXT,
144 user_agent TEXT,
145 expires_at TIMESTAMP NOT NULL,
146 approved_did TEXT,
147 approved_at TIMESTAMP,
148 device_secret TEXT,
149 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
150);
151CREATE INDEX IF NOT EXISTS idx_pending_device_auth_user_code ON pending_device_auth(user_code);
152CREATE INDEX IF NOT EXISTS idx_pending_device_auth_expires ON pending_device_auth(expires_at);
153
154CREATE TABLE IF NOT EXISTS repository_stats (
155 did TEXT NOT NULL,
156 repository TEXT NOT NULL,
157 pull_count INTEGER NOT NULL DEFAULT 0,
158 last_pull TIMESTAMP,
159 push_count INTEGER NOT NULL DEFAULT 0,
160 last_push TIMESTAMP,
161 PRIMARY KEY(did, repository),
162 FOREIGN KEY(did) REFERENCES users(did) ON DELETE CASCADE
163);
164CREATE INDEX IF NOT EXISTS idx_repository_stats_did ON repository_stats(did);
165CREATE INDEX IF NOT EXISTS idx_repository_stats_pull_count ON repository_stats(pull_count DESC);
166
167CREATE TABLE IF NOT EXISTS stars (
168 starrer_did TEXT NOT NULL,
169 owner_did TEXT NOT NULL,
170 repository TEXT NOT NULL,
171 created_at TIMESTAMP NOT NULL,
172 PRIMARY KEY(starrer_did, owner_did, repository),
173 FOREIGN KEY(starrer_did) REFERENCES users(did) ON DELETE CASCADE,
174 FOREIGN KEY(owner_did) REFERENCES users(did) ON DELETE CASCADE
175);
176CREATE INDEX IF NOT EXISTS idx_stars_owner_repo ON stars(owner_did, repository);
177CREATE INDEX IF NOT EXISTS idx_stars_starrer ON stars(starrer_did);
178
179CREATE TABLE IF NOT EXISTS hold_captain_records (
180 hold_did TEXT PRIMARY KEY,
181 owner_did TEXT NOT NULL,
182 public BOOLEAN NOT NULL,
183 allow_all_crew BOOLEAN NOT NULL,
184 deployed_at TEXT,
185 region TEXT,
186 provider TEXT,
187 updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
188);
189CREATE INDEX IF NOT EXISTS idx_hold_captain_updated ON hold_captain_records(updated_at);
190
191CREATE TABLE IF NOT EXISTS hold_crew_approvals (
192 hold_did TEXT NOT NULL,
193 user_did TEXT NOT NULL,
194 approved_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
195 expires_at TIMESTAMP NOT NULL,
196 PRIMARY KEY(hold_did, user_did)
197);
198CREATE INDEX IF NOT EXISTS idx_crew_approvals_expires ON hold_crew_approvals(expires_at);
199
200CREATE TABLE IF NOT EXISTS hold_crew_denials (
201 hold_did TEXT NOT NULL,
202 user_did TEXT NOT NULL,
203 denial_count INTEGER NOT NULL DEFAULT 1,
204 next_retry_at TIMESTAMP NOT NULL,
205 last_denied_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
206 PRIMARY KEY(hold_did, user_did)
207);
208CREATE INDEX IF NOT EXISTS idx_crew_denials_retry ON hold_crew_denials(next_retry_at);
209
210CREATE TABLE IF NOT EXISTS repo_pages (
211 did TEXT NOT NULL,
212 repository TEXT NOT NULL,
213 description TEXT,
214 avatar_cid TEXT,
215 created_at TIMESTAMP NOT NULL,
216 updated_at TIMESTAMP NOT NULL,
217 PRIMARY KEY(did, repository),
218 FOREIGN KEY(did) REFERENCES users(did) ON DELETE CASCADE
219);
220CREATE INDEX IF NOT EXISTS idx_repo_pages_did ON repo_pages(did);