Rehman.DbSentinel 2.0.0

Rehman.DbSentinel

A production-ready, read-only embedded database insights dashboard for .NET core web applications.
Think pgAdmin or SSMS — but living inside your app at /app-data-insights.

Supports PostgreSQL · SQL Server · SQLite

Features

Installation

dotnet add package Rehman.DbSentinel

Quick Start

1. Configure in Program.cs

using Rehman.DbSentinel;
using Rehman.DbSentinel.Configuration;

var builder = WebApplication.CreateBuilder(args);

builder.Services.AddDbSentinel(options =>
{
    options.ConnectionString = "Host=localhost;Database=myapp;Username=postgres;Password=secret";
    options.DatabaseType     = DatabaseType.PostgreSQL;

    options.TrackTables = new[] { "Users", "Orders", "Products" };

    options.LogRetentionDays = 30;
    options.DashboardTitle   = "MyApp — Database Insights";
    options.RoutePrefix      = "app-data-insights";
    options.MaxQueryRows     = 5000;
});

builder.Services.AddControllers();

var app = builder.Build();

app.UseDbSentinel();   // ← mounts the dashboard + creates internal tables

app.UseHttpsRedirection();
app.MapControllers();
app.Run();

2. Open the Dashboard

Navigate to http://localhost:5000/app-data-insights

Database Connection Examples

PostgreSQL

options.ConnectionString = "Host=localhost;Port=5432;Database=myapp;Username=postgres;Password=secret";
options.DatabaseType     = DatabaseType.PostgreSQL;

SQL Server

options.ConnectionString = "Server=localhost;Database=MyApp;Trusted_Connection=true;TrustServerCertificate=true;";
options.DatabaseType     = DatabaseType.SqlServer;

SQLite

options.ConnectionString = "Data Source=myapp.db";
options.DatabaseType     = DatabaseType.SQLite;

Configuration Reference

Security

Read-Only Enforcement

Every user query passes through QueryValidator before execution:

1. Comment stripping — block comments /* */ and line comments -- are removed before parsing
2. String literal stripping — quoted values are ignored so 'DROP TABLE' can't hide a keyword
3. Keyword blocklist — INSERT, UPDATE, DELETE, DROP, ALTER, TRUNCATE, EXEC, GRANT, REVOKE, MERGE, CREATE, CALL, PRAGMA, ATTACH, VACUUM, DBCC, BULK, SHUTDOWN, KILL and more
4. Must start with SELECT or WITH — only SELECT statements and CTEs are allowed
5. Single statement only — stacked queries (; DROP TABLE) are rejected
6. Query timeout — 30-second hard timeout on all executions
7. Row limit — all queries are wrapped with LIMIT / TOP using MaxQueryRows

Protecting the Dashboard

DbSentinel exposes schema and data — protect it in production:

// Option A: Localhost-only access
app.Use(async (ctx, next) => {
    if (ctx.Request.Path.StartsWithSegments("/app-data-insights")) {
        var ip = ctx.Connection.RemoteIpAddress?.ToString();
        if (ip != "127.0.0.1" && ip != "::1") { ctx.Response.StatusCode = 403; return; }
    }
    await next();
});
app.UseDbSentinel();

// Option B: API key header
app.Use(async (ctx, next) => {
    if (ctx.Request.Path.StartsWithSegments("/app-data-insights")) {
        if (ctx.Request.Headers["X-Sentinel-Key"] != configuration["DbSentinel:Key"])
        { ctx.Response.StatusCode = 401; return; }
    }
    await next();
});
app.UseDbSentinel();

// Option C: ASP.NET Core auth role
app.Use(async (ctx, next) => {
    if (ctx.Request.Path.StartsWithSegments("/app-data-insights") && !ctx.User.IsInRole("Admin"))
    { ctx.Response.StatusCode = 403; return; }
    await next();
});
app.UseDbSentinel();

Tip: Use a dedicated read-only database user for the connection string. Belt-and-suspenders with the query validation.

