Blend IQ

Overview

Blend IQ integrates Microsoft Power BI reports into Blend with hierarchical folder organization and HubSpot-based access control. Uses service principal authentication for Power BI connections.

Platform Routes

Main App Routes

• /app/:tenant/apps/blend-iq - Connections dashboard & folder browser
• /app/:tenant/apps/blend-iq/:reportId - Embedded report viewer
• /app/:tenant/apps/blend-iq/reports - All reports list
• /app/:tenant/apps/blend-iq/settings - Report/folder configuration
• /app/:tenant/apps/blend-iq/settings/add-report - Add report wizard
• /app/:tenant/apps/blend-iq/settings/test-user - Test user access (admin only)

Admin Routes

• /admin/accounts/apps/:tenant/:app/logs - App-specific logs

External App Routes

The standalone TanStack Start app (blendx-iq) provides:

Public Routes

• /login - Authentication (HubSpot OAuth, OTP, Password)
• /auth/hubspot/callback - OAuth callback
• /logout - Logout

Protected Routes

• / - Folder/report browser
• /report/:reportId - Power BI report viewer
• /profile - User profile
• /admin - Admin dashboard

Embed Routes (Auth Required)

• /embed - Embedded folder browser
• /embed/report/:reportId - Embedded report viewer

API Endpoints

All endpoints require X-API-KEY header for authentication.

Data Endpoints

Get App Info

GET /api/app/blend-iq/info
Headers: X-API-KEY

Returns tenant logo, theme, and login configuration.

Get Folders & Reports

POST /api/app/blend-iq/reports
Headers: X-API-KEY
Body: { "email": "user@example.com" }

Returns hierarchical folder structure with accessible reports for user (respects HubSpot access control).

Response:

{
  "user": { "id": "...", "email": "...", "firstName": "...", "lastName": "..." },
  "userTeams": [{ "id": "...", "name": "..." }],
  "folders": [
    {
      "id": "...",
      "name": "...",
      "isPublic": false,
      "children": [],
      "reports": [{ "id": "...", "reportName": "...", "reportId": "...", "workspaceId": "..." }]
    }
  ],
  "reports": []
}

Get Report Embed Token

POST /api/app/blend-iq/report/:reportId/embed?email={email}
Headers: X-API-KEY

Returns Power BI embed token and configuration.

Response:

{
  "success": true,
  "embedUrl": "https://app.powerbi.com/reportEmbed?...",
  "accessToken": "eyJ0eXAi...",
  "reportId": "...",
  "reportType": "PowerBIReport"
}

Authentication Endpoints

Request OTP

POST /api/app/blend-iq/auth/request-otp
Headers: X-API-KEY
Body: { "email": "user@example.com" }

Generates 6-digit OTP (5-minute expiration). Supports both database users and HubSpot users.

Response:

{
  "success": true,
  "code": "123456",
  "expiresAt": "2025-01-31T12:00:00Z"
}

User Lookup:

• First checks database user with tenant membership
• Falls back to HubSpot if not found in database or no tenant membership
• Returns 404 if user not found in either system

Verify OTP

POST /api/app/blend-iq/auth/verify-otp
Headers: X-API-KEY
Body: { "email": "user@example.com", "code": "123456" }

Verifies OTP and returns user info. Supports both database users and HubSpot users.

Response:

{
  "success": true,
  "user": {
    "id": "...",
    "email": "...",
    "firstName": "...",
    "lastName": "...",
    "source": "database"  // "database" or "hubspot"
  }
}

Authentication Priority:

1. Database user with tenant membership (returns source: "database")
2. HubSpot user (returns source: "hubspot")
3. User not found (404 error)

Verify Password

POST /api/app/blend-iq/auth/verify-password
Headers: X-API-KEY
Body: { "email": "user@example.com", "password": "secret123" }

Verifies password and returns user info. Only supports database users (passwords not stored for HubSpot-only users).

Response:

{
  "success": true,
  "user": {
    "id": "...",
    "email": "...",
    "firstName": "...",
    "lastName": "...",
    "source": "database"  // Always "database" for password auth
  }
}

Database Models

Core Models

DataConnection (type: "power-bi"):

