flagon-io/g1t

public

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

124 lines4,439 bytesCode Blame

Pick any line to see why it is the way it is: the commit, the pull request and issue it came from, and what the agent was thinking.

Packages, with a container registry on g1t.sh; workspaces deleted whole and kept 30 days; Members for every member23h ago	1	-- Packages: the registries beside the code (docs/PACKAGES.md). Times are
	2	-- RFC 3339 text, sizes bytes.
	3
	4	CREATE 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	);
	25	CREATE INDEX packages_workspace ON packages (workspace, updated_at);
	26	CREATE INDEX packages_repo ON packages (repo_id);
	27
	28	CREATE 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	);
	48	CREATE INDEX versions_digest ON versions (package_id, digest);
	49	CREATE INDEX versions_subject ON versions (package_id, subject) WHERE subject IS NOT NULL;
	50	CREATE INDEX versions_published ON versions (package_id, published_at);
	51
	52	CREATE 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.
	62	CREATE 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.
	68	CREATE 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	);
	76	CREATE 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.
	80	CREATE 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	);
	86	CREATE 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.
	90	CREATE 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).
	100	CREATE 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	);
	114	CREATE INDEX uploads_expires ON uploads (expires_at);
	115
	116	-- Container tags and npm dist-tags.
	117	CREATE 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	);
	124	CREATE INDEX tags_version ON tags (version_id);