跳到正文
DEV Community· Elijah Brown·· 2 小時前AI 評分65

如何防止 Supabase 與 Next.js 應用程式的免費 AI 額度遭濫用

Stop free AI credits from leaking in Supabase and Next.js apps

AI 導讀

這篇教學以 Supabase SQL 遷移和 Next.js 路由處理，防止免費 AI 額度因 Gmail 地址別名、未登入呼叫、未確認電郵及大量請求而遭濫用。流程會在電郵確認後發放 50 點額度，並以每日上限控制生成請求。作者提供的 17 項測試全部通過，涵蓋並行確認和扣額。

正文

An app that gives every new account free AI credits tends to leak them in four places.

  1. The same person opens a second Google account, or signs up again as jane.doe+2@gmail.com, and gets a fresh balance.
  2. The /api/generate route calls the model for anyone who can send a POST, signed in or not.
  3. Credits land at signup, so an address nobody ever opens still gets the full allowance.
  4. Nothing stops one account from firing a few hundred requests in a minute.

None of these needs a clever attacker. A loop in the browser console is enough for the second one. This post closes all four with one route handler and one SQL migration. It works for a plain Next.js app on Supabase, and for a Lovable project, since Lovable apps run on Supabase too and the SQL goes into the same SQL editor.

The layout:

lib/email-key.mjs            normalise the address into a claim key
db/schema.sql                claim table, credits, daily counter, triggers
app/api/generate/handler.mjs the route logic, testable without Next
app/api/generate/route.js    wires it to Supabase
test/                        node --test suites

1. One key per inbox

Gmail ignores dots in the local part, drops everything after a +, and treats googlemail.com as gmail.com. So the claim key collapses those. Every other domain keeps its dots and tags, because plenty of mail servers treat them as separate mailboxes.

// lib/email-key.mjs
const GMAIL = new Set(["gmail.com", "googlemail.com"]);

export function emailKey(raw) {
  const email = String(raw ?? "").trim().toLowerCase();
  const at = email.lastIndexOf("@");
  if (at < 1 || at === email.length - 1) return null;
  let local = email.slice(0, at);
  let domain = email.slice(at + 1);
  if (GMAIL.has(domain)) {
    local = local.split("+")[0].replaceAll(".", "");
    domain = "gmail.com";
  }
  return local ? `${local}@${domain}` : null;
}

The stored email stays exactly as typed. Only the claim key is normalised.

2. The migration

Run this in the Supabase SQL editor. It does four things:

  • email_key() is the same normaliser in SQL, so the database enforces it and the client can't skip it.
  • trial_claims has the claim key as its primary key. That unique index is the whole trick: a second spelling of the same inbox can make an account, but its claim insert hits the index and does nothing.
  • Credits are granted by a trigger on auth.users, and only when email_confirmed_at is set. Email signups get there when the link is clicked. Google sign in arrives already confirmed, so a second trigger covers the insert path.
  • spend_credit() counts today's calls and takes one credit in a single statement pair inside one function call. It reads the user id from the session with auth.uid(), so a caller can't spend someone else's balance by passing their id. 
-- db/schema.sql
create or replace function public.email_key(raw text)
returns text language sql immutable as $$
  with p as (
    select lower(trim(raw)) as e
  ), s as (
    select split_part(e, '@', 1) as local,
           substr(e, length(split_part(e, '@', 1)) + 2) as domain
    from p where e like '_%@_%'
  )
  select case
    when domain in ('gmail.com', 'googlemail.com')
      then nullif(replace(split_part(local, '+', 1), '.', ''), '') || '@gmail.com'
    else local || '@' || domain
  end
  from s
$$;

create table if not exists public.trial_claims (
  email_key  text primary key,
  user_id    uuid not null unique,
  claimed_at timestamptz not null default now()
);

create table if not exists public.credits (
  user_id uuid primary key,
  balance integer not null default 0 check (balance >= 0)
);

create table if not exists public.usage_daily (
  user_id uuid not null,
  day     date not null default current_date,
  calls   integer not null default 0,
  primary key (user_id, day)
);

