EES (Employee Engagement Survey) — Report Backend Engineering Documentation

Audience: Backend developers joining or maintaining the "EES - Report backend" Laravel application. Goal: Enable a new backend engineer to understand the architecture, business logic, data flow, and codebase well enough to be productive without a live knowledge-transfer session.

Method: Everything below is inferred from the actual source in this repository (d:/Downloads/EES Documentation/EES-2026/EES - Report backend). Where a fact cannot be derived from the code, it is explicitly marked "Unable to determine from the codebase." Assumptions are labelled (Assumption). Facts are stated plainly, with path/to/file.php:LINE citations.

---

Table of Contents

1. Backend Overview
2. Project Structure
3. Architecture
4. Module / Controller Breakdown
5. Request Lifecycle
6. Business Logic
7. API Layer
8. Database Interaction
9. Authentication & Authorization
10. Background Processing
11. Integrations
12. Configuration
13. Logging & Error Handling
14. Performance Considerations
15. Security
16. Developer Guide
17. Code Quality Review
18. Improvement Opportunities
19. System Diagrams
20. Appendix — Assumptions & "Unable to determine" items

---

1. Backend Overview

Purpose

This repository ("EES - Report backend") is the Laravel 8 backend for "EES" — an Employee Engagement Survey reporting/analytics platform operated by a consultancy (branding strings in the code reference "EnCs" and "bptwpakistan.com" — see app/Http/Controllers/api/NA2ApiController.php:792 and app/Http/Controllers/PortalController.php:251, an external legacy sibling system). It serves two consumers:

