yarn dev

Clinic Appointment & Treatment Management System (CATMS)

A Next.js-based backend and admin UI for managing clinic branches, patients, appointments, treatments, invoices and insurance claims.

This repository contains server-side API routes (Next.js app router) which interact with a PostgreSQL database. It was built as a university Database Systems project.

Features

• Authentication: user login and registration (with roles: admin / staff).
• Patient management: add, update, retrieve, and delete patient records.
• Appointments: create, update, delete and list appointments (conflict checks, rescheduling, emergency flag).
• Treatments: treatment catalog and per-appointment treatment associations.
• Payments & invoices: create payments (database triggers may generate invoices and update pending payments automatically).
• Insurance: manage insurance policies and claims; utilities to update claim status.
• Reporting: various reports including branch revenue, doctor revenue, treatment analysis, outstanding balances.
• Staff action logs: actions (login, add/update entities) are recorded in staff_action_log.
• Database debug and test endpoints to inspect schema and run health checks.

Project structure (important folders)

• src/app — Next.js app routes and pages.
  • src/app/api — Server API endpoints used by the frontend and for external integrations.
• lib/db.js — PostgreSQL connection pool using pg.
• database — SQL files, views, triggers and sample data used to initialize the database.

Environment

The app expects a PostgreSQL connection string in the DATABASE_URL environment variable. A .env file at the project root is supported in development (Next.js will load it automatically).

Example .env:

DATABASE_URL="postgresql://user:password@host:port/dbname?sslmode=require&channel_binding=require"

Make sure .env is ignored by git (this project includes .env* in .gitignore).

Local development

Install dependencies and run the dev server:

npm install
npm run dev

Open http://localhost:3000

Important API endpoints (server-side)

Below is a summary of the main API routes (all under /api):

• POST /api/login — Login with JSON body: { username, password } -> returns userId and role.

• POST /api/register — Register a new user. Required: username, password, role; non-admin roles need email, branch, phone.

• Patient endpoints (/api/patient):

  • GET /api/patient — list patients
  • GET /api/patient?id=<id> — get patient by id
  • GET /api/patient?count=true — get patient count
  • POST /api/patient — create patient (supports walk-ins)
  • PUT /api/patient?id=<id> — update patient
  • DELETE /api/patient?id=<id> — delete patient

• Appointment endpoints (/api/appointment):

  • GET /api/appointment — list appointments
  • GET /api/appointment?id=<id> — get appointment details
  • POST /api/appointment — create appointment (validates conflicts)
  • PUT /api/appointment?id=<id> — update or reschedule appointment
  • DELETE /api/appointment?id=<id> — delete appointment

• Payment endpoints (/api/payment):

  • GET /api/payment — list payments
  • POST /api/payment — create a payment (triggers DB-side invoice/pending payments updates)

• Invoice endpoints (/api/invoice):

  • GET /api/invoice — list invoices
  • POST /api/invoice — create invoice (manual case)

• Insurance & claims: /api/insurance, /api/insurance_claim, /api/insurance_policy

• Staff logs: /api/staff_action_log — retrieve action logs

• Reports: /api/reports and nested report routes for branch revenue, doctor revenue, treatment analysis, outstanding balances, etc.

• Debug & DB test endpoints:

  • GET /api/db-test — performs basic SELECT 1 test and inspects invoice table/sample data
  • GET /api/debug — returns database schema info and selected table column listings

Database notes

• SQL files in database/ contain table creation, views, triggers and sample data useful for initializing a local dev DB.
• Some behavior (like creating invoices or updating pending payments) is implemented via database triggers — check database/ for trigger SQL.

Troubleshooting

• Module not found: dotenv — Do not require dotenv in code that Next.js bundles. Next.js loads .env files in development. If you need dotenv for external scripts, install it with npm install dotenv and import only in those scripts.

• If API routes return 500 errors, check the server logs and ensure DATABASE_URL is set and the DB is reachable.

• To test DB connectivity quickly: visit /api/db-test which will run a simple test query and provide basic info.

Next steps / TODOs

• Add automated tests for core API endpoints.
• Add a small admin UI for managing branches, doctors and treatments (currently pages exist under src/app but could be expanded).
• Add pagination to large list endpoints (patients, appointments, invoices).

---

If you'd like, I can also add a short curl examples section demonstrating a few API calls (login, create patient, create appointment). Tell me which examples you prefer and I'll add them.