search_notes_sql
Execute SQL SELECT queries to search and retrieve notes from your Flint Note vault, enabling precise data extraction from notes and metadata tables.
Instructions
Direct SQL search against notes database for maximum flexibility. Only SELECT queries allowed.
Input Schema
TableJSON Schema
| Name | Required | Description | Default |
|---|---|---|---|
| query | Yes | SQL SELECT query. Tables: notes (id, title, content, type, filename, path, created, updated, size), note_metadata (note_id, key, value, value_type) | |
| params | No | Optional parameters for parameterized queries | |
| limit | No | Maximum number of results | |
| timeout | No | Query timeout in milliseconds | |
| vault_id | No | Optional vault ID to operate on. If not provided, uses the current active vault. | |
| fields | No | Optional array of field names to include in response. Supports dot notation for nested fields (e.g. "metadata.tags") and wildcard patterns (e.g. "metadata.*"). If not specified, all fields are returned. |
Implementation Reference
- src/server/search-handlers.ts:78-97 (handler)MCP tool handler: validates input, resolves vault context, executes search via HybridSearchManager, applies field filtering, returns JSON-formatted results.handleSearchNotesSQL = async (args: SearchNotesSqlArgs) => { // Validate arguments validateToolArgs('search_notes_sql', args); const { hybridSearchManager } = await this.resolveVaultContext(args.vault_id); const results = await hybridSearchManager.searchNotesSQL(args); // Apply field filtering if specified const filteredResults = filterSearchResults(results, args.fields); return { content: [ { type: 'text', text: JSON.stringify(filteredResults, null, 2) } ] }; };
- src/server.ts:1258-1261 (registration)Registers the tool handler in the MCP server's CallToolRequestSchema switch statement, mapping 'search_notes_sql' calls to SearchHandlers.handleSearchNotesSQL.case 'search_notes_sql': return await this.searchHandlers.handleSearchNotesSQL( args as unknown as SearchNotesSqlArgs );
- src/server/tool-schemas.ts:393-442 (schema)Tool schema definition including name, description, and detailed inputSchema for parameters like query, params, limit, etc.{ name: 'search_notes_sql', description: 'Direct SQL search against notes database for maximum flexibility. Only SELECT queries allowed.', inputSchema: { type: 'object', properties: { query: { type: 'string', description: 'SQL SELECT query. Tables: notes (id, title, content, type, filename, path, created, updated, size), note_metadata (note_id, key, value, value_type)' }, params: { type: 'array', items: { oneOf: [ { type: 'string' }, { type: 'number' }, { type: 'boolean' }, { type: 'null' } ] }, description: 'Optional parameters for the SQL query' }, limit: { type: 'number', description: 'Maximum number of results to return' }, timeout: { type: 'number', description: 'Query timeout in milliseconds', default: 5000 }, vault_id: { type: 'string', description: 'Optional vault ID to search in. If not provided, uses the current active vault.' }, fields: { type: 'array', items: { type: 'string' }, description: 'Optional list of fields to include in response (id, title, content, type, filename, path, created, updated, size, metadata)' } }, required: ['query'] } },
- Core SQL execution logic in HybridSearchManager: validates query safety, executes SELECT with params and limits, handles aggregation vs regular queries, converts results to standardized SearchResult format.async searchNotesSQL(options: SqlSearchOptions): Promise<SearchResponse> { const startTime = Date.now(); const connection = await this.getReadOnlyConnection(); // Validate SQL query for safety this.validateSQLQuery(options.query); try { const limit = options.limit ?? 1000; const timeout = options.timeout ?? 30000; // Set query timeout await connection.run(`PRAGMA busy_timeout = ${timeout}`); // Execute query with limit let sql = options.query.trim(); if (!sql.toLowerCase().includes('limit')) { sql += ` LIMIT ${limit}`; } const rows = await connection.all<SearchRow | Record<string, unknown>>( sql, options.params || [] ); // Detect if this is an aggregation query or custom SQL const isAggregationQuery = this.isAggregationQuery(sql); let results: SearchResult[]; if (isAggregationQuery) { // For aggregation queries, return raw results with custom columns preserved results = rows.map(row => ({ ...row, // Preserve all custom aggregation columns first id: String(row.id || ''), title: String(row.title || ''), type: String(row.type || ''), tags: [], score: 1.0, snippet: '', lastUpdated: String(row.updated || ''), filename: String(row.filename || ''), path: String(row.path || ''), created: String(row.created || ''), modified: String(row.updated || ''), size: Number(row.size ?? 0), metadata: { title: String(row.title || ''), type: String(row.type || ''), created: String(row.created || ''), updated: String(row.updated || ''), filename: String(row.filename || '') } })); } else { // For regular note queries, convert to SearchResult format results = await this.convertRowsToResults(rows as SearchRow[], connection); } const queryTime = Date.now() - startTime; return { results, total: results.length, has_more: results.length >= limit, query_time_ms: queryTime }; } catch (error) { throw new Error( `SQL search failed: ${error instanceof Error ? error.message : 'Unknown error'}` ); } }
- src/server/types.ts:95-102 (schema)TypeScript interface defining the input arguments for the search_notes_sql tool.export interface SearchNotesSqlArgs { query: string; params?: (string | number | boolean | null)[]; limit?: number; timeout?: number; vault_id?: string; fields?: string[]; }