Expand — Migrate — Contract

-- 1. EXPAND: a nullable column with no default is a fast metadata-only change.
--    NOT NULL and a backfill at this step would take ACCESS EXCLUSIVE on the table.
ALTER TABLE users ADD COLUMN email_normalized TEXT;

-- 2. DUAL-WRITE is the application's job: both fields written in one transaction.

-- 3. BACKFILL in batches with a checkpoint, not a single UPDATE:
--    one big UPDATE = long locks, WAL volume blowup, bloat,
--    replica lag, and no way to interrupt the job.
WITH batch AS (
    SELECT id FROM users
    WHERE email_normalized IS NULL AND id > :checkpoint
    ORDER BY id LIMIT 5000 FOR UPDATE SKIP LOCKED
)
UPDATE users u SET email_normalized = lower(u.email)
FROM batch b WHERE u.id = b.id
RETURNING u.id;   -- the max returned id becomes the new :checkpoint

-- 4. VERIFY: there must be no mismatches; this gates the move to step 5
SELECT count(*) FROM users
WHERE email_normalized IS DISTINCT FROM lower(email);

-- 5-6. Reads switch via a flag (72); dual write stays on for the whole observation period.

-- 7. The constraint is added in two steps to avoid holding a long lock
ALTER TABLE users ADD CONSTRAINT users_email_norm_not_null
    CHECK (email_normalized IS NOT NULL) NOT VALID;
ALTER TABLE users VALIDATE CONSTRAINT users_email_norm_not_null;

-- 8. CONTRACT: only once no live deployment still reads the old field
ALTER TABLE users DROP COLUMN email;

# Same for API fields: the deprecated field lives alongside; return both.
# The marker goes on the field itself, so it reaches JSON Schema and OpenAPI
class UserOut(BaseModel):
    email: str = Field(deprecated="Use email_normalized")
    email_normalized: str