This is a submission for the Open Source AI Challenge with pgai and Ollama

What I Built

In this project, I’ve developed a prototype for an AI-powered personal knowledge assistant (think "second brain") called pgai. It’s designed to work locally and includes integrations with cutting-edge AI tools like Gemini LLM and Ollama, as well as essential database technologies like Postgres and Drizzle ORM. The project is based on Vercel’s repository and adapted for compatibility with locally running language models (LLMs), which keeps the functionality self-contained and secure on personal machines.

The main objective behind this project is to explore how easily we can create, embed, and query knowledge bases locally using tools that are accessible and developer-friendly.

Demo

You can view the demo on Vercel here: pgai-challenge

The GitHub repository is available here: pgai_challenge

Tools Used

• AI SDK & Gemini LLM: These tools power the underlying logic and intelligence of pgai.
• Ollama: A locally-hosted LLM server that allows me to run and access models directly on my machine without needing cloud-based resources.
• Postgres with pgai and vector extensions: Postgres isn’t just storing data here—it’s playing a central role in embedding and querying information efficiently.
• Drizzle ORM: A lightweight ORM for managing database interactions.
• Next.js: Provides the frontend and API functionality for the application, making it fast, reliable, and easy to navigate.

Setting Up the Environment

Before you can run pgai, make sure you have the following installed:

• Docker: If you’re new to Docker, I suggest using Docker Desktop for a beginner-friendly setup.
• Ollama: Download and install Ollama’s binary for your system here. Once installed, verify by running:

  ollama --help

• LLM model: You’ll need to pull a model, for example, llama3.2. Run:

  ollama pull llama3.2

• TimescaleDB: Use Docker to set up TimescaleDB. In a working directory, create a file named docker-compose.yml and paste in this configuration:

  services:
    timescaledb:
      image: timescale/timescaledb:latest-pg16
      container_name: timescaledb
      environment:
        - POSTGRES_DB=mydb
        - POSTGRES_USER=myuser
        - POSTGRES_PASSWORD=mypassword
      ports:
        - "5444:5432"
      volumes:
        - timescaledb_data:/var/lib/postgresql/data
      restart: unless-stopped

  volumes:
    timescaledb_data:
      driver: local

Run docker-compose up --build in this directory to start TimescaleDB, which will enable the essential pgai and vector extensions.

Using pgai and Postgres Vector Extensions

To configure Postgres for handling AI queries, you’ll need to install the pgai extension and set up a table for your data.

Enable the pgai Extension

To start using AI embeddings directly within SQL queries, enable the extension:

CREATE EXTENSION IF NOT EXISTS ai CASCADE;

Setting Up the Lyrics Table

Here’s an example of a table schema to store lyrics along with metadata and embeddings:

CREATE TABLE lyrics (
    id SERIAL PRIMARY KEY,
    author TEXT,
    title TEXT,
    content TEXT,
    metadata JSON,
    embedding vector(768)
);

INSERT INTO lyrics (id, author, title, content, metadata) VALUES 
(1, 'Alex Rivers', 'Wandering Heart', 'I took the road less traveled, through shadows and light, searching for pieces I lost in the night.',
    '{"genre": "Indie Folk", "year": 2022, "mood": "reflective"}'),
(2, 'Jamie Cross', 'Echoes of You', 'I hear your voice in the silence, a whisper that fades, memories linger, but time never waits.',
    '{"genre": "Pop Ballad", "year": 2023, "mood": "nostalgic"}'),
(3, 'Taylor Lane', 'Endless Dream', 'We danced in the midnight, stars in our eyes, promised forever, but forever flies.',
    '{"genre": "Synthwave", "year": 2021, "mood": "dreamy"}'),
(4, 'Morgan Lee', 'Fading Fire', 'The embers are low now, but they still burn bright, a love once so fierce, lost in the night.',
    '{"genre": "Rock", "year": 2022, "mood": "intense"}'),
(5, 'Casey Dawn', 'Empty Streets', 'These streets hold stories of laughter and tears, footsteps that echo through the passing years.',
    '{"genre": "Blues", "year": 2020, "mood": "melancholy"}');

Generating Embeddings

Use the following SQL to generate embeddings based on each row’s content:

UPDATE lyrics SET embedding = ai.ollama_embed('nomic-embed-text', content, host => 'http://host.docker.internal:11434');

This command utilizes the pgai extension to call Ollama’s embedding model and automatically store embeddings directly in your database, making data retrieval more efficient.

Running Embedding-based Queries

Once embeddings are generated, you can query based on similarity to find lyrics related to a specific theme or mood. Here are some examples:

Simple Similarity Query

SELECT embedding <=> ai.ollama_embed('nomic-embed-text', 'I am sad', host => 'http://host.docker.internal:11434') as distance
FROM lyrics
ORDER BY distance;

This finds the closest matches to the query “I am sad” and ranks them based on distance.

Query with Metadata Filter

You can add filters for metadata, such as searching only within a specific genre:

SELECT embedding <=> ai.ollama_embed('nomic-embed-text', 'timeless music', host => 'http://host.docker.internal:11434') as distance
FROM lyrics 
WHERE metadata->>'genre' = 'Synthwave'
ORDER BY distance
LIMIT 10;

This query will return lyrics from the “Synthwave” genre most similar to the phrase “timeless music.”

Final Thoughts

This project shows how tools like pgai and Ollama make it easy to use Postgres as a knowledge base with powerful vector search capabilities. The potential applications are vast: you could adapt this for organizing any data—articles, notes, even multimedia—in a way that allows for more intelligent querying.

Working with pgai has been exciting as it removes many of the usual complexities in building and deploying AI-based applications. This project has been an eye-opener to how quickly AI tools are evolving to make data organization, recall, and search more intelligent and user-friendly. I hope this prototype showcases the potential of combining Postgres with locally hosted AI models for any knowledge-based application!

Prize Categories: Open-source Models from Ollama, Vectorizer