pg-api 0.2.0

# PG-API Production Deployment Roadmap

## Overview
This roadmap outlines the implementation of a production-ready deployment system for pg-api, including PostgreSQL management, license integration, observability, and automated installation processes, based on patterns from the opensearch-api project.

## Current Status
As of August 2025, the pg-api project has made significant progress:
- Core API functionality is implemented using Rust and Axum
- Rate limiting middleware is partially implemented (`src/rate_limit.rs`)
- Connection limiting middleware is partially implemented (`src/connection_limit.rs`)
- Structured logging with tracing is implemented
- Configuration management via JSON files is working
- Basic observability features are implemented (`src/observability.rs`)

## Phase 1: Setup Infrastructure (Week 1)

### 1.1 Create Python Setup Script
- [x] Create `setup.py` with modular architecture
  - [x] Implement `PostgreSQLAPISetup` class
  - [x] Add interactive and non-interactive modes
  - [x] Include multi-language support (EN/PT)
  - [x] Add comprehensive error handling and logging
  - [x] Implement configuration validation

### 1.2 PostgreSQL Management
- [x] Implement PostgreSQL detection and version checking
  - [x] Detect existing PostgreSQL installations
  - [x] Check current version vs latest stable
  - [x] Create backup functionality before upgrades
- [x] Add PostgreSQL upgrade functionality
  - [x] Implement safe upgrade process with rollback
  - [x] Handle configuration migration
  - [x] Preserve existing databases and users
- [x] Create PostgreSQL installation for new systems
  - [x] Support multiple OS distributions
  - [x] Configure optimal settings for API usage
  - [x] Set up connection pooling parameters

### 1.3 Build System Enhancement
- [x] Create comprehensive Makefile
  - [x] Add `make build` for development builds
  - [x] Add `make release` for production builds
  - [x] Add `make package` to create distribution tarball
  - [x] Add `make test` for running test suite
  - [x] Add `make install` for system installation
- [x] Implement version management
  - [x] Create VERSION file
  - [x] Add Git tag integration
  - [x] Implement semantic versioning

## Phase 2: Simple License Management (v0.1.0 - Week 1-2)

### 2.1 Offline License Key System
- [x] Implement simple license key validation
  - [x] Add `LICENSE_KEY` environment variable check
  - [x] Create HMAC-based license key format
  - [x] Add expiration date encoding in key
  - [x] Validate key on service startup
- [x] Create license key generation script
  - [x] `scripts/generate_license.py` for manual key generation
  - [x] Support different license types (trial, standard, enterprise)
  - [x] Include expiration date in generated keys
  - [x] Generate human-readable license info file

### 2.2 License Enforcement
- [x] Add license check middleware
  - [x] Block API requests if license invalid/expired
  - [x] Return appropriate error messages
  - [x] Add grace period for expired licenses (7 days)
- [x] License status endpoint
  - [x] `GET /v1/license` - Show license status
  - [x] Display type, expiration, and validity
  - [x] No external dependencies required

### 2.3 Installation Integration
- [x] Update setup.py to accept license key
  - [x] `--license-key` parameter
  - [x] Store in .env configuration
  - [x] Validate format during installation
- [ ] Add license documentation
  - [ ] How to generate keys
  - [ ] How to install with license
  - [ ] Troubleshooting guide

## Phase 2.5: Full License Management (v0.2.0 - Future)

### Advanced Features (Postponed to v0.2.0)
- [ ] Online license validation with license-manager service
- [ ] Hardware fingerprinting and IP binding
- [ ] Automatic trial generation
- [ ] License usage tracking and audit logs
- [ ] API client for remote validation
- [ ] Caching and offline fallback
- [ ] Database schema for license management
- [ ] License revocation and renewal APIs
- [ ] Multi-tenant license support

## Phase 3: Observability Integration (Week 2)

### 3.1 OpenSearch API Integration (Optional)
- [x] Add configurable OpenSearch client
  ```rust
  pub struct ObservabilityConfig {
      opensearch_url: Option<String>,  // Optional - can be null
      api_token: String,
      index_prefix: String,
      batch_size: usize,
      flush_interval: Duration,
      enabled: bool,  // Toggle for observability
  }
  ```
- [x] Implement metrics middleware
  - [x] Request/response metrics
  - [x] Database query metrics
  - [x] Connection pool metrics
  - [x] System resource metrics

### 3.2 Structured Logging
- [x] Enhance logging with optional OpenSearch export
  - [x] JSON formatted logs
  - [x] Correlation ID generation
  - [x] Request tracing
  - [x] Error aggregation
  - [x] Local file fallback when OpenSearch unavailable
- [x] Create log shipping configuration
  - [x] Buffer and batch logs
  - [x] Handle connection failures gracefully
  - [x] Implement circuit breaker pattern

