Channels Feature — Implementation Plan

Overview

The Channels feature lets tenants connect 3rd-party platforms (Facebook, LinkedIn, Google Ads, etc.) via OAuth, Basic auth, or API key. Connected channels power social post publishing, SEO data ingestion, analytics reporting, and billing (ZohoBooks).

This document is the authoritative implementation spec. Build in order — each phase depends on the previous one.

Table of Contents

1. Database Schema
2. Provider Library — @leadmetrics/provider-channels
3. API Service Layer
4. API Routes
5. Dashboard UI
6. Environment Variables
7. Seeding
8. Testing Requirements
9. Build Order

1. Database Schema

Two tables in packages/db/prisma/schema.prisma.

1.1 ChannelMaster — static catalogue

Seeded once. Describes every platform the system supports. Never written by user actions.

model ChannelMaster {
  id                 String   @id @default(cuid())
  type               String   @unique  // "Facebook" | "LinkedIn" | "Google Ads" | ...
  name               String            // display name
  description        String            // one-line subtitle shown in UI
  iconKey            String            // key used by UI to pick the brand icon component
  categories         String[]          // ["Social Media"] | ["SEO"] | etc.
  authenticationType String            // "None" | "OAuth" | "Basic" | "ApiKey"
  requiresUrl        Boolean  @default(false)  // true for Website / LandingPage / WordPress
  isActive           Boolean  @default(true)   // false = hide from UI without deleting
 
  connectedChannels  ConnectedChannel[]
 
  @@map("channel_master")
}

Seed rows — Active channels (April 2026)

Deactivated channels (isActive: false — hidden from UI)

The seedChannelMaster() function auto-deactivates any type no longer in CHANNEL_CATALOGUE via an updateMany call.

1.2 ConnectedChannel — tenant instances

One row per connected channel per tenant. Stores tokens, sub-channel selection, history.

model ConnectedChannel {
  id                 String    @id @default(cuid())
  title              String                          // user-given name e.g. "Acme Facebook Page"
  url                String?                         // for Website / LandingPage / WordPress
  type               String                          // denormalised from ChannelMaster.type
  authenticationType String                          // denormalised from ChannelMaster.authenticationType
  isConnected        Boolean   @default(false)
  lastConnectedOn    DateTime?
 
  // Encrypted JSON fields (use @leadmetrics/crypto encrypt/decrypt)
  // apiKeyInfo   stored as encrypted JSON string: { key: string }
  // basicInfo    stored as encrypted JSON string: { userName: string; password: string }
  // tokenInfo    stored as encrypted JSON string: { accessToken; refreshToken?; expireOn?; scope?; secret? }
  apiKeyInfo         String?   @db.Text             // encrypted
  basicInfo          String?   @db.Text             // encrypted
  tokenInfo          String?   @db.Text             // encrypted
 
  // Plain JSON fields (not sensitive)
  subChannelInfo     Json?     // { id; title; parentId?; tokenInfo?: { accessToken } }
  userInfo           Json?     // { id; name? }
  connectionHistory  Json[]    // [{ operation; executedById; executedByName?; executedOn; message }]
 
  channelMasterId    String
  channelMaster      ChannelMaster @relation(fields: [channelMasterId], references: [id])
 
  tenantId           String
  tenant             Tenant    @relation(fields: [tenantId], references: [id])
 
  createdByUserId    String    // User.id who initiated the connection
  createdAt          DateTime  @default(now())
  updatedAt          DateTime  @updatedAt
 
  @@index([tenantId])
  @@index([tenantId, type])
  @@map("connected_channel")
}

Token encryption rule: apiKeyInfo, basicInfo, and tokenInfo are stored as encrypted strings using encrypt(JSON.stringify(value)) from @leadmetrics/crypto. Always decrypt before use. subChannelInfo and userInfo are not encrypted (no secrets).

Add to Tenant model:

connectedChannels  ConnectedChannel[]

Run pnpm --filter @leadmetrics/db db:generate after schema changes.

2. Provider Libraries

Instead of one monolithic package, providers are split by platform company into 5 separate workspace packages. Each is fully independent with its own package.json, tsconfig.json, vitest.config.ts, src/index.ts, src/types.ts, service files, and src/__tests__/.

Each package re-exports all classes and types from its src/index.ts.

All five packages share the same patterns:

• OAuthInfo and ChannelApiError defined locally per package (intentional duplication)
• Google providers share an abstract GoogleServiceBase class (packages/providers/google/src/base.ts)
• Zoho — OAuthInfo includes an extra accountsServer? field; passed to authorizeCallback and refreshToken

2.1 File structure (example — google)

packages/providers/google/
  package.json           ← name: "@leadmetrics/provider-google"
  tsconfig.json
  vitest.config.ts
  src/
    index.ts
    types.ts             ← OAuthInfo, ChannelApiError, *Config, *DTO interfaces
    base.ts              ← abstract GoogleServiceBase (authorize / authorizeCallback / refreshToken)
    gmail.ts
    google-ads.ts
    google-analytics.ts
    google-business-profile.ts
    google-search-console.ts
    __tests__/
      base.test.ts
      google-ads.test.ts
      google-search-console.test.ts

2.2 types.ts (per package)

export interface OAuthInfo {
  accessToken: string;
  refreshToken?: string;
  expiresInSeconds?: number | null;
  expireOn?: Date | null;
  scope?: string;
  userId?: string;
  username?: string;
  accessTokenSecret?: string;   // Twitter OAuth 1.0a only
}
 
export interface TokenInfo {
  accessToken: string;
  refreshToken?: string;
  expireOn?: Date | null;
  scope?: string;
  secret?: string;              // Twitter OAuth 1.0a token secret
}
 
export interface SubChannelInfo {
  id: string;
  title: string;
  parentId?: string;
  tokenInfo?: { accessToken: string };
}
 
export interface UserInfo {
  id: string;
  name?: string;
}
 
export class ChannelApiError extends Error {
  statusCode: number;
  constructor(message: string, statusCode: number) {
    super(message);
    this.statusCode = statusCode;
  }
}
 
export interface ChannelMasterRow {
  type: string;
  name: string;
  description: string;
  iconKey: string;
  categories: string[];
  authenticationType: 'None' | 'OAuth' | 'Basic' | 'ApiKey';
  requiresUrl: boolean;
}

2.3 catalogue.ts (inlined in router and seed)

The CHANNEL_CATALOGUE array (15 platform rows matching section 1.1) is defined inline in both apps/api/src/routers/channels.ts (for runtime use) and apps/api/src/seed.ts (for seeding). There is no separate catalogue package.

2.4 Provider classes

Each class accepts its credentials via constructor (no global singletons). The route instantiates the service: new FacebookService({ appId, appSecret }).

Every provider exposes only the methods needed by the route layer. No internal caching, no DI container. Return types are exactly OAuthInfo, sub-channel arrays, or named DTOs.

Methods exposed per provider (what routes call):

All data methods (Google Ads campaign queries, LinkedIn statistics, etc.) are also implemented in the provider classes but are NOT exposed through the channels routes — they are called by reporting workers/agents directly.

3. API Service Layer

File: apps/api/src/services/connected-channel.service.ts

Thin DB-access layer. Routes call this; never call db.* directly in route files.

// Key methods:
 
searchChannels(tenantId, filter: { state; categories }, sortBy): Promise<ConnectedChannelRow[]>
getChannel(id): Promise<ConnectedChannelRow | null>
getChannelsByType(tenantId, type): Promise<ConnectedChannelRow[]>
getSocialChannels(tenantId): Promise<ConnectedChannelRow[]>
createChannel(data, userId, tenantId): Promise<ConnectedChannelRow>
updateChannel(id, data, userId, tenantId): Promise<ConnectedChannelRow>
updateConnectionState(id, isConnected, tokenInfo, userInfo?, userId): Promise<void>
addConnectionHistory(id, operation, userId, userName, message): Promise<void>
disconnectChannel(id, userId, userName, reason): Promise<ConnectedChannelRow>
deleteChannel(id, userId, tenantId): Promise<ConnectedChannelRow>
renameChannel(id, newTitle, userId, tenantId): Promise<ConnectedChannelRow>

Encryption/decryption of tokenInfo, apiKeyInfo, basicInfo happens here — NOT in routes or providers. Use encrypt(JSON.stringify(value)) on write, JSON.parse(decrypt(stored)) on read.

ConnectedChannelRow is the Prisma type extended with decrypted fields parsed from JSON strings.

4. API Routes

4.1 Register in apps/api/src/app.ts

await fastify.register(channelsRouter,       { prefix: '/tenant/v1/channels' });
await fastify.register(channelConnectRouter, { prefix: '/tenant/v1/channel-connect' });

