abuse-detection

Requirements

• Tinybird CLI (tb): Must be authenticated
• Run queries from enter.pollinations.ai/observability/ (has .tinyb config)
• Cloudflare D1 access for banning users (via wrangler)

Tinybird query pattern:

cd enter.pollinations.ai/observability
tb --cloud sql "SELECT ... FROM generation_event ..."

Workspace: This skill is prod-only. The .tinyb in observability/ points to the pollinations_enter workspace (prod traffic). Staging traffic lives in pollinations_enter_staging and has no real abuse signal — don't waste time analyzing it. To pin a query to staging anyway (e.g. testing a new scoring query), set TB_TOKEN=<staging_admin_token> for that one command.

Quoting: Use double quotes for the SQL string. Use single quotes inside SQL. Avoid != with

#x27;...' shell quoting (escaping issues) — prefer NOT IN ('undefined', '') instead.

tb CLI caps at 100 rows. For large result sets, use the HTTP API:

TB_TOKEN=$(python3 -c "import json; print(json.load(open('.tinyb'))['token'])")
curl -s "https://api.europe-west2.gcp.tinybird.co/v0/sql" \
  -H "Authorization: Bearer $TB_TOKEN" \
  --data-urlencode "q=SELECT ... FORMAT JSONCompact" | python3 -c "import json,sys; ..."

---

Composite Abuse Score (0-100)

Six signals, each weighted independently:

Score interpretation:

---

Quick Ban Criteria (High Confidence)

For accounts generating massive failing traffic with no paid pack usage, a simpler signal is sufficient:

zero pack spend + 95%+ error rate + 1000+ requests/week

This catches bot farm accounts that are already rate-limited (no spendable balance) but still hammering the API with failing requests. These accounts waste server resources with zero legitimate usage.

Query:

SELECT user_id
FROM (
    SELECT
        g.user_id,
        count() as total_reqs,
        round(sumIf(g.total_price, g.selected_meter_slug IN ('v1:meter:pack', 'local:pack')), 4) as pack_spend,
        countIf(g.response_status >= 400) * 100.0 / count() as err_pct
    FROM generation_event g
    WHERE g.start_time >= now() - INTERVAL 7 DAY
        AND g.user_id NOT IN ('undefined', '')
    GROUP BY g.user_id
    HAVING total_reqs >= 1000
)
WHERE pack_spend = 0 AND err_pct >= 95

Note: tb --cloud sql caps output at 100 rows. For large result sets, use the Tinybird HTTP API with FORMAT JSONCompact.

---

Key Queries

1. Full Abuse Scoring Query

Returns all users with abuse score, sorted by score descending. The spend signal uses selected_meter_slug to distinguish paid pack consumption from the other active balance bucket.

SELECT
    user_id, github_username, email,
    total_reqs, pack_spend, total_spend, max_ip_cluster, distinct_ips,
    round(err_pct, 1) as err_pct, round(sex_pct, 1) as sex_pct,
    abuse_score
