fintechner closed-trades diagnostic

34-ct-diag.sh

#!/bin/bash
set -euo pipefail
export AWS_PAGER=""
REGION=ap-northeast-1
INSTANCE_ID=i-089ad4a6b76132ba2

SQL_B64=$(base64 -w0 <<'SQL'
SELECT 'closed_trades:'||COUNT(*) FROM closed_trades;
SELECT 'fills by origin:' AS x;
SELECT origin, COUNT(*) FROM fills GROUP BY origin;
SELECT 'fills.owner_id null vs set:' AS x;
SELECT (CASE WHEN owner_id IS NULL THEN 'null' ELSE 'set' END) AS state, COUNT(*) FROM fills GROUP BY state;
SQL
)

CMD_ID=$(aws --region "$REGION" ssm send-command \
  --instance-ids "$INSTANCE_ID" \
  --document-name AWS-RunShellScript \
  --parameters "{\"commands\": [\"echo '$SQL_B64' | base64 -d > /tmp/q.sql && sudo docker cp /tmp/q.sql timescale:/tmp/q.sql && sudo docker exec timescale psql -U fintechner -d fintechner -tA -f /tmp/q.sql\", \"echo --- connector commit ---\", \"sudo sha256sum /opt/fintechner/bin/connector | cut -c1-16\", \"echo --- backfill log lines ---\", \"sudo journalctl -u fintechner-connector --since '15 min ago' --no-pager 2>&1 | grep -iE 'closedtrades|backfill|closed trade' | head -20\", \"echo --- recent insert failures ---\", \"sudo journalctl -u fintechner-connector --since '15 min ago' --no-pager 2>&1 | grep -iE 'insert.*fail|error' | head -10\"]}" \
  --query Command.CommandId --output text)

for i in $(seq 1 20); do
  sleep 3
  STATUS=$(aws --region "$REGION" ssm get-command-invocation --command-id "$CMD_ID" --instance-id "$INSTANCE_ID" --query Status --output text 2>/dev/null || echo Pending)
  case "$STATUS" in Success|Failed|Cancelled|TimedOut) break ;; esac
done
aws --region "$REGION" ssm get-command-invocation --command-id "$CMD_ID" --instance-id "$INSTANCE_ID" --query StandardOutputContent --output text
echo "--- STDERR ---"
aws --region "$REGION" ssm get-command-invocation --command-id "$CMD_ID" --instance-id "$INSTANCE_ID" --query StandardErrorContent --output text