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:
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
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:
category field with ENUM values representing different budget allocation categoriesThis structure allows for more granular budget tracking and reporting by categorizing funds according to their intended purpose within each budget type.
You can send messages while the preview loads