-- Runs when Auth flips email_confirmed_at from null to a timestamp.
create or replace function public.grant_trial_on_confirm()
returns trigger language plpgsql security definer set search_path = public as $$
declare
  k text := public.email_key(new.email);
begin
  if k is null then
    return new;
  end if;

  insert into public.trial_claims (email_key, user_id)
  values (k, new.id)
  on conflict do nothing;

  if found then
    insert into public.credits (user_id, balance)
    values (new.id, 50)
    on conflict (user_id) do nothing;
  end if;

  return new;
end $$;

drop trigger if exists grant_trial_on_confirm on auth.users;
create trigger grant_trial_on_confirm
  after update of email_confirmed_at on auth.users
  for each row
  when (old.email_confirmed_at is null and new.email_confirmed_at is not null)
  execute function public.grant_trial_on_confirm();

-- Google sign in arrives already confirmed, so it never hits the update path.
drop trigger if exists grant_trial_on_confirmed_insert on auth.users;
create trigger grant_trial_on_confirmed_insert
  after insert on auth.users
  for each row
  when (new.email_confirmed_at is not null)
  execute function public.grant_trial_on_confirm();

-- One call: under the daily cap and has a credit, or nothing changes.
-- The user id comes from the caller's JWT, never from the request body.
create or replace function public.spend_credit(daily_cap integer)
returns integer language plpgsql security definer set search_path = public as $$
declare
  uid uuid := auth.uid();
  used integer;
  left_over integer;
begin
  if uid is null then
    raise exception 'not_signed_in' using errcode = 'P0001';
  end if;

  insert into public.usage_daily as u (user_id, day, calls)
  values (uid, current_date, 1)
  on conflict (user_id, day) do update
    set calls = u.calls + 1
    where u.calls < daily_cap
  returning calls into used;

  if used is null then
    return -1;  -- daily cap reached
  end if;

  update public.credits
     set balance = balance - 1
   where user_id = uid and balance > 0
  returning balance into left_over;

  if left_over is null then
    raise exception 'no_credits' using errcode = 'P0001';
  end if;

  return left_over;
end $$;

revoke all on function public.spend_credit(integer) from public, anon;
grant execute on function public.spend_credit(integer) to authenticated;

alter table public.trial_claims enable row level security;
alter table public.credits enable row level security;
alter table public.usage_daily enable row level security;

create policy "read own credits" on public.credits
  for select to authenticated using (user_id = auth.uid());

A few details worth calling out.

on conflict do nothing followed by if found is what makes the grant safe under load. Twenty confirmations of the same inbox at once still produce exactly one credits row, and the test below runs that case.

The daily counter uses on conflict ... do update ... where u.calls < daily_cap. When the cap is reached the where fails, no row comes back, and the function returns -1 without touching the balance.

If the counter goes up but the balance is empty, the function raises. Postgres rolls the whole call back, so a failed request doesn't eat a slot from the daily cap.

The three tables have row level security on with no write policies, so the browser can read its own balance and nothing else. All writes go through the triggers and spend_credit.

3. The route handler

The logic lives in a small factory so it can be tested without booting Next. It checks the session first, refuses unconfirmed accounts, validates the prompt, spends a credit, and only then calls the model. The order matters: every early return happens before any quota is touched.

// app/api/generate/handler.mjs
export function makeHandler({ getUser, spendCredit, callModel, dailyCap = 30 }) {
  return async function POST(request) {
    const user = await getUser(request);
    if (!user) {
      return Response.json({ error: "sign_in_required" }, { status: 401 });
    }
    if (!user.email_confirmed_at) {
      return Response.json({ error: "confirm_email_first" }, { status: 403 });
    }

    let body;
    try {
      body = await request.json();
    } catch {
      return Response.json({ error: "bad_json" }, { status: 400 });
    }
    const prompt = typeof body?.prompt === "string" ? body.prompt.trim() : "";
    if (!prompt || prompt.length > 4000) {
      return Response.json({ error: "bad_prompt" }, { status: 400 });
    }

    let left;
    try {
      left = await spendCredit(user.id, dailyCap);
    } catch (err) {
      if (err?.message?.includes("no_credits")) {
        return Response.json({ error: "out_of_credits" }, { status: 402 });
      }
      throw err;
    }
    if (left < 0) {
      return Response.json({ error: "daily_limit" }, { status: 429 });
    }

    const output = await callModel(prompt);
    return Response.json({ output, credits_left: left });
  };
}

