80, 'outcome' => 20, 'status_code' => 40, 'subject_type' => 20, 'ip_hash' => 64, 'user_agent' => self::MAX_USER_AGENT_LENGTH, ); /** Max rows a single query()/count() call may request. */ private const MAX_QUERY_LIMIT = 200; /** Default page size when none is requested. */ private const DEFAULT_QUERY_LIMIT = 50; /** @var \wpdb */ private $wpdb; /** @var string */ private $table; /** * @param \wpdb|null $wpdb Defaults to the global $wpdb. */ public function __construct( ?\wpdb $wpdb = null ) { $this->wpdb = $wpdb instanceof \wpdb ? $wpdb : $GLOBALS['wpdb']; $this->table = $this->wpdb->prefix . 'aipt_mcp_audit_log'; } /** * Fully-qualified table name. * * @return string */ public function table(): string { return $this->table; } /** * Write one audit row. Never throws, and refuses (returns false) * silently-named events rather than letting an unrecognized * `event_type` corrupt the log with free-text values. * * @param array{connection_id?:int,user_id?:int,event_type:string,tool_name?:string,outcome?:string,status_code?:string,request_summary?:array|string|null,subject_type?:string,subject_id?:int,ip_hash?:string,user_agent?:string,duration_ms?:int,now?:string} $event Event fields. * @return bool */ public function write( array $event ): bool { $wpdb = $this->wpdb; $event_type = isset( $event['event_type'] ) ? (string) $event['event_type'] : ''; if ( ! in_array( $event_type, self::EVENT_TYPES, true ) ) { return false; } $now = isset( $event['now'] ) ? (string) $event['now'] : gmdate( 'Y-m-d H:i:s' ); $result = $wpdb->insert( $this->table, array( 'connection_id' => isset( $event['connection_id'] ) ? (int) $event['connection_id'] : 0, 'user_id' => isset( $event['user_id'] ) ? (int) $event['user_id'] : 0, 'event_type' => $event_type, 'tool_name' => self::clamp( $event['tool_name'] ?? '', self::COLUMN_WIDTHS['tool_name'] ), 'outcome' => self::clamp( $event['outcome'] ?? '', self::COLUMN_WIDTHS['outcome'] ), 'status_code' => self::clamp( $event['status_code'] ?? '', self::COLUMN_WIDTHS['status_code'] ), 'request_summary' => $this->encodeSummary( $event['request_summary'] ?? null ), 'subject_type' => self::clamp( $event['subject_type'] ?? '', self::COLUMN_WIDTHS['subject_type'] ), 'subject_id' => isset( $event['subject_id'] ) ? (int) $event['subject_id'] : 0, 'ip_hash' => self::clamp( $event['ip_hash'] ?? '', self::COLUMN_WIDTHS['ip_hash'] ), 'user_agent' => self::clamp( $event['user_agent'] ?? '', self::COLUMN_WIDTHS['user_agent'] ), 'duration_ms' => isset( $event['duration_ms'] ) ? max( 0, (int) $event['duration_ms'] ) : 0, 'created_at' => $now, ), array( '%d', '%d', '%s', '%s', '%s', '%s', '%s', '%s', '%d', '%s', '%s', '%d', '%s' ) ); if ( false === $result ) { $this->logError( __FUNCTION__ ); return false; } return true; } /** * Query audit rows, newest first. * * @param array{connection_id?:int,user_id?:int,event_type?:string,tool_name?:string,outcome?:string,since?:string,until?:string,limit?:int,offset?:int} $filters Filters. * @return array> * @phpstan-return array */ public function query( array $filters ): array { $wpdb = $this->wpdb; list( $where_sql, $params ) = $this->buildWhere( $filters ); $limit = isset( $filters['limit'] ) ? max( 1, min( self::MAX_QUERY_LIMIT, (int) $filters['limit'] ) ) : self::DEFAULT_QUERY_LIMIT; $offset = isset( $filters['offset'] ) ? max( 0, (int) $filters['offset'] ) : 0; $params[] = $limit; $params[] = $offset; $rows = $wpdb->get_results( $wpdb->prepare( "SELECT * FROM {$this->table}{$where_sql} ORDER BY created_at DESC, id DESC LIMIT %d OFFSET %d", $params ), ARRAY_A ); if ( '' !== $wpdb->last_error ) { $this->logError( __FUNCTION__ ); return array(); } return array_map( array( $this, 'mapRow' ), is_array( $rows ) ? $rows : array() ); } /** * Count audit rows matching the same filters {@see query()} accepts * (limit/offset ignored). * * @param array{connection_id?:int,user_id?:int,event_type?:string,tool_name?:string,outcome?:string,since?:string,until?:string} $filters Filters. * @return int 0 on a database error. */ public function count( array $filters ): int { $wpdb = $this->wpdb; list( $where_sql, $params ) = $this->buildWhere( $filters ); $sql = "SELECT COUNT(*) FROM {$this->table}{$where_sql}"; $value = empty( $params ) ? $wpdb->get_var( $sql ) : $wpdb->get_var( $wpdb->prepare( $sql, $params ) ); if ( null === $value ) { $this->logError( __FUNCTION__ ); return 0; } return (int) $value; } /** * Delete rows older than $cutoff, chunked with LIMIT. * * @param string $cutoff UTC datetime; rows created before this are eligible. * @param int $limit Max rows deleted by this call. * @return int Number of rows deleted; 0 on a database error. */ public function deleteOlderThan( string $cutoff, int $limit ): int { $wpdb = $this->wpdb; $deleted = $wpdb->query( $wpdb->prepare( "DELETE FROM {$this->table} WHERE created_at < %s LIMIT %d", $cutoff, max( 1, $limit ) ) ); if ( false === $deleted ) { $this->logError( __FUNCTION__ ); return 0; } return (int) $deleted; } /** * Build a WHERE clause and its bound parameters from the shared * query()/count() filter shape. * * @param array $filters Filters. * @return array{0:string,1:array} */ private function buildWhere( array $filters ): array { $where = array(); $params = array(); $equals = array( 'connection_id' => '%d', 'user_id' => '%d', 'event_type' => '%s', 'tool_name' => '%s', 'outcome' => '%s', ); foreach ( $equals as $field => $format ) { if ( isset( $filters[ $field ] ) && '' !== $filters[ $field ] ) { $where[] = "{$field} = {$format}"; $params[] = '%d' === $format ? (int) $filters[ $field ] : (string) $filters[ $field ]; } } if ( ! empty( $filters['since'] ) ) { $where[] = 'created_at >= %s'; $params[] = (string) $filters['since']; } if ( ! empty( $filters['until'] ) ) { $where[] = 'created_at <= %s'; $params[] = (string) $filters['until']; } list( $connection_ids_clause, $connection_ids_params ) = $this->buildIntInClause( 'connection_id', $filters['connection_ids'] ?? null ); if ( '' !== $connection_ids_clause ) { $where[] = $connection_ids_clause; $params = array_merge( $params, $connection_ids_params ); } list( $tool_names_clause, $tool_names_params ) = $this->buildToolNamesInClause( $filters ); if ( '' !== $tool_names_clause ) { $where[] = $tool_names_clause; $params = array_merge( $params, $tool_names_params ); } list( $search_clause, $search_params ) = $this->buildSearchClause( $filters ); if ( '' !== $search_clause ) { $where[] = $search_clause; $params = array_merge( $params, $search_params ); } $where_sql = empty( $where ) ? '' : ' WHERE ' . implode( ' AND ', $where ); return array( $where_sql, $params ); } /** * A `{$column} IN (%d, %d, ...)` fragment for an int-id filter, built the * same way for every caller that needs one (`connection_ids` here, * `queryForOwner()`/`countForOwner()`'s own connection-id scope group). * Omitted entirely (never an always-false `1=0`) when the list is empty * or absent: every call site that must fail closed on "no ids" (e.g. an * owner with zero connections) checks that before ever reaching the * store, so this stays a plain, reusable SQL builder rather than a * second place that same guarantee has to be kept correct. * * @param string $column Column name (never user input). * @param array|null $ids Candidate ids. * @return array{0:string,1:array} */ private function buildIntInClause( string $column, ?array $ids ): array { if ( empty( $ids ) ) { return array( '', array() ); } $ids = array_values( array_unique( array_map( 'intval', $ids ) ) ); $placeholders = implode( ', ', array_fill( 0, count( $ids ), '%d' ) ); return array( "{$column} IN ({$placeholders})", $ids ); } /** * `tool_name IN (%s, %s, ...)` for the `tool_names` filter (an explicit * allowlist of tool names, e.g. the admin REST surface's "which tools * write to the store" aggregate). Omitted when the list is empty/absent. * * @param array $filters `tool_names` (string[]). * @return array{0:string,1:array} */ private function buildToolNamesInClause( array $filters ): array { if ( empty( $filters['tool_names'] ) || ! is_array( $filters['tool_names'] ) ) { return array( '', array() ); } $names = array_values( array_unique( array_map( 'strval', $filters['tool_names'] ) ) ); if ( empty( $names ) ) { return array( '', array() ); } $placeholders = implode( ', ', array_fill( 0, count( $names ), '%s' ) ); return array( "tool_name IN ({$placeholders})", $names ); } /** * The admin REST `search` filter's WHERE group: a row matches when its * `tool_name` or stored `request_summary` text contains the search text, * OR its `subject_id` is one of the product ids the caller already * resolved from a title match (the controller's job, not this store's -- * this class only ever sees ids, never touches `wp_posts`). Returns `''` * when `search` is empty; the `subject_id IN (...)` branch is left out * entirely when `subject_ids` is empty (no product title matched), so a * search with no product hits still matches on tool name/summary alone. * * @param array $filters `search` (already trimmed/length-capped by the controller) plus `subject_ids` (int[]). * @return array{0:string,1:array} */ private function buildSearchClause( array $filters ): array { $search = isset( $filters['search'] ) ? trim( (string) $filters['search'] ) : ''; if ( '' === $search ) { return array( '', array() ); } $wpdb = $this->wpdb; $like = '%' . $wpdb->esc_like( $search ) . '%'; $parts = array( 'tool_name LIKE %s', 'request_summary LIKE %s' ); $params = array( $like, $like ); $subject_ids = isset( $filters['subject_ids'] ) && is_array( $filters['subject_ids'] ) ? array_values( array_unique( array_map( 'intval', $filters['subject_ids'] ) ) ) : array(); if ( ! empty( $subject_ids ) ) { $placeholders = implode( ', ', array_fill( 0, count( $subject_ids ), '%d' ) ); $parts[] = "subject_id IN ({$placeholders})"; $params = array_merge( $params, $subject_ids ); } return array( '(' . implode( ' OR ', $parts ) . ')', $params ); } /** * @param mixed $value Raw field value. * @param int $width Column width in characters. * @return string */ private static function clamp( $value, int $width ): string { $value = is_scalar( $value ) ? (string) $value : ''; return function_exists( 'mb_substr' ) ? mb_substr( $value, 0, $width ) : substr( $value, 0, $width ); } /** * Encode request_summary for storage: arrays become JSON, everything * else is cast to string, and the result is hard-capped at * {@see MAX_SUMMARY_BYTES} bytes so a caller's redaction mistake can * never grow this column unbounded. * * @param array|string|null $summary Raw summary. * @return string|null */ private function encodeSummary( $summary ) { if ( null === $summary ) { return null; } $encoded = is_array( $summary ) ? (string) wp_json_encode( $summary ) : (string) $summary; if ( strlen( $encoded ) > self::MAX_SUMMARY_BYTES ) { if ( function_exists( 'mb_strcut' ) ) { $encoded = mb_strcut( $encoded, 0, self::MAX_SUMMARY_BYTES, 'UTF-8' ); } else { $encoded = substr( $encoded, 0, self::MAX_SUMMARY_BYTES ); } } return $encoded; } /** * Normalize a raw row. * * @param array $row Raw row from $wpdb. * @return array * @phpstan-return McpAuditLogRow */ private function mapRow( array $row ): array { return array( 'id' => (int) ( $row['id'] ?? 0 ), 'connection_id' => (int) ( $row['connection_id'] ?? 0 ), 'user_id' => (int) ( $row['user_id'] ?? 0 ), 'event_type' => (string) ( $row['event_type'] ?? '' ), 'tool_name' => (string) ( $row['tool_name'] ?? '' ), 'outcome' => (string) ( $row['outcome'] ?? '' ), 'status_code' => (string) ( $row['status_code'] ?? '' ), 'request_summary' => isset( $row['request_summary'] ) ? (string) $row['request_summary'] : null, 'subject_type' => (string) ( $row['subject_type'] ?? '' ), 'subject_id' => (int) ( $row['subject_id'] ?? 0 ), 'ip_hash' => (string) ( $row['ip_hash'] ?? '' ), 'user_agent' => (string) ( $row['user_agent'] ?? '' ), 'duration_ms' => (int) ( $row['duration_ms'] ?? 0 ), 'created_at' => (string) ( $row['created_at'] ?? '' ), ); } /** * @param string $method Calling method name, for the log line. * @return void */ private function logError( string $method ): void { $wpdb = $this->wpdb; } /** * Query audit rows visible to a non-`manage_options` admin REST caller: * rows of a connection they own, plus their own account-level events (an * auth attempt or similar row written before any connection could be * resolved, `connection_id = 0`). Every other filter {@see query()} * accepts still applies on top of this scope. Added for the admin REST * surface (`aipt/v1/mcp-admin/log`); `query()`/`count()` stay the * unscoped, `manage_options` path. * * @param int $userId Caller's own user id. * @param array $connectionIds Connection ids the caller owns. * @param array $filters Same filter shape as {@see query()} (`connection_id`/`event_type`/`tool_name`/`outcome`, plus `limit`/`offset`). * @return array> * @phpstan-return array */ public function queryForOwner( int $userId, array $connectionIds, array $filters ): array { $wpdb = $this->wpdb; list( $where_sql, $params ) = $this->buildOwnerWhere( $userId, $connectionIds, $filters ); $limit = isset( $filters['limit'] ) ? max( 1, min( self::MAX_QUERY_LIMIT, (int) $filters['limit'] ) ) : self::DEFAULT_QUERY_LIMIT; $offset = isset( $filters['offset'] ) ? max( 0, (int) $filters['offset'] ) : 0; $params[] = $limit; $params[] = $offset; $rows = $wpdb->get_results( $wpdb->prepare( "SELECT * FROM {$this->table}{$where_sql} ORDER BY created_at DESC, id DESC LIMIT %d OFFSET %d", $params ), ARRAY_A ); if ( '' !== $wpdb->last_error ) { $this->logError( __FUNCTION__ ); return array(); } return array_map( array( $this, 'mapRow' ), is_array( $rows ) ? $rows : array() ); } /** * Count audit rows matching the same owner scope and filters {@see queryForOwner()} accepts (`limit`/`offset` ignored). * * @param int $userId Caller's own user id. * @param array $connectionIds Connection ids the caller owns. * @param array $filters Same filter shape as {@see queryForOwner()}. * @return int 0 on a database error. */ public function countForOwner( int $userId, array $connectionIds, array $filters ): int { $wpdb = $this->wpdb; list( $where_sql, $params ) = $this->buildOwnerWhere( $userId, $connectionIds, $filters ); $sql = "SELECT COUNT(*) FROM {$this->table}{$where_sql}"; $value = empty( $params ) ? $wpdb->get_var( $sql ) : $wpdb->get_var( $wpdb->prepare( $sql, $params ) ); if ( null === $value ) { $this->logError( __FUNCTION__ ); return 0; } return (int) $value; } /** * Build the WHERE clause and bound parameters {@see queryForOwner()}/ * {@see countForOwner()} share: an `(connection_id IN (...) OR (user_id * = %d AND connection_id = 0))` scope group, ANDed with any of the * equals filters {@see buildWhere()} also supports (deliberately not * reused from there: that method has no OR-group concept and is kept * exactly as the tool-execution pipeline already depends on it). * * @param int $userId Caller's own user id. * @param array $connectionIds Connection ids the caller owns. * @param array $filters `connection_id`/`event_type`/`tool_name`/`outcome`. * @return array{0:string,1:array} */ private function buildOwnerWhere( int $userId, array $connectionIds, array $filters ): array { $connection_ids = array_values( array_unique( array_map( 'intval', $connectionIds ) ) ); $scope_parts = array(); $scope_params = array(); if ( ! empty( $connection_ids ) ) { $placeholders = implode( ', ', array_fill( 0, count( $connection_ids ), '%d' ) ); $scope_parts[] = "connection_id IN ({$placeholders})"; $scope_params = array_merge( $scope_params, $connection_ids ); } $scope_parts[] = '(user_id = %d AND connection_id = 0)'; $scope_params[] = $userId; $where = array( '(' . implode( ' OR ', $scope_parts ) . ')' ); $params = $scope_params; $equals = array( 'connection_id' => '%d', 'event_type' => '%s', 'tool_name' => '%s', 'outcome' => '%s', ); foreach ( $equals as $field => $format ) { if ( isset( $filters[ $field ] ) && '' !== $filters[ $field ] ) { $where[] = "{$field} = {$format}"; $params[] = '%d' === $format ? (int) $filters[ $field ] : (string) $filters[ $field ]; } } if ( ! empty( $filters['since'] ) ) { $where[] = 'created_at >= %s'; $params[] = (string) $filters['since']; } if ( ! empty( $filters['until'] ) ) { $where[] = 'created_at <= %s'; $params[] = (string) $filters['until']; } list( $tool_names_clause, $tool_names_params ) = $this->buildToolNamesInClause( $filters ); if ( '' !== $tool_names_clause ) { $where[] = $tool_names_clause; $params = array_merge( $params, $tool_names_params ); } list( $search_clause, $search_params ) = $this->buildSearchClause( $filters ); if ( '' !== $search_clause ) { $where[] = $search_clause; $params = array_merge( $params, $search_params ); } return array( ' WHERE ' . implode( ' AND ', $where ), $params ); } /** * Count the distinct connections with at least one matching row (the * admin REST `overview` route's `agents_active_today`). Scoping to one * caller's own connections is the caller's job: pass `connection_ids` * (never the owner-scope OR-group {@see buildOwnerWhere()} builds) -- * every row this counts is a `tool_call` event, which always carries a * real `connection_id` (never 0), so a plain `IN` filter already matches * {@see queryForOwner()}'s scope for this event type without needing the * "my own zero-connection auth events" branch that scope also carries * (an auth event has no tool and would never belong in a distinct * connection count anyway). * * @param array $filters Same filter shape as {@see count()}, plus optionally `connection_ids` (int[]). * @return int 0 on a database error. */ public function countDistinctConnections( array $filters ): int { $wpdb = $this->wpdb; list( $where_sql, $params ) = $this->buildWhere( $filters ); $sql = "SELECT COUNT(DISTINCT connection_id) FROM {$this->table}{$where_sql}"; $value = empty( $params ) ? $wpdb->get_var( $sql ) : $wpdb->get_var( $wpdb->prepare( $sql, $params ) ); if ( null === $value ) { $this->logError( __FUNCTION__ ); return 0; } return (int) $value; } /** * The most recent `tool_call` row for each of a page of connection ids, * in one query (no N+1): an `INNER JOIN` against a per-connection * `MAX(id)` subquery, so the admin REST `GET connections` route can * attach every row's `last_activity` without one lookup per connection. * * @param array $connectionIds Connection ids on the current page. * @return array> Keyed by connection_id; a connection with no tool_call row is simply absent. * @phpstan-return array */ public function latestToolCallForConnections( array $connectionIds ): array { $ids = array_values( array_unique( array_map( 'intval', $connectionIds ) ) ); if ( empty( $ids ) ) { return array(); } $wpdb = $this->wpdb; $placeholders = implode( ', ', array_fill( 0, count( $ids ), '%d' ) ); $params = $ids; $params[] = self::EVENT_TOOL_CALL; $rows = $wpdb->get_results( $wpdb->prepare( "SELECT a.* FROM {$this->table} a INNER JOIN ( SELECT connection_id, MAX(id) AS max_id FROM {$this->table} WHERE connection_id IN ({$placeholders}) AND event_type = %s GROUP BY connection_id ) latest ON a.connection_id = latest.connection_id AND a.id = latest.max_id", $params ), ARRAY_A ); if ( '' !== $wpdb->last_error ) { $this->logError( __FUNCTION__ ); return array(); } $by_connection = array(); foreach ( array_map( array( $this, 'mapRow' ), is_array( $rows ) ? $rows : array() ) as $row ) { $by_connection[ $row['connection_id'] ] = $row; } return $by_connection; } }