Supabase Add Tenant User

Add a new multi-tenant user: $ARGUMENTS

$ARGUMENTS can include any of:

• email (required)
• display name / full name
• role preference (user | author | creator | editor | subscriber | admin) — default user
• organization slug if joining an existing tenant (no new org created)
• membership role (owner | admin | member) — default owner if new org, member if joining
• Supabase project id / ref if not inferrable

Operating principles

1. Never grant role = 'admin' in user_profiles unless the user is explicitly the Movemental platform account. Platform admin is reserved for the operator of the SaaS. Creators on the platform are author or creator, not admin.
2. One auth user = one owned organization. The handle_new_user trigger provisions a personal org automatically. Only bypass when joining an existing tenant.
3. Never insert an organization_memberships row pointing to a non-existent org or user. Verify both exist first.
4. Never commit plaintext passwords to the repo. Prefer magic-link invites (supabase.auth.admin.inviteUserByEmail) or passwordless OTP. If a password is required, print it once to the operator and do not persist.
5. Always prefer the DB trigger over manual inserts. handle_new_user is idempotent (ON CONFLICT DO NOTHING / existence checks) and will fill user_profiles, organizations, and organization_memberships automatically.

Protocol

Step 1 — Identify target project

Pick the Supabase project id for the app:

• Look in CLAUDE.md, .env.shared, or .env.local for SUPABASE_PROJECT_ID / NEXT_PUBLIC_SUPABASE_URL.
• If ambiguous, run mcp__supabase__list_projects and ask the operator.

For this repo (movemental-visual-editor):

• project id: vhaiiiykcukrlyvwlgip
• platform org slug: movemental
• platform org id: 928be32a-879a-407f-9bf4-4dfb17e5ea08

Step 2 — Confirm pre-reqs exist

Run once before acting (paste into the chat as a single SQL call):

-- Confirm schema is in the expected shape.
SELECT to_regclass('public.user_profiles')  AS user_profiles_exists,
       to_regclass('public.organizations')  AS organizations_exists,
       to_regclass('public.organization_memberships') AS memberships_exists;

-- Confirm the handle_new_user trigger exists.
SELECT tgname FROM pg_trigger
WHERE tgname = 'on_auth_user_created' AND tgrelid = 'auth.users'::regclass;

If any are missing, stop and tell the operator to run the auto_provision_user_organization migration first.

Step 3 — Classify the request

A. New creator, new tenant. Default case. Example:

"Add brad@example.com as a new creator named Brad Example."

Action: create auth user; trigger auto-provisions org + owner membership.

B. New member joining an existing tenant. Example:

"Add editor@foo.com as an editor on the alan-hirsch org."

Action: create auth user (trigger still provisions their own personal org; that's fine — multi-tenant platforms often let humans have one personal org and many org memberships). Then add an extra organization_memberships row pointing to the target tenant.

C. Platform admin (Movemental operator only). Example:

"Add ops@movemental.com as a platform admin."

Action: create auth user as (B) joining the movemental org as owner, then UPDATE public.user_profiles SET role = 'admin' for that user. Only do this if the operator has confirmed the new admin should have cross-tenant access.

Step 4 — Create the auth user

Prefer the Supabase Auth admin API via the service role. The Service Role key is in ~/Desktop/Dev/.env.shared as SUPABASE_SERVICE_ROLE_KEY.

Options (in order of preference):

1. Magic-link invite (no password):

   curl -sS -X POST \
     "$SUPABASE_URL/auth/v1/admin/generate_link" \
     -H "apikey: $SUPABASE_SERVICE_ROLE_KEY" \
     -H "Authorization: Bearer $SUPABASE_SERVICE_ROLE_KEY" \
     -H "Content-Type: application/json" \
     -d '{
       "type": "invite",
       "email": "NEW_EMAIL",
       "data": { "display_name": "NEW_DISPLAY_NAME" }
     }'
   

