Worked Example — desired_sell_price on pricing_rules

A complete pass through the nine-step schema-evolution workflow.

User request

"I need to add an attribute to product to track the price the user actually wants to sell at."

Step 1 — Inspect

MCP introspection of catalog_products:

Neighbour pricing_rules: tenant-scoped, holds per-channel pricing overrides.

JSONB sampling on metadata.wholesale_price: present in ≈ 8 % of rows, inconsistent units (some cents, some dollars).

Step 2 — Consult prior decisions

schema-change-log.md shows ADR-0009 (2026-03-12): compare_at_price is the strikethrough/MSRP, distinct from the selling price. Settled.

Step 3 — Five-placement analysis

• A. New column on catalog_products — could fit if intrinsic. Doesn't fit: table at 13 columns, semantic overlap with price and compare_at_price would mislead readers.
• B. JSONB on catalog_products.metadata — already happening informally. Forbidden: § V — selling price is a price-critical field.
• C. EAV — doesn't fit: tenants don't define this attribute themselves.
• D. New related table product_target_prices — doesn't fit: overkill for a single value per product per tenant.
• E. Different entity (pricing_rules) — best fit. Selling price is a pricing-rule concern; the resolved value is the effective price exposed via a derived view.

Recommended: Option E — add pricing_rules.target_price numeric(12,2) NULL with kind = 'target_sell'. Backfill the 8 % of rows using metadata.wholesale_price, then drop the JSONB key.

Step 4 — Blast radius

Step 5 — Migration plan (expand/contract)

Phase 1 — Expand

ALTER TABLE pricing_rules
    ADD COLUMN kind text;

ALTER TABLE pricing_rules
    ADD COLUMN target_price numeric(12,2);

CREATE INDEX CONCURRENTLY idx_pricing_rules_tenant_product_kind
    ON pricing_rules (tenant_id, product_id, kind);

• Lock impact: ALTER TABLE ... ADD COLUMN (nullable, no default) takes AccessExclusiveLock for milliseconds in PG ≥ 11. CREATE INDEX CONCURRENTLY takes ShareUpdateExclusiveLock, allows concurrent reads/writes; expected duration ≈ 60 s on a 5 M-row table.
• Backward compatibility: new columns nullable, old code unaffected.
• Rollback:

  DROP INDEX CONCURRENTLY idx_pricing_rules_tenant_product_kind;
  ALTER TABLE pricing_rules DROP COLUMN target_price;
  ALTER TABLE pricing_rules DROP COLUMN kind;

Phase 2 — Backfill

-- Repeat in batches of 5,000 rows until no more rows return.
WITH batch AS (
    SELECT id, tenant_id, (metadata->>'wholesale_price')::numeric AS wp
    FROM catalog_products
    WHERE metadata ? 'wholesale_price'
      AND id > $last_id
    ORDER BY id
    LIMIT 5000
)
INSERT INTO pricing_rules (tenant_id, product_id, kind, target_price)
SELECT tenant_id, id, 'target_sell', wp
FROM batch
ON CONFLICT (tenant_id, product_id, kind) DO UPDATE
   SET target_price = EXCLUDED.target_price;

• Lock impact: row-level only; throttle 100 ms between batches.
• Backward compatibility: old code still reads metadata.wholesale_price.
• Rollback: delete inserted rows by (tenant_id, product_id, kind).

Phase 3 — Cutover

ALTER TABLE pricing_rules
    ADD CONSTRAINT uq_pricing_rules_tenant_product_kind
    UNIQUE USING INDEX idx_pricing_rules_tenant_product_kind;

• Application code now reads pricing_rules.target_price. Outbox publishes PriceChanged@v2 for every change.
• Lock impact: ALTER TABLE ... ADD CONSTRAINT upgrades the existing unique index without table rewrite.
• Backward compatibility: old metadata.wholesale_price still readable (read-only) for one deploy cycle.
• Rollback: ALTER TABLE pricing_rules DROP CONSTRAINT uq_pricing_rules_tenant_product_kind;

Phase 4 — Contract

ALTER TABLE pricing_rules
    ALTER COLUMN kind SET NOT NULL;

UPDATE catalog_products
   SET metadata = metadata - 'wholesale_price'
 WHERE metadata ? 'wholesale_price';

• Lock impact: SET NOT NULL revalidates rows; brief AccessExclusiveLock. Run after Phase 2 fully drained.
• Rollback: ALTER TABLE pricing_rules ALTER COLUMN kind DROP NOT NULL; (JSONB key cannot be restored — keep a backup of pre-Phase-4 metadata.wholesale_price snapshots in cold storage for 30 days.)

Step 6 — Test surface

• Unit — effective_price resolver returns target_price when set, falls back to price.
• Integration — read tenant 7's product 42 → returns the seeded target_price; deleting the rule restores price.
• Tenant isolation — tenant 7 cannot read tenant 8's pricing_rules.target_price even by id (RLS positive + negative).
• Migration — apply Phases 1-4 against a staging snapshot of 5 M rows; rollback Phase 4 → Phase 1; verify counts match per phase.
• Projection sync — PriceChanged@v2 projector idempotently sets Product.target_price on Neo4j; replaying the same event twice yields the same node.
• Concurrency — two writers updating target_price for the same product → second write wins, no torn read in pricing_rule_history.
• Idempotency — POST /admin/pricing/target with same Idempotency-Key twice returns the cached response.

Step 7 — Eventing & projection delta

• PriceChanged v1 → v2.

• Payload delta:

  {
    "type": "object",
    "properties": {
      "price_type": { "enum": ["list", "compare_at", "target_sell", "effective"] },
      "tenant_id": { "type": "integer" },
      "product_id": { "type": "integer" },
      "previous_amount": { "type": "string", "pattern": "^-?\\d+(\\.\\d+)?$" },
      "new_amount": { "type": "string", "pattern": "^-?\\d+(\\.\\d+)?$" }
    },
    "required": ["price_type", "tenant_id", "product_id", "new_amount"]
  }

• Consumers: Neo4j projection worker (must MERGE on (tenant_id, product_id, price_type)), analytics warehouse, notification worker.

• Replay safety: idempotent — same payload re-applied yields the same node property.

Step 8 — Pending change-log entry

## 2026-05-04 — pricing_rules.target_price (Option E)

- ADR-0010: selling price lives on `pricing_rules`, not `catalog_products`.
- Migration: 4-phase expand/contract (see SKILL).
- Backfill source: `catalog_products.metadata.wholesale_price` (8 % rows).
- Event: `PriceChanged` v1 → v2 with `price_type` discriminator.
- Neo4j: `Product.target_price` denormalized via `PriceChanged@v2` projector.

Step 9 — ADR needed?

Yes — ADR-0010: "Selling price lives in pricing_rules, not on catalog_products". Drafted via adr-drafting skill, status Proposed. Migration SQL above is conditional on ADR acceptance.