Aller au contenu

Story AB-002: Data Model + Flyway Migrations

Module: absence-management Slice: VS-AB-002 from architecture Brand context: [BOTH] Papillon HR Suite + ALTARYS ENTERPRISE HR Suite Priority: 2 Depends on: AB-001 Can develop concurrently with: None Merge order: After AB-001, Before AB-003 Estimated complexity: M PRD User Stories: N/A - Technical Story

Objective

Create all database tables and Spring Data JDBC entities for the Absence Management module via Flyway migrations, establishing the data foundation for all subsequent stories. This slice defines the canonical schema for leave requests, balances, employee profiles, configuration tables, and public holidays — every AB story from AB-003 onward depends on this schema.

Backend Scope

Entities & Records

1. LeaveRequest

Field Type Constraints
id UUID v7 PK, server-generated
tenant_id UUID NOT NULL
employee_id UUID NOT NULL
leave_type_code VARCHAR(20) NOT NULL
event_type_code VARCHAR(30) Nullable (for exceptional leave sub-type)
start_date DATE NOT NULL
end_date DATE NOT NULL
half_day_start BOOLEAN NOT NULL, default false
half_day_end BOOLEAN NOT NULL, default false
number_of_days DECIMAL NOT NULL (jours ouvrables)
number_of_days_ouvrables DECIMAL NOT NULL
status VARCHAR(30) NOT NULL (DRAFT, PENDING_APPROVAL, APPROVED, REJECTED, CANCELLED)
reason TEXT Nullable
rejection_reason TEXT Nullable
cancellation_reason TEXT Nullable
approver_id UUID Nullable, FK conceptual to User
is_retroactive BOOLEAN NOT NULL, default false
supporting_document_id UUID Nullable
submitted_offline BOOLEAN NOT NULL, default false
created_at TIMESTAMP WITH TIME ZONE NOT NULL
updated_at TIMESTAMP WITH TIME ZONE NOT NULL
version INT NOT NULL, default 0 (optimistic locking)
created_by UUID NOT NULL

2. LeaveEmployeeProfile

Field Type Constraints
id UUID v7 PK, server-generated
tenant_id UUID NOT NULL
employee_id UUID NOT NULL, UNIQUE per tenant
annual_balance_ouvrables DECIMAL NOT NULL, default 0
exceptional_days_used_this_period DECIMAL NOT NULL, default 0
sick_days_current_rolling_12m DECIMAL NOT NULL, default 0
sick_leave_phase VARCHAR(20) Nullable (FULL_PAY, HALF_PAY, UNPAID)
number_of_children_under_21 INTEGER NOT NULL, default 0
reference_period_start DATE NOT NULL
reference_period_end DATE NOT NULL
last_accrual_date DATE Nullable
created_at TIMESTAMP WITH TIME ZONE NOT NULL
updated_at TIMESTAMP WITH TIME ZONE NOT NULL
version INT NOT NULL, default 0

3. LeaveBalanceLedger

Field Type Constraints
id UUID v7 PK, server-generated
tenant_id UUID NOT NULL
employee_id UUID NOT NULL
leave_type VARCHAR(20) NOT NULL
transaction_type VARCHAR(30) NOT NULL (MONTHLY_ACCRUAL, SENIORITY_BONUS, CHILDREN_BONUS, DEDUCTION, CARRY_OVER, MANUAL_ADJUSTMENT)
amount DECIMAL NOT NULL (signed: positive = credit, negative = debit)
amount_calendaires DECIMAL NOT NULL
amount_ouvres DECIMAL NOT NULL
reference_id UUID Nullable (FK to LeaveRequest or other source)
reason TEXT NOT NULL
created_at TIMESTAMP WITH TIME ZONE NOT NULL
created_by UUID NOT NULL

Index: idx_balance_ledger_lookup(tenant_id, employee_id, leave_type, created_at)

4. MaternityDeclaration

Field Type Constraints
id UUID v7 PK, server-generated
tenant_id UUID NOT NULL
employee_id UUID NOT NULL
expected_delivery_date DATE NOT NULL
actual_delivery_date DATE Nullable
leave_request_id UUID NOT NULL, FK to LeaveRequest
created_at TIMESTAMP WITH TIME ZONE NOT NULL
created_by UUID NOT NULL

