Crime Records Management System

A full-stack, server-rendered web application for a small police/court department to record and cross-reference crimes, FIRs (First Information Reports), criminals, courts, punishments, and prisoners — with server-enforced role-based access control for Admins, Police Officers, Court Officers, and Clerks.

Table of Contents

• Overview
• Key Features
• User Roles & Permissions
• System Architecture
• Technology Stack
• Application Modules
• Security
• Database Architecture / Relationships
• Installation & Setup
• Database Initialization & Reconciliation
• Bootstrap Credentials (Local Development Only)
• Running the Application
• Testing
• Project Structure
• Troubleshooting
• Known Limitations / Future Improvements
• Educational / Project Note
• License / Usage

Overview

Small police and court departments still coordinate crime records, First Information Reports, criminal profiles, court assignments, sentencing, and prisoner intake across disconnected paper trails or spreadsheets. This system gives a department a single, role-appropriate place to do all of it: an officer files an FIR against a recorded crime, links a criminal to it, a court gets assigned, a punishment gets recorded, and — if applicable — a prisoner's intake, cell, and release are tracked, all cross-referenced and searchable from one dashboard.

Key Features

• Authentication — session-based login with bcrypt-hashed passwords
• Role-based access control — every module route is gated server-side by role, not just hidden in the UI
• Crime / FIR / Criminal / Court / Punishment / Prisoner management — full create/read/update/delete for all seven modules
• Dashboard — live counts, charts, and recent activity pulled from the real database
• Case history search — cross-module search across crimes, FIRs, and criminals
• Photo uploads — user, criminal, and prisoner photos with sensible default avatars
• Server-side input validation — required fields, ID/date/enum checks on every create/update route
• CSRF protection — every state-changing request, from both HTML forms and fetch() calls
• Security headers — a Content-Security-Policy scoped to the app's real dependencies, via Helmet
• Secure password hashing — bcrypt everywhere, with automatic legacy-plaintext upgrade on login
• Error-leakage prevention — no raw exception, SQL, or stack-trace text ever reaches the browser

User Roles & Permissions

Role	Crimes	Criminals	FIRs	Prisoners	Courts	Punishments	Users
Admin	✅	✅	✅	✅	✅	✅	✅
Police Officer	✅	✅	✅	✅	❌	❌	❌
Court Officer	❌	❌	❌	❌	✅	✅	❌
Clerk	❌	❌	view/create only	❌	❌	❌	❌