1. The primary consumer is a React SPA (EES-V2.0-Frontend, documented separately, not present in this checkout) that talks to this backend exclusively through the Sanctum-protected JSON API in routes/api.php. This SPA is the main admin/report web application used by client companies to view their engagement survey results (scores, NPS, demographic cuts, line-manager effectiveness, benchmarks, open-ended-feedback analysis).
2. A legacy server-rendered Blade portal (routes/web.php, session/cookie-authenticated) that renders the same kind of report pages directly as HTML views under resources/views/portal/*. This is the older UI; the routes and controller logic largely duplicate the API controllers (see §6 and §17 for the duplication analysis).

In addition, the backend exposes AI-driven report-generation endpoints (app/Http/Controllers/api/NA2ApiController.php) that call out to Anthropic Claude and OpenAI to generate narrative summaries, sentiment-tag open-ended feedback, and produce executive-summary content for the survey report. These are not a separate "survey-portal" service — they live inside this same Laravel app, under the /api/na2/* route prefix.

There is no separate "survey-api" backend in this checkout — data ingestion (the process that actually creates entries, scores, netpromoterscores, etc. from a live survey) is not present in this codebase; only the reporting/analytics layer that reads that data is present here. The user has indicated a separate survey-api backend exists but will be added later; it is intentionally not documented here since it does not exist in this repository.

Main Responsibilities

Responsibility	Owning code
Sanctum token auth for the SPA	app/Http/Controllers/api/AuthController.php
Session/Blade auth for the legacy portal	app/Http/Controllers/Auth/*, app/Http/Middleware/Authenticate.php
Survey score aggregation (Combine → Cluster → Driver → Dimension hierarchy, "ECEM" and "LMEI" score trees)	app/Http/Controllers/ProviderController.php, app/Http/Controllers/api/PortalController.php
Report pages: overview, participation, insights, breakdown, ranking, item-detail, NPS & OEF, demographic cuts, comparison, line-manager effectiveness (LME), benchmarking	app/Http/Controllers/api/PortalController.php (API/SPA) and app/Http/Controllers/PortalController.php (Blade)
Pivot-table style line-manager and demographic breakdowns	app/Http/Controllers/TestController.php, PortalController::DGPivotTableView / LMPivotTableView (both variants)
AI-generated executive summary / OEF sentiment tagging / narrative content	app/Http/Controllers/api/NA2ApiController.php, app/Services/ClaudeService.php, app/Services/OpenAIService.php
Word-cloud generation from open-ended feedback	app/Services/WordCloudService.php
Benchmark score recompute	app/Services/HelperService.php::updateBenchmarkScores
Excel bulk user import	app/Imports/UsersImport.php
Outbound bulk email (SendGrid)	app/Services/SendGridAppService.php
Public benchmark-data API consumed by an external legacy system	app/Http/Controllers/ApiController.php

High-Level Architecture

flowchart TB
    subgraph Clients
        SPA["React SPA - EES-V2.0-Frontend"]
        Legacy["Legacy Blade Portal - session-authenticated browser"]
        External["External legacy system - 2021.bptwpakistan.com"]
    end

    subgraph Laravel["Laravel 8 App (this repo)"]
        WebRoutes["routes/web.php - session + auth middleware"]
        ApiRoutes["routes/api.php - auth:sanctum"]
        Controllers["Controllers - PortalController api/web, NA2ApiController, - AuthController, ApiController, ProviderController, TestController"]
        Services["Services - ClaudeService, OpenAIService, - HelperService, ProviderService, - WordCloudService, SendGridAppService"]
        Models["Eloquent Models - User, Company, Survey, Entry, Score, ..."]
        Blade["Blade Views - resources/views/portal/*"]
    end

    DB[("MySQL - externally-managed schema")]
    Claude[["Anthropic Claude API"]]
    OpenAI[["OpenAI API"]]
    SendGrid[["SendGrid Email API"]]

    SPA -->|JSON, Bearer token| ApiRoutes
    Legacy -->|Blade views, session cookie| WebRoutes
    External -->|POST /api/get-benchmark-data| WebRoutes

    ApiRoutes --> Controllers
    WebRoutes --> Controllers
    Controllers --> Services
    Controllers --> Models
    Controllers -->|raw DB::select/insert/update| DB
    Models --> DB
    WebRoutes --> Blade

    Services --> Claude
    Services --> OpenAI
    Services --> SendGrid

Tech Stack

Layer	Technology	Version (from composer.json)
Language	PHP	^7.3\|^8.0
Framework	Laravel	^8.54
API auth	Laravel Sanctum	^2.15
Session/Blade auth scaffolding	Laravel UI	^3.3
Database	MySQL (via DB_CONNECTION=mysql default, config/database.php:18)	—
Excel import/export	maatwebsite/excel (PhpSpreadsheet wrapper)	^3.1
Outbound email	sendgrid/sendgrid	^8.0
HTTP client	guzzlehttp/guzzle (+ Laravel's Http facade, which wraps Guzzle)	^7.0.1
CORS	fruitcake/laravel-cors	^2.0
Dev/test	phpunit, facade/ignition, laravel/sail, mockery, fakerphp/faker	—

Source: composer.json:7-24.

Key Libraries in Use (by purpose)

Category	Library	Used for
API auth	laravel/sanctum	Personal-access-token auth for the SPA (auth:sanctum middleware in routes/api.php:20,27,55)
Session auth	laravel/ui + stock Laravel Auth facade	Blade-portal login (app/Http/Controllers/Auth/LoginController.php and siblings)
Spreadsheet	maatwebsite/excel	App\Imports\UsersImport — bulk user creation from an uploaded spreadsheet
Email	sendgrid/sendgrid	App\Services\SendGridAppService::sendBulkEmail — bulk transactional email
HTTP	guzzlehttp/guzzle (directly, in OpenAIService) and Laravel's Http facade (in ClaudeService, and PortalController::benchmarkRequest)	Calling OpenAI, Anthropic Claude, and a legacy external benchmark API
CORS	fruitcake/laravel-cors	Registered as global middleware \Fruitcake\Cors\HandleCors::class in app/Http/Kernel.php:19

---

2. Project Structure

This is a stock Laravel 8 application skeleton — there are no custom top-level "modules"; everything lives in the conventional app/ tree. The business logic is concentrated almost entirely in a small number of very large controllers rather than being split into domain packages.

EES - Report backend/
├── app/
│   ├── Console/
│   │   └── Kernel.php              # scheduler entry point (empty schedule)
│   ├── Exceptions/
│   │   └── Handler.php             # stock Laravel exception handler (unmodified)
│   ├── Http/
│   │   ├── Controllers/
│   │   │   ├── Auth/               # Laravel UI scaffolding (Login/Register/ForgotPassword/...)
│   │   │   ├── api/                # JSON API controllers consumed by the SPA (Sanctum)
│   │   │   │   ├── AuthController.php
│   │   │   │   ├── NA2ApiController.php     # AI report-generation endpoints
│   │   │   │   └── PortalController.php     # 2410 lines — the bulk of the report/analytics API
│   │   │   ├── ApiController.php            # public /api/get-benchmark-data endpoint
│   │   │   ├── Controller.php               # base controller
│   │   │   ├── PortalController.php         # 831 lines — Blade equivalent of api/PortalController
│   │   │   ├── ProviderController.php       # 1016 lines — shared score-calculation query layer
│   │   │   └── TestController.php           # pivot-table diagnostic views (auth-protected)
│   │   ├── Middleware/
│   │   │   ├── Authenticate.php             # customized redirectTo()
│   │   │   ├── FirstLogin.php                # gate on Auth::user()->first_login
│   │   │   ├── RoleAdmin.php / RoleClient.php / RoleEmp.php   # numeric role-id gates
│   │   │   ├── EncryptCookies.php, TrimStrings.php, TrustHosts.php,
│   │   │   │   TrustProxies.php, VerifyCsrfToken.php,
│   │   │   │   PreventRequestsDuringMaintenance.php, RedirectIfAuthenticated.php  # stock Laravel
│   │   ├── Requests/
│   │   │   ├── ChangeBenchmarkRequest.php   # validates companies[] array (5-10 items)
│   │   │   └── DemographicChangeRequest.php # validates filter_demographic required
│   │   └── Kernel.php                       # middleware groups & route-middleware aliases
│   ├── Imports/
│   │   └── UsersImport.php          # maatwebsite/excel ToModel import
│   ├── Models/                      # 14 Eloquent models — see §8
│   ├── Providers/                   # stock Laravel service providers (unmodified except routing)
│   └── Services/
│       ├── ClaudeService.php        # Anthropic Claude API client (OEF sentiment/theme extraction)
│       ├── HelperService.php        # score-formatting + benchmark recompute helpers
│       ├── OpenAIService.php        # OpenAI chat-completions client (OEF answer summarization)
│       ├── ProviderService.php      # legacy 360-style "questionnaire" scoring helpers (see note)
│       ├── SendGridAppService.php   # bulk email via SendGrid SDK
│       └── WordCloudService.php     # stop-word-filtered word/bigram frequency extraction
├── bootstrap/app.php                # stock Laravel bootstrap
├── config/                          # stock Laravel config, plus services.php holds AI/SendGrid keys
├── database/
│   ├── factories/UserFactory.php
│   ├── migrations/                  # only 5 files — see §8 for what this implies
│   └── seeders/DatabaseSeeder.php
├── public/
│   ├── index.php                    # front controller
│   └── data/                        # ai_content_json/*.json + oef_ai_content.json (static AI fallback content)
├── resources/
│   ├── lang/en/                     # stock Laravel translation strings
│   └── views/
│       ├── auth/login.blade.php
│       ├── frontend/index.blade.php # SPA bootstrap shell ("hello world" placeholder — see note below)
│       ├── layouts/                 # portal.blade.php, demographic.blade.php
│       ├── portal/                  # consolidated/, demographic/, comparison/, introduction/ view trees
│       └── test/                    # dg-pivot-table.blade.php, lm-pivot-table.blade.php
├── routes/
│   ├── api.php                      # 67 lines — Sanctum JSON API (no prefix beyond /api)
│   ├── channels.php                 # stock broadcast channel (App.Models.User.{id})
│   ├── console.php                  # stock `inspire` artisan command
│   └── web.php                      # 68 lines — session/Blade routes + public benchmark endpoint
├── server.php                       # PHP built-in server entry point
└── tests/                           # stock PHPUnit scaffolding (ExampleTest only — no real tests)

Note on resources/views/frontend/index.blade.php: this file (resources/views/frontend/index.blade.php:1-12) is a literal <h1>hello world</h1> placeholder, not an SPA bootstrap shell with asset tags. The route Route::get('/{any}', ...)->where('any', '.*') in routes/web.php:66-68 (a catch-all SPA fallback) currently serves this placeholder rather than a compiled React index.html. This strongly suggests the React SPA (EES-V2.0-Frontend) is built/deployed and served separately (e.g. a different web root, CDN, or reverse-proxy rule) and does not rely on this Blade view in production — but this cannot be confirmed from this repository alone.

Folder-by-folder notes

• app/Http/Controllers/api/ — the "real" API surface for the SPA. PortalController.php here is the largest file in the app (2410 lines) and contains virtually all of the report/analytics logic exposed as JSON.
• app/Http/Controllers/PortalController.php (non-api namespace) — the Blade-rendering twin of the above. Large stretches of SQL and business logic are duplicated between the two (see §6 and §17).
• app/Http/Controllers/ProviderController.php — a static-method-only "service" class (despite being namespaced as a Controller) that both PortalController variants call into for the core score-aggregation SQL (Combine/Cluster/Driver/Dimension roll-ups against the calculations table).
• app/Services/ProviderService.php — a different, apparently legacy/dead scoring implementation. It references tables (participants, questionnaires, likerts, components) and columns (rater_type_id) that do not appear anywhere else in the codebase's query surface (which otherwise consistently uses entries, scores, dimensions/drivers/clusters/combines, netpromoterscores). Nothing in routes/api.php or routes/web.php references ProviderService. It looks like it was carried over from a different (360-degree feedback / "questionnaire") product and is unused dead code in this app — see §20.
• app/Models/ — thin Eloquent models; most only declare relationships, several declare nothing but use HasFactory. The schema is not created by these migrations; see §8.
• public/data/ai_content_json/ — static JSON files (exec_summary.json, org_nps.json, lmei.json, oef.json, pr_summary.json) that are read directly off disk by NA2ApiController endpoints (e.g. app/Http/Controllers/api/NA2ApiController.php:140) and merged into JSON responses as ai_content_json. This is static/canned narrative copy, not dynamically generated per request in those endpoints (the actually-AI-generated content comes from getOEFContentFromClaude / generateSummaries, which call out to Claude/OpenAI live).

---

3. Architecture

Architectural pattern actually implemented

flowchart LR
    subgraph "Per Request"
        R["Route"] --> C["Controller method - fat, does everything"]
        C -->|static calls| PC["ProviderController - static score-query helpers"]
        C -->|DB::select raw SQL| DB[("MySQL")]
        C -->|Eloquent, occasionally| M["Models"]
        C -->|constructor-injected| SV["Services - Claude/OpenAI/WordCloud"]
        C --> RESP["response json / Blade view"]
    end

1. Fat-controller, no service layer for business logic. There is no repository layer, no dedicated query-builder classes, and no form-object/DTO layer beyond two trivial FormRequest classes. Almost all business logic — score aggregation, NPS computation, demographic filtering, benchmark comparison — is written as raw SQL strings directly inside controller methods (app/Http/Controllers/api/PortalController.php, app/Http/Controllers/ProviderController.php, app/Http/Controllers/PortalController.php).
2. "Service" classes exist, but narrowly scoped to external integrations and formatting helpers, not general business orchestration: ClaudeService, OpenAIService (external AI calls), WordCloudService (pure text-processing utility), HelperService (score-formatting + one big benchmark-recompute batch job), SendGridAppService (email), ProviderService (apparently dead/legacy).
3. Heavy use of raw SQL via DB::select/DB::insert/DB::update/DB::delete rather than the Eloquent query builder or Eloquent relationships, even where models with relationships exist (e.g. Entry::scores()/Entry::demographicEntries() are defined but most of the app queries the underlying tables directly with string-interpolated SQL instead). Eloquent is used more consistently only in TestController::DGPivotTableView / its api/PortalController twin DGPivotTableView, and in a handful of relationship lookups (Company::industry(), Cluster::drivers()).
4. No Repository pattern, no CQRS, no event-driven design. app/Providers/EventServiceProvider.php only wires the stock Laravel Registered => SendEmailVerificationNotification listener; there is no custom event/listener code in the app.
5. Duplicate MVC surfaces. The app effectively implements the same report pages twice: once as JSON API endpoints (api/PortalController.php, consumed by the React SPA) and once as server-rendered Blade views (PortalController.php, consumed by the legacy portal). See §6 for a side-by-side comparison and §17 for the maintenance-risk implications.
6. Response conventions are inconsistent. Some API endpoints return response()->json([...]) with a success boolean and a message (e.g. AuthController::login, most of NA2ApiController); others return the raw $view_data array directly, relying on Laravel's implicit conversion of an array return value to a JSON response (e.g. api/PortalController::LMPivotTableView at app/Http/Controllers/api/PortalController.php:1310 returns $view_data; with no response()->json() wrapper, and api/PortalController::consolidatedRankingView at :1704 does the same). There is no single uniform response envelope across the API — no consistent success/data/error contract, unlike, e.g., a TomResponse-style wrapper in more disciplined codebases.

Layering (as it actually exists, not as it "should" be)

flowchart TB
    Route --> Controller
    Controller -->|raw SQL, inline| MySQL[("MySQL")]
    Controller -->|static helper calls| ProviderController
    ProviderController -->|raw SQL, inline| MySQL
    Controller -->|constructor DI| ExternalServices["ClaudeService / OpenAIService"]
    ExternalServices -->|HTTP| ExternalAPIs["Anthropic / OpenAI"]
    Controller -->|response json or view| Client

There is effectively one layer doing both query construction and business/presentation logic: the controller method. ProviderController is the closest thing to a second layer, but it is itself just more raw SQL wrapped in static methods, called directly (not via dependency injection) as ProviderController::getCalculatedScores(...).

---

4. Module / Controller Breakdown

4.1 App\Http\Controllers\api\AuthController (app/Http/Controllers/api/AuthController.php)

• Purpose: Sanctum token issuance/revocation for the SPA.
• Entry points: POST /login (routes/api.php:25, unauthenticated), POST /logout (routes/api.php:51, auth:sanctum).
• login(Request $request) (:14-39): validates email (required, email) and password (required); looks up User::where('email', ...)->first(); verifies with Hash::check; on failure returns HTTP 401 with {success:false, message:'Invalid credentials'}; on success calls $user->createToken('api-token')->plainTextToken (Sanctum personal access token, no expiry set — see §9) and also eagerly loads $user->survey() (a raw-SQL relation method, see §8) to embed the user's current survey in the login response.
• logout(Request $request) (:41-49): $request->user()->currentAccessToken()->delete() — revokes only the token used for the current request, not all tokens for the user.
• No password-reset or "forgot password" API path is implemented for the Sanctum side (only the Blade Auth\ForgotPasswordController scaffolding exists for the session side).

4.2 App\Http\Controllers\api\NA2ApiController (app/Http/Controllers/api/NA2ApiController.php, 859 lines)

• Purpose: AI-driven report-content generation and the underlying raw data feeds those AI calls consume. All routes are auth:sanctum-protected GET endpoints under /api/na2/* (routes/api.php:55-67).
• Key methods:
• getExecutiveSummary(Request $request) (:15-143) — builds the "ECEM" (the org-wide engagement model) score tree (Combine → Cluster → Driver, using ProviderController::getCalculatedScores), a hardcoded ecem_drivers_impact weighting table (:26-38), NPS summary/matrix via inline SQL, and merges in static public/data/ai_content_json/exec_summary.json content.
• getOrganisationalNPS(Request $request) (:145-297) — NPS summary, a per-demographic NPS matrix (hardcoded to demographic ids 1/4/6 in the commented-out original and to a dynamic $sv_id-scoped query in the active version, :190-256), and top/bottom dimension drivers of promoter vs detractor scores.
• getLineManagerEffectiveness(Request $request) (:299-390) — the "LMEI" (line-manager engagement/impact) equivalent of the exec summary, using a separate hardcoded lmei_dimensions_impact weighting table (:310-324).
• getVoiceOfTheEmployee(Request $request) (:396-455) — open-ended-feedback (netpromoterscores.other_text) category breakdown (positive/neutral/negative counts by oef_categories) and a highlighted-comments feed (nps.hl = 1).
• getParticipationSummary(Request $request) (:461-520) — invite/attempt counts, a 5-part "integrity score" computed from miscstats.intgr1..5 (attention-check style data quality flags), and demographic participation breakdown.
• getJSONDataForPrompt() (:522-819) — assembles the entire ECEM + LMEI score trees, NPS data, and participation summary into one large nested object, intended as the structured input fed to an LLM prompt (note: this method does not itself call an AI service — it is the data-shaping step for getOEFContentFromClaude/similar prompt-construction flows elsewhere).
• getOEFJSONForPrompt() (:821-831) — extracts the raw open-ended feedback texts (netpromoterscores.other_text) and category list, formatted for the Claude prompt.
• getOEFContentFromClaude(ClaudeService $claude) (:833-846) — calls $this->getOEFJSONForPrompt() (note: calls it as an instance method despite it not depending on $this), then $claude->getOEFContent($json_data), which sends a large structured prompt to Claude asking for per-comment sentiment/category/highlight tagging plus synthesized "good/bad/ugly" themes (see §6.5 and §11). Temporarily bumps max_execution_time to 60000 seconds for the call (:834).
• updateAIValuesOfOEF() (:848-856) — reads a static file public/data/oef_ai_content.json (not the live Claude response) and bulk-updates netpromoterscores.sent/ctgr/hl from it. This means the AI-generated sentiment/category/highlight tags are applied to the database from a pre-computed JSON file, not directly from the live getOEFContentFromClaude response — there is no code path shown that writes Claude's live JSON output back to public/data/oef_ai_content.json before this endpoint is called (see §20).

4.3 App\Http\Controllers\api\PortalController (app/Http/Controllers/api/PortalController.php, 2410 lines)

The largest and most important controller. Constructor-injects OpenAIService (:22-27). All routes are auth:sanctum-protected (routes/api.php:27-53). Selected methods (grouped by report area — the full endpoint-to-method map is in §7):

• Access/session helpers: changeDataTypeRequest, getDataType, changeFirstLoginRequest — read/write surveys.data_type (avg vs percentage/"favorable" display mode) and surveys.first_login.
• Overview: OverView(Request $request) (:89-245) — the report landing page: overall/cluster ECEM scores, NPS collection + 3×3 quadrant matrix, top-driver "impact" scores (driver score × a hardcoded impact-weight joined via newimpactmaps), and top-5/bottom-5 ranked statements.
• Participation: Participation(Request $request) (:247-323) — invite/attempt counts, the same 5-part integrity score as NA2ApiController::getParticipationSummary, and demographic breakdown (near-identical SQL duplicated here again).
• Insights: consolidatedInsights(Request $request) (:326-440) — computes statement gap analysis: for each dimension, the delta between the organization's own score (yrs) and each comparison lens (top = top-performer benchmark, ind = industry benchmark, bmk = custom benchmark set, pv1 = previous-survey/"prior value" comparison), sorted to surface the 5 biggest positive and negative gaps per lens (:343-376). Also computes "on the fence" (highest-n, i.e. neutral-response-count, statements) and cross-references driver impact on both LM-NPS and organizational commitment via impactmaps.
• Word cloud + NPS/OEF: generateWordCloud($responses) (:442-601, a large duplicate stop-word list also inlined here rather than reusing WordCloudService) and consolidatedNPSAndOEF(Request $request, WordCloudService $wordCloud) (:603-673) — pulls three open-ended-feedback buckets (promoters/passives/detractors, based on the nps_id=1 "would you recommend" question), attaches a cached oef_summaries DB row if present, and runs each bucket through the injected WordCloudService.
• Item detail / ranking: consolidatedItemDetailView, consolidatedRankingView, consolidatedBreakdownView — different drill-down shapes (flat Combine/Cluster/Driver/Dimension list vs Cluster→Driver→Dimension nested tree vs a ranking table) over the same underlying ProviderController::getCalculatedScores data.
• Demographic cuts: demographicRanking, demographicItemDetailView, demographicDataInsight — these implement the "demographic filter" feature: a user selects a combination of demographic options (selected_demographic table) and the report re-scopes all statement scores to only entries matching every selected demographic dimension simultaneously (ProviderController::getDemographicEntryIds, :343-357 of ProviderController.php — a HAVING COUNT(DISTINCT dge.demographic_id) = (SELECT COUNT(DISTINCT demographic_id) ...) pattern). A minimum cell size of 5 is enforced before returning cut data ($e_ids_count > 4 gate, e.g. api/PortalController.php:744) — a privacy/statistical-significance safeguard against showing scores for very small demographic slices.
• Comparison: comparisonMainView, comparisonMain — a configurable 1D/2D/3D cross-tab of statement scores by up to three chosen demographic dimensions (comparison_demographic.comparison_x/y/z), with a HAVING COUNT(DISTINCT sc.entry_id) > 4 cell-suppression rule identical in spirit to the demographic-cut gate.
• Line-manager effectiveness (LME): LMPivotTableView, fetchReporteesByLMENID, getIndvLMDataForPivotTable (static helper), reportLineManagerEffectiveness — a recursive org-chart pivot: entries.lm_entry_id self-references the entries table to form a management hierarchy; the pivot table is built lazily (top-level "starters" where lm_entry_id = 0 are loaded first, and fetchReporteesByLMENID is called on-demand by the frontend to expand each node's direct reports) rather than loading the whole tree in one request.
• Demographic pivot table: DGPivotTableView(Request $request) (:1347-1428) — the one method in this controller that uses Eloquent eager loading (Entry::with(['scores.dimension.driver.cluster.combine', 'demographicEntries.demographicOption.demographic'])) instead of raw SQL, flattening every entry's every score into a row suitable for a client-side pivot-table UI. Computes (but does not currently use/return) a $optionCounts per-demographic-option count via the query builder (:1362-1365) — likely intended for small-cell suppression but not wired into the output (dead computation — see §17).
• Benchmarking: benchmarkView(Request $request) (:1464-1489) — lists industries with their companies and flags which are already selected as this company's benchmark set (via an EXISTS correlated subquery against benchmarks).
• Organisational NPS: organisationalNps(Request $request) (:1708-1905) — contains a real bug: $survey = $user->survey(); is executed on line 1711 before $user = $request->user(); is assigned on line 1712, meaning $user is undefined at the point $survey is computed (see §17, finding #1).
• AI summary trigger: generateSummaries(Request $request) (:1907-1965) — validates nps_id, gathers the three OEF answer buckets, and calls $this->openAIService->generateOefCollSummaries($oef_coll, $npsId), which persists per-question summaries into oef_summaries (see §6.5).
• getDemographicScores(Request $request) (:1967-2410 region) — a newer, more defensively-validated endpoint (uses Laravel's Validator facade with an explicit rule set, :1970-1979) that wraps a private, apparently in-progress reimplementation of the demographic-score pipeline (calculateDimensionScores, calculateYrsScores, getDemographics, getDemographicOptions, getEmptyScore, generateNick — all private methods at the tail of the file). This looks like a parallel/newer implementation of the same demographic-scoring logic that exists elsewhere in ProviderController — see §17 for the duplication risk this represents.

4.4 App\Http\Controllers\PortalController (non-api, app/Http/Controllers/PortalController.php, 831 lines)

The Blade-rendering counterpart to 4.3, protected by session auth (Route::group(['middleware' => ['auth']], ...) in routes/web.php:22-62). Method-for-method it mirrors most of api/PortalController (consolidatedSummaryView ≈ OverView, consolidatedInsightsView ≈ consolidatedInsights, demographicSummaryView/demographicBreakdownView/demographicRankingView/demographicItemDetailView ≈ their api equivalents, comparisonMainView/comparisonMainRequest ≈ comparisonMainView/comparisonMain) but returns view('portal.....', $view_data) instead of response()->json(...). Notable differences:

• benchmarkRequest(ChangeBenchmarkRequest $request) (:243-304) is only implemented here, not in the API controller. It calls an external legacy HTTP API (http://2021.bptwpakistan.com/api/get-benchmark-data, :251-256) to fetch benchmark comparison scores for a chosen set of 5-10 companies, then deletes and re-inserts the benchmarks and calculations (type='bmk') rows for the current survey from the response. This is the write side of the benchmark feature; ApiController::getBenchmarkData (§4.5) is the read side that this exact external call is presumably hitting (both implement near-identical score-aggregation SQL, suggesting 2021.bptwpakistan.com may itself be running a copy of this same codebase or a predecessor of it).
• tempView1() / tempRequest1() (:22-186) — an un-routed-from-nowhere-else (actually routed: routes/web.php:15-16) diagnostic/debug view that builds a full Combine→Cluster→Driver→Dimension ranked table per demographic option, clearly a developer scratch tool left in the codebase.

4.5 App\Http\Controllers\ApiController (app/Http/Controllers/ApiController.php)

• Purpose: a single public, unauthenticated endpoint: POST /api/get-benchmark-data (routes/web.php:20 — note this is registered in web.php, not api.php, so it runs under the web middleware group, not auth:sanctum, and has no authentication or authorization check at all).
• getBenchmarkData(Request $request) (:10-44): given benchmarks (a list of company ids) and survey_type, computes average/SA/A/N/D/SD score roll-ups at all four tiers (dm/dr/cl/cb) for en.year = 2021 (a hardcoded year, :38) via eesmaps-joined SQL, and returns the raw array (implicitly JSON-encoded by Laravel). This is almost certainly the endpoint the external 2021.bptwpakistan.com/api/get-benchmark-data call in PortalController::benchmarkRequest is meant to interoperate with (same route name pattern) — but the hardcoded 2021 year and the reference to a differently-hosted "2021.bptwpakistan.com" URL suggest this may be legacy/dead code from an earlier "2021 wave" of the survey product, now superseded by the recursive benchmarkView/benchmarkRequest flow that fetches from the external host instead of this local endpoint. See §15 for the security implication of this being unauthenticated and §20.

4.6 App\Http\Controllers\ProviderController (app/Http/Controllers/ProviderController.php, 1016 lines)

A static-method-only class (not actually used as an HTTP controller — no routes reference it directly) that both PortalController variants call into. This is the closest thing to a shared query/business layer in the app. Key methods:

• getCalculatedScores($sv_id, $gb, $sct) (:21-74) — the central score-tree builder. $gb selects the grouping tier (cb/cl/dr/dm), $sct selects the score-model ("ecem" = organizational engagement model, "lmei" = line-manager engagement/impact model — these map to different *_driver_id foreign keys on the dimensions table). Fetches five parallel score sets (yrs = this year/current survey, top = top-performer benchmark, ind = industry benchmark, bmk = the company's chosen custom benchmark set, pv1 = previous survey) via getScoreByType and merges them per tier row via tierMapping.
• getScoreByType($sv_id, $tier, $type, $sct) (:76-116) — reads pre-aggregated rows from the calculations table (this table is not populated by this controller — it must be populated by an upstream/external ingestion process not present in this repo, or by HelperService::updateBenchmarkScores for the bmk type specifically). Computes dflt/dflt_pct conditionally on Auth::user()->survey()->data_type (avg = literal Likert average; otherwise = "top-2-box" favorable percentage ((sa+a)/count)*100).
• getSelectedDemographicScore, getTopDemographicScore, demographicAnalysisQuery, previousdemographicAnalysisQuery — demographic-filtered variants of the above that compute scores live from scores/entries rather than from the pre-aggregated calculations table (because a demographic cut is a runtime-selected filter, it cannot be pre-aggregated).
• getDemographicEntryIds($sv_id) / getPVDemographicEntryIds($sv_id) (:343-379) — the "entry ids matching every currently-selected demographic filter" queries described in §4.3.
• getScore, getScore2, analysisQuery, analysisQuery2, getRankQuery, getDemographicOverview, getTopGroupIds, getBenchmarkIds, getIndustryIds, etc. — a long tail of older/alternate scoring helper methods (many referencing a year-based scoping model — en.year = 2021/2019/2017/2015, :499-505 — that does not match the survey_id-based scoping used by the currently-routed methods). These look like carryovers from an earlier multi-year-comparison design that predates the current survey_id/previous_survey_id model, and are not called from any currently-routed controller method as far as could be verified by cross-referencing call sites — flagged as likely-dead code in §17.
• getThemeColors($type) (:952-996) — returns hardcoded hex color CSS/JS snippets for chart theming (yrs/top/bmk/ind/pv1 series colors) — presentation logic embedded in a "controller".

4.7 App\Http\Controllers\TestController (app/Http/Controllers/TestController.php)

Diagnostic/dev views, routed under routes/web.php:25-27 inside the session-auth middleware group (so not public, but not gated by any role check either). DGPivotTableView and LMPivotTableView are earlier, non-survey-id-scoped versions of the same-named methods now living properly scoped inside api/PortalController (compare TestController::DGPivotTableView at :18-83, which has no survey_id filter at all — ->where('has_scores','=',1) only, no survey_id — against api/PortalController::DGPivotTableView at :1347, which adds ->where('survey_id', $sv_id)). This looks like the original prototype that was later copied into the API controller and fixed; the TestController version was left behind.

4.8 App\Http\Controllers\Auth\* (Laravel UI scaffolding)

LoginController, RegisterController, ForgotPasswordController, ResetPasswordController, ConfirmPasswordController, VerificationController — stock, unmodified Laravel UI scaffolding traits (AuthenticatesUsers, RegistersUsers, etc.). routes/web.php:8 has Auth::routes(...) commented out, meaning none of these controllers' default routes are actually registered; the app instead uses its own hand-rolled /logout route (routes/web.php:58-61) and relies on resources/views/auth/login.blade.php being rendered by... no visible route registers a GET /login view either (see §20 — the session-login entry point could not be fully traced in this codebase).

4.9 App\Http\Controllers\ProviderController (Blade session) vs NA2ApiController/api\PortalController overlap

Note that ProviderController is called by both PortalController (Blade) and api\PortalController (JSON), and its data is also independently re-derived (with slightly different SQL) inside NA2ApiController. There is no single source of truth for "what is the current ECEM score tree" — it is computed at least three separate times in three separate places with hand-copied SQL. See §17.

---

5. Request Lifecycle

Middleware stacks (app/Http/Kernel.php)

flowchart TB
    REQ(["Incoming request"]) --> G1["TrustHosts"]
    G1 --> G2["TrustProxies"]
    G2 --> G3["Fruitcake HandleCors"]
    G3 --> G4["PreventRequestsDuringMaintenance"]
    G4 --> G5["ValidatePostSize"]
    G5 --> G6["TrimStrings"]
    G6 --> G7["ConvertEmptyStringsToNull"]
    G7 --> ROUTER{"Route group"}
    ROUTER -->|web.php| WEB["web group: - EncryptCookies, QueuedCookies, - StartSession, ShareErrorsFromSession, - SubstituteBindings - — CSRF middleware is COMMENTED OUT"]
    ROUTER -->|api.php| API["api group: - throttle:api, SubstituteBindings"]
    WEB --> WEBAUTH{"route middleware"}
    WEBAUTH -->|'auth' group routes| AUTHMW["Authenticate - session guard"]
    WEBAUTH -->|none| PUBLICWEB["public web routes"]
    API --> APIAUTH{"route middleware"}
    APIAUTH -->|auth:sanctum group| SANCTUM["Sanctum token check"]
    APIAUTH -->|/login only| PUBLICAPI["public"]
    AUTHMW --> CTRL["Controller"]
    SANCTUM --> CTRL
    PUBLICWEB --> CTRL
    PUBLICAPI --> CTRL

Source: global middleware app/Http/Kernel.php:16-24; groups :31-47; route-middleware aliases :56-66.

Important finding: App\Http\Middleware\VerifyCsrfToken::class is commented out of the web middleware group (app/Http/Kernel.php:38, // \App\Http\Middleware\VerifyCsrfToken::class,). This means CSRF protection is disabled for all session-authenticated Blade-portal POST routes (/report/benchmark, /report/demographic/change, /report/comparison/main, /temp/temp1). See §15.

Sequence — Sanctum API request (e.g. POST /overView)

sequenceDiagram
    participant SPA as React SPA
    participant MW as Global + api middleware<br/>(CORS, throttle:api)
    participant San as auth:sanctum guard
    participant Ctrl as api\PortalController::OverView
    participant Prov as ProviderController (static)
    participant DB as MySQL

    SPA->>MW: POST /overView<br/>Authorization: Bearer <token>
    MW->>San: resolve bearer token via personal_access_tokens
    San-->>Ctrl: request()->user() = authenticated User
    Ctrl->>Ctrl: $user->survey() (raw SQL: SELECT * FROM surveys WHERE id = current_survey_id)
    Ctrl->>DB: SELECT * FROM companies WHERE id = ?
    Ctrl->>Prov: ProviderController::getCalculatedScores("sv_id, 'cb', 'ecem'")
    Prov->>DB: SELECT ... FROM calculations JOIN dimensions/drivers/clusters/combines ...
    DB-->>Prov: rows
    Prov-->>Ctrl: score tree
    Ctrl->>DB: further inline SQL (NPS, impact scores, top/bottom 5)
    DB-->>Ctrl: rows
    Ctrl-->>SPA: response()->json({success, page_name, overall_score, cluster_score, nps_coll, ...})

Sequence — session/Blade request (e.g. GET /report/consolidated/summary)

sequenceDiagram
    participant Browser
    participant MW as web middleware group<br/>(session, NO CSRF)
    participant Auth as Authenticate middleware (session guard)
    participant Ctrl as PortalController::consolidatedSummaryView
    participant Prov as ProviderController (static)
    participant DB as MySQL
    participant Blade as portal.consolidated.summary view

    Browser->>MW: GET /report/consolidated/summary (session cookie)
    MW->>Auth: check Auth::check()
    Auth-->>Ctrl: Auth::user() resolved from session
    Ctrl->>Ctrl: Auth::user()->survey()->id (raw SQL)
    Ctrl->>Prov: ProviderController::getScoreByType / getCalculatedScores
    Prov->>DB: SELECT ...
    DB-->>Prov: rows
    Prov-->>Ctrl: score data
    Ctrl->>Blade: view('portal.consolidated.summary', view_data)
    Blade-->>Browser: rendered HTML

---

6. Business Logic

This section explains the algorithms, not just the method names — read this before changing any scoring code.

6.1 The score hierarchy (Combine → Cluster → Driver → Dimension)

The survey's content model is a 4-level tree, shared by both score models:

flowchart TB
    CB["Combine - combines table"] -->|hasMany| CL["Cluster - clusters table"]
    CL -->|hasMany| DR["Driver - drivers table"]
    DR -->|hasMany, via ecem_driver_id OR lmei_driver_id| DM["Dimension - dimensions table"]
    DM -->|hasMany| SC["Score - scores table, one row per respondent per statement"]

• App\Models\Combine → clusters() hasMany (app/Models/Combine.php:12-15).
• App\Models\Cluster → combine() belongsTo, drivers() hasMany (app/Models/Cluster.php:12-20).
• App\Models\Driver → cluster() belongsTo, dimensions() hasMany (app/Models/Driver.php:12-20).
• App\Models\Dimension → two belongsTo relations to Driver: driver() via ecem_driver_id, and driverlm() via lmei_driver_id (app/Models/Dimension.php:12-20). This is the mechanism by which a single statement/dimension can roll up into two different driver trees — the organization-wide "ECEM" model and the line-manager "LMEI" model — depending on which foreign key is followed. Every raw-SQL score query in the app picks the tree by string-interpolating dm.<sct>_driver_id where $sct is 'ecem' or 'lmei' (e.g. app/Http/Controllers/ProviderController.php:38).
• App\Models\Score → entry() belongsTo, dimension() belongsTo (app/Models/Score.php:12-20).
• App\Models\Entry → scores() hasMany, demographicEntries() hasMany (app/Models/Entry.php:12-20). An Entry is one survey respondent's full response; entries.lm_entry_id self-references to build the reporting-line hierarchy used by LME.

6.2 Score aggregation and the "comparison lenses"

Every score value shown in the UI is computed as one of five comparison lenses, consistently named yrs/top/ind/bmk/pv1 throughout the codebase:

Lens	Meaning	Computed from
yrs	"Your Results" — this company's current-survey score	calculations table, type='yrs', scoped to survey_id
top	Top-performer benchmark (segmented by company size, top_large/top_medsml)	calculations/nps_calculations, type='top_<size>'
ind	Industry benchmark	calculations/nps_calculations, type='ind', scoped by belongers_id = industry_id
bmk	Custom benchmark — a self-selected peer set of 5-10 companies (benchmarks table)	calculations, type='bmk', recomputed by HelperService::updateBenchmarkScores or fetched from the external 2021.bptwpakistan.com API in PortalController::benchmarkRequest
pv1	"Previous survey" — this company's own prior-wave score, for year-over-year trend	calculations, type='pv1'

ProviderController::getScoreByType() (app/Http/Controllers/ProviderController.php:76-116) is the canonical reader: given a tier (cb/cl/dr/dm), a lens $type, and a score model $sct, it reads one pre-aggregated row per tier-node from calculations. ProviderController::tierMapping() (:715-737) then merges the five lenses onto a single per-node object by matching on the appropriate id column (cbid/clid/drid/dmid depending on $gb), falling back to a zeroed "empty score object" when a lens has no row for that node (e.g. a company that opted out of an industry benchmark).

data_type toggle: Every score computation branches on Auth::user()->survey()->data_type (or, in api/PortalController, the injected $request->user()->survey()->data_type): if 'avg', the headline "default" score (dflt) is the raw 1-5 Likert average; otherwise it is a "top-2-box" favorable percentage — ROUND(((sa+a)/count)*100, 2) where sa/a are counts of "Strongly Agree"/"Agree" responses. This toggle is per-survey (stored on surveys.data_type) and user-changeable via POST /change-data-type (api/PortalController::changeDataTypeRequest, routes/api.php:28).

6.3 Demographic cuts and cell-size suppression

A "demographic cut" filters all statement scores down to only respondents matching a specific combination of demographic selections (e.g. "Female AND Manager AND Karachi"), not just a single demographic dimension. The mechanism:

1. The frontend posts a selection into selected_demographic (survey_id, demographic_id, demographic_option_id) via demographicChangeRequest (api/PortalController.php:1137-1171), which first deletes all prior selections for the survey then bulk-inserts the new ones.
2. ProviderController::getDemographicEntryIds($sv_id) (:343-357) finds every entries.id that has a matching demographic_entry row for every distinct demographic_id currently selected — implemented as a GROUP BY dge.entry_id HAVING COUNT(DISTINCT dge.demographic_id) = (SELECT COUNT(DISTINCT demographic_id) FROM selected_demographic WHERE survey_id = ...). This is an AND-across-dimensions, OR-within-a-dimension's-selected-options filter.
3. The resulting comma-joined id string $e_ids is string-interpolated directly into subsequent IN (...) clauses (e.g. ProviderController::demographicAnalysisQuery, :298-341) — see §15 for the SQL-injection risk this pattern carries generally, though $e_ids itself is sourced from an internal id query rather than raw user input.
4. Minimum cell size of 5: the controller only returns cut-scoped data if count(explode(',', $e_ids)) > 4 (e.g. api/PortalController.php:744,800; PortalController.php:547,592,652,695). Below that threshold, the response omits the demographic-scoped fields entirely (falls back to just the "summary"/"selections"/"list" metadata), a k-anonymity-style privacy safeguard so individual respondents in small groups cannot be inferred from aggregate scores. The comparison feature applies an equivalent per-cell rule at the SQL level: HAVING COUNT(DISTINCT sc.entry_id) > 4 (e.g. api/PortalController.php:878,895,913).

6.4 Line-manager effectiveness (LME) — recursive org-chart pivot

entries.lm_entry_id self-references entries.id: each respondent's row points to their line manager's entry (or 0 for top-level/no-manager). The LME pivot table is built lazily, not as one deep recursive query:

1. LMPivotTableView loads only the top level: SELECT * FROM entries WHERE lm_entry_id = 0 AND survey_id = ? (api/PortalController.php:1284-1287).
2. For each such "starter" entry, getIndvLMDataForPivotTable($o, $nest_level, $dtype) (:1173-1266) computes that manager's aggregated cluster/driver/dimension scores across their direct reports (WHERE en.lm_entry_id = <manager's entry id>) plus a computed line-manager NPS (nps.nps_id = 2, the "would you recommend your manager" question), and reports whether this manager's reports themselves have further reports (has_dr_with_dr).
3. The frontend calls POST /lme/individual-lme-report (routed to fetchReporteesByLMENID, :1315-1345) on-demand to expand a given node, passing en_id (the manager's entry id) and nest_level. This avoids loading a potentially very deep/wide tree in a single request, at the cost of one request per tree-expansion click.
4. reportLineManagerEffectiveness (:1491+) computes the organization-wide (not per-manager) LMEI rollup via HelperService::getCompiledScore, plus an NPS-by-percentile-bucket histogram (lmNpsByDistribution, grouping each manager's own NPS score into bands like >90, >80, ... down to -100) and an NPS-by-demographic breakdown.

6.5 AI-generated content: OEF sentiment tagging & executive summary

Two independent AI pipelines exist, using two different providers:

Claude pipeline (Anthropic, via ClaudeService) — open-ended-feedback (OEF) sentiment/category/highlight tagging and synthesized theme extraction: 1. NA2ApiController::getOEFJSONForPrompt() pulls every netpromoterscores.other_text (for nps_id=1, the recommend-the-org question) plus the oef_categories lookup table. 2. getOEFContentFromClaude sends this as a single large JSON blob into a very detailed prompt (ClaudeService::buildOEFPrompt, app/Services/ClaudeService.php:56-135) instructing Claude (model claude-sonnet-4-6, max_tokens: 50000, temperature: 0, :32-33) to: (a) sentiment-classify each comment (positive/negative/neutral, handling English/Urdu/Roman-Urdu), (b) assign each to one of 8 categories, (c) pick up to 100 "highlight" comments, and (d) synthesize a "good/bad/ugly" set of 3 themes each with a paraphrased composite quote and an estimated percentage of respondents. The prompt explicitly instructs redaction of identifying details and rephrasing of "ugly" quotes into constructive framing (:93). 3. The Claude HTTP call uses a 1200-second client timeout with 2 retries on connection failure (app/Services/ClaudeService.php:22-25). 4. Critically, the live Claude response is not shown to be persisted anywhere in this codebase. updateAIValuesOfOEF() (NA2ApiController.php:848-856) applies sentiment/category/highlight values to the netpromoterscores table, but it reads them from a static file public/data/oef_ai_content.json, not from the JSON getOEFContentFromClaude just returned to the caller. This implies either (a) an external/manual process writes Claude's output to that file before this endpoint runs, or (b) this write-back step is not yet fully wired up. See §20.

OpenAI pipeline (OpenAIService) — per-question narrative summarization of OEF answers: 1. PortalController::generateSummaries (api) gathers the three OEF answer buckets (promoters/passives/detractors text) for a given nps_id. 2. OpenAIService::generateOefCollSummaries (app/Services/OpenAIService.php:79-124) calls summarizeAnswers() → getResponse() per question, hitting gpt-3.5-turbo (:41) with a hand-written prompt (buildPrompt, :145-156) instructing a single third-person paragraph, ≤8 lines, no mention of "the survey"/"raters", with named individuals genericized to "The Leader/Management/Employee". 3. Prompts longer than 7500 characters are hard-truncated (preparePrompt, :162-165) — for a survey with many long comments this can silently drop content without any indication to the caller. 4. Results are updateOrInserted into oef_summaries keyed by (type_id, nps_id) (:93-103); on any exception, a static 'Analysis unavailable' string is persisted instead of leaving the row stale (:112-121), and the OpenAI-side error is logged (Log::error, :67-70 and :107-110) but the request still reports overall success back to generateSummaries's caller as long as no PHP exception propagates past generateOefCollSummaries's internal try/catch.

6.6 Benchmark score recompute (HelperService::updateBenchmarkScores)

app/Services/HelperService.php:130-277 is a large batch job (not queued — runs synchronously, ini_set('max_execution_time', 600000) at :132) that recomputes the company's bmk (custom benchmark) comparison data from scratch:

1. Reads the company's chosen benchmark company ids from benchmarks (type='bmk').
2. For every (sct, tier) combination (ecem/lmei × cb/cl/dr/dm — 8 combinations), computes average/SA/A/N/D/SD/count across all likerts/scores rows belonging to entries in those benchmark companies, excluding a hardcoded blacklist of company ids (79,121,64,147) (:164, :218, :259 — repeated three times, once per score/demographic/NPS section) — likely test/internal accounts permanently excluded from ever being counted as benchmark data.
3. Deletes and bulk-reinserts the corresponding rows in calculations (type='bmk'), demographic_calculations (type='bmk'), and nps_calculations (type='bmk').
4. No caller of this method could be located in routes/api.php, routes/web.php, or any controller — it appears to be invoked only manually (e.g. via php artisan tinker or a one-off script) or by a process outside this repository. See §10 and §20.

6.7 Excel user import (App\Imports\UsersImport)

app/Imports/UsersImport.php:16-27 implements maatwebsite/excel's ToModel contract: each spreadsheet row (positional columns 0-4: name, email, employee_id, phone, role_id) is mapped to a new User with Hash::make(5) as the password — the literal integer 5 is hashed as the password for every imported user, not a randomly generated or per-row value (see §17, finding). No controller in this codebase is shown invoking UsersImport via Excel::import(...) — it is present but its trigger point could not be located (see §20).

---

7. API Layer

7.1 Sanctum JSON API (routes/api.php) — consumed by the React SPA

All routes below are prefixed with /api by RouteServiceProvider::boot() (app/Providers/RouteServiceProvider.php:41-44, Route::prefix('api')->middleware('api')->group(base_path('routes/api.php'))), so e.g. Route::post('/overView', ...) is actually reachable at POST /api/overView.

Method	Path	Controller@action	Auth	Request body (inferred)	Response shape (inferred)
GET	/api/user	closure (routes/api.php:20)	auth:sanctum	—	the authenticated User model
POST	/api/login	api\AuthController@login	none	{email, password}	{success, token, user, survey} or 401 {success:false, message}
POST	/api/logout	api\AuthController@logout	auth:sanctum	—	{success, message}
POST	/api/change-data-type	api\PortalController@changeDataTypeRequest	auth:sanctum	{data_type: string}	{success, message, data_type}
POST	/api/change-first-login	api\PortalController@changeFirstLoginRequest	auth:sanctum	{first_login: boolean}	{success, message, data_type}
POST	/api/get-datatype	api\PortalController@getDataType	auth:sanctum	—	{data_type, first_login}
POST	/api/overView	api\PortalController@OverView	auth:sanctum	—	{success, page_name, overall_score, cluster_score, nps_coll, nps_matrix, impact_scores, impact_bmks, top_5, bottom_5}
POST	/api/participation	api\PortalController@Participation	auth:sanctum	—	{success, page_name, participation_summary}
POST	/api/statement-benchmark	api\PortalController@consolidatedInsights	auth:sanctum	—	{success, page_name, statement_gaps, on_the_fence, impact}
POST	/api/open-ended-feedback	api\PortalController@consolidatedNPSAndOEF	auth:sanctum	—	{success, oef_coll} (each item has wordCloud)
POST	/api/item-detail	api\PortalController@consolidatedItemDetailView	auth:sanctum	—	{success, page_name, item_detail:{combine, cluster, driver, dimension}}
POST	/api/demographic/ranking	api\PortalController@demographicRanking	auth:sanctum	—	{demographics, dimensions?, drivers?, clusters?} (score fields only if cut ≥5)
POST	/api/demographic/item-detail	api\PortalController@demographicItemDetailView	auth:sanctum	—	{success, page_name, demographics, item_detail}
POST	/api/demographic/comparison	api\PortalController@comparisonMainView	auth:sanctum	—	{page_name, demographics, selections, comparison_type, scores, dex_options, dey_options, dez_options}
POST	/api/demographic/change-comparison	api\PortalController@comparisonMain	auth:sanctum	{comparison_x?, comparison_y?, comparison_z?: int}	{success, message, data}
POST	/api/demographic/data-insight	api\PortalController@demographicDataInsight	auth:sanctum	query: cm_dg_id, lm_dg_id (default 190)	{demographics, dm_options?, impact_lmei?, impact_ecem?, ...}
POST	/api/demographic/change-demographic	api\PortalController@demographicChangeRequest	auth:sanctum	DemographicChangeRequest: {filter_demographic: {demographic_id: [option_id, ...]}}	{success, message, data}
POST	/api/lme/individual-lme	api\PortalController@LMPivotTableView	auth:sanctum	—	{page_name, reportees, clusters} (raw array return, no envelope)
POST	/api/lme/individual-lme-report	api\PortalController@fetchReporteesByLMENID	auth:sanctum	{en_id, nest_level}	{status, message, res:{reportees}}
POST	/api/lme/overall	api\PortalController@reportLineManagerEffectiveness	auth:sanctum	query: dg_id (default 1)	{lmNpsByDemographic, lmNpsByDistribution, lmei_scores, lmei_nps, impact_bmks, impact_scores, dg_list, impact}
POST	/api/demographic/pivot-table	api\PortalController@DGPivotTableView	auth:sanctum	—	{page_name, pivotData, demographics} (raw array return)
POST	/api/statement-ranking	api\PortalController@consolidatedBreakdownView	auth:sanctum	—	{page_name, row, drivers, clusters} (raw array return)
POST	/api/consolidated/statement-ranking	api\PortalController@consolidatedRankingView	auth:sanctum	—	{page_name, dimensions, drivers, clusters} (raw array return)
POST	/api/benchmark-view	api\PortalController@benchmarkView	auth:sanctum	—	{message, industries}
POST	/api/organisational-nps	api\PortalController@organisationalNps	auth:sanctum	query: dg_id (default 1)	{page_name, org_nps, p_top_5, p_btm_5, passive_top_5, passive_btm_5, d_top_5, d_btm_5, og_lm_nps, dg_list} — currently broken, see §17 #1
POST	/api/oef/generate-summaries	api\PortalController@generateSummaries	auth:sanctum	{nps_id: int}	{success, message, data} or 500 {success:false, message, error}
POST	/api/demographic-scores	api\PortalController@getDemographicScores	auth:sanctum	{sv_id, pv_sv_id, gb, sct, e_ids?, e_pv_ids?, demographic_ids?: int[]}	{success, ...} (422 on validation failure)
GET	/api/na2/get-json-data-for-prompt	api\NA2ApiController@getJSONDataForPrompt	auth:sanctum	—	large nested object (no response()->json() wrapper — implicit array→JSON)
GET	/api/na2/get-oef-json-for-prompt	api\NA2ApiController@getOEFJSONForPrompt	auth:sanctum	—	{open_ended_feedbacks, categories}
GET	/api/na2/get-oef-content-from-claude	api\NA2ApiController@getOEFContentFromClaude	auth:sanctum	—	{message, res:{oef_content}} — oef_content is Claude's raw text response
GET	/api/na2/update-ai-values-of-oef	api\NA2ApiController@updateAIValuesOfOEF	auth:sanctum	—	{message}
GET	/api/na2/get-executive-summary	api\NA2ApiController@getExecutiveSummary	auth:sanctum	—	{message, res:{overall, clusters, drivers, nps, oef_count, ai_content_json}}
GET	/api/na2/get-organisational-nps	api\NA2ApiController@getOrganisationalNPS	auth:sanctum	—	{message, res:{nps, ai_content_json}}
GET	/api/na2/get-line-manager-effectiveness	api\NA2ApiController@getLineManagerEffectiveness	auth:sanctum	—	{message, res:{overall, dimensions, nps, ai_content_json}}
GET	/api/na2/get-voice-of-the-employee	api\NA2ApiController@getVoiceOfTheEmployee	auth:sanctum	—	{message, res:{category_breakdown, explore_comments, ai_content_json}}
GET	/api/na2/get-participation-summary	api\NA2ApiController@getParticipationSummary	auth:sanctum	—	{message, res:{participation_summary, ai_content_json}}

Source: routes/api.php:1-67.

7.2 Session/Blade web routes (routes/web.php) — legacy portal + public endpoint

Method	Path	Controller@action	Auth	Notes
GET	/	closure → view('frontend.index')	none	serves the placeholder view (see §2 note)
GET	/seed	PortalController@seedCalculations	none	route references a method not found in PortalController.php — likely dead/broken route (see §20)
GET	/temp/temp1	PortalController@tempView1	none	dev scratch view
POST	/temp/temp1	PortalController@tempRequest1	none	empty method body — no-op
POST	/api/get-benchmark-data	ApiController@getBenchmarkData	none	public, unauthenticated, despite the /api path prefix it is defined in web.php and runs under the web middleware group, not Sanctum
GET	/test/dg-pivot-table	TestController@DGPivotTableView	session auth
GET	/test/lm-pivot-table	TestController@LMPivotTableView	session auth
POST	/test/fetch-reportees-by-lmenid	TestController@fetchReporteesByLMENID	session auth
POST	/report/change-data-type	PortalController@changeDataTypeRequest	session auth	no CSRF check (middleware disabled)
GET	/report/introduction/understanding-your-report	PortalController@introductionUnderstandingView	session auth	static content view
GET	/report/introduction/intro	PortalController@introductionIntroView	session auth
GET	/report/introduction/engagement-model	PortalController@introductionEngagementModelView	session auth	Cluster::with('drivers')->get()
GET	/report/introduction/methodology	PortalController@introductionMethodologyView	session auth
GET	/report/introduction/keys	PortalController@introductionKeysView	session auth
GET	/report/benchmark	PortalController@benchmarkView	session auth
POST	/report/benchmark	PortalController@benchmarkRequest	session auth	ChangeBenchmarkRequest; calls external 2021.bptwpakistan.com API
GET	/report/consolidated/summary	PortalController@consolidatedSummaryView	session auth
GET	/report/consolidated/insights	PortalController@consolidatedInsightsView	session auth
GET	/report/consolidated/breakdown	PortalController@consolidatedBreakdownView	session auth
GET	/report/consolidated/ranking	PortalController@consolidatedRankingView	session auth
GET	/report/consolidated/item-detail	PortalController@consolidatedItemDetailView	session auth
GET	/report/consolidated/nps-and-oef	PortalController@consolidatedNPSAndOEFView	session auth
POST	/report/demographic/change	PortalController@demographicChangeRequest	session auth	DemographicChangeRequest; no CSRF check
GET	/report/demographic/summary	PortalController@demographicSummaryView	session auth
GET	/report/demographic/breakdown	PortalController@demographicBreakdownView	session auth
GET	/report/demographic/ranking	PortalController@demographicRankingView	session auth
GET	/report/demographic/item-detail	PortalController@demographicItemDetailView	session auth
GET	/report/comparison/main	PortalController@comparisonMainView	session auth
POST	/report/comparison/main	PortalController@comparisonMainRequest	session auth	no CSRF check
GET	/logout	closure	session auth	Auth::logout() then redirect to /login
GET	/{any} (catch-all, .*)	closure → view('frontend.index')	none	SPA fallback — serves placeholder (see §2)

Source: routes/web.php:1-68. Note /seed at :13 references PortalController::class,'seedCalculations' — no such public method exists in app/Http/Controllers/PortalController.php as read in full; invoking this route would throw a BadMethodCallException/Error at runtime.

---

8. Database Interaction

8.1 ORM usage pattern

The app uses Eloquent models sparingly (mostly for relationship traversal in a handful of methods — Entry::with([...]), Cluster::with('drivers'), Company::industry(), Industry::companies(), Benchmark::where(...)) and raw DB::select/DB::insert/DB::update/DB::delete extensively for everything else, including most business-critical scoring queries. Many raw queries string-interpolate PHP variables directly into SQL rather than using parameter binding (?/named bindings), for example ProviderController::getScoreByType (app/Http/Controllers/ProviderController.php:79-114) builds its entire query via string concatenation of $tier, $type, $sct, $sv_id. Some newer code (e.g. api/PortalController::demographicChangeRequest, :1154-1163; NA2ApiController company lookups, :20) does use parameter binding. See §15 for the SQL-injection risk analysis.

8.2 Migration coverage vs actual schema — IMPORTANT CAVEAT

Only 5 Laravel migrations exist in this repository (database/migrations/):

Migration	Creates
2014_10_12_000000_create_users_table.php	users
2014_10_12_100000_create_password_resets_table.php	password_resets (stock Laravel, content not separately re-read but standard shape assumed)
2019_08_19_000000_create_failed_jobs_table.php	failed_jobs (stock Laravel queue-failure table)
2019_12_14_000001_create_personal_access_tokens_table.php	personal_access_tokens (stock Sanctum table)
2025_12_22_053542_create_misc_stats_table.php	misc_stats — but this migration only creates id and timestamp columns (database/migrations/2025_12_22_053542_create_misc_stats_table.php:16-19); it does not create the intgr1..intgr5 columns that MiscStat/raw queries against miscstats (note table-name mismatch, next paragraph) actually read.

Every other table referenced throughout the codebase — companies, surveys, entries, scores, dimensions, drivers, clusters, combines, demographics, demographic_options, demographic_entry, demographic_survey, selected_demographic, comparison_demographic, calculations, demographic_calculations, nps_calculations, netpromoterscores, oef_categories, oef_summaries, benchmarks, industries, eesmaps, impactmaps, newimpactmaps, miscstats, and more — is NOT created by any migration in this repository. This confirms the task brief's framing: the production database schema for this app is externally managed / legacy, and this Laravel codebase is a reporting layer bolted on top of a pre-existing database rather than the schema's owner. All schema facts below are reverse-engineered from Eloquent model definitions and raw SQL usage in controllers/services, not from migrations — every table description carries this caveat explicitly.

Notable naming inconsistency: App\Models\MiscStat declares protected $table = 'miscstats' (app/Models/MiscStat.php:9, no underscore), while the migration creates a table named misc_stats (with underscore, database/migrations/2025_12_22_053542_create_misc_stats_table.php:16) and all raw SQL in the controllers also queries `miscstats` (no underscore — e.g. NA2ApiController.php:465, api/PortalController.php:296). This means the misc_stats migration does not correspond to the table the application code actually reads/writes — either the migration is vestigial/wrong, or miscstats is a separate, externally-managed table and misc_stats is unused. Either way, running this migration alone will not produce a working miscstats table with the intgr1..5 columns the code depends on.

8.3 Entity-relationship diagrams (reverse-engineered)

The schema is easier to read as four subsystems rather than one large diagram. Each one below shows a slice of the same overall database; entities that appear in more than one diagram (e.g. Company, Entry) are the join points between subsystems.

8.3.1 Org, users & survey structure

Who the respondents are, which company/survey they belong to, and the manager reporting line used for line-manager-effectiveness (LME) scoring.

erDiagram
    Company ||--o{ User : employs
    Company ||--o{ Entry : has_respondents
    Company ||--o| Industry : belongs_to
    Company ||--o{ MiscStat : has

    Survey ||--o{ Entry : scopes
    Survey ||--o| Survey : previous_survey_id

    Entry }o--o| Entry : lm_entry_id_reports_to

8.3.2 Scoring tree (ECEM / LMEI) & pre-aggregated calculations

The four-level statement hierarchy (Dimension → Driver → Cluster → Combine) that every survey statement rolls up through, and the Calculation table that stores pre-computed averages per tier so pages don't need to re-aggregate raw Score rows on every request (see §8.6).

erDiagram
    Entry ||--o{ Score : answers

    Dimension ||--o{ Score : scored_by
    Dimension }o--|| Driver : ecem_driver_id
    Dimension }o--|| Driver : lmei_driver_id
    Driver }o--|| Cluster : belongs_to
    Cluster }o--|| Combine : belongs_to

    Combine ||--o{ Calculation : scored_at_tier
    Cluster ||--o{ Calculation : scored_at_tier
    Driver ||--o{ Calculation : scored_at_tier
    Dimension ||--o{ Calculation : scored_at_tier

8.3.3 Demographics & benchmarking

How a respondent's demographic tags (department, tenure, location, …) are recorded, and how a company's chosen peer set (its "benchmark") is stored and scored per demographic cut.

erDiagram
    Entry ||--o{ DemographicEntry : tagged_with

    Demographic ||--o{ DemographicOption : has_options
    Demographic ||--o{ DemographicEntry : classifies
    DemographicOption ||--o{ DemographicEntry : selected_as
    DemographicOption ||--o{ DemographicCalculation : scored_for

    Company ||--o{ Benchmark : selects_as_peer

8.3.4 NPS & open-ended feedback (AI-assisted)

The Net Promoter Score answer + free-text comment table, and how AI (Claude/OpenAI) enriches each comment with a sentiment, category, and highlight flag (see §11).

erDiagram
    Entry ||--o{ NetPromoterScore : answers_nps
    NetPromoterScore }o--|| OefCategory : categorized_as
    NetPromoterScore ||--o{ NpsCalculation : benchmarked_at

8.4 Schema reference — per-table column notes

Every table in this section is inferred from usage (Eloquent $fillable/relationships and raw SQL column references), not a formal migration, except where noted otherwise. Types are best-effort inferences from how each column is used (compared numerically, joined as an id, concatenated as text, etc.) — actual MySQL column types/lengths/nullability cannot be confirmed without direct database access. Treat this section as a map for writing correct queries, not as an authoritative DDL reference.

users — confirmed by migration + model

Column	Inferred type	Nullable	Notes
id	bigint unsigned, PK	No	$table->id()
name	varchar	No	migration
email	varchar, unique	No	migration; login identifier
email_verified_at	timestamp	Yes	migration; cast to datetime in model (app/Models/User.php:49-51)
password	varchar	No	migration; hashed
employee_id	varchar	No	migration (2014_10_12_000000_create_users_table.php:22) — not in the model's $fillable (app/Models/User.php:25-40), so mass-assignment to this column would be silently blocked unless set individually
phone	varchar	No	migration — also not in $fillable
role_id	varchar (per migration $table->string('role_id'), unusual for an FK — normally would be an integer FK)	No	migration; also read as Auth::user()->role->id in RoleAdmin/RoleClient/RoleEmp middleware, implying a role() relationship is expected to exist even though it is not defined on the User model as read (see §17)
remember_token	varchar	Yes	migration (rememberToken())
pp, report_type, invites, c_top, c_ind, c_bmk, c_p19, c_p17, c_p15, c_dem, company_id	mixed (int/bool-ish flags, FK)	—	present in $fillable (app/Models/User.php:25-40) but not created by the users migration — these columns must exist in the externally-managed live schema; c_top/c_ind/c_bmk/c_p19/c_p17/c_p15/c_dem read like legacy boolean "include this comparison lens" flags (compare ProviderController::getReportTypeObject, :739-746, which reads $user->c_top/c_ind/c_bmk/c_p19)
current_survey_id	bigint (inferred)	—	read by User::survey() (app/Models/User.php:20-23) via raw SQL WHERE id = {$this->current_survey_id} — not in $fillable, and string-interpolated directly into SQL with no parameter binding, a SQL-injection-shaped pattern even though in practice this value originates from the authenticated user's own row (see §15)
first_login	boolean/tinyint (inferred)	—	read by FirstLogin middleware (app/Http/Middleware/FirstLogin.php:13)

Note: App\Models\User sets public $timestamps = false; (app/Models/User.php:47), so Eloquent will not manage created_at/updated_at for this model even though the migration includes $table->timestamps().

personal_access_tokens — confirmed by migration (stock Sanctum shape)

Column	Type	Nullable	Notes
id	bigint unsigned, PK	No
tokenable_type, tokenable_id	polymorphic morph columns	No	$table->morphs('tokenable')
name	varchar	No
token	varchar(64), unique	No	SHA-256 hash of the plaintext token
abilities	text	Yes	JSON-encoded ability list
last_used_at	timestamp	Yes
created_at, updated_at	timestamp	Yes

misc_stats — confirmed by migration, but incomplete relative to code usage

Column	Type	Nullable	Notes
id	bigint unsigned, PK	No	migration
created_at, updated_at	timestamp	Yes	migration
company_id	FK (inferred)	—	App\Models\MiscStat::company() belongsTo (app/Models/MiscStat.php:11-14) — not in the migration
intgr1..intgr5	int (inferred, used as counts)	—	read raw as `miscstats`.intgr1..5 (e.g. NA2ApiController.php:466-472) — not in the migration, and queried against a differently-named table (miscstats) than the one the migration creates (misc_stats) — see §8.2 caveat

companies — not migrated; inferred from raw SQL + Company/Industry models

Column	Inferred type	Notes
id	bigint, PK
name	varchar	referenced in Company::whereIn('id', ...)->get('name') (ProviderController.php:801,809)
industry_id	bigint, FK → industries.id	Company::industry() belongsTo (app/Models/Company.php:11-13)
size	varchar/enum ('large'/'medium'/'small')	drives getTopTypeByCompanyId (ProviderController.php:777-786) and the top_<size> benchmark lens key
pool	boolean/tinyint	filters "companies eligible to appear in the benchmark picker" (PortalController.php:232, ProviderController.php:617)
top, ind, bmk, pv1	boolean/tinyint flags	gate whether each comparison lens is enabled for this company (e.g. api/PortalController.php:160-163)
data_type	varchar ('avg' or other)	also appears on surveys; some code reads it from companies, most reads it from surveys — see §17 for the inconsistency this creates

surveys — not migrated; inferred from Survey model + raw SQL

Column	Inferred type	Notes
id	bigint, PK
company_id	bigint, FK	implied by User::survey() and company-scoped queries
data_type	varchar ('avg' / percentage mode)	read constantly, written via changeDataTypeRequest
first_login	boolean/tinyint	read/written via changeFirstLoginRequest/getDataType
previous_survey_id	bigint, nullable, self-FK	used for year-over-year (pv1) comparisons
invites	int	participation summary "total invites"
att_eval	int	"failed attention evaluation" count
tm_name	varchar	passed as sv_type to the external benchmark API (PortalController.php:254)

entries — not migrated; inferred from Entry model + extensive raw SQL

Column	Inferred type	Notes
id	bigint, PK
survey_id	bigint, FK → surveys.id	pervasive scoping column
company_id	bigint, FK → companies.id
has_scores	boolean/tinyint	gates whether a respondent's answers are counted (partial/incomplete responses excluded)
lm_entry_id	bigint, nullable self-FK, default 0 for "no manager"	the LME reporting-line pointer (§6.4)
year	int	used only by the apparently-legacy analysisQuery/getScore family in ProviderController (values 2015/2017/2019/2021)

scores — not migrated; inferred from Score model + raw SQL

Column	Inferred type	Notes
id	bigint, PK
entry_id	bigint, FK → entries.id	Score::entry()
dimension_id	bigint, FK → dimensions.id	Score::dimension()
score	tinyint (1-5 Likert scale, given sa/a/n/d/sd bucket logic)

dimensions, drivers, clusters, combines — not migrated; inferred from models + raw SQL

Table.Column	Inferred type	Notes
dimensions.id	bigint, PK
dimensions.name	varchar
dimensions.def	text/varchar	statement definition text; also referenced as defl in some older ProviderController queries (:182,571 etc.) — inconsistent column naming (def vs defl) between newer and older query code, another signal that some ProviderController methods are legacy/unmaintained
dimensions.ecem_driver_id	bigint, FK → drivers.id	the ECEM-tree parent
dimensions.lmei_driver_id	bigint, FK → drivers.id, nullable (>0 check used, e.g. :1212)	the LMEI-tree parent — only set for dimensions relevant to line-manager scoring
dimensions.impactmap_type_id	bigint, nullable	joined for demographic impact analysis (api/PortalController.php:1081)
drivers.id, .name, .def/.defl, .cluster_id	—	Driver::cluster()/dimensions()
clusters.id, .name, .def/.defl, .combine_id	—	Cluster::combine()/drivers()
combines.id, .name, .def/.defl	—	Combine::clusters()

demographics, demographic_options, demographic_entry, demographic_survey, selected_demographic, comparison_demographic

Table.Column	Inferred type	Notes
demographics.id, .name	—
demographics.highlight	boolean/tinyint	flags "show in the summary demographic breakdown" (e.g. api/PortalController.php:120)
demographics.comparison	boolean/tinyint	flags "eligible as an X/Y/Z axis in the Comparison feature"
demographics.order	int	display sort order
demographics.nick	varchar	a "nickname" used as a dynamic column-name fragment in HelperService::updateBenchmarkScores (en.".$dg->nick — i.e. this implies entries also has one denormalized column per demographic, named by nick, in addition to the normalized demographic_entry table — an inconsistent/duplicated demographic-storage design, see §17)
demographic_options.id, .name, .demographic_id (FK)	—	DemographicOption::demographic()/demographicEntries()
demographic_entry.entry_id, .demographic_id, .demographic_option_id	FKs	DemographicEntry uses table name demographic_entry (singular, set via protected $table at app/Models/DemographicEntry.php:9)
demographic_survey.survey_id, .demographic_id	FKs	join table — which demographics are active/relevant for a given survey
selected_demographic.survey_id, .demographic_id, .demographic_option_id	—	the user's current demographic-cut filter selection (§6.3)
comparison_demographic.survey_id, .comparison_x, .comparison_y, .comparison_z	bigint demographic ids, 0 = unset	the Comparison feature's selected axes

calculations, demographic_calculations, nps_calculations — pre-aggregated score tables

Table.Column	Inferred type	Notes
calculations.tier	varchar enum-like ('cb'/'cl'/'dr'/'dm')
calculations.type	varchar enum-like ('yrs'/'top_large'/'top_medsml'/'ind'/'bmk'/'pv1')	the comparison lens
calculations.sct	varchar ('ecem'/'lmei')	score model
calculations.survey_id, .belongers_id	bigint	belongers_id appears to be a company id for lens types scoped by company (e.g. bmk)
calculations.cb_id, .cl_id, .dr_id, .dm_id	bigint, nullable depending on tier
calculations.avg, .sa, .a, .n, .d, .sd, .count	numeric	raw Likert-bucket counts + average, source of all dflt/dflt_pct/eng derived percentages
demographic_calculations.belongers_id, .type, .tier, .demographic_id, .demographic_option_id, .avg, .sa...sd, .c	mirrors calculations but scoped by demographic option instead of by tree tier	written by HelperService::updateBenchmarkScores
nps_calculations.type, .belongers_id, .question_id, .score	—	pre-computed NPS benchmark scores per lens

netpromoterscores, oef_categories, oef_summaries

Table.Column	Inferred type	Notes
netpromoterscores.id, .entry_id (FK), .nps_id (1 = org NPS, 2 = line-manager NPS, per code comments e.g. api/PortalController.php:1598), .score (0-10)	—	the core NPS response table
netpromoterscores.other_text	text, nullable	the open-ended-feedback free-text answer attached to the NPS question
netpromoterscores.sent	tinyint (-1/0/1)	AI-assigned sentiment, written by updateAIValuesOfOEF
netpromoterscores.ctgr	bigint, FK → oef_categories.id	AI-assigned category
netpromoterscores.hl	boolean/tinyint	AI-assigned "highlight this comment" flag
oef_categories.id, .name, .explain	—	category lookup table (categories 1-7 = specific themes, 8 = uncategorizable, per the Claude prompt instructions)
oef_summaries.type_id, .nps_id, .summary, .updated_at	—	OpenAI-generated per-question narrative summary cache, unique on (type_id, nps_id) per the updateOrInsert key (OpenAIService.php:94-97)

benchmarks, industries

Table.Column	Inferred type	Notes
benchmarks.survey_id, .company_id, .type ('bmk'/'top_large'/etc.), .belongers_id	—	the "which companies did this company pick as its custom benchmark set" table
industries.id, .name, .pool	—	Industry::companies() hasMany; pool likely mirrors companies.pool as a "has any poolable companies" filter

eesmaps, impactmaps, newimpactmaps, pvmaps

Table.Column	Inferred type	Notes
eesmaps.dm_id, .ees_id, .bptw_id, .type	—	a cross-mapping table (its exact purpose — mapping this app's dimension ids to an external/legacy "bptw" id scheme — is inferred from the join pattern in ApiController::getBenchmarkData, :34, and is not fully confirmed)
impactmaps/newimpactmaps.bptw_dm_id, .type ('cnsd'/'demg'), .confidence, .sct, .impact, .type_id	—	drives the "impact score" (how much each driver statistically correlates with NPS/commitment) shown in Overview and Insights
pvmaps.pv_dm_id, .dm_id	—	maps this survey wave's dimensions to the previous wave's dimensions, for pv1 comparison across surveys whose question sets may have changed

8.5 Transactions

No explicit database transactions (DB::transaction()/DB::beginTransaction()) were found anywhere in the reviewed controllers or services. Multi-statement write sequences — e.g. PortalController::benchmarkRequest's delete-then-bulk-insert of benchmarks and calculations (app/Http/Controllers/PortalController.php:248-301), or HelperService::updateBenchmarkScores's three delete-then-bulk-insert sequences (app/Services/HelperService.php:175-272) — run as a series of independent statements with no atomicity guarantee. A failure partway through (e.g. the external HTTP call in benchmarkRequest succeeding but a subsequent insert failing) can leave the database in an inconsistent state (deleted-but-not-reinserted rows).

8.6 Query patterns and lifecycle

• Reads are almost entirely synchronous, request-scoped raw SQL, re-executed from scratch on every request (no caching layer observed anywhere in the app — see §14).
• Writes are simple delete-then-insert or updateOrInsert patterns; no soft-delete convention is used anywhere in this codebase (unlike more mature Laravel apps).
• The calculations/demographic_calculations/nps_calculations tables function as a manual materialized-view layer: expensive aggregate statistics are pre-computed (by HelperService::updateBenchmarkScores for the bmk lens, and presumably by an external/upstream process not in this repo for yrs/top/ind/pv1) and then simply read back per-request, rather than being computed live on every page load. This is a sound performance pattern for the benchmark data specifically, but it also means the app depends on that pre-computation having run and being current — there is no cache-invalidation or staleness check visible in the code.

---

9. Authentication & Authorization

9.1 Two parallel auth systems

flowchart LR
    subgraph Sanctum["Sanctum (SPA / api.php)"]
        L1["POST /login"] --> H1{"Hash check"}
        H1 -->|ok| T1["createToken('api-token') - no expiry configured"]
        T1 --> PAT[("personal_access_tokens")]
        Req1["Subsequent request - Authorization Bearer token"] --> San["Sanctum guard - hashes token, looks up PAT row"]
        San --> U1["request user"]
    end
    subgraph Session["Session (Blade / web.php)"]
        L2["Blade login form - entry point not fully traced"] --> AuthFacade["Auth::attempt via LoginController trait"]
        AuthFacade --> Sess[("session store")]
        Req2["Subsequent request - session cookie"] --> AuthMW["Authenticate middleware"]
        AuthMW --> U2["Auth::user"]
    end

9.2 Sanctum token flow

• Config: config/sanctum.php — stock defaults. expiration is null (:33), meaning personal access tokens never expire unless revoked. stateful domains default to localhost/127.0.0.1 variants plus APP_URL's host (:16-20) — this only matters for Sanctum's SPA cookie-session mode, which this app does not appear to use (the EnsureFrontendRequestsAreStateful middleware is commented out in app/Http/Kernel.php:43), confirming the SPA authenticates purely via bearer tokens, not cookies.
• Issuance: AuthController::login (app/Http/Controllers/api/AuthController.php:31) — $user->createToken('api-token')->plainTextToken. Every login creates a new token; old tokens for the same user are never revoked on a fresh login, so a user can accumulate unlimited valid tokens over time (no "log out other devices" or token-rotation logic).
• Revocation: AuthController::logout deletes only currentAccessToken() — the specific token used for that request (:43). There is no "revoke all tokens" endpoint.
• Authorization check: none beyond "is this a valid, unexpired token" — Sanctum's auth:sanctum middleware alone gates every protected API route. There is no role or permission check anywhere in routes/api.php or its controllers — any authenticated user can call any API endpoint and will see whatever survey/company data $user->company_id/$user->survey() resolves to for their own account. This is fine as a data-scoping model (each user only ever sees their own company's data, because the queries are scoped by $user->company_id/current_survey_id) but there is no distinction between e.g. an admin user and a regular report-viewer user at the API layer.

9.3 Session/Blade auth flow

• Guard: config/auth.php:39-43 — web guard, session driver, App\Models\User as the Eloquent provider.
• Middleware: App\Http\Middleware\Authenticate (app/Http/Middleware/Authenticate.php) extends the stock Laravel Authenticate middleware, only overriding redirectTo() to send unauthenticated non-JSON requests to the named login route (:17-19) — but as noted in §4.8, Auth::routes() is commented out in routes/web.php:8, so no route is registered with the name login, meaning this redirectTo() call would itself throw a RouteNotFoundException if it were ever triggered for an unauthenticated Blade-portal request. This is flagged as a genuine gap — see §17 and §20.
• Logout: the only working session-logout path is the hand-rolled GET /logout closure in routes/web.php:58-61.

9.4 Role-based middleware (RoleAdmin/RoleClient/RoleEmp)

app/Http/Middleware/RoleAdmin.php, RoleClient.php, RoleEmp.php each check Auth::user()->role->id against a hardcoded numeric set:

Middleware	Allowed role->id values
RoleAdmin	[1, 2]
RoleClient	[2]
RoleEmp	[3]

Source: app/Http/Middleware/RoleAdmin.php:19, RoleClient.php:19, RoleEmp.php:19. All three redirect to / on failure (not a 403 JSON response — these are clearly designed for the Blade/session flow, not the API). None of these three middleware classes are registered as route middleware aliases in app/Http/Kernel.php (:56-66 lists only auth, auth.basic, cache.headers, can, guest, password.confirm, signed, throttle, verified — no role.admin/role.client/role.emp alias), and no route in routes/web.php or routes/api.php applies them. They are therefore entirely dead code as currently wired — present in the codebase but unreachable from any route. Additionally, Auth::user()->role implies a role() relationship/accessor on User, but no such relationship is defined on App\Models\User (app/Models/User.php:15-23 only defines company() and survey()) — calling this middleware as-is would likely throw an error (accessing an undefined dynamic property or relation) even if it were wired up. See §17.

9.5 FirstLogin middleware

app/Http/Middleware/FirstLogin.php:13 checks Auth::user()->first_login == 1; if true, passes through, otherwise redirects to /portal/first-login — a route that does not exist in routes/web.php. Like the Role* middleware, FirstLogin is not registered as a route-middleware alias and not applied to any route — dead code. (Note this is the inverse of the "first login onboarding" pattern one might expect — == 1 passing through, rather than gating toward a first-login flow, suggests first_login here may mean "has completed first-login setup" rather than "is on their first login," but this cannot be confirmed without seeing where it is actually used.)

9.6 Authorization summary

Layer	Mechanism	Status
API authentication	Sanctum bearer token	Working, wired to every protected api.php route
API authorization (role/permission)	none	Not implemented — data scoping is implicit via $user->company_id/current_survey_id only
Session authentication	Laravel session guard	Working for already-authenticated requests; the unauthenticated redirect target (login named route) appears broken
Session authorization (role)	RoleAdmin/RoleClient/RoleEmp	Defined but not wired to any route — dead code
First-login gating	FirstLogin middleware	Defined but not wired to any route — dead code

---

10. Background Processing

No queue-based background processing is used anywhere in this codebase.

• config/queue.php defaults to the sync driver (env('QUEUE_CONNECTION', 'sync'), :16) — Laravel's "run jobs immediately, inline, no worker needed" driver. .env.example also sets QUEUE_CONNECTION=sync (.env.example:20).
• No app/Jobs/ directory exists in this codebase (not present in the file listing), and no dispatch()/->queue()/Bus::dispatch calls were found in any controller or service reviewed.
• app/Console/Kernel.php:25-28 — the scheduler's schedule() method body is entirely commented out (only the stock example $schedule->command('inspire')->hourly(); remains, itself commented). No scheduled commands exist.
• Long-running operations that in a more scalable design would be queued jobs are instead run synchronously inline within the HTTP request, protected only by raising max_execution_time via ini_set:
• HelperService::updateBenchmarkScores — ini_set('max_execution_time', 600000) (app/Services/HelperService.php:132) — a 10-minute-equivalent synchronous batch recompute.
• Most PortalController/api/PortalController report methods — ini_set('max_execution_time', 600000) at the top of nearly every action (e.g. api/PortalController.php:91,249,330).
• NA2ApiController::getOEFContentFromClaude — ini_set('max_execution_time', 60000) (:834) around a synchronous Claude API call with a 1200-second Guzzle timeout (ClaudeService.php:22) — meaning a single slow Claude response can occupy a PHP-FPM/web worker for up to 20 minutes.

Conclusion: there is no background processing in this application as it stands. Any future work involving long-running AI calls, bulk email, or large benchmark recomputations should introduce Laravel's queue system (with a real driver — database or redis) rather than continuing to rely on ini_set('max_execution_time', ...) inline. See §18.

---

11. Integrations

flowchart LR
    APP["EES Report Backend"]
    APP -->|HTTPS POST, x-api-key header| Claude["Anthropic Claude API - claude-sonnet-4-6"]
    APP -->|HTTPS POST, Bearer key, Guzzle| OpenAI["OpenAI Chat Completions - gpt-3.5-turbo"]
    APP -->|SendGrid PHP SDK| SendGrid["SendGrid Email API"]
    APP -->|Http::post, no auth header shown| Legacy["2021.bptwpakistan.com - external legacy benchmark API"]
    APP -->|maatwebsite/excel, PhpSpreadsheet| ExcelFiles[("uploaded .xlsx files")]

Integration	Purpose	Client	Where	Failure handling
Anthropic Claude	OEF sentiment/category/highlight tagging + synthesized "good/bad/ugly" theme extraction from open-ended feedback	Laravel Http facade (wraps Guzzle)	app/Services/ClaudeService.php	Throws a generic \Exception with the response body on non-2xx (:43-45); no fallback content; 2 retries on connection failure only, 1200s timeout (:22-25); caller (NA2ApiController::getOEFContentFromClaude) does not itself catch this exception, so a Claude failure surfaces as an uncaught 500
OpenAI	Per-question narrative summarization of OEF answer buckets	guzzlehttp/guzzle Client directly (not the Laravel Http facade)	app/Services/OpenAIService.php	Catches all \Exceptions and returns a hardcoded fallback string 'Professional analysis could not be generated at this time.' (:66-73,170-179) — graceful degradation, unlike the Claude path
SendGrid	Bulk transactional email	sendgrid/sendgrid official SDK	app/Services/SendGridAppService.php	Catches Exception per 1000-recipient chunk and returns {status:false, message} (:25-29) — but note the catch (Exception $e) clause references the global \Exception without an import or leading backslash (:25), which in a namespaced file only works if PHP falls back to the global namespace for an unresolved class — functionally works in PHP but is a style inconsistency vs. the rest of the codebase which does use Exception; or \Exception explicitly
Legacy external benchmark API (http://2021.bptwpakistan.com)	Fetch benchmark comparison score data for a chosen peer-company set	Laravel Http facade	app/Http/Controllers/PortalController.php:251-256	No error handling shown — json_decode(Http::post(...)->getBody()) — a failed/non-JSON response would produce null, which the subsequent foreach($raw_data as $o1) would then simply skip (no rows inserted, no error surfaced to the user); the URL is hardcoded plaintext HTTP (not HTTPS) with no API key/auth header
Excel import	Bulk user creation from spreadsheet	maatwebsite/excel (Maatwebsite\Excel\Concerns\ToModel)	app/Imports/UsersImport.php	No explicit error handling in the import class itself; package-level transaction wrapping is configured (config/excel.php:278-283, 'handler' => 'db') so a failed import batch should roll back

Configuration wiring

• config/services.php:17,35-41 exposes sendgrid_api_key (env SENDGRID_API_KEY), openai.key (env OPENAI_API_KEY), and anthropic.key (env ANTHROPIC_API_KEY) — note .env.example does not list any of SENDGRID_API_KEY, OPENAI_API_KEY, or ANTHROPIC_API_KEY (see §12), so a developer copying .env.example to .env would need to know to add these separately; they are undocumented in the example file.
• ClaudeService and OpenAIService read their keys via config('services.anthropic.key')/config('services.openai.key') respectively (correct, config-driven pattern). OpenAIService's constructor throws immediately if the key is empty (app/Services/OpenAIService.php:22-24) — meaning the service cannot even be constructed (and thus cannot be dependency-injected into api/PortalController, which injects it in its constructor at :24-27) without a valid key present, which would break every route on api/PortalController (not just the AI-related ones) if OPENAI_API_KEY is unset — a significant availability risk, since this controller also serves the Overview/Participation/Demographic/LME endpoints that have nothing to do with OpenAI. See §17.

---

12. Configuration

Environment variables

.env.example (.env.example:1-52) only documents the stock Laravel skeleton defaults. Based on code usage, the following variables are required in practice but absent from .env.example:

Variable	Purpose	Where read
ANTHROPIC_API_KEY	Claude API key	config/services.php:40 → ClaudeService::__construct
OPENAI_API_KEY	OpenAI API key	config/services.php:36 → OpenAIService::__construct (constructor throws if empty)
SENDGRID_API_KEY	SendGrid API key	config/services.php:17 → SendGridAppService::sendBulkEmail

Variable (documented in .env.example)	Purpose
APP_NAME, APP_ENV, APP_KEY, APP_DEBUG, APP_URL	stock Laravel app identity/bootstrap; APP_KEY must be generated via php artisan key:generate before first run
LOG_CHANNEL, LOG_LEVEL	logging config (see §13)
DB_CONNECTION, DB_HOST, DB_PORT, DB_DATABASE, DB_USERNAME, DB_PASSWORD	MySQL connection (config/database.php:46-64)
BROADCAST_DRIVER, CACHE_DRIVER, FILESYSTEM_DRIVER, QUEUE_CONNECTION, SESSION_DRIVER, SESSION_LIFETIME	stock driver selection; CACHE_DRIVER=file, QUEUE_CONNECTION=sync — no Redis/queue infra configured by default
MEMCACHED_HOST, REDIS_HOST, REDIS_PASSWORD, REDIS_PORT	present but unused — no cache/queue driver in this app is set to redis
MAIL_MAILER, MAIL_HOST, MAIL_PORT, MAIL_USERNAME, MAIL_PASSWORD, MAIL_ENCRYPTION, MAIL_FROM_ADDRESS, MAIL_FROM_NAME	stock Laravel Mail facade config — not used by this app's actual email path, which goes through SendGridAppService (the SendGrid PHP SDK directly) rather than Laravel's mailer; MAIL_* appears vestigial
AWS_ACCESS_KEY_ID, AWS_SECRET_ACCESS_KEY, AWS_DEFAULT_REGION, AWS_BUCKET, AWS_USE_PATH_STYLE_ENDPOINT	present for the stock s3 filesystem disk (config/filesystems.php:45-54) — no code in this app was found writing to the s3 disk; likely vestigial/unused unless the (undocumented) Excel-import or file-upload flow uses it
PUSHER_APP_ID, PUSHER_APP_KEY, PUSHER_APP_SECRET, PUSHER_APP_CLUSTER, MIX_PUSHER_*	stock Laravel Echo/broadcasting scaffolding — unused; routes/channels.php only defines the default App.Models.User.{id} channel and no broadcasting code triggers events

Config files

File	Purpose
config/database.php	MySQL as default connection (:18); also declares unused sqlite/pgsql/sqlsrv connection stanzas (stock)
config/sanctum.php	Token auth for the API; expiration: null (never expires)
config/cors.php	paths: ['api/*', 'sanctum/csrf-cookie'], allowed_origins: ['*'], supports_credentials: false — see §15
config/auth.php	web guard/session-based; App\Models\User as the sole provider
config/services.php	SendGrid, OpenAI, Anthropic keys (see above); also stock mailgun/postmark/ses stanzas (unused)
config/excel.php	maatwebsite/excel defaults — chunk_size: 1000, heading_row.formatter: slug, transaction handler db
config/queue.php	sync driver by default — see §10
config/filesystems.php	local default disk; s3 disk configured but apparently unused
config/app.php	stock Laravel app config; timezone: UTC (:70), locale: en

Feature flags

None. No feature-flag framework or env-driven boolean toggle mechanism was found beyond the ad-hoc surveys.data_type (avg vs. percentage display mode) and surveys.first_login/user.first_login fields, which are data-driven UI-state toggles rather than deployment feature flags.

---

13. Logging & Error Handling

Logging

config/logging.php was not separately re-read in full, but .env.example:7-8 sets LOG_CHANNEL=stack/LOG_LEVEL=debug — the stock Laravel default (writes to storage/logs/laravel.log via the single/daily channel stack). Application-level logging calls found in the codebase:

• Illuminate\Support\Facades\Log::error(...) in OpenAIService::getResponse (:67-70) and OpenAIService::generateOefCollSummaries's catch block (:107-110).
• Log::info(...) calls appear commented out in TestController::DGPivotTableView (:49) and left as dead debug code.
• No structured/contextual logging convention (correlation ids, request ids) is used anywhere.
• No logging at all around the Claude API call (ClaudeService), the external legacy-benchmark-API call (PortalController::benchmarkRequest), or the vast majority of the raw-SQL-heavy controller methods — failures in those paths are only visible via the generic Laravel exception handler (below) or, for a failed Claude call, an uncaught \Exception that will still be logged by Laravel's default report() behavior since Handler::$dontReport is empty (app/Exceptions/Handler.php:15-17).

Exception handling

app/Exceptions/Handler.php is the stock, unmodified Laravel exception handler: - $dontReport = [] (:15-17) — every exception type is reported (logged) by default. - $dontFlash = ['current_password', 'password', 'password_confirmation'] (:24-28) — stock sensitive-field exclusion from session-flashed validation error data. - register() (:35-40) has an empty reportable() closure — no custom exception-to-response mapping is implemented. This means: - Any uncaught exception in an API controller method (e.g. NA2ApiController::getOEFContentFromClaude when Claude errors, or any of the many raw-SQL calls if the underlying table/column doesn't exist) falls through to Laravel's default behavior: in debug mode (APP_DEBUG=true), a full stack trace is returned as HTML/JSON (via facade/ignition, a dev dependency); in production (APP_DEBUG=false), a generic 500 response with no detail. - There is no uniform API error envelope. Some controller methods manually catch and format errors (api/PortalController::generateSummaries, :1958-1964; TestController::fetchReporteesByLMENID, :135-140), returning {success:false, message}-shaped JSON with an explicit status code. Most other methods have no try/catch at all and rely entirely on the framework default, meaning API clients cannot rely on a consistent error response shape across endpoints.

Error responses observed in the wild (from code reading)

Pattern	Where
response()->json(['success'=>false, 'message'=>...], 404)	"No survey found" / "Company not found" guards, used consistently across most api/PortalController methods
response()->json(['success'=>false, 'message'=>..., 'error'=>$e->getMessage()], 500)	generateSummaries
response()->json(['status'=>500, 'message'=>$e->getMessage()], 500)	fetchReporteesByLMENID (note: uses status key, not success — inconsistent with the rest of the app)
Uncaught exception → framework default	everything else

---

14. Performance Considerations

N+1 and repeated-query risk

• ProviderController::getCalculatedScores is called repeatedly, once per tier, inside the same request in several controller methods rather than once with the results reused. For example, api/PortalController::consolidatedBreakdownView (:1438-1453) calls getCalculatedScores($sv_id, 'dr', 'ecem') inside a loop over clusters (once per cluster iteration, :1441) and getCalculatedScores($sv_id, 'dm', 'ecem') inside the nested loop over drivers (once per driver iteration, :1443) — for a report with, say, 5 clusters and 4 drivers each, this issues the full dimension-score query 20 times instead of once, each time re-running 5 separate SQL statements internally (the tiers query plus 5 getScoreByType calls). The same pattern repeats in the Blade twin PortalController::consolidatedBreakdownView (:392-419) and in demographicBreakdownView (:592-618).
• Every "get NPS with benchmark lenses" query (e.g. api/PortalController.php:157-166, :1044-1053, :1535-1544) runs one DB::select per lens per NPS row inside a ->map() closure — for top/ind/bmk/pv1, that is up to 4 extra round-trip queries per NPS question, per request, executed serially.
• ProviderController::getScore2/getScore and their analysisQuery/analysisQuery2 helpers run a nested DB::select inside a ->transform() closure per row (ProviderService::analysisQuery, app/Services/ProviderService.php:64-131 — two nested subqueries per row) — though as noted in §4.6 this specific class appears to be legacy/unused.

Caching

No application-level caching is used anywhere. config/cache.php was not separately re-read, but .env.example:18 sets CACHE_DRIVER=file (stock default). No Cache::remember(...) or similar call was found in any controller or service reviewed. Every report page recomputes its full score tree from raw SQL on every single request, even though most of the underlying calculations/demographic_calculations/nps_calculations data changes infrequently (only on survey close / benchmark recompute). This is the single largest performance opportunity in the codebase (see §18).

Pagination

No pagination exists anywhere in the API. Every list-shaped response (industries+companies in benchmarkView, the full pivot-table row set in DGPivotTableView, the full OEF comment list in getVoiceOfTheEmployee) is returned in full in a single response. For a large company with thousands of respondents, DGPivotTableView's per-score flattened row array (api/PortalController.php:1382-1418, one row per score per respondent) could be very large — a company with 500 respondents and, say, 40 dimensions produces 20,000 flattened rows in one JSON payload.

Query construction cost

Nearly every query is built via PHP string concatenation of SQL fragments (see §8.1), which: - Prevents MySQL query-plan caching from being as effective as it would be with consistently-parameterized queries (each unique literal value produces a textually-different query string). - Makes query auditing/EXPLAIN-plan review harder, since the same logical query appears with many different literal-embedded variants across the codebase.

Indexes

Indexing cannot be confirmed from this codebase (no migrations define the relevant tables — see §8.2). Given the query patterns observed (heavy filtering on survey_id, entry_id, dimension_id, demographic_id/demographic_option_id, and (tier, type, sct, survey_id) on calculations), the following composite indexes would likely be beneficial if not already present: calculations(survey_id, sct, tier, type), entries(survey_id, has_scores), entries(lm_entry_id), demographic_entry(entry_id, demographic_id), scores(entry_id, dimension_id). This is a recommendation, not a confirmed gap — unable to determine actual index presence from the codebase.

---

15. Security

Area	Status in this codebase
CORS	config/cors.php:22 — allowed_origins: ['*'] (any origin allowed) combined with allowed_methods: ['*'] and allowed_headers: ['*'] (:20,26). supports_credentials: false (:32) somewhat limits the blast radius (cookies/credentials are not shared cross-origin), but since the API is bearer-token authenticated (not cookie-based) rather than relying on supports_credentials, a malicious site could still make authenticated requests on behalf of a user if it can obtain that user's bearer token — the wildcard CORS origin itself does not directly leak tokens, but it does mean any website can make (unauthenticated) requests to /api/get-benchmark-data and read the response, and any website could probe API shape/errors freely.
CSRF	Disabled for the entire session/Blade portal — \App\Http\Middleware\VerifyCsrfToken::class is commented out of the web middleware group (app/Http/Kernel.php:38). Every session-authenticated POST route (/report/benchmark, /report/demographic/change, /report/comparison/main, /temp/temp1) is therefore vulnerable to cross-site request forgery: a malicious page could submit a form to any of these endpoints on behalf of a logged-in portal user, and the request would be honored purely on the strength of the session cookie.
Public unauthenticated endpoints	POST /api/get-benchmark-data (app/Http/Controllers/ApiController.php, routed in routes/web.php:20) has no authentication or authorization check at all — anyone can call it and retrieve aggregate engagement scores for any set of company ids they choose to pass in benchmarks[], effectively an open cross-company data-scoping bypass for whichever companies exist in the underlying companies/scores/entries tables. This is a significant confidentiality concern if the underlying data is meant to be company-confidential.
SQL injection surface	Widespread string-interpolated SQL (see §8.1). The majority of interpolated values originate from server-side-resolved ids ($sv_id, $cmp_id from the authenticated user's own row) rather than directly from raw request input, which reduces — but does not eliminate — practical exploitability. However, several endpoints interpolate request-supplied values with no validation or binding: e.g. ApiController::getBenchmarkData builds implode(", ", $request->benchmarks) directly into an IN (...) clause (app/Http/Controllers/ApiController.php:12,37) with no type/format validation on the benchmarks array elements, and this endpoint is also unauthenticated (compounding the risk); api/PortalController::demographicDataInsight reads cm_dg_id/lm_dg_id from query params with a numeric default but interpolates them via parameter binding in some queries (:1091-1099, correctly parameterized) while NA2ApiController and much of ProviderController interpolate similarly-shaped ids as raw string concatenation elsewhere. A rigorous injection audit of every query is out of scope here, but the pattern itself is the risk: any future change that starts accepting a new free-text or loosely-validated parameter into one of these string-built queries would be exploitable by default, since the codebase's dominant convention is concatenation, not binding.
Auth guards	Sanctum correctly gates all api.php business routes except /login. Session auth middleware correctly gates all web.php /report/*//test/* routes. The one glaring gap is POST /api/get-benchmark-data, described above.
Password handling	Hash::check/Hash::make used correctly in AuthController::login. However, UsersImport::model() (app/Imports/UsersImport.php:24) sets every bulk-imported user's password to Hash::make(5) — the literal integer 5 as the plaintext password for every single imported user, meaning any bulk-imported account shares the exact same trivially-guessable password unless changed out-of-band.
Token lifetime	Sanctum tokens never expire (config/sanctum.php:33) and are never rotated on login (a new token is issued but old ones remain valid indefinitely until manually revoked, and there is no "revoke all" endpoint) — see §9.2.
Transport security	PortalController::benchmarkRequest calls the external benchmark API over plaintext HTTP, not HTTPS (http://2021.bptwpakistan.com/..., app/Http/Controllers/PortalController.php:251) — any survey benchmark data exchanged with that host is unencrypted in transit.
Dead/unwired authorization code	RoleAdmin/RoleClient/RoleEmp/FirstLogin middleware exist but are not registered or applied anywhere (see §9.4/§9.5) — if a developer assumes these provide real protection (e.g. because they exist and look complete), they would be mistaken; any route "protected" only by adding one of these middleware classes as a string alias that was never registered would silently not apply it (Laravel would throw a ReflectionException/InvalidArgumentException for an unregistered alias, which is at least a loud failure — but the more dangerous case is a developer registering the alias correctly going forward without realizing the underlying Auth::user()->role relationship doesn't exist on the model).
Sensitive data in responses	AuthController::login returns the full User model in the JSON response (app/Http/Controllers/api/AuthController.php:36) — the model's $hidden array correctly excludes password/remember_token (app/Models/User.php:42-45), so this is safe as implemented, but note it is the only protection; any new sensitive column added to users in the future would be exposed by default unless also added to $hidden.

---

16. Developer Guide

Prerequisites

• PHP ^7.3 or ^8.0 (per composer.json:8) with the extensions Laravel 8 requires (ext-mbstring, ext-pdo, ext-openssl, etc. — standard for a Laravel install).
• Composer.
• MySQL server (the app assumes an already-populated, externally-managed schema — see §8.2; running fresh migrations alone will not produce a working database for this app, since most tables the code depends on are not created by any migration here).
• Node.js is not required for this repository (no package.json/frontend build tooling was found in the file listing) — the actual SPA lives in the separate EES-V2.0-Frontend repository.

First-time setup

# 1. Install PHP dependencies
composer install

# 2. Environment
cp .env.example .env
php artisan key:generate

# 3. Fill in .env — beyond the stock values already in .env.example, you MUST also add:
#    DB_HOST / DB_DATABASE / DB_USERNAME / DB_PASSWORD pointing at the existing EES database
#    ANTHROPIC_API_KEY=...   (required or ClaudeService will fail when constructed)
#    OPENAI_API_KEY=...      (required — OpenAIService's constructor THROWS if empty,
#                              which will break the entire api\PortalController since it is
#                              constructor-injected there; see §11)
#    SENDGRID_API_KEY=...    (required only if bulk email features are exercised)

# 4. Run only the migrations that exist (stock Laravel tables + misc_stats).
#    This will NOT create the application's real schema (companies, surveys, entries,
#    scores, dimensions, drivers, clusters, combines, demographics, calculations, etc.) —
#    you need a database dump/copy of the existing EES database for the app to function.
php artisan migrate

# 5. Serve
php artisan serve
# or, using the bundled server.php entry point directly:
php -S localhost:8000 server.php

Running

• Web/API server: php artisan serve (uses server.php/public/index.php as the front controller).
• No queue worker is needed — QUEUE_CONNECTION=sync means jobs (if any existed) would run inline. There is currently no scheduler (schedule:run) work either (see §10).
• Sanctum-authenticated API testing: POST /api/login with {email, password} against a seeded users row, then send Authorization: Bearer <token> on subsequent requests.
• Session/Blade portal testing: log in via whatever front-end form posts to the Laravel session-auth login flow (the exact login route could not be confirmed in this codebase — see §20) and then browse /report/*.

Where to add things

I want to…	Do it here
Add a new API endpoint consumed by the SPA	Add a method to app/Http/Controllers/api/PortalController.php (or a new controller under app/Http/Controllers/api/) and register it under Route::middleware('auth:sanctum')->group(...) in routes/api.php. Follow the existing {success, message, ...} JSON convention for new endpoints even though older endpoints are inconsistent (§3) — do not perpetuate the raw-array-return pattern.
Add a new Blade report page	Add a method to app/Http/Controllers/PortalController.php, a Blade view under resources/views/portal/..., and a route inside the Route::group(['middleware' => ['auth']], ...) block in routes/web.php.
Add a new score-aggregation query	Prefer adding a method to ProviderController (the existing shared query layer) rather than inlining more raw SQL directly into a controller action, even though the existing code does not always follow this.
Add a new external AI integration	Follow the ClaudeService/OpenAIService pattern: a dedicated service class under app/Services/, reading its API key via config('services.<name>.key'), with the key sourced from config/services.php (add a new stanza there) and documented in .env.example (currently ANTHROPIC_API_KEY/OPENAI_API_KEY/SENDGRID_API_KEY are not documented there — fix this alongside any new addition).
Add request validation	Prefer a dedicated FormRequest class (see ChangeBenchmarkRequest/DemographicChangeRequest for the pattern) over inline $request->validate([...]) calls, for consistency and reusability.
Add a background/async job	Introduce Laravel's queue system properly (switch QUEUE_CONNECTION to database or redis, add a ShouldQueue job class) rather than continuing the ini_set('max_execution_time', ...) inline-synchronous pattern — see §18.

---

17. Code Quality Review

Findings below are drawn directly from the source, each with a file:line citation. Severity is the author's engineering judgment.

Correctness bugs (high priority)

#	Location	Issue
1	app/Http/Controllers/api/PortalController.php:1708-1712 (organisationalNps)	$survey = $user->survey(); is executed on :1711, before $user = $request->user(); is assigned on :1712. $user is referenced while undefined, which under PHP 8 raises a warning ("Undefined variable $user") and returns null, causing $user->survey() to throw a fatal error (Call to a member function survey() on null). This endpoint (POST /api/organisational-nps) is currently broken as written.
2	routes/web.php:13	Route::get('/seed', [PortalController::class,'seedCalculations']); references a method that does not exist anywhere in app/Http/Controllers/PortalController.php (read in full, 831 lines, no seedCalculations method present). Hitting this route throws a fatal error.
3	app/Http/Middleware/Authenticate.php:17-19 combined with routes/web.php:8	redirectTo() routes unauthenticated non-JSON requests to route('login'), but Auth::routes(...) is commented out in routes/web.php:8, so no route is registered under the name login. An unauthenticated request to any session-auth-protected route would throw a RouteNotFoundException instead of gracefully redirecting to a login page.
4	app/Http/Middleware/RoleAdmin.php:19, RoleClient.php:19, RoleEmp.php:19	All three reference Auth::user()->role->id, but App\Models\User (app/Models/User.php) defines no role() relationship — only company() and survey(). Even if these middleware were wired to a route (they currently are not — see finding below), invoking them would error.
5	app/Http/Middleware/RoleAdmin.php, RoleClient.php, RoleEmp.php, FirstLogin.php	None of these four middleware classes are registered as aliases in app/Http/Kernel.php:56-66, and none are applied to any route in routes/web.php/routes/api.php. They are fully dead/unreachable code, which is misleading to a developer who might reasonably assume role-based access control exists in this app because these classes are present.
6	database/migrations/2025_12_22_053542_create_misc_stats_table.php:16-19 vs. every raw-SQL reference to `miscstats` (e.g. app/Http/Controllers/api/NA2ApiController.php:465-472)	The migration creates a table named misc_stats with only id/timestamps; the application code queries a differently-named table miscstats (no underscore) for columns (intgr1..intgr5) that this migration does not create at all. Running this migration does not produce a table the app can actually use.
7	app/Imports/UsersImport.php:24	"password" => Hash::make(5) hashes the literal integer 5, not a randomly generated password or a value derived per-row. Every user created via this Excel import shares the identical (extremely weak) password.

Duplicate logic

• The entire report/analytics surface is implemented twice — once as JSON in app/Http/Controllers/api/PortalController.php (2410 lines) and once as Blade views in app/Http/Controllers/PortalController.php (831 lines) — with the majority of the underlying SQL hand-copied between the two rather than shared. For example, the NPS-summary-with-benchmark-lenses query appears near-verbatim in api/PortalController::OverView (:144-166), api/PortalController::reportLineManagerEffectiveness (:1522-1544), NA2ApiController::getExecutiveSummary (:60-79), NA2ApiController::getOrganisationalNPS (:154-173), and api/PortalController::organisationalNps (:1730-1752) — five separate copies of essentially the same SQL + mapping logic, each with minor, easy-to-miss variations (e.g. some use ->first()->score ?? 'N/A', others use [0]->score with no null guard).
• The 5-part "integrity score" computation (intgr1..5 weighted formula) is duplicated verbatim in NA2ApiController::getParticipationSummary (:466-473) and api/PortalController::Participation (:296-305).
• The demographic-breakdown query (dem_temp/demographic_breakdown loop) is duplicated across at least api/PortalController::OverView (:114-142), api/PortalController::Participation (:263-291), NA2ApiController::getParticipationSummary (:476-503), and the Blade PortalController::consolidatedSummaryView (:316-343) — four near-identical copies.
• The word-cloud stop-word list is defined twice, once (smaller, 12-concept-map version) in app/Services/WordCloudService.php:11-104 and once (larger, ad-hoc version) inline in api/PortalController::generateWordCloud (:506-557), with different stop-word sets and no shared source of truth — the injected WordCloudService is used for consolidatedNPSAndOEF but generateWordCloud (an apparently-superseded method on the same controller) still contains its own separate implementation.
• ProviderController::getCalculatedScores is re-run redundantly inside loops rather than computed once and filtered in-memory in at least 4 methods (consolidatedBreakdownView in both controllers, demographicBreakdownView calls the demographic equivalent similarly) — see §14 for the performance angle of this same finding.
• getDemographicScores's private helper methods (calculateDimensionScores, calculateYrsScores, tail of api/PortalController.php) reimplement logic that already exists, differently, in ProviderController::getSelectedDemographicScore/demographicAnalysisQuery — a second, parallel implementation of "compute a dimension's score for a set of entry ids," with no indication which is canonical going forward.

Tight coupling / architectural smells

• api/PortalController constructor-injects OpenAIService, whose constructor throws if OPENAI_API_KEY is unset (app/Services/OpenAIService.php:22-24). This couples the availability of every single endpoint on this controller (Overview, Participation, LME, demographic cuts, etc. — none of which use OpenAI) to the presence of a valid OpenAI key, purely because of where the dependency was injected.
• ProviderController is used as a static utility class from two independent controllers (PortalController and api\PortalController) and is itself namespaced/suffixed as a Controller despite never being routed to directly — a naming/architecture mismatch that makes its actual role (shared query layer) non-obvious to a new developer.
• Raw table/column names are string-literal-duplicated across dozens of call sites with no central query-builder or model-scope wrapping them, so a future schema rename would require a manual, error-prone, codebase-wide find/replace across raw SQL strings rather than a single model/scope change.

Large classes / methods

• app/Http/Controllers/api/PortalController.php — 2410 lines, dozens of unrelated report-endpoint methods in one class.
• app/Http/Controllers/ProviderController.php — 1016 lines, ~35 static methods, several apparently dead/legacy (getScore, getScore2, analysisQuery, analysisQuery2, getRankQuery, year-scoped 2015/2017/2019 query variants).
• app/Http/Controllers/PortalController.php — 831 lines, substantially duplicating api/PortalController.
• NA2ApiController::getJSONDataForPrompt (:522-819, ~300 lines) — a single method building a deeply nested multi-branch data structure with heavy unset()-based cleanup, hard to safely modify without full-method comprehension.

Missing validation

• Most api/PortalController methods perform no input validation beyond the two FormRequest classes (ChangeBenchmarkRequest, DemographicChangeRequest) and a handful of inline $request->validate([...]) calls (changeDataTypeRequest, changeFirstLoginRequest, comparisonMain, generateSummaries). Query-string-driven endpoints like reportLineManagerEffectiveness (dg_id), organisationalNps (dg_id), and demographicDataInsight (cm_dg_id/lm_dg_id) accept arbitrary values with only a numeric default, no type/range validation, and interpolate some of them into SQL.
• ApiController::getBenchmarkData (app/Http/Controllers/ApiController.php:12) does not validate that $request->benchmarks is even an array before implode()-ing it, nor that its elements are numeric — combined with the endpoint being unauthenticated (§15), this is the weakest input-validation point in the app.

Dead / vestigial code

• app/Services/ProviderService.php — entirely disconnected from any route or controller reviewed; references a different table schema (participants, questionnaires, likerts, components) than the rest of the app.
• app/Http/Controllers/TestController.php — earlier prototype versions of pivot-table views later reimplemented (correctly, with survey scoping) inside api/PortalController.
• app/Http/Middleware/RoleAdmin.php/RoleClient.php/RoleEmp.php/FirstLogin.php — unwired (see Correctness bugs #5).
• ProviderController's year-scoped (2015/2017/2019/2021) query family (getScore, getScore2, analysisQuery, analysisQuery2, getRankQuery) — no call site found from any currently-routed controller method.
• ProviderController::getCutOffByUser (:838-856) — the method body returns a hardcoded 30 on its very first line (:839), making the rest of the method (a switch-like if chain computing a value based on getReportTypeObject) fully unreachable dead code.
• api/PortalController::DGPivotTableView's $optionCounts computation (:1362-1365) is computed but never referenced again in the method — dead computation, possibly intended for small-cell suppression that was never wired in.
• Large blocks of commented-out SQL/code left in place rather than removed (e.g. NA2ApiController.php:175-188, api/PortalController.php:425-438, 1602-1609, 1635-1645, 1875-1881; PortalController.php throughout the word-cloud stop-word section :446-504).

Miscellaneous smells

• Inconsistent response envelopes across the API (see §3 and §13) — some methods return response()->json(['success'=>..., ...]), others return a bare associative array (implicitly JSON-encoded), with no consistent top-level contract.
• Inconsistent SQL binding style — some queries use ? placeholders with a bindings array (correct, safe), most use direct string interpolation (risk-prone, see §15) — often within the same method (e.g. api/PortalController::getVoiceOfTheEmployee, :400-445, mixes both styles).
• Column-name drift between "newer" and "older" code paths: def vs defl for dimension/driver/cluster/combine descriptions (§8.4); year-based vs survey_id-based entry scoping (§4.6); Auth::user()->survey()->data_type (used in ProviderController, most of api/PortalController) vs Auth::user()->data_type (used in ProviderController::analysisQuery2/analysisQuery, :438,556 — reading the field directly off the user rather than off their survey) — these are two different columns on two different tables being read interchangeably by code that looks superficially similar, a strong indicator of code copied-and-lightly-adapted across different historical versions of the app without full reconciliation.
• Magic numbers: excluded company ids (79,121,64,147) hardcoded three times in HelperService::updateBenchmarkScores (:164,218,259) and again in PortalController::benchmarkView (:1470) and its Blade twin — a shared constant would remove this repetition and the risk of the lists drifting apart.
• ini_set('max_execution_time', 600000) cargo-culted onto nearly every controller method, regardless of whether that specific method actually performs expensive work — a signal that performance problems were patched locally/symptomatically rather than addressed at the query/caching level.
• SendGridAppService::sendBulkEmail catches Exception (global namespace, unqualified) without an explicit use import (app/Services/SendGridAppService.php:4,25) — works in PHP but is stylistically inconsistent with the rest of the codebase.

---

18. Improvement Opportunities

Refactoring

1. Collapse the API/Blade duplication. Extract the shared score-computation and data-shaping logic (currently hand-copied between api/PortalController and PortalController) into a single service/query layer that both a JSON-responding controller and a Blade-view-responding controller call into. ProviderController is a partial attempt at this already — finish the job by moving the remaining inline SQL (NPS summaries, integrity scores, demographic breakdowns, word-cloud stop-word logic) there or into new dedicated service classes.
2. Adopt a single, uniform API response envelope (a small helper akin to response()->json(['success'=>bool,'message'=>string,'data'=>mixed])) and apply it consistently — fixing both the raw-array-return inconsistency and the varying success/status key naming in error responses.
3. Introduce query binding everywhere, replacing string-interpolated SQL fragments with ?/named bindings, even for internally-sourced ids — removes an entire class of latent risk and makes the query cache more effective.
4. Remove or explicitly deprecate dead code: ProviderService, the year-scoped ProviderController methods, the unwired Role*/FirstLogin middleware (or actually wire them up if role-based access control is a real requirement), TestController's superseded pivot views, and getCutOffByUser's unreachable branch.
5. Fix the misc_stats/miscstats migration mismatch — either correct the migration to match the table the app actually queries, or remove the migration if miscstats is genuinely externally managed and this migration was a mistaken addition.

