/**
* SQLite Vector Search Tools
*
* Vector similarity search and embedding operations.
* Uses JSON arrays for vector storage (no external extensions needed).
* 11 tools total.
*/
import { z } from 'zod';
import type { SqliteAdapter } from '../SqliteAdapter.js';
import type { ToolDefinition, RequestContext } from '../../../types/index.js';
// Vector schemas
const VectorStoreSchema = z.object({
table: z.string().describe('Table name'),
idColumn: z.string().describe('ID column name'),
vectorColumn: z.string().describe('Column to store vector (as JSON)'),
id: z.union([z.string(), z.number()]).describe('Row identifier'),
vector: z.array(z.number()).describe('Vector as array of numbers')
});
const VectorSearchSchema = z.object({
table: z.string().describe('Table name'),
vectorColumn: z.string().describe('Vector column name'),
queryVector: z.array(z.number()).describe('Query vector'),
metric: z.enum(['cosine', 'euclidean', 'dot']).optional().default('cosine'),
limit: z.number().optional().default(10),
whereClause: z.string().optional(),
returnColumns: z.array(z.string()).optional().describe('Columns to return')
});
const VectorCreateTableSchema = z.object({
tableName: z.string().describe('Table name'),
dimensions: z.number().describe('Vector dimensions'),
additionalColumns: z.array(z.object({
name: z.string(),
type: z.string()
})).optional()
});
const VectorNormalizeSchema = z.object({
vector: z.array(z.number()).describe('Vector to normalize')
});
const VectorDistanceSchema = z.object({
vector1: z.array(z.number()).describe('First vector'),
vector2: z.array(z.number()).describe('Second vector'),
metric: z.enum(['cosine', 'euclidean', 'dot']).optional().default('cosine')
});
const VectorBatchStoreSchema = z.object({
table: z.string().describe('Table name'),
idColumn: z.string().describe('ID column name'),
vectorColumn: z.string().describe('Vector column name'),
items: z.array(z.object({
id: z.union([z.string(), z.number()]),
vector: z.array(z.number())
})).describe('Items to store')
});
const VectorDeleteSchema = z.object({
table: z.string().describe('Table name'),
idColumn: z.string().describe('ID column name'),
ids: z.array(z.union([z.string(), z.number()])).describe('IDs to delete')
});
const VectorGetSchema = z.object({
table: z.string().describe('Table name'),
idColumn: z.string().describe('ID column name'),
vectorColumn: z.string().describe('Vector column name'),
id: z.union([z.string(), z.number()]).describe('Row identifier')
});
const VectorCountSchema = z.object({
table: z.string().describe('Table name'),
dimensions: z.number().optional().describe('Filter by dimension count')
});
const VectorStatsSchema = z.object({
table: z.string().describe('Table name'),
vectorColumn: z.string().describe('Vector column name'),
sampleSize: z.number().optional().default(100)
});
const VectorDimensionsSchema = z.object({
table: z.string().describe('Table name'),
vectorColumn: z.string().describe('Vector column name')
});
/**
* Get all vector tools
*/
export function getVectorTools(adapter: SqliteAdapter): ToolDefinition[] {
return [
createVectorCreateTableTool(adapter),
createVectorStoreTool(adapter),
createVectorBatchStoreTool(adapter),
createVectorSearchTool(adapter),
createVectorGetTool(adapter),
createVectorDeleteTool(adapter),
createVectorCountTool(adapter),
createVectorStatsTool(adapter),
createVectorDimensionsTool(adapter),
createVectorNormalizeTool(),
createVectorDistanceTool()
];
}
// Helper functions for vector operations
function cosineSimilarity(a: number[], b: number[]): number {
if (a.length !== b.length) throw new Error('Vector dimensions must match');
let dotProd = 0;
let normA = 0;
let normB = 0;
for (let i = 0; i < a.length; i++) {
const aVal = a[i] ?? 0;
const bVal = b[i] ?? 0;
dotProd += aVal * bVal;
normA += aVal * aVal;
normB += bVal * bVal;
}
const magnitude = Math.sqrt(normA) * Math.sqrt(normB);
return magnitude === 0 ? 0 : dotProd / magnitude;
}
function euclideanDistance(a: number[], b: number[]): number {
if (a.length !== b.length) throw new Error('Vector dimensions must match');
let sum = 0;
for (let i = 0; i < a.length; i++) {
const aVal = a[i] ?? 0;
const bVal = b[i] ?? 0;
const diff = aVal - bVal;
sum += diff * diff;
}
return Math.sqrt(sum);
}
function dotProduct(a: number[], b: number[]): number {
if (a.length !== b.length) throw new Error('Vector dimensions must match');
let sum = 0;
for (let i = 0; i < a.length; i++) {
sum += (a[i] ?? 0) * (b[i] ?? 0);
}
return sum;
}
function normalizeVector(v: number[]): number[] {
const norm = Math.sqrt(v.reduce((sum, x) => sum + x * x, 0));
if (norm === 0) return v;
return v.map(x => x / norm);
}
function parseVector(value: unknown): number[] {
if (Array.isArray(value)) {
return value.map(Number);
}
if (typeof value === 'string') {
try {
const parsed: unknown = JSON.parse(value);
if (Array.isArray(parsed)) {
return parsed.map(Number);
}
} catch {
// Not valid JSON
}
}
throw new Error('Invalid vector format');
}
/**
* Create a table for vector storage
*/
function createVectorCreateTableTool(adapter: SqliteAdapter): ToolDefinition {
return {
name: 'sqlite_vector_create_table',
description: 'Create a table optimized for vector storage with JSON vector column.',
group: 'vector',
inputSchema: VectorCreateTableSchema,
requiredScopes: ['write'],
handler: async (params: unknown, _context: RequestContext) => {
const input = VectorCreateTableSchema.parse(params);
if (!/^[a-zA-Z_][a-zA-Z0-9_]*$/.test(input.tableName)) {
throw new Error('Invalid table name');
}
const columns = [
'id INTEGER PRIMARY KEY',
'vector TEXT NOT NULL', // JSON array
`dimensions INTEGER DEFAULT ${input.dimensions}`
];
if (input.additionalColumns) {
for (const col of input.additionalColumns) {
if (!/^[a-zA-Z_][a-zA-Z0-9_]*$/.test(col.name)) {
throw new Error(`Invalid column name: ${col.name}`);
}
columns.push(`"${col.name}" ${col.type}`);
}
}
const sql = `CREATE TABLE IF NOT EXISTS "${input.tableName}" (${columns.join(', ')})`;
await adapter.executeQuery(sql);
return {
success: true,
message: `Vector table '${input.tableName}' created with ${input.dimensions} dimensions`,
sql
};
}
};
}
/**
* Store a vector
*/
function createVectorStoreTool(adapter: SqliteAdapter): ToolDefinition {
return {
name: 'sqlite_vector_store',
description: 'Store or update a vector in the database.',
group: 'vector',
inputSchema: VectorStoreSchema,
requiredScopes: ['write'],
handler: async (params: unknown, _context: RequestContext) => {
const input = VectorStoreSchema.parse(params);
if (!/^[a-zA-Z_][a-zA-Z0-9_]*$/.test(input.table)) {
throw new Error('Invalid table name');
}
if (!/^[a-zA-Z_][a-zA-Z0-9_]*$/.test(input.idColumn)) {
throw new Error('Invalid ID column name');
}
if (!/^[a-zA-Z_][a-zA-Z0-9_]*$/.test(input.vectorColumn)) {
throw new Error('Invalid vector column name');
}
const vectorJson = JSON.stringify(input.vector);
const idValue = typeof input.id === 'string' ? `'${input.id}'` : input.id;
// Try update first, then insert
const updateSql = `UPDATE "${input.table}" SET "${input.vectorColumn}" = '${vectorJson}' WHERE "${input.idColumn}" = ${idValue}`;
const updateResult = await adapter.executeWriteQuery(updateSql);
if (updateResult.rowsAffected === 0) {
const insertSql = `INSERT INTO "${input.table}" ("${input.idColumn}", "${input.vectorColumn}") VALUES (${idValue}, '${vectorJson}')`;
await adapter.executeWriteQuery(insertSql);
}
return {
success: true,
id: input.id,
dimensions: input.vector.length
};
}
};
}
/**
* Batch store vectors
*/
function createVectorBatchStoreTool(adapter: SqliteAdapter): ToolDefinition {
return {
name: 'sqlite_vector_batch_store',
description: 'Store multiple vectors in a batch operation.',
group: 'vector',
inputSchema: VectorBatchStoreSchema,
requiredScopes: ['write'],
handler: async (params: unknown, _context: RequestContext) => {
const input = VectorBatchStoreSchema.parse(params);
if (!/^[a-zA-Z_][a-zA-Z0-9_]*$/.test(input.table)) {
throw new Error('Invalid table name');
}
if (!/^[a-zA-Z_][a-zA-Z0-9_]*$/.test(input.idColumn)) {
throw new Error('Invalid ID column name');
}
if (!/^[a-zA-Z_][a-zA-Z0-9_]*$/.test(input.vectorColumn)) {
throw new Error('Invalid vector column name');
}
let stored = 0;
for (const item of input.items) {
const vectorJson = JSON.stringify(item.vector);
const idValue = typeof item.id === 'string' ? `'${item.id}'` : item.id;
const sql = `INSERT OR REPLACE INTO "${input.table}" ("${input.idColumn}", "${input.vectorColumn}") VALUES (${idValue}, '${vectorJson}')`;
await adapter.executeWriteQuery(sql);
stored++;
}
return {
success: true,
stored,
dimensions: input.items[0]?.vector.length
};
}
};
}
/**
* Vector similarity search
*/
function createVectorSearchTool(adapter: SqliteAdapter): ToolDefinition {
return {
name: 'sqlite_vector_search',
description: 'Find similar vectors using cosine, euclidean, or dot product similarity.',
group: 'vector',
inputSchema: VectorSearchSchema,
requiredScopes: ['read'],
handler: async (params: unknown, _context: RequestContext) => {
const input = VectorSearchSchema.parse(params);
if (!/^[a-zA-Z_][a-zA-Z0-9_]*$/.test(input.table)) {
throw new Error('Invalid table name');
}
if (!/^[a-zA-Z_][a-zA-Z0-9_]*$/.test(input.vectorColumn)) {
throw new Error('Invalid vector column name');
}
// Build select clause
let selectCols = '*';
if (input.returnColumns && input.returnColumns.length > 0) {
for (const col of input.returnColumns) {
if (!/^[a-zA-Z_][a-zA-Z0-9_]*$/.test(col)) {
throw new Error(`Invalid column name: ${col}`);
}
}
selectCols = input.returnColumns.map(c => `"${c}"`).join(', ');
}
let sql = `SELECT ${selectCols}, "${input.vectorColumn}" FROM "${input.table}"`;
if (input.whereClause) {
sql += ` WHERE ${input.whereClause}`;
}
const result = await adapter.executeReadQuery(sql);
// Calculate similarities in JavaScript
const queryVector = input.queryVector;
const scored = (result.rows ?? []).map(row => {
try {
const storedVector = parseVector(row[input.vectorColumn]);
let score: number;
switch (input.metric) {
case 'euclidean':
// Invert so lower distance = higher score
score = 1 / (1 + euclideanDistance(queryVector, storedVector));
break;
case 'dot':
score = dotProduct(queryVector, storedVector);
break;
case 'cosine':
default:
score = cosineSimilarity(queryVector, storedVector);
}
return { ...row, _similarity: Math.round(score * 10000) / 10000 };
} catch {
return { ...row, _similarity: -1 };
}
}).filter(r => r._similarity >= 0);
// Sort by similarity (descending) and limit
scored.sort((a, b) => b._similarity - a._similarity);
const limited = scored.slice(0, input.limit);
return {
success: true,
metric: input.metric,
count: limited.length,
results: limited
};
}
};
}
/**
* Get a vector by ID
*/
function createVectorGetTool(adapter: SqliteAdapter): ToolDefinition {
return {
name: 'sqlite_vector_get',
description: 'Retrieve a vector by its ID.',
group: 'vector',
inputSchema: VectorGetSchema,
requiredScopes: ['read'],
handler: async (params: unknown, _context: RequestContext) => {
const input = VectorGetSchema.parse(params);
if (!/^[a-zA-Z_][a-zA-Z0-9_]*$/.test(input.table)) {
throw new Error('Invalid table name');
}
if (!/^[a-zA-Z_][a-zA-Z0-9_]*$/.test(input.idColumn)) {
throw new Error('Invalid ID column name');
}
if (!/^[a-zA-Z_][a-zA-Z0-9_]*$/.test(input.vectorColumn)) {
throw new Error('Invalid vector column name');
}
const idValue = typeof input.id === 'string' ? `'${input.id}'` : input.id;
const sql = `SELECT * FROM "${input.table}" WHERE "${input.idColumn}" = ${idValue}`;
const result = await adapter.executeReadQuery(sql);
if (!result.rows || result.rows.length === 0) {
return { success: false, error: 'Vector not found' };
}
const row = result.rows[0];
if (!row) {
return { success: false, error: 'Vector not found' };
}
const vectorData = parseVector(row[input.vectorColumn]);
return {
success: true,
id: input.id,
dimensions: vectorData.length,
vector: vectorData,
metadata: row
};
}
};
}
/**
* Delete vectors by ID
*/
function createVectorDeleteTool(adapter: SqliteAdapter): ToolDefinition {
return {
name: 'sqlite_vector_delete',
description: 'Delete vectors by their IDs.',
group: 'vector',
inputSchema: VectorDeleteSchema,
requiredScopes: ['write'],
handler: async (params: unknown, _context: RequestContext) => {
const input = VectorDeleteSchema.parse(params);
if (!/^[a-zA-Z_][a-zA-Z0-9_]*$/.test(input.table)) {
throw new Error('Invalid table name');
}
if (!/^[a-zA-Z_][a-zA-Z0-9_]*$/.test(input.idColumn)) {
throw new Error('Invalid ID column name');
}
const idValues = input.ids.map(id =>
typeof id === 'string' ? `'${id}'` : String(id)
).join(', ');
const sql = `DELETE FROM "${input.table}" WHERE "${input.idColumn}" IN (${idValues})`;
const result = await adapter.executeWriteQuery(sql);
return {
success: true,
deleted: result.rowsAffected
};
}
};
}
/**
* Count vectors
*/
function createVectorCountTool(adapter: SqliteAdapter): ToolDefinition {
return {
name: 'sqlite_vector_count',
description: 'Count vectors in a table.',
group: 'vector',
inputSchema: VectorCountSchema,
requiredScopes: ['read'],
handler: async (params: unknown, _context: RequestContext) => {
const input = VectorCountSchema.parse(params);
if (!/^[a-zA-Z_][a-zA-Z0-9_]*$/.test(input.table)) {
throw new Error('Invalid table name');
}
const sql = `SELECT COUNT(*) as count FROM "${input.table}"`;
const result = await adapter.executeReadQuery(sql);
return {
success: true,
count: result.rows?.[0]?.['count'] ?? 0
};
}
};
}
/**
* Vector statistics
*/
function createVectorStatsTool(adapter: SqliteAdapter): ToolDefinition {
return {
name: 'sqlite_vector_stats',
description: 'Get statistics about vectors in a table.',
group: 'vector',
inputSchema: VectorStatsSchema,
requiredScopes: ['read'],
handler: async (params: unknown, _context: RequestContext) => {
const input = VectorStatsSchema.parse(params);
if (!/^[a-zA-Z_][a-zA-Z0-9_]*$/.test(input.table)) {
throw new Error('Invalid table name');
}
if (!/^[a-zA-Z_][a-zA-Z0-9_]*$/.test(input.vectorColumn)) {
throw new Error('Invalid vector column name');
}
// Get sample of vectors
const sql = `SELECT "${input.vectorColumn}" FROM "${input.table}" LIMIT ${input.sampleSize}`;
const result = await adapter.executeReadQuery(sql);
const vectors = (result.rows ?? []).map(row => {
try {
return parseVector(row[input.vectorColumn]);
} catch {
return null;
}
}).filter((v): v is number[] => v !== null);
if (vectors.length === 0) {
return {
success: true,
count: 0,
message: 'No valid vectors found'
};
}
const firstVector = vectors[0];
if (!firstVector) {
return {
success: true,
count: 0,
message: 'No valid vectors found'
};
}
const dimensions = firstVector.length;
const magnitudes = vectors.map(v => Math.sqrt(v.reduce((s, x) => s + x * x, 0)));
return {
success: true,
sampleSize: vectors.length,
dimensions,
magnitudeStats: {
min: Math.min(...magnitudes),
max: Math.max(...magnitudes),
avg: magnitudes.reduce((s, m) => s + m, 0) / magnitudes.length
}
};
}
};
}
/**
* Get vector dimensions
*/
function createVectorDimensionsTool(adapter: SqliteAdapter): ToolDefinition {
return {
name: 'sqlite_vector_dimensions',
description: 'Get the dimensions of vectors in a table.',
group: 'vector',
inputSchema: VectorDimensionsSchema,
requiredScopes: ['read'],
handler: async (params: unknown, _context: RequestContext) => {
const input = VectorDimensionsSchema.parse(params);
if (!/^[a-zA-Z_][a-zA-Z0-9_]*$/.test(input.table)) {
throw new Error('Invalid table name');
}
if (!/^[a-zA-Z_][a-zA-Z0-9_]*$/.test(input.vectorColumn)) {
throw new Error('Invalid vector column name');
}
const sql = `SELECT "${input.vectorColumn}" FROM "${input.table}" LIMIT 1`;
const result = await adapter.executeReadQuery(sql);
if (!result.rows || result.rows.length === 0) {
return { success: true, dimensions: null, message: 'No vectors found' };
}
const firstRow = result.rows[0];
if (!firstRow) {
return { success: true, dimensions: null, message: 'No vectors found' };
}
const vector = parseVector(firstRow[input.vectorColumn]);
return {
success: true,
dimensions: vector.length
};
}
};
}
/**
* Normalize a vector
*/
function createVectorNormalizeTool(): ToolDefinition {
return {
name: 'sqlite_vector_normalize',
description: 'Normalize a vector to unit length.',
group: 'vector',
inputSchema: VectorNormalizeSchema,
requiredScopes: ['read'],
handler: (params: unknown, _context: RequestContext) => {
const input = VectorNormalizeSchema.parse(params);
const normalized = normalizeVector(input.vector);
return Promise.resolve({
success: true,
original: input.vector,
normalized,
originalMagnitude: Math.sqrt(input.vector.reduce((s, x) => s + x * x, 0))
});
}
};
}
/**
* Calculate distance between two vectors
*/
function createVectorDistanceTool(): ToolDefinition {
return {
name: 'sqlite_vector_distance',
description: 'Calculate distance or similarity between two vectors.',
group: 'vector',
inputSchema: VectorDistanceSchema,
requiredScopes: ['read'],
handler: (params: unknown, _context: RequestContext) => {
const input = VectorDistanceSchema.parse(params);
if (input.vector1.length !== input.vector2.length) {
return Promise.resolve({
success: false,
error: 'Vector dimensions must match'
});
}
let result: number;
switch (input.metric) {
case 'euclidean':
result = euclideanDistance(input.vector1, input.vector2);
break;
case 'dot':
result = dotProduct(input.vector1, input.vector2);
break;
case 'cosine':
default:
result = cosineSimilarity(input.vector1, input.vector2);
}
return Promise.resolve({
success: true,
metric: input.metric,
value: Math.round(result * 10000) / 10000
});
}
};
}
