Skip to content

About

No description, website, or topics provided.

Resources

Stars

0 stars

Watchers

0 watching

Forks

ย 
ย 

Latest commit

ย 

History

23 Commits

Folders and files

NameName
Last commit message
Last commit date
ย 
ย 
ย 
ย 
ย 
ย 
ย 
ย 
ย 
ย 
ย 
ย 
ย 
ย 
ย 
ย 
ย 
ย 
ย 
ย 
ย 
ย 
ย 
ย 
ย 
ย 
ย 
ย 
ย 
ย 

Repository files navigation

AI Database Assistant Backend

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.

๐Ÿš€ Features

  • 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

๐Ÿ“ Project Structure

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

๐Ÿ› ๏ธ Tech Stack

  • Language: Go 1.21+
  • Framework: net/http with Gorilla Mux
  • Database: PostgreSQL
  • ORM: GORM
  • Authentication: JWT tokens
  • Password Hashing: bcrypt
  • Logging: Structured JSON logging with log/slog

๐Ÿ“‹ Prerequisites

  • Go 1.21 or higher
  • PostgreSQL 13+ (or use Docker)
  • Make (optional, for using Makefile commands)

๐Ÿš€ Quick Start

1. Clone the repository

git clone https://github.com/ephym-lab/ai-db-assistant.git
cd ai-db-assistant

2. Set up environment variables

cp .env.example .env

Edit .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=development

3. Start PostgreSQL (using Docker)

make docker-up
# or
docker-compose up -d

4. Install dependencies

make deps
# or
go mod download

5. Run the application

make run
# or
go run cmd/api/main.go

The server will start on http://localhost:8080

๐Ÿ“ก API Endpoints

Authentication

Method Endpoint Description Auth Required
POST /api/auth/signup Create new user account No
POST /api/auth/login Login and get JWT token No

Projects

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

Dashboard

Method Endpoint Description Auth Required
GET /api/projects/:id/summary Get project statistics Yes
GET /api/dashboard Get user dashboard stats Yes

Chat

Method Endpoint Description Auth Required
POST /api/chat/:project_id Send chat message Yes
GET /api/chat/:project_id/history Get chat history Yes

Health Check

Method Endpoint Description Auth Required
GET /health Health check endpoint No

๐Ÿ“ API Usage Examples

Signup

curl -X POST http://localhost:8080/api/auth/signup \
  -H "Content-Type: application/json" \
  -d '{
    "name": "John Doe",
    "email": "john@example.com",
    "password": "securepassword123"
  }'

Login

curl -X POST http://localhost:8080/api/auth/login \
  -H "Content-Type: application/json" \
  -d '{
    "email": "john@example.com",
    "password": "securepassword123"
  }'

Create Project

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"
  }'

Update Project Permissions

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
  }'

Get All Projects

curl -X GET http://localhost:8080/api/projects \
  -H "Authorization: Bearer YOUR_JWT_TOKEN"

๐Ÿ—๏ธ Development

Run tests

make test

Build the application

make build

Clean build artifacts

make clean

๐Ÿ”’ Security Features

  • 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

๐Ÿ“Š Database Schema

Users Table

  • id (Primary Key)
  • name
  • email (Unique)
  • password (Hashed)
  • created_at
  • updated_at

Projects Table

  • id (Primary Key)
  • user_id (Foreign Key)
  • name
  • description
  • database_type (mysql/postgresql)
  • connection_string
  • created_at
  • updated_at

Permissions Table

  • 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_at
  • updated_at

Queries Table

  • id (Primary Key)
  • project_id (Foreign Key)
  • query
  • status (success/error)
  • result
  • error
  • created_at

Messages Table

  • id (Primary Key)
  • project_id (Foreign Key)
  • role (user/assistant)
  • content
  • created_at

๐Ÿ”ฎ Future Enhancements

  • 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

๐Ÿ“„ License

MIT License

๐Ÿค Contributing

Contributions are welcome! Please feel free to submit a Pull Request.

๐Ÿ“ง Contact

For questions or support, please open an issue on GitHub.

About

No description, website, or topics provided.

Resources

Stars

0 stars

Watchers

0 watching

Forks

Releases

Packages

Contributors

Languages