K
Klivvr Blog

Data Modeling for Payroll Systems

A detailed guide to designing data models for payroll systems in TypeScript, covering employee records, pay structures, temporal data, and the relational patterns that ensure accuracy and auditability.

technical9 min readBy Klivvr Engineering
Share:

The data model is the foundation upon which every payroll system is built. A poorly designed model leads to calculation errors, compliance failures, and an ever-growing tangle of workarounds. A well-designed model makes the complex business rules of payroll expressible, auditable, and maintainable.

In this article, we explore the key data modeling decisions for a payroll system built in TypeScript. We cover employee compensation structures, temporal data patterns, pay run modeling, and the relational patterns that keep financial data consistent across millions of transactions.

Employee Compensation Modeling

An employee's compensation is rarely a single number. It comprises a base salary or hourly rate, various allowances, benefit elections, and deduction configurations. The data model must capture all of these dimensions while supporting changes over time.

interface Employee {
  id: string;
  employeeNumber: string;
  firstName: string;
  lastName: string;
  email: string;
  hireDate: Date;
  terminationDate: Date | null;
  status: EmployeeStatus;
  departmentId: string;
  locationId: string;
  createdAt: Date;
  updatedAt: Date;
}
 
type EmployeeStatus = "active" | "on_leave" | "terminated" | "suspended";
 
interface CompensationRecord {
  id: string;
  employeeId: string;
  effectiveDate: Date;
  endDate: Date | null;
  compensationType: CompensationType;
  amount: number;
  currency: string;
  payFrequency: PayFrequency;
  reason: string;
  approvedBy: string;
  createdAt: Date;
}
 
type CompensationType = "salary" | "hourly" | "commission" | "stipend";
type PayFrequency = "weekly" | "biweekly" | "semi_monthly" | "monthly";
 
interface BenefitElection {
  id: string;
  employeeId: string;
  planId: string;
  coverageLevel: "employee" | "employee_spouse" | "employee_children" | "family";
  employeeContribution: number;
  employerContribution: number;
  effectiveDate: Date;
  endDate: Date | null;
  enrollmentDate: Date;
}
 
interface DeductionConfiguration {
  id: string;
  employeeId: string;
  deductionCode: string;
  amount: number | null;
  percentage: number | null;
  calculationMethod: "fixed" | "percentage" | "formula";
  effectiveDate: Date;
  endDate: Date | null;
  maxAnnualAmount: number | null;
}

The separation of Employee from CompensationRecord is deliberate. An employee's compensation changes over time---raises, promotions, role changes---and each change must be tracked as a distinct record with its own effective date. This temporal pattern is central to payroll data modeling.

A practical tip: never update a compensation record in place. Instead, close the current record by setting its endDate and insert a new record with the new effective date. This preserves the complete history and makes retroactive calculations straightforward.

Temporal Data Patterns

Payroll is fundamentally a temporal system. Almost every piece of data has a time dimension: compensation records, benefit elections, tax withholding configurations, and even organizational assignments. The bitemporal pattern---tracking both the valid time (when something is true in the real world) and the transaction time (when it was recorded in the system)---is the gold standard for payroll data.

interface BitemporalRecord<T> {
  data: T;
  validFrom: Date;
  validTo: Date | null;
  transactionFrom: Date;
  transactionTo: Date | null;
  recordedBy: string;
}
 
class BitemporalRepository<T extends { id: string }> {
  constructor(private readonly store: DataStore) {}
 
  async getAsOf(
    entityId: string,
    validDate: Date,
    transactionDate: Date = new Date()
  ): Promise<T | null> {
    const records = await this.store.query<BitemporalRecord<T>>({
      "data.id": entityId,
      validFrom: { $lte: validDate },
      $or: [
        { validTo: null },
        { validTo: { $gt: validDate } },
      ],
      transactionFrom: { $lte: transactionDate },
      $or: [
        { transactionTo: null },
        { transactionTo: { $gt: transactionDate } },
      ],
    });
 
    return records.length > 0 ? records[0].data : null;
  }
 
  async recordChange(
    entity: T,
    validFrom: Date,
    validTo: Date | null,
    actor: string
  ): Promise<void> {
    // Close the current transaction-time version
    await this.store.update(
      {
        "data.id": entity.id,
        transactionTo: null,
      },
      { transactionTo: new Date() }
    );
 
    // Insert the new version
    const record: BitemporalRecord<T> = {
      data: entity,
      validFrom,
      validTo,
      transactionFrom: new Date(),
      transactionTo: null,
      recordedBy: actor,
    };
 
    await this.store.insert(record);
  }
}

Bitemporality answers two distinct questions. "What was the employee's salary on March 15?" is a valid-time query. "What did we think the employee's salary was on March 15, as of April 1?" is a bitemporal query. The second question is essential for understanding and correcting retroactive payroll adjustments.

Pay Run Modeling

A pay run is the central organizing entity for payroll processing. It represents a single execution of payroll for a defined set of employees and a specific pay period. The pay run model must capture the lifecycle of the processing event, the individual employee calculations, and the approval workflow.

