Prowider Mini: Premium Lead Distribution Platform

A full-stack, production-ready mini lead distribution platform built with Next.js 15, PostgreSQL, and Prisma 7. The platform utilizes transactional row locking for database-backed allocation, enforces strict idempotency on webhook integrations, and broadcasts updates in real-time via Server-Sent Events (SSE).

---

🛠️ Technology Stack

• Framework: Next.js 15 (App Router, Server Actions / Route Handlers)
• Language: TypeScript
• Database: PostgreSQL
• ORM: Prisma 7 (using Driver Adapters with @prisma/adapter-pg & pg)
• Real-Time Communication: Server-Sent Events (SSE)
• Validation: Zod 4
• Styling: TailwindCSS 4 (Utility-first styling with modern inline themes)

---

🧠 Allocation Engine Architecture

To ensure strict consistency and avoid double-allocations under high concurrency, the allocation engine uses PostgreSQL row-level locking (SELECT ... FOR UPDATE) wrapped inside a Prisma transaction:

1. Transaction & Row Locking:
   • The engine locks the AllocationState pointer row for the target service: SELECT * FROM "AllocationState" WHERE "serviceId" = $1 FOR UPDATE
   • It also locks all eligible provider rows (both static and fair-pool providers): SELECT * FROM "Provider" WHERE "id" = ANY($1) FOR UPDATE
   • Concurrent allocations for the same service queue up, preventing race conditions.
2. Quota Verification:
   • Checks remaining quota of all providers. A provider requires a remainingQuota > 0 to be eligible.
3. Distribution Rules:
   • Service 1: 1 Static Provider (P1), Fair Pool (P2, P3, P4).
   • Service 2: 1 Static Provider (P5), Fair Pool (P6, P7, P8).
   • Service 3: 2 Static Providers (P1, P4), Fair Pool (P2, P3, P5, P6, P7, P8).
4. Allocation Logic:
   • Always assigns the lead to the active static providers of the service first (if quota exists).
   • Then, fills the remaining slots (up to exactly 3) from the Fair Pool using round-robin.
   • Pointers are saved dynamically in the AllocationState table. If exactly 3 providers cannot be found with remaining quotas, the transaction rolls back safely.

---

🚀 Setup & Execution Guide

1. Prerequisites

• Node.js 18+
• PostgreSQL server running locally

2. Configure Environment Variables

Create a .env file in the root directory:

DATABASE_URL="postgresql://postgres:ambar2006@localhost:5432/lead_distribution?schema=public"

3. Install Dependencies

npm install

4. Apply Database Migrations

Run the migrations to create all database tables, foreign keys, indices, and constraints:

npx prisma migrate dev --name init

5. Seed the Database

Populate the database with initial services, providers (quota initialized to 10), and allocation states:

npx prisma db seed

6. Run the Development Server

npm run dev

Open http://localhost:3000 in your browser.

---

🧪 System Test Suite

The system includes a dedicated /test-tools dashboard to verify correctness under stress:

1. Idempotency Webhook:
   • Endpoint: POST /api/webhook/reset-quota
   • Body: { "eventId": "unique-event-id" }
   • Test: Click "Test Webhook Idempotency" on /test-tools. It fires 3 concurrent requests with the exact same event ID. The database transaction ensures only 1 runs successfully, while the remaining 2 are rejected as duplicates (returning 200 OK without double-resetting or throwing errors).
2. Concurrency Safe-Guard:
   • Endpoint: POST /api/request-service
   • Test: Click "Generate 10 Concurrent Leads" on /test-tools. It dispatches 10 requests simultaneously. Row-locking guarantees that the pointer transitions, allocations, and quota subtractions execute in a clean serializable sequence.
3. Real-time Live Sync:
   • Open /dashboard and /test-tools side-by-side.
   • Click "Generate 10 Concurrent Leads" or "Reset Provider Quotas" on /test-tools.
   • Watch the /dashboard update instantly with slide animations, updated progress bars, and allocation logs fed via the /api/events SSE stream.