coolride-telegram / relevant_schema.sql
Swaminaatha krishna
Added relevant updated database schema backup
794500a
Raw History Blame Contribute Delete
8.43 kB
--
-- PostgreSQL database dump
--
\restrict lUtXg8tmeCyIZ5oci1TsVpXqbqcocLeERyHGNxqpmewUVF1LCzrex3BnhXfyYmk
-- Dumped from database version 17.6
-- Dumped by pg_dump version 17.7 (Homebrew)
SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET transaction_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;
SET default_tablespace = '';
SET default_table_access_method = heap;
--
-- Name: community_reports; Type: TABLE; Schema: public; Owner: postgres
--
CREATE TABLE public.community_reports (
id bigint NOT NULL,
created_at timestamp with time zone DEFAULT timezone('utc'::text, now()) NOT NULL,
user_email text,
lat double precision NOT NULL,
lon double precision NOT NULL,
report_type text NOT NULL,
description text,
upvotes integer DEFAULT 0,
verified boolean DEFAULT false
);
ALTER TABLE public.community_reports OWNER TO postgres;
--
-- Name: community_reports_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--
ALTER TABLE public.community_reports ALTER COLUMN id ADD GENERATED BY DEFAULT AS IDENTITY (
SEQUENCE NAME public.community_reports_id_seq
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1
);
--
-- Name: feedback_logs; Type: TABLE; Schema: public; Owner: postgres
--
CREATE TABLE public.feedback_logs (
id bigint NOT NULL,
created_at timestamp with time zone DEFAULT timezone('utc'::text, now()) NOT NULL,
user_email text,
route_id bigint,
sentiment text,
user_comment text,
chat_transcript text,
context_weather_temp double precision
);
ALTER TABLE public.feedback_logs OWNER TO postgres;
--
-- Name: feedback_logs_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--
ALTER TABLE public.feedback_logs ALTER COLUMN id ADD GENERATED BY DEFAULT AS IDENTITY (
SEQUENCE NAME public.feedback_logs_id_seq
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1
);
--
-- Name: route_logs; Type: TABLE; Schema: public; Owner: postgres
--
CREATE TABLE public.route_logs (
id bigint NOT NULL,
created_at timestamp with time zone DEFAULT timezone('utc'::text, now()) NOT NULL,
user_email text DEFAULT 'guest'::text,
start_point text,
end_point text,
fast_dist_km double precision,
cool_dist_km double precision,
dist_diff_km double precision,
shade_gain_pct integer,
temp_c double precision,
weather_station text,
via_point text
);
ALTER TABLE public.route_logs OWNER TO postgres;
--
-- Name: route_logs_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--
ALTER TABLE public.route_logs ALTER COLUMN id ADD GENERATED BY DEFAULT AS IDENTITY (
SEQUENCE NAME public.route_logs_id_seq
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1
);
--
-- Name: sensor_logs; Type: TABLE; Schema: public; Owner: postgres
--
CREATE TABLE public.sensor_logs (
id bigint NOT NULL,
created_at timestamp with time zone DEFAULT timezone('utc'::text, now()) NOT NULL,
user_id text,
wbgt_value double precision,
lat double precision,
lon double precision,
notes text,
nea_temp double precision,
weather_station text
);
ALTER TABLE public.sensor_logs OWNER TO postgres;
--
-- Name: sensor_logs_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--
ALTER TABLE public.sensor_logs ALTER COLUMN id ADD GENERATED BY DEFAULT AS IDENTITY (
SEQUENCE NAME public.sensor_logs_id_seq
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1
);
--
-- Name: community_reports community_reports_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--
ALTER TABLE ONLY public.community_reports
ADD CONSTRAINT community_reports_pkey PRIMARY KEY (id);
--
-- Name: feedback_logs feedback_logs_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--
ALTER TABLE ONLY public.feedback_logs
ADD CONSTRAINT feedback_logs_pkey PRIMARY KEY (id);
--
-- Name: route_logs route_logs_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--
ALTER TABLE ONLY public.route_logs
ADD CONSTRAINT route_logs_pkey PRIMARY KEY (id);
--
-- Name: sensor_logs sensor_logs_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--
ALTER TABLE ONLY public.sensor_logs
ADD CONSTRAINT sensor_logs_pkey PRIMARY KEY (id);
--
-- Name: feedback_logs Allow all inserts; Type: POLICY; Schema: public; Owner: postgres
--
CREATE POLICY "Allow all inserts" ON public.feedback_logs FOR INSERT WITH CHECK (true);
--
-- Name: sensor_logs Allow authenticated users to insert logs; Type: POLICY; Schema: public; Owner: postgres
--
CREATE POLICY "Allow authenticated users to insert logs" ON public.sensor_logs FOR INSERT TO authenticated WITH CHECK (true);
--
-- Name: route_logs Public Insert Logs; Type: POLICY; Schema: public; Owner: postgres
--
CREATE POLICY "Public Insert Logs" ON public.route_logs FOR INSERT WITH CHECK (true);
--
-- Name: community_reports Public Insert Reports; Type: POLICY; Schema: public; Owner: postgres
--
CREATE POLICY "Public Insert Reports" ON public.community_reports FOR INSERT WITH CHECK (true);
--
-- Name: route_logs Public Read Logs; Type: POLICY; Schema: public; Owner: postgres
--
CREATE POLICY "Public Read Logs" ON public.route_logs FOR SELECT USING (true);
--
-- Name: community_reports Public Read Reports; Type: POLICY; Schema: public; Owner: postgres
--
CREATE POLICY "Public Read Reports" ON public.community_reports FOR SELECT USING (true);
--
-- Name: community_reports; Type: ROW SECURITY; Schema: public; Owner: postgres
--
ALTER TABLE public.community_reports ENABLE ROW LEVEL SECURITY;
--
-- Name: route_logs; Type: ROW SECURITY; Schema: public; Owner: postgres
--
ALTER TABLE public.route_logs ENABLE ROW LEVEL SECURITY;
--
-- Name: sensor_logs; Type: ROW SECURITY; Schema: public; Owner: postgres
--
ALTER TABLE public.sensor_logs ENABLE ROW LEVEL SECURITY;
--
-- Name: TABLE community_reports; Type: ACL; Schema: public; Owner: postgres
--
GRANT ALL ON TABLE public.community_reports TO anon;
GRANT ALL ON TABLE public.community_reports TO authenticated;
GRANT ALL ON TABLE public.community_reports TO service_role;
--
-- Name: SEQUENCE community_reports_id_seq; Type: ACL; Schema: public; Owner: postgres
--
GRANT ALL ON SEQUENCE public.community_reports_id_seq TO anon;
GRANT ALL ON SEQUENCE public.community_reports_id_seq TO authenticated;
GRANT ALL ON SEQUENCE public.community_reports_id_seq TO service_role;
--
-- Name: TABLE feedback_logs; Type: ACL; Schema: public; Owner: postgres
--
GRANT ALL ON TABLE public.feedback_logs TO anon;
GRANT ALL ON TABLE public.feedback_logs TO authenticated;
GRANT ALL ON TABLE public.feedback_logs TO service_role;
--
-- Name: SEQUENCE feedback_logs_id_seq; Type: ACL; Schema: public; Owner: postgres
--
GRANT ALL ON SEQUENCE public.feedback_logs_id_seq TO anon;
GRANT ALL ON SEQUENCE public.feedback_logs_id_seq TO authenticated;
GRANT ALL ON SEQUENCE public.feedback_logs_id_seq TO service_role;
--
-- Name: TABLE route_logs; Type: ACL; Schema: public; Owner: postgres
--
GRANT ALL ON TABLE public.route_logs TO anon;
GRANT ALL ON TABLE public.route_logs TO authenticated;
GRANT ALL ON TABLE public.route_logs TO service_role;
--
-- Name: SEQUENCE route_logs_id_seq; Type: ACL; Schema: public; Owner: postgres
--
GRANT ALL ON SEQUENCE public.route_logs_id_seq TO anon;
GRANT ALL ON SEQUENCE public.route_logs_id_seq TO authenticated;
GRANT ALL ON SEQUENCE public.route_logs_id_seq TO service_role;
--
-- Name: TABLE sensor_logs; Type: ACL; Schema: public; Owner: postgres
--
GRANT ALL ON TABLE public.sensor_logs TO anon;
GRANT ALL ON TABLE public.sensor_logs TO authenticated;
GRANT ALL ON TABLE public.sensor_logs TO service_role;
--
-- Name: SEQUENCE sensor_logs_id_seq; Type: ACL; Schema: public; Owner: postgres
--
GRANT ALL ON SEQUENCE public.sensor_logs_id_seq TO anon;
GRANT ALL ON SEQUENCE public.sensor_logs_id_seq TO authenticated;
GRANT ALL ON SEQUENCE public.sensor_logs_id_seq TO service_role;
--
-- PostgreSQL database dump complete
--
\unrestrict lUtXg8tmeCyIZ5oci1TsVpXqbqcocLeERyHGNxqpmewUVF1LCzrex3BnhXfyYmk