app/projects/migrations/0013_notification_preferences.sql

begin;

-- Notification Preferences
create table if not exists notification_preferences (
    id text primary key,
    workspace_id text not null references workspaces(id) on delete cascade,
    user_id text not null references users(id) on delete cascade,
    event_type varchar(50) not null check (event_type in (
        'collaboration_event', 'approval_request', 'publishing_failure', 'mention', 'project_activity'
    )),
    enabled boolean not null default true,
    created_at timestamptz not null default now(),
    unique(workspace_id, user_id, event_type)
);

create index ix_notification_preferences_workspace_user on notification_preferences(workspace_id, user_id);

-- RLS
alter table notification_preferences enable row level security;
alter table notification_preferences force row level security;
create policy notification_preferences_workspace_isolation on notification_preferences 
    using (workspace_id = current_setting('app.workspace_id', true)) 
    with check (workspace_id = current_setting('app.workspace_id', true));

commit;