Skip to content

Shalin-Shah-2002/ApiProtection_.Net

BranchesTags

Repository files navigation

API Protection Rate Limiting System - Project Documentation

?? Table of Contents

?? Project Overview

API Protection is an enterprise-grade ASP.NET Core Web API project implementing a comprehensive rate limiting and API key management system. This project demonstrates industry-standard practices for building secure, scalable APIs with SQL Server-based business logic and Redis-powered rate limiting.

Business Problem Solved

  • API Key Authentication: Secure API access using custom API keys
  • Rate Limiting: Prevent API abuse with per-minute and per-day request limits
  • Client Blocking: Administrative control to block/unblock clients
  • Request Logging: Track API usage and rate limit violations
  • Multi-tier Plans: Different rate limits based on subscription plans

?? Technology Stack

Backend Framework

  • ASP.NET Core 10.0 (.NET 10)
  • C# 14.0 - Latest language features

Database & Data Access

  • Microsoft SQL Server - Primary relational database
  • SQL Server Management Studio (SSMS) - Database management and query execution
  • Entity Framework Core 10.0 - ORM for database scaffolding and context management
  • Stored Procedures - All business logic encapsulated in SQL Server stored procedures

Caching & Performance

  • Redis 7 - In-memory data store for rate limiting counters
  • StackExchange.Redis - .NET client for Redis
  • Docker - Redis containerization

API Documentation

  • Swashbuckle.AspNetCore - Swagger/OpenAPI documentation
  • Swagger UI - Interactive API testing interface

Development Tools

  • Visual Studio 2022 (v18 Community)
  • Docker Desktop - Container management
  • Postman - API testing

?? Architecture & Design Patterns

1. Database-First Approach with Stored Procedures

This project uses a SQL-first architecture where all business logic resides in SQL Server stored procedures. This approach provides:

Benefits:

  • ? Performance: Compiled execution plans in SQL Server
  • ? Security: Parameterized queries prevent SQL injection
  • ? Maintainability: Business logic changes don't require application redeployment
  • ? Testing: Database logic can be tested independently
  • ? Separation of Concerns: Clear boundary between data and application layers

Stored Procedures Implemented:

-- Authentication & Authorization
sp_ValidateApiKey         -- Validates API key and returns client/plan info
sp_IsClientBlocked        -- Checks if a client is currently blocked

-- Rate Limiting
sp_GetRatePolicy          -- Retrieves rate limits for a specific plan

-- Administration
sp_BlockClientTemp        -- Temporarily blocks a client (with expiration)
sp_BlockClientPermanent   -- Permanently blocks a client
sp_UnblockClient          -- Removes block from a client

-- Logging & Analytics
sp_LogRequest             -- Records API request with rate limit status
sp_GetAnalytics           -- Retrieves usage analytics (if implemented)

2. Middleware Pipeline Pattern

Custom middleware for cross-cutting concerns:

HTTP Request
    ?
[Swagger/Health Endpoints] ? Skip middleware
    ?
[RateLimitMiddleware]
    ??? API Key Validation
    ??? Block Check
    ??? Rate Policy Retrieval
    ??? Redis Rate Counting
    ??? Request Forwarding
    ?
[GlobalExceptionMiddleware]
    ??? Centralized error handling
    ?
[Controllers]
    ?
HTTP Response

3. Service Layer Pattern

Services encapsulate stored procedure calls and database operations:

  • Interface segregation: Each service has a corresponding interface
  • Dependency injection: Scoped lifetime for DbContext
  • Single responsibility: Each service handles one domain area

4. DTO Pattern

Data Transfer Objects separate API contracts from database models:

  • ApiKeyValidationResultDto
  • RatePolicyResultDto
  • BlockClientRequestDto
  • ApiResponseDto

?? Project Structure (Industry Standard)

