feat: API exercise test validates response structure on all 3 backends

[Yeraze]

Summary

Upgrades the API exercise test from status-code-only checks to JSON response body validation, and integrates it into the system test suite running against all three database backends.

This would have caught #2419 (backup history wrong column names) — the endpoint returned HTTP 200 but with broken/empty response data.

Changes

api-exercise-test.sh — Enhanced ~40 endpoints with check_json() validation:

• Nodes: validates array/object structure, required keys (connected, nodeCount, etc.)
• Messages: validates messages, hasMore, success keys
• Packets: validates packets array, total, byDevice, byType
• Security: validates total, nodes, running, events, count keys
• Backups: validates list type (catches column name mismatches)
• Audit: validates logs, total keys
• V1 API: validates success, data response envelope on all endpoints

test-api-exercise-all-backends.sh — New wrapper script:

• Runs API exercise test sequentially on SQLite, PostgreSQL, MySQL
• Handles per-backend startup/teardown
• MySQL gets extra 30s init delay for database initialization
• Reports per-backend pass/fail

system-tests.sh — Integrated as "API Exercise (3 DBs)" test suite

Test Results

• SQLite: 135/135 passed
• PostgreSQL: 135/135 passed
• MySQL: 135/135 passed

Files

File	Change
tests/api-exercise-test.sh	check_json(), check_v1() helpers, ~40 endpoints upgraded
tests/test-api-exercise-all-backends.sh	New wrapper for 3-backend testing
tests/system-tests.sh	Added API Exercise test to suite

🤖 Generated with Claude Code

[github-actions]

Claude finished @Yeraze's task —— View job

---

PR Review: API Exercise Test Response Validation

This PR significantly enhances API testing by moving from status-code-only validation to comprehensive JSON response structure validation across all three database backends. Here's my detailed review:

✅ Strengths

Excellent Test Coverage Enhancement

• 40+ endpoints now validate JSON structure instead of just HTTP status codes
• Multi-backend consistency testing - runs against SQLite, PostgreSQL, and MySQL
• Would have caught real bugs like [BUG] in View Saved Backups: SqliteError: no such column: backupPath #2419 (backup history wrong column names)
• Comprehensive V1 API coverage with proper Bearer token authentication

Robust Implementation

• Smart error handling in check_json() with Python JSON validation
• Proper authentication flow including CSRF token handling and session management
• Fallback password attempt (changeme → changeme1) handles different deployments
• Clean separation of concerns between status checks and JSON validation

Good Integration

• Seamlessly integrated into existing system test suite
• Docker compose profile management properly isolates backend testing
• Proper cleanup of test containers and temporary files

⚠️ Areas for Improvement

Bash Script Robustness (tests/api-exercise-test.sh:165-173)

