cve-dashboard/.kiro/specs/vcl-compliance-reporting/tasks.md

Tasks

Task 1: Database Migration

• 1.1 Create migration file backend/migrations/add_vcl_reporting_columns.js that adds resolution_date DATE DEFAULT NULL and remediation_plan TEXT DEFAULT NULL columns to the compliance_items table using ALTER TABLE ... ADD COLUMN IF NOT EXISTS
• 1.2 In the same migration, create the compliance_snapshots table with columns: id (SERIAL PK), snapshot_month (TEXT NOT NULL), vertical (TEXT NOT NULL), total_devices (INTEGER), compliant (INTEGER), non_compliant (INTEGER), compliance_pct (NUMERIC(5,2)), created_at (TIMESTAMPTZ), with a UNIQUE constraint on (snapshot_month, vertical)
• 1.3 Add index idx_compliance_snapshots_month on compliance_snapshots(snapshot_month)
• 1.4 Run the migration and verify columns and table exist in the database

Task 2: Backend — Pure Helper Functions

• 2.1 Implement and export truncateText(text, maxLen) — returns original if length <= maxLen, otherwise first maxLen chars + "…"
• 2.2 Implement and export validateRemediationPlan(text) — returns { valid: true } if length <= 2000, otherwise { valid: false, error: "..." }
• 2.3 Implement and export isValidDateString(str) — returns true only for strings parseable as real calendar dates (rejects "2026-02-30", null, empty)
• 2.4 Implement and export formatPct(decimal) — returns Math.round(decimal * 100) + '%'
• 2.5 Implement and export computeVCLStats(items, targetPct) — computes total, in_scope, compliant, non_compliant, remediations_required, compliance_pct from an array of device objects
• 2.6 Implement and export categorizeNonCompliant(items) — partitions items into "blocked" (no resolution_date set) and "in_progress" (resolution_date set) with counts and percentages
• 2.7 Implement and export rankHeavyHitters(verticalData) — sorts verticals by non_compliant count descending
• 2.8 Implement and export computeForecastBurndown(items) — buckets items by resolution_date month, returns monthly forecast counts
• 2.9 Implement and export matchByHostname(uploadedRows, existingHostnames) — returns { matched, unmatched } arrays
• 2.10 Implement and export computeBulkDiff(matchedRows, currentData) — compares uploaded vs current values, returns change details
• 2.11 Implement and export mapColumnHeaders(headers) — maps column header strings to known field names (case-insensitive matching)

Task 3: Backend — Device Metadata Endpoint

• 3.1 Add PATCH /items/:hostname/metadata endpoint to backend/routes/compliance.js with requireAuth() and requireGroup('Admin', 'Standard_User') middleware
• 3.2 Implement request body validation: resolution_date must be a valid ISO date or null, remediation_plan must be <= 2000 chars or null
• 3.3 Update all active compliance_items rows matching the hostname with the provided fields
• 3.4 Return 404 if no rows match the hostname, 200 with { updated: count } on success
• 3.5 Log audit entry with action compliance_metadata_update

Task 4: Backend — VCL Stats Endpoint

• 4.1 Add GET /vcl/stats endpoint to backend/routes/compliance.js with requireAuth() middleware
• 4.2 Query compliance_items to compute summary statistics (total devices, compliant, non-compliant counts) using computeVCLStats
• 4.3 Compute donut chart data using categorizeNonCompliant on active non-compliant items
• 4.4 Compute heavy hitters using rankHeavyHitters grouped by team/vertical
• 4.5 Compute vertical breakdown with actual burndown (from upload history) and forecast burndown (from resolution_dates) using computeForecastBurndown
• 4.6 Return the combined JSON response with stats, donut, heavy_hitters, and vertical_breakdown

Task 5: Backend — VCL Trend Endpoint

• 5.1 Add GET /vcl/trend endpoint to backend/routes/compliance.js with requireAuth() middleware
• 5.2 Query compliance_snapshots table for monthly compliance data points
• 5.3 Compute forecast line using linear regression on the last 3+ months of actual data
• 5.4 Return monthly data array with compliant_count, compliance_pct, forecast_pct, and target_pct