ApiProtection/
?
??? Controllers/                          # API Endpoints (Presentation Layer)
?   ??? AdminController.cs                # Admin operations (block/unblock)
?   ??? AdminAnalyticsController.cs       # Analytics endpoints
?   ??? DiagnosticsController.cs          # Health checks & diagnostics
?   ??? HealthController.cs               # Basic health endpoint
?
??? Models/                               # Database Entity Models
?   ??? ApiClient.cs                      # Client information
?   ??? ApiKey.cs                         # API keys
?   ??? BlockedClient.cs                  # Blocked clients
?   ??? Plan.cs                           # Subscription plans
?   ??? RateLimitPolicy.cs                # Rate limit configurations
?   ??? RequestLog.cs                     # Request history
?
??? DTOs/                                 # Data Transfer Objects
?   ??? ApiKeyValidationResultDto.cs      # API key validation response
?   ??? ApiResponseDto.cs                 # Standard API response
?   ??? BlockClientRequestDto.cs          # Block request payload
?   ??? RatePolicyResultDto.cs            # Rate policy response
?   ??? UnblockClientRequestDto.cs        # Unblock request payload
?
??? Data/                                 # Data Access Layer
?   ??? AppDbContext.cs                   # EF Core DbContext (scaffolded)
?
??? Services/                             # Business Logic Layer
?   ??? Interface/                        # Service contracts
?   ?   ??? IApiKeySqlService.cs
?   ?   ??? IBlockSqlService.cs
?   ?   ??? IRatePolicySqlService.cs
?   ?   ??? IRequestLogSqlService.cs
?   ?   ??? IAdminBlockSqlService.cs
?   ?   ??? IAdminAnalyticsSqlService.cs
?   ?
?   ??? ApiKeySqlService.cs               # API key validation logic
?   ??? BlockSqlService.cs                # Block check logic
?   ??? RatePolicySqlService.cs           # Rate policy retrieval
?   ??? RequestLogSqlService.cs           # Request logging
?   ??? AdminBlockSqlService.cs           # Admin block operations
?   ??? AdminAnalyticsSqlService.cs       # Analytics queries
?
??? Middleware/                           # Custom Middleware
?   ??? RateLimitMiddleware.cs            # Rate limiting & API key validation
?   ??? GlobalExceptionMiddleware.cs      # Global error handling
?
??? Program.cs                            # Application entry point & configuration
??? appsettings.json                      # Configuration (connection strings)
??? appsettings.Development.json          # Development-specific settings
??? ApiProtection.csproj                  # Project file

Why This Structure?

This is an industry-standard layered architecture that provides:

  1. Separation of Concerns: Each folder has a single responsibility
  2. Scalability: Easy to add new features without affecting existing code
  3. Testability: Each layer can be tested independently
  4. Maintainability: Clear organization makes code easy to navigate
  5. Team Collaboration: Multiple developers can work on different layers

?? Database Design

Entity Relationship Diagram

???????????????????
?   ApiClients    ?
???????????????????
? ClientId (PK)   ?????
? ClientName      ?   ?
? ContactEmail    ?   ?
? IsActive        ?   ?
? CreatedAt       ?   ?
???????????????????   ?
                       ?
                       ? 1:N
                       ?
???????????????????   ???????????????????
?   ApiKeys       ?   ? BlockedClients  ?
???????????????????   ???????????????????
? ApiKeyId (PK)   ?   ? BlockId (PK)    ?
? ApiKey (UQ)     ?   ? ClientId (FK)   ?
? ClientId (FK)   ????? Reason          ?
? PlanId (FK)     ?   ? BlockedUntil    ?
? CreatedAt       ?   ? IsPermanent     ?
???????????????????   ? CreatedAt       ?
         ?             ???????????????????
         ? N:1
         ?
???????????????????
?     Plans       ?
???????????????????
? PlanId (PK)     ?????
? PlanName (UQ)   ?   ?
? CreatedAt       ?   ?
???????????????????   ?
                       ? 1:N
                       ?
?????????????????????????
?  RateLimitPolicies    ?
?????????????????????????
? PolicyId (PK)         ?
? PlanId (FK)           ?
? WindowType            ?
? RequestLimit          ?
? WindowMinutes         ?
?????????????????????????

???????????????????
?  RequestLogs    ?
???????????????????
? LogId (PK)      ?
? ApiKeyId (FK)   ????? ApiKeys
? Endpoint        ?
? StatusCode      ?
? IsRateLimited   ?
? RequestTime     ?
???????????????????

Database Tables

ApiClients

Stores client/company information

