Settings - SiteAdvisor

Step 0: Configure Supabase Connection

Before running the SQL scripts, you need to connect this application to your Supabase project. You'll need to edit one file in your project's code.

Create a new project in your Supabase dashboard at supabase.com.
Go to your project's Settings (the gear icon).
Click on API in the sidebar.
Find your Project URL and your Project API key for the `anon` `public` role.
Open the `storage.js` file in your code editor.
Copy and paste your URL and anon key into the top of the file, replacing the placeholder values.

// in storage.js
import { createClient } from '...';

const SUPABASE_URL = 'YOUR_SUPABASE_URL_HERE';
const SUPABASE_ANON_KEY = 'YOUR_SUPABASE_ANON_KEY_HERE';

// ... rest of the file

Step 1: Go to the SQL Editor

First, you need to run some SQL queries to create the necessary database table and security policies.

Log in to your Supabase project dashboard at supabase.com.
Navigate to the correct project if you have more than one.
In the left sidebar, find and click on the SQL Editor icon.
Click on + New query to open a new editor tab.

Step 2: Enable Authentication

To handle user accounts, you need to enable Supabase's built-in authentication.

In the left sidebar of your Supabase dashboard, click the Authentication icon (looks like a person).
Go to the Providers section.
Find Email in the list and click to enable it. You can leave the default settings, including keeping 'Enable email confirmations' turned on. This is crucial for security.
Supabase automatically creates a users table in the auth schema when you enable a provider. You don't need to create it manually.

After this, you can return to the SQL Editor for the next steps.

Step 3: Create the 'reports' Table

Copy the following SQL code, paste it into the query editor, and click 'RUN' (or press Cmd/Ctrl + Enter). This version includes a `user_id` field to link reports to authenticated users.

CREATE TABLE public.reports (
  id uuid NOT NULL DEFAULT gen_random_uuid(),
  created_at timestamp with time zone NOT NULL DEFAULT now(),
  url text NOT NULL,
  report_data jsonb NOT NULL,
  -- user_id links the report to a user. It defaults to the ID of the user creating the report.
  user_id uuid DEFAULT auth.uid(),
  CONSTRAINT reports_pkey PRIMARY KEY (id),
  CONSTRAINT reports_user_id_fkey FOREIGN KEY (user_id) REFERENCES auth.users (id) ON DELETE SET NULL
);

Step 4: Enable Row Level Security (RLS)

Row Level Security is a PostgreSQL feature that controls which rows users can access. It's crucial for securing your data. Create a new query, paste the following code, and run it.

ALTER TABLE public.reports
ENABLE ROW LEVEL SECURITY;

Step 5: Create Security Policies for 'reports' Table

Policies are rules that determine who can access or modify data. These policies will allow your app to read, create, and delete reports. Note: These have been updated to allow any logged-in user to create reports. Run all parts of this step.

First, we need a helper function to check a user's role. Create a new query, paste this, and run it:

CREATE OR REPLACE FUNCTION public.has_role(role_to_check text)
RETURNS boolean
LANGUAGE plpgsql
SECURITY DEFINER SET search_path = public
AS $$
DECLARE
  user_roles text[];
BEGIN
  IF auth.uid() IS NULL THEN
    RETURN false;
  END IF;
  SELECT roles INTO user_roles FROM public.profiles WHERE id = auth.uid();
  RETURN role_to_check = ANY(user_roles);
END;
$$;

Now, create the actual policies for the `reports` table. Run this in a new query:

-- Drop old policies if they exist to avoid conflicts. It's okay if these return an error that the policy does not exist.
DROP POLICY IF EXISTS "Enable read access for all users" ON public.reports;
DROP POLICY IF EXISTS "Enable insert for subscribers and admins" ON public.reports;
DROP POLICY IF EXISTS "Enable insert for authenticated users" ON public.reports;
DROP POLICY IF EXISTS "Enable delete for admins" ON public.reports;

-- 1. Anyone can view existing reports (e.g. via a shared link).
CREATE POLICY "Enable read access for all users"
ON public.reports FOR SELECT USING (true);

-- 2. Any authenticated (logged-in) user can create new reports.
CREATE POLICY "Enable insert for authenticated users"
ON public.reports FOR INSERT
WITH CHECK (auth.role() = 'authenticated');

