atharvadeokar/Horizon

1��--
2-- PostgreSQL database dump
3--
4
5\restrict ORiQeFrDKXSwNyG1XMOjkEIEHHIqokFhqRIgFoOo7oSboleXUEegCTvFX1Rfhb7
6
7-- Dumped from database version 18.2
8-- Dumped by pg_dump version 18.2
9
10SET statement_timeout = 0;
11SET lock_timeout = 0;
12SET idle_in_transaction_session_timeout = 0;
13SET transaction_timeout = 0;
14SET client_encoding = 'UTF8';
15SET standard_conforming_strings = on;
16SELECT pg_catalog.set_config('search_path', '', false);
17SET check_function_bodies = false;
18SET xmloption = content;
19SET client_min_messages = warning;
20SET row_security = off;
21
22--
23-- Name: public; Type: SCHEMA; Schema: -; Owner: postgres
24--
25
26-- *not* creating schema, since initdb creates it
27
28
29ALTER SCHEMA public OWNER TO postgres;
30
31--
32-- Name: SCHEMA public; Type: COMMENT; Schema: -; Owner: postgres
33--
34
35COMMENT ON SCHEMA public IS '';
36
37
38SET default_tablespace = '';
39
40SET default_table_access_method = heap;
41
42--
43-- Name: application_milestones; Type: TABLE; Schema: public; Owner: postgres
44--
45
46CREATE TABLE public.application_milestones (
47    id uuid NOT NULL,
48    student_id uuid NOT NULL,
49    milestone_type character varying NOT NULL,
50    due_date date NOT NULL,
51    is_hard_deadline boolean,
52    buffer_start_date date,
53    urgency_score integer,
54    created_at timestamp without time zone
55);
56
57
58ALTER TABLE public.application_milestones OWNER TO postgres;
59
60--
61-- Name: profile_intake_logs; Type: TABLE; Schema: public; Owner: postgres
62--
63
64CREATE TABLE public.profile_intake_logs (
65    id uuid NOT NULL,
66    student_id uuid NOT NULL,
67    raw_text text,
68    extracted_json json,
69    created_at timestamp without time zone
70);
71
72
73ALTER TABLE public.profile_intake_logs OWNER TO postgres;
74
75--
76-- Name: roadmap_nodes; Type: TABLE; Schema: public; Owner: postgres
77--
78
79CREATE TABLE public.roadmap_nodes (
80    id uuid NOT NULL,
81    roadmap_id uuid,
82    title character varying NOT NULL,
83    description text,
84    video_links json,
85    order_index integer NOT NULL,
86    parent_node_id uuid,
87    created_at timestamp without time zone
88);
89
90
91ALTER TABLE public.roadmap_nodes OWNER TO postgres;
92
93--
94-- Name: roadmaps; Type: TABLE; Schema: public; Owner: postgres
95--
96
97CREATE TABLE public.roadmaps (
98    id uuid NOT NULL,
99    exam_type character varying NOT NULL,
100    title character varying NOT NULL,
101    description text,
102    created_at timestamp without time zone
103);
104
105
106ALTER TABLE public.roadmaps OWNER TO postgres;
107
108--
109-- Name: schedule_blocks; Type: TABLE; Schema: public; Owner: postgres
110--
111
112CREATE TABLE public.schedule_blocks (
113    id uuid NOT NULL,
114    student_id uuid NOT NULL,
115    block_date date,
116    block_type character varying,
117    title character varying,
118    description text,
119    duration_minutes integer,
120    status character varying,
121    priority character varying,
122    created_at timestamp without time zone
123);
124
125
126ALTER TABLE public.schedule_blocks OWNER TO postgres;
127
128--
129-- Name: speaking_sessions; Type: TABLE; Schema: public; Owner: postgres
130--
131
132CREATE TABLE public.speaking_sessions (
133    id uuid NOT NULL,
134    user_id uuid NOT NULL,
135    duration_seconds double precision,
136    speech_rate_wpm double precision,
137    average_pause double precision,
138    long_pauses integer,
139    filler_density double precision,
140    pitch_mean double precision,
141    pitch_variance double precision,
142    vocab_diversity double precision,
143    complex_sentence_ratio double precision,
144    feedback jsonb,
145    created_at timestamp without time zone
146);
147
148
149ALTER TABLE public.speaking_sessions OWNER TO postgres;
150
151--
152-- Name: student_profiles; Type: TABLE; Schema: public; Owner: postgres
153--
154
155CREATE TABLE public.student_profiles (
156    id uuid NOT NULL,
157    user_id uuid NOT NULL,
158    institution_name character varying,
159    branch character varying,
160    current_year integer,
161    cgpa numeric(4,2),
162    cgpa_scale numeric(3,1),
163    monthly_family_income integer,
164    savings_amount integer,
165    willing_for_loan boolean,
166    career_goal text,
167    preferred_intake character varying,
168    created_at timestamp without time zone,
169    updated_at timestamp without time zone
170);
171
172
173ALTER TABLE public.student_profiles OWNER TO postgres;
174
175--
176-- Name: target_preferences; Type: TABLE; Schema: public; Owner: postgres
177--
178
179CREATE TABLE public.target_preferences (
180    id uuid NOT NULL,
181    student_id uuid NOT NULL,
182    country character varying,
183    degree_level character varying,
184    intake_year integer
185);
186
187
188ALTER TABLE public.target_preferences OWNER TO postgres;
189
190--
191-- Name: test_diagnostics; Type: TABLE; Schema: public; Owner: postgres
192--
193
194CREATE TABLE public.test_diagnostics (
195    id uuid NOT NULL,
196    student_id uuid NOT NULL,
197    test_type character varying,
198    reading_score double precision,
199    writing_score double precision,
200    speaking_score double precision,
201    reading_data json,
202    writing_data json,
203    speaking_data json,
204    status character varying,
205    created_at timestamp without time zone
206);
207
208
209ALTER TABLE public.test_diagnostics OWNER TO postgres;
210
211--
212-- Name: test_skill_profiles; Type: TABLE; Schema: public; Owner: postgres
213--
214
215CREATE TABLE public.test_skill_profiles (
216    id uuid NOT NULL,
217    student_id uuid NOT NULL,
218    test_type character varying,
219    reading_score double precision,
220    writing_score double precision,
221    speaking_score double precision,
222    listening_score double precision,
223    target_score double precision,
224    created_at timestamp without time zone
225);
226
227
228ALTER TABLE public.test_skill_profiles OWNER TO postgres;
229
230--
231-- Name: user_roadmap_progress; Type: TABLE; Schema: public; Owner: postgres
232--
233
234CREATE TABLE public.user_roadmap_progress (
235    id uuid NOT NULL,
236    user_id uuid,
237    roadmap_id uuid,
238    node_id uuid,
239    status character varying,
240    completed_at timestamp without time zone
241);
242
243
244ALTER TABLE public.user_roadmap_progress OWNER TO postgres;
245
246--
247-- Name: users; Type: TABLE; Schema: public; Owner: postgres
248--
249
250CREATE TABLE public.users (
251    id uuid NOT NULL,
252    email character varying NOT NULL,
253    password_hash character varying NOT NULL,
254    role character varying,
255    created_at timestamp without time zone
256);
257
258
259ALTER TABLE public.users OWNER TO postgres;
260
261--
262-- Name: weekly_digests; Type: TABLE; Schema: public; Owner: postgres
263--
264
265CREATE TABLE public.weekly_digests (
266    id uuid NOT NULL,
267    student_id uuid NOT NULL,
268    week_start date NOT NULL,
269    insights json,
270    motivational_line character varying,
271    created_at timestamp without time zone
272);
273
274
275ALTER TABLE public.weekly_digests OWNER TO postgres;
276
277--
278-- Name: writing_questions; Type: TABLE; Schema: public; Owner: postgres
279--
280
281CREATE TABLE public.writing_questions (
282    id uuid NOT NULL,
283    task_type character varying,
284    difficulty character varying,
285    prompt_text text,
286    is_active boolean
287);
288
289
290ALTER TABLE public.writing_questions OWNER TO postgres;
291
292--
293-- Name: writing_submissions; Type: TABLE; Schema: public; Owner: postgres
294--
295
296CREATE TABLE public.writing_submissions (
297    id uuid NOT NULL,
298    student_id uuid NOT NULL,
299    task_type character varying,
300    prompt text,
301    response_text text,
302    band_score double precision,
303    feedback_json json,
304    created_at timestamp without time zone
305);
306
307
308ALTER TABLE public.writing_submissions OWNER TO postgres;
309
310--
311-- Name: application_milestones application_milestones_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
312--
313
314ALTER TABLE ONLY public.application_milestones
315    ADD CONSTRAINT application_milestones_pkey PRIMARY KEY (id);
316
317
318--
319-- Name: profile_intake_logs profile_intake_logs_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
320--
321
322ALTER TABLE ONLY public.profile_intake_logs
323    ADD CONSTRAINT profile_intake_logs_pkey PRIMARY KEY (id);
324
325
326--
327-- Name: roadmap_nodes roadmap_nodes_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
328--
329
330ALTER TABLE ONLY public.roadmap_nodes
331    ADD CONSTRAINT roadmap_nodes_pkey PRIMARY KEY (id);
332
333
334--
335-- Name: roadmaps roadmaps_exam_type_key; Type: CONSTRAINT; Schema: public; Owner: postgres
336--
337
338ALTER TABLE ONLY public.roadmaps
339    ADD CONSTRAINT roadmaps_exam_type_key UNIQUE (exam_type);
340
341
342--
343-- Name: roadmaps roadmaps_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
344--
345
346ALTER TABLE ONLY public.roadmaps
347    ADD CONSTRAINT roadmaps_pkey PRIMARY KEY (id);
348
349
350--
351-- Name: schedule_blocks schedule_blocks_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
352--
353
354ALTER TABLE ONLY public.schedule_blocks
355    ADD CONSTRAINT schedule_blocks_pkey PRIMARY KEY (id);
356
357
358--
359-- Name: speaking_sessions speaking_sessions_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
360--
361
362ALTER TABLE ONLY public.speaking_sessions
363    ADD CONSTRAINT speaking_sessions_pkey PRIMARY KEY (id);
364
365
366--
367-- Name: student_profiles student_profiles_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
368--
369
370ALTER TABLE ONLY public.student_profiles
371    ADD CONSTRAINT student_profiles_pkey PRIMARY KEY (id);
372
373
374--
375-- Name: student_profiles student_profiles_user_id_key; Type: CONSTRAINT; Schema: public; Owner: postgres
376--
377
378ALTER TABLE ONLY public.student_profiles
379    ADD CONSTRAINT student_profiles_user_id_key UNIQUE (user_id);
380
381
382--
383-- Name: target_preferences target_preferences_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
384--
385
386ALTER TABLE ONLY public.target_preferences
387    ADD CONSTRAINT target_preferences_pkey PRIMARY KEY (id);
388
389
390--
391-- Name: test_diagnostics test_diagnostics_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
392--
393
394ALTER TABLE ONLY public.test_diagnostics
395    ADD CONSTRAINT test_diagnostics_pkey PRIMARY KEY (id);
396
397
398--
399-- Name: test_skill_profiles test_skill_profiles_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
400--
401
402ALTER TABLE ONLY public.test_skill_profiles
403    ADD CONSTRAINT test_skill_profiles_pkey PRIMARY KEY (id);
404
405
406--
407-- Name: user_roadmap_progress unique_user_node; Type: CONSTRAINT; Schema: public; Owner: postgres
408--
409
410ALTER TABLE ONLY public.user_roadmap_progress
411    ADD CONSTRAINT unique_user_node UNIQUE (user_id, node_id);
412
413
414--
415-- Name: user_roadmap_progress user_roadmap_progress_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
416--
417
418ALTER TABLE ONLY public.user_roadmap_progress
419    ADD CONSTRAINT user_roadmap_progress_pkey PRIMARY KEY (id);
420
421
422--
423-- Name: users users_email_key; Type: CONSTRAINT; Schema: public; Owner: postgres
424--
425
426ALTER TABLE ONLY public.users
427    ADD CONSTRAINT users_email_key UNIQUE (email);
428
429
430--
431-- Name: users users_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
432--
433
434ALTER TABLE ONLY public.users
435    ADD CONSTRAINT users_pkey PRIMARY KEY (id);
436
437
438--
439-- Name: weekly_digests weekly_digests_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
440--
441
442ALTER TABLE ONLY public.weekly_digests
443    ADD CONSTRAINT weekly_digests_pkey PRIMARY KEY (id);
444
445
446--
447-- Name: writing_questions writing_questions_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
448--
449
450ALTER TABLE ONLY public.writing_questions
451    ADD CONSTRAINT writing_questions_pkey PRIMARY KEY (id);
452
453
454--
455-- Name: writing_submissions writing_submissions_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
456--
457
458ALTER TABLE ONLY public.writing_submissions
459    ADD CONSTRAINT writing_submissions_pkey PRIMARY KEY (id);
460
461
462--
463-- Name: application_milestones application_milestones_student_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
464--
465
466ALTER TABLE ONLY public.application_milestones
467    ADD CONSTRAINT application_milestones_student_id_fkey FOREIGN KEY (student_id) REFERENCES public.student_profiles(id) ON DELETE CASCADE;
468
469
470--
471-- Name: profile_intake_logs profile_intake_logs_student_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
472--
473
474ALTER TABLE ONLY public.profile_intake_logs
475    ADD CONSTRAINT profile_intake_logs_student_id_fkey FOREIGN KEY (student_id) REFERENCES public.student_profiles(id) ON DELETE CASCADE;
476
477
478--
479-- Name: roadmap_nodes roadmap_nodes_parent_node_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
480--
481
482ALTER TABLE ONLY public.roadmap_nodes
483    ADD CONSTRAINT roadmap_nodes_parent_node_id_fkey FOREIGN KEY (parent_node_id) REFERENCES public.roadmap_nodes(id);
484
485
486--
487-- Name: roadmap_nodes roadmap_nodes_roadmap_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
488--
489
490ALTER TABLE ONLY public.roadmap_nodes
491    ADD CONSTRAINT roadmap_nodes_roadmap_id_fkey FOREIGN KEY (roadmap_id) REFERENCES public.roadmaps(id) ON DELETE CASCADE;
492
493
494--
495-- Name: schedule_blocks schedule_blocks_student_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
496--
497
498ALTER TABLE ONLY public.schedule_blocks
499    ADD CONSTRAINT schedule_blocks_student_id_fkey FOREIGN KEY (student_id) REFERENCES public.student_profiles(id) ON DELETE CASCADE;
500
501
502--
503-- Name: speaking_sessions speaking_sessions_user_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
504--
505
506ALTER TABLE ONLY public.speaking_sessions
507    ADD CONSTRAINT speaking_sessions_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.users(id) ON DELETE CASCADE;
508
509
510--
511-- Name: student_profiles student_profiles_user_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
512--
513
514ALTER TABLE ONLY public.student_profiles
515    ADD CONSTRAINT student_profiles_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.users(id) ON DELETE CASCADE;
516
517
518--
519-- Name: target_preferences target_preferences_student_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
520--
521
522ALTER TABLE ONLY public.target_preferences
523    ADD CONSTRAINT target_preferences_student_id_fkey FOREIGN KEY (student_id) REFERENCES public.student_profiles(id) ON DELETE CASCADE;
524
525
526--
527-- Name: test_diagnostics test_diagnostics_student_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
528--
529
530ALTER TABLE ONLY public.test_diagnostics
531    ADD CONSTRAINT test_diagnostics_student_id_fkey FOREIGN KEY (student_id) REFERENCES public.student_profiles(id) ON DELETE CASCADE;
532
533
534--
535-- Name: test_skill_profiles test_skill_profiles_student_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
536--
537
538ALTER TABLE ONLY public.test_skill_profiles
539    ADD CONSTRAINT test_skill_profiles_student_id_fkey FOREIGN KEY (student_id) REFERENCES public.student_profiles(id) ON DELETE CASCADE;
540
541
542--
543-- Name: user_roadmap_progress user_roadmap_progress_node_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
544--
545
546ALTER TABLE ONLY public.user_roadmap_progress
547    ADD CONSTRAINT user_roadmap_progress_node_id_fkey FOREIGN KEY (node_id) REFERENCES public.roadmap_nodes(id) ON DELETE CASCADE;
548
549
550--
551-- Name: user_roadmap_progress user_roadmap_progress_roadmap_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
552--
553
554ALTER TABLE ONLY public.user_roadmap_progress
555    ADD CONSTRAINT user_roadmap_progress_roadmap_id_fkey FOREIGN KEY (roadmap_id) REFERENCES public.roadmaps(id) ON DELETE CASCADE;
556
557
558--
559-- Name: user_roadmap_progress user_roadmap_progress_user_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
560--
561
562ALTER TABLE ONLY public.user_roadmap_progress
563    ADD CONSTRAINT user_roadmap_progress_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.users(id) ON DELETE CASCADE;
564
565
566--
567-- Name: weekly_digests weekly_digests_student_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
568--
569
570ALTER TABLE ONLY public.weekly_digests
571    ADD CONSTRAINT weekly_digests_student_id_fkey FOREIGN KEY (student_id) REFERENCES public.student_profiles(id) ON DELETE CASCADE;
572
573
574--
575-- Name: writing_submissions writing_submissions_student_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
576--
577
578ALTER TABLE ONLY public.writing_submissions
579    ADD CONSTRAINT writing_submissions_student_id_fkey FOREIGN KEY (student_id) REFERENCES public.student_profiles(id) ON DELETE CASCADE;
580
581
582--
583-- Name: SCHEMA public; Type: ACL; Schema: -; Owner: postgres
584--
585
586REVOKE USAGE ON SCHEMA public FROM PUBLIC;
587
588
589--
590-- PostgreSQL database dump complete
591--
592
593\unrestrict ORiQeFrDKXSwNyG1XMOjkEIEHHIqokFhqRIgFoOo7oSboleXUEegCTvFX1Rfhb7
594
595