Building an API Activity Log Table (Who Accessed What, When, and What Changed)

This article explains how to design and implement a complete API Activity Logging System in ASP.NET Core + SQL Server + Angular, allowing you to track:

Who accessed an API
Which endpoint was called
When the request happened
What input and output were sent
What data changed (field-level before/after values)

All headings follow your preferred smaller format and include practical diagrams and full implementation detail.

1. Introduction

Activity logging is essential for audit, compliance, debugging, and user accountability.
A well-designed system captures:

Request details
Response details
IP address
User identity
Browser info
Changed data fields

This article builds a centralised, scalable, query-friendly logging module suitable for enterprise systems.

2. Requirements for a Professional Activity Log System

A complete logging solution should include:

Capturing request metadata
Request body and response body
Execution time
Before/after values for update APIs
Secure storage (encrypted JSON if needed)
Dashboard for search and filtering
Pagination and export support
Minimal performance impact

3. Database Design

Table: ApiActivityLog

Column	Type	Description
LogId (PK)	BIGINT	Unique entry
UserId	INT	API caller
Endpoint	NVARCHAR(500)	URL / Controller-Action
HttpMethod	VARCHAR(50)	GET/POST/PUT/DELETE
RequestBody	NVARCHAR(MAX)	Input JSON
ResponseBody	NVARCHAR(MAX)	Output JSON
StatusCode	INT	API status
ExecutionTimeMs	INT	Time taken
IPAddress	VARCHAR(100)	Client IP
UserAgent	NVARCHAR(500)	Browser/Client
CreatedDate	DATETIME	Timestamp

Table: ApiDataChangeLog (Optional)

Column	Type	Description
ChangeId (PK)	BIGINT	Unique entry
LogId (FK)	BIGINT	Links to ApiActivityLog
TableName	NVARCHAR(200)	Affected table
PrimaryKeyValue	NVARCHAR(200)	Row identifier
FieldName	NVARCHAR(200)	Column changed
OldValue	NVARCHAR(MAX)	Before update
NewValue	NVARCHAR(MAX)	After update
ChangedDate	DATETIME	Timestamp

4. ER Diagram

+-----------------------+        1 → N       +------------------------+
|   ApiActivityLog      |------------------- |   ApiDataChangeLog     |
+-----------------------+                    +------------------------+
| LogId (PK)            |                    | ChangeId (PK)          |
| UserId                |                    | LogId (FK)             |
| Endpoint              |                    | TableName              |
| HttpMethod            |                    | FieldName              |
| RequestBody           |                    | OldValue               |
| ResponseBody          |                    | NewValue               |
| StatusCode            |                    | ChangedDate            |
+-----------------------+                    +------------------------+

5. Architecture Diagram (Visio Style)

     +--------------------+
     | Angular Frontend   |
     +---------+----------+
               |
               v
     +---------+----------+
     | ASP.NET Core API   |
     |  - Middleware Log  |
     |  - Change Tracker  |
     +---------+----------+
               |
               v
     +---------------------+
     | SQL Server Logging  |
     +---------------------+

6. Workflow / Flowchart

[Incoming API Request]
        |
        v
[Log Request Metadata]
        |
        v
[Execute Controller Action]
        |
        v
[Log Response + Status]
        |
        v
[If Update/Delete → Compare Old/New Values]
        |
        v
[Save Logs to Database]

7. ASP.NET Core Middleware for Activity Logging

7.1 Register Middleware

public void Configure(IApplicationBuilder app)
{
    app.UseMiddleware<ApiActivityLogMiddleware>();
}

7.2 Logging Middleware Code

public class ApiActivityLogMiddleware
{
    private readonly RequestDelegate _next;

    public ApiActivityLogMiddleware(RequestDelegate next)
    {
        _next = next;
    }

    public async Task Invoke(HttpContext context, AppDbContext db)
    {
        var watch = Stopwatch.StartNew();

        // Read request body
        context.Request.EnableBuffering();
        var requestBody = await new StreamReader(context.Request.Body).ReadToEndAsync();
        context.Request.Body.Position = 0;

        // Capture response
        var originalBody = context.Response.Body;
        using var newBody = new MemoryStream();
        context.Response.Body = newBody;

        await _next(context);

        watch.Stop();

        // Read response
        newBody.Position = 0;
        var responseBody = await new StreamReader(newBody).ReadToEndAsync();
        newBody.Position = 0;

        await newBody.CopyToAsync(originalBody);

        // Save log entry
        var log = new ApiActivityLog
        {
            UserId = Convert.ToInt32(context.User.FindFirst("UserId")?.Value),
            Endpoint = context.Request.Path,
            HttpMethod = context.Request.Method,
            RequestBody = requestBody,
            ResponseBody = responseBody,
            StatusCode = context.Response.StatusCode,
            ExecutionTimeMs = (int)watch.ElapsedMilliseconds,
            IPAddress = context.Connection.RemoteIpAddress?.ToString(),
            UserAgent = context.Request.Headers["User-Agent"],
            CreatedDate = DateTime.Now
        };

        db.ApiActivityLog.Add(log);
        await db.SaveChangesAsync();
    }
}

8. Tracking Field-Level Changes for PUT/PATCH

Step 1: Before update, load old record

var oldEntity = _context.Users.AsNoTracking().First(x => x.Id == model.Id);

Step 2: After update, compare fields

var differences = new List<ApiDataChangeLog>();

foreach (var prop in typeof(User).GetProperties())
{
    var oldVal = prop.GetValue(oldEntity)?.ToString();
    var newVal = prop.GetValue(model)?.ToString();

    if (oldVal != newVal)
    {
        differences.Add(new ApiDataChangeLog
        {
            LogId = logId,
            TableName = "Users",
            PrimaryKeyValue = model.Id.ToString(),
            FieldName = prop.Name,
            OldValue = oldVal,
            NewValue = newVal,
            ChangedDate = DateTime.Now
        });
    }
}

Step 3: Save change logs

_context.ApiDataChangeLog.AddRange(differences);
await _context.SaveChangesAsync();

9. Angular Dashboard for Viewing Logs

9.1 API Service

getLogs(filter: any) {
  return this.http.post('/api/logs/search', filter);
}

9.2 Log List Component

<table>
  <tr>
    <th>Endpoint</th>
    <th>User</th>
    <th>Status</th>
    <th>Time</th>
  </tr>

  <tr *ngFor="let log of logs">
    <td>{{ log.endpoint }}</td>
    <td>{{ log.userId }}</td>
    <td>{{ log.statusCode }}</td>
    <td>{{ log.createdDate | date:'medium' }}</td>
  </tr>
</table>

10. Sequence Diagram (API Logging)

Client → API → Middleware → Capture RequestClient → API → ControllerController → DB → Data UpdateDB → ControllerController → Middleware → Capture ResponseMiddleware → DB → Save Log

11. Performance Considerations

Store large Request/Response bodies in compressed format
Use async logging queue (Channel<T> or BackgroundService)
Move logs to a separate logging DB
Purge logs older than X months
Use ElasticSearch for fast searching

12. Enhancements for Production

Track login failures
Track permissions used for a request
Track file uploads and downloads
Detect unusual API usage (rate-limit alerts)
Generate weekly audit reports
Add filters in UI: Date, User, API, Status, Duration

13. Conclusion

A robust API activity logging system is an essential part of modern enterprise applications.

With a clean ER structure, ASP.NET Core middleware, and Angular dashboard, you can build a complete audit system suitable for any product.