Skip to content

CodeWithSharma-Ji/College_complaints_Manager

BranchesTags

Repository files navigation

📚 Project Structure Overview

Your College Complaint Management System - With SQL Database

🎯 Current Status: ✅ READY TO USE

The system is now running with a persistent SQL database backend. All data is stored permanently in college_complaints.db.

📂 Project Files

Core Application Files

File Size Purpose
index.html 12K Main UI - Student/Official dashboards and modals
script.js 30K Main application logic (now uses API)
styles.css 12K All styling for the application

Backend/Database Files

File Size Purpose
server.js 15K Express.js server with REST API endpoints
api-client.js 9.6K Frontend API client for HTTP requests
college_complaints.db 56K SQLite database file (persistent storage)
package.json 482B Node.js dependencies configuration

Documentation Files

File Size Purpose
SQL_SETUP_GUIDE.md 4.6K Complete setup & usage guide
MIGRATION_COMPLETE.md 6.5K Summary of changes from IndexedDB to SQL
README.md This file Project structure overview

Utility Files

File Size Purpose
start-server.sh 902B Bash script to start server
database.js 15K Old IndexedDB code (deprecated, not used)
package-lock.json 81K npm dependency lock file
node_modules/ ~200MB Installed npm packages

🚀 Quick Start Commands

# 1. Navigate to project
cd /Users/ntggamer1/Desktop/Project

# 2. Install dependencies (first time only)
npm install

# 3. Start the server
npm start

# 4. Open in browser
# file:///Users/ntggamer1/Desktop/Project/index.html

Server runs on: http://localhost:3001

🗄️ Database Schema

Three Main Tables

1. users (Users Table)

- id: student_username or official_username
- username: Unique login name
- password: User password (plain text - for demo only)
- name: Full name
- email: Email address
- type: 'student' or 'official'
- student_id: Student ID (optional)
- registered_date: Registration timestamp

2. complaints (Complaints Table)

- id: Unique complaint ID
- student_id: Student who filed complaint
- student_name: Student's name
- title: Complaint title
- category: Category (Gender, Academic, Maintenance, etc.)
- description: Detailed description
- status: Pending/Resolved/Student Confirmed/Student Rejected
- timestamp: When filed

3. chats (Communication Table)

- id: Unique message ID
- complaint_id: Related complaint
- sender_name: Who sent message
- sender_id: Sender's username
- sender_role: student or official
- text: Message content
- timestamp: When sent

🔗 How Components Work Together

┌─────────────────────────────────────────────────────────┐
│                    BROWSER                              │
├─────────────────────────────────────────────────────────┤
│  index.html (UI)                                        │
│  script.js (Application Logic)                          │
│  styles.css (Styling)                                   │
│  api-client.js (API Calls)                              │
└────────────────────┬────────────────────────────────────┘
                     │ HTTP Requests/Responses
                     │ (REST API calls on port 3001)
                     ↓
┌─────────────────────────────────────────────────────────┐
│                    SERVER (Node.js)                      │
├─────────────────────────────────────────────────────────┤
│  server.js                                              │
│  - Express.js web server                               │
│  - REST API route handlers                             │
│  - Request validation & processing                     │
└────────────────────┬────────────────────────────────────┘
                     │ SQL Queries
                     │
                     ↓
┌─────────────────────────────────────────────────────────┐
│                  DATABASE (SQLite)                       │
├─────────────────────────────────────────────────────────┤
│  college_complaints.db                                  │
│  - Persistent file-based database                      │
│  - Three tables: users, complaints, chats              │
│  - Indexed for fast queries                            │
└─────────────────────────────────────────────────────────┘

📡 API Communication

When User Logs In:

// Browser sends login request
fetch('http://localhost:3001/api/users/student/student1')
  
// Server queries database
SELECT * FROM users WHERE username = 'student1' AND type = 'student'

// Server returns user data to browser
{ id: 'student_student1', username: 'student1', name: 'Student One', ... }

// Browser updates UI
showStudentDashboard()

When User Submits Complaint:

// Browser sends complaint data
POST http://localhost:3001/api/complaints
{ id: 'c123', studentId: 'student1', title: '...', ... }

// Server inserts into database
INSERT INTO complaints (id, student_id, ...) VALUES (...)

// Data is permanently saved to college_complaints.db

When Browser is Closed & Reopened:

// All data is still in college_complaints.db ✅
// Server reads from database again
// User can login and see all previous data

🔐 Default Demo Accounts

Student Accounts:

Username: student1
Password: 1234
Name: Student One

Username: student2
Password: 1234
Name: Student Two

Official Account:

Username: admin
Password: admin123
Name: Admin Official