And the actual route file. createClient is the server client from the Supabase Next.js guide (@supabase/ssr with cookies). Use auth.getUser() here, not getSession(): getUser() asks the Supabase Auth server about the token, while getSession() just reads the cookie.

// app/api/generate/route.js
import { createClient } from "@/utils/supabase/server";
import { makeHandler } from "./handler.mjs";
import { callModel } from "@/lib/model";

export const POST = makeHandler({
  async getUser() {
    const supabase = await createClient();
    const { data, error } = await supabase.auth.getUser();
    return error ? null : data.user;
  },
  async spendCredit(userId, dailyCap) {
    const supabase = await createClient();
    // spend_credit reads the user id from the session JWT.
    const { data, error } = await supabase.rpc("spend_credit", {
      daily_cap: dailyCap,
    });
    if (error) throw new Error(error.message);
    return data;
  },
  callModel,
});

Because spend_credit runs with the caller's JWT, the RPC goes through the user's own session. No service role key is needed in this route, which is one less secret that could end up in a client bundle.

4. Tests

Two suites. The handler suite uses fakes and runs anywhere. The database suite runs against a local Postgres when DATABASE_URL is set and skips otherwise. Locally it needs a tiny stand in for what Supabase already ships: an auth.users table, auth.uid(), and the anon and authenticated roles.

-- Minimal stand-in for what Supabase already provides. Not part of the article's migration.
create schema if not exists auth;
create table if not exists auth.users (
  id uuid primary key default gen_random_uuid(),
  email text,
  email_confirmed_at timestamptz
);
create or replace function auth.uid() returns uuid language sql stable as $$
  select nullif(current_setting('request.jwt.claim.sub', true), '')::uuid
$$;
do $$ begin
  if not exists (select from pg_roles where rolname = 'anon') then create role anon; end if;
  if not exists (select from pg_roles where rolname = 'authenticated') then create role authenticated; end if;
end $$;

The anon and authenticated roles need a superuser to create, so on a plain local Postgres make them once by hand before the first run.

// test/handler.test.mjs
import { test } from "node:test";
import assert from "node:assert/strict";
import { makeHandler } from "../app/api/generate/handler.mjs";

const req = (body) =>
  new Request("http://x/api/generate", { method: "POST", body: JSON.stringify(body) });

function setup({ user, balance = 2, cap = 3 }) {
  const calls = { model: 0, used: 0 };
  const POST = makeHandler({
    dailyCap: cap,
    getUser: async () => user,
    spendCredit: async (_id, dailyCap) => {
      if (calls.used >= dailyCap) return -1;
      if (balance === 0) throw new Error("no_credits");
      calls.used++;
      return --balance;
    },
    callModel: async (p) => { calls.model++; return `echo: ${p}`; },
  });
  return { POST, calls };
}

const confirmed = { id: "u1", email_confirmed_at: "2026-10-20T10:00:00Z" };

test("no session: 401 and the model is never called", async () => {
  const { POST, calls } = setup({ user: null });
  assert.equal((await POST(req({ prompt: "hi" }))).status, 401);
  assert.equal(calls.model, 0);
});

test("unconfirmed email: 403", async () => {
  const { POST, calls } = setup({ user: { id: "u1", email_confirmed_at: null } });
  assert.equal((await POST(req({ prompt: "hi" }))).status, 403);
  assert.equal(calls.model, 0);
});

test("spends credits, then 402 when they run out", async () => {
  const { POST, calls } = setup({ user: confirmed, balance: 2, cap: 10 });
  const a = await POST(req({ prompt: "hi" }));
  assert.equal(a.status, 200);
  assert.equal((await a.json()).credits_left, 1);
  assert.equal((await POST(req({ prompt: "hi" }))).status, 200);
  assert.equal((await POST(req({ prompt: "hi" }))).status, 402);
  assert.equal(calls.model, 2);
});

