Business website data

[ngoiyaeric]

Enable links to RAG functionality for business websites to be in context of system chat from settings system prompts.
Autonomous data extraction and embedding per user/business.

[ngoiyaeric]

@coderabbitai plan

[ngoiyaeric]

Instagram accounts as well! @coderabbitai

[coderabbitai]

Warning

A plan generation is already in progress. Please wait for it to complete before requesting a new plan.

[coderabbitai]

Coding Plan

Summary

• Extend the existing PostgreSQL schema with pgvector-enabled tables for storing website URLs and their embedded content chunks
• Add a new settings tab following established UI patterns for users to manage their knowledge source URLs
• Build a synchronous extraction pipeline using simple HTTP fetch + cheerio parsing, triggered on-demand when URLs are added or refreshed
• Integrate RAG retrieval into the existing chat flow by querying similar chunks and injecting them alongside the system prompt before agent invocation

Design Choices

Design Choice 1: Embedding Model Selection

Options Considered:

1. Use text-embedding-ada-002 (1536 dimensions) - matches existing schema definition
2. Use text-embedding-3-small (1536 dimensions) - newer, cheaper, similar quality
3. Use text-embedding-3-large (3072 dimensions) - requires schema migration

Chosen Option: 2

Rationale: text-embedding-3-small provides better cost efficiency while maintaining compatibility with the existing 1536-dimension vector type already defined in the schema.

Design Choice 2: Content Extraction Approach

Options Considered:

1. Simple HTTP fetch + HTML parsing (cheerio) - fast, no dependencies
2. Headless browser (Puppeteer/Playwright) - handles JS-rendered content, heavier
3. External service (Firecrawl, Jina Reader API) - managed, handles edge cases

Chosen Option: 1

Rationale: Start with simple fetch + cheerio parsing. Most business websites serve static/SSR content. This can be extended later if JS-rendered sites become a requirement.

Design Choice 3: Processing Trigger Mechanism

Options Considered:

1. On-demand processing when URL is added + manual refresh button
2. Scheduled background jobs (cron) for periodic re-crawling
3. Queue-based async processing with status polling

Chosen Option: 1

Rationale: On-demand processing keeps the implementation simple without requiring job infrastructure. A manual "Refresh" action allows users to re-crawl when content changes.

💡 User Tips

Regenerate the plan with different choices with @coderabbitai <feedback>.

Implementation Steps

Phase 1: Database Schema & Vector Infrastructure

Establish the data model for storing website sources and their extracted, embedded content. This phase creates the foundation for RAG storage with pgvector similarity search capabilities.

Task 1: Enable pgvector Extension

Add migration to enable the pgvector PostgreSQL extension required for vector similarity operations.

• Create new migration file in drizzle/migrations/ to execute CREATE EXTENSION IF NOT EXISTS vector
• Update migration journal in drizzle/migrations/meta/_journal.json to include both the pgvector migration and the previously untracked 0001_add_calendar_notes.sql

Task 2: Create RAG Sources Table

Define storage for user-configured website URLs with processing status tracking.

• Add rag_sources table to lib/db/schema.ts with columns: id (UUID PK), user_id (FK to users), url (text, unique per user), name (optional display name), status (enum: pending/processing/completed/error), error_message (nullable), last_crawled_at, created_at, updated_at
• Create corresponding migration in drizzle/migrations/

Task 3: Create Document Chunks Table

Define storage for extracted text chunks with their vector embeddings for similarity search.

• Add document_chunks table to lib/db/schema.ts with columns: id (UUID PK), source_id (FK to rag_sources with cascade delete), user_id (FK to users), content (text), embedding (vector 1536), chunk_index (integer for ordering), metadata (jsonb for source URL, title, section headers), created_at
• Add index on embedding column using ivfflat or hnsw for efficient similarity search
• Create corresponding migration

🤖 Prompt for AI agents

Set up the pgvector database infrastructure and schema tables required for RAG
storage. This phase creates the migration files and Drizzle schema definitions
needed before any application code can be written.

- Create a new migration file in `drizzle/migrations/` that runs `CREATE
EXTENSION IF NOT EXISTS vector`
- Update `drizzle/migrations/meta/_journal.json` to register both the new
pgvector migration and the previously untracked `0001_add_calendar_notes.sql`
- In `lib/db/schema.ts`, define the `rag_sources` table with the following
columns:
  - `id`: UUID primary key
  - `user_id`: foreign key referencing users
  - `url`: text, unique per user
  - `name`: optional display name
  - `status`: enum with values `pending`, `processing`, `completed`, `error`
  - `error_message`: nullable text
  - `last_crawled_at`, `created_at`, `updated_at`: timestamps
