feat: site authentication and CMS enhancements v2 - #231

Hey @SamSCx, thanks for this - site authentication and CMS user scoping are features we'd be interested in exploring, and the scope of work here is impressive. No promises it will get merged, but we want to give it the attention it deserves. Unfortunately I couldn't get far enough to evaluate the features because I hit a fundamental issue with the auth model that blocks login entirely.

Requesting changes so we can get this to a testable state.

1. app_metadata vs user_metadata - builder login is broken for all users
   Every auth check in this PR gates on user.app_metadata.role === 'admin' (in useAuthStore.signIn, initialize, checkSession, onAuthStateChange, useAuthSession, getAdminUser, and ~40 API routes). However, the two code paths that create builder admin users both write the role to the wrong field:

• signUp (stores/useAuthStore.ts:102–111) uses options.data: { role: 'admin' }, which Supabase writes to user_metadata, not app_metadata. Only the Admin API (service role key) can write to app_metadata (Supabase docs).
• inviteUserByEmail (app/(builder)/ycode/api/auth/invite/route.ts:42–48) passes data: { role: AUTH_ROLES.ADMIN }, which also goes to user_metadata.

No code in this PR calls auth.admin.updateUserById to promote the role to app_metadata. This means:

• Existing users - no app_metadata.role exists, login fails with "Access denied: You do not have administrator permissions."
• New installs - the wizard completes (because signUp doesn't check the role), but the next page refresh or login attempt fails permanently.
• Invited users - same issue.

The fix: after signUp or inviteUserByEmail, call the Admin API server-side to set app_metadata:

await adminClient.auth.admin.updateUserById(userId, {
  app_metadata: { role: 'admin' }
});

The site registration endpoint (app/api/auth/register/route.ts) already does this correctly using auth.admin.createUser({ app_metadata: { role: AUTH_ROLES.USER } }) - the same pattern needs to be applied to the builder admin flows.

2. Migration/login deadlock for existing deployments

The migration (20260515000001_initialize_auth_system.ts) backfills app_metadata.role = 'admin' for existing users via SQL, which would fix the login issue for upgrades. However, MigrationChecker only renders after the login gate in YCodeBuilderMain.tsx (line 1862 checks !user, line 1960 renders MigrationChecker). Since login requires the role, and the role requires the migration, existing deployments are deadlocked with no UI path to resolve it.

The migrate endpoint (/ycode/api/setup/migrate) has no auth guard, so the simplest fix is to move MigrationChecker above the login check in YCodeBuilderMain, or run migrations on app startup.

3. Migration timing on fresh installs

The wizard runs migrations in step 2 and creates the admin account in step 3. The backfill SQL (UPDATE auth.users ... WHERE role IS NULL) updates zero rows because no users exist yet. The admin created in step 3 never gets the role in app_metadata. This needs a post-signup server call (see fix in point 1).

4. Branch needs rebasing onto current develop

The branch diverged at 8aed9a82 (v1.10.0). develop is now at v1.14.0 with 104 commits ahead. Merging as-is would roll back features that have since landed, including static export, MCP 1.0 tooling, editable 401 password forms, and collection link pickers. A rebase onto current develop would resolve this - most of the ~8,400 "deleted" lines in the diff are missing upstream work, not intentional removals.

5. Other issues noticed during review

• /ycode/api/supabase/config - the getAdminUser() guard on this endpoint creates a chicken-and-egg problem since the browser needs this config to bootstrap the Supabase client before any session exists. This endpoint only returns public config (URL + anon key) and should remain unauthenticated.

• SVG icon CSS - app/site.css removes the [data-icon] fill/stroke inheritance rules, which would regress icon coloring on published sites.

• Scope - this PR bundles site auth, security hardening, static export removal, and MCP cleanup. Splitting into smaller PRs would make review and rollback easier.

If you can address the auth model issues and rebase onto current develop, happy to test again.