hf_space_dashboard_schema.sql

-- Draft schema for HF Space authenticated dashboard ownership.
-- Reuse-first strategy:
--   1. Reuse existing public.generations / public.generation_variants from the Arti QR schema.
--   2. Keep analytics_* tables as telemetry only.
--   3. Keep qrcut public.short_links / public.short_link_scan_events as redirect analytics source of truth.
--
-- Prerequisites expected before applying this file:
--   - public.generations exists (for example from arti-qrcode-app supabase migration 20260403_init_qr_app.sql)
--   - public.generation_variants exists if multi-asset support is desired
--   - qrcut short_links / short_link_scan_events exist in the same Supabase project if per-QR analytics joins should work immediately

create extension if not exists pgcrypto;

create table if not exists public.app_users (
  id uuid primary key default gen_random_uuid(),
  display_name text,
  avatar_url text,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  last_login_at timestamptz
);

create table if not exists public.user_identities (
  id uuid primary key default gen_random_uuid(),
  app_user_id uuid not null references public.app_users(id) on delete cascade,
  provider text not null,
  provider_subject text not null,
  username text,
  email text,
  raw_profile jsonb not null default '{}'::jsonb,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  unique (provider, provider_subject)
);

create index if not exists user_identities_app_user_id_idx
  on public.user_identities(app_user_id);

create index if not exists user_identities_username_idx
  on public.user_identities(username);

alter table public.app_users enable row level security;
alter table public.user_identities enable row level security;

revoke all on table public.app_users from anon, authenticated;
revoke all on table public.user_identities from anon, authenticated;

-- Reuse the existing public.generations table and extend it for dashboard ownership + qrcut linkage.
-- Safer first-pass migration strategy: add app_user_id uuid beside the existing user_id text
-- instead of mutating or dropping the old field immediately.

alter table public.generations
  add column if not exists app_user_id uuid references public.app_users(id) on delete set null,
  add column if not exists input_type text,
  add column if not exists destination_url_normalized text,
  add column if not exists effective_qr_text text,
  add column if not exists shortener_applied boolean not null default false,
  add column if not exists short_link_id uuid,
  add column if not exists short_code text,
  add column if not exists short_url text,
  add column if not exists shortener_expires_at timestamptz,
  add column if not exists completed_image_url text,
  add column if not exists thumbnail_url text,
  add column if not exists metadata jsonb not null default '{}'::jsonb,
  add column if not exists updated_at timestamptz not null default now();

create index if not exists generations_app_user_id_created_at_idx
  on public.generations(app_user_id, created_at desc);

create index if not exists generations_short_link_id_idx
  on public.generations(short_link_id);

create index if not exists generations_short_code_idx
  on public.generations(short_code);

create index if not exists generations_short_url_idx
  on public.generations(short_url);

alter table public.generations enable row level security;
revoke all on table public.generations from anon, authenticated;

-- generation_variants can be reused as-is for multi-file support.
-- Add RLS hardening only if it has not already been added elsewhere.
alter table public.generation_variants enable row level security;
revoke all on table public.generation_variants from anon, authenticated;

-- Optional helper view for owner-scoped dashboard summaries.
-- This keeps the object model rooted in public.generations while exposing a stable surface
-- for list/detail handlers using service-role reads.
create or replace view public.dashboard_generation_summaries as
select
  g.id,
  g.app_user_id,
  g.user_id,
  g.created_at,
  g.updated_at,
  g.status,
  g.input_type,
  g.qr_mode,
  g.prompt,
  g.destination_url as destination_url_original,
  g.destination_url_normalized,
  g.effective_qr_text,
  g.shortener_applied,
  g.short_link_id,
  g.short_code,
  g.short_url,
  g.shortener_expires_at,
  coalesce(g.completed_image_url, gv.image_url) as completed_image_url,
  coalesce(g.thumbnail_url, gv.thumbnail_url) as thumbnail_url,
  g.metadata
from public.generations g
left join lateral (
  select image_url, thumbnail_url
  from public.generation_variants gv
  where gv.generation_id = g.id
  order by gv.variant_index asc
  limit 1
) gv on true;

alter view public.dashboard_generation_summaries set (security_invoker = true);
revoke all on table public.dashboard_generation_summaries from anon, authenticated;

comment on table public.app_users is
  'HF Space dashboard app-level users. Stable internal owner records.';

comment on table public.user_identities is
  'Maps external auth providers such as Hugging Face OAuth subjects to app_users.';

comment on column public.generations.app_user_id is
  'Preferred owner linkage for the HF Space dashboard. Added beside legacy user_id text for gradual migration.';

comment on column public.generations.short_link_id is
  'Preferred join key to qrcut public.short_links.id once qrcut returns the link UUID from POST /shorten.';

comment on view public.dashboard_generation_summaries is
  'Owner-facing generation summary surface for list/detail dashboard handlers.';