CREATE TABLE ApiClients (
    ClientId INT PRIMARY KEY IDENTITY(1,1),
    ClientName NVARCHAR(100) NOT NULL,
    ContactEmail NVARCHAR(150),
    IsActive BIT DEFAULT 1,
    CreatedAt DATETIME2 DEFAULT SYSDATETIME()
);

ApiKeys

Stores API keys with plan associations

CREATE TABLE ApiKeys (
    ApiKeyId INT PRIMARY KEY IDENTITY(1,1),
    ApiKey NVARCHAR(200) UNIQUE NOT NULL,
    ClientId INT FOREIGN KEY REFERENCES ApiClients(ClientId),
    PlanId INT FOREIGN KEY REFERENCES Plans(PlanId),
    CreatedAt DATETIME2 DEFAULT SYSDATETIME()
);
CREATE INDEX IX_ApiKeys_ApiKey ON ApiKeys(ApiKey);

BlockedClients

Tracks blocked clients (temporary or permanent)

CREATE TABLE BlockedClients (
    BlockId INT PRIMARY KEY IDENTITY(1,1),
    ClientId INT FOREIGN KEY REFERENCES ApiClients(ClientId),
    Reason NVARCHAR(255),
    BlockedUntil DATETIME2,
    IsPermanent BIT DEFAULT 0,
    CreatedAt DATETIME2 DEFAULT SYSDATETIME()
);
CREATE INDEX IX_BlockedClients_ClientId ON BlockedClients(ClientId);

Plans

Subscription plan definitions

CREATE TABLE Plans (
    PlanId INT PRIMARY KEY IDENTITY(1,1),
    PlanName NVARCHAR(50) UNIQUE NOT NULL,
    CreatedAt DATETIME2 DEFAULT SYSDATETIME()
);

RateLimitPolicies

Rate limit configurations per plan

CREATE TABLE RateLimitPolicies (
    PolicyId INT PRIMARY KEY IDENTITY(1,1),
    PlanId INT FOREIGN KEY REFERENCES Plans(PlanId),
    WindowType NVARCHAR(10), -- 'minute' or 'day'
    RequestLimit INT NOT NULL,
    WindowMinutes INT,
    CONSTRAINT UQ_Plan_Window UNIQUE(PlanId, WindowType)
);

RequestLogs

API request history and rate limit violations

CREATE TABLE RequestLogs (
    LogId BIGINT PRIMARY KEY IDENTITY(1,1),
    ApiKeyId INT FOREIGN KEY REFERENCES ApiKeys(ApiKeyId),
    Endpoint NVARCHAR(200),
    StatusCode INT,
    IsRateLimited BIT DEFAULT 0,
    RequestTime DATETIME2 DEFAULT SYSDATETIME()
);
CREATE INDEX IX_Logs_ApiKey_Time ON RequestLogs(ApiKeyId, RequestTime);

?? Setup & Installation

Prerequisites

  • ? Visual Studio 2022 or later
  • ? .NET 10 SDK
  • ? SQL Server 2019+ or SQL Server Express
  • ? SQL Server Management Studio (SSMS)
  • ? Docker Desktop (for Redis)

Step 1: Database Setup

1.1 Create Database

CREATE DATABASE ApiProtectionDB;
GO

USE ApiProtectionDB;
GO

1.2 Create Tables

Execute the table creation scripts from the Database Design section.

1.3 Create Stored Procedures

sp_ValidateApiKey

CREATE PROCEDURE sp_ValidateApiKey
    @ApiKey NVARCHAR(200),
    @IsValid BIT OUTPUT,
    @ApiKeyId INT OUTPUT,
    @ClientId INT OUTPUT,
    @PlanId INT OUTPUT
AS
BEGIN
    SELECT 
        @IsValid = CASE WHEN k.ApiKeyId IS NOT NULL AND c.IsActive = 1 THEN 1 ELSE 0 END,
        @ApiKeyId = k.ApiKeyId,
        @ClientId = k.ClientId,
        @PlanId = k.PlanId
    FROM ApiKeys k
    INNER JOIN ApiClients c ON k.ClientId = c.ClientId
    WHERE k.ApiKey = @ApiKey;

    IF @IsValid IS NULL
        SET @IsValid = 0;
END;
GO

sp_IsClientBlocked

CREATE PROCEDURE sp_IsClientBlocked
    @ClientId INT,
    @IsBlocked BIT OUTPUT
