Here's the complete database table structure for the Provincial Budget Management and PC-1 Approval Workflow system:
-- Users and Authentication Tables
CREATE TABLE users (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
email VARCHAR(255) UNIQUE NOT NULL,
password_hash VARCHAR(255) NOT NULL,
full_name VARCHAR(100) NOT NULL,
employee_id VARCHAR(50) UNIQUE NOT NULL,
role ENUM('XEN', 'SE', 'CE', 'ADMIN') NOT NULL,
department VARCHAR(100) NOT NULL,
division VARCHAR(100),
phone VARCHAR(20),
is_active BOOLEAN DEFAULT true,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
-- Provincial Budget Management
CREATE TABLE provincial_budget (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
fiscal_year VARCHAR(10) NOT NULL, -- e.g., '2024-25'
total_budget DECIMAL(15,2) NOT NULL,
allocated_budget DECIMAL(15,2) DEFAULT 0,
remaining_budget DECIMAL(15,2) GENERATED ALWAYS AS (total_budget - allocated_budget) STORED,
budget_type ENUM('DEVELOPMENT', 'MAINTENANCE', 'EMERGENCY') NOT NULL,
department VARCHAR(100) NOT NULL DEFAULT 'C&W',
status ENUM('DRAFT', 'APPROVED', 'ACTIVE', 'CLOSED') DEFAULT 'DRAFT',
approved_by UUID REFERENCES users(id),
approved_at TIMESTAMP NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
UNIQUE(fiscal_year, budget_type)
);
-- Road Segments Master Data
CREATE TABLE road_segments (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
segment_code VARCHAR(20) UNIQUE NOT NULL,
segment_name VARCHAR(200) NOT NULL,
road_type ENUM('MOTORWAY', 'HIGHWAY', 'MAIN_ROAD', 'LINK_ROAD') NOT NULL,
start_point VARCHAR(100) NOT NULL,
end_point VARCHAR(100) NOT NULL,
total_length_km DECIMAL(8,2) NOT NULL,
district VARCHAR(50) NOT NULL,
division VARCHAR(50) NOT NULL,
pci_score INTEGER CHECK (pci_score >= 0 AND pci_score <= 100),
last_pci_assessment DATE,
coordinates_start POINT, -- GPS coordinates
coordinates_end POINT,
is_active BOOLEAN DEFAULT true,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
-- PC-1 Submissions
CREATE TABLE pc1_submissions (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
pc1_number VARCHAR(20) UNIQUE NOT NULL, -- Auto-generated: PC1-YYYY-XXX
project_title VARCHAR(200) NOT NULL,
road_segment_id UUID NOT NULL REFERENCES road_segments(id),
maintenance_type ENUM('PREVENTIVE', 'CORRECTIVE', 'EMERGENCY', 'REHABILITATION') NOT NULL,
priority_level ENUM('LOW', 'MEDIUM', 'HIGH', 'CRITICAL') NOT NULL,
scope_of_work TEXT NOT NULL,
estimated_cost DECIMAL(12,2) NOT NULL CHECK (estimated_cost >= 100000),
start_date DATE NOT NULL,
end_date DATE NOT NULL,
justification TEXT NOT NULL,
current_status ENUM('DRAFT', 'SUBMITTED', 'UNDER_REVIEW_SE', 'UNDER_REVIEW_CE', 'PENDING_FINAL_APPROVAL', 'APPROVED', 'REJECTED', 'CHANGES_REQUESTED') DEFAULT 'DRAFT',
current_reviewer_id UUID REFERENCES users(id),
submitted_by UUID NOT NULL REFERENCES users(id),
submitted_at TIMESTAMP NULL,
approved_amount DECIMAL(12,2) NULL,
budget_allocation_id UUID REFERENCES provincial_budget(id),
completion_percentage INTEGER DEFAULT 0 CHECK (completion_percentage >= 0 AND completion_percentage <= 100),
actual_start_date DATE NULL,
actual_end_date DATE NULL,
actual_cost DECIMAL(12,2) NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
INDEX idx_status (current_status),
INDEX idx_priority (priority_level),
INDEX idx_submitted_by (submitted_by),
INDEX idx_road_segment (road_segment_id)
);
-- PC-1 Document Attachments
CREATE TABLE pc1_documents (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
pc1_id UUID NOT NULL REFERENCES pc1_submissions(id) ON DELETE CASCADE,
document_name VARCHAR(255) NOT NULL,
document_type ENUM('PDF', 'DOCX', 'XLSX', 'JPG', 'PNG') NOT NULL,
file_size_bytes INTEGER NOT NULL,
file_path VARCHAR(500) NOT NULL, -- Storage path/URL
uploaded_by UUID NOT NULL REFERENCES users(id),
uploaded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
is_active BOOLEAN DEFAULT true
);
-- PC-1 Approval Workflow
CREATE TABLE pc1_approvals (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
pc1_id UUID NOT NULL REFERENCES pc1_submissions(id) ON DELETE CASCADE,
reviewer_id UUID NOT NULL REFERENCES users(id),
reviewer_role ENUM('SE', 'CE', 'ADMIN') NOT NULL,
action_taken ENUM('APPROVED', 'REJECTED', 'CHANGES_REQUESTED') NOT NULL,
comments TEXT,
rejection_reason TEXT NULL,
reviewed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
next_reviewer_id UUID REFERENCES users(id),
INDEX idx_pc1_reviewer (pc1_id, reviewer_id)
);
-- PC-1 Audit Trail
CREATE TABLE pc1_audit_trail (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
pc1_id UUID NOT NULL REFERENCES pc1_submissions(id) ON DELETE CASCADE,
action_type ENUM('CREATED', 'SUBMITTED', 'APPROVED', 'REJECTED', 'CHANGES_REQUESTED', 'UPDATED', 'DOCUMENT_UPLOADED', 'FUNDS_ALLOCATED') NOT NULL,
performed_by UUID NOT NULL REFERENCES users(id),
previous_status VARCHAR(50),
new_status VARCHAR(50),
comments TEXT,
metadata JSON, -- Additional data like old/new values
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
INDEX idx_pc1_audit (pc1_id),
INDEX idx_action_date (created_at)
);
-- Budget Allocations
CREATE TABLE budget_allocations (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
pc1_id UUID NOT NULL REFERENCES pc1_submissions(id),
budget_id UUID NOT NULL REFERENCES provincial_budget(id),
allocated_amount DECIMAL(12,2) NOT NULL,
allocation_date DATE NOT NULL,
allocated_by UUID NOT NULL REFERENCES users(id),
allocation_status ENUM('ALLOCATED', 'RELEASED', 'UTILIZED', 'RETURNED') DEFAULT 'ALLOCATED',
release_date DATE NULL,
utilization_amount DECIMAL(12,2) DEFAULT 0,
remaining_amount DECIMAL(12,2) GENERATED ALWAYS AS (allocated_amount - utilization_amount) STORED,
remarks TEXT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
UNIQUE(pc1_id, budget_id)
);
-- Budget Utilization Tracking
CREATE TABLE budget_utilization (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
allocation_id UUID NOT NULL REFERENCES budget_allocations(id),
pc1_id UUID NOT NULL REFERENCES pc1_submissions(id),
utilization_date DATE NOT NULL,
amount_utilized DECIMAL(12,2) NOT NULL,
utilization_type ENUM('ADVANCE', 'PROGRESS_PAYMENT', 'FINAL_PAYMENT', 'ADJUSTMENT') NOT NULL,
bill_reference VARCHAR(100),
description TEXT,
approved_by UUID NOT NULL REFERENCES users(id),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- System Settings and Configuration
CREATE TABLE system_settings (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
setting_key VARCHAR(100) UNIQUE NOT NULL,
setting_value TEXT NOT NULL,
setting_type ENUM('STRING', 'NUMBER', 'BOOLEAN', 'JSON') DEFAULT 'STRING',
description TEXT,
is_editable BOOLEAN DEFAULT true,
updated_by UUID REFERENCES users(id),
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
-- Notifications
CREATE TABLE notifications (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
user_id UUID NOT NULL REFERENCES users(id),
pc1_id UUID REFERENCES pc1_submissions(id),
notification_type ENUM('PC1_SUBMITTED', 'PC1_APPROVED', 'PC1_REJECTED', 'CHANGES_REQUESTED', 'FUNDS_ALLOCATED', 'DEADLINE_REMINDER') NOT NULL,
title VARCHAR(200) NOT NULL,
message TEXT NOT NULL,
is_read BOOLEAN DEFAULT false,
priority ENUM('LOW', 'MEDIUM', 'HIGH') DEFAULT 'MEDIUM',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
read_at TIMESTAMP NULL,
INDEX idx_user_notifications (user_id, is_read),
INDEX idx_notification_date (created_at)
);
-- Reports and Analytics
CREATE TABLE report_templates (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
template_name VARCHAR(100) NOT NULL,
template_type ENUM('PC1_SUMMARY', 'BUDGET_UTILIZATION', 'PERFORMANCE_REPORT', 'AUDIT_REPORT') NOT NULL,
template_config JSON NOT NULL, -- Report configuration
created_by UUID NOT NULL REFERENCES users(id),
is_active BOOLEAN DEFAULT true,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- Generated Reports
CREATE TABLE generated_reports (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
template_id UUID REFERENCES report_templates(id),
report_name VARCHAR(200) NOT NULL,
report_period_start DATE,
report_period_end DATE,
file_path VARCHAR(500), -- Path to generated report file
generated_by UUID NOT NULL REFERENCES users(id),
generated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
report_data JSON -- Cached report data
);
-- Session Management
CREATE TABLE user_sessions (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
user_id UUID NOT NULL REFERENCES users(id),
session_token VARCHAR(255) UNIQUE NOT NULL,
ip_address INET,
user_agent TEXT,
expires_at TIMESTAMP NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
INDEX idx_session_token (session_token),
INDEX idx_user_session (user_id)
);-- Additional Performance Indexes
CREATE INDEX idx_pc1_status_priority ON pc1_submissions(current_status, priority_level);
CREATE INDEX idx_pc1_dates ON pc1_submissions(start_date, end_date);
CREATE INDEX idx_pc1_cost ON pc1_submissions(estimated_cost);
CREATE INDEX idx_budget_fiscal_year ON provincial_budget(fiscal_year);
CREATE INDEX idx_road_segments_district ON road_segments(district);
CREATE INDEX idx_road_segments_pci ON road_segments(pci_score);
CREATE INDEX idx_allocations_status ON budget_allocations(allocation_status);
CREATE INDEX idx_utilization_date ON budget_utilization(utilization_date);
CREATE INDEX idx_audit_trail_date ON pc1_audit_trail(created_at);
-- Full-text search indexes
CREATE FULLTEXT INDEX idx_pc1_search ON pc1_submissions(project_title, scope_of_work, justification);
CREATE FULLTEXT INDEX idx_road_segments_search ON road_segments(segment_name, start_point, end_point);-- Useful Views for Reporting and Dashboard
-- PC-1 Dashboard View
CREATE VIEW v_pc1_dashboard AS
SELECT
p.id,
p.pc1_number,
p.project_title,
p.current_status,
p.priority_level,
p.estimated_cost,
p.approved_amount,
p.start_date,
p.end_date,
p.completion_percentage,
rs.segment_name as road_segment_name,
rs.district,
rs.pci_score,
u.full_name as submitted_by_name,
u.department,
p.submitted_at,
p.created_at,
CASE
WHEN p.current_status = 'APPROVED' THEN 100
WHEN p.current_status = 'PENDING_FINAL_APPROVAL' THEN 80
WHEN p.current_status = 'UNDER_REVIEW_CE' THEN 60
WHEN p.current_status = 'UNDER_REVIEW_SE' THEN 40
WHEN p.current_status = 'SUBMITTED' THEN 20
ELSE 0
END as approval_progress_percentage
FROM pc1_submissions p
JOIN road_segments rs ON p.road_segment_id = rs.id
JOIN users u ON p.submitted_by = u.id;
-- Budget Summary View
CREATE VIEW v_budget_summary AS
SELECT
pb.fiscal_year,
pb.budget_type,
pb.total_budget,
pb.allocated_budget,
pb.remaining_budget,
COUNT(ba.id) as total_allocations,
SUM(ba.allocated_amount) as total_allocated_amount,
SUM(bu.amount_utilized) as total_utilized_amount,
(pb.total_budget - COALESCE(SUM(ba.allocated_amount), 0)) as available_budget
FROM provincial_budget pb
LEFT JOIN budget_allocations ba ON pb.id = ba.budget_id
LEFT JOIN budget_utilization bu ON ba.id = bu.allocation_id
GROUP BY pb.id, pb.fiscal_year, pb.budget_type, pb.total_budget, pb.allocated_budget, pb.remaining_budget;
-- User Workload View
CREATE VIEW v_user_workload AS
SELECT
u.id as user_id,
u.full_name,
u.role,
u.department,
COUNT(CASE WHEN p.current_status IN ('UNDER_REVIEW_SE', 'UNDER_REVIEW_CE', 'PENDING_FINAL_APPROVAL') THEN 1 END) as pending_reviews,
COUNT(CASE WHEN p.submitted_by = u.id THEN 1 END) as submitted_pc1s,
COUNT(CASE WHEN pa.reviewer_id = u.id THEN 1 END) as total_reviews_completed
FROM users u
LEFT JOIN pc1_submissions p ON (
(u.role = 'SE' AND p.current_status = 'UNDER_REVIEW_SE') OR
(u.role = 'CE' AND p.current_status = 'UNDER_REVIEW_CE') OR
(u.role = 'ADMIN' AND p.current_status = 'PENDING_FINAL_APPROVAL') OR
p.submitted_by = u.id
)
LEFT JOIN pc1_approvals pa ON pa.reviewer_id = u.id
WHERE u.is_active = true
GROUP BY u.id, u.full_name, u.role, u.department;
-- Road Maintenance Priority View
CREATE VIEW v_road_maintenance_priority AS
SELECT
rs.id,
rs.segment_code,
rs.segment_name,
rs.district,
rs.pci_score,
rs.last_pci_assessment,
COUNT(p.id) as pending_pc1_count,
SUM(p.estimated_cost) as total_estimated_cost,
CASE
WHEN rs.pci_score < 40 THEN 'CRITICAL'
WHEN rs.pci_score < 60 THEN 'HIGH'
WHEN rs.pci_score < 80 THEN 'MEDIUM'
ELSE 'LOW'
END as maintenance_priority,
DATEDIFF(CURRENT_DATE, rs.last_pci_assessment) as days_since_assessment
FROM road_segments rs
LEFT JOIN pc1_submissions p ON rs.id = p.road_segment_id
AND p.current_status NOT IN ('REJECTED', 'APPROVED')
WHERE rs.is_active = true
GROUP BY rs.id, rs.segment_code, rs.segment_name, rs.district, rs.pci_score, rs.last_pci_assessment
ORDER BY rs.pci_score ASC, days_since_assessment DESC;-- Insert Sample Data
-- Sample Users
INSERT INTO users (email, password_hash, full_name, employee_id, role, department, division, phone) VALUES
('admin@cw.gov.pk', '$2b$10$hash', 'Secretary C&W', 'SEC001', 'ADMIN', 'C&W Department', 'Punjab', '+92-42-1234567'),
('ce.lahore@cw.gov.pk', '$2b$10$hash', 'Ahmad Ali Khan', 'CE001', 'CE', 'C&W Department', 'Lahore', '+92-42-1234568'),
('se.lahore@cw.gov.pk', '$2b$10$hash', 'Sarah Khan', 'SE001', 'SE', 'C&W Department', 'Lahore', '+92-42-1234569'),
('xen.lahore1@cw.gov.pk', '$2b$10$hash', 'John Doe', 'XEN001', 'XEN', 'C&W Department', 'Lahore', '+92-42-1234570'),
('xen.lahore2@cw.gov.pk', '$2b$10$hash', 'Jane Smith', 'XEN002', 'XEN', 'C&W Department', 'Lahore', '+92-42-1234571');
-- Sample Provincial Budget
INSERT INTO provincial_budget (fiscal_year, total_budget, allocated_budget, budget_type, status) VALUES
('2024-25', 500000000.00, 150000000.00, 'MAINTENANCE', 'ACTIVE'),
('2024-25', 1000000000.00, 300000000.00, 'DEVELOPMENT', 'ACTIVE'),
('2024-25', 100000000.00, 25000000.00, 'EMERGENCY', 'ACTIVE');
-- Sample Road Segments
INSERT INTO road_segments (segment_code, segment_name, road_type, start_point, end_point, total_length_km, district, division, pci_score, last_pci_assessment) VALUES
('RS001', 'Lahore-Islamabad Motorway (KM 0-50)', 'MOTORWAY', 'Lahore', 'Sheikhupura', 50.0, 'Lahore', 'Lahore', 85, '2024-01-01'),
('RS002', 'GT Road Lahore-Gujranwala (KM 10-30)', 'HIGHWAY', 'Lahore', 'Gujranwala', 20.0, 'Lahore', 'Lahore', 65, '2023-12-15'),
('RS003', 'Ring Road Lahore (Sector A)', 'MAIN_ROAD', 'Thokar Niaz Baig', 'Saggian', 15.0, 'Lahore', 'Lahore', 45, '2023-11-20'),
('RS004', 'Canal Road Faisalabad (KM 5-15)', 'MAIN_ROAD', 'Faisalabad', 'Jaranwala', 10.0, 'Faisalabad', 'Faisalabad', 72, '2024-01-10');
-- Sample System Settings
INSERT INTO system_settings (setting_key, setting_value, setting_type, description) VALUES
('MIN_PC1_COST', '100000', 'NUMBER', 'Minimum PC-1 estimated cost in PKR'),
('MAX_FILE_SIZE_MB', '10', 'NUMBER', 'Maximum file upload size in MB'),
('PC1_NUMBER_PREFIX', 'PC1', 'STRING', 'Prefix for PC-1 number generation'),
('FISCAL_YEAR', '2024-25', 'STRING', 'Current fiscal year'),
('AUTO_APPROVAL_LIMIT', '1000000', 'NUMBER', 'Auto approval limit for emergency PC-1s');This comprehensive database schema includes:
Core Tables:
- users: User management with roles
- provincial_budget: Budget allocation and tracking
- road_segments: Road infrastructure data with PCI scores
- pc1_submissions: Main PC-1 form data
- pc1_documents: File attachments
- pc1_approvals: Approval workflow
- pc1_audit_trail: Complete audit history
Budget Management:
- budget_allocations: Fund allocation to projects
- budget_utilization: Expense tracking
- system_settings: Configurable parameters
Additional Features:
- notifications: User notifications
- report_templates: Report generation
- user_sessions: Session management
- Performance views: Dashboard and analytics
Key Features:
- ✅ Role-based access control
- ✅ Complete audit trail
- ✅ Budget tracking and allocation
- ✅ File management
- ✅ Status workflow management
- ✅ Performance indexes for scalability
- ✅ Reporting and analytics views
324d ago
CREATE TABLE provincial_budget (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
fiscal_year VARCHAR(10) NOT NULL, -- e.g., '2024-25'
total_budget DECIMAL(15,2) NOT NULL,
allocated_budget DECIMAL(15,2) DEFAULT 0,
remaining_budget DECIMAL(15,2) GENERATED ALWAYS AS (total_budget - allocated_budget) STORED,
budget_type ENUM('DEVELOPMENT', 'MAINTENANCE', 'EMERGENCY') NOT NULL,
department VARCHAR(100) NOT NULL DEFAULT 'C&W',
status ENUM('DRAFT', 'APPROVED', 'ACTIVE', 'CLOSED') DEFAULT 'DRAFT',
approved_by UUID REFERENCES users(id),
approved_at TIMESTAMP NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
UNIQUE(fiscal_year, budget_type)
); ..... add category field with enum values here as well
324d ago
Here's the updated table definition with the added category field:
CREATE TABLE provincial_budget (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
fiscal_year VARCHAR(10) NOT NULL, -- e.g., '2024-25'
total_budget DECIMAL(15,2) NOT NULL,
allocated_budget DECIMAL(15,2) DEFAULT 0,
remaining_budget DECIMAL(15,2) GENERATED ALWAYS AS (total_budget - allocated_budget) STORED,
budget_type ENUM('DEVELOPMENT', 'MAINTENANCE', 'EMERGENCY') NOT NULL,
category ENUM(
'NEW_CONSTRUCTION',
'REHABILITATION',
'PERIODIC_MAINTENANCE',
'ROUTINE_MAINTENANCE',
'EMERGENCY_REPAIRS',
'PLANNING_DESIGN',
'EQUIPMENT_PROCUREMENT',
'ADMINISTRATIVE',
'OTHER'
) NOT NULL,
department VARCHAR(100) NOT NULL DEFAULT 'C&W',
status ENUM('DRAFT', 'APPROVED', 'ACTIVE', 'CLOSED') DEFAULT 'DRAFT',
approved_by UUID REFERENCES users(id),
approved_at TIMESTAMP NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
UNIQUE(fiscal_year, budget_type, category)
);I've added:
- A
categoryfield with ENUM values representing different budget allocation categories - Updated the UNIQUE constraint to include the new category field
- Added 'OTHER' as a catch-all category for miscellaneous budget items
This structure allows for more granular budget tracking and reporting by categorizing funds according to their intended purpose within each budget type.
324d ago