interface PayRun {
  id: string;
  companyId: string;
  payPeriod: PayPeriodDefinition;
  status: PayRunStatus;
  employeeCount: number;
  totalGrossPay: number;
  totalNetPay: number;
  totalTaxes: number;
  totalDeductions: number;
  createdBy: string;
  approvedBy: string | null;
  processedAt: Date | null;
  createdAt: Date;
  updatedAt: Date;
}
 
type PayRunStatus =
  | "draft"
  | "calculating"
  | "calculated"
  | "in_review"
  | "approved"
  | "processing"
  | "processed"
  | "reversed";
 
interface PayPeriodDefinition {
  startDate: Date;
  endDate: Date;
  payDate: Date;
  frequency: PayFrequency;
  year: number;
  periodNumber: number;
}
 
interface PayStub {
  id: string;
  payRunId: string;
  employeeId: string;
  payPeriod: PayPeriodDefinition;
  grossPay: number;
  netPay: number;
  earnings: EarningLine[];
  deductions: DeductionLine[];
  taxes: TaxLine[];
  ytdGross: number;
  ytdNet: number;
  ytdTaxes: number;
  calculationVersion: string;
}
 
interface EarningLine {
  code: string;
  description: string;
  hours: number | null;
  rate: number | null;
  amount: number;
  type: "regular" | "overtime" | "bonus" | "commission" | "allowance";
}
 
interface DeductionLine {
  code: string;
  description: string;
  amount: number;
  employerMatch: number | null;
  preTax: boolean;
  ytdAmount: number;
}
 
interface TaxLine {
  jurisdiction: string;
  taxType: string;
  taxableWages: number;
  amount: number;
  ytdAmount: number;
}

The PayStub is the immutable record of an individual employee's payroll for a given period. Once a pay run is processed, pay stubs should never be modified. If a correction is needed, a new pay run with adjustment entries is created instead.

A practical tip: model year-to-date amounts directly on the pay stub rather than computing them on the fly. This makes pay stub rendering fast and eliminates discrepancies that arise from recalculating YTD totals after retroactive corrections have been applied.

Organizational Structure and Multi-Entity Support

Payroll systems must account for organizational complexity. Departments, cost centers, legal entities, and locations all affect how payroll is processed, reported, and funded.

interface LegalEntity {
  id: string;
  name: string;
  taxId: string;
  country: string;
  state: string | null;
  registeredAddress: Address;
  payrollSettings: PayrollSettings;
}
 
interface PayrollSettings {
  defaultPayFrequency: PayFrequency;
  defaultCurrency: string;
  fiscalYearStart: { month: number; day: number };
  overtimeRules: OvertimeConfiguration;
  approvalChain: ApprovalStep[];
}
 
interface OvertimeConfiguration {
  dailyThresholdHours: number | null;
  weeklyThresholdHours: number;
  defaultMultiplier: number;
  doubleTimeThresholdHours: number | null;
  doubleTimeMultiplier: number | null;
}
 
interface Department {
  id: string;
  entityId: string;
  name: string;
  costCenterCode: string;
  managerId: string | null;
  parentDepartmentId: string | null;
}
 
interface CostAllocation {
  id: string;
  employeeId: string;
  allocations: CostAllocationLine[];
  effectiveDate: Date;
  endDate: Date | null;
}
 
interface CostAllocationLine {
  departmentId: string;
  costCenterCode: string;
  projectCode: string | null;
  percentage: number;
}
 
class CostAllocationService {
  async allocatePayStub(
    payStub: PayStub,
    allocation: CostAllocation
  ): Promise<JournalEntry[]> {
    const entries: JournalEntry[] = [];
 
    for (const line of allocation.allocations) {
      const allocatedGross = payStub.grossPay * (line.percentage / 100);
      const allocatedTaxes = payStub.taxes.reduce(
        (sum, t) => sum + t.amount,
        0
      ) * (line.percentage / 100);
 
      entries.push({
        id: crypto.randomUUID(),
        payStubId: payStub.id,
        costCenterCode: line.costCenterCode,
        departmentId: line.departmentId,
        projectCode: line.projectCode,
        debitAccount: "salary_expense",
        creditAccount: "payroll_liability",
        amount: Math.round((allocatedGross + allocatedTaxes) * 100) / 100,
        currency: "USD",
        payPeriod: payStub.payPeriod,
        createdAt: new Date(),
      });
    }
 
    return entries;
  }
}
 
interface JournalEntry {
  id: string;
  payStubId: string;
  costCenterCode: string;
  departmentId: string;
  projectCode: string | null;
  debitAccount: string;
  creditAccount: string;
  amount: number;
  currency: string;
  payPeriod: PayPeriodDefinition;
  createdAt: Date;
}

Cost allocation splits a single employee's payroll expense across multiple departments or projects. This is common for shared resources, part-time arrangements, and matrix organizations. The allocation percentages must sum to 100 and are themselves temporal---they can change when an employee transfers or takes on a new project.