- In `lib/db/schema.ts`, define the `document_chunks` table with the following
columns:
  - `id`: UUID primary key
  - `source_id`: foreign key to `rag_sources` with cascade delete
  - `user_id`: foreign key referencing users
  - `content`: text
  - `embedding`: vector(1536)
  - `chunk_index`: integer for ordering
  - `metadata`: jsonb (for source URL, title, section headers)
  - `created_at`: timestamp
- Add an index on the `embedding` column using `ivfflat` or `hnsw` for efficient
similarity search
- Create corresponding migration files in `drizzle/migrations/` for both the
`rag_sources` and `document_chunks` tables

Phase 2: Settings UI for Website Management

Add interface for users to manage their RAG source websites within the existing settings panel.

Task 1: Create RAG Sources Server Actions

Implement CRUD operations for managing website sources.

• Create lib/actions/rag-sources.ts with server actions following existing patterns in lib/actions/chat.ts
• Implement getRagSources(userId) to fetch all sources for a user with status
• Implement addRagSource(userId, url, name?) to create a new source with 'pending' status
• Implement deleteRagSource(userId, sourceId) with cascade to chunks
• Implement refreshRagSource(userId, sourceId) to reset status to 'pending' for re-processing

Task 2: Create RAG Sources Form Component

Build the UI component for the settings panel.

• Create components/settings/components/rag-sources-form.tsx following pattern of system-prompt-form.tsx
• Include URL input field with validation (valid URL format)
• Display list of existing sources with status indicators (pending/processing/completed/error)
• Add delete button per source and refresh button for completed sources
• Show error messages for failed sources

Task 3: Integrate RAG Sources Tab into Settings

Wire the new form into the existing settings structure.

• Update components/settings/components/settings.tsx to add "Knowledge Sources" or "Website Data" tab alongside existing tabs
• Extend the settings form schema in the same file to include RAG source management state if needed
• Load existing sources on mount via getRagSources() server action

🤖 Prompt for AI agents

Build the settings UI layer for managing RAG website sources, including server
actions and the React component. Follow the existing patterns established in
`lib/actions/chat.ts` and
`components/settings/components/system-prompt-form.tsx`.

**Server Actions (`lib/actions/rag-sources.ts`):**
- Implement `getRagSources(userId)` — fetch all RAG sources for a user,
including their current status
- Implement `addRagSource(userId, url, name?)` — create a new source record with
`status: 'pending'`
- Implement `deleteRagSource(userId, sourceId)` — delete a source (chunks
cascade via DB)
- Implement `refreshRagSource(userId, sourceId)` — reset source status back to
`'pending'` for re-processing

**Form Component (`components/settings/components/rag-sources-form.tsx`):**
- Model after `system-prompt-form.tsx` in the same directory
- Include a URL input field with validation enforcing valid URL format
- Display a list of existing sources with visual status indicators for each
state: `pending`, `processing`, `completed`, `error`
- Render a delete button per source entry and a refresh button for sources in
`completed` state
- Display error messages inline for sources with `error` status

**Settings Integration (`components/settings/components/settings.tsx`):**
- Add a new tab labeled "Knowledge Sources" or "Website Data" alongside the
existing tabs
- Extend the settings form schema if RAG source management state needs to be
tracked at the settings level
- Load existing sources on component mount by calling the `getRagSources()`
server action

Phase 3: Content Extraction & Embedding Pipeline

Build the autonomous pipeline to crawl websites, extract text, chunk content, and generate embeddings.

Task 1: Create Text Extraction Utility

Implement website content fetching and parsing.

• Create lib/rag/extractor.ts with function to fetch URL and extract meaningful text content
• Use cheerio for HTML parsing (add as dependency if not present)
• Extract text from semantic elements (article, main, p, h1-h6, li), excluding nav, footer, script, style
• Preserve some structure metadata (page title, headings hierarchy)
• Handle common error cases (network failures, invalid HTML, redirects)

Task 2: Create Text Chunking Utility

Implement text segmentation for embedding.

• Create lib/rag/chunker.ts with function to split text into overlapping chunks
• Use chunk size of ~500-800 tokens with ~100 token overlap for context continuity
• Respect paragraph/sentence boundaries when possible
• Return array of chunks with metadata (chunk index, source section)

Task 3: Create Embedding Generation Utility

Implement OpenAI embedding API integration.

