supabase_notifications.sql

-- Create notifications table
create table if not exists public.notifications (
    id bigint generated by default as identity primary key,
    user_id uuid references auth.users(id) on delete cascade not null,
    sender_id uuid references auth.users(id) on delete set null,
    type text check (type in ('like', 'comment', 'save', 'schedule')) not null,
    message text not null,
    is_read boolean default false,
    link text,
    created_at timestamp with time zone default timezone('utc'::text, now()) not null
);

-- Enable RLS
alter table public.notifications enable row level security;

-- Create policies
create policy "Users can view their own notifications"
    on public.notifications for select
    using (auth.uid() = user_id);

create policy "Users can update their own notifications"
    on public.notifications for update
    using (auth.uid() = user_id);

create policy "Users can delete their own notifications"
    on public.notifications for delete
    using (auth.uid() = user_id);

-- Allow anyone (authenticated) to insert notifications (e.g. when liking a post)
create policy "Anyone can insert notifications"
    on public.notifications for insert
    with check (auth.role() = 'authenticated');