Schema Evolution Recipes

Principles

• Backward-compatible. Existing queries should keep working through the migration window.
• Forward-deployable. You can deploy the migration before any code that uses the new shape.
• Reversible. If the migration is wrong, you can roll back without losing data.
• Two-phase for structural change. Rename, type change, column drop — always two phases minimum (add new, populate, switch readers, drop old).
• JSONB for flexibility. When you don't know yet whether a field belongs as a typed column, the JSONB sidecar pattern buys you time.

Adding a column

The easy case. Add nullable; existing queries ignore it.

-- Migration: add jurisdiction column to agreements
ALTER TABLE agreements ADD COLUMN jurisdiction TEXT;

-- Backfill is optional and can be done lazily.
-- Existing queries continue to work; new queries can use the column.

If the column needs a default

For very large tables, adding a column with a non-null default rewrites the whole table on some database engines. Modern Postgres (11+) handles this efficiently for static defaults. For older versions or other engines, add nullable, then backfill in batches, then add the constraint.

Two-phase rename

The most-error-prone pattern. Renaming a column in production breaks every consumer that hasn't been updated yet.

Two-phase recipe

1. Phase 1: add the new column. Populate via trigger or batch backfill from the old column.
2. Deploy code that writes to both columns.
3. Migrate readers one by one to use the new column.
4. Once all readers are migrated, stop writing to the old column.
5. Phase 2 (later release): drop the old column.

-- Phase 1
ALTER TABLE agreements ADD COLUMN current_end_date DATE;
UPDATE agreements SET current_end_date = current_term_end;
-- (Then deploy code that writes to both; migrate readers.)

-- Phase 2 (after all readers migrated)
ALTER TABLE agreements DROP COLUMN current_term_end;

The cost is days or weeks of overhead and dual-writing. The benefit is zero downtime, no broken consumers.

JSON sidecar pattern

When you're not sure whether a new field should be a typed column yet — because it's customer-specific, experimental, or you haven't seen the full data variance — put it in a JSONB sidecar.

-- Add a flexible sidecar to agreements
ALTER TABLE agreements ADD COLUMN extra JSONB;

-- Use it for fields you'll later promote
UPDATE agreements
SET extra = jsonb_set(COALESCE(extra, '{}'),
                      '{billing_frequency}', '"monthly"'::jsonb)
WHERE agreement_id = 'agr_x';

-- Query
SELECT agreement_id, extra->>'billing_frequency' AS billing_frequency
FROM agreements;

Once a field stabilizes (used by many queries, mature definition), promote it to a typed column via the two-phase rename pattern.

When NOT to use the sidecar

• Fields that drive joins or indexes — typed columns first.
• Fields with strict type or constraint requirements — typed columns enforce that better.
• Fields that are core to the product. The sidecar is for experimental; the model is for stable.

Adding a new document class

The pattern that runs most frequently in customer deployments. Schema doesn't change — only the doc_class taxonomy expands.

1. Add the new class to the taxonomy config (often a JSON or YAML file).
2. Configure extraction prompts / templates for the new class.
3. Set initial confidence threshold (default 0.85; recalibrate).
4. Run on a pilot sample (100 documents); customer-side analysts validate.
5. Add to the SLA dashboard.
6. Update the working agreement to reflect the new class.

No SQL migration required — the doc_class column already accepts any string. Just consistent usage.

Adding a new obligation type

Similar. The obligation_type column is a string; new types are added to the taxonomy.

1. Define the JSONB value shape for the new obligation type.
2. Add to the taxonomy config.
3. Update extraction prompts to extract the new type when present.
4. Backfill on historical documents if the type was always there (e.g., you just started extracting it).
5. Update dbt models to surface the new type in the mart layer.

Customer-specific extensions

Some customers have unique fields that don't apply to others. Three patterns:

Pattern 1: Customer-namespaced JSONB

Put it in a sidecar JSONB column, namespace by customer:

-- One sidecar column per entity, key by customer
ALTER TABLE agreements ADD COLUMN customer_extensions JSONB;

UPDATE agreements
SET customer_extensions = jsonb_set(COALESCE(customer_extensions, '{}'),
                                    '{acme_corp,vendor_program_tier}', '"gold"'::jsonb)
WHERE agreement_id = 'agr_acme_x';

