Database Layout & Server Specs

I have a Joinazo project that was built in Lovable and pushed to GitHub. I need to set it up locally and connect it to Supabase.

Please do the following:

1. INSTALL DEPENDENCIES: Run "npm install" or "bun install" to install all packages.

2. CREATE SUPABASE PROJECT: Go to https://supabase.com/dashboard and create a new project called "joinazo". Note down:
   - Project URL (VITE_SUPABASE_URL)
   - Anon public key (VITE_SUPABASE_ANON_KEY)
   - Service role key (for migrations only — never expose in frontend)
   - Database password

3. CREATE .env.local file in the project root:
   VITE_SUPABASE_URL=https://your-project.supabase.co
   VITE_SUPABASE_ANON_KEY=your-anon-key-here

4. INSTALL SUPABASE CLI: Run "npm install -g supabase" then "supabase init" and "supabase link --project-ref your-project-ref".

5. INSTALL SUPABASE JS CLIENT: Run "npm install @supabase/supabase-js".

6. CREATE src/lib/supabase.ts:
   import { createClient } from '@supabase/supabase-js'
   export const supabase = createClient(
     import.meta.env.VITE_SUPABASE_URL,
     import.meta.env.VITE_SUPABASE_ANON_KEY
   )

7. TEST: Run "npm run dev" to check the app starts. The UI should load fine — all data is currently hardcoded/demo. We will replace it with real Supabase data after setting up the database.

Once this is done, proceed to Prompt 1 to start creating the database tables.

Set up the Joinazo Supabase project. First, create the following enum type:

CREATE TYPE public.app_role AS ENUM ('admin', 'moderator', 'user');

Then create the security definer function used in all RLS policies:

CREATE OR REPLACE FUNCTION public.has_role(_user_id uuid, _role app_role)
RETURNS boolean
LANGUAGE sql STABLE SECURITY DEFINER
SET search_path = public
AS $$
  SELECT EXISTS (
    SELECT 1 FROM public.user_roles
    WHERE user_id = _user_id AND role = _role
  )
$$;

This function must exist before any RLS policies are created.

Create the users and user_roles tables in Supabase for Joinazo:

CREATE TABLE public.users (
  id UUID PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
  email TEXT UNIQUE NOT NULL,
  full_name TEXT,
  avatar_url TEXT,
  phone TEXT,
  bio TEXT,
  location TEXT,
  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now()
);
ALTER TABLE public.users ENABLE ROW LEVEL SECURITY;

CREATE TABLE public.user_roles (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID REFERENCES auth.users(id) ON DELETE CASCADE NOT NULL,
  role app_role NOT NULL,
  UNIQUE (user_id, role)
);
ALTER TABLE public.user_roles ENABLE ROW LEVEL SECURITY;

RLS policies:
- users: Users can read all profiles. Users can update their own row only.
- user_roles: Only admins can read (use has_role function). No public access.

Also create a trigger function that auto-creates a users row when a new auth.users record is created (handle_new_user trigger on auth.users).

Create the groups and group_images tables for Joinazo:

CREATE TABLE public.groups (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  owner_id UUID REFERENCES public.users(id) NOT NULL,
  name TEXT UNIQUE NOT NULL,
  slug TEXT UNIQUE NOT NULL,
  description TEXT,
  category TEXT,
  sub_category TEXT,
  area_tag TEXT,
  cover_image_url TEXT,
  logo_url TEXT,
  age_range_min INTEGER,
  age_range_max INTEGER,
  all_ages BOOLEAN DEFAULT false,
  status TEXT DEFAULT 'draft' CHECK (status IN ('draft','pending_review','approved','rejected','suspended')),
  name_approved BOOLEAN DEFAULT false,
  published BOOLEAN DEFAULT false,
  stripe_account_id TEXT,
  embed_enabled BOOLEAN DEFAULT false,
  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now()
);
ALTER TABLE public.groups ENABLE ROW LEVEL SECURITY;

CREATE TABLE public.group_images (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  group_id UUID REFERENCES public.groups(id) ON DELETE CASCADE NOT NULL,
  image_url TEXT NOT NULL,
  sort_order INTEGER DEFAULT 0,
  created_at TIMESTAMPTZ DEFAULT now()
);
ALTER TABLE public.group_images ENABLE ROW LEVEL SECURITY;

