CrestApp use document as nosql

🔒 You must be logged in as an Administrator or Editor to listen to this audio.

The session history in this project is not stored in a traditional NoSQL database like Redis or MongoDB. Because this ecosystem is built natively on top of the Orchard Core framework, it relies on YesSql for storing chat sessions.

YesSql is a unique, hybrid database tool specifically designed for Orchard Core. It acts as a document database (like NoSQL) built on top of a standard Relational Database (like SQL Server, SQLite, PostgreSQL, or MySQL).

Here is the architectural breakdown of how session history is stored and managed:

1. The Document Store (YesSql)

When a user interacts with the chat, the system creates an AIChatSession object (and associated prompt/message objects). Instead of breaking this object apart into multiple relational tables, YesSql serializes the entire C# object into a JSON string and saves it into a single Document table in your SQL database.

This gives you the flexibility of a NoSQL store—you can easily add new properties to a chat session without having to write complex SQL migration scripts to alter database columns.

2. The Indexing Engine for Fast Lookups

Storing JSON strings is great for flexibility, but terrible for searching (e.g., finding all sessions for a specific user). To solve this, the architecture uses YesSql Indexes (AIChatSessionIndex).

Whenever a chat session document is saved, the index provider extracts key fields—such as SessionId, UserId, ProfileId, and IsActive—and writes them into a standard, flat SQL table. When the system needs to retrieve a user's history, it queries this highly optimized SQL index table first, gets the Document ID, and then pulls the full JSON chat history from the Document table.

3. Where Specialized Databases (NoSQL/Vector) Are Actually Used

While the linear transcript of the chat history lives in YesSql, the project does use external NoSQL-style databases for a different purpose: Semantic Search and RAG (Retrieval-Augmented Generation).

If the AI needs to remember concepts long-term or search through uploaded knowledge bases, it doesn't use the SQL database. Instead, it relies on the CrestApps.OrchardCore.AI.Memory and DataSources modules, which connect to:

  • Elasticsearch: A NoSQL search engine optimized for vector embeddings and full-text search.
  • Azure AI Search: A cloud-based NoSQL vector store.

Summary of the Flow

  1. User sends a message: The interaction is appended to an AIChatSession C# object.
  2. Storage: YesSql converts the session to JSON and saves it in the SQL Server/SQLite Document table.
  3. Indexing: Key queryable fields (like SessionId) are mapped to an SQL index table for fast retrieval later.
  4. Long-Term "AI Memory": If the chat contains facts the AI needs to remember across different sessions, those specific facts are converted to vector embeddings and sent to a NoSQL vector database like Elasticsearch.