Overview

package wittgenstein_pgvector_qsql

wittgenstein-pgvector-qsql — Q→SQL semantic training and retrieval via LangChain PGVector.

Classes

• DedupEntry — One row from indexed_sql_dedup. Frozen — read-only value object.

• SqlDedupRepository — CRUD for the indexed_sql_dedup table in PostgreSQL.

• EmbedderConfig — Structural protocol for embedder configuration.

• OllamaEmbedder — Sync HTTP client for Ollama /api/embeddings + LangChain contract.

• OpenAIEmbedder — Sync OpenAI embeddings client + LangChain contract.

• MultiModelQSQLStore — Q→SQL pair store with one embedding column per model.

• QSQLSettings

• QSQLVectorStore — Semantic Q→SQL pair store backed by LangChain PGVector ('sql' collection).

• TrainResult — Result of a train_qsql call.

• TrainingService — Trains Q→SQL pairs into the pgvector store, with SQL-level dedup.

Functions

• normalize_sql — Normalize SQL: strip, remove trailing semicolons, lowercase, collapse whitespace.

• sql_hash — SHA-256 (truncated to 16 hex chars) of the normalized SQL.

• build_embedder — Factory that returns the embedder for the active provider.

• ensure_vector_index — Create an HNSW cosine-distance index on langchain_pg_embedding.embedding.

• build_training_service — Convenience factory: creates QSQLVectorStore + SqlDedupRepository and wires them.

wittgenstein_pgvector_qsql.DedupEntry

mkapi_definition_mkapi class DedupEntry()

One row from indexed_sql_dedup. Frozen — read-only value object.

wittgenstein_pgvector_qsql.SqlDedupRepository

mkapi_definition_mkapi class SqlDedupRepository(pg_dsn: str)

CRUD for the indexed_sql_dedup table in PostgreSQL.

Table DDL (created automatically by `ensure_table()`)

CREATE TABLE indexed_sql_dedup (
    sql_hash       TEXT PRIMARY KEY,
    sql_normalized TEXT NOT NULL,
    first_question TEXT NOT NULL,
    chart_config   JSONB,
    indexed_at     TIMESTAMP DEFAULT NOW()
);

Parameters

• pg_dsn : str — psycopg2-compatible DSN, e.g. 'postgresql://user:pass@host:port/dbname'

Methods

• ensure_table — Create the dedup table if it does not exist. Idempotent.

• get_first_question — Return the first question that indexed this SQL, or None if unseen.

• insert — Insert a new dedup entry. Caller must verify hash_value is unseen first.

• list_all — Return all dedup entries ordered by recency (DESC).

• find_chart_by_hash — Return the chart_config stored for a SQL hash, or None.

wittgenstein_pgvector_qsql.SqlDedupRepository.ensure_table

mkapi_definition_mkapi method SqlDedupRepository.ensure_table() → None

Create the dedup table if it does not exist. Idempotent.

wittgenstein_pgvector_qsql.SqlDedupRepository.get_first_question

mkapi_definition_mkapi method SqlDedupRepository.get_first_question(hash_value: str) → str | None

Return the first question that indexed this SQL, or None if unseen.

wittgenstein_pgvector_qsql.SqlDedupRepository.insert

mkapi_definition_mkapi method SqlDedupRepository.insert(hash_value: str, sql_normalized: str, first_question: str, chart_config: dict[str, Any] | None) → None

Insert a new dedup entry. Caller must verify hash_value is unseen first.

wittgenstein_pgvector_qsql.SqlDedupRepository.list_all

mkapi_definition_mkapi method SqlDedupRepository.list_all() → list[DedupEntry]

Return all dedup entries ordered by recency (DESC).

wittgenstein_pgvector_qsql.SqlDedupRepository.find_chart_by_hash

mkapi_definition_mkapi method SqlDedupRepository.find_chart_by_hash(hash_value: str) → dict[str, Any] | None

Return the chart_config stored for a SQL hash, or None.

wittgenstein_pgvector_qsql.normalize_sql

mkapi_definition_mkapi normalize_sql(sql: str) → str

Normalize SQL: strip, remove trailing semicolons, lowercase, collapse whitespace.

wittgenstein_pgvector_qsql.sql_hash

mkapi_definition_mkapi sql_hash(sql: str) → str

SHA-256 (truncated to 16 hex chars) of the normalized SQL.

16 chars ≈ 2^64 combinations — collision probability is negligible for catalogs of thousands of SQLs.

wittgenstein_pgvector_qsql.EmbedderConfig

mkapi_definition_mkapi class EmbedderConfig()

Bases : Protocol

Structural protocol for embedder configuration.

Any object exposing these attributes works with build_embedder — including an app's Settings, the core's config, or a plain dataclass.

wittgenstein_pgvector_qsql.OllamaEmbedder

mkapi_definition_mkapi class OllamaEmbedder(host: str, model: str, timeout_seconds: float = 60.0)

Bases : LangChainEmbeddingAdapter

Sync HTTP client for Ollama /api/embeddings + LangChain contract.

Logic now lives in wittgenstein_embeddings.OllamaEmbeddingClient — this class is a backward-compat name/shape for existing callers.

Methods

• embed

wittgenstein_pgvector_qsql.OllamaEmbedder.embed

mkapi_definition_mkapi method OllamaEmbedder.embed(text: str) → list[float]

wittgenstein_pgvector_qsql.OpenAIEmbedder

mkapi_definition_mkapi class OpenAIEmbedder(api_key: str, model: str = 'text-embedding-3-small')

Bases : LangChainEmbeddingAdapter

Sync OpenAI embeddings client + LangChain contract.

Logic now lives in wittgenstein_embeddings.OpenAIEmbeddingClient — this class is a backward-compat name/shape for existing callers.

Methods

• embed

wittgenstein_pgvector_qsql.OpenAIEmbedder.embed

mkapi_definition_mkapi method OpenAIEmbedder.embed(text: str) → list[float]

wittgenstein_pgvector_qsql.build_embedder

mkapi_definition_mkapi build_embedder(config: Any) → Embeddings

Factory that returns the embedder for the active provider.

Accepts any object with the `EmbedderConfig` attribute set (duck-typed)

an app's Settings, the lib's QSQLSettings, or a plain dataclass all work.

Parameters

• config : Any — any object with embedding_provider and the provider-specific fields listed in EmbedderConfig.

Raises

• ValueError — unknown embedding_provider or a required API key is missing.

wittgenstein_pgvector_qsql.MultiModelQSQLStore

mkapi_definition_mkapi class MultiModelQSQLStore(pg_dsn: str)

Q→SQL pair store with one embedding column per model.

Schema (auto-created, additive only)

qsql_pair_embeddings( id BIGSERIAL PRIMARY KEY, question TEXT NOT NULL, sql TEXT NOT NULL, question_hash TEXT UNIQUE NOT NULL, embedding_ VECTOR(dim_1), embedding_ VECTOR(dim_2), ... )

Methods

• ensure_model_column — Add embedding_<model_slug> (+ its HNSW index) if not present.

• upsert_pair — Insert the pair if new (by question_hash); return its id either way.

• set_embedding

• search — Return the k most similar (question, sql, similarity) for model_slug's column.

• pairs_missing_embedding — Return (id, question) of every pair whose model_slug column is NULL.

wittgenstein_pgvector_qsql.MultiModelQSQLStore.ensure_model_column

mkapi_definition_mkapi method MultiModelQSQLStore.ensure_model_column(model_slug: str, dimension: int) → None

Add embedding_<model_slug> (+ its HNSW index) if not present.

Safe to call every run — IF NOT EXISTS throughout, never drops or resizes an existing column (a dimension mismatch on a pre-existing column raises rather than silently reinterpreting data).

Raises

• RuntimeError

wittgenstein_pgvector_qsql.MultiModelQSQLStore.upsert_pair

mkapi_definition_mkapi method MultiModelQSQLStore.upsert_pair(question: str, sql: str, question_hash: str) → int

Insert the pair if new (by question_hash); return its id either way.

wittgenstein_pgvector_qsql.MultiModelQSQLStore.set_embedding

mkapi_definition_mkapi method MultiModelQSQLStore.set_embedding(model_slug: str, pair_id: int, embedding: list[float]) → None

wittgenstein_pgvector_qsql.MultiModelQSQLStore.search

mkapi_definition_mkapi method MultiModelQSQLStore.search(model_slug: str, query_embedding: list[float], k: int = 3) → list[dict]

Return the k most similar (question, sql, similarity) for model_slug's column.

similarity is 1 − cosine distance (the <=> operator), so higher is closer, matching what LangChain's similarity_search_with_score consumers expect after the same conversion.

wittgenstein_pgvector_qsql.MultiModelQSQLStore.pairs_missing_embedding

mkapi_definition_mkapi method MultiModelQSQLStore.pairs_missing_embedding(model_slug: str) → list[tuple[int, str]]

Return (id, question) of every pair whose model_slug column is NULL.

This is what makes reindexing-on-model-change safe to run on every deploy: pairs already embedded for this model are skipped, new pairs (or a brand-new model column) get backfilled.

wittgenstein_pgvector_qsql.QSQLSettings

mkapi_definition_mkapi class QSQLSettings()

Bases : BaseSettings

Attributes

• connection_string : str — psycopg3 URL for LangChain PGVector.

• pg_dsn : str — psycopg2 DSN for SqlDedupRepository.

wittgenstein_pgvector_qsql.QSQLSettings.connection_string

mkapi_definition_mkapi property QSQLSettings.connection_string: str

psycopg3 URL for LangChain PGVector.

wittgenstein_pgvector_qsql.QSQLSettings.pg_dsn

mkapi_definition_mkapi property QSQLSettings.pg_dsn: str

psycopg2 DSN for SqlDedupRepository.

wittgenstein_pgvector_qsql.QSQLVectorStore

mkapi_definition_mkapi class QSQLVectorStore(connection_string: str, embedder: Embeddings, collection_name: str | None = None, pre_delete_collection: bool = False)

Semantic Q→SQL pair store backed by LangChain PGVector ('sql' collection).

Questions are embedded and stored; SQL is kept in metadata. Retrieval returns the most similar (question, sql) pairs for a given query.

Parameters

• connection_string : str — psycopg3 URL, e.g. 'postgresql+psycopg://user:pass@host:port/dbname'

• embedder : Embeddings — any LangChain Embeddings instance (OllamaEmbedder, OpenAIEmbedder, or any compatible class).

• collection_name : str | None — PGVector collection to read/write. Defaults to COLLECTION_NAME ('sql') — the production collection. langchain_pg_embedding is one physical table shared by every collection (scoped by collection_id), so different models' embeddings must live in different collections: a query embedded with model A run against rows embedded with model B would error at query time (pgvector's <=> requires matching vector widths). Pass a per-model name (e.g. "sql__local__multilingual-e5-base") when comparing embedder choices — see training/reindex_embeddings.py.

• pre_delete_collection : bool — wipe collection_name (only that collection, not the shared table) before use — makes a full reindex idempotent across re-runs. Never use this against COLLECTION_NAME ('sql'), the production collection.

Methods

• add_pair — Index a question→SQL pair into the vector store.

• get_similar — Return the N most similar (question, sql) pairs for a query.

wittgenstein_pgvector_qsql.QSQLVectorStore.add_pair

mkapi_definition_mkapi method QSQLVectorStore.add_pair(question: str, sql: str) → None

Index a question→SQL pair into the vector store.

The question text is embedded; the SQL is stored in document metadata so it can be returned alongside the question on retrieval.

wittgenstein_pgvector_qsql.QSQLVectorStore.get_similar

mkapi_definition_mkapi method QSQLVectorStore.get_similar(question: str, n: int = 3) → list[dict]

Return the N most similar (question, sql) pairs for a query.

Parameters

• question : str — the user question to match against.

• n : int — maximum number of results to return.

Returns

• list[dict] — list of dicts with keys 'question' and 'sql'.

wittgenstein_pgvector_qsql.ensure_vector_index

mkapi_definition_mkapi ensure_vector_index(connection_or_engine: str | Engine) → None

Create an HNSW cosine-distance index on langchain_pg_embedding.embedding.

langchain_postgres.PGVector auto-creates its schema on first use, but EmbeddingStore.__table_args__ (in the installed langchain_postgres package) only defines a GIN index on the JSONB cmetadata column — nothing on embedding itself. Every get_similar call therefore runs an exact, brute-force cosine-distance scan (DistanceStrategy.COSINE, PGVector's default) across the whole table. This helper closes that gap without touching langchain_postgres's own schema-creation code.

SHARED TABLE, NOT JUST THIS LIBRARY'S DATA: langchain_pg_embedding is one physical table shared by every PGVector collection in the database — rows are scoped by collection_id, not by table. Running this once benefits (and adds index-maintenance overhead to) every other feature that stores vectors via PGVector in the same database, not only this library's "sql" collection. That's an intentional trade-off worth knowing about, not a side effect to hide.

Opt-in by design: this is NOT called automatically by QSQLVectorStore. HNSW build time grows with existing row count, so a caller should invoke this once during deliberate setup/migration for an environment rather than pay a surprise index-build cost on first use in a fresh one.

Requires pgvector >= 0.5.0 (the release that introduced the HNSW index type). Safe to call repeatedly — uses IF NOT EXISTS.

Parameters

• connection_or_engine : str | Engine — either the same psycopg3 SQLAlchemy URL passed to QSQLVectorStore (e.g. 'postgresql+psycopg://user:pass@host/db') or an existing SQLAlchemy Engine pointed at the same database.

wittgenstein_pgvector_qsql.TrainResult

mkapi_definition_mkapi class TrainResult()

Result of a train_qsql call.

existing_question is populated when status='skipped', showing which question originally indexed this SQL.

wittgenstein_pgvector_qsql.TrainingService

mkapi_definition_mkapi class TrainingService(store: QSQLVectorStore, repository: SqlDedupRepository)

Trains Q→SQL pairs into the pgvector store, with SQL-level dedup.

Accepts collaborators via constructor DI so tests can inject mocks. Use build_training_service(connection_string, embedder) for production.

Parameters

• store : QSQLVectorStore — QSQLVectorStore that persists question→sql embeddings.

• repository : SqlDedupRepository — SqlDedupRepository that tracks already-indexed SQL hashes.

Methods

• train_qsql — Index a Q→SQL pair into the vector store, skipping duplicates.

• list_dedup — Return the dedup table as a DataFrame for auditing.

• find_chart_for_sql — Return the chart_config trained for a SQL, or None if not stored.

wittgenstein_pgvector_qsql.TrainingService.train_qsql

mkapi_definition_mkapi method TrainingService.train_qsql(question: str, sql: str, *, chart: dict[str, Any] | None = None, dry_run: bool = False) → TrainResult

Index a Q→SQL pair into the vector store, skipping duplicates.

Flow

1. Hash the normalized SQL → check dedup table.
2. If already present → return status='skipped'.
3. If dry_run=True → return status='dry_run' without writing.
4. Otherwise → embed question + SQL into the store, write dedup entry.

wittgenstein_pgvector_qsql.TrainingService.list_dedup

mkapi_definition_mkapi method TrainingService.list_dedup() → pd.DataFrame

Return the dedup table as a DataFrame for auditing.

wittgenstein_pgvector_qsql.TrainingService.find_chart_for_sql

mkapi_definition_mkapi method TrainingService.find_chart_for_sql(sql: str) → dict[str, Any] | None

Return the chart_config trained for a SQL, or None if not stored.

wittgenstein_pgvector_qsql.build_training_service

mkapi_definition_mkapi build_training_service(connection_string: str, pg_dsn: str, embedder: Any) → TrainingService

Convenience factory: creates QSQLVectorStore + SqlDedupRepository and wires them.

Parameters

• connection_string : str — psycopg3 URL for PGVector (e.g. 'postgresql+psycopg://user:pass@host/db').

• pg_dsn : str — psycopg2 DSN for dedup table (e.g. 'postgresql://user:pass@host:port/db').

• embedder : Any — LangChain Embeddings instance.