The Trinity Beast Infrastructure — Unified Logs & Observability

1. The Problem — Why This Was Needed

Before June 4, 2026, the Trinity Beast Infrastructure had a logging blind spot. Application code logged to CloudWatch — that part worked. But the surrounding infrastructure was largely invisible:

What We Couldn't Do Before

• Forensics: "Which requests hit this IP in the last 24 hours?" — required guessing from WAF sample metrics
• Performance: "What's the P99 latency for /price endpoints?" — no per-request latency data
• Security correlation: "Did this blocked IP also hit CloudFront?" — separate systems, no join path
• Capacity planning: "What's our cache miss rate for which commands?" — Valkey slow-log was invisible
• Incident review: "What queries were slow during that spike?" — Aurora logs not in CloudWatch, manual RDS download required
• Visitor analytics: "Which docs are most popular by edge location?" — CloudFront data was dark

What We Can Do Now

• Full request tracing: client → CloudFront → ALB → ECS container (correlated by request ID)
• Security forensics across all layers in one S3 bucket (WAF + ALB + CloudFront + S3 access)
• Slow query alerting directly from CloudWatch (no more manual downloads)
• Cache performance monitoring (Valkey slow-log + engine events)
• Athena SQL queries across all S3 log sources (WAF + ALB + CloudFront together)
• 365-day archive with Glacier transition — incident review up to a year later

2. Architecture — The Unified Logging Model

The logging architecture splits into three tiers based on query pattern and retention needs:

flowchart TB
    %% Sources
    subgraph sources["Log Sources"]
        direction LR
        ALB["ALB
Trinity-Beast-TCP-ALB"]
        CF["CloudFront
E110PRKEIYQVLL"]
        WAF["WAF
trinity-beast-api-waf"]
        S3SRC["S3 Website
trinity-beast-website-east2"]
        ECS["ECS Containers
Main · Mirror · LRS · Webhook"]
        LAMBDA["Lambda Functions
10 functions"]
        AURORA["Aurora PostgreSQL
Writer + Reader"]
        VALKEY["Valkey 9.0 Serverless"]
        VPC["VPC Flow Logs
2 VPCs"]
        CT["CloudTrail
Multi-region"]
    end

    %% Tier 1 - S3
    subgraph tier1["TIER 1 — S3: Long-Term Archive + Bulk Query"]
        direction TB
        BUCKET["aws-waf-logs-trinity-beast"]
        WAF_PREFIX["AWSLogs/.../WAFLogs/
JSON · gzipped · every 5 min"]
        ALB_PREFIX["alb/
space-delimited · gzipped · every 5 min"]
        CF_PREFIX["cloudfront/
tab-delimited · gzipped · every few min"]
        S3_PREFIX["s3-access/
space-delimited · best effort"]
        LIFECYCLE["Lifecycle: Standard 0-90d → Glacier IR 90-365d → Delete"]
    end

    %% Tier 2 - CloudWatch
    subgraph tier2["TIER 2 — CloudWatch: Real-Time + Instant Search"]
        direction TB
        CW_ECS["/aws/ecs/trinity-beast
4 containers · 1 group · UME self-identifies"]
        CW_LAMBDA["/aws/lambda/*
10 function log groups (us-east-2)"]
        CW_AURORA["/aws/rds/cluster/.../postgresql
Slow queries · Lock waits · Errors"]
        CW_VALKEY["/aws/elasticache/.../slow-log + engine-log
Slow commands · Engine events"]
        CW_VPC["/aws/vpc/trinity-beast-flowlogs
Both VPCs · Accept/Reject"]
        CW_CT["/aws/cloudtrail/trinity-beast
30-day window · S3 has full archive"]
        CW_INSIGHTS["Container Insights + RDSOSMetrics"]
        RETENTION["All groups: 90-day retention"]
    end

    %% Tier 3 - Application State
    subgraph tier3["TIER 3 — Valkey + Aurora: Application-Level State"]
        direction TB
        VK_OPS["autoops:actions:log · threats:daily
Autonomous actions · Threat summaries"]
        VK_HONEY["honeypot:log · honeypot:ip:*
Trap hits · Per-IP forensics"]
        VK_TX["tx:job:* · tx:history
Translation job progress"]
        VK_REPORT["report:text:* · docs:session:log
Daily reports · Session history"]
        AU_EVENTS["translation_job_events
usage_logs · support_tickets"]
        AU_CRON["cron.job_run_details
pg_cron execution history"]
    end

    %% Connections - Tier 1
    WAF -->|"full request logs"| WAF_PREFIX
    ALB -->|"access logs"| ALB_PREFIX
    CF -->|"standard logs"| CF_PREFIX
    S3SRC -->|"server access"| S3_PREFIX
    WAF_PREFIX --> BUCKET
    ALB_PREFIX --> BUCKET
    CF_PREFIX --> BUCKET
    S3_PREFIX --> BUCKET
    BUCKET --> LIFECYCLE

    %% Connections - Tier 2
    ECS -->|"stdout/stderr"| CW_ECS
    LAMBDA -->|"execution logs"| CW_LAMBDA
    AURORA -->|"pg logs export"| CW_AURORA
    VALKEY -->|"slow + engine"| CW_VALKEY
    VPC -->|"flow logs"| CW_VPC
    CT -->|"API events"| CW_CT
    ECS -->|"metrics"| CW_INSIGHTS
    AURORA -->|"OS metrics"| CW_INSIGHTS

    %% Connections - Tier 3
    ECS -->|"app writes"| VK_OPS
    ECS -->|"honeypot hits"| VK_HONEY
    ECS -->|"job state"| VK_TX
    ECS -->|"reports"| VK_REPORT
    ECS -->|"audit rows"| AU_EVENTS
    AURORA -->|"cron results"| AU_CRON

    %% Also CloudTrail to S3
    CT -->|"archive"| BUCKET

    %% Styling
    classDef s3Style fill:#1a365d,stroke:#60a5fa,color:#e2e8f0
    classDef cwStyle fill:#14532d,stroke:#10b981,color:#e2e8f0
    classDef appStyle fill:#4c1d95,stroke:#a78bfa,color:#e2e8f0
    classDef sourceStyle fill:#1e293b,stroke:#FF9900,color:#e2e8f0
    classDef bucketStyle fill:#0f172a,stroke:#FF9900,color:#FF9900

    class WAF_PREFIX,ALB_PREFIX,CF_PREFIX,S3_PREFIX,LIFECYCLE s3Style
    class CW_ECS,CW_LAMBDA,CW_AURORA,CW_VALKEY,CW_VPC,CW_CT,CW_INSIGHTS,RETENTION cwStyle
    class VK_OPS,VK_HONEY,VK_TX,VK_REPORT,AU_EVENTS,AU_CRON appStyle
    class ALB,CF,WAF,S3SRC,ECS,LAMBDA,AURORA,VALKEY,VPC,CT sourceStyle
    class BUCKET bucketStyle
      

Request Lifecycle — What Gets Logged Where

A single API request generates log entries across multiple tiers. This diagram traces a request from edge to database, showing where each log event lands:

sequenceDiagram
    participant Client as Client (Browser/SDK)
    participant CF as CloudFront
    participant WAF as WAF
    participant ALB as ALB
    participant ECS as ECS Container
    participant Aurora as Aurora
    participant Valkey as Valkey
    participant S3Log as S3 Logs Bucket

    Note over Client,S3Log: A single GET /price?asset=BTC request

    Client->>CF: HTTPS request
    Note right of CF: 📝 CloudFront log → S3
cloudfront/ prefix
(edge, IP, URI, cache hit/miss)

    CF->>WAF: Forward to origin
    Note right of WAF: 📝 WAF log → S3
AWSLogs/.../WAFLogs/
(IP, headers, rule match, ALLOW)

    WAF->>ALB: Passed rules
    Note right of ALB: 📝 ALB access log → S3
alb/ prefix
(latency, status, target IP)

    ALB->>ECS: Route to healthy target
    Note right of ECS: 📝 Container log → CloudWatch
/aws/ecs/trinity-beast
(UME: cluster_node, endpoint, api_key_id)

    ECS->>Valkey: Check price cache
    Note right of Valkey: (no log unless slow command)

    ECS->>Aurora: Query if cache miss
    Note right of Aurora: 📝 If >1s → PostgreSQL log
/aws/rds/.../postgresql

    ECS-->>Client: UME Response (200)

    Note over Client,S3Log: Result: 4-6 log entries for ONE request across 3-4 destinations
      

Design Decisions

3. S3 Unified Logs Bucket

3.1 Prefix Structure

3.2 What Each Log Contains

WAF Logs (richest security signal)

• Timestamp, action (ALLOW/BLOCK/COUNT), terminating rule ID
• Full HTTP request: client IP, URI, method, protocol, all headers
• Country of origin, HTTP version
• Rule group evaluations (which rules matched, which terminated)
• Labels applied by rules (for custom logic)

ALB Access Logs

• Request timestamp, ELB name, client IP:port, target IP:port
• Request processing time, target processing time, response processing time
• HTTP status code (ELB and target), bytes received/sent
• Full request line (method, URI, protocol)
• User-Agent, SSL cipher, SSL protocol, target group ARN
• Trace ID (for X-Ray correlation)

CloudFront Standard Logs

• Date, time, edge location (POP code), bytes served
• Client IP, HTTP method, host, URI stem, status code
• Referer, User-Agent, URI query string
• Result type (Hit/Miss/Error), request ID
• Protocol (HTTP/HTTPS), bytes from client, time-taken, time-to-first-byte

S3 Server Access Logs

• Bucket owner, bucket name, timestamp, remote IP
• Requester (IAM principal), request ID, operation (GET/PUT/LIST)
• Key (object path), request-URI, HTTP status
• Error code, bytes sent, object size, total time, turn-around time
• Referer, User-Agent, version ID

3.3 Bucket Policy

The bucket policy grants write access to five AWS service principals, verified live against get-bucket-policy:

3.4 Lifecycle Rules — How Long Each Log Is Kept

3.5 Delivery Freshness — Configuration Is Not Delivery

A feed's configuration says nothing about whether it is actually delivering — get-logging-configuration returning a destination is not evidence, only object recency is. Every S3-delivered feed here is checked for freshness against a per-feed threshold: WAF and ALB at 30 minutes, CloudFront at 360 minutes, S3 access at 720 minutes. WAF full-request logs specifically deliver every 5 minutes and are expected to show a newest object well inside that 30-minute window; anything older is a stale-feed signal, not a quiet-traffic one.

A second, region-locked bucket also exists: aws-waf-logs-trinity-beast-use1 in us-east-1, created as the future home for anything that must be delivered in that region (principally CloudFront WAF logs, if that logging is ever enabled). It is empty by design today and deliberately excluded from the freshness check for that reason — an empty prefix would otherwise false-alarm as stale.

4. CloudWatch Log Groups (Application Logs) & Alarms

All application-level logs live in CloudWatch for instant search. Every group has explicit 90-day retention — no infinite growth.

4.1 ECS Container Logs

The translate worker and sync job have separate log groups because they are different binaries (Python and Go respectively) with different lifecycles — they are not LPO containers. But the four LPO/LRS nodes are the same binary running in parallel, differentiated only by SERVER_TYPE and CLUSTER_NODE environment variables. UME makes separation unnecessary — search across all four at once and filter by the fields that matter.

flowchart LR
    subgraph containers["4 Containers — Same Binary, Same Log Group"]
        M["BeastMain
APP_REPORT_SERVER"]
        R["BeastMirror
APP_REPORT_SERVER"]
        L["BeastLRS
APP_REPORT_SERVER"]
        W["BeastWebhook
WEBHOOK_SERVER"]
    end

    subgraph loggroup["/aws/ecs/trinity-beast"]
        LOG["Unified Stream
All 4 containers interleaved"]
    end

    subgraph ume["UME Fields — Self-Identification"]
        CN["cluster_node:
BeastMain | BeastMirror | BeastLRS | BeastWebhook"]
        AP["agent_profile_arn:
tbi | webhook-engine | rhema | ..."]
        EP["endpoint:
/price | /reports | /admin/..."]
        IP["ip_address:
client source IP"]
    end

    M --> LOG
    R --> LOG
    L --> LOG
    W --> LOG
    LOG --> CN
    LOG --> AP
    LOG --> EP
    LOG --> IP

    classDef containerStyle fill:#1e293b,stroke:#FF9900,color:#e2e8f0
    classDef logStyle fill:#14532d,stroke:#10b981,color:#e2e8f0
    classDef umeStyle fill:#1e1b4b,stroke:#a78bfa,color:#e2e8f0

    class M,R,L,W containerStyle
    class LOG logStyle
    class CN,AP,EP,IP umeStyle
      

# Search ALL containers at once — UME self-reports which node answered
aws logs filter-log-events --log-group-name "/aws/ecs/trinity-beast" \
  --start-time $(date -v-1H +%s)000 --filter-pattern "BeastMain" \
  --region us-east-2 --query 'events[*].message' --output json

# Find errors across all 4 nodes simultaneously
aws logs filter-log-events --log-group-name "/aws/ecs/trinity-beast" \
  --start-time $(date -v-1H +%s)000 --filter-pattern "ERROR" \
  --region us-east-2 --query 'events[*].message' --output json

# Filter by agent actor (e.g., only webhook-engine responses)
aws logs filter-log-events --log-group-name "/aws/ecs/trinity-beast" \
  --start-time $(date -v-1H +%s)000 --filter-pattern "webhook-engine" \
  --region us-east-2 --query 'events[*].message' --output json

4.2 Lambda Function Logs

4.3 Infrastructure Logs

4.4 CloudWatch Alarms

Alarms are not a log group — each maintains its own state history — but they are the mechanism that turns everything logged above into an actual notification, so the current count belongs here. 37 alarms in us-east-2 (verified live via describe-alarms), plus 1 in us-east-1 for CloudFront-specific metrics (region-locked, since AWS/CloudFront metrics are only published in us-east-1) — 38 account-wide. The steady state reported by the security dashboard is 35/35 OK, which deliberately excludes the 2 ECS AlarmLow scale-in alarms from that count by convention (a scale-in alarm being in its "not currently scaling in" state is not itself a health signal worth surfacing).

All alarms publish to SNS topic tbi-ops-notifications, routed through the tbi-ops-notify Lambda for formatted email. See CloudWatch Dashboard & Alarm Notifications for the full per-alarm inventory, thresholds, and response playbook — this section exists here only so the alarm count travels with the rest of the observability inventory rather than living in one document alone.

5. Valkey Operational Logs

Beyond CloudWatch export, the application writes structured operational data directly to Valkey as keys. These function as real-time logs for the AutoOps system.

6. Aurora Database Logs

6.1 PostgreSQL Server Logs (CloudWatch)

Enabled June 4, 2026. Aurora exports slow queries and errors to CloudWatch automatically. The instance parameter group controls what gets logged:

6.2 pg_cron Execution History

Scheduled job execution history lives in Aurora itself — not CloudWatch. Query via:

SELECT r.jobid, j.jobname, r.start_time, r.end_time, r.status, r.return_message
FROM cron.job_run_details r JOIN cron.job j ON r.jobid = j.jobid
ORDER BY r.start_time DESC LIMIT 20;

6.3 Application Audit Tables

6.4 webhook_delivery_log — read this before querying it

This is a log store that lives in Aurora rather than CloudWatch, and it behaves like a high-volume log, not a normal table. At ~7.5M rows and ~2.2 GB (91% of the 2.4 GB database, measured live — the exact figures move nightly with the 33-day purge), it is the large majority of the entire database.

-- ✅ Bounded — newest 200 rows for one subscription, off the composite index
SELECT id, created_at, asset, status, delivery_method
FROM webhook_delivery_log
WHERE subscription_id = 'sub_...'
ORDER BY created_at DESC
LIMIT 200;

-- ✅ Bounded — an indexed time range instead of a row cap
SELECT count(*) FILTER (WHERE status = 'failed') AS failed
FROM webhook_delivery_log
WHERE subscription_id = 'sub_...'
  AND created_at >= now() - INTERVAL '24 hours';

-- ❌ Never — no LIMIT and no time range, will scan the whole 9M-row table
-- SELECT * FROM webhook_delivery_log WHERE subscription_id = 'sub_...' ORDER BY created_at DESC;

Index to use: idx_webhook_delivery_sub_created (subscription_id, created_at DESC). For a full row estimate without scanning, use SELECT reltuples FROM pg_class WHERE relname = 'webhook_delivery_log' rather than COUNT(*).

Retention & the purge procedure

Purged nightly by pg_cron job tbi-purge-webhook-delivery-log (2:30 AM daily) at 33-day retention, via a batched stored procedure — not a bare DELETE:

CALL purge_webhook_delivery_log(p_retain_days := 33, p_batch_size := 30000, p_max_batches := 300);

The procedure commits every 30,000 rows so a retention cut never becomes one enormous transaction, one large WAL burst, and one dead-tuple cliff. A DO block cannot do this — it runs inside a single transaction and cannot COMMIT mid-loop; only a procedure can (PostgreSQL 11+; this cluster runs 17.7).

The rollup — refreshed every 15 minutes by pg_cron, plus nightly by BeastReconciler

webhook_delivery_daily is refreshed by refresh_webhook_delivery_rollup() on two independent schedules: pg_cron job tbi-webhook-delivery-rollup runs every 15 minutes (trailing 2-day window, all subscriptions), and BeastReconciler's nightly sync job runs it again on a trailing 3-day window as a wider self-healing net with no HTTP deadline. A dashboard "Refresh Now" button is a today-only on-demand convenience layered on top — it is not the only path and was never the sole refresh mechanism.

The budget you are working inside is 5 seconds, not the database

Aurora's statement_timeout is 0 — unbounded, no server-side cutoff. The real ceiling for anything served to a browser is http_write_timeout_seconds = 5 (an application parameter). Dashboard handlers run on the request context, so a query that runs past 5 seconds gets its context cancelled and dies — not because the database refused it.

7. Retention & Lifecycle Policy

8. Querying & Searching Logs

8.1 CloudWatch — Instant Search

# Find errors in any Lambda (last hour)
aws logs filter-log-events --log-group-name "/aws/lambda/tbi-ops-notify" \
  --start-time $(date -v-1H +%s)000 --filter-pattern "ERROR" \
  --region us-east-2 --query 'events[*].message' --output json

# Slow Aurora queries (last 24h)
aws logs filter-log-events \
  --log-group-name "/aws/rds/cluster/trinity-beast-aurora-cluster/postgresql" \
  --start-time $(date -v-24H +%s)000 --filter-pattern "duration" \
  --region us-east-2 --query 'events[*].message' --output json

# ECS container output for sync job (latest run)
STREAM=$(aws logs describe-log-streams --log-group-name "/aws/ecs/trinity-beast-sync" \
  --order-by LastEventTime --descending --limit 1 \
  --region us-east-2 --query 'logStreams[0].logStreamName' --output text)
aws logs get-log-events --log-group-name "/aws/ecs/trinity-beast-sync" \
  --log-stream-name "$STREAM" --limit 30 \
  --region us-east-2 --query 'events[*].message' --output json

8.2 Aurora — Bounded Queries Only

webhook_delivery_log is ~7.5M rows and the large majority of the database. Every query against it must be bounded by rows or by an indexed time range — see §6.4 for the full explanation, the index to use, and worked bounded/unbounded examples.

-- Newest 200 rows for one subscription (bounded by LIMIT, off idx_webhook_delivery_sub_created)
SELECT id, created_at, asset, status, delivery_method
FROM webhook_delivery_log
WHERE subscription_id = 'sub_...'
ORDER BY created_at DESC
LIMIT 200;

-- Row estimate without a full-table scan
SELECT reltuples FROM pg_class WHERE relname = 'webhook_delivery_log';

-- pg_cron execution history (job run detail)
SELECT r.jobid, j.jobname, r.start_time, r.end_time, r.status, r.return_message
FROM cron.job_run_details r JOIN cron.job j ON r.jobid = j.jobid
ORDER BY r.start_time DESC LIMIT 20;

8.3 S3 — Download & Analyze

# WAF: Get recent blocked requests
latest=$(aws s3 ls "s3://aws-waf-logs-trinity-beast/AWSLogs/211998422884/WAFLogs/\
us-east-2/trinity-beast-api-waf/$(date -u +%Y/%m/%d/%H)/" --region us-east-2 | tail -1 | awk '{print $4}')
aws s3 cp "s3://aws-waf-logs-trinity-beast/AWSLogs/211998422884/WAFLogs/\
us-east-2/trinity-beast-api-waf/$(date -u +%Y/%m/%d/%H)/$latest" - \
  --region us-east-2 | gunzip | jq -c 'select(.action=="BLOCK") | {ip: .httpRequest.clientIp, uri: .httpRequest.uri, rule: .terminatingRuleId}'

# ALB: Check latency for a specific endpoint
aws s3 cp "s3://aws-waf-logs-trinity-beast/alb/AWSLogs/211998422884/\
elasticloadbalancing/us-east-2/$(date +%Y/%m/%d)/<file>.log.gz" - \
  --region us-east-2 | gunzip | awk '{print $6, $13}' | grep "/price"

# CloudFront: Top requested URIs
aws s3 cp "s3://aws-waf-logs-trinity-beast/cloudfront/<file>.gz" - \
  --region us-east-2 | gunzip | awk '{print $8}' | sort | uniq -c | sort -rn | head -20

8.4 Valkey — Operational State

# Recent autonomous actions
curl -s -X POST -H "X-Admin-Key: $ADMIN_KEY" -H "Content-Type: application/json" \
  -d '{"command":"ZREVRANGEBYSCORE autoops:actions:log +inf -inf LIMIT 0 10"}' \
  "$LPO_BASE/admin/valkey"

# Today's threat assessment
curl -s -X POST -H "X-Admin-Key: $ADMIN_KEY" -H "Content-Type: application/json" \
  -d '{"command":"GET autoops:threats:daily"}' "$LPO_BASE/admin/valkey"

# Honeypot activity (last 10 hits)
curl -s -X POST -H "X-Admin-Key: $ADMIN_KEY" -H "Content-Type: application/json" \
  -d '{"command":"ZREVRANGEBYSCORE honeypot:log +inf -inf LIMIT 0 10"}' \
  "$LPO_BASE/admin/valkey"

9. Quick Reference — Where Do I Look?

9.1 Translation Log Codes

The October 2026 translation release gave every money and start decision its own searchable code, so you can follow a single quote from fetch to refund with one filter. The codes go to the existing container log groups; nothing new was added to watch. LPO codes are in /aws/ecs/trinity-beast and worker codes in /ecs/tbi-translate-worker (section 4). Anything marked + SNS also reaches the ops topic, because it means money and state disagree and a person should look. The Aurora side of the same story is translation_job_events.

A quick search: filter /aws/ecs/trinity-beast for the quote id to see every LPO decision about it in order, then filter /ecs/tbi-translate-worker for its job id to see each language run.

10. Cost Analysis

11. Athena — SQL Analytics Across All Logs

Amazon Athena is a serverless SQL engine that queries data directly in S3 — no database to provision, no data to move, no ETL. Point it at the log files, define a schema, and run standard SQL. Deployed June 4, 2026.

11.1 Tables

11.2 Saved Queries (Ready to Run)

Nine pre-built queries are saved in the trinity-beast-analytics workgroup. Run them from the Athena console or CLI — just click "Saved queries" in the console.

11.3 Example Queries

Security — Who got blocked and why?

-- Top 30 blocked IPs with the rules that caught them
SELECT httprequest.clientip AS ip,
       httprequest.country AS country,
       count(*) AS blocks,
       array_agg(DISTINCT terminatingruleid) AS rules
FROM tbi_logs.waf_logs
WHERE action = 'BLOCK'
  AND year = '2026' AND month = '06' AND day = '04'
GROUP BY httprequest.clientip, httprequest.country
ORDER BY blocks DESC
LIMIT 30;

Performance — P95 latency by endpoint

-- API endpoint latency percentiles from ALB logs
SELECT request_url,
       count(*) AS requests,
       approx_percentile(target_processing_time, 0.5) AS p50,
       approx_percentile(target_processing_time, 0.95) AS p95,
       approx_percentile(target_processing_time, 0.99) AS p99
FROM tbi_logs.alb_logs
WHERE year = '2026' AND month = '06' AND day = '04'
  AND request_url LIKE '%/price%'
GROUP BY request_url
ORDER BY requests DESC;

Cross-Source — Reconnaissance detection

-- IPs that hit both the API AND the website (probing behavior)
SELECT w.ip, w.waf_requests, w.blocks, cf.cf_requests
FROM (
  SELECT httprequest.clientip AS ip,
         count(*) AS waf_requests,
         sum(CASE WHEN action='BLOCK' THEN 1 ELSE 0 END) AS blocks
  FROM tbi_logs.waf_logs
  WHERE year = '2026' AND month = '06' AND day = '04'
  GROUP BY httprequest.clientip
) w
JOIN (
  SELECT c_ip AS ip, count(*) AS cf_requests
  FROM tbi_logs.cloudfront_logs
  WHERE date = DATE '2026-06-04'
  GROUP BY c_ip
) cf ON w.ip = cf.ip
ORDER BY w.blocks DESC, w.waf_requests DESC
LIMIT 20;

Forensics — Full trace of a suspicious IP

-- Everything a specific IP did across all API endpoints today
SELECT from_unixtime(timestamp/1000) AS time,
       action,
       httprequest.uri,
       httprequest.httpmethod,
       terminatingruleid,
       httprequest.country
FROM tbi_logs.waf_logs
WHERE httprequest.clientip = '45.148.10.51'
  AND year = '2026' AND month = '06'
ORDER BY timestamp DESC
LIMIT 100;

Infrastructure — What AWS APIs are being called?

-- Top AWS API calls in our account today (spot automation vs manual)
SELECT eventsource, eventname, sourceipaddress, count(*) AS calls
FROM tbi_logs.cloudtrail_logs
WHERE account = '211998422884' AND region = 'us-east-2'
  AND year = '2026' AND month = '06' AND day = '04'
GROUP BY eventsource, eventname, sourceipaddress
ORDER BY calls DESC
LIMIT 30;

11.4 How to Use

# Run a query
QID=$(aws athena start-query-execution \
  --query-string "SELECT action, count(*) FROM tbi_logs.waf_logs WHERE year='2026' AND month='06' AND day='04' GROUP BY action" \
  --work-group trinity-beast-analytics \
  --region us-east-2 --query 'QueryExecutionId' --output text)

# Wait for completion (typically 1-3 seconds)
aws athena get-query-execution --query-execution-id $QID \
  --region us-east-2 --query 'QueryExecution.Status.State' --output text

# Get results
aws athena get-query-results --query-execution-id $QID \
  --region us-east-2 --output json | jq '.ResultSet.Rows[] | .Data | map(.VarCharValue) | join(" | ")'

11.5 Cost & Limits

11.6 Pattern Recognition — What Athena Reveals

The real power isn't in individual queries — it's in combining signals across log sources to surface patterns invisible to any single source. These are the questions Athena answers that nothing else in the stack can:

11.7 Future Enhancements

• S3 Tables (Apache Iceberg): If query volume or data size grows, migrate table definitions to S3 Tables for automatic compaction, 3× faster queries, and time-travel capability. Same SQL, same Athena — just faster underneath.
• CloudWatch Logs Insights: Already available on all CloudWatch groups — structured query language for real-time pattern analysis on application logs.
• Athena Views: Create pre-joined views (WAF + CloudFront by IP, CloudTrail errors by service) for one-click analysis.
• Scheduled Queries: Athena can run queries on a schedule and output to S3 — automated daily security reports without any Lambda code.
• QuickSight Integration: If visual dashboards are needed, QuickSight connects directly to Athena for interactive BI over all log data.