settings; if ( ! $settings->get( 'supabase_logging', 0 ) ) { return false; } return (int) $settings->get( 'supabase_logging_attempts', 0 ) < self::MAX_ATTEMPTS; } /** * Whether the address and key needed for the mirror are present. * * The mirror reuses the vector store credentials on purpose: the same project already * holds the knowledge base, and its table needs the same `service_role` key, because RLS * is on with no policy. * * @return bool */ public static function configured() { $settings = mybot()->settings; return '' !== trim( (string) $settings->get( 'vector_url', '' ) ) && '' !== trim( (string) $settings->secret( 'vector_key' ) ); } /** * Everything the admin screens need to describe the mirror. * * Exists because this feature failed in the worst possible way (3.28): the box was ticked, * the vector store worked and every surface looked configured, while the counter that had * reached its limit kept the mirror switched off in silence. A state that cannot be read * cannot be shown, and a state that cannot be shown is not fixed. * * @return array */ public static function state() { $settings = mybot()->settings; return array( 'wanted' => (bool) $settings->get( 'supabase_logging', 0 ), 'configured' => self::configured(), 'enabled' => self::enabled(), 'attempts' => (int) $settings->get( 'supabase_logging_attempts', 0 ), 'error' => (string) $settings->get( 'supabase_logging_error', '' ), 'last_ok' => (int) $settings->get( 'supabase_logging_ok_at', 0 ), 'max' => self::MAX_ATTEMPTS, ); } /** * One sentence describing the mirror, for the Status card. * * @return string */ public static function describe() { $state = self::state(); if ( ! $state['wanted'] ) { return __( 'Off. Tick "Also copy conversations to Supabase" on the RAG tab to switch it on.', 'aihouse' ); } if ( ! $state['configured'] ) { return __( 'On, but the vector store URL or API key above is missing, so nothing can be copied.', 'aihouse' ); } if ( ! $state['enabled'] ) { return sprintf( /* translators: %d: number of consecutive failures */ __( 'Switched itself off after %d failures in a row. Untick and tick the option, then press Save, to start it again.', 'aihouse' ), $state['attempts'] ); } if ( $state['last_ok'] > 0 ) { return sprintf( /* translators: %s: human readable time difference, for example "5 mins" */ __( 'On. Last successful copy %s ago.', 'aihouse' ), human_time_diff( $state['last_ok'], time() ) ); } return __( 'On, but no message has been copied yet. Press Test connection above: it writes a probe row to public.mybot_messages and removes it again, so a broken table or key shows up without waiting for a visitor.', 'aihouse' ); } /** * Proves the mirror can write, without waiting for a visitor to chat. * * This is the same move that 1.5.2 made for the vector key, and it exists for the same * reason: "the option is on and nothing arrived" is not a diagnosis. The probe writes one * row under a negative ID that no real message can ever have, then deletes it again, and * reports what Supabase actually answered. Runs even when the mirror has switched itself * off, because testing it is exactly what somebody does after fixing the problem. * * @return true|WP_Error */ public static function probe() { $settings = mybot()->settings; if ( ! self::configured() ) { return new WP_Error( 'mybot_supabase_log_unconfigured', __( 'The Supabase URL or API key is missing, so the log mirror cannot be tested.', 'aihouse' ) ); } $base = rtrim( (string) $settings->get( 'vector_url', '' ), '/' ); $key = (string) $settings->secret( 'vector_key' ); $url = $base . '/rest/v1/' . rawurlencode( self::TABLE ); // Negative on purpose: WordPress IDs start at 1, so this row can be removed again // without any chance of touching a real conversation. $probe_id = -1; $response = MYBOT_HTTP::post_json( $url, array( array( 'wp_id' => $probe_id, 'session_id' => '__mybot_log_probe__', 'role' => 'user', 'content' => 'mybot log mirror test', 'lang' => '', 'mode' => 'quick', 'provider' => '', 'model' => '', 'latency_ms' => 0, 'tokens_in' => 0, 'tokens_out' => 0, 'cache_hit' => false, 'rating' => 0, 'created_at' => current_time( 'mysql' ), ), ), array_merge( self::headers( $key ), array( 'Prefer' => 'resolution=ignore-duplicates,return=minimal' ) ), 15 ); if ( is_wp_error( $response ) ) { $status = $response->get_error_data(); $status = is_array( $status ) && isset( $status['status'] ) ? (int) $status['status'] : 0; return new WP_Error( 'mybot_supabase_log_probe_failed', $response->get_error_message() . self::hint_for_status( $status, $response->get_error_message() ) ); } // Best effort: a probe row left behind is harmless, but it should not linger. wp_remote_request( $url . '?wp_id=eq.' . $probe_id, array( 'method' => 'DELETE', 'timeout' => 15, 'headers' => self::headers( $key ), ) ); /* * A table that accepts a probe row is a table the mirror can write to, so a successful * test is also the moment to clear the failure counter. Without this the admin would fix * the problem, see a green line, and still get no rows until they found the untick/tick * dance described in the settings — a second silent failure stacked on the first. * * The timestamp is deliberately NOT moved: "last successful copy 2 minutes ago" has to * mean a conversation, not a probe row, or the status line tells a comfortable lie. */ self::succeed( false ); return true; } /** * Authentication headers for the log table. * * @param string $key API key. * @return array */ protected static function headers( $key ) { return array( 'apikey' => $key, 'Authorization' => 'Bearer ' . $key, ); } /** * Turns a refused write into something a site owner can act on. * * The message is passed in as well as the status, because PostgREST names the exact column it * could not find and that name is the whole diagnosis: on 04/10/2026 a real site answered * *"Could not find the 'lang' column of 'mybot_messages' in the schema cache"* while this * method said *"most often it still has the old id column"* — true of the family, wrong about * the case, and it sent the reader looking at the primary key instead of the missing column. * A hint that names the wrong cause is a wrong hint (6.20, 6.26). * * @param int $status HTTP status returned by Supabase. * @param string $message Error message returned by Supabase. * @return string */ protected static function hint_for_status( $status, $message = '' ) { if ( 401 === $status || 403 === $status ) { return ' ' . __( 'Supabase refused the key. The vector store key must be the service_role key, because the log table has row level security on with no policy.', 'aihouse' ); } if ( 404 === $status ) { return ' ' . __( 'The table was not found. Run the mybot_messages block from sql/supabase-schema.sql in the Supabase SQL Editor.', 'aihouse' ); } if ( 400 === $status || 409 === $status ) { /* * PostgREST answers "Could not find the 'x' column ... in the schema cache" when the * table exists but was created from an older version of the schema. Naming that column * turns "the table does not match" into "this one column is missing", which is the * difference between reading a paragraph and knowing what to do. */ if ( preg_match( "/Could not find the '([A-Za-z0-9_]+)' column/", (string) $message, $found ) ) { return ' ' . sprintf( /* translators: %s: name of the column Supabase could not find */ __( 'The table is missing the column "%s", so it was created before that column existed. Check the row count first — select count(*) from public.mybot_messages; — and if it is 0, run "drop table if exists public.mybot_messages;" and then the create table block from sql/supabase-schema.sql. If the table already holds rows you want to keep, use the repair block at the end of that file instead, which adds the missing columns. Nothing has ever been written by the plugin, so 0 is what to expect.', 'aihouse' ), $found[1] ); } return ' ' . __( 'The table does not match the current schema: either it still has the old id column instead of wp_id, or it is missing a column the plugin now writes (lang, tokens_in, tokens_out). Check the row count first — select count(*) from public.mybot_messages; — and if it is 0, run "drop table if exists public.mybot_messages;" and then the create table block from sql/supabase-schema.sql. If the table already holds rows you want to keep, use the repair block at the end of that file instead. Nothing has ever been written by the plugin, so 0 is what to expect.', 'aihouse' ); } return ''; } /** * Copies a batch of already-normalized log rows to Supabase. * * Never throws: the caller has already written the rows to MySQL, and a mirror problem is * not worth failing a job over. The return value exists so the caller can record why. * * @param array $rows Normalized rows, already carrying `wp_id`. * @return true|WP_Error True when mirrored (or deliberately skipped), WP_Error on failure. */ public static function mirror( $rows ) { if ( empty( $rows ) ) { return true; } if ( ! self::enabled() ) { return true; } if ( ! self::configured() ) { return self::fail( __( 'Supabase is not configured, so the conversation log was not mirrored.', 'aihouse' ) ); } $settings = mybot()->settings; $base = rtrim( (string) $settings->get( 'vector_url', '' ), '/' ); /* * secret(), never get(). * * The key is stored encrypted, and `get()` returns exactly that ciphertext. Sending it as a * bearer token produced a 401 from Supabase on every single message — while every check on * the surface said the feature was on: the box was ticked, the vector store worked, the URL * was there. The vector store has always used `secret()`; this class did not, and this bug * is the whole reason the log never appeared in Supabase. */ $key = (string) $settings->secret( 'vector_key' ); $payload = array(); foreach ( $rows as $row ) { if ( ! is_array( $row ) ) { continue; } // Without a WordPress ID there is nothing to de-duplicate on, and writing a zero // would make every later message collide with the first one. if ( empty( $row['wp_id'] ) ) { continue; } $payload[] = array( 'wp_id' => (int) $row['wp_id'], 'session_id' => (string) $row['session_id'], 'role' => (string) $row['role'], 'content' => (string) $row['content'], 'lang' => (string) $row['lang'], 'mode' => (string) $row['mode'], 'provider' => (string) $row['provider'], 'model' => (string) $row['model'], 'latency_ms' => (int) $row['latency_ms'], 'tokens_in' => (int) $row['tokens_in'], 'tokens_out' => (int) $row['tokens_out'], 'cache_hit' => ! empty( $row['cache_hit'] ), 'rating' => (int) $row['rating'], 'created_at' => (string) $row['created_at'], ); } if ( empty( $payload ) ) { return true; } foreach ( array_chunk( $payload, self::BATCH ) as $batch ) { $response = MYBOT_HTTP::post_json( $base . '/rest/v1/' . rawurlencode( self::TABLE ), $batch, array( 'apikey' => $key, 'Authorization' => 'Bearer ' . $key, /* * The four columns of the unique index, named explicitly: without it PostgREST * would conflict on the primary key, which WordPress does not know, and a job * retry would append a second copy of every message instead of being ignored. */ 'Prefer' => 'resolution=ignore-duplicates,return=minimal', ), 15, 1 ); if ( is_wp_error( $response ) ) { $status = $response->get_error_data(); $status = is_array( $status ) && isset( $status['status'] ) ? (int) $status['status'] : 0; return self::fail( $response->get_error_message() . self::hint_for_status( $status, $response->get_error_message() ) ); } } self::succeed(); return true; } /** * Records a successful mirror run. * * @param bool $stamp Whether this success means a conversation was copied. False for the * test probe, which proves the path works but copies no conversation. * @return void */ protected static function succeed( $stamp = true ) { $settings = mybot()->settings; $attempts = (int) $settings->get( 'supabase_logging_attempts', 0 ); $error = (string) $settings->get( 'supabase_logging_error', '' ); $last_ok = (int) $settings->get( 'supabase_logging_ok_at', 0 ); /* * "Last successful copy" is what turns "I enabled it and nothing arrived" into a fact: * either the mirror has never written anything, or it stopped at a known time. The * timestamp is refreshed at most every five minutes, because this runs on every batch. */ $changes = array(); if ( $attempts > 0 || '' !== $error ) { $changes['supabase_logging_attempts'] = 0; $changes['supabase_logging_error'] = ''; } if ( $stamp && ( time() - $last_ok ) > 300 ) { $changes['supabase_logging_ok_at'] = time(); } if ( ! empty( $changes ) ) { self::patch( $changes ); } } /** * Counts a failure and switches the mirror off after too many in a row. * * @param string $reason Error message from the request. * @return WP_Error */ protected static function fail( $reason ) { $settings = mybot()->settings; $attempts = (int) $settings->get( 'supabase_logging_attempts', 0 ) + 1; $message = $reason; if ( $attempts >= self::MAX_ATTEMPTS ) { $message .= ' ' . sprintf( /* translators: %d: number of consecutive failures */ __( 'The Supabase log mirror has now failed %d times in a row, so it has switched itself off. The conversation log is still complete in WordPress. Fix the problem and untick then tick the option again to restart it.', 'aihouse' ), $attempts ); } self::patch( array( 'supabase_logging_attempts' => $attempts, 'supabase_logging_error' => $message, ) ); return new WP_Error( 'mybot_supabase_log_failed', $message ); } /** * Writes a few keys into the settings option without touching anything else. * * `MYBOT_Settings::save()` rebuilds the whole option from the submitted form, which is the * documented way to lose every other setting, so it must not be used from here. * * @param array $changes Key => value pairs. * @return void */ protected static function patch( $changes ) { $settings = mybot()->settings; $current = $settings->all(); if ( ! is_array( $current ) ) { return; } update_option( MYBOT_OPTION_KEY, array_merge( $current, $changes ), false ); } }