Created
January 31, 2026 09:35
-
-
Save debsouryadatta/0abdceeb2e21b75e2263bcfbb0285530 to your computer and use it in GitHub Desktop.
Vyora supabase schema
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| -- Vyora - Simple Supabase Schema | |
| -- YouTube Channel Analytics & Sponsorship Platform | |
| -- Enable UUID extension | |
| CREATE EXTENSION IF NOT EXISTS "uuid-ossp"; | |
| -- ============================================ | |
| -- USERS TABLE | |
| -- ============================================ | |
| CREATE TABLE users ( | |
| id UUID PRIMARY KEY DEFAULT uuid_generate_v4(), | |
| email TEXT UNIQUE NOT NULL, | |
| name TEXT, | |
| avatar_url TEXT, | |
| created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(), | |
| updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW() | |
| ); | |
| -- ============================================ | |
| -- CHANNELS TABLE | |
| -- Stores YouTube channel information | |
| -- ============================================ | |
| CREATE TABLE channels ( | |
| id UUID PRIMARY KEY DEFAULT uuid_generate_v4(), | |
| channel_id TEXT UNIQUE NOT NULL, -- YouTube channel ID | |
| name TEXT NOT NULL, | |
| description TEXT, | |
| thumbnail_url TEXT, | |
| subscriber_count BIGINT DEFAULT 0, | |
| video_count INTEGER DEFAULT 0, | |
| view_count BIGINT DEFAULT 0, | |
| niche TEXT, -- e.g., 'tech', 'gaming', 'lifestyle' | |
| country TEXT, | |
| estimated_rate_min DECIMAL(10, 2), -- Min sponsorship rate | |
| estimated_rate_max DECIMAL(10, 2), -- Max sponsorship rate | |
| created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(), | |
| updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW() | |
| ); | |
| -- ============================================ | |
| -- SPONSORS TABLE | |
| -- Stores brand/sponsor information | |
| -- ============================================ | |
| CREATE TABLE sponsors ( | |
| id UUID PRIMARY KEY DEFAULT uuid_generate_v4(), | |
| name TEXT NOT NULL, | |
| logo_url TEXT, | |
| website TEXT, | |
| niche TEXT, -- e.g., 'tech', 'finance', 'gaming' | |
| description TEXT, | |
| created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW() | |
| ); | |
| -- ============================================ | |
| -- SPONSORSHIPS TABLE | |
| -- Tracks sponsorship deals between channels and sponsors | |
| -- ============================================ | |
| CREATE TABLE sponsorships ( | |
| id UUID PRIMARY KEY DEFAULT uuid_generate_v4(), | |
| channel_id UUID REFERENCES channels(id) ON DELETE CASCADE, | |
| sponsor_id UUID REFERENCES sponsors(id) ON DELETE CASCADE, | |
| video_url TEXT, | |
| deal_date DATE, | |
| estimated_amount DECIMAL(10, 2), | |
| created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW() | |
| ); | |
| -- ============================================ | |
| -- SAVED CHANNELS TABLE | |
| -- Users can save channels for tracking | |
| -- ============================================ | |
| CREATE TABLE saved_channels ( | |
| id UUID PRIMARY KEY DEFAULT uuid_generate_v4(), | |
| user_id UUID REFERENCES users(id) ON DELETE CASCADE, | |
| channel_id UUID REFERENCES channels(id) ON DELETE CASCADE, | |
| created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(), | |
| UNIQUE(user_id, channel_id) | |
| ); | |
| -- ============================================ | |
| -- INDEXES FOR BETTER QUERY PERFORMANCE | |
| -- ============================================ | |
| CREATE INDEX idx_channels_niche ON channels(niche); | |
| CREATE INDEX idx_channels_subscriber_count ON channels(subscriber_count DESC); | |
| CREATE INDEX idx_sponsors_niche ON sponsors(niche); | |
| CREATE INDEX idx_sponsorships_channel ON sponsorships(channel_id); | |
| CREATE INDEX idx_sponsorships_sponsor ON sponsorships(sponsor_id); | |
| CREATE INDEX idx_saved_channels_user ON saved_channels(user_id); | |
| -- ============================================ | |
| -- ROW LEVEL SECURITY (RLS) | |
| -- ============================================ | |
| ALTER TABLE users ENABLE ROW LEVEL SECURITY; | |
| ALTER TABLE channels ENABLE ROW LEVEL SECURITY; | |
| ALTER TABLE sponsors ENABLE ROW LEVEL SECURITY; | |
| ALTER TABLE sponsorships ENABLE ROW LEVEL SECURITY; | |
| ALTER TABLE saved_channels ENABLE ROW LEVEL SECURITY; | |
| -- Public read access for channels and sponsors | |
| CREATE POLICY "Channels are viewable by everyone" | |
| ON channels FOR SELECT USING (true); | |
| CREATE POLICY "Sponsors are viewable by everyone" | |
| ON sponsors FOR SELECT USING (true); | |
| CREATE POLICY "Sponsorships are viewable by everyone" | |
| ON sponsorships FOR SELECT USING (true); | |
| -- Users can only see their own data | |
| CREATE POLICY "Users can view own profile" | |
| ON users FOR SELECT USING (auth.uid() = id); | |
| CREATE POLICY "Users can update own profile" | |
| ON users FOR UPDATE USING (auth.uid() = id); | |
| -- Saved channels - users can only manage their own | |
| CREATE POLICY "Users can view own saved channels" | |
| ON saved_channels FOR SELECT USING (auth.uid() = user_id); | |
| CREATE POLICY "Users can insert own saved channels" | |
| ON saved_channels FOR INSERT WITH CHECK (auth.uid() = user_id); | |
| CREATE POLICY "Users can delete own saved channels" | |
| ON saved_channels FOR DELETE USING (auth.uid() = user_id); |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment