A container registry that uses the AT Protocol for manifest storage and S3 for blob storage.
atcr.io
docker
container
atproto
go
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 created_at TIMESTAMP NOT NULL,
31 UNIQUE(did, repository, digest),
32 FOREIGN KEY(did) REFERENCES users(did) ON DELETE CASCADE
33);
34CREATE INDEX IF NOT EXISTS idx_manifests_did_repo ON manifests(did, repository);
35CREATE INDEX IF NOT EXISTS idx_manifests_created_at ON manifests(created_at DESC);
36CREATE INDEX IF NOT EXISTS idx_manifests_digest ON manifests(digest);
37
38CREATE TABLE IF NOT EXISTS repository_annotations (
39 did TEXT NOT NULL,
40 repository TEXT NOT NULL,
41 key TEXT NOT NULL,
42 value TEXT NOT NULL,
43 updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
44 PRIMARY KEY(did, repository, key),
45 FOREIGN KEY(did) REFERENCES users(did) ON DELETE CASCADE
46);
47CREATE INDEX IF NOT EXISTS idx_repository_annotations_did_repo ON repository_annotations(did, repository);
48CREATE INDEX IF NOT EXISTS idx_repository_annotations_key ON repository_annotations(key);
49
50CREATE TABLE IF NOT EXISTS layers (
51 manifest_id INTEGER NOT NULL,
52 digest TEXT NOT NULL,
53 size INTEGER NOT NULL,
54 media_type TEXT NOT NULL,
55 layer_index INTEGER NOT NULL,
56 PRIMARY KEY(manifest_id, layer_index),
57 FOREIGN KEY(manifest_id) REFERENCES manifests(id) ON DELETE CASCADE
58);
59CREATE INDEX IF NOT EXISTS idx_layers_digest ON layers(digest);
60
61CREATE TABLE IF NOT EXISTS manifest_references (
62 manifest_id INTEGER NOT NULL,
63 digest TEXT NOT NULL,
64 media_type TEXT NOT NULL,
65 size INTEGER NOT NULL,
66 platform_architecture TEXT,
67 platform_os TEXT,
68 platform_variant TEXT,
69 platform_os_version TEXT,
70 reference_index INTEGER NOT NULL,
71 PRIMARY KEY(manifest_id, reference_index),
72 FOREIGN KEY(manifest_id) REFERENCES manifests(id) ON DELETE CASCADE
73);
74CREATE INDEX IF NOT EXISTS idx_manifest_references_digest ON manifest_references(digest);
75
76CREATE TABLE IF NOT EXISTS tags (
77 id INTEGER PRIMARY KEY AUTOINCREMENT,
78 did TEXT NOT NULL,
79 repository TEXT NOT NULL,
80 tag TEXT NOT NULL,
81 digest TEXT NOT NULL,
82 created_at TIMESTAMP NOT NULL,
83 UNIQUE(did, repository, tag),
84 FOREIGN KEY(did) REFERENCES users(did) ON DELETE CASCADE
85);
86CREATE INDEX IF NOT EXISTS idx_tags_did_repo ON tags(did, repository);
87
88CREATE TABLE IF NOT EXISTS oauth_sessions (
89 session_key TEXT PRIMARY KEY,
90 account_did TEXT NOT NULL,
91 session_id TEXT NOT NULL,
92 session_data TEXT NOT NULL,
93 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
94 updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
95 UNIQUE(account_did, session_id)
96);
97CREATE INDEX IF NOT EXISTS idx_oauth_sessions_did ON oauth_sessions(account_did);
98CREATE INDEX IF NOT EXISTS idx_oauth_sessions_updated ON oauth_sessions(updated_at DESC);
99
100CREATE TABLE IF NOT EXISTS oauth_auth_requests (
101 state TEXT PRIMARY KEY,
102 request_data TEXT NOT NULL,
103 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
104);
105CREATE INDEX IF NOT EXISTS idx_oauth_auth_requests_created ON oauth_auth_requests(created_at);
106
107CREATE TABLE IF NOT EXISTS ui_sessions (
108 id TEXT PRIMARY KEY,
109 did TEXT NOT NULL,
110 handle TEXT NOT NULL,
111 pds_endpoint TEXT NOT NULL,
112 oauth_session_id TEXT,
113 expires_at TIMESTAMP NOT NULL,
114 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
115 FOREIGN KEY(did) REFERENCES users(did) ON DELETE CASCADE
116);
117CREATE INDEX IF NOT EXISTS idx_ui_sessions_did ON ui_sessions(did);
118CREATE INDEX IF NOT EXISTS idx_ui_sessions_expires ON ui_sessions(expires_at);
119
120CREATE TABLE IF NOT EXISTS devices (
121 id TEXT PRIMARY KEY,
122 did TEXT NOT NULL,
123 handle TEXT NOT NULL,
124 name TEXT NOT NULL,
125 secret_hash TEXT NOT NULL UNIQUE,
126 ip_address TEXT,
127 location TEXT,
128 user_agent TEXT,
129 created_at TIMESTAMP NOT NULL,
130 last_used TIMESTAMP,
131 FOREIGN KEY(did) REFERENCES users(did) ON DELETE CASCADE
132);
133CREATE INDEX IF NOT EXISTS idx_devices_did ON devices(did);
134CREATE INDEX IF NOT EXISTS idx_devices_hash ON devices(secret_hash);
135
136CREATE TABLE IF NOT EXISTS pending_device_auth (
137 device_code TEXT PRIMARY KEY,
138 user_code TEXT NOT NULL UNIQUE,
139 device_name TEXT NOT NULL,
140 ip_address TEXT,
141 user_agent TEXT,
142 expires_at TIMESTAMP NOT NULL,
143 approved_did TEXT,
144 approved_at TIMESTAMP,
145 device_secret TEXT,
146 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
147);
148CREATE INDEX IF NOT EXISTS idx_pending_device_auth_user_code ON pending_device_auth(user_code);
149CREATE INDEX IF NOT EXISTS idx_pending_device_auth_expires ON pending_device_auth(expires_at);
150
151CREATE TABLE IF NOT EXISTS repository_stats (
152 did TEXT NOT NULL,
153 repository TEXT NOT NULL,
154 pull_count INTEGER NOT NULL DEFAULT 0,
155 last_pull TIMESTAMP,
156 push_count INTEGER NOT NULL DEFAULT 0,
157 last_push TIMESTAMP,
158 PRIMARY KEY(did, repository),
159 FOREIGN KEY(did) REFERENCES users(did) ON DELETE CASCADE
160);
161CREATE INDEX IF NOT EXISTS idx_repository_stats_did ON repository_stats(did);
162CREATE INDEX IF NOT EXISTS idx_repository_stats_pull_count ON repository_stats(pull_count DESC);
163
164CREATE TABLE IF NOT EXISTS stars (
165 starrer_did TEXT NOT NULL,
166 owner_did TEXT NOT NULL,
167 repository TEXT NOT NULL,
168 created_at TIMESTAMP NOT NULL,
169 PRIMARY KEY(starrer_did, owner_did, repository),
170 FOREIGN KEY(starrer_did) REFERENCES users(did) ON DELETE CASCADE,
171 FOREIGN KEY(owner_did) REFERENCES users(did) ON DELETE CASCADE
172);
173CREATE INDEX IF NOT EXISTS idx_stars_owner_repo ON stars(owner_did, repository);
174CREATE INDEX IF NOT EXISTS idx_stars_starrer ON stars(starrer_did);
175
176CREATE TABLE IF NOT EXISTS hold_captain_records (
177 hold_did TEXT PRIMARY KEY,
178 owner_did TEXT NOT NULL,
179 public BOOLEAN NOT NULL,
180 allow_all_crew BOOLEAN NOT NULL,
181 deployed_at TEXT,
182 region TEXT,
183 provider TEXT,
184 updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
185);
186CREATE INDEX IF NOT EXISTS idx_hold_captain_updated ON hold_captain_records(updated_at);
187
188CREATE TABLE IF NOT EXISTS hold_crew_approvals (
189 hold_did TEXT NOT NULL,
190 user_did TEXT NOT NULL,
191 approved_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
192 expires_at TIMESTAMP NOT NULL,
193 PRIMARY KEY(hold_did, user_did)
194);
195CREATE INDEX IF NOT EXISTS idx_crew_approvals_expires ON hold_crew_approvals(expires_at);
196
197CREATE TABLE IF NOT EXISTS hold_crew_denials (
198 hold_did TEXT NOT NULL,
199 user_did TEXT NOT NULL,
200 denial_count INTEGER NOT NULL DEFAULT 1,
201 next_retry_at TIMESTAMP NOT NULL,
202 last_denied_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
203 PRIMARY KEY(hold_did, user_did)
204);
205CREATE INDEX IF NOT EXISTS idx_crew_denials_retry ON hold_crew_denials(next_retry_at);
206
207CREATE TABLE IF NOT EXISTS readme_cache (
208 url TEXT PRIMARY KEY,
209 html TEXT NOT NULL,
210 fetched_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
211);
212CREATE INDEX IF NOT EXISTS idx_readme_cache_fetched ON readme_cache(fetched_at);