Base table schema

1 files changed, 119 insertions, 0 deletions

diff --git a/src/db/migrations/2024102303_0.1.0_tables.up.sql b/src/db/migrations/2024102303_0.1.0_tables.up.sql
new file mode 100644
index 0000000..428c3c2
--- /dev/null
+++ b/src/db/migrations/2024102303_0.1.0_tables.up.sql
@@ -0,0 +1,119 @@
+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 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."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)
+);