{
  id: string
  tenantId: string
  type: "power-bi"
  name: string
  config: {
    clientId: string
    tenantId: string
    clientSecret: string
  }
  isActive: boolean
}

BlendIQ_Report:

{
  id: string
  tenantId: string
  appId: string
  connectionId: string
  reportId: string          // Power BI report ID
  reportName: string
  workspaceId: string
  reportType: string        // "PowerBIReport" | "PaginatedReport"
  embedUrl: string
  webUrl: string
  description?: string
  thumbnailUrl?: string
  folderId?: string
  order: number
  isActive: boolean
  isPublic: boolean
}

BlendIQ_Folder:

{
  id: string
  tenantId: string
  appId: string
  name: string
  description?: string
  parentFolderId?: string
  order: number
  isPublic: boolean
}

Access Control Models

BlendIQ_FolderAccess:

{
  id: string
  tenantId: string
  folderId: string
  type: "team" | "user"
  hubspotTeamId?: string    // populated when type="team"
  hubspotUserId?: string    // populated when type="user"
  name: string              // snapshot: team name or user email
  description?: string      // snapshot: team description or user full name
}

BlendIQ_ReportAccess (same structure as FolderAccess):

{
  id: string
  tenantId: string
  reportId: string
  type: "team" | "user"
  hubspotTeamId?: string
  hubspotUserId?: string
  name: string
  description?: string
}

Power BI Integration

Service Principal Setup

Azure App Registration requires:

• Application (Client) ID
• Directory (Tenant) ID
• Client Secret
• API Permissions: Content.Create, Dashboard.Read.All, Dataset.Read.All, Report.ReadWrite.All, Workspace.ReadWrite.All
• Admin consent granted

Embedding

Uses Power BI JavaScript Client v2.23.1 with AAD token authentication (tokenType: 0).

Requirements:

• Premium capacity or Power BI Embedded (A SKU) required for embedding
• Service principal must be added to workspace as Member or Admin

Error Handling:

• 403: Premium capacity required → fallback to "Open in Power BI" button
• Gateway errors: On-premises data sources → fallback to web URL
• Personal workspace: Special API endpoint handling

Access Control Flow

1. Permission Resolution (priority order):

   • isPublic = true → Everyone has access
   • HubSpot team membership → Check BlendIQ_FolderAccess / BlendIQ_ReportAccess with type="team"
   • Direct user assignment → Check with type="user"
   • No match → No access

2. Inheritance:

   • New folders inherit parent folder permissions + isPublic value
   • New reports inherit parent folder permissions + isPublic value

3. Cascading Updates:

   • Folder permission changes cascade to all descendant folders and reports
   • isPublic changes cascade down entire tree

HubSpot Integration

Data Fetching

GET /app/:tenant/apps/blend-iq/api/hubspot-data?type=teams&connectionId=...
GET /app/:tenant/apps/blend-iq/api/hubspot-data?type=users&connectionId=...

Returns teams or users from HubSpot for access control assignment.

Access Resolution Query Pattern

// Example: Get accessible folders for user
const folders = await db.blendIQ_Folder.findMany({
  where: {
    tenantId,
    appId,
    OR: [
      { isPublic: true },
      {
        access: {
          some: {
            OR: [
              { type: "user", hubspotUserId },
              { type: "team", hubspotTeamId: { in: userTeamIds } }
            ]
          }
        }
      }
    ]
  }
});

API Documentation

Interactive Swagger documentation available at:

• /api/app/blend-iq/docs - Swagger UI
• /api/app/blend-iq/docs/json - OpenAPI spec

External App Connection

The standalone TanStack Start app connects via:

1. Authentication: All auth endpoints (/api/app/blend-iq/auth/*)
2. Data Fetching: /api/app/blend-iq/reports for folder/report tree
3. Embedding: /api/app/blend-iq/report/:reportId/embed for Power BI tokens
4. Configuration: /api/app/blend-iq/info for tenant settings

Environment Variables (External App):

API_URL=https://blendx.ai         # Blend platform URL
API_KEY=your-tenant-api-key       # Tenant API key
HUBSPOT_CLIENT_ID=...             # Optional: HubSpot OAuth
HUBSPOT_CLIENT_SECRET=...
SESSION_SECRET=...
ENCRYPTION_KEY=...