# AssetTrack v3 — cPanel Deployment Guide
## School Inventory & Asset Management System (Production MySQL Edition)

---

## What's in v3

- MySQL-backed, image blobs stored directly in the database (avatars, item photos, school logo, procurement bills, petty cash receipts)
- FIFO/LIFO stock batch tracking per item, with a permanent, unchangeable purchase log (including the bill image) for accountability
- Annual budget plans, weekly stock orders, and petty cash allocations with approval workflow
- Grid / list / compact inventory views, tag-based search and filtering
- Forgot-password via emailed OTP, themed HTML emails with the school logo
- Production hardening: helmet, compression, rate limiting, graceful shutdown, connection draining, structured error handling

---

## Prerequisites

| Requirement | Notes |
|---|---|
| cPanel with Node.js | Version 18+ recommended |
| MySQL 5.7 / 8.0 or MariaDB 10.11+ | Provided by cPanel/WHM |
| SMTP email access | For OTP and notification emails |

---

## Step 1 — Create MySQL Database

1. Log in to cPanel → **MySQL Databases**
2. Create a new database, e.g. `youraccount_assettrack`
3. Create a MySQL user, e.g. `youraccount_atuser` with a strong password
4. Add the user to the database with **All Privileges**
5. Note down: host (`localhost`), database name, username, password

---

## Step 2 — Upload & Extract Files

1. Go to cPanel → **File Manager** → navigate to your domain root (or a subdirectory)
2. Upload `assettrack-v3.zip`
3. Right-click → **Extract**
4. All project files should be in e.g. `/home/youraccount/assettrack/`

---

## Step 3 — Configure Environment Variables

1. Copy `.env.example` to `.env`:
   ```bash
   cp .env.example .env
   ```
2. Edit `.env` with your actual values:
   ```
   DB_HOST=localhost
   DB_PORT=3306
   DB_USER=youraccount_atuser
   DB_PASS=your_strong_password
   DB_NAME=youraccount_assettrack
   SESSION_SECRET=a-very-long-random-string-change-this
   SMTP_HOST=mail.yourdomain.com
   SMTP_PORT=587
   SMTP_USER=noreply@yourdomain.com
   SMTP_PASS=your_email_password
   ```

> **Session secret:** generate one with:
> ```bash
> node -e "console.log(require('crypto').randomBytes(32).toString('hex'))"
> ```

If using cPanel's **Setup Node.js App** panel instead of a `.env` file, add each variable individually under **Environment Variables** — both approaches are supported since the app reads from `process.env`.

---

## Step 4 — Set Up Node.js App in cPanel

1. Go to cPanel → **Setup Node.js App**
2. Click **Create Application**
3. Fill in:
   - **Node.js version:** 18.x or 20.x
   - **Application mode:** Production
   - **Application root:** `/home/youraccount/assettrack`
   - **Application URL:** your domain or subdomain
   - **Application startup file:** `server.js`
4. Add the environment variables from Step 3
5. Click **Create**

---

## Step 5 — Install Dependencies

In the cPanel Node.js app panel, click **Run NPM Install**, or via SSH:
```bash
cd /home/youraccount/assettrack
npm install
```

---

## Step 6 — Seed Initial Data (first run only)

```bash
node seed.js
```

This creates a realistic starter dataset for "Silver Oak School":
- 5 departments, 8 staff accounts, 10 locations, 8 categories, 16 items
- Stock batches (FIFO and LIFO examples), purchase logs, 4 stocking plans, 4 petty cash expenses
- **Admin login:** `rajesh.shrestha@silveroak.edu.np` / `Admin@123`
- **Staff login:** `anita.rai@silveroak.edu.np` / `Welcome@123`

> ⚠️ Change all default passwords immediately after first login via **My Profile → Change Password**.

`seed.js` is safe to run only once on a fresh database — running it again will fail on duplicate-key errors for fixed IDs like `pl_1`, `sp_annual_1`, etc. If you need to reset, drop and recreate the database first.

---

## Step 7 — Start the App

Click **Start App** in the cPanel Node.js panel, or via SSH:
```bash
node server.js
```

On startup the app will:
1. Connect to MySQL
2. Auto-create all tables and safely `ALTER TABLE` any missing columns on existing installs (safe to run repeatedly)
3. Start listening on the configured port
4. Begin periodic housekeeping (expired OTP cleanup every 30 minutes)

The app also handles `SIGTERM`/`SIGINT` gracefully — in-flight requests (e.g. a large bill upload) are allowed to finish before the process exits, which matters on cPanel redeploys/restarts.

---

## Step 8 — Configure Your Domain

In cPanel → **Domains** or **Subdomains**, point your domain/subdomain to the Node.js app port. cPanel's Passenger/reverse proxy handles this automatically.

---

## Post-Deployment Checklist

