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.