Scalability

1. Add a caching layer for the pre-aggregated score data (calculations/demographic_calculations/nps_calculations reads), since this data only changes when a survey closes or a benchmark recompute runs — the single biggest available performance win given the current all-synchronous, no-cache design.
2. Stop re-running getCalculatedScores inside loops (§14/§17) — compute once per request, filter/group the result in PHP.
3. Move long-running operations to a real queue (HelperService::updateBenchmarkScores, the Claude/OpenAI calls, the external legacy-API call in benchmarkRequest) — switch QUEUE_CONNECTION off sync, introduce ShouldQueue job classes, and stop relying on ini_set('max_execution_time', ...) as the sole mitigation for slow requests.
4. Add pagination to any endpoint that can return an unbounded row set (DGPivotTableView, getVoiceOfTheEmployee's explore_comments).

Maintainability

1. Write the missing tests. tests/Feature/ExampleTest.php and tests/Unit/ExampleTest.php are the stock Laravel placeholders — there is no real test coverage for any of the scoring math, demographic-cut logic, or AI-integration code, all of which is exactly the kind of hand-written-SQL logic most likely to regress silently.
2. Reconcile the def/defl and year/survey_id column-naming drift identified in §17, ideally by confirming with whoever owns the live database schema which naming is current and removing/updating the stale code paths.
3. Document .env.example fully — add ANTHROPIC_API_KEY, OPENAI_API_KEY, SENDGRID_API_KEY (currently undocumented but required — §12) and remove or clearly mark vestigial variables (MAIL_*, PUSHER_*, AWS_* if genuinely unused) so a new developer doesn't waste time chasing dead configuration.
4. Fix the login named-route gap (§17 #3) so unauthenticated session-portal requests redirect gracefully instead of throwing a RouteNotFoundException.

Architecture

1. Decide whether role-based authorization is actually needed. If yes, wire RoleAdmin/RoleClient/RoleEmp up properly (register aliases, apply to routes, add the missing role() relationship to User) and extend the same model to the Sanctum API side (currently zero role/permission distinction exists at the API layer — any authenticated user can call any endpoint). If no, remove the dead middleware entirely to reduce confusion.
2. Restore CSRF protection on the session/Blade routes (§15) — re-enable VerifyCsrfToken and add the CSRF token to whichever forms currently rely on it being absent.
3. Authenticate or remove POST /api/get-benchmark-data (§15) — this is the single highest-priority security fix identified in this review.
4. Clarify the relationship between this repo and the external 2021.bptwpakistan.com host. If that system is a predecessor/sibling of this app, document the data contract between them explicitly (ideally replacing the ad-hoc HTTP call with a properly versioned internal API or retiring it in favor of locally-computed benchmark data via HelperService::updateBenchmarkScores).

---

19. System Diagrams

Component map

flowchart LR
    subgraph "Entry points"
        WebRoutes["routes/web.php"]
        ApiRoutes["routes/api.php"]
    end
    subgraph "Controllers"
        AuthC["api/AuthController"]
        NA2["api/NA2ApiController"]
        ApiPortal["api/PortalController"]
        BladePortal["PortalController"]
        PublicApi["ApiController"]
        Test["TestController"]
        Provider["ProviderController - shared static query layer"]
    end
    subgraph "Services"
        Claude["ClaudeService"]
        OpenAI["OpenAIService"]
        Helper["HelperService"]
        WordCloud["WordCloudService"]
        SendGrid["SendGridAppService"]
        ProviderSvc["ProviderService — likely dead"]
    end
    subgraph "Models"
        M["User, Company, Survey, Entry, - Score, Dimension, Driver, - Cluster, Combine, Demographic*, - Benchmark, Industry, MiscStat"]
    end
    DB[("MySQL — externally managed schema")]
    External[["Anthropic / OpenAI / SendGrid / - 2021.bptwpakistan.com"]]

    WebRoutes --> BladePortal
    WebRoutes --> PublicApi
    WebRoutes --> Test
    ApiRoutes --> AuthC
    ApiRoutes --> NA2
    ApiRoutes --> ApiPortal

    ApiPortal --> Provider
    BladePortal --> Provider
    NA2 --> Provider
    ApiPortal --> Claude
    ApiPortal --> OpenAI
    NA2 --> Claude
    ApiPortal --> WordCloud
    BladePortal --> External

    Provider --> DB
    ApiPortal --> DB
    BladePortal --> DB
    NA2 --> DB
    PublicApi --> DB
    Test --> DB
    Helper --> DB
    M --> DB

    Claude --> External
    OpenAI --> External
    SendGrid --> External

Deployment topology

Unable to determine from the codebase — no Dockerfile, docker-compose.yml, CI/CD workflow files, Procfile, or deployment scripts were found in this repository's file listing. This suggests deployment is either handled entirely outside this repo (a separate infra/ops repository) or via manual/undocumented means. What can be confirmed from the code:

• public/index.php is the standard Laravel front controller, implying a conventional Apache/Nginx + PHP-FPM (or php artisan serve for local dev) deployment.
• server.php at the repo root is present specifically to support PHP's built-in development server (php -S) serving from the repo root while still routing through public/index.php — a local-dev convenience, not a production deployment mechanism.
• No queue worker process is needed (sync queue driver, §10).
• No scheduler (cron → artisan schedule:run) is needed (empty schedule, §10).

flowchart TB
    Browser["Browser — legacy portal"]
    SPAClient["React SPA — EES-V2.0-Frontend - deployment mechanism not in this repo"]
    WebServer["Web server — Apache/Nginx + PHP-FPM - assumed, not confirmed"]
    Laravel["This Laravel app - public/index.php"]
    DB[("MySQL — externally managed, - hosting details not in this repo")]

    Browser --> WebServer
    SPAClient -->|CORS: allowed_origins '*'| WebServer
    WebServer --> Laravel
    Laravel --> DB

---

20. Appendix — Assumptions & "Unable to determine" items

• Database schema authority. The live MySQL schema this app depends on is not created by any migration in this repository (only users, password_resets, failed_jobs, personal_access_tokens, and an incomplete misc_stats exist as migrations). Every table/column description in §8 is reverse-engineered from Eloquent model definitions and raw SQL usage, not from a confirmed DDL source. Actual column types, nullability, default values, indexes, and foreign-key constraints could not be verified without direct database access. (Explicit caveat, not an assumption — this is a confirmed gap given the task's own framing.)
• ProviderService (app/Services/ProviderService.php) is likely dead/legacy code carried over from a different (possibly 360-degree-feedback) product, based on its reference to an entirely different table/column set (participants, questionnaires, likerts, rater_type_id, components) not used anywhere else in this codebase, and the absence of any call site referencing it. (Assumption — no call site was found in the controllers reviewed, but a call site outside the reviewed file set cannot be fully ruled out.)
• The Claude→database write-back path for OEF AI tagging is incomplete or external to this repo. NA2ApiController::updateAIValuesOfOEF reads a static file (public/data/oef_ai_content.json) rather than the live response from getOEFContentFromClaude. Unable to determine from the codebase whether an external/manual process regenerates that static file from Claude's output, or whether this write-back step is simply not yet finished.
• The session/Blade login entry point could not be fully traced. Auth::routes(...) is commented out (routes/web.php:8), no route registers a GET /login view render, and resources/views/auth/login.blade.php exists but its serving route was not found in routes/web.php. Unable to determine from the codebase exactly how a user reaches the session-login form in the current routing configuration — this may be handled by a route defined elsewhere not reviewed, by direct view access, or this may be a genuine gap (see §17 finding #3 for the related redirectTo() breakage).
• App\Imports\UsersImport's trigger point could not be located. No controller method calling Excel::import(new UsersImport, ...) was found in the files reviewed. Unable to determine whether this import is triggered from a route not covered by the file listing provided, an Artisan command, or is currently unused.
• HelperService::updateBenchmarkScores's caller could not be located. No route or controller method invokes this method in the files reviewed. Unable to determine whether it is run via php artisan tinker, a separate script, or is effectively dead code pending a proper trigger.
• AWS S3 configuration (config/filesystems.php, .env.example AWS variables) appears unused — no code path writing to the s3 disk was found. Unable to determine whether file uploads (survey report exports, images, etc.) happen via a mechanism not present in this repository.
• Deployment topology (web server, process manager, CI/CD) is entirely unconfirmed — no Docker/CI/deployment-script files exist in this repository. See §19.
• The relationship between this backend and http://2021.bptwpakistan.com is inferred (same domain family/branding as "bptw", likely a predecessor system or sibling deployment) but not confirmed. Unable to determine whether that host runs the same codebase, a predecessor version, or an entirely separate system that happens to expose a compatible API shape.
• Whether the React SPA (EES-V2.0-Frontend) is served through this Laravel app's catch-all route or entirely separately — the placeholder content of resources/views/frontend/index.blade.php (<h1>hello world</h1>) strongly suggests the SPA is built and served independently of this repo in the real deployment, but this is inferred, not confirmed.
• Actual index presence on the externally-managed MySQL tables — flagged as a performance recommendation in §14, not a confirmed gap, since the schema itself is outside this repository's control.

Document generated from static analysis of the repository at d:/Downloads/EES Documentation/EES-2026/EES - Report backend. Line-number references reflect the state of the code at analysis time (2026-09-23) and may drift as the code evolves.