Edwin Eames
af9c2f99e7
FastAPI-based scraper for commodity ticker prices (HO, CL, RB futures) and competitor oil pricing from NewEnglandOil. Includes cron-driven scraping, PostgreSQL storage, and REST endpoints for price retrieval. Co-Authored-By: Claude Opus 4.6 <noreply@anthropic.com>
30 lines
1.6 KiB
SQL
30 lines
1.6 KiB
SQL
-- Database migration for eamco_scraper
|
|
-- Creates company_prices table for storing historical oil price data
|
|
|
|
-- Create company_prices table
|
|
CREATE TABLE IF NOT EXISTS company_prices (
|
|
id SERIAL PRIMARY KEY,
|
|
company_name VARCHAR(255) NOT NULL,
|
|
town VARCHAR(100),
|
|
price_decimal DECIMAL(6,3) NOT NULL,
|
|
scrape_date DATE NOT NULL,
|
|
zone VARCHAR(50) NOT NULL DEFAULT 'zone10',
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- Create indexes for efficient querying
|
|
CREATE INDEX IF NOT EXISTS idx_company_prices_company ON company_prices(company_name);
|
|
CREATE INDEX IF NOT EXISTS idx_company_prices_scrape_date ON company_prices(scrape_date);
|
|
CREATE INDEX IF NOT EXISTS idx_company_prices_zone ON company_prices(zone);
|
|
CREATE INDEX IF NOT EXISTS idx_company_prices_company_date ON company_prices(company_name, scrape_date);
|
|
CREATE INDEX IF NOT EXISTS idx_company_prices_zone_date ON company_prices(zone, scrape_date);
|
|
|
|
-- Add comment to table
|
|
COMMENT ON TABLE company_prices IS 'Historical oil price data scraped from New England Oil website';
|
|
COMMENT ON COLUMN company_prices.company_name IS 'Name of the oil company';
|
|
COMMENT ON COLUMN company_prices.town IS 'Town where the company operates';
|
|
COMMENT ON COLUMN company_prices.price_decimal IS 'Price per gallon in dollars';
|
|
COMMENT ON COLUMN company_prices.scrape_date IS 'Date when the price was listed on the website';
|
|
COMMENT ON COLUMN company_prices.zone IS 'Geographic zone (e.g., zone10 for Central Massachusetts)';
|
|
COMMENT ON COLUMN company_prices.created_at IS 'Timestamp when the record was inserted into database';
|