4.2 apps/api/src/routers/channels.ts

Prefix: /tenant/v1/channels

All routes require JWT auth (requireTenantUser).

Route bodies/shapes:

// POST /  — create
interface AddChannelDTO {
  title: string;
  url?: string;       // required if requiresUrl
  type: string;
  apiKey?: string;    // required if authenticationType === 'ApiKey'
  userName?: string;  // required if authenticationType === 'Basic'
  password?: string;  // required if authenticationType === 'Basic'
}
 
// PUT /:id/rename
interface RenameChannelDTO {
  channelName: string;
}

Response — never expose raw tokens. Strip tokenInfo, apiKeyInfo, basicInfo before sending to client. The route returns a safe view with isConnected, lastConnectedOn, subChannelInfo.title, userInfo, connectionHistory.

4.3 apps/api/src/routers/channel-connect.ts

Prefix: /tenant/v1/channel-connect

OAuth popup flow. Callback and page-select routes are anonymous (no JWT) because the browser arrives from the OAuth provider redirect.

GET  /channel/close                      [anon]  → HTML that calls window.close()

Per-provider pattern (8 OAuth providers):

GET  /{provider}/connect?id={channelId}  [auth]  → calls ProviderService.authorize(callbackUrl, channelId)
                                                    returns { url: string } (frontend opens this in popup)
GET  /{provider}/callback                [anon]  → provider returns ?code=...&state=channelId
                                                    calls ProviderService.authorizeCallback(code, callbackUrl)
                                                    saves tokenInfo via service layer
                                                    if no subChannelInfo → redirect to /{provider}/page/select?state=channelId
                                                    else → redirect to /channel/close
GET  /{provider}/page/select?state=id    [anon]  → fetch sub-channels from provider, return JSON list
POST /{provider}/page/select             [anon]  → body: { state: channelId, selectedId: string }
                                                    saves subChannelInfo, redirect to /channel/close

Providers covered:

Callback URL pattern: {BASE_URL}/tenant/v1/channel-connect/{provider}/callback (BASE_URL is the public API base URL from process.env.API_BASE_URL)

Google Search Console post-connect: after saving subChannelInfo, enqueue BullMQ jobs:

• gsc-keywords-fetch-v2 with payload channelId
• gsc-pages-stats-v2 with payload channelId
• gsc-keywords-stats-v2 with payload channelId

Google Ads post-connect: after saving subChannelInfo, enqueue:

• google-ads-keywords-metrics-v2 with payload tenantId

Route files must not contain OAuth URL construction, token exchange HTTP calls, or JSON payload building. All of that lives in the provider class. Routes do: call provider method → call service layer method → redirect or return.

5. Dashboard UI

5.1 Files to change

apps/dashboard/src/app/(dashboard)/channels/
  page.tsx                                  ← requireAuth; SSR initial data; passes apiUrl + token
  ChannelsClient.tsx                        ← full client component (tabs, cards, dialogs)
  ConnectDialog.tsx                         ← connect/reconnect modal with OAuth popup polling
  ChannelCard.tsx                           ← AvailableChannelCard + ConnectedChannelCard variants
  [id]/page.tsx                             ← SSR: fetch basic channel info, route to detail component
  [id]/FacebookChannelDetail.tsx            ← Facebook analytics + posts tabs
  [id]/InstagramChannelDetail.tsx           ← Instagram analytics + posts tabs
  [id]/LinkedInChannelDetail.tsx            ← LinkedIn follower/impression/click stats
  [id]/GoogleSearchConsoleChannelDetail.tsx ← GSC clicks/impressions/CTR/position
  [id]/GoogleBusinessProfileChannelDetail.tsx ← GBP views/searches/photos/keywords
  [id]/GoogleAnalyticsChannelDetail.tsx     ← GA sessions/users + 6 extended tabs
  [id]/BingWebmasterToolsChannelDetail.tsx  ← Bing clicks/impressions/CTR/position
  [id]/NotImplementedDetail.tsx             ← fallback for unsupported channel types

5.2 Data flow

ChannelsClient (client component)
  ├── on mount: GET /tenant/v1/channels/status  → { existing, allChannels, yetToConnect }
  ├── tab=connected     → render existing[]   with ChannelCard (connected variant)
  ├── tab=not_connected → render yetToConnect[] with ChannelCard (connect variant)
  └── tab=all           → render allChannels[] with ChannelCard (mixed)

5.3 ChannelCard — available (not connected)

