Supabase Flows table migration script

[ardevpk]

Here's the script that can be directly executed in Supabase SQL Editor via Dashboard to create a Flows table which is being used within project (otherwise the logic breaks when we click on: + Create Flow button )!

-- Create the flows table
CREATE TABLE IF NOT EXISTS public.flows (
    id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
    user_id UUID REFERENCES auth.users(id) ON DELETE CASCADE NOT NULL,
    name TEXT NOT NULL DEFAULT 'Untitled Flow',
    description TEXT DEFAULT '',
    graph_json JSONB DEFAULT '{"nodes": [], "edges": []}'::jsonb,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT timezone('utc'::text, now()) NOT NULL,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT timezone('utc'::text, now()) NOT NULL
);

-- Create an index on user_id for better query performance
CREATE INDEX IF NOT EXISTS flows_user_id_idx ON public.flows(user_id);

-- Create an index on updated_at for ordering
CREATE INDEX IF NOT EXISTS flows_updated_at_idx ON public.flows(updated_at DESC);

-- Create a function to automatically update the updated_at column
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = timezone('utc'::text, now());
    RETURN NEW;
END;
$$ language 'plpgsql';

-- Create a trigger to automatically update updated_at on row updates
DROP TRIGGER IF EXISTS update_flows_updated_at ON public.flows;
CREATE TRIGGER update_flows_updated_at
    BEFORE UPDATE ON public.flows
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

-- Enable Row Level Security (RLS)
ALTER TABLE public.flows ENABLE ROW LEVEL SECURITY;

-- Create RLS policies
-- Policy: Users can only view their own flows
CREATE POLICY "Users can view their own flows" ON public.flows
    FOR SELECT USING (auth.uid() = user_id);

-- Policy: Users can insert their own flows
CREATE POLICY "Users can insert their own flows" ON public.flows
    FOR INSERT WITH CHECK (auth.uid() = user_id);

-- Policy: Users can update their own flows
CREATE POLICY "Users can update their own flows" ON public.flows
    FOR UPDATE USING (auth.uid() = user_id);

-- Policy: Users can delete their own flows
CREATE POLICY "Users can delete their own flows" ON public.flows
    FOR DELETE USING (auth.uid() = user_id);

-- Grant necessary permissions
GRANT ALL ON public.flows TO authenticated;
GRANT ALL ON public.flows TO service_role;