Requirements

• sops and jq (the prod Tinybird read token comes from SOPS)
• Cloudflare D1 access for banning users (via wrangler)

Tinybird query pattern (prod workspace pollinations_enter, no row cap):

enter.pollinations.ai/observability/scripts/tb-prod.sh "SELECT ... FROM generation_event_v2 ... FORMAT JSONCompact"

Workspace: prod-only — staging has no real traffic. tb-prod.sh --check confirms events are fresh. Don't switch or save credentials just to run an audit.

Quoting: Use double quotes for the SQL string. Use single quotes inside SQL. Avoid != with $'...' shell quoting (escaping issues) — prefer NOT IN ('undefined', '') instead.

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

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)

Pack spend is not proof of payment

pack_balance is the bucket paid users draw from, but money gets into it in more than one way. Besides Stripe purchases and auto-top-ups, earnings land there too: BYOP markup and community-model rewards are credited to the same bucket the payer used (shared/billing/track-helpers.ts), and some quest rewards are pack-bucket (shared/billing/rewards.ts). So pack_spend > 0 means "spent from the paid bucket", not "paid us cash". The reverse check is incomplete too: Stripe checkout rows miss auto-top-ups, which never create a checkout.session.* event.

Before calling an account a real customer (or clearing it) on spend alone, check the actual credit sources: Stripe checkout pollen_credited, auto-top-up amount_usd, historical Polar credits, and claimed rewards by bucket. No cash purchase is not an abuse signal by itself.

Auto-top-up amount_usd is the Pollen principal credited, not what the customer paid (fees and tax sit on top). It proves a funding event happened; the exact dollars are on the Stripe invoice (stripe_invoice_id, amount_paid) — rarely needed for an abuse verdict.

Banning Users

How Banning Works

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

Enforcement (shared/auth/api-key.ts, shared/auth/ban.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
• A ban also hides the user's community models, blocks checkout and auto top-up, and sends the sign-in flow to the Account Suspended screen

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

A ban is three statements, not one

Setting banned = 1 alone leaves the account logged in and still charging its card. Use all three, matching fraudBanQueries() in enter.pollinations.ai/src/utils/stripe-fraud-ban.ts:

UPDATE user SET banned = 1, ban_reason = '<reason>', ban_expires = NULL, auto_top_up_enabled = 0
  WHERE id = '<USER_ID>' AND (COALESCE(banned, 0) = 0 OR ban_expires <= unixepoch());
UPDATE user SET auto_top_up_enabled = 0 WHERE id = '<USER_ID>';
DELETE FROM session WHERE user_id = '<USER_ID>';

Then expire any open Stripe Checkout sessions for the user's stripe_customer_id, or they can still complete a purchase already in flight.

Verify afterwards: banned count, ban_reason, auto_top_up_enabled = 0, and zero rows left in session.

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) - see the warning below first
npx wrangler d1 execute production-pollinations-enter-db --remote \
  --command "UPDATE user SET banned = 0, ban_reason = NULL, ban_expires = 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>'"

Unbanning: the trap

If the account was banned by the payment-fraud policy, clearing banned is not enough to remove it from the review queue. The daily job rescores all attributable history but takes no enforcement action. Add a reviewed exception to the repository variable FRAUD_BAN_EXCLUDED_USER_IDS (comma separated) to omit it from that queue.

Auto top-up stays off after an unban; re-enable it deliberately if the user asks.

Payment-fraud bans

Separate from the abuse scoring above, .github/workflows/billing-check-fraud.yml scores accounts daily on Stripe signals (enter.pollinations.ai/src/utils/stripe-fraud-score.ts).

• Scheduled and manually dispatched checks are read-only during calibration. The report always passes apply: false; bans and refunds require manual decisions.
• Every run posts accounts still needing review to the private Discord channel via DISCORD_FRAUD_WEBHOOK_URL, as a TSV of score, user id, name. No message means nothing needs action.
• A ban needs a confirmed signal: a fraudulent dispute, a fraud report, or an early fraud warning. Radar's blocked and highest-risk flags raise the score but never convict alone, because repeated declines of one legitimate card escalate them.
• Charges before 2026-05-01 carry no identity and are not scanned. Accounts whose activity predates that must be reviewed and banned by hand.
• One account is excluded by id in the source (a settled case). Add later exceptions to FRAUD_BAN_EXCLUDED_USER_IDS instead of editing code.

Before a manual payment-fraud ban, confirm the account owns its charges using the Checkout Session metadata our server wrote (metadata.userId). Never ban on a billing-email match: the buyer controls that field, which is why production refuses it.

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, but not proof — see "Pack spend is not proof of payment". Filter on pack_spend to narrow the review, then check credit sources before treating it as cash paid.
• 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