Index table not created if sqlite not working when config loaded

Turns out there were earlier errors related to the sqlite extension not loading correctly. I'll report back, but it seems like not a problem with the module.

The error still happens once I made sure the extension could load.

At what point is it supposed to create the table? I see SQLiteVectorClient::createCollection() where it has this code:

  public function createCollection(
    string  $collection_name,
    int     $dimension,
    SQLite3   $connection,
  ): void {
    $escaped_collection_name = $this->escapeIdentifierForSql($collection_name);

    // 1. Create the main table for metadata
    $create_main_table_sql = "CREATE VIRTUAL TABLE IF NOT EXISTS {$escaped_collection_name} USING vec0 (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      content TEXT,
      drupal_entity_id TEXT,
      drupal_long_id TEXT,
      server_id TEXT,
      index_id TEXT,
      embedding float[{$dimension}]
    );";

    if (!$connection->exec($create_main_table_sql)) {
      throw new CreateCollectionException(message: "Failed to create metadata table '{$collection_name}': " . $connection->lastErrorMsg());
    }
  }

I added a breakpoint there but it doesn't get triggered when editing the index or indexing the content.

When saving the index would it be useful to check the index table exists, and if not to create it?

I worked out the sequence if events - if the sqlite extension is working correctly when the configuration is first created it then doesn't try to recreate it during any other steps (indexing, etc). To fix the problem I first got the sqlite extension working ;-) then I deleted the index in Search API then re-imported the configuration, it then was able to connect to the database file and created the necessary table.

It might be worth adding some extra checks to verify the index is working correctly, e.g. it should show on admin/config/search/search-api/index/INDEXNAME whether the index can be connected to.