Add Data Retention Columns to User Mapping Tables

Everyone can contribute. Help move this issue forward while earning points, leveling up and collecting rewards.

• Close this issue

Background

To implement the data retention policy #527002 for user mapping tables, we need to add new columns to track expiration dates for placeholder memberships and reference records.

Database Changes

1. import_source_users Table

• Add column: reassignment_expires_at (timestamp, nullable)
  • Purpose: Track deadline for user reassignment eligibility
  • Default: null (to be set via backend)
  • Behavior: When timestamp is reached, import source status transitions to "keep as placeholder"
• Add index: reassignment_expires_at
• Migration: Existing records should be backfilled with migration date + 1 year

2. import_source_user_placeholder_references Table

• Add column: expires_at (timestamp, not null)
  • Purpose: Mark when references becomes eligible for deletion
  • Default: null (to be set via backend)
• Add index: index_on_expires_at on (expires_at)

3. import_placeholder_memberships Table

• Add column: expires_at (timestamp, not null)
  • Purpose: Mark when membership becomes eligible for deletion
  • Default: null (to be set via backend)
• Add index: index_on_expires_at on (expires_at)

Tasks

• Create database migrations for new columns
• Add indexes for efficient cleanup queries
• Implement backfill for existing records in import_source_users