APIs Repository - Performance & Reliability Audit¶

-- Common pattern in stored procedures
CREATE PROCEDURE Appointment_GetByClientId
    @ClientId INT
AS
BEGIN
    SELECT * FROM Appointments 
    WHERE ClientId = @ClientId -- Is there an index on ClientId?
    ORDER BY StartDate DESC
END

-- Run on production database
SELECT 
    OBJECT_NAME(s.object_id) AS TableName,
    i.name AS IndexName,
    s.user_seeks,
    s.user_scans,
    s.user_lookups,
    s.user_updates
FROM sys.dm_db_index_usage_stats s
INNER JOIN sys.indexes i ON s.object_id = i.object_id AND s.index_id = i.index_id
WHERE s.database_id = DB_ID('PsyterDatabase')
ORDER BY s.user_scans DESC;

-- Identify missing indexes
SELECT 
    OBJECT_NAME(d.object_id) AS TableName,
    d.equality_columns,
    d.inequality_columns,
    d.included_columns,
    s.avg_total_user_cost * s.avg_user_impact * (s.user_seeks + s.user_scans) AS ImprovementMeasure
FROM sys.dm_db_missing_index_details d
INNER JOIN sys.dm_db_missing_index_groups g ON d.index_handle = g.index_handle
INNER JOIN sys.dm_db_missing_index_group_stats s ON g.index_group_handle = s.group_handle
WHERE d.database_id = DB_ID('PsyterDatabase')
ORDER BY ImprovementMeasure DESC;

-- Based on common query patterns

-- User lookups
CREATE NONCLUSTERED INDEX IX_UserLogin_Email 
ON UserLogin(Email) INCLUDE (UserId, PasswordHash, IsActive);

CREATE NONCLUSTERED INDEX IX_UserLogin_PhoneNumber 
ON UserLogin(PhoneNumber) INCLUDE (UserId);

-- Appointment queries
CREATE NONCLUSTERED INDEX IX_Appointments_ClientId_StartDate 
ON Appointments(ClientId, StartDate DESC) INCLUDE (ProviderId, Status);

CREATE NONCLUSTERED INDEX IX_Appointments_ProviderId_StartDate 
ON Appointments(ProviderId, StartDate DESC) INCLUDE (ClientId, Status);

CREATE NONCLUSTERED INDEX IX_Appointments_Status 
ON Appointments(Status) INCLUDE (ClientId, ProviderId, StartDate);

-- Payment queries
CREATE NONCLUSTERED INDEX IX_Payments_AppointmentId 
ON Payments(AppointmentId) INCLUDE (Amount, Status, CreatedDate);

CREATE NONCLUSTERED INDEX IX_Payments_ProviderId_CreatedDate 
ON Payments(ProviderId, CreatedDate DESC) INCLUDE (Amount, Status);

-- Notification queries
CREATE NONCLUSTERED INDEX IX_Notifications_UserId_IsRead 
ON Notifications(UserId, IsRead) INCLUDE (CreatedDate, Title);

// ❌ Returns all columns, even when not needed
public List<User> GetAllUsers()
{
    using (SqlConnection connection = GetConnection())
    {
        using (SqlCommand command = new SqlCommand())
        {
            command.Connection = connection;
            command.CommandText = "User_GetAll";
            command.CommandType = CommandType.StoredProcedure;

            connection.Open();
            using (SqlDataReader reader = command.ExecuteReader())
            {
                List<User> users = new List<User>();
                while (reader.Read())
                {
                    users.Add(new User
                    {
                        UserId = reader.GetInt32(0),
                        FirstName = reader.GetString(1),
                        LastName = reader.GetString(2),
                        Email = reader.GetString(3),
                        PhoneNumber = reader.GetString(4),
                        Address = reader.GetString(5),
                        City = reader.GetString(6),
                        State = reader.GetString(7),
                        ZipCode = reader.GetString(8),
                        ProfileImage = reader.GetString(9), // Large base64 string
                        Bio = reader.GetString(10), // Large text
                        CreatedDate = reader.GetDateTime(11),
                        ModifiedDate = reader.GetDateTime(12),
                        // ... 20 more columns
                    });
                }
                return users;
            }
        }
    }
}

// ✅ Return only what's needed
public List<UserSummary> GetUserSummaries()
{
    // Stored procedure should SELECT only needed columns
    command.CommandText = "User_GetSummaries";

    // Map to lightweight DTO
    return users.Select(u => new UserSummary
    {
        UserId = u.UserId,
        FullName = $"{u.FirstName} {u.LastName}",
        Email = u.Email
        // Only 3 fields instead of 30+
    }).ToList();
}

<connectionStrings>
  <add name="PsyterDatabase" 
       connectionString="Server=.;Database=PsyterDatabase;
                        User Id=sa;Password=xxx;
                        Min Pool Size=10;
                        Max Pool Size=100;
                        Connection Timeout=30;
                        Connection Lifetime=0;
                        Pooling=true;" />
</connectionStrings>

// Add performance counters
var category = new PerformanceCounterCategory(".NET Data Provider for SqlServer");
var instanceNames = category.GetInstanceNames();

foreach (var instance in instanceNames)
{
    var poolCounter = new PerformanceCounter(
        ".NET Data Provider for SqlServer",
        "NumberOfPooledConnections",
        instance,
        true);

    Console.WriteLine($"Pooled Connections: {poolCounter.NextValue()}");
}

[Route("GetProviderAnalytics")]
public IHttpActionResult GetProviderAnalytics(int providerId, DateTime startDate, DateTime endDate)
{
    // This could query millions of rows
    var analytics = analyticsRepository.GetAnalytics(providerId, startDate, endDate);
    // ❌ May timeout on large date ranges
    return Ok(analytics);
}

[Route("GetProviderAnalytics")]
public async Task<IHttpActionResult> GetProviderAnalytics(
    int providerId, DateTime startDate, DateTime endDate)
{
    // Validate date range
    if ((endDate - startDate).TotalDays > 365)
    {
        return BadRequest("Date range cannot exceed 1 year");
    }

    // Use async with appropriate timeout
    var analytics = await analyticsRepository.GetAnalyticsAsync(
        providerId, startDate, endDate, timeoutSeconds: 120);

    return Ok(analytics);
}

// Repository
public async Task<Analytics> GetAnalyticsAsync(
    int providerId, DateTime startDate, DateTime endDate, int timeoutSeconds = 30)
{
    using (SqlConnection connection = new SqlConnection(_connectionString))
    {
        await connection.OpenAsync();

        using (SqlCommand command = new SqlCommand())
        {
            command.Connection = connection;
            command.CommandText = "Provider_GetAnalytics";
            command.CommandType = CommandType.StoredProcedure;
            command.CommandTimeout = timeoutSeconds; // ✅ Configurable timeout

            command.Parameters.AddWithValue("@ProviderId", providerId);
            command.Parameters.AddWithValue("@StartDate", startDate);
            command.Parameters.AddWithValue("@EndDate", endDate);

            using (SqlDataReader reader = await command.ExecuteReaderAsync())
            {
                // Map results
            }
        }
    }
}

[Route("GetAppointments")]
public IHttpActionResult GetAppointments(int userId)
{
    // Query 1: Get appointments
    var appointments = appointmentRepository.GetUserAppointments(userId);
    // Returns 50 appointments

    foreach (var appointment in appointments) // ❌ N+1 PROBLEM
    {
        // Query 2: Get provider details (50 queries!)
        appointment.Provider = providerRepository.GetProvider(appointment.ProviderId);

        // Query 3: Get client details (50 queries!)
        appointment.Client = userRepository.GetUser(appointment.ClientId);
    }

    // Total queries: 1 + 50 + 50 = 101 queries!
    return Ok(appointments);
}

-- Optimized stored procedure
CREATE PROCEDURE Appointment_GetUserAppointmentsWithDetails
    @UserId INT
AS
BEGIN
    SELECT 
        a.AppointmentId,
        a.StartDate,
        a.EndDate,
        a.Status,
        -- Provider details
        p.ProviderId,
        p.FirstName AS ProviderFirstName,
        p.LastName AS ProviderLastName,
        p.Specialty,
        -- Client details
        c.UserId AS ClientId,
        c.FirstName AS ClientFirstName,
        c.LastName AS ClientLastName
    FROM Appointments a
    INNER JOIN ServiceProviders p ON a.ProviderId = p.ProviderId
    INNER JOIN Users c ON a.ClientId = c.UserId
    WHERE a.ClientId = @UserId OR a.ProviderId IN (
        SELECT ProviderId FROM ServiceProviders WHERE UserId = @UserId
    )
    ORDER BY a.StartDate DESC
END

public List<Appointment> GetUserAppointmentsWithDetails(int userId)
{
    using (SqlConnection connection = GetConnection())
    {
        using (SqlCommand command = new SqlCommand())
        {
            command.Connection = connection;
            command.CommandText = "Appointment_GetUserAppointmentsWithDetails";
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.AddWithValue("@UserId", userId);

            connection.Open();
            using (SqlDataReader reader = command.ExecuteReader())
            {
                var appointments = new List<Appointment>();

                while (reader.Read())
                {
                    appointments.Add(new Appointment
                    {
                        AppointmentId = reader.GetInt32("AppointmentId"),
                        StartDate = reader.GetDateTime("StartDate"),
                        EndDate = reader.GetDateTime("EndDate"),
                        Status = reader.GetInt32("Status"),
                        Provider = new Provider
                        {
                            ProviderId = reader.GetInt32("ProviderId"),
                            FirstName = reader.GetString("ProviderFirstName"),
                            LastName = reader.GetString("ProviderLastName"),
                            Specialty = reader.GetString("Specialty")
                        },
                        Client = new User
                        {
                            UserId = reader.GetInt32("ClientId"),
                            FirstName = reader.GetString("ClientFirstName"),
                            LastName = reader.GetString("ClientLastName")
                        }
                    });
                }

                return appointments; // ✅ Single query!
            }
        }
    }
}

[Route("GetNotifications")]
public IHttpActionResult GetNotifications(int userId)
{
    var notifications = notificationRepository.GetUserNotifications(userId);
    // Returns 100 notifications

    foreach (var notification in notifications) // ❌ N+1 PROBLEM
    {
        // Get sender details for each notification (100 queries!)
        if (notification.SenderId.HasValue)
        {
            notification.Sender = userRepository.GetUser(notification.SenderId.Value);
        }
    }

    // Total: 1 + 100 = 101 queries
    return Ok(notifications);
}

public List<Notification> GetUserNotifications(int userId)
{
    var notifications = notificationRepository.GetNotifications(userId);

    // Get unique sender IDs
    var senderIds = notifications
        .Where(n => n.SenderId.HasValue)
        .Select(n => n.SenderId.Value)
        .Distinct()
        .ToList();

    // Single query to get all senders (instead of N queries)
    var senders = userRepository.GetUsersByIds(senderIds);
    var senderDictionary = senders.ToDictionary(u => u.UserId);

    // Map senders to notifications (in-memory operation)
    foreach (var notification in notifications)
    {
        if (notification.SenderId.HasValue && 
            senderDictionary.TryGetValue(notification.SenderId.Value, out var sender))
        {
            notification.Sender = sender;
        }
    }

    return notifications; // ✅ Only 2 queries instead of 101!
}

// Add to UserRepository
public List<User> GetUsersByIds(List<int> userIds)
{
    // Stored procedure with IN clause
    command.CommandText = "User_GetByIds";

    // Pass IDs as table-valued parameter or comma-separated
    var idsTable = new DataTable();
    idsTable.Columns.Add("UserId", typeof(int));
    foreach (var id in userIds)
    {
        idsTable.Rows.Add(id);
    }

    var param = command.Parameters.AddWithValue("@UserIds", idsTable);
    param.SqlDbType = SqlDbType.Structured;
    param.TypeName = "dbo.IntListType";
}

// ❌ Blocks thread during I/O
[Route("GetUser")]
public IHttpActionResult GetUser(int userId)
{
    var user = userRepository.GetUser(userId);
    // Thread is BLOCKED waiting for database
    // Cannot handle other requests during this time
    return Ok(user);
}

// ✅ Frees thread during I/O
[Route("GetUser")]
public async Task<IHttpActionResult> GetUser(int userId)
{
    var user = await userRepository.GetUserAsync(userId);
    // Thread is FREED while waiting for database
    // Can handle other requests during this time
    return Ok(user);
}

// Repository
public async Task<User> GetUserAsync(int userId)
{
    using (SqlConnection connection = new SqlConnection(_connectionString))
    {
        await connection.OpenAsync();

        using (SqlCommand command = new SqlCommand())
        {
            command.Connection = connection;
            command.CommandText = "User_GetById";
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.AddWithValue("@UserId", userId);

            using (SqlDataReader reader = await command.ExecuteReaderAsync())
            {
                if (await reader.ReadAsync())
                {
                    return MapUser(reader);
                }
            }
        }
    }

    return null;
}

[Route("GetProvider")]
public IHttpActionResult GetProvider(int providerId)
{
    var provider = providerRepository.GetProvider(providerId);

    // ❌ Returns 200+ KB of data
    return Ok(new BaseResponse
    {
        Status = 1,
        Data = new
        {
            Provider = provider, // Includes all fields
            Appointments = provider.Appointments, // 100 appointments
            Reviews = provider.Reviews, // 50 reviews
            Availability = provider.Availability, // Weekly schedule
            Education = provider.Education, // 5 education entries
            Certifications = provider.Certifications // 10 certifications
        }
    });
}

