Ai Maria Db Store Laravel Package

Getting Started

Minimal Setup for Laravel

1. Install the Package:

   composer require symfony/ai-maria-db-store
   

   Ensure your composer.json includes Symfony’s AI components if not already present:

   "require": {
       "symfony/ai": "^0.8",
       "symfony/dependency-injection": "^7.0"
   }
   

2. Configure MariaDB:

   • Upgrade to MariaDB 11.7+ (check with mariadb --version).
   • Create a table with a VECTOR column:

     CREATE TABLE ai_embeddings (
         id INT AUTO_INCREMENT PRIMARY KEY,
         content TEXT,
         metadata JSON,
         embedding VECTOR(1536),  -- Adjust dimensions to your embedding size
         INDEX vec_idx USING HNSW(embedding) WITH (distance_type = 'COSINE')
     ) ENGINE=InnoDB;
     

   • Update Laravel’s .env:

     DB_MARIADB_CONNECTION=mariadb
     DB_MARIADB_URL="mysql://user:pass@host/db?serverVersion=11.7"
     

3. Register the Store: Add a service provider (e.g., App\Providers\MariaDbStoreServiceProvider):

   use Symfony\Component\AI\Store\AiStoreInterface;
   use Symfony\AI\MariaDbStore\MariaDbStore;
   
   public function register()
   {
       $this->app->singleton(AiStoreInterface::class, function ($app) {
           return new MariaDbStore(
               $app['db']->connection('mariadb')->getPdo(),
               [
                   'table' => 'ai_embeddings',
                   'vector_column' => 'embedding',
                   'distance' => 'cosine',
                   'dimensions' => 1536,
               ]
           );
       });
   }
   

4. First Use Case: Insert and query embeddings in a Laravel controller:

   use Symfony\Component\AI\Store\AiStoreInterface;
   
   public function storeEmbedding(AiStoreInterface $store)
   {
       $embedding = [0.1, 0.2, ..., 0.1]; // Your 1536-dim vector
       $store->insert([
           'content' => 'Sample text',
           'metadata' => ['category' => 'tech'],
           'embedding' => $embedding,
       ]);
   
       // Query with similarity
       $results = $store->query($embedding, [
           'limit' => 5,
           'filter' => ['category' => 'tech'],
       ]);
   }
   

Implementation Patterns

Core Workflows

1. CRUD Operations:

   • Insert: Use insert() for single records or insertMany() for batches:

     $store->insertMany([
         ['content' => 'Doc 1', 'embedding' => $vec1, 'metadata' => [...]],
         ['content' => 'Doc 2', 'embedding' => $vec2, 'metadata' => [...]],
     ]);
     

   • Update: Replace records via insert() (no native update; use remove() + insert()).
   • Remove: Delete by ID or filter:

     $store->remove(['id' => 1]); // By ID
     $store->remove(['category' => 'deprecated']); // By metadata filter
     

2. Hybrid Queries: Combine vector similarity with SQL filters:

   $results = $store->query($queryVector, [
       'limit' => 10,
       'filter' => [
           'category' => 'tech',
           'created_at' => ['>', '2023-01-01'],
       ],
   ]);
   

   • Supported Filters: Use MariaDB’s WHERE syntax (e.g., JSON_EXTRACT(metadata, '$.priority') > 5).

3. Batch Processing:

   • Bulk Insert: Use insertMany() for efficiency (test with 1K+ records).
   • Parallel Queries: Offload heavy queries to queues (e.g., Laravel Horizon):

     dispatch(new ProcessEmbeddings($store, $batch));
     

4. Schema Management:

   • Dynamic Dimensions: Recreate the table if dimensions change (no ALTER TABLE support for VECTOR).
   • Indexing: Manually add/drop indexes:

     ALTER TABLE ai_embeddings ADD INDEX vec_idx USING HNSW(embedding);
     

Integration Tips

• Laravel-Specific:

  • Use Repositories: Isolate store logic in a repository to avoid mixing Eloquent/PDO:

    class EmbeddingRepository {
        public function __construct(private AiStoreInterface $store) {}
    
        public function findSimilar($vector, array $filters = []) {
            return $this->store->query($vector, ['filter' => $filters]);
        }
    }
    

  • Events: Trigger events for critical operations (e.g., EmbeddingStored):

    event(new EmbeddingStored($record));
    

• Symfony Abstraction:

  • Extend AiStoreInterface for custom methods:

    interface CustomStoreInterface extends AiStoreInterface {
        public function getByMetadata(array $filters);
    }
    

• Testing:

  • Mock the store in unit tests:

    $store = $this->createMock(AiStoreInterface::class);
    $store->method('query')->willReturn([...]);
    

Gotchas and Tips

Pitfalls

1. MariaDB Version:

   • Error: Unknown column type 'VECTOR' → You’re on MariaDB <11.7.
   • Fix: Upgrade or use a compatible alternative (e.g., pgvector).

2. Distance Functions:

   • Error: Unknown system variable 'distance_type' → HNSW index misconfiguration.
   • Fix: Use COSINE or L2 (Euclidean) explicitly:

     INDEX vec_idx USING HNSW(embedding) WITH (distance_type = 'COSINE')
     

3. Vector Dimensions:

   • Error: Data too long for column 'embedding' → Mismatched dimensions.
   • Fix: Ensure dimensions in config match the VECTOR(N) column.

4. Hybrid Query Limits:

   • Error: SQLSTATE[HY000]: General error: 1118 → Complex filters may fail.
   • Fix: Simplify filters or use raw SQL:

     $store->query($vector, ['filter' => ['raw' => 'category = ? AND priority > ?', ['tech', 5]]]);
     

5. Laravel-Symfony Conflicts:

   • Error: Class 'Symfony\Component\AI\Store\AiStoreInterface' not found.
   • Fix: Ensure symfony/ai is installed and autoloaded.

Debugging Tips

• Enable MariaDB Logging:

  [mysqld]
  general_log = 1
  general_log_file = /var/log/mysql/mariadb-query.log
  

• Query Inspection: Use DB::enableQueryLog() to capture raw SQL:

  DB::enableQueryLog();
  $store->query($vector);
  dd(DB::getQueryLog());
  

Performance Quirks

1. Indexing Overhead:

   • HNSW indexes improve performance but slow down inserts. Test with:

     -- Disable index temporarily for bulk inserts
     ALTER TABLE ai_embeddings DROP INDEX vec_idx;
     -- Insert data
     ALTER TABLE ai_embeddings ADD INDEX vec_idx;
     

2. Batch Size:

   • Optimal insertMany() batch size: 500–2000 records (benchmark with EXPLAIN).

3. Distance Metrics:

   • Cosine is faster for high-dimensional vectors (>512D) than Euclidean.

Extension Points

1. Custom Distance Functions: Override the store’s getDistanceSql() method:

   class CustomMariaDbStore extends MariaDbStore {
       protected function getDistanceSql(string $column, string $distance): string
       {
           return match ($distance) {
               'custom' => "1 - (($column) * $this->getVectorPlaceholder())",
               default => parent::getDistanceSql($column, $distance),
           };
       }
   }
   

2. Metadata Serialization: Extend to handle custom metadata formats (e.g., arrays):

   protected function serializeMetadata(array $metadata): string
   {
       return json_encode($metadata, JSON_THROW_ON_ERROR);
   }
   

3. Async Operations: Use Laravel Queues for long-running queries:

   class ProcessVectorQuery implements ShouldQueue {
       public function handle(AiStoreInterface $store) {
           $store->query