PCT License Dashboard - MVP Phase 1

Internal multi-tenant reporting tool for Microsoft Graph data (Entra ID, Exchange, SharePoint, Teams). Read-only MVP focused on inventory and compliance reporting.

Features (Phase 1)

✅ Entra ID Reporting

• User inventory with enabled/disabled status
• Group membership mapping
• Privileged role assignments
• MFA registration coverage

✅ License Reporting

• SKU utilization (assigned vs. available)
• Per-user license details
• Shortage/surplus metrics

✅ Data Ingestion

• Nightly batch pulls via Microsoft Graph (configurable schedule)
• Cached snapshots for fast dashboard loads
• Per-tenant data isolation (fail-closed)

✅ Multi-Tenant Architecture

• Single MSP service manages multiple customer tenants
• Admin-consent workflow for tenant onboarding
• Delegated (per-user) auth by default for audit attribution

✅ API-First Design

• REST endpoints for reports
• LLM-compatible JSON responses
• Hybrid caching (snapshots + optional fresh pulls)

✅ TUI Client

• Interactive terminal UI for MSP operators
• Browse reports, drill into details
• Calls same REST API as LLM clients

Architecture

┌─────────────────────────────────────────────────────────────┐
│                      External Systems                        │
├─────────────────────────────────────────────────────────────┤
│                    Microsoft Graph API                       │
│            (Entra ID, Exchange, SharePoint, Teams)          │
└─────────────────────┬───────────────────────────────────────┘
                      │ (Delegated or App-Only Auth)
                      ↓
┌─────────────────────────────────────────────────────────────┐
│                   Backend Service (Node.js)                  │
├─────────────────────────────────────────────────────────────┤
│  ┌──────────────────┐  ┌──────────────────────────────────┐ │
│  │ GraphClient      │  │ REST API Endpoints               │ │
│  │ - Tenant isolate │  │ /api/tenants/:id/reports/...     │ │
│  │ - Throttling     │  │ - Auth middleware (fail-closed)  │ │
│  │ - Audit logs     │  │ - Tenant isolation checks        │ │
│  └──────────────────┘  │ - Hybrid caching                 │ │
│  ┌──────────────────┐  └──────────────────────────────────┘ │
│  │ Scheduled Jobs   │  ┌──────────────────────────────────┐ │
│  │ (node-cron)      │  │ Normalizers                      │ │
│  │ - Nightly pulls  │  │ Graph → Domain models            │ │
│  │ - Store snapshot │  │ Calculation helpers              │ │
│  └──────────────────┘  └──────────────────────────────────┘ │
│  ┌──────────────────────────────────────────────────────────┐│
│  │ Postgres Database (Snapshots + Audit)                    ││
│  │ - user_snapshots, group_snapshots, etc. (JSONB)          ││
│  │ - audit_logs (all Graph calls + data access)             ││
│  │ - Tenant isolation via FK constraints                    ││
│  └──────────────────────────────────────────────────────────┘│
└─────────────────────────────────────────────────────────────┘
         ↑                           ↑
    REST API                    REST API
┌─────────────────┐          ┌──────────────────┐
│  LLM Interface  │          │  TUI (Ink)       │
│  Natural lang   │          │  Interactive CLI │
│  → JSON reports │          │  Menu-driven     │
└─────────────────┘          └──────────────────┘

Security Model

Tenant Isolation (Fail-Closed)

Every GraphClient method validates tenant context before making API calls:

// FAIL-CLOSED: This throws immediately if tenant IDs don't match
private assertTenantContext(requestedTenantId: string): void {
  if (requestedTenantId !== this.customerTenantId) {
    throw new TenantIsolationError(...);
  }
}

Authentication Flows

Delegated (Default - Per-User)

• User signs in with credentials
• Access token passed to API (Authorization: Bearer <token>)
• Audit logs record user identity
• Use for: MSP operators, manual queries, compliance audits

App-Only (Service Account)

• Service uses client credentials (clientId + clientSecret)
• API key passed as header (Authorization: ApiKey <key>)
• Audit logs record service account identity
• Use for: Scheduled jobs, LLM background queries

Authorization Levels

1. No access: User lacks API key or valid token → 401
2. Own tenant only: User can query their assigned tenant → 403 for others
3. Read-only: All API endpoints are GET; no writes

Audit Logging

All Graph API calls logged to audit_logs table:

INSERT INTO audit_logs (tenant_id, user_id, action, resource_id, timestamp, result, details)
VALUES ('tenant-a', 'user@example.com', 'GRAPH_GET_USERS', '/users', NOW(), 'success', '{...}')

Data Model

Core Tables (with tenant isolation via FK)

• tenants: Registered customer tenants (admin-consent URLs)
• users: User inventory snapshots
• user_snapshots: Daily user counts + full data (JSONB)
• groups: Group inventory snapshots
• group_snapshots: Daily group data
• licenses: License SKU data
• license_snapshots: Daily license utilization
• auth_method_snapshots: MFA registration coverage
• directory_roles: Privileged role inventory
• role_members: Role assignment data
• audit_logs: All Graph API calls + data access

Normalized Domain Models

// Raw Graph response
{
  id: "user-123",
  userPrincipalName: "john.doe@example.com",
  ...
}

// Normalized domain model
User {
  id: "user-123",
  tenantId: "tenant-a",
  userPrincipalName: "john.doe@example.com",
  accountEnabled: boolean,
  createdDateTime: Date,
  ...
}

Deployment

Prerequisites

• Node.js 18+
• Postgres 12+
• Azure app registration (multi-tenant)
• Customer tenants with admin consent granted

Local Development

# 1. Install dependencies
npm install

# 2. Set up environment
cp .env.example .env
# Edit .env with your Postgres + Azure credentials