Data Integrity and Validation

Financial data demands strict integrity constraints. The data model must enforce consistency at multiple levels: individual field validation, cross-field rules, and aggregate invariants.

class PayRunValidator {
  validate(payRun: PayRun, payStubs: PayStub[]): ValidationResult[] {
    const errors: ValidationResult[] = [];
 
    errors.push(...this.validateAggregates(payRun, payStubs));
    errors.push(...this.validateIndividualStubs(payStubs));
    errors.push(...this.validatePeriodContinuity(payRun));
 
    return errors;
  }
 
  private validateAggregates(
    payRun: PayRun,
    stubs: PayStub[]
  ): ValidationResult[] {
    const errors: ValidationResult[] = [];
 
    const sumGross = stubs.reduce((s, p) => s + p.grossPay, 0);
    if (Math.abs(sumGross - payRun.totalGrossPay) > 0.01) {
      errors.push({
        field: "totalGrossPay",
        message: `Aggregate mismatch: sum=${sumGross}, header=${payRun.totalGrossPay}`,
        severity: "error",
      });
    }
 
    const sumNet = stubs.reduce((s, p) => s + p.netPay, 0);
    if (Math.abs(sumNet - payRun.totalNetPay) > 0.01) {
      errors.push({
        field: "totalNetPay",
        message: `Aggregate mismatch: sum=${sumNet}, header=${payRun.totalNetPay}`,
        severity: "error",
      });
    }
 
    if (stubs.length !== payRun.employeeCount) {
      errors.push({
        field: "employeeCount",
        message: `Count mismatch: stubs=${stubs.length}, header=${payRun.employeeCount}`,
        severity: "error",
      });
    }
 
    return errors;
  }
 
  private validateIndividualStubs(stubs: PayStub[]): ValidationResult[] {
    const errors: ValidationResult[] = [];
 
    for (const stub of stubs) {
      const totalEarnings = stub.earnings.reduce((s, e) => s + e.amount, 0);
      if (Math.abs(totalEarnings - stub.grossPay) > 0.01) {
        errors.push({
          field: `stub.${stub.employeeId}.grossPay`,
          message: `Earnings sum ${totalEarnings} does not match gross pay ${stub.grossPay}`,
          severity: "error",
        });
      }
 
      const totalDeductions = stub.deductions.reduce((s, d) => s + d.amount, 0);
      const totalTaxes = stub.taxes.reduce((s, t) => s + t.amount, 0);
      const expectedNet = stub.grossPay - totalDeductions - totalTaxes;
 
      if (Math.abs(expectedNet - stub.netPay) > 0.01) {
        errors.push({
          field: `stub.${stub.employeeId}.netPay`,
          message: `Net pay mismatch: expected=${expectedNet}, actual=${stub.netPay}`,
          severity: "error",
        });
      }
    }
 
    return errors;
  }
 
  private validatePeriodContinuity(payRun: PayRun): ValidationResult[] {
    const errors: ValidationResult[] = [];
 
    if (payRun.payPeriod.payDate < payRun.payPeriod.endDate) {
      errors.push({
        field: "payDate",
        message: "Pay date cannot be before period end date",
        severity: "warning",
      });
    }
 
    return errors;
  }
}
 
interface ValidationResult {
  field: string;
  message: string;
  severity: "error" | "warning" | "info";
}

A practical tip: run validation at every state transition in the pay run lifecycle. Validate after calculation, before approval, and before processing. Catching inconsistencies early prevents costly corrections after payments have been disbursed.

Conclusion

Data modeling for payroll systems requires a deep understanding of temporal patterns, financial integrity constraints, and organizational complexity. The bitemporal pattern ensures that historical calculations can be reconstructed accurately. Immutable pay stubs provide a reliable audit trail. Cost allocation models bridge the gap between payroll processing and general ledger accounting.

TypeScript's type system brings significant value to payroll data modeling. Discriminated unions for statuses and compensation types, nullable types for temporal boundaries, and interface-driven entity definitions create a contract between the data layer and the business logic that catches errors at compile time rather than in production pay runs.

The models described here form the backbone of a payroll system that can handle multi-entity operations, complex compensation structures, and the ever-changing regulatory landscape that makes payroll one of the most demanding domains in business software.

Related Articles

business

Scaling Payroll Processing for Growing Organizations

A strategic and technical guide to scaling payroll systems as organizations grow, covering batch processing optimization, infrastructure scaling patterns, and the operational strategies that keep payroll reliable at scale.

10 min read
business

Security Best Practices for Payroll Systems

A comprehensive guide to securing payroll systems, covering data encryption, access controls, PII protection, threat modeling, and the security architecture that protects sensitive employee financial data.

10 min read
business

Digital Payroll Transformation: Strategy Guide

A strategic guide to modernizing payroll operations through digital transformation, covering technology selection, change management, compliance continuity, and the business case for building custom payroll infrastructure.

9 min read