orchard/migrations/001_initial.sql

-- Orchard Database Schema
-- Content-Addressable Storage System

-- Projects (top-level organizational containers)
CREATE TABLE IF NOT EXISTS projects (
    id UUID PRIMARY KEY,
    name VARCHAR(255) UNIQUE NOT NULL,
    description TEXT,
    is_public BOOLEAN DEFAULT true,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    created_by VARCHAR(255) NOT NULL
);

CREATE INDEX idx_projects_name ON projects(name);
CREATE INDEX idx_projects_created_by ON projects(created_by);
CREATE INDEX idx_projects_public ON projects(name) WHERE is_public = true;

-- Packages (collections within projects)
CREATE TABLE IF NOT EXISTS packages (
    id UUID PRIMARY KEY,
    project_id UUID NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    format VARCHAR(50) DEFAULT 'generic',  -- package type: generic, npm, pypi, docker, etc.
    platform VARCHAR(50) DEFAULT 'any',    -- target platform: any, linux, darwin, windows, etc.
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    UNIQUE(project_id, name)
);

CREATE INDEX idx_packages_project_id ON packages(project_id);
CREATE INDEX idx_packages_name ON packages(name);
CREATE INDEX idx_packages_format ON packages(format);
CREATE INDEX idx_packages_platform ON packages(platform);

-- Artifacts (Content-Addressable)
CREATE TABLE IF NOT EXISTS artifacts (
    id VARCHAR(64) PRIMARY KEY,  -- SHA256 hash
    size BIGINT NOT NULL CHECK (size > 0),
    content_type VARCHAR(255),
    original_name VARCHAR(1024),
    checksum_md5 VARCHAR(32),    -- MD5 hash for additional verification
    metadata JSONB,              -- format-specific metadata
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    created_by VARCHAR(255) NOT NULL,
    ref_count INTEGER DEFAULT 1 CHECK (ref_count >= 0),
    s3_key VARCHAR(1024) NOT NULL
);

CREATE INDEX idx_artifacts_created_at ON artifacts(created_at);
CREATE INDEX idx_artifacts_created_by ON artifacts(created_by);
CREATE INDEX idx_artifacts_metadata ON artifacts USING GIN (metadata);

-- Tags (Aliases pointing to artifacts)
CREATE TABLE IF NOT EXISTS tags (
    id UUID PRIMARY KEY,
    package_id UUID NOT NULL REFERENCES packages(id) ON DELETE CASCADE,
    name VARCHAR(255) NOT NULL,
    artifact_id VARCHAR(64) NOT NULL REFERENCES artifacts(id),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    created_by VARCHAR(255) NOT NULL,
    UNIQUE(package_id, name)
);

CREATE INDEX idx_tags_package_id ON tags(package_id);
CREATE INDEX idx_tags_artifact_id ON tags(artifact_id);
CREATE INDEX idx_tags_package_created_at ON tags(package_id, created_at DESC);

-- Tag History (for rollback capability)
CREATE TABLE IF NOT EXISTS tag_history (
    id UUID PRIMARY KEY,
    tag_id UUID NOT NULL REFERENCES tags(id) ON DELETE CASCADE,
    old_artifact_id VARCHAR(64) REFERENCES artifacts(id),
    new_artifact_id VARCHAR(64) NOT NULL REFERENCES artifacts(id),
    change_type VARCHAR(20) NOT NULL DEFAULT 'update' CHECK (change_type IN ('create', 'update', 'delete')),
    changed_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    changed_by VARCHAR(255) NOT NULL
);

CREATE INDEX idx_tag_history_tag_id ON tag_history(tag_id);
CREATE INDEX idx_tag_history_changed_at ON tag_history(changed_at);

-- Uploads (upload event records)
CREATE TABLE IF NOT EXISTS uploads (
    id UUID PRIMARY KEY,
    artifact_id VARCHAR(64) NOT NULL REFERENCES artifacts(id),
    package_id UUID NOT NULL REFERENCES packages(id),
    original_name VARCHAR(1024),
    tag_name VARCHAR(255),              -- tag assigned during upload
    user_agent VARCHAR(512),            -- client identification
    duration_ms INTEGER,                -- upload timing in milliseconds
    deduplicated BOOLEAN DEFAULT false, -- whether artifact was deduplicated
    checksum_verified BOOLEAN DEFAULT true, -- whether checksum was verified
    uploaded_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    uploaded_by VARCHAR(255) NOT NULL,
    source_ip VARCHAR(45)
);

CREATE INDEX idx_uploads_artifact_id ON uploads(artifact_id);
CREATE INDEX idx_uploads_package_id ON uploads(package_id);
CREATE INDEX idx_uploads_uploaded_at ON uploads(uploaded_at);
CREATE INDEX idx_uploads_package_uploaded_at ON uploads(package_id, uploaded_at DESC);
CREATE INDEX idx_uploads_uploaded_by_at ON uploads(uploaded_by, uploaded_at DESC);

