xtablo-source/supabase/migrations/20251116093421_create_notifications_table.sql

-- Create notifications table
CREATE TABLE IF NOT EXISTS public.notifications (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
    actor_id UUID REFERENCES auth.users(id) ON DELETE SET NULL,
    entity_type TEXT NOT NULL,
    entity_id TEXT NOT NULL,
    action_type TEXT NOT NULL CHECK (action_type IN ('created', 'updated')),
    message TEXT NOT NULL,
    metadata JSONB DEFAULT '{}'::jsonb,
    read_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

COMMENT ON TABLE public.notifications IS 'Stores user notifications triggered by changes to major tables';
COMMENT ON COLUMN public.notifications.user_id IS 'Recipient of the notification';
COMMENT ON COLUMN public.notifications.actor_id IS 'User who triggered the action (can be NULL if user deleted)';
COMMENT ON COLUMN public.notifications.entity_type IS 'Type of entity: tablo, task, event, note, tablo_access, tablo_invite';
COMMENT ON COLUMN public.notifications.entity_id IS 'ID of the entity that changed';
COMMENT ON COLUMN public.notifications.action_type IS 'Operation performed: created or updated';
COMMENT ON COLUMN public.notifications.message IS 'Human-readable notification message';
COMMENT ON COLUMN public.notifications.metadata IS 'Additional context as JSONB (old/new values, etc.)';
COMMENT ON COLUMN public.notifications.read_at IS 'When the user read the notification (NULL = unread)';

-- Create indexes for efficient querying
CREATE INDEX idx_notifications_user_id ON public.notifications(user_id);
CREATE INDEX idx_notifications_entity_type ON public.notifications(entity_type);
CREATE INDEX idx_notifications_entity_id ON public.notifications(entity_id);
CREATE INDEX idx_notifications_read_at ON public.notifications(read_at) WHERE read_at IS NULL;
CREATE INDEX idx_notifications_created_at ON public.notifications(created_at DESC);

-- Enable Row Level Security
ALTER TABLE public.notifications ENABLE ROW LEVEL SECURITY;

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

-- RLS Policy: Users can mark their own notifications as read
CREATE POLICY "Users can mark their own notifications as read"
    ON public.notifications
    FOR UPDATE
    USING (auth.uid() = user_id)
    WITH CHECK (auth.uid() = user_id AND read_at IS NOT NULL);

-- Create a single generic trigger function to handle all table notifications
CREATE OR REPLACE FUNCTION public.notify_users()
RETURNS TRIGGER AS $$
DECLARE
    affected_users UUID[];
    affected_user UUID;
    actor UUID;
    action TEXT;
    msg TEXT;
    meta JSONB;
    entity_type_name TEXT;
    entity_identifier TEXT;
BEGIN
    -- Determine if this is an INSERT or UPDATE
    IF TG_OP = 'INSERT' THEN
        action := 'created';
    ELSE
        action := 'updated';
        -- Skip if soft delete happened (only for tables with deleted_at column)
        IF TG_TABLE_NAME IN ('tablos', 'events', 'notes', 'event_types') THEN
            IF NEW.deleted_at IS NOT NULL AND OLD.deleted_at IS NULL THEN
                RETURN NEW;
            END IF;
        END IF;
    END IF;

    -- Get the actor (current user)
    actor := auth.uid();
    
    -- Set entity type and ID based on the table
    entity_type_name := TG_TABLE_NAME;
    
    -- Determine entity ID and affected users based on table
    CASE TG_TABLE_NAME
        WHEN 'tablos' THEN
            entity_identifier := NEW.id;
            
            IF TG_OP = 'INSERT' THEN
                msg := 'New tablo "' || NEW.name || '" was created';
                -- Notify owner (but not if they are the creator)
                IF NEW.owner_id != actor THEN
                    affected_users := ARRAY[NEW.owner_id];
                END IF;
            ELSE
                msg := 'Tablo "' || NEW.name || '" was updated';
                -- Notify owner and all collaborators
                affected_users := ARRAY(
                    SELECT DISTINCT user_id 
                    FROM public.tablo_access 
                    WHERE tablo_id = NEW.id 
                        AND is_active = true
                        AND user_id != actor
                    UNION
                    SELECT NEW.owner_id
                    WHERE NEW.owner_id != actor
                );
            END IF;
            
            meta := jsonb_build_object(
                'tablo_name', NEW.name,
                'status', NEW.status,
                'color', NEW.color
            );

        WHEN 'tasks' THEN
            entity_identifier := NEW.id;
            
            IF TG_OP = 'INSERT' THEN
                msg := 'New task "' || NEW.title || '" was created';
            ELSE
                IF OLD.status != NEW.status THEN
                    msg := 'Task "' || NEW.title || '" status changed to ' || NEW.status;
                ELSIF OLD.assignee_id != NEW.assignee_id OR (OLD.assignee_id IS NULL AND NEW.assignee_id IS NOT NULL) THEN
                    msg := 'You were assigned to task "' || NEW.title || '"';
                ELSE
                    msg := 'Task "' || NEW.title || '" was updated';
                END IF;
            END IF;
            
            -- Notify tablo collaborators and assignee
            affected_users := ARRAY(
                SELECT DISTINCT user_id 
                FROM public.tablo_access 
                WHERE tablo_id = NEW.tablo_id 
                    AND is_active = true
                    AND user_id != actor
                UNION
                SELECT t.owner_id
                FROM public.tablos t
                WHERE t.id = NEW.tablo_id
                    AND t.owner_id != actor
                UNION
                SELECT NEW.assignee_id
                WHERE NEW.assignee_id IS NOT NULL
                    AND NEW.assignee_id != actor
            );
            
            meta := jsonb_build_object(
                'task_title', NEW.title,
                'status', NEW.status,
                'tablo_id', NEW.tablo_id,
                'assignee_id', NEW.assignee_id
            );

        WHEN 'events' THEN
            entity_identifier := NEW.id;
            
            IF TG_OP = 'INSERT' THEN
                msg := 'New event "' || NEW.title || '" was created';
            ELSE
                msg := 'Event "' || NEW.title || '" was updated';
            END IF;
            
            -- Notify tablo collaborators
            affected_users := ARRAY(
                SELECT DISTINCT user_id 
                FROM public.tablo_access 
                WHERE tablo_id = NEW.tablo_id 
                    AND is_active = true
                    AND user_id != actor
                UNION
                SELECT t.owner_id
                FROM public.tablos t
                WHERE t.id = NEW.tablo_id
                    AND t.owner_id != actor
                UNION
                SELECT NEW.created_by
                WHERE NEW.created_by != actor
            );
            
            meta := jsonb_build_object(
                'event_title', NEW.title,
                'start_date', NEW.start_date,
                'start_time', NEW.start_time,
                'tablo_id', NEW.tablo_id
            );

        WHEN 'notes' THEN
            entity_identifier := NEW.id;
            
            IF TG_OP = 'INSERT' THEN
                msg := 'New note "' || NEW.title || '" was created';
            ELSE
                msg := 'Note "' || NEW.title || '" was updated';
            END IF;
            
            -- Notify note owner and users with access
            affected_users := ARRAY(
                SELECT DISTINCT user_id 
                FROM public.note_access 
                WHERE note_id = NEW.id 
                    AND is_active = true
                    AND user_id != actor
                UNION
                SELECT NEW.user_id
                WHERE NEW.user_id != actor
            );
            
            meta := jsonb_build_object(
                'note_title', NEW.title,
                'note_owner', NEW.user_id
            );

        WHEN 'tablo_access' THEN
            entity_identifier := NEW.id::TEXT;
            
            IF TG_OP = 'INSERT' THEN
                msg := 'You were granted access to a tablo';
            ELSE
                IF OLD.is_admin != NEW.is_admin AND NEW.is_admin = true THEN
                    msg := 'You were promoted to admin on a tablo';
                ELSIF OLD.is_active != NEW.is_active AND NEW.is_active = false THEN
                    msg := 'Your access to a tablo was revoked';
                ELSE
                    msg := 'Your tablo access was updated';
                END IF;
            END IF;
            
            -- Notify the user being granted/modified access
            IF NEW.user_id != actor THEN
                affected_users := ARRAY[NEW.user_id];
            END IF;
            
            meta := jsonb_build_object(
                'tablo_id', NEW.tablo_id,
                'is_admin', NEW.is_admin,
                'is_active', NEW.is_active,
                'granted_by', NEW.granted_by
            );

        WHEN 'tablo_invites' THEN
            entity_identifier := NEW.id::TEXT;
            
            IF TG_OP = 'INSERT' THEN
                msg := 'You were invited to collaborate on a tablo';
            ELSE
                IF OLD.is_pending != NEW.is_pending AND NEW.is_pending = false THEN
                    msg := 'Your tablo invitation status changed';
                ELSE
                    msg := 'Your tablo invitation was updated';
                END IF;
            END IF;
            
            -- Find the user by email and notify them (case-insensitive)
            -- Only notify if user exists in the system
            affected_users := ARRAY(
                SELECT id 
                FROM auth.users 
                WHERE LOWER(email) = LOWER(NEW.invited_email)
                    AND id != actor
            );
            
            meta := jsonb_build_object(
                'tablo_id', NEW.tablo_id,
                'invited_email', NEW.invited_email,
                'invited_by', NEW.invited_by,
                'is_pending', NEW.is_pending
            );

        ELSE
            -- Unknown table, skip
            RETURN NEW;
    END CASE;

    -- Insert notifications for all affected users
    IF affected_users IS NOT NULL AND array_length(affected_users, 1) > 0 THEN
        FOREACH affected_user IN ARRAY affected_users
        LOOP
            INSERT INTO public.notifications (
                user_id,
                actor_id,
                entity_type,
                entity_id,
                action_type,
                message,
                metadata
            ) VALUES (
                affected_user,
                actor,
                entity_type_name,
                entity_identifier,
                action,
                msg,
                meta
            );
        END LOOP;
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

COMMENT ON FUNCTION public.notify_users() IS 'Single trigger function that handles notifications for all major tables';

-- Create triggers on all major tables using the same function
CREATE TRIGGER notify_users_on_tablos
    AFTER INSERT OR UPDATE ON public.tablos
    FOR EACH ROW
    EXECUTE FUNCTION public.notify_users();

CREATE TRIGGER notify_users_on_tasks
    AFTER INSERT OR UPDATE ON public.tasks
    FOR EACH ROW
    EXECUTE FUNCTION public.notify_users();

CREATE TRIGGER notify_users_on_events
    AFTER INSERT OR UPDATE ON public.events
    FOR EACH ROW
    EXECUTE FUNCTION public.notify_users();

CREATE TRIGGER notify_users_on_notes
    AFTER INSERT OR UPDATE ON public.notes
    FOR EACH ROW
    EXECUTE FUNCTION public.notify_users();

CREATE TRIGGER notify_users_on_tablo_access
    AFTER INSERT OR UPDATE ON public.tablo_access
    FOR EACH ROW
    EXECUTE FUNCTION public.notify_users();

CREATE TRIGGER notify_users_on_tablo_invites
    AFTER INSERT OR UPDATE ON public.tablo_invites
    FOR EACH ROW
    EXECUTE FUNCTION public.notify_users();