// ✅ Return minimal data, client requests more if needed
[Route("GetProvider")]
public IHttpActionResult GetProvider(int providerId)
{
    var provider = providerRepository.GetProviderBasicInfo(providerId);

    // Only 5 KB response
    return Ok(new BaseResponse
    {
        Status = 1,
        Data = new
        {
            ProviderId = provider.ProviderId,
            FirstName = provider.FirstName,
            LastName = provider.LastName,
            Specialty = provider.Specialty,
            Rating = provider.AverageRating,
            ReviewCount = provider.ReviewCount,
            ProfileImage = provider.ProfileImageUrl, // URL, not base64
            Bio = provider.Bio,
            // Links to get more data
            Links = new
            {
                Appointments = $"/api/Provider/GetAppointments?providerId={providerId}",
                Reviews = $"/api/Provider/GetReviews?providerId={providerId}",
                Availability = $"/api/Provider/GetAvailability?providerId={providerId}"
            }
        }
    });
}

// Separate paginated endpoints
[Route("GetProviderReviews")]
public IHttpActionResult GetProviderReviews(int providerId, int page = 1, int pageSize = 10)
{
    var reviews = reviewRepository.GetProviderReviews(providerId, page, pageSize);

    return Ok(new BaseResponse
    {
        Status = 1,
        Data = new
        {
            Reviews = reviews,
            TotalCount = reviews.TotalCount,
            Page = page,
            PageSize = pageSize,
            TotalPages = (int)Math.Ceiling(reviews.TotalCount / (double)pageSize)
        }
    });
}

using System.Runtime.Caching;

public class CacheService
{
    private static readonly MemoryCache _cache = MemoryCache.Default;

    public T Get<T>(string key, Func<T> fetchData, TimeSpan? expiration = null)
    {
        if (_cache.Contains(key))
        {
            return (T)_cache.Get(key);
        }

        var data = fetchData();

        var policy = new CacheItemPolicy
        {
            AbsoluteExpiration = DateTimeOffset.Now.Add(expiration ?? TimeSpan.FromMinutes(10))
        };

        _cache.Set(key, data, policy);

        return data;
    }

    public void Remove(string key)
    {
        _cache.Remove(key);
    }

    public void RemoveByPrefix(string prefix)
    {
        var keysToRemove = _cache
            .Where(kvp => kvp.Key.StartsWith(prefix))
            .Select(kvp => kvp.Key)
            .ToList();

        foreach (var key in keysToRemove)
        {
            _cache.Remove(key);
        }
    }
}

// Usage in repository
public class UserRepository
{
    private readonly CacheService _cache = new CacheService();

    public User GetUser(int userId)
    {
        string cacheKey = $"User:{userId}";

        return _cache.Get(cacheKey, () =>
        {
            // Only called if not in cache
            return FetchUserFromDatabase(userId);
        }, TimeSpan.FromMinutes(30));
    }

    public bool UpdateUser(User user)
    {
        bool success = UpdateUserInDatabase(user);

        if (success)
        {
            // Invalidate cache
            _cache.Remove($"User:{user.UserId}");
        }

        return success;
    }
}

// Install-Package StackExchange.Redis

public class RedisCache
{
    private static Lazy<ConnectionMultiplexer> _connection = new Lazy<ConnectionMultiplexer>(() =>
    {
        string connectionString = ConfigurationManager.AppSettings["Redis:ConnectionString"];
        return ConnectionMultiplexer.Connect(connectionString);
    });

    private static IDatabase Database => _connection.Value.GetDatabase();

    public T Get<T>(string key)
    {
        var value = Database.StringGet(key);

        if (value.IsNullOrEmpty)
            return default(T);

        return JsonConvert.DeserializeObject<T>(value);
    }

    public void Set<T>(string key, T value, TimeSpan? expiration = null)
    {
        var json = JsonConvert.SerializeObject(value);
        Database.StringSet(key, json, expiration);
    }

    public async Task<T> GetAsync<T>(string key)
    {
        var value = await Database.StringGetAsync(key);

        if (value.IsNullOrEmpty)
            return default(T);

        return JsonConvert.DeserializeObject<T>(value);
    }

    public async Task SetAsync<T>(string key, T value, TimeSpan? expiration = null)
    {
        var json = JsonConvert.SerializeObject(value);
        await Database.StringSetAsync(key, json, expiration);
    }

    public void Remove(string key)
    {
        Database.KeyDelete(key);
    }
}

[Route("SearchProviders")]
public async Task<IHttpActionResult> SearchProviders(string specialty, string location, int page = 1)
{
    string cacheKey = $"Search:Providers:{specialty}:{location}:{page}";

    var results = await _redisCache.GetAsync<List<ProviderSummary>>(cacheKey);

    if (results == null)
    {
        // Cache miss - query database
        results = await _providerRepository.SearchProvidersAsync(specialty, location, page);

        // Cache for 5 minutes
        await _redisCache.SetAsync(cacheKey, results, TimeSpan.FromMinutes(5));
    }

    return Ok(new BaseResponse
    {
        Status = 1,
        Data = results,
        Meta = new { CacheHit = results != null }
    });
}

