agent-sync/export_shared_agents.js

// export_shared_agents.js
// Emits strict Extended JSON to stdout (safe JSON for ObjectId/Date)

const pipeline = [
  { $match: { resourceType: "agent" } },
  { $group: { _id: "$resourceId", count: { $sum: 1 } } },
  { $match: { count: { $gt: 1 } } },
  {
    $lookup: {
      from: "agents",
      localField: "_id",
      foreignField: "_id",
      as: "agentDetails"
    }
  },
  { $unwind: "$agentDetails" },
  {
    $lookup: {
      from: "users",
      localField: "agentDetails.author",
      foreignField: "_id",
      as: "authorDetails"
    }
  },
  { $unwind: { path: "$authorDetails", preserveNullAndEmptyArrays: true } },
  { $set: { "agentDetails.author": "$authorDetails.email" } },

  // NEW: Get usage timeline - daily message counts
  {
    $lookup: {
      from: "conversations",
      let: { agentId: "$agentDetails.id" },
      pipeline: [
        {
          $match: {
            $expr: { $eq: ["$agent_id", "$$agentId"] },
            endpoint: "agents"
          }
        },
        // Unwind messages array to get individual message ObjectIds
        { $unwind: "$messages" },
        // Lookup actual message documents
        {
          $lookup: {
            from: "messages",
            localField: "messages",
            foreignField: "_id",
            as: "messageDoc"
          }
        },
        { $unwind: "$messageDoc" },
        // Group by date to count messages per day
        {
          $group: {
            _id: {
              $dateToString: {
                format: "%Y-%m-%d",
                date: "$messageDoc.createdAt"
              }
            },
            messageCount: { $sum: 1 }
          }
        },
        // Sort by date
        { $sort: { _id: 1 } },
        // Reshape to nice format
        {
          $project: {
            _id: 0,
            date: "$_id",
            message_count: "$messageCount"
          }
        }
      ],
      as: "usageTimeline"
    }
  },

  // NEW: Get usage summary stats
  {
    $lookup: {
      from: "conversations",
      let: { agentId: "$agentDetails.id" },
      pipeline: [
        {
          $match: {
            $expr: { $eq: ["$agent_id", "$$agentId"] },
            endpoint: "agents"
          }
        },
        {
          $group: {
            _id: null,
            conversationCount: { $sum: 1 },
            uniqueUsers: { $addToSet: "$user" },
            totalMessages: { $sum: { $size: "$messages" } },
            firstUsed: { $min: "$createdAt" },
            lastUsed: { $max: "$updatedAt" }
          }
        }
      ],
      as: "usageSummary"
    }
  },
  { $unwind: { path: "$usageSummary", preserveNullAndEmptyArrays: true } },

  // NEW: Get token usage from transactions collection
  {
    $lookup: {
      from: "conversations",
      let: { agentId: "$agentDetails.id" },
      pipeline: [
        {
          $match: {
            $expr: { $eq: ["$agent_id", "$$agentId"] },
            endpoint: "agents"
          }
        },
        // Lookup transactions for each conversation
        {
          $lookup: {
            from: "transactions",
            localField: "conversationId",
            foreignField: "conversationId",
            as: "txns"
          }
        },
        { $unwind: "$txns" },
        // Aggregate token totals across all conversations
        {
          $group: {
            _id: null,
            prompt_tokens: {
              $sum: {
                $cond: [
                  { $eq: ["$txns.tokenType", "prompt"] },
                  { $abs: "$txns.rawAmount" },
                  0
                ]
              }
            },
            completion_tokens: {
              $sum: {
                $cond: [
                  { $eq: ["$txns.tokenType", "completion"] },
                  { $abs: "$txns.rawAmount" },
                  0
                ]
              }
            },
            total_tokens: {
              $sum: { $abs: "$txns.rawAmount" }
            }
          }
        }
      ],
      as: "tokenUsage"
    }
  },
  { $unwind: { path: "$tokenUsage", preserveNullAndEmptyArrays: true } },

  // Add usage data to agentDetails
  {
    $set: {
      "agentDetails.usage_timeline": "$usageTimeline",
      "agentDetails.usage_summary": {
        conversation_count: { $ifNull: ["$usageSummary.conversationCount", 0] },
        unique_users: {
          $cond: {
            if: { $isArray: "$usageSummary.uniqueUsers" },
            then: { $size: "$usageSummary.uniqueUsers" },
            else: 0
          }
        },
        total_messages: { $ifNull: ["$usageSummary.totalMessages", 0] },
        first_used: {
          $cond: {
            if: { $ne: ["$usageSummary.firstUsed", null] },
            then: { $dateToString: { format: "%Y-%m-%dT%H:%M:%S.%LZ", date: "$usageSummary.firstUsed" } },
            else: null
          }
        },
        last_used: {
          $cond: {
            if: { $ne: ["$usageSummary.lastUsed", null] },
            then: { $dateToString: { format: "%Y-%m-%dT%H:%M:%S.%LZ", date: "$usageSummary.lastUsed" } },
            else: null
          }
        }
      },
      "agentDetails.token_usage": {
        prompt_tokens: { $ifNull: ["$tokenUsage.prompt_tokens", 0] },
        completion_tokens: { $ifNull: ["$tokenUsage.completion_tokens", 0] },
        total_tokens: { $ifNull: ["$tokenUsage.total_tokens", 0] }
      }
    }
  },

  { $project: {
      "agentDetails.versions": 0,
      "authorDetails": 0,
      "usageTimeline": 0,
      "usageSummary": 0,
      "tokenUsage": 0
    }
  }
];

const out = db.aclentries.aggregate(pipeline).toArray();
print(EJSON.stringify(out, { relaxed: true, indent: 2 }));