test("daily cap: 429 even with credits left", async () => {
  const { POST, calls } = setup({ user: confirmed, balance: 100, cap: 2 });
  await POST(req({ prompt: "a" }));
  await POST(req({ prompt: "b" }));
  assert.equal((await POST(req({ prompt: "c" }))).status, 429);
  assert.equal(calls.model, 2);
});

test("empty or oversized prompt: 400 before any credit is spent", async () => {
  const { POST, calls } = setup({ user: confirmed });
  assert.equal((await POST(req({ prompt: "  " }))).status, 400);
  assert.equal((await POST(req({ prompt: "x".repeat(4001) }))).status, 400);
  assert.equal(calls.used, 0);
});

// test/db.test.mjs
import { test, before, after } from "node:test";
import assert from "node:assert/strict";
import { readFileSync } from "node:fs";
import pg from "pg";
import { emailKey } from "../lib/email-key.mjs";

const url = process.env.DATABASE_URL;
const pool = url ? new pg.Pool({ connectionString: url }) : null;
const opts = { skip: !url && "set DATABASE_URL to run" };

before(async () => {
  if (!pool) return;
  await pool.query("drop schema if exists auth cascade; drop table if exists public.trial_claims, public.credits, public.usage_daily cascade");
  await pool.query(readFileSync(new URL("./supabase-stub.sql", import.meta.url), "utf8"));
  await pool.query(readFileSync(new URL("../db/schema.sql", import.meta.url), "utf8"));
});
after(() => pool?.end());

async function signUp(email) {
  const { rows } = await pool.query("insert into auth.users (email) values ($1) returning id", [email]);
  return rows[0].id;
}
const confirm = (id) => pool.query("update auth.users set email_confirmed_at = now() where id = $1", [id]);
const balance = async (id) =>
  (await pool.query("select balance from public.credits where user_id = $1", [id])).rows[0]?.balance ?? null;

async function spendAs(id, cap) {
  const c = await pool.connect();
  try {
    await c.query("select set_config('request.jwt.claim.sub', $1, false)", [id]);
    return (await c.query("select public.spend_credit($1) as left", [cap])).rows[0].left;
  } finally {
    await c.query("reset all");
    c.release();
  }
}

test("SQL email_key matches the JS one", opts, async () => {
  for (const e of ["Jane.Doe+2@GoogleMail.com", "Jane.Doe+a@Example.com", "nope", "+x@gmail.com"]) {
    const { rows } = await pool.query("select public.email_key($1) k", [e]);
    assert.equal(rows[0].k, emailKey(e), e);
  }
});

test("no credits until the email is confirmed", opts, async () => {
  const id = await signUp("first.person@gmail.com");
  assert.equal(await balance(id), null);
  await confirm(id);
  assert.equal(await balance(id), 50);
  await confirm(id); // second update must not grant again
  assert.equal(await balance(id), 50);
});

test("a second Gmail spelling gets an account but no credits", opts, async () => {
  const id = await signUp("FirstPerson+again@googlemail.com");
  await confirm(id);
  assert.equal(await balance(id), null);
});

test("Google sign in (confirmed on insert) gets credits once per inbox", opts, async () => {
  const ins = "insert into auth.users (email, email_confirmed_at) values ($1, now()) returning id";
  const a = (await pool.query(ins, ["oauth.person@gmail.com"])).rows[0].id;
  const b = (await pool.query(ins, ["oauthperson+2@gmail.com"])).rows[0].id;
  assert.equal(await balance(a), 50);
  assert.equal(await balance(b), null);
});

test("daily cap and balance are enforced in one call", opts, async () => {
  const id = await signUp("capped@example.com");
  await confirm(id);
  assert.equal(await spendAs(id, 2), 49);
  assert.equal(await spendAs(id, 2), 48);
  assert.equal(await spendAs(id, 2), -1);
  assert.equal(await balance(id), 48);
});

