/* ========================================================================= Elikser Employee Portal V3 — Production Relational Schema (SQL Server) Target Engine : SQL Server 2022+ / Azure SQL Database Design Goals : Strict 3NF, Zero Blob-in-Row, UTC Everywhere, Engine-Level Invariants (Computed Payroll Math, Filtered Unique Indexes) ========================================================================= */ SET NOCOUNT ON; SET XACT_ABORT ON; SET ANSI_NULLS ON; SET QUOTED_IDENTIFIER ON; GO /* ------------------------------------------------------------------------- 0. LOGICAL DOMAIN SCHEMAS ------------------------------------------------------------------------- */ IF NOT EXISTS (SELECT 1 FROM sys.schemas WHERE name = 'core') EXEC('CREATE SCHEMA [core]'); IF NOT EXISTS (SELECT 1 FROM sys.schemas WHERE name = 'cfg') EXEC('CREATE SCHEMA [cfg]'); IF NOT EXISTS (SELECT 1 FROM sys.schemas WHERE name = 'hr') EXEC('CREATE SCHEMA [hr]'); IF NOT EXISTS (SELECT 1 FROM sys.schemas WHERE name = 'ats') EXEC('CREATE SCHEMA [ats]'); IF NOT EXISTS (SELECT 1 FROM sys.schemas WHERE name = 'work') EXEC('CREATE SCHEMA [work]'); IF NOT EXISTS (SELECT 1 FROM sys.schemas WHERE name = 'req') EXEC('CREATE SCHEMA [req]'); IF NOT EXISTS (SELECT 1 FROM sys.schemas WHERE name = 'pay') EXEC('CREATE SCHEMA [pay]'); IF NOT EXISTS (SELECT 1 FROM sys.schemas WHERE name = 'hub') EXEC('CREATE SCHEMA [hub]'); IF NOT EXISTS (SELECT 1 FROM sys.schemas WHERE name = 'audit') EXEC('CREATE SCHEMA [audit]'); GO /* ========================================================================= 1. [core] — IDENTITY (ENTRA SSO), RBAC, FILE POINTERS & NOTIFICATIONS ========================================================================= */ CREATE TABLE [core].[Users] ( [UserId] BIGINT IDENTITY(1,1) NOT NULL, [EntraObjectId] UNIQUEIDENTIFIER NULL, -- Microsoft Entra ID 'oid' claim [Email] NVARCHAR(256) NOT NULL, [FullName] NVARCHAR(120) NOT NULL, [IsActive] BIT NOT NULL CONSTRAINT [DF_Users_IsActive] DEFAULT (1), [IsOnboardingCompleted] BIT NOT NULL CONSTRAINT [DF_Users_Onboarded] DEFAULT (0), [LastLoginAtUtc] DATETIME2(3) NULL, [CreatedAtUtc] DATETIME2(3) NOT NULL CONSTRAINT [DF_Users_Created] DEFAULT (SYSUTCDATETIME()), [UpdatedAtUtc] DATETIME2(3) NOT NULL CONSTRAINT [DF_Users_Updated] DEFAULT (SYSUTCDATETIME()), CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED ([UserId]), CONSTRAINT [UQ_Users_Email] UNIQUE ([Email]) ); CREATE UNIQUE NONCLUSTERED INDEX [UX_Users_EntraObjectId] ON [core].[Users]([EntraObjectId]) WHERE [EntraObjectId] IS NOT NULL; CREATE TABLE [core].[Roles] ( [RoleId] INT IDENTITY(1,1) NOT NULL, [Code] VARCHAR(40) NOT NULL, -- 'ADMIN','EXECUTIVE','HR','PROJECT_LEAD','EMPLOYEE','INTERVIEWER_TECH' [Name] NVARCHAR(80) NOT NULL, [RoleType] TINYINT NOT NULL, -- 1 = PortalAccess, 2 = InterviewerStage [IsSystemRole] BIT NOT NULL CONSTRAINT [DF_Roles_System] DEFAULT (0), [IsActive] BIT NOT NULL CONSTRAINT [DF_Roles_Active] DEFAULT (1), CONSTRAINT [PK_Roles] PRIMARY KEY CLUSTERED ([RoleId]), CONSTRAINT [UQ_Roles_Code] UNIQUE ([Code]), CONSTRAINT [CK_Roles_RoleType] CHECK ([RoleType] IN (1, 2)) ); CREATE TABLE [core].[Permissions] ( [PermissionId] INT IDENTITY(1,1) NOT NULL, [ModuleCode] VARCHAR(30) NOT NULL, -- 'ATS','EMPLOYEE','ATTENDANCE','TIMESHEET','REQUEST','PAYROLL','KNOWLEDGE','SETUP' [ActionCode] VARCHAR(30) NOT NULL, -- 'VIEW','CREATE','EDIT','APPROVE','EXPORT','COMPENSATION_WRITE' [Description] NVARCHAR(200) NULL, CONSTRAINT [PK_Permissions] PRIMARY KEY CLUSTERED ([PermissionId]), CONSTRAINT [UQ_Permissions_ModuleAction] UNIQUE ([ModuleCode], [ActionCode]) ); CREATE TABLE [core].[RolePermissions] ( [RoleId] INT NOT NULL, [PermissionId] INT NOT NULL, CONSTRAINT [PK_RolePermissions] PRIMARY KEY CLUSTERED ([RoleId], [PermissionId]), CONSTRAINT [FK_RolePermissions_Role] FOREIGN KEY ([RoleId]) REFERENCES [core].[Roles]([RoleId]) ON DELETE CASCADE, CONSTRAINT [FK_RolePermissions_Perm] FOREIGN KEY ([PermissionId]) REFERENCES [core].[Permissions]([PermissionId]) ON DELETE CASCADE ); CREATE TABLE [core].[UserRoles] ( [UserId] BIGINT NOT NULL, [RoleId] INT NOT NULL, [AssignedByUserId] BIGINT NULL, [AssignedAtUtc] DATETIME2(3) NOT NULL CONSTRAINT [DF_UserRoles_At] DEFAULT (SYSUTCDATETIME()), CONSTRAINT [PK_UserRoles] PRIMARY KEY CLUSTERED ([UserId], [RoleId]), CONSTRAINT [FK_UserRoles_User] FOREIGN KEY ([UserId]) REFERENCES [core].[Users]([UserId]), CONSTRAINT [FK_UserRoles_Role] FOREIGN KEY ([RoleId]) REFERENCES [core].[Roles]([RoleId]) ); -- Single object-storage pointer table replacing 10+ legacy VARBINARY/Base64 columns CREATE TABLE [core].[Files] ( [FileId] UNIQUEIDENTIFIER NOT NULL CONSTRAINT [DF_Files_Id] DEFAULT (NEWSEQUENTIALID()), [StorageProvider] VARCHAR(20) NOT NULL CONSTRAINT [DF_Files_Provider] DEFAULT ('AZURE_BLOB'), [StorageKey] NVARCHAR(500) NOT NULL, [OriginalFileName] NVARCHAR(255) NOT NULL, [ContentType] VARCHAR(100) NOT NULL, [SizeBytes] BIGINT NOT NULL, [Sha256Hex] CHAR(64) NOT NULL, [UploadedByUserId] BIGINT NULL, [CreatedAtUtc] DATETIME2(3) NOT NULL CONSTRAINT [DF_Files_Created] DEFAULT (SYSUTCDATETIME()), CONSTRAINT [PK_Files] PRIMARY KEY CLUSTERED ([FileId]), CONSTRAINT [UQ_Files_StorageKey] UNIQUE ([StorageKey]), CONSTRAINT [CK_Files_Size] CHECK ([SizeBytes] > 0 AND [SizeBytes] <= 26214400), -- <= 25 MB CONSTRAINT [FK_Files_UploadedBy] FOREIGN KEY ([UploadedByUserId]) REFERENCES [core].[Users]([UserId]) ); CREATE TABLE [core].[Notifications] ( [NotificationId] BIGINT IDENTITY(1,1) NOT NULL, [Title] NVARCHAR(200) NOT NULL, [Body] NVARCHAR(2000) NOT NULL, [ActionUrl] NVARCHAR(300) NULL, [AudienceType] TINYINT NOT NULL, -- 1 = AllCompany, 2 = RoleGroup, 3 = SpecificUsers [TargetRoleId] INT NULL, [ValidFromUtc] DATETIME2(3) NOT NULL CONSTRAINT [DF_Notif_From] DEFAULT (SYSUTCDATETIME()), [ValidToUtc] DATETIME2(3) NULL, [IsArchived] BIT NOT NULL CONSTRAINT [DF_Notif_Archived] DEFAULT (0), [CreatedByUserId] BIGINT NULL, [CreatedAtUtc] DATETIME2(3) NOT NULL CONSTRAINT [DF_Notif_Created] DEFAULT (SYSUTCDATETIME()), [UpdatedAtUtc] DATETIME2(3) NULL, CONSTRAINT [PK_Notifications] PRIMARY KEY CLUSTERED ([NotificationId]), CONSTRAINT [CK_Notifications_Audience] CHECK ([AudienceType] IN (1, 2, 3)), CONSTRAINT [CK_Notifications_ValidRange] CHECK ([ValidToUtc] IS NULL OR [ValidToUtc] >= [ValidFromUtc]), CONSTRAINT [FK_Notifications_Role] FOREIGN KEY ([TargetRoleId]) REFERENCES [core].[Roles]([RoleId]), CONSTRAINT [FK_Notifications_Creator] FOREIGN KEY ([CreatedByUserId]) REFERENCES [core].[Users]([UserId]) ); CREATE TABLE [core].[NotificationReads] ( [NotificationId] BIGINT NOT NULL, [UserId] BIGINT NOT NULL, [IsTargeted] BIT NOT NULL CONSTRAINT [DF_NotifReads_Targeted] DEFAULT (1), [ReadAtUtc] DATETIME2(3) NULL, CONSTRAINT [PK_NotificationReads] PRIMARY KEY CLUSTERED ([UserId], [NotificationId]), CONSTRAINT [FK_NotifReads_Notif] FOREIGN KEY ([NotificationId]) REFERENCES [core].[Notifications]([NotificationId]) ON DELETE CASCADE, CONSTRAINT [FK_NotifReads_User] FOREIGN KEY ([UserId]) REFERENCES [core].[Users]([UserId]) ); CREATE NONCLUSTERED INDEX [IX_NotifReads_Unread] ON [core].[NotificationReads]([UserId], [NotificationId]) WHERE [ReadAtUtc] IS NULL; GO /* ========================================================================= 2. [cfg] — MASTER DATA, HOLIDAYS & DOCUMENT TEMPLATES ========================================================================= */ CREATE TABLE [cfg].[Departments] ( [DepartmentId] INT IDENTITY(1,1) NOT NULL, [Code] VARCHAR(20) NOT NULL, [Name] NVARCHAR(100) NOT NULL, [IsActive] BIT NOT NULL CONSTRAINT [DF_Departments_Active] DEFAULT (1), CONSTRAINT [PK_Departments] PRIMARY KEY CLUSTERED ([DepartmentId]), CONSTRAINT [UQ_Departments_Code] UNIQUE ([Code]) ); CREATE TABLE [cfg].[JobPositions] ( [JobPositionId] INT IDENTITY(1,1) NOT NULL, [DepartmentId] INT NOT NULL, [Title] NVARCHAR(120) NOT NULL, [Description] NVARCHAR(500) NULL, [IsActive] BIT NOT NULL CONSTRAINT [DF_JobPositions_Active] DEFAULT (1), CONSTRAINT [PK_JobPositions] PRIMARY KEY CLUSTERED ([JobPositionId]), CONSTRAINT [FK_JobPositions_Dept] FOREIGN KEY ([DepartmentId]) REFERENCES [cfg].[Departments]([DepartmentId]) ); -- Consolidates identical lookup tables: 1=ActivityGroup, 2=TechTool, 3=EquipmentItem, 4=EvaluationSubject CREATE TABLE [cfg].[CatalogItems] ( [CatalogItemId] INT IDENTITY(1,1) NOT NULL, [CatalogType] TINYINT NOT NULL, [Name] NVARCHAR(120) NOT NULL, [Description] NVARCHAR(500) NULL, [IsActive] BIT NOT NULL CONSTRAINT [DF_CatalogItems_Active] DEFAULT (1), CONSTRAINT [PK_CatalogItems] PRIMARY KEY CLUSTERED ([CatalogItemId]), CONSTRAINT [UQ_CatalogItems_TypeName] UNIQUE ([CatalogType], [Name]), CONSTRAINT [CK_CatalogItems_Type] CHECK ([CatalogType] IN (1, 2, 3, 4)) ); CREATE TABLE [cfg].[PublicHolidays] ( [HolidayId] INT IDENTITY(1,1) NOT NULL, [HolidayDate] DATE NOT NULL, [Name] NVARCHAR(120) NOT NULL, [OfficeLocation] NVARCHAR(60) NOT NULL CONSTRAINT [DF_Holidays_Loc] DEFAULT ('ALL'), [IsRecurringAnnual] BIT NOT NULL CONSTRAINT [DF_Holidays_Recurring] DEFAULT (0), [CreatedAtUtc] DATETIME2(3) NOT NULL CONSTRAINT [DF_Holidays_Created] DEFAULT (SYSUTCDATETIME()), CONSTRAINT [PK_PublicHolidays] PRIMARY KEY CLUSTERED ([HolidayId]), CONSTRAINT [UQ_PublicHolidays_DateLoc] UNIQUE ([HolidayDate], [OfficeLocation]) ); CREATE TABLE [cfg].[DocumentTemplates] ( [TemplateId] INT IDENTITY(1,1) NOT NULL, [TemplateCode] VARCHAR(50) NOT NULL, -- 'EMPLOYMENT_CONTRACT','NDA','RESIGNATION_LETTER','EXPERIENCE_LETTER','BANK_LETTER','PROBATION_LETTER','PAYSLIP' [DisplayName] NVARCHAR(120) NOT NULL, [FileId] UNIQUEIDENTIFIER NOT NULL, [RequiredPlaceholdersJson] NVARCHAR(1000) NOT NULL CONSTRAINT [DF_Templates_Placeholders] DEFAULT ('[]'), [IsVerified] BIT NOT NULL CONSTRAINT [DF_Templates_Verified] DEFAULT (1), [UpdatedByUserId] BIGINT NULL, [UpdatedAtUtc] DATETIME2(3) NOT NULL CONSTRAINT [DF_Templates_Updated] DEFAULT (SYSUTCDATETIME()), CONSTRAINT [PK_DocumentTemplates] PRIMARY KEY CLUSTERED ([TemplateId]), CONSTRAINT [UQ_DocumentTemplates_Code] UNIQUE ([TemplateCode]), CONSTRAINT [FK_DocumentTemplates_File] FOREIGN KEY ([FileId]) REFERENCES [core].[Files]([FileId]) ); GO /* ========================================================================= 3. [hr] — EMPLOYEES, CONTACTS/DEPENDENTS, COMPENSATION, CHECKLISTS & EXIT ========================================================================= */ CREATE TABLE [hr].[Employees] ( [EmployeeId] BIGINT IDENTITY(1,1) NOT NULL, [EmployeeCode] VARCHAR(20) NOT NULL, -- e.g. 'EMP-104' [UserId] BIGINT NOT NULL, [DepartmentId] INT NOT NULL, [JobPositionId] INT NOT NULL, [ReportsToEmployeeId] BIGINT NULL, -- Direct escalation approver (closes Lead/HR self-approval gap) [EmploymentStatus] TINYINT NOT NULL CONSTRAINT [DF_Emp_Status] DEFAULT (1), -- 1=Probation, 2=Permanent, 3=NoticePeriod, 4=Resigned, 5=Terminated [JoiningDate] DATE NOT NULL, [ProbationEndDate] DATE NULL, [LastWorkingDate] DATE NULL, [NationalIdNumber] VARCHAR(25) NULL, -- CNIC e.g. 37405-1234567-1 [DateOfBirth] DATE NULL, [Gender] VARCHAR(16) NULL, [MaritalStatus] VARCHAR(16) NULL, [BloodGroup] VARCHAR(5) NULL, [PhonePrimary] VARCHAR(30) NULL, [PhoneSecondary] VARCHAR(30) NULL, [PersonalEmail] NVARCHAR(256) NULL, [CurrentAddress] NVARCHAR(500) NULL, [PermanentAddress] NVARCHAR(500) NULL, [Qualification] NVARCHAR(200) NULL, [AnnualLeaveQuotaDays] DECIMAL(5,2) NOT NULL CONSTRAINT [DF_Emp_LeaveQuota] DEFAULT (18.00), [CreatedAtUtc] DATETIME2(3) NOT NULL CONSTRAINT [DF_Emp_Created] DEFAULT (SYSUTCDATETIME()), [UpdatedAtUtc] DATETIME2(3) NOT NULL CONSTRAINT [DF_Emp_Updated] DEFAULT (SYSUTCDATETIME()), CONSTRAINT [PK_Employees] PRIMARY KEY CLUSTERED ([EmployeeId]), CONSTRAINT [UQ_Employees_Code] UNIQUE ([EmployeeCode]), CONSTRAINT [UQ_Employees_User] UNIQUE ([UserId]), CONSTRAINT [CK_Employees_Status] CHECK ([EmploymentStatus] BETWEEN 1 AND 5), CONSTRAINT [FK_Employees_User] FOREIGN KEY ([UserId]) REFERENCES [core].[Users]([UserId]), CONSTRAINT [FK_Employees_Dept] FOREIGN KEY ([DepartmentId]) REFERENCES [cfg].[Departments]([DepartmentId]), CONSTRAINT [FK_Employees_Job] FOREIGN KEY ([JobPositionId]) REFERENCES [cfg].[JobPositions]([JobPositionId]), CONSTRAINT [FK_Employees_Manager] FOREIGN KEY ([ReportsToEmployeeId]) REFERENCES [hr].[Employees]([EmployeeId]) ); CREATE NONCLUSTERED INDEX [IX_Employees_Status_Dept] ON [hr].[Employees]([EmploymentStatus], [DepartmentId]) INCLUDE ([EmployeeCode], [JobPositionId], [JoiningDate]); CREATE TABLE [hr].[EmployeeBankAccounts] ( [BankAccountId] BIGINT IDENTITY(1,1) NOT NULL, [EmployeeId] BIGINT NOT NULL, [AccountTitle] NVARCHAR(120) NOT NULL, [BankName] NVARCHAR(100) NOT NULL, [BranchCode] VARCHAR(20) NULL, [AccountNumberIban] VARCHAR(40) NOT NULL, [AccountType] VARCHAR(20) NOT NULL CONSTRAINT [DF_Bank_Type] DEFAULT ('PERSONAL'), [BankAddress] NVARCHAR(300) NULL, [IsPrimary] BIT NOT NULL CONSTRAINT [DF_Bank_Primary] DEFAULT (1), [UpdatedAtUtc] DATETIME2(3) NOT NULL CONSTRAINT [DF_Bank_Updated] DEFAULT (SYSUTCDATETIME()), CONSTRAINT [PK_EmployeeBankAccounts] PRIMARY KEY CLUSTERED ([BankAccountId]), CONSTRAINT [FK_Bank_Employee] FOREIGN KEY ([EmployeeId]) REFERENCES [hr].[Employees]([EmployeeId]) ); CREATE UNIQUE NONCLUSTERED INDEX [UX_Bank_PrimaryPerEmployee] ON [hr].[EmployeeBankAccounts]([EmployeeId]) WHERE [IsPrimary] = 1; -- Replaces 15 sparse parent/spouse/guardian columns + separate Children table CREATE TABLE [hr].[EmployeeContacts] ( [ContactId] BIGINT IDENTITY(1,1) NOT NULL, [EmployeeId] BIGINT NOT NULL, [Relationship] VARCHAR(20) NOT NULL, -- 'FATHER','MOTHER','SPOUSE','CHILD','GUARDIAN','EMERGENCY' [FullName] NVARCHAR(120) NOT NULL, [NationalIdNumber] VARCHAR(25) NULL, [DateOfBirth] DATE NULL, [Gender] VARCHAR(16) NULL, [Phone] VARCHAR(30) NULL, [IsDependent] BIT NOT NULL CONSTRAINT [DF_Contacts_Dep] DEFAULT (0), [IsEmergencyContact] BIT NOT NULL CONSTRAINT [DF_Contacts_Emg] DEFAULT (0), CONSTRAINT [PK_EmployeeContacts] PRIMARY KEY CLUSTERED ([ContactId]), CONSTRAINT [CK_Contacts_Relationship] CHECK ([Relationship] IN ('FATHER','MOTHER','SPOUSE','CHILD','GUARDIAN','EMERGENCY')), CONSTRAINT [FK_Contacts_Employee] FOREIGN KEY ([EmployeeId]) REFERENCES [hr].[Employees]([EmployeeId]) ON DELETE CASCADE ); CREATE NONCLUSTERED INDEX [IX_Contacts_Employee] ON [hr].[EmployeeContacts]([EmployeeId]); -- Effective-dated salary history (isolates compensation from self-service profile updates) CREATE TABLE [hr].[EmployeeCompensations] ( [CompensationId] BIGINT IDENTITY(1,1) NOT NULL, [EmployeeId] BIGINT NOT NULL, [BasicSalary] DECIMAL(18,2) NOT NULL, [CurrencyCode] CHAR(3) NOT NULL CONSTRAINT [DF_Comp_Currency] DEFAULT ('PKR'), [EffectiveFrom] DATE NOT NULL, [EffectiveTo] DATE NULL, [ChangeReason] NVARCHAR(300) NOT NULL, [ApprovedByUserId] BIGINT NOT NULL, [CreatedAtUtc] DATETIME2(3) NOT NULL CONSTRAINT [DF_Comp_Created] DEFAULT (SYSUTCDATETIME()), CONSTRAINT [PK_EmployeeCompensations] PRIMARY KEY CLUSTERED ([CompensationId]), CONSTRAINT [CK_Comp_Positive] CHECK ([BasicSalary] >= 0), CONSTRAINT [CK_Comp_Dates] CHECK ([EffectiveTo] IS NULL OR [EffectiveTo] >= [EffectiveFrom]), CONSTRAINT [FK_Comp_Employee] FOREIGN KEY ([EmployeeId]) REFERENCES [hr].[Employees]([EmployeeId]), CONSTRAINT [FK_Comp_Approver] FOREIGN KEY ([ApprovedByUserId]) REFERENCES [core].[Users]([UserId]) ); CREATE UNIQUE NONCLUSTERED INDEX [UX_Comp_ActiveSalary] ON [hr].[EmployeeCompensations]([EmployeeId]) WHERE [EffectiveTo] IS NULL; CREATE TABLE [hr].[EmployeeDocuments] ( [EmployeeDocumentId] BIGINT IDENTITY(1,1) NOT NULL, [EmployeeId] BIGINT NOT NULL, [Category] VARCHAR(40) NOT NULL, -- 'CONTRACT','NDA','CV','HR_LETTER','ID_DOC','CERTIFICATE' [Title] NVARCHAR(200) NOT NULL, [FileId] UNIQUEIDENTIFIER NOT NULL, [IsVisibleToEmployee] BIT NOT NULL CONSTRAINT [DF_EmpDocs_Visible] DEFAULT (1), [UploadedByUserId] BIGINT NOT NULL, [CreatedAtUtc] DATETIME2(3) NOT NULL CONSTRAINT [DF_EmpDocs_Created] DEFAULT (SYSUTCDATETIME()), CONSTRAINT [PK_EmployeeDocuments] PRIMARY KEY CLUSTERED ([EmployeeDocumentId]), CONSTRAINT [FK_EmpDocs_Employee] FOREIGN KEY ([EmployeeId]) REFERENCES [hr].[Employees]([EmployeeId]), CONSTRAINT [FK_EmpDocs_File] FOREIGN KEY ([FileId]) REFERENCES [core].[Files]([FileId]), CONSTRAINT [FK_EmpDocs_Uploader] FOREIGN KEY ([UploadedByUserId]) REFERENCES [core].[Users]([UserId]) ); CREATE NONCLUSTERED INDEX [IX_EmpDocs_Employee] ON [hr].[EmployeeDocuments]([EmployeeId], [CreatedAtUtc] DESC); -- Consolidates 3 duplicate V2 task tables (Onboarding, Probation, Offboarding) CREATE TABLE [hr].[LifecycleTasks] ( [LifecycleTaskId] BIGINT IDENTITY(1,1) NOT NULL, [EmployeeId] BIGINT NOT NULL, [Stage] TINYINT NOT NULL, -- 1=Onboarding, 2=Probation90Day, 3=Offboarding [OwnerGroup] VARCHAR(20) NOT NULL, -- 'HR','IT','FINANCE','LEAD' [Title] NVARCHAR(250) NOT NULL, [SortOrder] SMALLINT NOT NULL CONSTRAINT [DF_LifeTask_Order] DEFAULT (1), [DueDate] DATE NULL, [Status] TINYINT NOT NULL CONSTRAINT [DF_LifeTask_Status] DEFAULT (1), -- 1=ToDo, 2=InProgress, 3=Done [CompletedByUserId] BIGINT NULL, [CompletedAtUtc] DATETIME2(3) NULL, CONSTRAINT [PK_LifecycleTasks] PRIMARY KEY CLUSTERED ([LifecycleTaskId]), CONSTRAINT [CK_LifeTask_Stage] CHECK ([Stage] IN (1, 2, 3)), CONSTRAINT [CK_LifeTask_Status] CHECK ([Status] IN (1, 2, 3)), CONSTRAINT [FK_LifeTask_Employee] FOREIGN KEY ([EmployeeId]) REFERENCES [hr].[Employees]([EmployeeId]), CONSTRAINT [FK_LifeTask_CompletedBy] FOREIGN KEY ([CompletedByUserId]) REFERENCES [core].[Users]([UserId]) ); CREATE NONCLUSTERED INDEX [IX_LifeTask_Employee_Stage] ON [hr].[LifecycleTasks]([EmployeeId], [Stage], [SortOrder]); CREATE TABLE [hr].[Resignations] ( [ResignationId] BIGINT IDENTITY(1,1) NOT NULL, [EmployeeId] BIGINT NOT NULL, [SubmittedDate] DATE NOT NULL CONSTRAINT [DF_Resign_SubDate] DEFAULT (CAST(SYSUTCDATETIME() AS DATE)), [SubmittedAtUtc] DATETIME2(3) NOT NULL CONSTRAINT [DF_Resign_SubUtc] DEFAULT (SYSUTCDATETIME()), [ProposedLastWorkingDate] DATE NOT NULL, [Reason] NVARCHAR(2000) NOT NULL, [SignatureFileId] UNIQUEIDENTIFIER NULL, [LetterPdfFileId] UNIQUEIDENTIFIER NULL, [Status] TINYINT NOT NULL CONSTRAINT [DF_Resign_Status] DEFAULT (1), -- 1=Pending, 2=Approved, 3=Rejected, 4=Withdrawn [ReviewedByUserId] BIGINT NULL, [ReviewedAtUtc] DATETIME2(3) NULL, [ReviewComment] NVARCHAR(1000) NULL, CONSTRAINT [PK_Resignations] PRIMARY KEY CLUSTERED ([ResignationId]), CONSTRAINT [CK_Resign_FutureDate] CHECK ([ProposedLastWorkingDate] >= [SubmittedDate]), CONSTRAINT [CK_Resign_Status] CHECK ([Status] BETWEEN 1 AND 4), CONSTRAINT [FK_Resign_Employee] FOREIGN KEY ([EmployeeId]) REFERENCES [hr].[Employees]([EmployeeId]), CONSTRAINT [FK_Resign_SigFile] FOREIGN KEY ([SignatureFileId]) REFERENCES [core].[Files]([FileId]), CONSTRAINT [FK_Resign_PdfFile] FOREIGN KEY ([LetterPdfFileId]) REFERENCES [core].[Files]([FileId]), CONSTRAINT [FK_Resign_Reviewer] FOREIGN KEY ([ReviewedByUserId]) REFERENCES [core].[Users]([UserId]) ); GO /* ========================================================================= 4. [ats] — RECRUITMENT PIPELINE, INTERVIEWS, SCORECARDS & QUESTION BANK ========================================================================= */ CREATE TABLE [ats].[Candidates] ( [CandidateId] BIGINT IDENTITY(1,1) NOT NULL, [JobPositionId] INT NOT NULL, [FullName] NVARCHAR(120) NOT NULL, [FatherName] NVARCHAR(120) NULL, [Email] NVARCHAR(256) NOT NULL, [Phone] VARCHAR(30) NOT NULL, [NationalIdNumber] VARCHAR(25) NULL, [City] NVARCHAR(80) NULL, [Address] NVARCHAR(500) NULL, [Degree] NVARCHAR(80) NULL, [Discipline] NVARCHAR(100) NULL, [ExperienceYears] DECIMAL(4,1) NULL, [PreviousSalary] DECIMAL(18,2) NULL, [ExpectedSalary] DECIMAL(18,2) NULL, [OfferedSalary] DECIMAL(18,2) NULL, [ProposedJoiningDate] DATE NULL, [ResumeFileId] UNIQUEIDENTIFIER NULL, [OfferContractFileId] UNIQUEIDENTIFIER NULL, [NdaFileId] UNIQUEIDENTIFIER NULL, [PipelineStage] TINYINT NOT NULL CONSTRAINT [DF_Cand_Stage] DEFAULT (1), -- 1=Applied,2=Screen,3=Interview,4=Eligible,5=OfferSent,6=Hired,7=Rejected [ConvertedEmployeeId] BIGINT NULL, [CreatedAtUtc] DATETIME2(3) NOT NULL CONSTRAINT [DF_Cand_Created] DEFAULT (SYSUTCDATETIME()), CONSTRAINT [PK_Candidates] PRIMARY KEY CLUSTERED ([CandidateId]), CONSTRAINT [CK_Cand_Stage] CHECK ([PipelineStage] BETWEEN 1 AND 7), CONSTRAINT [FK_Cand_Position] FOREIGN KEY ([JobPositionId]) REFERENCES [cfg].[JobPositions]([JobPositionId]), CONSTRAINT [FK_Cand_Resume] FOREIGN KEY ([ResumeFileId]) REFERENCES [core].[Files]([FileId]), CONSTRAINT [FK_Cand_Contract] FOREIGN KEY ([OfferContractFileId]) REFERENCES [core].[Files]([FileId]), CONSTRAINT [FK_Cand_Nda] FOREIGN KEY ([NdaFileId]) REFERENCES [core].[Files]([FileId]), CONSTRAINT [FK_Cand_Employee] FOREIGN KEY ([ConvertedEmployeeId]) REFERENCES [hr].[Employees]([EmployeeId]) ); CREATE NONCLUSTERED INDEX [IX_Candidates_Stage_Pos] ON [ats].[Candidates]([PipelineStage], [JobPositionId]); CREATE TABLE [ats].[Interviews] ( [InterviewId] BIGINT IDENTITY(1,1) NOT NULL, [CandidateId] BIGINT NOT NULL, [StageRoleId] INT NOT NULL, [ScheduledStartUtc] DATETIME2(3) NOT NULL, [DurationMinutes] SMALLINT NOT NULL CONSTRAINT [DF_Interview_Dur] DEFAULT (30), [IsOnline] BIT NOT NULL CONSTRAINT [DF_Interview_Online] DEFAULT (1), [MeetingLinkOrLocation] NVARCHAR(300) NULL, [ExternalCalendarEventId] NVARCHAR(200) NULL, [Status] TINYINT NOT NULL CONSTRAINT [DF_Interview_Status] DEFAULT (1), -- 1=Scheduled, 2=Completed, 3=Cancelled [Notes] NVARCHAR(1000) NULL, [CreatedAtUtc] DATETIME2(3) NOT NULL CONSTRAINT [DF_Interview_Created] DEFAULT (SYSUTCDATETIME()), CONSTRAINT [PK_Interviews] PRIMARY KEY CLUSTERED ([InterviewId]), CONSTRAINT [CK_Interview_Status] CHECK ([Status] IN (1, 2, 3)), CONSTRAINT [FK_Interview_Candidate] FOREIGN KEY ([CandidateId]) REFERENCES [ats].[Candidates]([CandidateId]), CONSTRAINT [FK_Interview_StageRole] FOREIGN KEY ([StageRoleId]) REFERENCES [core].[Roles]([RoleId]) ); CREATE TABLE [ats].[InterviewPanelists] ( [InterviewId] BIGINT NOT NULL, [InterviewerUserId] BIGINT NOT NULL, CONSTRAINT [PK_InterviewPanelists] PRIMARY KEY CLUSTERED ([InterviewId], [InterviewerUserId]), CONSTRAINT [FK_Panel_Interview] FOREIGN KEY ([InterviewId]) REFERENCES [ats].[Interviews]([InterviewId]) ON DELETE CASCADE, CONSTRAINT [FK_Panel_User] FOREIGN KEY ([InterviewerUserId]) REFERENCES [core].[Users]([UserId]) ); CREATE TABLE [ats].[InterviewEvaluations] ( [EvaluationId] BIGINT IDENTITY(1,1) NOT NULL, [InterviewId] BIGINT NOT NULL, [EvaluatorUserId] BIGINT NOT NULL, [SoftSkillsRating] TINYINT NOT NULL, [TechnicalRating] TINYINT NOT NULL, [ExpectedSalary] DECIMAL(18,2) NULL, [Recommendation] TINYINT NOT NULL, -- 1=MoveNextStage, 2=EligibleForHire, 3=Reject [NextStageRoleId] INT NULL, [Remarks] NVARCHAR(2000) NOT NULL, [SubmittedAtUtc] DATETIME2(3) NOT NULL CONSTRAINT [DF_Eval_Sub] DEFAULT (SYSUTCDATETIME()), CONSTRAINT [PK_InterviewEvaluations] PRIMARY KEY CLUSTERED ([EvaluationId]), CONSTRAINT [UQ_Eval_InterviewEvaluator] UNIQUE ([InterviewId], [EvaluatorUserId]), CONSTRAINT [CK_Eval_Soft] CHECK ([SoftSkillsRating] BETWEEN 1 AND 5), CONSTRAINT [CK_Eval_Tech] CHECK ([TechnicalRating] BETWEEN 1 AND 5), CONSTRAINT [CK_Eval_Rec] CHECK ([Recommendation] IN (1, 2, 3)), CONSTRAINT [FK_Eval_Interview] FOREIGN KEY ([InterviewId]) REFERENCES [ats].[Interviews]([InterviewId]), CONSTRAINT [FK_Eval_Evaluator] FOREIGN KEY ([EvaluatorUserId]) REFERENCES [core].[Users]([UserId]), CONSTRAINT [FK_Eval_NextRole] FOREIGN KEY ([NextStageRoleId]) REFERENCES [core].[Roles]([RoleId]) ); CREATE TABLE [ats].[QuestionBank] ( [QuestionId] INT IDENTITY(1,1) NOT NULL, [SubjectCatalogId] INT NOT NULL, -- FK -> cfg.CatalogItems (CatalogType=4) [QuestionType] TINYINT NOT NULL, -- 1=Technical, 2=Scenario, 3=Behavioral, 4=Practical [QuestionText] NVARCHAR(1000) NOT NULL, [ReferenceAnswer] NVARCHAR(2000) NOT NULL, [IsActive] BIT NOT NULL CONSTRAINT [DF_QBank_Active] DEFAULT (1), [CreatedByUserId] BIGINT NULL, CONSTRAINT [PK_QuestionBank] PRIMARY KEY CLUSTERED ([QuestionId]), CONSTRAINT [FK_QBank_Subject] FOREIGN KEY ([SubjectCatalogId]) REFERENCES [cfg].[CatalogItems]([CatalogItemId]) ); GO /* ========================================================================= 5. [work] — ATTENDANCE, REGULARIZATION, PROJECTS & WEEKLY TIMESHEETS ========================================================================= */ CREATE TABLE [work].[AttendanceShifts] ( [AttendanceId] BIGINT IDENTITY(1,1) NOT NULL, [EmployeeId] BIGINT NOT NULL, [ShiftDate] DATE NOT NULL, -- Local business date (Asia/Karachi) [CheckInUtc] DATETIME2(3) NOT NULL, [CheckOutUtc] DATETIME2(3) NULL, [WorkedMinutes] AS (DATEDIFF(MINUTE, [CheckInUtc], [CheckOutUtc])) PERSISTED, [IsWfh] BIT NOT NULL CONSTRAINT [DF_Att_Wfh] DEFAULT (0), [Status] TINYINT NOT NULL CONSTRAINT [DF_Att_Status] DEFAULT (1), -- 1=Open, 2=Complete, 3=MissingCheckOut, 4=Regularized [ClientIp] VARCHAR(45) NULL, [UpdatedByUserId] BIGINT NULL, [UpdatedAtUtc] DATETIME2(3) NULL, CONSTRAINT [PK_AttendanceShifts] PRIMARY KEY CLUSTERED ([AttendanceId]), CONSTRAINT [CK_Att_Status] CHECK ([Status] BETWEEN 1 AND 4), CONSTRAINT [CK_Att_Range] CHECK ([CheckOutUtc] IS NULL OR [CheckOutUtc] >= [CheckInUtc]), CONSTRAINT [FK_Att_Employee] FOREIGN KEY ([EmployeeId]) REFERENCES [hr].[Employees]([EmployeeId]), CONSTRAINT [FK_Att_Updater] FOREIGN KEY ([UpdatedByUserId]) REFERENCES [core].[Users]([UserId]) ); CREATE NONCLUSTERED INDEX [IX_Attendance_Emp_Date] ON [work].[AttendanceShifts]([EmployeeId], [ShiftDate] DESC) INCLUDE ([CheckInUtc], [CheckOutUtc], [IsWfh], [Status]); CREATE TABLE [work].[AttendanceRegularizations] ( [RegularizationId] BIGINT IDENTITY(1,1) NOT NULL, [AttendanceId] BIGINT NOT NULL, [EmployeeId] BIGINT NOT NULL, [RequestedCheckInUtc] DATETIME2(3) NOT NULL, [RequestedCheckOutUtc] DATETIME2(3) NOT NULL, [Reason] NVARCHAR(300) NOT NULL, [Status] TINYINT NOT NULL CONSTRAINT [DF_AttReg_Status] DEFAULT (1), -- 1=Pending, 2=Approved, 3=Rejected [ApproverUserId] BIGINT NULL, [DecidedAtUtc] DATETIME2(3) NULL, [DecisionComment] NVARCHAR(500) NULL, [CreatedAtUtc] DATETIME2(3) NOT NULL CONSTRAINT [DF_AttReg_Created] DEFAULT (SYSUTCDATETIME()), CONSTRAINT [PK_AttendanceRegularizations] PRIMARY KEY CLUSTERED ([RegularizationId]), CONSTRAINT [CK_AttReg_Range] CHECK ([RequestedCheckOutUtc] > [RequestedCheckInUtc]), CONSTRAINT [CK_AttReg_ReasonLen] CHECK (LEN([Reason]) >= 6), CONSTRAINT [FK_AttReg_Shift] FOREIGN KEY ([AttendanceId]) REFERENCES [work].[AttendanceShifts]([AttendanceId]), CONSTRAINT [FK_AttReg_Employee] FOREIGN KEY ([EmployeeId]) REFERENCES [hr].[Employees]([EmployeeId]), CONSTRAINT [FK_AttReg_Approver] FOREIGN KEY ([ApproverUserId]) REFERENCES [core].[Users]([UserId]) ); CREATE TABLE [work].[Projects] ( [ProjectId] INT IDENTITY(1,1) NOT NULL, [Code] VARCHAR(30) NOT NULL, [Title] NVARCHAR(150) NOT NULL, [IsBillable] BIT NOT NULL CONSTRAINT [DF_Proj_Billable] DEFAULT (1), [ProjectLeadEmployeeId] BIGINT NOT NULL, [StartDate] DATE NOT NULL, [EndDate] DATE NOT NULL, [Status] TINYINT NOT NULL CONSTRAINT [DF_Proj_Status] DEFAULT (2), -- 1=Planned, 2=Active, 3=Completed, 4=Archived [Description] NVARCHAR(1000) NULL, [CreatedAtUtc] DATETIME2(3) NOT NULL CONSTRAINT [DF_Proj_Created] DEFAULT (SYSUTCDATETIME()), CONSTRAINT [PK_Projects] PRIMARY KEY CLUSTERED ([ProjectId]), CONSTRAINT [UQ_Projects_Code] UNIQUE ([Code]), CONSTRAINT [CK_Projects_Dates] CHECK ([EndDate] >= [StartDate]), CONSTRAINT [FK_Projects_Lead] FOREIGN KEY ([ProjectLeadEmployeeId]) REFERENCES [hr].[Employees]([EmployeeId]) ); CREATE TABLE [work].[ProjectMembers] ( [ProjectMemberId] BIGINT IDENTITY(1,1) NOT NULL, [ProjectId] INT NOT NULL, [EmployeeId] BIGINT NOT NULL, [ProjectRoleTitle] NVARCHAR(100) NULL, [Responsibilities] NVARCHAR(1000) NULL, [IsActive] BIT NOT NULL CONSTRAINT [DF_ProjMem_Active] DEFAULT (1), [JoinedDate] DATE NOT NULL CONSTRAINT [DF_ProjMem_Joined] DEFAULT (CAST(SYSUTCDATETIME() AS DATE)), CONSTRAINT [PK_ProjectMembers] PRIMARY KEY CLUSTERED ([ProjectMemberId]), CONSTRAINT [UQ_ProjectMembers_ProjEmp] UNIQUE ([ProjectId], [EmployeeId]), CONSTRAINT [FK_ProjMem_Project] FOREIGN KEY ([ProjectId]) REFERENCES [work].[Projects]([ProjectId]), CONSTRAINT [FK_ProjMem_Employee] FOREIGN KEY ([EmployeeId]) REFERENCES [hr].[Employees]([EmployeeId]) ); CREATE TABLE [work].[ProjectMemberTools] ( [ProjectMemberId] BIGINT NOT NULL, [ToolCatalogId] INT NOT NULL, -- FK -> cfg.CatalogItems (CatalogType=2) CONSTRAINT [PK_ProjectMemberTools] PRIMARY KEY CLUSTERED ([ProjectMemberId], [ToolCatalogId]), CONSTRAINT [FK_PMTools_Member] FOREIGN KEY ([ProjectMemberId]) REFERENCES [work].[ProjectMembers]([ProjectMemberId]) ON DELETE CASCADE, CONSTRAINT [FK_PMTools_Tool] FOREIGN KEY ([ToolCatalogId]) REFERENCES [cfg].[CatalogItems]([CatalogItemId]) ); CREATE TABLE [work].[ProjectActivities] ( [ProjectActivityId] BIGINT NOT NULL IDENTITY(1,1), [ProjectId] INT NOT NULL, [ActivityCatalogId] INT NOT NULL, -- FK -> cfg.CatalogItems (CatalogType=1) [EmployeeId] BIGINT NOT NULL, [IsActive] BIT NOT NULL CONSTRAINT [DF_ProjAct_Active] DEFAULT (1), -- 0 = Unassigned cleanly while preserving past timesheets CONSTRAINT [PK_ProjectActivities] PRIMARY KEY CLUSTERED ([ProjectActivityId]), CONSTRAINT [UQ_ProjectActivities_Tuple] UNIQUE ([ProjectId], [ActivityCatalogId], [EmployeeId]), CONSTRAINT [FK_ProjAct_Project] FOREIGN KEY ([ProjectId]) REFERENCES [work].[Projects]([ProjectId]), CONSTRAINT [FK_ProjAct_Catalog] FOREIGN KEY ([ActivityCatalogId]) REFERENCES [cfg].[CatalogItems]([CatalogItemId]), CONSTRAINT [FK_ProjAct_Employee] FOREIGN KEY ([EmployeeId]) REFERENCES [hr].[Employees]([EmployeeId]) ); CREATE TABLE [work].[TimesheetWeeks] ( [TimesheetWeekId] BIGINT IDENTITY(1,1) NOT NULL, [EmployeeId] BIGINT NOT NULL, [WeekStartDate] DATE NOT NULL, -- Always ISO Monday [WeekEndDate] AS (DATEADD(DAY, 6, [WeekStartDate])) PERSISTED, [TotalHours] DECIMAL(5,2) NOT NULL CONSTRAINT [DF_TSWeek_Total] DEFAULT (0), [Status] TINYINT NOT NULL CONSTRAINT [DF_TSWeek_Status] DEFAULT (0), -- 0=Draft, 1=Pending, 2=Approved, 3=Rejected [SubmittedAtUtc] DATETIME2(3) NULL, [ApproverUserId] BIGINT NULL, [DecidedAtUtc] DATETIME2(3) NULL, [DecisionComment] NVARCHAR(1000) NULL, CONSTRAINT [PK_TimesheetWeeks] PRIMARY KEY CLUSTERED ([TimesheetWeekId]), CONSTRAINT [UQ_TimesheetWeeks_EmpWeek] UNIQUE ([EmployeeId], [WeekStartDate]), CONSTRAINT [CK_TSWeek_Status] CHECK ([Status] BETWEEN 0 AND 3), -- Date-first-independent Monday check (1900-01-01 was a Monday) CONSTRAINT [CK_TSWeek_IsMonday] CHECK (DATEDIFF(DAY, '19000101', [WeekStartDate]) % 7 = 0), CONSTRAINT [FK_TSWeek_Employee] FOREIGN KEY ([EmployeeId]) REFERENCES [hr].[Employees]([EmployeeId]), CONSTRAINT [FK_TSWeek_Approver] FOREIGN KEY ([ApproverUserId]) REFERENCES [core].[Users]([UserId]) ); CREATE NONCLUSTERED INDEX [IX_TimesheetWeeks_Status] ON [work].[TimesheetWeeks]([Status], [WeekStartDate] DESC); CREATE TABLE [work].[TimesheetEntries] ( [TimesheetEntryId] BIGINT IDENTITY(1,1) NOT NULL, [TimesheetWeekId] BIGINT NOT NULL, [ProjectActivityId] BIGINT NOT NULL, [WorkDate] DATE NOT NULL, [Hours] DECIMAL(4,2) NOT NULL, [TaskDescription] NVARCHAR(1000) NULL, [UpdatedAtUtc] DATETIME2(3) NOT NULL CONSTRAINT [DF_TSEntry_Updated] DEFAULT (SYSUTCDATETIME()), CONSTRAINT [PK_TimesheetEntries] PRIMARY KEY CLUSTERED ([TimesheetEntryId]), CONSTRAINT [UQ_TimesheetEntries_Cell] UNIQUE ([TimesheetWeekId], [ProjectActivityId], [WorkDate]), CONSTRAINT [CK_TSEntry_Hours] CHECK ([Hours] >= 0 AND [Hours] <= 24), CONSTRAINT [FK_TSEntry_Week] FOREIGN KEY ([TimesheetWeekId]) REFERENCES [work].[TimesheetWeeks]([TimesheetWeekId]) ON DELETE CASCADE, CONSTRAINT [FK_TSEntry_Activity] FOREIGN KEY ([ProjectActivityId]) REFERENCES [work].[ProjectActivities]([ProjectActivityId]) ); CREATE NONCLUSTERED INDEX [IX_TSEntries_WorkDate] ON [work].[TimesheetEntries]([WorkDate]) INCLUDE ([ProjectActivityId], [Hours]); GO /* ========================================================================= 6. [req] — LEAVES & LEDGER, LETTERS, EXPENSE CLAIMS & EQUIPMENT REQUESTS ========================================================================= */ CREATE TABLE [req].[LeaveRequests] ( [LeaveRequestId] BIGINT IDENTITY(1,1) NOT NULL, [EmployeeId] BIGINT NOT NULL, [LeaveType] TINYINT NOT NULL, -- 1=Annual, 2=Sick, 3=Casual, 4=HalfDay, 5=Parental, 6=Unpaid [StartDate] DATE NOT NULL, [EndDate] DATE NOT NULL, [CalculatedWorkingDays] DECIMAL(4,1) NOT NULL, -- Holiday- & weekend-aware server calculation [Title] NVARCHAR(150) NOT NULL, [Reason] NVARCHAR(1000) NOT NULL, [Status] TINYINT NOT NULL CONSTRAINT [DF_Leave_Status] DEFAULT (1), -- 1=Pending, 2=Approved, 3=Rejected, 4=Cancelled [ApproverUserId] BIGINT NULL, [DecidedAtUtc] DATETIME2(3) NULL, [DecisionComment] NVARCHAR(1000) NULL, [CreatedAtUtc] DATETIME2(3) NOT NULL CONSTRAINT [DF_Leave_Created] DEFAULT (SYSUTCDATETIME()), CONSTRAINT [PK_LeaveRequests] PRIMARY KEY CLUSTERED ([LeaveRequestId]), CONSTRAINT [CK_Leave_Dates] CHECK ([EndDate] >= [StartDate]), CONSTRAINT [CK_Leave_Days] CHECK ([CalculatedWorkingDays] > 0), CONSTRAINT [CK_Leave_Status] CHECK ([Status] BETWEEN 1 AND 4), CONSTRAINT [FK_Leave_Employee] FOREIGN KEY ([EmployeeId]) REFERENCES [hr].[Employees]([EmployeeId]), CONSTRAINT [FK_Leave_Approver] FOREIGN KEY ([ApproverUserId]) REFERENCES [core].[Users]([UserId]) ); CREATE NONCLUSTERED INDEX [IX_LeaveRequests_Status] ON [req].[LeaveRequests]([Status], [StartDate] DESC); -- Append-only ledger; SUM(DaysDelta) = exact current balance. Filtered unique index prevents double-debit! CREATE TABLE [req].[LeaveLedger] ( [LedgerEntryId] BIGINT IDENTITY(1,1) NOT NULL, [EmployeeId] BIGINT NOT NULL, [EntryType] TINYINT NOT NULL, -- 1=OpeningQuota, 2=MonthlyAccrual, 3=ApprovedLeaveDebit, 4=CarryForward, 5=ManualAdjustment [DaysDelta] DECIMAL(5,2) NOT NULL, [EffectiveDate] DATE NOT NULL, [LeaveRequestId] BIGINT NULL, [Notes] NVARCHAR(300) NULL, [CreatedByUserId] BIGINT NULL, [CreatedAtUtc] DATETIME2(3) NOT NULL CONSTRAINT [DF_Ledger_Created] DEFAULT (SYSUTCDATETIME()), CONSTRAINT [PK_LeaveLedger] PRIMARY KEY CLUSTERED ([LedgerEntryId]), CONSTRAINT [FK_Ledger_Employee] FOREIGN KEY ([EmployeeId]) REFERENCES [hr].[Employees]([EmployeeId]), CONSTRAINT [FK_Ledger_Request] FOREIGN KEY ([LeaveRequestId]) REFERENCES [req].[LeaveRequests]([LeaveRequestId]) ); CREATE UNIQUE NONCLUSTERED INDEX [UX_LeaveLedger_SingleDebitPerRequest] ON [req].[LeaveLedger]([LeaveRequestId]) WHERE [LeaveRequestId] IS NOT NULL; CREATE NONCLUSTERED INDEX [IX_LeaveLedger_Employee] ON [req].[LeaveLedger]([EmployeeId]) INCLUDE ([DaysDelta]); CREATE TABLE [req].[DocumentRequests] ( [DocumentRequestId] BIGINT IDENTITY(1,1) NOT NULL, [EmployeeId] BIGINT NOT NULL, [TemplateCode] VARCHAR(50) NOT NULL, -- FK -> cfg.DocumentTemplates(TemplateCode) [PurposeReason] NVARCHAR(500) NOT NULL, [IssuedFileId] UNIQUEIDENTIFIER NULL, [Status] TINYINT NOT NULL CONSTRAINT [DF_DocReq_Status] DEFAULT (1), -- 1=Pending, 2=ApprovedIssued, 3=Rejected [ApproverUserId] BIGINT NULL, [DecidedAtUtc] DATETIME2(3) NULL, [DecisionComment] NVARCHAR(500) NULL, [CreatedAtUtc] DATETIME2(3) NOT NULL CONSTRAINT [DF_DocReq_Created] DEFAULT (SYSUTCDATETIME()), CONSTRAINT [PK_DocumentRequests] PRIMARY KEY CLUSTERED ([DocumentRequestId]), CONSTRAINT [FK_DocReq_Employee] FOREIGN KEY ([EmployeeId]) REFERENCES [hr].[Employees]([EmployeeId]), CONSTRAINT [FK_DocReq_Template] FOREIGN KEY ([TemplateCode]) REFERENCES [cfg].[DocumentTemplates]([TemplateCode]), CONSTRAINT [FK_DocReq_File] FOREIGN KEY ([IssuedFileId]) REFERENCES [core].[Files]([FileId]), CONSTRAINT [FK_DocReq_Approver] FOREIGN KEY ([ApproverUserId]) REFERENCES [core].[Users]([UserId]) ); CREATE TABLE [req].[ExpenseClaims] ( [ExpenseClaimId] BIGINT IDENTITY(1,1) NOT NULL, [EmployeeId] BIGINT NOT NULL, [ClaimType] VARCHAR(20) NOT NULL, -- 'REIMBURSEMENT','EXPENSE' [TargetMonth] DATE NOT NULL, -- First of month YYYY-MM-01 [Amount] DECIMAL(18,2) NOT NULL, [CurrencyCode] CHAR(3) NOT NULL CONSTRAINT [DF_Claim_Curr] DEFAULT ('PKR'), [Description] NVARCHAR(1000) NOT NULL, [ReceiptFileId] UNIQUEIDENTIFIER NOT NULL, [Status] TINYINT NOT NULL CONSTRAINT [DF_Claim_Status] DEFAULT (1), -- 1=Pending, 2=Approved, 3=Rejected, 4=SettledInPayroll [ApproverUserId] BIGINT NULL, [DecidedAtUtc] DATETIME2(3) NULL, [DecisionComment] NVARCHAR(500) NULL, [CreatedAtUtc] DATETIME2(3) NOT NULL CONSTRAINT [DF_Claim_Created] DEFAULT (SYSUTCDATETIME()), CONSTRAINT [PK_ExpenseClaims] PRIMARY KEY CLUSTERED ([ExpenseClaimId]), CONSTRAINT [CK_Claim_Positive] CHECK ([Amount] > 0), CONSTRAINT [CK_Claim_FirstOfMonth] CHECK (DAY([TargetMonth]) = 1), CONSTRAINT [FK_Claim_Employee] FOREIGN KEY ([EmployeeId]) REFERENCES [hr].[Employees]([EmployeeId]), CONSTRAINT [FK_Claim_Receipt] FOREIGN KEY ([ReceiptFileId]) REFERENCES [core].[Files]([FileId]), CONSTRAINT [FK_Claim_Approver] FOREIGN KEY ([ApproverUserId]) REFERENCES [core].[Users]([UserId]) ); CREATE TABLE [req].[EquipmentRequests] ( [EquipmentRequestId] BIGINT IDENTITY(1,1) NOT NULL, [EmployeeId] BIGINT NOT NULL, [RequestKind] TINYINT NOT NULL, -- 1=NewItem, 2=Replacement [ItemCatalogId] INT NULL, -- FK -> cfg.CatalogItems (CatalogType=3); NULL when custom 'Other' [CustomItemName] NVARCHAR(120) NULL, [Justification] NVARCHAR(1000) NOT NULL, [Status] TINYINT NOT NULL CONSTRAINT [DF_Equip_Status] DEFAULT (1), -- 1=Pending, 2=Approved, 3=Fulfilled, 4=Rejected [ApproverUserId] BIGINT NULL, [DecidedAtUtc] DATETIME2(3) NULL, [DecisionComment] NVARCHAR(500) NULL, [CreatedAtUtc] DATETIME2(3) NOT NULL CONSTRAINT [DF_Equip_Created] DEFAULT (SYSUTCDATETIME()), CONSTRAINT [PK_EquipmentRequests] PRIMARY KEY CLUSTERED ([EquipmentRequestId]), CONSTRAINT [CK_Equip_ItemSpec] CHECK ([ItemCatalogId] IS NOT NULL OR [CustomItemName] IS NOT NULL), CONSTRAINT [FK_Equip_Employee] FOREIGN KEY ([EmployeeId]) REFERENCES [hr].[Employees]([EmployeeId]), CONSTRAINT [FK_Equip_Catalog] FOREIGN KEY ([ItemCatalogId]) REFERENCES [cfg].[CatalogItems]([CatalogItemId]), CONSTRAINT [FK_Equip_Approver] FOREIGN KEY ([ApproverUserId]) REFERENCES [core].[Users]([UserId]) ); GO /* ========================================================================= 7. [pay] — MONTHLY PAYROLL ENGINE, ITEMS & DISCREPANCY CLAIMS ========================================================================= */ CREATE TABLE [pay].[PayrollRuns] ( [PayrollRunId] INT IDENTITY(1,1) NOT NULL, [PeriodMonth] DATE NOT NULL, -- Always YYYY-MM-01 [WorkingDays] TINYINT NOT NULL, [ExpectedHours] DECIMAL(5,1) NOT NULL, [Status] TINYINT NOT NULL CONSTRAINT [DF_PayRun_Status] DEFAULT (0), -- 0=Draft, 1=PendingExecutive, 2=ApprovedLocked, 3=Rejected [PreparedByUserId] BIGINT NOT NULL, [SubmittedAtUtc] DATETIME2(3) NULL, [ApprovedByUserId] BIGINT NULL, [ApprovedAtUtc] DATETIME2(3) NULL, [ExecutiveComment] NVARCHAR(1000) NULL, [CreatedAtUtc] DATETIME2(3) NOT NULL CONSTRAINT [DF_PayRun_Created] DEFAULT (SYSUTCDATETIME()), CONSTRAINT [PK_PayrollRuns] PRIMARY KEY CLUSTERED ([PayrollRunId]), CONSTRAINT [UQ_PayrollRuns_Month] UNIQUE ([PeriodMonth]), CONSTRAINT [CK_PayrollRuns_FirstOfMonth] CHECK (DAY([PeriodMonth]) = 1), CONSTRAINT [CK_PayrollRuns_Status] CHECK ([Status] BETWEEN 0 AND 3), CONSTRAINT [FK_PayRun_Preparer] FOREIGN KEY ([PreparedByUserId]) REFERENCES [core].[Users]([UserId]), CONSTRAINT [FK_PayRun_Approver] FOREIGN KEY ([ApprovedByUserId]) REFERENCES [core].[Users]([UserId]) ); -- PERSISTED COMPUTED COLUMNS guarantee GrossIncome & NetSalary are ALWAYS exact and non-cumulative! CREATE TABLE [pay].[PayrollItems] ( [PayrollItemId] BIGINT IDENTITY(1,1) NOT NULL, [PayrollRunId] INT NOT NULL, [EmployeeId] BIGINT NOT NULL, [BasicSalarySnapshot] DECIMAL(18,2) NOT NULL, [ApprovedWorkedHours] DECIMAL(5,2) NOT NULL, [PaidLeaveHours] DECIMAL(5,2) NOT NULL CONSTRAINT [DF_PayItem_LeaveHrs] DEFAULT (0), [BaseEarned] DECIMAL(18,2) NOT NULL, [TravelAllowance] DECIMAL(18,2) NOT NULL CONSTRAINT [DF_PayItem_Travel] DEFAULT (0), [Bonus] DECIMAL(18,2) NOT NULL CONSTRAINT [DF_PayItem_Bonus] DEFAULT (0), [Reimbursements] DECIMAL(18,2) NOT NULL CONSTRAINT [DF_PayItem_Reimb] DEFAULT (0), [TaxDeduction] DECIMAL(18,2) NOT NULL CONSTRAINT [DF_PayItem_Tax] DEFAULT (0), [OtherDeductions] DECIMAL(18,2) NOT NULL CONSTRAINT [DF_PayItem_OtherDed] DEFAULT (0), [GrossIncome] AS ([BaseEarned] + [TravelAllowance] + [Bonus] + [Reimbursements]) PERSISTED, [NetSalary] AS ([BaseEarned] + [TravelAllowance] + [Bonus] + [Reimbursements] - [TaxDeduction] - [OtherDeductions]) PERSISTED, [AdjustmentNotes] NVARCHAR(500) NULL, [PayslipFileId] UNIQUEIDENTIFIER NULL, [UpdatedAtUtc] DATETIME2(3) NOT NULL CONSTRAINT [DF_PayItem_Updated] DEFAULT (SYSUTCDATETIME()), CONSTRAINT [PK_PayrollItems] PRIMARY KEY CLUSTERED ([PayrollItemId]), CONSTRAINT [UQ_PayrollItems_RunEmp] UNIQUE ([PayrollRunId], [EmployeeId]), CONSTRAINT [CK_PayItem_NonNegLeave] CHECK ([PaidLeaveHours] >= 0), CONSTRAINT [CK_PayItem_NonNegParts] CHECK ([BaseEarned] >= 0 AND [TravelAllowance] >= 0 AND [Bonus] >= 0 AND [TaxDeduction] >= 0), CONSTRAINT [FK_PayItem_Run] FOREIGN KEY ([PayrollRunId]) REFERENCES [pay].[PayrollRuns]([PayrollRunId]) ON DELETE CASCADE, CONSTRAINT [FK_PayItem_Employee] FOREIGN KEY ([EmployeeId]) REFERENCES [hr].[Employees]([EmployeeId]), CONSTRAINT [FK_PayItem_Payslip] FOREIGN KEY ([PayslipFileId]) REFERENCES [core].[Files]([FileId]) ); CREATE TABLE [pay].[PayrollDisputes] ( [DisputeId] BIGINT IDENTITY(1,1) NOT NULL, [PayrollItemId] BIGINT NOT NULL, [EmployeeId] BIGINT NOT NULL, [Description] NVARCHAR(1000) NOT NULL, [Status] TINYINT NOT NULL CONSTRAINT [DF_PayDisp_Status] DEFAULT (1), -- 1=Open, 2=UnderReview, 3=ResolvedRetro, 4=Declined [RetroAdjustmentAmount] DECIMAL(18,2) NULL, [ResolvedByUserId] BIGINT NULL, [ResolvedAtUtc] DATETIME2(3) NULL, [ResolutionNotes] NVARCHAR(1000) NULL, [CreatedAtUtc] DATETIME2(3) NOT NULL CONSTRAINT [DF_PayDisp_Created] DEFAULT (SYSUTCDATETIME()), CONSTRAINT [PK_PayrollDisputes] PRIMARY KEY CLUSTERED ([DisputeId]), CONSTRAINT [CK_PayDisp_Status] CHECK ([Status] BETWEEN 1 AND 4), CONSTRAINT [FK_PayDisp_Item] FOREIGN KEY ([PayrollItemId]) REFERENCES [pay].[PayrollItems]([PayrollItemId]), CONSTRAINT [FK_PayDisp_Employee] FOREIGN KEY ([EmployeeId]) REFERENCES [hr].[Employees]([EmployeeId]), CONSTRAINT [FK_PayDisp_Resolver] FOREIGN KEY ([ResolvedByUserId]) REFERENCES [core].[Users]([UserId]) ); GO /* ========================================================================= 8. [hub] — KNOWLEDGE BASE (POLICIES/BENEFITS), IDEAS & COMPANY BLOG ========================================================================= */ CREATE TABLE [hub].[KnowledgeDocuments] ( [DocumentId] INT IDENTITY(1,1) NOT NULL, [Category] TINYINT NOT NULL, -- 1=Policy, 2=Benefit, 3=InterviewGuideline [Title] NVARCHAR(200) NOT NULL, [RequiresAcknowledgment] BIT NOT NULL CONSTRAINT [DF_KDoc_ReqAck] DEFAULT (0), [IsArchived] BIT NOT NULL CONSTRAINT [DF_KDoc_Archived] DEFAULT (0), [CreatedByUserId] BIGINT NOT NULL, [CreatedAtUtc] DATETIME2(3) NOT NULL CONSTRAINT [DF_KDoc_Created] DEFAULT (SYSUTCDATETIME()), CONSTRAINT [PK_KnowledgeDocuments] PRIMARY KEY CLUSTERED ([DocumentId]), CONSTRAINT [CK_KDoc_Category] CHECK ([Category] IN (1, 2, 3)), CONSTRAINT [FK_KDoc_Creator] FOREIGN KEY ([CreatedByUserId]) REFERENCES [core].[Users]([UserId]) ); CREATE TABLE [hub].[KnowledgeVersions] ( [VersionId] INT IDENTITY(1,1) NOT NULL, [DocumentId] INT NOT NULL, [VersionLabel] VARCHAR(20) NOT NULL, -- e.g. 'v2.1' [Summary] NVARCHAR(2000) NOT NULL, [ChangeNotes] NVARCHAR(500) NULL, [FileId] UNIQUEIDENTIFIER NOT NULL, [IsLatest] BIT NOT NULL CONSTRAINT [DF_KVer_Latest] DEFAULT (1), [PublishedByUserId] BIGINT NOT NULL, [PublishedAtUtc] DATETIME2(3) NOT NULL CONSTRAINT [DF_KVer_Published] DEFAULT (SYSUTCDATETIME()), CONSTRAINT [PK_KnowledgeVersions] PRIMARY KEY CLUSTERED ([VersionId]), CONSTRAINT [UQ_KVer_DocLabel] UNIQUE ([DocumentId], [VersionLabel]), CONSTRAINT [FK_KVer_Doc] FOREIGN KEY ([DocumentId]) REFERENCES [hub].[KnowledgeDocuments]([DocumentId]), CONSTRAINT [FK_KVer_File] FOREIGN KEY ([FileId]) REFERENCES [core].[Files]([FileId]), CONSTRAINT [FK_KVer_Pub] FOREIGN KEY ([PublishedByUserId]) REFERENCES [core].[Users]([UserId]) ); CREATE UNIQUE NONCLUSTERED INDEX [UX_KVer_SingleLatestPerDoc] ON [hub].[KnowledgeVersions]([DocumentId]) WHERE [IsLatest] = 1; CREATE TABLE [hub].[DocumentAcknowledgments] ( [VersionId] INT NOT NULL, [UserId] BIGINT NOT NULL, [AcknowledgedAtUtc] DATETIME2(3) NOT NULL CONSTRAINT [DF_DocAck_At] DEFAULT (SYSUTCDATETIME()), CONSTRAINT [PK_DocumentAcknowledgments] PRIMARY KEY CLUSTERED ([VersionId], [UserId]), CONSTRAINT [FK_DocAck_Ver] FOREIGN KEY ([VersionId]) REFERENCES [hub].[KnowledgeVersions]([VersionId]), CONSTRAINT [FK_DocAck_User] FOREIGN KEY ([UserId]) REFERENCES [core].[Users]([UserId]) ); CREATE TABLE [hub].[Ideas] ( [IdeaId] BIGINT IDENTITY(1,1) NOT NULL, [AuthorUserId] BIGINT NOT NULL, [Title] NVARCHAR(200) NOT NULL, [Abstract] NVARCHAR(MAX) NOT NULL, [AttachmentFileId] UNIQUEIDENTIFIER NULL, [Status] TINYINT NOT NULL CONSTRAINT [DF_Ideas_Status] DEFAULT (1), -- 1=Submitted, 2=UnderReview, 3=Planned, 4=Implemented, 5=Archived [UpvoteCount] INT NOT NULL CONSTRAINT [DF_Ideas_Votes] DEFAULT (0), [CreatedAtUtc] DATETIME2(3) NOT NULL CONSTRAINT [DF_Ideas_Created] DEFAULT (SYSUTCDATETIME()), [UpdatedAtUtc] DATETIME2(3) NOT NULL CONSTRAINT [DF_Ideas_Updated] DEFAULT (SYSUTCDATETIME()), CONSTRAINT [PK_Ideas] PRIMARY KEY CLUSTERED ([IdeaId]), CONSTRAINT [FK_Ideas_Author] FOREIGN KEY ([AuthorUserId]) REFERENCES [core].[Users]([UserId]), CONSTRAINT [FK_Ideas_File] FOREIGN KEY ([AttachmentFileId]) REFERENCES [core].[Files]([FileId]) ); CREATE TABLE [hub].[IdeaUpvotes] ( [IdeaId] BIGINT NOT NULL, [UserId] BIGINT NOT NULL, [CreatedAtUtc] DATETIME2(3) NOT NULL CONSTRAINT [DF_Upvote_At] DEFAULT (SYSUTCDATETIME()), CONSTRAINT [PK_IdeaUpvotes] PRIMARY KEY CLUSTERED ([IdeaId], [UserId]), CONSTRAINT [FK_Upvote_Idea] FOREIGN KEY ([IdeaId]) REFERENCES [hub].[Ideas]([IdeaId]) ON DELETE CASCADE, CONSTRAINT [FK_Upvote_User] FOREIGN KEY ([UserId]) REFERENCES [core].[Users]([UserId]) ); CREATE TABLE [hub].[BlogPosts] ( [BlogPostId] BIGINT IDENTITY(1,1) NOT NULL, [AuthorUserId] BIGINT NOT NULL, [AssignedByUserId] BIGINT NULL, [Title] NVARCHAR(200) NOT NULL, [Slug] VARCHAR(220) NOT NULL, [Summary] NVARCHAR(500) NULL, [SanitizedHtmlBody] NVARCHAR(MAX) NOT NULL, [AssignmentInstructions] NVARCHAR(1000) NULL, [Status] TINYINT NOT NULL CONSTRAINT [DF_Blog_Status] DEFAULT (0), -- 0=Draft, 1=PendingReview, 2=ChangesRequested, 3=Published, 4=Rejected [ReviewerUserId] BIGINT NULL, [ReviewComment] NVARCHAR(1000) NULL, [PublishedAtUtc] DATETIME2(3) NULL, [CreatedAtUtc] DATETIME2(3) NOT NULL CONSTRAINT [DF_Blog_Created] DEFAULT (SYSUTCDATETIME()), CONSTRAINT [PK_BlogPosts] PRIMARY KEY CLUSTERED ([BlogPostId]), CONSTRAINT [UQ_BlogPosts_Slug] UNIQUE ([Slug]), CONSTRAINT [CK_BlogPosts_Status] CHECK ([Status] BETWEEN 0 AND 4), CONSTRAINT [FK_Blog_Author] FOREIGN KEY ([AuthorUserId]) REFERENCES [core].[Users]([UserId]), CONSTRAINT [FK_Blog_Assigner] FOREIGN KEY ([AssignedByUserId]) REFERENCES [core].[Users]([UserId]), CONSTRAINT [FK_Blog_Reviewer] FOREIGN KEY ([ReviewerUserId]) REFERENCES [core].[Users]([UserId]) ); CREATE NONCLUSTERED INDEX [IX_BlogPosts_Published] ON [hub].[BlogPosts]([PublishedAtUtc] DESC) WHERE [Status] = 3; GO /* ========================================================================= 9. [audit] — IMMUTABLE APPEND-ONLY AUDIT LOG ========================================================================= */ CREATE TABLE [audit].[AuditEvents] ( [AuditEventId] BIGINT IDENTITY(1,1) NOT NULL, [OccurredAtUtc] DATETIME2(3) NOT NULL CONSTRAINT [DF_Audit_At] DEFAULT (SYSUTCDATETIME()), [ActorUserId] BIGINT NULL, [Category] VARCHAR(40) NOT NULL, -- 'AUTH','RBAC','COMPENSATION','ATTENDANCE_EDIT','LEAVE','PAYROLL','RESIGNATION' [EntityName] VARCHAR(60) NOT NULL, [EntityId] VARCHAR(64) NOT NULL, [Action] VARCHAR(30) NOT NULL, -- 'CREATE','UPDATE','APPROVE','REJECT','LOGIN_DENIED' [Reason] NVARCHAR(500) NULL, -- Mandatory for ATTENDANCE_EDIT & COMPENSATION [OldValuesJson] NVARCHAR(MAX) NULL, [NewValuesJson] NVARCHAR(MAX) NULL, [IpAddress] VARCHAR(45) NULL, CONSTRAINT [PK_AuditEvents] PRIMARY KEY CLUSTERED ([AuditEventId]), CONSTRAINT [CK_Audit_MandatoryReason] CHECK ( [Category] NOT IN ('ATTENDANCE_EDIT', 'COMPENSATION') OR ([Reason] IS NOT NULL AND LEN(LTRIM(RTRIM([Reason]))) >= 6) ) ); CREATE NONCLUSTERED INDEX [IX_AuditEvents_Entity] ON [audit].[AuditEvents]([EntityName], [EntityId], [OccurredAtUtc] DESC); CREATE NONCLUSTERED INDEX [IX_AuditEvents_Category_Date] ON [audit].[AuditEvents]([Category], [OccurredAtUtc] DESC); GO