config.yaml

# SQL Proxy Configuration
# Environment variables can be used with ${VAR_NAME} syntax
# All fields are required - no defaults are assumed

server:
  host: "127.0.0.1"        # Listen address (use 0.0.0.0 for all interfaces)
  port: 8081               # HTTP port
  default_timeout_sec: 30  # Default query timeout (seconds)
  max_timeout_sec: 300     # Maximum allowed timeout (caps _timeout param)
  # api_version: "1.0.0"   # API version shown in OpenAPI spec (version your query config changes)
  # Cache configuration (optional)
  # cache:
  #   enabled: true
  #   max_size_mb: 256       # Total cache memory limit (default: 256)
  #   default_ttl_sec: 300   # Default TTL if not specified per-workflow (default: 300)

# Database connections - type-specific fields required
databases:
  - name: "primary"                # Connection name (referenced by workflows)
    type: "sqlserver"              # Database type: sqlserver or sqlite (required)
    host: "${DB_HOST}"             # SQL Server host
    port: 1433                     # SQL Server port (typically 1433)
    user: "${DB_USER}"             # Database user
    password: "${DB_PASSWORD}"     # Database password
    database: "${DB_NAME}"         # Database name
    readonly: true                 # Defaults to true if omitted; sets ApplicationIntent=ReadOnly
    # Session defaults (optional - these are the implicit defaults for readonly: true)
    # isolation: "read_uncommitted"   # read_uncommitted, read_committed, repeatable_read, serializable, snapshot
    # lock_timeout_ms: 5000           # Lock wait timeout in milliseconds
    # deadlock_priority: "low"        # low, normal, high
    # Connection pool settings (optional - sensible defaults apply)
    # max_open_conns: 10             # Max open connections (default: 5)
    # max_idle_conns: 5              # Max idle connections (default: 2)
    # conn_max_lifetime: 300         # Max connection lifetime in seconds (default: 300)
    # conn_max_idle_time: 120        # Max idle time before closing in seconds (default: 120)

  # Example: SQLite database (useful for testing)
  # - name: "test_db"
  #   type: "sqlite"
  #   path: ":memory:"              # File path or :memory: for in-memory database
  #   # SQLite session settings (optional)
  #   # journal_mode: "wal"         # wal, delete, truncate, memory, off (default: wal)
  #   # busy_timeout_ms: 5000       # Busy timeout in milliseconds (default: 5000)
  #   # Connection pool settings (optional - applies to all database types)
  #   # max_open_conns: 5
  #   # max_idle_conns: 2
  #   # conn_max_lifetime: 300
  #   # conn_max_idle_time: 120

# Logging Configuration
logging:
  level: "info"                              # debug, info, warn, error
  # file_path: empty string = stdout, non-empty = file with rotation
  # Platform-specific paths for service mode:
  # Windows: C:/Services/SQLProxy/logs/sql-proxy.log
  # Linux:   /var/log/sql-proxy/sql-proxy.log
  # macOS:   /usr/local/var/log/sql-proxy/sql-proxy.log
  file_path: "./logs/sql-proxy.log"          # Empty string for stdout
  max_size_mb: 100                           # Rotate at this size
  max_backups: 5                             # Old files to keep
  max_age_days: 30                           # Delete after days

# Metrics Configuration
metrics:
  enabled: true

# Debug Configuration (pprof endpoints for profiling)
# Disabled by default for security. Enable only when debugging.
# debug:
#   enabled: true      # Enable /_/debug/pprof/* endpoints
#   port: 6060         # Separate port (0 = same as main server)
#   host: "localhost"  # Only valid with separate port; defaults to localhost

# Rate Limit Pools (optional)
# Named rate limit pools that can be referenced by workflows
# rate_limits:
#   - name: "default"
#     requests_per_second: 100
#     burst: 200
#     key: "{{.trigger.client_ip}}"
#   - name: "strict"
#     requests_per_second: 10
#     burst: 20
#     key: "{{.trigger.client_ip}}"

# ============================================================================
# WORKFLOWS
# ============================================================================
# Workflows define query pipelines with triggers, steps, and conditional execution.
#
# A workflow consists of:
# - triggers: How the workflow is initiated (HTTP request or cron schedule)
# - steps: Sequential execution of query, httpcall, or response steps
# - conditions: Named expressions for conditional step execution
#
# Workflows support:
# - HTTP and cron triggers
# - Query steps for database operations
# - HTTPCall steps for external API calls
# - Response steps with templated output
# - Iteration over results with blocks
# - Conditional execution based on results
# - Caching and rate limiting per trigger
#
# HTTP Methods: GET, POST, PUT, DELETE, PATCH, HEAD, OPTIONS
# Note: Same path with different methods is supported (RESTful pattern)

