65 lines
2 KiB
JavaScript
65 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.'
|
||
|
|
);
|
||
|
|
};
|