DB index optimization recommendations (PostgreSQL vs SQLite)

## Context
Reviewed latest `main` schema and query patterns in:
- `TelemetryDbContext`
- `EfTelemetryRepository` (filters, sorting, pagination, aggregations)

Current schema has several single-column indexes, which is good baseline. The app’s primary read patterns combine multiple filters + date range + descending time sort. This is where composite indexes should help most.

## Why separate recommendations per DB engine?
Yes — PostgreSQL and SQLite should have different index strategy details.

- PostgreSQL can exploit richer features (partial indexes, INCLUDE, concurrent index build, better planner for complex composites).
- SQLite supports composites but has simpler planner behavior and fewer advanced index features; over-indexing can hurt writes/file size quickly.

---

## PostgreSQL recommendations

### 1) `BuildMetrics` hot-path composite index
Most list/dashboard queries filter by environment/category/project/repo/branch/platform + date range and then sort by `ReceivedAt DESC`.

**Recommend:**
- `(BuildCategory, ExecutionEnvironment, ReceivedAt DESC)`
- `(ProjectName, RepositoryName, Branch, Platform, ReceivedAt DESC)`

Rationale: supports common equality filters first, then range/order on time.

### 2) `TestRuns` hot-path composite index
Similar filter pattern and sort behavior.

**Recommend:**
- `(ExecutionEnvironment, ReceivedAt DESC)`
- `(ProjectName, RepositoryName, Branch, Platform, TestRunner, ReceivedAt DESC)`

### 3) Partial indexes for skewed categories (optional)
Because API queries pin `BuildCategory = 'API'` and clientside queries pin `BuildCategory = 'Clientside'`, partial indexes may outperform generic ones on large tables.

**Recommend (optional):**
- `WHERE BuildCategory = 'API'`
- `WHERE BuildCategory = 'Clientside'`

### 4) Covering index for paginated list endpoints (optional)
For `GetBuildMetricsAsync`/`GetTestRunsAsync`, consider INCLUDE columns used in projection to reduce heap fetches for top-N pages.

### 5) Maintenance
- Use `CREATE INDEX CONCURRENTLY` in production.
- Validate with `EXPLAIN (ANALYZE, BUFFERS)` on representative queries.

---

## SQLite recommendations

### 1) Keep index set smaller and practical
Start with just 1–2 composites per table to balance read gains vs write cost.

**Recommend:**
- `BuildMetrics(BuildCategory, ExecutionEnvironment, ReceivedAt)`
- `TestRuns(ExecutionEnvironment, ReceivedAt)`
- `TestRuns(ProjectName, RepositoryName, Branch, Platform, ReceivedAt)`

SQLite can scan reverse order from ASC index for DESC queries in many cases, so explicit DESC index is often less critical.

### 2) Avoid too many overlapping indexes
Each insert path (`AddBuildMetricAsync`, `AddTestRunAsync`) is write-heavy; too many indexes will degrade ingest throughput.

### 3) Verify with query plans
Use `EXPLAIN QUERY PLAN` for:
- list endpoints with pagination
- summary endpoints with date filters

### 4) Consider PRAGMA tuning in non-prod local scenarios
For local-only/dev deployments, tune pragmas separately (journal mode/cache/synchronous) if needed, but keep default-safe settings for production-like envs.

---

## Suggested rollout plan
1. Capture baseline query timings for key endpoints.
2. Add PostgreSQL composites first (and optional partial indexes for API/Clientside if table volume justifies).
3. Add minimal SQLite composites.
4. Re-measure and keep only indexes with measurable benefit.

## Candidate endpoints to benchmark
- `GetBuildMetricsAsync` (paged + sorted)
- `GetTestRunsAsync` (paged + sorted)
- `GetApiBuildSummaryAsync` (category/date aggregation)
- `GetClientsideBuildSummaryAsync` (category/reload/date aggregation)
- `GetTestRunSummaryAsync` (date grouping + totals)