agent-governance/ledger/migrations/002_marketplace.sql

-- Agent Marketplace Schema Migration
-- Phase 11: Agent Marketplace

-- ============================================================
-- AGENT TEMPLATES
-- ============================================================

-- Core template registry
CREATE TABLE IF NOT EXISTS agent_templates (
    template_id TEXT PRIMARY KEY,
    name TEXT NOT NULL,
    slug TEXT NOT NULL,
    description TEXT,
    author_id TEXT NOT NULL,  -- tenant_id of publisher
    author_name TEXT,
    category TEXT DEFAULT 'general',  -- general, automation, monitoring, security, devops, data
    tier_required INTEGER DEFAULT 0,  -- Minimum tier to use this template
    is_public INTEGER DEFAULT 0,  -- 1 = visible in public marketplace
    is_verified INTEGER DEFAULT 0,  -- 1 = reviewed and approved
    is_official INTEGER DEFAULT 0,  -- 1 = created by platform team
    license TEXT DEFAULT 'MIT',
    repository_url TEXT,
    documentation_url TEXT,
    icon_url TEXT,
    tags TEXT,  -- JSON array of tags
    capabilities TEXT,  -- JSON array of capabilities
    requirements TEXT,  -- JSON: required tools, permissions, etc.
    created_at TEXT DEFAULT CURRENT_TIMESTAMP,
    updated_at TEXT DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(author_id, slug)
);

-- Template versions
CREATE TABLE IF NOT EXISTS template_versions (
    version_id TEXT PRIMARY KEY,
    template_id TEXT NOT NULL,
    version TEXT NOT NULL,  -- Semantic version: 1.0.0
    version_number INTEGER NOT NULL,  -- Numeric for sorting: 10000
    release_notes TEXT,
    config_schema TEXT,  -- JSON Schema for configuration
    template_content TEXT NOT NULL,  -- JSON: full template definition
    file_hash TEXT,  -- SHA256 of template content
    min_platform_version TEXT,
    is_stable INTEGER DEFAULT 1,
    is_deprecated INTEGER DEFAULT 0,
    published_at TEXT DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (template_id) REFERENCES agent_templates(template_id) ON DELETE CASCADE,
    UNIQUE(template_id, version)
);

-- Template dependencies
CREATE TABLE IF NOT EXISTS template_dependencies (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    template_id TEXT NOT NULL,
    version_id TEXT NOT NULL,
    depends_on_template TEXT NOT NULL,  -- template_id of dependency
    version_constraint TEXT,  -- Semver constraint: >=1.0.0, ^2.0.0
    is_optional INTEGER DEFAULT 0,
    FOREIGN KEY (template_id) REFERENCES agent_templates(template_id) ON DELETE CASCADE,
    FOREIGN KEY (version_id) REFERENCES template_versions(version_id) ON DELETE CASCADE
);

-- ============================================================
-- MARKETPLACE INTERACTIONS
-- ============================================================

-- Template installations (per tenant/project)
CREATE TABLE IF NOT EXISTS template_installations (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    template_id TEXT NOT NULL,
    version_id TEXT NOT NULL,
    tenant_id TEXT NOT NULL,
    project_id TEXT NOT NULL,
    installed_by TEXT,
    config_overrides TEXT,  -- JSON: custom configuration
    installed_at TEXT DEFAULT CURRENT_TIMESTAMP,
    updated_at TEXT DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (template_id) REFERENCES agent_templates(template_id) ON DELETE CASCADE,
    FOREIGN KEY (version_id) REFERENCES template_versions(version_id) ON DELETE SET NULL,
    UNIQUE(template_id, tenant_id, project_id)
);

-- Ratings and reviews
CREATE TABLE IF NOT EXISTS template_ratings (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    template_id TEXT NOT NULL,
    tenant_id TEXT NOT NULL,
    user_id TEXT,
    rating INTEGER NOT NULL CHECK(rating >= 1 AND rating <= 5),
    review_title TEXT,
    review_text TEXT,
    is_verified_user INTEGER DEFAULT 0,  -- 1 = user has installed and used
    created_at TEXT DEFAULT CURRENT_TIMESTAMP,
    updated_at TEXT DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (template_id) REFERENCES agent_templates(template_id) ON DELETE CASCADE,
    UNIQUE(template_id, tenant_id)
);