FROM (
    SELECT
        g.user_id, u.github_username, u.email,
        count() as total_reqs,
        round(sumIf(g.total_price, g.selected_meter_slug IN ('v1:meter:pack', 'local:pack')), 4) as pack_spend,
        round(sum(g.total_price), 4) as total_spend,
        max(coalesce(ips.ip_cluster_size, 0)) as max_ip_cluster,
        countDistinct(g.ip_hash) as distinct_ips,
        countIf(g.response_status >= 400) * 100.0 / count() as err_pct,
        countIf(g.moderation_prompt_sexual_severity NOT IN ('safe', '')) * 100.0 / count() as sex_pct,
        round(
            least(30, max(coalesce(ips.ip_cluster_size, 0)) * 0.15) +
            multiIf(
                splitByChar('@', u.email)[2] = 'proton.me' AND pack_spend = 0, 15,
                splitByChar('@', u.email)[2] = 'hotmail.com' AND pack_spend = 0, 12,
                splitByChar('@', u.email)[2] = 'outlook.com' AND pack_spend = 0, 10,
                0) +
            if(pack_spend = 0, 15, 0) +
            if(countIf(g.response_status >= 400) * 100.0 / count() >= 95, 15,
               if(countIf(g.response_status >= 400) * 100.0 / count() >= 70, 10, 0)) +
            if(countIf(g.moderation_prompt_sexual_severity NOT IN ('safe', '')) * 100.0 / count() >= 90, 15,
               if(countIf(g.moderation_prompt_sexual_severity NOT IN ('safe', '')) * 100.0 / count() >= 50, 8, 0)) +
            if(countDistinct(g.ip_hash) >= 50, 10, if(countDistinct(g.ip_hash) >= 20, 5, 0))
        , 0) as abuse_score
    FROM generation_event g
    LEFT JOIN d1_user u ON g.user_id = u.id
        AND u.synced_at = (SELECT max(synced_at) FROM d1_user)
    LEFT JOIN (
        SELECT ip_hash, count(DISTINCT user_id) as ip_cluster_size
        FROM generation_event
        WHERE start_time >= now() - INTERVAL 7 DAY
            AND ip_hash NOT IN ('undefined', '')
            AND user_id NOT IN ('undefined', '')
        GROUP BY ip_hash
    ) ips ON g.ip_hash = ips.ip_hash
    WHERE g.start_time >= now() - INTERVAL 7 DAY
        AND g.user_id NOT IN ('undefined', '')
    GROUP BY g.user_id, u.github_username, u.email
    HAVING total_reqs >= 5
)
WHERE abuse_score >= 40
ORDER BY abuse_score DESC, total_reqs DESC
LIMIT 100

2. IP Cluster Analysis

Find IPs shared by many users (bot farm detection):

SELECT
    ip_subnet, ip_hash,
    count(DISTINCT user_id) as unique_users,
    count() as total_requests,
    dateDiff('minute', min(start_time), max(start_time)) as span_min
FROM generation_event
WHERE start_time >= now() - INTERVAL 7 DAY
    AND ip_hash NOT IN ('undefined', '')
    AND user_id NOT IN ('undefined', '')
GROUP BY ip_hash, ip_subnet
HAVING unique_users >= 10
ORDER BY unique_users DESC
LIMIT 30

3. User Details for an IP Cluster

SELECT DISTINCT
    g.user_id, u.github_username, u.email,
    sumIf(g.total_price, g.selected_meter_slug IN ('v1:meter:pack', 'local:pack')) as pack_spend,
    sum(g.total_price) as total_spend
FROM generation_event g
LEFT JOIN d1_user u ON g.user_id = u.id
    AND u.synced_at = (SELECT max(synced_at) FROM d1_user)
WHERE g.start_time >= now() - INTERVAL 7 DAY
    AND g.ip_hash = '<IP_HASH_HERE>'
    AND g.user_id NOT IN ('undefined', '')
GROUP BY g.user_id, u.github_username, u.email
ORDER BY pack_spend DESC

4. Score Distribution (Overview)

SELECT
    multiIf(abuse_score >= 90, '90-100 definite',
            abuse_score >= 70, '70-89 likely',
            abuse_score >= 40, '40-69 suspicious',
            abuse_score >= 10, '10-39 low_risk',
            '0-9 clean') as bucket,
    count() as users,
    round(sum(total_spend), 2) as spend,
    sum(total_reqs) as requests
FROM ( /* ... full scoring subquery from #1 ... */ )
GROUP BY bucket
ORDER BY bucket DESC

5. Extract User IDs for Banning

SELECT user_id
FROM ( /* ... full scoring subquery from #1 ... */ )
WHERE abuse_score >= 90

---

Known False Positive Patterns

Always check before banning:

Safe to ban (high confidence):

• Zero pack spend + 95%+ error rate + 1000+ requests/week
• Score >= 90 + hotmail/outlook random email + zero pack spend
• Score >= 70 + IP cluster >= 100 + zero pack spend
• Disposable email (hotmail/outlook/proton/qq/mail.ru/vk.com/anonaddy/anondrop/rambler/gmx/yandex) + error >= 95% + $0 pack spend
• 100% error rate + $0 pack spend (zero successful requests ever)
• 99%+ error rate + 1000+ requests/week (hammering)
• Multi-account cluster (same email root, e.g. reksely/notreksely/rekselicha)

