Information Technology Capstone Project -- SEM 1 2025 Group 9
| No. | Student Name | Student Number |
|---|---|---|
| 1 | Ryan Chang | 23691038 |
| 2 | Jasmine Zhang | 23941952 |
| 3 | Yifei Tang | 23254894 |
| 4 | Chuan Wang | 24035732 |
| 5 | Yumin Zeng | 24073955 |
- CITS5206 Capstone - Office Contacts: Sync Excels
This project is a web application developed for the Business School Operations Team to streamline the synchronization of Excel files via a centralized web interface. Currently, updates are performed manually across two separate documents, a process that is both time-consuming and prone to human error. Mistakes during cross-referencing often lead to outdated or missing contact information.
To enhance accuracy and operational efficiency, this application integrates Business School accommodation maps with contact lists, ensuring that all contact information is consistently up to date and ready for printing. By automating the synchronization process, the application reduces administrative workload, eliminates manual syncing, and maintains a reliable, real-time contact directory.
The application is built using Next.js. Client-provided Excel files are converted to JSON format and rendered as interactive accommodation maps and contact list web pages using Handsontable. Student lists are extracted from the original files to monitor PhD student completion status. Users can edit file contents directly on the web interface, and updates are automatically synchronized across all back-end files. The application also offers a download feature for the modified files and sends email notifications regarding PhD student completion, enabling the operations team to efficiently reassign rooms.
The system is built using a modern full-stack architecture that emphasizes scalability, responsiveness, and ease of maintenance. Below is a breakdown of the main components:
Framework: Next.js – A React-based framework for building server-rendered and statically generated web applications.
Styling: Tailwind CSS – A utility-first CSS framework for creating highly customizable user interfaces with minimal effort.
Language: TypeScript – Enhances JavaScript with type safety, improving code quality and reducing runtime errors.
UI Component: Handsontable – An Excel-like data grid component that allows users to view and edit tabular data interactively within the browser.
Hosting Platform: Vercel Serverless Functions – Handles backend logic using lightweight serverless architecture with built-in scalability.
Backend Framework: Node.js with Express – Used within Vercel serverless functions for API routing and backend processing.
Email Notifications: PythonAnywhere – A Python-based platform used to send automated email notifications, specifically for tracking PhD student completions and alerting the operations team.
ORM: Prisma – A modern database toolkit that simplifies database access with type-safe queries and schema management, integrated into the backend for managing contact and accommodation data.
This architecture ensures fast front-end rendering, real-time data synchronization, and reliable serverless performance while maintaining a clear separation of concerns and modularity.
This is a Next.js project with TypeScript, Tailwind CSS, and Ant Design. Here are the steps you need to take before running the code:
git clone https://github.com/yourusername/CITS5206-Capstone.git
cd CITS5206-CapstoneUsing npm:
npm installOr using yarn:
yarn installa. Create a .env file in the project root with the following content:
DATABASE_URL="postgresql://USER:PASSWORD@HOST:PORT/DATABASE"
Replace USER, PASSWORD, HOST, PORT, and DATABASE with your PostgreSQL credentials.
Note: The project is configured to use PostgreSQL by default. If you wish to use SQLite for local development, you must update the
providerinprisma/schema.prismato"sqlite"and setDATABASE_URL="file:./prisma/dev.db"in your.envfile.
In .env
DATABASE_URL="file:./prisma/dev.db"
In prisma/schema.prisma
datasource db {
provider = "sqlite"
url = env("DATABASE_URL")
}
b. Run Prisma Migrations to set up the database schema:
npx prisma migrate dev --name init
This will create the necessary tables and columns as defined in prisma/schema.prisma .
c. (Optional) Generate Prisma Client:
npx prisma generate
npm run devOr using yarn:
yarn devThe application will be available at http://localhost:3000
npm run build
npm startOr using yarn:
yarn build
yarn startPress Ctrl + C in the terminal to stop the process.
📁 Auto_notify # Python scripts for automated email notifications
├── auto_notify.md # Documentation for the auto notification process
├── check_notify.py # Script to check notification conditions
├── config.py # Configuration for notification settings
├── students_data.json # Student data for notification logic
├── test.json # Sample test data
└── __pycache__/ # Python bytecode cache
📁 prisma # Prisma ORM configuration and migrations
├── dev.db # SQLite database file (local development)
├── migrations/ # Database migration history
│ ├── ... # Individual migration folders
│ └── migration_lock.toml # Migration lock file
├── prisma/ # (May contain generated Prisma client code)
├── schema.prisma # Prisma schema definition
📁 public # Static assets and public data
├── favicon.ico # Website favicon
├── file.svg # SVG assets
├── globe.svg
├── next.svg
├── vercel.svg
├── window.svg
├── data/ # Data used by the frontend
│ ├── contact_list/ # Contact list JSONs
│ └── maps/ # Accommodation map data
├── documentation/ # Project documentation files
│ ├── DesignChanges.md
│ ├── DeveloperHandover.md
│ ├── UserManual.md
│ ├── api.md
│ ├── auth.md
│ ├── components.md
│ ├── data.md
│ ├── excel-edit.md
│ ├── features.md
│ ├── lib.md
│ ├── prisma.md
│ ├── student_json.md
│ ├── tests.md
│ ├── ui.md
│ └── upload.md
📁 src # Main application source code
├── app/ # Next.js app directory
│ ├── actions/ # Server actions (API logic)
│ ├── api/ # API route handlers
│ ├── auth/ # Authentication logic
│ ├── features/ # Feature-specific modules
│ ├── globals.css # Global CSS
│ ├── layout.tsx # App layout component
│ ├── not-found.tsx # 404 page
│ ├── page.tsx # Main entry page
│ ├── signin/ # Sign-in page/components
│ ├── signup/ # Sign-up page/components
│ └── ui/ # Shared UI components
├── components/ # Reusable React components
│ ├── ContentMap.tsx
│ ├── UploadDragger.tsx
│ ├── header.tsx
│ └── usePreventScrollBleed.ts
├── lib/ # Utility libraries and helpers
│ ├── definitions.ts
│ ├── loadFilteredContacts.ts
│ ├── loadFilteredStudents.ts
│ ├── parseExcelAndSave.ts
│ └── session.tsx
└── middleware.ts # Next.js middleware
📁 student_json # Scripts and data for student JSON processing
├── data_transfer.py # Script for data transfer
└── raw_data.xlsx # Raw Excel data
📁 tests # Automated test suites (Jest)
├── auth.test.tsx # Authentication tests
├── middleware.test.tsx # Middleware tests
├── session.test.tsx # Session management tests
├── session_route.test.tsx # Session API route tests
└── upload_route.test.tsx # Upload API route tests
README.md # Project documentation (this file)- Open the website: https://cits-5206-capstone.vercel.app
- Or run the application using the commands provided above to open the website.
- Navigate to the Sign Up page by clicking the "Sign up" button on the homepage.
- Enter your username, email address, and create a secure password.
- Click the "Register" button to create your account.
- Upon successful registration, you'll be automatically redirected to the Sign In page.
- Navigate to the Login page by clicking the "Sign in" button on the homepage.
- Enter your username and password.
- Click the "Sign in" button to access your account.
- Once logged in, you'll have access to the Excel synchronization features.
- After logging in, click on the "Edit" option in the navigation menu.
- From the sidebar menu, select the Excel file you wish to edit.
- Use the spreadsheet interface to make changes to your data:
- Add or delete rows using the context menu (right-click)
- Edit cell values directly by clicking on them
- Use dropdown menus to choose the source type “Academic” or “Research”
- After making edits to your spreadsheet, click the "Save Changes" button located at the top of the editing interface.
- The system will automatically synchronize your changes across all related files.
- A success notification will appear when your changes have been saved successfully.
- To download your synchronized Excel files, click on the "Download" option in the navigation menu.
- Select the file you wish to download from the available options.
- The file will be downloaded to your device with all the latest changes applied.
Flask Reminder System on PythonAnywhere
This manual explains how to set up, configure, and use a Flask-based reminder system hosted on PythonAnywhere. The system reads a .json file (such as a list of students or staff) and sends notifications (e.g., email) when specific conditions are met (such as dates matching today's date).
This 3rd-party based system includes:
- A Flask web app (for web access or API triggering)
- A JSON data file (e.g.,
students_data.json, will be stored on cloud) - A reminder function that checks the data and triggers email alerts
- A scheduled task (cron job) to run the check daily
- An email system to notify target users
If you're using Gmail (or another provider with 2FA):
- Go to your Google Account > Security.
- Enable 2-Step Verification if not already on.
- Go to App passwords.
- Generate an App Password for "Mail".
- Copy the password. You’ll use this instead of your Gmail password in your script.
Example config in Auto_notify/config.py`:
# Email account used to send the notifications
SYSTEM_EMAIL = os.environ.get("SYSTEM_EMAIL", "PUT THE SYSTEM MAILBOX HERE")
# App password for the sending email (never use your real email password)
SYSTEM_PASSWORD = os.environ.get("SYSTEM_PASSWORD", "REPLACE WITH THE APP PASSWORD")
# Target/recipient email for receiving the notifications
ADMIN_EMAIL = os.environ.get("ADMIN_EMAIL", "PUT THE TARGET MAILBOX HERE")By now, you should be able to finish the email settings and could receive the reminder.
Problem: Application fails to start with errors related to syntax or unsupported features.
Solution:
- Ensure you're using Node.js version 18.x or higher
- Run
node -vto check your current version - If needed, install the correct version using a version manager like nvm:
nvm install 18 nvm use 18
Problem: npm install fails with package conflicts or errors.
Solution:
- Clear npm cache:
npm cache clean --force - Delete node_modules folder and package-lock.json:
rm -rf node_modules package-lock.json - Reinstall dependencies:
npm install - If specific packages fail, try installing them individually with exact versions from package.json
Problem: Errors such as Environment variable not found: DATABASE_URL or PrismaClientInitializationError.
Solution:
- Ensure you have created a
.envfile in the project root with a validDATABASE_URLfor PostgreSQL. - If using a local PostgreSQL instance, verify that the database server is running and accessible.
- Double-check your credentials and connection string format.
- If you wish to use SQLite for local development, update both your
.envandprisma/schema.prismaas described above. - After any changes to the database or schema, re-run
npx prisma migrate devandnpx prisma generate.
Problem: prisma.user.findUnique() or other Prisma queries fail.
Solution:
- This is usually due to an incorrect or missing
DATABASE_URL, or the database schema not being migrated. Follow the steps above to resolve.
Problem: npm run build fails with TypeScript or compilation errors.
Solution:
- Check for TypeScript errors:
npx tsc --noEmit - Ensure all required environment variables are set (see below)
- Try clearing Next.js cache:
rm -rf .next
Problem: Unable to log in despite correct credentials.
Solution:
- Ensure the database is properly initialized with user accounts
- Check if SESSION_SECRET environment variable is set
- Clear browser cookies and try again
- If using a new account, verify it was created successfully in the database
Problem: Frequently being logged out or redirected to login page.
Solution:
- Sessions are set to expire after 3 hours by default
- Check for proper cookie storage in your browser
- Ensure your system clock is correctly synchronized
- Verify the SESSION_SECRET environment variable hasn't changed
Problem: Unable to create a new account.
Solution:
- Ensure email and username are unique (not already in the database)
- Check for proper database connection
- Verify password meets minimum requirements
- Check server logs for specific error messages
This project uses Jest for testing. The tests cover various aspects of the application including authentication, middleware, session management, and API routes.
Before running the tests, make sure you have installed all the dependencies:
npm installTo run all tests, use the following command:
npx jestTo run a specific test file, use:
npx jest tests/auth.test.tsxThe test suite includes the following categories:
-
Authentication Tests (
auth.test.tsx)- Tests for user signup, signin, and logout functionality
- Validates user creation, credential verification, and session management
-
Middleware Tests (
middleware.test.tsx)- Tests for route protection and authentication checks
- Validates redirects for authenticated and unauthenticated users
-
Session Management Tests (
session.test.tsx)- Tests for session creation, updating, and deletion
- Validates JWT encryption/decryption and cookie management
-
API Route Tests
- Session API tests (
session_route.test.tsx): Validates session status endpoints - Upload API tests (
upload_route.test.tsx): Tests Excel file upload functionality
- Session API tests (
When adding new features, please include appropriate tests. Follow these guidelines:
- Create test files in the
testsdirectory - Use descriptive test names that explain what is being tested
- Mock external dependencies using Jest's mocking capabilities
- Follow the existing test patterns for consistency
Tests run in a Node.js environment with Jest. The test environment uses:
- In-memory mocks for database operations
- Mocked authentication and session management
- Isolated API route testing
-
Input Validation
Introduce constraints on input fields to prevent the submission of invalid or inconsistent data during editing. -
Search Functionality
Implement a robust search feature to quickly locate room or staff information. -
Integrated Navigation
Enable clickable links between the accommodation map and the contact list, allowing users to easily navigate between related information in both views. -
Printable Contact List
Refine the formatting of the contact list to enhance readability and ensure it is print-ready. -
Role-Based Access Control (RBAC)
Implement a permission management system to assign different levels of access based on user roles (e.g., admin, staff, viewer), ensuring data security and appropriate usage.
To learn more about Next.js, take a look at the following resources:
- Next.js Documentation - learn about Next.js features and API.
- Learn Next.js - an interactive Next.js tutorial.
- the Next.js GitHub repository - Next.js GitHub Repository.
Deploy on Vercel:
The easiest way to deploy your Next.js app is to use the Vercel Platform from the creators of Next.js.
Check out Next.js deployment documentation for more details.
This project is licensed under a Proprietary License.
This repository contains confidential data and proprietary code developed for internal use within the University of Western Australia.
🚫 Do not copy, distribute, or disclose any part of this repository without prior written consent.
All names, office locations, and contact details are considered sensitive information and must be handled in accordance with applicable privacy and data protection laws.