# Inspection Scope Migration Plan — Use `inspections.organizationId/siteId` for scope reads

## Ticket / Context
- **Request:** Org and Site id column usage update in inspection table across usage.
- **Why:** `inspections.organizationId` / `inspections.siteId` now exist and are backfilled, but many read paths still infer scope via `inspections.assetId -> assets`.
- **Risk being fixed:** incorrect scoping when inspected asset is not the site root (ex: inverter inspections), plus slower query paths due to unnecessary asset-join inference.

---

## Goal
Switch inspection-scope **read paths** to canonical inspection columns:
- `i."organizationId"`
- `i."siteId"`

Keep behavior/functionality equivalent for dashboard, ET, compare, reports, BAM, and deep links.

---

## Scope

### In Scope
- SQL scope resolvers and inspection-scope selection logic
- Backend org/site access checks that currently depend on `inspection.asset.*`
- Backend service/job context derivation for org/site from inspection
- Frontend inspection context sync (org/site switch) from inspection payload
- Frontend `Inspection` type update to include top-level `organizationId` and `siteId`
- Tests/fixtures impacted by scoping source change

### Out of Scope (explicit)
- Finding/asset rule lookup behavior
- Work order org checks
- Geospatial/hierarchy asset filtering behavior

---

## Already Done (do not re-open in this plan)
- Schema migration + backfill for `inspections.organizationId` / `inspections.siteId`.
- Writer paths (create/update/ingest/restore).
- Inspections list query migration.

---

## Problem Statement (why this change)

- `inspections.organizationId` and `inspections.siteId` are now canonical and backfilled.
- Some read paths still derive scope through `inspection.assetId -> assets` or `inspection.asset.*`.
- That causes wrong org/site scope in non-site-root inspections (for example inverter inspections), and adds unnecessary join-based inference.
- This migration standardizes scope resolution:
  - **Scope / access / context:** `inspection.organizationId`, `inspection.siteId`
  - **Inspected asset identity:** `inspection.assetId`

---

## List 1 — API mapping (API -> function(s) -> files to change)

### 1) `GET /v1/inspections` (including `compare=true` candidate mode)
- **Functions**
  - `InspectionsService.getInspections`
  - `InspectionsRepository.findComparableInspections`
- **Files**
  - `backend/src/modules/inspections/inspections.service.ts`
  - `backend/src/modules/inspections/inspections.repository.ts`
  - `backend/src/modules/inspections/sql/find-comparable-inspections.sql`

### 2) `GET /v1/inspections/findings`
- **Functions**
  - `InspectionsService.listFindings`
  - `InspectionsService.getFindings`
  - `InspectionsRepository.resolveScopedInspectionIds`
- **Files**
  - `backend/src/modules/inspections/inspections.service.ts`
  - `backend/src/modules/inspections/inspections.repository.ts`
  - `backend/src/modules/inspections/sql/resolve-scoped-inspection-ids.sql`

### 3) `GET /v1/inspections/:id/findings/summary` (legacy + scoped ET mode)
- **Functions**
  - `InspectionsService.getFindingsSummary`
  - `InspectionsRepository.resolveScopedInspectionIds`
- **Files**
  - `backend/src/modules/inspections/inspections.service.ts`
  - `backend/src/modules/inspections/inspections.repository.ts`
  - `backend/src/modules/inspections/sql/resolve-scoped-inspection-ids.sql`

### 4) `GET /v1/inspections/compare`
- **Functions**
  - `InspectionsService.compareInspections`
  - `InspectionsService.validateInspectionAccess`
  - `InspectionsRepository.compareInspectionSummaryAggregates`
  - `InspectionsRepository.findComparableInspections`
- **Files**
  - `backend/src/modules/inspections/inspections.service.ts`
  - `backend/src/modules/inspections/inspections.repository.ts`
  - `backend/src/modules/inspections/sql/compare-summary-aggregates.sql`
  - `backend/src/modules/inspections/sql/find-comparable-inspections.sql`

### 5) `GET /v1/inspections/compare/findings`
- **Functions**
  - `InspectionsService.compareInspectionFindings`
  - `InspectionsService.validateInspectionAccess`
  - `InspectionsRepository.compareInspectionFindingsPage`
- **Files**
  - `backend/src/modules/inspections/inspections.service.ts`
  - `backend/src/modules/inspections/inspections.repository.ts`
  - `backend/src/modules/inspections/sql/compare-findings-page.sql`

