DEV Community: vapmail16

I set up my own chatgpt for analysing company reports — here's exactly how you can too

vapmail16 — Mon, 02 Mar 2026 17:27:19 +0000

why i did this

i spend a lot of time analysing company reports — annual filings, quarterly earnings, financial statements. i was using chatgpt and claude for this but had a few problems:

privacy — i didn't want to upload sensitive financial documents to third party AI services
cost — the subscriptions add up, especially if you want the good models
control — i wanted to customise the system prompt, use specific models, and share access with a friend who does similar analysis

so i decided to set up my own private chatgpt-like interface running on a rented GPU. the whole thing costs me less than £1 per session and my data never leaves a server i control.

here's exactly how i did it.

what you're building

by the end of this guide you'll have:

a chatgpt-like web interface you can access from any browser
the ability to upload PDFs and chat about them (company reports, annual filings, etc.)
a vision model that can read charts, tables and images from reports
multi-user access so you can share it with a colleague or friend
a web research tool that searches the internet and analyses results using your local model

and it all runs on a rented GPU that you can stop and start whenever you want.

the stack

Vast.ai — rent a GPU by the hour (way cheaper than buying one)
Ollama — runs AI models on the GPU
Open WebUI — the chatgpt-like interface with PDF upload, conversation history, user management
Qwen2.5:32b — brilliant open source model for financial analysis
Qwen2.5-VL:7b — vision model for reading charts and images

total cost: roughly $0.40/hour when running, basically nothing when stopped.

step 1: rent a GPU on vast.ai

go to cloud.vast.ai and create an account if you haven't already. add some credit — $5-10 is enough to get started.

picking the right GPU

click Search and look for instances with:

GPU: RTX 5090 (32GB), RTX 4090 (24GB), RTX 3090 (24GB), or A6000 (48GB)
VRAM: 24GB minimum, 32GB+ ideal
Disk: 60GB+
Reliability: 95%+

for company report analysis, the 32B parameter model gives the best results and needs around 20GB of VRAM. if you can only get a 24GB card, the 14B model still works well.

selecting the template

this is the key bit that saves you loads of setup time.

click Select Template on the left panel
scroll down and find "Open Webui (Ollama)"
select it

this template comes pre-configured with Ollama and Open WebUI already installed. no messing about with docker commands or manual installation.

before you click rent

set Container Size to at least 80GB (models take up space)
pick your instance from the list
click RENT

a word of warning

i tried an RTX 5090 instance first and got a GPU error — Error: GPU error, unable to start instance. this happens sometimes on vast.ai, especially with newer hardware. if this happens to you, just destroy the instance and rent a different one. my second attempt with a different host worked within seconds.

step 2: wait for it to boot

once you click rent, go to the Instances tab in the left sidebar. you'll see your instance with a status indicator.

it goes through a few stages:

Loading — downloading the docker image
Starting — preparing GPUs
Running — ready to go

the first boot can take anywhere from 30 seconds to 20 minutes depending on whether the host has the docker image cached. if it's taking ages, check the status text — you should see progress like "Pull complete" and "Verifying Checksum".

once it shows a blue "Open" button, you're good.

step 3: download the AI models

click Open on your instance. you'll see the vast.ai applications dashboard with several options.

click "Launch Application" on Jupyter Terminal. this opens a command line in your browser.

run these commands:

ollama pull qwen2.5:32b

this downloads the main analysis model (~19GB). takes about 10 minutes depending on the connection speed.

then download the vision model for reading charts and images:

ollama pull qwen2.5vl:7b

note: it's qwen2.5vl not qwen2.5-vl — i got tripped up by this myself. the download is about 6GB.

you can verify both models are there by running:

ollama list

step 4: open the chat interface

go back to the applications dashboard and click "Launch Application" on Open Webui.

this opens the chatgpt-like interface. the first thing you'll see is a signup page.

important: the first person to sign up becomes the admin. so make sure you create your account before sharing the link with anyone.

once you're in, you'll see a familiar chat interface with a model dropdown at the top. select qwen2.5:32b and start chatting.

step 5: set up the system prompt

this makes a massive difference to the quality of analysis you get.

in Open WebUI, go to Settings (the sliders icon at the top) and find the System Prompt section. paste this:

You are a senior financial analyst with 20 years of experience analysing
company reports, annual filings, quarterly earnings, and market data.

When analysing any document or question:
- Always cite specific numbers with their context (page, section, table)
- Flag inconsistencies between different sections of a report
- Compare metrics against industry benchmarks
- Identify both risks and opportunities
- Be precise — if you're unsure about a number, say so
- Never fabricate or hallucinate data points

Structure detailed analyses as:
1. EXECUTIVE SUMMARY
2. KEY FINANCIALS (revenue, profit, margins, growth)
3. RISKS & RED FLAGS
4. OPPORTUNITIES
5. OUTLOOK & RECOMMENDATION

For quick questions, respond concisely without this structure.

the difference between a generic model response and one with a good system prompt is night and day. it stops the model from being wishy-washy and forces it to give you structured, actionable analysis.

step 6: upload and analyse PDFs

click the "+" button at the bottom-left of the chat input to upload files.

Open WebUI extracts text from PDFs automatically using its built-in RAG (retrieval augmented generation) pipeline. so you can upload an annual report and ask things like:

"summarise the key financials from this report"
"what are the main risk factors mentioned?"
"compare the revenue growth year over year"
"what does management say about future outlook?"
"extract all the numbers from the balance sheet"

for charts and images

switch to the qwen2.5vl:7b model using the dropdown at the top. this model can see and understand images. upload a screenshot of a chart or table and ask:

"what does this chart show?"
"extract the data from this table"
"what trend is this graph showing?"

step 7: share with your friend

this was one of my main requirements — being able to share access with someone else for joint analysis.

the URL in your browser when you have Open WebUI open is your access link. it looks something like:

https://something-something.trycloudflare.com/

send that to your friend. they can create their own account with their own username and password.

lock it down

once your friend has signed up, you don't want random people creating accounts. as the admin:

click your profile icon (bottom-left)
go to Admin Panel
navigate to Settings → General
toggle off "Enable New Sign Ups"
save

now only you two can access it.

step 8: stop it when you're done

this is crucial for managing costs.

when you're done analysing for the day:

go to your vast.ai instances page
click the stop button (the power icon) — NOT destroy
your instance goes to sleep

when stopped, you only pay a tiny storage fee (around $0.02/hr). all your models, conversations, and settings are preserved.

when you want to use it again, just click resume and wait a minute for it to start up. everything will be exactly as you left it.

destroy the instance only when you're completely done and don't need it anymore.

bonus: web research tool

i also set up a python script that searches the web and feeds the results to the local model for analysis. this is useful for enriching your PDF analysis with current market data, news, and competitor information.

SSH into your instance or use the Jupyter Terminal and create this file:

# web-research.py
from openai import OpenAI
from duckduckgo_search import DDGS

client = OpenAI(
    base_url="http://localhost:11434/v1",
    api_key="ollama"
)

def research(query):
    # search the web
    print(f"searching: {query}")
    with DDGS() as ddgs:
        results = list(ddgs.text(query, max_results=8))

    search_text = "\n".join(
        [f"[{i+1}] {r['title']}: {r['body']}" for i, r in enumerate(results)]
    )

    # analyse with local model
    response = client.chat.completions.create(
        model="qwen2.5:32b",
        messages=[
            {"role": "system", "content": "You are a financial analyst. Cite sources, flag risks, be precise."},
            {"role": "user", "content": f"Research: {query}\n\nResults:\n{search_text}\n\nProvide analysis."}
        ],
        temperature=0.3
    )
    print(response.choices[0].message.content)

# usage
import sys
if len(sys.argv) > 1:
    research(" ".join(sys.argv[1:]))

install the dependencies:

pip install openai duckduckgo-search

run it:

python3 web-research.py "Tesla Q4 2025 earnings analysis"
python3 web-research.py "Compare Nvidia vs AMD data centre revenue"

costs breakdown

let's talk real numbers.

what	cost
RTX 5090 (32GB) per hour	~$0.40
a typical 2-3 hour analysis session	~$0.80-1.20
storage while stopped per day	~$0.50
monthly usage (2hrs/day, 20 days)	~$25-35

compare that to chatgpt plus at $20/month or claude pro at $20/month — you're getting a much more powerful model with complete privacy and control for roughly the same price.

what i learned

a few things worth noting from going through this process:

template selection matters. using the pre-built "Open Webui (Ollama)" template on vast.ai saved me hours of setup time. trying to install everything manually from a bare ubuntu image is painful and error-prone.

host reliability varies. my first GPU instance failed with a GPU error. the second one worked instantly. if something fails, just destroy it and try a different host. don't waste time debugging someone else's hardware.

model names can be tricky. qwen2.5-vl:7b doesn't exist but qwen2.5vl:7b does. small details like hyphens matter. always check the ollama library for exact model names.

system prompts make a huge difference. the same model with a generic prompt gives you generic answers. with a targeted financial analysis prompt, it gives you structured, specific, actionable insights. invest time in getting your system prompt right.

the 32B model is the sweet spot. it's significantly better than 14B models at understanding context, catching nuances in financial documents, and giving structured analysis. if you can get a 32GB VRAM GPU, go for the 32B model. if not, 14B is still decent.

the model choice guide

depending on what GPU you can rent:

VRAM available	best model	quality
24GB	qwen2.5:14b	good for summaries and basic analysis
32GB	qwen2.5:32b	excellent — catches nuances, structured output
48GB+	qwen2.5:72b	best possible — deep analysis, cross-referencing

for the vision model (charts, images), qwen2.5vl:7b works great on any of these setups.

wrapping up

the whole setup takes about 30 minutes from zero to a working system. the first time i did it, it took longer because of the GPU error and the model name typo, but once you know the steps it's straightforward.

if you're doing any kind of company analysis, financial research, or document review, this is genuinely worth setting up. you get the power of a large language model with complete privacy and control over your data.

the best part? when you're not using it, you stop the instance and it costs almost nothing. when you need it again, it boots up in a minute with everything preserved.

if you've got questions or run into issues, drop a comment and i'll try to help.

tools used: vast.ai | ollama | open webui | qwen2.5

NextSaaS: "Would Your SaaS Pass a Security Audit? (Honest Checklist)

vapmail16 — Fri, 27 Feb 2026 17:14:56 +0000

Would Your SaaS Pass a Security Audit? (Honest Checklist)
When I ran OWASP ZAP against my own app, I expected a clean report. I'd been careful about security from day one — parameterized queries, proper authentication, HTTPS everywhere.
Instead, I found 3 medium-severity issues in the first scan.
That scan taught me something important: there's a massive gap between "secure" and "provably secure." The first means you haven't been hacked yet. The second means you can demonstrate to an auditor, a customer, or a regulator that your systems are hardened, logged, and defensible.
Here's the checklist I built after going through this process. Score yourself honestly.

Encryption at Rest — Not Just HTTPS Most developers stop at HTTPS. "Data is encrypted in transit — we're good." Auditors ask a different question: Is PII encrypted in your database? If someone gains database access — a leaked backup, a compromised admin account, a SQL injection you missed — can they read your users' email addresses, phone numbers, and payment references in plain text? Field-level encryption is the answer. AES-256-GCM is the standard for SOC 2 and enterprise compliance. Here's the pattern I use: typescriptimport crypto from 'crypto';

const ALGORITHM = 'aes-256-gcm';
const KEY = Buffer.from(process.env.ENCRYPTION_KEY!, 'hex'); // 32 bytes

export function encrypt(text: string): string {
const iv = crypto.randomBytes(16);
const cipher = crypto.createCipheriv(ALGORITHM, KEY, iv);

let encrypted = cipher.update(text, 'utf8', 'hex');
encrypted += cipher.final('hex');

const authTag = cipher.getAuthTag().toString('hex');
return ${iv.toString('hex')}:${authTag}:${encrypted};
}

