supabase-schema-roster-update.sql

-- ============================================================
-- TRUSTIV — Active Roster table (homepage hero card)
-- Run this ONCE in Supabase Dashboard → SQL Editor → New query
-- (this is additive — it does NOT touch your existing tables/data)
-- ============================================================

create table if not exists roster_items (
  id uuid primary key default gen_random_uuid(),
  role_name text not null,        -- e.g. "Senior Video Editor"
  status_label text not null,     -- e.g. "Hiring", "Offer sent"
  dot_color text not null default '#5B4FE9',  -- hex color for the status dot
  display_order int not null default 0,
  is_active boolean not null default true,
  created_at timestamptz not null default now()
);

alter table roster_items enable row level security;

create policy "public read active roster_items" on roster_items
  for select using (is_active = true);
create policy "admin full access roster_items" on roster_items
  for all using (auth.role() = 'authenticated') with check (auth.role() = 'authenticated');

-- Seed with the roles currently hardcoded on the homepage
insert into roster_items (role_name, status_label, dot_color, display_order) values
('Script Writer', 'Interview stage', '#1FC8A3', 1),
('Graphic Designer', 'Offer sent', '#5B4FE9', 2),
('Senior Video Editor', 'Hiring', '#FF5C72', 3),
('Sales Interns', 'Applications Open', '#171533', 4),
('Finance executive', 'Completed', '#171533', 5);