Add Data Retention Columns to User Mapping Tables

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) — created asynchronously (table is ~52M rows); see Tasks below

3. import_placeholder_memberships Table

  • Add column: retention_expires_at (timestamp, not null) — named retention_expires_at, not expires_at as originally proposed: this table already has a live, unrelated expires_at (date) column for membership access expiry.
    • Purpose: Mark when membership becomes eligible for deletion
    • Default: null (to be set via backend)
  • Add index on retention_expires_at

Row counts on GitLab.com (2026-09-07, via Database Lab)

Table Rows
import_source_users ~533K
import_source_user_placeholder_references ~52.3M
import_placeholder_memberships ~21.1M

All three backfills use batched background migrations (BBMs). import_source_users (533K rows) would technically have fit a synchronous post-deploy migration's time budget too, but a BBM is the safer default and keeps the process consistent across all three tables. The two placeholder tables additionally need their NOT NULL constraint split into a later milestone, per doc/development/database/not_null_constraints.md's large-table process and doc/development/database/check_constraints.md (a validate: false constraint alongside a new column is only safe when the column has a DB default satisfying it — these columns don't have one, since the value is set later by application code).

Tasks

Milestone 19.4 (in review: !253935 (merged))

  • Add reassignment_expires_at, expires_at, retention_expires_at as nullable columns, each with an index
  • Queue a batched background migration to backfill import_source_users.reassignment_expires_at (~533K rows)
  • Queue a batched background migration to backfill import_source_user_placeholder_references.expires_at (~52M rows)
  • Queue a batched background migration to backfill import_placeholder_memberships.retention_expires_at (~21M rows)
  • Add application-level defaults/validations on the two placeholder tables' columns so new records always get a value
  • Schedule the import_source_user_placeholder_references.expires_at index asynchronously (table too large for a synchronous CREATE INDEX CONCURRENTLY)

All three BBMs queued in the same milestone as their column-add migrations (precedent: merge_request_diffs, see MR description). Only the NOT NULL constraint work needs a later milestone, since that's the part not_null_constraints.md's large-table process actually requires to wait for the backfill to finish.

Milestone 19.6 (not 19.5 - a BBM can only be finalized after at least one required stop has passed since it was queued; these were queued at 19.4, the next required stop is 19.5, so finalization is only valid from 19.6 onward. Confirmed at runtime: prevent_early_finalization! raises if attempted earlier.)

  • Merge !254114 (already prepared, stacked on !254095, draft/blocked until all three BBMs have completed on GitLab.com and 19.5 has passed)

Async index follow-up (no fixed milestone — check periodically starting 19.4 until each step is unblocked)

  • Verify this MR's post-deploy migration reached GitLab.com (/chatops gitlab run auto_deploy status <merge_sha>)
  • Wait for a weekend so GitLab.com's async-index job (runs every 12 min, Sat/Sun) can create index_import_source_user_placeholder_references_on_expires_at
  • Verify the index actually exists (postgres_async_indexes table, or \d+ import_source_user_placeholder_references on a thin clone)
  • Merge !254095 (already prepared, stacked on !253935 (merged), draft/blocked until the steps above are verified)

Milestone 19.6 (same milestone as finalization, per the doc's own worked example)

  • Merge !254141 (already prepared, stacked on !254114, adds and validates the NOT NULL constraint on import_source_user_placeholder_references.expires_at and import_placeholder_memberships.retention_expires_at in one step - see the MR for why validate: false + a separate async-validation milestone isn't needed here: both tables are medium/small tier, not the "very large" tier that option is for, and the backfill is already confirmed complete by finalization time)
Edited by George Koltsov