[PERF] Add database indexes and query plans for high-volume task/finding views

[utksh1]

Problem

SecuScan needs a production-grade improvement in this area: Database performance..

Scope

Profile common dashboard, findings, reports, and task queries; add indexes; document query plans and migration impact.

Acceptance Criteria

• The implementation is focused and does not introduce unrelated UI, docs, lockfile, or formatting churn.
• Security-sensitive behavior has explicit negative tests where applicable.
• Existing tests continue to pass, and new tests cover the main success and failure paths.
• Documentation or configuration examples are updated when operator behavior changes.

Verification

Tests or benchmarks should show improved query time on a seeded large dataset.

Difficulty

Hard, useful issue intended for experienced contributors.

[KaparthyReddy]

Hi @utksh1!

When database models scale up to production workloads, un-indexed lookups on dashboard counters and finding metrics inevitably bottleneck application speed via costly full-table sequential scans. Strategically tracking down these hot query patterns, structuring composite or single-field indexes, and recording execution plans creates a dramatic reduction in IO operations and request latencies.

I would love to profile your query structures and optimize the data layer execution pathways. Here is my performance engineering blueprint:

1. Pinpointing Slow Queries & Query Execution Profiling

• Isolating Target Workloads: I will target the active dashboard aggregation views, findings tables, and task reporting query methods inside the backend service layers.
• Analyzing Execution Frameworks: I will leverage standard database engine diagnostic utilities (such as EXPLAIN ANALYZE for PostgreSQL or SQLite equivalents) to profile target operations, looking for costly sequential scans, un-indexed foreign key evaluations, or resource-heavy join operations on massive dataset tables.

2. High-Performance Indexing Strategy & Migration Documentation

• Targeted Structural Index Placement: Based on the profiling metrics, I will introduce focused single-field or composite indexes covering critical conditional parameters (such as status flags, user identifiers, or creation timestamps) to transition performance profiles from linear scans to immediate index lookups.
• Isolated Performance Verification: The patch will be clean, targeted, and safe—completely avoiding lockfile modifications or extraneous file formatting changes. I will supply a clear query plan breakdown showing the optimization impact on a seeded dataset.

I am an active contributor under GSSoC'26, my database optimization setups are fully prepared, and I can deliver a clean, well-documented performance tuning patch for this issue.

Please assign this issue to me!
/assign gssoc26