*/ private static function legacy_table_suffixes(): array { return array( 'aivalve_log', 'ai_valve_log', ); } /** * @return list */ private static function legacy_version_keys(): array { return array( 'aivalve_db_version', 'ai_valve_db_version', ); } /* ------------------------------------------------------------------ * Table name helper * ----------------------------------------------------------------*/ public static function table_name(): string { global $wpdb; return $wpdb->prefix . self::TABLE_SUFFIX; } /** * @return list */ private static function legacy_table_names(): array { global $wpdb; return array_map( static fn( string $suffix ): string => $wpdb->prefix . $suffix, self::legacy_table_suffixes() ); } /* ------------------------------------------------------------------ * Activation — create / upgrade table * ----------------------------------------------------------------*/ public static function activate(): void { self::run_migrations(); } /** * Run schema migrations if the stored version is behind. * * Called from Plugin::register() on every request so that in-place * file updates (without deactivate/activate) still apply migrations. * The version check makes this a no-op on most requests. */ public static function maybe_upgrade(): void { $current_version = get_option( self::VERSION_KEY, false ); if ( false === $current_version ) { self::run_migrations(); return; } if ( (int) $current_version < self::SCHEMA_VERSION ) { self::run_migrations(); } } private static function run_migrations(): void { global $wpdb; self::maybe_rename_legacy_table(); $table = self::table_name(); $charset = $wpdb->get_charset_collate(); $sql = "CREATE TABLE {$table} ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, plugin_slug VARCHAR(191) NOT NULL DEFAULT '', provider_id VARCHAR(191) NOT NULL DEFAULT '', model_id VARCHAR(191) NOT NULL DEFAULT '', capability VARCHAR(64) NOT NULL DEFAULT '', context VARCHAR(32) NOT NULL DEFAULT '', prompt_tokens INT UNSIGNED NOT NULL DEFAULT 0, completion_tokens INT UNSIGNED NOT NULL DEFAULT 0, total_tokens INT UNSIGNED NOT NULL DEFAULT 0, duration_ms INT UNSIGNED NOT NULL DEFAULT 0, status VARCHAR(64) NOT NULL DEFAULT 'allowed', created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_plugin_slug (plugin_slug), KEY idx_provider_id (provider_id), KEY idx_created_at (created_at), KEY idx_status (status) ) {$charset};"; require_once ABSPATH . 'wp-admin/includes/upgrade.php'; dbDelta( $sql ); $current_version = get_option( self::VERSION_KEY, false ); if ( false === $current_version ) { foreach ( self::legacy_version_keys() as $legacy_version_key ) { $current_version = get_option( $legacy_version_key, false ); if ( false !== $current_version ) { break; } } } $current_version = (int) $current_version; // v1 → v2: widen status column. if ( $current_version < 2 ) { $wpdb->query( "ALTER TABLE {$table} MODIFY status VARCHAR(64) NOT NULL DEFAULT 'allowed'" ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared } // v2 → v3: add duration_ms column. if ( $current_version < 3 ) { $col = $wpdb->get_var( $wpdb->prepare( 'SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = %s AND TABLE_NAME = %s AND COLUMN_NAME = %s', DB_NAME, $table, 'duration_ms' ) ); if ( null === $col ) { $wpdb->query( "ALTER TABLE {$table} ADD COLUMN duration_ms INT UNSIGNED NOT NULL DEFAULT 0 AFTER total_tokens" ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared } } update_option( self::VERSION_KEY, self::SCHEMA_VERSION, true ); foreach ( self::legacy_version_keys() as $legacy_version_key ) { delete_option( $legacy_version_key ); } } private static function maybe_rename_legacy_table(): void { global $wpdb; $table = self::table_name(); foreach ( self::legacy_table_names() as $legacy_table ) { if ( $table === $legacy_table ) { continue; } $table_exists = $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $table ) ); $legacy_exists = $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $legacy_table ) ); if ( ! $table_exists && $legacy_exists ) { $wpdb->query( "RENAME TABLE {$legacy_table} TO {$table}" ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared break; } } } /* ------------------------------------------------------------------ * Insert * ----------------------------------------------------------------*/ /** * @param array{ * plugin_slug: string, * provider_id: string, * model_id: string, * capability: string, * context: string, * prompt_tokens: int, * completion_tokens: int, * total_tokens: int, * status: string, * } $row */ public function insert( array $row ): int|false { global $wpdb; $defaults = array( 'plugin_slug' => '', 'provider_id' => '', 'model_id' => '', 'capability' => '', 'context' => '', 'prompt_tokens' => 0, 'completion_tokens' => 0, 'total_tokens' => 0, 'duration_ms' => 0, 'status' => 'allowed', ); $data = array_merge( $defaults, array_intersect_key( $row, $defaults ) ); $result = $wpdb->insert( self::table_name(), $data, array( '%s', // plugin_slug '%s', // provider_id '%s', // model_id '%s', // capability '%s', // context '%d', // prompt_tokens '%d', // completion_tokens '%d', // total_tokens '%d', // duration_ms '%s', // status ) ); return false === $result ? false : (int) $wpdb->insert_id; } /* ------------------------------------------------------------------ * Update * ----------------------------------------------------------------*/ /** * Update an existing log row by ID. * * @param int $id Row ID to update. * @param array $data Column => value pairs to set. * @return bool True on success, false on failure. */ public function update( int $id, array $data ): bool { global $wpdb; $allowed = array( 'plugin_slug' => '%s', 'provider_id' => '%s', 'model_id' => '%s', 'capability' => '%s', 'context' => '%s', 'prompt_tokens' => '%d', 'completion_tokens' => '%d', 'total_tokens' => '%d', 'duration_ms' => '%d', 'status' => '%s', ); $set = array_intersect_key( $data, $allowed ); $formats = array_map( static fn( string $col ) => $allowed[ $col ], array_keys( $set ) ); if ( empty( $set ) ) { return false; } return (bool) $wpdb->update( self::table_name(), $set, array( 'id' => $id ), $formats, array( '%d' ) ); } /* ------------------------------------------------------------------ * Queries * ----------------------------------------------------------------*/ /** * Paginated log entries. * * @param array $filters Associative filter params. * @return array{ items: list, total: int } */ public function query( array $filters = array() ): array { global $wpdb; $table = self::table_name(); $where = array(); $values = array(); $per_page = max( 1, min( 100, (int) ( $filters['per_page'] ?? 20 ) ) ); $page = max( 1, (int) ( $filters['page'] ?? 1 ) ); $offset = ( $page - 1 ) * $per_page; if ( ! empty( $filters['plugin_slug'] ) ) { $where[] = 'plugin_slug = %s'; $values[] = $filters['plugin_slug']; } if ( ! empty( $filters['provider_id'] ) ) { $where[] = 'provider_id = %s'; $values[] = $filters['provider_id']; } if ( ! empty( $filters['model_id'] ) ) { $where[] = 'model_id = %s'; $values[] = $filters['model_id']; } if ( ! empty( $filters['context'] ) ) { $where[] = 'context = %s'; $values[] = $filters['context']; } if ( ! empty( $filters['status'] ) ) { if ( 'denied' === $filters['status'] ) { $where[] = 'status LIKE %s'; $values[] = 'denied%'; } else { $where[] = 'status = %s'; $values[] = $filters['status']; } } if ( ! empty( $filters['date_from'] ) ) { $where[] = 'created_at >= %s'; $values[] = $filters['date_from']; } if ( ! empty( $filters['date_to'] ) ) { $where[] = 'created_at <= %s'; $values[] = $filters['date_to']; } $where_sql = $where ? 'WHERE ' . implode( ' AND ', $where ) : ''; // Build the count query. $count_sql = "SELECT COUNT(*) FROM {$table} {$where_sql}"; if ( $values ) { $count_sql = $wpdb->prepare( $count_sql, ...$values ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared } $total = (int) $wpdb->get_var( $count_sql ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared // Build the data query. $data_sql = "SELECT * FROM {$table} {$where_sql} ORDER BY created_at DESC LIMIT %d OFFSET %d"; $all_vals = array_merge( $values, array( $per_page, $offset ) ); $data_sql = $wpdb->prepare( $data_sql, ...$all_vals ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared $items = $wpdb->get_results( $data_sql ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared return array( 'items' => $items ? $items : array(), 'total' => $total, ); } /* ------------------------------------------------------------------ * Aggregation helpers * ----------------------------------------------------------------*/ /** * Token totals for a given date range. * * @return array{ prompt_tokens: int, completion_tokens: int, total_tokens: int, request_count: int } */ public function totals( string $from, string $to, string $plugin_slug = '' ): array { global $wpdb; $table = self::table_name(); $where = 'WHERE created_at >= %s AND created_at <= %s AND status = %s'; $vals = array( $from, $to, 'allowed' ); if ( '' !== $plugin_slug ) { $where .= ' AND plugin_slug = %s'; $vals[] = $plugin_slug; } $sql = $wpdb->prepare( "SELECT COALESCE( SUM(prompt_tokens), 0 ) AS prompt_tokens, COALESCE( SUM(completion_tokens), 0 ) AS completion_tokens, COALESCE( SUM(total_tokens), 0 ) AS total_tokens, COUNT(*) AS request_count FROM {$table} {$where}", ...$vals ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared $row = $wpdb->get_row( $sql, ARRAY_A ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared return array( 'prompt_tokens' => (int) ( $row['prompt_tokens'] ?? 0 ), 'completion_tokens' => (int) ( $row['completion_tokens'] ?? 0 ), 'total_tokens' => (int) ( $row['total_tokens'] ?? 0 ), 'request_count' => (int) ( $row['request_count'] ?? 0 ), ); } /** * Token usage grouped by plugin slug for a date range. * * @return list */ public function totals_by_plugin( string $from, string $to ): array { global $wpdb; $table = self::table_name(); $sql = $wpdb->prepare( "SELECT plugin_slug, COALESCE( SUM(total_tokens), 0 ) AS total_tokens, COUNT(*) AS request_count FROM {$table} WHERE created_at >= %s AND created_at <= %s AND status = %s GROUP BY plugin_slug ORDER BY total_tokens DESC", $from, $to, 'allowed' ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared $rows = $wpdb->get_results( $sql, ARRAY_A ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared return array_map( static fn( array $r ) => array( 'plugin_slug' => $r['plugin_slug'], 'total_tokens' => (int) $r['total_tokens'], 'request_count' => (int) $r['request_count'], ), $rows ? $rows : array() ); } /** * Token usage grouped by context for a date range. * * @return list */ public function totals_by_context( string $from, string $to ): array { global $wpdb; $table = self::table_name(); $sql = $wpdb->prepare( "SELECT context, COALESCE( SUM(total_tokens), 0 ) AS total_tokens, COUNT(*) AS request_count FROM {$table} WHERE created_at >= %s AND created_at <= %s AND status = %s GROUP BY context ORDER BY total_tokens DESC", $from, $to, 'allowed' ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared $rows = $wpdb->get_results( $sql, ARRAY_A ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared return array_map( static fn( array $r ) => array( 'context' => $r['context'], 'total_tokens' => (int) $r['total_tokens'], 'request_count' => (int) $r['request_count'], ), $rows ? $rows : array() ); } /** * Token usage grouped by provider for a date range. * * @return list */ public function totals_by_provider( string $from, string $to ): array { global $wpdb; $table = self::table_name(); $sql = $wpdb->prepare( "SELECT provider_id, COALESCE( SUM(total_tokens), 0 ) AS total_tokens, COUNT(*) AS request_count FROM {$table} WHERE created_at >= %s AND created_at <= %s AND status = %s GROUP BY provider_id ORDER BY total_tokens DESC", $from, $to, 'allowed' ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared $rows = $wpdb->get_results( $sql, ARRAY_A ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared return array_map( static fn( array $r ) => array( 'provider_id' => $r['provider_id'], 'total_tokens' => (int) $r['total_tokens'], 'request_count' => (int) $r['request_count'], ), $rows ? $rows : array() ); } /** * Token usage grouped by provider and model for a date range. * * @return list */ public function totals_by_provider_model( string $from, string $to ): array { global $wpdb; $table = self::table_name(); $sql = $wpdb->prepare( "SELECT provider_id, model_id, COALESCE( SUM(total_tokens), 0 ) AS total_tokens, COUNT(*) AS request_count FROM {$table} WHERE created_at >= %s AND created_at <= %s AND status = %s GROUP BY provider_id, model_id ORDER BY total_tokens DESC", $from, $to, 'allowed' ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared $rows = $wpdb->get_results( $sql, ARRAY_A ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared return array_map( static fn( array $r ) => array( 'provider_id' => $r['provider_id'], 'model_id' => $r['model_id'], 'total_tokens' => (int) $r['total_tokens'], 'request_count' => (int) $r['request_count'], ), $rows ? $rows : array() ); } /* ------------------------------------------------------------------ * Distinct filter values * ----------------------------------------------------------------*/ /** * Return distinct non-empty values for the filterable columns. * * @return array{ plugins: list, providers: list, models: list } */ public function distinct_filter_values(): array { global $wpdb; $table = self::table_name(); return array( 'plugins' => $wpdb->get_col( "SELECT DISTINCT plugin_slug FROM {$table} WHERE plugin_slug != '' ORDER BY plugin_slug" ), // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared 'providers' => $wpdb->get_col( "SELECT DISTINCT provider_id FROM {$table} WHERE provider_id != '' ORDER BY provider_id" ), // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared 'models' => $wpdb->get_col( "SELECT DISTINCT model_id FROM {$table} WHERE model_id != '' ORDER BY model_id" ), // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared ); } /* ------------------------------------------------------------------ * Cleanup * ----------------------------------------------------------------*/ /** * Delete all rows from the log table. */ public function purge(): int { global $wpdb; $table = self::table_name(); return (int) $wpdb->query( "TRUNCATE TABLE {$table}" ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared } /** * Delete rows older than the given number of days. */ public function delete_older_than( int $days ): int { global $wpdb; $table = self::table_name(); $cutoff = gmdate( 'Y-m-d H:i:s', time() - ( $days * DAY_IN_SECONDS ) ); return (int) $wpdb->query( $wpdb->prepare( "DELETE FROM {$table} WHERE created_at < %s", // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared $cutoff ) ); } /** * Drop the table and remove the schema version option. */ public static function uninstall(): void { global $wpdb; foreach ( array_merge( array( self::table_name() ), self::legacy_table_names() ) as $table ) { $wpdb->query( "DROP TABLE IF EXISTS {$table}" ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared } delete_option( self::VERSION_KEY ); foreach ( self::legacy_version_keys() as $legacy_version_key ) { delete_option( $legacy_version_key ); } } }