If you want the best possible solution vertical for most business, I'd be looking at using Lucene for FTS duty.

Having the FTS engine provide a google-style snippet of the most relevant document chunk is the holy grail for RAG applications. Lucene does this kind of thing better than anyone else:

https://lucene.apache.org/core/8_0_0/highlighter/org/apache/...

It is also very easy to customize this engine and align the document tokenization & indexing concerns with your specific retrieval scenarios.

This is for LLMs. In general RAG takes a user prompt and uses it to find potentially relevant documents in the database. It then enriches the original prompt with those documents so that the LLM has context that wasn't in its training dataset.

RAG -> Vector search -> means that your documents are not indexed as full text but as Vectorized objects which mean that then you can search using concepts instead of exacts strings you would use with a regular "Fulltext search".

This makes the search less precise and more powerful at the same time (ie it could look clever to some extent).

This might provide context: https://alexgarcia.xyz/blog/2024/sqlite-vec-hybrid-search/in...

It's provided via https://github.com/asg017/sqlite-vec

SQLite itself doesn't have native embedding search, but extensions like sqlite-vss and sqlite-vectorize add vector similarity search capabilities to SQLite.