📖 Premium FAQ & Ticket Management System — Full-Stack Technical Specification

Welcome to the FAQ & Ticket Management System—a modern, enterprise-grade, full-stack knowledge base and helpdesk application. This application bridges the gap between self-service information retrieval (via FAQs & a smart chatbot) and dedicated user assistance (via ticket creation, moderating workflows, status tracking, escalations, and discussion forums).

The system is fully animated and responsive, built with sleek modern UI paradigms including fluid glassmorphism, dynamic scrolling helpers, real-time query logs, customizable themes, and smooth page transitions.

---

🚀 Key Features Overview

• Smart Chatbot Interface: Built-in NLP keyword matching and suggestions to quickly answer placement and internship queries, with full local LLM proxy integration support (e.g. vLLM or LM-Studio). Features automated support ticket raising directly from the chat prompt.
• Helpdesk Ticket Lifecycle: A complete workflow where users submit queries, moderators claim them to review, resolve them with answers, and admins approve resolutions (which automatically creates official FAQs) or reject queries.
• Discussion Forums & Community QA: Interactive thread board allowing users to create posts, upvote threads, provide answers, comment/reply, react, and accept the best answers.
• PostgreSQL Database Viewer: Dedicated secure database viewer page for admins, enabling server-side pagination, searching, sorting, and metadata inspection of all database tables.
• Unified Database Driver: Auto-negotiates connection between PostgreSQL, MongoDB, and Local JSON storage dynamically on startup, ensuring zero-configuration fallback.
• Interactive Ticket Tracking: Interactive timelines for checking the live claim, review, resolution, and escalation history of submitted tickets.
• Secure Authentication & Reset: JSON Web Token (JWT) bearer-token authentication flow, complete with password recovery emails via SMTP (Nodemailer) and expiration-validated reset tokens.

---

🛠️ Technology Stack

Backend

• Core Framework: Node.js + Express.js
• Databases Supported:
  • PostgreSQL (via pg)
  • MongoDB (via Mongoose)
  • Local File Storage (custom JSON database wrapper)
• Authentication & Hashing: JWT (jsonwebtoken) + bcryptjs
• Mailing System: Nodemailer (SMTP password reset flow)
• Process Manager: Nodemon (Development hot reloading)

Frontend

• Core UI Library: React 18 + Vite
• Routing: React Router DOM (v6)
• Client Communication: Axios (interceptor injected with JWT tokens)
• Animations: Framer Motion (page transitions & gesture animations)
• Styling: Custom modern CSS (theme variables, dark/light modes, animations)

---

📁 Detailed Project Structure

faq-system/
├── backend/
│   ├── config/
│   │   ├── db.js             (Unified data layer connector: pg -> mongo -> local)
│   │   └── localDb.js         (JSON file database implementation)
│   ├── controllers/
│   │   ├── authController.js     (Login, register, forgot/reset password logic)
│   │   ├── chatbotController.js  (KB scoring algorithm & ticket drafting)
│   │   ├── faqController.js      (FAQ listing, modification & helpful votes)
│   │   ├── forumController.js    (Discussion forum message CRUD)
│   │   ├── postController.js     (Community QA, threads, votes, accepted answers)
│   │   ├── queryController.js    (Ticket lifecycle: claim, resolve, approve, reject)
│   │   └── userController.js     (Admin user listing & deletion management)
│   ├── middleware/
│   │   └── auth.js           (JWT parsing & role security verification)
│   ├── models/
│   │   ├── FAQ.js            (Mongoose FAQ Schema)
│   │   ├── Forum.js          (Mongoose Forum Schema)
│   │   ├── Post.js           (Mongoose Community QA Schema)
│   │   ├── Query.js          (Mongoose Query/Ticket Schema)
│   │   └── User.js           (Mongoose User Schema)
│   ├── routes/
│   │   ├── auth.js           (Auth endpoint bindings)
│   │   ├── chatbot.js        (Chatbot & LLM proxy endpoints)
│   │   ├── dbView.js         (Admin PostgreSQL tables viewer endpoints)
│   │   ├── faqRoutes.js      (FAQ endpoints)
│   │   ├── forumRoutes.js    (Forum discussion endpoints)
│   │   ├── postRoutes.js     (Community posts endpoints)
│   │   ├── queries.js        (Queries & Tickets workflow endpoints)
│   │   └── users.js          (User management endpoints)
│   ├── server.js             (App startup & route configuration)
│   └── package.json
└── frontend/
    ├── src/
    │   ├── components/
    │   │   ├── ChatBot.jsx            (Slide-in floating chatbot widget)
    │   │   ├── FloatingScrollbar.jsx  (Custom animated progress indicator)
    │   │   ├── Footer.jsx             (Theme-aware responsive footer)
    │   │   ├── Navbar.jsx             (Navigation bar with session states)
    │   │   ├── ProtectedRoute.jsx     (Auth & admin permission wrapper)
    │   │   └── ThemeToggle.jsx        (Glassmorphic light/dark mode switch)
    │   ├── context/
    │   │   └── AuthContext.jsx        (Global authentication provider)
    │   ├── pages/
    │   │   ├── AdminDB.jsx            (PostgreSQL database inspection grid)
    │   │   ├── AdminReview.jsx        (Moderation workflow dashboard)
    │   │   ├── AskQuery.jsx           (Support ticket submit interface)
    │   │   ├── Auth.jsx               (Animated login & registration page)
    │   │   ├── Escalation.jsx         (Admin priority escalation queue)
    │   │   ├── FAQ.jsx                (Expandable public Q&A directory)
    │   │   ├── ForgotPassword.jsx     (Password recovery request)
    │   │   ├── Forum.jsx              (Discussions list & thread initiation)
    │   │   ├── ForumPost.jsx          (Thread viewer with nested answers & voting)
    │   │   ├── Home.jsx               (Fluid glassmorphism landing hub)
    │   │   ├── QueryBoard.jsx         (Personal ticket logs dashboard)
    │   │   ├── ResetPassword.jsx      (Secure new password entry form)
    │   │   ├── StatusTracker.jsx      (Ticket timeline history tracker)
    │   │   └── Users.jsx              (Admin user roster control panel)
    │   ├── utils/
    │   │   └── api.js                 (Axios configuration with token interceptors)
    │   ├── App.css                    (Modern UI design system stylesheet)
    │   ├── App.jsx                    (React Router routes & Framer transition settings)
    │   └── main.jsx
    ├── index.html
    └── package.json