// ❌ Connection not disposed if exception occurs
public User GetUser(int userId)
{
    SqlConnection connection = new SqlConnection(_connectionString);
    connection.Open();

    SqlCommand command = new SqlCommand("User_GetById", connection);
    command.CommandType = CommandType.StoredProcedure;
    command.Parameters.AddWithValue("@UserId", userId);

    SqlDataReader reader = command.ExecuteReader();
    if (reader.Read())
    {
        var user = MapUser(reader);
        reader.Close();
        connection.Close(); // ❌ Never reached if exception in MapUser()
        return user;
    }

    return null;
}

// ✅ Always disposed, even if exception
public User GetUser(int userId)
{
    using (SqlConnection connection = new SqlConnection(_connectionString))
    {
        connection.Open();

        using (SqlCommand command = new SqlCommand("User_GetById", connection))
        {
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.AddWithValue("@UserId", userId);

            using (SqlDataReader reader = command.ExecuteReader())
            {
                if (reader.Read())
                {
                    return MapUser(reader); // Exception safe
                }
            }
        }
    } // All resources disposed here, even if exception

    return null;
}

# Search for potential issues
Select-String -Path ".\Repositories\*.cs" -Pattern "SqlConnection.*=.*new.*SqlConnection" -Context 0,10
# Review each occurrence for proper disposal

// ❌ Creates large allocations that go to LOH
[Route("UploadProfileImage")]
public IHttpActionResult UploadProfileImage()
{
    var file = HttpContext.Current.Request.Files[0];

    // Large allocation (> 85 KB goes to Large Object Heap)
    byte[] imageBytes = new byte[file.ContentLength]; // Could be 5 MB
    file.InputStream.Read(imageBytes, 0, file.ContentLength);

    // Convert to base64 (doubles memory usage!)
    string base64 = Convert.ToBase64String(imageBytes);

    // Store in database (even more memory during serialization)
    userRepository.UpdateProfileImage(userId, base64);

    return Ok();
}

// ✅ Stream to Azure Blob Storage (no large allocations)
[Route("UploadProfileImage")]
public async Task<IHttpActionResult> UploadProfileImage(int userId)
{
    var file = HttpContext.Current.Request.Files[0];

    if (file.ContentLength > 5 * 1024 * 1024) // 5 MB limit
    {
        return BadRequest("File size exceeds 5 MB limit");
    }

    // Stream directly to Azure Blob Storage
    var blobClient = new BlobServiceClient(azureStorageConnectionString);
    var containerClient = blobClient.GetBlobContainerClient("profile-images");

    string blobName = $"{userId}_{Guid.NewGuid()}.jpg";
    var blobClient = containerClient.GetBlobClient(blobName);

    // Stream upload (no large memory allocation)
    await blobClient.UploadAsync(file.InputStream, new BlobHttpHeaders
    {
        ContentType = file.ContentType
    });

    // Store URL in database (not image data)
    string imageUrl = blobClient.Uri.ToString();
    userRepository.UpdateProfileImageUrl(userId, imageUrl);

    return Ok(new { ImageUrl = imageUrl });
}

// ❌ Blocks thread for entire duration
[Route("ProcessPayment")]
public IHttpActionResult ProcessPayment(PaymentRequest request)
{
    // 1. Validate (10ms) - thread BLOCKED
    ValidatePayment(request);

    // 2. Call payment gateway (500ms) - thread BLOCKED
    var gatewayResponse = _paymentGateway.ProcessPayment(request);

    // 3. Save to database (50ms) - thread BLOCKED
    _paymentRepository.SavePayment(gatewayResponse);

    // 4. Send notification (200ms) - thread BLOCKED
    _notificationService.SendPaymentConfirmation(request.UserId);

    return Ok(); // Thread blocked for 760ms total
}

// ✅ Frees thread during I/O
[Route("ProcessPayment")]
public async Task<IHttpActionResult> ProcessPayment(PaymentRequest request)
{
    // 1. Validate (synchronous, fast)
    ValidatePayment(request);

    // 2. Call payment gateway (async, frees thread)
    var gatewayResponse = await _paymentGateway.ProcessPaymentAsync(request);

    // 3. Save to database (async, frees thread)
    await _paymentRepository.SavePaymentAsync(gatewayResponse);

    // 4. Send notification (fire-and-forget, don't wait)
    _ = Task.Run(async () =>
    {
        try
        {
            await _notificationService.SendPaymentConfirmationAsync(request.UserId);
        }
        catch (Exception ex)
        {
            // Log but don't fail payment
            _logger.Error(ex, "Failed to send payment notification");
        }
    });

    return Ok(); // Returns quickly, notification sent in background
}