🎯 Key Improvements Over IndexedDB

Feature IndexedDB SQL Database
Persistence ⚠️ Lost on cache clear ✅ Permanent
Data Backup ❌ No ✅ Single file
Multi-device ❌ No ✅ Via Server
Reliability ⚠️ Browser-dependent ✅ Standard SQL
Performance 📊 OK 📊 Indexed Queries
Scalability ⚠️ Limited ✅ Unlimited
Security ⚠️ Client-side only ✅ Server-side

📊 File Size Breakdown

Total Project Size: ~450 KB

Code Files:        ~100 KB
├─ server.js: 15 KB
├─ script.js: 30 KB
├─ api-client.js: 9.6 KB
├─ database.js: 15 KB (deprecated)
├─ index.html: 12 KB
└─ styles.css: 12 KB

Database:          56 KB
└─ college_complaints.db

Dependencies:      ~200 MB
└─ node_modules/ (npm packages)

Documentation:     ~11 KB
├─ SQL_SETUP_GUIDE.md: 4.6 KB
├─ MIGRATION_COMPLETE.md: 6.5 KB
└─ Other guides

✨ Features

✅ Persistent Storage - Data stays after browser closes
✅ SQL Database - Professional-grade storage
✅ REST API - Clean endpoints for all operations
✅ Same UI - User interface unchanged
✅ Demo Data - Pre-loaded with test accounts
✅ Indexed Queries - Fast lookups and searches
✅ Error Handling - Comprehensive error messages
✅ CORS Support - Frontend-backend communication

🛠️ Technology Stack

Frontend:
├─ HTML5
├─ CSS3
└─ JavaScript (ES6+)

Backend:
├─ Node.js (Runtime)
├─ Express.js (Web Framework)
└─ SQLite3 (Database)

Communication:
└─ REST API (HTTP)

📈 What Happens After npm start

1. Server starts on port 3001
2. SQLite database initializes
3. Creates 3 tables if they don't exist
4. Creates default user accounts
5. Creates database indexes
6. Waits for HTTP requests from browser

When user opens index.html:
7. JavaScript loads and initializes
8. API client connects to server
9. User can login/register
10. All operations save to database immediately
11. Data persists indefinitely

🔄 Data Flow Example

User Action: Submit Complaint
     ↓
JavaScript function: submitComplaint()
     ↓
API call: dbAPI.addComplaint()
     ↓
HTTP POST: /api/complaints
     ↓
Server receives JSON
     ↓
Express route handler
     ↓
SQLite INSERT query
     ↓
Data written to college_complaints.db
     ↓
Response sent to browser
     ↓
UI updated with success message

🎓 Learning from This Setup

This project demonstrates:

  • How to build a full-stack web application
  • Backend API design with Express.js
  • Frontend-backend communication
  • SQL database usage (SQLite)
  • CORS and REST principles
  • Error handling and validation
  • Single Page Application (SPA) patterns

📝 Next Steps (Optional)

To Enhance the Project:

  1. Add authentication - Hash passwords properly
  2. Add database validation - Prevent SQL injection
  3. Add user session management - Better security
  4. Add backup functionality - Export/import data
  5. Deploy to web - Use Heroku, Vercel, etc.
  6. Add more categories - Expand complaint types
  7. Add email notifications - Send updates to users
  8. Add analytics - Track complaint statistics

🐛 Common Issues

Server won't start?

# Check if port 3001 is in use
lsof -i :3001

# Kill process if needed
kill -9 <PID>

API calls failing?

# Make sure server is running
npm start

# Check browser console for errors
# Press F12 → Console tab

Database not persisting?

# Make sure server is running
# Check file exists: college_complaints.db
ls -l college_complaints.db

✅ System Status

Component Status Details
Backend Server ✅ Running http://localhost:3001
Database ✅ Created college_complaints.db (56 KB)
API Endpoints ✅ Working All 20+ endpoints functional
Frontend ✅ Ready index.html ready to open
Demo Accounts ✅ Available student1, student2, admin

📞 Support

For issues:

  1. Check SQL_SETUP_GUIDE.md for detailed instructions
  2. Check MIGRATION_COMPLETE.md for architecture overview
  3. Check browser console (F12) for error messages
  4. Check server terminal for API errors
  5. Verify port 3001 is not in use

Your SQL database system is ready to use! 🎉

Start server: npm start Open app: file:///Users/ntggamer1/Desktop/Project/index.html

About

A Webapp for the students to complaint their official about the college.

Resources

Readme
Activity

Stars

1 star

Watchers

0 watching

Forks

0 forks
Report repository

Releases

No releases published

Packages

 
 
 

Contributors

Languages