Rate Limiting

This template does not ship with rate limiting. If you are deploying as a public-facing SaaS (or any environment where abuse is a concern), you can add a Postgres-backed fixed-window rate limiter with zero external dependencies.

The following prompt can be pasted directly into Claude Code to implement it end-to-end.

The prompt

Add a Postgres-backed fixed-window rate limiter to this project. Here is the
full specification:

### 1. Database migration

Create a Supabase migration that:

- Creates a `rate_limits` table:
  - `key` (text, not null) — arbitrary string identifying the resource being
    limited (e.g. "invite-create:<team_id>")
  - `window_start` (timestamptz, not null) — the start of the current time
    window
  - `count` (int, not null, default 0) — number of requests in this window
  - Primary key: `(key, window_start)`
- Adds an index on `window_start` for cleanup queries
- Enables RLS with zero policies (table is only accessed by the service role)
- Creates an `enforce_rate_limit(p_key text, p_window_seconds int)` function
  (SECURITY DEFINER, search_path = public, pg_temp) that:
  - Snaps to the current fixed window: `to_timestamp(floor(extract(epoch from
    now()) / p_window_seconds) * p_window_seconds)`
  - Upserts into `rate_limits` with `ON CONFLICT DO UPDATE SET count =
    rate_limits.count + 1`
  - Returns the new count
- Creates a `cleanup_rate_limits()` function that deletes rows older than 1 day

### 2. Server utility

Create `server/utils/rateLimit.ts` with an `enforceRateLimit` function:

```ts
import type { SupabaseClient } from "@supabase/supabase-js";
import type { Database } from "~~/shared/types/database.types";
import { createError } from "h3";

type RateLimitOptions = {
  key: string;
  limit: number;
  windowSeconds: number;
};

export async function enforceRateLimit(
  client: SupabaseClient<Database>,
  { key, limit, windowSeconds }: RateLimitOptions,
) {
  const { data, error } = await client.rpc("enforce_rate_limit", {
    p_key: key,
    p_window_seconds: windowSeconds,
  });

  if (error || data == null) {
    console.error("[rateLimit] enforce_rate_limit failed:", error);
    return; // fail open
  }

  if (data > limit) {
    throw createError({
      statusCode: 429,
      statusMessage: "Too many requests. Please slow down and try again shortly.",
    });
  }
}
```

### 3. Add rate limits to these server routes

Each `enforceRateLimit` call goes right after the auth check, before any
business logic:

- `server/api/teams/index.post.ts` — 10 per hour per user:
  `{ key: \`team-create:${user.sub}\`, limit: 10, windowSeconds: 60 * 60 }`

- `server/api/teams/[teamId]/invitations/index.post.ts` — two limits:
  - 10 per minute per team:
    `{ key: \`invite-create:${teamId}:m\`, limit: 10, windowSeconds: 60 }`
  - 100 per hour per team:
    `{ key: \`invite-create:${teamId}:h\`, limit: 100, windowSeconds: 60 * 60 }`

- `server/api/invitations/[token].get.ts` — 30 per minute per IP (this is an
  unauthenticated endpoint, so key on IP instead of user):
  ```ts
  const ip = getRequestIP(event, { xForwardedFor: true }) ?? "unknown";
  await enforceRateLimit(client, { key: \`invite-lookup:${ip}\`, limit: 30, windowSeconds: 60 });
  ```

- `server/api/invitations/[token]/accept.post.ts` — 20 per minute per user:
  `{ key: \`invite-accept:${user.sub}\`, limit: 20, windowSeconds: 60 }`

- `server/api/teams/[teamId]/avatar.post.ts` — 10 per minute per team:
  `{ key: \`team-avatar-upload:${teamMember.teamId}\`, limit: 10, windowSeconds: 60 }`

- `server/api/auth/profile/avatar.post.ts` — 10 per minute per user:
  `{ key: \`avatar-upload:${user.sub}\`, limit: 10, windowSeconds: 60 }`

### 4. Regenerate database types

Run `npx supabase gen types typescript` to add the `rate_limits` table and
the `enforce_rate_limit` / `cleanup_rate_limits` functions to
`shared/types/database.types.ts`.