### 3.3 Metrics and Tracing
- [x] Implement distributed tracing
  - [x] OpenTelemetry integration
  - [x] Trace context propagation
  - [x] Span creation for database operations
- [x] Create custom metrics
  - [x] Query performance metrics
  - [x] Authentication metrics
  - [x] Rate limit metrics
  - [x] Connection pool utilization

## Phase 4: Production Deployment (Week 2-3)

### 4.1 Service Installation
- [x] Create SystemD service configuration
  - [x] Security hardening (PrivateTmp, NoNewPrivileges)
  - [x] Resource limits (memory, CPU, file descriptors)
  - [x] Automatic restart on failure
  - [x] Journal integration for logging
- [x] Implement health check endpoints
  - [x] `/health` - basic liveness
  - [ ] `/ready` - readiness with dependency checks
  - [ ] `/metrics` - Prometheus-compatible metrics

### 4.2 Web Server Configuration
- [x] Implement generic domain configuration
  - [x] Generate Nginx configuration template
  - [x] Generate Apache configuration template
  - [x] Add WebSocket support for streaming
  - [x] Support custom domain input during setup
- [x] SSL/TLS automation
  - [x] Certbot integration (optional)
  - [x] Self-signed certificate generation
  - [x] Custom certificate support
  - [x] Security headers configuration

### 4.3 Production Installation
- [x] Create flexible production deployment
  ```bash
  sudo python3 setup.py \
    --non-interactive \
    --action install \
    --domain [user_provided_domain] \
    --opensearch-url [optional_opensearch_url] \
    --install-path /opt/prod/pg-api
  ```
- [x] Implement configuration management
  - [x] Environment file generation
  - [x] Secret management (JWT, API tokens)
  - [x] Database connection strings
  - [x] Allow custom installation paths

## Phase 5: API Token and Security (Week 3)

### 5.1 API Token Management
- [x] Create token generation system
  - [x] Generate OpenSearch API tokens (when configured)
  - [x] Store tokens securely
  - [ ] Token rotation mechanism
- [x] Implement token validation
  - [x] Environment-based token storage
  - [x] Token format validation (sk_live_*, sk_test_*)
  - [x] Rate limiting per token

### 5.2 Security Enhancements
- [ ] Implement request signing
  - [ ] HMAC-based request validation
  - [ ] Timestamp validation
  - [ ] Replay attack prevention
- [x] Add audit logging
  - [x] All authentication attempts
  - [x] Configuration changes
  - [x] License operations
  - [x] Critical database operations

## Phase 6: Automation and Testing (Week 3-4)

### 6.1 Automated Testing
- [x] Implement comprehensive test suite
  - [x] Unit tests for all modules
  - [ ] Integration tests with PostgreSQL
  - [ ] Load testing scenarios
  - [ ] Security testing
  - [x] License validation testing
  - [ ] API endpoint testing

### 6.2 Deployment Automation
- [ ] Create Ansible playbooks (optional)
  - [ ] Initial server setup
  - [ ] Application deployment
  - [ ] Configuration updates
  - [ ] Rolling updates
- [ ] Implement backup automation
  - [ ] Scheduled configuration backups
  - [ ] Database backup integration
  - [ ] Backup to S3/cloud storage (configurable)

### 6.3 Monitoring and Alerting
- [ ] Create monitoring dashboards
  - [ ] Grafana dashboard templates
  - [ ] Key performance indicators
  - [ ] Error rate tracking
  - [ ] Resource utilization
- [ ] Set up alerting rules
  - [ ] High error rates
  - [ ] License expiration warnings
  - [ ] Resource exhaustion
  - [ ] Security events

## Implementation Priority

### Critical (Must Have - Week 1)
1. Python setup.py script with generic installation
2. PostgreSQL detection and management
3. Production installation to configurable path (default: /opt/prod/pg-api)
4. SystemD service creation
5. Generic domain configuration support

### High Priority (Should Have - Week 2)
1. License manager integration
2. Optional OpenSearch API integration for logging
3. API token configuration
4. SSL/TLS setup (self-signed or custom)
5. Health check endpoints

### Medium Priority (Nice to Have - Week 3)
1. Distributed tracing
2. Advanced metrics collection
3. Automated backups
4. Monitoring dashboards
5. Automated testing

### Low Priority (Future Enhancement - Week 4+)
1. Multi-region deployment
2. Kubernetes manifests
3. Advanced security features
4. Performance optimization
5. Documentation generation

## Configuration Templates

