Spaces:
Sleeping
Sleeping
File size: 2,812 Bytes
5d435b4 |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 |
-- Create table for caching index calculations
CREATE TABLE IF NOT EXISTS index_cache (
id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
cache_key VARCHAR(32) UNIQUE NOT NULL,
start_date DATE NOT NULL,
end_date DATE NOT NULL,
locale VARCHAR(50) NOT NULL,
market_type VARCHAR(50) NOT NULL,
index_data JSONB NOT NULL,
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);
-- Create index for faster lookups
CREATE INDEX IF NOT EXISTS idx_cache_key ON index_cache(cache_key);
CREATE INDEX IF NOT EXISTS idx_cache_dates ON index_cache(start_date, end_date);
-- Create table for user-specific weights
CREATE TABLE IF NOT EXISTS user_weights (
id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
session_id VARCHAR(255) NOT NULL,
locale VARCHAR(50) NOT NULL,
market_type VARCHAR(50) NOT NULL,
weights_data JSONB NOT NULL,
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);
-- Create indexes for user weights
CREATE INDEX IF NOT EXISTS idx_user_session ON user_weights(session_id);
CREATE INDEX IF NOT EXISTS idx_user_params ON user_weights(session_id, locale, market_type);
-- Optional: Create table for user sessions (if you want persistent user management)
CREATE TABLE IF NOT EXISTS user_sessions (
id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
session_id VARCHAR(255) UNIQUE NOT NULL,
user_email VARCHAR(255),
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
last_active TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);
-- Enable Row Level Security (RLS) for better data isolation
ALTER TABLE index_cache ENABLE ROW LEVEL SECURITY;
ALTER TABLE user_weights ENABLE ROW LEVEL SECURITY;
ALTER TABLE user_sessions ENABLE ROW LEVEL SECURITY;
-- Create policies for public access (adjust based on your auth strategy)
-- For now, allowing all authenticated users to read cached data
CREATE POLICY "Public read access for index cache" ON index_cache
FOR SELECT USING (true);
CREATE POLICY "Public insert access for index cache" ON index_cache
FOR INSERT WITH CHECK (true);
CREATE POLICY "Public update access for index cache" ON index_cache
FOR UPDATE USING (true);
-- User weights should be session-specific
CREATE POLICY "Session-specific access for user weights" ON user_weights
FOR ALL USING (true);
-- Function to clean up old cache entries (optional)
CREATE OR REPLACE FUNCTION cleanup_old_cache()
RETURNS void AS $$
BEGIN
DELETE FROM index_cache WHERE created_at < NOW() - INTERVAL '30 days';
DELETE FROM user_weights WHERE created_at < NOW() - INTERVAL '7 days';
END;
$$ LANGUAGE plpgsql;
-- Optional: Create a scheduled job to run cleanup (requires pg_cron extension)
-- SELECT cron.schedule('cleanup-old-cache', '0 0 * * *', 'SELECT cleanup_old_cache();'); |