// ❌ Single attempt, fails on temporary issue
public Payment ProcessPayment(PaymentRequest request)
{
    try
    {
        // Network blip → immediate failure
        var response = _paymentGateway.ProcessPayment(request);
        return response;
    }
    catch (Exception ex)
    {
        ExceptionManager.LogException(ex, "PaymentController.ProcessPayment");
        throw; // User sees error, even if retry would succeed
    }
}

// Install-Package Polly

public class ResilientPaymentGateway
{
    private readonly IPaymentGateway _inner;
    private readonly IAsyncPolicy<PaymentResponse> _retryPolicy;

    public ResilientPaymentGateway(IPaymentGateway inner)
    {
        _inner = inner;

        // Retry 3 times with exponential backoff
        _retryPolicy = Policy<PaymentResponse>
            .Handle<HttpRequestException>()
            .Or<TimeoutException>()
            .WaitAndRetryAsync(
                retryCount: 3,
                sleepDurationProvider: attempt => TimeSpan.FromSeconds(Math.Pow(2, attempt)),
                onRetry: (outcome, timespan, retryAttempt, context) =>
                {
                    _logger.Warning(
                        "Payment gateway retry {RetryAttempt} after {Delay}ms due to {Exception}",
                        retryAttempt, timespan.TotalMilliseconds, outcome.Exception?.Message);
                });
    }

    public async Task<PaymentResponse> ProcessPaymentAsync(PaymentRequest request)
    {
        return await _retryPolicy.ExecuteAsync(async () =>
        {
            return await _inner.ProcessPaymentAsync(request);
        });
    }
}

// Usage:
var gateway = new SmartRoutingPaymentGateway();
var resilientGateway = new ResilientPaymentGateway(gateway);

var response = await resilientGateway.ProcessPaymentAsync(request);

Scenario:
1. VideoSDK service goes down
2. Every video call request times out (30 seconds)
3. 100 users try to start video calls
4. All 100 requests timeout
5. Thread pool exhausted
6. Entire API becomes unresponsive

Result: Total system failure due to one dependency

public class ResilientVideoSDKService
{
    private readonly IVideoSDKService _inner;
    private readonly IAsyncPolicy<VideoSession> _circuitBreakerPolicy;

    public ResilientVideoSDKService(IVideoSDKService inner)
    {
        _inner = inner;

        _circuitBreakerPolicy = Policy<VideoSession>
            .Handle<HttpRequestException>()
            .Or<TimeoutException>()
            .CircuitBreakerAsync(
                exceptionsAllowedBeforeBreaking: 5, // Open circuit after 5 failures
                durationOfBreak: TimeSpan.FromMinutes(1), // Stay open for 1 minute
                onBreak: (outcome, duration) =>
                {
                    _logger.Error("Circuit breaker opened for VideoSDK service for {Duration}", duration);
                    // Send alert to operations team
                },
                onReset: () =>
                {
                    _logger.Information("Circuit breaker closed for VideoSDK service");
                },
                onHalfOpen: () =>
                {
                    _logger.Information("Circuit breaker half-open, testing VideoSDK service");
                });
    }

    public async Task<VideoSession> CreateSessionAsync(int appointmentId)
    {
        try
        {
            return await _circuitBreakerPolicy.ExecuteAsync(async () =>
            {
                return await _inner.CreateSessionAsync(appointmentId);
            });
        }
        catch (BrokenCircuitException)
        {
            // Circuit is open, fail fast
            _logger.Warning("VideoSDK service unavailable (circuit open)");
            throw new ServiceUnavailableException("Video service temporarily unavailable");
        }
    }
}

[Route("GetProviderProfile")]
public IHttpActionResult GetProviderProfile(int providerId)
{
    var provider = _providerRepository.GetProvider(providerId);
    var reviews = _reviewRepository.GetReviews(providerId); // ❌ If this fails, entire request fails
    var availability = _videoSDK.GetAvailability(providerId); // ❌ External service

    return Ok(new
    {
        Provider = provider,
        Reviews = reviews,
        Availability = availability
    });
}

[Route("GetProviderProfile")]
public async Task<IHttpActionResult> GetProviderProfile(int providerId)
{
    var provider = await _providerRepository.GetProviderAsync(providerId);

    if (provider == null)
    {
        return NotFound();
    }

    // Try to get reviews, but don't fail if unavailable
    List<Review> reviews = null;
    try
    {
        reviews = await _reviewRepository.GetReviewsAsync(providerId);
    }
    catch (Exception ex)
    {
        _logger.Warning(ex, "Failed to load reviews for provider {ProviderId}", providerId);
        // Continue without reviews
    }

    // Try to get availability from external service
    Availability availability = null;
    try
    {
        availability = await _videoSDK.GetAvailabilityAsync(providerId);
    }
    catch (Exception ex)
    {
        _logger.Warning(ex, "Failed to load availability for provider {ProviderId}", providerId);
        // Continue without availability
    }

    return Ok(new BaseResponse
    {
        Status = 1,
        Data = new
        {
            Provider = provider,
            Reviews = reviews, // May be null
            Availability = availability, // May be null
            Warnings = BuildWarnings(reviews, availability)
        }
    });
}

