Visual Database Engineer

A comprehensive, AI-powered visual database design tool — similar to dbdiagram.io — that enables developers to design, visualize, and generate production-ready backend code for 9 frameworks across 4 SQL dialects.

1. OST — Overview & Synopsis

1.1 Problem Statement

Database design is a foundational step in software development, yet existing tools often present significant friction:

Problem	Impact
Manual SQL DDL writing is error-prone	Schema bugs discovered only at runtime
No visual designer for quick prototyping	Developers waste time on boilerplate
Framework-specific code requires deep knowledge	Switching frameworks means rewriting everything
No unified import/export pipeline	Schema silos across teams and tools
AI tools generate incomplete or non-functional schemas	Developers still need to manually refine output

1.2 Proposed Solution

Visual Database Engineer is a browser-based, AI-powered database design tool that solves these problems by providing:

Drag-and-drop canvas — Design tables and relationships visually
Multi-framework code generation — One schema → 9 backend frameworks
Multi-dialect SQL — Generate MySQL, PostgreSQL, SQLite, or MSSQL DDL
AI-assisted design — Describe your app in natural language, get a complete schema
Import/Export ecosystem — SQL, JSON, ZIP, PNG, PDF support
Production-ready output — Validators, Swagger docs, seed data included

1.3 Objectives

#	Objective	Status
1	Visual table/column editing with React Flow canvas	✅ Complete
2	Relationship drawing (1:1, 1:N, M:N) with FK generation	✅ Complete
3	Code generation for 9 backend frameworks	✅ Complete
4	SQL generation for 4 dialects	✅ Complete
5	AI-powered schema generation via Groq API	✅ Complete
6	Import from SQL (CREATE TABLE + ALTER TABLE) and JSON	✅ Complete
7	Export to SQL, JSON, ZIP, PNG, PDF	✅ Complete
8	Undo/redo with full history	✅ Complete
9	Auto-save to localStorage	✅ Complete
10	Dark/light theme toggle with glassmorphism UI	✅ Complete

1.4 Scope

In Scope:

Visual database schema design
Code generation for 9 backend frameworks
SQL generation for 4 dialects
AI-assisted schema generation
Import/Export (SQL, JSON, ZIP, PNG, PDF)
Validation schemas, Swagger docs, seed data
Responsive glassmorphism UI

Out of Scope:

Direct database connection/query execution
Database migration management
User authentication/multi-user collaboration
Database performance optimization

2. FST — Feasibility Study & Technical

2.1 Technical Feasibility

Aspect	Assessment	Evidence
Frontend Framework	✅ Feasible	React 18 + TypeScript — mature, well-documented ecosystem
Visual Canvas	✅ Feasible	React Flow (@xyflow/react) — battle-tested library for node-based UIs
State Management	✅ Feasible	Zustand — lightweight, no boilerplate, built-in persistence
AI Integration	✅ Feasible	Groq API — fast inference, generous free tier
Code Generation	✅ Feasible	Template-based engine — deterministic, testable output
Build Tool	✅ Feasible	Vite 5 — fast HMR, native ESM, excellent DX

2.2 Operational Feasibility

Concern	Mitigation
Browser compatibility	Targets modern browsers (Chrome, Firefox, Edge, Safari) — no IE11
API key security	Groq key stored server-side in Express proxy; never exposed to frontend
Data persistence	Auto-save to localStorage; export to JSON/ZIP for backup
Performance	Canvas virtualization via React Flow; Zustand minimal re-renders

2.3 Economic Feasibility

Item	Cost
Groq API	Free tier (sufficient for development/personal use)
Hosting (GitHub Pages/Vercel)	Free for public repos
Development tools	All open-source (Vite, React, TypeScript, Tailwind)
Total	$0

2.4 Legal Feasibility

All dependencies are MIT/Apache 2.0 licensed
No proprietary data stored or transmitted
Groq API usage subject to their terms of service

3. SST — System Specifications & Technical

3.1 Hardware Requirements

Component	Minimum	Recommended
CPU	Dual-core 1.5 GHz	Quad-core 2.5 GHz+
RAM	4 GB	8 GB+
Storage	500 MB free	1 GB+ free
Display	1280×720	1920×1080+

3.2 Software Requirements

Component	Version
Node.js	18.x+ (located at `D:\Nodejs\node.exe`)
npm	9.x+
Browser	Chrome 90+, Firefox 88+, Edge 90+, Safari 14+
OS	Windows 10/11 (primary), macOS, Linux

3.3 Development Stack

┌─────────────────────────────────────────────────┐
│                  Frontend (Client)                │
│  React 18 + TypeScript + Vite 5 + Tailwind CSS  │
│  Zustand (State) + React Flow (Canvas)           │
│  glassmorphism UI + Dark/Light Theme             │
└──────────────────────┬──────────────────────────┘
                       │ HTTP (localhost:3001)
┌──────────────────────▼──────────────────────────┐
│              Backend Proxy (server.js)            │
│  Express.js + dotenv + CORS                      │
│  Groq API Key stored server-side                 │
└──────────────────────┬──────────────────────────┘
                       │ HTTPS