valid=$(echo "$body" | python3 -c "
import sys, json
try:
    data = json.load(sys.stdin)
    result = bool(${validation})
    print('OK' if result else 'FAIL')
except Exception as e:
    print(f'ERR:{e}')
" 2>/dev/null || echo "ERR:python failed")

Issues:

• Code injection risk: The ${validation} parameter is directly interpolated into Python code without escaping
• Silent failures: 2>/dev/null could mask Python syntax errors
• Fragile error handling: The fallback || echo "ERR:python failed" might not catch all failure modes

Recommendation:

# Pass validation as argument to avoid injection
valid=$(echo "$body" | python3 -c "
import sys, json
validation_expr = sys.argv[1] if len(sys.argv) > 1 else 'True'
try:
    data = json.load(sys.stdin)
    result = bool(eval(validation_expr))
    print('OK' if result else 'FAIL')
except Exception as e:
    print(f'ERR:{e}')
" "$validation" || echo "ERR:python failed")

Race Conditions in Backend Startup (tests/test-api-exercise-all-backends.sh:77-94)

for i in $(seq 1 90); do
    if docker compose -f "$COMPOSE_FILE" ps "$CONTAINER" 2>/dev/null | grep -q "Up"; then
        HTTP_STATUS=$(curl -s -o /dev/null -w "%{http_code}" "${BASE_URL}/api/health" 2>/dev/null || echo "000")
        if [ "$HTTP_STATUS" = "200" ] || [ "$HTTP_STATUS" = "404" ]; then
            HTTP_STATUS=$(curl -s -o /dev/null -w "%{http_code}" "${BASE_URL}/api/csrf-token" 2>/dev/null || echo "000")

Issues:

• No curl timeout: Long-hanging requests could cause false positives
• MySQL timing: 30s hardcoded delay may not be sufficient for all environments
• Container state vs API readiness: Container being "Up" doesn't guarantee API readiness

Recommendations:

• Add curl timeouts: curl --max-time 5
• Make MySQL delay configurable: MYSQL_INIT_DELAY=${MYSQL_INIT_DELAY:-30}
• Add database-specific health checks before API testing

Missing Validation Edge Cases

Some validation expressions could be more robust:

# Current: 
"isinstance(data, list)"

# Better:
"isinstance(data, list) and all('id' in item for item in data[:5])"

Resource Management (tests/test-api-exercise-all-backends.sh:44-49)

The cleanup function properly handles most resources but could benefit from:

• Logging cleanup actions for debugging failed teardowns
• Force cleanup option for stuck containers
• Volume pruning to prevent disk space issues over time

🔒 Security Assessment

Low Risk Issues:

• API token cleanup: Properly handled in both success and failure paths
• Credential handling: Uses environment variables appropriately
• Test isolation: Each backend test runs in isolation

Medium Risk Issue:

• Python code injection in check_json() function as mentioned above

⚡ Performance Considerations

Good:

• Sequential backend testing prevents resource conflicts
• Proper container lifecycle management
• Efficient JSON validation using Python

Could Improve:

• Parallel endpoint testing within each backend (currently sequential)
• Configurable test timeouts for different environments
• Smart skipping of similar endpoint patterns

📊 Test Coverage Assessment

Excellent improvement:

• From ~40 status-only checks to ~135 comprehensive validations
• Cross-database validation ensures consistency
• Real-world usage patterns mimic actual UI interactions

Missing coverage:

• Error response validation (only tests happy paths)
• Edge cases like empty datasets, malformed requests
• Performance regression detection

🎯 Final Recommendation

APPROVE with suggested improvements. This PR significantly enhances test quality and would have prevented real production issues. The bash script injection issue should be addressed, but it doesn't block the core value proposition.

Priority fixes:

1. Fix Python code injection in check_json()
2. Add curl timeouts to prevent hanging
3. Improve error logging for easier debugging

Nice-to-have:

1. More robust validation expressions
2. Configurable timeouts and delays
3. Enhanced cleanup logging

The multi-backend approach is particularly valuable for catching database-specific issues early.

---

[github-actions]

Claude finished @Yeraze's task —— View job

---

PR Review: API Exercise Test Response Validation

This PR significantly enhances API testing by moving from status-code-only validation to comprehensive JSON response structure validation across all three database backends. Here's my detailed review:

Todo List:

• Read and analyze changed files
• Assess code quality and best practices
• Identify potential bugs or security issues
• Evaluate performance considerations
• Review test coverage improvements
• Provide comprehensive feedback

---

✅ Strengths

Excellent Test Coverage Enhancement

• 40+ endpoints now validate JSON structure instead of just HTTP status codes
• Multi-backend consistency testing - runs against SQLite, PostgreSQL, and MySQL
• Would have caught real bugs like [BUG] in View Saved Backups: SqliteError: no such column: backupPath #2419 (backup history wrong column names)
• Comprehensive V1 API coverage with proper Bearer token authentication

Robust Implementation

• Smart error handling in check_json() with Python JSON validation (tests/api-exercise-test.sh:165-173)
• Proper authentication flow including CSRF token handling and session management
• Fallback password attempt (changeme → changeme1) handles different deployments
• Clean separation of concerns between status checks and JSON validation

Good Integration

• Seamlessly integrated into existing system test suite (tests/system-tests.sh)
• Docker compose profile management properly isolates backend testing
• Proper cleanup of test containers and temporary files

---

⚠️ Areas for Improvement

Critical: Code Injection Vulnerability (tests/api-exercise-test.sh:165-173)

valid=$(echo "$body" | python3 -c "
import sys, json
try:
    data = json.load(sys.stdin)
    result = bool(${validation})  # ← UNSAFE: Direct interpolation
    print('OK' if result else 'FAIL')
except Exception as e:
    print(f'ERR:{e}')
" 2>/dev/null || echo "ERR:python failed")

Issues:

• Code injection risk: The ${validation} parameter is directly interpolated into Python code without escaping
• Silent failures: 2>/dev/null could mask Python syntax errors
• Fragile error handling: The fallback || echo "ERR:python failed" might not catch all failure modes

Recommendation:

# Pass validation as argument to avoid injection
valid=$(echo "$body" | python3 -c "
import sys, json
validation_expr = sys.argv[1] if len(sys.argv) > 1 else 'True'
try:
    data = json.load(sys.stdin)
    result = bool(eval(validation_expr))
    print('OK' if result else 'FAIL')
except Exception as e:
    print(f'ERR:{e}')
" "$validation" || echo "ERR:python failed")

Race Conditions in Backend Startup (tests/test-api-exercise-all-backends.sh:77-94)

for i in $(seq 1 90); do
    if docker compose -f "$COMPOSE_FILE" ps "$CONTAINER" 2>/dev/null | grep -q "Up"; then
        HTTP_STATUS=$(curl -s -o /dev/null -w "%{http_code}" "${BASE_URL}/api/health" 2>/dev/null || echo "000")
        if [ "$HTTP_STATUS" = "200" ] || [ "$HTTP_STATUS" = "404" ]; then
            HTTP_STATUS=$(curl -s -o /dev/null -w "%{http_code}" "${BASE_URL}/api/csrf-token" 2>/dev/null || echo "000")

Issues:

• No curl timeout: Long-hanging requests could cause false positives
• MySQL timing: 30s hardcoded delay may not be sufficient for all environments
• Container state vs API readiness: Container being "Up" doesn't guarantee API readiness

Recommendations:

• Add curl timeouts: curl --max-time 5
• Make MySQL delay configurable: MYSQL_INIT_DELAY=${MYSQL_INIT_DELAY:-30}
• Add database-specific health checks before API testing

Missing Validation Edge Cases

Some validation expressions could be more robust:

# Current: 
"isinstance(data, list)"

# Better:
"isinstance(data, list) and all('id' in item for item in data[:5])"

---

🔒 Security Assessment

Low Risk Issues:

• API token cleanup: Properly handled in both success and failure paths
• Credential handling: Uses environment variables appropriately
• Test isolation: Each backend test runs in isolation

Medium Risk Issue:

• Python code injection in check_json() function as mentioned above

---

⚡ Performance Considerations

Good:

• Sequential backend testing prevents resource conflicts
• Proper container lifecycle management
• Efficient JSON validation using Python

Could Improve:

• Parallel endpoint testing within each backend (currently sequential)
• Configurable test timeouts for different environments
• Smart skipping of similar endpoint patterns

---

📊 Test Coverage Assessment

Excellent improvement:

• From ~40 status-only checks to ~135 comprehensive validations
• Cross-database validation ensures consistency
• Real-world usage patterns mimic actual UI interactions

Missing coverage:

• Error response validation (only tests happy paths)
• Edge cases like empty datasets, malformed requests
• Performance regression detection

---

🎯 Final Recommendation

APPROVE with suggested improvements. This PR significantly enhances test quality and would have prevented real production issues. The bash script injection issue should be addressed, but it doesn't block the core value proposition.

Priority fixes:

1. Fix Python code injection in check_json()
2. Add curl timeouts to prevent hanging
3. Improve error logging for easier debugging

Nice-to-have:

1. More robust validation expressions
2. Configurable timeouts and delays
3. Enhanced cleanup logging

The multi-backend approach is particularly valuable for catching database-specific issues early.

---

[github-actions]

Claude finished @Yeraze's task —— View job

---

PR Review: API Exercise Test Response Validation

Reviewing the enhanced API testing implementation that adds JSON response validation across all three database backends.

Todo List:

• Read and analyze changed files
• Assess code quality and best practices
• Identify potential bugs or security issues
• Evaluate performance considerations
• Review test coverage improvements
• Provide comprehensive feedback

---

✅ Strengths

Excellent Test Coverage Enhancement

• 40+ endpoints now validate JSON structure instead of just HTTP status codes
• Multi-backend consistency testing - runs against SQLite, PostgreSQL, and MySQL
• Would have caught real bugs like [BUG] in View Saved Backups: SqliteError: no such column: backupPath #2419 (backup history wrong column names)
• Comprehensive V1 API coverage with proper Bearer token authentication

Robust Implementation

• Smart error handling in check_json() with Python JSON validation (api-exercise-test.sh:165-173)
• Proper authentication flow including CSRF token handling and session management
• Fallback password attempt (changeme → changeme1) handles different deployments
• Clean separation of concerns between status checks and JSON validation

Good Integration

• Seamlessly integrated into existing system test suite (system-tests.sh:353)
• Docker compose profile management properly isolates backend testing
• Proper cleanup of test containers and temporary files

---

⚠️ Critical Issues Found

🔴 Code Injection Vulnerability (api-exercise-test.sh:169)

result = bool(${validation})  # ← UNSAFE: Direct interpolation

Issues:

• Code injection risk: The ${validation} parameter is directly interpolated into Python code without escaping
• Silent failures: 2>/dev/null could mask Python syntax errors
• Fragile error handling: The fallback might not catch all failure modes

Recommendation:

# Pass validation as argument to avoid injection
valid=$(echo "$body" | python3 -c "
import sys, json
validation_expr = sys.argv[1] if len(sys.argv) > 1 else 'True'
try:
    data = json.load(sys.stdin)
    result = bool(eval(validation_expr))
    print('OK' if result else 'FAIL')
except Exception as e:
    print(f'ERR:{e}')
" "$validation" || echo "ERR:python failed")

🟡 Race Conditions in Backend Startup (test-api-exercise-all-backends.sh:89-103)

• No curl timeout: Long-hanging requests could cause false positives
• MySQL timing: 30s hardcoded delay may not be sufficient for all environments
• Container state vs API readiness: Container being "Up" doesn't guarantee API readiness

Recommendations:

• Add curl timeouts: curl --max-time 5
• Make MySQL delay configurable: MYSQL_INIT_DELAY=${MYSQL_INIT_DELAY:-30}
• Add database-specific health checks before API testing

---

⚡ Performance Considerations

Good:

• Sequential backend testing prevents resource conflicts
• Proper container lifecycle management
• Efficient JSON validation using Python

Could Improve:

• Parallel endpoint testing within each backend (currently sequential)
• Configurable test timeouts for different environments
• Smart skipping of similar endpoint patterns

---

📊 Test Coverage Assessment

Excellent improvement:

• From ~40 status-only checks to ~135 comprehensive validations
• Cross-database validation ensures consistency
• Real-world usage patterns mimic actual UI interactions

Missing coverage:

• Error response validation (only tests happy paths)
• Edge cases like empty datasets, malformed requests
• Performance regression detection

---

🔒 Security Assessment

Low Risk Issues:

• API token cleanup: Properly handled in both success and failure paths
• Credential handling: Uses environment variables appropriately
• Test isolation: Each backend test runs in isolation

Medium Risk Issue:

• Python code injection in check_json() function as mentioned above

---

🎯 Final Recommendation

APPROVE with suggested improvements. This PR significantly enhances test quality and would have prevented real production issues. The bash script injection issue should be addressed, but it doesn't block the core value proposition.

Priority fixes:

1. Fix Python code injection in check_json()
2. Add curl timeouts to prevent hanging
3. Improve error logging for easier debugging

Nice-to-have:

1. More robust validation expressions
2. Configurable timeouts and delays
3. Enhanced cleanup logging

The multi-backend approach is particularly valuable for catching database-specific issues early.

---

[github-actions]

Claude finished @Yeraze's task —— View job

---

PR Review: API Exercise Test Response Validation

Reviewing the enhanced API testing implementation that adds JSON response validation across all three database backends.

Todo List:

• Read and analyze changed files
• Assess code quality and best practices
• Identify potential bugs or security issues
• Evaluate performance considerations
• Review test coverage improvements
• Provide comprehensive feedback

---

✅ Strengths

Excellent Test Coverage Enhancement

• 40+ endpoints now validate JSON structure instead of just HTTP status codes (api-exercise-test.sh:144-184)
• Multi-backend consistency testing - runs against SQLite, PostgreSQL, and MySQL (test-api-exercise-all-backends.sh:62-130)
• Would have caught real bugs like [BUG] in View Saved Backups: SqliteError: no such column: backupPath #2419 (backup history wrong column names)
• Comprehensive V1 API coverage with proper Bearer token authentication (api-exercise-test.sh:583-681)

Robust Implementation

• Smart error handling in check_json() with Python JSON validation (api-exercise-test.sh:165-173)
• Proper authentication flow including CSRF token handling and session management
• Fallback password attempt (changeme → changeme1) handles different deployments (api-exercise-test.sh:242-246)
• Clean separation of concerns between status checks and JSON validation

Good Integration

• Seamlessly integrated into existing system test suite (system-tests.sh around line 353)
• Docker compose profile management properly isolates backend testing
• Proper cleanup of test containers and temporary files

Code Quality Positives

• Clear, descriptive function names (check_json, check_v1, api_body)
• Consistent error handling patterns across all validation functions
• Good use of bash best practices (set -euo pipefail, proper quoting)
• Informative logging with color-coded pass/fail/skip status

---

⚠️ Critical Issues Found

🔴 Code Injection Vulnerability (api-exercise-test.sh:169)

result = bool(${validation})  # ← UNSAFE: Direct interpolation

Issues:

• Code injection risk: The ${validation} parameter is directly interpolated into Python code without escaping
• Silent failures: 2>/dev/null could mask Python syntax errors
• Fragile error handling: The fallback might not catch all failure modes

Impact: Malicious input in validation expressions could execute arbitrary Python code

Recommendation:

# Pass validation as argument to avoid injection
valid=$(echo "$body" | python3 -c "
import sys, json
validation_expr = sys.argv[1] if len(sys.argv) > 1 else 'True'
try:
    data = json.load(sys.stdin)
    result = bool(eval(validation_expr))
    print('OK' if result else 'FAIL')
except Exception as e:
    print(f'ERR:{e}')
" "$validation" || echo "ERR:python failed")

🔴 Duplicate Code Injection (api-exercise-test.sh:638)

The same vulnerability exists in the check_v1() function - same fix needed there.

🟡 Race Conditions in Backend Startup (test-api-exercise-all-backends.sh:89-103)

• No curl timeout: Long-hanging requests could cause false positives
• MySQL timing: 30s hardcoded delay may not be sufficient for all environments
• Container state vs API readiness: Container being "Up" doesn't guarantee API readiness

Recommendations:

• Add curl timeouts: curl --max-time 5
• Make MySQL delay configurable: MYSQL_INIT_DELAY=${MYSQL_INIT_DELAY:-30}
• Add database-specific health checks before API testing

---

⚡ Performance Considerations

Good:

• Sequential backend testing prevents resource conflicts
• Proper container lifecycle management
• Efficient JSON validation using Python

Could Improve:

• Parallel endpoint testing within each backend (currently sequential)
• Configurable test timeouts for different environments
• Smart skipping of similar endpoint patterns

---

📊 Test Coverage Assessment

Excellent improvement:

• From ~40 status-only checks to ~135 comprehensive validations
• Cross-database validation ensures consistency
• Real-world usage patterns mimic actual UI interactions

Missing coverage:

• Error response validation (only tests happy paths)
• Edge cases like empty datasets, malformed requests
• Performance regression detection

JSON Validation Quality:

• Basic structure checks are comprehensive (lists, objects, required keys)
• Some validations could be more robust - e.g., isinstance(data, list) could check for required fields in list items
• Good coverage of different response types - arrays, objects, success/data envelopes

---

🔒 Security Assessment

Low Risk Issues:

• API token cleanup: Properly handled in both success and failure paths (api-exercise-test.sh:677)
• Credential handling: Uses environment variables appropriately
• Test isolation: Each backend test runs in isolation

Medium Risk Issue:

• Python code injection in both check_json() and check_v1() functions as mentioned above

---

🎯 Final Recommendation

APPROVE with suggested improvements. This PR significantly enhances test quality and would have prevented real production issues. The bash script injection issue should be addressed, but it doesn't block the core value proposition.

Priority fixes:

1. Fix Python code injection in check_json() and check_v1()
2. Add curl timeouts to prevent hanging
3. Improve error logging for easier debugging

Nice-to-have:

1. More robust validation expressions
2. Configurable timeouts and delays
3. Enhanced cleanup logging

The multi-backend approach is particularly valuable for catching database-specific issues early. This is a significant quality improvement that moves the project from basic smoke testing to comprehensive API validation.

---

[Yeraze]

System Test Results

All 11 system tests passed, including the new API Exercise test on all 3 backends:

Test	Result
Configuration Import	✓ PASSED
Quick Start Test	✓ PASSED
Security Test	✓ PASSED
V1 API Test	✓ PASSED
Reverse Proxy Test	✓ PASSED
Reverse Proxy + OIDC	✓ PASSED
Virtual Node CLI Test	✓ PASSED
Backup & Restore Test	✓ PASSED
Database Migration Test	✓ PASSED
DB Backing Consistency	✓ PASSED
API Exercise (3 DBs)	✓ PASSED

API Exercise per-backend:

• SQLite: 135/135 passed
• PostgreSQL: 135/135 passed
• MySQL: 135/135 passed