• Create lib/rag/embeddings.ts with function to generate embeddings for text chunks
• Use OpenAI text-embedding-3-small model (1536 dimensions)
• Implement batching for efficiency (OpenAI allows up to 2048 inputs per request)
• Handle rate limiting with exponential backoff

Task 4: Create Processing Orchestrator

Coordinate the full extraction-to-embedding pipeline.

• Create lib/rag/processor.ts with main processRagSource(sourceId) function
• Update source status to 'processing' at start
• Call extractor → chunker → embedding generator in sequence
• Delete existing chunks for source before inserting new ones (for refresh scenarios)
• Store all chunks with embeddings in document_chunks table
• Update source status to 'completed' or 'error' with message on completion

Task 5: Create Processing API Route

Expose an endpoint to trigger processing.

• Create app/api/rag/process/route.ts for POST requests
• Accept sourceId in request body, validate user ownership
• Call processRagSource() and return status
• This will be called immediately after addRagSource() from the settings UI

🤖 Prompt for AI agents

Implement the end-to-end content extraction, chunking, and embedding pipeline
under the `lib/rag/` directory, plus an API route to trigger it. Each utility is
a separate module.

**Text Extraction (`lib/rag/extractor.ts`):**
- Add `cheerio` as a dependency if not already present
- Implement a function to fetch a given URL and parse its HTML using cheerio
- Extract text from semantic elements: `article`, `main`, `p`, `h1`–`h6`, `li`
- Exclude content from `nav`, `footer`, `script`, and `style` elements
- Preserve structure metadata including page title and headings hierarchy
- Handle error cases: network failures, invalid HTML, and redirects

**Text Chunking (`lib/rag/chunker.ts`):**
- Implement a function to split extracted text into overlapping chunks
- Target chunk size of ~500–800 tokens with ~100 token overlap for context
continuity
- Respect paragraph and sentence boundaries where possible
- Return an array of chunk objects including chunk index and source section
metadata

**Embedding Generation (`lib/rag/embeddings.ts`):**
- Implement a function to call the OpenAI embeddings API using the
`text-embedding-3-small` model (1536 dimensions)
- Batch inputs for efficiency — OpenAI supports up to 2048 inputs per request
- Implement exponential backoff to handle rate limiting

**Processing Orchestrator (`lib/rag/processor.ts`):**
- Implement the main `processRagSource(sourceId)` function
- At the start, update the source's status to `'processing'` in the DB
- Execute the pipeline in sequence: extractor → chunker → embedding generator
- Before inserting new chunks, delete any existing chunks for the source (to
support refresh)
- Persist all generated chunks with their embeddings to the `document_chunks`
table
- On completion, update source status to `'completed'`; on failure, set to
`'error'` with an error message

**Processing API Route (`app/api/rag/process/route.ts`):**
- Create a POST handler that accepts `sourceId` in the request body
- Validate that the authenticated user owns the specified source
- Call `processRagSource()` and return the resulting status
- This route is intended to be called immediately after `addRagSource()` from
the settings UI

Phase 4: RAG Retrieval & Chat Integration

Connect the embedded knowledge to the chat pipeline for context-aware responses.

Task 1: Create Similarity Search Function

Implement vector similarity retrieval.

• Create lib/rag/retrieval.ts with getRelevantChunks(userId, query, limit?) function
• Generate embedding for the user's query
• Execute pgvector similarity search (<=> cosine distance operator) against user's document chunks
• Return top-k chunks (default 5) with their content and metadata
• Include similarity score for potential relevance filtering

Task 2: Integrate RAG Context into Chat Flow

Modify the chat submission to include retrieved knowledge.

• Update app/actions.tsx in the submit() function after system prompt retrieval
• Call getRelevantChunks(userId, userMessage) with the latest user message
• Format retrieved chunks into a context block (e.g., "Relevant Knowledge:\n[chunk contents]")
• Append this context to the system prompt or pass as additional context parameter

Task 3: Update Agent to Utilize RAG Context

Ensure the researcher agent properly incorporates the retrieved context.

• Update lib/agents/researcher.tsx to accept optional ragContext parameter alongside dynamicSystemPrompt
• Modify getDefaultSystemPrompt() or the prompt construction to include RAG context when present
• Add instruction in system prompt guiding the model to reference provided knowledge sources when relevant

Task 4: Fix User ID Resolution in Chat Pipeline

Address the existing bug where userId is hardcoded as 'anonymous'.

