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

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 $'...' 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_v2 g
    WHERE g.start_time >= now() - INTERVAL 7 DAY
        AND g.is_final
        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_v2 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_v2
        WHERE start_time >= now() - INTERVAL 7 DAY
            AND is_final
            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.is_final
        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_v2
WHERE start_time >= now() - INTERVAL 7 DAY
    AND is_final
    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_v2 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.is_final
    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