at-container-registry/pkg/appview/db/schema.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,
    default_hold_did TEXT,
    oci_client TEXT DEFAULT '',
    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,
    artifact_type TEXT NOT NULL DEFAULT 'container-image',  -- container-image, helm-chart, unknown
    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 INDEX IF NOT EXISTS idx_manifests_artifact_type ON manifests(artifact_type);

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,
    annotations TEXT,
    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,
    successor 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);

-- Cached hold crew memberships from Jetstream
-- Enables reverse lookup: "which holds is user X a member of?"
CREATE TABLE IF NOT EXISTS hold_crew_members (
    hold_did TEXT NOT NULL,
    member_did TEXT NOT NULL,
    rkey TEXT NOT NULL,
    role TEXT,
    permissions TEXT,  -- JSON array
    tier TEXT,
    added_at TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (hold_did, member_did)
);
CREATE INDEX IF NOT EXISTS idx_hold_crew_member ON hold_crew_members(member_did);
CREATE INDEX IF NOT EXISTS idx_hold_crew_hold ON hold_crew_members(hold_did);
CREATE INDEX IF NOT EXISTS idx_hold_crew_rkey ON hold_crew_members(hold_did, rkey);

CREATE TABLE IF NOT EXISTS repo_pages (
    did TEXT NOT NULL,
    repository TEXT NOT NULL,
    description TEXT,
    avatar_cid TEXT,
    user_edited BOOLEAN NOT NULL DEFAULT 0,
    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);

CREATE TABLE IF NOT EXISTS crypto_keys (
    name TEXT PRIMARY KEY,
    key_data BLOB NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS webhooks (
    id TEXT PRIMARY KEY,
    user_did TEXT NOT NULL,
    url TEXT NOT NULL,
    secret TEXT,
    triggers INTEGER NOT NULL DEFAULT 1,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY(user_did) REFERENCES users(did) ON DELETE CASCADE
);
CREATE INDEX IF NOT EXISTS idx_webhooks_user ON webhooks(user_did);

CREATE TABLE IF NOT EXISTS scans (
    hold_did TEXT NOT NULL,
    manifest_digest TEXT NOT NULL,
    user_did TEXT NOT NULL,
    repository TEXT NOT NULL,
    critical INTEGER NOT NULL DEFAULT 0,
    high INTEGER NOT NULL DEFAULT 0,
    medium INTEGER NOT NULL DEFAULT 0,
    low INTEGER NOT NULL DEFAULT 0,
    total INTEGER NOT NULL DEFAULT 0,
    scanner_version TEXT,
    scanned_at TIMESTAMP NOT NULL,
    PRIMARY KEY(hold_did, manifest_digest)
);
CREATE INDEX IF NOT EXISTS idx_scans_user ON scans(user_did);