Researched: 2026-03-25 Domain: Data analytics, charting, PDF/Excel export, full-stack dashboard Confidence: HIGH

Phase 4 builds personal MR dashboards with performance trends and skill radar charts, admin organization-level analytics (BU comparison, skill gap heatmaps, training completion rates), and PDF/Excel export capabilities. The codebase already has substantial infrastructure in place: recharts 3.8.0 is installed with radar/line chart usage patterns established, a score history API exists (GET /scoring/history), session history is partially rendered, and openpyxl is already a backend dependency. The user dashboard page exists but uses limited live data -- this phase enhances it with real aggregated statistics, trend computation, and per-dimension performance tracking.

The primary challenge is that the User model has no business_unit field, which is required for ANLYT-03 (BU comparisons). An Alembic migration to add this field is necessary. The admin dashboard is currently an empty placeholder (EmptyState component). The export flow requires a backend endpoint for Excel (using openpyxl + FastAPI StreamingResponse) and a frontend-driven PDF export using window.print() (already partially implemented in scoring-feedback.tsx) or a dedicated library for richer PDF output.

Primary recommendation: Extend the existing backend analytics service layer with aggregation queries, add the business_unit column to User, build dedicated API endpoints for dashboard stats and admin analytics, enhance the existing user dashboard page with live data, and create the admin analytics page. Use openpyxl for server-side Excel export. Use browser print CSS for PDF export (already established pattern), with jspdf+html2canvas as optional enhancement.

<phase_requirements>

Backend:

• Async everywhere: async def, await, AsyncSession
• Pydantic v2 schemas with model_config = ConfigDict(from_attributes=True)
• Service layer holds business logic, routers only handle HTTP
• No raw SQL -- use SQLAlchemy ORM or Alembic migrations
• Schema changes MUST use Alembic migration
• All models MUST use TimestampMixin
• Create returns 201, Delete returns 204
• Static routes BEFORE parameterized routes
• Ruff lint/format, pytest with >= 95% coverage

Frontend:

• TypeScript strict mode, no any types
• TanStack Query hooks per domain, no inline useQuery
• Path alias @/ for imports
• cn() for conditional classes
• Design tokens as CSS custom properties
• All UI text externalized via react-i18next (zh-CN + en-US)
• No Redux -- TanStack Query for server state

General:

• English for commits/code, Chinese for user-facing text where applicable
• Conventional commits: feat:, fix:, docs:, test:
• API routes under /api/v1/ prefix
• Pagination via PaginatedResponse wrapper
• JWT auth on all protected routes

Installation:

# Frontend
cd frontend
npm install file-saver@^2.0.5 @types/file-saver@^2.0.7

# Backend: no new dependencies needed -- openpyxl already in pyproject.toml

backend/app/
├── api/
│   ├── analytics.py           # NEW: Admin analytics endpoints
│   └── exports.py             # NEW: PDF/Excel export endpoints
├── schemas/
│   └── analytics.py           # NEW: Analytics response schemas
├── services/
│   ├── analytics_service.py   # NEW: Aggregation queries, recommendation engine
│   └── export_service.py      # NEW: Excel workbook generation

frontend/src/
├── api/
│   └── analytics.ts           # NEW: Analytics API client
├── types/
│   └── analytics.ts           # NEW: Analytics TypeScript types
├── hooks/
│   └── use-analytics.ts       # NEW: TanStack Query hooks for analytics
├── components/
│   └── analytics/             # NEW: Chart wrapper components
│       ├── performance-radar.tsx
│       ├── trend-line-chart.tsx
│       ├── skill-gap-heatmap.tsx
│       ├── bu-comparison-bar.tsx
│       └── completion-rate.tsx
├── pages/
│   ├── user/
│   │   └── dashboard.tsx      # MODIFY: Replace hardcoded stats with live data
│   └── admin/
│       ├── dashboard.tsx       # MODIFY: Replace EmptyState with analytics
│       └── reports.tsx         # NEW: Admin export/reports page
├── public/locales/
│   ├── en-US/
│   │   └── analytics.json     # NEW: Analytics i18n namespace
│   └── zh-CN/
│       └── analytics.json     # NEW: Analytics i18n namespace

What: Backend service with SQLAlchemy aggregate functions for dashboard stats When to use: Any endpoint returning computed metrics (averages, counts, trends) Example:

# Source: Existing project pattern from scoring_service.py
from sqlalchemy import func, select
from sqlalchemy.ext.asyncio import AsyncSession

async def get_user_dashboard_stats(db: AsyncSession, user_id: str) -> dict:
    """Compute dashboard statistics for a single user."""
    result = await db.execute(
        select(
            func.count(CoachingSession.id).label("total_sessions"),
            func.avg(CoachingSession.overall_score).label("avg_score"),
        )
        .where(
            CoachingSession.user_id == user_id,
            CoachingSession.status == "scored",
        )
    )
    row = result.one()
    return {
        "total_sessions": row.total_sessions or 0,
        "avg_score": round(row.avg_score or 0, 1),
    }