### 6) `GET /v1/organizations/:orgId/dashboard/widget/:widgetId`
- **Functions**
  - `DashboardRepository.getChartPowerLossTrend`
  - shared scope fragment consumed by widget repository methods (`getKpiPowerLossPct`, `getKpiRevenueAtRisk`, `getKpiFireRisk`, `getKpiDefectDensity`, `getChartAnomalyPareto`, `getRankSites`)
- **Files**
  - `backend/src/modules/dashboard/sql/resolve-dashboard-inspection-scope.sql`
  - `backend/src/modules/dashboard/sql/chart-power-loss-trend.sql`

### 7) Reports APIs (`/v1/inspections/:id/reports*`, `/v1/reports`)
- **Functions**
  - `ReportsService.validateInspectionAccess`
  - `ReportsService.getAllReports`
  - `ReportsService.getReportsForInspection`
  - `ReportsService.getReportById`
  - `ReportsService.streamReportFile`
- **Files**
  - `backend/src/modules/reports/reports.service.ts`

### 8) `GET /v1/admin/bam/inspection-pollers`
- **Functions**
  - `AdminBamService.getInspectionPollers`
- **Files**
  - `backend/src/modules/admin-bam/admin-bam.service.ts`

### 9) `POST /v1/ai-workflow/detect-anomaly`
- **Functions**
  - `AiWorkflowService.detectAnomaly`
  - `DetectAnomalyHandler.handle`
- **Files**
  - `backend/src/modules/ai-workflow/ai-workflow.service.ts`
  - `backend/src/modules/jobs/handlers/detect-anomaly.handler.ts`

### 10) `POST /v1/inspections/:id/import-anomalies`
- **Functions**
  - `InspectionsService.buildImportComputeContext`
  - `InspectionsService.importAnomalies`
  - `ImportAnomaliesHandler.handle`
  - `ImportAnomaliesHandler.handleDataplaneImport`
- **Files**
  - `backend/src/modules/inspections/inspections.service.ts`
  - `backend/src/modules/jobs/handlers/import-anomalies.handler.ts`

### 11) `PATCH /v1/ai-agent/threads/:threadId/context` (inspection access path)
- **Functions**
  - `assertThreadContextAccess`
  - `assertInspectionAccess`
- **Files**
  - `backend/src/modules/ai-agent/context/context-access.validation.ts`

### 12) `GET /v1/organizations/:orgId/assets/hierarchy` (inspection-scoped rollup path)
- **Functions**
  - `AssetService.validateInspectionInOrganization`
  - `AssetRepository.findInspectionOrgScope`
  - `siteInspectionSelectForSite`
  - `siteInspectionSelectForSiteRoots`
  - `AssetRepository.getRolledUpFindingsCountsForSiteRoots`
- **Files**
  - `backend/src/modules/asset/asset.service.ts`
  - `backend/src/modules/asset/asset.repository.ts`

### 13) Frontend consumers of the above inspection APIs (context sync)
- **Functions / logic points**
  - `Inspection` contract in API client
  - org/site deep-link synchronization logic in inspection details, AI review, and DT route flows
- **Files**
  - `frontend/src/services/api.ts`
  - `frontend/src/pages/inspections/InspectionDetailsPage.tsx`
  - `frontend/src/pages/findings/AIFindingReviewPage.tsx`
  - `frontend/src/hooks/digital-twin-v2/useDigitalTwinInspection.ts`
  - `frontend/src/pages/digital-twin-v2/DigitalTwinPage.tsx`

---

## List 2 — Files that still need changes but are NOT in List 1

### Runtime code
- None identified. All required runtime changes are connected to at least one API path in List 1.

### Test files (expected updates)
- `backend/src/modules/inspections/inspections.repository.spec.ts`
- `backend/src/modules/inspections/inspections.service.spec.ts`
- `backend/src/modules/inspections/inspection-compare.integration.spec.ts`
- `backend/src/modules/inspections/inspections.scope-summary.provided-ids.integration.spec.ts`
- `backend/src/modules/dashboard/dashboard.repository.spec.ts`
- `backend/src/modules/dashboard/dashboard.service.spec.ts`
- `backend/src/modules/dashboard/dashboard.response-contract.spec.ts`
- `backend/src/modules/reports/reports.service.spec.ts`
- `backend/src/modules/admin-bam/admin-bam.service.spec.ts`
- `backend/src/modules/ai-workflow/ai-workflow.service.spec.ts`
- `backend/src/modules/ai-agent/context/context-access.validation.spec.ts`
- `backend/src/modules/asset/asset.repository.spec.ts`
- `frontend/src/services/api.spec.ts`
- Frontend tests covering:
  - `InspectionDetailsPage.tsx`
  - `AIFindingReviewPage.tsx`
  - `useDigitalTwinInspection.ts`
  - `DigitalTwinPage.tsx`

