the bomber

Posted on Nov 15

Using PostgreSQL Counters for Rate‑Limiting a Public Chat on Netlify Functions

#netlify #postgres #ratelimiting #automation

Introduction

When I built the chat widget for dreamfuturestech.com, I needed a cheap, reliable way to stop a single IP from spamming the public endpoint. Netlify Functions give me serverless compute, but they don’t provide built‑in rate‑limiting. I solved the problem with a tiny PostgreSQL table that acts as a counter, combined with row‑level locking to keep everything atomic.

Why Netlify Functions?

Zero‑ops deployment – just push to the repo and Netlify builds the functions.
Free tier – enough for a small‑business site with a few hundred daily visitors.
Edge‑ready – functions run close to the user, keeping latency low.

The downside is that each invocation is stateless, so any per‑user throttling must live outside the function. That’s where Postgres comes in.

Designing the Rate Limit

The goal is simple: max 20 messages per IP per hour. The algorithm is:

Look up the counter row for the caller’s IP.
If the row doesn’t exist, create it with count = 1 and reset_at = now() + interval '1 hour'.
If the row exists and reset_at is in the future, increment count.
If count exceeds the limit, reject the request.
If reset_at has passed, reset count to 1 and push reset_at forward.

All of this must happen in a single transaction to avoid race conditions when two requests hit the same IP at the same time.

PostgreSQL Counter Table

CREATE TABLE ip_rate_limit (
    ip          inet PRIMARY KEY,
    count       int NOT NULL,
    reset_at    timestamptz NOT NULL
);

ip – the caller’s address (stored as inet for efficient indexing).
count – how many requests have been accepted in the current window.
reset_at – the timestamp when the window expires.

I keep the table in a small managed Postgres instance (e.g., Supabase or Railway) because it’s cheap and gives me full SQL control.

Implementing Row‑Level Locking in the Function

// netlify/functions/chat.js
import { Client } from '@supabase/supabase-js';

const LIMIT = 20;
const WINDOW = 60 * 60 * 1000; // 1 hour in ms

export async function handler(event, context) {
  const ip = event.headers['x-forwarded-for']?.split(',')[0]?.trim() || '0.0.0.0';
  const client = new Client(process.env.SUPABASE_URL, process.env.SUPABASE_SERVICE_ROLE_KEY);
  await client.connect();

  try {
    await client.query('BEGIN');
    // Lock the row for this IP – prevents concurrent updates
    const res = await client.query(
      `SELECT count, reset_at FROM ip_rate_limit WHERE ip = $1 FOR UPDATE`,
      [ip]
    );

    const now = new Date();
    let count, resetAt;

    if (res.rowCount === 0) {
      // First request from this IP – insert a new row
      count = 1;
      resetAt = new Date(now.getTime() + WINDOW);
      await client.query(
        `INSERT INTO ip_rate_limit (ip, count, reset_at) VALUES ($1, $2, $3)`,
        [ip, count, resetAt]
      );
    } else {
      ({ count, reset_at: resetAt } = res.rows[0]);
      if (now > resetAt) {
        // Window has expired – start a fresh count
        count = 1;
        resetAt = new Date(now.getTime() + WINDOW);
        await client.query(
          `UPDATE ip_rate_limit SET count = $1, reset_at = $2 WHERE ip = $3`,
          [count, resetAt, ip]
        );
      } else if (count >= LIMIT) {
        // Rate limit exceeded – abort
        await client.query('ROLLBACK');
        return {
          statusCode: 429,
          body: JSON.stringify({ error: 'Rate limit exceeded' })
        };
      } else {
        // Within limit – increment
        count += 1;
        await client.query(
          `UPDATE ip_rate_limit SET count = $1 WHERE ip = $2`,
          [count, ip]
        );
      }
    }

    await client.query('COMMIT');
    // Process the chat message (omitted for brevity)
    return { statusCode: 200, body: JSON.stringify({ success: true }) };
  } catch (e) {
    await client.query('ROLLBACK');
    console.error(e);
    return { statusCode: 500, body: JSON.stringify({ error: 'Server error' }) };
  } finally {
    await client.end();
  }
}

Key points

FOR UPDATE acquires a row‑level lock, guaranteeing that two concurrent invocations can’t both read the same count and then both increment it.
The whole flow lives inside a transaction (BEGIN … COMMIT/ROLLBACK).
If the limit is hit, we roll back immediately and return HTTP 429.

Daily Cleanup of Stale Rows

Over time the table could fill with IPs that never return. A simple nightly job removes rows whose reset_at is older than a day:

DELETE FROM ip_rate_limit WHERE reset_at < now() - interval '1 day';

I schedule this with a Netlify “Scheduled Function” that runs at 02:00 UTC. The function just runs the above DELETE and logs the count of removed rows.

Deploying to Netlify

Add the function file under netlify/functions/.
Set environment variables in the Netlify UI:
- SUPABASE_URL
- SUPABASE_SERVICE_ROLE_KEY
Commit and push – Netlify builds the function automatically.
In the site’s front‑end, POST to /.netlify/functions/chat with the message payload.

Testing the Limit

I use a tiny script that fires 25 requests from the same IP:

for i in {1..25}; do
  curl -X POST -d '{"msg":"test"}' https://my‑site.netlify.app/.netlify/functions/chat
done

The first 20 calls return 200, the remaining five return 429. I also verified that after an hour the counter resets automatically.

Conclusion

Using a PostgreSQL counter with row‑level locking gives me a precise, cheap, and scalable rate‑limit for a public Netlify endpoint. The approach works for any low‑traffic API where you need per‑IP throttling without pulling in a full‑blown Redis cluster.

I build sites and automations for small businesses at dreamfuturestech.com.

DEV Community