Task 6: Backend — Bulk Upload Endpoints

• 6.1 Add POST /vcl/bulk-preview endpoint with requireAuth() and requireGroup('Admin', 'Standard_User') middleware
• 6.2 Validate request body: require rows array, enforce 2000 row limit, require hostname field on each row
• 6.3 Use mapColumnHeaders to identify updatable fields; return 400 if no updatable fields found
• 6.4 Use matchByHostname to separate matched/unmatched rows
• 6.5 Validate each row's fields using isValidDateString and validateRemediationPlan; flag invalid rows
• 6.6 Use computeBulkDiff to identify changed rows and return the full diff preview response
• 6.7 Add POST /vcl/bulk-commit endpoint with requireAuth() and requireGroup('Admin', 'Standard_User') middleware
• 6.8 Accept validated changes array, execute all updates in a single PostgreSQL transaction
• 6.9 Log audit entry with action compliance_bulk_update including count of rows updated

Task 7: Backend — Snapshot Creation on Upload

• 7.1 Modify the existing persistUpload function in compliance.js to insert/update a compliance_snapshots row for the current month after each upload commit
• 7.2 Compute per-vertical compliance percentages at snapshot time from the current state of compliance_items

Task 8: Frontend — Device Detail Panel Fields

• 8.1 Add a "Resolution Date" section to ComplianceDetailPanel.js with an <input type="date"> field
• 8.2 Add a "Remediation Plan" section with a <textarea> field and character counter showing current/max (2000)
• 8.3 Implement save logic that calls PATCH /api/compliance/items/:hostname/metadata on blur or explicit save button click
• 8.4 Display loading state during save and error message on failure
• 8.5 Fetch and display existing resolution_date and remediation_plan values when the panel opens (extend the existing GET /items/:hostname response)

Task 9: Frontend — Device Table Columns

• 9.1 Add "Resolution Date" column to the device table in CompliancePage.js, displaying the date or empty cell
• 9.2 Add "Remediation Plan" column to the device table, truncated to 80 characters with ellipsis using truncateText logic

Task 10: Frontend — VCL Report Page Shell

• 10.1 Create frontend/src/components/pages/VCLReportPage.js with the page layout structure (header, stats bar area, charts area, tables area)
• 10.2 Add navigation to VCLReportPage from CompliancePage (tab or button in the page header)
• 10.3 Implement data fetching on mount: call /api/compliance/vcl/stats and /api/compliance/vcl/trend
• 10.4 Add loading and error states consistent with existing page patterns

Task 11: Frontend — VCL Stats Bar

• 11.1 Implement VCLStatsBar component displaying 7 stat cards in a horizontal row: Total Devices, In-Scope, Compliant, Non-Compliant, Remediations Required, Current %, Target %
• 11.2 Style stat cards with the dark tactical theme (monospace font, teal accents, gradient backgrounds)
• 11.3 Format percentage values as whole numbers with percent sign

Task 12: Frontend — Compliance Overview Trend Chart

• 12.1 Implement ComplianceOverviewChart using Recharts ComposedChart with dual Y-axes (count left, percentage right)
• 12.2 Render compliant asset counts as green bars on the count axis
• 12.3 Render actual compliance percentage as a solid teal line on the percentage axis
• 12.4 Render forecasted compliance percentage as a dashed teal line
• 12.5 Render target compliance as a horizontal amber ReferenceLine
• 12.6 Handle edge case: when < 2 months of data, show available points without forecast line

Task 13: Frontend — Non-Compliant Assets Donut Chart