### 1. Generic Environment Configuration
```bash
# Application Configuration
APP__ADDR=127.0.0.1:8580
APP__LOG_LEVEL=info
APP__DOMAIN=${USER_DOMAIN}  # Set during installation

# PostgreSQL Configuration
PG__MAX_CONNECTIONS=100
PG__POOL_SIZE=25

# OpenSearch Integration (Optional)
OPENSEARCH_API_URL=${OPENSEARCH_URL:-}  # Optional
OPENSEARCH_API_TOKEN=${OPENSEARCH_TOKEN:-}  # Generated if OpenSearch configured
OPENSEARCH_INDEX_PREFIX=pg-api
OPENSEARCH_BATCH_SIZE=100
OPENSEARCH_FLUSH_INTERVAL=5s
OPENSEARCH_ENABLED=${OPENSEARCH_ENABLED:-false}

# License Configuration
LICENSE_MANAGER_URL=${LICENSE_URL:-}  # Required
LICENSE_KEY=${LICENSE_KEY:-}  # To be provided

# Security
JWT_SECRET=[generated]
API_TOKENS=[generated_tokens]
```

### 2. Generic Nginx Configuration Template
```nginx
server {
    listen 80;
    server_name ${DOMAIN};
    return 301 https://$server_name$request_uri;
}

server {
    listen 443 ssl http2;
    server_name ${DOMAIN};
    
    ssl_certificate /etc/ssl/certs/${DOMAIN}.pem;
    ssl_certificate_key /etc/ssl/private/${DOMAIN}.key;
    
    location / {
        proxy_pass http://127.0.0.1:${APP_PORT:-8580};
        proxy_set_header Host $host;
        proxy_set_header X-Real-IP $remote_addr;
        proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;
        proxy_set_header X-Forwarded-Proto $scheme;
        
        # WebSocket support
        proxy_http_version 1.1;
        proxy_set_header Upgrade $http_upgrade;
        proxy_set_header Connection "upgrade";
    }
    
    location /health {
        proxy_pass http://127.0.0.1:${APP_PORT:-8580}/health;
        access_log off;
    }
}
```

### 3. Generic SystemD Service Template
```ini
[Unit]
Description=PostgreSQL API Service
After=network.target postgresql.service
Wants=postgresql.service

[Service]
Type=simple
User=${SERVICE_USER}
Group=${SERVICE_GROUP}
WorkingDirectory=${INSTALL_PATH}
EnvironmentFile=${INSTALL_PATH}/.env
ExecStart=${INSTALL_PATH}/target/release/pg-api
Restart=always
RestartSec=10

# Security
NoNewPrivileges=true
PrivateTmp=true
ProtectSystem=strict
ProtectHome=true
ReadWritePaths=${INSTALL_PATH}/logs

# Resource Limits
LimitNOFILE=65536
MemoryMax=${MEMORY_LIMIT:-2G}
CPUQuota=${CPU_QUOTA:-400%}

# Logging
StandardOutput=journal
StandardError=journal
SyslogIdentifier=pg-api

[Install]
WantedBy=multi-user.target
```

## Setup Script Parameters

```bash
# Interactive mode (prompts for all values)
sudo python3 setup.py

# Non-interactive with minimal config
sudo python3 setup.py \
  --non-interactive \
  --action install

# Full configuration example
sudo python3 setup.py \
  --non-interactive \
  --action install \
  --install-path /opt/prod/pg-api \
  --domain api.example.com \
  --opensearch-url https://opensearch.example.com \
  --opensearch-token sk_live_xxxxx \
  --license-manager-url https://license.example.com \
  --license-key LICENSE_KEY_HERE \
  --ssl-cert /path/to/cert.pem \
  --ssl-key /path/to/key.pem \
  --postgres-host localhost \
  --postgres-port 5432
```

## Testing Deployment (evoluame.com example)

After the generic setup is complete and tested, deployment to evoluame.com infrastructure:

```bash
# Production deployment for evoluame.com
sudo python3 setup.py \
  --non-interactive \
  --action install \
  --install-path /opt/prod/pg-api \
  --domain pg-api.evoluame.com \
  --opensearch-url https://opensearch-api.evoluame.com \
  --license-manager-url https://license-manager.evoluame.com
```

## Success Criteria

- [ ] Generic installation works on Ubuntu/Debian/RHEL/CentOS
- [ ] PostgreSQL detection works across different installations
- [ ] Setup completes without hardcoded domains or paths
- [ ] Optional OpenSearch integration can be enabled/disabled
- [ ] License validation works with configurable license-manager URL
- [ ] Service runs with custom domain configuration
- [ ] All paths and domains are configurable
- [ ] Installation can work offline (except license validation)
- [ ] Backup and restore procedures work generically
- [ ] Security audit passed for default configuration

## Notes

- All domain-specific configurations should be parameters
- Support both online and offline installation modes
- Provide sensible defaults for all optional parameters
- Create example configuration for common deployments
- Document environment variables for runtime configuration
- Support multiple installation instances on same server
- Allow custom port configuration to avoid conflicts
- Performance baseline should be deployment-agnostic