Ground It
A RAG System with Cloud SQL + pgvector
Answers grounded in your real documents, not just one pasted resume.
You build
Answers from your own documents.
Core concept
RAG, embeddings, vector search.
In Phase 2 you pasted your resume into every prompt. That doesn't scale past a page or two. In this phase you'll build RAG — Retrieval-Augmented Generation — so your site can answer from a whole library of your material. This is the single most important pattern in applied AI, and the most valuable thing in this entire track to have built.
The mental model, in one paragraph: split your documents into chunks. Turn each chunk into an embedding (a list of numbers capturing its meaning) and store it in Postgres. When a question comes in, embed the question too, find the chunks whose embeddings are most similar (retrieval), and hand just those chunks to Gemini along with the question (generation). “Find the relevant bits, then answer grounded in them.”
Why it matters in industry
A base model doesn't know your company's documents, and it will confidently make things up. RAG connects a model to a private knowledge source so answers are grounded and cite-able. It's the most widely deployed enterprise AI pattern, and the most requested skill in AI-engineer job postings.
Example — An insurance company builds an internal assistant that answers policy questions from thousands of internal PDFs — with citations, so agents can verify. That's the same retrieve-then-generate pipeline you'll build over your own documents here.
A few terms, in plain English
- Embedding
- Meaning turned into numbers, so similarity can be measured mathematically. Generated by an embedding model (you'll use Vertex AI's).
- Vector
- That list of numbers. Vertex's text-embedding models output a fixed size (e.g. 768); your database column must match it exactly.
- pgvector
- The Postgres extension adding a vector column type and similarity operators (<=> for cosine distance).
- Chunking
- How you split documents. Do it badly and retrieval quality collapses, so it's worth experimenting with sizes and overlap.
- Cloud SQL
- GCP's managed Postgres. It supports both pgvector and a google_ml_integration extension that can call Vertex AI embeddings directly from SQL.
Step 1 · Provision
Stand up Cloud SQL for Postgres
Enable the API and define a small Postgres instance in Terraform. For a portfolio, the smallest tier is plenty. Unlike Cloud Run, a database bills continuously — so understanding its cost and how to pause it matters here.
“Enable the Cloud SQL Admin API, then in my /terraform folder add a small Postgres 16 Cloud SQL instance (smallest tier, deletion protection off) and a database called portfolio. Put it on a branch and open a PR. Explain what this will cost and how to stop it when I'm not using it.”
gcloud services enable sqladmin.googleapis.com
cd terraform && terraform init && terraform applyresource "google_sql_database_instance" "pg" {
name = "portfolio-pg"
database_version = "POSTGRES_16"
region = "YOUR_REGION"
settings {
tier = "db-f1-micro" # smallest; fine for a portfolio
ip_configuration { ipv4_enabled = true }
}
deletion_protection = false # so you can tear it down easily
}
resource "google_sql_database" "app" {
name = "portfolio"
instance = google_sql_database_instance.pg.name
}This is the piece most likely to cost money — watch it
Unlike Cloud Run, a database runs continuously and bills continuously — it doesn't scale to zero. On the free credit it's fine, but this is the resource to shut down when you're done experimenting. Your Phase 0 budget alert is your safety net.
Step 2 · Enable the Extensions
Turn Postgres into a vector store
Connect to the database securely with the Cloud SQL Auth Proxy (no exposing the database to the public internet), enable the two extensions, then create your table.
“Help me connect to my Cloud SQL Postgres instance securely with the Cloud SQL Auth Proxy, enable the vector and google_ml_integration extensions, and create a doc_chunks table with an embedding column and an index. Explain what each extension does.”
./cloud-sql-proxy PROJECT_ID:YOUR_REGION:portfolio-pg &
psql "host=127.0.0.1 dbname=portfolio user=postgres"CREATE EXTENSION IF NOT EXISTS vector; -- pgvector
CREATE EXTENSION IF NOT EXISTS google_ml_integration; -- Vertex AI from SQL
CREATE TABLE doc_chunks (
id BIGSERIAL PRIMARY KEY,
source TEXT, -- which document this came from
content TEXT, -- the chunk text
embedding VECTOR(768) -- must match your embedding model's size
);
-- an index makes similarity search fast at scale
CREATE INDEX ON doc_chunks
USING hnsw (embedding vector_cosine_ops);Step 3 · Ingest
Load and embed your documents
Write a one-time ingestion script: gather source material (resume, project write-ups, a bio, blog posts), chunk it into passages of a few hundred tokens with a little overlap, embed each chunk with Vertex AI, and insert the rows.
“Write a script that loads my documents (I'll point you at them), splits them into overlapping chunks, generates a Vertex AI embedding for each, and inserts them into doc_chunks. Explain how you chose the chunk size and why overlap matters.”
-- with google_ml_integration you can embed straight from SQL
INSERT INTO doc_chunks (source, content, embedding)
VALUES (
'resume.md',
'Led migration of the risk model to a RAG pipeline...',
embedding('text-embedding-005',
'Led migration of the risk model to a RAG pipeline...')
);Step 4 · Retrieve + Generate
Upgrade your endpoint to use RAG
Now change the /api/ask endpoint from Phase 2. Instead of pasting your whole resume, you embed the question, pull the top matching chunks from Postgres, and give only those to Gemini — with their sources, so it can cite where each answer came from.
Ingest · once
Documents
resume, writing, projects
Chunk
overlapping passages
Embed
Vertex AI
Postgres + pgvector
stored with source
Answer a question
Question
from the visitor
Embed
same model
Similarity search
top matching chunks
Gemini
chunks + question
Answer + citation
grounded
Store your documents as embeddings once. For each question, retrieve the closest chunks and let the model answer grounded in just those — with a citation back to the source.
“Upgrade my /api/ask endpoint to use RAG: embed the incoming question, retrieve the top matching chunks from doc_chunks by vector similarity, and pass only those (with their sources) to Gemini. Have it cite which source each answer came from. Then branch, PR, and merge to deploy.”
-- the retrieval query: closest chunks to the question
SELECT source, content
FROM doc_chunks
ORDER BY embedding <=> embedding('text-embedding-005', $1)
LIMIT 5;const chunks = await db.query(RETRIEVAL_SQL, [question]);
const context = chunks.map(c => c.content).join('\n---\n');
const answer = await gemini(`
Answer using ONLY the context below. If it's not covered,
say you don't have that information.
CONTEXT:\n${context}\n\nQUESTION: ${question}`);Cite the source — it's the moment RAG clicks
Because each chunk carries its source, you can show the user where each answer came from. When your site correctly answers something the base model couldn't possibly know — pulled from your own writing, with a citation — that's the payoff.
Step 5 · Refine & Connect
Tune retrieval, then ship
A first RAG system is rarely great; making it good is the real learning. In order of impact: tune chunk size & overlap (too big and retrieval is vague, too small and it loses context); tune how many chunks to retrieve (more context isn't always better — irrelevant chunks distract the model); and connect from Cloud Run securely.
“Connect my Cloud Run service to Cloud SQL using the built-in connector and IAM database auth (no password in code). Grant the site's service account the cloudsql.client role in /terraform, add the DB connection settings to the Cloud Run service, then branch, PR, and merge to deploy.”
IAM database auth means there's no password stored anywhere — same keyless philosophy as WIF.
Done when
- Cloud SQL Postgres running, with vector and google_ml_integration enabled.
- A doc_chunks table with an embedding column and an index.
- An ingestion script that chunks and embeds your documents.
- A retrieval-backed /api/ask that answers from your material and cites sources.
- Cloud Run connected to Cloud SQL with no password in code.
1.What is the core RAG loop, in order?
2.Which Phase 3 resource is the one that bills continuously and must be watched?
3.Why store each chunk's source alongside its text and embedding?
Answer every question correctly to complete this phase.