Vector Database

Vector Database Skill

Iron Law

Pin dimensions at model selection. The embedding dimension must match the model exactly and is set once — changing models requires a full re-embedding migration. Store embedding_model varchar(100) alongside every vector column so the model is always auditable.

Embedding Model Selection

Choose model before writing any schema. Dimension determines index shape.

Rule: Once a dimension is written into schema, it cannot change without /migrate-embedding-model.

pgvector

When to Use

• Data lives in PostgreSQL (tickets, vendors, invoices, tenant records)
• You need vector search + relational JOINs in a single query
• Filtering on structured columns (city, category, rating) alongside semantic search

Vector Column Declaration

-- ALWAYS enable extension in migration up
CREATE EXTENSION IF NOT EXISTS vector;

-- Column: dimension must match model exactly
ALTER TABLE vendors ADD COLUMN embedding vector(1536);
ALTER TABLE vendors ADD COLUMN embedding_model varchar(100) DEFAULT 'text-embedding-3-small';

Distance Operators

Critical: The operator used in queries MUST match the operator used in CREATE INDEX. Mismatch = full table scan silently.

Index Selection

-- HNSW (production default)
CREATE INDEX ON vendors USING hnsw (embedding vector_cosine_ops)
  WITH (m = 16, ef_construction = 64);

-- IVFFlat (dev/budget)
CREATE INDEX ON vendors USING ivfflat (embedding vector_cosine_ops)
  WITH (lists = 100);
-- After bulk insert: ANALYZE vendors;

Example Query Pattern (vendor matching)

SELECT v.id, v.name, v.city, v.rating,
       1 - (v.embedding <=> $1::vector) AS similarity
FROM vendors v
WHERE v.city = $2
  AND v.category = $3
  AND v.rating >= $4
  AND v.embedding IS NOT NULL
ORDER BY v.embedding <=> $1::vector
LIMIT 10;

For full migration template → see references/pgvector-migration-template.md

Weaviate

When to Use

• Data lives outside PostgreSQL (scraped text, PDFs, docs, external reviews)
• You need native hybrid search (BM25 + vector) with fusion control
• Multi-tenancy (one collection shared across tenants)
• Phase 2+ RAG against GCS / external document stores

Collection Schema Essentials

import weaviate
import weaviate.classes as wvc

client = weaviate.connect_to_weaviate_cloud(
    cluster_url=os.environ["WEAVIATE_URL"],
    auth_credentials=wvc.init.Auth.api_key(os.environ["WEAVIATE_API_KEY"]),
)

# ALWAYS set multi_tenancy at creation — cannot change later
client.collections.create(
    name="VendorReview",
    vectorizer_config=wvc.config.Configure.Vectorizer.text2vec_openai(
        model="text-embedding-3-small",
    ),
    vector_index_config=wvc.config.Configure.VectorIndex.hnsw(
        distance_metric=wvc.config.VectorDistances.COSINE,
    ),
    multi_tenancy_config=wvc.config.Configure.multi_tenancy(enabled=True),
    properties=[
        wvc.config.Property(name="text", data_type=wvc.config.DataType.TEXT),
        wvc.config.Property(name="source", data_type=wvc.config.DataType.TEXT),
        wvc.config.Property(name="vendor_id", data_type=wvc.config.DataType.TEXT),
    ],
)

Query Patterns

collection = client.collections.get("VendorReview")

# nearText (semantic)
result = collection.query.near_text(query="plumber emergency repair", limit=5)

# Hybrid search (BM25 + vector)
# alpha=1.0 = pure vector | alpha=0.0 = pure BM25
result = collection.query.hybrid(
    query="24-hour plumber",
    alpha=0.75,
    fusion_type=wvc.query.HybridFusion.RELATIVE_SCORE,
    limit=10,
)

Fusion algorithms:

• RANKED_FUSION — rank-based merge; stable, good default
• RELATIVE_SCORE_FUSION — score-normalized merge; better when score magnitude matters

For named vectors and multi-tenant patterns → see references/weaviate-collection-patterns.md

Two-Tier Architecture Decision

When building a system with both structured and unstructured data:

Rule: Keep data in the tier where it originates. Don't sync Postgres rows to Weaviate at MVP — async sync lag introduces consistency bugs. Move to Weaviate only when matching against external documents.

Slash Commands

• /design-vector-schema — pgvector migration SQL from model + row count + metric
• /design-weaviate-collection — Weaviate Python collection from vectorizer + schema
• /scaffold-rag-pipeline — Full RAG pipeline: chunk → embed → store → retrieve → rerank
• /migrate-embedding-model — Re-embedding migration plan + reversible SQL
• /tune-vector-index — HNSW vs IVFFlat recommendation + tuned params

Reference Files

Documentation Sources

Before generating code, consult these sources for current APIs:

Post-Code Review

After writing vector database code, dispatch:

• pgvector-schema-reviewer — operator/index alignment, dimension match, null guards, reversible migrations
• weaviate-schema-reviewer — v4 client API, multi-tenancy declaration, distance metric
• rag-pipeline-reviewer — model pinning, batch embedding, silent retrieval failure risks
• postgresql-database-reviewer — for the broader migration (indexes, constraints, performance)