private List<string> BuildWarnings(List<Review> reviews, Availability availability)
{
    var warnings = new List<string>();

    if (reviews == null)
        warnings.Add("Reviews temporarily unavailable");

    if (availability == null)
        warnings.Add("Availability information temporarily unavailable");

    return warnings;
}

<sessionState mode="InProc" timeout="20"/>

<!-- Option 1: SQL Server -->
<sessionState mode="SQLServer" 
              sqlConnectionString="Server=.;Database=SessionState;Integrated Security=true"
              timeout="20"/>

<!-- Option 2: Redis (Recommended) -->
<sessionState mode="Custom" customProvider="RedisSessionProvider">
  <providers>
    <add name="RedisSessionProvider"
         type="Microsoft.Web.Redis.RedisSessionStateProvider"
         host="psyter-cache.redis.cache.windows.net"
         port="6380"
         accessKey="..."
         ssl="true"/>
  </providers>
</sessionState>

[Client] → [API Server 1] ↘
[Client] → [API Server 2] → [Single Database] ← BOTTLENECK
[Client] → [API Server 3] ↗

[Writes] → [Primary Database]
              ↓ (Replication)
[Reads]  → [Read Replica 1]
[Reads]  → [Read Replica 2]
[Reads]  → [Read Replica 3]

public class ScalableRepository : BaseRepository
{
    private readonly string _writeConnectionString;
    private readonly List<string> _readConnectionStrings;
    private int _readReplicaIndex = 0;

    public ScalableRepository()
    {
        _writeConnectionString = ConfigurationManager.ConnectionStrings["PsyterDatabase"].ConnectionString;
        _readConnectionStrings = new List<string>
        {
            ConfigurationManager.ConnectionStrings["PsyterDatabase_Read1"].ConnectionString,
            ConfigurationManager.ConnectionStrings["PsyterDatabase_Read2"].ConnectionString,
            ConfigurationManager.ConnectionStrings["PsyterDatabase_Read3"].ConnectionString
        };
    }

    protected SqlConnection GetReadConnection()
    {
        // Round-robin across read replicas
        var connectionString = _readConnectionStrings[_readReplicaIndex];
        _readReplicaIndex = (_readReplicaIndex + 1) % _readConnectionStrings.Count;

        return new SqlConnection(connectionString);
    }

    protected SqlConnection GetWriteConnection()
    {
        return new SqlConnection(_writeConnectionString);
    }

    // Read operations use read replicas
    public async Task<User> GetUserAsync(int userId)
    {
        using (var connection = GetReadConnection())
        {
            // Use read replica
        }
    }

    // Write operations use primary
    public async Task<bool> UpdateUserAsync(User user)
    {
        using (var connection = GetWriteConnection())
        {
            // Use primary database
        }
    }
}

Users 1-10000    → Database Shard 1
Users 10001-20000 → Database Shard 2
Users 20001-30000 → Database Shard 3

public class ShardedRepository
{
    private Dictionary<int, string> _shardConnections;

    private string GetShardConnectionString(int userId)
    {
        int shardId = (userId / 10000) + 1;
        return _shardConnections[shardId];
    }

    public async Task<User> GetUserAsync(int userId)
    {
        string connectionString = GetShardConnectionString(userId);
        using (var connection = new SqlConnection(connectionString))
        {
            // Query correct shard
        }
    }
}

import http from 'k6/http';
import { check, sleep } from 'k6';

export let options = {
  stages: [
    { duration: '2m', target: 100 }, // Ramp to 100 users
    { duration: '5m', target: 100 }, // Stay at 100
    { duration: '2m', target: 500 }, // Ramp to 500
    { duration: '5m', target: 500 }, // Stay at 500
    { duration: '2m', target: 0 },   // Ramp down
  ],
  thresholds: {
    http_req_duration: ['p(95)<500'], // 95% under 500ms
    http_req_failed: ['rate<0.01'],   // < 1% errors
  },
};

export default function () {
  // Login
  let loginRes = http.post('https://api.psyter.com/User/UserLogin', JSON.stringify({
    Email: 'test@example.com',
    Password: 'password123'
  }), {
    headers: { 'Content-Type': 'application/json' },
  });

  check(loginRes, {
    'login successful': (r) => r.status === 200,
    'token received': (r) => r.json('Data.Token') !== undefined,
  });

  let token = loginRes.json('Data.Token');

  // Get appointments
  let apptRes = http.get('https://api.psyter.com/Appointment/GetAppointments', {
    headers: { 'Authorization': `Bearer ${token}` },
  });

  check(apptRes, {
    'appointments retrieved': (r) => r.status === 200,
  });

  sleep(1);
}

