this repo has no description
1-- EXTENSIONS ------------------------------------------------------------------
2create extension pg_trgm;
3
4-- TYPES -----------------------------------------------------------------------
5create type user_role_enum as enum (
6 'admin',
7 'guest',
8 'professor',
9 'student',
10 'cleaning',
11 'developer'
12);
13
14-- TABLES ----------------------------------------------------------------------
15
16create table user_account (
17 id uuid default uuidv7(),
18 user_role user_role_enum not null,
19 full_name text not null,
20 password_hash text not null,
21 phone text unique not null,
22 email text unique not null check (email like '%@%'),
23 is_active boolean not null default true,
24
25 created_at timestamp not null default current_timestamp,
26 updated_at timestamp not null default current_timestamp,
27
28 primary key (id),
29 check (updated_at >= created_at)
30);
31
32create index idx_user_account_search_name
33on user_account using gin (full_name gin_trgm_ops);
34
35
36create table access_area (
37 id uuid default uuidv7(),
38 label text not null,
39
40 primary key (id)
41);
42
43
44create table access_card (
45 id uuid default uuidv7(),
46 label text not null,
47
48 created_at timestamp not null default current_timestamp,
49 updated_at timestamp not null default current_timestamp
50 check (updated_at >= created_at),
51
52 primary key (id)
53);
54
55
56create table access_card_ownership (
57 id uuid default uuidv7(),
58
59 card_id uuid not null references access_card (id)
60 on update cascade on delete cascade,
61
62 user_id uuid not null references user_account (id)
63 on update cascade on delete cascade,
64
65 primary key (id),
66 unique (card_id, user_id)
67);
68
69
70create table card_usage (
71 id uuid default uuidv7(),
72
73 card_id uuid not null references access_card (id)
74 on update cascade on delete cascade,
75
76 user_id uuid not null references user_account (id)
77 on update cascade on delete cascade,
78
79 access_area_id uuid not null references access_area (id)
80 on update cascade on delete cascade,
81
82 usage_timestamp timestamp not null
83 default current_timestamp,
84
85 primary key (id)
86);
87
88create index idx_card_usage_card_id
89on card_usage (card_id);
90
91create index idx_card_usage_user_id
92on card_usage (user_id);
93
94create index idx_card_usage_area_id
95on card_usage (access_area_id);
96
97create index idx_card_usage_timestamp
98on card_usage (usage_timestamp);
99
100
101create table item (
102 id uuid default uuidv7(),
103 label text not null,
104
105 primary key (id)
106);
107
108create index idx_item_search_label
109on item using gin (label gin_trgm_ops);