299 lines
12 KiB
PL/PgSQL
299 lines
12 KiB
PL/PgSQL
-- =============================================================================
|
|
-- Ultimate Investment Tool — Supabase Schema
|
|
-- Paste this entire file into Supabase SQL Editor and click Run.
|
|
-- =============================================================================
|
|
|
|
-- -----------------------------------------------------------------------------
|
|
-- 1. TABLES
|
|
-- -----------------------------------------------------------------------------
|
|
|
|
CREATE TABLE IF NOT EXISTS licenses (
|
|
id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
|
|
license_key TEXT UNIQUE NOT NULL,
|
|
hwid TEXT, -- NULL until first activation
|
|
tier TEXT NOT NULL DEFAULT 'monthly', -- 'weekly' | 'monthly' | 'lifetime'
|
|
channel TEXT NOT NULL DEFAULT 'stable', -- 'stable' | 'beta'
|
|
expiry_date TIMESTAMPTZ, -- NULL = lifetime
|
|
active BOOLEAN NOT NULL DEFAULT true,
|
|
machines_allowed INTEGER NOT NULL DEFAULT 1,
|
|
notes TEXT, -- customer name / order ref
|
|
created_at TIMESTAMPTZ DEFAULT NOW(),
|
|
activated_at TIMESTAMPTZ
|
|
);
|
|
|
|
-- Add channel column to existing tables (safe to run on already-created tables)
|
|
ALTER TABLE licenses ADD COLUMN IF NOT EXISTS channel TEXT NOT NULL DEFAULT 'stable';
|
|
|
|
CREATE TABLE IF NOT EXISTS app_versions (
|
|
id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
|
|
version TEXT UNIQUE NOT NULL, -- e.g. '1.0.1'
|
|
channel TEXT NOT NULL DEFAULT 'stable', -- 'stable' | 'beta'
|
|
gui_py_url TEXT, -- Supabase Storage URL for screener_gui.py
|
|
screener_py_url TEXT, -- Supabase Storage URL for stock_screener.py
|
|
release_notes TEXT,
|
|
is_latest BOOLEAN NOT NULL DEFAULT true,
|
|
released_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
|
|
-- Add channel column to existing app_versions tables (safe to run on already-created tables)
|
|
ALTER TABLE app_versions ADD COLUMN IF NOT EXISTS channel TEXT NOT NULL DEFAULT 'stable';
|
|
|
|
CREATE TABLE IF NOT EXISTS auth_log (
|
|
id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
|
|
license_key TEXT,
|
|
hwid TEXT,
|
|
result TEXT, -- 'valid' | 'invalid'
|
|
reason TEXT,
|
|
created_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS analyst_cache (
|
|
ticker TEXT PRIMARY KEY,
|
|
pe_forward DOUBLE PRECISION,
|
|
eps_forward DOUBLE PRECISION,
|
|
analyst_norm DOUBLE PRECISION,
|
|
analyst_upside DOUBLE PRECISION,
|
|
analyst_count INTEGER,
|
|
analyst_target DOUBLE PRECISION,
|
|
recommendation TEXT,
|
|
news_sentiment DOUBLE PRECISION,
|
|
sector TEXT,
|
|
industry TEXT,
|
|
updated_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS fundamentals_cache (
|
|
ticker TEXT PRIMARY KEY,
|
|
-- Valuation
|
|
pe_trailing DOUBLE PRECISION,
|
|
pb_ratio DOUBLE PRECISION,
|
|
ev_ebitda DOUBLE PRECISION,
|
|
eps_trailing DOUBLE PRECISION,
|
|
-- Growth
|
|
revenue DOUBLE PRECISION,
|
|
revenue_growth DOUBLE PRECISION,
|
|
earnings_growth DOUBLE PRECISION,
|
|
-- Quality
|
|
roe DOUBLE PRECISION,
|
|
roa DOUBLE PRECISION,
|
|
debt_to_equity DOUBLE PRECISION,
|
|
total_debt DOUBLE PRECISION,
|
|
total_cash DOUBLE PRECISION,
|
|
book_value DOUBLE PRECISION,
|
|
current_ratio DOUBLE PRECISION,
|
|
-- Profitability
|
|
profit_margin DOUBLE PRECISION,
|
|
operating_margin DOUBLE PRECISION,
|
|
fcf_yield DOUBLE PRECISION,
|
|
dividend_yield DOUBLE PRECISION,
|
|
-- Market
|
|
market_cap DOUBLE PRECISION,
|
|
shares_outstanding DOUBLE PRECISION,
|
|
ev_revenue DOUBLE PRECISION,
|
|
-- Risk
|
|
short_percent DOUBLE PRECISION,
|
|
-- Meta
|
|
filer_type TEXT, -- 'us-gaap-quarterly' | 'us-gaap-annual' | 'ifrs-annual' | 'none'
|
|
updated_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS sector_stats (
|
|
sector TEXT PRIMARY KEY,
|
|
-- Valuation
|
|
pe_trailing_med DOUBLE PRECISION, pe_trailing_mad DOUBLE PRECISION,
|
|
pb_ratio_med DOUBLE PRECISION, pb_ratio_mad DOUBLE PRECISION,
|
|
ev_ebitda_med DOUBLE PRECISION, ev_ebitda_mad DOUBLE PRECISION,
|
|
ev_revenue_med DOUBLE PRECISION, ev_revenue_mad DOUBLE PRECISION,
|
|
-- Growth
|
|
revenue_growth_med DOUBLE PRECISION, revenue_growth_mad DOUBLE PRECISION,
|
|
earnings_growth_med DOUBLE PRECISION, earnings_growth_mad DOUBLE PRECISION,
|
|
eps_growth_med DOUBLE PRECISION, eps_growth_mad DOUBLE PRECISION,
|
|
-- Quality
|
|
roe_med DOUBLE PRECISION, roe_mad DOUBLE PRECISION,
|
|
roa_med DOUBLE PRECISION, roa_mad DOUBLE PRECISION,
|
|
debt_to_equity_med DOUBLE PRECISION, debt_to_equity_mad DOUBLE PRECISION,
|
|
current_ratio_med DOUBLE PRECISION, current_ratio_mad DOUBLE PRECISION,
|
|
-- Profitability
|
|
profit_margin_med DOUBLE PRECISION, profit_margin_mad DOUBLE PRECISION,
|
|
operating_margin_med DOUBLE PRECISION, operating_margin_mad DOUBLE PRECISION,
|
|
fcf_yield_med DOUBLE PRECISION, fcf_yield_mad DOUBLE PRECISION,
|
|
-- Analyst
|
|
analyst_upside_med DOUBLE PRECISION, analyst_upside_mad DOUBLE PRECISION,
|
|
-- Meta
|
|
ticker_count INTEGER,
|
|
updated_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
|
|
-- Add columns that may be missing if sector_stats was created before the current schema
|
|
ALTER TABLE sector_stats ADD COLUMN IF NOT EXISTS eps_growth_med DOUBLE PRECISION;
|
|
ALTER TABLE sector_stats ADD COLUMN IF NOT EXISTS eps_growth_mad DOUBLE PRECISION;
|
|
ALTER TABLE sector_stats ADD COLUMN IF NOT EXISTS ticker_count INTEGER;
|
|
|
|
ALTER TABLE sector_stats ENABLE ROW LEVEL SECURITY;
|
|
|
|
DO $$ BEGIN
|
|
IF NOT EXISTS (
|
|
SELECT 1 FROM pg_policies
|
|
WHERE tablename = 'sector_stats' AND policyname = 'anon_select'
|
|
) THEN
|
|
CREATE POLICY anon_select ON sector_stats FOR SELECT TO anon USING (true);
|
|
END IF;
|
|
END $$;
|
|
|
|
ALTER TABLE fundamentals_cache ENABLE ROW LEVEL SECURITY;
|
|
|
|
DO $$ BEGIN
|
|
IF NOT EXISTS (
|
|
SELECT 1 FROM pg_policies
|
|
WHERE tablename = 'fundamentals_cache' AND policyname = 'anon_select'
|
|
) THEN
|
|
CREATE POLICY anon_select ON fundamentals_cache FOR SELECT TO anon USING (true);
|
|
END IF;
|
|
END $$;
|
|
|
|
-- Seed the initial version row so the updater has something to compare against.
|
|
INSERT INTO app_versions (version, is_latest, release_notes)
|
|
SELECT '1.0.0', true, 'Initial release'
|
|
WHERE NOT EXISTS (SELECT 1 FROM app_versions WHERE version = '1.0.0');
|
|
|
|
-- -----------------------------------------------------------------------------
|
|
-- 2. ROW LEVEL SECURITY — no direct table access for anon
|
|
-- -----------------------------------------------------------------------------
|
|
|
|
ALTER TABLE licenses ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE app_versions ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE auth_log ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE analyst_cache ENABLE ROW LEVEL SECURITY;
|
|
|
|
-- No RLS policies on licenses/app_versions/auth_log = anon cannot access directly.
|
|
-- All sensitive access goes through SECURITY DEFINER functions below.
|
|
|
|
-- analyst_cache is public market data — allow the anon key to read it.
|
|
DO $$ BEGIN
|
|
IF NOT EXISTS (
|
|
SELECT 1 FROM pg_policies
|
|
WHERE tablename = 'analyst_cache' AND policyname = 'anon_select'
|
|
) THEN
|
|
CREATE POLICY anon_select ON analyst_cache FOR SELECT TO anon USING (true);
|
|
END IF;
|
|
END $$;
|
|
|
|
-- -----------------------------------------------------------------------------
|
|
-- 3. RPC: verify_license(p_key, p_hwid) → JSON
|
|
-- -----------------------------------------------------------------------------
|
|
-- Called by the app on every launch with the stored license key + machine HWID.
|
|
-- Handles: first-time HWID binding, expiry, disabled keys, HWID mismatch.
|
|
-- Returns: { valid, reason, tier, expiry_date }
|
|
-- -----------------------------------------------------------------------------
|
|
|
|
CREATE OR REPLACE FUNCTION verify_license(p_key TEXT, p_hwid TEXT)
|
|
RETURNS JSON
|
|
LANGUAGE plpgsql
|
|
SECURITY DEFINER
|
|
AS $$
|
|
DECLARE
|
|
lic RECORD;
|
|
BEGIN
|
|
-- Look up the key
|
|
SELECT * INTO lic FROM licenses WHERE license_key = p_key;
|
|
|
|
IF NOT FOUND THEN
|
|
INSERT INTO auth_log (license_key, hwid, result, reason)
|
|
VALUES (p_key, p_hwid, 'invalid', 'key_not_found');
|
|
RETURN json_build_object(
|
|
'valid', false,
|
|
'reason', 'License key not found.'
|
|
);
|
|
END IF;
|
|
|
|
-- Check disabled
|
|
IF NOT lic.active THEN
|
|
INSERT INTO auth_log (license_key, hwid, result, reason)
|
|
VALUES (p_key, p_hwid, 'invalid', 'key_disabled');
|
|
RETURN json_build_object(
|
|
'valid', false,
|
|
'reason', 'This license has been disabled. Please contact support.'
|
|
);
|
|
END IF;
|
|
|
|
-- Check expiry (NULL = lifetime, never expires)
|
|
IF lic.expiry_date IS NOT NULL AND lic.expiry_date < NOW() THEN
|
|
INSERT INTO auth_log (license_key, hwid, result, reason)
|
|
VALUES (p_key, p_hwid, 'invalid', 'expired');
|
|
RETURN json_build_object(
|
|
'valid', false,
|
|
'reason', 'Your subscription has expired. Please renew to continue.'
|
|
);
|
|
END IF;
|
|
|
|
-- HWID check
|
|
IF lic.hwid IS NULL THEN
|
|
-- First activation — bind this machine
|
|
UPDATE licenses
|
|
SET hwid = p_hwid, activated_at = NOW()
|
|
WHERE license_key = p_key;
|
|
ELSIF lic.hwid <> p_hwid THEN
|
|
INSERT INTO auth_log (license_key, hwid, result, reason)
|
|
VALUES (p_key, p_hwid, 'invalid', 'hwid_mismatch');
|
|
RETURN json_build_object(
|
|
'valid', false,
|
|
'reason', 'This license is registered to a different machine. Contact support to transfer.'
|
|
);
|
|
END IF;
|
|
|
|
-- All checks passed
|
|
INSERT INTO auth_log (license_key, hwid, result, reason)
|
|
VALUES (p_key, p_hwid, 'valid', 'ok');
|
|
|
|
RETURN json_build_object(
|
|
'valid', true,
|
|
'reason', 'ok',
|
|
'tier', lic.tier,
|
|
'channel', lic.channel,
|
|
'expiry_date', lic.expiry_date
|
|
);
|
|
END;
|
|
$$;
|
|
|
|
-- -----------------------------------------------------------------------------
|
|
-- 4. RPC: get_latest_version() → JSON
|
|
-- -----------------------------------------------------------------------------
|
|
-- Called by the updater after a successful license check.
|
|
-- Returns: { version, gui_py_url, screener_py_url, release_notes }
|
|
-- -----------------------------------------------------------------------------
|
|
|
|
CREATE OR REPLACE FUNCTION get_latest_version(p_channel TEXT DEFAULT 'stable')
|
|
RETURNS JSON
|
|
LANGUAGE plpgsql
|
|
SECURITY DEFINER
|
|
AS $$
|
|
DECLARE
|
|
ver RECORD;
|
|
BEGIN
|
|
SELECT * INTO ver
|
|
FROM app_versions
|
|
WHERE is_latest = true
|
|
AND channel = p_channel
|
|
ORDER BY released_at DESC
|
|
LIMIT 1;
|
|
|
|
IF NOT FOUND THEN
|
|
RETURN json_build_object('version', '1.0.0');
|
|
END IF;
|
|
|
|
RETURN json_build_object(
|
|
'version', ver.version,
|
|
'gui_py_url', ver.gui_py_url,
|
|
'screener_py_url', ver.screener_py_url,
|
|
'release_notes', ver.release_notes
|
|
);
|
|
END;
|
|
$$;
|
|
|
|
-- -----------------------------------------------------------------------------
|
|
-- 5. GRANTS — allow anon key to call the two RPC functions only
|
|
-- -----------------------------------------------------------------------------
|
|
|
|
GRANT EXECUTE ON FUNCTION verify_license(TEXT, TEXT) TO anon;
|
|
GRANT EXECUTE ON FUNCTION get_latest_version(TEXT) TO anon;
|