使用临时 ID 进行欺诈检测

CREATE TABLE turnstile_events (
    id              BIGSERIAL PRIMARY KEY,
    ephemeral_id    VARCHAR(64) NOT NULL,
    event_type      VARCHAR(50) NOT NULL,  -- 'signup', 'login', 'checkout'
    ip_address      VARCHAR(45),
    user_id         VARCHAR(128),          -- NULL for signups, populated after
    created_at      TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE blocked_ephemeral_ids (
    ephemeral_id    VARCHAR(64) PRIMARY KEY,
    reason          VARCHAR(255),
    created_at      TIMESTAMPTZ DEFAULT NOW()
);

async function verifyAndLogTurnstile(
	token: string,
	ip: string,
	secretKey: string,
	eventType: string,
	db: Database,
): Promise<{ success: boolean; ephemeralId?: string; isBlocked: boolean }> {
	// 调用 Siteverify API
	const response = await fetch(
		"https://challenges.cloudflare.com/turnstile/v0/siteverify",
		{
			method: "POST",
			headers: { "Content-Type": "application/x-www-form-urlencoded" },
			body: new URLSearchParams({
				secret: secretKey,
				response: token,
				remoteip: ip,
			}),
		},
	);

	const result = await response.json();

	if (!result.success) {
		return { success: false, isBlocked: false };
	}

	const ephemeralId = result.metadata?.ephemeral_id;

	if (ephemeralId) {
		// 记录事件
		await db.query(
			`INSERT INTO turnstile_events (ephemeral_id, event_type, ip_address)
       VALUES ($1, $2, $3)`,
			[ephemeralId, eventType, ip],
		);

		// 检查是否已被阻止
		const blocked = await db.query(
			`SELECT 1 FROM blocked_ephemeral_ids WHERE ephemeral_id = $1`,
			[ephemeralId],
		);

		if (blocked.rows.length > 0) {
			return { success: true, ephemeralId, isBlocked: true };
		}
	}

	return { success: true, ephemeralId, isBlocked: false };
}

export async function handleSignup(request: Request, env: Env) {
	const formData = await request.formData();
	const email = formData.get("email") as string;
	const turnstileToken = formData.get("cf-turnstile-response") as string;
	const ip = request.headers.get("CF-Connecting-IP") || "";

	// 验证 Turnstile 并记录临时 ID
	const verification = await verifyAndLogTurnstile(
		turnstileToken,
		ip,
		env.TURNSTILE_SECRET_KEY,
		"signup",
		env.DB,
	);

	if (!verification.success) {
		return new Response("验证失败", { status: 400 });
	}

	// 如果该设备被标记，则予以阻止
	if (verification.isBlocked) {
		// 返回通用消息 - 不要透露已被检测到
		return new Response("请验证您的邮箱以继续", {
			status: 202,
		});
	}

	// 继续正常注册
	const userId = await createUser(email, formData.get("password"));

	// 使用新用户 ID 更新日志
	if (verification.ephemeralId) {
		await env.DB.query(
			`UPDATE turnstile_events
       SET user_id = $1
       WHERE ephemeral_id = $2 AND event_type = 'signup' AND user_id IS NULL
       ORDER BY created_at DESC LIMIT 1`,
			[userId, verification.ephemeralId],
		);
	}

	return new Response("账户已创建", { status: 201 });
}

-- 查找在过去一小时内创建多个账户的设备
SELECT
    ephemeral_id,
    COUNT(*) as signup_count,
    COUNT(DISTINCT ip_address) as unique_ips
FROM turnstile_events
WHERE
    event_type = 'signup'
    AND created_at > NOW() - INTERVAL '1 hour'
GROUP BY ephemeral_id
HAVING COUNT(*) > 3;  -- 注册次数超过 3 次 = 可疑

INSERT INTO blocked_ephemeral_ids (ephemeral_id, reason)
SELECT
    ephemeral_id,
    '多次注册：1 小时内 ' || COUNT(*) || ' 次'
FROM turnstile_events
WHERE
    event_type = 'signup'
    AND created_at > NOW() - INTERVAL '1 hour'
GROUP BY ephemeral_id
HAVING COUNT(*) > 3
ON CONFLICT (ephemeral_id) DO NOTHING;

-- 查找与被封禁用户使用同一设备创建的所有账户
SELECT DISTINCT te2.user_id, te2.created_at
FROM turnstile_events te1
JOIN turnstile_events te2 ON te1.ephemeral_id = te2.ephemeral_id
WHERE te1.user_id = 'BANNED_USER_ID'
  AND te2.user_id IS NOT NULL
  AND te2.user_id != 'BANNED_USER_ID';

-- 标记来自可疑设备的所有账户
UPDATE users
SET status = 'under_review'
WHERE id IN (
    SELECT DISTINCT user_id
    FROM turnstile_events
    WHERE ephemeral_id = 'x:SUSPICIOUS_ID_HERE'
      AND user_id IS NOT NULL
);