Tax Discounts and Rate Reductions Schema

Overview

The payroll system supports tax benefits for employees based on their number of children and age group through two main tables:

1. payrollRuleTaxDiscounts - Stores annual tax discount amounts
2. payrollRuleBracketRateReductions - Stores reduced tax bracket rates

These tables allow jurisdictions like Greece to implement complex tax benefit systems where families with children receive both tax discounts and reduced tax rates.

Schema Structure

Base Rule (payrollRules)

• Contains default/base contribution rates and tax brackets
• Used when no specific tax benefits apply

Tax Discounts (payrollRuleTaxDiscounts)

• Links to a payrollRule via payrollRuleId
• Contains numberOfChildren field (0-9)
• Stores discountAmount (annual tax discount amount)
• Contains reductionThreshold (income threshold above which discount reduces, default €12,000)
• Contains reductionAmount (amount to reduce per €1,000 above threshold, default €20)

Bracket Rate Reductions (payrollRuleBracketRateReductions)

• Links to a payrollRule via payrollRuleId
• Contains nullable ageGroup and numberOfChildren fields
• NULL values act as wildcards (match any value)
• bracketFrom identifies which tax bracket this reduction applies to
• reducedRatePct is the reduced tax rate percentage
• Has priority field to determine precedence when multiple rules match

Matching Logic

When calculating payroll for an employee:

1. Get tax discount based on numberOfChildren

   • If numberOfChildren is provided, look up the discount
   • Calculate effective discount after income-based reduction:
     • If annual income ≤ reductionThreshold: use full discountAmount
     • If annual income > reductionThreshold: reduce by reductionAmount per €1,000 above threshold
     • Formula: effectiveDiscount = discountAmount - floor((annualIncome - reductionThreshold) / 1000) * reductionAmount

2. Get bracket rate reductions based on numberOfChildren and ageGroup

   • Filter by matching criteria:
     • ageGroup matches employee's age group OR is NULL
     • numberOfChildren matches employee's number of children OR is NULL
   • Sort by priority (ascending - lower number = higher priority)
   • Select the most specific match for each bracket
   • Fallback: If no match found, use standard rates from payrollRuleBrackets

3. Calculate tax using the Greece 2026 method:

   • Calculate tax using standard or reduced bracket rates
   • Subtract the effective tax discount from the calculated tax

Priority Guidelines

• Lower priority number = Higher priority (more specific)
• Most specific rules (both ageGroup and numberOfChildren set) should have priority 1-20
• Rules with one characteristic set should have priority 21-50
• Default/wildcard rules should have priority 51-100

Examples

Example 1: Employee with 3 children, annual taxable income €25,000

• Tax discount: €1,340 base discount
• Income above threshold: €25,000 - €12,000 = €13,000
• Reduction: floor(€13,000 / €1,000) × €20 = 13 × €20 = €260
• Effective discount: €1,340 - €260 = €1,080
• Rate reductions: Bracket €10,001-€20,000: 20% → 9%, Bracket €20,001-€30,000: 26% → 20%
• Calculation: Tax(€25,000 with reduced rates) - €1,080 discount

Example 2: Employee under 25 with no children

• Tax discount: €777 (0 children)
• Rate reductions: Bracket €0-€10,000: 9% → 0%, Bracket €10,001-€20,000: 20% → 0%
• Calculation: Tax(income with reduced rates) - €777 discount

Example 3: Employee with 4 children, annual taxable income €15,000

• Tax discount: €1,580 base discount
• Income above threshold: €15,000 - €12,000 = €3,000
• Reduction: floor(€3,000 / €1,000) × €20 = 3 × €20 = €60
• Effective discount: €1,580 - €60 = €1,520
• Rate reductions: Bracket €0-€10,000: 9% → 0%, Bracket €10,001-€20,000: 20% → 0%
• Result: First €20,000 is tax-free via rate reductions, plus €1,520 discount applied

Query Pattern

// Get tax discount
const discount = await getPayrollRuleTaxDiscount(
  payrollRuleId,
  numberOfChildren
);

// Calculate effective discount with income-based reduction
const monthlyTaxableIncome = grossSalary - employeeContribution;
const annualTaxableIncome = monthlyTaxableIncome * numberOfSalaries;
const effectiveDiscount = calculateEffectiveTaxDiscount(
  {
    discountAmount: discount.discountAmount,
    reductionThreshold: discount.reductionThreshold,
    reductionAmount: discount.reductionAmount,
  },
  annualTaxableIncome
);

// Get bracket rate reductions
const rateReductions = await getPayrollRuleBracketRateReductions(
  payrollRuleId,
  numberOfChildren,
  ageGroup
);

// Build map of bracket.from -> reduced rate
const bracketRateMap = new Map();
for (const reduction of rateReductions) {
  bracketRateMap.set(reduction.bracketFrom, reduction.reducedRatePct);
}

// Calculate tax
const taxOnIncome = calculateTaxOnIncome(
  annualTaxableIncome,
  taxBrackets,
  bracketRateMap
);

const finalTax = Math.max(0, taxOnIncome - effectiveDiscount);

Benefits

1. Flexible: Supports any combination of age groups and number of children (0-9)
2. Backward Compatible: Base rates in payrollRules still work for jurisdictions without variations
3. Extensible: Easy to add more characteristics in the future (e.g., disability status, marital status)
4. Performance: Indexed for efficient lookups
5. Clear Precedence: Priority system makes matching logic explicit
6. Accurate: Implements the exact Greece 2026 tax calculation method with income-based discount reduction

Greece 2026 Implementation

The seeder (seedGreecePayroll) populates:

• Tax discounts for 0-9 children:

  • 0 children: €777
  • 1 child: €900
  • 2 children: €1,120
  • 3 children: €1,340
  • 4 children: €1,580
  • 5 children: €1,780
  • 6+ children: €1,780 + €220 per additional child
  • All discounts reduce by €20 per €1,000 above €12,000 annual income

• Rate reductions for:

  • 1-9 children (various reductions)
  • Under 25 age group (0% tax on first €20,000)
  • 25-30 age group (reduced rates)

All data is based on official Greece 2026 tax regulations.