Health Checks

Health Checks Skill

Overview

This skill provides knowledge and procedures for monitoring infrastructure health across GitHub Actions, Railway, Supabase, and Postgres.

Health Check Philosophy

Why Regular Health Checks?

1. Proactive Detection: Find issues before users do 2. Trend Identification: Spot degradation early 3. Capacity Planning: Know when to scale 4. Compliance: Maintain system hygiene 5. Documentation: Track system state over time

Health Check Frequency

Health Status Framework

Traffic Light System

GREEN  - All systems healthy
         - No critical issues
         - Metrics within normal ranges
         - Advisory count: 0

YELLOW - Warning state
         - Non-critical issues present
         - Metrics approaching limits
         - Performance advisories present

RED    - Critical state
         - Service impaired or unavailable
         - Critical metrics exceeded
         - Security advisories present
         - Immediate action required

Status Determination Rules

Health Metrics

Key Performance Indicators

Platform-Specific Metrics

Supabase

• API error rate
• Auth failure rate
• Storage utilization
• Edge function cold starts
• Realtime connection count
• Advisory count (security/performance)

GitHub Actions

• Workflow success rate
• Average build time
• Queue wait time
• Cache hit rate
• Failed workflow count

Railway

• Service uptime
• Deploy success rate
• Memory utilization
• CPU utilization
• Health check pass rate

Postgres

• Connection utilization
• Query duration distribution
• Lock contention
• Dead tuple ratio
• Index usage efficiency
• Table bloat

Health Check Procedures

Quick Health Check (5 min)

Purpose: Verify basic system functionality

1. [ ] Check for active incidents (any platform)
2. [ ] Verify all services responding
3. [ ] Check for critical advisories
4. [ ] Review last hour error rate
5. [ ] Check connection pool status

Daily Health Check (15 min)

Purpose: Assess overall system health

1. [ ] Run quick health check
2. [ ] Review 24-hour error trends
3. [ ] Check CI/CD success rate
4. [ ] Review all advisories
5. [ ] Check slow query log
6. [ ] Verify backups completed
7. [ ] Review resource utilization

Weekly Health Check (30 min)

Purpose: Comprehensive review and trending

1. [ ] Run daily health check
2. [ ] Analyze weekly error patterns
3. [ ] Review index usage stats
4. [ ] Check for table bloat
5. [ ] Review connection patterns
6. [ ] Assess capacity trends
7. [ ] Review deployment frequency
8. [ ] Check certificate expirations

Monthly Deep Check (1+ hours)

Purpose: Full system audit

1. [ ] Run weekly health check
2. [ ] Full index analysis
3. [ ] Query performance review
4. [ ] Security configuration audit
5. [ ] Capacity planning review
6. [ ] Cost analysis
7. [ ] Documentation review
8. [ ] Disaster recovery test

Alert Thresholds

Immediate Alerts (Page)

• Service unavailable > 1 minute
• Error rate > 10%
• Database connections > 90%
• Security advisory created
• Deployment failure (production)
• Health check failure > 5 minutes

Warning Alerts (Slack/Email)

• Error rate > 2%
• Database connections > 75%
• Performance advisory created
• Build time increase > 50%
• Response time P95 > 1s
• Disk usage > 80%

Info Alerts (Daily Digest)

• New advisory (any type)
• Build time change
• Resource trend change
• Configuration change

Health Report Template

# Infrastructure Health Report

**Generated**: {TIMESTAMP}
**Report Type**: {Quick | Daily | Weekly | Monthly}
**Overall Status**: {GREEN | YELLOW | RED}

## Executive Summary
{2-3 sentence overview}

## Platform Status

| Platform | Status | Issues | Warnings |
|----------|--------|--------|----------|
| GitHub Actions | {STATUS} | {N} | {N} |
| Railway | {STATUS} | {N} | {N} |
| Supabase | {STATUS} | {N} | {N} |
| Postgres | {STATUS} | {N} | {N} |

## Key Metrics

### Database
- Connections: {N}/{MAX} ({PCT}%)
- Query P95: {MS}ms
- Dead Rows: {PCT}%

### API
- Error Rate: {PCT}%
- Response Time P95: {MS}ms

### CI/CD
- Success Rate: {PCT}%
- Avg Build Time: {MIN}m

## Advisories

### Security
{List or "None"}

### Performance
{List or "None"}

