-- ════════════════════════════════════ -- SADA Growth Agency — Database Schema -- ════════════════════════════════════ -- Clients table create table public.clients ( id uuid default gen_random_uuid() primary key, user_id uuid references auth.users(id) on delete cascade, name text not null, email text not null, plan text default 'basic', is_active boolean default true, status text default 'active', created_at timestamptz default now() ); -- Add columns if the table already exists alter table public.clients add column if not exists is_active boolean default true; alter table public.clients add column if not exists status text default 'active'; alter table public.clients add column if not exists photo_url text; -- Storage bucket for client profile photos (public read) insert into storage.buckets (id, name, public) values ('client-avatars', 'client-avatars', true) on conflict (id) do nothing; -- Allow the SADA admin to upload/replace/delete client avatar photos drop policy if exists "admin_manage_avatars" on storage.objects; create policy "admin_manage_avatars" on storage.objects for all using (bucket_id = 'client-avatars' and auth.jwt()->>'email' = 'omar.alagha@metu.edu.tr') with check (bucket_id = 'client-avatars' and auth.jwt()->>'email' = 'omar.alagha@metu.edu.tr'); -- Platform accounts table create table public.client_accounts ( id uuid default gen_random_uuid() primary key, client_id uuid references public.clients(id) on delete cascade, platform text not null, account_name text not null, account_handle text, account_url text, platform_id text, sort_order int default 0, created_at timestamptz default now() ); -- Add the ordering column if the table already exists (controls the order -- accounts appear in, per platform — set by the admin via "Save Order") alter table public.client_accounts add column if not exists sort_order int default 0; -- Note for the client on an account (shown on its card in their dashboard; empty shows nothing) alter table public.client_accounts add column if not exists client_note text; -- Statistics table create table public.account_stats ( id uuid default gen_random_uuid() primary key, account_id uuid references public.client_accounts(id) on delete cascade, views bigint default 0, likes bigint default 0, followers bigint default 0, videos_count int default 0, date_recorded date default current_date, created_at timestamptz default now() ); -- Enable RLS alter table public.clients enable row level security; alter table public.client_accounts enable row level security; alter table public.account_stats enable row level security; -- Client policy (can only see their own data) create policy "client_own" on public.clients for select using (auth.uid() = user_id); create policy "accounts_own" on public.client_accounts for select using ( exists (select 1 from public.clients where id = client_id and user_id = auth.uid()) ); create policy "stats_own" on public.account_stats for select using ( exists ( select 1 from public.client_accounts ca join public.clients c on c.id = ca.client_id where ca.id = account_id and c.user_id = auth.uid() ) ); -- Full write access for admin (service_role) -- Added automatically via the service role key in Supabase -- Automatically link the client on signup create or replace function public.link_client_on_signup() returns trigger as $$ begin update public.clients set user_id = new.id where email = new.email and user_id is null; return new; end; $$ language plpgsql security definer; create trigger on_auth_user_created after insert on auth.users for each row execute function public.link_client_on_signup(); -- ── Reporting period + client reports ── -- Start date of each client's current 30-day reporting cycle alter table public.clients add column if not exists report_period_start date; -- Personal message the admin writes for a client (shown on their dashboard) alter table public.clients add column if not exists personal_quote text; -- Show the end-of-period views expectation to this client (off by default) alter table public.clients add column if not exists show_views_expectation boolean not null default false; -- Saved reports (generated by the admin, visible to the client) create table if not exists public.client_reports ( id uuid default gen_random_uuid() primary key, client_id uuid references public.clients(id) on delete cascade, period_start date, period_end date, report_data jsonb not null, created_at timestamptz default now() ); alter table public.client_reports enable row level security; drop policy if exists "reports_own" on public.client_reports; create policy "reports_own" on public.client_reports for select using ( exists (select 1 from public.clients where id = client_id and user_id = auth.uid()) ); drop policy if exists "reports_admin" on public.client_reports; create policy "reports_admin" on public.client_reports for all using (auth.jwt()->>'email' = 'omar.alagha@metu.edu.tr') with check (auth.jwt()->>'email' = 'omar.alagha@metu.edu.tr');