Remote API/gRPC Control Interface

[peter7775]

Issue: Remote API/gRPC Control Interface

🌐 Feature Request: Remote Control Interface for All CLI Operations

Problem Statement

Currently, all application functionality is only accessible through direct CLI commands on the local machine. This creates significant limitations for:

• Remote Management: Cannot control the application from remote locations
• Frontend Integration: Web dashboards cannot trigger operations like transformations or benchmarks
• Automation: CI/CD pipelines and orchestration tools cannot interact with the service remotely
• Multi-User Access: Multiple users cannot share and control a centralized instance
• Cloud Deployment: Difficult to manage containerized deployments without shell access
• Monitoring Integration: External monitoring systems cannot trigger operations or retrieve detailed status

Current Limitations

# Current CLI-only approach
ssh server "sql-graph-visualizer transform --config=prod.yml"   # Requires SSH access
ssh server "sql-graph-visualizer benchmark --duration=300s"    # Manual remote execution

Proposed Solution

Implement a comprehensive remote control interface that exposes all CLI functionality through:

1. REST API for web frontends and HTTP clients
2. gRPC API for high-performance service-to-service communication
3. GraphQL API for flexible data queries and operations
4. WebSocket API for real-time operations and streaming

API Design Principles

1. CLI-API Parity

Every CLI command should have a corresponding API endpoint:

# CLI Command
sql-graph-visualizer transform --config=prod.yml --live

# REST API Equivalent
POST /api/v1/transform
{
  "config": "prod.yml",
  "live": true,
  "async": true
}

2. Multiple Interface Support

• REST API: Standard HTTP/JSON for web applications
• gRPC API: High-performance binary protocol for services
• GraphQL: Flexible queries for frontend applications
• WebSocket: Real-time streaming and live updates

3. Async Operations

Long-running operations should support asynchronous execution:

{
  "operation_id": "transform-abc123",
  "status": "running",
  "started_at": "2025-01-06T14:23:05Z",
  "progress": 45.2,
  "eta": "2025-01-06T14:28:30Z"
}

API Specification

1. Transform Operations

REST API

# Start transformation
POST /api/v1/transform
{
  "config_path": "config/production.yml",
  "live_mode": true,
  "performance_monitoring": true,
  "dry_run": false
}

# Get transformation status
GET /api/v1/transform/{operation_id}

# Stop transformation
DELETE /api/v1/transform/{operation_id}

# List active transformations
GET /api/v1/transform

gRPC Service

service TransformService {
  rpc StartTransform(TransformRequest) returns (TransformResponse);
  rpc GetTransformStatus(StatusRequest) returns (TransformStatus);
  rpc StopTransform(StopRequest) returns (StopResponse);
  rpc ListTransforms(ListRequest) returns (TransformList);
  rpc StreamTransformProgress(StatusRequest) returns (stream TransformProgress);
}

message TransformRequest {
  string config_path = 1;
  bool live_mode = 2;
  bool performance_monitoring = 3;
  bool dry_run = 4;
  repeated string tags = 5;
}

2. Server Management

REST API

# Start server
POST /api/v1/server/start
{
  "config_path": "config/server.yml",
  "port": 3000,
  "api_port": 8080,
  "performance": true
}

# Get server status
GET /api/v1/server/status

# Update server configuration
PUT /api/v1/server/config
{
  "live_performance": true,
  "metrics_interval": "5s"
}

# Stop server
POST /api/v1/server/stop

WebSocket API

// Server status streaming
ws://api.example.com/ws/server/status

// Live performance metrics
ws://api.example.com/ws/performance/metrics

// Transformation progress
ws://api.example.com/ws/transform/progress/{operation_id}

3. Configuration Management

REST API

# List configurations
GET /api/v1/config

# Get configuration
GET /api/v1/config/{config_name}

# Create configuration
POST /api/v1/config
{
  "name": "production-mysql",
  "template": "mysql",
  "database": {
    "host": "mysql.prod.com",
    "port": 3306,
    "database": "analytics"
  }
}

# Validate configuration
POST /api/v1/config/{config_name}/validate

# Generate configuration from database
POST /api/v1/config/generate
{
  "connection_string": "mysql://user:pass@host/db",
  "name": "auto-generated-config"
}

