diff --git a/src/db/migrations/2024102303_0.1.0_tables.down.sql b/src/db/migrations/2024102303_0.1.0_tables.down.sql
index 0cfc411..3d13777 100644
--- a/src/db/migrations/2024102303_0.1.0_tables.down.sql
+++ b/src/db/migrations/2024102303_0.1.0_tables.down.sql
@@ -9,8 +9,11 @@ drop table phtx."image_data";
drop table phtx."image";
drop table phtx."login_session";
drop table phtx."user";
+drop table phtx."user_token";
drop type phtx.image_data_image_type;
drop type phtx.image_data_storage_type;
drop type phtx.image_data_file_format;
drop type phtx.image_field_type;
+drop type phtx.token_invalidation_reason;
+drop type phtx.token_scope;
diff --git a/src/db/migrations/2024102303_0.1.0_tables.up.sql b/src/db/migrations/2024102303_0.1.0_tables.up.sql
index 428c3c2..faa99e3 100644
--- a/src/db/migrations/2024102303_0.1.0_tables.up.sql
+++ b/src/db/migrations/2024102303_0.1.0_tables.up.sql
@@ -2,6 +2,34 @@ 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(),
@@ -14,6 +42,27 @@ create table phtx."user" (
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(),
|
