tbl_expense_policy_history

-1 rows


Description

Module: HRMS Expense Management
Purpose: Provides complete, immutable audit trail for all changes to expense policy configurations, preserving historical versions of policy rules, limits, and eligibility criteria.
Data: Each row represents a historical snapshot of an expense policy configuration at the time of modification, capturing only the changed fields to optimize storage while maintaining complete version history.
Process Usage:
- Automatically populated when expense policies are created, updated, or have configuration changes.
- Uses differential storage strategy to store only modified fields between versions.
- Used for compliance audits, policy evolution analysis, and historical expense validation.
Key Points:
- Differential change tracking minimizes storage while preserving complete history.
- Enables point-in-time reconstruction of policy configurations for expense validation.
- Essential for regulatory compliance and policy change impact analysis.
Business Impact: Critical for financial audits, expense dispute resolution, regulatory compliance, and maintaining complete policy evolution history.

Columns

Column Type Size Nulls Auto Default Children Parents Comments
id bigserial 19 √ nextval('tbl_expense_policy_history_id_seq'::regclass)

Primary key
Format: Long (auto-increment)
Used as: Unique identifier for each historical policy version record.
policyid int8 19 √ null

FK: public.tbl_expense_policy.id
Purpose: Links this historical record to the current/live expense policy.
Usage: Enables tracking all historical versions of a specific policy.
Storage Strategy: Always stored in every history record for efficient querying.
expense_type_id int8 19 √ null

Historical Snapshot: Expense type reference when changed.
Purpose: Preserves policy categorization changes over time.
Storage Logic: Only stored when different from previous version.
policy_name varchar 255 √ null

Historical Snapshot: Policy name when changed.
Purpose: Tracks policy naming and rebranding evolution.
Storage Logic: Only stored when different from previous version.
effective_date date 13 √ null

Historical Snapshot: Effective date when changed.
Purpose: Preserves policy activation timeline changes.
Storage Logic: Only stored when different from previous version.
payment_date varchar 100 √ null

Historical Snapshot: Payment schedule when changed.
Purpose: Tracks payment processing rule modifications.
Storage Logic: Only stored when different from previous version.
next_payment_cycle varchar 100 √ null

Historical Snapshot: Next payment cycle when changed.
Purpose: Preserves payment forecasting modifications.
Storage Logic: Only stored when different from previous version.
claim_submission_days_limit int8 19 √ null

Historical Snapshot: Submission deadline when changed.
Purpose: Critical for validating historical expense submissions.
Storage Logic: Only stored when different from previous version.
weightage int8 19 √ null

Historical Snapshot: Policy priority weight when changed.
Purpose: Preserves policy evaluation hierarchy changes.
Storage Logic: Only stored when different from previous version.
applicable_criteria text 2147483647 √ null

Historical Snapshot: Eligibility rules when changed.
Purpose: Essential for historical expense eligibility validation.
Storage Logic: Only stored when different from previous version.
JSON Structure: Preserves exact employee targeting rules for compliance.
calculate_payment int8 19 √ null

Historical Snapshot: Payment calculation method when changed.
Purpose: Tracks calculation methodology evolution.
Storage Logic: Only stored when different from previous version.
is_fixed int8 19 √ null

Historical Snapshot: Fixed/variable amount flag when changed.
Purpose: Preserves amount type configuration changes.
Storage Logic: Only stored when different from previous version.
amount int8 19 √ null

Historical Snapshot: Fixed amount value when changed.
Purpose: Critical for historical fixed-amount expense validation.
Storage Logic: Only stored when different from previous version.
allow_to_exceed_limit int8 19 √ null

Historical Snapshot: Limit exceedance permission when changed.
Purpose: Tracks policy flexibility modifications.
Storage Logic: Only stored when different from previous version.
max_number_of_instance_in_day int8 19 √ null

Historical Snapshot: Daily instance limit when changed.
Purpose: Essential for historical frequency limit validation.
Storage Logic: Only stored when different from previous version.
max_number_of_instance_in_month int8 19 √ null

Historical Snapshot: Monthly instance limit when changed.
max_number_of_instance_in_quarter int8 19 √ null

Historical Snapshot: Quarterly instance limit when changed.
max_number_of_instance_in_half_year int8 19 √ null

Historical Snapshot: Half-yearly instance limit when changed.
max_number_of_instance_in_year int8 19 √ null

Historical Snapshot: Yearly instance limit when changed.
max_amount_in_day int8 19 √ null

Historical Snapshot: Daily amount limit when changed.
Purpose: Critical for historical spending limit validation.
Storage Logic: Only stored when different from previous version.
max_amount_in_month int8 19 √ null

Historical Snapshot: Monthly amount limit when changed.
max_amount_in_quarter int8 19 √ null

Historical Snapshot: Quarterly amount limit when changed.
max_amount_in_half_year int8 19 √ null

Historical Snapshot: Half-yearly amount limit when changed.
max_amount_in_year int8 19 √ null

Historical Snapshot: Yearly amount limit when changed.
variation_type varchar 255 √ null

Historical Snapshot: Amount variation type when changed.
Purpose: Preserves complex calculation method evolution.
Storage Logic: Only stored when different from previous version.
amount_fields text 2147483647 √ null

Historical Snapshot: Additional amount fields when changed.
Purpose: Tracks multi-component calculation configuration changes.
Storage Logic: Only stored when different from previous version.
amount_field_limit text 2147483647 √ null

Historical Snapshot: Field-specific limits when changed.
Purpose: Preserves granular limit configuration evolution.
Storage Logic: Only stored when different from previous version.
amount_field_limit_day text 2147483647 √ null

Historical Snapshot: Daily field limits when changed.
amount_field_limit_month text 2147483647 √ null

Historical Snapshot: Monthly field limits when changed.
amount_field_limit_quarter text 2147483647 √ null

Historical Snapshot: Quarterly field limits when changed.
amount_field_limit_half_year text 2147483647 √ null

Historical Snapshot: Half-yearly field limits when changed.
amount_field_limit_year text 2147483647 √ null

Historical Snapshot: Yearly field limits when changed.
max_number_of_attachments int8 19 √ null

Historical Snapshot: Attachment limit when changed.
Purpose: Tracks documentation requirement modifications.
Storage Logic: Only stored when different from previous version.
createdby varchar 100 √ null
modifiedby varchar 100 √ null
createddate timestamp 29,6 √ null
modifieddate timestamp 29,6 √ null

Indexes

Constraint Name Type Sort Column(s)
pk_tbl_expense_policy_history Primary key Asc id

Relationships

Close relationships within degrees of separation