@stackline/ai-rag-postgres

Read-only PostgreSQL RAG retriever for Stackline AI, using parameterized SQL, stable views, row mapping, tenant-safe query hooks, and backend-only database credentials.

Documentation & Live Demos | npm | Issues | Repository | Community Discussions

Latest tested package release: 0.0.3

Credits: Stackline AI package architecture, publishing, and documentation by Alexandro Paixao Marques.

Why this package?

@stackline/ai-rag-postgres gives Stackline AI a database retrieval layer without tying the provider to PostgreSQL. The core receives normalized StacklineRagContext[], so the same provider/UI path works with or without RAG.

Features

| Feature | Supported | | :--- | :---: | | PostgreSQL connection string | ✅ | | Existing client/pool support | ✅ | | Parameterized SQL helper | ✅ | | Custom query callback | ✅ | | Custom row mapping | ✅ | | Minimum query length | ✅ | | Result limit | ✅ | | Read-only view friendly | ✅ | | TypeScript declarations | ✅ |

Table of Contents

1. Why this package?
2. Features
3. Status
4. Where This Fits
5. Install By Situation
6. Database Shape
7. Complete Integration
8. Prove RAG Is Used
9. Public API
10. Query Contract
11. Row Mapping
12. Security

Status

Initial public API, ESM-only, TypeScript declarations included.

Where This Fits

This package is backend-only retrieval. It reads context from PostgreSQL and returns StacklineRagContext[] to @stackline/ai.

Runtime path:

Browser UI
  -> @stackline/ai-server
  -> @stackline/ai
  -> @stackline/ai-rag-postgres retrieves context
  -> provider receives context as a system message

The browser should never receive database URLs, SQL, or tenant filters.

Install By Situation

RAG Retriever Only

Use this when you are wiring PostgreSQL retrieval into an existing Stackline backend.

npm init -y
npm pkg set type=module
npm install @stackline/ai @stackline/ai-rag-postgres

Full UI App With Ollama And PostgreSQL RAG

npm init -y
npm pkg set type=module
npm install @stackline/ai @stackline/ai-server @stackline/ai-ollama @stackline/ai-ui @stackline/ai-rag-postgres
npm install -D vite
mkdir -p src sql

Add to .env:

STACKLINE_AI_RAG=true
RAG_DATABASE_URL=postgres://readonly_user:[email protected]:5432/app
RAG_MIN_QUERY_LENGTH=2
RAG_LIMIT=4

Requirements

• Runtime: Node.js >=18.17.0.
• PostgreSQL connection string or compatible query client.
• Read-only SQL query or custom query function.

When To Use

Use this package when your RAG context can be read from PostgreSQL tables, views, or tenant-filtered queries.

When Not To Use

Do not use it as a vector database replacement. The current package is lexical SQL retrieval unless you provide your own SQL/ranking.

Database Shape

A stable view is the simplest production contract:

create table if not exists documents (
  id text primary key,
  title text not null,
  content text not null,
  source text,
  metadata jsonb default '{}'::jsonb,
  updated_at timestamptz default now()
);

create or replace view stackline_ai_rag_view as
select id, title, content, source, metadata, updated_at
from documents;

Grant the application a read-only user for this view.

Complete Integration

import { createStacklineAIServer } from "@stackline/ai/server";
import { createPostgresRagRetriever } from "@stackline/ai-rag-postgres";
import { ollamaProvider } from "@stackline/ai-ollama";

const retriever = createPostgresRagRetriever({
  connectionString: process.env.RAG_DATABASE_URL,
  sql: `
    select id, title, content, source, metadata, 100 as score
    from stackline_ai_rag_view
    where content ilike $1 or title ilike $1
    order by updated_at desc
    limit $2
  `,
  minQueryLength: 2,
  limit: 4,
});

const ai = createStacklineAIServer({
  provider: ollamaProvider({
    target: process.env.OLLAMA_TARGET || "http://127.0.0.1:11434",
    model: process.env.OLLAMA_MODEL || "auto",
  }),
  rag: {
    retriever,
    maxContextItems: 4,
    onFailure: "continue",
  },
  memory: false,
});

process.on("SIGINT", async () => {
  await retriever.close();
  process.exit(0);
});

Use @stackline/ai-server to expose this ai instance over HTTP.

Prove RAG Is Used

Seed:

insert into documents (id, title, content, source, metadata)
values (
  'apollo',
  'Apollo Project',
  'Apollo is the internal codename for the Stackline AI starter project.',
  'seed:apollo',
  '{"kind":"demo"}'
);

Ask:

{
  "model": "llama3.1",
  "messages": [
    { "role": "user", "content": "What is Apollo in this database?" }
  ]
}

The provider receives a prepended system message containing the retrieved context. The HTTP response metadata includes RAG evidence under message.metadata.stacklineRag.

Public API

• createPostgresRagRetriever(options)
• StacklinePostgresRagRetrieverOptions
• StacklinePostgresQuery
• StacklinePostgresQueryable

Options

• connectionString
• connection
• client
• sql
• query
• mapRow
• limit
• minQueryLength

Query Contract

With sql, Stackline supplies:

values: [`%${query}%`, limit]

For advanced filters, use query({ query, request, limit }):

createPostgresRagRetriever({
  connectionString: process.env.RAG_DATABASE_URL,
  query: ({ query, request, limit }) => ({
    text: `
      select id, title, content, source, metadata, 100 as score
      from stackline_ai_rag_view
      where tenant_id = $1 and (content ilike $2 or title ilike $2)
      order by updated_at desc
      limit $3
    `,
    values: [request.metadata?.tenantId, `%${query}%`, limit],
  }),
});

Row Mapping

Default mapping prefers content, text, body, or description. Provide mapRow for domain-specific metadata:

createPostgresRagRetriever({
  connectionString: process.env.RAG_DATABASE_URL,
  sql: "select title, body, url, rank as score from docs where body ilike $1 limit $2",
  mapRow: (row) => ({
    content: row.body,
    source: row.url,
    score: row.score,
    metadata: { title: row.title },
  }),
});

Test The Example

pnpm --filter stackline-ai-example-postgres-rag smoke

Security

Use read-only database users, stable views, tenant filters, and parameterized queries. Do not expose SQL or connection strings to browsers.

Limitations

No embeddings are implemented in this package line. Add embeddings in SQL or a separate retriever when needed.

Versioning

Use the same release line as @stackline/ai.

License

MIT

Documentation

• Full tutorial: docs/getting-started/full-stack-tutorial.md
• Production guide: docs/guides/production.md