export function decrypt(encryptedText: string): string {
const [ivHex, authTagHex, encrypted] = encryptedText.split(':');

const iv = Buffer.from(ivHex, 'hex');
const authTag = Buffer.from(authTagHex, 'hex');
const decipher = crypto.createDecipheriv(ALGORITHM, KEY, iv);
decipher.setAuthTag(authTag);

let decrypted = decipher.update(encrypted, 'hex', 'utf8');
decrypted += decipher.final('utf8');
return decrypted;
}
What to encrypt: email addresses, phone numbers, payment references, any PII. Not everything — just the fields that would cause damage if exposed.
Quick test: If you dumped your database right now, could someone read your users' emails? If yes, you'd fail this check.

Audit Logging — The Thing That Makes or Breaks Audits Logging is not console.log("User logged in"). Audit-grade logging means a complete, immutable, queryable trail of every significant action in your system. Here's the schema I use: prismamodel AuditLog { id String @id @default(cuid()) userId String? action String // LOGIN_SUCCESS, LOGIN_FAILURE, DATA_EXPORT, USER_DELETE entity String // USER, PAYMENT, SETTINGS entityId String? before Json? // State before the change after Json? // State after the change ipAddress String userAgent String requestId String // Correlation ID for tracing metadata Json? createdAt DateTime @default(now())

@@index([userId])
@@index([action])
@@index([entity, entityId])
@@index([createdAt])
}
The key properties auditors look for:

Completeness: Every login attempt (success AND failure), every data modification (with before/after values), every admin action.
Immutability: Append-only. No updates, no deletes. If someone tampers with logs, you've lost your evidence.
Retention: 7 years for financial data. Configurable per data type.
Queryable: "Show me every action User X took in the last 30 days." If you can't answer this in seconds, your logging isn't audit-ready.

The service function:
typescriptexport async function createAuditLog(data: {
userId?: string;
action: string;
entity: string;
entityId?: string;
before?: any;
after?: any;
ipAddress: string;
userAgent: string;
requestId: string;
}) {
return prisma.auditLog.create({
data: {
...data,
before: data.before ? JSON.parse(JSON.stringify(data.before)) : undefined,
after: data.after ? JSON.parse(JSON.stringify(data.after)) : undefined,
},
});
}
Quick test: Can you tell me exactly what admin actions were performed on your system last Tuesday? If not, you'd fail this check.

Rate Limiting Per Endpoint Global rate limiting — "100 requests per minute per IP" — is a start. But it's not enough. Each endpoint has different attack vectors and different acceptable thresholds: typescriptimport rateLimit from 'express-rate-limit';

// Login: prevent brute force
export const loginLimiter = rateLimit({
windowMs: 15 * 60 * 1000, // 15 minutes
max: 5, // 5 attempts
keyGenerator: (req) => req.ip,
message: 'Too many login attempts. Try again in 15 minutes.',
});

// Password reset: prevent email bombing
export const passwordResetLimiter = rateLimit({
windowMs: 60 * 60 * 1000, // 1 hour
max: 3,
keyGenerator: (req) => req.body.email || req.ip,
message: 'Too many password reset requests.',
});

// API: general usage
export const apiLimiter = rateLimit({
windowMs: 60 * 1000, // 1 minute
max: 100,
keyGenerator: (req) => req.user?.id || req.ip,
});

// Registration: prevent mass account creation
export const registrationLimiter = rateLimit({
windowMs: 60 * 60 * 1000,
max: 10,
keyGenerator: (req) => req.ip,
});

// MFA verification: prevent code guessing
export const mfaLimiter = rateLimit({
windowMs: 15 * 60 * 1000,
max: 3,
keyGenerator: (req) => req.sessionID || req.ip,
});
Apply them individually:
typescriptapp.post('/api/auth/login', loginLimiter, authController.login);
app.post('/api/auth/reset-password', passwordResetLimiter, authController.resetPassword);
app.post('/api/auth/verify-mfa', mfaLimiter, authController.verifyMfa);
app.post('/api/auth/register', registrationLimiter, authController.register);
app.use('/api/', apiLimiter);
Quick test: Could someone attempt 1,000 logins to your API right now? If yes, you'd fail this check.

PII in Logs — The Silent Compliance Killer I found this pattern in a real production codebase: typescript// ❌ WRONG — this logs passwords and emails in plain text logger.info("User login attempt:", { email, password }); Passwords. In. Logs. Sitting in CloudWatch, Datadog, or a log file on a server somewhere, accessible to anyone with log access. The fix is a PII masking format for your logger: typescriptimport winston from 'winston';

const sensitiveFields = ['password', 'token', 'secret', 'authorization',
'creditCard', 'ssn', 'refreshToken'];

const emailRegex = /([a-zA-Z0-9._%+-]+)@([a-zA-Z0-9.-]+.[a-zA-Z]{2,})/g;

const piiMaskingFormat = winston.format((info) => {
const masked = JSON.parse(JSON.stringify(info));

// Mask sensitive fields
for (const field of sensitiveFields) {
if (masked[field]) {
masked[field] = '[REDACTED]';
}
}

// Mask email addresses
const str = JSON.stringify(masked);
const cleaned = str.replace(emailRegex, (match, local, domain) => {
return ${local[0]}***@${domain};
});

return JSON.parse(cleaned);
});

export const logger = winston.createLogger({
level: process.env.LOG_LEVEL || 'info',
format: winston.format.combine(
piiMaskingFormat(),
winston.format.timestamp(),
winston.format.json()
),
transports: [
new winston.transports.File({ filename: 'logs/error.log', level: 'error' }),
new winston.transports.File({ filename: 'logs/combined.log' }),
],
});
Now logger.info("Login:", { email: "john@example.com", password: "secret123" }) outputs:
json{ "message": "Login:", "email": "j***@example.com", "password": "[REDACTED]" }
Quick test: Search your logs for @gmail.com or @yahoo.com right now. If you find full email addresses, you have a GDPR problem.

Security Headers — 2 Lines of Code, Massive Impact Helmet.js handles this in Express: typescriptimport helmet from 'helmet';

app.use(helmet({
contentSecurityPolicy: {
directives: {
defaultSrc: ["'self'"],
scriptSrc: ["'self'"],
styleSrc: ["'self'", "'unsafe-inline'"],
imgSrc: ["'self'", 'data:', 'https:'],
},
},
hsts: {
maxAge: 31536000,
includeSubDomains: true,
preload: true,
},
}));
This gives you: X-Content-Type-Options, X-Frame-Options, Strict-Transport-Security, Content-Security-Policy, and more. Each header blocks a specific class of attacks.
CORS configuration matters too:
typescriptimport cors from 'cors';

app.use(cors({
origin: process.env.FRONTEND_URL, // NOT ""
credentials: true,
methods: ['GET', 'POST', 'PUT', 'DELETE', 'PATCH'],
allowedHeaders: ['Content-Type', 'Authorization'],
}));
origin: "" is the most common security misconfiguration I see in SaaS codebases. It means any website can make authenticated requests to your API.
Quick test: Run curl -I https://your-app.com and check the response headers. No Strict-Transport-Security? You'd fail.

Input Validation at the Edge Every request should be validated before it reaches your controller. Not inside the controller — at the middleware layer: typescriptimport { z } from 'zod'; import { Request, Response, NextFunction } from 'express';

// Define schema
const registerSchema = z.object({
email: z.string().email(),
password: z.string()
.min(8, 'Password must be at least 8 characters')
.regex(/[A-Z]/, 'Must contain uppercase letter')
.regex(/[a-z]/, 'Must contain lowercase letter')
.regex(/[0-9]/, 'Must contain number'),
name: z.string().min(1).max(100),
});

// Validation middleware factory
export function validate(schema: z.ZodSchema) {
return (req: Request, res: Response, next: NextFunction) => {
const result = schema.safeParse(req.body);
if (!result.success) {
return res.status(400).json({
success: false,
errors: result.error.issues.map(i => ({
field: i.path.join('.'),
message: i.message,
})),
});
}
req.body = result.data; // Use parsed/cleaned data
next();
};
}

// Usage
app.post('/api/auth/register',
registrationLimiter,
validate(registerSchema),
authController.register
);
Your controller now receives guaranteed clean, typed data. Zero "cannot read property of undefined" errors. And Prisma's parameterized queries handle SQL injection protection at the ORM layer.
Quick test: Send {"email": "not-an-email", "password": "1"} to your registration endpoint. Does it return a clear validation error, or does it crash?

Security Audit Readiness Score
Score yourself honestly. 10 points each:

CheckY/N1PII is encrypted at rest (not just HTTPS)2Audit logs capture every login attempt (success + failure)3Audit logs include before/after values for data changes4Logs are append-only (immutable)5Rate limiting is per-endpoint, not just global6No PII in log files (emails, passwords, tokens)7Security headers set via Helmet.js (or equivalent)8CORS is configured to specific origins (not "*")9All input is validated at the middleware layer10You can query "what did User X do last Tuesday?" in seconds

Scoring:

90-100: Audit-ready. You're ahead of most SaaS products.
60-80: Solid foundation, gaps to close. Prioritize encryption and logging.
30-50: Significant work needed. Start with Helmet.js and rate limiting — quickest wins.
0-20: Honest. And fixable. Every item above is buildable in a day or less.

The difference between "secure" and "audit-ready" is documentation and traceability. If you can't prove it's secure, it's not secure enough.
Run these checks against your own app. Score honestly.
What did you get?

I Switched From Pure Vector Search to Hybrid Retrieval in My RAG System — Here's What Changed

vapmail16 — Thu, 26 Feb 2026 21:51:00 +0000

I've been building RAG (Retrieval-Augmented Generation) systems for a while now, and I recently made one change that boosted my retrieval accuracy from ~60% to ~85%.

The change? Adding BM25 keyword matching alongside my existing vector search.

That's it. No fancy model swaps. No expensive rerankers. Just combining two search strategies that complement each other's blind spots.

Let me walk you through exactly what happened, why it works, and what I learned from other engineers running RAG in production.

The Problem With Pure Vector Search

Vector search (using embeddings + cosine similarity) is incredible at understanding meaning. Ask it for "employee vacation policy" and it'll find documents about "time off benefits," "annual leave," and "PTO guidelines."

But here's the catch — it sometimes misses exact terminology.

In my test set of 50 questions against internal documentation, I kept running into this pattern:

User query: "What's the PTO policy?"

Vector search found: Chunks about "vacation time," "time off benefits," "leave of absence"

Vector search missed: The exact chunk that contained the acronym "PTO"

The embeddings understood the concept of paid time off perfectly. But when a document used a specific acronym, abbreviation, or domain-specific term, vector search would sometimes grab semantically similar but wrong chunks.

This matters a lot in production. Your users don't speak in perfect semantic paragraphs — they use acronyms, product names, jargon, and exact phrases they remember from documents.

Enter Hybrid Retrieval: Vector + BM25

BM25 is an old-school keyword matching algorithm. It doesn't understand meaning at all — it just finds documents that contain the exact words in your query. Think of it as a very sophisticated Ctrl+F.

Hybrid retrieval combines both:

Vector search finds chunks that are semantically similar to your query
BM25 finds chunks that contain exact keyword matches
Reciprocal Rank Fusion (RRF) merges both result sets into a single ranked list

Here's a simplified view of how RRF works:

def reciprocal_rank_fusion(vector_results, bm25_results, k=60):
    """
    Merge two ranked lists using RRF.
    k=60 is the standard constant that controls 
    how much weight lower-ranked results get.
    """
    fused_scores = {}

    for rank, doc in enumerate(vector_results):
        fused_scores[doc.id] = fused_scores.get(doc.id, 0) + 1 / (k + rank + 1)

    for rank, doc in enumerate(bm25_results):
        fused_scores[doc.id] = fused_scores.get(doc.id, 0) + 1 / (k + rank + 1)

    # Sort by combined score — docs appearing in BOTH lists rank highest
    return sorted(fused_scores.items(), key=lambda x: x[1], reverse=True)

The beauty of RRF is that documents appearing in both result sets naturally bubble to the top. If a chunk is semantically relevant AND contains exact keywords, it's almost certainly the right one.

My Results

Metric	Pure Vector	Hybrid (Vector + BM25)
Accuracy (50 questions)	~60%	~85%
Acronym/jargon queries	Poor	Strong
Natural language queries	Strong	Strong
Implementation complexity	Simple	Moderate

The 25-percentage-point jump came almost entirely from queries involving exact terminology — acronyms, product names, specific phrases, and domain jargon.

