🚛 FleetCore - Logistics Management System

FleetCore is a comprehensive, location-aware logistics management system designed to simulate the full lifecycle of fleet operations. It features a hybrid architecture combining .NET 9 (LINQ) for strategic planning and SQL Server Stored Procedures for high-performance tactical dispatching, secured by a Role-Based Access Control (RBAC) system with a rich Text User Interface (TUI) built using Spectre.Console.

📦 Download Latest Release

Pre-built executables are available in GitHub Releases:

🐧 Linux x64
🪟 Windows x64
🍎 macOS Intel
🍎 macOS Apple Silicon

Each release includes setup scripts, documentation, and ready-to-run executables.

🎯 Project Overview

FleetCore is a production-ready logistics management system that demonstrates:

Hybrid Business Logic: Factory Pattern switching between LINQ (Planner) and Stored Procedures (Dispatcher)
Location-Aware Assignment: Automatic matching of drivers/vehicles based on current city location
Trip Lifecycle Management: Complete workflow from Scheduled → InProgress → Completed
Role-Based Security: Five distinct user roles with granular access control
Rich Console UI: Interactive menus, paginated tables, status indicators using Spectre.Console
Database Best Practices: 3NF normalization, partitioning, indexing, triggers, CTEs, UDFs
Audit & Compliance: Complete audit trail of all system changes
Self-Healing: Automatic database health checks and repair on startup

🏛 Architecture & Design Patterns

Core Design Patterns

Factory Pattern: Dynamic switching between LinqTripService and SpTripService at runtime
Repository Pattern: Data Access Layer abstraction via Entity Framework Core
Service Layer Pattern: Business logic separated from UI and data access
RBAC Pattern: Role-based menu rendering and access guards

Technology Stack

Layer	Technology	Purpose
Frontend	.NET 9 Console App + Spectre.Console	Rich TUI with interactive menus
Business Logic	LINQ (EF Core) / T-SQL Stored Procedures	Dual-mode operation
Data Access	Entity Framework Core 9.0	ORM with trigger awareness
Database	SQL Server 2022	Partitioned tables, advanced SQL features
Containerization	Docker + Kubernetes	High availability deployment

🏛 Project Architecture & Logic

This system is designed around distinct operational personas, making the choice between LINQ and Stored Procedures functionally meaningful.

👥 Operational Personas & Workflows

1. 📅 The "Planner" Mode (LINQ - Manual Selection)

Target User: Logistics Manager / Strategic Planner
Role: Planner
Use Case: Future Scheduling & Precise Allocation

Technical Implementation:

Service: LinqTripService (Entity Framework Core with LINQ queries)
Database Access: Client-side filtering and selection
Transaction Scope: Application-level

Workflow:

Planner selects Required Certifications for the trip (e.g., "Hazmat", "Refrigerated")
System filters and displays:
- Drivers who are:
  - Status = 'Available'
  - IsActive = 1
  - CurrentLocation matches the Start Location City (parsed from address)
  - Hold ALL required certifications (non-expired)
- Vehicles that are:
  - Status = 'Available'
  - IsActive = 1
  - CurrentLocation matches the Start Location City
  - VehicleTypeID matches the selected type
Planner manually selects specific Driver and Vehicle from filtered lists
Planner enters:
- Start Location (format: "Address, City")
- Destination (format: "Address, City")
- Departure Time (future date/time)
Outcome: Creates a Trip with:
- Status = 'Scheduled' (if departure > 30 minutes from now)
- Status = 'InProgress' (if departure ≤ 30 minutes from now)
- Driver/Vehicle status updated to 'OnTrip' if InProgress

Key Features:

Full control over asset selection
Certification-based filtering
Location-aware matching
Future scheduling capability

2. ⚡ The "Dispatcher" Mode (Stored Procedures - Auto-Assign)

Target User: Floor Dispatcher / Operations Manager
Role: Dispatcher
Use Case: Immediate Deployment (ASAP)

Technical Implementation:

Service: SpTripService (SQL Server Stored Procedures)
Database Access: Server-side transactional logic
Transaction Scope: Database-level with locking

Workflow:

Dispatcher selects Vehicle Type (e.g., "Truck", "Van")
Dispatcher enters:
- Start Location (format: "Address, City")
- Destination (format: "Address, City")
System calls sp_AssignTrip which:
- Parses City from Start Location using CTE (e.g., "Warehouse A, Lahore" → "Lahore")
- Locks the first available Vehicle in that City (transactional)
- Locks the first available Competent Driver in that City (transactional)
- Validates driver has ALL mandatory certifications for Vehicle Type
- Updates both assets to Status = 'OnTrip'
- Creates Trip with Status = 'InProgress' and DepartureTime = GETDATE()
Outcome: Trip auto-assigned and immediately active

Key Features:

Zero manual selection (fully automated)
Transactional safety (prevents double-booking)
City-based location matching
Immediate deployment

Note: In SP mode, the UI still shows driver/vehicle selection prompts, but the selected values are ignored. The stored procedure performs the actual assignment.

3. ⏱️ The "Driver Admin" Mode (Lifecycle Management)

Target User: Gate Manager / Driver / Warehouse Staff
Role: Planner, Dispatcher, or any non-Viewer role
Use Case: Real-time Tracking & Asset Movement

Workflow A: Depart Scheduled Trip

User selects a Trip with Status = 'Scheduled'
System calls DepartTrip() which:
- Updates Status = 'InProgress'
- Sets DepartureTime = GETDATE() (actual departure time)
- Updates Driver Status = 'OnTrip'
- Updates Vehicle Status = 'OnTrip'
Outcome: Trip is now active and tracking

Workflow B: Complete Active Trip

User selects a Trip with Status = 'InProgress'
User enters Arrival Time (validated to be after DepartureTime)
System calls CompleteTrip() which:
- Updates Status = 'Completed'
- Sets ArrivalTime to user input
- AUTOMATIC ASSET MOVEMENT:
  - Parses Destination City from Trip's Destination field
  - Updates Driver Status = 'Available' and CurrentLocation = Destination City
  - Updates Vehicle Status = 'Available' and CurrentLocation = Destination City
Outcome: Trip completed, assets available at destination

Key Features:

Real-time status updates
Automatic location tracking
Lifecycle state management
Audit trail of all changes

4. 🛡️ The "System Admin" Mode (RBAC)

Target User: SuperAdmin / Fleet Manager
Roles: SuperAdmin, FleetManager
Use Case: Configuration, Security, and Overrides

SuperAdmin Capabilities:

User Management (UserManagerUI):
- Create new system users
- Assign roles (Planner, Dispatcher, FleetManager, Viewer, SuperAdmin)
- View all users with role highlighting
- Username uniqueness validation
Persona Switching:
- Toggle between LINQ (Planner) and SP (Dispatcher) modes at runtime
- Useful for testing and demonstration
Fleet Asset Manager (AssetManagerUI):
- Add new drivers (with location, certifications)
- Add new vehicles (with location, type)
- Archive drivers/vehicles (soft delete via triggers)
- Reactivate archived assets
Audit Logs (AuditLogUI):
- View last 20 system changes
- Filter by table, action, user
- Color-coded action types (INSERT/UPDATE/DELETE)

FleetManager Capabilities:

Fleet Asset Manager: Add, edit, and archive drivers/vehicles
Audit Logs: View system audit trail
Manual Location Override:
- Update Driver CurrentLocation by ID
- Update Vehicle CurrentLocation by ID
- Useful for handling breakdowns, relocations

FleetManager Restrictions:

❌ Cannot create new users (User Management is SuperAdmin-only)
❌ Cannot view user list
❌ Cannot switch personas (Planner ↔ Dispatcher)
❌ Cannot create trips (read-only trip history)

Key Features:

Complete system control
Security and compliance
Master data management
Audit trail visibility

🔒 Role-Based Access Control (RBAC) & Use Cases

The system enforces security through a Login Portal (LoginUI.cs) that restricts menu access based on the user's role. The main menu (ShowRBACMenu) dynamically renders options based on the logged-in user's permissions.

Login Flow

Application Startup:
- Database connection check with spinner and status message
- Self-healing: Ensures default admin user exists
- Displays connection success/failure with troubleshooting tips
Login Screen Options:
- Secure Login: Username/password authentication
  - Type back at username prompt to return to login menu
  - On failure: Option to "Try Again" or "Back to Login Menu"
- Simulation Mode: Quick role selection (for testing)
  - Select role directly without password
  - Includes "Back" option
- Exit: Terminate application
Post-Login:
- Role-based persona assignment:
  - Dispatcher → Auto-assigned to SP mode
  - All others → Default to LINQ mode
- Main menu rendered based on role
- Session context maintained (_currentUser)

Sample Credentials (Seeded Data)

Username	Password	Role	Menu Access	Use Case Description
`admin`	`admin123`	SuperAdmin	All menus + User Management + Persona Switch	Full system control. Can create users, switch personas, manage assets, view audit logs.
`planner`	`plan123`	Planner	Fleet Assets, Trip Management (Create + View), Gate Ops	Manual trip scheduling with LINQ. Can view assets, create scheduled trips, depart/complete trips.
`dispatch`	`disp123`	Dispatcher	Fleet Assets, Trip Management (Create + View), Gate Ops	Auto-assignment with SP. Can view assets, create immediate trips, depart/complete trips.
`manager`	`manage123`	FleetManager	Fleet Assets, Trip Management (View Only), Admin Tools	Asset management. Can add/archive drivers/vehicles, override locations, view audit logs. Cannot create users or switch personas.
`guest`	`guest123`	Viewer	Fleet Assets (View Only), Trip Management (History Only)	Read-only access. Can view drivers, vehicles, and trip history. Cannot create trips or access admin tools.

Menu Structure by Role

SuperAdmin Menu:

🚛 Fleet Assets (Drivers & Vehicles)
🗺️ Trip Management (Create, History)
🚧 Gate Operations (Depart, Complete)
🔄 Switch Persona (Planner ↔ Dispatcher)
🛠️ Admin Tools (Audit, Asset Mgr)
👥 User Management
🔓 Logout
🚪 Exit

Planner/Dispatcher Menu:

🚛 Fleet Assets (Drivers & Vehicles)
🗺️ Trip Management (Create, History)
🚧 Gate Operations (Depart, Complete)
🔓 Logout
🚪 Exit

FleetManager Menu:

🚛 Fleet Assets (Drivers & Vehicles)
🗺️ Trip Management (History Only)
🛠️ Admin Tools (Audit, Asset Mgr) - Note: Cannot access User Management
🔓 Logout
🚪 Exit

Viewer Menu:

🚛 Fleet Assets (Drivers & Vehicles) - View Only
🗺️ Trip Management (History Only)
🔓 Logout
🚪 Exit

Access Guards

The application implements runtime access guards:

Menu Level: Options hidden from unauthorized roles
Function Level: Additional checks before executing sensitive operations
Error Messages: Clear "Access Denied" messages for unauthorized attempts

✅ SQL Implementation Details

This project strictly adheres to 3NF normalization and demonstrates advanced SQL Server 2022 features including partitioning, CTEs, triggers, UDFs, and comprehensive indexing.

1. Schema & Normalization

Core Tables:

Driver: Includes CurrentLocation VARCHAR(50) for location-aware assignment. Maintains 3NF as location is functionally dependent on the entity's current state.
Vehicle: Includes CurrentLocation VARCHAR(50) for location-aware assignment.
Trip: Partitioned by Year using pf_TripDate partition function for performance (active vs historical data).
AdminUser: Stores credentials (Username, PasswordHash), Role, and ContactEmail for RBAC.
AuditLog: Partitioned by Year using pf_AuditDate for efficient log management.
DriverCertification: Composite key (DriverID, CertificationID) with ExpiryDate for compliance tracking.
VehicleTypeRequirement: Composite key (VehicleTypeID, CertificationID) with IsMandatory flag.

Normalization:

All tables in 3NF (no transitive dependencies)
Composite keys for junction tables
Foreign key constraints with referential integrity
Check constraints for status values

2. Stored Procedures

Procedure	Purpose	Parameters	Logic
`sp_AssignTrip`	Auto-assigns trip with location-aware matching	`@VehicleTypeID`, `@StartLocation`, `@Destination`, `@AdminID`	1. Parses City from StartLocation using CTE 2. Locks first available Vehicle in that City 3. Locks first available Competent Driver in that City 4. Validates certifications via `fn_CheckCompetency` 5. Creates Trip with `Status = 'InProgress'` 6. Updates asset statuses to `'OnTrip'`
`sp_CompleteTrip`	Completes active trip and updates asset locations	`@TripID`, `@ArrivalTime`	1. Updates Trip `Status = 'Completed'` 2. Sets `ArrivalTime` 3. Parses Destination City using CTE 4. Updates Driver/Vehicle `Status = 'Available'` 5. Updates Driver/Vehicle `CurrentLocation = Destination City`
`sp_UpdateAssetLocation`	Manual location override (Admin)	`@AssetType`, `@ID`, `@NewLocation`	1. Parses City from NewLocation 2. Updates Driver or Vehicle `CurrentLocation` 3. Used for handling breakdowns/relocations
`sp_RegisterDriver`	Registers new driver with certifications	`@FirstName`, `@LastName`, `@CNIC`, `@LicenseNumber`	Inserts driver and optional certifications
`sp_GetDriverStats`	Performance analytics using CTE	None	Returns driver statistics (total trips, last trip date) using Common Table Expression

3. User-Defined Functions (UDFs)

Function	Type	Purpose	Returns
`fn_CheckCompetency`	Scalar	Verifies driver has ALL mandatory certifications for vehicle type	`BIT` (1 = Qualified, 0 = Not Qualified)
`fn_IsDriverAvailable`	Scalar	Checks if driver is active and available	`BIT` (1 = Available, 0 = Not Available)

Implementation Details:

fn_CheckCompetency: Validates that for every IsMandatory = 1 requirement, the driver has a non-expired certification
fn_IsDriverAvailable: Simple status and active flag check

4. Triggers

Trigger	Type	Table	Purpose
`trg_SoftDeleteDriver`	INSTEAD OF DELETE	`Driver`	Prevents physical deletion. Sets `IsActive = 0` and `Status = 'Archived'`
`trg_SoftDeleteVehicle`	INSTEAD OF DELETE	`Vehicle`	Prevents physical deletion. Sets `IsActive = 0` and `Status = 'Archived'`
`trg_TripCompletion`	AFTER UPDATE	`Trip`	When Trip `Status` changes to `'Completed'`, automatically resets Driver/Vehicle `Status` to `'Available'` (redundant safety check)
`trg_AuditTrip`	AFTER UPDATE	`Trip`	Logs Trip status changes to `AuditLog` table with old/new status values and user ID

Trigger Logic:

INSTEAD OF: Intercepts DELETE operations, performs UPDATE instead
AFTER: Executes after data modification, used for automation and auditing

5. Views

View	Purpose	Columns
`vw_FleetOverview`	Real-time active trips dashboard	TripID, TripStatus, DriverName, VehiclePlate, VehicleType, StartLocation, Destination, DepartureTime
`vw_ComplianceAlerts`	Expired certification warnings	DriverID, Name, CertificationName, ExpiryDate

