Function getOrganizationBillingData

apps/sim/lib/billing/core/organization.ts:92–274

(
  organizationId: string,
  executor: DbClient = db
)

 * Get comprehensive organization billing and usage data
 */
export async function getOrganizationBillingData(
  organizationId: string,
  executor: DbClient = db
): Promise<OrganizationUsageData | null> {
  try {
    // Get organization info
    const orgRecord = await executor
      .select()
      .from(organization)
      .where(eq(organization.id, organizationId))
      .limit(1)

    if (orgRecord.length === 0) {
      logger.warn('Organization not found', { organizationId })
      return null
    }

    const organizationData = orgRecord[0]

    // Get organization subscription directly (referenceId = organizationId)
    const subscription = await getOrganizationSubscription(organizationId, { executor })

    if (!subscription) {
      logger.warn('No subscription found for organization', { organizationId })
      return null
    }

    // Get all organization members with their usage data
    const membersWithUsage = await executor
      .select({
        userId: member.userId,
        userName: user.name,
        userEmail: user.email,
        role: member.role,
        joinedAt: member.createdAt,
        // User stats fields
        currentPeriodCost: userStats.currentPeriodCost,
        currentUsageLimit: userStats.currentUsageLimit,
      })
      .from(member)
      .innerJoin(user, eq(member.userId, user.id))
      .leftJoin(userStats, eq(member.userId, userStats.userId))
      .where(eq(member.organizationId, organizationId))

    // Per-member current-period usage = userStats baseline + attributed usage_log
    // rows. currentPeriodCost is no longer incremented on the hot path, so the
    // baseline alone under-reports; add each member's ledger sum for the period.
    const billingPeriod =
      subscription.periodStart && subscription.periodEnd
        ? { start: subscription.periodStart, end: subscription.periodEnd }
        : null
    const usageByUser = await getOrgMemberLedgerByUser(organizationId, billingPeriod, executor)

    // Process member data
    const members: MemberUsageData[] = membersWithUsage.map((memberRecord) => {
      const currentUsage =
        Number(memberRecord.currentPeriodCost || 0) + (usageByUser.get(memberRecord.userId) ?? 0)
      const usageLimit = Number(memberRecord.currentUsageLimit || getFreeTierLimit())