• Update app/actions.tsx line ~400 to call getCurrentUserIdOnServer() instead of hardcoding 'anonymous'
• This ensures both system prompt and RAG context are fetched for the authenticated user
• Handle unauthenticated case gracefully (skip personalized context)

🤖 Prompt for AI agents

Wire the RAG retrieval system into the chat pipeline so that relevant embedded
knowledge chunks are injected into agent context alongside the system prompt.

**Similarity Search (`lib/rag/retrieval.ts`):**
- Implement `getRelevantChunks(userId, query, limit?)` function
- Generate a query embedding using the same `text-embedding-3-small` model
- Execute a pgvector cosine distance similarity search using the `<=>` operator
against the `document_chunks` table, scoped to the given `userId`
- Return the top-k chunks (default 5) including their content, metadata, and
similarity score

**Chat Flow Integration (`app/actions.tsx`):**
- In the `submit()` function, fix the user ID resolution at line ~400: replace
the hardcoded `'anonymous'` value with a call to `getCurrentUserIdOnServer()`
- Handle the unauthenticated case gracefully by skipping personalized context
retrieval
- After fetching the system prompt, call `getRelevantChunks(userId,
userMessage)` using the latest user message
- Format the retrieved chunks into a context block, e.g.: `"Relevant
Knowledge:\n[chunk contents]"`
- Append the formatted context to the system prompt or pass it as an additional
context parameter to the agents

**Agent Update (`lib/agents/researcher.tsx`):**
- Add an optional `ragContext` parameter alongside the existing
`dynamicSystemPrompt` parameter
- Update `getDefaultSystemPrompt()` or the prompt construction logic to
incorporate `ragContext` when it is present
- Include an instruction in the system prompt directing the model to reference
the provided knowledge sources when relevant

Research

The project uses Next.js with Drizzle ORM on PostgreSQL (via Supabase). Settings are managed through components/settings/ with react-hook-form + Zod validation, persisting to DB via server actions in lib/actions/. The schema already defines a vector(1536) custom type for pgvector embeddings (currently unused). The chat pipeline in app/actions.tsx fetches the user's system prompt and passes it to agents (researcher, writer), which accept dynamic prompt injection. There is currently no RAG retrieval or vector similarity search implemented—only the system prompt is injected into chat context.

---

🚀 Next Steps

🤖 All AI agent prompts combined

Task: 1

Set up the pgvector database infrastructure and schema tables required for RAG
storage. This phase creates the migration files and Drizzle schema definitions
needed before any application code can be written.

- Create a new migration file in `drizzle/migrations/` that runs `CREATE
EXTENSION IF NOT EXISTS vector`
- Update `drizzle/migrations/meta/_journal.json` to register both the new
pgvector migration and the previously untracked `0001_add_calendar_notes.sql`
- In `lib/db/schema.ts`, define the `rag_sources` table with the following
columns:
  - `id`: UUID primary key
  - `user_id`: foreign key referencing users
  - `url`: text, unique per user
  - `name`: optional display name
  - `status`: enum with values `pending`, `processing`, `completed`, `error`
  - `error_message`: nullable text
  - `last_crawled_at`, `created_at`, `updated_at`: timestamps
- In `lib/db/schema.ts`, define the `document_chunks` table with the following
columns:
  - `id`: UUID primary key
  - `source_id`: foreign key to `rag_sources` with cascade delete
  - `user_id`: foreign key referencing users
  - `content`: text
  - `embedding`: vector(1536)
  - `chunk_index`: integer for ordering
  - `metadata`: jsonb (for source URL, title, section headers)
  - `created_at`: timestamp
- Add an index on the `embedding` column using `ivfflat` or `hnsw` for efficient
similarity search
- Create corresponding migration files in `drizzle/migrations/` for both the
`rag_sources` and `document_chunks` tables
===============================================================================

Task: 2

Build the settings UI layer for managing RAG website sources, including server
actions and the React component. Follow the existing patterns established in
`lib/actions/chat.ts` and
`components/settings/components/system-prompt-form.tsx`.

**Server Actions (`lib/actions/rag-sources.ts`):**
- Implement `getRagSources(userId)` — fetch all RAG sources for a user,
including their current status
- Implement `addRagSource(userId, url, name?)` — create a new source record with
`status: 'pending'`
- Implement `deleteRagSource(userId, sourceId)` — delete a source (chunks
cascade via DB)
- Implement `refreshRagSource(userId, sourceId)` — reset source status back to
`'pending'` for re-processing

