Oracle Project Management System
A comprehensive, role-based enterprise solution for team-based management, built for partnered client, Oracle Corporation (ORCL), as a college capstone business solution project.
OVERVIEW
The Project
In collaboration with Oracle Corporation, our team was tasked with building a comprehensive project management solution as a capstone business deliverable.
The goal: create a fully functional, production-ready system that Oracle stakeholders could use to reliably perform basic project management.
Over the course of a 4-month agile sprint, we delivered a complete role-based platform with four user tiers, real-time collaboration features, comprehensive reporting, access request hierarchies, and enterprise-grade audit logging. All hosted live with HTTPS enforcement throughout the semester.
My Role: Sole Backend Developer
While the project was a team effort, my responsibility was the entire backend architecture:
- Database Architecture: Designed and implemented the 3 of 4 MySQL schemas with 12+ tables and 20+ foreign key constraints.
- API & Business Logic: Built all server-side PHP logic, including role-based access control, transaction management, and data validation.
- Security Implementation: Developed authentication system, password recovery with security questions, session management, and cross-site scripting (XSS) prevention.
- Frontend Integration: Collaborated with our frontend designer to translate UI designs into functional interfaces, implementing dynamic JavaScript features for user accessibility.
User
Task viewing, status updates, and task access requests.
Manager
Project oversight, task creation, team management, and reporting.
Director
Project ownership, team creation, manager assignment, and reporting.
System Admin
User lifecycle, archive management, audit logs, and reassignment.
Key Achievements
Password hashing with bcrypt/argon2id, session regeneration, prepared statements, and complete XSS prevention
Transaction-safe operations with commit/rollback, foreign key constraints, and soft delete patterns
Database triggers capturing before/after states in JSON with IP, user agent, and request context
Planning deviation metrics, completion rates, average task times, and real-time KPI dashboards
Project Timeline
Foundation: Database ERD, login authentication system, role-based access control
Core Features: User dashboard, task management, commenting system
Management Layer: Manager dashboard, team management, reporting, access requests
Enterprise Features: Director controls, admin dashboard, audit logging, archive management
By The Numbers
Database Tables: 15+
Database Triggers: 30+
PHP Files: 30+
SQL Queries: 200+ prepared statements
User Roles: 4 (User, Manager, Director, Admin)
Audit Log Entries: Complete history of every action
ARCHITECTURE
The system was built on a custom PHP/MySQL foundation with no frameworks. Every component was hand-crafted for performance, security, and maintainability. This approach gave us complete control over the architecture and allowed us to implement enterprise features that frameworks often abstract away.
Database Schema Design
The database consists of 15+ normalized tables with foreign key constraints ensuring referential integrity at the database level. Key tables include:
Users & Roles
Users, Roles, User_Roles
Projects
Projects, Project_Members, Project_Comments
Tasks
Tasks, Task_Members, Task_Comments
Teams
Teams, Team_Members
Security
Standard_Questions, User_Secret_Questions
Audit
Audit_Log (with JSON storage)
Entity Relationship Diagram
Finalized ERD showing relationships between Users, Roles, Projects, Tasks, and Teams. Exported by our team DBA: Greg San Agustin.
[Full ERD available upon request - 15+ tables with foreign key constraints]
Role-Based Access Control (RBAC)
The system implements a four-tier hierarchy with granular permissions at every level:
System Admin (Role_ID=4)
- Full system control
- User creation/deletion
- Archive management
- Audit log viewing
- Password management
Director (Role_ID=3)
- Owns projects (Project_Owner)
- Creates/manages teams
- Assigns managers to projects
- Add & view project comments
- View task comments
- Approves project access requests
Manager (Role_ID=2)
- Manages assigned projects
- Add & view project comments
- Creates/edits tasks
- Assigns users to tasks
- Add & views task comments
- Approves task access requests
User (Role_ID=1)
- Views assigned tasks
- Updates task status
- Adds & views task comments
- Requests task access
Key Architectural Patterns
Junction Tables for Many-to-Many Relationships
Project_Members (Project_ID, User_ID)
-- Task assignments (users)
Task_Members (Task_ID, User_ID)
-- Team membership (all roles)
Team_Members (Team_ID, User_ID, Role_ID)
All many-to-many relationships use junction tables with composite keys, enabling clean queries and referential integrity.
Soft Deletes For Data Integrity, with Audit Trail
ALTER TABLE Users ADD is_active TINYINT(1) DEFAULT 1;
ALTER TABLE Projects ADD Is_Archived TINYINT(1) DEFAULT 0;
ALTER TABLE Tasks ADD Is_Archived TINYINT(1) DEFAULT 0;
ALTER TABLE Team_Members ADD Is_Active TINYINT(1) DEFAULT 1;
Data is never truly deleted, just flagged. This enables recovery and maintains historical integrity.
Transaction Management
All operations that modify multiple tables use database transactions to ensure data consistency:
try {
// 1. Insert into Users
$stmt = mysqli_prepare($conn, "INSERT INTO Users ...");
mysqli_stmt_execute($stmt);
$user_id = mysqli_insert_id($conn);
// 2. Insert into User_Roles
$stmt = mysqli_prepare($conn, "INSERT INTO User_Roles ...");
mysqli_stmt_execute($stmt);
// 3. Commit if both succeed
mysqli_commit($conn);
} catch (Exception $e) {
// Rollback on any failure
mysqli_rollback($conn);
}
This pattern appears throughout the codebase. User creation, task assignment, project archiving, and hard deletions all use transactions to maintain data integrity.
Performance Considerations
- Prepared Statements: All database queries use prepared statements for security and performance (query caching)
- Indexed Foreign Keys: Every foreign key is indexed for JOIN performance
- JSON Storage: Audit logs use JSON for flexible schema evolution without migrations
- Soft Deletes: Data remains in tables, enabling fast restoration without complex recovery
Building without a framework forced me to gain a deep understanding of every layer, from connection management to cyberattack prevention. This knowledge is directly transferable to any tech stack.
SECURITY
Security was designed with multiple layers of protection, ensuring that even if one control fails, others remain. From authentication to authorization to audit, every aspect was hand-crafted with enterprise requirements in mind.
Authentication System
All passwords are hashed using PHP's password_hash() with the
PASSWORD_DEFAULT algorithm.
$hashedPassword = password_hash($Password, PASSWORD_DEFAULT);
// Verifying login
if (password_verify($Password, $hashedPassword)) {
// Successful login
}
After successful login, the session ID is regenerated to prevent session fixation attacks
session_regenerate_id(true);
$_SESSION["User_ID"] = $userID;
$_SESSION["Role_ID"] = $roleID;
Every login checks the is_active flag. Deactivated users cannot log in, but
their data remains for historical purposes.
if ($UserIDExists["is_active"] == 0) {
header("location: ../index.php?error=accountInactive");
exit();
}
Password Recovery (No Email Required)
Since email servers were out of scope, I built a multi-level self-service password recovery system using security questions.
1. Setup (Settings Page)
$hashed_answer = password_hash($answers[$i], PASSWORD_DEFAULT);
INSERT INTO User_Secret_Questions
(User_ID, Question_ID, Answer_Hash, KDF)
VALUES (?, ?, ?, 'bcrypt');
Users select 3 unique questions and provide answers, all hashed as a security breach precaution.
2. Recovery Flow
Step 1: Verify email exists AND is_active=1
Step 2: Present 3 questions
Step 3: Verify all answers match hashes
Step 4: Allow password reset
All 3 answers must be correct. Otherwise, access will be revoked and the recovery process will be reset.
Authorization: Role-Based Access Control
Every page checks both authentication (logged in) and authorization (correct role):
if (!isset($_SESSION["User_ID"]) || $_SESSION["Role_ID"] != 2) {
header("Location: index.php?error=unauthorized");
exit();
}
But roles alone aren't enough! I also added validation for ownership and relationships as our functionality grew:
Project Ownership
$sql = "SELECT 1 FROM Projects
WHERE Project_ID = ? AND Project_Owner = ?";
$stmt = mysqli_prepare($conn, $sql);
mysqli_stmt_bind_param($stmt, "ii", $project_id, $director_id);
...
}
Directors can only modify projects they own.
Task Assignment
$sql = "SELECT tm.Task_ID FROM Task_Members tm
WHERE tm.Task_ID = ? AND tm.User_ID = ?";
$stmt = mysqli_prepare($conn, $sql);
mysqli_stmt_bind_param($stmt, "ii", $task_id, $user_id);
Users can only update tasks they're assigned to.
Team Management
// Directors own teams, managers are members
return ($team['Director_ID'] == $user_id) ||
($is_manager_in_team);
Managers can view teams they belong to; directors can edit them.
SQL Injection Prevention
Zero raw SQL queries with user input. Every database interaction uses prepared statements:
$sql = "SELECT * FROM Users WHERE Email = '$email'";
// ✅ Always this
$sql = "SELECT * FROM Users WHERE Email = ?";
$stmt = mysqli_prepare($conn, $sql);
mysqli_stmt_bind_param($stmt, "s", $email);
mysqli_stmt_execute($stmt);
This pattern appears 200+ times across the codebase. Every query with user input uses prepared statements.
Intended Defensive Process
When a user attempts to update a task status, these checks occur:
- Session validation: Is the user logged in? (header check)
- Role check: Does the user have User role? (session Role_ID)
- Assignment check: Is this user assigned to this task? (Task_Members query)
- Input validation: Is the status value valid? (whitelist check)
- Date validation: If setting Complete/Abandoned, validate end date logic
- Transaction: Update task, recalculate project completion
- Audit trigger: Database automatically logs the change
Security is the foundation of this system. Every line of code was written with the assumption that it would be attacked. Prepared statements, output escaping, session management, and validation work together to create a system that's resilient by design.
Audit Trail
Every significant action is logged with before/after states:
{
"action": "UPDATE",
"entity": "PROJECT",
"entity_id": 42,
"old_value": {"Status": "Pending", "Percent_Complete": 0},
"new_value": {"Status": "WIP", "Percent_Complete": 25},
"actor": 7,
"ip": "192.168.1.100",
"timestamp": "2026-02-14 15:30:00 UTC"
}
This complete audit trail means every change is traceable, which was a critical consideration for standard compliance and future debugging.
FEATURES
The system delivers four distinct experiences for each role in the organizational hierarchy. Every feature is gated by authentication, authorization, and relationship validation.
TECHNICAL DEEP DIVE
Building a complete enterprise system from scratch meant solving complex problems daily. Here are five of the most challenging issues I encountered and how I solved them.
Team Integrity Enforcement
CHALLENGE
In a real organization, managers belong to specific teams, and projects should be managed by a cohesive team. But our initial design didn't enforce this. Managers from different teams could be assigned to the same project, creating organizational chaos and confusion.
PROBLEM: How do we maintain team integrity while giving directors flexibility to assign managers?
SOLUTION
I built a team detection and enforcement system that tracks which team each manager belongs to and warns when assignments cross team boundaries.
Implementation
function getProjectTeamId($conn, $project_id) {
$sql = "SELECT DISTINCT tm.Team_ID, COUNT(*) as manager_count
FROM Project_Members pm
INNER JOIN Team_Members tm ON pm.User_ID = tm.User_ID
WHERE pm.Project_ID = ? AND tm.Role_ID = 2
GROUP BY tm.Team_ID
ORDER BY manager_count DESC";
// If one team has majority, that's the project's team
}
$project_team_id = getProjectTeamId($conn, $project_id);
if ($project_team_id) {
$manager_team = getManagerTeam($conn, $manager_id);
if ($manager_team && $manager_team['Team_ID'] != $project_team_id) {
throw new Exception("This project already has managers from a different team");
}
}
Result: Directors see clear warnings when cross-team assignments occur, and the system prevents accidental organizational fragmentation. If a project already has managers from Team A, only managers from Team A can be added. If a project needs to be delegated for any reason, Directors can add secondary managers to their teams to assume temporary control over that project.
Auto-Dating Logic
CHALLENGE
When users and managers mark tasks and projects as "Complete" or "Abandoned", the system should automatically record the completion date and calculate actual duration. When re-opened, we wanted existing dates to reflect the new status of the task or project. Manual date entry would be error-prone and frustrating, so we needed a way to automate it to avoid errors.
SOLUTION
I built an intelligent state machine that automatically manages dates based on status transitions, with validation to prevent invalid states. When a project/tasks status is changed to "Complete" or "Abandoned", the completion date (End_Date) is set to the current date. When an item's status is set to "On Hold/Work in Progress/Pending" any existing End_Date is set to NULL until otherwise completed or abandoned.
Implementation
$end_date = $current_end_date; // Keep existing by default
// Case 1: Marking as Complete/Abandoned
if (($status === 'Complete' || $status === 'Abandoned') &&
($current_status !== 'Complete' && $current_status !== 'Abandoned')) {
$end_date = date('Y-m-d'); // Auto-set today
$actual_days = $start->diff($end)->days;
}
// Case 2: Reopening from Complete/Abandoned
if ($status !== 'Complete' && $status !== 'Abandoned' &&
($current_status === 'Complete' || $current_status === 'Abandoned')) {
$end_date = null;
$actual_days = null; // Clear both
}
Validation Layer: Before any update, I validate date logic:
function validateDates($start_date, $due_date = null, $end_date = null) {
$errors = [];
if ($due_date && strtotime($due_date) < strtotime($start_date)) {
$errors[] = "Due date cannot be before start date";
}
if ($end_date && strtotime($end_date) < strtotime($start_date)) {
$errors[] = "End date cannot be before start date";
}
return $errors;
}
Result: Users never manually enter completion dates. The system handles it automatically, ensuring data consistency and preventing invalid states.
Planning Deviation Metrics
CHALLENGE
Project managers needed to understand how accurate their estimates were. Raw numbers don't tell the story. A project that took 10 days vs. estimated 5 days is very different from one that took 5 days vs. estimated 10 days. We needed a metric that showed direction and magnitude of estimation error.
SOLUTION
I created a planning deviation percentage with color coding: green for overestimation (completed faster), red for underestimation (took longer), and gray for perfect estimation.
Formula
Implementation
function getProjectPlanningDeviation($conn, $project_id) {
$sql = "SELECT
SUM(t.Est_Days) as total_estimated_days,
SUM(DATEDIFF(t.End_Date, t.Start_Date)) as total_actual_days
FROM Tasks t
WHERE t.Project_ID = ?
AND t.Status = 'Complete'
AND t.Est_Days > 0
AND t.Start_Date IS NOT NULL
AND t.End_Date IS NOT NULL";
$deviation = ($estimated - $actual) / $actual;
return round($deviation * 100); // Return as percentage
}
Visual Feedback
function formatPlanningDeviation($deviation_percentage) {
if ($deviation_percentage > 0) {
$color = '#28a745'; // Green - overestimated
$sign = '+';
} elseif ($deviation_percentage < 0) {
$color = '#dc3545'; // Red - underestimated
$sign = '';
} else {
$color = '#6c757d'; // Gray - perfect
}
return "<span style='color: {$color}'>{$sign}{$deviation_percentage}%</span>";
}
Result: Managers can instantly see estimation accuracy. +25% means they overestimated (completed 25% faster than planned). -25% means they underestimated (took 25% longer). This drives better estimation over time.
Persistent Filters with SessionStorage
CHALLENGE
Users wanted filter settings to persist across page reloads, but server-side session storage would require constant AJAX calls. We needed a client-side solution that remembered each user's preferences without database overhead.
SOLUTION
I used the browser's sessionStorage API with user-specific keys to maintain filter state across page views while ensuring filters from one user don't affect another.
Implementation
const currentUserId = <?php echo $_SESSION['User_ID']; ?>;
const projectsFilterKey = `manager_${currentUserId}_projects_filter`;
const tasksFilterKey = `manager_${currentUserId}_tasks_filter`;
// Load saved filters
let projectsFilterState = JSON.parse(
sessionStorage.getItem(projectsFilterKey)
) || { 'Pending': true, 'WIP': true, 'On Hold': true, 'Complete': false, 'Abandoned': false };
function applyProjectsFilter() {
// Update state from checkboxes...
sessionStorage.setItem(projectsFilterKey, JSON.stringify(projectsFilterState));
// Apply filter to DOM...
}
function clearAllFilters() {
const filterKeys = [
`user_${currentUserId}_tasks_filter`,
`manager_${currentUserId}_projects_filter`,
`director_${currentUserId}_projects_filter`
];
filterKeys.forEach(key => sessionStorage.removeItem(key));
}
Result: Filters persist across page reloads, are unique per user, and automatically clear on logout. Zero server overhead, instant response.
Transaction-Safe Hard Deletions
CHALLENGE
When an admin permanently deletes a user or project, dozens of related records must be removed in the correct order. Foreign key constraints prevent orphaned data, but also make deletion complex. A single failure could leave the database in an inconsistent state.
SOLUTION
I implemented transaction-safe cascade deletion with temporary foreign key disabling and comprehensive error handling.
Implementation
function hardDeleteUser($conn, $user_id) {
mysqli_begin_transaction($conn);
try {
// Temporarily disable FK checks
mysqli_query($conn, "SET FOREIGN_KEY_CHECKS = 0");
// 1. Remove from junction tables
mysqli_query($conn, "DELETE FROM Project_Members WHERE User_ID = $user_id");
mysqli_query($conn, "DELETE FROM Task_Members WHERE User_ID = $user_id");
mysqli_query($conn, "DELETE FROM Team_Members WHERE User_ID = $user_id");
// 2. Set ownership to NULL
mysqli_query($conn, "UPDATE Projects SET Project_Owner = NULL WHERE Project_Owner = $user_id");
mysqli_query($conn, "UPDATE Tasks SET Task_Owner = NULL WHERE Task_Owner = $user_id");
// 3. Delete user-specific data
mysqli_query($conn, "DELETE FROM User_Secret_Questions WHERE User_ID = $user_id");
mysqli_query($conn, "DELETE FROM User_Roles WHERE User_ID = $user_id");
// 4. Finally, delete the user
mysqli_query($conn, "DELETE FROM Users WHERE User_ID = $user_id");
// Re-enable FK checks
mysqli_query($conn, "SET FOREIGN_KEY_CHECKS = 1");
mysqli_commit($conn);
return true;
} catch (Exception $e) {
mysqli_rollback($conn);
mysqli_query($conn, "SET FOREIGN_KEY_CHECKS = 1");
return false;
}
}
Critical Safety Features:
- Only archived users can be hard deleted (prevents accidental removal of active users)
- Foreign keys are disabled only temporarily and re-enabled even if the transaction fails
- All operations are in a single transaction. If anything fails, everything rolls back
- Audit logs remain intact (not deleted) for historical compliance
Result: Admins can permanently remove users with confidence, knowing the system maintains integrity and can recover from any failure.
Each of these challenges required deep understanding of databases, application logic, and user experience. Building without a framework meant I had to solve them at the fundamental level. This fundamental knowledge can transfer directly to countless technology stack.
LESSONS LEARNED
Building a complete enterprise system from scratch in 4 months was an intense, rewarding experience. Beyond the technical skills, I gained deep insights into software architecture, team collaboration, and what it takes to deliver a production-ready business solution.
Key Takeaways
Foreign keys, transactions, and soft deletes aren't optional. They're the foundation of a reliable system. Every time I thought about skipping a constraint "just for now," I later discovered why it was essential. The upfront investment in data integrity saved countless hours of debugging and workarounds.
Prepared statements, output escaping, and session management couldn't be afterthoughts. By making security part of every coding decision, the system remained resilient throughout development.
The RBAC hierarchy (User → Manager → Director → Admin) wasn't just a client specification. It shaped the foundation of how we developed the system. Good architecture enables accessible features, but bad architecture limits them. The team integrity enforcement wouldn't have been possible without the team tables designed during Sprint (Month) 3.
Working with a frontend developer who couldn't directly contribute to the codebase taught me to write clean, documented, and predictable backend code. Every function needed clear inputs/outputs, and the API surface had to be intuitive. This discipline will serve me well in any team environment.
Business Value for Oracle
Operational Efficiency
- Self-service password recovery eliminated help desk tickets for resets
- Automated date tracking removed manual entry and reduced errors
- Persistent filters saved users time by remembering their preferences
- Real-time warnings prevented organizational confusion before it happened
Data-Driven Decisions
Planning deviation metrics helped managers improve estimation accuracy
Completion rate tracking identified high-performing team members
Average completion times informed future project planning
Audit logs provided complete traceability for compliance
What I'd Do Differently
Hindsight is 20/20
1. API-First Design
I built the backend and frontend together, which sometimes
led to tight coupling. Next time, I'd design a clean REST
API first, then build the frontend against it. This would
make testing easier and allow for future mobile apps or
third-party integrations.
2. More Comprehensive Testing
With 15,000+ lines of code, manual testing became time-consuming.
I'd implement unit tests for critical paths (authentication,
authorization, transaction logic) to catch regressions faster.
3. Database Migration System
Schema changes required manual SQL scripts. A simple
migration system would have made updates safer and more
repeatable, especially when working with a DBA.
4. Earlier Performance Optimization
Some complex reports with multiple JOINs could be slow
with large datasets. I'd add query optimization and pagination
earlier in the development cycle.
Future Improvements
Originally deemed out-of-scope, email integration would notify users of access request approvals, task assignments, and password changes. This would complete the user experience.
More sophisticated reporting with trend lines, forecasting, and export capabilities (CSV/PDF) would give managers deeper insights into team performance.
Implement WebSocket or polling for live updates and notifications. When a task status changes, all viewers see the update without refreshing.
With 25+ PHP files sharing similar patterns, a component-based approach (even simple PHP includes) would reduce code duplication and ensure consistency.
Personal Reflection
When I started this project, I was confident in my coding ability but hadn't faced the complexity of a full enterprise system. Four months later, I've learned that building reliable, secure, maintainable software can be quite a difficult task.
The moments I'm most proud of aren't the features that worked on the first try. Those moments were the last few days of rigorous error fixing, seeing my team work with the software, and the constant late nights throughout those 4 months. Every prepared statement, every validation check, every foreign key constraint represents a lesson learned.
Most importantly, I learned that frameworks are tools, not crutches. Building this without one forced me to understand the fundamentals at a depth I never would have achieved otherwise. I'm now beginning to pick up frameworks and learn how to not just use them, but why they are so valuable.
Skills Demonstrated
My Final Thoughts
This project proved that I can architect, build, and deliver a complete enterprise system—from database schema to user interface—with security, data integrity, and user experience as first-class concerns. No frameworks, no shortcuts, just 15,000+ lines of carefully crafted code.
Acknowledgements
Developed by "Team Broncos" at UCCS in collaboration with Oracle Corporation
Team Broncos:
- Teammate: Project Manager
- Noah Ragsdale: Frontend Designer
- Connor Hill: Backend Developer
- Greg San Agustin: Database Administrator
All JavaScript functionality, PHP/SQL backend architecture, and business logic
implemented by me.