Dashboard and Case History are available to every authenticated role. Enforcement lives in middleware/auth.js and is applied per-route in every routes/*.js file — verified with live, role-by-role HTTP tests, not just documented.

System Architecture

Classic server-rendered MVC — no SPA framework, no separate API layer:

flowchart LR
    A[Browser] --> B[Routes]
    B --> C[Middleware<br/>auth · CSRF · validation]
    C --> D[Controllers]
    D --> E[Sequelize Models]
    E --> F[(MySQL)]
    D --> G[EJS Views]
    G --> A

Loading

• app.js — bootstrap: security headers, sessions, CSRF, the global authentication gate, route mounting
• config/db.js — the Sequelize/MySQL connection
• models/ — one file per table, plus models/index.js wiring every association/foreign key
• controllers/ — business logic per module
• routes/ — Express routers, each declaring its own role requirements and validation rules
• middleware/ — auth.js (session + role checks), upload.js (Multer image uploads), validators.js (express-validator rules)
• views/ — EJS templates, one directory per module plus shared partials/

Technology Stack

Layer	Technology
Runtime	Node.js + Express 4
Database	MySQL, via Sequelize ORM (mysql2 driver)
Views	Server-rendered EJS + Bootstrap 5 + Chart.js
Sessions	express-session
Security	helmet, a dependency-free CSRF synchronizer-token layer, bcryptjs, express-validator
Testing	Jest + Supertest, run against a real MySQL connection

Application Modules

Module	What it tracks	Key relationships
Crimes	Type, location, date, severity, solved/unsolved status	Optionally linked to a Court
FIRs	First Information Reports — type, description, auto-generated FIR number, status	Linked to a Crime and optionally a Court
Criminals	Identity, alias, age, gender, photo, status (At Large / Arrested / …)	Linked to a Crime and optionally a Court
Courts	Name, type, judge, address	Referenced by Crimes, FIRs, Criminals, Punishments
Punishments	Type, description, date sentenced, status	Linked to a Court, Criminal, Crime, and FIR simultaneously
Prisoners	Identity, cell number, admission/release dates, status, notes	Linked to a Crime, Criminal, FIR, and Punishment
Users	Staff accounts (Admin-only) — profile, badge number, address, role	Linked to a Login (credentials) record

Security

This is a genuinely hardened application, not just a documented one — every item below is implemented in code and covered by the automated test suite:

• bcrypt password hashing — every password-creating/changing code path hashes with bcryptjs; no plaintext storage or comparison remains anywhere.
• Legacy-to-bcrypt migration on login — a Login row whose password doesn't look like a bcrypt hash is treated as legacy plaintext, compared directly, and transparently re-hashed the moment it's used to log in successfully.
• CSRF protection — a dependency-free synchronizer-token layer. Every session gets a token, exposed via a <meta> tag and auto-attached to every form submission and fetch() call app-wide; every non-GET request is rejected without a valid, matching token.
• Helmet / security headers — a Content-Security-Policy scoped to the app's actual CDN dependencies (not left at generic defaults), plus the rest of Helmet's standard header set.
• Secure session cookies — httpOnly and sameSite: 'lax' always set; secure is conditional on NODE_ENV=production.
• Required SESSION_SECRET — the app refuses to start at all if it isn't set, rather than falling back to a predictable default.
• express-validator — required-field, ID, date, and enum validation on every create/update route across Users, Crimes, FIRs, Criminals, Courts, and Punishments.
• Parameterized Sequelize queries — no string-concatenated SQL anywhere in the codebase.
• EJS auto-escaping — all user/DB-sourced text in views goes through <%= %>, not raw output.
• Generic client-facing errors — full detail is logged server-side; the browser only ever sees a short, generic message.
• Server-side RBAC — enforced in route middleware, independently of anything the UI hides.

See SECURITY.md for the full write-up, including exactly which npm audit findings remain and why.

Database Architecture / Relationships

The live MySQL schema predates several of these models and uses its own column-naming conventions (documented column-by-column in MODEL_MAPPING.md). The real, DB-enforced relationships:

erDiagram
    ROLE ||--o{ LOGIN : "assigned to"
    ROLE ||--o{ USER : "assigned to"
    LOGIN ||--o| USER : "linked profile"
    CRIME ||--o{ FIR : "reported as"
    CRIME ||--o{ CRIMINAL : "involves"
    CRIME }o--o| COURT : "assigned to"
    FIR }o--o| COURT : "assigned to"
    CRIMINAL }o--o| COURT : "assigned to"
    COURT ||--o{ PUNISHMENT : "hands down"
    CRIMINAL ||--o{ PUNISHMENT : "receives"
    CRIME ||--o{ PUNISHMENT : "results in"
    FIR ||--o{ PUNISHMENT : "results in"
    CRIME ||--o{ PRISONER : "relates to"
    CRIMINAL ||--o{ PRISONER : "relates to"
    FIR ||--o{ PRISONER : "relates to"
    PUNISHMENT ||--o| PRISONER : "served by"

Loading

Every foreign key above is a real, named MySQL constraint (e.g. fk_login_user, fk_fir_crime, fk_pun_court), not just an application-level assumption — verified directly against the live schema.

Installation & Setup

Prerequisites: Node.js (LTS) with npm, and a running MySQL server.

git clone https://github.com/soyebmohammad03-dev/Crime-Records-Management-System.git
cd Crime-Records-Management-System
npm install
cp .env.example .env   # then fill in real values, see below

Create the database:

CREATE DATABASE crime_db_new;

Edit .env:

Variable	Required	Notes
DB_HOST	yes	e.g. 127.0.0.1
DB_PORT	yes	3306 for a standard local MySQL install
DB_USER	yes	MySQL user
DB_PASSWORD	yes	MySQL password
DB_NAME	yes	e.g. crime_db_new
SESSION_SECRET	yes	app refuses to start without it — generate one with node -e "console.log(require('crypto').randomBytes(48).toString('hex'))"
PORT	no	defaults to 3000

Database Initialization & Reconciliation

Run these three scripts once, in order. All three are idempotent — each checks the current database state before making any change, so re-running any of them at any time is always safe (no duplicate columns, no double-hashed passwords, no errors on a second run):

node scripts/init-db.js                        # creates default roles + a bootstrap admin account if missing
node scripts/migrate-hash-passwords.js          # hashes any remaining plaintext Login passwords
node scripts/reconcile-crime-module-schema.js   # adds any columns the models need that the live schema is missing

Bootstrap Credentials (Local Development Only)

scripts/init-db.js creates a bootstrap account — username admin, password admin (stored as a bcrypt hash, not plaintext) — only if no admin account already exists.

⚠️ This is a local-development convenience, not a production credential. Change it immediately via the Users page (Admin only) in any environment beyond your own machine, and never reuse it in a deployed or shared environment. Treat it exactly like you would any other default/well-known credential.

Running the Application

npm start        # production
npm run dev       # development, auto-reload via nodemon

Open http://localhost:3000 — you'll be redirected to the login page.

Testing

npm test

Last verified result: 8 test suites, 66 tests, 66 passed, 0 failed.

The suite (Jest + Supertest) runs against a real MySQL-backed instance of the application — not a mocked database and not a mocked Express app. Every test that needs data creates clearly-namespaced, disposable rows and cleans them up afterward; the suite never touches or leaves behind real seeded data. Coverage includes: authentication (success/failure/inactive-account), CSRF (missing/forged/valid token), RBAC (positive and negative per role), input validation, security headers, password hashing/migration idempotency, and full create/read/update/delete flows for every module via real HTTP requests.

Project Structure

Crime-Records-Management-System/
├── app.js                   # Application entry point
├── config/
│   └── db.js                 # Sequelize/MySQL connection
├── models/                   # One file per table + index.js (associations)
├── controllers/               # Business logic per module
├── routes/                    # Express routers (role checks + validation wired in)
├── middleware/
│   ├── auth.js                # Session + role checks
│   ├── upload.js               # Multer image uploads
│   └── validators.js           # express-validator rule sets
├── views/                     # EJS templates
│   ├── partials/                # Shared header/footer (CSRF meta tag lives here)
│   ├── auth/ crimes/ firs/ criminals/ courts/ punishments/ prisoners/ users/ case-history/
├── public/                    # Static assets (CSS, client JS, uploads)
├── scripts/                    # Bootstrap/migration/maintenance scripts
│   └── archive/                  # Retired scripts, kept for history only
├── tests/                      # Jest + Supertest suite
└── package.json

Troubleshooting

Problem	Fix
Database connection error	Confirm MySQL is running and .env's DB_HOST/DB_PORT/DB_USER/DB_PASSWORD/DB_NAME are correct
App exits immediately on startup	Check SESSION_SECRET is set in .env
"Unknown column" errors	Run node scripts/reconcile-crime-module-schema.js
Module not found	Run npm install
Upload directory errors	Ensure public/uploads/ exists and is writable
Session issues	Clear browser cookies and restart the server

Known Limitations / Future Improvements

These are documented, deliberate tradeoffs — not oversights:

• No login rate limiting — a determined attacker could brute-force a weak password given enough attempts; not implemented in the current version.
• In-memory session store — fine for a single process, but sessions don't survive a restart and won't work correctly across multiple app instances without a shared store.
• Two moderate npm audit findings remain (in uuid, pulled in transitively by sequelize). Fixing them would require forcing sequelize down to a breaking, years-old major version (3.30.0) — a deliberate risk-acceptance decision, not an oversight. See SECURITY.md for the full reasoning.
• CSP currently allows 'unsafe-inline' for scripts/styles, a pragmatic tradeoff given existing inline <script> blocks — tightening this with nonces is a reasonable future step.
• No record-level (per-officer) authorization — any user with module access can act on any record within it by ID.

Educational / Project Note

This project was built and iteratively hardened as a learning exercise in full-stack development, database schema reconciliation, and applying real security practices (authentication, CSRF, RBAC, input validation, and safe error handling) to an existing, imperfect codebase rather than a fresh one.

License / Usage

This project is created for educational purposes.