2. Create with a temporary password (dev / local testing only):

   curl -sS -X POST \
     "$SUPABASE_URL/auth/v1/admin/users" \
     -H "apikey: $SUPABASE_SERVICE_ROLE_KEY" \
     -H "Authorization: Bearer $SUPABASE_SERVICE_ROLE_KEY" \
     -H "Content-Type: application/json" \
     -d '{
       "email": "NEW_EMAIL",
       "password": "TEMP_PASSWORD",
       "email_confirm": true,
       "user_metadata": { "display_name": "NEW_DISPLAY_NAME" }
     }'
   

After this succeeds, the trigger creates user_profiles, organizations, and organization_memberships automatically. You do not need to insert them by hand.

Step 5 — (Case B only) Join an existing tenant

If the user should also be a member of another org, insert the membership:

WITH usr AS (
  SELECT id FROM public.user_profiles WHERE email = 'NEW_EMAIL'
),
org AS (
  SELECT id FROM public.organizations WHERE slug = 'TARGET_ORG_SLUG'
)
INSERT INTO public.organization_memberships
  (user_id, organization_id, role, status, joined_at, invited_by)
SELECT usr.id, org.id, 'MEMBERSHIP_ROLE', 'active', now(), NULL
FROM usr, org
WHERE NOT EXISTS (
  SELECT 1 FROM public.organization_memberships m
  WHERE m.user_id = usr.id AND m.organization_id = org.id
);

Step 6 — (Case C only) Promote to platform admin

Only after the operator has confirmed. Only for the Movemental account.

UPDATE public.user_profiles
SET role = 'admin', updated_at = now()
WHERE email = 'NEW_EMAIL';

Verify no other email keeps role = 'admin' unintentionally:

SELECT email, role FROM public.user_profiles WHERE role = 'admin' ORDER BY email;

Step 7 — Smoke-test the result

Always run after provisioning:

SELECT
  up.email,
  up.role                     AS platform_role,
  up.account_status,
  o.slug                      AS owned_org_slug,
  om.role                     AS membership_role,
  om.status                   AS membership_status
FROM public.user_profiles up
LEFT JOIN public.organizations o       ON o.account_owner_id = up.id
LEFT JOIN public.organization_memberships om
       ON om.user_id = up.id AND om.organization_id = o.id
WHERE up.email = 'NEW_EMAIL';

Expect exactly one row, with:

• platform_role ∈ {user, author, editor, subscriber, creator} (never admin unless Case C)
• owned_org_slug not null (except for Case B-only members of another org)
• membership_role = 'owner' for the owned org

If any field is NULL where it shouldn't be, inspect the trigger: SELECT pg_get_functiondef('public.handle_new_user'::regproc);

Step 8 — Report back

Reply to the operator with:

1. The user id (uuid) and email
2. The owned org slug + id (if new tenant)
3. Any extra memberships created
4. Whether the platform role is admin or not
5. A copy of the smoke-test result
6. The invite / magic link URL (if step 4 option 1)

Gotchas seen in this codebase

• user_profiles.role is a free-text column (not an enum). Case-sensitive. Allowed values today: admin, author, creator, editor, subscriber, user. Do not invent new values.
• organizations.slug is globally unique. The trigger appends -1, -2, … on collision. If joining an existing tenant rather than creating one, pass the slug to Step 5.
• organizations.account_owner_id is nullable in the schema but the app (see src/lib/tenant/resolve-owned-org.ts) requires it set for the user to be able to save. The trigger now sets it; legacy rows may not — run: UPDATE public.organizations SET account_owner_id = $uid WHERE id = $org to fix.
• NEXT_PUBLIC_SHOW_ADMIN_TENANT_SWITCHER="false" in .env.local silently hides the tenant switcher even for admins. Set to "true" for the Movemental deploy.
• RLS: public.user_profiles and public.organizations have user_select_own policies keyed on auth.uid(). Inserts via the trigger run as SECURITY DEFINER, so they bypass RLS intentionally.