---

🔍 Detailed Pinpoint Features Specifications

🧭 1. Unified Database Layer & Repository Pattern (Backend)

• Connection Negotiation: Autodetects available databases dynamically on startup. It connects to PostgreSQL if possible, falls back to MongoDB as a secondary server, and defaults to a zero-config local file-based database wrapper (localDb.js) if no remote servers are running.
• Automatic Migrations: Connects via a pool configuration and executes schema creations (users, queries, faqs, forums, posts, password_resets) with custom constraints and indices (e.g. password resets emails) automatically.
• JSONB Mappings: Standardizes PostgreSQL json columns (tags, attachments, linkedFAQs, votedBy, answers, helpfulVotes, notHelpfulVotes) automatically in database queries.

🔐 2. Authentication, Session & Access Security

• Bcrypt Hashing: Salting and hashing processes protect credentials (salt rounds = 10).
• JWT Bearer Tokens: Generates authentication tokens upon login and checks requests via authentication middleware (auth.js).
• Permission Redirection: Redirection shields restrict administrative panels (/admin, /db, /escalation, /users) to verified admin profiles and dashboard components to registered accounts.

📧 3. Nodemailer Password Reset Engine

• Dispatcher System: Registers temporary validation credentials maps inside database collections.
• Console Logging Fallback: Intercepts outgoing SMTP traffic and logs recovery links directly to the server terminal if nodemailer mail credentials are not present in .env (Dev mode).
• Expiration Windows: Verifies that incoming reset keys fall within the designated expiration window (15 minutes).

🎫 4. Helpdesk Ticket Lifecycle & User Ratings

• Atomic Claim Engine: Employs atomic claiming logic to prevent concurrent claiming conflicts among moderators.
• Helpful/Not Helpful Feedback: Logged-in users can rate resolved tickets using the /helpful route, voting helpful or notHelpful. It aggregates totals (helpful & notHelpful count fields) and records voter user IDs (helpfulVotes, notHelpfulVotes arrays) to prevent duplicate votes per query.
• State Machine Pipeline: Coordinates ticket updates between PENDING, REVIEWING, RESOLVED, APPROVED (generates FAQ automatically), ESCALATED, and REJECTED states.

💬 5. Chatbot Interface & LLM Proxy Engine

• NLP Keyword Matching: Scores search strings against FAQs using keyword relevance matching and filters stop words.
• Generative AI Proxy: Forwards conversations to local LLM frameworks (vLLM / LM-Studio running on port 6006) if standard scoring falls below threshold values.
• Automated Ticket Creation: Matches triggers like "file complaint" or "open ticket" to return ticket compilation templates directly in the chat panel.

🎨 6. User Interface Design & Aesthetics (Frontend)

