2. Supabase setup
- Go to Supabase.com and login or create account.
- Create project
- Go to SQL Editor
- Copy & Paste SQL Code to create the tables. (below)
- Create User and password. Project / Authentication / Add User / Create new user.
Enter email & password you want to use for leaderboard admin login.
- Create user in profiles table. insert / insert row / id, select record
Enter email address as username and "admin" as role.
- Import sample data (optional)
Create Table: hotlaps (Copy & Paste in SQL Editor)
Skip This if you already have a table for ACCR-Sync hotlaps.
CREATE TABLE public.hotlaps (
id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
created_at timestamptz NOT NULL DEFAULT timezone('utc'::text, now()),
driver_name text NOT NULL,
car_model text NOT NULL,
track text NOT NULL,
best_lap_ms integer NOT NULL,
session_id text UNIQUE,
wetlap text,
best_splits integer[]
);
create or replace function get_unique_tracks()
returns table (track text)
language sql
as $$
select distinct track from hotlaps where track is not null;
$$;
ALTER TABLE hotlaps
ADD CONSTRAINT unique_session_lap UNIQUE (session_id, best_lap_ms);
ALTER TABLE public.hotlaps ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Allow public read access" ON public.hotlaps FOR SELECT TO public USING (true);
CREATE POLICY "Allow authenticated write access" ON public.hotlaps FOR ALL TO authenticated USING (true) WITH CHECK (true);
Create Required Tables: (Copy & Paste in SQL Editor)
Required.
-- 1. Profiles Table & Policies
create table public.profiles (
id uuid references auth.users on delete cascade primary key,
username text,
role text default 'user' check (role in ('user', 'moderator', 'admin'))
);
alter table public.profiles enable row level security;
create policy "Public profiles are viewable by everyone." on profiles for select using (true);
create policy "Users can update own profile." on profiles for update using (auth.uid() = id);
-- 2. Championships Table
CREATE TABLE public.championships (
id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
created_at timestamptz NOT NULL DEFAULT timezone('utc'::text, now()),
championship_name text NOT NULL,
league_id bigint NOT NULL,
season bigint NOT NULL,
season_complete BOOLEAN,
entrylist_id int NULL,
showschedule BOOLEAN,
servername text NOT NULL,
serverpass text NOT NULL
);
-- 3. Clmconfig Table
CREATE TABLE public.clmconfig (
id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
created_at timestamptz NOT NULL DEFAULT timezone('utc'::text, now()),
pagetitle text NOT NULL,
commname text NOT NULL,
commslogan text NOT NULL,
local_tz text DEFAULT 'GMT',
uselogo BOOLEAN,
usercolor text NOT NULL,
commsubtitle text NOT NULL,
discord text NOT NULL,
youtube text NOT NULL,
instagram text NOT NULL,
twitch text NOT NULL,
accrsync BOOLEAN,
logourl text NOT NULL,
showcalendar BOOLEAN
);
-- Insert initial configuration
INSERT INTO public.clmconfig (
pagetitle,
commname,
commslogan,
local_tz,
uselogo,
usercolor,
commsubtitle,
discord,
youtube,
instagram,
twitch,
accrsync,
logourl,
showcalendar
)
VALUES (
'Your Racing Team | Championship Leaderboards',
'Your Racing Team',
'COMMUNITY DRIVEN • POWERED BY PASSION',
'GMT',
TRUE,
'#ff00dd',
'CHAMPIONSHIP LEADERBOARDS',
'',
'',
'',
'',
FALSE,
'logo.png',
FALSE
);
-- 4. Driverlist Table
CREATE TABLE public.driverlist (
id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
created_at timestamptz NOT NULL DEFAULT timezone('utc'::text, now()),
driver_name text NOT NULL,
nationality text NOT NULL,
driversid text NULL,
championshipids integer[],
listids integer[]
);
-- 5. Entrylists Table
CREATE TABLE public.entrylists (
id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
created_at timestamptz NOT NULL DEFAULT timezone('utc'::text, now()),
entrylistname text NOT NULL
);
-- 6. Leagues Table
CREATE TABLE public.leagues (
id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
created_at timestamptz NOT NULL DEFAULT timezone('utc'::text, now()),
league_name text NOT NULL
);
-- 7. Champresults Table
CREATE TABLE public.champresults (
id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
created_at timestamptz NOT NULL DEFAULT timezone('utc'::text, now()),
driver_name text NOT NULL,
car_model text NOT NULL,
track text NOT NULL,
best_lap_ms integer NOT NULL,
session_id text NOT NULL,
wetlap text,
best_splits integer[],
final_position bigint NOT NULL,
championshipid bigint NOT NULL,
raceid bigint NOT NULL,
rpoints bigint,
driversid text NOT NULL,
total_time_ms bigint NOT NULL,
penalty_time int NOT NULL
);
-- 8. Seasonschedule Table
CREATE TABLE public.seasonschedule (
id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
created_at timestamptz NOT NULL DEFAULT timezone('utc'::text, now()),
championshipid bigint NOT NULL,
raceid bigint NOT NULL,
trackname text NOT NULL,
racedate text NOT NULL,
"pitReq" BOOLEAN,
"fuelReq" BOOLEAN,
"tyresReq" BOOLEAN,
racetimelength smallint DEFAULT '0'::smallint,
qualitimelength smallint DEFAULT '0'::smallint,
practicetimelength smallint DEFAULT '0'::smallint
);
-- 9. Apply Admin Policies Loop to Tables
DO $$
DECLARE
t text;
tables text[] := ARRAY['clmconfig', 'leagues', 'championships', 'seasonschedule', 'champresults', 'entrylists', 'hotlaps', 'driverlist'];
BEGIN
FOREACH t IN ARRAY tables
LOOP
-- 1. Ensure RLS is enabled
EXECUTE format('ALTER TABLE %I ENABLE ROW LEVEL SECURITY;', t);
-- 2. Drop old conflicting policies if they exist
EXECUTE format('DROP POLICY IF EXISTS "Enable all access for authenticated users" ON %I;', t);
EXECUTE format('DROP POLICY IF EXISTS "Allow public read access" ON %I;', t);
EXECUTE format('DROP POLICY IF EXISTS "Allow authenticated write access" ON %I;', t);
-- 3. Allow public (anonymous front-end visitors) to READ data
EXECUTE format('CREATE POLICY "Allow public read access" ON %I FOR SELECT TO public USING (true);', t);
-- 4. Allow ONLY logged-in users to INSERT, UPDATE, and DELETE
EXECUTE format('CREATE POLICY "Allow authenticated write access" ON %I FOR ALL TO authenticated USING (true) WITH CHECK (true);', t);
END LOOP;
END $$;