| 1 | -- Container registry (OCI distribution API at /v2/). Blob and manifest bytes live in R2; |
| 2 | -- this is the index: packages, which blobs and manifests each one holds, tags, and uploads. |
| 3 | |
| 4 | -- A package is an image name below an owner: diffinery.com/<owner>/<name>. |
| 5 | -- Linked to a repository, it shares that repository's permissions (like ghcr.io). |
| 6 | CREATE TABLE packages ( |
| 7 | id TEXT PRIMARY KEY, |
| 8 | owner_id TEXT NOT NULL REFERENCES accounts(id), |
| 9 | name TEXT NOT NULL COLLATE NOCASE, |
| 10 | repo_id TEXT REFERENCES repos(id) ON DELETE SET NULL, |
| 11 | visibility TEXT NOT NULL DEFAULT 'private' CHECK (visibility IN ('private', 'internal', 'public')), |
| 12 | description TEXT, |
| 13 | created_by TEXT, |
| 14 | created_at TEXT NOT NULL, |
| 15 | updated_at TEXT NOT NULL, |
| 16 | UNIQUE (owner_id, name) |
| 17 | ); |
| 18 | CREATE INDEX packages_repo ON packages(repo_id); |
| 19 | |
| 20 | -- Content-addressed blobs, stored once however many packages use them. |
| 21 | CREATE TABLE registry_blobs ( |
| 22 | digest TEXT PRIMARY KEY, |
| 23 | size INTEGER NOT NULL, |
| 24 | r2_key TEXT NOT NULL, |
| 25 | created_at TEXT NOT NULL |
| 26 | ); |
| 27 | |
| 28 | CREATE TABLE package_blobs ( |
| 29 | package_id TEXT NOT NULL REFERENCES packages(id) ON DELETE CASCADE, |
| 30 | digest TEXT NOT NULL, |
| 31 | created_at TEXT NOT NULL, |
| 32 | PRIMARY KEY (package_id, digest) |
| 33 | ); |
| 34 | CREATE INDEX package_blobs_digest ON package_blobs(digest); |
| 35 | |
| 36 | CREATE TABLE package_manifests ( |
| 37 | package_id TEXT NOT NULL REFERENCES packages(id) ON DELETE CASCADE, |
| 38 | digest TEXT NOT NULL, |
| 39 | media_type TEXT NOT NULL, |
| 40 | artifact_type TEXT, |
| 41 | subject TEXT, |
| 42 | annotations TEXT, |
| 43 | size INTEGER NOT NULL, |
| 44 | total_size INTEGER NOT NULL DEFAULT 0, |
| 45 | platforms TEXT, |
| 46 | pushed_by TEXT, |
| 47 | created_at TEXT NOT NULL, |
| 48 | PRIMARY KEY (package_id, digest) |
| 49 | ); |
| 50 | CREATE INDEX package_manifests_subject ON package_manifests(package_id, subject); |
| 51 | CREATE INDEX package_manifests_digest ON package_manifests(digest); |
| 52 | |
| 53 | CREATE TABLE package_tags ( |
| 54 | package_id TEXT NOT NULL REFERENCES packages(id) ON DELETE CASCADE, |
| 55 | tag TEXT NOT NULL, |
| 56 | digest TEXT NOT NULL, |
| 57 | updated_at TEXT NOT NULL, |
| 58 | PRIMARY KEY (package_id, tag) |
| 59 | ); |
| 60 | |
| 61 | -- Blob uploads in progress: an R2 object (small uploads) or multipart upload, plus a tail |
| 62 | -- object holding bytes that don't fill a whole part yet. |
| 63 | CREATE TABLE registry_uploads ( |
| 64 | id TEXT PRIMARY KEY, |
| 65 | package_id TEXT NOT NULL REFERENCES packages(id) ON DELETE CASCADE, |
| 66 | r2_key TEXT NOT NULL, |
| 67 | multipart_id TEXT, |
| 68 | parts TEXT NOT NULL DEFAULT '[]', |
| 69 | size INTEGER NOT NULL DEFAULT 0, |
| 70 | tail INTEGER NOT NULL DEFAULT 0, |
| 71 | digest TEXT, |
| 72 | created_at TEXT NOT NULL, |
| 73 | updated_at TEXT NOT NULL |
| 74 | ); |
| 75 | CREATE INDEX registry_uploads_updated ON registry_uploads(updated_at); |
| 76 | |
| 77 | -- Which blobs each manifest uses, so blobs a package no longer needs can be dropped. |
| 78 | CREATE TABLE package_manifest_blobs ( |
| 79 | package_id TEXT NOT NULL REFERENCES packages(id) ON DELETE CASCADE, |
| 80 | manifest TEXT NOT NULL, |
| 81 | digest TEXT NOT NULL, |
| 82 | PRIMARY KEY (package_id, manifest, digest) |
| 83 | ); |
| 84 | CREATE INDEX package_manifest_blobs_digest ON package_manifest_blobs(package_id, digest); |