Usage:

vw_FleetOverview: Filtered to Status = 'InProgress' trips only
vw_ComplianceAlerts: Shows expired certifications for active drivers

6. Common Table Expressions (CTEs)

Used in:

sp_AssignTrip: Parses City from StartLocation address string

WITH CityParser AS (
    SELECT CASE 
        WHEN CHARINDEX(',', @StartLocation) > 0 
        THEN LTRIM(RTRIM(RIGHT(@StartLocation, ...)))
        ELSE @StartLocation
    END AS City
)

sp_CompleteTrip: Parses City from Destination address string
sp_GetDriverStats: Aggregates trip statistics by driver

7. Indexing Strategy

Non-Clustered Indexes:

IX_Driver_Status_Location: On Driver(Status, CurrentLocation) with included columns (FirstName, LastName, LicenseNumber) - Optimizes "Find Available Driver in City" queries
IX_Vehicle_Status_Location: On Vehicle(Status, CurrentLocation, VehicleTypeID) with included columns (RegistrationNumber, Model) - Optimizes "Find Available Vehicle in City" queries
IX_Trip_Status: On Trip(Status) with included columns (DriverID, VehicleID, DepartureTime) - Accelerates dashboard filtering and trip history queries

Partitioning:

Trip Table: Partitioned by DepartureTime Year (active vs historical)
AuditLog Table: Partitioned by ChangeDate Year (recent vs archived logs)

🖥️ Frontend Features & UI Components

The console application uses Spectre.Console to create a rich Text User Interface (TUI) with interactive menus, formatted tables, pagination, and status indicators.

UI Components

1. LoginUI.cs - Authentication Portal

Secure Login: Username/password with masked password input
Simulation Mode: Quick role selection for testing
Back Navigation: Ability to return to login menu from credential entry
Error Handling: Clear messages for invalid credentials with retry option

2. UserInterface.cs - Core UI Helpers

Menu Components:

ShowHeader(): ASCII art header with "FLEETCORE" branding
ShowMainMenu(): Legacy main menu (deprecated, replaced by ShowRBACMenu)
ShowAssetMenu(): Fleet asset management submenu
ShowTripMenu(role): Trip management menu (role-aware, hides "Create Trip" for Viewers)

Table Components:

ShowDrivers(): Paginated driver list with status color-coding
ShowVehicles(): Paginated vehicle list with status color-coding
ShowTripHistory(): Paginated trip history with status filtering (Scheduled/InProgress/Completed)

Selection Prompts:

SelectVehicleType(): Interactive vehicle type selection with "Go Back" option
SelectDriver(): Driver selection from filtered list
SelectVehicle(): Vehicle selection from filtered list
SelectTripToDepart(): Trip selection for departure (only Scheduled trips)
SelectTripToComplete(): Trip selection for completion (only InProgress trips)
SelectEntity<T>(): Generic selection method for code reusability

Utility Methods:

ShowSpinner(): Loading spinner for long-running operations
Success(): Green success message
Error(): Red error message
Info(): Blue informational message
Warning(): Yellow warning message
FormatStatus(): Color-codes status strings (Available=green, OnTrip=yellow, Archived=grey)
GetLocationInput(): Validated location input
GetIdInput(): Validated numeric ID input
Pause(): Wait for user input

Pagination:

All list views support "Next Page" and "Previous Page" navigation
Page size: 10 items per page
Shows current page number and total pages

3. AssetManagerUI.cs - Fleet Asset Management

Features:

Add New Driver:
- Input: FirstName, LastName, CNIC, LicenseNumber, CurrentCity
- Validation: Username uniqueness (if applicable)
- Confirmation prompt before creation
Add New Vehicle:
- Input: VehicleType (selection), RegistrationNumber, Model, CurrentCity
- Validation: Vehicle type selection required
- Confirmation prompt before creation
Archive Driver:
- Select from active drivers list
- Confirmation prompt
- Triggers trg_SoftDeleteDriver (soft delete)
Archive Vehicle:
- Select from active vehicles list
- Confirmation prompt
- Triggers trg_SoftDeleteVehicle (soft delete)

4. UserManagerUI.cs - User Management (SuperAdmin Only)

Features:

Create New User:
- Input: Username, Password (masked), Role (dropdown), ContactEmail
- Validation: Username uniqueness check
- Role options: Planner, Dispatcher, FleetManager, Viewer, SuperAdmin
- Confirmation prompt before creation
View All Users:
- Table display with ID, Username, Role, Email
- SuperAdmin roles highlighted in red/bold
- All users listed regardless of status

5. AuditLogUI.cs - Audit Log Viewer

Features:

Displays last 20 audit log entries
Columns: Time, Table, Action, Changes (Old/New values), User ID
Color-coded actions:
- INSERT = Green
- UPDATE = Blue
- DELETE = Red
Formatted change summary showing old and new values

Application Flow (Program.cs)

Startup Sequence:

Display header
Initialize database context
Check database connection (with spinner)
Display connection status (success/failure)
Self-heal: Ensure default admin exists
Enter login loop

Main Application Loop:

Display header and current user info
Render role-based menu (ShowRBACMenu)
Handle user selection:
- Fleet Assets: View drivers/vehicles, update locations
- Trip Management: Create trips, view history (role-aware)
- Gate Operations: Depart/complete trips
- Switch Persona: Toggle LINQ/SP mode (SuperAdmin only)
- Admin Tools: Asset manager, audit logs (SuperAdmin/FleetManager)
- User Management: Create users, view users (SuperAdmin only)
- Logout: Return to login screen
- Exit: Terminate application

Key Functions:

HandleFleetAssets(): Fleet asset viewing and location updates
HandleTripManagement(): Trip creation and history viewing
HandleGateOperations(): Trip departure and completion
HandleAdminTools(): Admin tools submenu
HandleAssetManager(): Asset management submenu
SwitchBLL(): Toggle between LINQ and SP modes
CreateTripSmart(): Smart trip creation (manual for LINQ, auto for SP)
ListDrivers(), ListVehicles(), ListTrips(): Paginated list views
UpdateAssetLocationUI(): Manual location override interface
DepartTripUI(), CompleteTripUI(): Trip lifecycle management interfaces

Input Validation

Location Format:

Preferred: "Address, City" (e.g., "Warehouse A, Lahore")
Fallback: If no comma, entire string treated as city
Warning message displayed if format is non-standard

Date Logic:

Departure time must be in the future
Arrival time must be after departure time
Clear error messages for invalid dates

Numeric Inputs:

All ID inputs validated as integers
Error handling for non-numeric input
Clear prompts for expected format

Username Uniqueness:

Checked before user creation
Error message if duplicate found

📂 Repository Structure & File Guide

