auth-profile-lifecycle.md

Auth Profile Lifecycle

This document explains the auth/profile lifecycle migration:

supabase/migrations/0003_auth_profile_lifecycle.sql

The migration has not been applied to a real Supabase project yet. Task 4A adds admin login and route protection in the app, but CRUD, CMS editing, storage, public data integration, and staff management screens are still deferred.

Why Profiles Reference auth.users

Supabase Auth stores real sign-in identities in auth.users.

FrameSignal stores staff-facing profile data in public.profiles, including display name, username, avatar URL, bio, role, and activation status.

The profiles.id column references auth.users.id so each staff auth user can have one matching profile row.

Why auth.users Is Not Exposed Directly

The auth.users table belongs to Supabase Auth and can contain sensitive identity data.

Application code should use public.profiles for safe staff profile information, while Supabase Auth handles authentication internally.

What Happens When A New Auth User Is Created

The migration creates public.handle_new_user() and attaches it to this trigger:

on_auth_user_created

When a row is inserted into auth.users, the trigger creates a matching public.profiles row.

The profile uses:

• id = new.id
• full_name from raw_user_meta_data.full_name, then email prefix, then FrameSignal User
• username from raw_user_meta_data.username when available and not already used
• avatar_url from raw_user_meta_data.avatar_url when available
• role = editor
• is_active = false

Why New Users Are Inactive Editors By Default

FrameSignal does not have public reader accounts in the MVP. Future auth users are staff accounts only.

New users are created as inactive editors so they cannot immediately receive CMS permissions. An admin must activate them first.

This keeps accidental signups, invited-but-unreviewed accounts, and test users from gaining editor access automatically.

How An Admin Later Activates Staff

After real auth and admin management are built, an active admin can activate a staff profile by setting is_active = true.

Admins can also decide whether a staff profile should remain editor or become admin.

Why Users Must Not Change Their Own Role

If users could update their own role, an editor could promote themselves to admin.

If users could update their own is_active, an inactive staff account could activate itself.

That is privilege escalation. It must be blocked at the database level, not only hidden in the UI.

Role-Hardening Trigger

The migration adds public.prevent_profile_privilege_escalation().

The trigger runs before updates on public.profiles.

It protects:

• role
• is_active

Active admins can change those fields. Non-admin users cannot. If a non-admin tries to change either field, the trigger raises this error:

Only active admins can change profile role or activation status.

Non-admin users may still update safe profile fields later if RLS and server-side validation allow it:

• full_name
• username
• avatar_url
• bio

Active Profiles And RLS Helpers

The role helper functions only trust active profiles:

• public.current_user_role()
• public.is_admin()
• public.is_editor()
• public.is_admin_or_editor()

An inactive profile returns no staff role, so RLS policies do not treat that user as an editor or admin.

First Admin Bootstrap

The first admin must be bootstrapped manually after local or cloud Supabase is set up.

Use a carefully checked auth user UUID. This example uses a placeholder only:

update public.profiles
set role = 'admin', is_active = true
where id = '<AUTH_USER_UUID>';

Before running this, verify the auth user is the intended owner/admin account. Do not paste a random UUID, and do not run this against the wrong Supabase project.

Manual bootstrap should be done only in a trusted database context. It is not a public UI flow.

Still Not Added

This profile lifecycle task does not add:

• Public reader signups
• CRUD
• API routes for content operations
• Admin user management screens
• Supabase reads or writes from the app UI

Those pieces should come later through explicit auth and admin integration tasks.