65 lines
2.2 KiB
Markdown
65 lines
2.2 KiB
Markdown
# Pension Schedule Schema Fix
|
|
|
|
## Issue
|
|
The `pension_schedule` table had an incorrect schema that prevented importing vesting schedules with multiple milestones per pension.
|
|
|
|
### Original Error
|
|
```
|
|
UNIQUE constraint failed: pension_schedule.file_no, pension_schedule.version
|
|
```
|
|
|
|
## Root Cause
|
|
The table was defined with a **composite primary key** on `(file_no, version)`, which only allowed one vesting schedule entry per pension. However, pension vesting schedules often have **multiple milestones** (e.g., 20% vested at year 1, 50% at year 3, 100% at year 5).
|
|
|
|
### Example from Data
|
|
File `1989.089`, Version `A` has 3 vesting milestones:
|
|
- 12/31/1989: 10% vested
|
|
- 12/31/1990: 10% vested
|
|
- 12/31/1991: 10% vested
|
|
|
|
## Solution
|
|
Changed the table schema to use an **auto-increment integer** as the primary key, allowing multiple vesting schedule entries per pension:
|
|
|
|
### Before
|
|
```python
|
|
class PensionSchedule(Base):
|
|
file_no = Column(String, primary_key=True)
|
|
version = Column(String, primary_key=True)
|
|
vests_on = Column(Date)
|
|
vests_at = Column(Numeric(12, 2))
|
|
```
|
|
|
|
### After
|
|
```python
|
|
class PensionSchedule(Base):
|
|
id = Column(Integer, primary_key=True, autoincrement=True)
|
|
file_no = Column(String, nullable=False)
|
|
version = Column(String, nullable=False)
|
|
vests_on = Column(Date)
|
|
vests_at = Column(Numeric(12, 2))
|
|
|
|
__table_args__ = (
|
|
ForeignKeyConstraint(...),
|
|
Index("ix_pension_schedule_file_version", "file_no", "version"),
|
|
)
|
|
```
|
|
|
|
## Impact
|
|
- Successfully imported **502 vesting schedule entries** for **416 unique pensions**
|
|
- Some pensions have up to **6 vesting milestones**
|
|
- No data loss or integrity issues
|
|
|
|
## Related Tables
|
|
The following tables were checked and have **correct schemas**:
|
|
- `pension_marriage` - Already uses auto-increment ID (can have multiple marriage periods)
|
|
- `pension_death` - Uses composite PK correctly (one row per pension)
|
|
- `pension_separate` - Uses composite PK correctly (one row per pension)
|
|
- `pension_results` - Uses composite PK correctly (one row per pension)
|
|
|
|
## Migration Steps
|
|
1. Drop existing `pension_schedule` table
|
|
2. Update model in `app/models.py`
|
|
3. Run `create_tables()` to recreate with new schema
|
|
4. Import data successfully
|
|
|