boomika-nallasivam/nl2sql-offline

Purpose and entry points

QueryGenie is a full-stack natural-language-to-SQL application. Users authenticate, upload Excel datasets, inspect schema, ask questions, review generated SQL/results, and view history; an offline admin area manages users, queries, and logs. Backend startup is Spring Boot at querygenie-backend/src/main/java/com/querygenie/QueryGenieApplication.java; the browser SPA starts at querygenie-frontend/src/main.jsx and composes routes/UI in querygenie-frontend/src/App.jsx.

Client application boundary

The frontend is a Vite/React application with authentication and selected-dataset state centralized in querygenie-frontend/src/context/AuthContext.jsx and querygenie-frontend/src/context/DatasetContext.jsx. Dashboard, upload, history, and schema screens call the backend through API modules such as querygenie-frontend/src/api/queryApi.js, querygenie-frontend/src/api/uploadApi.js, and querygenie-frontend/src/api/historyApi.js. Route guards distinguish standard users and admins in querygenie-frontend/src/components/ProtectedRoute.jsx and querygenie-frontend/src/components/ProtectedAdminRoute.jsx.

HTTP/API orchestration

Spring MVC controllers expose the service boundary: authentication, query execution, uploads, schema, and history are represented by querygenie-backend/src/main/java/com/querygenie/controller/AuthController.java, querygenie-backend/src/main/java/com/querygenie/controller/QueryController.java, and querygenie-backend/src/main/java/com/querygenie/controller/ExcelController.java. Admin operations are separate in querygenie-backend/src/main/java/com/querygenie/controller/AdminController.java. DTOs provide request/response contracts; global API error translation is centralized in querygenie-backend/src/main/java/com/querygenie/exception/GlobalExceptionHandler.java.

Identity and authorization

AuthService owns signup/login behavior, while CurrentUserResolver establishes the authenticated caller for downstream operations: querygenie-backend/src/main/java/com/querygenie/service/AuthService.java, querygenie-backend/src/main/java/com/querygenie/service/CurrentUserResolver.java. JWT issuance/validation and password handling are isolated in querygenie-backend/src/main/java/com/querygenie/util/JwtUtil.java and querygenie-backend/src/main/java/com/querygenie/util/PasswordUtil.java. User records persist through querygenie-backend/src/main/java/com/querygenie/repository/UserRepository.java; admin bootstrap/offline administration is handled separately.

Dataset ingestion and schema boundary

Excel upload is transformed by querygenie-backend/src/main/java/com/querygenie/service/ExcelService.java, with uploaded-file metadata stored via querygenie-backend/src/main/java/com/querygenie/repository/UploadedFileRepository.java. The target queryable database is supplied through querygenie-backend/src/main/java/com/querygenie/config/TargetDataSourceProvider.java; SQLite connection setup is in querygenie-backend/src/main/java/com/querygenie/config/SqliteConnectionFactory.java. Schema discovery uses querygenie-backend/src/main/java/com/querygenie/schema/SchemaMetadataProvider.java and may persist metadata in querygenie-backend/src/main/java/com/querygenie/repository/SchemaCacheRepository.java.

Natural-language query pipeline

A question enters QueryService, which builds an LLM prompt, requests SQL from Ollama, validates it, and executes against the selected target datasource: querygenie-backend/src/main/java/com/querygenie/service/QueryService.java, querygenie-backend/src/main/java/com/querygenie/prompt/PromptBuilder.java, querygenie-backend/src/main/java/com/querygenie/llm/OllamaService.java. querygenie-backend/src/main/java/com/querygenie/validator/SqlValidator.java is the critical trust boundary between model-produced SQL and database execution. Ollama endpoint/model settings are represented by querygenie-backend/src/main/java/com/querygenie/config/OllamaProperties.java.

Persistence, history, and runtime infrastructure

The backend is Maven/Spring Boot (querygenie-backend/pom.xml) configured through querygenie-backend/src/main/resources/application.properties, with SQLite database files checked into the repository. Query activity is recorded and returned through querygenie-backend/src/main/java/com/querygenie/service/HistoryService.java and querygenie-backend/src/main/java/com/querygenie/repository/QueryHistoryRepository.java. Material external/runtime dependencies are the browser client, Spring Boot JVM service, local SQLite target/application storage, and an Ollama generation service; CORS is configured in querygenie-backend/src/main/java/com/querygenie/config/CorsConfig.java.