ld-sysinfo-server-backend/REPORTING_API_IMPLEMENTATION.md

# Reporting API Implementation

## Overview
Backend API endpoints for compliance reporting system providing security metrics, vulnerability data, and software inventory reporting.

## Implemented Components

### 1. DTOs (Data Transfer Objects)
Location: `src/main/java/com/psg/dlsysinfo/dl_sysinfo_server/dto/`

#### ComplianceSummaryDTO
High-level security compliance metrics:
- `totalDevices` - Total device count
- `vulnerableDevices` - Devices with at least one vulnerability
- `totalVulnerabilities` - Total vulnerability count
- `criticalVulns`, `highVulns`, `mediumVulns`, `lowVulns` - Counts by severity
- `totalSoftware` - Total unique software packages
- `vulnerableSoftware` - Software with CVEs
- `lastUpdated` - Timestamp of last vulnerability scan

#### TopVulnerabilityDTO
Critical vulnerability information:
- `cveId` - CVE identifier
- `title` - Vulnerability description
- `severity` - CRITICAL/HIGH/MEDIUM/LOW
- `score` - CVSS score
- `affectedDevices` - Number of affected devices

#### VulnerableSoftwareDTO
Vulnerable software metrics:
- `softwareName` - Software package name
- `totalInstances` - Total installations
- `vulnerableInstances` - Installations with CVEs
- `totalCves` - CVE count for this software

### 2. Repository Layer
Location: `src/main/java/com/psg/dlsysinfo/dl_sysinfo_server/repository/ReportingRepository.java`

#### Compliance Summary Queries (JPQL)
- `countTotalDevices()` - Count all devices for client
- `countVulnerableDevices()` - Count devices with vulnerabilities
- `countTotalVulnerabilities()` - Total vulnerability count
- `countCriticalVulnerabilities()` - CRITICAL severity count
- `countHighVulnerabilities()` - HIGH severity count
- `countMediumVulnerabilities()` - MEDIUM severity count
- `countLowVulnerabilities()` - LOW severity count
- `countTotalSoftware()` - Unique software package count
- `countVulnerableSoftware()` - Software with CVEs > 0
- `findLastVulnerabilityScanDate()` - Last scan timestamp

#### Advanced Queries (Native SQL)
- `findTopVulnerabilitiesNative()` - Top 20 vulnerabilities by severity and device count
  - Sorted: CRITICAL > HIGH > MEDIUM > LOW, then by affected device count
  - Returns: cveId, title, severity, score, affectedDevices

- `findVulnerableSoftwareNative()` - Top 20 vulnerable software by risk score
  - Risk formula: (vulnerableInstances / totalInstances) × totalCves
  - Returns: softwareName, totalInstances, vulnerableInstances, totalCves

### 3. Service Layer
Location: `src/main/java/com/psg/dlsysinfo/dl_sysinfo_server/service/ReportingService.java`

#### Methods with Caching
1. **getComplianceSummary(clientId)**
   - Cache: 15 minutes
   - Aggregates multiple queries into single DTO
   - Handles null values with defaults

2. **getTopVulnerabilities(clientId)**
   - Cache: 30 minutes (configurable in CacheConfig)
   - Maps native query results to DTOs
   - Returns top 20 vulnerabilities

3. **getVulnerableSoftware(clientId)**
   - Cache: 60 minutes (configurable in CacheConfig)
   - Maps native query results to DTOs
   - Returns top 20 vulnerable software packages

### 4. Controller Layer
Location: `src/main/java/com/psg/dlsysinfo/dl_sysinfo_server/controller/ReportingController.java`

#### Endpoints

**GET /api/reporting/compliance-summary**
- Authentication: Required (`@PreAuthorize("isAuthenticated()")`)
- Returns: `ComplianceSummaryDTO`
- Filters by authenticated user's client
- Error handling: Returns 500 with error details on failure

**GET /api/reporting/top-vulnerabilities**
- Authentication: Required
- Returns: `List<TopVulnerabilityDTO>`
- Filters by authenticated user's client
- Error handling: Returns 500 with error details on failure

**GET /api/reporting/vulnerable-software**
- Authentication: Required
- Returns: `List<VulnerableSoftwareDTO>`
- Filters by authenticated user's client
- Error handling: Returns 500 with error details on failure

