onboarding/docs/database/schema.sql at desarrolloca · PepeBeto-code/onboarding · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
ALTER TABLE IF EXISTS public.address_info DROP CONSTRAINT IF EXISTS fk3304tvpyhy75smdyqgtrmaw7a;

ALTER TABLE IF EXISTS public.documents DROP CONSTRAINT IF EXISTS fkkxttj4tp5le2uth212lu49vny;

ALTER TABLE IF EXISTS public.onboarding DROP CONSTRAINT IF EXISTS fkinpyt4ngxlboj8ev3pe2e1cff;

ALTER TABLE IF EXISTS public.personal_info DROP CONSTRAINT IF EXISTS fk2ooyctbfk03w21tuk720ixnqh;

DROP TABLE IF EXISTS public.address_info;

CREATE TABLE IF NOT EXISTS public.address_info
(
    id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 9223372036854775807 CACHE 1 ),
    city character varying(255) COLLATE pg_catalog."default",
    encrypted boolean NOT NULL,
    "number" character varying(255) COLLATE pg_catalog."default",
    postal_code character varying(255) COLLATE pg_catalog."default",
    street character varying(255) COLLATE pg_catalog."default",
    user_id bigint NOT NULL,
    CONSTRAINT address_info_pkey PRIMARY KEY (id),
    CONSTRAINT uk1dufxt09egp8nuyqjh06u4d9p UNIQUE (user_id)
);

DROP TABLE IF EXISTS public.audit_logs;

CREATE TABLE IF NOT EXISTS public.audit_logs
(
    id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 9223372036854775807 CACHE 1 ),
    action character varying(255) COLLATE pg_catalog."default",
    description character varying(255) COLLATE pg_catalog."default",
    ip character varying(255) COLLATE pg_catalog."default",
    "timestamp" timestamp(6) without time zone,
    user_id bigint,
    CONSTRAINT audit_logs_pkey PRIMARY KEY (id)
);

DROP TABLE IF EXISTS public.documents;

CREATE TABLE IF NOT EXISTS public.documents
(
    id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 9223372036854775807 CACHE 1 ),
    doc_type character varying(255) COLLATE pg_catalog."default",
    file_base64 text COLLATE pg_catalog."default",
    file_name character varying(255) COLLATE pg_catalog."default",
    file_type character varying(255) COLLATE pg_catalog."default",
    uploaded_at timestamp(6) without time zone,
    user_id bigint NOT NULL,
    CONSTRAINT documents_pkey PRIMARY KEY (id)
);

DROP TABLE IF EXISTS public.onboarding;

CREATE TABLE IF NOT EXISTS public.onboarding
(
    id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 9223372036854775807 CACHE 1 ),
    created_at timestamp(6) without time zone NOT NULL,
    is_complete boolean NOT NULL,
    step_completed integer NOT NULL,
    updated_at timestamp(6) without time zone,
    user_id bigint NOT NULL,
    CONSTRAINT onboarding_pkey PRIMARY KEY (id),
    CONSTRAINT uksmp3cs28vns28xe5j6t91bvts UNIQUE (user_id)
);

DROP TABLE IF EXISTS public.personal_info;

CREATE TABLE IF NOT EXISTS public.personal_info
(
    id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 9223372036854775807 CACHE 1 ),
    birthday date NOT NULL,
    email character varying(255) COLLATE pg_catalog."default" NOT NULL,
    encrypted boolean NOT NULL,
    phone character varying(255) COLLATE pg_catalog."default" NOT NULL,
    user_id bigint NOT NULL,
    CONSTRAINT personal_info_pkey PRIMARY KEY (id),
    CONSTRAINT ukr55wm9v29gmt5v74lg8dbj84f UNIQUE (user_id)
);

DROP TABLE IF EXISTS public.user_stats;

CREATE TABLE IF NOT EXISTS public.user_stats
(
    id serial NOT NULL,
    total_users integer DEFAULT 0,
    CONSTRAINT user_stats_pkey PRIMARY KEY (id)
);

DROP TABLE IF EXISTS public.users;

CREATE TABLE IF NOT EXISTS public.users
(
    id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 9223372036854775807 CACHE 1 ),
    active boolean NOT NULL,
    password character varying(255) COLLATE pg_catalog."default" NOT NULL,
    role character varying(255) COLLATE pg_catalog."default",
    username character varying(255) COLLATE pg_catalog."default",
    CONSTRAINT users_pkey PRIMARY KEY (id),
    CONSTRAINT ukr43af9ap4edm43mmtq01oddj6 UNIQUE (username)
);

ALTER TABLE IF EXISTS public.address_info
    ADD CONSTRAINT fk3304tvpyhy75smdyqgtrmaw7a FOREIGN KEY (user_id)
    REFERENCES public.users (id) MATCH SIMPLE
    ON UPDATE NO ACTION
    ON DELETE NO ACTION;
CREATE INDEX IF NOT EXISTS uk1dufxt09egp8nuyqjh06u4d9p
    ON public.address_info(user_id);


ALTER TABLE IF EXISTS public.documents
    ADD CONSTRAINT fkkxttj4tp5le2uth212lu49vny FOREIGN KEY (user_id)
    REFERENCES public.users (id) MATCH SIMPLE
    ON UPDATE NO ACTION
    ON DELETE NO ACTION;


ALTER TABLE IF EXISTS public.onboarding
    ADD CONSTRAINT fkinpyt4ngxlboj8ev3pe2e1cff FOREIGN KEY (user_id)
    REFERENCES public.users (id) MATCH SIMPLE
    ON UPDATE NO ACTION
    ON DELETE NO ACTION;
CREATE INDEX IF NOT EXISTS uksmp3cs28vns28xe5j6t91bvts
    ON public.onboarding(user_id);


ALTER TABLE IF EXISTS public.personal_info
    ADD CONSTRAINT fk2ooyctbfk03w21tuk720ixnqh FOREIGN KEY (user_id)
    REFERENCES public.users (id) MATCH SIMPLE
    ON UPDATE NO ACTION
    ON DELETE NO ACTION;
CREATE INDEX IF NOT EXISTS ukr55wm9v29gmt5v74lg8dbj84f
    ON public.personal_info(user_id);