Flint Note

Instructions

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']
    }
  },

• src/database/search-manager.ts:364-435 (helper)

  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[];
  }