## Issues Requiring Attention

### Immediate
{List or "None"}

### This Week
{List or "None"}

## Trends

{Notable changes from previous period}

## Recommendations

{Specific actions to improve health}

---
*Next health check: {TIMESTAMP}*

Remediation Playbooks

High Connection Utilization

1. Check for connection leaks
2. Identify idle connections
3. Review connection pool settings
4. Consider connection pooler (PgBouncer/Supavisor)
5. Optimize application connection handling

High Error Rate

1. Identify error types
2. Check recent deployments
3. Review affected endpoints
4. Check downstream dependencies
5. Roll back if deployment-related

Slow Queries

1. Identify slow queries (pg_stat_statements)
2. Run EXPLAIN ANALYZE
3. Check for missing indexes
4. Review query patterns
5. Consider query optimization or caching

Build Failures

1. Review failure logs
2. Check for flaky tests
3. Verify dependencies available
4. Check for environment issues
5. Review recent changes

See checklists.md for detailed health check checklists.

Health Check Checklists

Quick Health Check Checklist

Time: ~5 minutes When: After deployments, start of day, incident investigation

GitHub Actions

• [ ] Check recent run status: gh run list --limit 5
• [ ] Any stuck runs? gh run list --status in_progress
• [ ] Recent failures? gh run list --status failure --limit 3

Railway

• [ ] Services running: railway status
• [ ] Recent errors in logs: railway logs 2>&1 | grep -i error | tail -10

Supabase

• [ ] Check for advisories: get_advisors(type: "security")
• [ ] Check API logs for errors: get_logs(service: "api")

Postgres

• [ ] Connection count OK:

  SELECT count(*) FROM pg_stat_activity;

• [ ] No blocked queries:

  SELECT count(*) FROM pg_stat_activity
  WHERE cardinality(pg_blocking_pids(pid)) > 0;

---

Daily Health Check Checklist

Time: ~15 minutes When: Start of business day

All Quick Checks Plus:

GitHub Actions

• [ ] 24-hour success rate:

  gh run list --limit 50 --json conclusion

• [ ] No workflows stuck > 1 hour

Railway

• [ ] All deployments successful
• [ ] Resource utilization within limits
• [ ] No repeated restarts in logs

Supabase

• [ ] Review all advisories:
• [ ] Security advisories: get_advisors(type: "security")
• [ ] Performance advisories: get_advisors(type: "performance")
• [ ] Auth service healthy: get_logs(service: "auth")
• [ ] Realtime service healthy: get_logs(service: "realtime")
• [ ] Edge functions healthy: get_logs(service: "edge-function")

Postgres

• [ ] Connection utilization < 70%:

  SELECT round(count(*)::numeric /
    (SELECT setting::int FROM pg_settings WHERE name = 'max_connections') * 100, 2)
  FROM pg_stat_activity;

• [ ] No long-running queries > 5 min:

  SELECT count(*) FROM pg_stat_activity
  WHERE state = 'active' AND query_start < now() - interval '5 minutes';

• [ ] No idle-in-transaction > 10 min:

  SELECT count(*) FROM pg_stat_activity
  WHERE state = 'idle in transaction'
  AND query_start < now() - interval '10 minutes';

---

Weekly Health Check Checklist

Time: ~30 minutes When: Beginning of week

All Daily Checks Plus:

GitHub Actions

• [ ] Review workflow efficiency
• [ ] Check cache hit rates
• [ ] Identify flaky tests
• [ ] Review build times trend

Railway

• [ ] Review deployment history
• [ ] Check for memory/CPU trends
• [ ] Verify environment variables current
• [ ] Check domain/SSL status

Supabase

• [ ] Review storage usage
• [ ] Check auth provider status
• [ ] Review RLS policies are adequate
• [ ] Verify realtime publication config

Postgres

• [ ] Index usage analysis:

  SELECT indexrelname, idx_scan
  FROM pg_stat_user_indexes
  WHERE idx_scan = 0
  AND indexrelname NOT LIKE '%pkey%';

• [ ] Table bloat check:

  SELECT schemaname || '.' || relname, n_dead_tup, n_live_tup
  FROM pg_stat_user_tables
  WHERE n_dead_tup > 1000
  ORDER BY n_dead_tup DESC LIMIT 10;

