Sign in

flagon-io/g1t

public

g1t/services/packages/migrations/0001_init.sql

124 lines4,439 bytesCodeBlame
1-- Packages: the registries beside the code (docs/PACKAGES.md). Times are
2-- RFC 3339 text, sizes bytes.
3
4CREATE TABLE packages (
5 id TEXT PRIMARY KEY,
6 workspace TEXT NOT NULL,
7 -- container, npm, composer, cargo, go.
8 ecosystem TEXT NOT NULL,
9 -- Normalized per ecosystem; for a container image, the name after the
10 -- workspace (`web/api` for g1t.sh/acme/web/api).
11 name TEXT NOT NULL,
12 -- The repository it is linked to, and its name as it is now (kept by
13 -- repo.renamed). A linked package has its repository's visibility.
14 repo_id TEXT,
15 repo_name TEXT,
16 visibility TEXT NOT NULL DEFAULT 'private',
17 description TEXT,
18 readme_digest TEXT,
19 created_by TEXT NOT NULL,
20 created_at TEXT NOT NULL,
21 updated_at TEXT NOT NULL,
22 downloads INTEGER NOT NULL DEFAULT 0,
23 UNIQUE (workspace, ecosystem, name)
24);
25CREATE INDEX packages_workspace ON packages (workspace, updated_at);
26CREATE INDEX packages_repo ON packages (repo_id);
27
28CREATE TABLE versions (
29 id TEXT PRIMARY KEY,
30 package_id TEXT NOT NULL REFERENCES packages (id) ON DELETE CASCADE,
31 -- A tag, semver or, for a container image, its manifest's digest.
32 version TEXT NOT NULL,
33 -- The manifest's or the archive's digest.
34 digest TEXT NOT NULL,
35 -- Bytes of its files.
36 size INTEGER NOT NULL,
37 -- What the ecosystem needs: for a container image its media type,
38 -- artifact type, annotations and platforms.
39 metadata TEXT NOT NULL DEFAULT '{}',
40 -- For an OCI artifact: the digest of the manifest it is about.
41 subject TEXT,
42 published_by TEXT,
43 published_at TEXT NOT NULL,
44 yanked INTEGER NOT NULL DEFAULT 0,
45 deprecated TEXT,
46 UNIQUE (package_id, version)
47);
48CREATE INDEX versions_digest ON versions (package_id, digest);
49CREATE INDEX versions_subject ON versions (package_id, subject) WHERE subject IS NOT NULL;
50CREATE INDEX versions_published ON versions (package_id, published_at);
51
52CREATE TABLE version_files (
53 version_id TEXT NOT NULL REFERENCES versions (id) ON DELETE CASCADE,
54 -- `manifest`, `config`, `layer:<n>`; a file name for other ecosystems.
55 name TEXT NOT NULL,
56 digest TEXT NOT NULL,
57 size INTEGER NOT NULL,
58 media_type TEXT,
59 PRIMARY KEY (version_id, name)
60);
61-- The sweep asks whether any version still uses a blob.
62CREATE INDEX version_files_digest ON version_files (digest);
63
64-- Every file kept, once, by digest. `object_key` is where it is in the
65-- store: blobs/sha256/<hex> when stored whole, blobs/parts/<upload> when it
66-- came in parts. `touched_at` moves when a push finds it already there, so
67-- the sweep spares a blob a push in flight is about to use.
68CREATE TABLE blobs (
69 digest TEXT PRIMARY KEY,
70 size INTEGER NOT NULL,
71 media_type TEXT,
72 object_key TEXT NOT NULL,
73 created_at TEXT NOT NULL,
74 touched_at TEXT NOT NULL
75);
76CREATE INDEX blobs_touched ON blobs (touched_at);
77
78-- Which blobs each package may serve: uploaded or mounted into it, or named
79-- by its manifests. A digest alone never reads another package's blob.
80CREATE TABLE package_blobs (
81 package_id TEXT NOT NULL REFERENCES packages (id) ON DELETE CASCADE,
82 digest TEXT NOT NULL,
83 created_at TEXT NOT NULL,
84 PRIMARY KEY (package_id, digest)
85);
86CREATE INDEX package_blobs_digest ON package_blobs (digest);
87
88-- What a workspace stores, each blob once, and whether any public package
89-- uses it: what billing measures.
90CREATE TABLE workspace_blobs (
91 workspace TEXT NOT NULL,
92 digest TEXT NOT NULL,
93 size INTEGER NOT NULL,
94 public INTEGER NOT NULL DEFAULT 0,
95 PRIMARY KEY (workspace, digest)
96);
97
98-- Uploads in progress: the multipart upload, its parts, how far it is,
99-- the SHA-256 state so far, and the bytes in its tail (src/upload.rs).
100CREATE TABLE uploads (
101 id TEXT PRIMARY KEY,
102 workspace TEXT NOT NULL,
103 package_id TEXT NOT NULL,
104 -- The image name it was started for, `workspace/name`.
105 package TEXT NOT NULL,
106 multipart_id TEXT,
107 parts TEXT NOT NULL DEFAULT '[]',
108 "offset" INTEGER NOT NULL DEFAULT 0,
109 tail INTEGER NOT NULL DEFAULT 0,
110 hash_state TEXT NOT NULL,
111 created_at TEXT NOT NULL,
112 expires_at TEXT NOT NULL
113);
114CREATE INDEX uploads_expires ON uploads (expires_at);
115
116-- Container tags and npm dist-tags.
117CREATE TABLE tags (
118 package_id TEXT NOT NULL REFERENCES packages (id) ON DELETE CASCADE,
119 tag TEXT NOT NULL,
120 version_id TEXT NOT NULL REFERENCES versions (id) ON DELETE CASCADE,
121 updated_at TEXT NOT NULL,
122 PRIMARY KEY (package_id, tag)
123);
124CREATE INDEX tags_version ON tags (version_id);