---

## **NEW SECTION — Detailed Required Changes (file-by-file)**

> Implementation appendix. Use the API map above as the primary checklist; keep this section for low-level execution details.

## 1) `resolve-dashboard-inspection-scope.sql`
### Current problem
- Uses `i."assetId" AS site_id`
- Scopes by joining `assets` and filtering `a."organizationId"` / `a.id`

### Required change
- Scope inspections directly:
  - `i."organizationId" = $1::text`
  - `($2::text IS NULL OR i."siteId" = $2::text)`
- Emit `site_id` from `i."siteId"`.
- Keep findings-existence logic and latest-per-category semantics intact.

### Guardrail
- Do not re-introduce scope dependency on `i."assetId"` for site scoping.

### Downstream impact note (important)
- The following dashboard SQL files consume this scope via `-- __DASHBOARD_SCOPE_CTES__` and are expected to inherit behavior after resolver fix:
  - `kpi-power-loss-pct.sql`
  - `kpi-revenue-at-risk.sql`
  - `kpi-fire-risk.sql`
  - `kpi-defect-density.sql`
  - `chart-anomaly-pareto.sql`
  - `rank-sites.sql`
- These files are **verify-only** in this migration unless a hidden local scope assumption is discovered.

---

## 1b) Dashboard KPI/Chart SQLs (verify-only)

### Files
- `backend/src/modules/dashboard/sql/kpi-power-loss-pct.sql`
- `backend/src/modules/dashboard/sql/kpi-revenue-at-risk.sql`
- `backend/src/modules/dashboard/sql/kpi-fire-risk.sql`
- `backend/src/modules/dashboard/sql/kpi-defect-density.sql`
- `backend/src/modules/dashboard/sql/chart-anomaly-pareto.sql`
- `backend/src/modules/dashboard/sql/rank-sites.sql`

### What to verify
- They continue to use only `resolved_inspections` / `resolved_sites` from shared CTE marker.
- They do **not** directly read `horizongrid.inspections i` for scope.
- They do **not** introduce `i."assetId"` site scoping logic.

### What should NOT change
- No direct scope rewrite in these files unless test/data reveals incorrect local assumptions.
- Keep existing metric formulas/filters/order unchanged.

---

## 2) `chart-power-loss-trend.sql`
### Current problem
- Scopes inspections with `JOIN inspections i ON i."assetId" = ss.site_id`.

### Required change
- Scope using inspection columns:
  - `i."organizationId" = $1`
  - site filter using `i."siteId"`
- Ensure partitioning/grouping by site uses `i."siteId"`.
- Preserve existing trend output contract and sort order.

---

## 3) `resolve-scoped-inspection-ids.sql`
### Current state
- Already filters with `i."organizationId"` / `i."siteId"`.
- Still joins assets for selected asset subtree filtering.
- Latest partition currently uses `fi."assetId"`.

### Required change
- Keep asset join only for explicit subtree filter logic (`$3/$4`) where needed.
- Confirm/adjust latest partition key based on intended semantics:
  - if **site-scope latest**, partition by `fi."siteId"` + category
  - if **asset-scope latest within site**, partition by `fi."assetId"` + category
- Document decision in SQL comment to avoid regressions.

---

## 4) `find-comparable-inspections.sql`
### Current problem
- Source org check done via `assets` join.

### Required change
- Validate source inspection org using `i."organizationId" = $2` directly.
- Keep same inspected-asset candidate matching (`i."assetId"`), unless product requirement changes.

---

## 5) `asset.repository.ts`
### `findInspectionOrgScope`
- Replace org/site derivation from joined asset with direct inspection columns.

### `siteInspectionSelectForSite`
- Replace `i."assetId" = siteId` with `i."siteId" = siteId`.

### `siteInspectionSelectForSiteRoots`
- Replace `i."assetId" = ANY(siteRootIds)` with `i."siteId" = ANY(siteRootIds)`.
- Latest partition key should be `i."siteId", c.category` (site-based selection).

### `getRolledUpFindingsCountsForSiteRoots`
- In `site_inspections` CTE, join/filter using `i."siteId"` instead of `i."assetId"`.

---

