Analyze Cloudflare Worker traffic

git clone https://github.com/scopedb/cloudflare-tail-worker.git
cd cloudflare-tail-worker
pnpm install

name: cloudflare_worker_events
comment: Cloudflare Worker invocations, console logs, and uncaught exceptions
columns:
  - name: event_id
    data_type: string
    comment: Unique row identifier
  - name: occurred_at
    data_type: timestamp
    comment: Event timestamp in UTC
  - name: service
    data_type: string
    comment: Producer Worker name (scriptName)
  - name: event_type
    data_type: string
    comment: worker.invocation, worker.log, or worker.exception
  - name: outcome
    data_type: string
    comment: Worker execution outcome (ok, exception, exceededCpu, etc.)
  - name: level
    data_type: string
    comment: Normalized severity (debug, info, warn, error)
  - name: method
    data_type: string
    comment: HTTP request method when triggered by fetch
  - name: host
    data_type: string
    comment: Request URL hostname
  - name: path
    data_type: string
    comment: Request URL pathname without query parameters
  - name: status
    data_type: int
    comment: HTTP response status code
  - name: ray_id
    data_type: string
    comment: Cloudflare Ray ID (cf-ray)
  - name: message
    data_type: string
    comment: Human-readable event summary, log line, or exception message
  - name: attributes
    data_type: object
    comment: Structured invocation timings, edge metadata, log arguments, or stack trace
cluster_by:
  - service
  - occurred_at

scope catalog apply --table scopedb.public.cloudflare_worker_events -f catalog/table.yaml --yes
scope catalog apply --view scopedb.public.v_cloudflare_requests -f catalog/v_cloudflare_requests.yaml --yes
scope catalog apply --view scopedb.public.v_cloudflare_pageviews -f catalog/v_cloudflare_pageviews.yaml --yes
scope catalog apply --view scopedb.public.v_cloudflare_errors -f catalog/v_cloudflare_errors.yaml --yes
scope catalog apply --view scopedb.public.v_cloudflare_ai_bots -f catalog/v_cloudflare_ai_bots.yaml --yes

pnpm exec wrangler secret put SCOPEDB_ENDPOINT
pnpm exec wrangler secret put SCOPEDB_API_KEY
pnpm run deploy

tail_consumers = [{ service = "scopedb-tail" }]

FROM scopedb.public.v_cloudflare_requests
WHERE occurred_at >= NOW() - '15m'::interval
SELECT occurred_at, service, method, path, status, wall_time_ms
ORDER BY occurred_at DESC
LIMIT 5

export default createTailWorker({
  allowedQueryParams: ["utm_source", "utm_medium", "utm_campaign", "plan"],
  redactQueryValue: (key, value) =>
    key === "plan" && !["free", "team"].includes(value) ? null : value,
  redactPath: (pathname) => pathname.replace(/^\/reset\/[^/]+/, "/reset/:token"),
});

FROM scopedb.public.cloudflare_worker_events
WHERE event_type = 'worker.invocation'
SELECT
    occurred_at,
    service,
    method,
    host,
    path,
    status,
    attributes['continent']::string AS continent,
    attributes['country']::string AS country,
    attributes['region']::string AS region,
    attributes['city']::string AS city,
    attributes['timezone']::string AS timezone,
    attributes['colo']::string AS colo,
    attributes['as_organization']::string AS as_organization,
    if(
        attributes['referer_host']::string = host
            OR attributes['referer_host']::string = concat('www.', host)
            OR concat('www.', attributes['referer_host']::string) = host,
        NULL,
        attributes['referer_host']::string
    ) AS referer_host,
    if(
        attributes['referer_host']::string = host
            OR attributes['referer_host']::string = concat('www.', host)
            OR concat('www.', attributes['referer_host']::string) = host,
        NULL,
        attributes['referer_path']::string
    ) AS referer_path,
    if(attributes['query'] IS NULL, attributes['utm_source']::string, attributes['query']['utm_source'][0]::string) AS utm_source,
    if(attributes['query'] IS NULL, attributes['utm_medium']::string, attributes['query']['utm_medium'][0]::string) AS utm_medium,
    if(attributes['query'] IS NULL, attributes['utm_campaign']::string, attributes['query']['utm_campaign'][0]::string) AS utm_campaign,
    if(attributes['query'] IS NULL, attributes['utm_term']::string, attributes['query']['utm_term'][0]::string) AS utm_term,
    if(attributes['query'] IS NULL, attributes['utm_content']::string, attributes['query']['utm_content'][0]::string) AS utm_content,
    if(attributes['query'] IS NULL, attributes['ref']::string, attributes['query']['ref'][0]::string) AS ref,
    attributes['query'] AS query,
    attributes['user_agent']::string AS user_agent,
    attributes['sec_fetch_mode']::string AS sec_fetch_mode,
    attributes['sec_fetch_dest']::string AS sec_fetch_dest,
    attributes['is_prefetch']::boolean AS is_prefetch,
    attributes['is_rsc']::boolean AS is_rsc,
    attributes['verified_bot_category']::string AS verified_bot_category,
    attributes['wall_time_ms']::float AS wall_time_ms,
    attributes['cpu_time_ms']::float AS cpu_time_ms