For natural language queries like "how do I request time off?", both approaches performed similarly. The hybrid approach essentially kept vector search's strengths while patching its biggest weakness.

What I Learned From Other Engineers

After sharing these results, I got some fascinating insights from engineers running RAG in production. Here are the key takeaways:

1. "It depends on your data" is annoyingly true

For technical/QA documentation (code, APIs, specs), BM25 alone can outperform vector search because queries tend to use exact terms. For enterprise/business documents with natural language, vector search pulls more weight. Hybrid gives you the best of both worlds, but the weighting between vector and BM25 scores should be tuned for your specific corpus.

2. Rerankers aren't always worth the latency

Cross-encoder rerankers (a second-pass model that re-scores your top results) are often recommended as the next step after hybrid retrieval. But several production teams reported minimal improvement when their initial retrieval was already solid. One engineer measured an NDCG of 0.74 on their hybrid setup and saw almost no gain from adding a reranker — so they dropped it to reduce latency.

Takeaway: If your hybrid retrieval is already good, a reranker might just add 100-200ms of latency for marginal improvement. Measure before committing.

3. Chunk size is NOT a solved problem

I started with ~500 characters and 100-character overlap, which works OK for my use case. But the consensus from production teams is clear: there is no universal best chunk size.

The right size depends on:

Document type: Legal docs need larger chunks (full clauses); FAQs need smaller ones
Query patterns: How specific are your users' questions?
Content structure: Is your data prose, tables, code, or mixed?

A good starting heuristic: think of chunks as "one complete thought." For prose, that's roughly a paragraph. For code, it's a function. For FAQs, it's a question-answer pair.

4. Dimensionality reduction is an underexplored lever

One interesting approach I came across: reducing embedding vectors from their native dimensions (e.g., 1536 for OpenAI) down to 25-100 dimensions using PCA, UMAP, or similar methods. The goal is to strip away noisy dimensions and keep only the ones that carry meaningful signal.

This could potentially improve accuracy AND speed — smaller vectors mean faster similarity search and less noise in the matching. Worth experimenting with if you're at scale.

Getting Started With Hybrid Retrieval

If you're running pure vector search and want to try hybrid, here's a practical starting point:

Option A: PostgreSQL + pgvector

If you're already using Postgres, you can run both searches in the same database:

-- Vector search (pgvector)
SELECT id, content, embedding <=> query_embedding AS vector_distance
FROM documents
ORDER BY embedding <=> query_embedding
LIMIT 20;

-- BM25 search (pg_trgm or ParadeDB for proper BM25)
SELECT id, content, ts_rank(to_tsvector(content), plainto_tsquery('PTO policy')) AS bm25_score
FROM documents
WHERE to_tsvector(content) @@ plainto_tsquery('PTO policy')
ORDER BY bm25_score DESC
LIMIT 20;

-- Merge results in application code using RRF

Option B: Dedicated tools

Weaviate, Qdrant, and Milvus all support hybrid search natively
LangChain and LlamaIndex have hybrid retriever implementations
NornicDB (MIT licensed) handles the full pipeline — embedding, BM25, reranking — in-process with impressive latency (~7ms on 1M documents)

Option C: Keep it simple

If you want minimal infrastructure, just run:

Your existing vector DB for semantic search
Elasticsearch or even SQLite FTS5 for BM25
Merge results with the RRF function above

It's not elegant, but it works. You can optimize later.

The 80/20 of RAG Optimization

Here's what I've learned about where to spend your time:

High impact, do first:

Switch from pure vector to hybrid retrieval
Build a proper evaluation set (50+ questions with expected answers)
Tune your chunk boundaries to match document structure

Moderate impact, do second:

Experiment with chunk sizes for your specific data
Try different embedding models
Add metadata filtering (date, source, category)

Low impact unless at scale:

Cross-encoder reranking (measure before committing)
Dimensionality reduction on embeddings
HyDE (Hypothetical Document Embeddings)

The single best investment? Build an eval set. Without one, you're just guessing whether your changes help or hurt.

Wrapping Up

Hybrid retrieval isn't new or cutting-edge — BM25 has been around since 1994. But combining it with modern vector search is one of those "boring but effective" improvements that should probably be the default for any production RAG system.

If you're running RAG with pure vector search and your accuracy isn't where you want it to be, try adding BM25 before you reach for more complex solutions. It took me about half a day to implement and delivered a bigger improvement than anything else I've tried.

What's your RAG setup look like? Are you running pure vector, hybrid, or something else entirely? I'd love to hear what's working (or not working) for you in production.

Tags: rag, ai, machinelearning, tutorial

Remote-to-Remote PostgreSQL Migration: Theory and Practice

vapmail16 — Wed, 25 Feb 2026 00:46:12 +0000

Remote-to-Remote PostgreSQL Migration: Theory and Practice

A guide for anyone who needs to clone a PostgreSQL database from one server to another—whether you use Prisma, another ORM, or raw SQL. We explain the concepts first, then show concrete scripts you can adapt. No prior knowledge of our app is required.

Who this is for: Developers or ops people planning a one-off or repeatable database move (new host, new region, new environment). The theory applies to any relational DB; the code examples use PostgreSQL and Prisma and can be translated to other stacks.

Theory: What is “remote-to-remote” migration?

Remote-to-remote means copying a database from one already running database server (the source) to another already running server (the target). Both are “remote” in the sense that they live on a host you connect to over the network—as opposed to dumping on your laptop and restoring elsewhere.

You might do this when:

Changing hosting or region — Moving from one cloud provider or region to another (e.g. new AWS region, new PaaS org).
Disaster recovery or failover — Bringing up a replica or standby in a different datacenter.
Environment cloning — Creating a staging or QA database that mirrors production.
Compliance or isolation — Moving data to a new tenant or organisation boundary (e.g. new “org” in a multi-tenant platform).

In all cases the goal is the same: the target should end up with the same schema (tables, columns, constraints, indexes, enums, triggers) and the same data (rows) as the source, so you can point your application at the new database and keep running.

Theory: Schema vs data

A PostgreSQL database has two main parts:

Schema (structure) — Tables, columns, data types, primary keys, foreign keys, unique and check constraints, indexes, custom types (e.g. enums), sequences, triggers, functions. This is the “shape” of your data.
Data — The actual rows in those tables.

When you migrate:

You must establish the schema on the target first (so tables and constraints exist).
Then you copy the data in an order that respects foreign keys (parent rows before child rows).
Finally you verify that the target matches the source (same tables, same row counts, and optionally same schema objects).

If you use an ORM or migration tool (e.g. Prisma, Flyway, Liquibase), the “schema” on the target is usually created by running your migrations against the new database. That way the target is defined by the same migration history as the rest of your project, not by a one-off dump.

Theory: Two ways to get schema + data to the target

Approach	How schema gets to target	How data gets to target	Best when
A. Deploy then copy	Run your migration tool (e.g. `prisma migrate deploy`) on the target	Application-level copy: connect to both DBs, read rows from source, insert into target in FK order	You want to avoid depending on `pg_dump`/`pg_restore` or your local Postgres client version. Works with any stack that can connect to both DBs.
B. Full dump/restore	`pg_dump` (schema + data) from source → `pg_restore` into target	Same dump file carries both	You’re comfortable with `pg_dump` and your client version is ≥ the server’s. One-shot clone.

Why Option A is often safer: pg_dump and pg_restore are tied to a specific PostgreSQL client version. If the server is newer (e.g. 17) and your laptop has 15, you can hit compatibility issues or subtle errors. With Option A, your app (e.g. Node + Prisma) talks to both databases using the same driver; the server version is what matters, not the tool on your machine.

Theory: Foreign keys and copy order

PostgreSQL (and any relational DB) enforces foreign key constraints: a row in a “child” table cannot reference a non-existent row in the “parent” table. So you must insert parents before children.

Example: if sessions has user_id → users.id, you must copy all users rows before any sessions rows. So the order of tables in your data copy script is critical. You need a list of tables in dependency order (topological order with respect to FKs). You can derive this from your schema or from the database’s information_schema and pg_catalog. In the code below we use a fixed list that we keep in sync with our schema.

Theory: PostgreSQL enums and “migrate-only” targets

PostgreSQL supports custom enum types. Once created, you can add new values with ALTER TYPE ... ADD VALUE. But you cannot add a value to an enum that doesn’t exist. If the target database was created only by running migrations (no full clone from source), it’s possible that an older migration never created a certain enum—e.g. it was added later in the app’s life. In that case, a migration that only does ALTER TYPE "ChartType" ADD VALUE 'TRANSIT_MOON' will fail on that target with “type does not exist”.

So the safe pattern in a migration is: if the enum doesn’t exist, create it with all values; else add the new values. That way the same migration works both on (a) a DB that already had the enum (e.g. cloned from production) and (b) a DB that was built from scratch by running migrations only.

Theory: Verification — row counts and schema diff

Copying data can fail silently (e.g. duplicate key skips, or a few rows error out). So you should verify the target.

Row counts — For each table, compare COUNT(*) on source vs target. If they match (and the set of tables matches), you have strong evidence that data was copied. This is cheap and easy to automate.
Schema diff — Compare not just tables but primary keys, foreign keys, unique constraints, indexes, check constraints, enum types, sequences, and triggers. That way you catch “target missing an index” or “target has different enum values”. Comparing by meaning (e.g. constraint clause, not constraint name) avoids false differences when constraint names are auto-generated differently on each DB.

Optional objects (e.g. a table used only by a trigger on the source) may exist only on one side. You can either apply the same trigger/table on the target, or explicitly exclude those from the schema comparison so they don’t cause a failure.

Why we did it (our case)

We had an existing app with a source database (current production) and a target database (new, empty) in a new organisation. Goal: clone schema and data to the target, verify parity, then point the app at the new DB—without long downtime or manual table-by-table copy. The theory above guided how we designed the scripts.

What we learned (short version)

Migration order matters. If migrations depend on each other (e.g. GDPR tables before a migration that adds columns), rename the migration folders so they run in the right order (e.g. 20251210_1_add_gdpr_tables, 20251210_2_add_error_message).
Enums and “migrate-only” DBs. If the target DB was created only by running Prisma migrations (no prior full clone), the DB might not have an enum that was added later in the schema. Our migration had to create the enum if it doesn’t exist, then add values—otherwise ALTER TYPE ... ADD VALUE fails.
Avoid pg_dump version mismatch. Using pg_dump/pg_restore from a client older than the server can cause subtle failures. We preferred a Node + Prisma data copy so we didn’t depend on local PostgreSQL client versions.
Copy order = FK order. Copy tables in dependency order (parents before children) to satisfy foreign keys. We maintain a single ordered list and use raw SQL with proper enum/JSONB casting.
Verify with row counts and schema. We run a row-count comparison and a separate schema comparison (PKs, FKs, indexes, constraints, enums, sequences, triggers) so we know the new DB is really in parity.
Triggers and optional objects. Things like user_deletion_trigger and user_deletion_logs may exist only on the source. Document them and apply the same SQL on the target (or exclude them from schema compare) so “diff” doesn’t mean “wrong.”
Special characters in passwords. Use URL-encoded passwords in DATABASE_URL (e.g. ^ → %5E, } → %7D).

Two ways to do it (practice)

Below we give concrete scripts for both approaches. You can reuse them for any PostgreSQL project; replace table lists and connection details with your own.

Option A: Deploy migrations on target, then copy data (recommended)

Theory in practice: Schema comes from your migration tool (e.g. Prisma); data is copied by an app that connects to both DBs in FK order; then we verify with row counts (and optionally schema diff). No dependency on local pg_dump version.

Steps: Deploy schema on target → copy data with a Node script → compare row counts (and optionally schema).

Option B: Full clone with pg_dump / pg_restore

Theory in practice: One binary dump carries schema + data. Restore into target. Your local pg_dump/pg_restore version should be ≥ the server’s PostgreSQL version.

Below we focus on Option A (full scripts), then give the exact commands for Option B.

Option A: Step-by-step with code

1. Prerequisites

Node.js, tsx, and Prisma in your backend.
Two env vars (or args): SOURCE_DATABASE_URL (old DB) and TARGET_DATABASE_URL (new, empty DB). Create the target DB in your host (e.g. dcdeploy) first.

2. Deploy schema on the target

