coolride-engine / db_schema.sql
Swaminaatha's picture
Created db_schema.sql containing the schema of the supabase db
bb56ce1 verified
Raw History Blame Contribute Delete
1.54 kB
-- 1. DROP (Clean Slate)
drop table if exists route_logs;
drop table if exists community_reports;
-- 2. ROUTE LOGS (For Model Training)
create table route_logs (
id bigint generated by default as identity primary key,
created_at timestamp with time zone default timezone('utc'::text, now()) not null,
user_email text default 'guest',
start_point text,
end_point text,
fast_dist_km float,
cool_dist_km float,
dist_diff_km float,
shade_gain_pct int,
temp_c float,
weather_station text
);
-- 3. COMMUNITY REPORTS (For "Waze" Logic)
create table community_reports (
id bigint generated by default as identity primary key,
created_at timestamp with time zone default timezone('utc'::text, now()) not null,
user_email text, -- Who reported it?
lat float not null,
lon float not null,
report_type text not null, -- 'too_hot', 'blocked', 'shade_oasis', 'construction'
description text,
-- Validation Metrics (For the Algorithm)
upvotes int default 0, -- Higher votes = More influence on route
verified boolean default false -- Admin override
);
-- 4. SECURITY (Open for MVP)
alter table route_logs enable row level security;
alter table community_reports enable row level security;
create policy "Public Insert Logs" on route_logs for insert with check (true);
create policy "Public Read Logs" on route_logs for select using (true);
create policy "Public Insert Reports" on community_reports for insert with check (true);
create policy "Public Read Reports" on community_reports for select using (true);