Serving Up Smart Data: How Our Restaurant of Mistaken Orders Got a Tech Makeover

Hey there, food lovers and tech enthusiasts! Today, we're dishing out a story that's as satisfying as our famous "Oops, I Dropped the Spaghetti" special. Gather 'round as we spill the beans on how our quirky little Restaurant of Mistaken Orders got a high-tech boost that would make even the most seasoned Silicon Valley geeks say, "I'll have what they're having!"

Picture this: You're sitting at one of our wobbly tables (we keep them that way on purpose, promise!), and you're curious about our menu's history. Wouldn't it be neat if our waiter could instantly tell you how many times the "Upside-Down Pizza" has been ordered in the last month? Well, now they can, thanks to a little bit of AI magic and some fancy database work!

We've cooked up a system that lets our staff query our restaurant database faster than you can say "accidental anchovy appetizer." Here's the secret sauce:

1. We use a super-smart AI called ChatGPT to translate regular human questions into database-speak (that's SQL for you tech-savvy folks).
2. We've got a special recipe (aka a prompt) that we feed to ChatGPT. This recipe includes our menu items, table layouts, and even our staff's favorite mistakes. It's like giving the AI a crash course in "Restaurant of Mistaken Orders 101."
3. Once ChatGPT whips up the SQL query, we double-check it faster than our chefs check for eggshells in the "Surprise Omelet." We do this both in our snazzy React app and in our Azure functions (that's cloud stuff, for those who prefer their tech terms sunny-side up).

Now, you might be wondering, "Why all this fuss over SQL?" Well, let me tell you, SQL might be as old as our "Vintage Veggie Surprise," but it's still the go-to language for data processing. In fact, some smart folks in lab coats (data scientists, they call themselves) spend 40-80% of their time crafting SQL queries by hand! That's like kneading dough for hours when you could use a mixer!

But here's where it gets really exciting: by using AI to translate regular language into SQL, we're not just making our waitstaff's lives easier. We're potentially putting the power of data analysis into everyone's hands! It's like we're creating the TikTok of the data world – suddenly, everyone can be a creator (or in our case, a data chef)!

Now, you might be thinking, "Isn't letting AI mess with your database like letting a cat loose in the kitchen?" Fear not! We've got more safety measures than a bulk order of hair nets:

1. ChatGPT is trained to keep the queries as safe as our "Definitely Not Ghost Pepper" sauce (spoiler: it's mild).
2. Our React app gives the query a once-over, making sure it's not trying any funny business.
3. Our Azure functions give it another check, just to be extra sure. It's like having three health inspectors instead of one!

And here's the cherry on top: our database is read-only. That means the AI can look at the menu all day long, but it can't add "Bug Surprise" as the dish of the day.

But wait, there's more! We've learned from the tech gurus that fine-tuning and prompt engineering are key ingredients in this AI soup. It's not just about throwing information at the AI and hoping for the best. We've carefully seasoned our prompts with just the right amount of:

• Schema information (like a map of our menu)
• Constraints (no mixing desserts with appetizers!)
• Query examples (showing how we've mixed up orders in the past)
• Semantic details (explaining why we call it the "Upside-Down Pizza")

Schema information example: ${databaseSchema}

export const databaseSchema = `

Table: adaptation_regions

Columns:

- region (nvarchar)

- market (nvarchar)

- id (int)

Keys:

- PRIMARY KEY (id)

Table: approver_groups

Columns:

- id (int)

- identifier (varchar)

- taxonomy_name (nvarchar)

- taxonomy_label (nvarchar)

- created_on (datetime2)

- modified_on (datetime2)

Keys:

- PRIMARY KEY (id) `;

Query example

export const getSqlCountPrompt = (input: string, messagesForAI: any[], limitTheAmountOfRowsTo: number = 5): string => {

const instruction = `

Generate a safe Azure SQL-compatible SELECT statement to retrieve detailed information about a specific item based on this request: "${input}"

Requirements:

- Write an optimized SQL SELECT query to count specific records as per the user request "${input}".

- Use explicit table aliases and prefix column names to avoid ambiguity.

- Use 'SELECT TOP (${limitTheAmountOfRowsTo})' along with an 'ORDER BY' clause to limit the number of rows returned.

- Ensure all columns in the ORDER BY clause are either in the SELECT clause or are part of an aggregate function.

- If GROUP BY is used, ensure all non-aggregated columns in the SELECT clause are included in the GROUP BY clause.

- For ratio comparisons like width/height, use appropriate WHERE clause conditions with explicit data type casting.

- For boolean values, use proper SQL syntax for true/false checks.

- Ensure that facet values are correctly associated with their corresponding columns, not interpreted as different data types like extensions.

- Only output a SELECT statement. Do not include any other SQL commands, comments, or formatting.

- Always include approved assets only in the select statement.

Example Queries:

- Example 1: SELECT COUNT(*) AS TotalAdaptations FROM assets WHERE creation_type = 'Adaptation';

- Example 2: SELECT COUNT(*) AS TotalMasters FROM assets WHERE creation_type = 'Master';

- Example 3: SELECT COUNT(t1.columnName) AS CountColumnName FROM TableName AS t1 JOIN AnotherTable AS t2 ON t1.commonColumn = t2.commonColumn;

- Example 4: SELECT COUNT(*) AS AssetCount FROM assets AS psa WHERE psa.creation_type = 'master' AND CAST(psa.width AS float) / CAST(psa.height AS float) = 16.0 / 9 AND psa.universal_retail_use = 1;

- Example 5: SELECT asset_id, COUNT(*) AS DownloadCount FROM asset_downloads GROUP BY asset_id ORDER BY DownloadCount DESC;

Note:

- Construct the SQL query using safe practices. Do not concatenate user inputs directly into the query. Use parameterized queries or safely escaped user inputs.

- Replace TableName, AnotherTable, columnName, and commonColumn with actual table and column names based on the database schema.

- Database Schema TABLE_NAME,COLUMNS,KEYS:

- ${databaseSchema}

`;

return `${instruction}`;

}

With all these techniques, we're seeing accuracy rates that would make even the most precise chef jealous. It's like we've solved the "how to make the perfect soufflé" of the data world!

So, next time you're at the Restaurant of Mistaken Orders and your waiter seems to know everything about our mixed-up menu history, you'll know it's not just stellar memory – it's our AI-powered secret weapon!

Remember, folks: this is just a taste of what's possible. We're not saying every restaurant needs to go full robot (though we are working on a "Robo-Waiter Who Always Gets Your Order Wrong" for laughs). But a sprinkle of tech here and there? That's the perfect recipe for a dining experience that's as entertaining as it is delicious.

Now, if you'll excuse me, I need to go ask our AI how many times we've accidentally served the napkins instead of the crepes. Bon appétit and happy querying!