Building a Custom Table for Embedding Storage

Lesson 4 settled on Option A, a custom table, as the right amount of infrastructure for most sites. This lesson builds that table for real: a proper dbDelta()-managed schema, a function that chunks and embeds a post and writes the rows, and the plumbing to keep it all wired into WordPress’s own upgrade routine.

Step 1: The schema

One row per chunk, not per post, since a long post produces many chunks (Lesson 3). Each row keeps a reference back to its source post so results can be linked and so Lesson 8 can clean up stale rows:

// File: my-plugin/class-embedding-table.php
function my_plugin_create_embeddings_table() {
	global $wpdb;

	$table_name      = $wpdb->prefix . 'ai_chunk_embeddings';
	$charset_collate = $wpdb->get_charset_collate();

	$sql = "CREATE TABLE {$table_name} (
		id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
		post_id BIGINT UNSIGNED NOT NULL,
		chunk_index SMALLINT UNSIGNED NOT NULL DEFAULT 0,
		chunk_text LONGTEXT NOT NULL,
		embedding LONGTEXT NOT NULL,
		dimensions SMALLINT UNSIGNED NOT NULL DEFAULT 0,
		created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
		PRIMARY KEY  (id),
		KEY post_id (post_id)
	) {$charset_collate};";

	require_once ABSPATH . 'wp-admin/includes/upgrade.php';
	dbDelta( $sql );
}

embedding is LONGTEXT holding the vector serialized as JSON, since WordPress’s supported database engines have no native vector column type, this is the honest, portable way to store it. dimensions records how many floats are in that vector, worth keeping as its own column so you can spot a mismatch if you ever switch models (Course 5’s fallback lesson covers why that matters).

Step 2: Running it on activation

Call the creation function on plugin activation, the same pattern any custom table uses:

// File: my-plugin.php
register_activation_hook( __FILE__, 'my_plugin_create_embeddings_table' );

If you change the schema later, for example adding a column, bump a stored option holding a schema version number and re-run dbDelta() when it’s out of date, checked on plugins_loaded rather than only on activation, so existing installs upgrading to a newer version of your plugin also get the new column.

Step 3: Writing a chunk and its embedding

A single insert function, one row per chunk:

// File: my-plugin/class-embedding-table.php
function my_plugin_store_chunk( int $post_id, int $chunk_index, string $chunk_text, array $vector ) {
	global $wpdb;

	$wpdb->insert(
		$wpdb->prefix . 'ai_chunk_embeddings',
		array(
			'post_id'     => $post_id,
			'chunk_index' => $chunk_index,
			'chunk_text'  => $chunk_text,
			'embedding'   => wp_json_encode( $vector ),
			'dimensions'  => count( $vector ),
		),
		array( '%d', '%d', '%s', '%s', '%d' )
	);
}

The wp_json_encode( $vector ) call is doing the real work of Lesson 4’s decision: turning a PHP float array into a string that fits in a LONGTEXT column. It comes back out with json_decode() at search time (Lesson 6).

Step 4: A full indexing function, chunk, embed in batch, store

Putting Lessons 2, 3, and this lesson’s table together into one function that indexes a single post:

// File: my-plugin/class-embedding-table.php
use WordPress\AiClient\AiClient;

function my_plugin_index_post( int $post_id ) {
	global $wpdb;

	$post = get_post( $post_id );

	if ( ! $post || 'publish' !== $post->post_status ) {
		return;
	}

	// Remove any existing chunks for this post before re-indexing.
	$wpdb->delete( $wpdb->prefix . 'ai_chunk_embeddings', array( 'post_id' => $post_id ), array( '%d' ) );

	$chunks = my_plugin_chunk_by_paragraph( $post->post_content );

	if ( empty( $chunks ) ) {
		return;
	}

	// One batch call embeds every chunk from this post at once.
	$results = AiClient::input( $chunks )->generateEmbeddings();

	foreach ( $results as $i => $result ) {
		my_plugin_store_chunk( $post_id, $i, $chunks[ $i ], $result->getValues() );
	}
}

Deleting existing rows before re-inserting keeps re-indexing idempotent, running this function twice on an unchanged post produces the same stored rows, not duplicates.

Test it: index one real post and inspect the rows

wp-env run cli wp eval '
my_plugin_index_post( 1 );
global $wpdb;
$rows = $wpdb->get_results( "SELECT id, post_id, chunk_index, dimensions, LEFT(chunk_text, 60) as preview FROM {$wpdb->prefix}ai_chunk_embeddings WHERE post_id = 1" );
foreach ( $rows as $row ) {
	echo "Chunk {$row->chunk_index}: {$row->dimensions} dims, \"{$row->preview}...\"\n";
}
'

You should see one row per chunk, each with a real dimension count and a readable text preview, not an empty result set or a truncated embedding column.

Recap

A dedicated table, one row per chunk, with post_id, the chunk text, its JSON-encoded embedding, and a dimension count, is the concrete shape of Lesson 4’s Option A. dbDelta() creates and upgrades it, formatted exactly to its parsing rules, and a single indexing function ties chunking, batch embedding, and storage together into one idempotent call per post. The next lesson turns these stored rows into an actual search.

Resources & further reading

• dbDelta() function reference, developer.wordpress.org
• Creating Tables with Plugins, developer.wordpress.org
• $wpdb class reference, developer.wordpress.org