5. ExceptionalLeaveConfig

Field Type Constraints
id UUID v7 PK, server-generated
tenant_id UUID NOT NULL
event_type_code VARCHAR(30) NOT NULL
duration_days DECIMAL NOT NULL
cci_minimum_days DECIMAL NOT NULL
country_code VARCHAR(2) NOT NULL
created_at TIMESTAMP WITH TIME ZONE NOT NULL
updated_at TIMESTAMP WITH TIME ZONE NOT NULL

Unique: (tenant_id, event_type_code)

6. PublicHoliday

Field Type Constraints
id UUID v7 PK, server-generated
tenant_id UUID Nullable (null = platform-level default)
country_code VARCHAR(2) NOT NULL
year INTEGER NOT NULL
date DATE NOT NULL
name_fr VARCHAR(100) NOT NULL
holiday_code VARCHAR(30) NOT NULL
is_fixed BOOLEAN NOT NULL
source VARCHAR(20) NOT NULL (SYSTEM, TENANT_CUSTOM)
created_at TIMESTAMP WITH TIME ZONE NOT NULL

Unique: (tenant_id, country_code, year, holiday_code) — Note: tenant_id nullable in unique constraint, use COALESCE or partial index.

7. CachedEmployeeData

Field Type Constraints
id UUID v7 PK, server-generated
tenant_id UUID NOT NULL
employee_id UUID NOT NULL
employee_matricule VARCHAR NOT NULL
first_name VARCHAR NOT NULL
last_name VARCHAR NOT NULL
category VARCHAR NOT NULL
hire_date DATE NOT NULL
org_unit_id UUID NOT NULL
current_manager_id UUID Nullable
employee_status VARCHAR NOT NULL
synced_at TIMESTAMP WITH TIME ZONE NOT NULL

Index: idx_cached_employee_lookup(tenant_id, employee_id)

8. LeaveTypeConfig

Field Type Constraints
id UUID v7 PK, server-generated
tenant_id UUID NOT NULL
code VARCHAR(20) NOT NULL
name_fr VARCHAR(100) NOT NULL
is_paid BOOLEAN NOT NULL
requires_document BOOLEAN NOT NULL
requires_approval BOOLEAN NOT NULL
accrual_based BOOLEAN NOT NULL
max_days_per_year DECIMAL Nullable
is_system BOOLEAN NOT NULL, default true
is_active BOOLEAN NOT NULL, default true
country_code VARCHAR(2) NOT NULL
created_at TIMESTAMP WITH TIME ZONE NOT NULL
updated_at TIMESTAMP WITH TIME ZONE NOT NULL

Unique: (tenant_id, code)

9. AbsenceSettings

Field Type Constraints
id UUID v7 PK, server-generated
tenant_id UUID NOT NULL, UNIQUE
deduction_unit VARCHAR NOT NULL (OUVRABLES, OUVRES, CALENDAIRES)
reference_period_type VARCHAR NOT NULL (CALENDAR_YEAR, CUSTOM)
carry_over_policy VARCHAR NOT NULL (NO_CARRY_OVER, FULL_CARRY_OVER, PARTIAL_CARRY_OVER)
partial_carry_over_max_days DECIMAL Nullable (required when carry_over_policy = PARTIAL_CARRY_OVER)
created_at TIMESTAMP WITH TIME ZONE NOT NULL
updated_at TIMESTAMP WITH TIME ZONE NOT NULL

10. SickLeaveIndemnizationRule

Field Type Constraints
id UUID v7 PK, server-generated
tenant_id UUID NOT NULL
country_code VARCHAR(2) NOT NULL
employee_category VARCHAR NOT NULL
seniority_min_years INT NOT NULL
seniority_max_years INT Nullable
full_pay_months INT NOT NULL
half_pay_months INT NOT NULL
total_months INT NOT NULL
created_at TIMESTAMP WITH TIME ZONE NOT NULL
updated_at TIMESTAMP WITH TIME ZONE NOT NULL