- [ ] Log in as admin and change the default password
- [ ] Update school name, tagline and **logo** under **Branding** — the logo appears in the sidebar, the login page, and every outgoing email
- [ ] Add your departments, locations and categories
- [ ] Invite staff via **Staff & Users** → Add User (they receive a welcome email with a temporary password, if SMTP is configured)
- [ ] Set up annual/weekly/petty stocking plans under **Stocking Plans**
- [ ] Set the petty cash per-expense limit if different from the Rs. 5,000 default (Settings)
- [ ] Test the forgot-password OTP flow end-to-end
- [ ] Verify email notifications are working (check SMTP settings and server logs — if SMTP isn't configured, emails are logged to the console instead of failing silently)

---

## Image & File Storage

Images and documents (avatars, item photos, school logo, procurement bills, purchase-log bills, petty cash receipts) are stored on **disk**, under an `uploads/` directory next to `server.js` — not in the database. This is deliberately chosen for cPanel and shared hosting generally: serving a file from disk is far cheaper than round-tripping it through Node and a MySQL query every time, it keeps the database small and fast to back up, and it avoids an entire class of bug where a large BLOB column bloats every unrelated query unless carefully excluded.

```
uploads/
├── avatars/                    (public — served via /api/images/avatar/:id)
├── items/                      (public — served via /api/images/item/:id)
├── logo/                       (public — served via /api/images/logo)
└── private/
    ├── bills/                  (auth required — procurement bills)
    ├── purchase-logs/          (auth required — permanent audit-trail copies)
    └── petty-receipts/         (auth required — petty cash receipts)
```

The `/api/images/...` URLs stay the same as earlier versions — the app resolves the stored file path (a small database lookup) and streams the file with `res.sendFile()`, which natively supports range requests and conditional GETs. "Public" here only means "no database round-trip and no sensitive content" (an avatar or item photo isn't private); bills and receipts stay behind the same `requireAuth` checks as before.

### ⚠️ This directory must persist across deployments

On cPanel, if you redeploy by re-extracting a fresh zip over your application root, **make sure `uploads/` is excluded from that overwrite** (or back it up and restore it after), the same way you'd protect a database — this directory now holds real, irreplaceable data (uploaded photos, bills, receipts) that doesn't exist anywhere else. The app creates the folder structure automatically on startup if it's missing, but an empty `uploads/` folder means every previously-uploaded image is gone.

**Recommended:** include `uploads/` in your regular backup routine alongside your MySQL database dump — the two together are the complete state of the application's data.

Purchase log entries (the permanent audit trail created whenever procurement is received into inventory) keep their **own copy** of the bill file on disk, independent of the original procurement request's bill — so if someone later removes or replaces the bill on the procurement request itself, the purchase record's copy is untouched.

Upload limits: item/avatar/logo images up to 15 MB; bills, receipts and PDFs up to 20 MB. The upload widgets validate file size and type in the browser before sending, so oversized or wrong-type files are rejected immediately with a clear message rather than after a slow upload attempt.

---

## File Structure

```
assettrack/
├── server.js          # Main Express application (60+ routes)
├── db.js              # MySQL data layer — pool, schema, FIFO/LIFO batches,
│                       # purchase logs, stocking plans, petty cash
├── fileStorage.js      # Filesystem storage for images/documents (uploads/)
├── mailer.js           # Themed HTML email system (OTP, notifications, welcome)
├── exports.js          # Excel report builders (inventory, transfers, etc.)
├── nepaliDate.js       # Bikram Sambat date utilities
├── seed.js             # Initial data seeder
├── package.json
├── .env                # Environment variables (NOT committed to git)
├── .env.example         # Template
├── uploads/             # Uploaded files — see "Image & File Storage" above;
│                        # back this up like a database, don't wipe on redeploy
└── public/
    ├── index.html      # Login + forgot password (OTP flow)
    ├── dashboard.html   # Main application shell + all modals
    ├── css/style.css    # Full design system
    └── js/
        ├── common.js    # API wrapper, toast, themed confirm dialog, upload validation
        └── app.js       # Full application logic
```

---

## Troubleshooting

| Problem | Solution |
|---|---|
| `ER_ACCESS_DENIED_ERROR` | Check `DB_USER` and `DB_PASS` in `.env` |
| `ECONNREFUSED` on port 3306 | Verify `DB_HOST=localhost` and MySQL is running |
| OTP email not sending | Check SMTP settings; look at Node.js error logs — unconfigured SMTP logs the OTP to the console instead of failing |
| `Cannot find module 'mysql2'` (or similar) | Run `npm install` again |
| Session lost on restart | Expected to *not* happen — sessions are stored in MySQL via `express-mysql-session`, not in memory |
| Duplicate-key error running `seed.js` | The database already has seed data; drop and recreate the database first if you need a clean reset |
| Uploaded file rejected as "too large" | Item/avatar/logo images: 15 MB max. Bills/receipts/PDFs: 20 MB max |
| A photo/bill/avatar disappeared after an unrelated edit | This was a known issue in early builds where saving any row in a table could wipe blob columns for other rows; v3's data layer fixes this by using `INSERT ... ON DUPLICATE KEY UPDATE` scoped to non-blob columns only, verified against a live database before release |
| Uploads return `EACCES`/permission denied, or `ENOENT` writing to `uploads/` | The Node process needs write permission to the `uploads/` directory. On cPanel this is usually automatic (the app runs as your account), but if you manually created the folder via File Manager first, check its permissions (755 for directories) and ownership match your cPanel user |
| Uploaded images/bills disappeared after a redeploy | The `uploads/` directory wasn't preserved across the redeploy — see "Image & File Storage" above. Back it up like a database; don't let a fresh zip extraction overwrite it |
| Images uploaded fine locally but "nothing happens" on cPanel | Check the Node.js app's error log in cPanel first — a common cause is the multipart POST being rejected by an intermediate proxy/WAF before it reaches Node. Also confirm `uploads/` was actually created (the app creates it automatically on boot; check cPanel's File Manager) and that the Node process has write access to it |

---

## Security Notes

- Change `SESSION_SECRET` to a unique random value per installation
- Use HTTPS (cPanel's Let's Encrypt SSL) in production
- The `.env` file must never be publicly accessible — prefer cPanel's Node.js app environment variables panel for secrets
- OTP codes expire after 10 minutes and are single-use
- Rate limiting: login (30/15 min), OTP requests (5/10 min)
- `helmet` sets standard security headers; `compression` reduces response payload sizes
- The app exits gracefully on `SIGTERM`/`SIGINT` and logs (without crashing) on unhandled promise rejections