---
name: abuse-detection-e566e5f72f9a
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

- `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):
```bash
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:

| Signal | Max Points | Threshold | What it catches |
|--------|-----------|-----------|-----------------|
| IP cluster size | 30 | `cluster * 0.15` | Multiple users sharing same IP hash |
| Zero pack spend | 15 | `spend = 0` | No paid pack usage |
| Error rate | 15 | `>= 95%` (15pts), `>= 70%` (10pts) | Bots hammering failing endpoints |
| Moderation flags | 15 | `>= 90%` sexual (15pts), `>= 50%` (8pts) | NSFW generation bots |
| Disposable email | 15 | hotmail/outlook/proton + no spend | Random-string throwaway emails |
| IP rotation | 10 | `>= 50` IPs (10pts), `>= 20` (5pts) | Rotating through many exit IPs |

**Score interpretation:**

| Score | Action | False positive risk |
|-------|--------|-------------------|
| 90-100 | Ban immediately | Very low |
| 70-89 | Ban after quick review | Low |
| 40-69 | Manual review needed | Medium |
| 10-39 | Monitor only | High — many legit users with NSFW or errors |
| 0-9 | Clean | N/A |

---

# 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:**

```sql
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.

```sql
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):

```sql
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

```sql
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)

```sql
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

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

---

# Known False Positive Patterns

**Always check before banning:**

| Pattern | Why it's a false positive | How to detect |
|---------|--------------------------|---------------|
| **Cloudflare WARP/Workers** | IPv6 `2a06:98c0:3600::` — legit users behind Cloudflare | Check `ip_subnet` starts with `2a06:98c0` |
| **VPN/proxy clusters** | Multiple real users behind same VPN exit | Check if cluster has paying users with real emails |
| **Chinese CGNAT** | Mobile carriers (China Mobile/Unicom/Telecom) share IPs via NAT | Cross-reference with email pattern + spend |
| **Free balance usage** | Accounts show small "spend" from non-pack balance, not real payment | Check `pack_spend` — a strong signal, not proof of payment (see below) |
| **High NSFW, legit user** | Some paying users generate NSFW content legitimately | Check pack spend > $5 — real customers |

**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:

| Field | Type | Description |
|-------|------|-------------|
| `banned` | boolean (integer 0/1) | Set to `1` to ban |
| `ban_reason` | text | Shown in 403 error response |
| `ban_expires` | integer (epoch ms) | `NULL` for permanent, epoch ms for temporary |

**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`:

```sql
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

```bash
# 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

| Table | Key columns for abuse |
|-------|----------------------|
| `generation_event_v2` | `user_id`, `ip_hash`, `ip_subnet`, `response_status`, `total_price`, `selected_meter_slug`, `moderation_prompt_*`, `event_type` |
| `d1_user` | `id`, `email`, `github_username`, `banned`, `banReason`, `created_at` |

**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

| Date | Action | Count | Details |
|------|--------|-------|---------|
| 2026-03-06 | Banned bot farm | 277 | IP cluster ≥100, 95%+ errors, $0 pack spend |
| 2026-03-06 | Rate-limited bot farm | 42 | Same bot farm, no pack spend |
| 2026-03-06 | Rate-limited bot farm | 59 | Multi-signal: IP clusters, gibberish suffixes, disposable emails, hammering |
| 2026-09-16 | Banned card testers | 22 | Stripe fraud signals; 9 found by the scan, 13 by hand (pre-May charges). 176 card fingerprints added to the Radar block list |

---

# 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


---
Source attribution: pollinations
Original project: https://github.com/pollinations/pollinations/blob/60d4074d1047a887b9ed7ed01f4bd75cb6122834/.claude/skills/abuse-detection/SKILL.md
License: MIT
License information: https://opensource.org/license/mit
Original upstream instructions; downloaded from Dave AI.

## Upstream license notice

MIT License

Copyright (c) 2026 pollinations.ai

Permission is hereby granted, free of charge, to any person obtaining a copy
of this software and associated documentation files (the "Software"), to deal
in the Software without restriction, including without limitation the rights
to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
copies of the Software, and to permit persons to whom the Software is
furnished to do so, subject to the following conditions:

The above copyright notice and this permission notice shall be included in all
copies or substantial portions of the Software.

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
SOFTWARE.



# Dave AI resource metadata

Creator / publisher: pollinations
Original project: https://github.com/pollinations/pollinations/blob/60d4074d1047a887b9ed7ed01f4bd75cb6122834/.claude/skills/abuse-detection/SKILL.md
Attribution basis: Source repository record

Fields of work: Data & Analytics, Marketing, Sales
Roles: Customer Success Manager, Data Analyst, Marketing Analyst
Specialties: API, Backend, Database, Frontend, Git, Mobile, Stripe, Testing
Tools: Tool not specified
Country applicability: Country not specified
Classification basis: Source catalog labels
Instruction SHA-256: dce849ec568ba95e644494e1fc9c2db151a2f76b060cf9d305f7ddd598913625