FleetCore/
├── docs/                                    # Project Documentation
│   ├── phase_1/                             # Phase 1: Conceptual Design & Proposal
│   └── phase_2/                             # Phase 2: Schema Evolution & ERDs
│       ├── FleetCore_Phase2_ERD.puml        # PlantUML ERD diagram (Location-Aware)
│       └── Phase_2_Schema_Evolution_Report.md
│
├── k8s/                                     # Kubernetes Deployment Manifests
│   ├── custom-image/                        # Self-contained SQL Server deployment
│   │   ├── Dockerfile                       # Custom SQL image with master_script.sql
│   │   ├── entrypoint.sh                    # Startup script (runs master_script.sql)
│   │   ├── secret.yaml                      # SQL Server credentials (K8s Secret)
│   │   ├── deployment.yaml                  # SQL Server Deployment (3 replicas)
│   │   └── service.yaml                     # ClusterIP Service (port 1433)
│   └── sidecar/                             # Sidecar monitoring pattern (optional)
│
├── sql/                                     # Database Source Code (T-SQL)
│   ├── 01_Schema_Creation.sql              # Tables, Partitions, Constraints, Indexes
│   │   └── Creates: Driver, Vehicle, Trip, AdminUser, AuditLog, etc.
│   │   └── Partition Functions: pf_TripDate, pf_AuditDate
│   │   └── Indexes: IX_Driver_Status_Location, IX_Vehicle_Status_Location, etc.
│   │
│   ├── 02_StoredProcedures.sql             # Business Logic (Location-Aware)
│   │   └── Functions: fn_IsDriverAvailable, fn_CheckCompetency
│   │   └── Procedures: sp_AssignTrip, sp_RegisterDriver, sp_GetDriverStats
│   │
│   ├── 03_Triggers.sql                      # Automation & Audit Logic
│   │   └── INSTEAD OF: trg_SoftDeleteDriver, trg_SoftDeleteVehicle
│   │   └── AFTER: trg_TripCompletion, trg_AuditTrip
│   │
│   ├── 04_Views.sql                         # Reporting Views & CTEs
│   │   └── Views: vw_FleetOverview, vw_ComplianceAlerts
│   │   └── Procedure: sp_GetDriverStats (uses CTE)
│   │
│   ├── 05_Data_Seeding.sql                  # Test Data Generation
│   │   └── Inserts: Default AdminUser, VehicleTypes, Certifications
│   │   └── Generates: 1M+ rows (Drivers, Vehicles, Trips with realistic data)
│   │
│   ├── 06_ManualUpdates.sql                 # Admin Override Procedures
│   │   └── Procedure: sp_UpdateAssetLocation (manual location override)
│   │
│   ├── 07_RBAC_Seeding.sql                  # User Accounts & Roles
│   │   └── Inserts: planner, dispatch, manager, guest users
│   │   └── Updates: admin user role to SuperAdmin
│   │
│   ├── master_script.sql                    # 🛠️ ONE-CLICK BUILD SCRIPT (Local Docker)
│   │   └── Executes: All above scripts in order
│   │   └── Creates: Complete database with 1M+ rows and RBAC users
│   │
│   └── master_script_azure.sql             # ☁️ AZURE-COMPATIBLE BUILD SCRIPT
│       └── Same as master_script.sql but Azure-compatible
│       └── Removes: Filegroups, USE statements, database creation
│       └── Use this for Azure SQL Database deployment
│
├── publish/                                 # Distribution Package
│   ├── linux-x64/                          # Linux executable
│   ├── win-x64/                            # Windows executable
│   ├── osx-x64/                            # macOS Intel executable
│   ├── osx-arm64/                          # macOS Apple Silicon executable
│   ├── scripts/                            # Setup & utility scripts
│   │   ├── setup.sh / setup.bat            # Connection string configuration
│   │   ├── setup_azure_firewall.sh / .bat  # Auto-add IP to Azure firewall
│   │   ├── test_connection.sh / .bat      # Test database connection
│   │   ├── run_linux.sh / run_windows.bat  # Application launchers
│   │   └── DIAGNOSE_WINDOWS.bat            # Windows diagnostics
│   ├── docs/                               # Documentation
│   │   ├── README_DISTRIBUTION.md          # Distribution guide
│   │   ├── AZURE_FIREWALL_SETUP.md         # Azure firewall setup
│   │   ├── TROUBLESHOOTING_LINUX.md        # Linux troubleshooting
│   │   ├── TROUBLESHOOTING_WINDOWS.md      # Windows troubleshooting
│   │   └── WINDOWS_FIREWALL_FIX.md         # Windows firewall issues
│   └── zips/                               # Ready-to-distribute packages
│       ├── FleetCore-Linux.zip
│       ├── FleetCore-Windows.zip
│       ├── FleetCore-macOS-Intel.zip
│       └── FleetCore-macOS-AppleSilicon.zip
│
└── src/                                     # Application Layer (.NET 9)
    ├── FleetCore.ConsoleApp/                # UI & Presentation Layer
    │   ├── Program.cs                       # Main Entry Point
    │   │   └── Database connection check
    │   │   └── Login loop with logout support
    │   │   └── RBAC menu rendering
    │   │   └── Factory Pattern: Switch between LINQ/SP services
    │   │   └── Handlers: FleetAssets, TripManagement, GateOperations, AdminTools
    │   │
    │   ├── Services/                        # Business Logic Layer (BLL)
    │   │   ├── ITripService.cs              # Service Interface (Contract)
    │   │   │   └── Methods: GetQualifiedDrivers, GetAvailableVehicles
    │   │   │   └── Methods: CreateTrip, DepartTrip, CompleteTrip
    │   │   │   └── Methods: UpdateDriverLocation, UpdateVehicleLocation
    │   │   │
    │   │   ├── LinqTripService.cs           # Planner Logic (EF Core + LINQ)
    │   │   │   └── Client-side filtering by location and certifications
    │   │   │   └── Manual driver/vehicle selection
    │   │   │   └── Creates Scheduled or InProgress trips
    │   │   │   └── Updates asset locations on trip completion
    │   │   │
    │   │   └── SpTripService.cs            # Dispatcher Logic (SQL Stored Procedures)
    │   │       └── Calls: sp_AssignTrip (auto-assignment)
    │   │       └── Calls: sp_CompleteTrip
    │   │       └── Calls: sp_UpdateAssetLocation
    │   │
    │   └── UI/                              # User Interface Modules
    │       ├── LoginUI.cs                   # Authentication Portal
    │       │   └── Secure login with username/password
    │       │   └── Simulation mode (quick role selection)
    │       │   └── Back navigation support
    │       │
    │       ├── UserInterface.cs             # Core UI Helpers (Spectre.Console)
    │       │   └── Menus: ShowHeader, ShowAssetMenu, ShowTripMenu
    │       │   └── Tables: ShowDrivers, ShowVehicles, ShowTripHistory (paginated)
    │       │   └── Selection: SelectVehicleType, SelectDriver, SelectVehicle, etc.
    │       │   └── Utilities: ShowSpinner, Success, Error, Info, Warning
    │       │   └── Generic: SelectEntity<T> for code reusability
    │       │
    │       ├── AssetManagerUI.cs           # Fleet Asset Management
    │       │   └── Add Driver/Vehicle
    │       │   └── Archive Driver/Vehicle (soft delete)
    │       │
    │       ├── UserManagerUI.cs            # User Management (SuperAdmin)
    │       │   └── Create new users with role assignment
    │       │   └── View all system users
    │       │
    │       └── AuditLogUI.cs              # Audit Log Viewer
    │           └── Display last 20 audit entries
    │           └── Color-coded action types
    │
    └── FleetCore.DataAccess/                # Data Access Layer
        ├── Context.cs                       # EF Core DbContext
        │   └── Connection string configuration
        │   └── Trigger awareness: HasTrigger() for Driver, Vehicle, Trip
        │   └── Composite key mappings
        │   └── DbSet definitions for all entities
        │
        └── Models.cs                        # C# Entity Models
            └── Driver (with CurrentLocation)
            └── Vehicle (with CurrentLocation)
            └── Trip (with StartLocation, Destination)
            └── AdminUser (with Role, PasswordHash)
            └── AuditLog (with TableName, Action, OldValue, NewValue)
            └── Certification, DriverCertification, VehicleTypeRequirement
            └── VehicleType

Key File Descriptions

SQL Files:

