App Optimizer Skill

Skill especializado en optimización integral de aplicaciones web full-stack. Combina análisis de performance, seguridad y arquitectura para maximizar la calidad del código.

Capacidades Principales

1. Análisis de Performance

Backend (Python/FastAPI)

# Detección de N+1 Queries
PROBLEMATIC_PATTERNS = {
    'n_plus_1': [
        r'for\s+\w+\s+in\s+\w+:.*execute\(',   # Query en loop
        r'for\s+\w+\s+in\s+\w+:.*\.get\(',     # ORM get en loop
    ],
    'full_table_scan': [
        r'SELECT\s+\*\s+FROM',                  # SELECT * sin filtro
        r'execute\(["\']SELECT.*without.*WHERE' # Query sin WHERE
    ],
    'missing_index': [
        r'WHERE\s+\w+\s*=',                     # Columnas en WHERE sin índice
        r'ORDER BY\s+\w+',                      # Columnas en ORDER BY
    ]
}

Frontend (JavaScript)

// Patrones de performance a detectar
const PERFORMANCE_ISSUES = {
    // DOM
    'layout_thrashing': /\.offset|\.client|\.scroll|getBoundingClientRect/,
    'forced_reflow': /\.style\.\w+\s*=.*\.offset/,

    // Memory
    'event_listener_leak': /addEventListener(?!.*removeEventListener)/,
    'closure_leak': /setInterval|setTimeout(?!.*clear)/,

    // Rendering
    'expensive_css': /box-shadow|filter|backdrop-filter|transform/,
    'large_paint': /position:\s*fixed|background-attachment:\s*fixed/
};

2. Optimizaciones Automáticas

Database Queries

# ANTES (N+1)
for emp in employees:
    details = db.query(Details).filter_by(emp_id=emp.id).first()

# DESPUÉS (JOIN optimizado)
employees_with_details = db.query(Employee, Details)\
    .join(Details)\
    .all()

Bundle Size Reduction

// Lazy loading de módulos
const ChartManager = await import('./modules/chart-manager.js');

// Tree shaking - importar solo lo necesario
import { debounce, throttle } from './utils.js';
// NO: import * as Utils from './utils.js';

Image Optimization

<!-- Lazy loading nativo -->
<img src="image.jpg" loading="lazy" decoding="async">

<!-- Responsive images -->
<picture>
  <source srcset="image.webp" type="image/webp">
  <source srcset="image.jpg" type="image/jpeg">
  <img src="image.jpg" alt="Description">
</picture>

3. Análisis de Seguridad

OWASP Top 10 Detection

SECURITY_PATTERNS = {
    'A01_BROKEN_ACCESS_CONTROL': {
        'missing_auth': r'@app\.(get|post|put|delete)\([^)]+\)\s*\n\s*async\s+def\s+\w+\([^)]*\):(?!.*Depends)',
        'hardcoded_secret': r'(password|secret|api_key)\s*=\s*["\'][^"\']+["\']',
    },
    'A03_INJECTION': {
        'sql_injection': r'execute\(f["\']|format\(.*\)|%\s*\(',
        'command_injection': r'os\.system\(|subprocess\.(call|run|Popen)\([^)]*\+',
        'xss': r'innerHTML\s*=|document\.write\(',
    },
    'A07_AUTH_FAILURES': {
        'weak_jwt': r'algorithm\s*=\s*["\']none["\']',
        'no_expiration': r'create.*token(?!.*exp)',
    }
}

Secret Scanning

SECRET_PATTERNS = {
    'api_key': r'(?i)(api[_-]?key|apikey)\s*[:=]\s*["\']?[\w-]{20,}',
    'jwt_secret': r'(?i)(jwt[_-]?secret|secret[_-]?key)\s*[:=]\s*["\'][^"\']{10,}',
    'database_url': r'(?i)(database_url|db_url)\s*[:=]\s*["\']?[a-z]+:\/\/[^"\']+',
    'aws_key': r'AKIA[0-9A-Z]{16}',
    'private_key': r'-----BEGIN (RSA |EC )?PRIVATE KEY-----',
}