┌──────────────────────▼──────────────────────────┐
│                   Groq Cloud API                  │
│  llama-3.3-70b-versatile model                   │
│  Schema generation + Chat completions            │
└─────────────────────────────────────────────────┘

3.4 API Endpoints

Method	Endpoint	Description
POST	`/api/schema`	Generate database schema from natural language
POST	`/api/chat`	General AI chat for database assistance
GET	`/health`	Health check endpoint

3.5 Data Models

Table

interface Table {
  id: string;
  name: string;
  color: string;
  columns: Column[];
  position: { x: number; y: number };
}

Column

interface Column {
  id: string;
  name: string;
  type: string;
  isPrimaryKey: boolean;
  isNullable: boolean;
  isUnique: boolean;
  isAutoIncrement: boolean;
  defaultValue?: string;
  foreignKey?: ForeignKey;
  indexes: Index[];
}

Relationship

interface Relationship {
  id: string;
  name: string;
  sourceTableId: string;
  sourceColumnId: string;
  targetTableId: string;
  targetColumnId: string;
  type: 'one-to-one' | 'one-to-many' | 'many-to-many';
  onUpdate: OnUpdateAction;
  onDelete: OnDeleteAction;
}

4. LST — Literature Survey & Technical

4.1 Comparative Analysis

Feature	This Tool	dbdiagram.io	DrawSQL	SchemaSpy
Visual Canvas	✅	✅	✅	❌
AI-Powered Design	✅ (Groq)	❌	❌	❌
Multi-Framework Code Gen	9 frameworks	5 frameworks	6 frameworks	❌
SQL Dialect Support	4 dialects	3 dialects	4 dialects	❌
Validation Schema Gen	✅	❌	❌	❌
Swagger/OpenAPI Gen	✅	❌	❌	❌
Seed Data Gen	✅	❌	❌	❌
Import (SQL + JSON)	✅	❌	✅ (SQL only)	❌
Export (ZIP/PNG/PDF)	✅	✅ (PNG)	❌	❌
Undo/Redo	✅	✅	❌	❌
Self-Hostable	✅	❌ (SaaS)	❌	✅
Dark/Light Theme	✅	❌ (Dark only)	❌	❌
Open Source	✅	❌	❌	✅
Offline Capable	Partial	❌	❌	✅

4.2 Technology Justification

Technology	Choice	Rationale
UI Framework	React 18	Component-based architecture, vast ecosystem, hooks for state management
Language	TypeScript	Type safety catches schema/design errors at compile time
Build Tool	Vite 5	10-100x faster than Webpack, native ESM, instant HMR
Styling	Tailwind CSS	Utility-first, rapid prototyping, consistent design system
State	Zustand	No providers/boilerplate, TypeScript-first, middleware support
Canvas	React Flow	Purpose-built for node-based UIs, handles edges/port/zoom/pan
AI	Groq API	Fastest inference speeds, generous free tier, OpenAI-compatible
Backend	Express.js	Minimal proxy for API key security, no complex setup

4.3 Design Patterns

Pattern	Application
Singleton	Zustand store (single source of truth)
Observer	Zustand subscriptions for auto-save
Strategy	Code generator templates per framework
Factory	Modal creation based on action type
Command	Undo/redo via state history stack
Proxy	Express server proxying Groq API calls

5. System Architecture

5.1 High-Level Architecture

┌─────────────────────────────────────────────────────────────┐
│                     BROWSER (Client)                         │
│                                                             │
│  ┌──────────┐  ┌──────────┐  ┌──────────┐  ┌──────────┐   │
│  │  Header   │  │Left      │  │  Canvas  │  │  Right   │   │
│  │  (Nav)   │  │Sidebar   │  │ (React   │  │  Sidebar │   │
│  │          │  │ (Tables) │  │  Flow)   │  │ (Props)  │   │
│  └──────────┘  └──────────┘  └──────────┘  └──────────┘   │
│                                                             │
│  ┌──────────────────────────────────────────────────────┐   │
│  │              Bottom Panel (Code Viewer)               │   │
│  │  SQL │ Prisma │ Sequelize │ NestJS │ Laravel │ ...   │   │
│  └──────────────────────────────────────────────────────┘   │
│                                                             │
│  ┌──────────────┐  ┌──────────────┐  ┌──────────────────┐  │
│  │  AI Assistant │  │   Modals     │  │  Zustand Store   │  │
│  │  (Groq Chat)  │  │  (CRUD/Edit) │  │  (State + Undo)  │  │
│  └──────────────┘  └──────────────┘  └──────────────────┘  │
└─────────────────────────────┬───────────────────────────────┘
                              │ fetch (localhost:3001)
┌─────────────────────────────▼───────────────────────────────┐
│                   EXPRESS PROXY (server.js)                   │
│  /api/schema  →  Groq API  →  Schema JSON                   │
│  /api/chat    →  Groq API  →  Chat Response                  │
│  API Key stored in .env (never in browser)                   │
└─────────────────────────────┬───────────────────────────────┘
                              │ HTTPS
