395 lines
17 KiB
SQL
395 lines
17 KiB
SQL
-- ============================================
|
|
-- MISSING DATABASE TABLES
|
|
-- ============================================
|
|
-- This file creates all tables referenced by SQL queries but missing from schema.sql
|
|
-- Run this in your Supabase SQL Editor after running schema.sql
|
|
|
|
-- ============================================
|
|
-- 1. MINUTE_BARS_ET
|
|
-- ============================================
|
|
-- Used by: premarketgaprangescreener.sql
|
|
CREATE TABLE IF NOT EXISTS public.minute_bars_et (
|
|
symbol VARCHAR(10) NOT NULL,
|
|
d_et DATE NOT NULL,
|
|
ts TIMESTAMP WITH TIME ZONE NOT NULL,
|
|
session VARCHAR(10) NOT NULL CHECK (session IN ('pre', 'rth', 'post')),
|
|
hh INTEGER,
|
|
mm INTEGER,
|
|
open NUMERIC(12, 4),
|
|
high NUMERIC(12, 4),
|
|
low NUMERIC(12, 4),
|
|
close NUMERIC(12, 4),
|
|
volume NUMERIC(15, 2),
|
|
PRIMARY KEY (symbol, ts)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_minute_bars_et_symbol_date ON public.minute_bars_et(symbol, d_et);
|
|
CREATE INDEX IF NOT EXISTS idx_minute_bars_et_session ON public.minute_bars_et(session, d_et);
|
|
CREATE INDEX IF NOT EXISTS idx_minute_bars_et_ts ON public.minute_bars_et(ts DESC);
|
|
|
|
-- ============================================
|
|
-- 2. BUCKET_MEMBERS
|
|
-- ============================================
|
|
-- Used by: premarketgaprangescreener.sql
|
|
CREATE TABLE IF NOT EXISTS public.bucket_members (
|
|
symbol VARCHAR(10) NOT NULL,
|
|
bucket VARCHAR(50) NOT NULL,
|
|
PRIMARY KEY (symbol, bucket)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_bucket_members_bucket ON public.bucket_members(bucket);
|
|
CREATE INDEX IF NOT EXISTS idx_bucket_members_symbol ON public.bucket_members(symbol);
|
|
|
|
-- ============================================
|
|
-- 3. DAILY_BARS
|
|
-- ============================================
|
|
-- Used by: premarketgaprangescreener.sql
|
|
CREATE TABLE IF NOT EXISTS public.daily_bars (
|
|
symbol VARCHAR(10) NOT NULL,
|
|
date DATE NOT NULL,
|
|
open NUMERIC(12, 4),
|
|
high NUMERIC(12, 4),
|
|
low NUMERIC(12, 4),
|
|
close NUMERIC(12, 4),
|
|
volume NUMERIC(15, 2),
|
|
PRIMARY KEY (symbol, date)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_daily_bars_symbol_date ON public.daily_bars(symbol, date DESC);
|
|
CREATE INDEX IF NOT EXISTS idx_daily_bars_date ON public.daily_bars(date DESC);
|
|
|
|
-- ============================================
|
|
-- 4. ALERTSTREAM_MONTHLY (CamelCase)
|
|
-- ============================================
|
|
-- Used by: premarketgaprangescreener.sql, optionflowrockerscorer.sql, intradayoptionfactors.sql
|
|
-- Note: Table may already exist, so we create it if not exists, then add missing columns
|
|
DO $$
|
|
BEGIN
|
|
-- Create table if it doesn't exist (with a minimal structure)
|
|
IF NOT EXISTS (SELECT 1 FROM information_schema.tables WHERE table_schema='public' AND table_name='AlertStream_monthly') THEN
|
|
CREATE TABLE public."AlertStream_monthly" (
|
|
"Date" TEXT,
|
|
"AlertDate" TEXT,
|
|
"date" TEXT,
|
|
"alert_date" TEXT,
|
|
"Ticker" TEXT,
|
|
"ticker" TEXT,
|
|
"Symbol" TEXT,
|
|
"symbol" TEXT,
|
|
"Type" TEXT,
|
|
"type" TEXT,
|
|
"AlertType" TEXT,
|
|
"alert_type" TEXT,
|
|
"Message" TEXT,
|
|
"message" TEXT,
|
|
"Timestamp" TEXT,
|
|
"timestamp" TEXT,
|
|
"alert_time_txt" TEXT,
|
|
"Notional" TEXT,
|
|
"notional" TEXT,
|
|
"Pct_of_Avg30Day" TEXT,
|
|
"pct_of_avg30day" TEXT,
|
|
"Pct_of_Avg30" TEXT,
|
|
"pct_of_avg30" TEXT,
|
|
"Price" NUMERIC(12, 4),
|
|
"price" NUMERIC(12, 4),
|
|
"Volume" NUMERIC(15, 2),
|
|
"volume" NUMERIC(15, 2)
|
|
);
|
|
END IF;
|
|
END $$;
|
|
|
|
-- Add missing columns if they don't exist
|
|
DO $$
|
|
BEGIN
|
|
-- Add all possible column variations used by different queries
|
|
IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema='public' AND table_name='AlertStream_monthly' AND column_name='Date') THEN
|
|
ALTER TABLE public."AlertStream_monthly" ADD COLUMN "Date" TEXT;
|
|
END IF;
|
|
|
|
IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema='public' AND table_name='AlertStream_monthly' AND column_name='AlertDate') THEN
|
|
ALTER TABLE public."AlertStream_monthly" ADD COLUMN "AlertDate" TEXT;
|
|
END IF;
|
|
|
|
IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema='public' AND table_name='AlertStream_monthly' AND column_name='date') THEN
|
|
ALTER TABLE public."AlertStream_monthly" ADD COLUMN "date" TEXT;
|
|
END IF;
|
|
|
|
IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema='public' AND table_name='AlertStream_monthly' AND column_name='alert_date') THEN
|
|
ALTER TABLE public."AlertStream_monthly" ADD COLUMN "alert_date" TEXT;
|
|
END IF;
|
|
|
|
IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema='public' AND table_name='AlertStream_monthly' AND column_name='Ticker') THEN
|
|
ALTER TABLE public."AlertStream_monthly" ADD COLUMN "Ticker" TEXT;
|
|
END IF;
|
|
|
|
IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema='public' AND table_name='AlertStream_monthly' AND column_name='ticker') THEN
|
|
ALTER TABLE public."AlertStream_monthly" ADD COLUMN "ticker" TEXT;
|
|
END IF;
|
|
|
|
IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema='public' AND table_name='AlertStream_monthly' AND column_name='Symbol') THEN
|
|
ALTER TABLE public."AlertStream_monthly" ADD COLUMN "Symbol" TEXT;
|
|
END IF;
|
|
|
|
IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema='public' AND table_name='AlertStream_monthly' AND column_name='symbol') THEN
|
|
ALTER TABLE public."AlertStream_monthly" ADD COLUMN "symbol" TEXT;
|
|
END IF;
|
|
|
|
IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema='public' AND table_name='AlertStream_monthly' AND column_name='Type') THEN
|
|
ALTER TABLE public."AlertStream_monthly" ADD COLUMN "Type" TEXT;
|
|
END IF;
|
|
|
|
IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema='public' AND table_name='AlertStream_monthly' AND column_name='type') THEN
|
|
ALTER TABLE public."AlertStream_monthly" ADD COLUMN "type" TEXT;
|
|
END IF;
|
|
|
|
IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema='public' AND table_name='AlertStream_monthly' AND column_name='AlertType') THEN
|
|
ALTER TABLE public."AlertStream_monthly" ADD COLUMN "AlertType" TEXT;
|
|
END IF;
|
|
|
|
IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema='public' AND table_name='AlertStream_monthly' AND column_name='alert_type') THEN
|
|
ALTER TABLE public."AlertStream_monthly" ADD COLUMN "alert_type" TEXT;
|
|
END IF;
|
|
|
|
IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema='public' AND table_name='AlertStream_monthly' AND column_name='Message') THEN
|
|
ALTER TABLE public."AlertStream_monthly" ADD COLUMN "Message" TEXT;
|
|
END IF;
|
|
|
|
IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema='public' AND table_name='AlertStream_monthly' AND column_name='message') THEN
|
|
ALTER TABLE public."AlertStream_monthly" ADD COLUMN "message" TEXT;
|
|
END IF;
|
|
|
|
IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema='public' AND table_name='AlertStream_monthly' AND column_name='Timestamp') THEN
|
|
ALTER TABLE public."AlertStream_monthly" ADD COLUMN "Timestamp" TEXT;
|
|
END IF;
|
|
|
|
IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema='public' AND table_name='AlertStream_monthly' AND column_name='timestamp') THEN
|
|
ALTER TABLE public."AlertStream_monthly" ADD COLUMN "timestamp" TEXT;
|
|
END IF;
|
|
|
|
IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema='public' AND table_name='AlertStream_monthly' AND column_name='alert_time_txt') THEN
|
|
ALTER TABLE public."AlertStream_monthly" ADD COLUMN "alert_time_txt" TEXT;
|
|
END IF;
|
|
|
|
IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema='public' AND table_name='AlertStream_monthly' AND column_name='Notional') THEN
|
|
ALTER TABLE public."AlertStream_monthly" ADD COLUMN "Notional" TEXT;
|
|
END IF;
|
|
|
|
IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema='public' AND table_name='AlertStream_monthly' AND column_name='notional') THEN
|
|
ALTER TABLE public."AlertStream_monthly" ADD COLUMN "notional" TEXT;
|
|
END IF;
|
|
|
|
IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema='public' AND table_name='AlertStream_monthly' AND column_name='Pct_of_Avg30Day') THEN
|
|
ALTER TABLE public."AlertStream_monthly" ADD COLUMN "Pct_of_Avg30Day" TEXT;
|
|
END IF;
|
|
|
|
IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema='public' AND table_name='AlertStream_monthly' AND column_name='pct_of_avg30day') THEN
|
|
ALTER TABLE public."AlertStream_monthly" ADD COLUMN "pct_of_avg30day" TEXT;
|
|
END IF;
|
|
|
|
IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema='public' AND table_name='AlertStream_monthly' AND column_name='Pct_of_Avg30') THEN
|
|
ALTER TABLE public."AlertStream_monthly" ADD COLUMN "Pct_of_Avg30" TEXT;
|
|
END IF;
|
|
|
|
IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema='public' AND table_name='AlertStream_monthly' AND column_name='pct_of_avg30') THEN
|
|
ALTER TABLE public."AlertStream_monthly" ADD COLUMN "pct_of_avg30" TEXT;
|
|
END IF;
|
|
|
|
IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema='public' AND table_name='AlertStream_monthly' AND column_name='Price') THEN
|
|
ALTER TABLE public."AlertStream_monthly" ADD COLUMN "Price" NUMERIC(12, 4);
|
|
END IF;
|
|
|
|
IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema='public' AND table_name='AlertStream_monthly' AND column_name='price') THEN
|
|
ALTER TABLE public."AlertStream_monthly" ADD COLUMN "price" NUMERIC(12, 4);
|
|
END IF;
|
|
|
|
IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema='public' AND table_name='AlertStream_monthly' AND column_name='Volume') THEN
|
|
ALTER TABLE public."AlertStream_monthly" ADD COLUMN "Volume" NUMERIC(15, 2);
|
|
END IF;
|
|
|
|
IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema='public' AND table_name='AlertStream_monthly' AND column_name='volume') THEN
|
|
ALTER TABLE public."AlertStream_monthly" ADD COLUMN "volume" NUMERIC(15, 2);
|
|
END IF;
|
|
END $$;
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_alertstream_monthly_ticker ON public."AlertStream_monthly"("Ticker");
|
|
CREATE INDEX IF NOT EXISTS idx_alertstream_monthly_symbol ON public."AlertStream_monthly"("symbol");
|
|
CREATE INDEX IF NOT EXISTS idx_alertstream_monthly_type ON public."AlertStream_monthly"("Type");
|
|
|
|
-- ============================================
|
|
-- 5. ALERTSTREAM_MONTHLY (lowercase)
|
|
-- ============================================
|
|
-- Used by: intradaysignalscorer.sql
|
|
CREATE TABLE IF NOT EXISTS public.alertstream_monthly (
|
|
symbol VARCHAR(10),
|
|
alert_type VARCHAR(50),
|
|
message TEXT,
|
|
notional NUMERIC(15, 2),
|
|
pct_of_avg30 NUMERIC(10, 2),
|
|
alert_date DATE,
|
|
alert_time_txt TEXT
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_alertstream_monthly_lower_symbol ON public.alertstream_monthly(symbol);
|
|
CREATE INDEX IF NOT EXISTS idx_alertstream_monthly_lower_date ON public.alertstream_monthly(alert_date);
|
|
CREATE INDEX IF NOT EXISTS idx_alertstream_monthly_lower_type ON public.alertstream_monthly(alert_type);
|
|
|
|
-- ============================================
|
|
-- 6. OPTIONSFLOW_MONTHLY (CamelCase)
|
|
-- ============================================
|
|
-- Used by: optionflowrockerscorer.sql, intradayflow.sql, intradayoptionfactors.sql, nearterm.sql, 1monthoptionflow.sql
|
|
-- Note: This table may already exist from setupLocalDB.js, but ensuring it's here for completeness
|
|
CREATE TABLE IF NOT EXISTS public."OptionsFlow_monthly" (
|
|
"CreatedDate" TEXT,
|
|
"CreatedTime" TEXT,
|
|
"Symbol" TEXT,
|
|
"Type" TEXT,
|
|
"Volume" TEXT,
|
|
"Price" TEXT,
|
|
"Side" TEXT,
|
|
"CallPut" TEXT,
|
|
"Strike" TEXT,
|
|
"Spot" TEXT,
|
|
"Premium" TEXT,
|
|
"ExpirationDate" TEXT,
|
|
"Color" TEXT,
|
|
"ImpliedVolatility" TEXT,
|
|
"Dte" TEXT,
|
|
"ER" TEXT,
|
|
"StockEtf" TEXT,
|
|
"Sector" TEXT,
|
|
"Uoa" TEXT,
|
|
"Weekly" TEXT,
|
|
"MktCap" TEXT,
|
|
"OI" TEXT,
|
|
"timestamp" TIMESTAMP WITH TIME ZONE,
|
|
"aggressor" TEXT
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_optionsflow_monthly_symbol ON public."OptionsFlow_monthly"("Symbol");
|
|
CREATE INDEX IF NOT EXISTS idx_optionsflow_monthly_timestamp ON public."OptionsFlow_monthly"("timestamp" DESC);
|
|
CREATE INDEX IF NOT EXISTS idx_optionsflow_monthly_expiration ON public."OptionsFlow_monthly"("ExpirationDate");
|
|
|
|
-- ============================================
|
|
-- 7. PRICES_INTRADAY_1M
|
|
-- ============================================
|
|
-- Used by: optionflowrockerscorer.sql, intradayflow.sql
|
|
CREATE TABLE IF NOT EXISTS public.prices_intraday_1m (
|
|
symbol VARCHAR(10) NOT NULL,
|
|
ts TIMESTAMP WITH TIME ZONE NOT NULL,
|
|
open NUMERIC(12, 4),
|
|
high NUMERIC(12, 4),
|
|
low NUMERIC(12, 4),
|
|
close NUMERIC(12, 4),
|
|
volume NUMERIC(15, 2),
|
|
short_volume_ratio NUMERIC(5, 4),
|
|
"timestamp" TIMESTAMP WITH TIME ZONE,
|
|
PRIMARY KEY (symbol, ts)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_prices_intraday_1m_symbol_ts ON public.prices_intraday_1m(symbol, ts DESC);
|
|
CREATE INDEX IF NOT EXISTS idx_prices_intraday_1m_ts ON public.prices_intraday_1m(ts DESC);
|
|
CREATE INDEX IF NOT EXISTS idx_prices_intraday_1m_timestamp ON public.prices_intraday_1m("timestamp" DESC);
|
|
|
|
-- ============================================
|
|
-- 8. PRICES_DAILY
|
|
-- ============================================
|
|
-- Used by: optionflowrockerscorer.sql
|
|
CREATE TABLE IF NOT EXISTS public.prices_daily (
|
|
symbol VARCHAR(10) NOT NULL,
|
|
"Date" DATE NOT NULL,
|
|
open NUMERIC(12, 4),
|
|
high NUMERIC(12, 4),
|
|
low NUMERIC(12, 4),
|
|
close NUMERIC(12, 4),
|
|
volume NUMERIC(15, 2),
|
|
PRIMARY KEY (symbol, "Date")
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_prices_daily_symbol_date ON public.prices_daily(symbol, "Date" DESC);
|
|
CREATE INDEX IF NOT EXISTS idx_prices_daily_date ON public.prices_daily("Date" DESC);
|
|
|
|
-- ============================================
|
|
-- 9. PRICE_DAILY
|
|
-- ============================================
|
|
-- Used by: intradayoptionfactors.sql
|
|
CREATE TABLE IF NOT EXISTS public.price_daily (
|
|
"Ticker" VARCHAR(10) NOT NULL,
|
|
dt DATE NOT NULL,
|
|
open NUMERIC(12, 4),
|
|
high NUMERIC(12, 4),
|
|
low NUMERIC(12, 4),
|
|
close NUMERIC(12, 4),
|
|
volume NUMERIC(15, 2),
|
|
PRIMARY KEY ("Ticker", dt)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_price_daily_ticker_dt ON public.price_daily("Ticker", dt DESC);
|
|
CREATE INDEX IF NOT EXISTS idx_price_daily_dt ON public.price_daily(dt DESC);
|
|
|
|
-- ============================================
|
|
-- 10. PRICE_1M
|
|
-- ============================================
|
|
-- Used by: intradayoptionfactors.sql
|
|
CREATE TABLE IF NOT EXISTS public.price_1m (
|
|
"Ticker" VARCHAR(10) NOT NULL,
|
|
ts TIMESTAMP WITH TIME ZONE NOT NULL,
|
|
open NUMERIC(12, 4),
|
|
high NUMERIC(12, 4),
|
|
low NUMERIC(12, 4),
|
|
close NUMERIC(12, 4),
|
|
volume NUMERIC(15, 2),
|
|
PRIMARY KEY ("Ticker", ts)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_price_1m_ticker_ts ON public.price_1m("Ticker", ts DESC);
|
|
CREATE INDEX IF NOT EXISTS idx_price_1m_ts ON public.price_1m(ts DESC);
|
|
|
|
-- ============================================
|
|
-- ROW LEVEL SECURITY (RLS)
|
|
-- ============================================
|
|
-- Enable RLS on all tables (adjust policies as needed for production)
|
|
|
|
ALTER TABLE public.minute_bars_et ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE public.bucket_members ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE public.daily_bars ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE public."AlertStream_monthly" ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE public.alertstream_monthly ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE public."OptionsFlow_monthly" ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE public.prices_intraday_1m ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE public.prices_daily ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE public.price_daily ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE public.price_1m ENABLE ROW LEVEL SECURITY;
|
|
|
|
-- Create permissive policies for development (adjust for production)
|
|
CREATE POLICY "Allow all operations on minute_bars_et" ON public.minute_bars_et
|
|
FOR ALL USING (true) WITH CHECK (true);
|
|
|
|
CREATE POLICY "Allow all operations on bucket_members" ON public.bucket_members
|
|
FOR ALL USING (true) WITH CHECK (true);
|
|
|
|
CREATE POLICY "Allow all operations on daily_bars" ON public.daily_bars
|
|
FOR ALL USING (true) WITH CHECK (true);
|
|
|
|
CREATE POLICY "Allow all operations on AlertStream_monthly" ON public."AlertStream_monthly"
|
|
FOR ALL USING (true) WITH CHECK (true);
|
|
|
|
CREATE POLICY "Allow all operations on alertstream_monthly" ON public.alertstream_monthly
|
|
FOR ALL USING (true) WITH CHECK (true);
|
|
|
|
CREATE POLICY "Allow all operations on OptionsFlow_monthly" ON public."OptionsFlow_monthly"
|
|
FOR ALL USING (true) WITH CHECK (true);
|
|
|
|
CREATE POLICY "Allow all operations on prices_intraday_1m" ON public.prices_intraday_1m
|
|
FOR ALL USING (true) WITH CHECK (true);
|
|
|
|
CREATE POLICY "Allow all operations on prices_daily" ON public.prices_daily
|
|
FOR ALL USING (true) WITH CHECK (true);
|
|
|
|
CREATE POLICY "Allow all operations on price_daily" ON public.price_daily
|
|
FOR ALL USING (true) WITH CHECK (true);
|
|
|
|
CREATE POLICY "Allow all operations on price_1m" ON public.price_1m
|
|
FOR ALL USING (true) WITH CHECK (true);
|
|
|