Refactor Contest Fetching: Implement Database-Backed Caching via Cron Jobs

[nilanshucodes]

Epic: Production Readiness & High Traffic Scalability

Critical Context

We are currently deployed on Vercel, but the application is not production-ready for concurrent users.

• Current Vulnerability: We rely on in-memory caching in Serverless functions.
• The Risk: During traffic spikes, Vercel spins up multiple server instances (Cold Starts). Each instance has an empty cache and triggers a fresh request to the CList API.
• Consequence: With >10 concurrent users causing cold starts, we will immediately hit the CList rate limit (10 req/min), causing the app to crash or return 429 errors for all users.

Objective

Transition the architecture from "Prototype" to "Production Scalable" to support high concurrency without external dependency failures.

Key Architectural Changes

1. Database Decoupling (The Primary Fix)

• Move from "Fetch on Request" to "Fetch on Background Schedule".
• Benefit: User traffic never hits the external CList API, only our internal Supabase DB.

2. Supabase Connection Pooling (Supabase Supavisor)

Problem: Serverless functions open a new DB connection per user. 100 users = 100 connections, which will crash the Postgres instance.
Solution: Configure Supabase Transaction Mode (Port 6543) in Vercel environment variables.

• Update DATABASE_URL to use the pooled connection string.

3. Client-Side Stale-While-Revalidate (SWR)

Problem: Even with a DB, if users refresh the page spam-click filters, we shouldn't spam the DB.
Solution: Implement Cache-Control headers or use React Query/SWR on the frontend.

• Set Cache-Control: public, s-maxage=60, stale-while-revalidate=300.
• This tells Vercel's Edge Network to cache the HTML/JSON response so it doesn't even hit the Serverless function for repeat visits.

Readiness Checklist

• Data Layer: Implement Cron-based ingestion (Decouple CList API).
• Infrastructure: Enable Supabase Connection Pooling (Transaction Mode) to handle Vercel scaling.
• Edge Caching: Add Cache-Control headers to API routes (s-maxage=60).
• Resilience: Add a "Fallback UI" if the database is empty or unreachable (don't show a blank screen).
• Stress Test: Verify the app doesn't crash 500 status when 20 users visit simultaneously.

Success Metrics

• Zero 429 (Too Many Requests) errors from CList.
• <200ms response time for users (serving from DB/Edge Cache).
• 100% Uptime during cold starts.

Implementation Details

1. Database Schema

We need a new table in Supabase.
Table: contests

• id (int8, PK)
• resource (text) - e.g., "codeforces.com"
• event (text) - Contest Title
• start (timestamptz)
• end (timestamptz)
• href (text) - Link to contest

2. Vercel Cron Configuration

Update vercel.json:

{
  "crons": [
    {
      "path": "/api/cron/update-contests",
      "schedule": "10 * * * *"
    }
  ]
}

Assignees:- @nilanshucodes
Created By:- @nilanshucodes

[nilanshucodes]

Current website is able to handle many users 150+
team is working on making extension first then will do enhancement on this