Tool Catalog — Technical Deep Dive & Implementation Specs

Two tools are pure pass-throughs to existing public endpoints, two need no Roster work at all (MCP-layer only), and the remaining seven are served by five new v2 Open API endpoints plus two small modifications — every one wrapping an existing, production-tested stored procedure or service the portal itself uses. No new business logic, no recomputation.

Verified findings that resolve PRD open questions

2.1 Upstream API envelope (existing — do not change)

All v2 Open API endpoints return (verified across App.WebApi.Open controllers):

{
  "success": true,
  "message": null,
  "result": { /* payload */ }
}

{
  "data": [ /* items */ ],
  "pagination": {
    "pageIndex": 1,        // 1-based
    "pageSize": 50,        // default 50, max 10,000
    "totalRecords": 1234,
    "totalPages": 25,
    "nextPageIndex": 2     // null on last page
  }
}

Sorting: sortField (per-endpoint enum) + sortDirection (Asc|Desc). Date filters: ISO 8601 (createdAtMin/createdAtMax, inclusive).

2.2 Portal deep-link paths (for portal_source)

Verified portal deep-link paths (from web-app-brand-portal/src/scripts/containers/menuV2/routes.js and AppShell.js:423,490,518):

2.3 MCP-layer behavior — see the MCP & OAuth writeup §2

The MCP response envelope (brand + portal_source + cursor pagination + truncated), error mapping, tool annotations (readOnlyHint: true, openWorldHint: false), and the timezone model are server-layer behavior, specified once in the MCP & OAuth writeup. The two facts tool implementers need here: dates are explicit ISO dates — Claude resolves relative ranges in the user's timezone before calling, omitted dates default to UTC last-30-days (PRD Decision 5; there is no brand-timezone field anywhere in Roster — verified) — and MCP page caps are 50 default / 200 max, well under the upstream 10,000.

