abuse-detection

name: abuse-detection description: Detect and analyze abusive accounts on Pollinations. IP clustering, multi-signal scoring, ban recommendations. Use when investigating abuse, bot farms, or suspicious usage patterns.

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 ..."

Quoting: Use double quotes for the SQL string. Use single quotes inside SQL. Avoid != with $'...' 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 microbe-tier users generating massive failing traffic, a simpler signal is sufficient:

microbe tier + 95%+ error rate + 1000+ requests/week

This catches bot farm accounts that are already rate-limited (microbe = 0 pollen) 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, u.tier,
        count() as total_reqs,
        countIf(g.response_status >= 400) * 100.0 / count() as err_pct
    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.user_id NOT IN ('undefined', '')
    GROUP BY g.user_id, u.tier
    HAVING total_reqs >= 1000
)
WHERE tier = 'microbe' 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.

Spore Tier Abuse Finder

Finds spore users to demote. Returns candidates with IP cluster size for classification.

SELECT g.user_id, u.github_username, u.email, u.tier,
    count() as total_reqs,
    round(sum(g.total_price), 4) as total_spend,
    round(countIf(g.response_status >= 400) * 100.0 / count(), 1) as err_pct,
    countDistinct(g.ip_hash) as distinct_ips,
    max(coalesce(ips.ip_cluster_size, 0)) as max_ip_cluster
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', '')
    AND u.tier = 'spore'
GROUP BY g.user_id, u.github_username, u.email, u.tier
HAVING total_reqs >= 100 AND err_pct >= 90 AND total_spend <= 1.6
ORDER BY max_ip_cluster DESC, total_reqs DESC

Then classify programmatically using the demotion signals listed under "Safe to demote" above.

Key Queries

1. Full Abuse Scoring Query

Returns all users with abuse score, sorted by score descending.

SELECT
    user_id, github_username, email, tier,
    total_reqs, 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, u.tier,
        count() as total_reqs,
        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 sum(g.total_price) = 0, 15,
                splitByChar('@', u.email)[2] = 'hotmail.com' AND sum(g.total_price) = 0, 12,
                splitByChar('@', u.email)[2] = 'outlook.com' AND sum(g.total_price) = 0, 10,
                0) +
            if(sum(g.total_price) = 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, u.tier
    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, u.tier,
    sum(g.total_price) as 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, u.tier
ORDER BY 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):

• Microbe tier + 95%+ error rate + 1000+ requests/week
• Score >= 90 + hotmail/outlook random email + zero spend + microbe tier
• Score >= 70 + IP cluster >= 100 + zero spend

Safe to demote (spore → microbe):

• IP cluster >= 10 + error rate >= 90%
• Gibberish suffix username (-boop, -a11y, -bit, -lang, -max, -sudo, -dot, -beep, -commits, -pixel, -cmd, -stack, -ops, -dotcom) + error >= 90%
• Disposable email (hotmail/outlook/proton/qq/mail.ru/vk.com/anonaddy/anondrop/rambler/gmx/yandex) + error >= 95% + $0 spend
• 100% error rate + $0 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 spend > $2 (could be real customer)
• Accounts on Cloudflare IPs (2a06:98c0:*)
• Accounts with real-looking Gmail addresses
• Spore users with 90-95% error rate but some successful 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>'"

Demote Commands (spore → microbe)

Demotion is preferred over banning for spore-tier abuse — it removes their pollen and rate-limits them without a hard block.

# Batch demote (from a file of user IDs, one per line)
IDS=$(cat user_ids_to_demote.txt | sed "s/^/'/;s/$/'/" | paste -sd, -)
npx wrangler d1 execute production-pollinations-enter-db --remote \
  --command "UPDATE user SET tier = 'microbe', tier_balance = 0 WHERE id IN ($IDS) AND tier = 'spore'"

# Verify (check a sample)
IDS=$(head -5 user_ids_to_demote.txt | sed "s/^/'/;s/$/'/" | paste -sd, -)
npx wrangler d1 execute production-pollinations-enter-db --remote \
  --command "SELECT id, tier, tier_balance FROM user WHERE id IN ($IDS)"

Important: Always include AND tier = 'spore' as a safety guard — prevents accidentally demoting users who were already upgraded.

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 (microbe tier), 100% NSFW moderation flags
• Spend: $0 (microbe) or ~$1.50 (spore free allotment)
• 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/demoting on D1, Tinybird data is stale — verify actions on D1 directly.
• Spore "spend" is misleading: Spore tier gets $0.01/hour ($0.24/day) free allotment. Filter with total_spend <= 0.25 to catch free-only users.
• 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