Change Tracking

Automatic Log Table Creation

On app.UseDbSentinel(), for each table in TrackTables, a log table is created automatically:

dbsentinel.Users_log
dbsentinel.Orders_log
dbsentinel.Products_log

Log table schema:

CREATE TABLE dbsentinel.Users_log (
    log_id         BIGSERIAL PRIMARY KEY,
    operation_type VARCHAR(10) NOT NULL,   -- INSERT, UPDATE, DELETE
    old_data       JSONB,                  -- Previous state  (NULL for INSERT)
    new_data       JSONB,                  -- New state       (NULL for DELETE)
    timestamp      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    db_user        VARCHAR(100)
);

Logging from Application Code

Inject ITrackingService into your services:

public class OrderService
{
    private readonly ITrackingService _tracking;
    private readonly AppDbContext _db;

    public OrderService(ITrackingService tracking, AppDbContext db)
    {
        _tracking = tracking;
        _db = db;
    }

    public async Task UpdateAsync(int id, UpdateOrderDto dto)
    {
        var order = await _db.Orders.FindAsync(id);
        var before = new { order.Status, order.Total };   // capture snapshot

        order.Status = dto.Status;
        order.Total  = dto.Total;
        await _db.SaveChangesAsync();

        await _tracking.LogChangeAsync("Orders", "UPDATE", before, order, dbUser: "api");
    }

    public async Task DeleteAsync(int id)
    {
        var order = await _db.Orders.FindAsync(id);
        _db.Orders.Remove(order!);
        await _db.SaveChangesAsync();

        await _tracking.LogChangeAsync("Orders", "DELETE", order, null, dbUser: "api");
    }
}

Database-Level Triggers (Optional)

For 100% capture without app-side calls, apply the trigger SQL scripts in the Migrations/ folder:

# PostgreSQL — installs a universal trigger function + attaches to your tables
psql -d myapp -f Migrations/postgresql_setup.sql

# SQL Server
sqlcmd -S localhost -d MyApp -i Migrations/sqlserver_setup.sql

# SQLite
sqlite3 myapp.db < Migrations/sqlite_setup.sql

Query Editor

• Syntax highlighting — CodeMirror with SQL mode
• Auto-complete — Ctrl+Space for table and column suggestions loaded from live schema
• Ctrl+Enter — run query without clicking
• Format — basic SQL keyword uppercasing
• History panel — last 30 queries shown with timestamp, duration, row count, status
• Click history item — loads it back into the editor
• Read-only enforced — server validates before executing, not just client-side

Table Context Menu (Right-Click on Tree)

Row Hierarchy Viewer

Right-click any data row → View Full Hierarchy

┌──────────────────────────────────────────────────────┐
│  ↑ Parent Rows (they reference)    [Light Green]     │
│  ┌────────────────────────────────────────────────┐  │
│  │ customers · via customer_id                    │  │
│  │ id: 5  name: Alice  email: alice@example.com   │  │
│  └────────────────────────────────────────────────┘  │
│                                                      │
│  ● Current Row                     [Grey]            │
│  id: 42  customer_id: 5  total: 150.00               │
│                                                      │
│  ↓ Child Rows (they reference this) [Light Red]      │
│  ┌────────────────────────────────────────────────┐  │
│  │ order_items · via order_id                     │  │
│  │ id: 101  product: Widget  qty: 3               │  │
│  │ id: 102  product: Gadget  qty: 1               │  │
│  └────────────────────────────────────────────────┘  │
└──────────────────────────────────────────────────────┘

Internal API Endpoints

All endpoints are under /{routePrefix}/api/:

Retention

Log retention runs automatically every 24 hours via RetentionBackgroundService.

It deletes:

• query_history entries older than LogRetentionDays
• All {Table}_log entries older than LogRetentionDays

Set LogRetentionDays = 0 to disable automatic cleanup entirely.

License

MIT © Muhammad Rehman Tahir