AS
BEGIN
    SET @IsBlocked = CASE 
        WHEN EXISTS (
            SELECT 1 FROM BlockedClients 
            WHERE ClientId = @ClientId 
            AND (IsPermanent = 1 OR BlockedUntil > SYSDATETIME())
        ) THEN 1 
        ELSE 0 
    END;
END;
GO

sp_GetRatePolicy

CREATE PROCEDURE sp_GetRatePolicy
    @PlanId INT,
    @RequestsPerMinute INT OUTPUT,
    @RequestsPerDay INT OUTPUT
AS
BEGIN
    SELECT @RequestsPerMinute = RequestLimit
    FROM RateLimitPolicies
    WHERE PlanId = @PlanId AND WindowType = 'minute';

    SELECT @RequestsPerDay = RequestLimit
    FROM RateLimitPolicies
    WHERE PlanId = @PlanId AND WindowType = 'day';
END;
GO

sp_LogRequest

CREATE PROCEDURE sp_LogRequest
    @ApiKeyId INT,
    @Endpoint NVARCHAR(200),
    @StatusCode INT,
    @IsRateLimited BIT
AS
BEGIN
    INSERT INTO RequestLogs (ApiKeyId, Endpoint, StatusCode, IsRateLimited, RequestTime)
    VALUES (@ApiKeyId, @Endpoint, @StatusCode, @IsRateLimited, SYSDATETIME());
END;
GO

sp_BlockClientTemp

CREATE PROCEDURE sp_BlockClientTemp
    @ClientId INT,
    @Minutes INT,
    @Reason NVARCHAR(255)
AS
BEGIN
    INSERT INTO BlockedClients (ClientId, Reason, BlockedUntil, IsPermanent, CreatedAt)
    VALUES (@ClientId, @Reason, DATEADD(MINUTE, @Minutes, SYSDATETIME()), 0, SYSDATETIME());
END;
GO

sp_BlockClientPermanent

CREATE PROCEDURE sp_BlockClientPermanent
    @ClientId INT,
    @Reason NVARCHAR(255)
AS
BEGIN
    INSERT INTO BlockedClients (ClientId, Reason, IsPermanent, CreatedAt)
    VALUES (@ClientId, @Reason, 1, SYSDATETIME());
END;
GO

1.4 Seed Sample Data

-- Insert sample plan
INSERT INTO Plans (PlanName) VALUES ('Free'), ('Pro'), ('Enterprise');

-- Insert sample client
INSERT INTO ApiClients (ClientName, ContactEmail, IsActive)
VALUES ('Test Client', 'test@example.com', 1);

-- Insert sample API key
INSERT INTO ApiKeys (ApiKey, ClientId, PlanId)
VALUES ('TEST_API_KEY_123', 1, 1);

-- Insert rate limit policies
INSERT INTO RateLimitPolicies (PlanId, WindowType, RequestLimit, WindowMinutes)
VALUES 
    (1, 'minute', 10, 1),    -- Free: 10 req/min
    (1, 'day', 1000, NULL),   -- Free: 1000 req/day
    (2, 'minute', 60, 1),     -- Pro: 60 req/min
    (2, 'day', 10000, NULL),  -- Pro: 10000 req/day
    (3, 'minute', 300, 1),    -- Enterprise: 300 req/min
    (3, 'day', 100000, NULL); -- Enterprise: 100000 req/day

Step 2: Scaffold Database Models

Use Entity Framework Core to generate models from the existing database:

# Navigate to project directory
cd D:\S_Projects\ApiProtection

# Install EF Core tools (if not already installed)
dotnet tool install --global dotnet-ef

# Scaffold database
dotnet ef dbcontext scaffold "Server=.;Database=ApiProtectionDB;Trusted_Connection=True;TrustServerCertificate=True;" Microsoft.EntityFrameworkCore.SqlServer -o Models -c AppDbContext --context-dir Data --force

This command:

  • Connects to your SQL Server database
  • Generates model classes in the Models folder
  • Creates AppDbContext.cs in the Data folder
  • --force overwrites existing files

Step 3: Redis Setup (Docker)

3.1 Pull Redis Image

docker pull redis:7

3.2 Run Redis Container

docker run -d --name redis-api-protection -p 6379:6379 redis:7

3.3 Verify Redis is Running