What: Generate Excel workbook server-side and stream to client When to use: Export endpoints for reports Example:

# Source: FastAPI + openpyxl pattern
from io import BytesIO
from fastapi.responses import StreamingResponse
from openpyxl import Workbook

async def export_sessions_excel(db: AsyncSession, user_id: str) -> StreamingResponse:
    wb = Workbook()
    ws = wb.active
    ws.title = "Session History"
    ws.append(["Date", "Scenario", "Score", "Duration (min)", "Result"])
    # ... populate rows from DB query ...
    buffer = BytesIO()
    wb.save(buffer)
    buffer.seek(0)
    return StreamingResponse(
        buffer,
        media_type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
        headers={"Content-Disposition": "attachment; filename=report.xlsx"},
    )

What: Download binary file from API endpoint using axios responseType blob When to use: When user clicks "Export Excel" button Example:

// Source: Standard axios blob pattern
import { saveAs } from "file-saver";
import apiClient from "./client";

export async function downloadSessionsExcel(): Promise<void> {
  const { data } = await apiClient.get("/analytics/export/sessions", {
    responseType: "blob",
  });
  saveAs(data, `sessions-report-${Date.now()}.xlsx`);
}

What: Simple rule-based scenario recommendation from scoring weakness patterns When to use: ANLYT-04 -- recommend next training scenarios Example:

async def get_recommended_scenarios(
    db: AsyncSession, user_id: str, limit: int = 3
) -> list[dict]:
    """Recommend scenarios targeting user's weakest dimensions."""
    # 1. Find user's average scores per dimension (last 10 sessions)
    # 2. Identify weakest dimension
    # 3. Find scenarios with highest weight for that dimension
    # 4. Exclude recently completed scenarios
    # 5. Return top N

• Inline aggregate queries in routers: All SQLAlchemy aggregate logic belongs in analytics_service.py, not in API routes
• Client-side data aggregation: Do NOT fetch all sessions to the frontend and compute averages/trends in JS. The backend must aggregate via SQL
• Hardcoded chart data: The existing dashboard has "--" placeholder values. Replace with real API-driven data
• Separate API call per stat card: Bundle related stats into a single endpoint (e.g., /analytics/dashboard returns all 4 stat values)
• Missing i18n: Every new string must go through react-i18next. Do not hardcode English/Chinese text

Key insight: This phase is primarily about backend aggregation queries and frontend visualization wiring. The charting library and export tools are already available -- the work is in writing the right SQL queries and connecting data to existing UI patterns.

What goes wrong: Loading each session's score details one-by-one in a loop when computing aggregates Why it happens: Easy to write for session in sessions: load score details How to avoid: Use SQLAlchemy selectinload for relationships, or write aggregate queries with func.avg/func.count and group_by to avoid loading individual records Warning signs: Dashboard stats endpoint takes > 500ms

