Setup pgvector and store questions for vector search

[gaboesquivel]

We need to integrate pgvector into our existing Next.js application to enhance our search capabilities using vector-based search. The goal is to store questions in a format that allows efficient similarity searches. The task involves setting up pgvector in our PostgreSQL database managed by Supabase and configuring it to work seamlessly with our Next.js API routes.

Requirements:

1. Database Setup: Install pgvector extension on the PostgreSQL database provided by Supabase.
2. Schema Design: Design and implement a database schema to store questions as vectors.
3. API Integration: Modify existing Next.js API routes to handle the storage and retrieval of vector data.
4. Testing: Ensure that the vector search correctly retrieves questions based on similarity.
5. Documentation: Update the project documentation to reflect the changes made for supporting vector search.

Acceptance Criteria:

• pgvector is properly installed and configured in the Supabase-managed PostgreSQL database.
• Questions can be added and queried based on vector similarity through the Next.js API routes.
• All changes are fully documented and tested to guarantee functionality.

Notes:

• Ensure compatibility with the current codebase and infrastructure.
• Assess performance implications and optimize query efficiency.

Reference:

https://supabase.com/docs/guides/ai/examples/nextjs-vector-search