• 13.1 Implement NonCompliantDonutChart using Recharts PieChart with innerRadius/outerRadius for donut shape
• 13.2 Render "Blocked" segment in red (#EF4444) and "In-Progress" segment in amber (#F59E0B)
• 13.3 Display count and percentage labels for each segment
• 13.4 Handle edge case: single category renders full donut in that color

Task 14: Frontend — Heavy Hitters Table

• 14.1 Implement HeavyHittersTable component with columns: Vertical (with team), Non-Compliant count, Compliance Date, Notes
• 14.2 Display rows sorted by non-compliant count descending (pre-sorted from API)
• 14.3 Display empty cell when compliance date is null
• 14.4 Style table with dark theme (border colors, monospace text, teal header accents)

Task 15: Frontend — Vertical Breakdown Table

• 15.1 Implement VerticalBreakdownTable component with all specified columns: Vertical, Compliance %, Team, Non-Compliant, Actual Burndown months, Forecast Burndown months, Blockers, RAs, Notes
• 15.2 Render actual burndown columns with historical monthly counts
• 15.3 Render forecast burndown columns from current month through Q4 2026
• 15.4 Display zero values for verticals with no non-compliant devices

Task 16: Frontend — Bulk Upload Modal

• 16.1 Create BulkUploadModal component with file picker accepting .xlsx files
• 16.2 Parse uploaded xlsx file client-side using the xlsx library to extract rows and headers
• 16.3 Use column mapping logic to identify Hostname, Resolution Date, Remediation Plan, Notes columns
• 16.4 Validate fields client-side (date format, plan length) before sending to backend
• 16.5 Call POST /api/compliance/vcl/bulk-preview with parsed rows and display the diff preview
• 16.6 Render diff preview showing: matched count, unmatched count, changes count, invalid count
• 16.7 Highlight changed rows with field-level old/new value comparison
• 16.8 Display unmatched hostnames and invalid rows with error details
• 16.9 Implement confirm button that calls POST /api/compliance/vcl/bulk-commit and closes modal on success
• 16.10 Implement cancel button that discards data and closes modal without changes
• 16.11 Show error message if file has no Hostname column or no updatable fields

Task 17: Property-Based Tests

• 17.1 Create backend/__tests__/vcl-compliance-reporting.property.test.js with fast-check
• 17.2 Implement Property 2 test: truncateText returns original for short strings, truncated + ellipsis for long strings (min 100 iterations)
• 17.3 Implement Property 3 test: validateRemediationPlan accepts strings <= 2000 chars, rejects longer (min 100 iterations)
• 17.4 Implement Property 4 test: computeVCLStats produces correct arithmetic relationships (non_compliant + compliant = total, correct percentage) (min 100 iterations)
• 17.5 Implement Property 5 test: formatPct produces correct percentage string matching /^\d{1,3}%$/ (min 100 iterations)
• 17.6 Implement Property 6 test: categorizeNonCompliant partitions all items into exactly two groups summing to total (min 100 iterations)
• 17.7 Implement Property 7 test: rankHeavyHitters output is sorted in non-increasing order by non_compliant (min 100 iterations)
• 17.8 Implement Property 8 test: computeForecastBurndown monthly bucket sum equals count of items with non-null resolution_dates (min 100 iterations)
• 17.9 Implement Property 9 test: matchByHostname matched + unmatched = total input, matched hostnames all exist in set (min 100 iterations)
• 17.10 Implement Property 10 test: computeBulkDiff flags row as changed iff at least one field differs (min 100 iterations)
• 17.11 Implement Property 11 test: mapColumnHeaders correctly identifies known columns case-insensitively (min 100 iterations)
• 17.12 Implement Property 12 test: isValidDateString rejects invalid calendar dates and non-date strings (min 100 iterations)
• 17.13 Implement Property 13 test: bulk preview row counts (matched + unmatched = total) invariant holds (min 100 iterations)

Task 18: Unit and Integration Tests

• 18.1 Write unit tests for PATCH /items/:hostname/metadata endpoint (happy path, invalid date, plan too long, not found)
• 18.2 Write unit tests for GET /vcl/stats with no data (verify zero/empty response)
• 18.3 Write unit tests for bulk preview with all unmatched hostnames
• 18.4 Write unit tests for bulk preview with mixed valid/invalid rows
• 18.5 Write integration test for full bulk upload flow: preview → commit → verify DB state
• 18.6 Write unit test for trend endpoint with < 2 months of data (no forecast)