49 lines
1.7 KiB
SQL
49 lines
1.7 KiB
SQL
-- NCUE user table for admin gating / auditing
|
|
-- Run: psql -h "$PG_DB_HOST" -p "$PG_DB_PORT" -U "$PG_DB_USER" -d "$PG_DB_NAME" -f db/schema.sql
|
|
|
|
DO $$
|
|
BEGIN
|
|
IF NOT EXISTS (
|
|
SELECT 1
|
|
FROM information_schema.tables
|
|
WHERE table_schema = 'public'
|
|
AND table_name = 'ncue_user'
|
|
) THEN
|
|
CREATE TABLE public.ncue_user (
|
|
sub text PRIMARY KEY,
|
|
email text,
|
|
name text,
|
|
picture text,
|
|
provider text,
|
|
first_login_at timestamptz,
|
|
last_login_at timestamptz,
|
|
last_logout_at timestamptz,
|
|
is_admin boolean NOT NULL DEFAULT false,
|
|
can_manage boolean NOT NULL DEFAULT false,
|
|
created_at timestamptz NOT NULL DEFAULT now(),
|
|
updated_at timestamptz NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE INDEX idx_ncue_user_email ON public.ncue_user (email);
|
|
END IF;
|
|
END $$;
|
|
|
|
-- Backward-compatible migration (if table already exists)
|
|
ALTER TABLE public.ncue_user ADD COLUMN IF NOT EXISTS first_login_at timestamptz;
|
|
ALTER TABLE public.ncue_user ADD COLUMN IF NOT EXISTS last_logout_at timestamptz;
|
|
ALTER TABLE public.ncue_user ADD COLUMN IF NOT EXISTS is_admin boolean NOT NULL DEFAULT false;
|
|
|
|
-- Backward-compat: previously can_manage was used as admin flag. Preserve existing admins.
|
|
UPDATE public.ncue_user SET is_admin = true WHERE can_manage = true AND is_admin = false;
|
|
-- Keep can_manage consistent with is_admin going forward.
|
|
UPDATE public.ncue_user SET can_manage = is_admin WHERE can_manage IS DISTINCT FROM is_admin;
|
|
|
|
-- App config (shared across browsers)
|
|
CREATE TABLE IF NOT EXISTS public.ncue_app_config (
|
|
key text PRIMARY KEY,
|
|
value jsonb NOT NULL,
|
|
updated_at timestamptz NOT NULL DEFAULT now()
|
|
);
|
|
|
|
|