Map verified Studio subjects to isolated workspaces

app/security/tenancy.py

@@ -100,6 +100,62 @@ class TenantService:
                 membership_role=membership.role,
             )
 
+    async def resolve_studio_identity(
+        self, *, subject: str, email: str, display_name: str
+    ) -> TenantPrincipal:
+        """Resolve a verified Supabase subject to its own backend workspace."""
+        if not subject or len(subject) > 255:
+            raise UnauthorizedError
+        external_subject = f"supabase:{subject}"
+        async with self.database.session() as session:
+            user = await session.scalar(select(User).where(User.subject == external_subject))
+            if user is not None:
+                if user.status != "active":
+                    raise ForbiddenError
+                membership = await session.scalar(
+                    select(WorkspaceMembership).where(
+                        WorkspaceMembership.user_id == user.id,
+                        WorkspaceMembership.status == "active",
+                    ).order_by(WorkspaceMembership.created_at.asc())
+                )
+                if membership is None:
+                    raise ForbiddenError
+                workspace = await session.get(Workspace, membership.workspace_id)
+                if workspace is None or workspace.status != "active":
+                    raise ForbiddenError
+                return TenantPrincipal(workspace.id, user.id, membership.id, membership.role)
+
+            user_id, workspace_id, membership_id = str(uuid4()), str(uuid4()), str(uuid4())
+            safe_name = (display_name or email or "Studio user").strip()[:255] or "Studio user"
+            user = User(id=user_id, subject=external_subject, display_name=safe_name,
+                        metadata_json={"identity_provider": "supabase"})
+            workspace = Workspace(id=workspace_id, slug=f"studio-{user_id.replace('-', '')}",
+                                  name=f"{safe_name}'s workspace"[:255],
+                                  metadata_json={"provisioned_from": "studio_auth"})
+            membership = WorkspaceMembership(id=membership_id, workspace_id=workspace_id,
+                                             user_id=user_id, role="admin", status="active")
+            session.add_all([user, workspace, membership])
+            try:
+                await session.commit()
+            except IntegrityError:
+                await session.rollback()
+                user = await session.scalar(select(User).where(User.subject == external_subject))
+                if user is None or user.status != "active":
+                    raise
+                membership = await session.scalar(
+                    select(WorkspaceMembership).where(
+                        WorkspaceMembership.user_id == user.id,
+                        WorkspaceMembership.status == "active",
+                    ).order_by(WorkspaceMembership.created_at.asc())
+                )
+                if membership is None:
+                    raise ForbiddenError
+                workspace = await session.get(Workspace, membership.workspace_id)
+                if workspace is None or workspace.status != "active":
+                    raise ForbiddenError
+                return TenantPrincipal(workspace.id, user.id, membership.id, membership.role)
+            return TenantPrincipal(workspace_id, user_id, membership_id, "admin")
+
     async def bind_api_key(
         self, *, api_key_id: str, workspace_id: str, user_id: str
     ) -> TenantPrincipal: