search_notes_sql
Perform SQL SELECT queries on the Flint Note database to retrieve and filter notes and metadata. Use predefined tables and fields for precise searches across notes, titles, content, and metadata.
Instructions
Direct SQL search against notes database for maximum flexibility. Only SELECT queries allowed.
Input Schema
| Name | Required | Description | Default |
|---|---|---|---|
| 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. | |
| limit | No | Maximum number of results | |
| params | No | Optional parameters for parameterized queries | |
| query | Yes | SQL SELECT query. Tables: notes (id, title, content, type, filename, path, created, updated, size), note_metadata (note_id, key, value, value_type) | |
| timeout | No | Query timeout in milliseconds | |
| vault_id | No | Optional vault ID to operate on. If not provided, uses the current active vault. |
Input Schema (JSON Schema)
{
"properties": {
"fields": {
"description": "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.",
"items": {
"type": "string"
},
"type": "array"
},
"limit": {
"default": 1000,
"description": "Maximum number of results",
"type": "number"
},
"params": {
"description": "Optional parameters for parameterized queries",
"items": {
"type": "string"
},
"type": "array"
},
"query": {
"description": "SQL SELECT query. Tables: notes (id, title, content, type, filename, path, created, updated, size), note_metadata (note_id, key, value, value_type)",
"type": "string"
},
"timeout": {
"default": 30000,
"description": "Query timeout in milliseconds",
"type": "number"
},
"vault_id": {
"description": "Optional vault ID to operate on. If not provided, uses the current active vault.",
"type": "string"
}
},
"required": [
"query"
],
"type": "object"
}
Implementation Reference
- src/server/search-handlers.ts:78-97 (handler)The main handler function for the 'search_notes_sql' MCP tool. Validates input, resolves vault context, executes SQL search via HybridSearchManager, applies field filtering, and 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)Registration of the search_notes_sql tool handler in the main MCP server switch statement within CallToolRequestSchema handler.case 'search_notes_sql': return await this.searchHandlers.handleSearchNotesSQL( args as unknown as SearchNotesSqlArgs );
- src/server/tool-schemas.ts:393-442 (schema)JSON Schema definition for the input arguments of the search_notes_sql tool.{ 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 implementation of SQL search in HybridSearchManager, including SQL validation, execution, result conversion, and security measures to prevent malicious queries.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 search_notes_sql.export interface SearchNotesSqlArgs { query: string; params?: (string | number | boolean | null)[]; limit?: number; timeout?: number; vault_id?: string; fields?: string[]; }