Schema Evolution Workflow

Translate the user's casual request into rigorous architecture: no one-line ALTER TABLE, no silent JSONB dump, no duplicate column.

Also triggered at the end of a mermaid-diagram-review when the user accepts the counter-proposal.

Procedure (must follow in order)

Step 1 — Inspect

Invoke postgres-schema-introspection for the target table and its immediate neighbors (incoming + outgoing FKs). Specifically:

• All columns (look for duplicates of what the user wants to add)
• JSONB key distribution (look for prior informal use of this attribute)
• All related tables (the attribute might belong on a neighbor)
• All RLS policies (the new attribute might break or require a policy)

Step 2 — Consult prior decisions

Search .specify/memory/schema-change-log.md for prior decisions about this entity or this attribute. If a prior decision exists, cite it. Don't re-litigate settled questions.

Step 3 — Five-placement analysis

Walk all five options every time. Per option emit one sentence each for "could fit" and "doesn't fit" / "best because". End with "Recommended: Option X because…".

Step 4 — Map the blast radius

Produce this table — fill every row, write N/A explicitly when inapplicable. Rows marked (if relevant) may be omitted only when genuinely not applicable to the change:

Step 5 — Migration plan (four phases, always)

Templates (CREATE INDEX CONCURRENTLY, never table rewrites):

-- P1 Expand:  brief AccessExclusiveLock + ShareUpdateExclusiveLock
ALTER TABLE <t> ADD COLUMN <c> <type>;
CREATE INDEX CONCURRENTLY idx_<t>_<c> ON <t> (tenant_id, <c>);

-- P2 Backfill: row-level only; loop 5,000-row batches, sleep 100 ms
WITH batch AS (
    SELECT id FROM <t> WHERE <c> IS NULL AND id > $last
    ORDER BY id LIMIT 5000
)
UPDATE <t> SET <c> = <expr> WHERE id IN (SELECT id FROM batch);

-- P3 Cutover: in-place index → constraint upgrade, no rewrite
ALTER TABLE <t> ADD CONSTRAINT uq_<t>_<c>
    UNIQUE USING INDEX idx_<t>_<c>;

-- P4 Contract: brief AccessExclusiveLock for revalidation
ALTER TABLE <t> ALTER COLUMN <c> SET NOT NULL;
ALTER TABLE <t> DROP COLUMN <old>;

Rollback per phase: drop the new column/constraint/index. P4 dropped column needs a pre-P4 backup to restore.

Step 6 — Test surface

One sentence per test. Specify tenant, RLS case, race condition concretely.

Step 7 — Eventing & projection delta

Name (or version-bump on existing), JSON-Schema payload delta, consumer impact (Neo4j worker? analytics? notifications?), replay idempotency story.

Step 8 — Pending change-log entry

Draft (do not commit) the .specify/memory/schema-change-log.md entry in that file's format. Show it to the user.

Step 9 — ADR check

Any deviation from defaults (missing FK, no RLS, JSONB for a critical field, destructive migration, cross-module reach) → invoke adr-drafting. Refuse to produce final migration SQL until ADR is Proposed.

Hard rules

• Never propose Option A without checking for duplicates in Step 1.
• Never default to Option B (JSONB) to avoid migration work. Justify explicitly against the forbidden-use list in Step 3.
• Never skip the five-placement analysis. The reasoning is the deliverable.
• Never produce SQL without showing the lock impact and rollback.
• Never apply the migration yourself. Produce the file; the user runs it.

Output structure (use these exact headings)

## Inventory of the affected area
## Five-placement analysis
## Recommended placement
## Blast radius
## Migration plan
## Test surface
## Eventing & projection delta
## Pending change-log entry
## ADR needed?

Worked example

A complete walk-through (desired_sell_price on pricing_rules — all nine steps with full SQL, blast radius, tests, event delta, change-log entry) lives in EXAMPLE-PRICING.md.