The Schema That Survived Three Pivots
How a flexible data model held up across three major product pivots — and what made it resilient.
Context
A project I worked on went through three significant pivots: first a generic task manager, then a project-specific workflow tool, then a team collaboration platform. Each pivot changed what 'a project' or 'an item' meant. Rewriting the schema every time would have been painful. I needed a data model that could absorb change without constant migrations. The challenge was balancing flexibility with query performance and data integrity.
Constraints
- Limited migration windows — we couldn't afford long-running migrations or downtime
- Existing data had to be preserved — we couldn't just drop and recreate
- Query patterns changed with each pivot — the schema had to support new access patterns
Architecture
I used a few patterns: polymorphic associations for items that could belong to different parent types (project, board, workspace), a flexible metadata JSONB column for attributes that varied by item type, and a strict core schema for fields that were stable (id, tenant_id, created_at, type). The key was separating 'what we know for sure' from 'what might change.' Core fields stayed in columns; variable attributes went into JSONB. We used PostgreSQL's JSONB indexing for the fields we queried often. Migrations were additive when possible — new columns, new indexes — and we avoided dropping columns until we were sure they were dead.
Alternatives considered
- EAV (Entity-Attribute-Value) model for maximum flexibility: EAV makes querying and reporting painful. JSONB gave us flexibility without sacrificing the ability to run efficient queries on common attributes.
- Separate tables per item type: Would have required a new table and migration for every new type. Polymorphic associations let us add types without schema changes.
Lessons learned
- Identify the stable core vs the variable surface. Invest in getting the core right; use JSONB or similar for the rest.
- Additive migrations are safer. New columns with defaults, new indexes — avoid destructive changes until you're certain.
- Document the schema evolution. Future you will thank present you when debugging why a column exists.