Unique: (tenant_id, country_code, employee_category, seniority_min_years)

Repository Layer

All repositories are Spring Data JDBC interfaces in package com.altarys.papillon.modules.absence.repository:

  • LeaveRequestRepository extends CrudRepository<LeaveRequest, UUID>:
  • findByTenantIdAndEmployeeId(UUID tenantId, UUID employeeId) -> List<LeaveRequest>
  • findByTenantIdAndStatus(UUID tenantId, String status) -> List<LeaveRequest>

  • findByTenantIdAndApproverId(UUID tenantId, UUID approverId) -> List<LeaveRequest>

  • LeaveEmployeeProfileRepository extends CrudRepository<LeaveEmployeeProfile, UUID>:

  • findByTenantIdAndEmployeeId(UUID tenantId, UUID employeeId) -> Optional<LeaveEmployeeProfile>

  • LeaveBalanceLedgerRepository extends CrudRepository<LeaveBalanceLedger, UUID>:

  • findByTenantIdAndEmployeeIdAndLeaveType(UUID tenantId, UUID employeeId, String leaveType) -> List<LeaveBalanceLedger>

  • MaternityDeclarationRepository extends CrudRepository<MaternityDeclaration, UUID>

  • ExceptionalLeaveConfigRepository extends CrudRepository<ExceptionalLeaveConfig, UUID>:

  • findByTenantIdAndEventTypeCode(UUID tenantId, String eventTypeCode) -> Optional<ExceptionalLeaveConfig>

  • PublicHolidayRepository extends CrudRepository<PublicHoliday, UUID>:

  • findByCountryCodeAndYear(String countryCode, int year) -> List<PublicHoliday>

  • findByTenantIdAndCountryCodeAndYear(UUID tenantId, String countryCode, int year) -> List<PublicHoliday>

  • CachedEmployeeDataRepository extends CrudRepository<CachedEmployeeData, UUID>:

  • findByTenantIdAndEmployeeId(UUID tenantId, UUID employeeId) -> Optional<CachedEmployeeData>

  • LeaveTypeConfigRepository extends CrudRepository<LeaveTypeConfig, UUID>:

  • findByTenantIdAndCode(UUID tenantId, String code) -> Optional<LeaveTypeConfig>

  • findByTenantIdAndIsActiveTrue(UUID tenantId) -> List<LeaveTypeConfig>

  • AbsenceSettingsRepository extends CrudRepository<AbsenceSettings, UUID>:

  • findByTenantId(UUID tenantId) -> Optional<AbsenceSettings>

  • SickLeaveIndemnizationRuleRepository extends CrudRepository<SickLeaveIndemnizationRule, UUID>:

  • findByTenantIdAndCountryCodeAndEmployeeCategory(UUID tenantId, String countryCode, String category) -> List<SickLeaveIndemnizationRule>

Service Layer

N/A — No business logic in this slice. Only schema + entities + repositories.

API Endpoints

N/A — No endpoints in this slice.

Validation Rules

  • All DECIMAL fields must use BigDecimal in Java — never double or float
  • All DATE columns map to java.time.LocalDate in entities
  • All TIMESTAMP WITH TIME ZONE columns map to java.time.Instant in entities
  • tenant_id is NOT NULL on all entities except PublicHoliday (nullable for platform-level defaults)
  • version field on LeaveRequest and LeaveEmployeeProfile enables optimistic locking via @Version
  • Unique constraints must include tenant_id for tenant isolation

