Phase 2: API Expansion

Timeline: Week 3-5
Prerequisites: Phase 1.1-1.2 (agent specs and skills defined)
Objective: Add new endpoints the agents need, and refactor existing ones for tool consumption.

2.1 — New API Endpoints

Tasks

[x] Implement GET /client/:id/context — Client brand voice and context
[x] Implement GET /client/:id/posts — List posts with filtering
[x] Implement GET /post/:id — Get full post details
[x] Implement POST /scrape — URL scraping to Markdown
[x] Implement POST /search_images — Unsplash image search
[x] Implement POST /publish — Unified publishing endpoint
[x] Implement POST /client/:id/media — Media upload handling
[x] Implement GET /client/:id/analytics — Engagement metrics

1. GET /client/:id/context

Returns everything an agent needs to know about a client.

Implementation note: Routes are split into api/src/routes/clients.ts, api/src/routes/posts.ts, and api/src/routes/content.ts — not api/src/index.ts as shown in the snippets below.

api/src/routes/clients.ts:

const ClientContextSchema = Type.Object({
  id: Type.Number()
});

app.get<{ Params: typeof ClientContextSchema.static }>('/client/:id/context', {
  schema: {
    params: ClientContextSchema
  }
}, async (request, reply) => {
  const { id } = request.params;

  // Fetch client data with brand voice rules
  const { data: client, error: clientError } = await supabase
    .from('customer_customer')
    .select(`
      id,
      name,
      description,
      logo_url,
      industries,
      brand_voice:client_brand_voice(rule_type, rule_text),
      recent_analytics:post_analytics(
        platform_post:customer_platform_post(platform),
        metric_type,
        metric_value
      )
    `)
    .eq('id', id)
    .limit(20, { foreignTable: 'post_analytics' })
    .order('recorded_at', { foreignTable: 'post_analytics', ascending: false })
    .single();

  if (clientError || !client) {
    return reply.code(404).send({ error: 'Client not found' });
  }

  // Format for agent consumption
  return {
    client_id: client.id,
    name: client.name,
    description: client.description,
    industries: client.industries || [],
    brand_voice: {
      tone: client.brand_voice?.filter(r => r.rule_type === 'tone').map(r => r.rule_text) || [],
      terminology: client.brand_voice?.filter(r => r.rule_type === 'terminology').map(r => r.rule_text) || [],
      audience: client.brand_voice?.filter(r => r.rule_type === 'audience').map(r => r.rule_text) || [],
      style: client.brand_voice?.filter(r => r.rule_type === 'style').map(r => r.rule_text) || []
    },
    recent_performance: client.recent_analytics || []
  };
});

2. GET /client/:id/posts

List posts with status filtering.

const ListPostsQuerySchema = Type.Object({
  status: Type.Optional(Type.String()), // 'new', 'approved', 'sent'
  platform: Type.Optional(Type.String()),
  limit: Type.Optional(Type.Number({ minimum: 1, maximum: 100 }))
});

app.get<{
  Params: { id: string },
  Querystring: typeof ListPostsQuerySchema.static
}>('/client/:id/posts', {
  schema: {
    querystring: ListPostsQuerySchema
  }
}, async (request, reply) => {
  const { id } = request.params;
  const { status, platform, limit = 20 } = request.query;

  let query = supabase
    .from('customer_posts')
    .select(`
      id,
      title,
      prompt,
      status,
      created_at,
      platforms,
      platform_content:customer_platform_post(
        platform,
        content,
        images
      )
    `)
    .eq('customer_id', parseInt(id))
    .order('created_at', { ascending: false })
    .limit(limit);

  if (status) {
    query = query.eq('status', status);
  }

  const { data: posts, error } = await query;

  if (error) {
    return reply.code(500).send({ error: error.message });
  }

  // Filter by platform if specified
  if (platform && posts) {
    return posts.filter(p => p.platforms?.includes(platform));
  }

  return posts || [];
});

3. POST /scrape

Scrapes a URL and returns clean Markdown.

Install dependencies:

cd api
pnpm add cheerio turndown

Create api/src/utils/scraper.ts:

import { JSDOM } from 'jsdom';
import TurndownService from 'turndown';

const turndownService = new TurndownService({
  headingStyle: 'atx',
  codeBlockStyle: 'fenced'
});

export async function scrapeUrl(url: string): Promise<{
  title: string;
  markdown: string;
  excerpt: string;
}> {
  const response = await fetch(url);
  if (!response.ok) {
    throw new Error(`Failed to fetch URL: ${response.statusText}`);
  }

  const html = await response.text();
  const dom = new JSDOM(html);
  const doc = dom.window.document;

  // Extract title
  const title = doc.querySelector('title')?.textContent || '';

  // Remove script, style, nav, footer
  doc.querySelectorAll('script, style, nav, footer, aside').forEach(el => el.remove());

  // Get main content (try common selectors)
  const mainContent = 
    doc.querySelector('main') ||
    doc.querySelector('article') ||
    doc.querySelector('.content') ||
    doc.querySelector('#content') ||
    doc.body;

  // Convert to Markdown
  const markdown = turndownService.turndown(mainContent.innerHTML);

  // Create excerpt (first 200 chars)
  const excerpt = markdown.substring(0, 200).trim() + '...';

  return { title, markdown, excerpt };
}

