# Secure Identity System - MySQL DBMS Project ## Overview Secure Identity System is a full-stack identity management and document verification demo built around a live MySQL database. The application models a citizen-centric digital locker where users can log in, view issued and uploaded documents, manage KYC records, approve or reject access requests, create and revoke e-signatures, inspect notifications, and review authentication history. The backend is a Node.js and Express API layer that owns authentication, session management, SQL execution, and database reset actions. The frontend is a static multi-page interface that renders live database state by calling those backend endpoints. This is a DBMS-oriented project, so the main goal is not only to show screens, but to demonstrate how relational tables, joins, insert/update/delete operations, and audit-style logging work together in a realistic workflow. ## Technology Stack - Frontend: HTML, CSS, vanilla JavaScript, Chart.js - Backend: Node.js, Express, express-session, bcryptjs - Database: MySQL via mysql2/promise - Utilities: dotenv for environment configuration ## High-Level Architecture The system is split into three layers: 1. Presentation layer - Static HTML pages under the project root provide the UI. - `css/style.css` handles the shared visual system. - `app.js` provides reusable browser helpers for data access, rendering, notifications, and modal/sidebar behavior. - `queries.js` centralizes SQL strings used by every page. 2. Application layer - `server/server.js` is the Express application. - It exposes authentication routes, SQL proxy routes, and a demo reset route. - It also serves the static frontend files from the repository root. 3. Data layer - `server/db/schema.mysql.sql` defines the relational schema. - `server/db/seed.js` truncates and seeds all tables with demo data. - `server/db/pool.js` configures the MySQL connection pool. - `server/scripts/init-db.js` creates the schema and loads the seed data. ## How The Frontend Works The frontend is intentionally simple: each page is a standalone HTML document that loads `db.js` and imports functions from `app.js`. The old browser-local database model has been replaced with a server-backed model, but the page code still uses a compact query wrapper so the UI code stays readable. ### Frontend responsibilities - Render the login page and all dashboard pages. - Fetch data from backend endpoints using `query()` and `exec()`. - Render tables, badges, timelines, notifications, charts, and modal dialogs. - Handle user actions such as login, logout, approve, reject, delete, mark read, revoke, reset, and submit forms. - Keep page-specific SQL out of the UI logic by reading SQL strings from `queries.js`. ### Frontend helper functions The main browser helper file is `app.js`. Its most important functions are: - `onReady(callback)` - Waits until the app bootstrap flag is set by `db.js`. - This preserves the startup flow expected by the existing pages. - `query(sql, params)` - Sends a POST request to `/api/sql/query`. - Returns the selected rows from MySQL. - `exec(sql, params)` - Sends a POST request to `/api/sql/exec`. - Used for INSERT, UPDATE, and DELETE statements. - `getCurrentUser()` and `getAccountId()` - Read the current authenticated session from `/api/me`. - Pages use this to scope inserts and account-specific queries. - `renderTable(containerId, columns, rows, options)` - Builds a reusable HTML table. - Supports custom cell formatting and row action buttons. - `badge(text)` - Renders status values like Verified, Pending, Approved, Failed, Revoked, and Active. - `showToast(message, type)` - Displays temporary success and error notifications. - `openModal(id)` and `closeModal(id)` - Toggle modal dialogs used by documents, KYC, e-sign, and admin pages. - `initSidebar()` - Highlights the active navigation item based on the current page. - `storageUsage(usedMb, limitMb)` - Converts database quota values into a percentage for the dashboard progress bar. ## How The Backend Works The backend in `server/server.js` is responsible for security-sensitive and database-sensitive operations. ### Backend responsibilities - Accept login credentials and validate them against hashed passwords. - Create and destroy Express sessions. - Record authentication activity in `AUTHENTICATION_LOG`. - Provide a mock Aadhaar OTP login for demo purposes. - Expose a generic SQL query endpoint for the frontend pages. - Provide a generic SQL execution endpoint for mutations. - Reset the database to its initial seed state. - Serve the static frontend files. ### Backend routes #### Authentication routes - `POST /api/auth/login` - Accepts `username` and `password`. - Reads the matching account from `DIGITAL_ACCOUNT`. - Verifies the password with `bcrypt.compare()`. - Writes a success or failure row to `AUTHENTICATION_LOG`. - Stores `{ accountId, citizenId, username }` in the session. - `POST /api/auth/mock-otp` - Demo shortcut that logs in the `arjun95` account. - Does not validate a real OTP. - Exists to simulate Aadhaar-linked OTP flow in a classroom demo. - `POST /api/auth/logout` - Destroys the session. - `GET /api/me` - Returns the current session user or `null`. #### SQL proxy routes - `POST /api/sql/query` - Requires authentication. - Executes parameterized SELECT queries through MySQL. - Returns `rows` and the current `accountId`. - `POST /api/sql/exec` - Requires authentication. - Executes parameterized write operations. - Returns `affectedRows` and `insertId`. #### Admin route - `POST /api/admin/reset` - Truncates all tables and re-seeds the demo dataset. - Used to restore the project to a known state during development and demo sessions. #### Static content routes - `GET /` - Redirects to `index.html`. - Static file serving - The Express app serves all frontend assets from the repository root. - Direct access to backend code, package files, and environment files is blocked. ## Database Model The MySQL schema is defined in `server/db/schema.mysql.sql`. It is normalized into 12 tables with foreign keys that model citizens, digital accounts, issuers, requesters, document lifecycles, consent, e-signing, notifications, and audits. ### Tables and relationships - `CITIZEN` - Core identity record for each person. - Contains Aadhaar number, profile data, status, creation time, and last login. - `DIGITAL_ACCOUNT` - Login account tied to a citizen through `citizen_id`. - Stores password hash, PIN hash, storage quota, verification flags, and tier. - `ISSUER_ORGANIZATION` - Trusted issuers such as education boards, transport offices, tax departments, and passport authorities. - `ISSUED_DOCUMENT` - Government or trusted documents issued to a digital account. - References both the owning account and the issuing organization. - `UPLOADED_DOCUMENT` - Citizen-uploaded supporting documents. - Includes file metadata, checksum, storage path, and verification state. - `REQUESTER_ORGANIZATION` - Banks, employers, fintechs, and agencies that request access to citizen documents. - `ACCESS_REQUEST` - Consent workflow table that links requesters, accounts, and documents. - Stores purpose, request timestamp, expiry, consent status, response time, and token hash. - `AUTHENTICATION_LOG` - Security audit trail for password and OTP logins. - Captures IP address, user agent, location, auth status, and failure reason. - `E_SIGN_RECORD` - Tracks e-signature activity on issued documents. - Stores Aadhaar verification state, certificate URL, signature hash, and revocation time. - `NOTIFICATION` - Inbox table for alerts and lifecycle events. - Records event type, channel, delivery status, retry count, and read state. - `KYC_VERIFICATION` - Tracks KYC attempts and results for each account. - Stores method, status, verifier, UIDAI response code, score, next review date, and remarks. - `DOCUMENT_SHARE_LOG` - Audit trail for document sharing events. - Stores share method, expiry, revocation, access count, and requester linkage. ### Key relationships - `DIGITAL_ACCOUNT.citizen_id` -> `CITIZEN.citizen_id` - `ISSUED_DOCUMENT.account_id` -> `DIGITAL_ACCOUNT.account_id` - `ISSUED_DOCUMENT.issuer_id` -> `ISSUER_ORGANIZATION.issuer_id` - `UPLOADED_DOCUMENT.account_id` -> `DIGITAL_ACCOUNT.account_id` - `ACCESS_REQUEST.requester_id` -> `REQUESTER_ORGANIZATION.requester_id` - `ACCESS_REQUEST.account_id` -> `DIGITAL_ACCOUNT.account_id` - `ACCESS_REQUEST.document_id` -> `ISSUED_DOCUMENT.document_id` - `AUTHENTICATION_LOG.account_id` -> `DIGITAL_ACCOUNT.account_id` - `E_SIGN_RECORD.account_id` -> `DIGITAL_ACCOUNT.account_id` - `E_SIGN_RECORD.document_id` -> `ISSUED_DOCUMENT.document_id` - `NOTIFICATION.account_id` -> `DIGITAL_ACCOUNT.account_id` - `KYC_VERIFICATION.citizen_id` -> `CITIZEN.citizen_id` - `KYC_VERIFICATION.account_id` -> `DIGITAL_ACCOUNT.account_id` - `DOCUMENT_SHARE_LOG.account_id` -> `DIGITAL_ACCOUNT.account_id` - `DOCUMENT_SHARE_LOG.document_id` -> `ISSUED_DOCUMENT.document_id` - `DOCUMENT_SHARE_LOG.requester_id` -> `REQUESTER_ORGANIZATION.requester_id` ## How Database Changes Happen The app does not mutate data through an ORM. Instead, each page calls a named SQL statement from `queries.js`, and the backend executes it through MySQL with parameter binding. That makes the database behavior easy to inspect in a DBMS class because each action maps directly to a query. ### Page-to-database behavior #### `index.html` - Login screen - `POST /api/auth/login` - Reads the `DIGITAL_ACCOUNT` row for the username. - Compares the submitted password to `password_hash`. - Inserts a log row into `AUTHENTICATION_LOG` on success or failure. - Creates a session on success. - `POST /api/auth/mock-otp` - Creates a session for the demo account `arjun95`. - Does not write any OTP-specific tables because this is a mock flow. #### `dashboard.html` - Aggregate status view - Uses read-only queries only. - Reads counts from `ISSUED_DOCUMENT`, `UPLOADED_DOCUMENT`, `KYC_VERIFICATION`, `ACCESS_REQUEST`, `DIGITAL_ACCOUNT`, `AUTHENTICATION_LOG`, `NOTIFICATION`. - Builds a recent activity feed from authentication, access, and notification tables. - Renders document distribution using Chart.js. #### `documents.html` - Documents management - Loads issued documents from `ISSUED_DOCUMENT` joined with `ISSUER_ORGANIZATION`. - Loads uploaded documents from `UPLOADED_DOCUMENT`. - Deletes uploaded documents with `DELETE FROM UPLOADED_DOCUMENT WHERE upload_id = ?`. - Adds a new uploaded document with `INSERT INTO UPLOADED_DOCUMENT`. - The inserted row stores `account_id`, filename, type, size, description, checksum placeholder, and a derived storage path. #### `kyc.html` - KYC lifecycle - Loads the latest KYC record and the KYC history from `KYC_VERIFICATION`. - Adds a new pending KYC row with `INSERT INTO KYC_VERIFICATION`. - Adds a notification in `NOTIFICATION` when a new KYC submission is created. - Updates KYC status with `UPDATE KYC_VERIFICATION`. - When a record becomes Verified or Failed, the page sets `verified_at`, `verified_by`, `uidai_response_code`, and optionally `kyc_score`. - Adds a second notification in `NOTIFICATION` when status changes. #### `access-requests.html` - Consent management - Reads pending requests from `ACCESS_REQUEST` joined with `REQUESTER_ORGANIZATION` and `ISSUED_DOCUMENT`. - Reads historical consent states using a status filter. - Updates consent status with `UPDATE ACCESS_REQUEST`. - Pulls requester and document names using a lookup query before writing a notification. - Inserts a consent notification into `NOTIFICATION` describing whether access was approved or rejected. #### `esign.html` - Digital signature management - Reads signed documents from `E_SIGN_RECORD` joined with `ISSUED_DOCUMENT`. - Reads unsigned documents by checking which issued documents do not yet appear in `E_SIGN_RECORD`. - Creates a new signature row with `INSERT INTO E_SIGN_RECORD`. - Revokes a signature with `UPDATE E_SIGN_RECORD`. - The revocation workflow sets `is_valid = 0` and timestamps `revoked_at`. #### `notifications.html` - Inbox - Reads notifications from `NOTIFICATION` with optional channel filtering. - Marks a notification read with `UPDATE NOTIFICATION SET is_read = 1`. - Deletes a notification with `DELETE FROM NOTIFICATION WHERE notification_id = ?`. #### `audit-log.html` - Authentication auditing - Reads login events from `AUTHENTICATION_LOG` with optional auth method filtering. - Builds a failure summary grouped by `account_id`. - This page is read-only and is meant to demonstrate audit analysis. #### `admin.html` - Administration tools - Reads issuer organizations from `ISSUER_ORGANIZATION`. - Reads requester organizations from `REQUESTER_ORGANIZATION`. - Approves requesters with `UPDATE REQUESTER_ORGANIZATION SET is_approved = 1`. - Adds a new requester organization with `INSERT INTO REQUESTER_ORGANIZATION`. - Reads document share logs from `DOCUMENT_SHARE_LOG` joined with issuer and requester tables. - Resets the whole database through `POST /api/admin/reset`. ## Page And Route Map | Page | Purpose | Main SQL / API Actions | | --- | --- | --- | | `index.html` | Login and mock Aadhaar OTP entry | `/api/auth/login`, `/api/auth/mock-otp` | | `dashboard.html` | Executive summary of identity activity | dashboard aggregate queries and chart data | | `documents.html` | View and manage issued and uploaded documents | issued document read, upload insert, upload delete | | `kyc.html` | Manage KYC lifecycle | KYC insert, status update, notification insert | | `access-requests.html` | Approve or reject access requests | consent update, requester lookup, consent notification insert | | `esign.html` | Create, inspect, and revoke e-sign records | e-sign insert, unsigned-document query, revoke update | | `notifications.html` | View inbox items | notification read update, delete | | `audit-log.html` | Review authentication activity | auth log read, failed attempt aggregation | | `admin.html` | Manage organizations and audit sharing | requester approve/insert, share log read, database reset | ## Project Structure And File Responsibilities ### Runtime files - `index.html` - Entry login page. - Handles password login and mock Aadhaar OTP login. - `dashboard.html` - Main landing page after login. - Shows statistics, recent activity, and a Chart.js document mix chart. - `documents.html` - Document inventory and upload management page. - Supports filters, detail modals, upload creation, and upload deletion. - `kyc.html` - KYC status dashboard. - Supports new KYC submission and KYC status editing. - `access-requests.html` - Consent control center for document access requests. - Approve and reject actions update the database and generate notifications. - `esign.html` - e-Sign record management page. - Creates signatures, shows certificate details, and revokes signatures. - `notifications.html` - Notification inbox with channel filters. - Mark-read and delete actions. - `audit-log.html` - Authentication timeline and aggregated failure analysis. - `admin.html` - Administrative organization management and share-log auditing. - Can reset the demo database. - `app.js` - Shared frontend runtime helpers. - Includes API wrappers, DOM helpers, table rendering, status badges, modal handling, sidebar activation, and toast notifications. - `queries.js` - Central query catalog used by the frontend pages. - Keeps SQL strings grouped by page and feature. - `db.js` - Compatibility bootstrap shim. - Signals `window.dbReady = true` so the existing page startup flow can execute. - `css/style.css` - Global UI styling, layout, forms, cards, tables, badges, modals, toasts, sidebar, login page, and responsive behavior. - `package.json` - Declares runtime dependencies and scripts. - `start` launches the server. - `init-db` creates the schema and loads seed data. ### Backend files - `server/server.js` - Main Express application. - Implements sessions, auth, SQL proxies, reset endpoint, and static hosting. - `server/db/pool.js` - MySQL connection pool configuration. - Reads DB connection values from environment variables. - `server/db/schema.mysql.sql` - Full relational schema definition for the project. - Defines all tables, keys, constraints, and defaults. - `server/db/seed.js` - Provides `truncateAll(pool)` and `seedAll(pool)`. - Truncates tables in FK-safe order and populates them with demo data. - `server/scripts/init-db.js` - Reads the schema file, strips comment lines, and runs each SQL statement. - Then truncates and seeds the database. - Prints the demo login credentials to the console. ### Supporting documentation files - `README.md` - Main project documentation and technical reference. - `ER_DIAGRAM.md` - Entity-relationship reference for the schema. - `NORMALIZATION.md` - Explains how the schema is normalized and why the tables are separated. - `SQL_README.md` - SQL-specific notes and likely usage guidance for the database layer. - `frontend-sql-queries.md` - Reference for the SQL statements used by the frontend pages. - `requirements.md` - Functional and non-functional requirements for the project. - `presentation.md` - Presentation and demo notes. - `ANOMALIES.md` - Notes about known irregularities or edge cases in the data model or implementation. - `server/README.md` - Server-specific documentation. ## Configuration The database pool reads the following environment variables from `.env` if present: - `MYSQL_HOST` - `MYSQL_PORT` - `MYSQL_USER` - `MYSQL_PASSWORD` - `MYSQL_DATABASE` - `SESSION_SECRET` - `PORT` Default values are provided for local development when the variables are missing. ## Setup And Run 1. Install dependencies. 2. Create a MySQL database for the project. 3. Set the environment variables above in a `.env` file. 4. Initialize schema and seed data with `npm run init-db`. 5. Start the application with `npm start`. 6. Open `http://localhost:3000`. ## Demo Accounts The seed script creates five demo citizen accounts. All demo users share the password `demo123`. - `arjun95` / `demo123` - `priya98` / `demo123` - `rahul92` / `demo123` - `sneha99` / `demo123` - `aman90` / `demo123` ## Notes On Security And Scope - The SQL proxy endpoints are intentionally broad because this is a DBMS demo. - The project uses sessions and password hashing, but it is not a production IAM system. - The `mock-otp` endpoint is a training shortcut, not a real authentication mechanism. - The database reset route is meant for local development and classroom demos only. ## Summary Of The Technical Flow 1. The user logs in through `index.html`. 2. The backend validates the account and creates a session. 3. Each page loads SQL from `queries.js` and executes it through `/api/sql/query` or `/api/sql/exec`. 4. MySQL returns live rows from the normalized schema. 5. The UI renders tables, charts, badges, and modals based on those rows. 6. Mutating actions write back into relational tables and often create a related audit or notification record. This structure makes the project suitable for demonstrating SQL joins, foreign keys, CRUD operations, session-backed authentication, and audit logging in one coherent application. - **INSERT**: upload documents, submit KYC, sign documents, add requester org (Admin) - **SELECT \***: list tables across pages - **SELECT with WHERE**: filter by type/status/channel/method - **SELECT with LIKE**: search uploaded documents by name - **SELECT with JOIN**: access requests (org + document), e-sign (document name), share logs (document + requester) - **SELECT with GROUP BY (Aggregate)**: failed/blocked login attempts per account (`audit-log.html`) - **ORDER BY**: sorting in lists - **UPDATE**: approve/reject access requests, mark notification read, revoke e-sign, update KYC status, approve org - **DELETE**: delete uploaded documents, delete notifications - **Subquery / NOT EXISTS**: find unsigned documents (e-sign page) - **COUNT / SUM**: dashboard stats (documents count, pending requests, storage usage) ## Tech Stack - **MySQL** (database) - **Node.js + Express** (backend server) - **mysql2** (MySQL driver) - **express-session** (demo auth session) - **Chart.js** (dashboard chart) loaded via CDN in `dashboard.html` - HTML + CSS + Vanilla JS (ES modules) ## Project Pages - `index.html` — Landing / Login (demo) - `dashboard.html` — Stats + chart + recent activity - `documents.html` — Issued vs Uploaded docs (CRUD + filter + search) - `kyc.html` — KYC submission + status updates - `access-requests.html` — Consent approve/reject + history (JOIN) - `esign.html` — e-sign list + sign + revoke + certificate details - `notifications.html` — Inbox + mark read + delete - `audit-log.html` — Timeline + aggregates (GROUP BY) - `admin.html` — Issuer/Requester org management + share logs (JOIN) + DB reset ## Database Notes (Important) - The schema is in **`server/db/schema.mysql.sql`**. - The seed script is in **`server/db/seed.js`** (passwords are bcrypt-hashed). - To create tables + insert demo data, run: `npm run init-db` - To restore the original seed data at any time, open **Admin Panel** (`admin.html`) and click **Reset Demo DB** (this resets MySQL tables). ## How to Run (Windows) ### Prerequisites - **MySQL Server** running (MySQL 8.x recommended) - **Node.js** installed ### Step 1 — Create a database Create a database (example name used by default): ```sql CREATE DATABASE secure_identity_system; ``` ### Step 2 — Configure environment variables 1. Copy `.env.example` to `.env` 2. Fill in your MySQL connection details: - `MYSQL_HOST`, `MYSQL_USER`, `MYSQL_PASSWORD`, `MYSQL_DATABASE` ### Step 3 — Install dependencies From the project folder: ```bash npm install ``` ### Step 4 — Initialize schema + seed data ```bash npm run init-db ``` ### Step 5 — Start the server ```bash npm start ``` Open the app: - `http://localhost:3000/index.html` ## Demo Login Credentials All demo users share the same password: - **Username**: `arjun95` (or `priya98`, `rahul92`, `sneha99`, `aman90`) - **Password**: `demo123` ### Internet Requirement The project loads **Chart.js from a CDN**, so you need an internet connection unless you vendor it locally. ## Folder Structure ``` verification/ ├── index.html ├── dashboard.html ├── documents.html ├── kyc.html ├── access-requests.html ├── esign.html ├── notifications.html ├── admin.html ├── audit-log.html ├── db.js ├── app.js ├── server/ │ ├── server.js │ ├── db/ │ │ ├── pool.js │ │ ├── schema.mysql.sql │ │ └── seed.js │ └── scripts/ │ └── init-db.js └── css/ └── style.css ``` ## Demo Walkthrough (Quick) 1. **Login** (`index.html`) → opens dashboard (demo redirect) 2. **Dashboard**: show `COUNT`/`SUM` stats + Chart.js doc-type chart 3. **Documents**: search (LIKE), filter (WHERE), add (INSERT), delete (DELETE) 4. **KYC**: submit new KYC (INSERT), update status (UPDATE) 5. **Access Requests**: approve/reject (UPDATE), show JOIN results 6. **e-Sign**: sign a doc (INSERT), revoke (UPDATE), view certificate modal 7. **Notifications**: mark read (UPDATE), delete (DELETE) 8. **Audit Log**: filter timeline (WHERE), failed logins per account (GROUP BY) 9. **Admin**: approve organizations (UPDATE), add requester org (INSERT), share log JOIN, reset DB # citizen-identity