You’re viewing Paradaux’s public workspaceSign in to vote and comment Get taskl.ie for your team

PAR-228

Loading issues

PAR-228 · Semantic (vector) search for issues — pgvector embeddings… · Paradaux · taskl.ie

PAR-228

Semantic (vector) search for issues — pgvector embeddings + hybrid retrieval for the triage bot

Follow-up to PAR-227 (conversational ANSWER). The bot's retrieval is purely lexical today — PostgresTaskSystem.searchTasks (V12 search_tsv tsvector + ts_rank, OR-prefix, ILIKE fallback). It misses conceptually-related issues that don't share words ("can't pay rent" vs "rent payment fails"). Add semantic search via embeddings so search_tasks (and thus FIND + the ANSWER action) retrieves by meaning, and fuse it with the existing full-text for the best of both.

Where it plugs in

Retrieval entrypoint: PostgresTaskSystem.searchTasks(query, limit) (src/main/java/io/paradaux/tesks/tasks/store/PostgresTaskSystem.java). The agentic loop's search_tasks tool (TriageService.runReadTool) and applyFind/applyAnswer all funnel through it — improving it lifts every path with no caller changes.
Embeddings provider: reuse the OpenAI Java SDK already on the classpath (com.openai:openai-java:4.37.0) — client.embeddings(). Mirror the LlmClient/OpenAiLlmClient port pattern: add an EmbeddingClient port + OpenAI adapter, configured under tesks.llm (api-key/base-url reused) with tesks.llm.embedding-model (default text-embedding-3-small, 1536 dims). Meter tokens through the existing LlmUsageSink.

Infra prerequisite (do first / confirm)

The deployed DB is stock postgres:16 (gitops/apps/postgres/base/resources.yaml) — no pgvector. Switch that image to pgvector/pgvector:pg16 (or install the extension) and CREATE EXTENSION IF NOT EXISTS vector in the tesks database. This is a shared instance (also hosts paradaux-api's apps db), so validate the image swap doesn't disrupt other databases. Gitops change + Argo sync.

Implementation

Migration V28__issue_embeddings.sql (latest is V27): create extension if not exists vector; add issue.embedding vector(1536) (nullable — NULL = not yet embedded), plus an HNSW index using hnsw (embedding vector_cosine_ops). Keep search_tsv — this is additive.
EmbeddingClient port + OpenAiEmbeddingClient adapter (io.paradaux.tesks.llm): float[] embed(String text) / batch variant; disabled-safe when no api-key (like OpenAiLlmClient).
Write path: embed title + "\n" + description on create and on title/description update (createTask/updateTask in PostgresTaskSystem). Do it best-effort/async so a failed/slow embedding never blocks the task write (NULL embedding just means it's not yet semantically searchable; full-text still covers it).
Backfill: a one-off runner (CommandLineRunner behind a flag, or an admin endpoint) that embeds all existing issues with NULL embedding, batched + rate-limit-aware, resumable.
Hybrid query: add a semantic ranking to searchTasks — embed the query, ANN by cosine distance, and fuse with the existing ts_rank lexical results (Reciprocal Rank Fusion is simplest/robust). Tenant-scope the ANN (where tenant_id = ? and archived_at is null and embedding is not null). Fall back to pure full-text when embeddings are disabled or the query embed fails. Keep the Task projection unchanged.
(Optional) expose a semantic_search tool to the agent in addition to keyword search_tasks, or just make search_tasks hybrid (preferred — fewer tools, the model already uses it well).

Considerations

Cost/latency: text-embedding-3-small is cheap; one embed per query + one per issue write. Cache the query embedding within an agentic loop if the same query repeats.
Multi-tenant: every ANN query MUST carry the tenant_id predicate (IIDOR parity with the rest of PostgresTaskSystem).
Dimension lock-in: 1536 is fixed by the model; note it in the migration. Switching embedding models later means a re-embed/backfill.
No mirror impact: this is the tesks-owned issue table in the tesks DB, not the tesks-ui Postgres mirror.

Acceptance

A query semantically related but lexically distinct from an issue's text retrieves it (regression test with a seeded issue + paraphrased query, using a stubbed EmbeddingClient so the test is deterministic — no live API).
Lexical results still rank well (hybrid doesn't regress exact-term matches); pure full-text path still works with embeddings disabled.
New issues become semantically searchable after create; backfill covers historical issues.
Tenant isolation holds on the ANN path. Full backend suite green; embedding calls metered.
pgvector enabled in dev (and prod when it exists) via gitops.

Activity

tesks created the issue Jun 17
tesks set status to In Progress Jun 18
tesksJun 18
Implementation landed in the working tree (tesks + gitops), compiles clean (compileJava + compileTestJava on JDK 21). Two premises in this ticket were stale and corrected:
1. DB is CloudNativePG, not the old apps/postgres:16 Deployment. The data/postgres CNPG cluster pg (operator chart 0.28.2 = CNPG 1.27, PostgreSQL 16) hosts tesks-dev/tesks-prod. So pgvector is a CNPG concern.
2. Latest migration is V28, so the embeddings migration is V29, not V28.
pgvector enablement (PG16 ⇒ no PG18 image-volume path):
- gitops/data/postgres/image/Dockerfile: bundled operand image (FROM ghcr.io/cloudnative-pg/postgresql:16 + postgresql-16-pgvector).
- cluster.yaml: imageName → harbor.paradaux.io/paradaux-public/postgresql-pgvector:16.
- databases.yaml: Database.spec.extensions: [{name: vector}] on tesks-dev/tesks-prod — operator runs CREATE EXTENSION as superuser (the tesks role can't; the V29 migration deliberately doesn't).
App layer (route-independent):
- V29__issue_embeddings.sql: embedding vector(1536) + HNSW (vector_cosine_ops).
- EmbeddingClient port + OpenAiEmbeddingClient (reuses tesks.llm api-key/base-url; tesks.llm.embedding-model=text-embedding-3-small, dims 1536; disabled-safe; metered via LlmUsageSink).
- PostgresTaskSystem.searchTasks: hybrid RRF (k=60) fusing tenant-scoped ANN with the existing ts_rank lexical arm; falls back to pure full-text when embeddings disabled or embed fails. Title/description edits null the embedding for re-embed.
- EmbeddingMaintainer (@WorkerComponent @Scheduled): fills NULL embeddings batched + tenant-attributed — unifies new-issue embedding AND historical backfill, resumable by construction.
- Tests: PgTestSupport moved from zonky embedded-PG to Testcontainers pgvector/pgvector:pg16 (CREATE EXTENSION before Flyway); SemanticSearchTest proves lexically-distinct retrieval, lexical fallback, no exact-match regression, tenant isolation on the ANN arm.
Remaining (needs cluster/Docker, not code): build+push the pgvector operand image to Harbor, manual-sync data/postgres (HA rolling restart of shared pg), run ./gradlew test where Docker is available, then commit to develop.

Sign in to comment

Join the conversation, react to comments and vote on this issue.