DEV Community

Sudharshan K S
Sudharshan K S

Posted on

From Email to Insight: Building a Natural Language SQL & Visualization Bot with FastAPI, Gemini, and Postmark

#fastapi #postmarkchallenge #text2sql #mail2insight

🚀 Project Overview

Imagine sending an email with a question like "Show me the top 5 customers by sales" and getting back a beautiful chart and the answer—automatically. That's what I built: a FastAPI app that receives natural language queries via email (using Postmark webhooks), uses Gemini AI to generate SQL and Plotly code, runs the query on a Northwind SQLite database, and emails back the answer and visualization.

📂 Source Code

Find the full code and deployment instructions on GitHub: sudharshan-ks/dev-postmark-challenge

🛠️ Tech Stack

  • FastAPI for the web server and API
  • Gemini API for natural language to SQL and visualization code
  • SQLite with a synthetic Northwind dataset
  • Plotly for dynamic chart generation
  • Postmark API for receiving and sending emails

🧩 How It Works

  1. Receive Email: Postmark webhook sends incoming email data to the FastAPI endpoint.
  2. NL to SQL: The app extracts the question and asks Gemini to generate a SQL query for the Northwind schema.
  3. Run SQL: The query is executed on a local SQLite database with realistic sample data.
  4. Visualize: Gemini generates Plotly code to visualize the results. The app runs this code and saves a PNG chart.
  5. Reply: The answer and chart are emailed back to the sender using Postmark's API. 
+-------------------+
|   User sends      |
|   Email Query     |
+--------+----------+
         |
         v
+--------+----------+
|  Postmark Webhook |
+--------+----------+
         |
         v
+--------+----------+
|   FastAPI App     |
+--------+----------+
         |
         v
+--------+----------+
|  Gemini AI:       |
|  NL -> SQL/Plotly |
+--------+----------+
         |
         v
+--------+----------+
|  SQLite Query     |
+--------+----------+
         |
         v
+--------+----------+
|  Plotly Chart     |
+--------+----------+
         |
         v
+--------+----------+
|  Email Response   |
+-------------------+
Enter fullscreen mode Exit fullscreen mode

✉️ Example Query & Response

Email Query:
Hey there,
Show me number of orders

Regards
Lark

Response Email:

Here are the number of orders from each country:

  • Canada: 2
  • France: 4
  • Germany: 8
  • Ireland: 2
  • Mexico: 4
  • Portugal: 2
  • Spain: 6
  • Sweden: 4
  • UK: 4
  • Venezuela: 4

Chart:

Number of Orders by Country

🗃️ Data: Synthetic Northwind

  • I created a synthetic Northwind dataset (~20 records per table, 40 orders) with realistic names and relationships.
  • Data is loaded into northwind.db (SQLite) for fast, serverless querying.

🧠 AI-Powered SQL & Visualization

  • Gemini is prompted with the user's question and the Northwind schema to generate safe, relevant SQL.
  • For visualization, Gemini returns Plotly Python code, which is executed in a sandboxed environment to produce a PNG chart.

📦 Deployment

  • The app can be deployed to any Python serverless environment or traditional server.
  • All secrets (API keys, Postmark tokens) are managed via environment variables.

🌐 Exposing Locally with ngrok

If you're running the app locally and want to receive Postmark webhooks, use ngrok to expose your FastAPI server:

  1. Start your FastAPI app (e.g., python run_with_ngrok.py or uvicorn main:app).
  2. Run ngrok to expose your server: ngrok http 8000
  3. Use the generated ngrok URL as your Postmark inbound webhook endpoint.

📝 Key Files

  • main.py: FastAPI app with all workflow logic
  • northwind.db: SQLite database with synthetic data
  • requirements.txt: Python dependencies
  • README.md: Full setup, features, and deployment guide

🏗️ Lessons Learned

  • AI for SQL is powerful: Gemini handled most NL-to-SQL tasks well, but prompts must be carefully crafted for safety and accuracy.
  • Serverless SQLite: Works great for small datasets and demo apps—just bundle the DB file!
  • Email as UI: Postmark webhooks make it easy to turn email into an API trigger.

💡 Try It Yourself

  1. Clone the repo and set up your environment variables (see README.md).
  2. Prepare the SQLite DB with the provided schema and data.
  3. Deploy to your preferred Python serverless platform or server.
  4. Set up a Postmark inbound webhook to your deployed endpoint.
  5. Email your questions and get instant insights!

Questions or feedback? Drop a comment below or reach out on GitHub!

Built with ❤️ using FastAPI, Gemini, SQLite, Plotly, and Postmark.

Top comments (0)