Spaces:
Sleeping
Sleeping
| create extension if not exists pgcrypto; | |
| create table if not exists public.app_users ( | |
| id uuid primary key default gen_random_uuid(), | |
| username text not null unique, | |
| password_hash text not null, | |
| created_at timestamptz not null default now() | |
| ); | |
| create table if not exists public.app_sessions ( | |
| id uuid primary key default gen_random_uuid(), | |
| user_id uuid not null references public.app_users(id) on delete cascade, | |
| token_hash text not null unique, | |
| expires_at timestamptz not null, | |
| created_at timestamptz not null default now() | |
| ); | |
| update public.scores | |
| set created_at = now() | |
| where created_at is null; | |
| alter table public.scores | |
| alter column id set default gen_random_uuid(); | |
| alter table public.scores | |
| alter column created_at set default now(); | |
| alter table public.scores | |
| alter column created_at set not null; | |
| alter table public.scores | |
| alter column user_id drop default; | |
| alter table public.scores | |
| drop constraint if exists scores_user_id_fkey; | |
| alter table public.scores | |
| add constraint scores_user_id_fkey | |
| foreign key (user_id) references public.app_users(id) on delete cascade; | |
| create unique index if not exists scores_user_slug_idx | |
| on public.scores (user_id, slug); | |
| create table if not exists public.score_artifacts ( | |
| id uuid primary key default gen_random_uuid(), | |
| score_id uuid not null references public.scores(id) on delete cascade, | |
| user_id uuid not null references public.app_users(id) on delete cascade, | |
| artifact_type text not null, | |
| storage_provider text not null, | |
| object_key text not null, | |
| content_type text not null, | |
| size_bytes bigint not null default 0, | |
| created_at timestamptz not null default now(), | |
| updated_at timestamptz not null default now() | |
| ); | |
| create unique index if not exists score_artifacts_score_user_type_idx | |
| on public.score_artifacts (score_id, user_id, artifact_type); | |
| create index if not exists score_artifacts_user_score_idx | |
| on public.score_artifacts (user_id, score_id); | |