• Channel icon (from iconKey using brand icon map)
• Channel name + description
• Connect button → opens ConnectDialog

5.4 ConnectDialog

Modal with channel icon, title, description.

Step 1 — Channel Name input (all types):

[Channel icon]
Connect to {channelName}
Connect to your {channelName} to integrate it to our platform and make all the features centralized.

Channel Name:  [________________]

[Cancel]  [Connect →]

On Connect click:

1. Call POST /tenant/v1/channels with { title, type, url?, userName?, password? }
2. Response: { id: channelId, authenticationType }

• If authenticationType === 'None' → channel auto-connected, close dialog, refresh list
• If authenticationType === 'Basic' (WordPress) → show URL + username + password form before step 1 POST
• If authenticationType === 'OAuth' → go to Step 2

Step 2 — OAuth popup (OAuth types only):

1. Fetch GET /tenant/v1/channel-connect/{provider}/connect?id={channelId} → { url }
2. Open url in window.open('', '_blank', 'width=600,height=700')
3. Poll popup.closed every 500ms
4. On popup close → call GET /tenant/v1/channels/{channelId} to check if now connected
5. If connected → close dialog, refresh channel list, show success toast
6. If not connected (user cancelled) → show error state in dialog

5.5 ChannelCard — connected

• Channel icon + “Active” badge (green) or “Inactive” badge (red)
• Channel name (user title) + sub-channel name if applicable
• “Connected on {date}”
• 3-dot menu → Disconnect / Rename / Delete
  • Disconnect: PUT /tenant/v1/channels/:id/disconnect
  • Rename: inline input or small dialog → PUT /tenant/v1/channels/:id/rename
  • Delete: confirmation dialog → DELETE /tenant/v1/channels/:id

5.6 Tab counts

After data load, update tab labels:

• Connected ({existing.length})
• Yet to Connect ({yetToConnect.length})
• All Channels ({allChannels.length})

6. Environment Variables

Add to .env (all apps/api):

# API public base URL (used to build OAuth callback URLs)
API_BASE_URL=https://api.leadmetrics.ai/
 
# Google (separate OAuth app per product)
GMAIL_APP_ID=
GMAIL_APP_SECRET=
GOOGLE_ADS_APP_ID=
GOOGLE_ADS_APP_SECRET=
GOOGLE_ADS_DEVELOPER_TOKEN=
GOOGLE_ANALYTICS_APP_ID=
GOOGLE_ANALYTICS_APP_SECRET=
GOOGLE_BUSINESS_PROFILE_APP_ID=
GOOGLE_BUSINESS_PROFILE_APP_SECRET=
GOOGLE_SEARCH_CONSOLE_APP_ID=
GOOGLE_SEARCH_CONSOLE_APP_SECRET=
 
# Social
FACEBOOK_APP_ID=
FACEBOOK_APP_SECRET=
INSTAGRAM_APP_ID=
INSTAGRAM_APP_SECRET=
LINKEDIN_APP_ID=
LINKEDIN_APP_SECRET=
TWITTER_API_KEY=
TWITTER_API_SECRET=
SPOTIFY_CLIENT_ID=
SPOTIFY_CLIENT_SECRET=
 
# Zoho / Microsoft
ZOHO_APP_ID=
ZOHO_APP_SECRET=
BING_WMT_APP_ID=
BING_WMT_APP_SECRET=

Token encryption uses the existing PROVIDER_CONFIG_ENCRYPTION_KEY from @leadmetrics/crypto.

7. Seeding

Add channel master seeding to apps/api/src/seed.ts (or a dedicated seed-channels.ts).

// CHANNEL_CATALOGUE is defined inline in seed.ts (no separate package)
for (const ch of CHANNEL_CATALOGUE) {
  await db.channelMaster.upsert({
    where: { type: ch.type },
    update: { name: ch.name, description: ch.description, iconKey: ch.iconKey,
               categories: ch.categories, authenticationType: ch.authenticationType,
               requiresUrl: ch.requiresUrl },
    create: { ...ch },
  });
}

Idempotent — safe to re-run.

8. Testing Requirements

Unit tests — packages/providers/{google,meta,linkedin,microsoft,zoho}/src/__tests__/

One test file per provider per package. Use vi.stubGlobal("fetch", mockFetch) via vi.hoisted().

Cover per provider:

• authorize() returns correctly constructed URL with all required params
• authorizeCallback() parses token response into OAuthInfo
• refreshToken() returns new OAuthInfo
• ChannelApiError is thrown on non-2xx response