master_script.sql: Single script to build entire database. Run this for fresh setup.
01_Schema_Creation.sql: Defines all tables, partitions, constraints, and indexes.
02_StoredProcedures.sql: Core business logic including location-aware sp_AssignTrip.
03_Triggers.sql: Soft delete and audit automation.
04_Views.sql: Reporting views and analytics procedures.
05_Data_Seeding.sql: Generates 1M+ test rows with realistic data.
06_ManualUpdates.sql: Admin override procedures for manual location updates.
07_RBAC_Seeding.sql: Creates test users for each role.

C# Files:

Program.cs: Main application orchestrator. Handles login, menu routing, and service switching.
ITripService.cs: Service contract enabling Factory Pattern.
LinqTripService.cs: LINQ-based business logic for Planner persona.
SpTripService.cs: Stored Procedure-based business logic for Dispatcher persona.
Context.cs: EF Core configuration with trigger awareness.
Models.cs: C# entity definitions matching database schema.
UserInterface.cs: Centralized UI helpers using Spectre.Console.
LoginUI.cs: Authentication and role selection.
AssetManagerUI.cs: Fleet asset CRUD operations.
UserManagerUI.cs: User management for SuperAdmin.
AuditLogUI.cs: Audit log viewing interface.

🛠️ Step-by-Step Execution Guide

Prerequisites

Docker installed and running (for local deployment)
.NET 9 SDK installed (for building from source)
SQL Server 2022 (via Docker) or Azure SQL Database (for cloud deployment)
Kubernetes (optional, for extra credit deployment)
Azure Account (optional, for cloud deployment - student accounts work great!)

1. Database Initialization (Standard Mode - Docker)

Step 1.1: Start SQL Server Container

sudo docker run -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=FleetCoreStrongPass123!" \
   -p 1433:1433 --name sql_fleetcore \
   -d mcr.microsoft.com/mssql/server:2022-latest

Step 1.2: Wait for SQL Server to Boot Wait approximately 15-20 seconds for SQL Server to fully initialize. You can check with:

sudo docker logs sql_fleetcore

Look for: SQL Server is now ready for client connections.

Step 1.3: Execute Master Script

cat sql/master_script.sql | sudo docker exec -i sql_fleetcore /opt/mssql-tools18/bin/sqlcmd \
   -S localhost -U sa -P 'FleetCoreStrongPass123!' -C

Expected Output:

Database 'FleetCoreDB' created.
Tables created successfully.
Stored procedures created.
Triggers created.
Data Seeding Complete.
RBAC Users Seeded.

Note: The master script creates 1M+ rows, so execution may take 2-5 minutes.

Step 1.4: Verify Database (Optional)

sudo docker exec -it sql_fleetcore /opt/mssql-tools18/bin/sqlcmd \
   -S localhost -U sa -P 'FleetCoreStrongPass123!' -C \
   -Q "USE FleetCoreDB; SELECT COUNT(*) FROM Driver; SELECT COUNT(*) FROM Vehicle; SELECT COUNT(*) FROM Trip;"

2. Launching the Application

Step 2.1: Navigate to Source Directory

cd src

Step 2.2: Run the Application

dotnet run --project FleetCore.ConsoleApp

Step 2.3: Application Startup Flow

Header Display: ASCII art "FLEETCORE" logo
Database Connection Check:
- Spinner: "Connecting to FleetCoreDB..."
- Success: "✅ Database connection successful!"
- Failure: "❌ Database connection failed!" with troubleshooting tips
Self-Healing: Automatically ensures default admin user exists
Login Screen: Choose Secure Login or Simulation Mode

3. Testing Workflows (RBAC Demo)

Scenario A: The Planner (Manual Scheduling - LINQ Mode)

Steps:

Login:
- Username: planner
- Password: plan123
- Or use Simulation Mode → Select "Planner"
Verify Mode: Main menu shows "LINQ: Manual Planner"
Create Trip:
- Navigate: Trip Management → Create New Trip
- Select required certifications (e.g., "Hazmat", "Refrigerated")
- System filters drivers/vehicles by:
  - Location (matching Start Location city)
  - Certifications (all required)
  - Availability status
- Manually select a driver from filtered list
- Manually select a vehicle from filtered list
- Enter Start Location: "Warehouse A, Lahore"
- Enter Destination: "Distribution Center, Karachi"
- Enter Departure Time: 2024-12-25 10:00:00 (future date)
Result: Trip created with Status = 'Scheduled'
Test Access Control: Try to access Admin Tools → Should show "Access Denied"

Scenario B: The Dispatcher (Auto-Assign - SP Mode)

Steps:

Login:
- Username: dispatch
- Password: disp123
- Note: Dispatcher role auto-assigns to SP mode
Verify Mode: Main menu shows "Stored Procedures: Auto-Dispatcher"
Create Trip:
- Navigate: Trip Management → Create New Trip
- Select Vehicle Type (e.g., "Truck")
- Enter Start Location: "Warehouse B, Lahore"
- Enter Destination: "Customer Site, Islamabad"
- Note: Driver/Vehicle selection prompts appear but are ignored
- System calls sp_AssignTrip which:
  - Parses "Lahore" from Start Location
  - Auto-selects first available vehicle in Lahore
  - Auto-selects first available competent driver in Lahore
  - Creates trip with Status = 'InProgress'
Result: Trip auto-assigned and immediately active

Scenario C: The SuperAdmin (Full Control)

Steps:

Login:
- Username: admin
- Password: admin123
Verify Access: All menu options visible including:
- User Management
- Admin Tools
- Switch Persona
Create New User:
- Navigate: User Management → Create New User
- Enter: Username, Password, Role (dropdown), Email
- Confirm creation
Manage Assets:
- Navigate: Admin Tools → Fleet Asset Manager
- Add new driver or vehicle
- Archive (soft delete) existing assets
View Audit Logs:
- Navigate: Admin Tools → View Audit Logs
- See last 20 system changes with color-coded actions
Switch Persona:
- Navigate: Switch Persona
- Toggle between LINQ and SP modes
- Verify menu updates to reflect current mode

Scenario D: The Viewer (Read-Only Access)

Steps:

Login:
- Username: guest
- Password: guest123
Verify Menu: Only "Fleet Assets" and "Trip Management (History Only)" visible
View Assets:
- Navigate: Fleet Assets → View Drivers or View Vehicles
- Can view all drivers/vehicles (active and inactive)
- Pagination available (Next/Previous page)
View Trip History:
- Navigate: Trip Management → View Trip History
- Can filter by status: Scheduled, InProgress, Completed
- Pagination available
Test Restrictions:
- Try to access Gate Operations → "Access Denied"
- Try to access Admin Tools → "Access Denied"

Scenario E: Trip Lifecycle Management

Steps:

Login as Planner or Dispatcher
Create a Scheduled Trip (as in Scenario A)
Depart Trip:
- Navigate: Gate Operations → Depart Scheduled Trip
- Select the scheduled trip from list
- System updates:
  - Trip Status = 'InProgress'
  - DepartureTime = GETDATE()
  - Driver/Vehicle Status = 'OnTrip'
Complete Trip:
- Navigate: Gate Operations → Complete Active Trip
- Select the in-progress trip from list
- Enter Arrival Time: 2024-12-25 14:30:00 (after departure)
- System updates:
  - Trip Status = 'Completed'
  - ArrivalTime set
  - Driver/Vehicle Status = 'Available'
  - Driver/Vehicle CurrentLocation = Destination City (parsed from Destination)

Scenario F: Manual Location Override (FleetManager)

Steps:

Login as FleetManager:
- Username: manager
- Password: manage123
Update Asset Location:
- Navigate: Fleet Assets → Update Asset Location
- Select asset type: Driver or Vehicle
- Enter Asset ID: 1
- Enter New Location: "Karachi" or "Office, Karachi"
- System calls sp_UpdateAssetLocation or LINQ update
- Location updated (city parsed if address format used)

4. Extra Credit: High Availability (Kubernetes)

Prerequisites:

Minikube installed and running
kubectl configured

Step 4.1: Build Custom Docker Image

# Set Docker environment to Minikube
eval $(minikube docker-env)

# Build custom SQL Server image with master_script.sql
docker build -f k8s/custom-image/Dockerfile -t fleetcore-sql-ha:v1 .

Step 4.2: Apply Kubernetes Manifests

# Create Secret for SQL Server credentials
kubectl apply -f k8s/custom-image/secret.yaml

# Deploy SQL Server (3 replicas for HA)
kubectl apply -f k8s/custom-image/deployment.yaml

# Create Service (ClusterIP)
kubectl apply -f k8s/custom-image/service.yaml

Step 4.3: Wait for Pods to be Ready

# Watch pod status
kubectl get pods -w

# Expected: 3 pods in "Running" state
# Wait for all pods to show "1/1 Ready"

Step 4.4: Port Forward Service

# Terminal 1: Port forward SQL Server service
kubectl port-forward svc/mssql-custom-service 31434:1433

# Keep this terminal open

Step 4.5: Update Connection String Edit src/FleetCore.DataAccess/Context.cs:

optionsBuilder.UseSqlServer("Server=localhost,31434;Database=FleetCoreDB;User Id=sa;Password=FleetCoreStrongPass123!;TrustServerCertificate=True;");

Step 4.6: Run Application

# Terminal 2: Run the application
cd src
dotnet run --project FleetCore.ConsoleApp

Step 4.7: Verify Kubernetes Deployment

# Check pod logs
kubectl logs <pod-name>

# Check service endpoints
kubectl get endpoints mssql-custom-service

# Scale deployment (test HA)
kubectl scale deployment mssql-custom --replicas=5

Note: The Kubernetes deployment is local-only (Minikube). For cloud deployment, see the Azure SQL Database section below.

5. Azure SQL Database Deployment (Cloud)

Deploy FleetCore to Azure SQL Database for cloud-based access from anywhere.

Step 5.1: Create Azure SQL Database

Go to Azure Portal
Create a resource → Search "SQL Database" → Create
Configure:
- Resource Group: Create new (e.g., "FleetCore-RG")
- Database name: FleetCoreDB
- Server: Create new
  - Server name: fleetcore-sql-[yourname] (must be globally unique)
  - Location: Choose closest region
  - Authentication: SQL authentication
  - Server admin login: fleetcoreadmin (or your choice)
  - Password: Create strong password (save it!)
- Compute + storage: Basic tier (for student accounts) or Serverless
- Backup storage redundancy: Locally-redundant (LRS) - cheapest option
Click "Review + create" → "Create"
Wait for deployment (2-3 minutes)

Step 5.2: Configure Firewall

In SQL Database resource → "Set server firewall"
Click "Add client IPv4 address" (adds your current IP)
Click "Save"
For multiple devices: Add each device's public IP (find at https://whatismyipaddress.com/)

Step 5.3: Run Master Script

Option A: Azure Query Editor (Easiest)

Go to SQL Database → "Query editor (preview)"
Login with your credentials
Copy contents of sql/master_script_azure.sql
Paste and click "Run"

Option B: Azure CLI

# Install Azure CLI first: https://docs.microsoft.com/en-us/cli/azure/install-azure-cli
az login
az sql db execute \
    --resource-group FleetCore-RG \
    --server fleetcore-sql-[yourname] \
    --name FleetCoreDB \
    --file-path sql/master_script_azure.sql

Note: master_script_azure.sql is Azure-compatible (no filegroups, no USE statements).

Step 5.4: Configure Application

Option A: Environment Variable

export FLEETCORE_DB_CONNECTION="Server=tcp:fleetcore-sql-[yourname].database.windows.net,1433;Initial Catalog=FleetCoreDB;Persist Security Info=False;User ID=fleetcoreadmin;Password=YourPassword;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;"

Option B: Config File

Run setup.sh (Linux/macOS) or setup.bat (Windows)
Choose "Azure SQL Database" option
Enter your connection details
Creates config.txt in executable directory

Step 5.5: Run Application

cd src
dotnet run --project FleetCore.ConsoleApp

The app will connect to Azure SQL Database automatically!

Step 5.6: Automatic IP Management

Use the automatic firewall setup scripts:

Linux/macOS: ./scripts/setup_azure_firewall.sh
Windows: scripts\setup_azure_firewall.bat

These scripts automatically detect your IP and add it to Azure firewall (requires Azure CLI).

6. Creating Executables (Distribution)

Build self-contained executables for distribution:

Step 6.1: Build Executables

cd src

# Linux x64
dotnet publish FleetCore.ConsoleApp/FleetCore.ConsoleApp.csproj \
    -c Release -r linux-x64 --self-contained true \
    -p:PublishSingleFile=true \
    -o ../publish/linux-x64

# Windows x64
dotnet publish FleetCore.ConsoleApp/FleetCore.ConsoleApp.csproj \
    -c Release -r win-x64 --self-contained true \
    -p:PublishSingleFile=true \
    -o ../publish/win-x64

# macOS Intel
dotnet publish FleetCore.ConsoleApp/FleetCore.ConsoleApp.csproj \
    -c Release -r osx-x64 --self-contained true \
    -p:PublishSingleFile=true \
    -o ../publish/osx-x64

# macOS Apple Silicon
dotnet publish FleetCore.ConsoleApp/FleetCore.ConsoleApp.csproj \
    -c Release -r osx-arm64 --self-contained true \
    -p:PublishSingleFile=true \
    -o ../publish/osx-arm64

Step 6.2: Distribution Package Structure

The publish/ folder contains:

Platform folders (linux-x64/, win-x64/, etc.) - Executables
scripts/ - Setup and utility scripts
docs/ - Documentation
zips/ - Ready-to-distribute zip files

Step 6.3: Distribution

Option A: GitHub Releases (Recommended)

Create a GitHub Release
Upload zip files from publish/zips/ as release assets
Users download from Releases page
Extract and follow setup instructions

Option B: Direct Distribution

Extract zip from publish/zips/ for target platform
Share the zip file
Recipient runs scripts/setup.sh or scripts/setup.bat
Adds their IP to Azure SQL Database firewall
Runs the executable

See publish/README.md for complete distribution guide.

🛡️ Validation & Safety Checks

The application implements comprehensive input validation and safety mechanisms to ensure data integrity and prevent errors.

Input Validation

1. Location Format Validation:

Preferred Format: "Address, City" (e.g., "Warehouse A, Lahore")
Fallback Mode: If no comma detected, entire string treated as city name
Warning Message: Displays format requirement if non-standard format detected
City Parsing: Both LINQ and SP modes parse city from address string
- LINQ: GetCityFromAddress() helper method
- SP: CTE-based parsing in sp_AssignTrip and sp_CompleteTrip

2. Date Logic Validation:

Departure Time: Must be in the future (validated against DateTime.Now)
Arrival Time: Must be after DepartureTime
Error Messages: Clear prompts indicating date requirements
Format: Accepts standard date/time formats

3. Numeric Input Validation:

ID Inputs: All ID fields validated as integers
Error Handling: Non-numeric input rejected with clear error message
Range Checks: Validates IDs exist in database before operations

4. Username Uniqueness:

Check: Before creating new user, verifies username doesn't exist
Error: "Username already exists" if duplicate found
Database Constraint: UNIQUE constraint on AdminUser.Username

5. Certification Selection:

Required: At least one certification must be selected for trip creation
Validation: System verifies selected certifications exist and are valid

6. Trip Status Validation:

Depart Trip: Only trips with Status = 'Scheduled' can be departed
Complete Trip: Only trips with Status = 'InProgress' can be completed
Error Messages: Clear indication if invalid status selected

Safety Mechanisms

1. Self-Healing Database:

Startup Check: On application startup, verifies default admin user exists
Auto-Repair: If missing, automatically inserts default admin user
Method: EnsureAdminExists() in Program.cs
SQL: Uses SET IDENTITY_INSERT ON to insert with specific ID

2. Soft Delete (Data Preservation):

Implementation: INSTEAD OF DELETE triggers on Driver and Vehicle tables
Behavior: Physical deletion prevented, sets IsActive = 0 and Status = 'Archived'
Benefit: Preserves historical trip data integrity
Triggers: trg_SoftDeleteDriver, trg_SoftDeleteVehicle

3. Transactional Safety:

SP Mode: sp_AssignTrip uses transactions to prevent double-booking
Locking: Row-level locking ensures atomic asset assignment
Rollback: On error, all changes rolled back

4. Referential Integrity:

Foreign Keys: All relationships enforced at database level
Cascade Rules: Appropriate CASCADE/SET NULL behaviors
Validation: EF Core validates relationships before save

5. Connection Resilience:

Startup Check: Database connection verified before application starts
Error Handling: Clear error messages with troubleshooting tips
Graceful Exit: Application exits cleanly if database unavailable

6. Role-Based Access Guards:

Menu Level: Options hidden from unauthorized roles
Function Level: Additional runtime checks before sensitive operations
Error Messages: "Access Denied" for unauthorized attempts

🔐 Backend Mechanisms (Admin & Audit)

AdminUser Table

Purpose: Stores system user credentials and role assignments for RBAC.

Schema:

AdminUserID (INT, Primary Key, Identity)
Username (VARCHAR(50), UNIQUE, NOT NULL)
PasswordHash (VARCHAR(255), NOT NULL) - Note: In production, use proper hashing
Role (VARCHAR(20), NOT NULL) - Values: SuperAdmin, Planner, Dispatcher, FleetManager, Viewer
ContactEmail (VARCHAR(100), NULLABLE)

Usage:

Login Authentication: LoginUI.cs queries this table for username/password match
Session Context: Program.cs maintains _currentUser for RBAC checks
User Management: UserManagerUI.cs allows SuperAdmin to create new users

Default Users (Seeded):

admin / admin123 → SuperAdmin
planner / plan123 → Planner
dispatch / disp123 → Dispatcher
manager / manage123 → FleetManager
guest / guest123 → Viewer

AuditLog Table

Purpose: Tracks all critical changes to the system for compliance and debugging.

Schema:

LogID (BIGINT, Primary Key, Identity)
TableName (VARCHAR(50), NOT NULL) - Table that was modified
RecordID (BIGINT, NOT NULL) - ID of the modified record
Action (VARCHAR(20), NOT NULL) - INSERT, UPDATE, or DELETE
OldValue (NVARCHAR(MAX), NULLABLE) - JSON representation of old state
NewValue (NVARCHAR(MAX), NULLABLE) - JSON representation of new state
ChangedBy (INT, NULLABLE) - Foreign Key to AdminUser.AdminUserID
ChangeDate (DATETIME, NOT NULL, DEFAULT GETDATE())

Partitioning:

Partitioned by Year using pf_AuditDate partition function
Improves query performance for recent vs historical logs

Trigger Integration:

trg_AuditTrip: Automatically logs all Trip table changes
Future: Can be extended to other tables via additional triggers

Access:

UI: AuditLogUI.cs displays last 20 entries

SQL: Direct queries for DB admins:

SELECT * FROM AuditLog 
WHERE TableName = 'Trip' 
ORDER BY ChangeDate DESC;

DB Admin Guide: Viewing Audit Logs

Query Recent Changes:

USE FleetCoreDB;

-- Last 50 audit entries
SELECT TOP 50 
    LogID,
    TableName,
    Action,
    RecordID,
    ChangedBy,
    ChangeDate,
    OldValue,
    NewValue
FROM AuditLog
ORDER BY ChangeDate DESC;

Filter by Table:

-- All Trip changes
SELECT * FROM AuditLog 
WHERE TableName = 'Trip'
ORDER BY ChangeDate DESC;

-- All Driver changes
SELECT * FROM AuditLog 
WHERE TableName = 'Driver'
ORDER BY ChangeDate DESC;

Filter by User:

-- Changes made by specific user
SELECT * FROM AuditLog 
WHERE ChangedBy = 1  -- AdminUserID
ORDER BY ChangeDate DESC;

Filter by Action Type:

-- All INSERTs
SELECT * FROM AuditLog 
WHERE Action = 'INSERT'
ORDER BY ChangeDate DESC;

-- All UPDATEs
SELECT * FROM AuditLog 
WHERE Action = 'UPDATE'
ORDER BY ChangeDate DESC;

Date Range Queries:

-- Changes in last 24 hours
SELECT * FROM AuditLog 
WHERE ChangeDate >= DATEADD(DAY, -1, GETDATE())
ORDER BY ChangeDate DESC;

-- Changes in specific date range
SELECT * FROM AuditLog 
WHERE ChangeDate BETWEEN '2024-12-01' AND '2024-12-31'
ORDER BY ChangeDate DESC;

Join with AdminUser for Username:

-- Audit log with usernames
SELECT 
    AL.LogID,
    AL.TableName,
    AL.Action,
    AL.RecordID,
    AU.Username AS ChangedByUser,
    AL.ChangeDate,
    AL.OldValue,
    AL.NewValue
FROM AuditLog AL
LEFT JOIN AdminUser AU ON AL.ChangedBy = AU.AdminUserID
ORDER BY AL.ChangeDate DESC;

🔧 Troubleshooting & FAQ

Common Issues

1. Database Connection Failed

Symptom: "❌ Database connection failed!" on startup
Solutions:
- Verify Docker container is running: sudo docker ps
- Check container logs: sudo docker logs sql_fleetcore
- Verify port 1433 is not in use: sudo netstat -tulpn | grep 1433
- Restart container: sudo docker restart sql_fleetcore
- Check connection string in Context.cs

2. "Procedure or function has too many arguments"

Symptom: Error when creating trip in SP mode
Cause: Mismatch between C# parameters and SQL procedure signature
Solution: Verify SpTripService.cs calls match sp_AssignTrip parameters

3. "Foreign Key constraint violation"

Symptom: Error when creating trip or user
Cause: Referenced record doesn't exist (e.g., AdminUserID = 1 missing)
Solution: Run EnsureAdminExists() or re-run master_script.sql

4. "Could not save changes because the target table has database triggers"

Symptom: EF Core save error
Cause: EF Core not aware of SQL triggers
Solution: Verify Context.cs has HasTrigger() configuration for Driver, Vehicle, Trip tables

5. No drivers/vehicles found for trip creation

Symptom: Empty lists when creating trip
Cause: No assets match location/certification requirements
Solution:
- Check asset CurrentLocation matches Start Location city
- Verify drivers have required certifications
- Check asset Status = 'Available' and IsActive = 1

6. Viewer cannot access Trip Management

Symptom: Menu option missing or "Access Denied"
Cause: Viewer role restrictions
Solution: This is expected behavior. Viewers can only view trip history, not create trips.