WHERE method = 'GET'
  AND status = 200
  AND path IS NOT NULL
  AND sec_fetch_mode = 'navigate'
  AND sec_fetch_dest = 'document'
  AND (is_prefetch IS NULL OR NOT is_prefetch)
  AND (is_rsc IS NULL OR NOT is_rsc)
  AND verified_bot_category IS NULL
  AND NOT starts_with(path, '/api/')
  AND NOT starts_with(path, '/_next/')
  AND NOT starts_with(path, '/_app/')
  AND NOT starts_with(path, '/.')
  AND NOT ends_with(path, '/__data.json')
  AND NOT regexp_like(path, regex => '(?i)[.](md|txt|xml|ico|svg|png|jpg|jpeg|webp|gif|css|js|map|json|woff2?)$')
  AND (user_agent IS NULL OR NOT regexp_like(user_agent, regex => '(?i)(bot|crawler|spider|slurp|facebookexternalhit|feedbin|pingwatch|uptime|statuscake|cortex-xpanse|paloaltonetworks|censys|shodan|zgrab|curl|wget|python|go-http-client|axios|node-fetch|undici|okhttp|headless|Claude-User|ChatGPT-User|Perplexity-User|Anthropic|cohere-ai|Cursor|Codex)'))

FROM scopedb.public.v_cloudflare_pageviews
WHERE service = 'scopedb-site'
  AND occurred_at >= '2026-11-08T14:00:00Z'::timestamp
  AND occurred_at < '2026-11-09T14:00:00Z'::timestamp
GROUP BY path
AGGREGATE count() AS document_requests
ORDER BY document_requests DESC, path ASC
LIMIT 10

FROM scopedb.public.v_cloudflare_pageviews
WHERE service = 'scopedb-site'
  AND occurred_at >= '2026-11-08T14:00:00Z'::timestamp
  AND occurred_at < '2026-11-09T14:00:00Z'::timestamp
  AND referer_host IS NOT NULL
GROUP BY referer_host, utm_source
AGGREGATE count() AS document_requests
ORDER BY document_requests DESC, referer_host ASC, utm_source ASC
LIMIT 10

FROM scopedb.public.v_cloudflare_pageviews
WHERE service = 'scopedb-site'
  AND occurred_at >= '2026-11-08T14:00:00Z'::timestamp
  AND occurred_at < '2026-11-09T14:00:00Z'::timestamp
  AND continent IS NOT NULL
GROUP BY continent
AGGREGATE count() AS document_requests
ORDER BY document_requests DESC, continent ASC

FROM scopedb.public.v_cloudflare_requests
WHERE service = 'app-console-production'
  AND occurred_at >= NOW() - '24h'::interval
  AND query['plan'][0]::string IS NOT NULL
GROUP BY query['plan'][0]::string AS plan
AGGREGATE count() AS requests
ORDER BY requests DESC, plan ASC

FROM scopedb.public.v_cloudflare_ai_bots
WHERE service = 'scopedb-site'
  AND occurred_at >= '2026-11-08T14:00:00Z'::timestamp
  AND occurred_at < '2026-11-09T14:00:00Z'::timestamp
GROUP BY is_verified_ai_bot, is_ai_user_agent, is_markdown_request
AGGREGATE count() AS requests
ORDER BY requests DESC

FROM scopedb.public.cloudflare_worker_events
WHERE attributes['invocation_id']::string = '<invocation_id>'
SELECT occurred_at, event_type, level, status, path, message, attributes
ORDER BY occurred_at ASC

Use $scopedb to analyze Cloudflare traffic from scopedb.public.v_cloudflare_pageviews and scopedb.public.v_cloudflare_ai_bots where service = 'scopedb-site', from 2026-11-08T14:00:00Z to 2026-11-09T14:00:00Z, and render @scopedb/scopeview interactive HTML charts for:
1. Browser document requests over time in 1-hour buckets using floor(occurred_at, n => 1, unit => 'hour') (line chart)
2. Top 10 document paths ordered by request count descending (top-list chart)
3. Top external referrers and utm_source values (top-list chart)
4. Request continents from Cloudflare GeoIP metadata (top-list chart)
5. Request counts grouped by is_verified_ai_bot, is_ai_user_agent, and is_markdown_request (top-list chart)

{
  "version": 1,
  "title": "scopedb.io — Top 10 document paths",
  "description": "Source: scopedb.public.v_cloudflare_pageviews (service = scopedb-site, 2026-11-08 14:00 UTC to 2026-11-09 14:00 UTC, LIMIT 10).",
  "data": {
    "fields": [
      { "name": "path", "data_type": "string" },
      { "name": "document_requests", "data_type": "number" }
    ],
    "rows": [
      ["/", "32"],
      ["/blog/raft-agent-observability", "11"],
      ["/docs", "6"],
      ["/docs/reference/commands/stmt-query", "5"],
      ["/blog/manage-observability-data-in-petabytes", "4"],
      ["/contact", "4"],
      ["/blog", "3"],
      ["/blog/schema-on-the-fly", "3"],
      ["/blog/scopedb-cloud-public-preview", "3"],
      ["/blog/scopeql-origins", "3"]
    ]
  },
  "spec": {
    "visualization": "top-list",
    "encoding": {
      "category": "path",
      "value": "document_requests"
    }
  },
  "semantics": {
    "provenance": {
      "kind": "rows",
      "scope": "returned"
    }
  }
}

npx @scopedb/scopeview render top-pages.json -o top-pages.html --theme system