-- Consumers (Dependency tracking)
CREATE TABLE IF NOT EXISTS consumers (
    id UUID PRIMARY KEY,
    package_id UUID NOT NULL REFERENCES packages(id) ON DELETE CASCADE,
    project_url VARCHAR(2048) NOT NULL,
    last_access TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    UNIQUE(package_id, project_url)
);

CREATE INDEX idx_consumers_package_id ON consumers(package_id);
CREATE INDEX idx_consumers_last_access ON consumers(last_access);

-- Access Permissions
CREATE TABLE IF NOT EXISTS access_permissions (
    id UUID PRIMARY KEY,
    project_id UUID NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
    user_id VARCHAR(255) NOT NULL,
    level VARCHAR(20) NOT NULL CHECK (level IN ('read', 'write', 'admin')),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    expires_at TIMESTAMP WITH TIME ZONE,
    UNIQUE(project_id, user_id)
);

CREATE INDEX idx_access_permissions_project_id ON access_permissions(project_id);
CREATE INDEX idx_access_permissions_user_id ON access_permissions(user_id);

-- API Keys
CREATE TABLE IF NOT EXISTS api_keys (
    id UUID PRIMARY KEY,
    key_hash VARCHAR(64) NOT NULL UNIQUE,
    name VARCHAR(255) NOT NULL,
    user_id VARCHAR(255) NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    expires_at TIMESTAMP WITH TIME ZONE,
    last_used TIMESTAMP WITH TIME ZONE
);

CREATE INDEX idx_api_keys_user_id ON api_keys(user_id);
CREATE INDEX idx_api_keys_key_hash ON api_keys(key_hash);

-- Audit Logs (Immutable)
CREATE TABLE IF NOT EXISTS audit_logs (
    id UUID PRIMARY KEY,
    action VARCHAR(100) NOT NULL,
    resource VARCHAR(1024) NOT NULL,
    user_id VARCHAR(255) NOT NULL,
    details JSONB,
    timestamp TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    source_ip VARCHAR(45)
);

CREATE INDEX idx_audit_logs_action ON audit_logs(action);
CREATE INDEX idx_audit_logs_resource ON audit_logs(resource);
CREATE INDEX idx_audit_logs_user_id ON audit_logs(user_id);
CREATE INDEX idx_audit_logs_timestamp ON audit_logs(timestamp);
CREATE INDEX idx_audit_logs_resource_timestamp ON audit_logs(resource, timestamp DESC);
CREATE INDEX idx_audit_logs_user_timestamp ON audit_logs(user_id, timestamp DESC);
CREATE INDEX idx_audit_logs_details ON audit_logs USING GIN (details);

-- Trigger to update tag history on changes
CREATE OR REPLACE FUNCTION track_tag_changes()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'UPDATE' AND OLD.artifact_id != NEW.artifact_id THEN
        INSERT INTO tag_history (id, tag_id, old_artifact_id, new_artifact_id, change_type, changed_at, changed_by)
        VALUES (gen_random_uuid(), NEW.id, OLD.artifact_id, NEW.artifact_id, 'update', NOW(), NEW.created_by);
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER tag_changes_trigger
    AFTER UPDATE ON tags
    FOR EACH ROW
    EXECUTE FUNCTION track_tag_changes();

-- Trigger to auto-update updated_at timestamps
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER projects_updated_at_trigger
    BEFORE UPDATE ON projects
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER packages_updated_at_trigger
    BEFORE UPDATE ON packages
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER tags_updated_at_trigger
    BEFORE UPDATE ON tags
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

-- Triggers for maintaining artifact ref_count accuracy
CREATE OR REPLACE FUNCTION increment_artifact_ref_count()
RETURNS TRIGGER AS $$
BEGIN
    UPDATE artifacts SET ref_count = ref_count + 1 WHERE id = NEW.artifact_id;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION decrement_artifact_ref_count()
RETURNS TRIGGER AS $$
BEGIN
    UPDATE artifacts SET ref_count = ref_count - 1 WHERE id = OLD.artifact_id;
    RETURN OLD;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION update_artifact_ref_count()
RETURNS TRIGGER AS $$
BEGIN
    IF OLD.artifact_id != NEW.artifact_id THEN
        UPDATE artifacts SET ref_count = ref_count - 1 WHERE id = OLD.artifact_id;
        UPDATE artifacts SET ref_count = ref_count + 1 WHERE id = NEW.artifact_id;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Note: ref_count triggers on tags table
-- These track how many tags reference each artifact
CREATE TRIGGER tags_ref_count_insert_trigger
    AFTER INSERT ON tags
    FOR EACH ROW
    EXECUTE FUNCTION increment_artifact_ref_count();

CREATE TRIGGER tags_ref_count_delete_trigger
    AFTER DELETE ON tags
    FOR EACH ROW
    EXECUTE FUNCTION decrement_artifact_ref_count();

CREATE TRIGGER tags_ref_count_update_trigger
    AFTER UPDATE ON tags
    FOR EACH ROW
    EXECUTE FUNCTION update_artifact_ref_count();