We deploy Prisma migrations on the target, then run db push so any tables that exist in the schema but aren’t in migrations (e.g. profiles, charts_cache) are created.

Orchestration script: backend/scripts/deploy-migrate-compare.sh

#!/usr/bin/env bash
#
# 1) Deploy Prisma migrations to the NEW (target) database
# 2) Copy all data from OLD (source) remote to NEW (target) remote (Node script, no pg_dump)
# 3) Compare source vs target to verify parity
#
set -e

SOURCE_URL="${1:-$SOURCE_DATABASE_URL}"
TARGET_URL="${2:-$TARGET_DATABASE_URL}"

if [ -z "$SOURCE_URL" ] || [ -z "$TARGET_URL" ]; then
  echo "Usage: SOURCE_DATABASE_URL=... TARGET_DATABASE_URL=... $0"
  exit 1
fi

SCRIPT_DIR="$(cd "$(dirname "${BASH_SOURCE[0]}")" && pwd)"
cd "$SCRIPT_DIR/.."

echo "=========================================="
echo "1) Deploy migrations to TARGET database"
echo "=========================================="
export DATABASE_URL="$TARGET_URL"
npx prisma migrate deploy

echo "1b) Sync schema (create any tables not in migrations)"
echo "=========================================="
npx prisma db push

echo "=========================================="
echo "2) Copy data: source remote → target remote"
echo "=========================================="
SOURCE_DATABASE_URL="$SOURCE_URL" TARGET_DATABASE_URL="$TARGET_URL" npx tsx scripts/migrate-remote-to-remote-data.ts

echo "=========================================="
echo "3) Compare source vs target"
echo "=========================================="
SOURCE_DATABASE_URL="$SOURCE_URL" TARGET_DATABASE_URL="$TARGET_URL" npx tsx scripts/compare-remote-databases.ts

echo "Done. New database is ready; point app to TARGET_DATABASE_URL."

Run from backend:

cd backend
export SOURCE_DATABASE_URL='postgresql://user:pass@old-host:port/olddb'
export TARGET_DATABASE_URL='postgresql://user:pass@new-host:port/newdb'   # URL-encode password if needed
./scripts/deploy-migrate-compare.sh

3. Data copy script (Node + Prisma, no pg_dump)

Theory in practice: We copy tables in FK order (parents first). For each row we build an INSERT; PostgreSQL requires enum columns to be cast explicitly (e.g. $1::"ChartType") and JSONB to be cast as ::jsonb, so we read column metadata from information_schema and add the right cast per column type. Duplicate key errors are treated as “already exists” (skip) so the script can be re-run safely.

Key ideas:

Two PrismaClient instances (source and target).
Tables listed in dependency order (users → audit_logs → sessions → …).
For each table: SELECT * from source; for each row, build INSERT with $n::"EnumType" for enum columns and $n::jsonb for JSONB.
Skip rows that already exist (by id) to allow re-runs.

File: backend/scripts/migrate-remote-to-remote-data.ts

/**
 * Copy data from one remote database to another (remote → remote).
 * Use after running Prisma migrations on the target. No pg_dump needed.
 *
 * Usage:
 *   SOURCE_DATABASE_URL="postgresql://..." TARGET_DATABASE_URL="postgresql://..." npx tsx scripts/migrate-remote-to-remote-data.ts
 */

import dotenv from 'dotenv';
import { PrismaClient } from '@prisma/client';

dotenv.config();

const sourceUrl = process.env.SOURCE_DATABASE_URL;
const targetUrl = process.env.TARGET_DATABASE_URL;

if (!sourceUrl || !targetUrl) {
  console.error('Set both SOURCE_DATABASE_URL and TARGET_DATABASE_URL');
  process.exit(1);
}

const sourcePrisma = new PrismaClient({ datasources: { db: { url: sourceUrl } } });
const targetPrisma = new PrismaClient({ datasources: { db: { url: targetUrl } } });

// Tables in FK order (adjust for your schema)
const TABLES_IN_ORDER = [
  'users',
  'audit_logs',
  'sessions',
  'password_resets',
  'notifications',
  'notification_preferences',
  'chart_settings',
  'payments',
  'subscriptions',
  'payment_webhook_logs',
  'payment_refunds',
  'data_export_requests',
  'data_deletion_requests',
  'consent_records',
  'profiles',
  'charts_cache',
  'matchings',
  'reports',
  'saved_transits',
  'aspect_relationships',
  'ai_predictions',
  'house_predictions',
  'ascendant_analyses',
];

async function migrateTable(tableName: string): Promise<{ migrated: number; skipped: number; errors: number }> {
  try {
    const rows = await sourcePrisma.$queryRawUnsafe(`SELECT * FROM "${tableName}"`);
    if (!Array.isArray(rows) || rows.length === 0) {
      return { migrated: 0, skipped: 0, errors: 0 };
    }

    const columns = await sourcePrisma.$queryRawUnsafe<
      Array<{ column_name: string; data_type: string; udt_name: string }>
    >(
      `SELECT column_name, data_type, udt_name FROM information_schema.columns 
       WHERE table_schema = 'public' AND table_name = $1
       ORDER BY ordinal_position`,
      tableName
    );
    const columnNames = columns.map((c) => c.column_name);
    const isEnum = (c: (typeof columns)[0]) => c.data_type === 'USER-DEFINED' && c.udt_name;
    const isJson = (c: (typeof columns)[0]) => c.data_type === 'jsonb' || c.udt_name === 'jsonb';
    let migrated = 0, skipped = 0, errors = 0;

    for (const row of rows as Record<string, unknown>[]) {
      try {
        const hasId = row.id !== undefined && row.id !== null;
        if (hasId) {
          const existing = await targetPrisma.$queryRawUnsafe<unknown[]>(
            `SELECT 1 FROM "${tableName}" WHERE id = $1 LIMIT 1`,
            row.id
          );
          if (Array.isArray(existing) && existing.length > 0) {
            skipped++;
            continue;
          }
        }

        const cols: string[] = [];
        const vals: unknown[] = [];
        const placeholders: string[] = [];
        let idx = 0;
        for (let i = 0; i < columnNames.length; i++) {
          const col = columnNames[i];
          if (row[col] === undefined) continue;
          cols.push(`"${col}"`);
          let val = row[col];
          if (isJson(columns[i]) && (typeof val === 'object' || Array.isArray(val))) {
            val = typeof val === 'string' ? val : JSON.stringify(val);
          }
          vals.push(val);
          const colMeta = columns[i];
          if (isEnum(colMeta)) {
            placeholders.push(`$${idx + 1}::"${colMeta.udt_name}"`);
          } else if (isJson(colMeta)) {
            placeholders.push(`$${idx + 1}::jsonb`);
          } else {
            placeholders.push(`$${idx + 1}`);
          }
          idx++;
        }
        await targetPrisma.$executeRawUnsafe(
          `INSERT INTO "${tableName}" (${cols.join(', ')}) VALUES (${placeholders.join(', ')})`,
          ...vals
        );
        migrated++;
      } catch (e: unknown) {
        const err = e as { message?: string };
        if (err?.message?.includes('duplicate key') || err?.message?.includes('unique constraint')) {
          skipped++;
        } else {
          errors++;
          if (errors <= 3) console.error(`   Row error in ${tableName}:`, err?.message ?? e);
        }
      }
    }
    return { migrated, skipped, errors };
  } catch (e: unknown) {
    console.error(`   Table ${tableName}:`, (e as Error).message);
    return { migrated: 0, skipped: 0, errors: 1 };
  }
}

async function main() {
  console.log('Connecting to source and target...');
  await sourcePrisma.$connect();
  await targetPrisma.$connect();
  console.log('✅ Connected\n');
  for (const table of TABLES_IN_ORDER) {
    const result = await migrateTable(table);
    const sym = result.errors > 0 ? '⚠️' : '✅';
    console.log(`${sym} ${table}: migrated=${result.migrated}, skipped=${result.skipped}, errors=${result.errors}`);
  }
  console.log('\nData copy finished. Run compare script to verify.');
  await sourcePrisma.$disconnect();
  await targetPrisma.$disconnect();
}

main().catch((e) => { console.error(e); process.exit(1); });

What to change for your project: set TABLES_IN_ORDER to your tables in FK order (parents first). You can derive the list from prisma migrate or your schema.

4. Row-count comparison script

Theory in practice: We verify parity with row counts (see “Verification” above): list tables in both DBs, then for each common table compare COUNT(*). If any table is missing on target or counts differ, we exit with failure so the migration is not considered successful.

File: backend/scripts/compare-remote-databases.ts

/**
 * Compare two remote databases: table list and row counts per table.
 * Usage: SOURCE_DATABASE_URL="..." TARGET_DATABASE_URL="..." npx tsx scripts/compare-remote-databases.ts
 */

import dotenv from 'dotenv';
import { PrismaClient } from '@prisma/client';

dotenv.config();

const sourceUrl = process.env.SOURCE_DATABASE_URL;
const targetUrl = process.env.TARGET_DATABASE_URL;

if (!sourceUrl || !targetUrl) {
  console.error('Set both SOURCE_DATABASE_URL and TARGET_DATABASE_URL');
  process.exit(1);
}

const sourcePrisma = new PrismaClient({ datasources: { db: { url: sourceUrl } } });
const targetPrisma = new PrismaClient({ datasources: { db: { url: targetUrl } } });

async function getTables(prisma: PrismaClient): Promise<string[]> {
  const rows = await prisma.$queryRawUnsafe<Array<{ tablename: string }>>(
    `SELECT tablename FROM pg_tables WHERE schemaname = 'public' AND tablename NOT LIKE '_prisma%' ORDER BY tablename`
  );
  return rows.map((r) => r.tablename);
}

async function getRowCount(prisma: PrismaClient, table: string): Promise<number> {
  const rows = await prisma.$queryRawUnsafe<Array<{ count: bigint }>>(
    `SELECT COUNT(*) as count FROM "${table}"`
  );
  return Number(rows[0]?.count ?? 0);
}

async function main() {
  await sourcePrisma.$connect();
  await targetPrisma.$connect();

  const sourceTables = await getTables(sourcePrisma);
  const targetTables = await getTables(targetPrisma);
  const common = sourceTables.filter((t) => targetTables.includes(t));
  const onlyInSource = sourceTables.filter((t) => !targetTables.includes(t));

  if (onlyInSource.length > 0) {
    console.log('❌ Tables only in SOURCE:', onlyInSource);
  }

  let allMatch = true;
  for (const table of common.sort()) {
    const [sourceCount, targetCount] = await Promise.all([
      getRowCount(sourcePrisma, table),
      getRowCount(targetPrisma, table),
    ]);
    const match = sourceCount === targetCount;
    if (!match) allMatch = false;
    console.log(`  ${match ? '✅' : '❌'} ${table}: source=${sourceCount}, target=${targetCount}`);
  }

  if (allMatch && onlyInSource.length === 0) {
    console.log('✅ All compared tables have matching row counts.');
  } else {
    process.exit(1);
  }
  await sourcePrisma.$disconnect();
  await targetPrisma.$disconnect();
}

main().catch((e) => { console.error(e); process.exit(1); });

5. Schema comparison (optional but recommended)

Theory in practice: Row counts tell you “same number of rows”; they don’t tell you “same indexes or triggers”. So we run a schema diff: query both DBs for primary keys, foreign keys, unique constraints, indexes, check constraints (compared by table + clause, not by name), enums, sequences, and triggers. Any difference fails the run. We exclude optional objects (e.g. a logging table that exists only on source) so that “only in source” doesn’t wrongly fail the run.

File: backend/scripts/compare-remote-databases-schema.ts (concept)

Query both DBs for: primary keys, foreign keys, unique constraints, indexes, check constraints (by table + clause), enum types, sequences, triggers.
Diff the sets; if something exists only in source or only in target (and isn’t in the ignore list), exit with code 1.
Full script lives in the repo at backend/scripts/compare-remote-databases-schema.ts.

Run after data copy:

SOURCE_DATABASE_URL='...' TARGET_DATABASE_URL='...' npx tsx scripts/compare-remote-databases-schema.ts

6. Migration order and enum migration (lessons in code)

