🔥 Schema Diff Weekly Challenge

Real schema changes from open-source projects. Can you spot the breaking change, estimate the risk, and choose the safe migration path? New challenge drops every week.

0/3

Your score this week

Week 1 Pattern: Soft Delete Migration PostgreSQL

Adding soft deletes to a users table

A growing SaaS needs to support user account recovery. The team decides to replace the hard-delete pattern with a deleted_at timestamp. Here's the schema change:

Before

-- Schema v1 CREATE TABLE users ( id SERIAL PRIMARY KEY, email VARCHAR(255) NOT NULL UNIQUE, username VARCHAR(50) NOT NULL, active BOOLEAN DEFAULT true, created_at TIMESTAMP DEFAULT NOW() ); CREATE INDEX idx_users_active ON users(active);

After

-- Schema v2 CREATE TABLE users ( id SERIAL PRIMARY KEY, email VARCHAR(255) NOT NULL UNIQUE, username VARCHAR(50) NOT NULL, active BOOLEAN DEFAULT true, deleted_at TIMESTAMP, created_at TIMESTAMP DEFAULT NOW() ); CREATE INDEX idx_users_active ON users(active); CREATE INDEX idx_users_deleted_at ON users(deleted_at) WHERE deleted_at IS NULL;

🧠 What's the highest risk?

Correct answer: C Medium Risk

This is a classic semantic breaking change. The schema migration itself is safe (nullable column addition, index creation), but existing application queries like SELECT * FROM users WHERE active = true will return "deleted" users because the active column is gone and nothing enforces the old logic. Safe migration: Keep active as a generated column based on deleted_at, or update all queries before deploying the schema change.

🔍 Diff this in SchemaLens
Week 2 Pattern: ID Type Expansion PostgreSQL

Migrating primary keys from INT to BIGINT

A social platform is approaching 2.1 billion rows and hitting the INT limit. The team migrates all primary keys and foreign keys to BIGINT:

Before

-- Schema v1 CREATE TABLE posts ( id INT PRIMARY KEY, user_id INT NOT NULL, title VARCHAR(255) NOT NULL, body TEXT, created_at TIMESTAMP DEFAULT NOW() ); CREATE TABLE comments ( id INT PRIMARY KEY, post_id INT NOT NULL, user_id INT NOT NULL, body TEXT NOT NULL, created_at TIMESTAMP DEFAULT NOW() );

After

-- Schema v2 CREATE TABLE posts ( id BIGINT PRIMARY KEY, user_id BIGINT NOT NULL, title VARCHAR(255) NOT NULL, body TEXT, created_at TIMESTAMP DEFAULT NOW() ); CREATE TABLE comments ( id BIGINT PRIMARY KEY, post_id BIGINT NOT NULL, user_id BIGINT NOT NULL, body TEXT NOT NULL, created_at TIMESTAMP DEFAULT NOW() );

🧠 Is this safe to deploy in a single migration?

Correct answer: B High Risk

In PostgreSQL, ALTER COLUMN ... TYPE BIGINT on a primary key requires a table rewrite and reindexes. But the bigger risk is referential integrity: if posts.id becomes BIGINT but comments.post_id stays INT, the foreign key constraint will fail. Additionally, application code (especially in languages like Java or C#) may use int types that overflow. Safe migration: Use the expand/contract pattern — add new BIGINT columns → dual-write → backfill → switch reads → drop old columns.

🔍 Diff this in SchemaLens
Week 3 Pattern: NOT NULL on Existing Data MySQL

Adding a required column without a default

A content platform wants to track when posts are published. They add a published_at column and make it NOT NULL in the same migration:

Before

-- Schema v1 CREATE TABLE posts ( id INT PRIMARY KEY AUTO_INCREMENT, author_id INT NOT NULL, title VARCHAR(255) NOT NULL, body TEXT, status VARCHAR(20) DEFAULT 'draft', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP );

After

-- Schema v2 CREATE TABLE posts ( id INT PRIMARY KEY AUTO_INCREMENT, author_id INT NOT NULL, title VARCHAR(255) NOT NULL, body TEXT, status VARCHAR(20) DEFAULT 'draft', published_at TIMESTAMP NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP );

🧠 What happens when you run this migration on a table with 10M rows?

Correct answer: C High Risk

MySQL (and most databases) will reject ALTER TABLE ... ADD COLUMN ... NOT NULL on a non-empty table unless you also provide a DEFAULT value. Even if you add a default, MySQL 8.0 may rewrite the table to populate it. Safe migration: (1) Add published_at TIMESTAMP NULL — instant in MySQL 8.0. (2) Backfill existing rows in batches. (3) Change to NOT NULL in a follow-up migration after verifying no NULLs remain.

🔍 Diff this in SchemaLens

Want more challenges?

New schema diff challenges drop every week. Follow along to sharpen your migration safety instincts.

Try SchemaLens Free Explore 60+ Tools