GraphQL Schema

type Query {
  configurations: [Configuration!]!
  configuration(name: String!): Configuration
  configurationTemplates: [ConfigTemplate!]!
}

type Mutation {
  createConfiguration(input: CreateConfigInput!): Configuration!
  updateConfiguration(name: String!, input: UpdateConfigInput!): Configuration!
  validateConfiguration(name: String!): ValidationResult!
  generateConfiguration(input: GenerateConfigInput!): Configuration!
}

type Configuration {
  name: String!
  template: String!
  database: DatabaseConfig!
  neo4j: Neo4jConfig!
  transformRules: [TransformRule!]!
  isValid: Boolean!
  lastUpdated: DateTime!
}

4. Performance Operations

REST API

# Start benchmark
POST /api/v1/benchmark
{
  "config_path": "config/load-test.yml",
  "duration": "300s",
  "concurrent_users": 50,
  "target_qps": 1000
}

# Get benchmark results
GET /api/v1/benchmark/{benchmark_id}

# Start monitoring
POST /api/v1/monitor
{
  "config_path": "config/monitoring.yml",
  "interval": "10s",
  "export_format": "prometheus"
}

# Get performance metrics
GET /api/v1/metrics?from=2025-01-06T14:00:00Z&to=2025-01-06T15:00:00Z

# Analyze performance data
POST /api/v1/analyze
{
  "data_source": "prometheus",
  "time_range": "1h",
  "metrics": ["latency", "throughput", "errors"]
}

gRPC Streaming

service PerformanceService {
  rpc StartBenchmark(BenchmarkRequest) returns (BenchmarkResponse);
  rpc StreamBenchmarkProgress(StatusRequest) returns (stream BenchmarkProgress);
  rpc StartMonitoring(MonitoringRequest) returns (MonitoringResponse);
  rpc StreamMetrics(MetricsRequest) returns (stream PerformanceMetrics);
  rpc AnalyzePerformance(AnalysisRequest) returns (AnalysisResponse);
}

5. Utility Operations

REST API

# Check connectivity
POST /api/v1/check
{
  "config_path": "config/production.yml",
  "check_database": true,
  "check_neo4j": true
}

# Export data
POST /api/v1/export
{
  "config_path": "config/export.yml",
  "format": "json",
  "output_path": "/tmp/export.json"
}

# Import data
POST /api/v1/import
{
  "input_path": "/tmp/data.json",
  "format": "json",
  "target": "neo4j"
}

# Get application status
GET /api/v1/status

# Get version information
GET /api/v1/version

Authentication & Authorization

1. Authentication Methods

authentication:
  methods:
    - jwt        # JSON Web Tokens for web apps
    - api_key    # API keys for service-to-service
    - oauth2     # OAuth2 for third-party integrations
    - mtls       # Mutual TLS for gRPC services

2. Authorization Levels

roles:
  viewer:
    permissions:
      - read_config
      - read_status
      - read_metrics
      
  operator:
    permissions:
      - viewer_permissions
      - start_transform
      - start_benchmark
      - manage_server
      
  admin:
    permissions:
      - operator_permissions
      - create_config
      - delete_config
      - manage_users

3. Security Features

• Rate Limiting: Prevent API abuse
• Input Validation: Sanitize all inputs
• Audit Logging: Track all API operations
• IP Whitelisting: Restrict access by IP ranges
• TLS Encryption: All communications encrypted

Implementation Architecture

1. API Gateway Layer

// Unified API gateway handling all protocols
type APIGateway struct {
    restServer    *gin.Engine
    grpcServer    *grpc.Server
    graphqlServer *graphql.Server
    wsServer      *websocket.Server
}

// Route all requests through service layer
func (g *APIGateway) RouteToService(operation Operation) Response {
    return g.serviceLayer.Execute(operation)
}

2. Service Abstraction Layer

// Abstract service interface
type ServiceLayer interface {
    // Transform operations
    StartTransform(ctx context.Context, req *TransformRequest) (*TransformResponse, error)
    GetTransformStatus(ctx context.Context, id string) (*TransformStatus, error)
    StopTransform(ctx context.Context, id string) error
    
    // Configuration operations
    CreateConfig(ctx context.Context, req *CreateConfigRequest) (*Configuration, error)
    ValidateConfig(ctx context.Context, name string) (*ValidationResult, error)
    
    // Performance operations
    StartBenchmark(ctx context.Context, req *BenchmarkRequest) (*BenchmarkResponse, error)
    GetMetrics(ctx context.Context, req *MetricsRequest) (*Metrics, error)
}