• [ ] Table sizes review:

  SELECT schemaname || '.' || tablename,
    pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename))
  FROM pg_tables
  WHERE schemaname = 'public'
  ORDER BY pg_total_relation_size(schemaname || '.' || tablename) DESC
  LIMIT 10;

• [ ] Query performance review (top 5 slow):

  SELECT query, calls, round(mean_exec_time::numeric, 2) as avg_ms
  FROM pg_stat_statements
  ORDER BY mean_exec_time DESC LIMIT 5;

---

Monthly Deep Check Checklist

Time: 1+ hours When: Beginning of month

All Weekly Checks Plus:

GitHub Actions

• [ ] Audit workflow permissions
• [ ] Review secret rotation needs
• [ ] Optimize workflow caching
• [ ] Review action version pins
• [ ] Check for deprecated actions

Railway

• [ ] Review resource allocation
• [ ] Audit environment variables
• [ ] Check for orphaned services
• [ ] Review scaling configuration
• [ ] Cost analysis

Supabase

• [ ] Full security audit:
• [ ] RLS policies complete
• [ ] No public access without intent
• [ ] Auth settings secure
• [ ] Storage policies adequate
• [ ] Performance optimization:
• [ ] Edge function performance
• [ ] API response times
• [ ] Realtime efficiency
• [ ] Review database extensions
• [ ] Check backup status

Postgres

• [ ] Full index analysis:

  -- Unused indexes
  SELECT schemaname, tablename, indexrelname, idx_scan,
    pg_size_pretty(pg_relation_size(indexrelid))
  FROM pg_stat_user_indexes WHERE idx_scan = 0;

  -- Missing indexes (high seq scan tables)
  SELECT schemaname, relname, seq_scan, seq_tup_read, n_live_tup
  FROM pg_stat_user_tables
  WHERE seq_scan > idx_scan AND n_live_tup > 10000;

• [ ] Query optimization review:

  -- Most resource-intensive queries
  SELECT query, calls, total_exec_time, mean_exec_time, rows
  FROM pg_stat_statements
  ORDER BY total_exec_time DESC LIMIT 10;

• [ ] Table maintenance:

  -- Tables needing vacuum
  SELECT schemaname, relname, n_dead_tup, last_vacuum, last_autovacuum
  FROM pg_stat_user_tables
  WHERE n_dead_tup > 10000;

  -- Run vacuum on needed tables
  VACUUM ANALYZE table_name;

• [ ] Connection pool optimization
• [ ] Capacity planning (growth projections)

---

Incident Response Checklist

When: During active incident

Immediate Assessment

• [ ] What is the impact? (Users affected, data at risk)
• [ ] When did it start?
• [ ] What changed recently?
• [ ] Is it getting worse?

Evidence Collection

• [ ] Gather logs from all platforms
• [ ] Check for correlated errors
• [ ] Build timeline of events
• [ ] Document current state

Mitigation

• [ ] Can we rollback?
• [ ] Can we scale?
• [ ] Can we route around?
• [ ] Who needs to be notified?

Resolution

• [ ] Root cause identified?
• [ ] Fix deployed?
• [ ] Service restored?
• [ ] Verification complete?

Post-Incident

• [ ] Timeline documented
• [ ] Root cause documented
• [ ] Prevention measures identified
• [ ] Postmortem scheduled

---

Pre-Deployment Checklist

When: Before production deployment

Code

• [ ] All tests passing
• [ ] No security vulnerabilities
• [ ] Database migrations reviewed
• [ ] Feature flags in place (if needed)

Infrastructure

• [ ] Environment variables updated
• [ ] Database capacity adequate
• [ ] Monitoring in place
• [ ] Rollback plan ready

Process

• [ ] Deployment window confirmed
• [ ] Team notified
• [ ] Runbook available
• [ ] On-call aware

Post-Deploy

• [ ] Quick health check passed
• [ ] No error spike
• [ ] Key features verified
• [ ] Monitoring normal

---

Post-Incident Checklist

When: After incident resolution

Documentation

• [ ] Timeline complete
• [ ] Root cause documented
• [ ] Impact quantified
• [ ] Resolution documented

Follow-up

• [ ] Prevention tasks created
• [ ] Monitoring improved
• [ ] Runbook updated
• [ ] Team retrospective scheduled

Communication

• [ ] Status page updated
• [ ] Affected users notified
• [ ] Internal report shared
• [ ] External postmortem (if needed)