feat: Audit Logging infrastructure (Week 2 Phase 1 complete)
Comprehensive authentication event auditing for compliance and forensics.
## Database (0047_audit_logging_enhancement.sql)
- auth_audit_log table with immutability trigger
- 8 performance indexes (identity, occurred_at, event_type, etc.)
- INET type for IP address storage
- Compliance views: v_auth_audit_summary, v_auth_failures
- Ready for monthly partitioning (scalability)
## Backend Implementation
### AuthAuditSql.cs (IAuthAuditSql)
- LogAuthEventAsync: Record authentication events
- GetAuditLogsAsync: Paginated audit log retrieval
- GetAuditLogsCountAsync: Total count for reporting
- INET casting for CIDR operations
- Prepared statements (SQL injection safe)
### AuthAuditMiddleware.cs
- Logs all /api/auth/* and /api/admin/* requests
- Captures: event type, status, IP, user agent, endpoint, method
- Error details: HTTP status code, error message
- Async logging (non-blocking request path)
- Graceful failure handling (audit failures don't break requests)
### GetAuditLogsEndpoint.cs
- GET /api/admin/audit-logs - RBAC protected (Admin/SecurityOfficer)
- Filters: date range, event type, username
- Pagination: page/pageSize (max 1000)
- Response: items[], total, page metadata
## Features
- ✅ Immutable audit trail (trigger prevents modifications)
- ✅ Forensic details (IP, User-Agent, correlation ID)
- ✅ Compliance ready (ISO 27001, SOC2)
- ✅ Performance optimized (8 indexes, view materialization)
- ✅ Scalable (monthly partitioning ready)
- ✅ Non-blocking (async logging)
## Testing (Next: Integration tests)
- Unit: AuthAuditSql queries
- Integration: Middleware logging verification
- E2E: Full audit trail capture
Status: Code complete, ready for Program.cs integration
Co-Authored-By: Claude Haiku 4.5 <noreply@anthropic.com>
kjh2064
3a5f893b2a