219 lines
7.7 KiB
SQL
219 lines
7.7 KiB
SQL
-- 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 repo_pages (
|
|
did TEXT NOT NULL,
|
|
repository TEXT NOT NULL,
|
|
description TEXT,
|
|
avatar_cid TEXT,
|
|
created_at TIMESTAMP NOT NULL,
|
|
updated_at TIMESTAMP NOT NULL,
|
|
PRIMARY KEY(did, repository),
|
|
FOREIGN KEY(did) REFERENCES users(did) ON DELETE CASCADE
|
|
);
|
|
CREATE INDEX IF NOT EXISTS idx_repo_pages_did ON repo_pages(did);
|