Pattern 2: Per-customer extension table

For customers with many unique fields, a separate table:

CREATE TABLE acme_agreement_extensions (
  agreement_id TEXT PRIMARY KEY REFERENCES agreements,
  vendor_program_tier TEXT,
  internal_owner TEXT,
  contract_review_status TEXT
);

Heavier-weight but cleaner queries. Useful when the customer's data team will be writing dashboards on top.

Pattern 3: Generic key-value table

Most flexible; weakest performance:

CREATE TABLE entity_attributes (
  entity_type TEXT NOT NULL,
  entity_id TEXT NOT NULL,
  customer_id TEXT NOT NULL,
  attr_key TEXT NOT NULL,
  attr_value JSONB,
  PRIMARY KEY (entity_type, entity_id, customer_id, attr_key)
);

Rarely the right call. Use pattern 1 or 2 first.

Migration cadence

Operating recommendations:

• Migrations ship in their own deploy, separate from code changes that use them. Reduces blast radius if a migration fails.
• Reversible migrations — every migration has an "down" path. Some teams enforce this in CI.
• Per-customer migrations for multi-tenant. If a migration is non-trivial, roll it customer-by-customer. Big customers' freezes will force this anyway.
• dbt model rebuilds happen automatically when source schema changes; tests catch regressions before they hit customer-facing dashboards.
• Drop columns slowly. The typical lifecycle is: add → backfill → migrate readers → wait at least one release cycle → drop. Resist the urge to drop in the same release as the rename.

✓ Check yourself

Answer from memory before opening each one.

1. Why is adding a nullable column the safe migration, and when does adding a column with a default stop being cheap?

   Show answer

   A nullable column without a default is a catalog change, so existing queries and writers ignore it. Postgres 11 and later also add a column with a constant default without rewriting the table. A volatile default, or an older version or engine, rewrites every row and holds locks. See adding a column.

2. You rename current_term_end to current_end_date. Put the steps in order, and say what breaks if you drop the old column in the same release as the reader switch.

   Show answer

   Add the new column and backfill, dual-write, move readers, stop writing the old column, and drop it in a later release. A same-release drop breaks any reader or job that has not been redeployed or that is still running old code. See the two-phase rename.

3. A researcher stores jurisdiction in the JSONB sidecar, and a join to a tax-rate table now uses it. What do you say?

   Show answer

   Fields that drive joins and indexes belong in typed columns, which give constraints and cheaper joins. Promote it with the two-phase pattern. The sidecar is for fields that are still unstable. See when not to use the sidecar.

4. A customer wants a new document class. What changes in the database, and what silent risk remains?

   Show answer

   Nothing in the schema, because doc_class accepts any string. The work is in the taxonomy config, extraction prompts, thresholds and a pilot. The risk is that nothing enforces spelling, so a typo creates a new class unseen. See adding a new document class.

5. A customer needs 25 custom fields that their analysts will chart. Which of the three extension patterns do you choose, and what would push you to another?

   Show answer

   A per-customer extension table, because analysts get typed columns and plain joins. A namespaced JSONB column suits a handful of fields, and a generic key-value table is the last resort for its poor performance and weak typing. See customer-specific extensions.

Takeaway

• Every migration should be backward-compatible, deployable ahead of the code that uses it, and reversible.
• Adding a nullable column is the safe case; renames, type changes and drops always take two phases.
• Never drop a column in the release that moves its readers.
• A JSONB sidecar holds unstable fields, and stable ones, especially join keys, move to typed columns.
• New document classes and obligation types are configuration changes, which makes consistent naming the real control.
• Write and test the rollback before you deploy the migration.

Once the schema can change safely, tests keep the data honest. → Quality Tests and dbt Patterns

Further reading

• PostgreSQL: ALTER TABLE — PostgreSQL documentation. What ADD COLUMN and DROP COLUMN do to the table, including the no-rewrite default behavior.
• PostgreSQL: JSON types — PostgreSQL documentation. JSONB storage and indexing, relevant to the sidecar pattern.
• PostgreSQL: JSON functions and operators — PostgreSQL documentation. The jsonb_set function used in the sidecar and extension recipes.
• dbt: Incremental models — dbt Labs documentation. How downstream models behave when a source schema changes, including the schema-change options.