Theory in practice: Migration tools run migrations in a defined order (usually by name/timestamp). If migration B depends on something migration A creates (e.g. a table), A must run first. Migration order: We had migrations that had to run in a specific order (e.g. GDPR tables before a later migration that referenced them). We renamed the folders so the timestamp prefix orders them, e.g.:

20251210_1_add_gdpr_tables
20251210_2_add_error_message
20251210_3_add_payment_tables

Enum that might not exist: (See “Theory: PostgreSQL enums” above.) On a DB that was created only by running migrations, the ChartType enum sometimes didn’t exist when we added new values. So we changed the migration to create the enum if missing, then add the values.

File: backend/prisma/migrations/20260214000000_add_transit_chart_types/migration.sql

-- Create ChartType enum if it does not exist (e.g. when DB was created from migrations only).
-- Then ensure TRANSIT_LAGNA and TRANSIT_MOON exist for transit house predictions.
DO $$
BEGIN
  IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'ChartType') THEN
    CREATE TYPE "ChartType" AS ENUM (
      'D1_LAGNA', 'D1_RASI', 'D2_HORA', 'D3_DREKKANA', 'D7_SAPTAMSA',
      'D9_NAVAMSA', 'D10_DASAMSA', 'D12_DWADASAMSA', 'D16_SHODASAMSA',
      'D20_VIMSAMSA', 'D24_CHATURVIMSAMSA', 'D27_BHAMSA', 'D30_TRIMSAMSA',
      'D40_KHAVEDAMSA', 'D45_AKSHAVEDAMSA', 'D60_SHASHTYAMSA',
      'TRANSIT_LAGNA', 'TRANSIT_MOON'
    );
  ELSE
    ALTER TYPE "ChartType" ADD VALUE IF NOT EXISTS 'TRANSIT_LAGNA';
    ALTER TYPE "ChartType" ADD VALUE IF NOT EXISTS 'TRANSIT_MOON';
  END IF;
END
$$;

If you add new enum values in Prisma, keep in mind: on a “migrate-only” target, the enum might not exist yet—so “create if not exists, then add value” is safer.

7. Triggers and optional objects

Theory in practice: Triggers and their side effects (e.g. a log table) are part of the “schema” in a broad sense. If the source has them and the target doesn’t, the schema diff will show “only in source”. You can either apply the same SQL on the target (so behaviour and schema match) or exclude those objects from the comparison. We apply the trigger SQL so the target behaves like the source.

psql "$TARGET_DATABASE_URL" -f backend/scripts/add-user-deletion-trigger.sql

The schema comparison script can exclude optional tables/sequences (e.g. user_deletion_logs) so they don’t cause a “diff” failure.

Option B: pg_dump / pg_restore (one-shot clone)

Use when you want a single full clone and your local PostgreSQL client version is ≥ the server’s.

Prerequisites: pg_dump, pg_restore (and optionally psql) on PATH. If your default client is older than the server, set:

export PG_DUMP=/path/to/pg_dump   # e.g. /usr/local/opt/postgresql@17/bin/pg_dump
export PG_RESTORE=/path/to/pg_restore

One-shot (schema + data):

cd backend
export SOURCE_DATABASE_URL="postgresql://user:pass@old-host:port/olddb"
export TARGET_DATABASE_URL="postgresql://user:pass@new-host:port/newdb"
./scripts/pg-migrate-remote-to-remote.sh

Data-only (target already has schema from Prisma):

DATA_ONLY=1 ./scripts/pg-migrate-remote-to-remote.sh "$SOURCE_DATABASE_URL" "$TARGET_DATABASE_URL"

Passwords with special characters: URL-encode them in the URL (e.g. ^ → %5E, } → %7D).

After migration

Point the app to the new DB: set DATABASE_URL (e.g. in .env or in your host’s env) to TARGET_DATABASE_URL.
Run npx prisma generate in the backend.
Map backend and frontend URLs in your host (e.g. dcdeploy) to the new environment.

Summary and takeaways

Theory (what to remember):

Remote-to-remote = copy from one live DB server to another (new host, region, or environment). You need the same schema and data on the target.
Schema first, then data: Create structure (tables, constraints, enums, etc.) on the target, then copy rows in FK order so parent rows exist before child rows.
Two strategies: (A) Deploy your migrations on the target and copy data with an app (avoids pg_dump version issues); (B) Use pg_dump/pg_restore when your client version matches or exceeds the server.
Enums: On a migrate-only target, an enum might not exist yet; migrations should create-if-not-exists then add value so they work on both fresh and cloned DBs.
Verification: Compare row counts (and optionally full schema: PKs, FKs, indexes, constraints, enums, sequences, triggers) so you know the target really matches the source.

Practice (what we did):

We migrated remote → remote by deploying Prisma migrations on the target, then copying data with a Node script (enum + JSONB safe), then comparing row counts and optionally schema.
We ordered migrations by folder naming, handled enums with create-if-not-exists in SQL, and verified with scripts. The same approach works for any Prisma/PostgreSQL project; adjust TABLES_IN_ORDER and optional/ignore lists to your schema.

All scripts referenced here live in this repo under backend/scripts/. The technical runbook is in REMOTE_TO_REMOTE_MIGRATION.md.

RAG From Scratch: Build a System That Answers Questions From Your Docs

vapmail16 — Tue, 24 Feb 2026 11:51:48 +0000

My first RAG system answered "I don't know" to questions that were clearly in the documents. The information was right there — paragraph three, page seven — and the AI couldn't find it.
Turns out, my chunking strategy was destroying context. I was splitting documents every 1,000 characters like every tutorial told me to. The split landed in the middle of a sentence about quarterly revenue targets. The first half ended up in one chunk, the second half in another, and the embedding for each half was meaningless.
That was the moment I understood: RAG isn't a retrieval problem or a generation problem. It's an architecture problem. And most tutorials stop at step one.
Here's how to build a RAG system that actually works — from loading documents to generating accurate answers.

What RAG Actually Is
RAG stands for Retrieval-Augmented Generation. Instead of asking an LLM to answer from its training data (which might be outdated or wrong), you give it YOUR documents and let it answer from those.
The pipeline:
Documents → Chunk → Embed → Store in Vector DB →
User asks question → Embed question → Search for similar chunks →
Feed chunks + question to LLM → Generate answer
Simple in theory. The devil is in each arrow.

Step 1: Load and Chunk Your Documents
Loading is straightforward. Chunking is where most RAG systems break.
pythonfrom langchain.document_loaders import DirectoryLoader, TextLoader
from langchain.text_splitter import RecursiveCharacterTextSplitter

Load documents

loader = DirectoryLoader('./docs', glob="*/.md", loader_cls=TextLoader)
documents = loader.load()

Bad chunking (what most tutorials teach)

bad_splitter = RecursiveCharacterTextSplitter(
chunk_size=1000,
chunk_overlap=0 # No overlap = lost context
)

Good chunking

good_splitter = RecursiveCharacterTextSplitter(
chunk_size=500, # Smaller = more precise retrieval
chunk_overlap=100, # Overlap preserves context at boundaries
separators=["\n## ", "\n### ", "\n\n", "\n", " "] # Respect document structure
)

chunks = good_splitter.split_documents(documents)
Why 500 instead of 1,000? Smaller chunks mean more precise retrieval. When a user asks "What's the refund policy?", you want to retrieve the exact paragraph about refunds — not a 1,000-character block that's half refund policy and half shipping information.
Why overlap of 100? Because sentences at chunk boundaries get split. Overlapping by 100 characters means the end of chunk N appears at the start of chunk N+1. Context preserved.
Why respect separators? Splitting on headings and paragraphs keeps semantic units together. Splitting on character count doesn't care if it lands mid-sentence.

Step 2: Embed and Store
Turn each chunk into a vector embedding and store it in a vector database.
pythonfrom langchain.embeddings import OpenAIEmbeddings
from langchain.vectorstores import Chroma # or Pinecone, Weaviate, pgvector

embeddings = OpenAIEmbeddings(model="text-embedding-3-small")

Create vector store

vectorstore = Chroma.from_documents(
documents=chunks,
embedding=embeddings,
persist_directory="./chroma_db"
)
Embedding model choice matters. text-embedding-3-small is fast and cheap. For higher accuracy on technical content, text-embedding-3-large is worth the extra cost. For completely local/private data, consider open-source alternatives like sentence-transformers/all-MiniLM-L6-v2.

Step 3: Retrieval — Where Most Systems Fail
Naive retrieval: find the top-k most similar vectors to the user's question. This works for simple questions but fails badly when:

The question uses different words than the document (semantic gap)
Multiple chunks contain partial answers
The most similar chunk isn't the most relevant chunk

Here are three upgrades that dramatically improve retrieval:
Upgrade A: Hybrid Search (Vector + Keyword)
pythonfrom langchain.retrievers import BM25Retriever, EnsembleRetriever

Vector retriever (semantic similarity)

vector_retriever = vectorstore.as_retriever(search_kwargs={"k": 10})

BM25 retriever (keyword matching)

bm25_retriever = BM25Retriever.from_documents(chunks)
bm25_retriever.k = 10

Combine both

ensemble_retriever = EnsembleRetriever(
retrievers=[vector_retriever, bm25_retriever],
weights=[0.6, 0.4] # Favor semantic, supplement with keywords
)
Why hybrid? Vector search finds semantically similar content. BM25 finds exact keyword matches. Together they catch what each misses alone. User asks "What is the PTO policy?" — vector search finds "vacation days and time off" while BM25 catches the exact term "PTO."
Upgrade B: HyDE (Hypothetical Document Embeddings)
Instead of embedding the question, generate a hypothetical answer first, then search with that.
pythonfrom langchain.chains import HypotheticalDocumentEmbedder

hyde_embeddings = HypotheticalDocumentEmbedder.from_llm(
llm=ChatOpenAI(model="gpt-4o-mini"),
base_embeddings=embeddings,
prompt_key="web_search"
)
The intuition: a question like "What's the refund policy?" is semantically different from the answer paragraph that describes the actual policy. But a hypothetical answer ABOUT refund policies is much closer to the real document. HyDE bridges that gap.
Upgrade C: Re-ranking
Retrieve broadly (top 20), then re-rank to find the best 5.
pythonfrom langchain.retrievers import ContextualCompressionRetriever
from langchain.retrievers.document_compressors import CrossEncoderReranker

reranker = CrossEncoderReranker(
model_name="cross-encoder/ms-marco-MiniLM-L-6-v2",
top_n=5
)

compression_retriever = ContextualCompressionRetriever(
base_compressor=reranker,
base_retriever=vector_retriever # Retrieves 20
)
Why re-rank? Bi-encoder similarity (what vector search uses) is fast but approximate. Cross-encoder re-ranking is slower but far more accurate. Retrieve broadly, then re-rank precisely.

Step 4: Generation — The Prompt Matters
The difference between a hallucinating RAG system and an honest one is usually the system prompt.
pythonfrom langchain.prompts import ChatPromptTemplate
from langchain.chat_models import ChatOpenAI

prompt = ChatPromptTemplate.from_template("""
Answer the user's question using ONLY the provided context.
If the context doesn't contain the answer, say "I don't have
enough information to answer this question."
Never make up facts. Never extrapolate beyond the context.

Context:
{context}

Question: {question}

Answer:
""")

llm = ChatOpenAI(model="gpt-4o-mini", temperature=0)

Build the chain

from langchain.chains import RetrievalQA

qa_chain = RetrievalQA.from_chain_type(
llm=llm,
retriever=compression_retriever,
chain_type_kwargs={"prompt": prompt},
return_source_documents=True
)

Ask a question

result = qa_chain.invoke({"query": "What is the refund policy?"})
print(result["result"])
print("Sources:", [doc.metadata for doc in result["source_documents"]])
Three critical instructions in that prompt: (1) ONLY use the provided context, (2) admit when you don't know, (3) never make up facts. Remove any of these and your system will hallucinate confidently.

Step 5: Evaluate (The Step Everyone Skips)
Most RAG systems ship with zero evaluation. That's like deploying a web app without testing.
Three things to measure:
python# 1. Retrieval quality: did we fetch the right chunks?
def retrieval_precision(query, retrieved_docs, relevant_docs):
relevant_retrieved = set(retrieved_docs) & set(relevant_docs)
return len(relevant_retrieved) / len(retrieved_docs)

2. Answer faithfulness: does the answer match the context?

