d0468015eb
Some checks failed
CI / Lint (pull_request) Has been cancelled
CI / Schema map up to date (pull_request) Has been cancelled
CI / Forbidden patterns (9 checks) (pull_request) Has been cancelled
CI / Migrations apply (node-pg-migrate) (pull_request) Has been cancelled
Visual diff / Should run? (pull_request) Has been cancelled
CI / Unit tests (vitest) (pull_request) Has been cancelled
Convoy metrics gate / Require role-event telemetry on convoy PRs (pull_request) Has been cancelled
PR Health rollup / Aggregate gate status (pull_request) Has been cancelled
Preview smoke / Should run? (pull_request) Has been cancelled
Preview smoke / Playwright smoke (pull_request) Has been cancelled
Visual diff / Screenshot diff (pull_request) Has been cancelled
Add pricing schema, free API integrations (MTG bulk, TCGdex, Foolio), valuation and sort-by-price, card detail pricing UI, and nearby store discovery via home zip and OSM geo clients. Co-authored-by: Cursor <cursoragent@cursor.com>
64 lines
2 KiB
JavaScript
64 lines
2 KiB
JavaScript
/**
|
|
* Pricing convoy schema: price timestamps, history snapshots, home zip, geo caches.
|
|
*
|
|
* @type {import('node-pg-migrate').ColumnDefinitions | undefined}
|
|
*/
|
|
export const shorthands = undefined;
|
|
|
|
/**
|
|
* @param {import('node-pg-migrate').MigrationBuilder} pgm
|
|
*/
|
|
export const up = (pgm) => {
|
|
pgm.sql(`
|
|
ALTER TABLE cards
|
|
ADD COLUMN IF NOT EXISTS prices_updated_at TIMESTAMPTZ;
|
|
|
|
CREATE TABLE IF NOT EXISTS card_price_history (
|
|
id SERIAL PRIMARY KEY,
|
|
card_id INTEGER NOT NULL REFERENCES cards(id) ON DELETE CASCADE,
|
|
source VARCHAR(32) NOT NULL,
|
|
price_type VARCHAR(32) NOT NULL DEFAULT 'market',
|
|
currency VARCHAR(3) NOT NULL DEFAULT 'USD',
|
|
amount DECIMAL(10,2) NOT NULL,
|
|
recorded_at DATE NOT NULL DEFAULT CURRENT_DATE,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
UNIQUE (card_id, source, price_type, currency, recorded_at)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_card_price_history_card_date
|
|
ON card_price_history (card_id, recorded_at DESC);
|
|
|
|
ALTER TABLE users
|
|
ADD COLUMN IF NOT EXISTS home_zip VARCHAR(10);
|
|
|
|
CREATE TABLE IF NOT EXISTS geo_zip_cache (
|
|
zip VARCHAR(10) PRIMARY KEY,
|
|
lat DOUBLE PRECISION NOT NULL,
|
|
lng DOUBLE PRECISION NOT NULL,
|
|
display_name TEXT,
|
|
geocoded_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS geo_stores_cache (
|
|
cache_key VARCHAR(64) PRIMARY KEY,
|
|
lat DOUBLE PRECISION NOT NULL,
|
|
lng DOUBLE PRECISION NOT NULL,
|
|
radius_meters INTEGER NOT NULL,
|
|
stores JSONB NOT NULL,
|
|
fetched_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_geo_stores_cache_fetched
|
|
ON geo_stores_cache (fetched_at);
|
|
`);
|
|
};
|
|
|
|
/**
|
|
* @param {import('node-pg-migrate').MigrationBuilder} pgm
|
|
*/
|
|
export const down = (pgm) => {
|
|
throw new Error(
|
|
'[migration:1783000000001_add-pricing-schema] Refusing to drop pricing schema. ' +
|
|
'Rolling back would erase card_price_history snapshots and geo caches.'
|
|
);
|
|
};
|