-- 3. Only admins can delete reports from the report management page.
CREATE POLICY "Enable delete for admins"
ON public.reports FOR DELETE
USING (public.has_role('admin'));

Step 6: User Management Setup

The following steps will create a `profiles` table to store user roles and a trigger to automatically create a profile when a new user signs up.

Part A: Create the `profiles` table. Run this in a new query:

-- Create a table for public user data
CREATE TABLE public.profiles (
  id uuid NOT NULL PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
  name text,
  email text,
  roles text[]
);

-- Set up Row Level Security
ALTER TABLE public.profiles ENABLE ROW LEVEL SECURITY;

-- Add policies for profiles table
CREATE POLICY "Allow individual read access" ON public.profiles FOR SELECT USING (auth.uid() = id);
CREATE POLICY "Allow admins to read all profiles" ON public.profiles FOR SELECT USING (public.has_role('admin'));
CREATE POLICY "Allow admins to update any profile" ON public.profiles FOR UPDATE USING (public.has_role('admin')) WITH CHECK (public.has_role('admin'));

Part B: Create a function to handle new users. This function will automatically create a profile for new users with no roles assigned by default. Run this in a new query:

-- Function to create a profile for new users
CREATE OR REPLACE FUNCTION public.handle_new_user()
RETURNS trigger
LANGUAGE plpgsql
SECURITY DEFINER SET search_path = public
AS $$
BEGIN
  INSERT INTO public.profiles (id, name, email, roles)
  VALUES (new.id, new.raw_user_meta_data->>'name', new.email, '{}');
  RETURN new;
END;
$$;

Part C: Create a trigger to call the function. This trigger will run the function every time a new user signs up. Run this in a new query:

-- Trigger to execute the function on user creation
CREATE TRIGGER on_auth_user_created
  AFTER INSERT ON auth.users
  FOR EACH ROW EXECUTE FUNCTION public.handle_new_user();

Part D: Create a function to allow users to make themselves an admin. This is useful for setting up the first admin user. Run this in a new query:

-- Function to add 'admin' to the calling user's roles array.
CREATE OR REPLACE FUNCTION make_caller_admin()
RETURNS void
LANGUAGE plpgsql
SECURITY DEFINER SET search_path = public
AS $$
BEGIN
  IF auth.uid() IS NOT NULL THEN
    UPDATE public.profiles
    SET roles = array_append(coalesce(roles, '{}'::text[]), 'admin')
    WHERE id = auth.uid() AND NOT ('admin' = ANY(coalesce(roles, '{}'::text[])));
  END IF;
END;
$$;

Step 7: Create Configuration Table (Optional)

This step creates a table to store site-wide API keys, which can be managed from the Admin Panel. If you want every visitor to use the same API keys, run the following script. Visitors will still be able to override these with their own keys using the settings on the main page.

This script has been updated to include Stripe API keys. If you have already run this, you can run the `ALTER TABLE` command separately to add the new columns.

-- 1. Create the configuration table for site-wide API keys
CREATE TABLE public.configuration (
  id bool PRIMARY KEY DEFAULT true,
  pagespeed_api_key text,
  gemini_api_key text,
  stripe_publishable_key text,
  stripe_secret_key text,
  CONSTRAINT singleton_check CHECK (id = true)
);

-- Use this command if the table already exists, to add the new columns:
-- ALTER TABLE public.configuration
-- ADD COLUMN stripe_publishable_key text,
-- ADD COLUMN stripe_secret_key text;

-- 2. Enable Row Level Security (RLS)
ALTER TABLE public.configuration
ENABLE ROW LEVEL SECURITY;

-- 3. Create security policies for the configuration table
-- Allows anyone to read keys, but only admins can update them.
CREATE POLICY "Enable read access for all users"
ON public.configuration FOR SELECT USING (true);

CREATE POLICY "Enable write access for admins"
ON public.configuration FOR UPDATE
USING (public.has_role('admin'))
WITH CHECK (public.has_role('admin'));

-- 4. Insert the initial empty configuration row
INSERT INTO public.configuration (id) VALUES (true);

All Done!

Once you've run all queries successfully, your database is configured! You can now return to the Reporter or the Report Management and start using the application.

API Key Management