irongit
Sign inRegister
huncholanehuncholane/defi_propertyPublic
defi_property/db/schema.sql
628 lines16 KBSQL
RawDownloadHistory
1\restrict dbmate
2
3-- Dumped from database version 16.9 (Debian 16.9-1.pgdg120+1)
4-- Dumped by pg_dump version 18.1
5
6SET statement_timeout = 0;
7SET lock_timeout = 0;
8SET idle_in_transaction_session_timeout = 0;
9SET transaction_timeout = 0;
10SET client_encoding = 'UTF8';
11SET standard_conforming_strings = on;
12SELECT pg_catalog.set_config('search_path', '', false);
13SET check_function_bodies = false;
14SET xmloption = content;
15SET client_min_messages = warning;
16SET row_security = off;
17
18SET default_tablespace = '';
19
20SET default_table_access_method = heap;
21
22--
23-- Name: blockchain_transactions; Type: TABLE; Schema: public; Owner: -
24--
25
26CREATE TABLE public.blockchain_transactions (
27 id integer NOT NULL,
28 property_id integer,
29 contract_address character varying(42) NOT NULL,
30 tx_hash character varying(66),
31 tx_type character varying(20) NOT NULL,
32 from_address character varying(42),
33 to_address character varying(42),
34 value_wei character varying(78),
35 value_eth numeric(18,8),
36 gas_used integer,
37 block_number integer,
38 contract_state integer DEFAULT 0,
39 status character varying(20) DEFAULT 'pending'::character varying,
40 created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP,
41 CONSTRAINT blockchain_transactions_status_check CHECK (((status)::text = ANY ((ARRAY['pending'::character varying, 'confirmed'::character varying, 'failed'::character varying])::text[]))),
42 CONSTRAINT blockchain_transactions_tx_type_check CHECK (((tx_type)::text = ANY ((ARRAY['deploy'::character varying, 'seller_sign'::character varying, 'buyer_deposit'::character varying, 'realtor_review'::character varying, 'finalize'::character varying, 'withdraw'::character varying])::text[])))
43);
44
45
46--
47-- Name: blockchain_transactions_id_seq; Type: SEQUENCE; Schema: public; Owner: -
48--
49
50CREATE SEQUENCE public.blockchain_transactions_id_seq
51 AS integer
52 START WITH 1
53 INCREMENT BY 1
54 NO MINVALUE
55 NO MAXVALUE
56 CACHE 1;
57
58
59--
60-- Name: blockchain_transactions_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
61--
62
63ALTER SEQUENCE public.blockchain_transactions_id_seq OWNED BY public.blockchain_transactions.id;
64
65
66--
67-- Name: cities; Type: TABLE; Schema: public; Owner: -
68--
69
70CREATE TABLE public.cities (
71 id integer NOT NULL,
72 name character varying(100) NOT NULL,
73 state_id integer,
74 is_active boolean DEFAULT true,
75 created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP
76);
77
78
79--
80-- Name: cities_id_seq; Type: SEQUENCE; Schema: public; Owner: -
81--
82
83CREATE SEQUENCE public.cities_id_seq
84 AS integer
85 START WITH 1
86 INCREMENT BY 1
87 NO MINVALUE
88 NO MAXVALUE
89 CACHE 1;
90
91
92--
93-- Name: cities_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
94--
95
96ALTER SEQUENCE public.cities_id_seq OWNED BY public.cities.id;
97
98
99--
100-- Name: properties; Type: TABLE; Schema: public; Owner: -
101--
102
103CREATE TABLE public.properties (
104 id integer NOT NULL,
105 title character varying(255) NOT NULL,
106 property_for character varying(10) DEFAULT 'sell'::character varying NOT NULL,
107 description text,
108 type_id integer,
109 state_id integer,
110 city_id integer,
111 locality character varying(255) NOT NULL,
112 length numeric(10,2) NOT NULL,
113 breadth numeric(10,2) NOT NULL,
114 corner_plot boolean DEFAULT false,
115 is_society boolean DEFAULT false,
116 society_name character varying(255),
117 flat_no character varying(50),
118 address text NOT NULL,
119 email character varying(255) NOT NULL,
120 price numeric(18,2),
121 price_eth numeric(18,8),
122 phone_no character varying(20) NOT NULL,
123 pincode character varying(10) NOT NULL,
124 user_id integer,
125 status character varying(20) DEFAULT 'available'::character varying,
126 is_active boolean DEFAULT true,
127 slug character varying(255) NOT NULL,
128 images text[],
129 img_path character varying(500),
130 contract_address character varying(42),
131 token_id character varying(100),
132 roi_percentage numeric(5,2),
133 total_tokens integer,
134 available_tokens integer,
135 min_investment numeric(18,2),
136 created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP,
137 updated_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP,
138 CONSTRAINT properties_property_for_check CHECK (((property_for)::text = ANY ((ARRAY['sell'::character varying, 'rent'::character varying])::text[]))),
139 CONSTRAINT properties_status_check CHECK (((status)::text = ANY ((ARRAY['available'::character varying, 'sold'::character varying, 'rented'::character varying, 'expired'::character varying])::text[])))
140);
141
142
143--
144-- Name: properties_id_seq; Type: SEQUENCE; Schema: public; Owner: -
145--
146
147CREATE SEQUENCE public.properties_id_seq
148 AS integer
149 START WITH 1
150 INCREMENT BY 1
151 NO MINVALUE
152 NO MAXVALUE
153 CACHE 1;
154
155
156--
157-- Name: properties_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
158--
159
160ALTER SEQUENCE public.properties_id_seq OWNED BY public.properties.id;
161
162
163--
164-- Name: property_investors; Type: TABLE; Schema: public; Owner: -
165--
166
167CREATE TABLE public.property_investors (
168 id integer NOT NULL,
169 property_id integer,
170 user_id integer,
171 wallet_address character varying(42) NOT NULL,
172 tokens_owned integer DEFAULT 0 NOT NULL,
173 investment_amount numeric(18,2),
174 investment_eth numeric(18,8),
175 tx_hash character varying(66),
176 created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP,
177 updated_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP
178);
179
180
181--
182-- Name: property_investors_id_seq; Type: SEQUENCE; Schema: public; Owner: -
183--
184
185CREATE SEQUENCE public.property_investors_id_seq
186 AS integer
187 START WITH 1
188 INCREMENT BY 1
189 NO MINVALUE
190 NO MAXVALUE
191 CACHE 1;
192
193
194--
195-- Name: property_investors_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
196--
197
198ALTER SEQUENCE public.property_investors_id_seq OWNED BY public.property_investors.id;
199
200
201--
202-- Name: property_types; Type: TABLE; Schema: public; Owner: -
203--
204
205CREATE TABLE public.property_types (
206 id integer NOT NULL,
207 title character varying(100),
208 type character varying(20) NOT NULL,
209 is_active boolean DEFAULT true,
210 created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP,
211 updated_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP,
212 CONSTRAINT property_types_type_check CHECK (((type)::text = ANY ((ARRAY['residential'::character varying, 'commercial'::character varying, 'agricultural'::character varying])::text[])))
213);
214
215
216--
217-- Name: property_types_id_seq; Type: SEQUENCE; Schema: public; Owner: -
218--
219
220CREATE SEQUENCE public.property_types_id_seq
221 AS integer
222 START WITH 1
223 INCREMENT BY 1
224 NO MINVALUE
225 NO MAXVALUE
226 CACHE 1;
227
228
229--
230-- Name: property_types_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
231--
232
233ALTER SEQUENCE public.property_types_id_seq OWNED BY public.property_types.id;
234
235
236--
237-- Name: schema_migrations; Type: TABLE; Schema: public; Owner: -
238--
239
240CREATE TABLE public.schema_migrations (
241 version character varying NOT NULL
242);
243
244
245--
246-- Name: states; Type: TABLE; Schema: public; Owner: -
247--
248
249CREATE TABLE public.states (
250 id integer NOT NULL,
251 name character varying(100) NOT NULL,
252 is_active boolean DEFAULT true,
253 created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP
254);
255
256
257--
258-- Name: states_id_seq; Type: SEQUENCE; Schema: public; Owner: -
259--
260
261CREATE SEQUENCE public.states_id_seq
262 AS integer
263 START WITH 1
264 INCREMENT BY 1
265 NO MINVALUE
266 NO MAXVALUE
267 CACHE 1;
268
269
270--
271-- Name: states_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
272--
273
274ALTER SEQUENCE public.states_id_seq OWNED BY public.states.id;
275
276
277--
278-- Name: users; Type: TABLE; Schema: public; Owner: -
279--
280
281CREATE TABLE public.users (
282 id integer NOT NULL,
283 fname character varying(100) NOT NULL,
284 lname character varying(100) NOT NULL,
285 email character varying(255) NOT NULL,
286 phone_no character varying(20) NOT NULL,
287 password character varying(255) NOT NULL,
288 wallet_address character varying(42),
289 state_id integer,
290 city_id integer,
291 pincode character varying(10),
292 user_type integer DEFAULT 1,
293 is_admin boolean DEFAULT false,
294 created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP,
295 updated_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP
296);
297
298
299--
300-- Name: users_id_seq; Type: SEQUENCE; Schema: public; Owner: -
301--
302
303CREATE SEQUENCE public.users_id_seq
304 AS integer
305 START WITH 1
306 INCREMENT BY 1
307 NO MINVALUE
308 NO MAXVALUE
309 CACHE 1;
310
311
312--
313-- Name: users_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
314--
315
316ALTER SEQUENCE public.users_id_seq OWNED BY public.users.id;
317
318
319--
320-- Name: blockchain_transactions id; Type: DEFAULT; Schema: public; Owner: -
321--
322
323ALTER TABLE ONLY public.blockchain_transactions ALTER COLUMN id SET DEFAULT nextval('public.blockchain_transactions_id_seq'::regclass);
324
325
326--
327-- Name: cities id; Type: DEFAULT; Schema: public; Owner: -
328--
329
330ALTER TABLE ONLY public.cities ALTER COLUMN id SET DEFAULT nextval('public.cities_id_seq'::regclass);
331
332
333--
334-- Name: properties id; Type: DEFAULT; Schema: public; Owner: -
335--
336
337ALTER TABLE ONLY public.properties ALTER COLUMN id SET DEFAULT nextval('public.properties_id_seq'::regclass);
338
339
340--
341-- Name: property_investors id; Type: DEFAULT; Schema: public; Owner: -
342--
343
344ALTER TABLE ONLY public.property_investors ALTER COLUMN id SET DEFAULT nextval('public.property_investors_id_seq'::regclass);
345
346
347--
348-- Name: property_types id; Type: DEFAULT; Schema: public; Owner: -
349--
350
351ALTER TABLE ONLY public.property_types ALTER COLUMN id SET DEFAULT nextval('public.property_types_id_seq'::regclass);
352
353
354--
355-- Name: states id; Type: DEFAULT; Schema: public; Owner: -
356--
357
358ALTER TABLE ONLY public.states ALTER COLUMN id SET DEFAULT nextval('public.states_id_seq'::regclass);
359
360
361--
362-- Name: users id; Type: DEFAULT; Schema: public; Owner: -
363--
364
365ALTER TABLE ONLY public.users ALTER COLUMN id SET DEFAULT nextval('public.users_id_seq'::regclass);
366
367
368--
369-- Name: blockchain_transactions blockchain_transactions_pkey; Type: CONSTRAINT; Schema: public; Owner: -
370--
371
372ALTER TABLE ONLY public.blockchain_transactions
373 ADD CONSTRAINT blockchain_transactions_pkey PRIMARY KEY (id);
374
375
376--
377-- Name: cities cities_name_state_id_key; Type: CONSTRAINT; Schema: public; Owner: -
378--
379
380ALTER TABLE ONLY public.cities
381 ADD CONSTRAINT cities_name_state_id_key UNIQUE (name, state_id);
382
383
384--
385-- Name: cities cities_pkey; Type: CONSTRAINT; Schema: public; Owner: -
386--
387
388ALTER TABLE ONLY public.cities
389 ADD CONSTRAINT cities_pkey PRIMARY KEY (id);
390
391
392--
393-- Name: properties properties_pkey; Type: CONSTRAINT; Schema: public; Owner: -
394--
395
396ALTER TABLE ONLY public.properties
397 ADD CONSTRAINT properties_pkey PRIMARY KEY (id);
398
399
400--
401-- Name: properties properties_slug_key; Type: CONSTRAINT; Schema: public; Owner: -
402--
403
404ALTER TABLE ONLY public.properties
405 ADD CONSTRAINT properties_slug_key UNIQUE (slug);
406
407
408--
409-- Name: property_investors property_investors_pkey; Type: CONSTRAINT; Schema: public; Owner: -
410--
411
412ALTER TABLE ONLY public.property_investors
413 ADD CONSTRAINT property_investors_pkey PRIMARY KEY (id);
414
415
416--
417-- Name: property_investors property_investors_property_id_user_id_key; Type: CONSTRAINT; Schema: public; Owner: -
418--
419
420ALTER TABLE ONLY public.property_investors
421 ADD CONSTRAINT property_investors_property_id_user_id_key UNIQUE (property_id, user_id);
422
423
424--
425-- Name: property_types property_types_pkey; Type: CONSTRAINT; Schema: public; Owner: -
426--
427
428ALTER TABLE ONLY public.property_types
429 ADD CONSTRAINT property_types_pkey PRIMARY KEY (id);
430
431
432--
433-- Name: schema_migrations schema_migrations_pkey; Type: CONSTRAINT; Schema: public; Owner: -
434--
435
436ALTER TABLE ONLY public.schema_migrations
437 ADD CONSTRAINT schema_migrations_pkey PRIMARY KEY (version);
438
439
440--
441-- Name: states states_name_key; Type: CONSTRAINT; Schema: public; Owner: -
442--
443
444ALTER TABLE ONLY public.states
445 ADD CONSTRAINT states_name_key UNIQUE (name);
446
447
448--
449-- Name: states states_pkey; Type: CONSTRAINT; Schema: public; Owner: -
450--
451
452ALTER TABLE ONLY public.states
453 ADD CONSTRAINT states_pkey PRIMARY KEY (id);
454
455
456--
457-- Name: users users_email_key; Type: CONSTRAINT; Schema: public; Owner: -
458--
459
460ALTER TABLE ONLY public.users
461 ADD CONSTRAINT users_email_key UNIQUE (email);
462
463
464--
465-- Name: users users_phone_no_key; Type: CONSTRAINT; Schema: public; Owner: -
466--
467
468ALTER TABLE ONLY public.users
469 ADD CONSTRAINT users_phone_no_key UNIQUE (phone_no);
470
471
472--
473-- Name: users users_pkey; Type: CONSTRAINT; Schema: public; Owner: -
474--
475
476ALTER TABLE ONLY public.users
477 ADD CONSTRAINT users_pkey PRIMARY KEY (id);
478
479
480--
481-- Name: idx_blockchain_tx_contract; Type: INDEX; Schema: public; Owner: -
482--
483
484CREATE INDEX idx_blockchain_tx_contract ON public.blockchain_transactions USING btree (contract_address);
485
486
487--
488-- Name: idx_blockchain_tx_property; Type: INDEX; Schema: public; Owner: -
489--
490
491CREATE INDEX idx_blockchain_tx_property ON public.blockchain_transactions USING btree (property_id);
492
493
494--
495-- Name: idx_investors_property; Type: INDEX; Schema: public; Owner: -
496--
497
498CREATE INDEX idx_investors_property ON public.property_investors USING btree (property_id);
499
500
501--
502-- Name: idx_investors_wallet; Type: INDEX; Schema: public; Owner: -
503--
504
505CREATE INDEX idx_investors_wallet ON public.property_investors USING btree (wallet_address);
506
507
508--
509-- Name: idx_properties_contract_address; Type: INDEX; Schema: public; Owner: -
510--
511
512CREATE INDEX idx_properties_contract_address ON public.properties USING btree (contract_address);
513
514
515--
516-- Name: idx_properties_slug; Type: INDEX; Schema: public; Owner: -
517--
518
519CREATE INDEX idx_properties_slug ON public.properties USING btree (slug);
520
521
522--
523-- Name: idx_properties_status; Type: INDEX; Schema: public; Owner: -
524--
525
526CREATE INDEX idx_properties_status ON public.properties USING btree (status);
527
528
529--
530-- Name: idx_properties_user_id; Type: INDEX; Schema: public; Owner: -
531--
532
533CREATE INDEX idx_properties_user_id ON public.properties USING btree (user_id);
534
535
536--
537-- Name: blockchain_transactions blockchain_transactions_property_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
538--
539
540ALTER TABLE ONLY public.blockchain_transactions
541 ADD CONSTRAINT blockchain_transactions_property_id_fkey FOREIGN KEY (property_id) REFERENCES public.properties(id) ON DELETE CASCADE;
542
543
544--
545-- Name: cities cities_state_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
546--
547
548ALTER TABLE ONLY public.cities
549 ADD CONSTRAINT cities_state_id_fkey FOREIGN KEY (state_id) REFERENCES public.states(id) ON DELETE CASCADE;
550
551
552--
553-- Name: users fk_users_city; Type: FK CONSTRAINT; Schema: public; Owner: -
554--
555
556ALTER TABLE ONLY public.users
557 ADD CONSTRAINT fk_users_city FOREIGN KEY (city_id) REFERENCES public.cities(id);
558
559
560--
561-- Name: users fk_users_state; Type: FK CONSTRAINT; Schema: public; Owner: -
562--
563
564ALTER TABLE ONLY public.users
565 ADD CONSTRAINT fk_users_state FOREIGN KEY (state_id) REFERENCES public.states(id);
566
567
568--
569-- Name: properties properties_city_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
570--
571
572ALTER TABLE ONLY public.properties
573 ADD CONSTRAINT properties_city_id_fkey FOREIGN KEY (city_id) REFERENCES public.cities(id);
574
575
576--
577-- Name: properties properties_state_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
578--
579
580ALTER TABLE ONLY public.properties
581 ADD CONSTRAINT properties_state_id_fkey FOREIGN KEY (state_id) REFERENCES public.states(id);
582
583
584--
585-- Name: properties properties_type_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
586--
587
588ALTER TABLE ONLY public.properties
589 ADD CONSTRAINT properties_type_id_fkey FOREIGN KEY (type_id) REFERENCES public.property_types(id);
590
591
592--
593-- Name: properties properties_user_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
594--
595
596ALTER TABLE ONLY public.properties
597 ADD CONSTRAINT properties_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.users(id) ON DELETE CASCADE;
598
599
600--
601-- Name: property_investors property_investors_property_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
602--
603
604ALTER TABLE ONLY public.property_investors
605 ADD CONSTRAINT property_investors_property_id_fkey FOREIGN KEY (property_id) REFERENCES public.properties(id) ON DELETE CASCADE;
606
607
608--
609-- Name: property_investors property_investors_user_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
610--
611
612ALTER TABLE ONLY public.property_investors
613 ADD CONSTRAINT property_investors_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.users(id) ON DELETE CASCADE;
614
615
616--
617-- PostgreSQL database dump complete
618--
619
620\unrestrict dbmate
621
622
623--
624-- Dbmate schema migrations
625--
626
627INSERT INTO public.schema_migrations (version) VALUES
628 ('20260128202118');