• Glassmorphic Design Tokens: Stylized theme-aware component grids utilizing backdrop filters (backdrop-filter: blur()), harmonic background gradients, translucent borders, and noise texture overlays (.grain).
• Smooth Page Transitions: Configured via AnimatePresence and motion.div in App.jsx. Smoothly translates page routes along the vertical axis while fading scaling parameters.
• Theme Control: Integrated theme selector switch (ThemeToggle.jsx) toggling global CSS variables to switch between high-contrast dark and light modes.
• Floating Progress Scrollbar (FloatingScrollbar.jsx): Custom scroll component tracking viewport progression and mapping reading status visually.

🧩 7. Component Architecture & State Management (Frontend)

• Context-Based Session Provider (AuthContext.jsx): Caches caller identity profiles and local storage authorization tokens globally. Handles asynchronous authentication requests.
• Axios Request Interceptor (api.js): Sets up a central Axios caller client to intercept outbound requests, automatically embedding JWT keys inside Authorization: Bearer <token> headers.
• Protected Guard Wrappers (ProtectedRoute.jsx): Guards navigation points, redirecting guest accounts to login frames and restricting administrative panels to verified admin profiles.

📁 8. Dashboard & View Configurations (Frontend)

• Public FAQ Accordion Directory (FAQ.jsx): Accordion card panels highlighting categories and query tags.
• Support Desk Dashboards: Includes submission forms (AskQuery.jsx), user logs (QueryBoard.jsx), and claims/resolution history timelines (StatusTracker.jsx).
• Administrative Inspector Pages: Includes claimed/pending tickets moderation grids (AdminReview.jsx), priority escalation queues (Escalation.jsx), user profile roster deletion forms (Users.jsx), and pg tables inspector grid (AdminDB.jsx).

🗄️ 9. Administrative SQL Database Inspector (Backend)

• Row Counters: Inspects and compiles row statistics across tables (users, queries, faqs, forums, posts).
• Dynamic SQL Builder: Builds SQL expressions on the fly to perform server-side sorting (sort, dir), pagination (limit, offset), and columns search filters (q).

🏛️ 10. Discussion Forums & Community Q&A Threads (Backend)

• Query-Scoped Forums: Houses conversations on specific queries, allowing users to coordinate, post messages, modify contents, or delete replies.
• Community Posts Board: Enables public threads with text posts, tags, upvoting threads/replies, accepted solutions marks, and emoji reactions.

📊 11. Database Migration Automation & Seeding (Backend - migrate.js & mongoimport)

• PostgreSQL Migration (migrate.js): Loads JSON documents (users.json, queries.json, faqs.json, posts.json), normalizes columns, and batch-upserts rows using ON CONFLICT DO NOTHING.
• MongoDB Seeding (mongoimport): Seed collections directly using native utility commands:

  mongoimport --db faq-system --collection users --file local_data/users.json --jsonArray
  mongoimport --db faq-system --collection queries --file local_data/queries.json --jsonArray
  mongoimport --db faq-system --collection faqs --file local_data/faqs.json --jsonArray
  mongoimport --db faq-system --collection posts --file local_data/posts.json --jsonArray

---

---

📋 Comprehensive API Route Matrix

Category	HTTP Method	Endpoint	Auth Required	Description
Auth	POST	/api/auth/register	None	Create a new user or admin account
	POST	/api/auth/login	None	Authenticate credentials & return JWT
	GET	/api/auth/me	JWT (Any)	Retrieve logged-in user profile details
	POST	/api/auth/forgot-password	None	Dispatch password reset token link
	GET	/api/auth/reset-validate/:token	None	Check validity of reset token
	POST	/api/auth/reset-password	None	Update credentials using token
Queries	GET	/api/queries/board	None	Get approved FAQ records
	GET	/api/queries/mine	JWT (User)	Retrieve user's submitted tickets
	GET	/api/queries/:id	None	Retrieve query details by ID
	POST	/api/queries/:id/helpful	JWT (Any)	Vote helpful/not-helpful on ticket query
	POST	/api/queries	JWT (User)	Open/Submit a new ticket query
	PUT	/api/queries/:id/claim	JWT (Admin)	Claim pending ticket -> REVIEWING
	PUT	/api/queries/:id/resolve	JWT (Admin)	Input resolution answer -> RESOLVED
	PUT	/api/queries/:id/escalate	JWT (Admin)	Flag resolution for escalation -> ESCALATED
	PUT	/api/queries/:id/approve	JWT (Admin)	Approve resolution answer -> FAQ entry
	PUT	/api/queries/:id/reject	JWT (Admin)	Flag ticket as rejected -> REJECTED
	GET	/api/queries/admin/pending	JWT (Admin)	Fetch pending ticket queue
	GET	/api/queries/admin/escalated	JWT (Admin)	Fetch escalated ticket queue
	GET	/api/queries/admin/reviewing	JWT (Admin)	Fetch claimed/reviewing ticket queue
