kacaii.dev
wip: currently rewriting the project as a full stack application
tangled.org/kacaii.dev/sigo
gleam
1begin;
2
3-- TYPES --------------------------------------------------------------------
4
5create type public.user_role_enum as enum (
6 'admin',
7 'analyst',
8 'firefighter',
9 'captain',
10 'developer',
11 'sargeant'
12);
13
14create type public.notification_type_enum as enum (
15 'fire',
16 'emergency',
17 'traffic',
18 'other'
19);
20
21create type public.occurrence_category_enum as enum (
22 'medic_emergency',
23 'fire',
24 'traffic_accident',
25 'other'
26);
27
28create type public.occurrence_subcategory_enum as enum (
29 -- Medic Emergency,
30 'heart_stop',
31 'pre_hospital_care',
32 'seizure',
33 'serious_injury',
34 'intoxication',
35
36 -- Fire
37 'residential',
38 'comercial',
39 'vegetation',
40 'vehicle',
41
42 -- Traffic Accident
43 'collision',
44 'run_over',
45 'rollover',
46 'motorcycle_crash',
47
48 -- Other
49 'tree_crash',
50 'flood',
51 'injured_animal'
52);
53
54create type occurrence_priority_enum as enum (
55 'low',
56 'medium',
57 'high'
58);
59
60-- TABLES -------------------------------------------------------------------
61
62create table if not exists public.user_account (
63 id uuid default uuidv7(),
64 user_role user_role_enum not null,
65 full_name text not null,
66 password_hash text not null,
67 registration text unique not null,
68 phone text unique default null,
69 email text not null unique,
70 is_active boolean not null default true,
71 created_at timestamp not null default current_timestamp,
72 updated_at timestamp not null default current_timestamp,
73 primary key (id)
74);
75
76create index if not exists idx_user_registration
77on public.user_account (registration);
78
79
80create table if not exists public.user_notification_preference (
81 id uuid default uuidv7(),
82 user_id uuid not null references public.user_account (id)
83 on update cascade on delete cascade,
84 notification_type notification_type_enum not null,
85 enabled boolean not null default false,
86 created_at timestamp not null default current_timestamp,
87 updated_at timestamp not null default current_timestamp,
88 unique (user_id, notification_type),
89 primary key (id)
90);
91
92
93create table if not exists public.brigade (
94 id uuid default uuidv7(),
95 leader_id uuid not null references public.user_account (id)
96 on update cascade on delete cascade,
97 vehicle_code text not null,
98 brigade_name text not null,
99 description text default null,
100 is_active boolean not null default false,
101 created_at timestamp not null default current_timestamp,
102 updated_at timestamp not null default current_timestamp,
103 primary key (id)
104);
105
106create index if not exists idx_brigade_leader_id
107on public.brigade (leader_id);
108
109
110create table if not exists public.brigade_membership (
111 id uuid default uuidv7(),
112 brigade_id uuid not null references public.brigade (id)
113 on update cascade on delete cascade,
114 user_id uuid not null references public.user_account (id)
115 on update cascade on delete cascade,
116 unique (user_id, brigade_id),
117 primary key (id)
118);
119
120create index if not exists idx_brigade_membership_user_id
121on public.brigade_membership (user_id);
122
123create index if not exists idx_brigade_membership_brigade_id
124on public.brigade_membership (brigade_id);
125
126
127create table if not exists public.occurrence (
128 id uuid default uuidv7(),
129 applicant_id uuid not null references public.user_account (id)
130 on update cascade on delete cascade,
131 occurrence_category occurrence_category_enum not null,
132 occurrence_subcategory occurrence_subcategory_enum,
133 priority occurrence_priority_enum not null,
134 description text,
135 occurrence_location float [],
136 reference_point text,
137 created_at timestamp not null default current_timestamp,
138 arrived_at timestamp default null,
139 updated_at timestamp not null default current_timestamp,
140 resolved_at timestamp default null,
141 primary key (id)
142);
143
144create index if not exists idx_occurrence_applicant_id
145on public.occurrence (applicant_id);
146
147create index if not exists idx_occurrence_created_at
148on public.occurrence (created_at);
149
150
151create table if not exists public.occurrence_brigade (
152 id uuid default uuidv7(),
153 occurrence_id uuid not null references public.occurrence (id)
154 on update cascade on delete cascade,
155 brigade_id uuid not null references public.brigade (id)
156 on update cascade on delete cascade,
157 unique (occurrence_id, brigade_id),
158 primary key (id)
159);
160
161create index if not exists idx_occurrence_brigade_occurrence_id
162on public.occurrence_brigade (occurrence_id);
163
164create index if not exists idx_occurrence_brigade_brigade_id
165on public.occurrence_brigade (brigade_id);
166
167commit;