Step 3 — Update Clients Service

File: api/src/services/clients.service.ts
Prerequisites: Step 2 — Posts Service

What This Does

Updates four functions in clients.service.ts that currently read customer_posts.scheduled:

  1. listScheduledPosts — now queries customer_platform_post.scheduled_at
  2. listAllScheduledPosts — same, cross-client
  3. getClientDashboard — upcoming_scheduled list sourced from platform posts
  4. getDailyBriefingSummary — removes the incorrect p.scheduled filter on sent posts

3.1 — Replace listScheduledPosts

Find the function (look for // listScheduledPosts — future scheduled posts for a client) and replace the entire function body:

// ---------------------------------------------------------------------------
// listScheduledPosts — future scheduled platform posts for a client
// ---------------------------------------------------------------------------

export async function listScheduledPosts(clientId: number, limit = 20) {
  const now = new Date().toISOString()

  const { data, error } = await supabase
    .from('customer_platform_post')
    .select(
      'id, platform, scheduled_at, sent_at, customer_post_id, customer_posts!inner(id, title, platforms, status, customer_id)'
    )
    .eq('customer_posts.customer_id', clientId)
    .not('scheduled_at', 'is', null)
    .gte('scheduled_at', now)
    .order('scheduled_at', { ascending: true })
    .limit(limit)

  if (error) throw new ServiceError(500, error.message)

  return (data ?? []).map((row: any) => ({
    id: row.id as number,
    platform: row.platform as string,
    scheduled_at: row.scheduled_at as string,
    sent_at: (row.sent_at as string | null) ?? null,
    customer_post_id: row.customer_post_id as number,
    title: (row.customer_posts as any)?.title ?? null,
    platforms: (row.customer_posts as any)?.platforms ?? [],
    status: (row.customer_posts as any)?.status ?? null,
  }))
}

3.2 — Replace listClientPosts

The existing listClientPosts function selects scheduled in its query string. Remove it:

Find the line:

    .select('id, title, prompt, status, scheduled, created_at, platforms')
    .eq('customer_id', clientId)
    .order('created_at', { ascending: false })

Replace with:

    .select('id, title, prompt, status, created_at, platforms')
    .eq('customer_id', clientId)
    .order('created_at', { ascending: false })

3.3 — Replace listAllScheduledPosts

Find the function (look for // listAllScheduledPosts — used by the list-scheduled-posts MCP tool) and replace the entire function body:

// ---------------------------------------------------------------------------
// listAllScheduledPosts — used by the list-scheduled-posts MCP tool
// Returns all upcoming scheduled platform posts across every active client
// belonging to a user.
// ---------------------------------------------------------------------------

export async function listAllScheduledPosts(userId: string) {
  const { data: customers, error: custErr } = await supabase
    .from('customer_customer')
    .select('id, name')
    .eq('user_id', userId)
    .eq('active', true)
    .order('name', { ascending: true })

  if (custErr) throw new ServiceError(500, custErr.message)

  const customerList = customers ?? []
  if (customerList.length === 0) return { scheduled_posts: [], count: 0 }

  const customerIds = customerList.map((c: { id: number; name: string }) => c.id)
  const customerMap: Record<number, string> = Object.fromEntries(
    customerList.map((c: { id: number; name: string }) => [c.id, c.name])
  )

  const now = new Date().toISOString()

  const { data, error: postsErr } = await supabase
    .from('customer_platform_post')
    .select(
      'id, platform, scheduled_at, customer_post_id, customer_posts!inner(id, title, platforms, customer_id)'
    )
    .in('customer_posts.customer_id', customerIds)
    .not('scheduled_at', 'is', null)
    .gte('scheduled_at', now)
    .order('scheduled_at', { ascending: true })
    .limit(50)

  if (postsErr) throw new ServiceError(500, postsErr.message)

  const scheduled_posts = (data ?? []).map((row: any) => ({
    id: row.id as number,
    platform: row.platform as string,
    scheduled_at: row.scheduled_at as string,
    customer_post_id: row.customer_post_id as number,
    title: ((row.customer_posts as any)?.title as string | null)?.slice(0, 60) || 'Untitled',
    platforms: (row.customer_posts as any)?.platforms ?? [],
    client_id: (row.customer_posts as any)?.customer_id as number,
    client_name: customerMap[(row.customer_posts as any)?.customer_id as number] || 'Unknown',
  }))

  return { scheduled_posts, count: scheduled_posts.length }
}

3.4 — Update getClientDashboard — upcoming_scheduled block

Find the block inside getClientDashboard that builds upcoming_scheduled (look for the comment const now = new Date().toISOString() followed by the .filter for status === 'scheduled'):

Remove this block:

const now = new Date().toISOString()
const upcoming_scheduled = allPosts
  .filter((p) => p.status === 'scheduled' && p.scheduled && (p.scheduled as string) >= now)
  .sort(
    (a, b) => new Date(a.scheduled as string).getTime() - new Date(b.scheduled as string).getTime()
  )
  .slice(0, 5)
  .map((p) => ({
    id: p.id,
    title: (p.title as string) || (p.prompt as string)?.slice(0, 60) || 'Untitled',
    platforms: p.platforms,
    scheduled: p.scheduled,
  }))

Replace with a separate query for platform posts:

const now = new Date().toISOString()

const { data: upcomingPlatformPosts } = await supabase
  .from('customer_platform_post')
  .select(
    'id, platform, scheduled_at, customer_post_id, customer_posts!inner(id, title, platforms)'
  )
  .eq('customer_posts.customer_id', clientId)
  .not('scheduled_at', 'is', null)
  .gte('scheduled_at', now)
  .order('scheduled_at', { ascending: true })
  .limit(5)

const upcoming_scheduled = (upcomingPlatformPosts ?? []).map((row: any) => ({
  id: row.customer_post_id as number,
  platform_post_id: row.id as number,
  platform: row.platform as string,
  title: ((row.customer_posts as any)?.title as string | null)?.slice(0, 60) || 'Untitled',
  platforms: (row.customer_posts as any)?.platforms ?? [],
  scheduled_at: row.scheduled_at as string,
}))

Also remove scheduled from the getClientDashboard posts SELECT query. Find:

    .select('id, title, prompt, status, scheduled, created_at, platforms')
    .eq('customer_id', clientId)

Replace with:

    .select('id, title, prompt, status, created_at, platforms')
    .eq('customer_id', clientId)

3.5 — Update getDailyBriefingSummary — remove p.scheduled from sent filter

Find (around line 943):

const sent_this_week = cPosts.filter(
  (p: any) => p.status === 'sent' && p.scheduled && new Date(p.scheduled) >= since7d
).length

Replace with (use sent_at when available, fall back to created_at):

const sent_this_week = cPosts.filter((p: any) => {
  if (p.status !== 'sent') return false
  const ref = p.sent_at ?? p.created_at
  return ref && new Date(ref) >= since7d
}).length

Also update the customer_posts select query earlier in getDailyBriefingSummary. Find:

    .select('id, title, customer_id, status, scheduled')
    .in('customer_id', customerIds)
    .order('scheduled', { ascending: true, nullsFirst: true })
    .order('id', { ascending: false })

Replace with:

    .select('id, title, customer_id, status, sent_at, created_at')
    .in('customer_id', customerIds)
    .order('created_at', { ascending: false })

3.6 — Verify

cd api
pnpm build
# Should compile without errors

✅ Done

Proceed to Step 4 — Plan Schedule Route.