### 5. Caching Configuration
Location: `src/main/java/com/psg/dlsysinfo/dl_sysinfo_server/config/CacheConfig.java`

- **Provider**: Caffeine Cache
- **Configuration**:
  - Maximum size: 1000 entries per cache
  - Default TTL: 15 minutes
  - Cache names: `complianceSummary`, `topVulnerabilities`, `vulnerableSoftware`

### 6. Build Dependencies
Location: `build.gradle`

Added dependencies:
```gradle
implementation 'org.springframework.boot:spring-boot-starter-cache'
implementation 'com.github.ben-manes.caffeine:caffeine'
```

## Data Flow

1. **Request Flow**:
   ```
   Client Request
   → Controller (authentication/authorization)
   → Service (cache check)
   → Repository (database query)
   → Service (DTO mapping)
   → Controller (response)
   ```

2. **Security**:
   - All endpoints require authentication via JWT
   - Client isolation: queries automatically filter by authenticated user's clientId
   - No cross-client data access possible

3. **Performance Optimization**:
   - Multi-tiered caching (15min/30min/60min)
   - Native SQL for complex queries with LIMIT
   - Database queries use indexed columns (client_id, device_id, severity)

## Database Schema Requirements

### Tables Used
1. `devices` - Device inventory (indexed on client_id)
2. `cached_device_vulns` - Device-vulnerability junction (indexed on device_id, severity)
3. `cached_installed_software` - Software inventory (indexed on device_id, total_cves)
4. `clients` - Client/organization data

### Key Columns
- `client_id` - Organization identifier (FK in devices)
- `device_id` - Device identifier (FK in cached tables)
- `severity` - Vulnerability severity level
- `total_cves` - CVE count per software installation
- `last_updated` - Scan timestamp

## Error Handling

All endpoints include try-catch blocks that:
- Log errors with slf4j
- Return HTTP 500 with JSON error response
- Format: `{"error": "message", "code": 500}`

## Testing Recommendations

1. **Unit Tests**:
   - Test repository queries with H2 in-memory database
   - Mock service layer for controller tests
   - Verify DTO mapping from Object[] arrays

2. **Integration Tests**:
   - Test with realistic dataset (100+ devices)
   - Verify cache behavior
   - Test client isolation

3. **Load Tests**:
   - Verify performance under concurrent requests
   - Test cache effectiveness
   - Monitor query execution times

## Cache Invalidation Strategy

Currently, cache TTL is time-based. For production, consider:
1. Event-based invalidation on vulnerability scan completion
2. Manual cache eviction endpoint for admins
3. Cache warming on application startup

Example cache eviction (for future implementation):
```java
@CacheEvict(value = {"complianceSummary", "topVulnerabilities", "vulnerableSoftware"}, allEntries = true)
public void invalidateReportingCache() {
    log.info("Reporting cache invalidated");
}
```

## Future Enhancements

1. **Pagination**: Add pagination support for top vulnerabilities and software lists
2. **Date Ranges**: Add time-based filtering for trend analysis
3. **Export**: Add CSV/PDF export functionality
4. **Webhooks**: Notify on critical vulnerability detection
5. **Custom Thresholds**: Allow clients to set custom severity thresholds
6. **Audit Logging**: Track all reporting API access for compliance

## Deployment Notes

1. Build the project: `./gradlew build`
2. Verify application starts without errors
3. Test endpoints with valid JWT token
4. Monitor cache hit rates in production
5. Adjust cache TTL based on scan frequency

## API Usage Examples

### Compliance Summary
```bash
curl -X GET https://your-domain:8443/api/reporting/compliance-summary \
  -H "Authorization: Bearer YOUR_JWT_TOKEN"
```

### Top Vulnerabilities
```bash
curl -X GET https://your-domain:8443/api/reporting/top-vulnerabilities \
  -H "Authorization: Bearer YOUR_JWT_TOKEN"
```

### Vulnerable Software
```bash
curl -X GET https://your-domain:8443/api/reporting/vulnerable-software \
  -H "Authorization: Bearer YOUR_JWT_TOKEN"
```