fix(core): resolve P0/P1 audit findings across security, tenant isolation, and scheduler' (!1) · Merge Requests · ThinhNC / upgrade-data-crawler-be

fix(core): resolve P0/P1 audit findings across security, tenant isolation, and scheduler'

Full Project Audit & Quality Remediation Summary

Overview

This PR implements comprehensive security, architectural, and business logic fixes identified during the autonomous full-project-audit cycle. It addresses all P0 (Critical) and P1 (High) findings with minimal safe changes, strictly maintaining backwards compatibility and adhering to the repository's layered architecture (Route -> Controller -> Service -> Repository -> Prisma).

Key Changes & Fixes

Critical Fixes (P0)

SSRF Prevention in Webhook Deliveries (BUG-P0-01):
- Replaced unconstrained axios.post in WebhookDeliveryService with getSecureAxios() (secureHttpAgent / secureHttpsAgent) to block internal subnet, loopback, and cloud metadata (169.254.169.254) exploitation.
Cross-Tenant Extraction Isolation (BUG-P0-02):
- Added findByUserAndDomain to ExtractionTemplateRepository with UUID validation.
- Scoped runExtractionIfTemplate and persistBatchResults across all crawl modes (SCRAPE, SITEMAP, URL_LIST, CRAWL) to ensure user templates are never leaked or cross-executed on other tenants' crawl jobs.
Scheduler Multi-Instance Concurrency Protection (BUG-P0-03):
- Implemented atomic conditional update claimDueSchedule(id, now, nextRunAt) in CrawlScheduleRepository using PostgreSQL row-level conditions (where: { id, isActive: true, nextRunAt: { lte: now } }), preventing duplicate job execution across clustered/replicated worker instances.

High Priority Fixes (P1)

Schedule Quota & Inactive Account Enforcement (BUG-P1-01):
- Added validation checks in processDueSchedules to prevent deactivated or soft-deleted users (isActive = false or deletedAt != null) from continuing background automated crawls.
Vietnam Timezone (UTC+7) Daily Quota (BUG-P1-02):
- Fixed startOfDay calculation in CrawlJobService using Asia/Ho_Chi_Minh timezone conversion helpers, ensuring daily quotas reset accurately at 00:00:00 UTC+7.
Database Index Optimization (BUG-P1-03):
- Added @@index([crawlJobId]) on CrawlAsset.
- Added @@index([userId, createdAt]), @@index([action]), @@index([createdAt]) on AuditLog in prisma/schema.prisma.
N+1 DNS Lookup Optimization (BUG-P1-04):
- Parallelized URL verification in URL_LIST mode using chunked Promise.all (chunkSize = 10).
Layered Architecture Compliance (BUG-P1-05):
- Created src/modules/webhooks/webhook.repository.ts.
- Removed direct prisma imports from auth.middleware.ts, api-key.middleware.ts, crawl-job.service.ts, webhook-config.service.ts, and webhook-delivery.service.ts.

Medium & Minor Enhancements (P2 / P3)

CSV Formula Injection Sanitization (BUG-P2-01):
- Neutralized potential DDE / formula injection triggers (=, +, -, @, \t, \r) in csv-export.service.ts.
OpenAPI / Swagger Synchronization:
- Updated CreateCrawlJobRequest schema in swagger.ts with urls array and regenerated swagger.json.

## Full Project Audit & Quality Remediation Summary

### Overview
This PR implements comprehensive security, architectural, and business logic fixes identified during the autonomous `full-project-audit` cycle. It addresses all **P0 (Critical)** and **P1 (High)** findings with minimal safe changes, strictly maintaining backwards compatibility and adhering to the repository's layered architecture (`Route -> Controller -> Service -> Repository -> Prisma`).

---

### Key Changes & Fixes

#### Critical Fixes (P0)
- **SSRF Prevention in Webhook Deliveries (`BUG-P0-01`)**:
  - Replaced unconstrained `axios.post` in `WebhookDeliveryService` with `getSecureAxios()` (`secureHttpAgent` / `secureHttpsAgent`) to block internal subnet, loopback, and cloud metadata (`169.254.169.254`) exploitation.
- **Cross-Tenant Extraction Isolation (`BUG-P0-02`)**:
  - Added `findByUserAndDomain` to `ExtractionTemplateRepository` with UUID validation.
  - Scoped `runExtractionIfTemplate` and `persistBatchResults` across all crawl modes (`SCRAPE`, `SITEMAP`, `URL_LIST`, `CRAWL`) to ensure user templates are never leaked or cross-executed on other tenants' crawl jobs.
- **Scheduler Multi-Instance Concurrency Protection (`BUG-P0-03`)**:
  - Implemented atomic conditional update `claimDueSchedule(id, now, nextRunAt)` in `CrawlScheduleRepository` using PostgreSQL row-level conditions (`where: { id, isActive: true, nextRunAt: { lte: now } }`), preventing duplicate job execution across clustered/replicated worker instances.

#### High Priority Fixes (P1)
- **Schedule Quota & Inactive Account Enforcement (`BUG-P1-01`)**:
  - Added validation checks in `processDueSchedules` to prevent deactivated or soft-deleted users (`isActive = false` or `deletedAt != null`) from continuing background automated crawls.
- **Vietnam Timezone (UTC+7) Daily Quota (`BUG-P1-02`)**:
  - Fixed `startOfDay` calculation in `CrawlJobService` using `Asia/Ho_Chi_Minh` timezone conversion helpers, ensuring daily quotas reset accurately at `00:00:00 UTC+7`.
- **Database Index Optimization (`BUG-P1-03`)**:
  - Added `@@index([crawlJobId])` on `CrawlAsset`.
  - Added `@@index([userId, createdAt])`, `@@index([action])`, `@@index([createdAt])` on `AuditLog` in `prisma/schema.prisma`.
- **N+1 DNS Lookup Optimization (`BUG-P1-04`)**:
  - Parallelized URL verification in `URL_LIST` mode using chunked `Promise.all` (`chunkSize = 10`).
- **Layered Architecture Compliance (`BUG-P1-05`)**:
  - Created `src/modules/webhooks/webhook.repository.ts`.
  - Removed direct `prisma` imports from `auth.middleware.ts`, `api-key.middleware.ts`, `crawl-job.service.ts`, `webhook-config.service.ts`, and `webhook-delivery.service.ts`.

#### Medium & Minor Enhancements (P2 / P3)
- **CSV Formula Injection Sanitization (`BUG-P2-01`)**:
  - Neutralized potential DDE / formula injection triggers (`=, +, -, @, \t, \r`) in `csv-export.service.ts`.
- **OpenAPI / Swagger Synchronization**:
  - Updated `CreateCrawlJobRequest` schema in `swagger.ts` with `urls` array and regenerated `swagger.json`.