adminandClaude Sonnet 4.6
05daf70177
8 SQL migration files covering all 10 tables, RLS policies with SECURITY DEFINER helpers, and seed data for SCW1 site + 3 zones. Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com> Claude-Session: https://claude.ai/code/session_01AWxyMibCuGGtSQSqfajDQ7
41 lines
1.1 KiB
PL/PgSQL
41 lines
1.1 KiB
PL/PgSQL
-- supabase/migrations/20260709000002_users.sql
|
|
CREATE TYPE user_role AS ENUM (
|
|
'reporter', 'supervisor', 'hse', 'capa_owner', 'management', 'admin'
|
|
);
|
|
|
|
CREATE TABLE users (
|
|
id UUID PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
|
|
name TEXT NOT NULL DEFAULT '',
|
|
email TEXT NOT NULL DEFAULT '',
|
|
phone TEXT,
|
|
role user_role NOT NULL DEFAULT 'reporter',
|
|
department TEXT,
|
|
site_id UUID REFERENCES sites(id),
|
|
active BOOLEAN NOT NULL DEFAULT true,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
-- Auto-create users row when auth user signs up
|
|
CREATE OR REPLACE FUNCTION public.handle_new_auth_user()
|
|
RETURNS TRIGGER
|
|
LANGUAGE plpgsql
|
|
SECURITY DEFINER
|
|
SET search_path = public
|
|
AS $$
|
|
BEGIN
|
|
INSERT INTO public.users (id, email, name)
|
|
VALUES (
|
|
NEW.id,
|
|
NEW.email,
|
|
COALESCE(NEW.raw_user_meta_data->>'full_name', NEW.email)
|
|
)
|
|
ON CONFLICT (id) DO NOTHING;
|
|
RETURN NEW;
|
|
END;
|
|
$$;
|
|
|
|
CREATE TRIGGER on_auth_user_created
|
|
AFTER INSERT ON auth.users
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION public.handle_new_auth_user();
|