api token schema HEAD main

1 files changed, 49 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
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(),