dbis_core/scripts/monitor-outbox.sh

#!/bin/bash
# Monitor Dual Ledger Outbox Queue
# Shows queue depth, failed jobs, and processing stats

set -e

# Load database URL from environment
DATABASE_URL="${DATABASE_URL:-${1:-postgresql://user:password@localhost:5432/dbis}}"

echo "=== Dual Ledger Outbox Queue Status ==="
echo ""

# Queue depth by status
echo "📊 Queue Depth by Status:"
psql "$DATABASE_URL" -c "
    SELECT 
        status,
        COUNT(*) as count,
        MIN(created_at) as oldest_job,
        MAX(created_at) as newest_job
    FROM dual_ledger_outbox
    GROUP BY status
    ORDER BY 
        CASE status
            WHEN 'QUEUED' THEN 1
            WHEN 'SENT' THEN 2
            WHEN 'ACKED' THEN 3
            WHEN 'FINALIZED' THEN 4
            WHEN 'FAILED' THEN 5
        END;
"

echo ""

# Failed jobs needing attention
echo "⚠️  Failed Jobs (last 10):"
psql "$DATABASE_URL" -c "
    SELECT 
        outbox_id,
        sovereign_bank_id,
        attempts,
        last_error,
        last_attempt_at,
        created_at
    FROM dual_ledger_outbox
    WHERE status = 'FAILED'
    ORDER BY last_attempt_at DESC
    LIMIT 10;
"

echo ""

# Jobs stuck in SENT status (may need manual intervention)
echo "🔍 Jobs Stuck in SENT Status (> 5 minutes):"
psql "$DATABASE_URL" -c "
    SELECT 
        outbox_id,
        sovereign_bank_id,
        attempts,
        last_attempt_at,
        AGE(now(), last_attempt_at) as stuck_duration
    FROM dual_ledger_outbox
    WHERE status = 'SENT'
      AND last_attempt_at < now() - INTERVAL '5 minutes'
    ORDER BY last_attempt_at ASC
    LIMIT 10;
"

echo ""

# Processing rate (last hour)
echo "⚡ Processing Rate (last hour):"
psql "$DATABASE_URL" -c "
    SELECT 
        status,
        COUNT(*) as count,
        COUNT(*) FILTER (WHERE finalized_at > now() - INTERVAL '1 hour') as last_hour
    FROM dual_ledger_outbox
    WHERE created_at > now() - INTERVAL '1 hour'
       OR finalized_at > now() - INTERVAL '1 hour'
    GROUP BY status
    ORDER BY count DESC;
"

echo ""

# Average processing time
echo "⏱️  Average Processing Times:"
psql "$DATABASE_URL" -c "
    SELECT 
        AVG(EXTRACT(EPOCH FROM (acked_at - created_at))) as avg_queue_to_ack_seconds,
        AVG(EXTRACT(EPOCH FROM (finalized_at - acked_at))) as avg_ack_to_final_seconds,
        AVG(EXTRACT(EPOCH FROM (finalized_at - created_at))) as avg_total_seconds,
        COUNT(*) as completed_jobs
    FROM dual_ledger_outbox
    WHERE status = 'FINALIZED'
      AND finalized_at > now() - INTERVAL '24 hours';
"

echo ""

# Sovereign bank breakdown
echo "🌐 Jobs by Sovereign Bank:"
psql "$DATABASE_URL" -c "
    SELECT 
        sovereign_bank_id,
        status,
        COUNT(*) as count
    FROM dual_ledger_outbox
    GROUP BY sovereign_bank_id, status
    ORDER BY sovereign_bank_id, status;
"