3. Operation Management

// Async operation tracking
type OperationManager struct {
    operations map[string]*Operation
    callbacks  map[string]chan OperationUpdate
}

type Operation struct {
    ID          string           `json:"id"`
    Type        string           `json:"type"`
    Status      OperationStatus  `json:"status"`
    Progress    float64          `json:"progress"`
    StartedAt   time.Time        `json:"started_at"`
    CompletedAt *time.Time       `json:"completed_at,omitempty"`
    Result      interface{}      `json:"result,omitempty"`
    Error       string           `json:"error,omitempty"`
}

Usage Examples

1. Frontend Dashboard

// React/Vue frontend controlling the application
const client = new SQLGraphVisualizerClient('https://api.example.com');

// Start transformation
const transform = await client.transform.start({
  config: 'production.yml',
  live: true,
  performance: true
});

// Monitor progress
client.transform.onProgress(transform.id, (progress) => {
  setProgress(progress.percentage);
});

// Get live performance metrics
const metricsStream = client.performance.streamMetrics();
metricsStream.on('data', (metrics) => {
  updatePerformanceCharts(metrics);
});

2. DevOps Automation

# Shell script automation for CI/CD
#!/bin/bash

API_URL="https://api.prod.com"
API_KEY="xxx"

# Validate configuration via API
validation=$(curl -s -H "Authorization: Bearer $API_KEY" \
  "$API_URL/api/v1/config/production.yml/validate")

if [[ $(echo $validation | jq -r '.is_valid') != "true" ]]; then
  echo "Config validation failed: $(echo $validation | jq -r '.errors')"
  exit 1
fi

# Start transformation via API
transform_response=$(curl -s -X POST \
  -H "Authorization: Bearer $API_KEY" \
  -H "Content-Type: application/json" \
  -d '{"config_path": "production.yml", "live": true, "async": true}' \
  "$API_URL/api/v1/transform")

operation_id=$(echo $transform_response | jq -r '.operation_id')
echo "Started transformation: $operation_id"

# Wait for completion
while true; do
  status=$(curl -s -H "Authorization: Bearer $API_KEY" \
    "$API_URL/api/v1/transform/$operation_id" | jq -r '.status')
  
  if [[ "$status" == "completed" ]]; then
    echo "Transformation completed successfully"
    break
  elif [[ "$status" == "failed" ]]; then
    echo "Transformation failed"
    exit 1
  fi
  
  sleep 10
done

3. Go Client Library

// Go client library for other Go applications
package main

import (
    "context"
    "log"
    "time"
    
    "github.com/your-org/sql-graph-visualizer-client"
)

func main() {
    // Create authenticated client
    client := sqlgraph.NewClient("https://api.prod.com", 
        sqlgraph.WithAPIKey("xxx"))
    
    ctx := context.Background()
    
    // Validate configuration
    validation, err := client.Config.Validate(ctx, "production.yml")
    if err != nil {
        log.Fatal(err)
    }
    if !validation.IsValid {
        log.Fatalf("Config invalid: %v", validation.Errors)
    }
    
    // Start transformation
    transform, err := client.Transform.Start(ctx, &sqlgraph.TransformRequest{
        ConfigPath: "production.yml",
        LiveMode:   true,
        Async:      true,
    })
    if err != nil {
        log.Fatal(err)
    }
    
    log.Printf("Started transformation: %s", transform.OperationID)
    
    // Wait for completion with timeout
    result, err := client.Transform.WaitForCompletion(ctx, 
        transform.OperationID, 5*time.Minute)
    if err != nil {
        log.Fatal(err)
    }
    
    log.Printf("Transformation completed: %+v", result.Stats)
}

4. Monitoring Integration

# Prometheus monitoring integration
- job_name: 'sql-graph-visualizer'
  metrics_path: '/api/v1/metrics/prometheus'
  static_configs:
    - targets: ['app.example.com:8080']