**Form Component (`components/settings/components/rag-sources-form.tsx`):**
- Model after `system-prompt-form.tsx` in the same directory
- Include a URL input field with validation enforcing valid URL format
- Display a list of existing sources with visual status indicators for each
state: `pending`, `processing`, `completed`, `error`
- Render a delete button per source entry and a refresh button for sources in
`completed` state
- Display error messages inline for sources with `error` status

**Settings Integration (`components/settings/components/settings.tsx`):**
- Add a new tab labeled "Knowledge Sources" or "Website Data" alongside the
existing tabs
- Extend the settings form schema if RAG source management state needs to be
tracked at the settings level
- Load existing sources on component mount by calling the `getRagSources()`
server action
===============================================================================

Task: 3

Implement the end-to-end content extraction, chunking, and embedding pipeline
under the `lib/rag/` directory, plus an API route to trigger it. Each utility is
a separate module.

**Text Extraction (`lib/rag/extractor.ts`):**
- Add `cheerio` as a dependency if not already present
- Implement a function to fetch a given URL and parse its HTML using cheerio
- Extract text from semantic elements: `article`, `main`, `p`, `h1`–`h6`, `li`
- Exclude content from `nav`, `footer`, `script`, and `style` elements
- Preserve structure metadata including page title and headings hierarchy
- Handle error cases: network failures, invalid HTML, and redirects

**Text Chunking (`lib/rag/chunker.ts`):**
- Implement a function to split extracted text into overlapping chunks
- Target chunk size of ~500–800 tokens with ~100 token overlap for context
continuity
- Respect paragraph and sentence boundaries where possible
- Return an array of chunk objects including chunk index and source section
metadata

**Embedding Generation (`lib/rag/embeddings.ts`):**
- Implement a function to call the OpenAI embeddings API using the
`text-embedding-3-small` model (1536 dimensions)
- Batch inputs for efficiency — OpenAI supports up to 2048 inputs per request
- Implement exponential backoff to handle rate limiting

**Processing Orchestrator (`lib/rag/processor.ts`):**
- Implement the main `processRagSource(sourceId)` function
- At the start, update the source's status to `'processing'` in the DB
- Execute the pipeline in sequence: extractor → chunker → embedding generator
- Before inserting new chunks, delete any existing chunks for the source (to
support refresh)
- Persist all generated chunks with their embeddings to the `document_chunks`
table
- On completion, update source status to `'completed'`; on failure, set to
`'error'` with an error message

**Processing API Route (`app/api/rag/process/route.ts`):**
- Create a POST handler that accepts `sourceId` in the request body
- Validate that the authenticated user owns the specified source
- Call `processRagSource()` and return the resulting status
- This route is intended to be called immediately after `addRagSource()` from
the settings UI
===============================================================================

Task: 4

Wire the RAG retrieval system into the chat pipeline so that relevant embedded
knowledge chunks are injected into agent context alongside the system prompt.

**Similarity Search (`lib/rag/retrieval.ts`):**
- Implement `getRelevantChunks(userId, query, limit?)` function
- Generate a query embedding using the same `text-embedding-3-small` model
- Execute a pgvector cosine distance similarity search using the `<=>` operator
against the `document_chunks` table, scoped to the given `userId`
- Return the top-k chunks (default 5) including their content, metadata, and
similarity score

**Chat Flow Integration (`app/actions.tsx`):**
- In the `submit()` function, fix the user ID resolution at line ~400: replace
the hardcoded `'anonymous'` value with a call to `getCurrentUserIdOnServer()`
- Handle the unauthenticated case gracefully by skipping personalized context
retrieval
- After fetching the system prompt, call `getRelevantChunks(userId,
userMessage)` using the latest user message
- Format the retrieved chunks into a context block, e.g.: `"Relevant
Knowledge:\n[chunk contents]"`
- Append the formatted context to the system prompt or pass it as an additional
context parameter to the agents

**Agent Update (`lib/agents/researcher.tsx`):**
- Add an optional `ragContext` parameter alongside the existing
`dynamicSystemPrompt` parameter
- Update `getDefaultSystemPrompt()` or the prompt construction logic to
incorporate `ragContext` when it is present
- Include an instruction in the system prompt directing the model to reference
the provided knowledge sources when relevant

💡 Iterate on the plan with: @coderabbitai <feedback>

Example Feedback
- `@coderabbitai` You can skip phase 3. Add a simple unit test case for phase 2.
- `@coderabbitai` For design choice 1 go ahead with option 3 and replan.

---

💬 Have feedback or questions? Drop into our discord!