Inspection Scope Migration Plan

• 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.

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.

#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

• Schema migration + backfill for inspections.organizationId / inspections.siteId.
• Writer paths (create/update/ingest/restore).
• Inspections list query migration.

• 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

#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

#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

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

#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.

#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.

#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.

#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.

#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.

#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".

#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.

#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.

#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.

• Replace siteId = inspection.assetId with inspection.siteId.
• Replace org derivation from asset with inspection.organizationId.

• 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).

• 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).

• 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.

• Extend Inspection type with:
  • organizationId: string
  • siteId?: string | null

• For deep-link org/site sync, use inspection.organizationId / inspection.siteId.

• Same org/site sync migration as above.

• Derive effective/synced org/site from top-level inspection fields.

• Update inspection route-context checks to top-level fields.

#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.

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.

#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

#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)

• 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.

• Plan only. Do not implement until explicit approval: "approved, proceed".