┌─────────────────────────────▼───────────────────────────────┐
│                    GROQ CLOUD API                             │
│  Model: llama-3.3-70b-versatile                             │
│  Endpoints: chat/completions                                 │
└─────────────────────────────────────────────────────────────┘

5.2 Component Flow

User Action → Zustand Store Update → React Re-render → UI Update
     │                                          │
     ├── Create Table ──────────────────────────┤
     ├── Add Column ────────────────────────────┤
     ├── Draw Relationship ─────────────────────┤
     ├── AI Generate ────────→ Groq API ────────┤
     ├── Import SQL/JSON ───────────────────────┤
     ├── Export ────────→ Code Generator ────────┤
     └── Undo/Redo ────→ History Stack ──────────┘

6. Features & Capabilities

6.1 Visual Canvas

Feature	Description
Drag & Drop	Position tables freely on the canvas
Zoom & Pan	Mouse wheel zoom, click-drag pan
Mini Map	Overview of entire schema in corner
Connection Drawing	Click port → drag to target port to create relationships
Multi-Select	Shift+click to select multiple tables
Responsive	Canvas adapts to window resize

6.2 Table Management

Feature	Description
Create Table	Name, color, initial columns
Edit Table	Rename, change color
Delete Table	Cascade removes relationships
Duplicate Table	Copy with all columns and settings
Position Persistence	Table positions saved to state

6.3 Column Management

Feature	Description
Data Types	20+ SQL types (INT, VARCHAR, TEXT, BOOLEAN, UUID, JSON, etc.)
Constraints	PRIMARY KEY, NOT NULL, UNIQUE, AUTO_INCREMENT
Foreign Keys	Link to any table/column with ON UPDATE/DELETE actions
Default Values	Custom default expressions
Indexes	Single-column and composite indexes
Cascade Actions	CASCADE, RESTRICT, SET NULL, SET DEFAULT, NO ACTION

6.4 Relationship Management

Feature	Description
One-to-One	1:1 foreign key relationship
One-to-Many	1:N foreign key on child table
Many-to-Many	M:N with junction table generation
ON UPDATE	CASCADE, RESTRICT, SET NULL, SET DEFAULT, NO ACTION
ON DELETE	CASCADE, RESTRICT, SET NULL, SET DEFAULT, NO ACTION
Auto FK Column	Creates foreign key column automatically

6.5 Code Generation

Framework	Output
Prisma	`schema.prisma` + Zod validators + Swagger JSON + Seed data
Sequelize	Models + Validators + Routes + `package.json`
NestJS	Entity + DTO (class-validator) + Swagger decorators + `main.ts`
Laravel	Migration + Model + Form Request (Store/Update) + Routes
Django	`models.py` + `serializers.py` + `views.py` + `urls.py`
Flask	SQLAlchemy models + Marshmallow schemas + Routes
Spring Boot	JPA Entity + DTO (Bean Validation) + Repository + Controller
ASP.NET Core	Entity + DTO (Data Annotations) + DbContext + Controller
Go/GORM	Struct + Validation tags + Repository + Service

6.6 SQL Generation

Dialect	Features
MySQL	AUTO_INCREMENT, ENGINE=InnoDB, UNSIGNED, ENUM, SET
PostgreSQL	SERIAL, BIGSERIAL, UUID, JSONB, ARRAY, GENERATED ALWAYS
SQLite	AUTOINCREMENT, STRICT mode, typed columns
MSSQL	IDENTITY, NVARCHAR, DATETIME2, DATETIMEOFFSET

6.7 Import/Export

Format	Import	Export
SQL	✅ CREATE TABLE + ALTER TABLE FK	✅ Per-dialect DDL
JSON	✅ Full schema	✅ Complete schema
ZIP	✅ Multiple files	✅ All code files bundled
PNG	❌	✅ Canvas screenshot
PDF	❌	✅ Schema report

7. Technology Stack

7.1 Frontend Dependencies

{
  "react": "^18.3.1",
  "react-dom": "^18.3.1",
  "@xyflow/react": "^12.6.0",
  "zustand": "^5.0.0",
  "html-to-image": "^1.11.11",
  "jspdf": "^2.5.2",
  "file-saver": "^2.0.5",
  "jszip": "^3.10.1"
}

7.2 Dev Dependencies

{
  "@types/react": "^18.3.18",
  "@types/react-dom": "^18.3.5",
  "@vitejs/plugin-react": "^4.3.4",
  "autoprefixer": "^10.4.20",
  "postcss": "^8.4.49",
  "tailwindcss": "^3.4.17",
  "typescript": "~5.6.2",
  "vite": "^6.0.5"
}

7.3 Backend Dependencies

{
  "express": "^4.21.2",
  "cors": "^2.8.5",
  "dotenv": "^16.4.7"
}

8. Project Structure