// Install-Package Microsoft.ApplicationInsights.Web

// Global.asax.cs
protected void Application_Start()
{
    TelemetryConfiguration.Active.InstrumentationKey = 
        ConfigurationManager.AppSettings["ApplicationInsights:InstrumentationKey"];

    // Track dependencies (SQL, HTTP)
    var builder = TelemetryConfiguration.Active.DefaultTelemetrySink.TelemetryProcessorChainBuilder;
    builder.Use((next) => new DependencyTrackingTelemetryModule(next));
    builder.Build();
}

// Usage in controllers
public class UserController : ApiController
{
    private readonly TelemetryClient _telemetry = new TelemetryClient();

    [Route("Login")]
    public async Task<IHttpActionResult> Login(LoginRequest request)
    {
        using (_telemetry.StartOperation<RequestTelemetry>("User.Login"))
        {
            _telemetry.TrackEvent("LoginAttempt", new Dictionary<string, string>
            {
                { "Email", request.Email }
            });

            var stopwatch = Stopwatch.StartNew();

            try
            {
                var user = await _userRepository.AuthenticateUserAsync(request.Email, request.Password);

                stopwatch.Stop();
                _telemetry.TrackMetric("LoginDuration", stopwatch.ElapsedMilliseconds);

                if (user == null)
                {
                    _telemetry.TrackEvent("LoginFailed");
                    return Unauthorized();
                }

                _telemetry.TrackEvent("LoginSuccess", new Dictionary<string, string>
                {
                    { "UserId", user.UserId.ToString() }
                });

                return Ok(user);
            }
            catch (Exception ex)
            {
                _telemetry.TrackException(ex);
                throw;
            }
        }
    }
}

[Route("health")]
[AllowAnonymous]
public class HealthController : ApiController
{
    [HttpGet]
    [Route("")]
    public async Task<IHttpActionResult> Check()
    {
        var health = new HealthCheckResult
        {
            Status = "Healthy",
            Timestamp = DateTime.UtcNow,
            Checks = new List<ComponentHealth>()
        };

        // Check database
        try
        {
            using (var connection = new SqlConnection(_connectionString))
            {
                await connection.OpenAsync();
                health.Checks.Add(new ComponentHealth
                {
                    Component = "Database",
                    Status = "Healthy",
                    ResponseTime = "< 10ms"
                });
            }
        }
        catch (Exception ex)
        {
            health.Status = "Unhealthy";
            health.Checks.Add(new ComponentHealth
            {
                Component = "Database",
                Status = "Unhealthy",
                Error = ex.Message
            });
        }

        // Check Redis
        try
        {
            await _redisCache.SetAsync("health-check", "ok", TimeSpan.FromSeconds(10));
            health.Checks.Add(new ComponentHealth { Component = "Redis", Status = "Healthy" });
        }
        catch
        {
            health.Checks.Add(new ComponentHealth { Component = "Redis", Status = "Degraded" });
        }

        // Check VideoSDK
        try
        {
            var response = await _httpClient.GetAsync("https://api.videosdk.live/health");
            health.Checks.Add(new ComponentHealth
            {
                Component = "VideoSDK",
                Status = response.IsSuccessStatusCode ? "Healthy" : "Unhealthy"
            });
        }
        catch
        {
            health.Checks.Add(new ComponentHealth { Component = "VideoSDK", Status = "Unavailable" });
        }

        var statusCode = health.Status == "Healthy" ? HttpStatusCode.OK : HttpStatusCode.ServiceUnavailable;
        return Content(statusCode, health);
    }
}

[Route("GetProviderList")]
[CacheOutput(ClientTimeSpan = 300, ServerTimeSpan = 300)] // 5 minute cache
public IHttpActionResult GetProviderList()
{
    var providers = _providerRepository.GetAllProviders();
    return Ok(providers);
}

<system.webServer>
  <urlCompression doStaticCompression="true" doDynamicCompression="true"/>
  <httpCompression>
    <dynamicTypes>
      <add mimeType="application/json" enabled="true"/>
    </dynamicTypes>
  </httpCompression>
</system.webServer>

// Use Json.NET settings
GlobalConfiguration.Configuration.Formatters.JsonFormatter.SerializerSettings = new JsonSerializerSettings
{
    NullValueHandling = NullValueHandling.Ignore, // Don't serialize nulls
    ReferenceLoopHandling = ReferenceLoopHandling.Ignore,
    ContractResolver = new CamelCasePropertyNamesContractResolver()
};