{
  "name": "list_programs",
  "title": "List ambassador programs",
  "description": "Lists the brand's ambassador programs with
    member and applicant counts. Use this first to resolve a
    program name (e.g. 'my VIP program') to a program_id for
    other tools. Cheap to call. Set include_details to also
    fetch one program's application form, smart link, discount
    rules, and referral commission settings.",
  "annotations": { "readOnlyHint": true },
  "inputSchema": {
    "type": "object",
    "properties": {
      "status": { "type": "string",
        "enum": ["active","archived","all"],
        "default": "active" },
      "include_details_for_program_id": { "type": "integer",
        "description": "Optional. Fetch extended
         join-requirement details for this one program." }
    }
  }
}

{
  "programs": [{
    "program_id": 42, "name": "VIP Ambassadors",
    "key": "vip", "status": "active",
    "ambassadors_count": 312,
    "pending_applicants_count": 18,
    "nominated_applicants_count": 4,
    "rejected_applicants_count": 51,
    "created_at": "2024-03-01T00:00:00Z",
    "updated_at": "2026-06-01T00:00:00Z"
  }],
  "program_details": {   // only when requested;
                         // from GET /v2/programs/{id}
    "application_form": { … },
    "smart_link": { … },
    "personal_discount_rule_id": 7,
    "shareable_discount_rule_id": 9,
    "referral_commissions": { … },
    "referral_points": { … }
  }
}

Implementation notes: status maps to the upstream status array (Active = lookup 7, Archived = 49; both when all). Drop color/icon fields (UI noise for an LLM). No backend work.

How the dashboard actually computes (verified end-to-end)

GET /api/user/page/component/{componentId}/data    UserPageApiController.cs:64-80
  → UserPageService.GetPageComponentData()         UserPageService.cs:199-314
  → UserPageData.GetProgramMetric()                UserPageData.cs:538-583
  → EXEC dbo.UserPageComponent_GetProgramMetric    1,738-line dispatcher SP — verified on Shared18
      → fn_CalculateComponentData_ProgramMetric*   per-metric table functions — verified on Shared18

The dispatcher is coupled to a saved dashboard component (@ComponentId VARCHAR(30) — it reads the component's saved Settings for metric type and defaults). The metric functions underneath are clean, reusable, and take (@BrandUserId, @ProgramIds, @StartDate DATETIMEOFFSET, @EndDate, @DateGrouping, …).

Each function returns a time series + comparison: [{Date, Value}] grouped by an auto-selected DateGrouping (span ≤1 day → finest grain; coarser as the span grows; dispatcher lines 77-94), plus Total, PrevCount (parallel preceding period of equal length: @DeltaStartDate = @StartDate − (EndDate−StartDate)), and Delta (% change).

New endpoint to build — GET /v2/programs/{program_id}/performance

New controller ProgramPerformanceController in App.WebApi.Open/Controllers/Community/. Params: startDate (DateTimeOffset, default 30 days ago — matches dashboard default), endDate (default now), metrics (optional filter — return only the dashboard cards matching these metric keys).

Implementation approach (decided — saved-dashboard mirror): service loads the brand's saved page + components for the program (default-template fallback), then calls the existing UserPageComponent_GetProgramMetric per component via UserPageData.GetProgramMetric (UserPageData.cs:538-583) — the exact path the portal uses. No new SP needed; the endpoint is controller + service composition + DTOs. The earlier alternative (a new SP over the fn_CalculateComponentData_* functions returning a fixed standard catalog regardless of dashboard config) is shelved unless parity-with-dashboard proves too narrow in beta.

{
  "name": "get_program_performance",
  "title": "Get program performance (Program Dashboard)",
  "description": "Returns the portal's Program Dashboard for
    one program over a date range — the same widgets the
    brand has configured on that program's dashboard
    (typically: applicants, members, first-time portal
    logins, post mentions, engagements, impressions, earned
    media value, points, referred revenue, and more). Each
    metric includes a time series, the period total, and the
    change vs the preceding period of equal length. Defaults
    to the last 30 days, matching the dashboard. Note: the
    set of metrics returned mirrors the brand's dashboard
    configuration; if a metric isn't on their dashboard, use
    the report tools instead (e.g. get_social_posts_report
    for EMV). Resolve relative date ranges in the user's
    timezone before calling. Use list_programs first to
    resolve the program_id.",
  "annotations": { "readOnlyHint": true },
  "inputSchema": {
    "type": "object",
    "required": ["program_id"],
    "properties": {
      "program_id": { "type": "integer" },
      "start_date": { "type": "string", "format": "date" },
      "end_date":   { "type": "string", "format": "date" },
      "metrics": { "type": "array", "items": { "type":
        "string", "enum": ["applicants","members",
        "first_time_logins","post_mentions",
        "post_engagements","post_impressions","emv",
        "points_earned","referred_revenue","referred_orders",
        "link_clicks","new_customers","personal_revenue",
        "campaigns_joined","campaigns_completed",
        "actions_completed","post_reach",
        "milestones_unlocked","rewards_earned",
        "referral_points","referral_commissions"] },
        "description": "Optional filter — only return
         dashboard cards matching these keys; omit for
         the full dashboard" }
    }
  }
}

{
  "program": { "program_id": 42,
               "name": "VIP Ambassadors" },
  "date_range": { "start": "2026-05-10",
                  "end": "2026-06-09",
                  "grouping": "week" },
  "dashboard_cards": [
    {
      "section": "Program Health",
      "metric": "post_mentions",
      "total": 184,
      "previous_period_total": 150,
      "delta_pct": 22.7,
      "series": [
        { "date": "2026-05-10", "value": 41 },
        { "date": "2026-05-17", "value": 48 }
      ]
    },
    {
      "section": "Social Engagement",
      "metric": "emv",
      "total": 12450.30,
      "previous_period_total": 9100.00,
      "delta_pct": 36.8,
      "series": [ /* … */ ],
      "unit": "USD"
    }
    /* … one entry per card on the brand's saved
       dashboard, in dashboard order … */
  ]
}

The Brand Portal's own campaigns page calls this exact public endpoint (web-app-brand-portal/src/scripts/containers/campaigns/actions.js:196-237 uses getRosterApiAxios() → GET campaigns), so parity is automatic.

• Verified params: query, type[] (Public_hashtag | Invite_only), status[] (Draft | Planned | Published | Active | Completed | Archived), createdBy[], actionRequired[] (needs_approval | needs_fulfillment), includeCampaignStats, paging, sorting. (Enum values verified in OpenApiCampaign.cs:83-97 — an earlier draft of this research had them wrong.)
• OpenApiCampaign fields: campaignId, name, title, type, status, publishAt, startAt, endAt, joinByDate, participantLimit, joinedCount, createdAt, updatedAt, createdBy, plus when includeCampaignStats=true: socialPostsCount, reach, engagementRate, addedCount, invitedCount, joinedCount, completedCount, participationRate, completionRate, needsApprovalCount, needsFulfillmentCount (OpenApiCampaignStats.cs; backed by SP dbo.UserCampaign_GetStats_V2 — verified on Shared18).

{
  "name": "list_campaigns",
  "title": "List campaigns",
  "description": "Lists the brand's campaigns with status, dates, and participant counts (invited,
    joined, completed, participation rate). Filter by status or search by name. Use this to resolve
    a campaign name to a campaign_id for get_campaign_performance.",
  "annotations": { "readOnlyHint": true },
  "inputSchema": {
    "type": "object",
    "properties": {
      "query":  { "type": "string", "description": "Search by campaign name/title" },
      "status": { "type": "array", "items": { "type": "string",
                  "enum": ["draft","planned","published","active","completed","archived"] } },
      "include_stats": { "type": "boolean", "default": true },
      "page": { "type": "integer", "default": 1 },
      "page_size": { "type": "integer", "default": 50, "maximum": 200 }
    }
  }
}

Response: pass-through of the fields above (snake_cased). No backend work.

What the campaign overview page actually calls (verified)

CampaignAnalyticsContainer → Overview.js fetches GET /api/user/report/no-cache?typeId=…&userCampaignId=… (campaigns/analytics/actions.js:245-275) for these report types (Overview.js:26-32, 67-73), all dispatched through SP UserCampaignReport_GetByType (verified on Shared18): REPORT_CAMPAIGN_ANALYTICS_TOTAL_CONTENT · TOTAL_REACH · TOTAL_ENGAGEMENT · CONTENT_BY_NETWORK · REACH_BY_NETWORK · CAMPAIGN_STATUS (the added → emails sent/opened → joined → completed funnel) · PERFORMANCE_BY_NETWORK — plus GET /api/campaign/{id}/ambassador-stats, top posts via GET /api/social-listening/campaign-posts, and participants via the public GET /v2/campaigns/{id}/participants.

What exists vs. what's missing

Backend work

• Add campaignIds filter to GET /v2/campaigns (CampaignRequest in CampaignController.cs:74-106; plumb through UserCampaignStatsRequest → UserCampaign_GetStats_V2). Today there is no way to fetch one campaign by id on the public API (verified — request DTO has no id field).
• New GET /v2/campaigns/{campaign_id}/performance returning: the campaign's OpenApiCampaignStats (reuse CampaignService.GetCampaignsStats filtered to the id), the campaign-overview report set (wrap UserCampaignReport_GetByType with the same REPORT_CAMPAIGN_ANALYTICS_* type IDs the overview page requests — funnel = CAMPAIGN_STATUS; service mapping in UserReportService.cs:24-140), content stats: posts / stories / uploads counts + likes/comments/views (REPORT_CAMPAIGN_TOTAL_POST_STATS case, SP lines 193-205), and a campaign EMV rollup (new small query over UserCampaignPost ⋈ UserSocialListeningPostEMV).

{
  "name": "get_campaign_performance",
  "title": "Get campaign performance
            (campaign overview)",
  "description": "Returns one campaign's full performance,
    matching the portal's campaign overview page: invite
    funnel (added, emails sent/opened, joined, completed,
    participation and completion rates), content generated
    (posts, stories, uploads with likes/comments/views),
    reach and engagement by network, earned media value, and
    reward fulfillment counts. Use list_campaigns to resolve
    the campaign_id. Note: Roster does not attribute order
    revenue to campaigns; for revenue questions use
    get_sales_attribution_report.",
  "annotations": { "readOnlyHint": true },
  "inputSchema": {
    "type": "object",
    "required": ["campaign_id"],
    "properties": {
      "campaign_id": { "type": "integer" }
    }
  }
}

{
  "campaign": { "campaign_id": 88,
    "name": "Summer Launch", "status": "active",
    "start_at": "2026-05-01", "end_at": "2026-07-01" },
  "funnel": {
    "added": 120, "emails_sent": 118,
    "emails_opened": 96, "joined": 74,
    "completed": 31,
    "participation_rate": 61.7,
    "completion_rate": 41.9
  },
  "content": {
    "posts": 64, "stories": 12, "uploads": 9,
    "likes": 8120, "comments": 644,
    "video_views": 19500
  },
  "social": {
    "follower_reach": 410000,
    "engagement_rate": 4.2,
    "emv": 9120.50
  },
  "rewards": {
    "needs_approval": 3,
    "needs_fulfillment": 5
  }
}

Why the existing endpoint isn't enough

GET /v2/contacts (ContactsController.cs:42-66) filters only by contactIds | email | instagramHandle | referralLinkId — no name query, no program/tag/status filter, no join-date range, no sorting, and the OpenApiContact DTO carries no performance fields. The PRD's leaderboard and export use cases need the portal's search.

What the portal actually uses (verified)

POST /api/account/account-searchV2 → AccountSearchService.AccountSearchV2 (AccountSearchService.cs:644-692) → Azure Cognitive Search (index fed from SQL view vw_AccountSearchAll_V4 via change tracking). The index already contains everything the tool needs (field list at AccountSearchService.cs:2305-2370): identity + phone, per-network handles/followers/engagement, tags, custom properties, programs + status + added date, TotalSpent/OrdersCount, TotalReferralValue/TotalReferralOrders/LastReferralDate, MentionsTotalCount/LastMentionDate, campaigns, points, LastLoginDate, referral links and discount codes.

Backend work — new GET /v2/contacts/search

New controller in App.WebApi.Open/Controllers/Community/ wrapping AccountSearchService.AccountSearchV2 with a fixed, documented column set. Params: query (name/email free text), programIds[], programStatus, tags[], joinedAtMin/Max, sortField + sortDirection, paging.

{
  "name": "list_ambassadors",
  "title": "Search ambassadors",
  "description": "Paginated search over the brand's
    ambassadors/contacts — the same search the portal's
    contact list uses. Filter by name/email text, program,
    tag, program status, or join-date range; sort by
    lifetime referral revenue, post mentions, joined date,
    followers, or engagement to build leaderboards. Returns
    full contact rows: email, phone, social handles with
    follower counts, tags, custom properties, program
    memberships, lifetime referral revenue and order counts,
    points, and last portal login. For one contact's full
    performance detail use get_ambassador.",
  "annotations": { "readOnlyHint": true },
  "inputSchema": {
    "type": "object",
    "properties": {
      "query": { "type": "string" },
      "program_id": { "type": "integer" },
      "status": { "type": "string", "enum":
        ["member","applicant","rejected","none"] },
      "tag": { "type": "string" },
      "joined_after":  { "type": "string", "format": "date" },
      "joined_before": { "type": "string", "format": "date" },
      "sort": { "type": "string", "enum":
        ["referral_revenue","posts","joined_date",
         "followers","engagement","total_spent"],
        "default": "referral_revenue" },
      "sort_direction": { "type": "string",
        "enum": ["asc","desc"], "default": "desc" },
      "page": { "type": "integer", "default": 1 },
      "page_size": { "type": "integer",
        "default": 50, "maximum": 200 }
    }
  }
}

{
  "contact_id": 9001,
  "first_name": "Stephanie", "last_name": "Lee",
  "email": "s@ex.com",
  "phone": "+1-555-0100",
  "socials": [{
    "network": "instagram", "handle": "steph.lee",
    "followers": 24800, "engagement_rate": 3.1
  }],
  "tags": ["vip", "ugc-approved"],
  "custom_properties": { "shoe_size": "8" },
  "programs": [{
    "program_id": 42, "name": "VIP Ambassadors",
    "status": "member", "joined_at": "2025-02-11"
  }],
  "lifetime_referral_revenue": 18250.00,
  "lifetime_referral_orders": 61,
  "last_referral_at": "2026-06-02",
  "post_mentions_total": 87,
  "last_mention_at": "2026-06-05",
  "total_points": 1240,
  "last_portal_login_at": "2026-06-01",
  "referral_link": "https://brand.com/?rstr=abc123",
  "discount_codes": ["STEPH20"]
}

Lifetime referral revenue semantics (so support can answer "why doesn't this match X"): computed in vw_AccountSearchAll_V4:485-527 as the UNION of (a) orders linked via the ambassador's assigned discount codes and (b) orders linked via referral-link tracking (UserCodeTrackingUsage), excluding cancelled/refunded orders, using SubtotalPrice in the brand's currency.

Existing piece

GET /v2/contacts/{contact_id} (ContactsController.cs:24-36) already returns the profile: name, email, phoneNumber, DOB, shipping address, PayPal/Venmo, per-network handle strings, tags, single program object, sharableCodes, referralLink + referralLinkIds, contactOwnerEmail, lifeTimePoints, dateAdded, termsAgreementDate, lastAccessedPortalDate (OpenApiContact.cs). Custom properties via GET /v2/contacts/{id}/properties.

Missing piece → new GET /v2/contacts/{contact_id}/performance

The portal detail page gets its performance numbers from internal GET /api/account/{accountId} (AccountApiController.cs:100-107 → AccountService.GetAccount), whose Account model carries (Account.cs:17-323): TotalReferralValue, TotalReferralOrders, LastReferralDate, Customer.{OrdersCount, TotalSpent, MostRecentOrderDate, MostRecentOrderValue} (personal orders), MentionsTotalCount, campaign participation fields, TotalPoints, ActionsCompleted, LastLoginDate, social profiles with stats.

{
  "name": "get_ambassador",
  "title": "Get ambassador profile & performance",
  "description": "Returns one ambassador's full profile and
    performance, mirroring the portal's contact detail page:
    profile (email, phone, socials with followers, tags,
    custom properties, program memberships), lifetime
    referral revenue and orders, personal orders, commission
    totals by status, post counts with engagement and EMV,
    rewards earned/redeemed, referral links and discount
    codes, and last activity dates. Look up by contact_id
    or email.",
  "annotations": { "readOnlyHint": true },
  "inputSchema": {
    "type": "object",
    "properties": {
      "contact_id": { "type": "integer" },
      "email": { "type": "string", "format": "email",
                 "description": "Alternative to contact_id" }
    }
  }
}
// Email lookup: resolve via existing
// GET /v2/contacts?email= first, then fetch by id.

{
  "profile": { /* GET /v2/contacts/{id} fields
                  + custom_properties */ },
  "performance": {
    "referral": {
      "lifetime_revenue": 18250.00,
      "lifetime_orders": 61,
      "last_referral_at": "2026-06-02"
    },
    "personal_orders": {
      "count": 9, "total_spent": 1120.00,
      "most_recent_at": "2026-05-20"
    },
    "commissions": {
      "pending": 240.00, "approved": 1180.00,
      "paid": 9320.00, "currency": "USD"
    },
    "social": {
      "posts": 87, "engagements": 14230,
      "impressions": 410200, "emv": 6210.40
    },
    "rewards": {
      "earned": 22, "fulfilled": 19,
      "not_redeemed": 2
    },
    "last_activity": {
      "last_portal_login_at": "2026-06-01",
      "last_post_at": "2026-06-05"
    }
  }
}

Verified data path (the wrapper target)

GET /api/user/report/sales-attribution             UserReportApiController.cs:133-144
  → UserReportService.GetSalesAttributionReport()  UserReportService.cs:184-250
  → UserReportData.GetSalesAttributionReport()     UserReportData.cs:79-117
  → EXEC dbo.UserReport_GetSalesAttribution        verified on Shared18; cmd timeout 300s

The SP is already fully parameterized — the new endpoint is a pure pass-through: @AccountIdsJson, @ProgramIdsJson, @AccountOwnerUserIdsJson, @AttributionMethodIdsJson, @TagIdsJson, @StartDate, @EndDate, @PageIndex, @PageSize, @SortBy, @SortDirection, @AdjustRevenue.

Row semantics (verified in SP + live lookups)

• A row appears per ambassador with any activity in range (clicks, referrals, revenue, or personal orders) — matches the help doc.
• Referred vs personal: AccountAttribution.TypeId 531 = referred, 709 = personal (confirmed live on Shared18 data).
• Attribution methods (lookup IDs, confirmed in OpenApiAttribution.cs enums): 870 emailAddress, 871 rewardCode (personal discount), 872 referralLink, 873 discountCode (shareable), 874 recurringOrder.
• Revenue = UserCustomerOrderCurrency.EffectivePrice (post-discount, pre-tax/shipping — matches the help-doc definition of referred revenue); rejected attributions are excluded/adjusted via AccountAttributionHistory.
• ReferralCommissions = sum of AccountReward.RewardAmount for reward types 710/560 (non-rejected), regardless of approval status; ReferralPoints likewise for 700/900.
• Sorts: totalClicks | newCustomers | referredOrders | referredRevenue | personalOrders | personalOrderRevenue (default referredRevenue DESC).
• First row carries grand totals (TotalTotalClicks, TotalNewCustomers, TotalReferredRevenue, TotalPersonalOrderRevenue, TotalRevenue, TotalRows).

Why not the existing /v2/attributions? It exists (rich row-level attributions + a summary block — AttributionController.cs:58-120, OpenApiAttributionSummary) but it is order-grained, not the per-ambassador rollup the portal report shows, and its summary fields don't match the report's totals. For report parity, wrap the report SP. (/v2/attributions remains the right citation for "show me the underlying orders" follow-ups.)

New endpoint — GET /v2/reports/sales-attribution

New controller ReportsController in App.WebApi.Open/Controllers/Reports/. Params: ambassadorIds[], programIds[], contactOwnerIds[], attributionMethods[] (enum → ids), tagIds[], fromDate, toDate, paging, sortField/sortDirection. Response DTO mirrors SalesAttributionReport (App.Domain/User/Reports/SalesAttributionReport.cs): totals{…} + rows[].

{
  "name": "get_sales_attribution_report",
  "title": "Sales Attribution report",
  "description": "The portal's Sales Attribution report: one
    row per ambassador with activity in the date range — link
    clicks, new customers, referred orders and referred
    revenue (order total after discounts, before
    tax/shipping), commissions and points earned, personal
    orders and personal-order revenue — plus grand totals.
    Filter by program, ambassador, tag, or attribution
    method; sort by any metric for leaderboards. Defaults to
    the last 30 days. This is the source of truth for
    ambassador revenue questions and exec reporting.",
  "annotations": { "readOnlyHint": true },
  "inputSchema": {
    "type": "object",
    "properties": {
      "start_date": { "type": "string", "format": "date" },
      "end_date":   { "type": "string", "format": "date" },
      "program_id": { "type": "integer" },
      "contact_id": { "type": "integer" },
      "tag": { "type": "string" },
      "attribution_methods": { "type": "array",
        "items": { "type": "string", "enum":
          ["emailAddress","rewardCode","referralLink",
           "discountCode","recurringOrder"] } },
      "sort": { "type": "string", "enum":
        ["total_clicks","new_customers","referred_orders",
         "referred_revenue","personal_orders",
         "personal_order_revenue"],
        "default": "referred_revenue" },
      "sort_direction": { "type": "string",
        "enum": ["asc","desc"], "default": "desc" },
      "page": { "type": "integer", "default": 1 },
      "page_size": { "type": "integer",
        "default": 50, "maximum": 200 }
    }
  }
}

{
  "totals": {
    "total_clicks": 4210,
    "new_customers": 96,
    "referred_revenue": 88120.50,
    "personal_order_revenue": 7240.00,
    "total_revenue": 95360.50,
    "row_count": 143
  },
  "rows": [{
    "ambassador": {
      "contact_id": 9001,
      "name": "Stephanie Lee",
      "email": "s@ex.com"
    },
    "total_clicks": 221,
    "new_customers": 4,
    "referred_orders": 8,
    "referred_revenue": 5230.00,
    "referral_commissions": 523.00,
    "referral_points": 0,
    "personal_orders": 2,
    "personal_order_revenue": 567.00,
    "shareable_codes": "STEPH20",
    "referral_link":
      "https://brand.com/?rstr=abc123",
    "currency": "USD",
    "tags": "vip"
  }]
}

Verified data path

GET /api/user/report/social-posts                  UserReportApiController.cs:180
  → UserReportService
  → EXEC dbo.UserSocialListeningPost_GetReportPage read in full; verified on Shared18

• SP signature (all JSON-array params): @ConnectionUserId, @AccountIdsJson, @ProgramIdsJson (id 0 = "no program"), @NetworkIdsJson, @AgeIdsJson, @CountryIdsJson, @GenderValuesJson, @CampaignIdsJson, @TagIdsJson, @FromDate, @ToDate, @PageIndex, @PageSize, @SortBy, @SortDirection.
• Row columns (SP final SELECT, lines 262-289): PostCount, StoryCount, ReachCount, Impressions, LikeCount, CommentCount, SharesCount, SavedCount, EMV (dollars: TotalEMVInCents/100), Engagement, EngagementRate + identity (AccountId, FirstName, LastName, Email) + grand totals (TotalPostCount, TotalStoryCount, TotalReachCount, TotalImpressions, TotalEMV, TotalEngagement, TotalEngagementRate, TotalRecords).
• ReachCount honors the brand's reach-calculation setting: enhanced → UniqueImpressions, else TotalReach (SP lines 24-45).
• Stories are counted separately (ListeningTypeId = SOCIAL_LISTENING_TYPE_INSTAGRAM_STORY); web-link "posts" excluded.
• Sort keys: postcount, storycount, reachcount, impressions, commentcount, likecount, sharescount, savedcount, emv, engagementrate (+asc|desc), default postcount desc.

New endpoint — GET /v2/reports/social-posts

In the new ReportsController. Pure pass-through to the SP. Expose filters: ambassadorIds[], programIds[], networks[] (instagram|facebook|tiktok|x), campaignIds[], tagIds[], fromDate, toDate, paging, sorting. (Age/country/gender filters exist in the SP; skip them in v1 — low LLM utility, more enum surface.)

{
  "name": "get_social_posts_report",
  "title": "Social Posts report",
  "description": "The portal's Social Posts report: one row
    per ambassador who posted in the date range, with post
    and story counts, reach, impressions, likes, comments,
    shares, saves, engagement rate, and earned media value
    (EMV) — plus grand totals across all ambassadors. Filter
    by program, campaign, network, tag, or a single
    ambassador; sort by any metric (e.g. EMV) for content
    leaderboards. Defaults to the last 30 days. For
    individual post URLs and captions use
    get_social_feed_posts.",
  "annotations": { "readOnlyHint": true },
  "inputSchema": {
    "type": "object",
    "properties": {
      "start_date": { "type": "string", "format": "date" },
      "end_date":   { "type": "string", "format": "date" },
      "platform": { "type": "array", "items": {
        "type": "string", "enum":
        ["instagram","facebook","tiktok","x"] } },
      "program_id":  { "type": "integer" },
      "campaign_id": { "type": "integer" },
      "contact_id":  { "type": "integer" },
      "tag": { "type": "string" },
      "sort": { "type": "string", "enum":
        ["posts","stories","reach","impressions","likes",
         "comments","shares","saves","emv",
         "engagement_rate"],
        "default": "posts" },
      "sort_direction": { "type": "string",
        "enum": ["asc","desc"], "default": "desc" },
      "page": { "type": "integer", "default": 1 },
      "page_size": { "type": "integer",
        "default": 50, "maximum": 200 }
    }
  }
}

{
  "totals": {
    "posts": 100, "stories": 0,
    "reach": 1300000, "impressions": 1520000,
    "emv": 48210.00, "engagement": 91200,
    "engagement_rate": 4.4,
    "ambassador_count": 37
  },
  "rows": [{
    "ambassador": {
      "contact_id": 9001,
      "name": "Stephanie Lee",
      "email": "s@ex.com"
    },
    "posts": 13, "stories": 2,
    "reach": 182000, "impressions": 210000,
    "likes": 8120, "comments": 644,
    "shares": 230, "saves": 410,
    "emv": 6210.40, "engagement": 9404,
    "engagement_rate": 5.2
  }]
}

Verified data path

POST /api/user/feed/social/page                    UserFeedApiController.cs:16-37
  → UserSocialFeedService.GetUserSocialFeedPage()  UserSocialFeedService.cs:23-43
  → UserSocialFeedData.GetUserSocialFeedPage()     UserSocialFeedData.cs:31-86
  → EXEC dbo.UserSocialListeningPost_GetFeedPage   verified on Shared18

• SP params: @UserId, @NetworkTypeIds, @ProgramIdsJson, @StartDate, @EndDate, @PostSettingTypeId (favorites/archived), @PageIndex, @PageSize, @SortBy (ENGAGEMENT_DESC | FOLLOWERS_DESC | POST_DATE_DESC), @AccountOwnerUserId, @SourceUniqueId. Inbox mode excludes archived, manually-added, and IG-story rows. Each row joins Account + UserProgram + the EMV breakdown.
• Post table columns verified live on Shared18 (UserSocialListeningPost): Permalink, Content, Link, Images, Album, LikeCount, CommentCount, SharesCount, SavedCount, VideoViews, TotalEngagement, TotalReach, UniqueImpressions, TotalImpressions, PostDate, TypeId (network), ListeningTypeId (post/story/reel), AccountId, ResourceId, … — media URLs are projected from Images/Album/ResourceId via the UserResources join in the SPs (the ImageUri the portal shows is not a table column).

New endpoint — GET /v2/social-posts

New controller SocialPostsController in App.WebApi.Open/Controllers/Community/ — note: distinct from /v2/community/posts, which is community-forum posts, not social listening. Params: networks[], programIds[], contactId, fromDate, toDate, sort (engagement|followers|post_date), paging. Each row exposes post id, ambassador (id/name), program, network, post type (post/story/reel), postDate, caption (Content), permalink, media URLs, engagement metrics (likes/comments/shares/saves/video views), impressions/reach, and the EMV breakdown.

{
  "name": "get_social_feed_posts",
  "title": "Social Feed — recent ambassador posts",
  "description": "Recent individual posts where ambassadors
    mentioned the brand, matching the portal's Social Feed:
    post URL and media, caption, platform, post type
    (post/story/reel), ambassador, program, posted date,
    likes, comments, shares, saves, video views,
    impressions, and earned media value per post. Filter by
    platform, program, ambassador, or date range; sort by
    engagement to find top-performing content, or by date
    for 'what are my ambassadors posting right now?'.
    Defaults to the last 30 days, newest first.",
  "annotations": { "readOnlyHint": true },
  "inputSchema": {
    "type": "object",
    "properties": {
      "start_date": { "type": "string", "format": "date" },
      "end_date":   { "type": "string", "format": "date" },
      "platform": { "type": "array", "items": {
        "type": "string", "enum":
        ["instagram","facebook","tiktok","x"] } },
      "program_id": { "type": "integer" },
      "contact_id": { "type": "integer" },
      "sort": { "type": "string", "enum":
        ["post_date","engagement","followers"],
        "default": "post_date" },
      "page": { "type": "integer", "default": 1 },
      "page_size": { "type": "integer",
        "default": 25, "maximum": 100 }
    }
  }
}
// Smaller default/max page size:
// rows are heavy — captions + media URLs.

{
  "post_id": 555123,
  "platform": "instagram",
  "post_type": "reel",
  "ambassador": {
    "contact_id": 9001,
    "name": "Stephanie Lee"
  },
  "program": {
    "program_id": 42,
    "name": "VIP Ambassadors"
  },
  "posted_at": "2026-06-05T18:22:00Z",
  "caption": "Loving my new @acmeoutdoor pack…",
  "permalink":
    "https://www.instagram.com/reel/abc/",
  "media": [{
    "type": "video_thumbnail",
    "url": "https://…"
  }],
  "metrics": {
    "likes": 1820, "comments": 96,
    "shares": 41, "saves": 88,
    "video_views": 21400,
    "impressions": 48200, "reach": 39800,
    "engagement": 2045
  },
  "emv": {
    "total": 812.40, "impressions": 482.00,
    "likes": 54.60, "comments": 172.80,
    "shares": 82.00, "saves": 21.00
  }
}

Corpus: 234 published articles at docs.getroster.com; canonical URLs in each article's frontmatter (docs/brand-portal-docs/*.txt).

Archbee has a public search API — verified in the CLI source this workspace already ships (roster-vendor-tools/archbee/src/archbee_cli/client.py:185-197):

POST {base_url}/api/public-api/docs/search
Authorization: Bearer {ARCHBEE_API_KEY}
Body: { "query": "smart links" }
→ { "data": { "docs": [ … ] } }

• Recommendation — Option A: proxy Archbee search from the MCP service with a server-held API key (same key the archbee CLI uses; scoped to the public doc space). Map results to {title, url, excerpt}; cache responses ~1h. One open item: confirm the exact per-doc fields in the search response with a live call (the CLI normalizes to data.docs[] but the doc-level shape — title/slug/highlight — should be captured during implementation). URL construction: https://docs.getroster.com/… exactly as in the export frontmatter url field.
• Option B (fallback / no runtime dependency): bundle the exported corpus (archbee pull) into the MCP service at deploy time with a small lexical index (BM25). Deterministic, zero external calls, but goes stale between deploys and re-implements ranking. Choose A unless Archbee rate limits or licensing object.

{
  "name": "search_help_docs",
  "title": "Search Roster help docs",
  "description": "Keyword search over Roster's published
    help documentation (docs.getroster.com). Returns article
    titles, canonical URLs, and excerpts. Use for how-to and
    troubleshooting questions ('why isn't my discount code
    tracking?', 'how do I set up smart links?') — cite the
    URL so the user can read the full article.",
  "annotations": { "readOnlyHint": true,
                   "openWorldHint": false },
  "inputSchema": {
    "type": "object", "required": ["query"],
    "properties": {
      "query": { "type": "string" },
      "max_results": { "type": "integer",
        "default": 5, "maximum": 10 }
    }
  }
}

{
  "results": [{
    "title": "Sales attribution report",
    "url": "https://docs.getroster.com/
            sales-attribution-report",
    "excerpt": "…rolls up key metrics such
                as link clicks…"
  }]
}

Primary source: the OAuth service's own grant record, captured at consent — no Roster API call needed per invocation. (Schemas verified in db-global/GlobalDB/dbo/Tables/ — ApiSession.sql, SystemBrand.sql, SystemUserDomain.sql, SystemUser.sql.)

Optional live-check enrichment — new GET /v2/me. The only whoami today is legacy GET /me (LegacyAuthController.cs:10-27), which returns just { UserId } and is hidden from docs. A proper v2 endpoint returning { brandName, brandDomain, tokenCreatedAt, accessType } lets the tool double as the PRD's "cheap connectivity check" (a 401 here = revoked bridge token → surface re-auth). Low effort; recommended but not blocking — the tool can ship on grant-store data alone with a ping to any cheap endpoint (e.g. /v2/programs?pageSize=1).

{
  "name": "get_connection_info",
  "title": "Show connection info",
  "description": "Returns which Roster brand this Claude
    connection is bound to (name and portal domain), who
    authorized it, the access scope (read-only), and when
    it was granted. Use when the user asks 'which brand is
    this?' or to verify the connection is alive. Takes no
    parameters.",
  "annotations": { "readOnlyHint": true },
  "inputSchema": { "type": "object", "properties": {} }
}

{
  "brand": {
    "name": "Acme Outdoor",
    "domain": "acme"
  },
  "authorized_by": {
    "name": "Jane Smith",
    "email": "jane@acme.com"
  },
  "scope": "read-only",
  "granted_at": "2026-06-09T17:00:00Z",
  "connection_healthy": true
}

All in repos/api-brand-portal, project src/App.WebApi.Open (+ App.OpenApi.Domain DTOs, App.DomainService/App.DomainRepository where noted, SPs in repos/db-brand-portal). Every endpoint: [ApiVersion("2")], standard auth filter, RateLimitAttribute, Swagger docs — follow the existing controller pattern (e.g. AttributionController.cs).

Suggested commit slicing: one PR per endpoint; E6/E7 first (smallest, immediately unblock the two highest-value report tools), then E1, E4, E5, E3+E2, E8, E9.

Parity test per endpoint (required by PRD): for a seeded test brand and fixed date range, assert the endpoint's figures equal the portal's — implement as integration tests that call the internal endpoint and the new open endpoint with the same params and diff the numbers.