## 6) `inspections.service.ts`
### Current problem
- Many call sites source org/site from `inspection.asset.organizationId/siteId`.

### Required change
- Replace inspection-scope org/site derivation with top-level inspection columns:
  - BAM sync inputs
  - findings scope fallbacks
  - inverter scope/assignment context
  - compare/org-site context mapping where applicable
- Keep `inspection.asset.*` only for asset identity/navigation concerns.

---

## 7) `reports.service.ts`
### Current problem
- `validateInspectionAccess` depends on `inspection.asset.organizationId`.

### Required change
- Select and validate using `inspection.organizationId` (and `inspection.siteId` where needed).
- Keep behavior of role/access checks unchanged.
- Preserve report filtering semantics when site filter is provided; descendant asset inclusion behavior must remain equivalent after scope migration.

---

## 8) `admin-bam.service.ts`
### Current problem
- Base scope built via inspection->asset for org/site inference.

### Required change
- Use inspection org/site columns for scope/access filtering.
- Keep asset joins only for display/metadata where truly required.

---

## 9) AI + Jobs context

## `ai-workflow.service.ts`
- Replace `siteId = inspection.assetId` with `inspection.siteId`.
- Replace org derivation from asset with `inspection.organizationId`.

## `detect-anomaly.handler.ts`
- In `handle(...)`, update inspection load + scope derivation to use inspection columns for scope:
  - keep inspection fetch include(s) needed by downstream logic.
  - replace:
    - `const siteId = inspection.assetId;`
    - `const organizationId = inspection.asset.organizationId;`
  - with:
    - `const siteId = inspection.siteId;`
    - `const organizationId = inspection.organizationId;`
- Add guard for missing `inspection.siteId` (should not happen after backfill, but fail fast with clear error for safety).
- Ensure `loadSiteAIConfig(...)` is called with `(organizationId, siteId)` from inspection columns only.
- Keep `inspection.assetId` usage only if explicitly needed for asset identity (not for scope).

## `import-anomalies.handler.ts`
- In compute context setup, replace asset-derived scope:
  - current pattern uses `inspection.asset.organizationId` / `inspection.asset.siteId`.
  - update to `inspection.organizationId` / `inspection.siteId`.
- Keep `inspection.assetId` argument passed to `processDamageGroup(...)` as-is where it represents the inspected asset identity for finding writes.
- Add explicit inline comment in code: `inspection.siteId/organizationId => scope`, `inspection.assetId => finding target asset identity`.
- Add null-site guard before building compute context (error/log + fail job clearly if absent).

## `context-access.validation.ts`
- Update `assertInspectionAccess(...)` inspection select to include top-level fields:
  - `organizationId`, `siteId`, and (only if needed) `assetId`.
- Perform org ownership check using `inspection.organizationId` (remove dependency on `inspection.asset.organizationId`).
- Perform site ownership check using `inspection.siteId` first:
  - `if (siteId && inspection.siteId !== siteId) -> Forbidden`.
  - keep backward-compatible allowance only if business explicitly requires legacy asset-id-as-site matching; otherwise remove that fallback.
- Call `assertUserSiteAccess(...)` with `siteId ?? inspection.siteId`.
- Retain nested asset select only if another non-scope validation still requires asset metadata; otherwise drop asset include/select entirely to reduce query work.

---

## 10) Frontend type + context synchronization

## `frontend/src/services/api.ts`
- Extend `Inspection` type with:
  - `organizationId: string`
  - `siteId?: string | null`

## `InspectionDetailsPage.tsx`
- For deep-link org/site sync, use `inspection.organizationId` / `inspection.siteId`.

## `AIFindingReviewPage.tsx`
- Same org/site sync migration as above.

## `useDigitalTwinInspection.ts`
- Derive effective/synced org/site from top-level inspection fields.

## `DigitalTwinPage.tsx`
- Update inspection route-context checks to top-level fields.

---

## 11) Additional assetId audit findings (new)

### Optional hardening candidates (not required for current acceptance)
- `backend/src/modules/inspections/inspection-redis-sync.service.ts`
  - Replace org/site derivation from `inspection.asset.*` with inspection top-level columns for scope-sensitive sync payloads.
- `backend/src/modules/inspections/inspection-image.service.ts`
  - Replace inspection access/context org derivation from `image.inspection.asset.organizationId` with inspection-level org/site fields where scope is intended.
- `backend/src/modules/inspections/inspection.anomaly.service.ts`
  - Replace org/site scope derivation from `inspection.asset.*` with inspection-level fields.