test("out of credits raises and does not count the call", opts, async () => {
  const id = await signUp("empty@example.com");
  await confirm(id);
  await pool.query("update public.credits set balance = 0 where user_id = $1", [id]);
  await assert.rejects(spendAs(id, 5), /no_credits/);
  const { rows } = await pool.query("select calls from public.usage_daily where user_id = $1", [id]);
  assert.equal(rows.length, 0);
});

test("no session: spend_credit refuses", opts, async () => {
  await assert.rejects(pool.query("select public.spend_credit(5)"), /not_signed_in/);
});

test("20 parallel confirmations of one inbox grant credits once", opts, async () => {
  const ids = [];
  for (let i = 0; i < 20; i++) ids.push(await signUp(`race.case+${i}@gmail.com`));
  await Promise.all(ids.map((id) => confirm(id)));
  const { rows } = await pool.query(
    "select count(*)::int n from public.credits where user_id = any($1::uuid[])", [ids]);
  assert.equal(rows[0].n, 1);
});

test("50 parallel spends against 10 credits never go below zero", opts, async () => {
  const id = await signUp("burst@example.com");
  await confirm(id);
  await pool.query("update public.credits set balance = 10 where user_id = $1", [id]);
  const results = await Promise.allSettled(Array.from({ length: 50 }, () => spendAs(id, 1000)));
  assert.equal(results.filter((r) => r.status === "fulfilled").length, 10);
  assert.equal(await balance(id), 0);
});

// test/email-key.test.mjs
import { test } from "node:test";
import assert from "node:assert/strict";
import { emailKey } from "../lib/email-key.mjs";

test("gmail dots, plus tags and googlemail collapse to one key", () => {
  for (const e of ["jane.doe@gmail.com", "JaneDoe+2@Gmail.com", "j.a.n.e.doe+x@googlemail.com"]) {
    assert.equal(emailKey(e), "janedoe@gmail.com");
  }
});

test("other domains keep dots and plus tags", () => {
  assert.equal(emailKey("Jane.Doe+a@Example.com"), "jane.doe+a@example.com");
});

test("junk returns null", () => {
  for (const e of ["", "nope", "@gmail.com", "a@", "+x@gmail.com", null]) {
    assert.equal(emailKey(e), null);
  }
});

With "type": "module" in package.json and pg installed, on Node 20.19 and PostgreSQL 17.11:

$ DATABASE_URL=postgres://trial:trial@localhost/trial node --test --test-reporter=spec
✔ SQL email_key matches the JS one
✔ no credits until the email is confirmed
✔ a second Gmail spelling gets an account but no credits
✔ Google sign in (confirmed on insert) gets credits once per inbox
✔ daily cap and balance are enforced in one call
✔ out of credits raises and does not count the call
✔ no session: spend_credit refuses
✔ 20 parallel confirmations of one inbox grant credits once
✔ 50 parallel spends against 10 credits never go below zero
✔ gmail dots, plus tags and googlemail collapse to one key
✔ other domains keep dots and plus tags
✔ junk returns null
✔ no session: 401 and the model is never called
✔ unconfirmed email: 403
✔ spends credits, then 402 when they run out
✔ daily cap: 429 even with credits left
✔ empty or oversized prompt: 400 before any credit is spent
ℹ tests 17
ℹ pass 17
ℹ fail 0
ℹ skipped 0

Without DATABASE_URL the eight handler and normaliser tests pass and the nine database tests are skipped.

What this doesn't cover

A unique key per inbox stops the same Gmail address from coming back in a new spelling. It does nothing about someone who owns ten separate Google accounts, or a script that rotates through fresh domains. For that you need signals from outside the email string: the IP the signup came from, whether the domain was registered last week, whether a phone number belongs to a reserved test range. The daily cap also limits damage per account, not per person, so pairing it with a per IP limit on the signup form is a sensible next step.

I built Orisift for the parts this doesn't cover; there's a free tier. orisift.com

來源：DEV Community · dev.to

#教學/實踐#部署/工程