🧠 CipherSQLStudio – SQL Learning Platform 🚀

A browser-based SQL learning platform where students can practice SQL queries against pre-configured assignments with real-time execution and **AI-powered intelligent hints🤖

---

📋 Table of Contents

• ✨ Features
• 🛠 Technology Stack
• 🏗 Architecture Overview
• 📦 Prerequisites
• 🚀 Installation
• 🔐 Environment Variables
• ▶️ Running the Application
• 📁 Project Structure
• 🔄 Data Flow
• 📘 API Documentation
• 🔐 Security Features
• 🧠 Technology Choices Explanation
• 🖼 Screenshots
• 🤝 Contributing
• 📄 License

---

✨ Features

🔹 Core Functionality

• 📚 Assignment Library – Browse SQL assignments by difficulty (Easy / Medium / Hard)
• 💻 Interactive SQL Editor – Write and execute SQL queries directly in the browser
• 🗂️ Schema Viewer – View table structures and sample data
• ▶️Real-time Query Execution – Execute queries against a PostgreSQL sandbox database
• 🤖 AI-Powered Hints – Intelligent guidance (hints only, no direct solutions)
• 📊 Results Visualization – Scrollable, formatted result tables
• ⚠️Error Handling – Clear syntax and logical error messages

🔐 Security Features

• 🛡 SQL injection prevention through query validation
• 🔒 Read-only database access for students
• ⏱ Query execution timeout (5 seconds)
• 🚦 API rate limiting
• ✅ Whitelist-based SQL keyword filtering

🎨 Design Features

• 📱 Mobile-first responsive design (320px → 1280px+)
• 🌙 Dark theme optimized for reading code
• 👆 Touch-friendly UI elements (minimum 44px)
• ✨ Smooth animations and transitions
• 🧩 BEM methodology for SCSS organization

---

🛠 Technology Stack

🎨 Frontend

Technology	Version	Purpose
⚛ React	18.2.0	Component-based UI
🎨 SCSS	1.70.0	Styling with variables & nesting
🔁 Axios	1.6.5	API communication
🧭 React Router	6.21.0	Client-side routing

🧩 Backend

Technology	Version	Purpose
🟢 Node.js	≥18	Runtime environment
🚀 Express.js	4.18.2	Web framework
🐘 PostgreSQL	≥14	Sandbox DB for query execution
🍃 MongoDB	≥6	Assignment persistence
🧬 Mongoose	8.1.0	MongoDB ODM

🤖 LLM Integration

• 🧠 OpenAI GPT-4 (Recommended)
• 🌐 Google Gemini
• 🧪 Anthropic Claude

🧰 Development Tools

• 📜 Morgan – HTTP request logging
• 🛡 Helmet – Security headers
• 🌍 CORS
• 🚦 Express Rate Limit
• 🔄 Nodemon

---

🏗 Architecture Overview

graph TD A[CLIENT: React + SCSS] -- Axios API Calls --> B[EXPRESS SERVER] B --> C[PostgreSQL: Sandbox Execution] B --> D[MongoDB: User Data & Metadata] B --> E[LLM API: AI Hints/Feedback]

📁 Project Structure

cipher-sql-studio/
│
├── client/ # Frontend React App
│ ├── public/ # Static assets
│ └── src/
│ ├── components/ # UI Components (Editor, Results, Sidebar)
│ ├── services/ # API service layer (Axios)
│ ├── hooks/ # Custom React hooks
│ ├── styles/ # SCSS / CSS modules
│ ├── App.jsx # Root component
│ └── index.js # React entry point
│
├── server/ # Backend Express App
│ ├── src/
│ │ ├── config/ # DB & App configuration
│ │ ├── models/ # MongoDB schemas (Metadata/Assignments)
│ │ ├── controllers/ # Route handlers
│ │ ├── services/ # Business logic & LLM integration
│ │ ├── routes/ # API endpoints
│ │ └── middleware/ # Security & Error handling
│ └── seeds/ # Database seeders
│
├── database/ # Database scripts
│ └── postgres/
│ ├── schema.sql # Sandbox table definitions
│ └── sample_data.sql# Initial sandbox data
│
└── docs/ # Documentation (API, Architecture)

📁 Server Configuration (server/.env)

Create a .env file in the server directory:

Code snippet

SERVER CONFIG

• PORT=5000
• NODE_ENV=development

DATABASES

• POSTGRES_HOST=localhost
• POSTGRES_USER=postgres
• POSTGRES_PASSWORD=your_password
• POSTGRES_DB=cipher_sql_sandbox -MONGODB_URI=mongodb://localhost:27017/cipher_sql_studio

AI CONFIG

• LLM_PROVIDER=openai
• OPENAI_API_KEY=sk-your-key-here

SECURITY

-MAX_QUERY_EXECUTION_TIME=5000 -MAX_RESULT_ROWS=1000

▶️ Running the Application

🧪 Development Mode

To get the environment up and running, open two separate terminal windows:

🔹 Terminal 1: Backend

cd server
npm run dev

🌐 Access Points

Component	URL
🎨 Frontend UI	http://localhost:3000
🚀 Backend API	http://localhost:5000/api
❤️ Health Check	http://localhost:5000/api/health

📄 Notes

• 🛡️ Security: The PostgreSQL sandbox is designed for read-only or simulated environments. Ensure user permissions are strictly restricted in production to prevent unauthorized data modification.
• 🤖 AI Hints: Execution and rate limits apply based on your chosen LLM provider's subscription plan.
• 🛠️ Troubleshooting: If you encounter any issues, please check the logs in the server terminal for detailed error messages.

---

✨ Happy Coding! You are all set to start building with CipherSQLStudio.