Migrate residency derivation from finance_incomes to Persons table

[jcleow]

Summary

The deriveResidency function in property_planner.go attempts to query residency_status from the finance_incomes table, but this column does not exist. The Persons table should be the single source of truth for residency information.

Additionally, the Persons table is missing the 'foreigner' enum value which is needed for ABSD calculations.

Current Broken Implementation

Location: backend/internal/financial_v2/repository/property_planner.go:708-729

// deriveResidency looks up residency_status from finance_incomes table
func (s *Store) deriveResidency(ctx context.Context, incomeID *string) string {
    // ...
    err := s.pool.QueryRow(ctx, `
        SELECT COALESCE(residency_status, 'singapore_citizen')
        FROM finance_incomes  // ❌ finance_incomes has NO residency_status column!
        WHERE id = $1
    `, *incomeID).Scan(&residencyStatus)
    // ...
}

Current Data Model

Table	Has residency_status?	Has person_id?
persons	✅ Yes ('citizen', 'pr') - missing 'foreigner'	N/A
finance_incomes	❌ No	✅ Yes (FK to persons)
cpf_accounts	❌ No (migrated out)	✅ Yes (FK to persons)

Required Changes

1. Add 'foreigner' to Persons table enum

Create migration to extend the CHECK constraint:

ALTER TABLE persons DROP CONSTRAINT IF EXISTS persons_residency_status_check;
ALTER TABLE persons ADD CONSTRAINT persons_residency_status_check 
    CHECK (residency_status IN ('citizen', 'pr', 'foreigner'));

2. Update deriveResidency function

Join finance_incomes → persons via person_id and read residency from persons:

func (s *Store) deriveResidency(ctx context.Context, incomeID *string) string {
    const defaultResidency = "singapore_citizen"
    if incomeID == nil {
        return defaultResidency
    }

    var residencyStatus string
    err := s.pool.QueryRow(ctx, `
        SELECT COALESCE(p.residency_status, 'citizen')
        FROM finance_incomes i
        LEFT JOIN persons p ON i.person_id = p.id
        WHERE i.id = $1
    `, *incomeID).Scan(&residencyStatus)
    if err != nil {
        return defaultResidency
    }

    return mapPersonResidencyToProperty(residencyStatus)
}

3. Add residency value mapping

Map Persons values to Property ABSD values:

• 'citizen' → 'singapore_citizen'
• 'pr' → 'permanent_resident'
• 'foreigner' → 'foreigner'

4. Fix test fixture

• property_planner_test.go:474 inserts residency_status into finance_incomes (invalid)
• Should: Create a person with residency and link income via person_id

Correct Pattern to Follow

CPF accounts already correctly derive residency by joining to persons:

// cpf_account.go:99-104 - CORRECT pattern
SELECT ...
       p.residency_status,
       p.pr_grant_date
FROM cpf_accounts c
LEFT JOIN persons p ON c.person_id = p.id

Files Affected

• backend/migrations/ - New migration for foreigner enum
• backend/internal/financial_v2/repository/property_planner.go - Fix deriveResidency
• backend/internal/financial_v2/repository/property_planner_test.go - Fix test fixture
• frontend/src/types/person.ts - Add foreigner to ResidencyStatus type

[linear]

ASS-75