EligereAI — Technical Breakdown, Background
How I built www.eligere.app/ai, AI-enhanced library data analysis and search app for Eligere (all-in-one Choral Music Management Platform)
How I built www.eligere.app/ai, AI-enhanced library data analysis and search app for Eligere (all-in-one Choral Music Management Platform)
Please note : Part 1 of the Eligere series goes over the technical details of EligereManage which is strongly recommended to read before you begin to read this article — the applications strongly complement each other in the same eco-system.
Why I built EligereAI
I am constantly curious about technology and especially concerning its practical application in my own projects. Whilst I have always found ‘AI’ technologies fascinating, as with the rest of the world, my curiosity piqued with the release of ‘good’ Large Language Models (LLMs) … I was just a little ahead of the curve.
As I have always said to family and friends, if I were not a Computer Scientist, I would be studying Linguistics in some manner. I find language and interpreting language absolutely fascinating.
When I first stumbled across OpenAI’s GPT 3 (ChatGPT’s mainstream explosion in popularity was powered with GPT 3.5), I figured I must build an application utilising it. At the time, it seemed that a natural progression was to attempt to complement EligereManage, hence EligereAI was born (although then I called it EligereX as a placeholder name) — also as at the time I did not have a ‘good’ search function built into EligereManage (SQL ‘LIKE’ is not an ideal solution!)
NOTE: GIF of ‘EligereX’ is not real-time
Similarly to EligereManage v1, the first proof-of-concept began as scrappy Python code (see above for a small demo) which merely generated a SQL statement based on the Eligere Database DDL (Data Definition Language) based on the interpretation of a plain english user query. I thought this was fascinating as it proved to me that LLMs could be used to interpret user desired actions based on contextual input.
EligereAI v2
Transitioning to .NET MAUI
The natural progression for EligereAI was to first transition over to a mobile-friendly front-end framework and then it was to complete the ‘retrieval’ part of the search for the user. Naturally, as this app was part of the Eligere ecosystem, it made sense to build the application using .NET MAUI. I began work.
Implementing Retrieval
The ‘retrieval’ part was a little more risky as this involved connecting to the LIVE production database. Given the unpredictability of non-deterministic systems such as LLMs, this was something I thought about for some time.
Security Concerns (Database Integrity)
I came to the realisation that the best way of implementing this interfacing with the live production database would be to provide the LLM with a ‘read-only’ key so that if malicious / accidentally damaging queries were generated it would not harm the database’s integrity. I also strengthened the EligereAI System Prompt to explicitly not generate harmful SQL commands which included INSERT, DELETE, DROP, etc. This is a Swiss-cheese style of protection of multiple layers of defence to mitigate the risk of damage to databases if a write permissions key is given to EligereAI.
Furthermore, there are a couple of tables which contains choir member details which has additional protections in place. As stated in EligereManage’s technical breakdown, the service which powers Eligere Database is www.neon.tech. Neon.tech supports PostgreSQL role-based access controls as standard, which means that LLM access can be isolated from accessing personal information tables (with a separate configured schema)— in a choral music library database this is within requirements but with businesses ONLY containing personal information in databases this is not a workable solution. Second of all, as an extra layer of security, the DDL script given to the LLM does not even include the sensitive tables in it — the LLM is not aware they even exist.
At this point, I had now built a C# wrapper for GPT which is able to respond with (almost always) valid SQL from a user input with smart-context depending on the accompanying DDL fed in by the application and that cannot respond to / access / process ANY sensitive choir member data.
I also had now built a C# Database wrapper for the PostgreSQL interfacing with the generated SQL from the GPT wrapper. The next issue was to work out how to format the responses to the user in a manageable and mobile friendly manner. As seen below, one of my initial tests was a simple pop-up window to the user, however this neither elegant (or particularly useful).
As I researched I realised that there was a glaringly simple way of managing the output which all of the other chatbot applications were utilising; rendering and displaying in the Markdown format. The Markdown format is a lightweight markup language that uses plain text formatting syntax to create structured documents, making it easy to convert to HTML and other formats. I then implemented this into a method of the C# GPT wrapper.
Overview of EligereAI — Current Architecture
In the following months, I gradually updated components of EligereAI such as updating the GPT model used to GPT 4-o[mni] as GPT 3.5 was deprecated by OpenAI. I have also since ’smartened up’ the GUI for the user and increased responsiveness through minor tweaks of .NET MAUI pages. I have also fixed many bugs and unexpected behaviours, for example, when the SQL command did not return any data (i.e. the user question was not valid or contained any records on the database matching the SQL), the markdown layer was still getting triggered in the process meaning that it would hallucinate content based on the lack of (context) tokens provided.
The current architecture of the EligereAI application is as follows …
EligereAI — Example User Flow
User enters plain-English query to EligereAI GUI — e.g. How many composers in library, when did Mozart die .. etc.
Plain-English query + Eligere Database DDL sent to EligereGenSQL GPT 4-omni session
Generated database SQL for request is sent back to the user’s client
SQL queried against Eligere Database
Eligere Database data sent back to the user’s client
Database data is formatted with EligereSmartMarkdown using GPT 4-omni session
Formatted Markdown sent back to the user’s client
User’s client device renders the markdown as an (interactive) HTML MAUI WebView page which is then displayed to the user.
Libraries, Packages Used (NUGET)
Markdig
Markdig is used for processing Markdown in the Eligere Markdown service within the EligereAI application. It is incredibly easy to use A code snippet of the service is included below:
Eligere MarkdownService
// Eligere MarkdownService.cs
using Markdig;
namespace EligereAI.Services
{
public class MarkdownService
{
public string ParseMarkdown(string markdown)
{
var pipeline = new MarkdownPipelineBuilder()
.UseAdvancedExtensions()
.Build();
return Markdown.ToHtml(markdown, pipeline);
}
}
}An example code snippet below shows the usage of Markdig in processing the LLM output into the new WebView page.
using EligereAI.Services;
namespace EligereAI
{
public partial class MarkdownEligereAIResponse : ContentPage
{
private readonly MarkdownService _markdownService = new MarkdownService();
public MarkdownEligereAIResponse(string markdown)
{
InitializeComponent();
LoadMarkdown(markdown);
}
private void LoadMarkdown(string markdown)
{
string htmlContent = $@"
<!DOCTYPE html>
<html>
<head>
<meta name='viewport' content='width=device-width, initial-scale=1.0'>
<style>
body {{
font-family: -apple-system, BlinkMacSystemFont, 'Segoe UI', Roboto, 'Helvetica Neue', Arial, sans-serif;
margin: 0;
padding: 10px;
padding-bottom: 50px; /* space for the footer */
}}
table {{
width: 100%;
border-collapse: collapse;
}}
th, td {{
border: 1px solid #ddd;
padding: 8px;
text-align: left;
}}
th {{
background-color: #f2f2f2;
}}
.footer {{
position: fixed;
left: 0;
bottom: 0;
width: 100%;
background-color: #f8d7da;
color: #D22B2B;
text-align: center;
padding: 10px 0;
border-top: 1px solid #f5c6cb;
font-size: 14px;
}}
</style>
</head>
<body>
{new MarkdownService().ParseMarkdown(markdown)}
<div class='footer'>
EligereAI is in BETA. Check all information.
</div>
</body>
</html>";
MarkdownWebView.Source = new HtmlWebViewSource { Html = htmlContent };
}
}
}Npgsql
Npgsql is a package that allows .NET-based projects to access and interface with PostgreSQL databases directly. It is fully open-source and all written in C#.
OpenAI
This crucial EligereAI package from OpenAI allows for a GPT wrapper to be developed in C# to interface directly with OpenAI’s Large Language Models.
HTML MAUI WebView page
This page contains a summary, data analysis and / or quick answers to questions and / or searches. It also supports tables and some graph views. I have since amended the System Prompt for this markdown ‘service’ so it now follows a ‘Chain-of-Thought’ embedded into the System Prompt which helps it self-correct mistakes and allows for easier human comprehension of the ’thought’ process (I use that term loosely on purpose due to the various ongoing research currently attempting to answer that question … but that is for another article and another day!)
System Prompts — Examples of EligereAI Usage
# EXAMPLE PAST SYSTEM PROMPT FOR EligereGenSQL
You are a SQL bot for EligereDatabase which generates SQL based on input from a user. You must follow these commandments:
1- ONLY OUTPUT READ-ONLY SQL
2- If a user asks a valid SQL query, return it if it doesn't violate rules.
3- Only generate valid SQL for EligereDatabase
4- Your responses must ONLY CONTAIN SQL
5- Always convert IDs to plain english. For example, LanguageID to 'English'.# EXAMPLE SYSTEM PROMPT FOR EligereSmartMarkdown
You are an AI assistant called SmartFormat. Your role is to convert database data into well-formatted and easy-to-understand markdown text, based on the provided user query, SQL query, and database schema.
For the user query: ""{originalUserQueryPlainEnglish}""
Your response should follow these guidelines:
1. Provide a clear and concise explanation of the data (maximum 3 sentences), including the thought process behind interpreting the query.
2. If applicable, present the data in a well-formatted markdown table.
3. Use only standard markdown syntax in your response. DO NOT include any other content.
4. Avoid using technical SQL terminology, as it may confuse the user.
5. Do not reference any data sources outside of the provided user query, SQL query, and database schema.
6. Use British English spelling conventions.
Additionally, you MUST: Focus on accurately interpreting and presenting the requested data, without making assumptions or hallucinating information.These are by no means final and are continually refined for performance, safety and cost (lower token usage = best).
Please note neither of these system prompts include additional data included as context such as the user query and EligereDatabase DDL script.
Reflection on current state and improvements for the future
This brings me nicely onto looking into the future of EligereAI, what I have learnt so far and what limitations this application will have.
For starters, whilst on the topic of ‘Chain-of-Thought’ reasoning, OpenAI has recently unveiled the ‘preview’ version of their so called ‘O1’ model (AKA Project Strawberry). O1 implements an unknown ‘Chain-of-Thought’ technique into its responses.
Whilst this could improve the ability of EligereAI in the longer term for more complex data analysis, it is currently far more expensive on the API, takes far longer to generate responses and is far too experimental to implement into production code.
Limitations, Improvements
Originally, I found that EligereAI would be a little overprotective over queries deemed ‘harmful’ to database integrity, but after implementing the separate schema and role-based access, this was able to be dialled-in and thus it would produce less of these false positives.
An issue I frequently found in an old build of this app was that it would confuse similar sounding queries relating to ‘people’ and ‘composers’ and thus generate incorrect responses from the database to the asked query (in all fairness to it, they are clearly named tables). This was remedied by adding little explainer comments in the LLM’s DDL script which then seemed to mostly be taken onboard.
Regarding current limitations of EligereAI, I have found it will typically make the majority of its mistakes when doing more complicated data analysis of a user’s question. Whilst uncommon in my testing, it can sometimes create some very erratic fake data or instil obviously incorrect assertions; the ‘Chain-of-Thought’ somewhat helps the human identify these considerably, however. Additionally, it is likely with several months of logging data of user questions this could be fine-tuned to be more reliable.
Alternatively, other database architectures could be explored such as vector databases which could increase the likelihood of related database searches yielding desired results. However, for EligereAI, I opted for a more traditional approach using SQL databases. This choice was driven by the need for EligereManage to handle inventory management and other structured data, as well as the requirement for direct compatibility with standard SQL databases to work natively with other organisations.
Our approach stands in contrast to some other AI-driven search systems. In the summer of 2024, I remember listening to a podcast episode with Aravind Srinivas, CEO of Perplexity.ai (an AI-search and competitor to Google). He was remarking on how in the early days of his startup, they had a heavy focus on natural language to SQL translation and they even built a tool. He remarked that this was because he felt the “output entropy is lower [with SQL]” and because it is very much like a template; very few statements like SELECT and COUNT used for all of the SQL output.
However, Srinivas accepted that this approach was not workable for Perplexity due to erroneous SQL queries and issues with retry mechanisms and such. This is the approach that I would have taken if EligereAI was solely a search and ranking based system like Perplexity. But our use case differs significantly. EligereAI requires the robustness and structure of traditional SQL tables and records for EligereManage’s functions (e.g. inventory management), while also needing to interface seamlessly with other organisations’ databases.
With regards to improvements in Large Language Models themselves used in this application, there could be a menu built-out which could allow for other models to be used instead such as an incredibly capable recent version of Claude by ANTHROPIC or perhaps the software could be adapted to run a smaller version of a language model locally on the mobile device itself (see Apple Intelligence or Mistral’s 7bn parameter model for examples of local implementations on mobile devices).
Overall, this system is fantastic for basic information lookup / analysis and being a language model I have found that speakers of other languages have been able to use EligereAI to navigate around the choral library’s catalogue (which is an incredible aspect of LLM technology!).
This article contains technical details accurate of October 2024 of the architecture of EligereAI and assorted components, however this is subject to change as the architecture is updated and tweaked to better security, speed, robustness and utility.
Thank you very much for reading this technical deep-dive on how and why I created EligereAI (and associated components). I hope this was of interest to you- please let me know if you have any thoughts … I would love to hear them. Finally, I would like to leave you on this thought:
This is the worst this technology will ever be.
Hayden Williams — Creator of www.eligere.app