Membuat Dashboard Admin

Terakhir diperbarui: 28 Jul 2026

Tabel Usage Statistics

CREATE TABLE agent_usage_logs (
    id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id         BIGINT UNSIGNED NULL,
    session_id      VARCHAR(128) NOT NULL,
    task            TEXT NOT NULL,
    tools_used      JSON NULL,
    prompt_tokens   INT DEFAULT 0,
    completion_tokens INT DEFAULT 0,
    total_tokens    INT DEFAULT 0,
    duration_ms     INT DEFAULT 0,
    is_success      TINYINT(1) DEFAULT 1,
    error_message   TEXT NULL,
    created_at      DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_user_id (user_id),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB;

Query Statistik untuk Dashboard

<?php

namespace App\Admin;

class DashboardService
{
    private PDO $pdo;

    public function __construct(PDO $pdo)
    {
        $this->pdo = $pdo;
    }

    public function getSummary(): array
    {
        $stmt = $this->pdo->query(
            'SELECT
                COUNT(*)                        AS total_requests,
                SUM(total_tokens)               AS total_tokens_used,
                AVG(total_tokens)               AS avg_tokens_per_request,
                SUM(duration_ms) / 1000         AS total_seconds,
                AVG(duration_ms)                AS avg_response_ms,
                SUM(CASE WHEN is_success = 1 THEN 1 ELSE 0 END) AS success_count,
                SUM(CASE WHEN is_success = 0 THEN 1 ELSE 0 END) AS error_count
             FROM agent_usage_logs
             WHERE created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)'
        );
        return $stmt->fetch(PDO::FETCH_ASSOC) ?: [];
    }

    public function getDailyStats(int $days = 7): array
    {
        $stmt = $this->pdo->prepare(
            'SELECT
                DATE(created_at) AS date,
                COUNT(*)          AS requests,
                SUM(total_tokens) AS tokens_used,
                AVG(duration_ms)  AS avg_ms
             FROM agent_usage_logs
             WHERE created_at >= DATE_SUB(NOW(), INTERVAL ? DAY)
             GROUP BY DATE(created_at)
             ORDER BY date ASC'
        );
        $stmt->execute([$days]);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    public function getMostUsedTools(): array
    {
        $stmt = $this->pdo->query(
            'SELECT tool_name, COUNT(*) AS usage_count
             FROM (
                 SELECT JSON_UNQUOTE(JSON_EXTRACT(tools_used, CONCAT("$[", n.n, "]"))) AS tool_name
                 FROM agent_usage_logs,
                      (SELECT 0 AS n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) n
                 WHERE JSON_LENGTH(tools_used) > n.n
             ) t
             WHERE tool_name IS NOT NULL AND tool_name != "null"
             GROUP BY tool_name
             ORDER BY usage_count DESC
             LIMIT 10'
        );
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    public function getRecentConversations(int $limit = 20): array
    {
        $stmt = $this->pdo->prepare(
            'SELECT l.*, u.name AS user_name, u.email
             FROM agent_usage_logs l
             LEFT JOIN users u ON l.user_id = u.id
             ORDER BY l.created_at DESC
             LIMIT ?'
        );
        $stmt->execute([$limit]);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }
}

Estimasi Biaya API

// Harga per 1M token (USD) - cek openai.com/pricing untuk harga terkini
$pricing = [
    'gpt-4o-mini'   => ['input' => 0.15, 'output' => 0.60],
    'gpt-4o'        => ['input' => 5.00, 'output' => 15.00],
    'gpt-3.5-turbo' => ['input' => 0.50, 'output' => 1.50],
];

function estimateCost(int $promptTokens, int $completionTokens, string $model): float
{
    global $pricing;
    $price = $pricing[$model] ?? ['input' => 0, 'output' => 0];
    $cost  = ($promptTokens / 1_000_000 * $price['input'])
           + ($completionTokens / 1_000_000 * $price['output']);
    return round($cost, 6);
}