RLS: Public can SELECT where status='approved' AND published=true. Owner and editors can SELECT/UPDATE their own groups. Owner can INSERT/DELETE.
Auto-generate slug from name on insert. When name changes, set name_approved = false. Create an updated_at trigger.

Create group_features, group_editors, and locations tables for Joinazo:

CREATE TABLE public.group_features (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  group_id UUID REFERENCES public.groups(id) ON DELETE CASCADE NOT NULL,
  label TEXT NOT NULL,
  icon_name TEXT NOT NULL,
  sort_order INTEGER DEFAULT 0
);

CREATE TABLE public.group_editors (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  group_id UUID REFERENCES public.groups(id) ON DELETE CASCADE NOT NULL,
  user_id UUID REFERENCES public.users(id),
  invited_email TEXT,
  status TEXT DEFAULT 'pending_invite' CHECK (status IN ('active','pending_invite')),
  added_by UUID REFERENCES public.users(id) NOT NULL,
  created_at TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE public.locations (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  group_id UUID REFERENCES public.groups(id) ON DELETE CASCADE NOT NULL,
  name TEXT NOT NULL,
  address_line_1 TEXT,
  address_line_2 TEXT,
  city TEXT,
  postcode TEXT,
  lat DECIMAL,
  lng DECIMAL,
  is_primary BOOLEAN DEFAULT false,
  created_at TIMESTAMPTZ DEFAULT now()
);

Enable RLS on all. Max 5 features per group — enforce via trigger. Owner + editors can manage. Public read for published groups.

Create the memberships and member_subscriptions tables for Joinazo:

CREATE TABLE public.memberships (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  group_id UUID REFERENCES public.groups(id) ON DELETE CASCADE NOT NULL,
  name TEXT NOT NULL,
  price DECIMAL(10,2) NOT NULL DEFAULT 0,
  billing_interval TEXT DEFAULT 'monthly' CHECK (billing_interval IN ('monthly','yearly','one_time','free')),
  description TEXT,
  features JSONB DEFAULT '[]'::jsonb,
  max_members INTEGER,
  is_active BOOLEAN DEFAULT true,
  stripe_price_id TEXT,
  sort_order INTEGER DEFAULT 0,
  created_at TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE public.member_subscriptions (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID REFERENCES public.users(id) NOT NULL,
  membership_id UUID REFERENCES public.memberships(id) NOT NULL,
  group_id UUID REFERENCES public.groups(id) NOT NULL,
  status TEXT DEFAULT 'active' CHECK (status IN ('active','cancelled','past_due','trialing')),
  stripe_subscription_id TEXT,
  current_period_start TIMESTAMPTZ,
  current_period_end TIMESTAMPTZ,
  created_at TIMESTAMPTZ DEFAULT now()
);

Enable RLS on both. Memberships: public read where is_active=true, owner can manage. Subscriptions: user reads own, group owner reads group's subs, admin reads all.

Create the sessions and session_attendees tables for Joinazo:

CREATE TABLE public.sessions (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  group_id UUID REFERENCES public.groups(id) ON DELETE CASCADE NOT NULL,
  location_id UUID REFERENCES public.locations(id),
  title TEXT NOT NULL,
  day_of_week INTEGER CHECK (day_of_week BETWEEN 0 AND 6),
  start_time TIME NOT NULL,
  end_time TIME NOT NULL,
  is_recurring BOOLEAN DEFAULT true,
  one_off_date DATE,
  max_attendees INTEGER,
  created_at TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE public.session_attendees (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  session_id UUID REFERENCES public.sessions(id) ON DELETE CASCADE NOT NULL,
  user_id UUID REFERENCES public.users(id) NOT NULL,
  status TEXT DEFAULT 'confirmed' CHECK (status IN ('confirmed','waitlisted','cancelled')),
  created_at TIMESTAMPTZ DEFAULT now(),
  UNIQUE (session_id, user_id)
);

Enable RLS and Supabase Realtime on session_attendees. Public read for published groups. Owner + editors manage sessions.

Create payments, refunds, and stripe_connected_accounts tables for Joinazo:

CREATE TABLE public.payments (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID REFERENCES public.users(id) NOT NULL,
  group_id UUID REFERENCES public.groups(id) NOT NULL,
  membership_id UUID REFERENCES public.memberships(id),
  amount DECIMAL(10,2) NOT NULL,
  stripe_fee DECIMAL(10,2),
  platform_fee DECIMAL(10,2),
  net_amount DECIMAL(10,2),
  status TEXT DEFAULT 'succeeded' CHECK (status IN ('succeeded','failed','refunded','disputed')),
  stripe_payment_intent_id TEXT,
  payment_method TEXT,
  created_at TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE public.refunds (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  payment_id UUID REFERENCES public.payments(id) NOT NULL,
  user_id UUID REFERENCES public.users(id) NOT NULL,
  amount DECIMAL(10,2) NOT NULL,
  reason TEXT,
  status TEXT DEFAULT 'pending' CHECK (status IN ('pending','completed','rejected')),
  stripe_refund_id TEXT,
  created_at TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE public.stripe_connected_accounts (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID REFERENCES public.users(id) NOT NULL,
  stripe_account_id TEXT UNIQUE NOT NULL,
  status TEXT DEFAULT 'pending' CHECK (status IN ('active','restricted','pending')),
  payouts_enabled BOOLEAN DEFAULT false,
  charges_enabled BOOLEAN DEFAULT false,
  balance DECIMAL(10,2) DEFAULT 0,
  last_payout_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ DEFAULT now()
);

Enable RLS on all. User reads own payments. Group owner reads group payments. Admin reads all via has_role().

Create the messages and notifications tables for Joinazo:

CREATE TABLE public.messages (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  sender_id UUID REFERENCES public.users(id) NOT NULL,
  recipient_id UUID REFERENCES public.users(id) NOT NULL,
  group_id UUID REFERENCES public.groups(id),
  content TEXT NOT NULL,
  read BOOLEAN DEFAULT false,
  created_at TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE public.notifications (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID REFERENCES public.users(id) NOT NULL,
  type TEXT NOT NULL,
  title TEXT NOT NULL,
  body TEXT,
  link TEXT,
  read BOOLEAN DEFAULT false,
  created_at TIMESTAMPTZ DEFAULT now()
);

Enable RLS and Supabase Realtime on BOTH tables. Messages: user can SELECT/INSERT where they are sender or recipient. Notifications: user can SELECT and UPDATE (mark read) own only.

Notification types: membership_approved, session_reminder, payment_received, profile_approved, profile_rejected, new_member, message, editor_invite, ownership_transfer.

Create liked_groups, reviews, and pro_subscriptions tables for Joinazo:

CREATE TABLE public.liked_groups (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID REFERENCES public.users(id) ON DELETE CASCADE NOT NULL,
  group_id UUID REFERENCES public.groups(id) ON DELETE CASCADE NOT NULL,
  created_at TIMESTAMPTZ DEFAULT now(),
  UNIQUE (user_id, group_id)
);

CREATE TABLE public.reviews (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID REFERENCES public.users(id) NOT NULL,
  group_id UUID REFERENCES public.groups(id) NOT NULL,
  rating INTEGER CHECK (rating BETWEEN 1 AND 5) NOT NULL,
  comment TEXT,
  created_at TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE public.pro_subscriptions (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID REFERENCES public.users(id) NOT NULL,
  plan TEXT CHECK (plan IN ('monthly','yearly')) NOT NULL,
  status TEXT DEFAULT 'active' CHECK (status IN ('active','cancelled','past_due')),
  stripe_subscription_id TEXT,
  current_period_end TIMESTAMPTZ,
  created_at TIMESTAMPTZ DEFAULT now()
);

Enable RLS on all. liked_groups: user manages own. reviews: public read, one per user per group. pro_subscriptions: user reads own, admin reads all.

Create support_tickets, profile_reviews, group_apps, visitor_analytics, and page_views tables for Joinazo:

CREATE TABLE public.support_tickets (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID REFERENCES public.users(id) NOT NULL,
  subject TEXT NOT NULL, message TEXT NOT NULL,
  status TEXT DEFAULT 'open' CHECK (status IN ('open','in_progress','resolved','closed')),
  priority TEXT DEFAULT 'medium' CHECK (priority IN ('low','medium','high','urgent')),
  assigned_to UUID REFERENCES public.users(id),
  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE public.profile_reviews (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  group_id UUID REFERENCES public.groups(id) NOT NULL,
  submitted_by UUID REFERENCES public.users(id) NOT NULL,
  status TEXT DEFAULT 'pending' CHECK (status IN ('pending','approved','rejected')),
  reason TEXT, is_name_change BOOLEAN DEFAULT false,
  reviewed_by UUID REFERENCES public.users(id),
  created_at TIMESTAMPTZ DEFAULT now(),
  reviewed_at TIMESTAMPTZ
);

CREATE TABLE public.group_apps (id UUID PRIMARY KEY DEFAULT gen_random_uuid(), group_id UUID REFERENCES public.groups(id) NOT NULL, app_key TEXT NOT NULL, settings JSONB DEFAULT '{}'::jsonb, is_active BOOLEAN DEFAULT true, installed_at TIMESTAMPTZ DEFAULT now());

CREATE TABLE public.visitor_analytics (id UUID PRIMARY KEY DEFAULT gen_random_uuid(), date DATE NOT NULL, visitors INTEGER DEFAULT 0, page_views INTEGER DEFAULT 0, unique_visitors INTEGER DEFAULT 0, bounce_rate DECIMAL(5,2));

CREATE TABLE public.page_views (id UUID PRIMARY KEY DEFAULT gen_random_uuid(), path TEXT NOT NULL, user_id UUID REFERENCES public.users(id), session_id TEXT, referrer TEXT, source TEXT, duration_seconds INTEGER, created_at TIMESTAMPTZ DEFAULT now());

Enable RLS on all. support_tickets: user reads own, admin reads all. profile_reviews: submitter reads own, admin manages all. Analytics: admin only. page_views INSERT allowed for all.

Set up Supabase Storage buckets for Joinazo:

1. "avatars" — User profile photos. Public: true. MIME: image/jpeg, image/png, image/gif, image/webp. Max: 5MB. RLS: Authenticated users upload to own folder (user_id/*). Users delete/update own files only.

2. "group-images" — Group gallery and cover images. Public: true. MIME: image/jpeg, image/png, image/webp. Max: 10MB. RLS: Group owner + editors upload to group folder (group_id/*).

3. "group-logos" — Group logo uploads. Public: true. MIME: image/jpeg, image/png, image/svg+xml, image/webp. Max: 5MB. RLS: Group owner can upload/update/delete.

4. "documents" — Private documents (DBS certs, contracts). Public: false. MIME: application/pdf, image/jpeg, image/png. Max: 10MB. RLS: User uploads to own folder. Admin can read all.

Create the following Supabase Edge Functions for Joinazo (Deno + supabase-js):

1. stripe-webhook — Handle Stripe webhooks. Process checkout.session.completed, invoice.payment_failed, charge.refunded. Verify webhook signature.

2. create-checkout-session — Accepts {membership_id, user_id}. Creates Stripe Checkout Session with application_fee using group's stripe_account_id. Returns checkout URL.

3. create-connect-account — Accepts {user_id}. Creates Stripe Connect Express account, stores in stripe_connected_accounts, returns onboarding link.

4. send-notification-email — Accepts {user_id, template, data}. Sends email via Resend/Postmark. Templates: welcome, payment_receipt, session_reminder, profile_approved, profile_rejected, editor_invite.

5. check-group-name — Accepts {name}. Case-insensitive query on groups. Returns {available: boolean}.

6. submit-for-review — Accepts {group_id}. Validates description, 5+ images, 1+ membership. Creates profile_reviews record, updates group status.

7. admin-approve-profile — Accepts {review_id, action, reason?}. Updates profile_reviews + group status. Creates notification.

8. invite-editor — Accepts {group_id, email}. If user exists, create active editor record. If not, create pending_invite + send email.

9. transfer-ownership — Accepts {group_id, new_owner_id}. Updates groups.owner_id. Notifies both parties.

10. cron-session-reminders — Hourly via pg_cron. Finds sessions starting in 2 hours, creates notifications for attendees.

All functions must validate JWT and check permissions.

Set up Supabase Realtime and database triggers for Joinazo:

REALTIME — Enable on: messages, notifications, session_attendees.

TRIGGERS:
1. handle_new_user — AFTER INSERT on auth.users. Creates public.users row with email.
2. update_updated_at — BEFORE UPDATE on users, groups, support_tickets. Sets updated_at = now().
3. auto_slug — BEFORE INSERT OR UPDATE on groups. Generate slug from name (lowercase, hyphens, dedup).
4. name_change_reset — BEFORE UPDATE on groups. If name changes, set name_approved = false.
5. enforce_max_features — BEFORE INSERT on group_features. If count >= 5 for group_id, raise exception.
6. notify_on_payment — AFTER INSERT on payments. Creates notification for group owner.
7. notify_on_new_member — AFTER INSERT on member_subscriptions. Creates notification for group owner.

Create all with plpgsql trigger functions.

Connect the Joinazo React frontend to Supabase:

1. Install @supabase/supabase-js. Create client in src/lib/supabase.ts with VITE_SUPABASE_URL and VITE_SUPABASE_ANON_KEY.

2. Create AuthContext (src/contexts/AuthContext.tsx): user, session, signIn, signUp, signOut, resetPassword. Listen to onAuthStateChange. Redirect to /sign-in on protected routes.

3. Create React Query hooks in src/hooks/: useGroups(), useGroup(id), useGroupMembers(groupId), useSessions(groupId), useMessages() with realtime, useNotifications() with realtime + unread count, useLikedGroups(), usePayments(groupId?).

4. Replace ALL demo/mock/hardcoded data throughout the app with these Supabase-backed hooks.

5. Add loading states (skeleton loaders exist) and error handling with toast notifications.

6. Connect Supabase auth to SignIn and SignUp pages — UI exists, just needs auth logic.

Migrate the Joinazo React app to Next.js for server-side rendering (SSR) so that group profiles are fully crawlable by Google and Bing:

1. SETUP: Convert the Vite + React Router project to Next.js App Router. Move pages from src/pages/ to app/ directory structure. Keep all existing React components in src/components/.

2. DYNAMIC GROUP PAGES: Create app/club/[slug]/page.tsx with:
   - generateMetadata() that fetches the group from Supabase and returns: title = "{group.name} — {group.category} Club in {group.area} | Joinazo", description = group.description (truncated to 160 chars), openGraph image = group.cover_image_url, canonical URL.
   - Server-side data fetching using Supabase server client so the full HTML is rendered before reaching the browser.
   - JSON-LD structured data (schema.org/SportsClub) with name, description, address, aggregateRating, offers (membership prices).

3. STATIC GENERATION: Use generateStaticParams() to pre-render all approved+published group pages at build time. Revalidate every 3600 seconds (ISR).

4. CATEGORY & LOCATION PAGES: Create app/category/[slug]/page.tsx and app/location/[location]/page.tsx with SSR. Each should have unique meta titles like "Football Clubs Near You | Joinazo" and "Clubs in Manchester | Joinazo".

5. SITEMAP: Create app/sitemap.ts that dynamically generates sitemap.xml from the database — include all published group URLs, category pages, and location pages.

6. ROBOTS.TXT: Create app/robots.ts that returns: Allow all crawlers, sitemap URL pointing to /sitemap.xml.

7. HOME PAGE: Server-render the home page with featured clubs data. Meta title: "Joinazo — Find Clubs & Activities Near You".

For each group profile page in Joinazo, implement the following SEO elements using data from the Supabase "groups" table:

1. PAGE TITLE: "{group.name} — {group.category} Club in {group.area_tag} | Joinazo" (under 60 chars if possible).

2. META DESCRIPTION: Use group.description truncated to 155 characters with "..." appended. This is the text that appears in Google search results.

3. OPEN GRAPH TAGS:
   - og:title = same as page title
   - og:description = same as meta description
   - og:image = group.cover_image_url (or first image from group_images)
   - og:url = https://joinazo.com/club/{group.slug}
   - og:type = "website"

4. TWITTER CARD:
   - twitter:card = "summary_large_image"
   - twitter:title, twitter:description, twitter:image = same as OG

5. CANONICAL URL: <link rel="canonical" href="https://joinazo.com/club/{group.slug}" />

6. JSON-LD STRUCTURED DATA (in <script type="application/ld+json">):
   {
     "@context": "https://schema.org",
     "@type": "SportsClub",
     "name": group.name,
     "description": group.description,
     "image": group.cover_image_url,
     "address": { "@type": "PostalAddress", "addressLocality": group.area_tag },
     "aggregateRating": { "@type": "AggregateRating", "ratingValue": avg_rating, "reviewCount": review_count },
     "offers": memberships.map(m => ({ "@type": "Offer", "name": m.name, "price": m.price, "priceCurrency": "GBP" })),
     "url": "https://joinazo.com/club/" + group.slug
   }

7. SEMANTIC HTML: Use <article> wrapper, single <h1> for group name, <h2> for sections. Alt text on all images.

8. PERFORMANCE: Lazy-load gallery images below the fold. Use next/image with priority on cover image. Add loading="lazy" to review avatars.

Set up comprehensive SEO infrastructure for Joinazo so all group profiles are indexed by Google and Bing:

1. DYNAMIC SITEMAP (app/sitemap.ts):
   Generate XML sitemap from Supabase. Include:
   - Homepage: priority 1.0, changefreq daily
   - All published groups: /club/{slug}, priority 0.8, changefreq weekly, lastmod = group.updated_at
   - Category pages: /category/{slug}, priority 0.7, changefreq weekly
   - Location pages: /location/{area}, priority 0.6, changefreq weekly
   - Static pages (features, about): priority 0.5
   Max 50,000 URLs per sitemap. If more, create sitemap index.

2. ROBOTS.TXT (app/robots.ts):
   User-agent: *\nAllow: /\nSitemap: https://joinazo.com/sitemap.xml\nDisallow: /dashboard\nDisallow: /admin\nDisallow: /profile/edit\nDisallow: /checkout

3. GOOGLE SEARCH CONSOLE:
   - Add property for joinazo.com
   - Submit sitemap URL
   - Request indexing for key pages
   - Monitor coverage report for errors

4. BING WEBMASTER TOOLS:
   - Add site and verify ownership
   - Submit sitemap
   - Use URL submission API for new groups: POST https://ssl.bing.com/webmaster/api.svc/json/SubmitUrl with API key

5. AUTOMATIC INDEXING ON PUBLISH:
   Create a Supabase Edge Function "notify-search-engines" triggered when a group status changes to 'approved' + published = true:
   - Ping Google: GET https://www.google.com/ping?sitemap=https://joinazo.com/sitemap.xml
   - Submit to Bing IndexNow API: POST https://api.indexnow.org/indexnow with the new group URL
   - This ensures new groups appear in search results within hours, not weeks.

6. PAGE SPEED: Implement Next.js Image optimization, font preloading (next/font), and component code-splitting for Core Web Vitals compliance.

Create the apps, feed_posts, and withdrawals tables for Joinazo:

CREATE TABLE public.apps (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  app_key TEXT UNIQUE NOT NULL,
  name TEXT NOT NULL,
  description TEXT,
  long_description TEXT,
  icon_name TEXT NOT NULL,
  category TEXT NOT NULL,
  tier TEXT DEFAULT 'free' CHECK (tier IN ('free', 'premium')),
  features JSONB DEFAULT '[]'::jsonb,
  installs_count INTEGER DEFAULT 0,
  rating DECIMAL(2,1) DEFAULT 0,
  is_active BOOLEAN DEFAULT true,
  created_at TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE public.feed_posts (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  group_id UUID REFERENCES public.groups(id) ON DELETE CASCADE NOT NULL,
  author_id UUID REFERENCES public.users(id) NOT NULL,
  content TEXT NOT NULL,
  image_url TEXT,
  post_type TEXT DEFAULT 'update' CHECK (post_type IN ('update', 'announcement', 'event', 'result', 'photo')),
  pinned BOOLEAN DEFAULT false,
  likes_count INTEGER DEFAULT 0,
  comments_count INTEGER DEFAULT 0,
  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE public.feed_comments (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  post_id UUID REFERENCES public.feed_posts(id) ON DELETE CASCADE NOT NULL,
  user_id UUID REFERENCES public.users(id) NOT NULL,
  content TEXT NOT NULL,
  created_at TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE public.feed_likes (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  post_id UUID REFERENCES public.feed_posts(id) ON DELETE CASCADE NOT NULL,
  user_id UUID REFERENCES public.users(id) NOT NULL,
  created_at TIMESTAMPTZ DEFAULT now(),
  UNIQUE (post_id, user_id)
);

CREATE TABLE public.withdrawals (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID REFERENCES public.users(id) NOT NULL,
  amount DECIMAL(10,2) NOT NULL,
  status TEXT DEFAULT 'pending' CHECK (status IN ('pending', 'processing', 'completed', 'failed')),
  stripe_payout_id TEXT,
  bank_last_four TEXT,
  requested_at TIMESTAMPTZ DEFAULT now(),
  completed_at TIMESTAMPTZ
);

Enable RLS on all. apps: public read, admin manages. feed_posts: members of group can read, group owner + editors can create/update. feed_comments/likes: authenticated users can read for groups they belong to, user manages own. withdrawals: user reads own, admin reads all.

Create staff_members, session_instances, and ticket_replies tables for Joinazo:

CREATE TABLE public.staff_members (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID REFERENCES public.users(id) ON DELETE CASCADE NOT NULL,
  invited_by UUID REFERENCES public.users(id) NOT NULL,
  role TEXT DEFAULT 'staff' CHECK (role IN ('staff', 'senior_staff')),
  permissions JSONB DEFAULT '{}'::jsonb,
  status TEXT DEFAULT 'pending' CHECK (status IN ('active', 'pending', 'revoked')),
  created_at TIMESTAMPTZ DEFAULT now()
);
-- permissions JSONB stores: { "manage_users": true, "manage_payments": true, "review_profiles": true, "manage_support": true, "view_analytics": true, "manage_settings": true, "manage_refunds": true, "manage_stripe": true }

ALTER TABLE public.sessions ADD COLUMN IF NOT EXISTS member_price DECIMAL(10,2) DEFAULT 0;
ALTER TABLE public.sessions ADD COLUMN IF NOT EXISTS non_member_price DECIMAL(10,2) DEFAULT 0;
ALTER TABLE public.sessions ADD COLUMN IF NOT EXISTS included_in_membership BOOLEAN DEFAULT true;
ALTER TABLE public.sessions ADD COLUMN IF NOT EXISTS free_trial_welcome BOOLEAN DEFAULT false;
ALTER TABLE public.sessions ADD COLUMN IF NOT EXISTS free_trial_info TEXT;
ALTER TABLE public.sessions ADD COLUMN IF NOT EXISTS description TEXT;
ALTER TABLE public.sessions ADD COLUMN IF NOT EXISTS image_url TEXT;
ALTER TABLE public.sessions ADD COLUMN IF NOT EXISTS frequency TEXT DEFAULT 'weekly';

CREATE TABLE public.session_instances (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  session_id UUID REFERENCES public.sessions(id) ON DELETE CASCADE NOT NULL,
  date DATE NOT NULL,
  status TEXT DEFAULT 'scheduled' CHECK (status IN ('scheduled', 'cancelled', 'completed')),
  cancellation_reason TEXT,
  actual_attendees INTEGER DEFAULT 0,
  revenue DECIMAL(10,2) DEFAULT 0,
  created_at TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE public.ticket_replies (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  ticket_id UUID REFERENCES public.support_tickets(id) ON DELETE CASCADE NOT NULL,
  user_id UUID REFERENCES public.users(id) NOT NULL,
  content TEXT NOT NULL,
  is_staff_reply BOOLEAN DEFAULT false,
  created_at TIMESTAMPTZ DEFAULT now()
);

Enable RLS on all. staff_members: admin-only read/write. session_instances: owner + editors manage, public read for published groups. ticket_replies: ticket owner + admin can read/write.

Wire up the admin dashboard features in the Joinazo frontend:

1. ADMIN SEARCH: Create a search endpoint or Supabase query that searches across users (by name, email) and groups (by name, owner). Display results with type icons and link to user/group detail sheets.

2. PENDING APPROVALS: Query profile_reviews WHERE status = 'pending' joined with groups. Show approval cards with group name, owner, category, location, and submitted date. Review button navigates to the Profile Reviews tab. Decline creates a rejection with mandatory reason.

3. BALANCE DASHBOARD: For the host dashboard sidebar, query the stripe_connected_accounts table for the current user to show real balance. The Withdraw button creates a new withdrawals record and triggers the create-payout edge function.

4. STAFF MANAGEMENT: In admin settings, build the "Recruit Staff" modal that:
   - Searches existing users by email
   - Assigns granular permissions via checkboxes
   - Creates a staff_members record
   - Sends an email notification via send-notification-email edge function

5. FEED TAB: Connect the dashboard Feed tab to feed_posts. Group owners can create posts (text + optional image). Members see posts from groups they belong to, ordered by date. Implement like/comment with optimistic updates.

6. Create React Query hooks: useAdminSearch(query), usePendingApprovals(), useHostBalance(), useStaffMembers(), useFeedPosts(groupId), useWithdrawals().

Create all Supabase Edge Functions for Stripe Connect payments in Joinazo. Each function goes in supabase/functions/<name>/index.ts.

Secrets needed (add via Supabase Dashboard → Settings → Secrets):
- STRIPE_SECRET_KEY
- STRIPE_WEBHOOK_SECRET

Install stripe in each function: import Stripe from "https://esm.sh/stripe@14.14.0?target=deno"

Functions to create:

1. create-connect-account
   - POST: Takes group_id from body
   - Creates a Stripe Express account (type: "express")
   - Saves stripe_account_id to groups table
   - Creates an account_link for onboarding and returns the URL
   - Trigger: Group setup Step 8 "Connect with Stripe" button

2. create-checkout-session
   - POST: Takes membership_id OR session_id, plus group_id
   - Looks up the group's stripe_account_id
   - Creates Stripe Checkout Session with:
     - payment_intent_data.application_fee_amount = 5% of price
     - payment_intent_data.transfer_data.destination = stripe_account_id
     - mode: "payment" for one-time, "subscription" for recurring
   - Returns checkout session URL
   - Trigger: Member clicks Join/Subscribe on club profile

3. create-subscription
   - POST: Takes membership_id, user_id
   - Creates or retrieves Stripe Customer
   - Creates Subscription with transfer_data to connected account
   - Inserts member_subscriptions row with status "active"
   - Returns client_secret for confirmation

4. stripe-webhook
   - POST: Verifies Stripe signature using STRIPE_WEBHOOK_SECRET
   - Handles events:
     a. checkout.session.completed → insert payments row, activate member_subscriptions
     b. invoice.paid → insert payments row for recurring billing
     c. customer.subscription.deleted → set member_subscriptions status to "cancelled"
     d. charge.refunded → insert refunds row with status "completed"
   - Uses Supabase service role client for all DB writes

5. create-connect-login-link
   - POST: Takes group_id
   - Looks up stripe_account_id from groups table
   - Creates Express Dashboard login link
   - Returns URL so owner can view balance/payouts on Stripe
   - Trigger: Dashboard "View Stripe Dashboard" or "Withdraw" button

6. cancel-subscription
   - POST: Takes subscription_id (member_subscriptions.id)
   - Looks up stripe_subscription_id
   - Cancels on Stripe (at_period_end: true)
   - Updates member_subscriptions status to "cancelled"

7. create-refund
   - POST: Takes payment_id, optional amount for partial refund
   - Looks up stripe_payment_intent_id from payments table
   - Creates Stripe refund
   - Inserts refunds row with status "completed"
   - Updates payments status to "refunded"

All functions MUST:
- Include CORS headers (Access-Control-Allow-Origin: *)
- Handle OPTIONS preflight requests
- Validate JWT from Authorization header using Supabase auth.getUser()
- Return proper error codes (400, 401, 500) with JSON error messages
- Use the service role key for database writes (createClient with SUPABASE_SERVICE_ROLE_KEY)

Create an embeddable widget system for Joinazo so group owners can embed their membership pricing table and session timetable on their own external websites.

1. CREATE EMBED ENDPOINT (Edge Function: serve-embed-data)
   - GET: Takes group_id or slug as query param
   - Returns JSON with group's active memberships and sessions
   - No auth required (public data for published groups)
   - Include CORS headers for cross-origin requests
   - Cache-Control: max-age=300 (5 min cache)

2. CREATE EMBED SCRIPT (public/embed.js)
   - Lightweight JS script (~5KB) that group owners paste on their site
   - Usage: <script src="https://joinazo.com/embed.js" data-group="slug"></script>
   - Fetches data from serve-embed-data edge function
   - Renders a styled membership table and session timetable
   - Includes a "Powered by Joinazo" link
   - Auto-refreshes data — any changes on Joinazo reflect on their site automatically
   - Responsive design that adapts to container width
   - Support data-theme="light" or data-theme="dark" attribute

3. FRONTEND: Embed Code Generator
   - On the group dashboard, add an "Embed on your website" section
   - Show the embed script tag with the group's slug pre-filled
   - Copy button to clipboard
   - Live preview of how it will look
   - Toggle options: show memberships, show sessions, theme selection

4. The widget should render:
   - Membership cards with name, price, billing interval, features list, and a "Join" button linking to the Joinazo checkout
   - Session timetable showing day, time, location, and available spots
   - Both sections update in real-time when the owner edits them on Joinazo