FAQs	GET	/api/faqs	None	List/search all FAQ entries
	GET	/api/faqs/:id	None	Fetch single FAQ details by ID
	POST	/api/faqs/:id/helpful	JWT (Any)	Vote helpful/not-helpful on FAQ
	POST	/api/faqs	JWT (Admin)	Direct creation of FAQ entry
	PUT	/api/faqs/:id	JWT (Admin)	Modify existing FAQ contents
	DELETE	/api/faqs/:id	JWT (Admin)	Delete FAQ entry
Forums	POST	/api/forum	JWT (Any)	Post nested message on a query forum
	GET	/api/forum/:queryId	None	Retrieve discussion logs for a query
	PUT	/api/forum/:id	JWT (Author)	Modify a forum comment message
	DELETE	/api/forum/:id	JWT (Author)	Delete a comment message
Posts	GET	/api/posts	None	Get community QA post threads
	GET	/api/posts/:id	None	Fetch single post thread detail
	POST	/api/posts	JWT (Any)	Initiate new community QA thread
	DELETE	/api/posts/:id	JWT (Author)	Delete community thread
	POST	/api/posts/:id/vote	JWT (Any)	Upvote/Downvote thread
	POST	/api/posts/:id/answers	JWT (Any)	Submit reply answer on thread
	POST	/api/posts/:id/answers/:answerId/vote	JWT (Any)	Vote on a reply answer
	PUT	/api/posts/:id/answers/:answerId	JWT (Author)	Edit reply answer message
	POST	/api/posts/:id/answers/:answerId/react	JWT (Any)	Add reaction to reply answer
	POST	/api/posts/:id/answers/:answerId/accept	JWT (Author)	Mark answer as Accepted Solution
	DELETE	/api/posts/:id/answers/:answerId	JWT (Author)	Delete reply answer
Chatbot	POST	/api/chatbot/message	None	Send chat message to NLP resolver
	POST	/api/chatbot/ticket	None	Draft ticket from chatbot flow
	POST	/api/chatbot/llm	None	Proxy message to local LLM backend
Users	GET	/api/users	JWT (Admin)	Fetch registered user roster
	DELETE	/api/users/:id	JWT (Admin)	Terminate user account
DB View	GET	/api/db-view/tables	JWT (Admin)	Fetch table counts (PostgreSQL only)
	GET	/api/db-view/:table	JWT (Admin)	Search/sort table rows (PostgreSQL only)

---

🖥️ Frontend Navigation Map

Client URL	Protected	Page Component	Features & Purpose
/	No	Home.jsx	Fluid glassmorphic welcome landing.
/login	Redirect if auth	Auth.jsx	Seamless registration & login panel.
/forgot-password	No	ForgotPassword.jsx	Generates passkey email request links.
/reset-password/:token	No	ResetPassword.jsx	Resets credential parameters using link.
/faq	No	FAQ.jsx	Accordion list of official FAQ categories.
/ask	Yes (User)	AskQuery.jsx	Form to open new query tickets.
/board	Yes (User)	QueryBoard.jsx	Overview of owned queries and responses.
/status	Yes (User)	StatusTracker.jsx	Interactive claim & resolution timeline.
/forum	Yes (User)	Forum.jsx	Discussions feed list.
/forum/:id	Yes (User)	ForumPost.jsx	Detailed QA post, reply lists & scoring.
/admin	Yes (Admin)	AdminReview.jsx	Queue workspace for query claiming/resolving.
/escalation	Yes (Admin)	Escalation.jsx	Admin escalated tickets queue.
/users	Yes (Admin)	Users.jsx	Management sheet for administrator roster.
/db	Yes (Admin)	AdminDB.jsx	Server-paginated SQL tabular inspector.

---

⚙️ Environment Configuration

Copy backend/.env.example to backend/.env and adjust setup configurations:

PORT=5000
PG_URI=postgresql://postgres:password@localhost:5432/faq_system
MONGO_URI=mongodb://localhost:27017/faq-system
JWT_SECRET=your-secure-jwt-secret-key-here

# 📧 SMTP Configuration (Nodemailer)
EMAIL_SERVICE=gmail
EMAIL_USER=your_email@gmail.com
EMAIL_PASS=your_app_password
EMAIL_FROM="VINS FAQ System Support" <your_email@gmail.com>