# 3. Build
npm run build

# 4. Start backend service
npm start
# Server runs on http://localhost:3000

# 5. In another terminal, start TUI
MSP_API_KEY=default-api-key TENANT_ID=your-tenant-id npm run tui

Docker Deployment (Recommended for Production)

# See Dockerfile template (TODO: add to repo)

Environment Variables

See .env.example for complete list. Critical ones:

• DB_HOST, DB_USER, DB_PASSWORD: Postgres connection
• CLIENT_ID, CLIENT_SECRET: Azure app registration
• MSP_API_KEY: API key for service-to-service auth
• INGEST_CRON: Schedule for nightly pulls (default: 0 2 * * * = 2am UTC)

API Reference

Authentication Headers

# Delegated (user token from OAuth flow)
Authorization: Bearer <access_token>

# App-only (service account)
Authorization: ApiKey <your-msp-api-key>

License Report

GET /api/tenants/:tenantId/reports/licenses?refresh=true

Response:
{
  "source": "graph",  // or "cache"
  "data": {
    "tenantId": "...",
    "reportDate": "2024-01-15T02:00:00Z",
    "licenses": [
      {
        "displayName": "Office 365 E3",
        "totalUnits": 100,
        "consumedUnits": 87,
        "utilization": {
          "percentage": 87,
          "surplus": 13,
          "shortage": 0
        }
      }
    ],
    "summary": {
      "totalLicenses": 5,
      "overallUtilization": 82
    }
  }
}

MFA Report

GET /api/tenants/:tenantId/reports/mfa

Response:
{
  "tenantId": "...",
  "coverage": {
    "percentage": 68,
    "usersWithMFA": 153,
    "usersWithoutMFA": 72
  },
  "methodBreakdown": {
    "email": 120,
    "phone": 95,
    "softwareOath": 42
  }
}

Groups Report

GET /api/tenants/:tenantId/reports/groups

Response:
{
  "groups": [
    {
      "id": "group-001",
      "displayName": "Sales Team",
      "memberCount": 24
    }
  ],
  "summary": {
    "totalGroups": 2,
    "totalMembers": 69
  }
}

Roles Report

GET /api/tenants/:tenantId/reports/roles

Response:
{
  "roles": [
    {
      "displayName": "Global Administrator",
      "memberCount": 3,
      "members": [...]
    }
  ]
}

Users Report

GET /api/tenants/:tenantId/reports/users

Response:
{
  "users": [
    {
      "userPrincipalName": "john.doe@example.com",
      "displayName": "John Doe",
      "accountEnabled": true,
      "lastSignInDateTime": "2024-01-14T10:00:00Z"
    }
  ],
  "summary": {
    "totalUsers": 225,
    "enabledUsers": 220,
    "disabledUsers": 5
  }
}

Testing

Run All Tests

npm test

Security Tests (Tenant Isolation)

npm run test:security
# Verifies:
# - Tenant A cannot query tenant B data
# - Every GraphClient method has tenant context check
# - Database queries include tenant_id filters

Integration Tests

npm run test:integration
# Verifies:
# - End-to-end: API → GraphClient → Postgres
# - Data counts match Graph API
# - Pagination works correctly

Development Roadmap

Phase 1 (MVP - Current)

✅ Entra ID inventory + license reporting ✅ Group membership + MFA posture ✅ Read-only REST API ✅ Nightly snapshots ✅ TUI client

Phase 2 (Q2)

• Exchange Online mailbox reports
• Mailbox size + archive status
• Inactive mailbox detection

Phase 3 (Q3)

• SharePoint/OneDrive storage reports
• Site ownership + external sharing
• Storage utilization trends

Phase 4 (Q4)

• Teams inventory + activity
• Team ownership + member counts
• Channel archive status

Phase 5 (Optional - Write-Enabled)

• Password resets (delegated)
• MFA resets (delegated)
• License assignments (app-only, with approval workflow)
• Group membership changes
• Requires: Elevation approvals, 2FA confirmation, detailed audit trails

Troubleshooting

Database Connection Failed

Error: connect ECONNREFUSED 127.0.0.1:5432

• Verify Postgres is running: pg_isready -h localhost -p 5432
• Check DB_HOST, DB_PORT in .env
• Verify credentials: psql -h localhost -U postgres

Graph API 401 (Unauthorized)

• Access token expired: Get fresh token via OAuth flow
• App-only: Verify CLIENT_ID + CLIENT_SECRET in .env
• Verify customer tenant granted admin consent

Graph API 429 (Throttled)

• Exponential backoff auto-retries up to 3 times
• Check Retry-After header in response
• Reduce query concurrency or batch size

Tenant Isolation Error

• Verify tenantId in URL matches authenticated user's tenant
• Check audit logs: SELECT * FROM audit_logs WHERE result = 'failure'
• Ensure database queries include WHERE tenant_id = ?

Contributing

Code Style

• TypeScript strict mode enabled
• ESLint + Prettier enforced
• No hardcoded secrets (use env vars)
• All public functions must include JSDoc

Tests

• Unit tests for Graph client (mocking)
• Security tests for tenant isolation (critical)
• Integration tests for end-to-end flows
• Minimum 60% coverage required

Pull Request Checklist

• Tests pass: npm test
• Linting passes: npm run lint
• No hardcoded secrets
• Tenant isolation verified
• Audit logging for sensitive ops
• Security review (if auth changes)

License

Internal tool - not for external use. Proprietary MSP software.

Support

For issues or questions:

1. Check .env.example for config examples
2. Review API logs: tail -f logs/api.log
3. Check audit logs: SELECT * FROM audit_logs ORDER BY timestamp DESC LIMIT 50
4. Enable debug logging: LOG_LEVEL=debug