Visual-Database-Engineer/
├── public/
│   └── vite.svg                    # Favicon
├── src/
│   ├── components/
│   │   ├── Canvas.tsx              # React Flow canvas wrapper
│   │   ├── ErrorBoundary.tsx       # React error boundary
│   │   ├── edges/
│   │   │   └── RelationshipEdge.tsx # Custom relationship edges
│   │   ├── layout/
│   │   │   ├── Header.tsx          # Top navigation bar
│   │   │   ├── LeftSidebar.tsx     # Table list panel
│   │   │   ├── RightSidebar.tsx    # Properties panel
│   │   │   └── BottomPanel.tsx     # Code viewer panel
│   │   ├── modals/
│   │   │   ├── CreateTableModal.tsx
│   │   │   ├── EditTableModal.tsx
│   │   │   ├── CreateColumnModal.tsx
│   │   │   ├── EditColumnModal.tsx
│   │   │   ├── CreateRelationshipModal.tsx
│   │   │   ├── EditRelationshipModal.tsx
│   │   │   ├── ExportModal.tsx
│   │   │   ├── ImportModal.tsx
│   │   │   └── ProjectSettingsModal.tsx
│   │   ├── nodes/
│   │   │   └── TableNode.tsx       # Custom table node
│   │   └── panels/
│   │       └── AIAssistant.tsx     # AI chat panel
│   ├── services/
│   │   ├── aiService.ts            # Groq API (via proxy)
│   │   └── codeGenerator.ts        # Code generation engine
│   ├── stores/
│   │   └── index.ts                # Zustand store
│   ├── types/
│   │   └── index.ts                # TypeScript interfaces
│   ├── utils/
│   │   └── index.ts                # Utility functions
│   ├── App.tsx                     # Main application
│   ├── index.css                   # Global styles + glassmorphism
│   └── main.tsx                    # Entry point
├── server.js                       # Express proxy (AI backend)
├── server.ts                       # TypeScript version (unused)
├── start.bat                       # One-click server launcher
├── .env                            # Groq API key (gitignored)
├── .gitignore
├── index.html                      # HTML entry point
├── package.json
├── postcss.config.js
├── tailwind.config.js
├── tsconfig.json
├── tsconfig.node.json
└── vite.config.ts

9. Installation & Setup

9.1 Prerequisites

Node.js 18.x or higher (verify: node --version)
npm 9.x or higher (verify: npm --version)
Git (for cloning)

9.2 Quick Start

# 1. Clone the repository
git clone https://github.com/M-Affan01/Visual-Database-Engineer.git
cd Visual-Database-Engineer

# 2. Install dependencies
npm install

# 3. Configure environment
# Create .env file with your Groq API key:
echo "GROQ_API_KEY=your_groq_api_key_here" > .env

# 4. Start the application
node server.js & node node_modules/vite/bin/vite.js --host 127.0.0.1 --port 3000

9.3 Getting a Groq API Key

Visit console.groq.com
Sign up for a free account
Navigate to API Keys → Create API Key
Copy the key (starts with gsk_)
Add to .env file: GROQ_API_KEY=gsk_your_key_here

9.4 One-Click Start (Windows)

# Double-click start.bat
# Starts both backend (port 3001) and frontend (port 3000)

9.5 Manual Start

# Terminal 1: Backend proxy
node server.js

# Terminal 2: Frontend dev server
node node_modules/vite/bin/vite.js --host 127.0.0.1 --port 3000

9.6 Access the Application

Frontend: http://127.0.0.1:3000
Backend API: http://127.0.0.1:3001/health

10. Usage Guide

10.1 Creating Your First Schema

Add a Table: Click "+ Table" in the left sidebar or header
Add Columns: Click "Add Column" in the table node or right sidebar
Draw Relationships: Drag from one table's port to another
Generate Code: View auto-generated code in the bottom panel
Export: Click "Export" → choose format → download

10.2 Using the AI Assistant

Click the AI button in the header

Describe your application:

"Create a blog platform with users, posts, comments, and categories.
Users can have many posts. Posts can have many comments. 
Categories have many posts. Each post belongs to one category."

The AI generates a complete schema with tables, columns, and relationships
Review and modify as needed

10.3 Importing an Existing Schema

From SQL:

CREATE TABLE users (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(255) UNIQUE NOT NULL
);

