Audited: D:\MO_Project\ekyc\backend\src | Date: 2026-04-01 | Target: Npgsql / PostgreSQL via EF Core 8
| Area | Status | Detail |
|---|---|---|
| EF Core Provider | PASS | UseNpgsql() in Program.cs:70. No UseSqlServer anywhere. |
| NuGet Packages | PASS | Npgsql.EntityFrameworkCore.PostgreSQL 8.0.* in Infrastructure.csproj. No SqlServer package. Hangfire uses Hangfire.PostgreSql. |
| Identity Columns | PASS | All 60+ entity configs use UseIdentityAlwaysColumn() (PostgreSQL-native). Zero usage of UseIdentityColumn(). |
| Column Types | PASS | All HasColumnType() calls use PostgreSQL types: jsonb (30 cols), date (1 col). No nvarchar, datetime2, bit, or uniqueidentifier. |
| Default Value SQL | PASS | All defaults use now() and gen_random_uuid(). Zero usage of GETDATE() or NEWID(). |
| Raw SQL / T-SQL | PASS | Zero calls to FromSqlRaw, FromSqlInterpolated, ExecuteSqlRaw, or ExecuteSqlInterpolated. |
| Connection Strings | PASS | Both appsettings use PostgreSQL format: Host=localhost;Port=5432;Database=ekyc3;... |
| Database Migration SQL | PASS | All 12 SQL files use PostgreSQL syntax (TIMESTAMPTZ, BOOLEAN, gen_random_uuid, SET search_path). Zero T-SQL constructs (GO, EXEC, sp_rename). |
| DateTimeOffset Usage | PASS | All 100+ timestamp entity properties use DateTimeOffset (maps to timestamptz). Zero public DateTime properties in Domain entities. |
| DateOnly Usage | PASS | 6 properties use DateOnly (DOB fields). Supported by Npgsql 8.x, maps to PostgreSQL date. |
| # | File | Line | Severity | Issue | Fix Needed |
|---|---|---|---|---|---|
| 1 | Infrastructure\Services\Stage1\RegistrationService.cs | 1096 | WARNING | string.Equals(l.Channel, channel, StringComparison.OrdinalIgnoreCase) inside a LINQ-to-EF .Where() query. Npgsql 8.x translates this to LOWER(l.channel) = LOWER(@p), which works but bypasses indexes on the channel column. Earlier Npgsql versions may throw a client-side evaluation warning. |
Replace with l.Channel.ToLower() == channel.ToLower() or use EF.Functions.ILike(l.Channel, channel) for explicit PostgreSQL case-insensitive match. Or ensure data is stored in consistent casing and compare directly. |
| 2 | Infrastructure\Services\Stage1\RegistrationService.cs | 1095 | WARNING | l.InterestedIn.Contains("MFD") inside a LINQ-to-EF .Where(). On a string property this translates to STRPOS(interested_in, 'MFD') > 0 which is case-sensitive in PostgreSQL. If data ever has mixed casing (e.g., "mfd"), it will silently miss matches. |
If case-insensitive matching is needed, use EF.Functions.ILike(l.InterestedIn, "%MFD%"). If data is always uppercase (which appears to be the case here), current code is functionally correct. |
| 3 | Infrastructure\Services\Stage1\RegistrationService.cs | 606 | WARNING | DateTime.Now used for SMS template display text. This returns local server time, not UTC. Not a database issue, but if the server is in a different timezone than IST, the "valid until" time shown in OTP SMS will be wrong. |
Replace with DateTimeOffset.UtcNow.AddHours(5).AddMinutes(30).AddMinutes(5) (explicit IST) or use TimeZoneInfo conversion for IST. |
| 4 | Infrastructure\Services\Stage7\LivenessService.cs | 640 | INFO | c.CityName.ToLower() == city.ToLower() in a LINQ-to-EF query. This works correctly in PostgreSQL (translates to LOWER(city_name) = LOWER(@p)) but prevents index usage on city_name. |
Consider using a citext column type or a functional index CREATE INDEX ON city_master (LOWER(city_name)) if this query is performance-sensitive. |
| 5 | Program.cs (global) | n/a | INFO | No AppContext.SetSwitch("Npgsql.EnableLegacyTimestampBehavior", ...) found. This is fine—the codebase correctly uses DateTimeOffset everywhere, so the Npgsql 6+ strict timestamp behavior is satisfied. Noted for awareness only. |
No action needed. Current approach is correct and future-proof. |
The EKYC 3.0 backend is well-architected for PostgreSQL. It was clearly built with Npgsql in mind from the start—not ported from SQL Server. All entity configurations, column types, default values, identity strategies, and timestamp handling use PostgreSQL-native patterns. There are zero CRITICAL issues. The 3 warnings are edge-case concerns around case-sensitive string comparisons in LINQ queries and a minor DateTime.Now usage for display text.