What goes wrong: ANLYT-03 requires BU comparisons but User model has no business_unit field Why it happens: The field was not part of the original auth schema How to avoid: Add Alembic migration with server_default="" for existing rows. Use render_as_batch=True for SQLite compatibility (Gotcha #1). Warning signs: Admin analytics queries fail with "no such column"

What goes wrong: "This week" and "last 7 days" calculations produce wrong results Why it happens: SQLite stores datetimes as timezone-naive strings. The session model has started.tzinfo is None handling already (see session_service.py:181). How to avoid: Always use datetime.now(UTC) and handle timezone-naive comparison as the existing code does Warning signs: "This week" count shows stale data or all-time data

What goes wrong: Regular users can access organization-level analytics Why it happens: New router uses get_current_user but not require_role("admin") How to avoid: Use require_role("admin") dependency on all admin analytics endpoints, same pattern as admin config/rubric endpoints Warning signs: Non-admin users see org-level data

What goes wrong: Using recharts TreeMap for a skill-gap heatmap produces poor visual results Why it happens: TreeMap is for hierarchical size comparison, not a 2D matrix How to avoid: Build the skill-gap heatmap as a CSS grid with colored cells (similar to GitHub contribution graph). recharts is not the right tool for matrix heatmaps. Warning signs: Heatmap looks like a random rectangle mosaic instead of a structured grid

What goes wrong: Excel export endpoint is accessible without authentication Why it happens: Export routers added without Depends(get_current_user) How to avoid: All export endpoints MUST include user: User = Depends(get_current_user) and admin-only exports must use require_role("admin") Warning signs: Unauthenticated request returns data

# Source: Existing project patterns from scoring_service.py and session_service.py
from datetime import UTC, datetime, timedelta
from sqlalchemy import and_, func, select

async def get_user_dashboard_stats(db: AsyncSession, user_id: str) -> dict:
    """All four stat card values in one query batch."""
    now = datetime.now(UTC)
    week_start = now - timedelta(days=7)

    # Total scored sessions
    total_q = await db.execute(
        select(func.count())
        .select_from(CoachingSession)
        .where(CoachingSession.user_id == user_id, CoachingSession.status == "scored")
    )
    total_sessions = total_q.scalar_one()

    # Average score
    avg_q = await db.execute(
        select(func.avg(CoachingSession.overall_score))
        .where(CoachingSession.user_id == user_id, CoachingSession.status == "scored")
    )
    avg_score = avg_q.scalar_one() or 0

    # This week count
    week_q = await db.execute(
        select(func.count())
        .select_from(CoachingSession)
        .where(
            CoachingSession.user_id == user_id,
            CoachingSession.created_at >= week_start,
        )
    )
    this_week = week_q.scalar_one()

    return {
        "total_sessions": total_sessions,
        "avg_score": round(float(avg_score), 1),
        "this_week": this_week,
    }

# Source: SQLAlchemy aggregate pattern from project
async def get_org_analytics(db: AsyncSession) -> dict:
    """Organization-level analytics for admin dashboard."""
    # Sessions per BU
    bu_result = await db.execute(
        select(
            User.business_unit,
            func.count(CoachingSession.id).label("session_count"),
            func.avg(CoachingSession.overall_score).label("avg_score"),
        )
        .join(CoachingSession, CoachingSession.user_id == User.id)
        .where(CoachingSession.status == "scored")
        .group_by(User.business_unit)
    )
    bu_stats = [dict(row._mapping) for row in bu_result.all()]

    # Training completion rate
    total_users = await db.execute(select(func.count()).select_from(User).where(User.role == "user"))
    users_with_sessions = await db.execute(
        select(func.count(func.distinct(CoachingSession.user_id)))
        .where(CoachingSession.status == "scored")
    )
    total = total_users.scalar_one()
    active = users_with_sessions.scalar_one()
    completion_rate = round((active / max(total, 1)) * 100, 1)

    return {
        "bu_stats": bu_stats,
        "completion_rate": completion_rate,
        "total_users": total,
        "active_users": active,
    }

// Source: Project convention with cn() and design tokens
interface SkillGapCell {
  bu: string;
  dimension: string;
  avgScore: number;
}

function getHeatColor(score: number): string {
  if (score >= 80) return "bg-green-100 text-green-800";
  if (score >= 70) return "bg-yellow-100 text-yellow-800";
  if (score >= 60) return "bg-orange-100 text-orange-800";
  return "bg-red-100 text-red-800";
}

export function SkillGapHeatmap({ data }: { data: SkillGapCell[] }) {
  // Group by BU, display dimensions as columns
  // Each cell shows avgScore with color intensity
}

// Source: TanStack Query mutation + file-saver pattern
import { useMutation } from "@tanstack/react-query";
import { downloadSessionsExcel } from "@/api/analytics";

export function useExportExcel() {
  return useMutation({
    mutationFn: () => downloadSessionsExcel(),
  });
}

Deprecated/outdated:

• recharts v2 API: v3 is installed; some old examples may reference v2 patterns but v3 is backward compatible
• SheetJS (xlsx) has licensing concerns for commercial use; openpyxl (server-side) avoids this entirely

1. Business Unit values for seed data

   • What we know: ANLYT-03 requires BU comparisons. User model needs a business_unit field.
   • What's unclear: What BU values to use for seed/demo data (e.g., "Oncology BU", "Hematology BU", "Solid Tumor BU")
   • Recommendation: Use generic BU names in seed data. The field should be a free-text String(100) -- admin can set values. Pre-populate the 3 seed users with different BUs.

2. Improvement percentage calculation

   • What we know: The scoring service already computes improvement_pct per dimension (current - previous). The dashboard "Improvement" stat card shows "--".
   • What's unclear: Should the dashboard improvement be overall score improvement (latest vs first), or average of last N sessions vs prior N?
   • Recommendation: Use latest scored session's overall_score minus the one before it. Simple, matches existing get_score_history logic.

3. Admin analytics -- which users to include

   • What we know: Admin sees org-level stats. All role="user" accounts should be included.
   • What's unclear: Should admin users who also take training be included in analytics?
   • Recommendation: Include only role="user" in organization analytics. Admins testing scenarios should not skew training metrics.

Missing dependencies with no fallback:

• None (all core tools available)

Missing dependencies with fallback:

• file-saver: Simple npm install in Plan 1

• Existing codebase -- Verified all models, schemas, services, pages, components, and dependencies by direct file inspection
  • backend/app/models/session.py -- CoachingSession model with overall_score, duration_seconds, session_type
  • backend/app/models/score.py -- SessionScore and ScoreDetail models
  • backend/app/models/user.py -- User model (confirmed NO business_unit field)
  • backend/app/services/scoring_service.py -- get_score_history() with dimension trends
  • backend/app/services/report_service.py -- generate_report() with full dimension breakdown
  • frontend/src/pages/user/dashboard.tsx -- Existing dashboard with hardcoded stat placeholders
  • frontend/src/pages/user/session-history.tsx -- LineChart with dimension trends
  • frontend/src/pages/admin/dashboard.tsx -- Empty placeholder
  • frontend/src/components/scoring/radar-chart.tsx -- Reusable recharts RadarChart
  • frontend/src/router/index.tsx -- Existing route structure
  • frontend/src/components/layouts/user-layout.tsx -- Nav items include /user/reports path
  • frontend/src/components/layouts/admin-layout.tsx -- Sidebar items include /admin/reports path

• npm registry -- Verified versions: recharts 3.8.0, file-saver 2.0.5, @types/file-saver 2.0.7, jspdf 4.2.1, html2canvas 1.4.1
• pip show openpyxl -- Confirmed version 3.1.5 installed

• Web search for recharts heatmap patterns was unavailable (API errors). Recommendation for CSS grid heatmap over recharts TreeMap is based on training data knowledge of recharts capabilities. The heatmap implementation should be validated during development.

Confidence breakdown:

• Standard stack: HIGH -- All libraries already installed and verified against npm/pip registries
• Architecture: HIGH -- Follows exact patterns from existing codebase (scoring_service, report_service, radar-chart)
• Pitfalls: HIGH -- Identified from direct codebase inspection (timezone handling, SQLite batch mode, auth patterns)
• Export: MEDIUM -- openpyxl server-side export is standard; PDF via print CSS is established but limited in formatting control
• Heatmap: MEDIUM -- CSS grid recommendation based on training data; recharts TreeMap is not ideal but could work

Research date: 2026-03-25 Valid until: 2026-04-25 (stable domain, no fast-moving dependencies)

Phase Goal: MRs can track their improvement over time via a personal dashboard, and admins can view organization-level analytics with export capabilities Verified: 2026-03-26T03:15:00Z Status: passed Re-verification: No -- initial verification

Score: 5/5 truths verified

Assessment: The mock data in admin pages is limited to supplementary visualizations (score distribution, individual performer rankings, training activity heatmap on the dashboard; and detailed chart sections on the reports page). All core analytics features required by ANLYT-01 through ANLYT-05 use live data. The stat cards, BU comparison, skill gap heatmap, and completion rate -- which are the org-level analytics specified in ANLYT-03 -- are all wired to live useOrgAnalytics data. The mock sections are aesthetic enhancements that do not block any requirement.

Test: Navigate to /user/dashboard as a user with scored sessions Expected: 4 stat cards show real numbers (not "--" or 0), recent sessions list is populated, skill radar chart renders with dimension scores, recommended scenario shows a real scenario name Why human: Visual rendering, chart correctness, and layout require browser interaction

Test: Navigate to /admin/dashboard as admin after running seed_data.py Expected: Stat cards show total users, active users, total sessions, avg score from seeded data; BU comparison bar chart shows Oncology/Hematology/Solid Tumor; skill gap heatmap shows color-coded dimension scores per BU Why human: Chart visual correctness and color coding require visual inspection

Test: Click "Export Excel" on user dashboard and "Export Full Report" on admin reports page Expected: Browser downloads .xlsx files; user export has session history sheet; admin export has Overview, BU Comparison, and Skill Gaps sheets Why human: File download behavior and Excel file content require browser interaction

Test: Click "Print Report" on user reports page Expected: Browser print dialog opens; preview shows content without navigation sidebar; output is suitable for PDF saving Why human: Print CSS rendering and dialog behavior require browser interaction

No blocking gaps found. All 5 ROADMAP success criteria are satisfied. All 7 requirement IDs (UI-04, UI-06, ANLYT-01 through ANLYT-05) are covered with implementation evidence.

Warnings (non-blocking):

• Admin dashboard has supplementary mock data (score distribution, top performers, needs attention, training activity heatmap) that are not required by any requirement. These are UI polish items.
• Admin reports page has chart sections using mock data (group performance, score trends, completion rates, skill gap table). The summary stats section is live. Chart wiring is documented as out-of-scope per 04-06-SUMMARY.

These warnings do not affect goal achievement since all ANLYT requirements are satisfied by the live-data sections.

Verified: 2026-03-26T03:15:00Z Verifier: Claude (gsd-verifier)