CREATE TABLE posts (
  id INT PRIMARY KEY AUTO_INCREMENT,
  title VARCHAR(200) NOT NULL,
  user_id INT NOT NULL,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

Click Import → SQL
Paste your SQL
Click Import
Tables and relationships are created automatically

From JSON:

{
  "tables": [
    {
      "name": "users",
      "columns": [
        { "name": "id", "type": "INT", "isPrimaryKey": true }
      ]
    }
  ]
}

10.4 Keyboard Shortcuts

Shortcut	Action
`Ctrl+Z`	Undo
`Ctrl+Y` / `Ctrl+Shift+Z`	Redo
`Ctrl+S`	Save to localStorage
`Delete` / `Backspace`	Delete selected
`Ctrl+E`	Toggle export modal
`Ctrl+I`	Toggle import modal
`Ctrl+Shift+A`	Toggle AI assistant
`Escape`	Close modal

11. Backend Frameworks Supported

11.1 Prisma (Node.js/TypeScript)

Output Files:

schema.prisma — Full database schema
src/validators/*.ts — Zod validation schemas
docs/swagger.json — OpenAPI 3.0 specification
prisma/seed.ts — Seed data script
package.json — Dependencies (zod, swagger-ui-express)

Example Output:

// schema.prisma
model User {
  id        Int      @id @default(autoincrement())
  email     String   @unique
  name      String?
  posts     Post[]
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
}

model Post {
  id        Int      @id @default(autoincrement())
  title     String
  content   String?
  authorId  Int
  author    User     @relation(fields: [authorId], references: [id], onDelete: Cascade)
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
}

11.2 Sequelize (Node.js)

Output Files:

models/*.js — Model definitions
validators/*.js — Joi validation
routes/*.js — REST routes with validation
package.json — Dependencies

11.3 NestJS (TypeScript)

Output Files:

entities/*.entity.ts — TypeORM entities
dto/*.dto.ts — DTOs with class-validator
main.ts — Swagger setup
package.json — Dependencies

11.4 Laravel (PHP)

Output Files:

database/migrations/*.php — Migration files
app/Models/*.php — Eloquent models
app/Http/Requests/*.php — Form Request validation
routes/api.php — API routes

11.5 Django (Python)

Output Files:

models.py — Django ORM models
serializers.py — DRF serializers
views.py — ViewSets
urls.py — URL routing

11.6 Flask (Python)

Output Files:

app/models.py — SQLAlchemy models
app/schemas.py — Marshmallow schemas
app/routes.py — Flask blueprints

11.7 Spring Boot (Java)

Output Files:

src/main/java/.../entity/*.java — JPA entities
src/main/java/.../dto/*.java — DTOs with Bean Validation
src/main/java/.../repository/*.java — Spring Data repositories
src/main/java/.../controller/*.java — REST controllers

11.8 ASP.NET Core (C#)

Output Files:

Models/*.cs — Entity classes
DTOs/*.cs — DTOs with Data Annotations
Data/AppDbContext.cs — EF Core DbContext
Controllers/*.cs — API controllers

11.9 Go/GORM

Output Files:

models/*.go — Struct definitions with GORM tags
validators/*.go — go-playground/validator tags
repositories/*.go — Repository pattern
services/*.go — Business logic layer

12. SQL Dialects

12.1 MySQL

CREATE TABLE `users` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `email` VARCHAR(255) NOT NULL,
  `name` VARCHAR(100) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `users_email_unique` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

12.2 PostgreSQL

CREATE TABLE "users" (
  "id" SERIAL NOT NULL,
  "email" VARCHAR(255) NOT NULL,
  "name" VARCHAR(100),
  CONSTRAINT "users_pkey" PRIMARY KEY ("id"),
  CONSTRAINT "users_email_unique" UNIQUE ("email")
);

12.3 SQLite

CREATE TABLE "users" (
  "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  "email" TEXT NOT NULL UNIQUE,
  "name" TEXT
);

12.4 MSSQL

CREATE TABLE [users] (
  [id] INT NOT NULL IDENTITY(1,1),
  [email] NVARCHAR(255) NOT NULL,
  [name] NVARCHAR(100) NULL,
  CONSTRAINT [PK_users] PRIMARY KEY ([id]),
  CONSTRAINT [UQ_users_email] UNIQUE ([email])
);

13. Import & Export Formats

13.1 SQL Import Parsing

The import engine handles:

Pattern	Parsed
`CREATE TABLE`	Table + columns
`PRIMARY KEY`	PK constraint
`NOT NULL`	Nullable flag
`UNIQUE`	Unique constraint
`DEFAULT`	Default value
`AUTO_INCREMENT` / `SERIAL`	Auto-increment
`FOREIGN KEY ... REFERENCES`	Relationships
`ON DELETE CASCADE/SET NULL`	Cascade actions
Inline `REFERENCES`	FK in column definition

13.2 JSON Schema Format

{
  "name": "MyProject",
  "tables": [
    {
      "name": "users",
      "columns": [
        {
          "name": "id",
          "type": "INT",
          "isPrimaryKey": true,
          "isAutoIncrement": true,
          "isNullable": false,
          "isUnique": false,
          "indexes": []
        }
      ],
      "position": { "x": 100, "y": 100 }
    }
  ],
  "relationships": [
    {
      "name": "posts_user_id_fkey",
      "sourceTableId": "posts",
      "sourceColumnId": "user_id",
      "targetTableId": "users",
      "targetColumnId": "id",
      "type": "many-to-one",
      "onUpdate": "CASCADE",
      "onDelete": "CASCADE"
    }
  ]
}

13.3 ZIP Export Contents

schema-export/
├── schema.sql              # SQL DDL (selected dialect)
├── schema.json             # Complete schema definition
├── prisma/
│   └── schema.prisma       # Prisma schema
├── src/
│   └── validators/
│       └── user.ts         # Zod validators
├── docs/
│   └── swagger.json        # OpenAPI spec
└── prisma/
    └── seed.ts             # Seed data

14. AI Assistant — Groq API Integration

14.1 Architecture

Frontend (AIAssistant.tsx)
    │
    ▼
aiService.ts (POST /api/schema)
    │
    ▼
server.js (Express proxy)
    │
    ▼
Groq API (llama-3.3-70b-versatile)
    │
    ▼
Response: JSON schema → parsed → Zustand store

14.2 System Prompt

The AI is instructed to:

Analyze the user's description
Identify entities, attributes, and relationships
Choose appropriate data types
Generate foreign keys and cascade rules
Return ONLY valid JSON in the exact schema format

14.3 Fallback Generation

If the AI response cannot be parsed, the system falls back to template-based generation:

Extracts table names from keywords
Creates standard columns (id, name, email, created_at, updated_at)
Generates relationships based on naming conventions

14.4 Response Format

{
  "tables": [
    {
      "name": "users",
      "columns": [
        { "name": "id", "type": "INT", "isPrimaryKey": true, "isAutoIncrement": true },
        { "name": "email", "type": "VARCHAR(255)", "isPrimaryKey": false, "isUnique": true }
      ],
      "color": "#3B82F6"
    }
  ],
  "relationships": [
    {
      "sourceTable": "posts",
      "sourceColumn": "user_id",
      "targetTable": "users",
      "targetColumn": "id",
      "type": "many-to-one",
      "onDelete": "CASCADE"
    }
  ]
}

15. UI/UX Design — Glassmorphism

15.1 Design Philosophy

The UI follows glassmorphism — a design trend that creates a glass-like, frosted effect:

Semi-transparent backgrounds with blur
Layered depth using backdrop filters
Subtle borders with transparency
Smooth transitions and hover effects

15.2 CSS Classes

Class	Description
`.glass-panel`	Main layout panels
`.glass-card`	Interactive cards and items
`.glass-input`	Text inputs and selects
`.glass-btn`	Primary action buttons
`.glass-modal`	Modal dialogs
`.glass-modal-overlay`	Modal backdrop with blur
`.glass-table-node`	Table nodes on canvas

15.3 Theme System

Property	Light	Dark
Background	`bg-white/70`	`bg-white/5`
Border	`border-slate-200/60`	`border-white/10`
Text	`text-slate-800`	`text-white/90`
Blur	`backdrop-blur-xl`	`backdrop-blur-xl`
Shadow	`shadow-sm`	`shadow-sm`

15.4 Theme Toggle

The theme is toggled via a state variable in App.tsx:

Dark: Default, applies .dark class to root
Light: Removes .dark class, enabling light CSS variants
Persistence: Theme preference saved to localStorage

16. State Management — Zustand Store

16.1 Store Structure

interface StoreState {
  // Data
  tables: Table[];
  relationships: Relationship[];
  selectedTableId: string | null;
  selectedColumnId: string | null;
  
  // UI State
  activeModal: string | null;
  activeCodeTab: string;
  isCodePanelOpen: boolean;
  isAiAssistantOpen: boolean;
  isLeftSidebarOpen: boolean;
  isRightSidebarOpen: boolean;
  
  // Settings
  projectName: string;
  sqlDialect: SQLDialect;
  activeBackend: string;
  theme: 'light' | 'dark';
  
  // History (Undo/Redo)
  history: StoreState[];
  historyIndex: number;
  
  // Actions
  addTable: (table: Table) => void;
  updateTable: (id: string, updates: Partial<Table>) => void;
  deleteTable: (id: string) => void;
  addColumn: (tableId: string, column: Column) => void;
  updateColumn: (tableId: string, columnId: string, updates: Partial<Column>) => void;
  deleteColumn: (tableId: string, columnId: string) => void;
  addRelationship: (relationship: Relationship) => void;
  updateRelationship: (id: string, updates: Partial<Relationship>) => void;
  deleteRelationship: (id: string) => void;
  undo: () => void;
  redo: () => void;
  saveToHistory: () => void;
}

16.2 Auto-Save

Interval: Every 30 seconds
Storage: localStorage with key visual-db-designer
Data saved: Tables, relationships, project settings, theme
Restore: On page load, state is hydrated from localStorage

17. Code Generation Engine

17.1 Architecture

// codeGenerator.ts
export function generateCode(
  tables: Table[],
  relationships: Relationship[],
  backend: string,
  dialect: SQLDialect,
  projectName: string
): CodeOutput {
  switch (backend) {
    case 'prisma': return generatePrisma(tables, relationships, projectName);
    case 'sequelize': return generateSequelize(tables, relationships, projectName);
    case 'nest': return generateNestJS(tables, relationships, projectName);
    case 'laravel': return generateLaravel(tables, relationships, projectName);
    case 'django': return generateDjango(tables, relationships, projectName);
    case 'flask': return generateFlask(tables, relationships, projectName);
    case 'spring': return generateSpringBoot(tables, relationships, projectName);
    case 'dotnet': return generateDotNet(tables, relationships, projectName);
    case 'go': return generateGo(tables, relationships, projectName);
    default: return generateSQL(tables, relationships, dialect);
  }
}

17.2 Output Format

interface CodeOutput {
  files: {
    filename: string;
    content: string;
    language: string;
  }[];
}

18. Validation, Swagger & Seed Generation

18.1 Prisma Output

Zod Validators:

// src/validators/user.ts
import { z } from 'zod';

export const CreateUserSchema = z.object({
  email: z.string().email(),
  name: z.string().min(1).max(100).optional(),
});

export const UpdateUserSchema = CreateUserSchema.partial();

Swagger/OpenAPI:

{
  "openapi": "3.0.0",
  "info": { "title": "MyProject API", "version": "1.0.0" },
  "paths": {
    "/api/users": {
      "get": { "summary": "List users" },
      "post": { "summary": "Create user" }
    }
  }
}

Seed Data:

// prisma/seed.ts
import { PrismaClient } from '@prisma/client';

const prisma = new PrismaClient();

async function main() {
  await prisma.user.create({
    data: { email: '[email protected]', name: 'John Doe' }
  });
}

main();

18.2 Framework-Specific Validators

Framework	Validator
Prisma	Zod schemas
Sequelize	Joi validators
NestJS	class-validator decorators
Laravel	Form Request classes
Django	DRF serializers
Flask	Marshmallow schemas
Spring Boot	Bean Validation (@NotNull, @Size)
ASP.NET Core	Data Annotations ([Required], [StringLength])
Go/GORM	go-playground/validator tags

19. Keyboard Shortcuts & UX Features

19.1 Complete Shortcut List

Shortcut	Action	Context
`Ctrl+Z`	Undo	Global
`Ctrl+Y`	Redo	Global
`Ctrl+Shift+Z`	Redo (alt)	Global
`Ctrl+S`	Save to localStorage	Global
`Delete`	Delete selected table/column	Canvas/Right sidebar
`Backspace`	Delete selected table/column	Canvas/Right sidebar
`Ctrl+E`	Export modal	Global
`Ctrl+I`	Import modal	Global
`Ctrl+Shift+A`	AI Assistant toggle	Global
`Escape`	Close active modal	Global

19.2 Additional UX Features

Feature	Description
Auto-Save	Saves to localStorage every 30 seconds
History Stack	Full undo/redo with 50-step history
Error Boundary	Graceful error handling with recovery UI
Responsive	Adapts to different screen sizes
Canvas Controls	Zoom, pan, fit view, mini-map
Drag & Drop	Free positioning of table nodes
Theme Persistence	Remembers dark/light preference

20. Security Considerations

20.1 API Key Protection

BAD:  API key in frontend JavaScript (visible in browser)
GOOD: API key in server.js .env file (never sent to browser)

Groq API key stored in .env (gitignored)
Frontend calls Express proxy at localhost:3001
Proxy forwards request to Groq API with server-side key
.env file never committed to git

20.2 Data Privacy

No database connections — tool operates entirely in-browser
No server-side data storage — all data in localStorage
No user tracking — no analytics or telemetry
No PII collected — AI prompts are processed by Groq, not stored

20.3 CORS Configuration

// server.js
app.use(cors()); // Allow all origins for local development

For production, restrict to specific origins:

app.use(cors({ origin: 'https://your-domain.com' }));

21. Development & Contributing

21.1 Development Setup

# Clone
git clone https://github.com/M-Affan01/Visual-Database-Engineer.git
cd Visual-Database-Engineer

# Install
npm install

# Configure
echo "GROQ_API_KEY=your_key" > .env

# Start dev servers
node server.js  # Backend
node node_modules/vite/bin/vite.js --host 127.0.0.1 --port 3000  # Frontend

21.2 Code Style

TypeScript — Strict mode enabled
React — Functional components with hooks
CSS — Tailwind utility classes, glassmorphism utilities
State — Zustand (no Redux)
Naming — PascalCase components, camelCase functions, kebab-case CSS

21.3 File Organization

src/
├── components/      # React components
│   ├── Canvas.tsx   # One component per file
│   ├── layout/      # Layout components
│   ├── modals/      # Modal dialogs
│   ├── nodes/       # React Flow nodes
│   ├── edges/       # React Flow edges
│   └── panels/      # Side panels
├── services/        # Business logic
├── stores/          # State management
├── types/           # TypeScript types
└── utils/           # Utility functions

21.4 Contributing Guidelines

Fork the repository
Create a feature branch (git checkout -b feature/amazing-feature)
Commit changes (git commit -m 'Add amazing feature')
Push to branch (git push origin feature/amazing-feature)
Open a Pull Request

22. Testing Strategy

22.1 Manual Testing Checklist

Test Case	Expected Result	Status
Create table	Table appears on canvas	✅
Add column	Column visible in table node	✅
Draw relationship	Edge connects two tables	✅
Import SQL	Tables created from SQL	✅
Export SQL	Valid SQL downloaded	✅
AI generate	Schema created from prompt	✅
Undo/Redo	State reverted correctly	✅
Theme toggle	Light/dark switch works	✅
Auto-save	Data persists on reload	✅
Keyboard shortcuts	All shortcuts functional	✅

22.2 Code Generation Testing

Framework	Validation
Prisma	Schema syntax valid, validators compile
Sequelize	Models load, validators pass
NestJS	DTOs compile, Swagger generates
Laravel	Migrations run, models work
Django	Migrations apply, serializers valid
Flask	Models register, schemas valid
Spring Boot	Entities compile, DTOs valid
ASP.NET Core	Context builds, annotations valid
Go/GORM	Structs compile, tags valid

23. Known Limitations

Limitation	Impact	Mitigation
No real DB connection	Cannot execute queries	Use for design only; export to framework
No migration versioning	No schema diffing	Export full schema each time
No multi-user collab	Single-user only	Export/import JSON for sharing
No direct SQL execution	Cannot test queries	Use external database tool
AI accuracy varies	May need manual refinement	Review generated schema
No dark mode in modals	Partial theme support	Light modals work in both themes
localStorage limit	~5MB storage limit	Export to JSON for backup

24. Future Enhancements

24.1 Short-Term (v1.1)

Direct SQL execution (SQLite via sql.js)
Schema versioning and diffing
More data types (ENUM, SET, HSTORE)
Composite primary keys
Table comments/documentation

24.2 Medium-Term (v1.2)

Multi-user collaboration (WebSocket)
Database connection (MySQL, PostgreSQL)
Query builder UI
Migration generation from schema changes
Reverse engineering from existing database

24.3 Long-Term (v2.0)

Plugin system for custom frameworks
Schema marketplace
AI-powered query optimization
Database administration dashboard
API documentation auto-generation

25. License

MIT License — See LICENSE for details.

Acknowledgments

React Flow — For the incredible node-based canvas library
Zustand — For lightweight, elegant state management
Groq — For fast, free AI inference
Tailwind CSS — For rapid, beautiful UI development
dbdiagram.io — For inspiration in database design tooling

Name		Name	Last commit message	Last commit date
Latest commit History 6 Commits
public		public
src		src
.env.example		.env.example
.gitignore		.gitignore
README.md		README.md
index.html		index.html
package-lock.json		package-lock.json
package.json		package.json
postcss.config.js		postcss.config.js
server.js		server.js
server.ts		server.ts
start.bat		start.bat
tailwind.config.js		tailwind.config.js
tsconfig.json		tsconfig.json
tsconfig.node.json		tsconfig.node.json
vercel.json		vercel.json
vite.config.ts		vite.config.ts

Folders and files

Latest commit

History

Repository files navigation

Visual Database Engineer

1. OST — Overview & Synopsis

1.1 Problem Statement

1.2 Proposed Solution

1.3 Objectives

1.4 Scope

2. FST — Feasibility Study & Technical

2.1 Technical Feasibility

2.2 Operational Feasibility

2.3 Economic Feasibility

2.4 Legal Feasibility

3. SST — System Specifications & Technical

3.1 Hardware Requirements

3.2 Software Requirements

3.3 Development Stack

3.4 API Endpoints

3.5 Data Models

Table

Column

Relationship

4. LST — Literature Survey & Technical

4.1 Comparative Analysis

4.2 Technology Justification

4.3 Design Patterns

5. System Architecture

5.1 High-Level Architecture

5.2 Component Flow

6. Features & Capabilities

6.1 Visual Canvas

6.2 Table Management

6.3 Column Management

6.4 Relationship Management

6.5 Code Generation

6.6 SQL Generation

6.7 Import/Export

7. Technology Stack

7.1 Frontend Dependencies

7.2 Dev Dependencies

7.3 Backend Dependencies

8. Project Structure

9. Installation & Setup

9.1 Prerequisites

9.2 Quick Start

9.3 Getting a Groq API Key

9.4 One-Click Start (Windows)

9.5 Manual Start

9.6 Access the Application

10. Usage Guide

10.1 Creating Your First Schema

10.2 Using the AI Assistant

10.3 Importing an Existing Schema

10.4 Keyboard Shortcuts

11. Backend Frameworks Supported

11.1 Prisma (Node.js/TypeScript)

11.2 Sequelize (Node.js)

11.3 NestJS (TypeScript)

11.4 Laravel (PHP)

11.5 Django (Python)

11.6 Flask (Python)

11.7 Spring Boot (Java)

11.8 ASP.NET Core (C#)

11.9 Go/GORM

12. SQL Dialects

12.1 MySQL

12.2 PostgreSQL

12.3 SQLite

12.4 MSSQL

13. Import & Export Formats

13.1 SQL Import Parsing

13.2 JSON Schema Format

13.3 ZIP Export Contents

14. AI Assistant — Groq API Integration

14.1 Architecture

14.2 System Prompt

14.3 Fallback Generation

14.4 Response Format