Local-First AI: Hybrid Relational & Vector Databases on SQLite

Modern AI architecture often mandates complex, expensive, and heavy infrastructure. Standard engineering guides will tell you that to run a vector search space, you need to sign up for a dedicated vector database provider (like Pinecone or Milvus), set up synchronization pipelines, and manage network overhead.

We can reject this artificial complexity. It is possible to build a hybrid relational/vector database system entirely on a single, local SQLite database, projecting high-dimensional vectors to a 3D visual explorer on-the-fly using a server-side Principal Component Analysis (PCA) solver.

Here is how the architecture works, how the databases relate, and how we map relational lead data into a semantic search space.

1. The Relational-Vector Schema

In SQLite, we store traditional relational entities (leads, blog articles, and active cron jobs) in standard structured tables. To add semantic capabilities, we introduce a single vector_embeddings table. Instead of using a separate database, we link them using a polymorphic foreign key relationship.

Here is the SQL schema:

-- Relational Entity Tables
CREATE TABLE IF NOT EXISTS leads (
  id TEXT PRIMARY KEY,
  business_name TEXT NOT NULL,
  neighborhood TEXT NOT NULL,
  address TEXT NOT NULL,
  source TEXT NOT NULL,
  type TEXT NOT NULL,
  growth_signal TEXT,
  details TEXT,
  date_detected TEXT,
  status TEXT DEFAULT 'Identified',
  notes TEXT,
  reference_url TEXT
);

CREATE TABLE IF NOT EXISTS articles (
  slug TEXT PRIMARY KEY,
  title TEXT NOT NULL,
  description TEXT NOT NULL,
  content TEXT NOT NULL,
  published_at TEXT NOT NULL,
  status TEXT DEFAULT 'published',
  read_time TEXT,
  image_url TEXT,
  tags TEXT
);

-- Unified Vector Table
CREATE TABLE IF NOT EXISTS vector_embeddings (
  id TEXT PRIMARY KEY,
  item_id TEXT NOT NULL,
  type TEXT NOT NULL, -- 'lead', 'article', or 'cron_job'
  embedding TEXT NOT NULL  -- JSON-serialized float array (1536 dimensions)
);

By storing the serialized array inside a text embedding column, we avoid external infrastructure. The item_id and type columns act as a composite key mapping back to the source records.

2. Compiling Relational Data into Vector Spaces

To generate a vector embedding, we must convert the multi-field relational data into a clean, contextual paragraph of text. For a BIA (Business Improvement Area) lead, we compile the business details, notes, and growth signals:

function compileLeadText(lead: Lead): string {
  return `
    Business Name: ${lead.businessName}
    Neighborhood: ${lead.neighborhood}
    Address: ${lead.address}
    Source: ${lead.source}
    Category Type: ${lead.type}
    Growth Signal: ${lead.growthSignal}
    Details: ${lead.details}
    Notes: ${lead.notes}
  `.trim();
}

This compiled text is sent to an embeddings model (such as OpenAI's text-embedding-3-small or a local Transformer). The model returns a 1536-dimensional array of floats representing the semantic coordinates of the lead. We then save this vector in the vector_embeddings table.

Because the vector directly maps to the relational ID, a search query can instantly join the vector search results back to the original database row to retrieve full details, business licenses, and notes.

3. Real-Time 3D Projection: Server-Side PCA

A 1536-dimensional coordinate system cannot be visualized by human eyes. To build a 3D interactive constellation, we must project these 1536 dimensions down to 3 coordinates (x, y, z).

Instead of spinning up a heavyweight Python microservice running PyTorch or scikit-learn, we can implement a server-side Power Iteration Principal Component Analysis (PCA) solver directly in JavaScript.

When a client requests the vector map, our Next.js API route:

1. Pulls all vector embeddings from SQLite.
2. Constructs an N x 1536 data matrix.
3. Computes the covariance matrix (or uses the Gram Matrix X * X^T for N << 1536 to achieve extreme speed).
4. Runs Power Iteration to find the top three eigenvectors.
5. Projects each 1536-D vector onto these eigenvectors, outputting (x, y, z) coordinates.

This whole mathematical projection runs in under 1 millisecond for our database of leads, enabling instant page loads and zero-latency client rendering.

4. Dual Local/Public Environments

To maintain digital sovereignty, we separate our environments:

• Local-First (Development & CRM): Runs a local file-backed SQLite database containing actual BIA leads (e.g. 70+ local businesses). This data is completely private, local-only, and never exposed to the public internet.
• Public Demo (Cloud): Syncs with a Turso database using mock leads. This allows us to share the interactive 3D visualizer with prospects online without compromising private local business intelligence.

By combining SQLite's relational reliability with simple JSON-stored embeddings and lightweight PCA, we prove that sovereign local-first tech can easily outperform bloated enterprise cloud SaaS.