Needs review:

• Any account with pack spend > $2 (could be real customer)
• Accounts on Cloudflare IPs (2a06:98c0:*)
• Accounts with real-looking Gmail addresses
• Accounts with 90-95% error rate but some successful pack spend (may be bad integration, not abuse)

---

Banning Users

How Banning Works

The ban system uses Better Auth fields on the user table in Cloudflare D1:

Enforcement (src/middleware/auth.ts):

• assertNotBanned() runs on every authenticated request (session + API key)
• If banned = 1 and not expired → HTTP 403 with ban reason
• If ban_expires is set and has passed → ban is automatically lifted

There is no admin API for banning — use wrangler d1 execute directly.

Ban Commands

# Single user ban (from enter.pollinations.ai/ directory)
npx wrangler d1 execute production-pollinations-enter-db --remote \
  --command "UPDATE user SET banned = 1, ban_reason = 'Bot farm abuse' WHERE id = '<USER_ID>'"

# Batch ban (from a file of user IDs, one per line)
IDS=$(cat user_ids_to_ban.txt | sed "s/^/'/;s/$/'/" | paste -sd, -)
npx wrangler d1 execute production-pollinations-enter-db --remote \
  --command "UPDATE user SET banned = 1, ban_reason = 'Automated: bot farm abuse' WHERE id IN ($IDS)"

# Unban a user (if false positive)
npx wrangler d1 execute production-pollinations-enter-db --remote \
  --command "UPDATE user SET banned = 0, ban_reason = NULL WHERE id = '<USER_ID>'"

# Temporary ban (expires after 7 days)
npx wrangler d1 execute production-pollinations-enter-db --remote \
  --command "UPDATE user SET banned = 1, ban_reason = 'Temporary: rate abuse', ban_expires = $(date -v+7d +%s)000 WHERE id = '<USER_ID>'"

D1 database names:

• Production: production-pollinations-enter-db
• Staging: staging-pollinations-enter-db
• Development: development-pollinations-enter-db

---

Abuse Profile: Chinese Bot Farm (March 2026)

Characteristics discovered:

• Scale: 234+ accounts, 6.4M requests/week (23% of all traffic)
• IPs: Chinese residential ISPs (China Telecom, Unicom, Mobile) — 60+ distinct IPs per user across 50+ subnets
• Emails: Random strings at hotmail.com/outlook.com/proton.me (e.g., lhanbqkf6005@hotmail.com)
• Usernames: Gibberish GitHub usernames (e.g., bomteupted-bsfo, jwolfwersenmroom)
• Behavior: 100% image generation, 100% error rate, 100% NSFW moderation flags
• Spend: $0 pack spend (small non-pack balance usage at most)
• Account age: All created within days of each other (Feb 2026)

---

Data Sources

IP implementation (src/middleware/track.ts):

• ip_hash: Salted SHA-256 of full IP (irreversible)
• ip_subnet: Truncated to /24 (IPv4) or /48 (IPv6)
• Source: cf-connecting-ip header

---

Action Log

---

Notes

• IP coverage: Started 2026-03-06, ~19% user coverage initially. Re-run analysis as coverage grows.
• d1_user sync lag: The d1_user table in Tinybird syncs periodically (not real-time). After banning on D1, Tinybird data is stale — verify actions on D1 directly.
• Pack spend is the strongest payment signal for abuse review. Total generation spend can include non-pack balance-bucket usage, so filter on pack_spend to catch accounts with no real payment.
• Gibberish suffix usernames: Bot farms use GitHub usernames with suffixes like -boop, -a11y, -max, -sudo, -cmd, -stack, -pixel, -dot, -beep, -commits, -ops, -dotcom, -lang, -bit. These are auto-generated.
• Consider adding: account age signal, GitHub account age, user-agent clustering