Vector Store Stinger

---
name: "vector-store-stinger"
description: "Design vector storage. Use for pgvector schemas, HNSW/IVFFlat, hybrid indexes, migrations, or storage choice. Read guides/ for the decision path."

license: AGPL-3.0-or-later
---

# vector-store-stinger

You are equipping **vector-store-wasp-drone** - the Wasp Nest's authority on vector and embedding storage. This skill covers the domain generally: schema and column design, index selection, distance-operator correctness, hybrid storage-layer search, migrations, and dataset versioning, across whichever store a project actually runs.

**Neon Postgres plus pgvector plus Drizzle is the primary, default option for this repo's stack.** Deep Lake (the columnar, versioned dataset engine a prior product on this codebase's history was built on) stays fully documented as a supported alternative for any project already running it. Qdrant and managed/serverless services (Pinecone-style) are documented as further alternatives with their own tradeoffs. See `guides/00-selection-matrix.md` for how to choose.

**Opinionation is the product**, scoped to whichever store is in play. On this repo's default: "add an HNSW index with `vector_cosine_ops`, not IVFFlat, because the table takes continuous writes" - not "here are two index types, pick one." On a Deep Lake project: the original Deep Lake opinions (single-source the schema, heal additively, never blanket) still apply in full, unchanged.

---

## First move on every invocation

1. **Identify which store is in play.** Read the project's schema/config (for this repo: `drizzle/schema.ts` or equivalent, and confirm `pgvector` is the extension in use; for a Deep Lake project: `deeplake-schema.ts` / `deeplake-api.ts`). If it is a greenfield decision with no store chosen yet, start at `guides/00-selection-matrix.md` instead of assuming.
2. **Classify the invocation** - store selection / schema design / indexing / hybrid search wiring / migration / schema-heal / versioning / storage-backend choice. Route to the matching guide(s) per the table below.
3. **Read `guides/00-principles.md` before writing any finding.** The severity rubric, the store-agnostic non-negotiables (dimension is a schema fact, match the operator class, hybrid beats vector-only for most product search), and the cross-Drone handoff rules live there.

---

## Routing table

| Invocation | Store | Primary guide(s) | Output |
|---|---|---|---|
| No store chosen yet / greenfield | any | `00-selection-matrix.md` | A store recommendation with the deciding factor named |
| New table / schema design | pgvector (default) | `pgvector-01-schema-and-drizzle.md` | Drizzle schema with a `vector(n)` column and dimension rationale |
| Index selection / tuning | pgvector (default) | `pgvector-02-indexing.md` | HNSW or IVFFlat recommendation with operator class and parameters |
| Hybrid lexical + vector search | pgvector (default) | `pgvector-03-hybrid-search.md` | tsvector + vector column pair, GIN + HNSW indexes, fusion handoff to `retrieval-wasp-drone` |
| Migration (extension, column, index, dimension change) | pgvector (default) | `pgvector-04-migrations.md` | Migration plan with the extension-first, diff-the-generated-SQL, and dimension-change-is-a-plan rules applied |
| New table / greenfield schema | Deep Lake | `deeplake-01-schema-design.md` + `deeplake-02-indexing.md` | `templates/schema-spec.md` + starter `ColumnDef[]` in `templates/columndef-table-spec.ts` |
| Schema review | Deep Lake | `deeplake-01-schema-design.md` + `deeplake-00-principles.md` | Findings report per `templates/audit-template.md` |
| Schema-heal plan | Deep Lake | `deeplake-03-schema-healing.md` | `templates/migration-plan.md` (additive heal plan) |
| Indexing audit | Deep Lake | `deeplake-02-indexing.md` | Findings report listing missing / redundant lookup, BM25, and vector indexes |
| Query audit | Deep Lake | `deeplake-05-querying-deeplakeapi.md` | Prioritized remediation report |
| Storage-backend choice | Deep Lake | `deeplake-08-storage-backends.md` | `examples/storage-backend-choice-walkthrough.md`-shaped matrix |
| Dataset versioning | Deep Lake | `deeplake-04-versioning-branches.md` | ADR via `templates/ADR.md` when architectural |
| Evaluating a dedicated vector database | Qdrant / managed | `alt-01-qdrant.md` / `alt-02-managed-vector-services.md` | A recommendation grounded in the selection matrix's escape hatches |

---

## Hard rules (never violate)

### Store-agnostic (apply regardless of backend)

1. **Dimension is a schema fact.** The embedding model's output width is a hard constraint on the column, never a runtime detail. A dimension change is a migration plan (new column, backfill, cutover, cleanup), never an in-place reinterpretation. See `guides/00-principles.md` and `pgvector-01-schema-and-drizzle.md` / `deeplake-06-embeddings-jsonb-versioning.md`.
2. **Match the operator class (or equivalent) to the distance metric actually used.** A mismatched index is either silently unused or silently wrong. See `guides/00-principles.md` and `pgvector-02-indexing.md`.
3. **Cite every claim.** File:line + guide section, research note, or official docs URL.
4. **Embedding storage only.** Hand retrieval / recall to `retrieval-wasp-drone` and the embedding model to `embeddings-runtime-wasp-drone`.
5. **Surface security; do not audit it.** Hand creds / token / PII handling to `security-wasp-drone`.

### pgvector-specific (this repo's default)

6. **`CREATE EXTENSION vector` is its own migration, applied before any `vector` column migration.** See `pgvector-04-migrations.md`.
7. **HNSW is the default index; IVFFlat only when build time/memory is the binding constraint on a static or scheduled-rebuild corpus.** See `pgvector-02-indexing.md`.
8. **Diff every generated migration that touches a `vector` column.** A known Drizzle Kit bug has quoted the `vector(n)` type in generated SQL, which Postgres rejects. See `pgvector-04-migrations.md`.

### Deep Lake-specific (unchanged from the original Deep Lake material)

9. **Single-source the schema in `deeplake-schema.ts`.** Every column lives in a `readonly ColumnDef[]`. See `deeplake-01-schema-design.md`.
10. **Heal additively, never blanket, never `ADD COLUMN IF NOT EXISTS`.** Deep Lake returns HTTP 500 (not 409) on a duplicate add. See `deeplake-03-schema-healing.md`.
11. **Every NOT NULL column has a DEFAULT.** `validateSchema()` enforces it. See `deeplake-01-schema-design.md`.
12. **Edits version-bump, they do not UPDATE.** skills / rules / goals / kpis are append-only. See `deeplake-06-embeddings-jsonb-versioning.md`.
13. **Guard every dynamic SQL fragment.** `sqlStr()` / `sqlLike()` / `sqlIdent()`. See `deeplake-05-querying-deeplakeapi.md`.

---

## The severity rubric

Store-agnostic; see `guides/00-principles.md` for the full rubric. Summary:

- **Must-fix** - operator/index mismatch, dimension mismatch or an unmigrated dimension change, an IVFFlat index built on an empty table, a Deep Lake blanket heal or `IF NOT EXISTS` add, a missing DEFAULT on a NOT NULL column, a true UPDATE on a Deep Lake append-only table, a raw interpolated table name. Blocks merge.
- **Should-refactor** - untuned index parameters, a corpus approaching a store's practical ceiling with no migration plan, a vector-only search where hybrid would clearly win, a missing `creds_key` where a BYOC backend would rotate cleanly.
- **Style** - naming, column ordering, comment density. Never blocks a PR alone.

---

## Cross-Drone handoffs

- **Schema PRD authoring** -> `library-wasp-drone`.
- **TypeScript data-access consumption** -> `typescript-node-wasp-drone`.
- **Security audit of creds, tenant isolation, PII columns** -> `security-wasp-drone`.
- **Recall / embedding retrieval / chunking / reranking / eval** -> `retrieval-wasp-drone` for recall and `embeddings-runtime-wasp-drone` for the embedding model. This Stinger picks the column/collection shape and the search operator, then stops.
- **Post-heal / post-migration verification** -> `quality-wasp-drone`.
- **Neon-specific connection, branching, or Drizzle-migration-runner mechanics beyond the vector column itself** -> `neon-drizzle-wasp-drone`.

---

## The guides

Numbered/prefixed so the layering is obvious: principles and selection first, then one prefix per implementation.

- `guides/00-principles.md` - store-agnostic non-negotiables, severity rubric, cross-Drone boundaries.
- `guides/00-selection-matrix.md` - which store, with an explicit escape-hatch table off pgvector.
- `guides/pgvector-01-schema-and-drizzle.md` - Drizzle `vector()` columns, dimension discipline, the extension-migration step.
- `guides/pgvector-02-indexing.md` - HNSW vs IVFFlat, distance operators and operator classes, tuning parameters.
- `guides/pgvector-03-hybrid-search.md` - tsvector + vector column pairing, GIN + HNSW, fusion handoff.
- `guides/pgvector-04-migrations.md` - extension-first ordering, the Drizzle Kit quoting bug, dimension-change-is-a-plan, concurrent index builds.
- `guides/deeplake-00-principles.md` through `guides/deeplake-08-storage-backends.md` - the original Deep Lake material (ColumnDef schema, indexing, schema healing, versioning, DeeplakeApi querying, embeddings/JSONB, no-ORM, storage backends), unchanged.
- `guides/alt-01-qdrant.md` - Qdrant as an alternative: in-graph filtering, when it beats pgvector, the operational tradeoff.
- `guides/alt-02-managed-vector-services.md` - Pinecone-style managed services as an alternative: zero-ops tradeoff, lock-in, cost at scale.

---

## Templates, scripts, examples

- **Templates** - `templates/schema-spec.md`, `templates/migration-plan.md`, `templates/indexes-decision-tree.md`, `templates/columndef-table-spec.ts`, `templates/ADR.md`, `templates/audit-template.md` (Deep Lake-shaped; reusable structurally for pgvector findings).
- **Examples** - `examples/new-deeplake-table.md`, `examples/schema-heal-add-column.md`, `examples/storage-backend-choice-walkthrough.md` (Deep Lake-specific, kept as worked examples of that implementation).
- **Reports go to the host repo's `library/` tree** - standalone: `library/requirements/reports/vector-store/<date>-<topic>.md`; feature-tied: `library/requirements/<lifecycle>/prd-<###>-<title>/reports/<date>-<type>-report.md`; ADRs: `library/knowledge/private/architecture/ADR-<n>-<topic>.md`. Use `templates/audit-template.md` as the starting skeleton.

---

## Output conventions

- **Always name the table/collection and the store** when a finding depends on a column or collection shape.
- **Every claim is sourced.** A guide section (`guides/pgvector-02-indexing.md`), a Deep Lake guide section, or `references/research/` (raw or distilled).
- **Migration and heal plans state the diff.** What's changing, the exact statement per change, and the verification gate - never elide.
- **Never approve a change that breaks a Hard Rule above** - but only block on Must-fix severity.

---

## When in doubt

- Unfamiliar operator, index type, or storage-backend combination? Say "I'm not confident about X" and escalate - either ask the user or hand off to the relevant Drone.
- Contested call between stores? Walk `guides/00-selection-matrix.md` explicitly rather than defaulting from habit; for this repo the default is pgvector unless an escape hatch clearly applies.

---

Part of The Wasp Nest, curated by [Mario Aldayuz a.k.a @thenotoriousllama]