fix(core): remediate backend audit findings across P0-P3 vulnerabilities and performance issues (!2) · Merge Requests · ThinhNC / upgrade-data-crawler-be

fix(core): remediate backend audit findings across P0-P3 vulnerabilities and performance issues

Backend Audit Remediation & Quality Enhancements (P0 – P3)

Overview

This PR resolves all confirmed findings from the full-project audit across security, concurrency/memory safety, database performance, and API contract compliance.

Key Changes

1. Security & Authentication (P0 & P1)

Fail-Fast Secret Validation: Added boot-time validation requiring JWT_ACCESS_SECRET / JWT_REFRESH_SECRET (>= 32 chars) and WEBHOOK_ENCRYPTION_KEY (64-character hex string) to prevent silent fallback to default keys.
Dedicated Email Verification Secret: Isolated email verification signing via jwtConfig.emailVerificationSecret to eliminate token reuse vectors.
Cryptographic Reset Password Flow: Isolated structural decode, user resolution, and cryptographic signature validation in verifyResetToken(), ensuring no state mutation or token revocation occurs prior to verification.
Real-Time Role Enforcement: Updated authMiddleware to assign req.user.role from the freshly queried database record rather than relying on stale JWT claims.
Token Leak Prevention: Removed raw password-reset token printing from stdout/dev console logs.
CORS Configuration: Decoupled CORS configuration from email settings, added multi-origin whitelist support via CORS_ALLOWED_ORIGINS, and configured maxAge: 86400 preflight caching.

2. Database & Resource Optimization (P0 & P2)

Memory-Safe Diff Projection: Replaced full page loading (include: { pages: true }) with selective field projection (url, normalizedUrl, contentHash, wordCount, status, statusCode, title, crawledAt) during diff generation, preventing worker heap exhaustion on large crawl jobs.
Asset Pagination: Added page and limit (max 500) parameters with skip/take pagination to GET /crawl-jobs/:id/assets.
Single-Query API Key Auth: Joined the user relation in ApiKeyRepository.findByHash, eliminating a sequential redundant database roundtrip.
Database Indexing: Added composite index @@index([jobId, createdAt]) on CrawlJobLog and @@index([ipAddress]) on AuditLog in prisma/schema.prisma.
Repository Cleanup: Removed unused duplicate findByUserId methods in favor of standardized findAllByUser.

3. API Contract & Validation (P1 & P3)

Standardized Response Envelopes: Wrapped GET /crawl-jobs/:id/diff in { success: true, data: diffReport } to strictly match the API response contract.
SSE Connection Guarding: Added a 30-minute maximum stream TTL guard and increased the polling interval to 3 seconds in streamEvents to prevent lingering database connections.
Reusable Path Validation: Added validateParams() middleware in validate.middleware.ts returning standard 422 VALIDATION_ERROR envelopes.
Password Refinement: Added validation rule requiring newPassword !== currentPassword in changePasswordSchema.
OpenAPI Documentation: Updated and regenerated Swagger definitions (src/docs/swagger.json).

## Backend Audit Remediation & Quality Enhancements (P0 – P3)

### Overview
This PR resolves all confirmed findings from the full-project audit across security, concurrency/memory safety, database performance, and API contract compliance.

---

### Key Changes

#### 1. Security & Authentication (P0 & P1)
- **Fail-Fast Secret Validation**: Added boot-time validation requiring `JWT_ACCESS_SECRET` / `JWT_REFRESH_SECRET` (>= 32 chars) and `WEBHOOK_ENCRYPTION_KEY` (64-character hex string) to prevent silent fallback to default keys.
- **Dedicated Email Verification Secret**: Isolated email verification signing via `jwtConfig.emailVerificationSecret` to eliminate token reuse vectors.
- **Cryptographic Reset Password Flow**: Isolated structural decode, user resolution, and cryptographic signature validation in `verifyResetToken()`, ensuring no state mutation or token revocation occurs prior to verification.
- **Real-Time Role Enforcement**: Updated `authMiddleware` to assign `req.user.role` from the freshly queried database record rather than relying on stale JWT claims.
- **Token Leak Prevention**: Removed raw password-reset token printing from stdout/dev console logs.
- **CORS Configuration**: Decoupled CORS configuration from email settings, added multi-origin whitelist support via `CORS_ALLOWED_ORIGINS`, and configured `maxAge: 86400` preflight caching.

#### 2. Database & Resource Optimization (P0 & P2)
- **Memory-Safe Diff Projection**: Replaced full page loading (`include: { pages: true }`) with selective field projection (`url`, `normalizedUrl`, `contentHash`, `wordCount`, `status`, `statusCode`, `title`, `crawledAt`) during diff generation, preventing worker heap exhaustion on large crawl jobs.
- **Asset Pagination**: Added `page` and `limit` (max 500) parameters with `skip`/`take` pagination to `GET /crawl-jobs/:id/assets`.
- **Single-Query API Key Auth**: Joined the `user` relation in `ApiKeyRepository.findByHash`, eliminating a sequential redundant database roundtrip.
- **Database Indexing**: Added composite index `@@index([jobId, createdAt])` on `CrawlJobLog` and `@@index([ipAddress])` on `AuditLog` in `prisma/schema.prisma`.
- **Repository Cleanup**: Removed unused duplicate `findByUserId` methods in favor of standardized `findAllByUser`.

#### 3. API Contract & Validation (P1 & P3)
- **Standardized Response Envelopes**: Wrapped `GET /crawl-jobs/:id/diff` in `{ success: true, data: diffReport }` to strictly match the API response contract.
- **SSE Connection Guarding**: Added a 30-minute maximum stream TTL guard and increased the polling interval to 3 seconds in `streamEvents` to prevent lingering database connections.
- **Reusable Path Validation**: Added `validateParams()` middleware in `validate.middleware.ts` returning standard 422 `VALIDATION_ERROR` envelopes.
- **Password Refinement**: Added validation rule requiring `newPassword !== currentPassword` in `changePasswordSchema`.
- **OpenAPI Documentation**: Updated and regenerated Swagger definitions (`src/docs/swagger.json`).