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
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
|
create type phtx.image_data_image_type as enum ('full', 'preview');
create type phtx.image_data_storage_type as enum ('local');
create type phtx.image_data_file_format as enum ('jpeg', 'jxl', 'x-canon-cr2');
create type phtx.image_field_type as enum ('text', 'integer', 'float', 'enumerated');
create type phtx.token_invalidation_reason as enum ('user', 'reuse_attempt');
create type phtx.token_scope as enum (
-- Unsure if we want to do more than this later
'photos:read',
'photos:write',
'photos:upload',
'photos:delete',
'photos:share',
'albums:read',
'albums:write',
'albums:delete',
'albums:share',
'user:profile:read',
'user:profile:write',
'user:account:read',
'user:account:write',
'user:account:delete',
'user:rules:read',
'user:rules:write',
'user:rules:delete',
'admin:users:read',
'admin:users:write',
'admin:users:delete',
'auth:clients:read',
'auth:clients:register',
'auth:clients:remove',
'auth:refresh'
);
create table phtx."user" (
user_uuid uuid primary key default phtx_ext.uuid_generate_v4(),
user_email phtx_ext.citext unique not null,
user_username phtx_ext.citext unique not null,
user_display_name text,
user_password_hash text,
user_time_created timestamptz not null default now(),
user_time_password_changed timestamptz,
user_time_email_confirmed timestamptz
);
create table phtx."user_token" (
user_token_uuid uuid primary key default phtx_ext.uuid_generate_v4(),
user_token_hash text not null,
user_token_issued timestamptz not null default now(),
user_token_expires timestamptz not null default now() + '1 hour',
user_token_invalidated timestamptz null default null,
user_token_invalidation_reason phtx.token_invalidation_reason null default null,
user_token_scopes phtx.token_scope[] not null default array[]::phtx.token_scope[],
constraint user_token_invalidation_needs_reason check (
(user_token_invalidated is null and user_token_invalidation_reason is null)
or (user_token_invalidated is not null and user_token_invalidation_reason is not null)
),
constraint refresh_token_no_other_scopes check (
(array_position(user_token_scopes, 'auth:refresh') is null)
or (array_length(user_token_scopes, 0) = 1)
)
);
create table phtx."login_session" (
login_session_uuid uuid primary key default phtx_ext.uuid_generate_v4(),
login_session_time_created timestamptz not null default now(),
login_session_time_last_active timestamptz not null default now(),
login_session_duration interval not null,
login_session_time_logged_out timestamptz null,
login_session_ip_address varchar(50) not null,
login_session_user_agent varchar(500) not null,
login_session_referer varchar(500) not null,
login_session_user_uuid uuid not null references phtx."user" (user_uuid)
);
create table phtx."image" (
image_uuid uuid primary key default phtx_ext.uuid_generate_v4(),
image_original_filename text not null,
image_title text,
image_description text,
image_owner uuid not null references phtx."user" (user_uuid)
);
create table phtx."image_data" (
image_data_uuid uuid primary key default phtx_ext.uuid_generate_v4(),
image_data_image uuid not null references phtx."image" (image_uuid),
image_data_storage_type phtx.image_data_storage_type not null,
image_data_image_type phtx.image_data_image_type not null,
image_data_dimensions_width integer not null,
image_data_dimensions_height integer not null,
image_data_file_format phtx.image_data_file_format not null,
-- Compound key to constrain specific storage types
unique (image_data_uuid, image_data_storage_type)
);
create unique index "only_one_full_image" on phtx."image_data" (image_data_image)
where (image_data_image_type = 'full');
create table phtx."image_data_local" (
image_data_uuid uuid primary key default phtx_ext.uuid_generate_v4(),
image_data_storage_type phtx.image_data_storage_type check ( image_data_storage_type = 'local' ),
image_data_file_path text not null,
image_data_file_sha256 varchar(64),
foreign key (image_data_uuid, image_data_storage_type) references phtx."image_data" (image_data_uuid, image_data_storage_type)
);
create table phtx."field" (
field_uuid uuid primary key default phtx_ext.uuid_generate_v4(),
field_type phtx.image_field_type not null,
field_user uuid references phtx."user" (user_uuid),
-- Compound key to constrain image field types
unique (field_uuid, field_type)
);
create table phtx."image_field" (
image_field_uuid uuid primary key default phtx_ext.uuid_generate_v4(),
image_field_type phtx.image_field_type not null,
image_field_image uuid not null references phtx."image" (image_uuid),
image_field_field uuid not null references phtx."field" (field_uuid),
-- Constrain an image-field record so its type matches the referenced field definition
foreign key (image_field_field, image_field_type) references phtx."field" (field_uuid, field_type),
-- Compound key to constrain value types
unique (image_field_uuid, image_field_type)
);
create table phtx."image_field_text" (
image_field_uuid uuid primary key default phtx_ext.uuid_generate_v4(),
image_field_type phtx.image_field_type not null check (image_field_type = 'text'),
image_field_value text not null,
foreign key (image_field_uuid, image_field_type) references phtx."image_field" (image_field_uuid, image_field_type)
);
create table phtx."image_field_integer" (
image_field_uuid uuid primary key default phtx_ext.uuid_generate_v4(),
image_field_type phtx.image_field_type not null check (image_field_type = 'integer'),
image_field_value bigint not null,
foreign key (image_field_uuid, image_field_type) references phtx."image_field" (image_field_uuid, image_field_type)
);
create table phtx."image_field_float" (
image_field_uuid uuid primary key default phtx_ext.uuid_generate_v4(),
image_field_type phtx.image_field_type not null check (image_field_type = 'float'),
image_field_value double precision not null,
foreign key (image_field_uuid, image_field_type) references phtx."image_field" (image_field_uuid, image_field_type)
);
create table phtx."image_field_enumerated" (
image_field_uuid uuid primary key default phtx_ext.uuid_generate_v4(),
image_field_type phtx.image_field_type not null check (image_field_type = 'integer'),
image_field_value text not null,
foreign key (image_field_uuid, image_field_type) references phtx."image_field" (image_field_uuid, image_field_type)
);
|