RESET_TOKEN_EXPIRY_MIN=15
FRONTEND_URL=http://localhost:3000
LLM_API_KEY=lm-studio

---

⚡ Setup & Execution

1. Database Provisioning

The application initiates databases in the following hierarchy on startup:

1. PostgreSQL: Tries to connect to PG_URI. Auto-generates the database schema (users, queries, faqs, forums, posts, password_resets) if not present.
2. MongoDB: Fallback. Connects to MONGO_URI if PostgreSQL is unavailable.
3. Local Storage: Fallback. Stores data in JSON files in backend/local_data/ if no database servers are reachable.

2. Run the Backend Server

cd backend
npm install
node server.js
# Starts backend server on http://localhost:5000

3. Run the Frontend Development Server

cd frontend
npm install
node .\node_modules\vite\bin\vite.js
# Starts local Vite client on http://localhost:3000

---

🔑 Seeding the Initial Admin Account

You can register as an admin using the Auth registration screen by choosing the administrative signup checkbox (if enabled), or inject it manually into your storage layer.

PostgreSQL / MongoDB / JSON manual insertion:

{
  "name": "Super Admin",
  "email": "admin@example.com",
  "password": "$2a$10$U4sW3N34.qR1k.91U517.uN3O3S1X3Y2c/P4C5Z4D5g1H1v1e1r1y", // bcrypt hash of "admin123"
  "role": "admin"
}

---

⚖️ High Availability & Load Balancing Guide

To scale the backend Node.js server to handle high concurrent traffic and ensure continuous service availability, two load-balancing architecture patterns are recommended.

                  ┌──────────────────────────────┐
                  │      Client HTTPS Traffic    │
                  └──────────────┬───────────────┘
                                 ▼
                  ┌──────────────────────────────┐
                  │    Nginx Reverse Proxy &     │
                  │   Load Balancer (Port 80)    │
                  └──────────────┬───────────────┘
                                 │
         ┌───────────────────────┼───────────────────────┐
         ▼                       ▼                       ▼
┌─────────────────┐     ┌─────────────────┐     ┌─────────────────┐
│ Node Server #1  │     │ Node Server #2  │     │ Node Server #3  │
│ (Port 5001)     │     │ (Port 5002)     │     │ (Port 5003)     │
└────────┬────────┘     └────────┬────────┘     └────────┬────────┘
         │                       │                       │
         └───────────────────────┼───────────────────────┘
                                 ▼
                    ┌──────────────────────────┐
                    │ PostgreSQL / MongoDB DB  │
                    └──────────────────────────┘

Pattern A: Process-Level Load Balancing (Single VM/Server)

Use PM2 Cluster Mode to automatically spawn instance processes matching the system's available CPU cores. PM2 shares the target port internally and distributes requests using round-robin logic:

1. Install PM2 globally:

   npm install pm2 -g

2. Define process parameters in ecosystem.config.js:

   module.exports = {
     apps: [{
       name: 'faq-backend',
       script: './server.js',
       instances: 'max', // Spawns one instance per CPU core
       exec_mode: 'cluster', // Enables round-robin balancing
       env: {
         NODE_ENV: 'production',
         PORT: 5000
       }
     }]
   };

3. Execute and monitor processes:

   pm2 start ecosystem.config.js
   pm2 status

Pattern B: Service-Level Load Balancing (Multi-Server/VM)

Use Nginx upstream configuration to balance traffic across multiple machines or container instances:

1. Establish running instances of the Node server on target addresses (e.g. 10.0.0.10:5000, 10.0.0.11:5000, 10.0.0.12:5000).
2. Configure Nginx reverse-proxy settings inside /etc/nginx/nginx.conf:

   upstream faq_backend_servers {
       # Least-connections strategy distributes to least busy instances
       least_conn; 
       
       server 10.0.0.10:5000 max_fails=3 fail_timeout=10s;
       server 10.0.0.11:5000 max_fails=3 fail_timeout=10s;
       server 10.0.0.12:5000 max_fails=3 fail_timeout=10s;
   }
   
   server {
       listen 80;
       server_name faq-system.local;
   
       location /api/ {
           proxy_pass http://faq_backend_servers;
           proxy_http_version 1.1;
           proxy_set_header Upgrade $http_upgrade;
           proxy_set_header Connection 'upgrade';
           proxy_set_header Host $host;
           proxy_cache_bypass $http_upgrade;
           proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;
           proxy_set_header X-Real-IP $remote_addr;
       }
   }

3. Reload Nginx configuration:

   sudo nginx -s reload