AI Agent Integration Dashboard

-- Main Tables
CREATE TABLE USER (
    user_id VARCHAR(36) PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    name VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_login TIMESTAMP,
    is_active BOOLEAN DEFAULT TRUE
);

CREATE TABLE API_KEY (
    key_id VARCHAR(36) PRIMARY KEY,
    user_id VARCHAR(36) REFERENCES USER(user_id),
    key_name VARCHAR(100) NOT NULL,
    key_value VARCHAR(255) NOT NULL UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    expires_at TIMESTAMP,
    is_active BOOLEAN DEFAULT TRUE,
    scopes JSONB
);

CREATE TABLE PROVIDER (
    provider_id VARCHAR(36) PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    website VARCHAR(255),
    api_docs_url VARCHAR(255),
    is_active BOOLEAN DEFAULT TRUE
);

CREATE TABLE MODEL (
    model_id VARCHAR(36) PRIMARY KEY,
    provider_id VARCHAR(36) REFERENCES PROVIDER(provider_id),
    name VARCHAR(100) NOT NULL,
    description TEXT,
    context_window INTEGER,
    is_multimodal BOOLEAN DEFAULT FALSE,
    is_finetunable BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP
);

CREATE TABLE MODEL_VERSION (
    version_id VARCHAR(36) PRIMARY KEY,
    model_id VARCHAR(36) REFERENCES MODEL(model_id),
    version_name VARCHAR(50) NOT NULL,
    release_date DATE,
    is_current BOOLEAN DEFAULT FALSE,
    deprecated_at TIMESTAMP
);

CREATE TABLE MODEL_CAPABILITY (
    capability_id VARCHAR(36) PRIMARY KEY,
    model_id VARCHAR(36) REFERENCES MODEL(model_id),
    capability_name VARCHAR(100) NOT NULL,
    capability_description TEXT
);

CREATE TABLE MODEL_PARAMETER (
    parameter_id VARCHAR(36) PRIMARY KEY,
    version_id VARCHAR(36) REFERENCES MODEL_VERSION(version_id),
    param_name VARCHAR(100) NOT NULL,
    param_type VARCHAR(50) NOT NULL,
    default_value VARCHAR(100),
    min_value VARCHAR(100),
    max_value VARCHAR(100),
    description TEXT
);

-- Integration Tables
CREATE TABLE INTEGRATION (
    integration_id VARCHAR(36) PRIMARY KEY,
    user_id VARCHAR(36) REFERENCES USER(user_id),
    model_id VARCHAR(36) REFERENCES MODEL(model_id),
    name VARCHAR(100) NOT NULL,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP,
    is_active BOOLEAN DEFAULT TRUE,
    config JSONB
);

CREATE TABLE API_CALL (
    call_id VARCHAR(36) PRIMARY KEY,
    integration_id VARCHAR(36) REFERENCES INTEGRATION(integration_id),
    timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    endpoint VARCHAR(255),
    status_code INTEGER,
    duration_ms INTEGER,
    prompt_tokens INTEGER,
    completion_tokens INTEGER,
    cost NUMERIC(10,6),
    error_message TEXT,
    request_body TEXT,
    response_body TEXT
);

-- Knowledge Base Tables
CREATE TABLE KNOWLEDGE_BASE (
    kb_id VARCHAR(36) PRIMARY KEY,
    user_id VARCHAR(36) REFERENCES USER(user_id),
    name VARCHAR(100) NOT NULL,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP,
    vector_store_type VARCHAR(50),
    index_status VARCHAR(20) DEFAULT 'empty'
);

CREATE TABLE DOCUMENT (
    document_id VARCHAR(36) PRIMARY KEY,
    kb_id VARCHAR(36) REFERENCES KNOWLEDGE_BASE(kb_id),
    name VARCHAR(255) NOT NULL,
    file_path VARCHAR(255),
    file_size INTEGER,
    file_type VARCHAR(50),
    upload_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    processing_status VARCHAR(20) DEFAULT 'pending'
);

CREATE TABLE DOCUMENT_METADATA (
    metadata_id VARCHAR(36) PRIMARY KEY,
    document_id VARCHAR(36) REFERENCES DOCUMENT(document_id),
    metadata_key VARCHAR(100) NOT NULL,
    metadata_value TEXT,
    metadata_type VARCHAR(20)
);

CREATE TABLE CHUNK (
    chunk_id VARCHAR(36) PRIMARY KEY,
    document_id VARCHAR(36) REFERENCES DOCUMENT(document_id),
    chunk_text TEXT NOT NULL,
    chunk_index INTEGER,
    vector_embedding VECTOR(1536),
    metadata JSONB
);

-- Cluster Management Tables
CREATE TABLE CLUSTER_NODE (
    node_id VARCHAR(36) PRIMARY KEY,
    hostname VARCHAR(255) NOT NULL,
    ip_address VARCHAR(50),
    node_type VARCHAR(50),
    gpu_type VARCHAR(50),
    cpu_cores INTEGER,
    memory_gb INTEGER,
    join_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_heartbeat TIMESTAMP
);

CREATE TABLE CLUSTER_STATUS (
    status_id VARCHAR(36) PRIMARY KEY,
    node_id VARCHAR(36) REFERENCES CLUSTER_NODE(node_id),
    timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    cpu_usage NUMERIC(5,2),
    memory_usage NUMERIC(5,2),
    gpu_usage NUMERIC(5,2),
    disk_usage NUMERIC(5,2),
    network_in NUMERIC(10,2),
    network_out NUMERIC(10,2)
);

CREATE TABLE MODEL_DEPLOYMENT (
    deployment_id VARCHAR(36) PRIMARY KEY,
    model_id VARCHAR(36) REFERENCES MODEL(model_id),
    node_id VARCHAR(36) REFERENCES CLUSTER_NODE(node_id),
    version_id VARCHAR(36) REFERENCES MODEL_VERSION(version_id),
    start_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_activity TIMESTAMP,
    status VARCHAR(20) DEFAULT 'running',
    replicas INTEGER DEFAULT 1,
    config JSONB
);

CREATE TABLE CLUSTER_LOG (
    log_id VARCHAR(36) PRIMARY KEY,
    node_id VARCHAR(36) REFERENCES CLUSTER_NODE(node_id),
    deployment_id VARCHAR(36) REFERENCES MODEL_DEPLOYMENT(deployment_id),
    timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    log_level VARCHAR(10),
    message TEXT,
    context JSONB
);