The file download endpoint loads two relationship chains in one query to prove ownership

The portal stores attachments on projects and on sessions. A session belongs to a project and a project belongs to a client, so a file attached to a session is two joins away from the client who owns it. The file download route at /api/portal/files/[id] needs to verify that the person requesting the file actually owns it. It does this with a single Prisma query that loads both relationship chains at once. The findUnique call includes project with a select on clientId, and session with a nested select that goes through project to clientId. One database round trip returns both paths. The authorization check reads the result with a single OR expression — attachment.project?.clientId || attachment.session?.project?.clientId. If the attachment sits directly on a project, the first path resolves. If it sits on a session, the second path resolves through the intermediate join. Either way, the result is a clientId that gets compared against the logged-in user's client record. Admin users bypass the check entirely — the route reads publicMetadata.role from the Clerk user object and skips the ownership verification if the role is admin. The whole file is forty-nine lines. It authenticates with Clerk, loads the attachment with both relationship paths, checks admin or ownership, generates a signed R2 download URL, and returns it with the filename. No file bytes pass through the server. The route returns a URL and the browser fetches the file directly from Cloudflare R2. Two relationship chains, one query, one authorization decision, and the same endpoint serves audio stems, accessibility reports, software deliverables, and session recordings without knowing or caring what type of file it is.