VillageServer Admin

Complete Supabase schema

The canonical schema is idempotent, enables row-level security, seeds the current affiliates, and includes every content, analytics, donation-interest, and kit-request table.

create table campaigns (
  id uuid default gen_random_uuid() primary key,
  slug text unique not null,
  name text not null,
  description text,
  story text,
  goal_amount numeric default 0,
  raised_amount numeric default 0,
  bibles_funded integer default 0,
  bibles_needed integer default 0,
  end_date date,
  zeffy_url text,
  image_url text,
  active boolean default false,
  created_at timestamptz default now()
);

create table posts (
  id uuid default gen_random_uuid() primary key,
  title text not null,
  body text not null,
  image_url text,
  author text default 'VillageServer Initiative',
  published boolean default false,
  published_at timestamptz default now(),
  created_at timestamptz default now()
);

create table photos (
  id uuid default gen_random_uuid() primary key,
  url text not null,
  caption text,
  alt text,
  category text default 'field',
  created_at timestamptz default now()
);

create table affiliates (
  id uuid default gen_random_uuid() primary key,
  name text not null,
  website_url text,
  description text,
  logo_url text,
  active boolean default true,
  sort_order integer default 0,
  created_at timestamptz default now()
);

create table page_visits (
  id uuid default gen_random_uuid() primary key,
  site_host text,
  path text,
  referrer text,
  utm_source text,
  utm_medium text,
  utm_campaign text,
  utm_content text,
  utm_term text,
  fbclid text,
  ttclid text,
  created_at timestamptz default now()
);

create table donation_interests (
  id uuid default gen_random_uuid() primary key,
  name text,
  email text,
  country text not null,
  initiative text,
  practical_need text,
  utm_source text,
  utm_medium text,
  utm_campaign text,
  created_at timestamptz default now()
);

create table availability_requests (
  id uuid default gen_random_uuid() primary key,
  country text not null,
  region text,
  name text,
  email text,
  organization text,
  message text,
  requested_items jsonb not null default '[]'::jsonb,
  utm_source text,
  utm_medium text,
  utm_campaign text,
  created_at timestamptz default now()
);

-- Allow public reads
alter table campaigns enable row level security;
alter table posts enable row level security;
alter table photos enable row level security;
alter table affiliates enable row level security;
alter table page_visits enable row level security;
alter table page_visits add column if not exists site_host text;
alter table donation_interests enable row level security;
alter table availability_requests enable row level security;
alter table availability_requests add column if not exists requested_items jsonb not null default '[]'::jsonb;

create policy "Public reads" on campaigns for select using (true);
create policy "Public reads published" on posts for select using (published = true);
create policy "Public reads photos" on photos for select using (true);
create policy "Public reads active affiliates" on affiliates for select using (active = true);

-- page_visits: public insert (tracking), admin select via service key
create policy "Public insert visits" on page_visits for insert with check (true);

-- donation_interests: public insert, admin select via service key
create policy "Public insert interests" on donation_interests for insert with check (true);

-- availability_requests: public insert, admin select via service key
create policy "Public insert availability" on availability_requests for insert with check (true);