abcnuts/production-system-audit

Production System Audit

Systematically audit production systems to identify weaknesses, single points of failure, and troubleshooting spots before they become problems.

When to Use This Skill

• Pre-production launch - Audit before going live to catch issues early
• Technical debt assessment - Identify accumulated issues in existing systems
• Systematic troubleshooting - Debug issues that span multiple layers
• Security review preparation - Comprehensive security and reliability audit
• Scaling preparation - Identify bottlenecks before traffic increases

Core Principles

Systematic, not symptomatic: Audit all layers methodically, don't just fix visible bugs.

Severity-based prioritization: Categorize findings as Critical/High/Medium/Low to guide action.

Actionable recommendations: Every finding includes specific fix guidance, not just problem description.

Comprehensive coverage: Database → APIs → Integrations → Performance → Security → Monitoring.

Audit Workflow

Phase 1: Architecture Audit

Audit the foundational layers: database schema, API endpoints, and core architecture.

1.1 Database Schema Audit

Read /home/ubuntu/skills/production-system-audit/references/database-audit-checklist.md and systematically check:

• Foreign key constraints and referential integrity
• Indexes on foreign keys and common query patterns
• Unique constraints on natural keys
• Data types, lengths, and NOT NULL constraints
• Timestamps and audit trails
• JSON column usage
• Enum definitions
• Cascading deletes and orphaned record prevention

1.2 API Endpoint Audit

Read /home/ubuntu/skills/production-system-audit/references/api-audit-checklist.md and systematically check:

• Input validation (Zod schemas, range checks, format validation)
• Error handling (TRPCError usage, appropriate codes, descriptive messages)
• Authentication and authorization (procedure types, ownership checks)
• Rate limiting on expensive operations
• Pagination on list endpoints
• Data sanitization
• Transaction handling
• Response size limits

Output: Document all findings with severity, impact, examples, and recommendations.

---

Phase 2: Integration Audit

Audit external service integrations for reliability and security.

Read /home/ubuntu/skills/production-system-audit/references/integration-audit-checklist.md and check each integration:

Common Integrations to Audit:

• AI services (Hume AI, OpenAI, etc.)
• Communication services (Twilio, SendGrid, etc.)
• Storage services (S3, Cloudinary, etc.)
• Database services (Supabase, PlanetScale, etc.)
• Payment services (Stripe, PayPal, etc.)

For each integration, check:

• Error handling and retry logic
• Timeout configuration
• API key and credential management
• Webhook security (signature verification)
• Resource cleanup (no unbounded accumulation)
• Rate limit handling
• Data validation from external sources
• Webhook reliability (async processing, idempotency)
• Connection pooling
• Service health monitoring

Output: Document integration-specific issues with recommendations.

---

Phase 3: Performance & Scalability Audit

Identify performance bottlenecks and scalability issues.

3.1 Database Query Performance

Identify slow queries:

• Full table scans without indexes
• N+1 query problems
• Missing composite indexes for common filters
• Inefficient JOIN operations
• Unbounded result sets

3.2 API Response Times

Measure baseline performance:

• Average response times for critical endpoints
• Slow endpoints (>500ms)
• Endpoints without caching that should have it

3.3 Concurrent Usage

Identify untested scenarios:

• Multiple users creating resources simultaneously
• Race conditions in status updates
• Concurrent access to shared resources
• Load testing gaps

Output: List performance bottlenecks with optimization recommendations.

---

Phase 4: Monitoring & Observability Audit

Identify gaps in logging, error tracking, and health checks.

4.1 Logging Gaps

Check for missing logging:

• External API call success/failure rates
• File upload/download operations
• User action tracking
• Performance metrics (response times, query times)
• Security events (login attempts, permission denials)

4.2 Error Tracking

Assess current error tracking:

• Centralized error tracking (Sentry, etc.)
• Error aggregation and alerting
• Error rate monitoring
• Stack trace capture

4.3 Health Checks

Identify missing health checks:

• Database connection health
• External service availability (Hume AI, Twilio, S3, etc.)
• Disk space, memory usage
• /api/health endpoint with dependency checks

Output: Monitoring and observability improvement plan.

---

Phase 5: Security Audit

Identify security vulnerabilities and compliance gaps.

5.1 Input Validation

Check for missing validation:

• Phone number formats
• Email formats
• Code/identifier formats
• File upload types and sizes
• User-provided content sanitization

5.2 Data Sanitization

Check for XSS and injection risks:

• User-provided content displayed as HTML
• Script content not sanitized
• SQL injection prevention (parameterized queries)
• No eval() or dangerous functions

5.3 API Security

Check for security gaps:

• Rate limiting on all endpoints
• Request size limits
• Webhook signature verification
• HTTPS enforcement
• CORS configuration

Output: Security findings with remediation steps.

---

Deliverables

1. Comprehensive Audit Report

Use the template at /home/ubuntu/skills/production-system-audit/templates/audit-report-template.md.

Structure:

• Executive Summary (overall health, issue counts)
• Phase 1: Architecture Audit (database, APIs)
• Phase 2: Integration Audit (external services)
• Phase 3: Performance & Scalability
• Phase 4: Monitoring & Observability
• Phase 5: Security Audit
• Summary of Findings (categorized by severity)
• Recommended Action Plan (4-week roadmap)
• Troubleshooting Guide (common failure scenarios)

2. Prioritized Improvement Roadmap

Week 1: Critical and high-priority issues
Week 2: Medium-priority issues (performance, monitoring)
Week 3: Medium-priority issues (security, reliability)
Week 4: Low-priority issues (nice-to-haves, cleanup)

3. Troubleshooting Guide

Document common failure scenarios with:

• Symptoms (what user sees)
• Possible causes
• Debugging steps
• Prevention strategies

---

Severity Classification

Use consistent severity levels across all findings:

🔴 CRITICAL - System broken, data loss possible, security breach
Fix immediately (same day)

🔴 HIGH - Data integrity risk, major functionality broken, significant security gap
Fix within 1 week

🟡 MEDIUM - Performance degradation, poor UX, moderate security risk
Fix within 1 month

🟢 LOW - Minor issues, nice-to-haves, limited impact
Fix when convenient

---

Tips

Start broad, then deep: Audit all areas at high level first, then dive deep into problem areas.

Use checklists systematically: Don't skip items—systematic coverage catches hidden issues.

Provide examples: Every finding should include code snippet or scenario showing the problem.

Be specific in recommendations: "Add foreign key constraints" is better than "improve data integrity."

Test your findings: Verify issues exist before documenting (run queries, test endpoints).

Consider the audience: Technical findings for engineers, executive summary for leadership.

---

Common Gotchas

Don't just audit code: Also check deployment config, environment variables, DNS, SSL certs.

Don't ignore "working" systems: Just because it works doesn't mean it's correct (e.g., missing FK constraints).

Don't assume test coverage: Verify tests exist and pass for critical paths.

Don't skip documentation: Undocumented systems are harder to maintain and debug.

Don't forget cleanup: Temporary resources, expired codes, old configs—all accumulate over time.