workflows:
  # ============================================================================
  # BASIC HTTP WORKFLOW
  # ============================================================================
  # Simple workflow exposing a query as an HTTP endpoint

  # - name: "list_machines"
  #   triggers:
  #     - type: http
  #       path: "/api/machines"
  #       method: GET
  #   steps:
  #     - name: fetch
  #       type: query
  #       database: "primary"
  #       sql: |
  #         SELECT MachineId, MachineName, MachineIP, LastPingTime, IsOnline
  #         FROM Machines ORDER BY MachineName
  #     - type: response
  #       template: |
  #         {"success": true, "count": {{.steps.fetch.count}}, "data": {{json .steps.fetch.data}}}

  # ============================================================================
  # WORKFLOW WITH PARAMETERS
  # ============================================================================

  # - name: "get_machine"
  #   triggers:
  #     - type: http
  #       path: "/api/machines/details"
  #       method: GET
  #       parameters:
  #         - name: "machineId"
  #           type: "int"
  #           required: true
  #   steps:
  #     - name: fetch
  #       type: query
  #       database: "primary"
  #       sql: |
  #         SELECT * FROM Machines WHERE MachineId = @machineId
  #     - type: response
  #       condition: "steps.fetch.count > 0"
  #       template: |
  #         {"success": true, "data": {{json (index .steps.fetch.data 0)}}}
  #     - type: response
  #       condition: "steps.fetch.count == 0"
  #       status_code: 404
  #       template: |
  #         {"success": false, "error": "Machine not found"}

  # ============================================================================
  # SCHEDULED WORKFLOW (CRON)
  # ============================================================================

  # - name: "machine_status_summary"
  #   triggers:
  #     - type: cron
  #       schedule: "*/5 * * * *"    # Every 5 minutes
  #   steps:
  #     - name: fetch
  #       type: query
  #       database: "primary"
  #       sql: |
  #         SELECT
  #           COUNT(*) AS TotalMachines,
  #           SUM(CASE WHEN IsOnline = 1 THEN 1 ELSE 0 END) AS OnlineMachines,
  #           SUM(CASE WHEN IsOnline = 0 THEN 1 ELSE 0 END) AS OfflineMachines
  #         FROM Machines

  # ============================================================================
  # WORKFLOW WITH HTTPCALL (WEBHOOK)
  # ============================================================================

  # - name: "alert_offline_machines"
  #   triggers:
  #     - type: cron
  #       schedule: "*/15 * * * *"   # Every 15 minutes
  #   conditions:
  #     has_offline: "steps.fetch.count > 0"
  #   steps:
  #     - name: fetch
  #       type: query
  #       database: "primary"
  #       sql: |
  #         SELECT MachineId, MachineName, LastPingTime
  #         FROM Machines WHERE IsOnline = 0
  #
  #     - name: notify
  #       type: httpcall
  #       condition: "has_offline"
  #       url: "https://hooks.slack.com/services/XXX/YYY/ZZZ"
  #       http_method: POST
  #       headers:
  #         Content-Type: "application/json"
  #       body: |
  #         {"text": "Offline machines: {{.steps.fetch.count}}", "data": {{json .steps.fetch.data}}}

  # ============================================================================
  # WORKFLOW WITH CACHING
  # ============================================================================

  # - name: "get_dashboard_stats"
  #   triggers:
  #     - type: http
  #       path: "/api/dashboard"
  #       method: GET
  #       parameters:
  #         - name: "period"
  #           type: "string"
  #           default: "day"
  #       cache:
  #         enabled: true
  #         key: "dashboard:{{.period}}"
  #         ttl_sec: 60
  #   steps:
  #     - name: stats
  #       type: query
  #       database: "primary"
  #       sql: |
  #         SELECT COUNT(*) AS total, SUM(CASE WHEN IsOnline = 1 THEN 1 ELSE 0 END) AS online
  #         FROM Machines
  #     - type: response
  #       template: |
  #         {"success": true, "stats": {{json (index .steps.stats.data 0)}}}

  # ============================================================================
  # WORKFLOW WITH RATE LIMITING
  # ============================================================================

  # - name: "expensive_report"
  #   triggers:
  #     - type: http
  #       path: "/api/reports/full"
  #       method: GET
  #       rate_limit:
  #         - pool: "strict"           # Reference named pool
  #         - requests_per_second: 1   # Or inline rate limit
  #           burst: 5
  #           key: "{{.trigger.client_ip}}"
  #   steps:
  #     - name: fetch
  #       type: query
  #       database: "primary"
  #       sql: "SELECT * FROM LargeTable"
  #     - type: response
  #       template: |
  #         {"success": true, "data": {{json .steps.fetch.data}}}

  # ============================================================================
  # WORKFLOW WITH ITERATION (BLOCK)
  # ============================================================================

  # - name: "process_pending_orders"
  #   triggers:
  #     - type: cron
  #       schedule: "*/5 * * * *"
  #   conditions:
  #     has_orders: "steps.fetch.count > 0"
  #   steps:
  #     - name: fetch
  #       type: query
  #       database: "primary"
  #       sql: "SELECT * FROM Orders WHERE Status = 'pending' ORDER BY CreatedAt"
  #
  #     - name: process_each
  #       condition: "has_orders"
  #       iterate:
  #         over: "steps.fetch.data"
  #         as: "order"
  #         on_error: continue      # Continue on error, abort, or skip
  #       steps:
  #         - name: call_api
  #           type: httpcall
  #           url: "https://api.example.com/process/{{.order.OrderId}}"
  #           http_method: POST
  #           body: |
  #             {"amount": {{.order.Amount}}}

  # ============================================================================
  # PARAMETER TYPES
  # ============================================================================
  # Supported: string, int, integer, float, double, bool, boolean,
  #            datetime, date, json, int[], string[], float[], bool[]

  # ============================================================================
  # TEMPLATE REFERENCE
  # ============================================================================
  # Available in templates:
  #   .steps.<name>.data      - Query results (array of maps)
  #   .steps.<name>.count     - Row count
  #   .steps.<name>.success   - Boolean success status
  #   .trigger.params.<name>  - Parameter values
  #   .trigger.headers.<name> - HTTP headers
  #   .trigger.client_ip      - Client IP address
  #   .workflow.request_id    - Request ID
  #   .workflow.name          - Workflow name
  #
  # Template functions: json, len, index, add, mod, default, eq, ne, lt, gt