docker ps
docker exec redis-api-protection redis-cli ping
# Should return: PONG

Step 4: Configure Connection String

Update appsettings.json:

{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft.AspNetCore": "Warning"
    }
  },
  "ConnectionStrings": {
    "DefaultConnection": "Server=.;Database=ApiProtectionDB;Trusted_Connection=True;TrustServerCertificate=True;"
  },
  "Redis": {
    "Configuration": "localhost:6379"
  },
  "AllowedHosts": "*"
}

Step 5: Build and Run

# Restore NuGet packages
dotnet restore

# Build project
dotnet build

# Run application
dotnet run

Or press F5 in Visual Studio.

? Key Features

1. API Key Authentication

Every request (except health/swagger) requires a valid X-API-KEY header.

2. Rate Limiting

  • Per-Minute Limits: Prevents burst traffic
  • Per-Day Limits: Controls overall usage
  • Redis-Backed: High-performance counter management
  • Automatic Expiration: Counters reset automatically

3. Client Blocking

Administrators can:

  • Block clients temporarily (with expiration)
  • Block clients permanently
  • Unblock clients
  • View block status

4. Request Logging

All API requests are logged with:

  • Endpoint accessed
  • HTTP status code
  • Rate limit violation flag
  • Timestamp

5. Analytics (If Implemented)

  • Request counts per client
  • Rate limit violations
  • Most accessed endpoints

?? API Endpoints

Health Check

GET /api/health
No authentication required

Diagnostics

GET /api/diagnostics/check-key
Headers: X-API-KEY: {your-api-key}
Response: API key details, block status, rate policy

Admin - Block Client

POST /api/admin/block
Headers: 
  X-API-KEY: {your-api-key}
  Content-Type: application/json
Body:
{
  "clientId": 1,
  "blockMinutes": 15,  // 0 for permanent
  "reason": "Policy violation"
}

Admin - Unblock Client

POST /api/admin/unblock
Headers: 
  X-API-KEY: {your-api-key}
  Content-Type: application/json
Body:
{
  "clientId": 1
}

Swagger Documentation

Navigate to: http://localhost:5174/swagger

?? Troubleshooting

Issue: "The ConnectionString property has not been initialized"

Root Cause: EF Core's Database.GetDbConnection() returns a shared connection. Using using var conn = ... disposes it, clearing the connection string.

Solution: Use _db.Database.OpenConnectionAsync() instead of conn.OpenAsync():

// ? Wrong
using var conn = _db.Database.GetDbConnection();
await conn.OpenAsync();

// ? Correct
var conn = _db.Database.GetDbConnection();
await _db.Database.OpenConnectionAsync();

Issue: Redis Connection Timeout

Solution: Ensure Redis container is running with port mapping:

docker run -d --name redis-api-protection -p 6379:6379 redis:7

Issue: 405 Method Not Allowed

Causes:

  1. CORS preflight not handled
  2. Wrong HTTP method
  3. Middleware blocking request

Solution: Add CORS configuration in Program.cs:

builder.Services.AddCors(options =>
{
    options.AddPolicy("AllowAll", policy =>
    {
        policy.AllowAnyOrigin()
              .AllowAnyMethod()
              .AllowAnyHeader();
    });
});

app.UseCors("AllowAll"); // Before UseMiddleware

?? Learning Resources

SQL Server Stored Procedures

EF Core Scaffolding

ASP.NET Core Middleware

Redis

Docker

?? Best Practices Demonstrated

  1. ? Stored Procedures for Business Logic
  2. ? Dependency Injection
  3. ? Interface-Based Design
  4. ? Repository Pattern (via Services)
  5. ? DTO Pattern
  6. ? Middleware for Cross-Cutting Concerns
  7. ? Layered Architecture
  8. ? Configuration Management
  9. ? Error Handling
  10. ? API Documentation (Swagger)

?? License

This project is for educational and demonstration purposes.

????? Author

Created as a demonstration of enterprise-level API development with SQL Server stored procedures and Redis rate limiting.

Last Updated: January 2026

About

No description, website, or topics provided.

Resources

Readme
Activity

Stars

0 stars

Watchers

0 watching

Forks

0 forks
Report repository

Releases

No releases published

Packages

 
 
 

Contributors

Languages