# Grafana dashboard queries
- query: 'rate(sql_graph_transform_duration_seconds[5m])'
- query: 'sql_graph_active_connections'
- query: 'sql_graph_benchmark_qps'

4. Kubernetes Integration

# Kubernetes operator controlling via API
apiVersion: v1
kind: ConfigMap
metadata:
  name: sql-graph-transform
data:
  config.yml: |
    database:
      type: postgresql
      host: postgres-service

---
apiVersion: batch/v1
kind: Job  
metadata:
  name: sql-graph-transform-job
spec:
  template:
    spec:
      containers:
      - name: trigger
        image: curlimages/curl
        command:
        - curl
        - -X POST
        - -H "Content-Type: application/json"
        - -d '{"config_path": "/config/config.yml"}'
        - http://sql-graph-api:8080/api/v1/transform

Migration Strategy

Phase 1: Core REST API (Week 1-2)

• Implement basic REST API for transform and server operations
• Add authentication and authorization
• Create operation management system
• Add async operation support

Phase 2: Performance & Config APIs (Week 3)

• Implement benchmark and monitoring APIs
• Add configuration management endpoints
• Create utility operation APIs
• Add comprehensive error handling

Phase 3: gRPC & GraphQL (Week 4)

• Implement gRPC service definitions
• Add GraphQL schema and resolvers
• Create Go client library and CLI wrapper
• Create client libraries for other popular languages (optional)
• Add streaming support for long operations

Phase 4: WebSocket & Advanced Features (Week 5)

• Implement WebSocket APIs for real-time updates
• Add comprehensive monitoring and metrics
• Create web-based admin dashboard
• Add advanced security features

Benefits

1. 🌐 Remote Management: Control application from anywhere
2. 🔧 Frontend Integration: Build rich web interfaces
3. 🤖 Automation Friendly: Perfect for CI/CD and orchestration
4. 👥 Multi-User Support: Share centralized instances
5. ☁️ Cloud Native: Ideal for containerized deployments
6. 📊 Monitoring Integration: Easy integration with monitoring stack
7. 🔌 Extensibility: Easy to add new functionality
8. 📱 Mobile Apps: Potential for mobile management apps

Security Considerations

• Input Validation: All API inputs validated and sanitized
• Rate Limiting: Prevent abuse and DoS attacks
• Audit Trail: Complete logging of all operations
• Network Security: TLS encryption for all communications
• Access Control: Fine-grained permissions and roles
• Secret Management: Secure handling of database credentials

Monitoring & Observability

metrics:
  api_requests_total: "Counter of API requests by endpoint and status"
  api_request_duration: "Histogram of API request durations"  
  active_operations: "Gauge of currently running operations"
  operation_duration: "Histogram of operation completion times"
  authentication_failures: "Counter of failed authentication attempts"

traces:
  - name: "API Request Trace"
    spans: ["authentication", "validation", "service_call", "response"]
  - name: "Operation Execution Trace" 
    spans: ["operation_start", "database_connect", "transform", "result"]

logs:
  - level: "info"
    message: "API request started"
    fields: ["endpoint", "user_id", "request_id"]
  - level: "error"
    message: "Operation failed"
    fields: ["operation_id", "error_type", "error_message"]

Success Metrics

• ✅ Reduced SSH usage for remote operations
• ✅ Increased automation adoption in CI/CD pipelines
• ✅ Improved user satisfaction with web interfaces
• ✅ Better observability through monitoring integration
• ✅ Faster deployment cycles with API-driven automation

Related Issues

• CLI Commands Unification (provides foundation for API parity)
• Performance Graph Snapshot System (enhanced with API access)
• Authentication and authorization system

---

Priority: High
Complexity: High
Estimated Effort: 4-5 weeks
Dependencies: CLI Commands Unification issue

Implementation Checklist

• Design unified API specification (OpenAPI 3.0)
• Implement core REST API with authentication
• Create async operation management system
• Add gRPC service definitions and implementation
• Implement GraphQL schema and resolvers
• Create WebSocket APIs for real-time features
• Build client libraries for popular languages
• Add comprehensive API documentation
• Implement monitoring and observability
• Create automated API testing suite
• Build web-based admin dashboard
• Add Kubernetes operator support