Add endpoint in api/src/index.ts:

const ScrapeSchema = Type.Object({
  url: Type.String({ format: 'uri' })
});

app.post<{ Body: typeof ScrapeSchema.static }>('/scrape', {
  schema: {
    body: ScrapeSchema
  }
}, async (request, reply) => {
  try {
    const { url } = request.body;
    const result = await scrapeUrl(url);
    return result;
  } catch (error) {
    request.log.error(error);
    return reply.code(500).send({
      error: `Scraping failed: ${error instanceof Error ? error.message : String(error)}`
    });
  }
});

4. POST /search_images

Extract Unsplash search from createPosts.ts into endpoint.

const SearchImagesSchema = Type.Object({
  query: Type.String(),
  count: Type.Optional(Type.Number({ minimum: 1, maximum: 10 }))
});

app.post<{ Body: typeof SearchImagesSchema.static }>('/search_images', {
  schema: {
    body: SearchImagesSchema
  }
}, async (request, reply) => {
  try {
    const { query, count = 3 } = request.body;
    
    // Import from existing createPosts.ts
    const { searchUnsplashImages } = await import('./agents/createPosts.js');
    
    const images = await searchUnsplashImages(query, count);
    
    return { images };
  } catch (error) {
    request.log.error(error);
    return reply.code(500).send({
      error: `Image search failed: ${error instanceof Error ? error.message : String(error)}`
    });
  }
});

5-8. Additional Endpoints

Implement similar endpoints for:

POST /publish — See section 2.4
POST /client/:id/media — Media upload (multipart/form-data handling)
GET /client/:id/analytics — Aggregate analytics summary
GET /post/:id — Full post details with all platform content

2.2 — New Database Tables

Tasks

[x] Create migration file for new tables
[ ] Run migration in Supabase
[x] Set up indexes for performance
[x] Create Row Level Security policies

Create Migration

Create front-end/supabase/migrations/20260308_phase2_tables.sql:

-- Brand voice rules per client
CREATE TABLE IF NOT EXISTS client_brand_voice (
  id SERIAL PRIMARY KEY,
  customer_id INTEGER REFERENCES customer_customer(id) ON DELETE CASCADE,
  rule_type TEXT NOT NULL CHECK (rule_type IN ('tone', 'terminology', 'audience', 'style')),
  rule_text TEXT NOT NULL,
  source TEXT DEFAULT 'manual' CHECK (source IN ('manual', 'analytics', 'ai_learned')),
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_brand_voice_customer ON client_brand_voice(customer_id);
CREATE INDEX idx_brand_voice_type ON client_brand_voice(customer_id, rule_type);

-- Content assets (uploaded media, scraped content)
CREATE TABLE IF NOT EXISTS client_content_asset (
  id SERIAL PRIMARY KEY,
  customer_id INTEGER REFERENCES customer_customer(id) ON DELETE CASCADE,
  asset_type TEXT NOT NULL CHECK (asset_type IN ('image', 'document', 'scraped_page', 'menu', 'video')),
  url TEXT,
  content TEXT,
  metadata JSONB DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_content_asset_customer ON client_content_asset(customer_id);
CREATE INDEX idx_content_asset_type ON client_content_asset(customer_id, asset_type);

-- Analytics tracking
CREATE TABLE IF NOT EXISTS post_analytics (
  id SERIAL PRIMARY KEY,
  platform_post_id INTEGER REFERENCES customer_platform_post(id) ON DELETE CASCADE,
  metric_type TEXT NOT NULL CHECK (metric_type IN ('open_rate', 'click_rate', 'likes', 'shares', 'comments', 'impressions', 'reach')),
  metric_value NUMERIC NOT NULL,
  recorded_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_post_analytics_platform ON post_analytics(platform_post_id);
CREATE INDEX idx_post_analytics_recorded ON post_analytics(recorded_at DESC);

-- Telegram client mapping
CREATE TABLE IF NOT EXISTS telegram_client_mapping (
  id SERIAL PRIMARY KEY,
  telegram_chat_id TEXT UNIQUE NOT NULL,
  customer_id INTEGER REFERENCES customer_customer(id) ON DELETE CASCADE,
  paired_at TIMESTAMPTZ DEFAULT NOW(),
  is_active BOOLEAN DEFAULT TRUE,
  pairing_code TEXT,
  last_interaction_at TIMESTAMPTZ
);

CREATE INDEX idx_telegram_mapping_chat ON telegram_client_mapping(telegram_chat_id);
CREATE INDEX idx_telegram_mapping_customer ON telegram_client_mapping(customer_id);

-- Event/holiday calendar
CREATE TABLE IF NOT EXISTS marketing_calendar (
  id SERIAL PRIMARY KEY,
  event_name TEXT NOT NULL,
  event_date DATE NOT NULL,
  event_type TEXT CHECK (event_type IN ('holiday', 'industry_event', 'custom', 'awareness_day')),
  industries TEXT[],
  description TEXT,
  is_global BOOLEAN DEFAULT FALSE,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_calendar_date ON marketing_calendar(event_date);
CREATE INDEX idx_calendar_industries ON marketing_calendar USING GIN(industries);

-- Add status column to customer_posts if not exists
DO $$ 
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM information_schema.columns 
    WHERE table_name = 'customer_posts' AND column_name = 'status'
  ) THEN
    ALTER TABLE customer_posts 
    ADD COLUMN status TEXT DEFAULT 'new' 
    CHECK (status IN ('new', 'pending_review', 'approved', 'revision_requested', 'scheduled', 'sent'));
  END IF;
END $$;

-- Row Level Security
ALTER TABLE client_brand_voice ENABLE ROW LEVEL SECURITY;
ALTER TABLE client_content_asset ENABLE ROW LEVEL SECURITY;
ALTER TABLE post_analytics ENABLE ROW LEVEL SECURITY;
ALTER TABLE telegram_client_mapping ENABLE ROW LEVEL SECURITY;

-- Policies (authenticated users can access their own client's data)
CREATE POLICY brand_voice_access ON client_brand_voice
  FOR ALL
  USING (auth.uid() IS NOT NULL);

CREATE POLICY content_asset_access ON client_content_asset
  FOR ALL
  USING (auth.uid() IS NOT NULL);

CREATE POLICY analytics_access ON post_analytics
  FOR SELECT
  USING (auth.uid() IS NOT NULL);

CREATE POLICY telegram_mapping_access ON telegram_client_mapping
  FOR ALL
  USING (auth.uid() IS NOT NULL);

-- Marketing calendar is public read
ALTER TABLE marketing_calendar ENABLE ROW LEVEL SECURITY;
CREATE POLICY calendar_read ON marketing_calendar
  FOR SELECT
  USING (TRUE);

Run Migration

cd front-end
npx supabase db push

Or upload via Supabase Dashboard SQL Editor.

2.3 — Refactor Content Generation for Agent Consumption

Tasks

[x] Separate content generation logic from DB insertion
[x] Return structured JSON from platform generators
[x] Add character count and metadata to responses
[ ] Create SSE endpoint for streaming generation (optional)

Refactor posts.ts

Current getPlatformContent() function in modules/posts.ts needs to return clean JSON:

interface GeneratedContent {
  platform: string;
  content: string;
  images: string[];
  suggested_hashtags?: string[];
  suggested_post_time?: string;
  character_count: number;
  metadata?: Record<string, any>;
}

const getPlatformContent = async (
    platform: Platform,
    customer: any,
    campaign: any,
    prompt: string,
): Promise<GeneratedContent> => {
    const generator = contentMap[platform];
    if (!generator) {
        throw new Error(`Unsupported platform: ${platform}`);
    }

    const result = await generator({
        campaign,
        customer,
        prompt
    });

    return {
        platform,
        content: result.content,
        images: result.images || [],
        character_count: result.content.length,
        metadata: {
            generated_at: new Date().toISOString(),
            model: env.LLM_PROVIDER
        }
    };
};

Update Platform Generators

Each generator in agents/createPosts.ts should return consistent structure:

interface PlatformResponse {
  content: string;
  images?: string[];
  fullHtml?: string; // for email
}

2.4 — Unified Publishing Adapter

Tasks

[x] Define PublishAdapter interface
[x] Implement Twitter adapter
[x] Implement LinkedIn adapter
[x] Implement Facebook adapter
[x] Implement Mailchimp adapter
[x] Refactor Ghost adapter to match interface
[x] Create unified /publish endpoint
[ ] Store per-client credentials in Supabase (encrypted)

PublishAdapter Interface

Create api/src/integrations/types.ts:

export interface PublishPayload {
  customer_id: number;
  platform_post_id: number;
  content: string;
  media_urls?: string[];
  scheduled_time?: Date;
  metadata?: Record<string, any>;
}

export interface PublishResult {
  success: boolean;
  external_id?: string;
  external_url?: string;
  error?: string;
}

export interface PublishAdapter {
  name: string;
  platform: string;
  
  /**
   * Validate that credentials are configured and working
   */
  validateCredentials(customerId: number): Promise<boolean>;
  
  /**
   * Publish content to the platform
   */
  publish(payload: PublishPayload): Promise<PublishResult>;
  
  /**
   * Schedule content for future publishing
   */
  schedule?(payload: PublishPayload): Promise<PublishResult>;
}

Twitter Adapter

Install: pnpm add twitter-api-v2

Create api/src/integrations/twitter.ts:

import { TwitterApi } from 'twitter-api-v2';
import { PublishAdapter, PublishPayload, PublishResult } from './types.js';
import { createSupabaseClient } from '../utils/supabase.js';

const supabase = createSupabaseClient();

export class TwitterAdapter implements PublishAdapter {
  name = 'Twitter/X';
  platform = 'twitter';

  private async getClient(customerId: number): Promise<TwitterApi> {
    // Fetch encrypted credentials from Supabase
    const { data, error } = await supabase
      .from('client_integrations')
      .select('credentials')
      .eq('customer_id', customerId)
      .eq('platform', 'twitter')
      .single();

    if (error || !data) {
      throw new Error('Twitter credentials not found');
    }

    const { api_key, api_secret, access_token, access_secret } = data.credentials;

    return new TwitterApi({
      appKey: api_key,
      appSecret: api_secret,
      accessToken: access_token,
      accessSecret: access_secret
    });
  }

  async validateCredentials(customerId: number): Promise<boolean> {
    try {
      const client = await this.getClient(customerId);
      await client.v2.me();
      return true;
    } catch {
      return false;
    }
  }

  async publish(payload: PublishPayload): Promise<PublishResult> {
    try {
      const client = await this.getClient(payload.customer_id);

      let mediaIds: string[] = [];

      // Upload media if present
      if (payload.media_urls && payload.media_urls.length > 0) {
        for (const url of payload.media_urls) {
          const response = await fetch(url);
          const buffer = Buffer.from(await response.arrayBuffer());
          const mediaId = await client.v1.uploadMedia(buffer, { mimeType: 'image/jpeg' });
          mediaIds.push(mediaId);
        }
      }

      // Post tweet
      const tweet = await client.v2.tweet({
        text: payload.content,
        media: mediaIds.length > 0 ? { media_ids: mediaIds } : undefined
      });

      return {
        success: true,
        external_id: tweet.data.id,
        external_url: `https://twitter.com/i/status/${tweet.data.id}`
      };
    } catch (error) {
      return {
        success: false,
        error: error instanceof Error ? error.message : String(error)
      };
    }
  }
}

Unified Publish Endpoint

Create api/src/index.ts endpoint:

const PublishSchema = Type.Object({
  customer_id: Type.Number(),
  platform_post_id: Type.Number(),
  channels: Type.Array(Type.String()) // ['twitter', 'linkedin', 'facebook']
});

const adapters: Record<string, PublishAdapter> = {
  twitter: new TwitterAdapter(),
  // linkedin: new LinkedInAdapter(),
  // facebook: new FacebookAdapter(),
  // ghost: new GhostAdapter(),
  // mailchimp: new MailchimpAdapter()
};

app.post<{ Body: typeof PublishSchema.static }>('/publish', {
  schema: {
    body: PublishSchema
  }
}, async (request, reply) => {
  const { customer_id, platform_post_id, channels } = request.body;

  // Fetch platform post content
  const { data: platformPost, error } = await supabase
    .from('customer_platform_post')
    .select('platform, content, images')
    .eq('id', platform_post_id)
    .single();

  if (error || !platformPost) {
    return reply.code(404).send({ error: 'Platform post not found' });
  }

  const results: Record<string, PublishResult> = {};

  for (const channel of channels) {
    const adapter = adapters[channel];
    if (!adapter) {
      results[channel] = { success: false, error: 'Adapter not found' };
      continue;
    }

    results[channel] = await adapter.publish({
      customer_id,
      platform_post_id,
      content: platformPost.content,
      media_urls: platformPost.images
    });

    // Log to integration_log
    await supabase.from('integration_log').insert({
      post_id: platform_post_id,
      integration: channel,
      result: results[channel]
    });
  }

  return { results };
});

New Tooling Required

cheerio + turndown — URL scraping → Markdown
twitter-api-v2 — Twitter publishing
Supabase migrations for 5 new tables
New API endpoints (8 total)

Verification Checklist

[x] All new endpoints return valid JSON
[x] GET /client/:id/context returns brand voice and analytics
[x] POST /search_images returns Unsplash URLs
[x] POST /scrape returns clean Markdown
[x] New database tables created with proper indexes
[x] At least one PublishAdapter (Twitter) is working
[x] /publish endpoint accepts multi-channel payloads

Next Steps

Proceed to Phase 3: Conversation & Approval Flow to build the end-to-end Telegram conversation experience.