Multi-Tenant Considerations

  • Every table includes tenant_id column (except PublicHoliday where it's nullable for platform defaults)
  • All unique constraints include tenant_id to prevent cross-tenant collisions
  • All repository query methods include tenantId parameter
  • Schema works across all 4 DB profiles: shared (tenant_id column filtering), schema-per-tenant (tenant_id column + schema isolation), DB-per-tenant (tenant_id column + DB isolation), BYO-DB (tenant_id column + external DB)

Flyway Migrations

All migrations in db/tenant-migration/ (tenant-scoped database):

  • V001__create_leave_request_table.sql
  • V002__create_leave_employee_profile_table.sql
  • V003__create_leave_balance_ledger_table.sql
  • V004__create_maternity_declaration_table.sql
  • V005__create_exceptional_leave_config_table.sql
  • V006__create_public_holiday_table.sql
  • V007__create_cached_employee_data_table.sql
  • V008__create_indexes.sql
  • V009__create_leave_type_config_table.sql
  • V010__create_absence_settings_table.sql
  • V011__create_sick_leave_indemnization_rule_table.sql

Frontend Scope

N/A — Backend infrastructure story.

Pages & Routes

N/A

Components

N/A

UI States (ALL REQUIRED)

N/A — Backend only.

Responsive Behavior

N/A

Interactions

N/A

Acceptance Criteria

AC-004: All tables created with correct schema
Given the application starts with Flyway enabled
When all migrations run
Then all 10 tables are created with correct columns, types, constraints, and indexes

AC-005: Spring Data JDBC entities map correctly
Given the Spring Data JDBC repositories are defined
When a LeaveRequest entity is saved and retrieved
Then all fields round-trip correctly including BigDecimal amounts and DATE types

AC-006: Migrations are reversible
Given a migration has been applied
When a rollback is needed
Then each migration has a corresponding undo script or the migration is safely re-runnable

OHADA & Regulatory Rules

  • Data types: All monetary and day-count fields use DECIMAL / BigDecimal per CI financial calculation requirements — never float or double
  • Three-unit system: LeaveBalanceLedger stores amount (primary unit, ouvrables), amount_calendaires, and amount_ouvres to support all three computation units per CI labor law
  • Jours ouvrables: The number_of_days_ouvrables field on LeaveRequest is the canonical deduction unit per Code du Travail Art. 25.2

Standards & Conventions

  • docs/standards/java-spring-guidelines.md — §Records & sealed types, §Spring Data JDBC entities
  • docs/standards/database-guidelines.md — §Flyway migration naming, §Column types, §Timestamp types (use TIMESTAMP WITH TIME ZONE, not TIMESTAMP), §Multi-tenant unique constraints
  • docs/standards/multi-datasource-patterns.md — §Tenant-scoped repositories, §Avoid CrudRepository for platform tables
  • docs/standards/api-guidelines.md — §UUID v7 generation

Testing Requirements

Unit Tests

  • Entity field mapping: all BigDecimal fields initialized correctly
  • Entity validation: version field starts at 0
  • Enum values: status, transaction_type, deduction_unit enums contain all expected values

Integration Tests

  • Flyway migrations run without errors on a fresh database
  • All 10 tables exist after migration with correct columns and types
  • LeaveRequest save + findById round-trips all fields correctly (including BigDecimal precision)
  • LeaveEmployeeProfile unique constraint on (tenant_id, employee_id) rejects duplicates
  • LeaveBalanceLedger index is used for typical queries (EXPLAIN ANALYZE)

What QA Will Validate

  • N/A — No user-facing functionality. Developer verifies migrations and entity mappings via integration tests.

Out of Scope

  • Business logic, services, or API endpoints — just tables + entities + repositories
  • Seed data for LeaveTypeConfig or ExceptionalLeaveConfig (done in configuration stories)
  • Frontend setup of any kind
  • Event listeners (AB-003)
  • Accrual logic (AB-004)

Definition of Done

  • [ ] All backend endpoints implemented and returning correct responses — N/A (no endpoints)
  • [ ] All frontend pages render correctly at 360px, 768px, 1024px — N/A (backend only)
  • [ ] All 5 UI states implemented (loading, empty, error, offline, success) — N/A
  • [ ] French micro-copy matches design spec exactly — N/A
  • [ ] All acceptance criteria pass manually
  • [ ] Unit tests written and passing
  • [ ] Integration tests written and passing
  • [ ] Multi-tenant isolation verified — Unique constraints include tenant_id
  • [ ] Offline write+sync works (Papillon) / N/A (Enterprise) — N/A (backend only)
  • [ ] No TypeScript any — N/A
  • [ ] No Java raw types — all generics explicit
  • [ ] API responses follow standard envelope format — N/A (no endpoints)
  • [ ] Audit trail entries for every data mutation — N/A (no data mutations in this slice)