- `backend/src/common/pixel-to-asset/module-derivation.cli.ts`
  - For inspection-scope/org-site derivation, prefer `finding.inspection.organizationId/siteId` instead of nested asset scope fields.

### Keep as-is (intentional asset identity usage)
- `backend/src/modules/inspections/inspections.service.ts` (`importAnomaly` path)
  - `inspection.assetId` is used as the **finding asset target** (`CreateFindingDto.assetId`), not site scope.
- `backend/src/modules/jobs/handlers/import-anomalies.handler.ts`
  - `inspection.assetId` passed to `processDamageGroup` is the inspected-asset identity for finding writes.
- `backend/src/modules/jobs/handlers/database-restore.handler.ts`
  - writer/backfill behavior for `assetId` linkage is not part of read-scope migration.

### Clarification
- Rule of thumb for this migration:
  - Use `inspection.organizationId` / `inspection.siteId` for **scope, access, and context resolution**.
  - Keep `inspection.assetId` for **inspected-asset identity** and asset-linked write semantics.

---

## Decision Log (must confirm during implementation)
1. `resolve-scoped-inspection-ids.sql` latest partition key:
   - Keep `assetId + category` if ET semantics are per inspected asset.
   - Switch to `siteId + category` only if product confirms site-level latest semantics.
2. `find-comparable-inspections.sql`:
   - Keep candidate matching on same `assetId` (inspected asset identity).
   - Move only org/site validation to inspection columns.
3. Reports site filter parity:
   - Ensure pre/post migration report counts for site-scoped requests are unchanged.

---

## Implementation Phases

### Phase 1 — SQL Scope Core
1. Update inspection SQL scope resolvers (`resolve-scoped-inspection-ids`, `resolve-dashboard-inspection-scope`, `chart-power-loss-trend`, `find-comparable-inspections`).
2. Update corresponding SQL-shape specs.

### Phase 2 — Backend Scope Consumers
1. Update `asset.repository.ts` scope selectors.
2. Update `inspections.service.ts`, `reports.service.ts`, `admin-bam.service.ts`.
3. Update AI/workflow/job handlers and context validation.

### Phase 3 — Frontend Contracts and Context
1. Add `organizationId/siteId` to `Inspection` type.
2. Migrate context sync logic in details/AI review/digital twin flows.
3. Adjust affected UI tests.

### Phase 4 — Validation & Smoke
1. Backend tests (targeted + impacted integration).
2. Frontend coverage/type-check for touched flows.
3. Manual smoke: list / ET / dashboard / compare / reports / deep-link org-site switcher.

### Phase 5 — Optional hardening (only if requested after acceptance)
1. `inspection-redis-sync.service.ts`
2. `inspection-image.service.ts`
3. `inspection.anomaly.service.ts`
4. `common/pixel-to-asset/module-derivation.cli.ts`

---

## Validation Checklist

### Backend automated
- `pnpm format`
- `pnpm build`
- `pnpm lint`
- `pnpm test`

### Frontend automated
- `pnpm build`
- `pnpm lint`
- `pnpm type-check`
- `pnpm test:coverage`

### Manual smoke scenarios
1. Inspection list still resolves correctly under org and site scopes.
2. ET-related inspection scoping behavior unchanged for site + all-sites.
3. Dashboard widgets return same/better results for inspections with non-site `assetId`.
4. Compare endpoints (options + summary/findings) still return expected pair sets.
5. Reports access checks enforce org/site access correctly.
6. Deep-linked inspection pages auto-sync org/site context correctly.

### Acceptance parity checklist (before final sign-off)
- [ ] Dashboard parity validated (org/site scopes; non-site `assetId` inspections included correctly)
- [ ] ET parity validated (scoped ids and latest logic unchanged unless explicitly intended)
- [ ] Compare parity validated (candidate sets and ordering remain correct)
- [ ] Reports parity validated (site/organization filtering counts unchanged)
- [ ] BAM parity validated (poller/sync scope behavior unchanged or improved)

---

## Acceptance Mapping

- **Site scope uses `i."siteId"`, not `i."assetId"`**
  - covered in SQL resolvers + asset repository selectors.
- **Org access checks use inspection columns**
  - covered in reports/AI/context/service updates.
- **Dashboard / ET / compare / reports / BAM parity**
  - covered in phased updates and smoke checklist.
- **Tests + frontend updated**
  - covered in test file inventory + validation plan.

---

## Execution Note
- Plan only. Do not implement until explicit approval: **"approved, proceed"**.