Integration tests — apps/api/src/__tests__/integration/channels.test.ts

Cover (using real test DB, not mocks):

• POST /tenant/v1/channels — creates channel, returns id; validates missing fields; auto-connects None type
• GET /tenant/v1/channels/status — returns all three lists correctly
• GET /tenant/v1/channels/lookups/social — returns only connected social channels
• PUT /tenant/v1/channels/:id/disconnect — sets isConnected=false, appends history
• PUT /tenant/v1/channels/:id/rename — updates title
• DELETE /tenant/v1/channels/:id — deletes; blocks on pending posts
• GET /tenant/v1/channel-connect/:provider/connect — returns OAuth URL (mock provider service)
• Verify tokens are never returned in API responses

Integration tests — apps/api/src/__tests__/integration/channels-analytics.test.ts

30 tests covering analytics routes for all 7 supported channel types. Provider packages are mocked with vi.mock() inline factories so no real API calls are made. Channels are seeded with updateConnectionState() so tokens are properly encrypted in the test DB.

Cover:

• GET /tenant/v1/channels/:id/analytics (200) for each of: Facebook, Instagram, LinkedIn, Google Search Console, Google Business Profile, Google Analytics, Bing Webmaster Tools
• Response shape validation (stat card fields, topPages, topKeywords arrays)
• GET /tenant/v1/channels/:id/analytics/tab for all 6 GA tab types (organic/sources/country/city/pagewise/audience)
• GET /tenant/v1/channels/:id/facebook/posts and /:id/instagram/posts
• 404 for non-existent channel ID
• Verify tokenInfo and apiKeyInfo are never present in any response body

Test DB setup note: leadmetrics_api_test must have the Prisma schema pushed before first run:

cd packages/db
DATABASE_URL="postgresql://leadmetrics:leadmetrics@127.0.0.1:5434/leadmetrics_api_test" npx prisma db push --skip-generate

apps/api/src/__tests__/setup.ts sets PROVIDER_CONFIG_ENCRYPTION_KEY so token encryption works in the test environment.

Playwright E2E tests — apps/dashboard/tests/e2e/channels.spec.ts

• Navigate to /channels
• Verify four tabs: Connected, Disconnected, Yet to Connect, All Channels (with correct counts)
• Click Connect on “Website” channel (None-type, no OAuth) → fill name + URL → auto-connects → appears in Connected tab
• Click Disconnect on connected channel → moves to Disconnected tab
• Click ⋯ → Rename → new name appears on card
• Click ⋯ → Delete → confirmation → card removed

Playwright E2E tests — apps/dashboard/tests/e2e/channel-detail.spec.ts

Seeds disconnected channels for all 7 supported types and a connected Twitter/X channel directly in the leadmetrics_test database. Tests:

• 404: navigating to a non-existent channel ID does not redirect to /login
• Not Yet Implemented modal: clicking a connected Twitter/X card (unsupported type) shows the modal and dismisses on “Got it”
• Detail page rendering for each supported type:
  • LinkedIn, GSC, GBP, GA, Bing: channel title visible + disconnected warning banner present
  • Facebook, Instagram: channel title visible + Overview and Posts tab buttons present
  • Back button navigates to /channels
• Security: tokenInfo and apiKeyInfo strings are not present in the page HTML

9. Social Publishing Pipeline (Live — April 2026)

After a client approves a social post (client_approved), it is automatically published to the connected platform via BullMQ.

Publisher Worker

File: packages/agents/src/workers/social-publisher.worker.ts
Queue: agent__social-publisher
Enqueue: enqueueSocialPublisher() from packages/queue
Dedup key: social-publisher__{socialPostId}

Supports: Facebook, Instagram, LinkedIn, Google Business Profile.
Twitter/X and TikTok are deferred — see docs/missing-incomplete-features/.

Token Resolution per Platform

New SocialPost Fields

platformPostId   String?
platformPostUrl  String?
publishedAt      DateTime?
publishError     String?   @db.Text
publishAttempts  Int       @default(0)

Extended Status States

client_approved → publishing → published | publish_failed

Plan-Outdated Banner

POST /tenant/v1/deliverable-plan/refresh — re-queries connected channels and re-enqueues the deliverable planner. Triggered from the Channels page banner when a new channel is connected after the plan was created.

10. Build Order

Follow exactly — each step gates the next.