(Use an LLM to verify — no hallucination beyond context)

3. Answer relevance: does the answer address the question?

(Use an LLM to score 1-5)

Build a test set of 20-50 question/answer pairs from your documents. Run your RAG against them. Track precision, faithfulness, and relevance over time. When you change chunking, retrieval, or prompts — re-run the eval.

RAG Architecture Comparison
Not all RAG is created equal:
ArchitectureHow It WorksBest ForNaive RAGTop-k vector search → generateSimple docs, getting startedHyDEHypothetical answer → search with thatWhen questions ≠ document languageHybridVector + BM25 keyword searchMost production systemsCorrective RAGCheck retrieval quality → retry if badHigh-accuracy requirementsGraph RAGKnowledge graph + vector searchComplex entity relationshipsAgentic RAGAgent decides retrieval strategyMulti-step reasoning
Most production systems should start with Hybrid + Re-ranking. It handles 80% of use cases well.

RAG Debugging Checklist
When your RAG isn't working, check these in order:
□ 1. Are chunks too large? (>1000 chars = probably too big)
□ 2. Is there chunk overlap? (0 overlap = context loss)
□ 3. Are you respecting document structure? (split on headings)
□ 4. Is retrieval returning relevant chunks? (print them!)
□ 5. Is your prompt explicit about "only use context"?
□ 6. Are you using temperature=0 for factual answers?
□ 7. Have you tried hybrid search? (vector + keyword)
□ 8. Do you have an evaluation set? (20+ Q&A pairs minimum)
If your RAG answers are mediocre, it's almost never the model. It's the retrieval. Fix retrieval first, always.
What's the hardest question your RAG system can't answer?

Tags: ai, rag, tutorial, python

Vector Databases Explained: How AI Actually Understands Your Text

vapmail16 — Sun, 22 Feb 2026 18:22:48 +0000

When I first saw that King - Man + Woman ≈ Queen in vector space, something clicked. Not intellectually — I'd read about word embeddings before. But seeing it actually work, watching the maths produce the right answer from pure numbers, was the moment I finally understood why everyone was excited about embeddings.

Vector databases are the backbone of every modern AI application — semantic search, recommendation engines, RAG systems, chatbots that actually know your data. But most tutorials skip the why and jump straight to pip install. That's like learning SQL without understanding what a relational database actually does.

Let me fix that.

What Are Embeddings? (And Why They Matter)

Traditional databases store data as rows and columns. You search with exact matches: WHERE name = 'pizza'. That's great for structured data. It's terrible for meaning.

Vector databases store data as embeddings — arrays of numbers that capture semantic meaning. The sentence "I love pizza" becomes something like:

[0.23, -0.41, 0.87, 0.12, -0.56, ...]  // 1,536 numbers

Here's the magic: sentences with similar meanings have similar numbers, regardless of the words used.

"I love pizza"              → [0.23, -0.41, 0.87, ...]
"Pizza is my favourite food" → [0.25, -0.39, 0.85, ...]  // Very close!
"I love debugging"          → [0.67, 0.12, -0.34, ...]   // Very different.

