A modular, scalable Go backend for an AI-powered database assistant platform that allows users to manage database projects, execute queries, and interact with databases through an AI chat interface.
- User Authentication: JWT-based authentication with secure password hashing
- Project Management: Create, read, update, and delete database projects (PostgreSQL & MySQL)
- Project Permissions: Fine-grained access control with DDL, Write, Read, and Delete permissions per project
- Dashboard Analytics: View project statistics, query history, and database metadata
- Chat Interface: Project-specific chat history (AI integration ready)
- Secure: Password hashing with bcrypt, JWT tokens, user authorization
- Modular Architecture: Clean separation of concerns with handlers, middleware, and utilities
ai-db-assistant/
โโโ cmd/
โ โโโ api/
โ โโโ main.go # Application entry point
โโโ internal/
โ โโโ config/
โ โ โโโ config.go # Configuration management
โ โโโ database/
โ โ โโโ database.go # Database connection & migrations
โ โโโ handlers/
โ โ โโโ auth.go # Auth handlers (signup, login)
โ โ โโโ project.go # Project CRUD handlers
โ โ โโโ chat.go # Chat message handlers
โ โ โโโ dashboard.go # Dashboard statistics handlers
โ โโโ middleware/
โ โ โโโ middleware.go # Auth, CORS, logging middleware
โ โโโ models/
โ โ โโโ models.go # Database models
โ โโโ router/
โ โโโ router.go # Route definitions
โโโ pkg/
โ โโโ jwt/
โ โ โโโ jwt.go # JWT utilities
โ โโโ logger/
โ โ โโโ logger.go # Structured logging
โ โโโ password/
โ โ โโโ password.go # Password hashing utilities
โ โโโ response/
โ โโโ response.go # HTTP response utilities
โโโ .env.example # Environment variables template
โโโ docker-compose.yml # PostgreSQL Docker setup
โโโ Makefile # Build automation
โโโ go.mod # Go module dependencies
- Language: Go 1.21+
- Framework:
net/httpwith Gorilla Mux - Database: PostgreSQL
- ORM: GORM
- Authentication: JWT tokens
- Password Hashing: bcrypt
- Logging: Structured JSON logging with
log/slog
- Go 1.21 or higher
- PostgreSQL 13+ (or use Docker)
- Make (optional, for using Makefile commands)
git clone https://github.com/ephym-lab/ai-db-assistant.git
cd ai-db-assistantcp .env.example .envEdit .env with your configuration:
PORT=8080
DATABASE_URL=postgresql://postgres:postgres@localhost:5432/ai_db_assistant?sslmode=disable
JWT_SECRET=your-super-secret-jwt-key-change-this-in-production
ENVIRONMENT=developmentmake docker-up
# or
docker-compose up -dmake deps
# or
go mod downloadmake run
# or
go run cmd/api/main.goThe server will start on http://localhost:8080
| Method | Endpoint | Description | Auth Required |
|---|---|---|---|
| POST | /api/auth/signup |
Create new user account | No |
| POST | /api/auth/login |
Login and get JWT token | No |
| Method | Endpoint | Description | Auth Required |
|---|---|---|---|
| POST | /api/projects |
Create a new project | Yes |
| GET | /api/projects |
Get all user projects | Yes |
| GET | /api/projects/:id |
Get specific project | Yes |
| PUT | /api/projects/:id |
Update project | Yes |
| DELETE | /api/projects/:id |
Delete project | Yes |
| Method | Endpoint | Description | Auth Required |
|---|---|---|---|
| GET | /api/projects/:id/summary |
Get project statistics | Yes |
| GET | /api/dashboard |
Get user dashboard stats | Yes |
| Method | Endpoint | Description | Auth Required |
|---|---|---|---|
| POST | /api/chat/:project_id |
Send chat message | Yes |
| GET | /api/chat/:project_id/history |
Get chat history | Yes |
| Method | Endpoint | Description | Auth Required |
|---|---|---|---|
| GET | /health |
Health check endpoint | No |
curl -X POST http://localhost:8080/api/auth/signup \
-H "Content-Type: application/json" \
-d '{
"name": "John Doe",
"email": "john@example.com",
"password": "securepassword123"
}'curl -X POST http://localhost:8080/api/auth/login \
-H "Content-Type: application/json" \
-d '{
"email": "john@example.com",
"password": "securepassword123"
}'curl -X POST http://localhost:8080/api/projects \
-H "Content-Type: application/json" \
-H "Authorization: Bearer YOUR_JWT_TOKEN" \
-d '{
"name": "My Database",
"description": "Production database",
"database_type": "postgresql",
"connection_string": "postgresql://user:pass@localhost:5432/mydb"
}'curl -X PUT http://localhost:8080/api/projects/1 \
-H "Content-Type: application/json" \
-H "Authorization: Bearer YOUR_JWT_TOKEN" \
-d '{
"allow_ddl": false,
"allow_write": false,
"allow_delete": false
}'curl -X GET http://localhost:8080/api/projects \
-H "Authorization: Bearer YOUR_JWT_TOKEN"make testmake buildmake clean- Password Hashing: Uses bcrypt for secure password storage
- JWT Authentication: Token-based authentication with configurable expiry
- Authorization: Middleware ensures users can only access their own resources
- CORS: Configurable CORS middleware for frontend integration
- Input Validation: Request validation to prevent invalid data
id(Primary Key)nameemail(Unique)password(Hashed)created_atupdated_at
id(Primary Key)user_id(Foreign Key)namedescriptiondatabase_type(mysql/postgresql)connection_stringcreated_atupdated_at
id(Primary Key)project_id(Foreign Key, Unique)allow_ddl(Boolean - DDL operations)allow_write(Boolean - INSERT, UPDATE)allow_read(Boolean - SELECT)allow_delete(Boolean - DELETE, TRUNCATE)created_atupdated_at
id(Primary Key)project_id(Foreign Key)querystatus(success/error)resulterrorcreated_at
id(Primary Key)project_id(Foreign Key)role(user/assistant)contentcreated_at
- OpenAI integration for AI chat responses
- Query execution engine for connected databases
- Real-time query monitoring
- Rate limiting
- API documentation with Swagger
- Comprehensive test coverage
- Docker support for full application
- CI/CD pipeline
MIT License
Contributions are welcome! Please feel free to submit a Pull Request.
For questions or support, please open an issue on GitHub.