Building AI Features into Existing Software: Architecture, APIs, and Data Considerations

Building an AI-first application from a clean slate is relatively straightforward: engineers select modern frameworks, configure an isolated vector database, and design schemas specifically for tokenized embeddings. However, in commercial technology, the vast majority of engineering value is created by retrofitting established, revenue-generating software applications with AI capabilities.

Integrating Large Language Models into an existing 'brownfield' codebase introduces complex architectural challenges: preserving multi-tenant data boundaries, avoiding database lockups during heavy vector queries, handling unpredictable third-party API latency, and preventing proprietary customer data from leaking into public training datasets. Navigating these constraints requires treating AI as an integrated subsystem within your broader custom software architecture.

1. The Brownfield AI Dilemma: Retrofitting vs. Rewriting

A frequent architectural mistake is attempting to rewrite an existing software platform simply to accommodate AI features. In reality, modern AI capabilities can be introduced incrementally as microservices or background worker pipelines connected to your primary database via standard APIs.

Rather than spinning up an entirely separate managed vector database with its own authentication, network latency, and backup overhead, most applications can leverage their existing database engine. PostgreSQL natively supports vector storage and high-speed approximate nearest-neighbor indexing through the battle-tested `pgvector` extension.

2. Retrieval-Augmented Generation (RAG) in Relational Databases

Retrieval-Augmented Generation (RAG) allows an LLM to answer user queries using an organization's private documents without costly fine-tuning. By storing high-dimensional text embeddings directly alongside your existing relational records, queries can combine traditional SQL filters (such as tenant ID, date range, or user permissions) with semantic similarity vector searches.

-- Enable pgvector extension
CREATE EXTENSION IF NOT EXISTS vector;

-- Add embedding column (e.g., 768 dimensions for Gemini embeddings)
ALTER TABLE organization_documents 
ADD COLUMN IF NOT EXISTS embedding vector(768);

-- Create HNSW index for fast approximate nearest-neighbor search
CREATE INDEX IF NOT EXISTS idx_documents_embedding 
ON organization_documents 
USING hnsw (embedding vector_cosine_ops);

-- Hybrid search query combining tenant security with semantic vector matching
SELECT id, title, content, 1 - (embedding <=> $1) AS similarity_score
FROM organization_documents
WHERE tenant_id = $2 AND is_archived = false
ORDER BY embedding <=> $1
LIMIT 5;

3. Clarifying Vector Operators: Cosine Distance vs. Similarity

A critical technical detail often confused in AI documentation is the mathematical distinction between vector distance and vector similarity. In `pgvector`, the `<=>` operator computes **cosine distance**, not cosine similarity.

Cosine distance measures how divergent two vectors are on a scale from 0 (identical direction) to 2 (directly opposing). To convert this distance metric into an intuitive similarity score where higher values represent closer relevance, the query subtracts the distance from 1: `1 - (embedding <=> $1)`. Ordering by `embedding <=> $1 ASC` ensures the database retrieves the closest semantic matches first using the HNSW index.

4. Document Chunking & Asynchronous Ingestion Queues

LLMs cannot consume entire 100-page enterprise manuals in a single vector comparison. Ingestion pipelines must divide documents into focused, semantically coherent segments before generating embeddings through practical AI integration workflows.

• Fixed-Window with Overlap: Slicing text into 400–600 token chunks with a 50-token sliding overlap. This prevents contextual thoughts from being severed across chunk boundaries.
• Document Structure-Aware Chunking: Parsing markdown, HTML headers, or PDF sections to preserve header hierarchy within chunk metadata.
• Asynchronous Ingestion Queues: Never compute embeddings synchronously inside web request lifecycles. Offload PDF parsing, chunking, and API calls to background job workers running on BullMQ or AWS SQS.

5. Multi-Tenant Data Privacy & Row-Level Security (RLS)

In multi-tenant SaaS platforms, the highest-severity risk is data cross-contamination: allowing Tenant A's private internal records to appear in an AI-generated summary presented to Tenant B. Standard vector search indexes, if queried without explicit predicates, are completely blind to organizational boundaries.

Enforce Row-Level Security (RLS) at the PostgreSQL layer, or mandate that every vector query incorporates a parameterized `tenant_id` filter prior to nearest-neighbor calculation. Furthermore, verify enterprise API agreements with model vendors to guarantee that inference payloads are zero-retention and strictly excluded from model training cycles.

6. Semantic Caching with Redis & Token Budgeting

External LLM APIs introduce variable latency and token-based billing that can spike unexpectedly. In high-volume systems, implementing defensive caching and rate-limiting patterns is essential for operational stability, mirroring best practices in resilient API integration.

• Semantic Redis Caching: Store previous user prompt embeddings in Redis. When an incoming query matches an existing embedding with >0.96 cosine similarity, return the cached completion instantly, reducing latency to under 30ms and eliminating API token costs.
• Per-Tenant Token Quotas: Implement token rate-limiters at your application API gateway using leaky bucket algorithms to protect against compromised credentials or accidental runaway loops.
• Model Tier Fallbacks: Configure automatic fallbacks from high-capability frontier models to faster, cost-effective models for routine summarization or classification tasks.