Something went wrong. Try again.
A container registry that uses the AT Protocol for manifest storage and S3 for blob storage.
Something went wrong. Try again.
7.8 kB · 213 lines
SQL
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214-- ATCR AppView Database Schema-- This file contains the complete base schema for fresh database installations.-- Migrations (in migrations/*.yaml) handle changes to existing databases.
CREATE TABLE IF NOT EXISTS schema_migrations ( version INTEGER PRIMARY KEY, applied_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP);
CREATE TABLE IF NOT EXISTS users ( did TEXT PRIMARY KEY, handle TEXT NOT NULL, pds_endpoint TEXT NOT NULL, avatar TEXT, last_seen TIMESTAMP NOT NULL, UNIQUE(handle));CREATE INDEX IF NOT EXISTS idx_users_handle ON users(handle);
CREATE TABLE IF NOT EXISTS manifests ( id INTEGER PRIMARY KEY AUTOINCREMENT, did TEXT NOT NULL, repository TEXT NOT NULL, digest TEXT NOT NULL, hold_endpoint TEXT NOT NULL, -- Stored as DID (e.g., did:web:hold.example.com) schema_version INTEGER NOT NULL, media_type TEXT NOT NULL, config_digest TEXT, config_size INTEGER, created_at TIMESTAMP NOT NULL, UNIQUE(did, repository, digest), FOREIGN KEY(did) REFERENCES users(did) ON DELETE CASCADE);CREATE INDEX IF NOT EXISTS idx_manifests_did_repo ON manifests(did, repository);CREATE INDEX IF NOT EXISTS idx_manifests_created_at ON manifests(created_at DESC);CREATE INDEX IF NOT EXISTS idx_manifests_digest ON manifests(digest);
CREATE TABLE IF NOT EXISTS repository_annotations ( did TEXT NOT NULL, repository TEXT NOT NULL, key TEXT NOT NULL, value TEXT NOT NULL, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY(did, repository, key), FOREIGN KEY(did) REFERENCES users(did) ON DELETE CASCADE);CREATE INDEX IF NOT EXISTS idx_repository_annotations_did_repo ON repository_annotations(did, repository);CREATE INDEX IF NOT EXISTS idx_repository_annotations_key ON repository_annotations(key);
CREATE TABLE IF NOT EXISTS layers ( manifest_id INTEGER NOT NULL, digest TEXT NOT NULL, size INTEGER NOT NULL, media_type TEXT NOT NULL, layer_index INTEGER NOT NULL, PRIMARY KEY(manifest_id, layer_index), FOREIGN KEY(manifest_id) REFERENCES manifests(id) ON DELETE CASCADE);CREATE INDEX IF NOT EXISTS idx_layers_digest ON layers(digest);
CREATE TABLE IF NOT EXISTS manifest_references ( manifest_id INTEGER NOT NULL, digest TEXT NOT NULL, media_type TEXT NOT NULL, size INTEGER NOT NULL, platform_architecture TEXT, platform_os TEXT, platform_variant TEXT, platform_os_version TEXT, is_attestation BOOLEAN DEFAULT FALSE, reference_index INTEGER NOT NULL, PRIMARY KEY(manifest_id, reference_index), FOREIGN KEY(manifest_id) REFERENCES manifests(id) ON DELETE CASCADE);CREATE INDEX IF NOT EXISTS idx_manifest_references_digest ON manifest_references(digest);
CREATE TABLE IF NOT EXISTS tags ( id INTEGER PRIMARY KEY AUTOINCREMENT, did TEXT NOT NULL, repository TEXT NOT NULL, tag TEXT NOT NULL, digest TEXT NOT NULL, created_at TIMESTAMP NOT NULL, UNIQUE(did, repository, tag), FOREIGN KEY(did) REFERENCES users(did) ON DELETE CASCADE);CREATE INDEX IF NOT EXISTS idx_tags_did_repo ON tags(did, repository);
CREATE TABLE IF NOT EXISTS oauth_sessions ( session_key TEXT PRIMARY KEY, account_did TEXT NOT NULL, session_id TEXT NOT NULL, session_data TEXT NOT NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, UNIQUE(account_did, session_id));CREATE INDEX IF NOT EXISTS idx_oauth_sessions_did ON oauth_sessions(account_did);CREATE INDEX IF NOT EXISTS idx_oauth_sessions_updated ON oauth_sessions(updated_at DESC);
CREATE TABLE IF NOT EXISTS oauth_auth_requests ( state TEXT PRIMARY KEY, request_data TEXT NOT NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP);CREATE INDEX IF NOT EXISTS idx_oauth_auth_requests_created ON oauth_auth_requests(created_at);
CREATE TABLE IF NOT EXISTS ui_sessions ( id TEXT PRIMARY KEY, did TEXT NOT NULL, handle TEXT NOT NULL, pds_endpoint TEXT NOT NULL, oauth_session_id TEXT, expires_at TIMESTAMP NOT NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY(did) REFERENCES users(did) ON DELETE CASCADE);CREATE INDEX IF NOT EXISTS idx_ui_sessions_did ON ui_sessions(did);CREATE INDEX IF NOT EXISTS idx_ui_sessions_expires ON ui_sessions(expires_at);
CREATE TABLE IF NOT EXISTS devices ( id TEXT PRIMARY KEY, did TEXT NOT NULL, handle TEXT NOT NULL, name TEXT NOT NULL, secret_hash TEXT NOT NULL UNIQUE, ip_address TEXT, location TEXT, user_agent TEXT, created_at TIMESTAMP NOT NULL, last_used TIMESTAMP, FOREIGN KEY(did) REFERENCES users(did) ON DELETE CASCADE);CREATE INDEX IF NOT EXISTS idx_devices_did ON devices(did);CREATE INDEX IF NOT EXISTS idx_devices_hash ON devices(secret_hash);
CREATE TABLE IF NOT EXISTS pending_device_auth ( device_code TEXT PRIMARY KEY, user_code TEXT NOT NULL UNIQUE, device_name TEXT NOT NULL, ip_address TEXT, user_agent TEXT, expires_at TIMESTAMP NOT NULL, approved_did TEXT, approved_at TIMESTAMP, device_secret TEXT, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP);CREATE INDEX IF NOT EXISTS idx_pending_device_auth_user_code ON pending_device_auth(user_code);CREATE INDEX IF NOT EXISTS idx_pending_device_auth_expires ON pending_device_auth(expires_at);
CREATE TABLE IF NOT EXISTS repository_stats ( did TEXT NOT NULL, repository TEXT NOT NULL, pull_count INTEGER NOT NULL DEFAULT 0, last_pull TIMESTAMP, push_count INTEGER NOT NULL DEFAULT 0, last_push TIMESTAMP, PRIMARY KEY(did, repository), FOREIGN KEY(did) REFERENCES users(did) ON DELETE CASCADE);CREATE INDEX IF NOT EXISTS idx_repository_stats_did ON repository_stats(did);CREATE INDEX IF NOT EXISTS idx_repository_stats_pull_count ON repository_stats(pull_count DESC);
CREATE TABLE IF NOT EXISTS stars ( starrer_did TEXT NOT NULL, owner_did TEXT NOT NULL, repository TEXT NOT NULL, created_at TIMESTAMP NOT NULL, PRIMARY KEY(starrer_did, owner_did, repository), FOREIGN KEY(starrer_did) REFERENCES users(did) ON DELETE CASCADE, FOREIGN KEY(owner_did) REFERENCES users(did) ON DELETE CASCADE);CREATE INDEX IF NOT EXISTS idx_stars_owner_repo ON stars(owner_did, repository);CREATE INDEX IF NOT EXISTS idx_stars_starrer ON stars(starrer_did);
CREATE TABLE IF NOT EXISTS hold_captain_records ( hold_did TEXT PRIMARY KEY, owner_did TEXT NOT NULL, public BOOLEAN NOT NULL, allow_all_crew BOOLEAN NOT NULL, deployed_at TEXT, region TEXT, provider TEXT, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);CREATE INDEX IF NOT EXISTS idx_hold_captain_updated ON hold_captain_records(updated_at);
CREATE TABLE IF NOT EXISTS hold_crew_approvals ( hold_did TEXT NOT NULL, user_did TEXT NOT NULL, approved_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, expires_at TIMESTAMP NOT NULL, PRIMARY KEY(hold_did, user_did));CREATE INDEX IF NOT EXISTS idx_crew_approvals_expires ON hold_crew_approvals(expires_at);
CREATE TABLE IF NOT EXISTS hold_crew_denials ( hold_did TEXT NOT NULL, user_did TEXT NOT NULL, denial_count INTEGER NOT NULL DEFAULT 1, next_retry_at TIMESTAMP NOT NULL, last_denied_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY(hold_did, user_did));CREATE INDEX IF NOT EXISTS idx_crew_denials_retry ON hold_crew_denials(next_retry_at);
CREATE TABLE IF NOT EXISTS readme_cache ( url TEXT PRIMARY KEY, html TEXT NOT NULL, fetched_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP);CREATE INDEX IF NOT EXISTS idx_readme_cache_fetched ON readme_cache(fetched_at);