🔥 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/4

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
Week 4 Pattern: JSONB Migration PostgreSQL

Consolidating preference columns into JSONB

A SaaS product wants to let users store arbitrary preferences. The team replaces three discrete columns with a single preferences JSONB column for flexibility:

Before

-- Schema v1 CREATE TABLE users ( id SERIAL PRIMARY KEY, email VARCHAR(255) NOT NULL UNIQUE, notification_email BOOLEAN DEFAULT true, theme VARCHAR(20) DEFAULT 'light', timezone VARCHAR(50) DEFAULT 'UTC', created_at TIMESTAMP DEFAULT NOW() );

After

-- Schema v2 CREATE TABLE users ( id SERIAL PRIMARY KEY, email VARCHAR(255) NOT NULL UNIQUE, preferences JSONB DEFAULT '{"email":true,"theme":"light","tz":"UTC"}'::jsonb, created_at TIMESTAMP DEFAULT NOW() );

🧠 What's the highest risk?

Correct answer: A High Risk

This is a classic semantic breaking change. The schema migration itself is safe — adding a nullable JSONB column is instant in PostgreSQL. But every piece of application code that queries WHERE notification_email = true, SELECT theme FROM users, or ORDER BY timezone will fail because those columns no longer exist. Safe migration: Use expand/contract — add the JSONB column, dual-write to both old and new columns, migrate all reads to the new JSONB path, then drop the old columns in a follow-up deploy.

🔍 Diff this in SchemaLens

Prefer a speed test? Try the Schema Diff Speed Challenge — race the clock to spot changes manually.

Want more challenges?

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

Try SchemaLens Free Explore 60+ Tools