-- Download/usage statistics
CREATE TABLE IF NOT EXISTS template_stats (
    template_id TEXT PRIMARY KEY,
    total_downloads INTEGER DEFAULT 0,
    total_installs INTEGER DEFAULT 0,
    active_installs INTEGER DEFAULT 0,
    avg_rating REAL DEFAULT 0,
    rating_count INTEGER DEFAULT 0,
    weekly_downloads INTEGER DEFAULT 0,
    monthly_downloads INTEGER DEFAULT 0,
    last_download_at TEXT,
    trending_score REAL DEFAULT 0,  -- Calculated popularity score
    updated_at TEXT DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (template_id) REFERENCES agent_templates(template_id) ON DELETE CASCADE
);

-- Download history for analytics
CREATE TABLE IF NOT EXISTS template_downloads (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    template_id TEXT NOT NULL,
    version_id TEXT,
    tenant_id TEXT,
    downloaded_at TEXT DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (template_id) REFERENCES agent_templates(template_id) ON DELETE CASCADE
);

-- ============================================================
-- CATEGORIES AND TAGS
-- ============================================================

-- Predefined categories
CREATE TABLE IF NOT EXISTS marketplace_categories (
    category_id TEXT PRIMARY KEY,
    name TEXT NOT NULL,
    description TEXT,
    icon TEXT,
    sort_order INTEGER DEFAULT 0,
    is_active INTEGER DEFAULT 1
);

-- Insert default categories
INSERT OR IGNORE INTO marketplace_categories (category_id, name, description, sort_order) VALUES
    ('general', 'General Purpose', 'Multi-purpose agents for common tasks', 1),
    ('automation', 'Automation', 'Workflow automation and task scheduling', 2),
    ('monitoring', 'Monitoring', 'System monitoring and alerting', 3),
    ('security', 'Security', 'Security scanning and compliance', 4),
    ('devops', 'DevOps', 'CI/CD, deployment, and infrastructure', 5),
    ('data', 'Data Processing', 'Data transformation and analytics', 6),
    ('integration', 'Integrations', 'Third-party service connectors', 7),
    ('testing', 'Testing', 'Test automation and validation', 8);

-- ============================================================
-- INDEXES
-- ============================================================

CREATE INDEX IF NOT EXISTS idx_templates_author ON agent_templates(author_id);
CREATE INDEX IF NOT EXISTS idx_templates_category ON agent_templates(category);
CREATE INDEX IF NOT EXISTS idx_templates_public ON agent_templates(is_public);
CREATE INDEX IF NOT EXISTS idx_templates_slug ON agent_templates(slug);

CREATE INDEX IF NOT EXISTS idx_versions_template ON template_versions(template_id);
CREATE INDEX IF NOT EXISTS idx_versions_version ON template_versions(version);

CREATE INDEX IF NOT EXISTS idx_installs_template ON template_installations(template_id);
CREATE INDEX IF NOT EXISTS idx_installs_tenant ON template_installations(tenant_id);
CREATE INDEX IF NOT EXISTS idx_installs_project ON template_installations(tenant_id, project_id);

CREATE INDEX IF NOT EXISTS idx_ratings_template ON template_ratings(template_id);
CREATE INDEX IF NOT EXISTS idx_downloads_template ON template_downloads(template_id);
CREATE INDEX IF NOT EXISTS idx_downloads_date ON template_downloads(downloaded_at);

-- Full-text search index (SQLite FTS5)
CREATE VIRTUAL TABLE IF NOT EXISTS template_search USING fts5(
    template_id,
    name,
    description,
    tags,
    category,
    author_name,
    content='agent_templates',
    content_rowid='rowid'
);

-- Triggers to keep FTS index updated
CREATE TRIGGER IF NOT EXISTS template_search_insert AFTER INSERT ON agent_templates BEGIN
    INSERT INTO template_search(template_id, name, description, tags, category, author_name)
    VALUES (NEW.template_id, NEW.name, NEW.description, NEW.tags, NEW.category, NEW.author_name);
END;

CREATE TRIGGER IF NOT EXISTS template_search_update AFTER UPDATE ON agent_templates BEGIN
    DELETE FROM template_search WHERE template_id = OLD.template_id;
    INSERT INTO template_search(template_id, name, description, tags, category, author_name)
    VALUES (NEW.template_id, NEW.name, NEW.description, NEW.tags, NEW.category, NEW.author_name);
END;

CREATE TRIGGER IF NOT EXISTS template_search_delete AFTER DELETE ON agent_templates BEGIN
    DELETE FROM template_search WHERE template_id = OLD.template_id;
END;