The embedding model (like OpenAI's text-embedding-3-small) has been trained on billions of text examples. It's learned that "love" and "favourite" carry similar weight, that "pizza" and "food" are related, and that "pizza" and "debugging" occupy completely different corners of meaning-space.

This is what makes semantic search possible. You don't search for keywords. You search for meaning.

How Similarity Search Works

Once your text is converted to vectors, you need to measure how close two vectors are. There are three common approaches, and which one you pick matters.

Cosine Similarity (Most Common)

Measures the angle between two vectors. Ignores magnitude, focuses on direction.

1.0 = identical meaning
0.0 = completely unrelated
-1.0 = opposite meaning

from numpy import dot
from numpy.linalg import norm

def cosine_similarity(a, b):
    return dot(a, b) / (norm(a) * norm(b))

Use when: Your embeddings vary in magnitude (most text use cases). This is the default for a reason.

Euclidean Distance

Measures the straight-line distance between two points in vector space. Smaller = more similar.

Use when: You care about absolute differences, not just direction. Less common for text, more useful for image features or numerical data.

Dot Product

Like cosine similarity but does consider magnitude. Vectors that are both similar in direction and large in magnitude score highest.

Use when: Your embedding model is designed for it (some models normalise vectors, making dot product equivalent to cosine).

My recommendation: Start with cosine similarity. It's robust, well-understood, and works for almost every text use case. Only switch if you have a specific reason.

Indexing: Why Brute Force Doesn't Scale

Here's the problem: if you have 1 million documents, comparing your query vector to every single stored vector takes 1 million similarity calculations. That's O(n). Slow. Unusable at scale.

Vector databases solve this with specialised index structures.

HNSW (Hierarchical Navigable Small World)

The most popular index type. Think of it like a network of neighbours.

Imagine you're looking for a specific house in a city. Brute force: check every house on every street. HNSW: ask a neighbour, they point you closer, you ask that person's neighbour, they point you closer still. A few hops and you're there.

Technically, HNSW builds a multi-layer graph where each node connects to its nearest neighbours. Higher layers are sparse (for big jumps), lower layers are dense (for precision). Search starts at the top and navigates down.

Search time: O(log n) — millions of vectors in milliseconds
Tradeoff: Uses more memory (stores the graph structure)
Best for: Most use cases. Fast, accurate, well-supported

IVF (Inverted File Index)

Divides vector space into clusters (like postal codes). At query time, only searches the nearest clusters instead of everything.

Search time: Faster than brute force, less accurate than HNSW
Tradeoff: Needs tuning (how many clusters? how many to search?)
Best for: Very large datasets where memory is constrained

Product Quantization (PQ)

Compresses vectors by splitting them into sub-vectors and approximating each one. Dramatically reduces memory usage at the cost of some accuracy.

Tradeoff: Lossy compression — some precision loss
Best for: Billions of vectors where memory is the bottleneck

My take: HNSW is the right choice for 95% of applications. It's the default in Pinecone, Weaviate, and Qdrant for good reason. Only reach for IVF or PQ when you're dealing with genuinely massive scale or tight memory constraints.

Choosing a Vector Database

This is where people get stuck. There are too many options and every vendor says they're the best. Here's an honest comparison based on what I've actually used:

Pinecone — Managed, Zero Ops

import pinecone

pinecone.init(api_key="your-key", environment="us-east-1")
index = pinecone.Index("my-index")

# Upsert
index.upsert(vectors=[
    ("doc1", [0.23, -0.41, 0.87, ...], {"text": "Original document"}),
])

# Query
results = index.query(vector=[0.25, -0.39, 0.85, ...], top_k=5)

Pros: Fully managed. No infrastructure to worry about. Scales automatically. Great for teams that don't want to manage databases.
Cons: Vendor lock-in. More expensive at scale. Limited querying beyond similarity search.
Best for: Production apps where you want zero ops overhead.

Weaviate — Open Source, Hybrid Search

Pros: Open source. Supports hybrid search (vector + keyword BM25 in one query). GraphQL API. Modules for auto-vectorisation.
Cons: More complex to set up and manage. Heavier resource usage.
Best for: Applications that need both semantic and keyword search — which, in practice, is most production RAG systems.

Chroma — Lightweight, Great for Prototyping

import chromadb

client = chromadb.Client()
collection = client.create_collection("my-collection")

collection.add(
    documents=["I love pizza", "Pizza is my favourite food"],
    ids=["doc1", "doc2"]
)

results = collection.query(query_texts=["What food do you enjoy?"], n_results=2)

Pros: Dead simple API. Runs locally. Handles embedding generation for you. Perfect for experimentation.
Cons: Not designed for production scale. Limited configuration.
Best for: Prototyping, local development, learning.

pgvector — Postgres Extension

CREATE EXTENSION vector;

CREATE TABLE documents (
  id SERIAL PRIMARY KEY,
  content TEXT,
  embedding vector(1536)
);

-- Similarity search
SELECT content, embedding <=> '[0.23, -0.41, ...]' AS distance
FROM documents
ORDER BY distance
LIMIT 5;

Pros: Use your existing Postgres. No new infrastructure. SQL familiarity. Transactions and joins with your regular data.
Cons: Slower than purpose-built vector DBs at scale. Limited index types.
Best for: Teams already on Postgres who don't want another database. Works well under 1M vectors.

Qdrant — Rust-Based, Fast, Self-Hosted

Pros: Very fast (written in Rust). Rich filtering alongside vector search. Open source with a managed option.
Cons: Smaller community than Pinecone/Weaviate. Fewer integrations.
Best for: Performance-sensitive applications. Teams comfortable self-hosting.

The Vector DB Decision Checklist

When someone asks me "which vector database should I use?", I run through this:

1. Are you prototyping or building for production?
Prototyping → Chroma. Get something working in 10 minutes.

2. Do you already have Postgres and < 1M vectors?
Yes → pgvector. Don't add infrastructure you don't need.

3. Do you need hybrid search (vector + keyword)?
Yes → Weaviate. Its hybrid search is genuinely best-in-class.

4. Do you want zero infrastructure management?
Yes → Pinecone. Pay more, worry less.

5. Is raw query speed your top priority?
Yes → Qdrant. Rust-level performance.

6. Budget-constrained and self-hosting?
Weaviate or Qdrant, both open source.

There's no universal "best." There's "best for your use case, your team, and your stage."

Putting It Together: The Embedding Pipeline

Here's the complete flow for any AI application using vector search:

1. Document comes in (PDF, webpage, chat message, etc.)
2. Split into chunks (paragraphs, sections — overlap by 200 chars)
3. Generate embeddings via API (OpenAI, Cohere, or local model)
4. Store embeddings + metadata in your vector database
5. User asks a question
6. Embed the question using the SAME model
7. Query vector DB for top-k similar chunks
8. Pass retrieved chunks + question to an LLM
9. LLM generates an answer grounded in YOUR data

Steps 5-9 is what people call RAG (Retrieval-Augmented Generation). But the quality of step 8's answer depends entirely on steps 2-4. Bad chunking, wrong embedding model, or a poorly-configured index means the LLM gets irrelevant context and hallucinates confidently.

The foundation matters. Get the vector layer right, and everything built on top works. Get it wrong, and no amount of prompt engineering will save you.

What's your use case? I'd genuinely love to hear — drop a comment and I'll tell you which vector DB fits. Building semantic search? RAG chatbot? Recommendation engine? The answer changes based on what you're actually building.

SaaS Database Design: 6 Decisions You'll Regret Getting Wrong

vapmail16 — Fri, 20 Feb 2026 14:11:48 +0000

I refactored my database schema 4 times before getting it right. Each time cost me a week.

The first version used auto-increment IDs everywhere. The second didn't account for soft deletes. The third had audit logging bolted on as an afterthought. The fourth tried to retrofit consent versioning into a schema that assumed a single boolean for "agreed to terms."

Database schema is the one foundation you can't easily refactor once you have real data. Migrations exist, but migrating a million-row table with zero downtime while restructuring relationships is a different beast than prisma migrate dev on localhost.

Here are the six decisions I'd get right from Day 1.

1. UUIDs for Public IDs

Auto-increment IDs leak information. When a competitor signs up and sees /users/47, they know you have 47 users. When they see /api/payments/12, they know your payment volume. Worse, sequential IDs are trivially enumerable — an attacker can crawl /api/users/1 through /api/users/10000 in seconds.

UUIDs fix this entirely:

model User {
  id        String   @id @default(uuid())
  email     String   @unique
  name      String?
  role      Role     @default(USER)
  isActive  Boolean  @default(true)
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
}

Every model uses @default(uuid()). No sequential integer, no information leakage, no enumeration attacks. The performance cost on PostgreSQL is negligible with proper indexing — and the security benefit is immediate.

2. RBAC as a Role Hierarchy, Not Flat Strings

A role: string field with values like "editor", "manager", "admin" scattered across your codebase becomes unmaintainable fast. Every permission check turns into if (role === 'admin' || role === 'manager' || role === 'editor').

Use an enum with a clear hierarchy instead:

enum Role {
  USER
  ADMIN
  SUPER_ADMIN
}

model User {
  id   String @id @default(uuid())
  role Role   @default(USER)
  // ...
}

The middleware that enforces this is one function:

export const requireRole = (...roles: string[]) => {
  return (req, res, next) => {
    if (!req.user) return next(new UnauthorizedError('Authentication required'));
    if (!roles.includes(req.user.role)) return next(new ForbiddenError('Insufficient permissions'));
    next();
  };
};

// ADMIN and SUPER_ADMIN can list users; only SUPER_ADMIN can delete
router.get('/admin/users', authenticate, requireRole('ADMIN', 'SUPER_ADMIN'), handler);
router.delete('/admin/users/:id', authenticate, requireRole('SUPER_ADMIN'), handler);

Because the enum is defined in Prisma, the database enforces it. No rogue "superadmin" or "Admin" strings sneaking in. Every role change gets audit-logged (more on that in a moment).

3. Soft Deletes With `deletedAt`

Hard-deleting a user row cascades through sessions, payments, notifications, audit logs — and if anything goes wrong mid-cascade, you're left with orphaned records and no way to recover.

Worse: GDPR requires you to prove deletion happened. If you DELETE FROM users WHERE id = ? and it's gone, you have no proof.

The deletedAt pattern gives you both safety and compliance:

model User {
  id           String    @id @default(uuid())
  email        String    @unique
  isActive     Boolean   @default(true)
  deletedAt    DateTime?
  anonymizedAt DateTime?
  // ...
}

Soft delete sets deletedAt and isActive = false. For GDPR "right to erasure," you anonymize the PII (replace email with a hash, wipe name and phone) and set anonymizedAt. The record still exists for accounting and audit purposes, but the personal data is gone.

Hard delete is available when legally required — but it's a separate, deliberate action (DeletionType.HARD) with email confirmation, not the default.

4. Audit Logs as a Separate, Append-Only Table

Audit logging isn't a "nice to have." It's the first thing auditors ask for, the first thing you'll need when debugging a production incident, and the first thing enterprise procurement teams check.

It needs to be a dedicated table, not a column on other tables:

model AuditLog {
  id         String    @id @default(uuid())
  userId     String?
  action     String    // "USER_LOGIN", "PAYMENT_CREATED", "ROLE_CHANGED"
  resource   String?   // "users", "payments", "mfa_methods"
  resourceId String?   // ID of affected resource
  details    Json?     // Before/after state, additional context
  ipAddress  String?
  userAgent  String?
  createdAt  DateTime  @default(now())
  archived   Boolean   @default(false)
  archivedAt DateTime?

  user User? @relation(fields: [userId], references: [id], onDelete: SetNull)

  @@index([userId])
  @@index([action])
  @@index([createdAt])
}

A few critical design decisions here:

onDelete: SetNull — if a user is deleted, their audit logs remain. You never cascade-delete audit records.
details: Json? — stores before/after snapshots for state changes. When someone asks "what did this user's profile look like before the admin edited it?", you have the answer.
archived + archivedAt — financial data needs 7-year retention in many jurisdictions. Archiving lets you move old logs to cold storage without deleting them.
Append-only by convention — the service only creates log entries, never updates or deletes them. Immutable audit trails are a compliance requirement.

Every meaningful action in the system — login, password change, MFA setup, role change, data export, OAuth linking — writes to this table with the user's IP and user agent.

5. Session Tracking With Device Context

Most tutorials store sessions as a token string and an expiry. That's not enough. When a user looks at "Active Sessions" in their security settings, they expect to see where they're logged in. And when you need to revoke sessions on password change, you need to know which ones exist.

model Session {
  id        String   @id @default(uuid())
  userId    String
  token     String   @unique
  expiresAt DateTime
  userAgent String?
  ipAddress String?
  createdAt DateTime @default(now())

  user User @relation(fields: [userId], references: [id], onDelete: Cascade)

  @@index([userId])
  @@index([token])
  @@index([expiresAt])
}

userAgent and ipAddress are captured at login and stored alongside the session. This enables:

"Active sessions" UI — show device/browser and location per session.
Password change → revoke all — delete all sessions for this user, forcing re-authentication everywhere.
Suspicious activity detection — flag when the same user logs in from two countries within an hour.

The @@index([expiresAt]) matters for cleanup: a scheduled job can efficiently delete expired sessions without a full table scan.

6. Consent Versioning

This is the one most SaaS templates get wrong — or skip entirely.

A single agreedToTerms: Boolean on the User model is legally meaningless. GDPR requires you to prove what the user consented to, which version of the policy, when, and from where.

That means two models: one for consent records, one for consent versions.

model ConsentRecord {
  id          String      @id @default(uuid())
  userId      String
  consentType ConsentType
  granted     Boolean     @default(false)
  grantedAt   DateTime?
  revokedAt   DateTime?
  ipAddress   String?
  userAgent   String?
  version     String?     // e.g. "2.1.0"
  versionId   String?     // FK to ConsentVersion
  expiresAt   DateTime?
  metadata    Json?
  createdAt   DateTime    @default(now())
  updatedAt   DateTime    @updatedAt

  user           User            @relation(fields: [userId], references: [id], onDelete: Cascade)
  consentVersion ConsentVersion? @relation(fields: [versionId], references: [id])

  @@unique([userId, consentType])
  @@index([consentType])
}

model ConsentVersion {
  id                String      @id @default(uuid())
  consentType       ConsentType
  version           String      // Semantic version "1.0.0"
  title             String
  content           String      @db.Text
  summary           String?     @db.Text // Summary of changes
  effectiveDate     DateTime
  expiryPeriod      Int?        // Days until consent expires
  requiresReConsent Boolean     @default(true)
  changes           String?     @db.Text
  isActive          Boolean     @default(false)
  createdAt         DateTime    @default(now())

  consentRecords ConsentRecord[]

  @@unique([consentType, version])
}

enum ConsentType {
  MARKETING_EMAILS
  ANALYTICS
  THIRD_PARTY_SHARING
  COOKIES
  TERMS_OF_SERVICE
  PRIVACY_POLICY
}

Six consent types, each independently versioned. When you update your privacy policy (version 1.0 → 2.0), requiresReConsent: true triggers a prompt for every user who consented to v1. The ConsentRecord stores the exact version they agreed to, with IP and timestamp.

When a regulator asks "did this user consent to third-party data sharing?", you don't check a boolean. You pull the record: they consented to version 1.2.0 of the THIRD_PARTY_SHARING policy on January 15th at 14:32 UTC from IP 203.0.113.42. That's the difference between "we think so" and "here's the evidence."

The Controversial Opinion

Prisma migrations > raw SQL. I know, I know.

Raw SQL gives you fine-grained control. But when you're iterating fast, the DX difference is massive. prisma migrate dev generates the SQL, tracks the migration history, and handles rollbacks. You can always eject to raw SQL for complex migrations. But for the 90% case — adding a field, creating a table, adjusting an index — the productivity gain is real.

That said: always review the generated SQL before running it in production. prisma migrate deploy, not prisma migrate dev, in prod. And keep backups before every migration. Trust the tool, but verify the output.

What database decisions have burned you?

Authentication That Actually Passes Security Audits

vapmail16 — Thu, 19 Feb 2026 12:59:26 +0000

I ran a security self-audit on my own auth code. Found 3 issues in the first hour.

Token expiry was too generous. Sessions weren't tracked by device. And password reset tokens weren't being invalidated after use. None of these would show up in a typical tutorial. All three would show up in an actual security review.

Most auth tutorials teach you the basics: hash the password, sign a JWT, protect a route. That gets you through the demo. Auditors check for depth — token rotation, MFA implementation, session tracking, role hierarchies, rate limiting. The gap between "works in development" and "passes a security questionnaire" is bigger than most developers expect.

Here's what a production auth system actually looks like, with real code.

1. JWT + Refresh Tokens: The Cookie-Based Approach

The first thing any auditor checks: where are you storing tokens?

If the answer is localStorage, you've already failed. Any XSS vulnerability — and every non-trivial app will eventually have one — gives an attacker full access to the token. Game over.

HTTP-only cookies fix this. The browser sends them automatically; JavaScript can't read them. Here's the login flow:

// Access token: short-lived (15 min), signed with a dedicated secret
const accessToken = jwt.sign(
  { userId },
  config.jwt.secret,
  { expiresIn: '15m' }
);

// Refresh token: long-lived (30 days), separate secret, stored in DB
const refreshToken = jwt.sign(
  { userId },
  config.jwt.refreshSecret,
  { expiresIn: '30d' }
);

Both tokens go into HTTP-only cookies — never in the response body:

// Set access token — httpOnly prevents XSS from reading it
res.cookie('accessToken', accessToken, {
  httpOnly: true,
  secure: true,        // HTTPS only
  sameSite: 'strict',  // CSRF protection
  maxAge: 15 * 60 * 1000,
});

// Set refresh token — same protections, longer lifespan
res.cookie('refreshToken', refreshToken, {
  httpOnly: true,
  secure: true,
  sameSite: 'strict',
  maxAge: 30 * 24 * 60 * 60 * 1000,
});

// Body contains user info only — no tokens exposed
res.json({ success: true, data: loginResult.user });

The refresh token is stored in the database tied to the session. When it's used, the old session is deleted and a new one is created. If a token is reused (i.e., someone stole it and the real user already rotated it), we know the session was compromised.

// Refresh: verify token, check DB session, issue new access token
const session = await prisma.session.findUnique({
  where: { token: refreshToken },
  include: { user: true },
});

if (!session || session.expiresAt < new Date()) {
  throw new UnauthorizedError('Invalid or expired refresh token');
}

if (!session.user.isActive) {
  throw new UnauthorizedError('Account is disabled');
}

const newAccessToken = jwt.sign(
  { userId: session.userId },
  config.jwt.secret,
  { expiresIn: '15m' }
);

The middleware that protects routes tries the cookie first, falls back to the Authorization header for API clients:

export const authenticate = async (req, res, next) => {
  // Prefer cookie (browser), fall back to header (API/mobile)
  let token = req.cookies?.accessToken;

  if (!token) {
    const authHeader = req.headers.authorization;
    if (authHeader?.startsWith('Bearer ')) {
      token = authHeader.substring(7);
    }
  }

  if (!token) throw new UnauthorizedError('No token provided');

  const decoded = jwt.verify(token, config.jwt.secret);

  const user = await prisma.user.findUnique({
    where: { id: decoded.userId },
    select: { id: true, email: true, role: true, isActive: true },
  });

  if (!user || !user.isActive) {
    throw new UnauthorizedError('User not found or disabled');
  }

  req.user = user;
  next();
};

2. MFA: Why SMS Fails Audits

SMS-based MFA is convenient and insecure. SIM swapping, SS7 vulnerabilities, social engineering at carrier stores — there's a reason NIST downgraded SMS verification years ago. Auditors know this.

TOTP (Google Authenticator, Authy) is the standard. Here's the setup flow — generate a secret, create a QR code, and store backup codes:

export const setupTotp = async (userId: string) => {
  const user = await prisma.user.findUnique({ where: { id: userId } });

  const secret = speakeasy.generateSecret({
    name: `MyApp (${user.email})`,
    length: 32,
  });

  const qrCodeUrl = await QRCode.toDataURL(secret.otpauth_url, {
    width: 512,
    errorCorrectionLevel: 'M',
  });

  // Generate 10 backup codes (stored hashed in production)
  const backupCodes = await generateBackupCodes(userId);

  // Store secret — not enabled until user verifies first code
  await prisma.mfaMethod.upsert({
    where: { userId_method: { userId, method: 'TOTP' } },
    create: { userId, method: 'TOTP', secret: secret.base32, isEnabled: false },
    update: { secret: secret.base32, isEnabled: false },
  });

  return { secret: secret.base32, qrCodeUrl, backupCodes };
};

The Prisma schema behind this:

model MfaMethod {
  id        String        @id @default(uuid())
  userId    String
  method    MfaMethodType // TOTP or EMAIL
  secret    String?
  isEnabled Boolean       @default(false)
  isPrimary Boolean       @default(false)
  createdAt DateTime      @default(now())
  updatedAt DateTime      @updatedAt

  user User @relation(fields: [userId], references: [id], onDelete: Cascade)
  @@unique([userId, method])
}

model MfaBackupCode {
  id        String    @id @default(uuid())
  userId    String
  code      String    @unique
  used      Boolean   @default(false)
  usedAt    DateTime?
  createdAt DateTime  @default(now())
}

MFA isn't enabled until the user verifies their first code. This prevents locking someone out if setup was interrupted. And Email OTP exists as a fallback for accessibility — not everyone has a smartphone.

During login, if MFA is enabled, we return a temporary token instead of the real session. The user completes MFA, and only then do we issue access/refresh tokens:

if (enabledMfaMethods.length > 0) {
  return {
    user: { id: user.id, email: user.email },
    requiresMfa: true,
    mfaMethod: primaryMethod.method,
    // No tokens — need MFA verification first
  };
}

3. OAuth Edge Cases Nobody Warns You About

OAuth tutorials show "click Google, get user." Real implementations deal with:

Account linking: User signs up with email/password, later clicks "Connect with Google." You need to match the email and link providers, not create a duplicate.
Email conflicts: User has a Google account with user@gmail.com and tries to link GitHub which has a different email. Which one wins?
Multi-provider: Supporting Google, GitHub, and Microsoft simultaneously means three different token formats and profile shapes.

The link/unlink flow needs its own endpoints and audit logging:

router.post('/oauth/link', authenticate, async (req, res) => {
  const { provider, token } = req.body;

  const profile = await verifyOAuthToken(provider, token);
  const user = await linkOAuthToUser(req.user.id, provider, profile);

  await prisma.auditLog.create({
    data: {
      userId: req.user.id,
      action: 'OAUTH_LINKED',
      resource: 'users',
      details: { provider },
      ipAddress: getClientIp(req),
    },
  });

  res.json({ success: true, data: user });
});

Every OAuth operation is audit-logged. When something goes wrong (and it will), you need to know exactly what happened and when.

4. Session Management

Sessions are tracked with device info. This isn't just for the "active sessions" UI — it's a security requirement:

model Session {
  id        String   @id @default(uuid())
  userId    String
  token     String   @unique
  expiresAt DateTime
  userAgent String?
  ipAddress String?
  createdAt DateTime @default(now())

  user User @relation(fields: [userId], references: [id], onDelete: Cascade)
}

On login, existing sessions for that user are cleared. On password change, all sessions are revoked. This means: compromised password → change password → attacker is immediately locked out, even if they have a valid refresh token.

5. RBAC: Role Hierarchy With One Middleware

Three roles — USER, ADMIN, SUPER_ADMIN — with inheritance. The middleware is simple because the hierarchy is explicit:

export const requireRole = (...roles: string[]) => {
  return (req, res, next) => {
    if (!req.user) {
      return next(new UnauthorizedError('Authentication required'));
    }
    if (!roles.includes(req.user.role)) {
      return next(new ForbiddenError('Insufficient permissions'));
    }
    next();
  };
};

// Usage
router.get('/admin/users', authenticate, requireRole('ADMIN', 'SUPER_ADMIN'), handler);
router.delete('/admin/users/:id', authenticate, requireRole('SUPER_ADMIN'), handler);

Every role change is audit-logged. An admin promoting someone to SUPER_ADMIN is a high-severity event that should be visible in your audit trail.

Auth Audit Readiness Checklist

Run this against your auth. Score yourself honestly.

#	Check	What Auditors Look For
1	Token storage	HTTP-only cookies, not localStorage
2	Access token expiry	15 minutes or less
3	Refresh token rotation	Old token invalidated on use
4	MFA support	TOTP (not just SMS)
5	Backup codes	Generated and stored for account recovery
6	Rate limiting	Login, registration, password reset endpoints
7	Session tracking	IP, user agent, device logged per session
8	Password change revocation	All other sessions killed on password change
9	OAuth audit trail	Link/unlink events logged with IP and timestamp
10	Role change logging	Every privilege escalation tracked in audit log

If your auth tutorial doesn't cover token rotation and reuse detection, it's teaching you to build a vulnerability.

Run this checklist against your auth. What did you score?

The #1 Thing Missing From Every SaaS Starter Kit

vapmail16 — Wed, 18 Feb 2026 12:16:52 +0000

I've evaluated dozens of SaaS starter kits. Next.js boilerplates, Express templates, Rails scaffolds — you name it.

They all nail the same things: authentication, a pretty dashboard, Stripe integration, maybe some admin CRUD. And they all skip the same thing.

Compliance.

Not "we added a cookie banner" compliance. I mean actual GDPR data export, right-to-deletion, consent management, audit logging, field-level encryption, breach notification — the stuff that turns a weekend project into something you can actually sell to businesses in the EU (or anywhere with privacy laws, which is increasingly everywhere).

I spent months building these features into a SaaS template. Here's what I learned and why most starter kits get this wrong.

The "We'll Add It Later" Trap

Every developer who's shipped a SaaS product knows this conversation:

"We'll handle GDPR later."
"Let's just get to market first."
"We only have 50 users, nobody's going to request a data export."

Then you land your first enterprise client. Their procurement team sends over a 40-page security questionnaire. Question 7: "Describe your data subject access request (DSAR) process." Question 23: "How do you handle data retention and deletion?" Question 31: "Provide evidence of audit logging for PII access."

And suddenly "later" is now, and you're bolting compliance onto an architecture that was never designed for it.

The fix isn't complicated — but it needs to be baked in from the start, not layered on after.

What Real GDPR Data Export Looks Like in Code

Most starter kits that claim "GDPR support" give you a checkbox component and call it done. Here's what an actual data export implementation looks like.

First, the service that collects everything you store about a user:

export const generateDataExport = async (requestId: string) => {
  const request = await prisma.dataExportRequest.findUnique({
    where: { id: requestId },
    include: { user: true },
  });

  if (!request) {
    throw new NotFoundError('Export request not found');
  }

  await prisma.dataExportRequest.update({
    where: { id: requestId },
    data: { status: DataExportStatus.PROCESSING },
  });

  const userId = request.userId;

  // Collect ALL user data — not just the profile
  const [user, sessions, auditLogs, notifications, payments, subscriptions, consentRecords] =
    await Promise.all([
      prisma.user.findUnique({ where: { id: userId } }),
      prisma.session.findMany({ where: { userId } }),
      prisma.auditLog.findMany({ where: { userId } }),
      prisma.notification.findMany({ where: { userId } }),
      prisma.payment.findMany({ where: { userId }, include: { refunds: true } }),
      prisma.subscription.findMany({ where: { userId } }),
      prisma.consentRecord.findMany({ where: { userId } }),
    ]);

  const exportData = {
    user: {
      id: user?.id,
      email: user?.email,
      name: user?.name,
      role: user?.role,
      createdAt: user?.createdAt,
    },
    sessions: sessions.map((s) => ({
      id: s.id,
      createdAt: s.createdAt,
      expiresAt: s.expiresAt,
      userAgent: s.userAgent,
      ipAddress: s.ipAddress,
    })),
    auditLogs: auditLogs.map((a) => ({
      action: a.action,
      resource: a.resource,
      createdAt: a.createdAt,
      ipAddress: a.ipAddress,
    })),
    payments: payments.map((p) => ({
      amount: p.amount,
      currency: p.currency,
      status: p.status,
      createdAt: p.createdAt,
      refunds: p.refunds,
    })),
    consents: consentRecords,
    exportMetadata: {
      requestId,
      generatedAt: new Date().toISOString(),
      format: 'JSON',
    },
  };

  // Update request, set 7-day download window
  const expiresAt = new Date();
  expiresAt.setDate(expiresAt.getDate() + 7);

  await prisma.dataExportRequest.update({
    where: { id: requestId },
    data: {
      status: DataExportStatus.COMPLETED,
      completedAt: new Date(),
      downloadUrl: `/api/gdpr/exports/${requestId}/download`,
      fileSize: Buffer.byteLength(JSON.stringify(exportData), 'utf8'),
      expiresAt,
    },
  });

  return exportData;
};

A few things to notice:

You must export everything — sessions, audit logs, payment history, consent records, IP addresses. Not just the profile. GDPR Article 15 is specific about this.
Download links expire — you don't want a permanent URL to someone's entire data sitting in their email forever.
The request itself is tracked — status goes from PENDING → PROCESSING → COMPLETED (or FAILED), with timestamps and audit logging at each step.

The route that serves the download is equally important. It validates ownership, checks expiry, and sets proper headers:

router.get(
  '/exports/:id/download',
  asyncHandler(async (req, res) => {
    const jsonString = await gdprService.getExportDataForDownload(
      req.params.id,
      req.user!.id // ensures only the data owner can download
    );

    const filename = `data-export-${req.params.id}.json`;
    res.setHeader('Content-Type', 'application/json');
    res.setHeader('Content-Disposition', `attachment; filename="${filename}"`);
    res.send(jsonString);
  })
);

Data Deletion Is Harder Than You Think

"Just delete the user row." If only.

Real right-to-erasure means you need two modes:

Soft delete (anonymize): Replace PII with anonymized values, keep the record for accounting/legal obligations. You can't delete a payment record — your accountant needs it — but you can strip the name and email.
Hard delete: Actually remove everything. Cascade through sessions, notifications, audit logs, payments, consents.

And crucially, deletion requires email confirmation before it executes. You don't want a compromised session to wipe a user's entire account:

export const requestDataDeletion = async (
  userId: string,
  deletionType: DeletionType = DeletionType.SOFT,
  reason?: string
) => {
  const confirmationToken = crypto.randomBytes(32).toString('hex');

  const deletionRequest = await prisma.dataDeletionRequest.create({
    data: {
      userId,
      deletionType,
      reason,
      confirmationToken,
      status: DataDeletionStatus.PENDING,
    },
  });

  // Send confirmation email — deletion only proceeds after click
  await sendDataDeletionRequestConfirmationEmail({
    to: user.email,
    name: user.name,
    confirmationLink: `${frontendUrl}/gdpr?confirmDeletion=${confirmationToken}`,
  });

  await createAuditLog({
    userId,
    action: 'DATA_DELETION_REQUESTED',
    resource: 'data_deletion_requests',
    resourceId: deletionRequest.id,
    details: { deletionType, reason },
  });

  return deletionRequest;
};

Every step is audit-logged. The confirmation token is cryptographically random. The request sits in PENDING until the user clicks the email link. This is the kind of thing that takes a couple of days to get right — and that's before you write the tests.

The Iceberg Below the Surface

Data export and deletion are the visible parts. Underneath, you need:

Consent management with versioned consent records (users agreed to v2.1 of your privacy policy on this date, from this IP).
Audit logging on every PII access — not just writes, but reads. Who looked at what, when, and why.
Field-level encryption for ultra-sensitive fields (phone numbers, addresses, tax IDs) as a second layer beyond database encryption.
Data retention policies that automatically purge expired data.
Breach notification workflows — GDPR requires reporting within 72 hours.
CSRF protection on every state-changing endpoint, because a compliance feature that can be exploited via cross-site request forgery isn't really compliant.
PII masking in logs so your error tracking doesn't accidentally become a data breach.

Each of these is individually straightforward. Together, they represent weeks of careful engineering. And they all need tests — you can't just eyeball compliance.

Why Starter Kits Skip This

I get it. Compliance features don't make for exciting demo videos. Nobody screenshots a consent versioning system for their landing page. "Look at this beautiful audit log table" doesn't go viral on Twitter.

But here's the business reality: compliance is a feature that sells. Enterprise buyers specifically ask for it. EU-based customers require it by law. And the fines for getting it wrong are not theoretical — they're in the headlines every month.

The starter kits that skip compliance are optimizing for the first sale. The ones that include it are optimizing for every sale after that.

What I'd Tell My Past Self

If you're building a SaaS template (or choosing one), here's the order I'd prioritize compliance:

Audit logging — add it first, to everything. You'll thank yourself when debugging and when filling out security questionnaires.
Data export — GDPR Article 15 requests are the most common. Have a one-click solution ready.
Consent management — versioned, timestamped, with IP. Not a single boolean "agreed to terms."
Data deletion — with soft/hard modes and email confirmation. Test the cascading deletes thoroughly.
Encryption — field-level for PII, on top of whatever your database provides.
Everything else — retention, breach notification, CSRF, monitoring. Important, but the first five cover 80% of what procurement teams ask about.

Build it in from day one. It's 10x easier than retrofitting it after you have real users and real data.

What compliance features do you wish your starter had? I'm genuinely curious — drop a comment below.