7. Kubernetes port-forward connection refused

Symptom: Cannot connect to K8s SQL service
Solutions:
- Verify pods are running: kubectl get pods
- Check service endpoints: kubectl get endpoints mssql-custom-service
- Verify port-forward is active: kubectl port-forward svc/mssql-custom-service 31434:1433
- Check firewall rules

Frequently Asked Questions

Q: Can I use this with an existing SQL Server instance?
A: Yes. Update the connection string in Context.cs to point to your SQL Server. Run master_script.sql against your database.

Q: How do I reset the database?
A: Stop the container, remove it, and start fresh:

sudo docker stop sql_fleetcore
sudo docker rm sql_fleetcore
# Then follow Step 1 of Execution Guide

Q: Can I deploy this to Azure?
A: Yes! See "Step 5: Azure SQL Database Deployment" above. The process includes:

Create Azure SQL Database (Basic tier works for students)
Configure firewall rules (add your IP)
Run sql/master_script_azure.sql (Azure-compatible version)
Configure connection string (use setup.sh/setup.bat or environment variable)
Run the application - it will connect to Azure automatically

Note: Azure SQL Database doesn't support filegroups, so use master_script_azure.sql instead of master_script.sql.

Q: How do I add more test data?
A: Modify sql/05_Data_Seeding.sql to generate more rows, or use the application's Asset Manager to add drivers/vehicles manually.

Q: Can I change the default admin password?
A: Yes. Update it in sql/05_Data_Seeding.sql and sql/07_RBAC_Seeding.sql, then re-run the master script.

Q: How does location-aware assignment work?
A: The system parses the city name from the address string (text after the last comma). It then matches assets where CurrentLocation equals that city. Both LINQ and SP modes use this logic.

Q: What's the difference between Available and Active?
A:

Active (IsActive): Whether the asset is currently in the system (not archived)
Available (Status): Whether the asset is ready for assignment (not on a trip)

Q: Can I use this without Docker?
A: Yes. Options:

Install SQL Server locally - Update connection string, run master_script.sql
Use Azure SQL Database - See "Step 5: Azure SQL Database Deployment"
Use existing SQL Server instance - Update connection string, run master script

Q: How do I view the ERD diagram?
A: Open docs/phase_2/FleetCore_Phase2_ERD.puml in a PlantUML viewer or VS Code with PlantUML extension.

Q: What .NET version is required?
A: .NET 9 SDK. The project targets net9.0.

Q: Can I extend this system?
A: Yes. The architecture is modular:

Add new services by implementing ITripService
Add new UI components in UI/ folder
Add new stored procedures in sql/02_StoredProcedures.sql
Add new roles by updating RBAC logic in Program.cs

Q: How do I create executables for distribution?
A: See "Step 6: Creating Executables" above. The publish/ folder contains:

Self-contained executables for all platforms (Linux, Windows, macOS)
Setup scripts for easy configuration
Complete documentation
Ready-to-distribute zip files

Q: How does the connection string work?
A: The application checks in this order:

Environment variable: FLEETCORE_DB_CONNECTION
Config file: config.txt (in executable directory)
Default: Local Docker SQL Server

Use scripts/setup.sh or scripts/setup.bat to create config.txt.

Q: Do I need to add IPs manually for Azure?
A: No! Use the automatic scripts:

scripts/setup_azure_firewall.sh (Linux/macOS)
scripts/setup_azure_firewall.bat (Windows)

These automatically detect your IP and add it to Azure firewall (requires Azure CLI).

📝 License & Credits

Project: FleetCore - Logistics Management System
Technology Stack: .NET 9, SQL Server 2022, Entity Framework Core, Spectre.Console
Architecture: Factory Pattern, Repository Pattern, RBAC
Database Features: Partitioning, CTEs, Triggers, UDFs, Indexing

🎯 Summary

FleetCore is a production-ready logistics management system demonstrating:

✅ Hybrid business logic (LINQ + Stored Procedures)
✅ Location-aware asset assignment
✅ Complete trip lifecycle management
✅ Role-based access control (5 roles)
✅ Rich console UI with Spectre.Console
✅ Advanced SQL features (partitioning, triggers, CTEs, UDFs)
✅ Comprehensive audit logging
✅ Self-healing database
✅ Azure SQL Database deployment (cloud-ready)
✅ Cross-platform executables (Linux, Windows, macOS)
✅ Configuration management (environment variables + config files)
✅ Kubernetes deployment (extra credit)

Ready to use, fully documented, cloud-deployable, and extensible.

Name		Name	Last commit message	Last commit date
Latest commit History 48 Commits
docs		docs
k8s		k8s
publish		publish
sql		sql
src		src
.gitignore		.gitignore
Dockerfile		Dockerfile
README.md		README.md
kind-config.yaml		kind-config.yaml

Folders and files

Latest commit

History

Repository files navigation

🚛 FleetCore - Logistics Management System

📦 Download Latest Release

📋 Table of Contents

🎯 Project Overview

🏛 Architecture & Design Patterns

Core Design Patterns

Technology Stack

🏛 Project Architecture & Logic

👥 Operational Personas & Workflows

1. 📅 The "Planner" Mode (LINQ - Manual Selection)

2. ⚡ The "Dispatcher" Mode (Stored Procedures - Auto-Assign)

3. ⏱️ The "Driver Admin" Mode (Lifecycle Management)

4. 🛡️ The "System Admin" Mode (RBAC)

🔒 Role-Based Access Control (RBAC) & Use Cases

Login Flow

Sample Credentials (Seeded Data)

Menu Structure by Role

Access Guards

✅ SQL Implementation Details

1. Schema & Normalization

2. Stored Procedures

3. User-Defined Functions (UDFs)

4. Triggers

5. Views

6. Common Table Expressions (CTEs)

7. Indexing Strategy

🖥️ Frontend Features & UI Components

UI Components

1. LoginUI.cs - Authentication Portal

2. UserInterface.cs - Core UI Helpers

3. AssetManagerUI.cs - Fleet Asset Management

4. UserManagerUI.cs - User Management (SuperAdmin Only)

5. AuditLogUI.cs - Audit Log Viewer

Application Flow (Program.cs)

Input Validation

📂 Repository Structure & File Guide

Key File Descriptions

🛠️ Step-by-Step Execution Guide

Prerequisites

1. Database Initialization (Standard Mode - Docker)

2. Launching the Application

3. Testing Workflows (RBAC Demo)

Scenario A: The Planner (Manual Scheduling - LINQ Mode)

Scenario B: The Dispatcher (Auto-Assign - SP Mode)

Scenario C: The SuperAdmin (Full Control)

Scenario D: The Viewer (Read-Only Access)

Scenario E: Trip Lifecycle Management

Scenario F: Manual Location Override (FleetManager)

4. Extra Credit: High Availability (Kubernetes)

5. Azure SQL Database Deployment (Cloud)

Step 5.1: Create Azure SQL Database

Step 5.2: Configure Firewall

Step 5.3: Run Master Script

Step 5.4: Configure Application

Step 5.5: Run Application

Step 5.6: Automatic IP Management

6. Creating Executables (Distribution)

Step 6.1: Build Executables

Step 6.2: Distribution Package Structure

Step 6.3: Distribution

🛡️ Validation & Safety Checks

Input Validation

Safety Mechanisms

🔐 Backend Mechanisms (Admin & Audit)

AdminUser Table

AuditLog Table

DB Admin Guide: Viewing Audit Logs

🔧 Troubleshooting & FAQ

Common Issues

Frequently Asked Questions

📝 License & Credits

🎯 Summary

About

Resources

Uh oh!

Stars

Packages