fiddy/docs/POSTGRES_RATELIMITTING_REFERENCE.md

Postgres Rate Limiting Reference

Overview

This document describes how to implement API rate limiting using Postgres in the Fiddy Finance Buddy App. It covers:

• Rate limiting tiers and keying strategies
• Data model and query patterns
• Cleanup/retention strategy
• Operational considerations

---

Rate Limiting Tiers & Keying

• Auth endpoints (login/register): Strict limits per IP and identifier (email)
• Write endpoints (POST/PUT/DELETE): Moderate limits per user and IP
• Read endpoints (GET): Higher limits per user and IP
• Keying:
  • Unauthenticated: IP only
  • Auth endpoints: IP + email
  • Authenticated: user ID + IP

---

Data Model (Suggested)

Table: rate_limits

• key (text, primary key) — composed from route + identifier
• window_start (timestamp) — start of the time window
• count (integer) — request count within the window
• updated_at (timestamp)

Key format examples:

• auth:login:ip:1.2.3.4
• auth:login:email:user@example.com
• entries:write:user:123

---

Query Pattern (High-Level)

• On each request:
  1. Compute key and current window
  2. Attempt upsert: increment count if within window
  3. If count exceeds limit, respond 429

---

Cleanup / Retention

• Expired rows can accumulate; set a retention window (e.g., 24–72 hours)
• Plan a periodic cleanup task or opportunistic cleanup in code paths
• Avoid large table growth by deleting rows older than retention

---

Operational Considerations

• Postgres rate limiting adds read/write load to the primary DB
• Use indexes on key and window_start for fast lookups
• Monitor query latency and lock contention under burst traffic
• Consider Redis migration when DB contention or volume increases

---

Migration Path

• See docs/POSTGRES_TO_REDIS_RATELIMITTING_MIGRATION_REFERENCE.md for triggers and Redis ops guidance.

---

Last updated: 2026-02-09