4. Métricas y Benchmarks

Core Web Vitals Targets

const WEB_VITALS_TARGETS = {
    LCP: 2500,  // Largest Contentful Paint < 2.5s
    FID: 100,   // First Input Delay < 100ms
    CLS: 0.1,   // Cumulative Layout Shift < 0.1
    TTFB: 600,  // Time to First Byte < 600ms
    FCP: 1800,  // First Contentful Paint < 1.8s
};

Database Query Benchmarks

QUERY_BENCHMARKS = {
    'simple_select': 10,    # < 10ms
    'join_2_tables': 50,    # < 50ms
    'join_3_tables': 100,   # < 100ms
    'aggregate': 200,       # < 200ms
    'full_text_search': 100 # < 100ms
}

Comandos de Optimización

Performance Audit

# Backend profiling
python -m cProfile -o profile.stats main.py
python -m pstats profile.stats

# Frontend bundle analysis
npx webpack-bundle-analyzer stats.json

# Database query analysis
sqlite3 yukyu.db "EXPLAIN QUERY PLAN SELECT ..."

Security Audit

# Python security scan
pip install bandit
bandit -r . -f json -o security_report.json

# Dependency vulnerabilities
pip-audit
npm audit

# Secrets scanning
git secrets --scan

Checklist de Optimización

Pre-Deploy

• No hay console.log en producción
• Imágenes optimizadas (WebP, lazy loading)
• CSS/JS minificados
• Gzip/Brotli habilitado
• Cache headers configurados
• Security headers (CSP, HSTS, X-Frame-Options)

Database

• Índices en columnas WHERE/ORDER BY
• No hay SELECT *
• Queries paginadas
• Connection pooling configurado
• Backups automáticos

API

• Rate limiting activo
• Autenticación en endpoints sensibles
• Validación de input (Pydantic)
• Error handling sin leak de info
• CORS restringido

Frontend

• Virtual scrolling para listas grandes
• Debounce en inputs
• Lazy loading de módulos
• Service Worker para offline
• Accesibilidad WCAG AA

Integración con YuKyuDATA

Optimizaciones Específicas

# 1. Paginación server-side para /api/employees
@app.get("/api/v1/employees")
async def get_employees_paginated(
    year: int = None,
    page: int = 1,
    page_size: int = 50,
    sort_by: str = "name"
):
    offset = (page - 1) * page_size
    query = "SELECT * FROM employees WHERE year = ? LIMIT ? OFFSET ?"
    return {"data": results, "total": total, "page": page}

# 2. Cache de datos frecuentes
from caching import cache

@cache(ttl=300)  # 5 minutos
def get_yearly_stats(year: int):
    return calculate_stats(year)

# 3. Batch queries para dashboard
async def get_dashboard_data(year: int):
    # Una sola query con JOINs en lugar de múltiples
    query = """
        SELECT
            e.*, g.dispatch_name, u.contract_business
        FROM employees e
        LEFT JOIN genzai g ON e.employee_num = g.employee_num
        LEFT JOIN ukeoi u ON e.employee_num = u.employee_num
        WHERE e.year = ?
    """

Output del Análisis

{
    "performance": {
        "score": 85,
        "issues": [
            {"type": "N+1", "file": "main.py", "line": 234, "severity": "HIGH"},
            {"type": "BUNDLE_SIZE", "file": "app.js", "size_kb": 450, "severity": "MEDIUM"}
        ],
        "recommendations": [
            "Implementar paginación server-side",
            "Lazy load chart libraries"
        ]
    },
    "security": {
        "score": 72,
        "vulnerabilities": [
            {"type": "MISSING_AUTH", "endpoint": "/api/employees", "severity": "CRITICAL"},
            {"type": "CORS_PERMISSIVE", "severity": "MEDIUM"}
        ],
        "secrets_found": 0
    },
    "overall_grade": "B"
}

Principio Guía: "Optimiza lo que importa. Mide antes de optimizar. Seguridad nunca es opcional."