Adding a vector database to Content Shifted

Why a vector database

Content Shifted already treats a site’s knowledge as a store on the canvas: App Profile, Persona, Audience, Brand Styles, crawl notes, uploads, and more, linked as prompt_augmentation. Today retrieve is still closer to concatenating linked text (with a character cap) than ranking what the step actually needs.

That was fine to ship the product shape. It fails as the store grows and workflows ask different questions of the same site. Tip copy wants objections and offers; an app-review script wants product claims and routes; ads want voice and proof. Dumping everything (then truncating) is not retrieve.

A vector index is how I plan to scale relevance: vectors for meanings, metadata for the pool, with citations back to real project files. This post is the implementation sketch, including the services and APIs I intend to use.

Stack choice

Pinecone / Qdrant stay optional later if scale demands it. The retrieve service interface should not leak pgvector so a remote store can swap behind the same Ruby API.

Schema (planned)

One row per chunk. Metadata columns are filterable; the embedding column is the vector.

# db/migrate/..._create_knowledge_chunks.rb (sketch)
enable_extension "vector" unless extension_enabled?("vector")

create_table :knowledge_chunks do |t|
  t.references :account, null: false, foreign_key: true
  t.references :project, null: false, foreign_key: true
  t.references :knowledge_database, null: false, foreign_key: true
  t.references :knowledge_database_item, null: false, foreign_key: true
  t.references :project_file, null: false, foreign_key: true

  t.integer :chunk_index, null: false
  t.text :content, null: false
  t.string :content_hash, null: false
  t.string :embedding_model, null: false, default: "text-embedding-3-small"
  t.string :kind, null: false, default: "text"
  t.string :origin                          # scraped | uploaded
  t.string :item_name                      # "App Profile"
  t.string :source_url
  t.jsonb :labels, null: false, default: []

  t.vector :embedding, limit: 1536         # pgvector
  t.timestamps
end

add_index :knowledge_chunks,
          %i[knowledge_database_item_id chunk_index],
          unique: true
# IVFFlat / HNSW index on embedding added after data exists

Dimension 1536 matches text-embedding-3-small defaults. If I switch models, I reindex rather than mix dimensions in one column.

End-to-end pipeline

Knowledge file created / refreshed / uploaded
        ↓
  Knowledge::IndexItemJob (Sidekiq)
        ↓
  Knowledge::Chunker  →  Knowledge::Embedder  →  upsert knowledge_chunks
        ↓
  automation runs with knowledge edge
        ↓
  Knowledge::Retrieve::ForAutomation
        filter metadata → embed query → top-k by cosine distance
        → prompt passages + citation structs
        ↓
  (optional) agent tool retrieve_knowledge(query) → same Retrieve API

Chunking service

Embeddings work on passages, not whole multi-page notes. Chunking is the first quality gate: too large and retrieve is vague; too small and you lose meaning and burn embedding calls.

Defaults for v1: chunk size 512 tokens, overlap 64 tokens (~12.5%). Why: curated knowledge notes fit a heading + a couple of paragraphs in 512; embedding models are happy in that band; several chunks still fit a tight prompt budget; 64 tokens bridges cut sentences without near-duplicate pollution. Values live in config so evals can try 384/48 or 768/96 later.

Prefer structure-aware splits (markdown headings / paragraphs) before a sliding window on oversized sections. Sketch of the service:

# app/services/knowledge/chunker.rb (sketch)
module Knowledge
  class Chunker
    Chunk = Struct.new(:index, :content, :content_hash, keyword_init: true)

    def initialize(token_size: 512, overlap: 64, tokenizer: Tiktoken.encoding_for_model("text-embedding-3-small"))
      @token_size = token_size
      @overlap = overlap
      @tokenizer = tokenizer
    end

    def call(text)
      sections = split_on_headings_and_paragraphs(text.to_s)
      chunks = []
      sections.each { |section| chunks.concat(window(section)) }
      chunks.each_with_index.map do |content, index|
        Chunk.new(
          index: index,
          content: content,
          content_hash: Digest::SHA256.hexdigest(content)
        )
      end
    end

    private

    def window(section)
      tokens = @tokenizer.encode(section)
      return [section] if tokens.length <= @token_size

      step = @token_size - @overlap
      slices = []
      i = 0
      while i < tokens.length
        slice = tokens[i, @token_size]
        break if slice.blank?
        slices << @tokenizer.decode(slice)
        break if i + @token_size >= tokens.length
        i += step
      end
      slices
    end
  end
end

Tokenizer should match the embedding model family so “512 tokens” means the same thing at chunk time and at the API. If a lightweight gem is awkward in Sidekiq, a character approx (e.g. ~4 chars/token) is an acceptable v1 fallback, but the committed product defaults stay 512 / 64 in token space.

Embedding service and OpenAI API

Extend the existing OpenAI client the same way chat and image already work (ruby-openai → Ai::Providers::Openai::Client).

# OpenAI HTTP (what ruby-openai wraps)
POST https://api.openai.com/v1/embeddings
Authorization: Bearer $OPENAI_API_KEY
Content-Type: application/json

{
  "model": "text-embedding-3-small",
  "input": [
    "Audience: skeptical SMB buyers care about price transparency…",
    "Brand Styles: primary blue #1B4… body font…"
  ]
}

# Response (abbreviated)
{
  "data": [
    { "embedding": [0.01, -0.02, …], "index": 0 },
    { "embedding": [0.03, 0.01, …], "index": 1 }
  ],
  "model": "text-embedding-3-small",
  "usage": { "prompt_tokens": 128, "total_tokens": 128 }
}

# app/services/ai/providers/openai/client.rb (add)
def embeddings(parameters:)
  ensure_configured!
  client.embeddings(parameters: parameters)
rescue Faraday::Error => e
  raise Errors.from_faraday(e, step: "OpenAI embeddings")
rescue ::OpenAI::Error => e
  raise Errors::ApiError, "OpenAI embeddings failed: #{e.message}"
end

# app/services/ai/providers/openai/embeddings.rb (sketch)
module Ai
  module Providers
    module Openai
      class Embeddings
        MODEL = "text-embedding-3-small"
        DIMENSIONS = 1536

        def initialize(client: Client.build)
          @client = client
        end

        def embed(texts)
          Array(texts).each_slice(64).flat_map do |batch|
            response = @client.embeddings(
              parameters: { model: MODEL, input: batch }
            )
            response.fetch("data").sort_by { |row| row["index"] }.map { |row| row.fetch("embedding") }
          end
        end

        def embed_one(text)
          embed([text]).first
        end
      end
    end
  end
end

# app/services/knowledge/embedder.rb (sketch)
module Knowledge
  class Embedder
    def initialize(provider: Ai::Providers::Openai::Embeddings.new)
      @provider = provider
    end

    def call(chunks)
      vectors = @provider.embed(chunks.map(&:content))
      chunks.zip(vectors)
    end
  end
end

• Batch inputs (API allows arrays) to cut HTTP overhead on refresh.
• Skip re-embed when content_hash is unchanged for that chunk index.
• Store embedding_model on each row for safe upgrades.
• Never embed across tenants in one request that mixes account ids in metadata later.

Index job

Hook after knowledge item refresh / upload: enqueue one job per item (or per database with fan-out). Keep HTTP scrape fast; indexing is async.

# app/jobs/knowledge/index_item_job.rb (sketch)
class Knowledge::IndexItemJob
  include Sidekiq::Job

  def perform(knowledge_database_item_id)
    item = KnowledgeDatabaseItem.find(knowledge_database_item_id)
    return unless item.kind == "text"

    text = item.project_file.read_text # existing file helpers
    chunks = Knowledge::Chunker.new.call(text)
    pairs = Knowledge::Embedder.new.call(chunks)

    Knowledge::Chunk.transaction do
      Knowledge::Chunk.where(knowledge_database_item_id: item.id).delete_all
      pairs.each do |chunk, embedding|
        Knowledge::Chunk.create!(
          account_id: item.knowledge_database.account_id,
          project_id: item.knowledge_database.project_id,
          knowledge_database_id: item.knowledge_database_id,
          knowledge_database_item_id: item.id,
          project_file_id: item.project_file_id,
          chunk_index: chunk.index,
          content: chunk.content,
          content_hash: chunk.content_hash,
          embedding_model: Ai::Providers::Openai::Embeddings::MODEL,
          kind: item.kind,
          origin: item.origin,
          item_name: item.name,
          source_url: item.try(:source_url),
          embedding: embedding
        )
      end
    end
  end
end

Replace-delete by item keeps refresh simple for v1. A later optimization only rewrites chunks whose hashes changed.

Semantic search service

At automation time, build a query from the step goal, then nearest-neighbor inside the filtered pool.

# app/services/knowledge/retrieve.rb (sketch)
module Knowledge
  class Retrieve
    Result = Struct.new(:content, :item_name, :project_file_id, :chunk_index, :score, :source_url, keyword_init: true)

    def initialize(embedder: Ai::Providers::Openai::Embeddings.new, top_k: 6, max_chars: 1_500)
      @embedder = embedder
      @top_k = top_k
      @max_chars = max_chars
    end

    def call(query:, account_id:, project_id:, knowledge_database_ids:, kind: "text")
      vector = @embedder.embed_one(query)
      scope = Knowledge::Chunk
        .where(account_id: account_id, project_id: project_id, kind: kind)
        .where(knowledge_database_id: knowledge_database_ids)

      rows = scope
        .order(Arel.sql("embedding <=> #{connection.quote(vector)}")) # cosine distance op
        .limit(@top_k)

      pack(rows)
    end

    def pack(rows)
      budget = @max_chars
      rows.filter_map do |row|
        next if budget <= 0
        excerpt = row.content.truncate(budget)
        budget -= excerpt.length
        Result.new(
          content: excerpt,
          item_name: row.item_name,
          project_file_id: row.project_file_id,
          chunk_index: row.chunk_index,
          score: row.try(:neighbor_distance),
          source_url: row.source_url
        )
      end
    end
  end
end

Query string for an automation (sketch):

query = [
  automation.prompt,
  subject_text.presence,          # upstream draft / page text when present
  "Prefer brand voice and audience objections"
].compact.join("\n")

hits = Knowledge::Retrieve.new.call(
  query: query,
  account_id: project.account_id,
  project_id: project.id,
  knowledge_database_ids: linked_knowledge_database_ids
)

top_k: 6 with a ~1,500 character pack budget mirrors today’s concat cap while choosing better passages. Exact filename pins (“always App Profile”) stay metadata or explicit pins, not something pure similarity must invent.

Metadata filtering

Vectors rank meaning. Metadata decides who is allowed in the race.

# Example: only scraped text in this knowledge DB
Knowledge::Chunk
  .where(account_id:, project_id:, knowledge_database_id:, kind: "text", origin: "scraped")
  .order(Arel.sql("embedding <=> #{quoted_query_vector}"))
  .limit(6)

The canvas edge remains the product ACL: no knowledge edge, no retrieve. Filters never widen past the linked store ids resolved from TransformationAutomationChain.

Reference links (citations)

Each hit already has project_file_id, item_name, and chunk_index. Format for the model and for humans:

def format_for_prompt(hits)
  hits.map { |h| "[#{h.item_name}##{h.chunk_index}]\n#{h.content}" }.join("\n\n")
end

def citation_payload(hits)
  hits.map do |h|
    {
      project_file_id: h.project_file_id,
      name: h.item_name,
      chunk_index: h.chunk_index,
      source_url: h.source_url,
      path: Rails.application.routes.url_helpers
              .account_project_project_file_path(account_id, project_id, h.project_file_id)
    }
  end
end

# Prompt append (replacing blind concat)
context = format_for_prompt(hits)
prompt = "#{prompt}\n\nAdditional context from linked knowledge (cited):\n#{context}"
# Persist citation_payload on the activity / TMC for the run UI

Run UI: “Used Audience#2, Brand Styles#0” as links. Evals: did the tip’s claim appear in a cited chunk?

Where it plugs into the canvas runtime

Replace the body of KnowledgeBaseContext.text_for / append_to_prompt, not the canvas contract:

# app/services/automation_chain/transformation_inputs/knowledge_base_context.rb
def text_for(automation)
  db_ids = linked_knowledge_database_ids(automation)
  return if db_ids.blank?

  hits = Knowledge::Retrieve.new.call(
    query: retrieve_query_for(automation),
    account_id: automation.project.account_id,
    project_id: automation.project_id,
    knowledge_database_ids: db_ids
  )
  format_for_prompt(hits)
end

Transform::Execute → Transform::Generate
  → KnowledgeBaseContext.append_to_prompt
       → Knowledge::Retrieve
       → model call (OpenAI chat / image / … as today)

Feature flag: fall back to legacy concat while comparing quality. Logo / tour / template slots stay explicit edges, not RAG.

How agents use the same index

# Tool schema (agentic_transform / later MCP)
{
  "name": "retrieve_knowledge",
  "description": "Semantically search linked knowledge for this automation",
  "parameters": {
    "type": "object",
    "properties": {
      "query": { "type": "string" }
    },
    "required": ["query"]
  }
}

# Tool handler → Knowledge::Retrieve with the same tenancy + knowledge_database_ids
# from the automation’s prompt_augmentation edges

Automatic top-k for strict transforms; on-demand retrieve tool for Ai::Agent::Runner. Same embed model, same chunk table, no second librarian.

Phased plan

1. Enable vector extension; knowledge_chunks table; OpenAI embeddings on Ai::Providers::Openai::Client.
2. Ship Knowledge::Chunker (512/64), Embedder, IndexItemJob; hook after text knowledge refresh/upload.
3. Ship Knowledge::Retrieve; flag-gate KnowledgeBaseContext to use it.
4. Citations on activity / run UI.
5. retrieve_knowledge tool for agentic transform.
6. Eval set per preset (tip, app review, ads); tune top_k / size / overlap.

Success: same canvas knowledge node, better context per step, visible references, no cross-tenant leakage. The vector database is infrastructure behind the knowledge edge, not a new customer-facing graph type.