---
convoy: pricing-online-local
brief_number: 1
depends_on: []
recommended_model: composer-2.5
model_tier: standard
files:
  - migrations/1783000000001_add-pricing-schema.js
  - docs/SCHEMA_MAP.md
---

# Brief 1: Pricing schema migration

## Goal (1 sentence)

Ship the node-pg-migrate migration for pricing timestamps, price history, user home zip, and geo caches.

## Files in scope (do not edit anything else)
- `migrations/1783000000001_add-pricing-schema.js`
- `docs/SCHEMA_MAP.md`

## Conventions to follow
- Generate via `npm run migrate create add-pricing-schema -- -j js`; apply with `npm run migrate up`.
- `down()` must hard-stub (throw) — see `migrations/1779853647564_initial-schema.js`.
- Update `docs/SCHEMA_MAP.md` for every new column/table (AGENTS.md § Schema changes).
- Use `ADD COLUMN IF NOT EXISTS` / `CREATE TABLE IF NOT EXISTS` for idempotency.

## Acceptance criteria
- [ ] Migration adds `cards.prices_updated_at TIMESTAMPTZ`
- [ ] Migration creates `card_price_history` with UNIQUE `(card_id, source, price_type, currency, recorded_at)` and index on `(card_id, recorded_at DESC)`
- [ ] Migration adds `users.home_zip VARCHAR(10)`
- [ ] Migration creates `geo_zip_cache` and `geo_stores_cache` tables per Architecture §3
- [ ] `docs/SCHEMA_MAP.md` updated
- [ ] `npm run migrate up` succeeds on fresh + existing DB
- [ ] No scope expansion (do not edit files outside `files:` above)

## Rationale (≤3 sentences)

All pricing and geo features depend on these columns/tables. Landing schema first unblocks parallel implementers for bulk ingest, APIs, and settings without merge conflicts on migration files.