The Case of the Invisible Project: Unlocking Shared Data in a Multi-Tenant tRPC App

Ever had a user report they can't see data that should be visible to them? It's a classic head-scratcher, especially in multi-tenant applications where data segmentation is paramount, but shared access is equally crucial. This past week, I dove deep into just such a problem within our nyxcore.cloud platform, specifically concerning our new ckb-nyx tenant.

The goal was straightforward: ensure all members of the ckb-nyx tenant, including those with a viewer role, could see project data. Simple, right? Turns out, the devil was in the details – or, more accurately, in the WHERE clauses of our tRPC queries.

The Problem: Data Hiding in Plain Sight

We recently onboarded Lisa (lisa@tastehub.io) to our ckb-nyx tenant with a viewer role. The expectation was that she'd be able to browse all projects and their associated data. However, upon testing, a critical bug emerged: Lisa couldn't load project detail pages. The application would fetch a project list, but clicking into any specific project resulted in an empty page or an error.

This was puzzling. We had already implemented robust role-based access control (RBAC) using an enforceMutationRole middleware, ensuring that viewer roles couldn't perform create, update, or delete operations. For read operations, we generally relied on tenantId filtering. So, why was this specific data invisible?

The Root Cause: Over-Scoped Queries

The issue boiled down to an overly aggressive filtering strategy in many of our read-only queries. In earlier stages of development, when the application was primarily single-user or focused on personal workspaces, many queries implicitly or explicitly filtered by userId.

For example, a project list query might look something like this:

// Initial (problematic) query logic
const projects = await ctx.db.project.findMany({
  where: {
    userId: ctx.user.id, // <-- The culprit!
    tenantId: ctx.tenant.id,
  },
  // ... other includes and selects
});

While this ensures a user only sees their own projects within a tenant, it completely breaks shared visibility. If Oliver (the ckb-nyx owner) created a project, Lisa (the viewer) wouldn't see it because project.userId would be Oliver's ID, not Lisa's.

The solution was clear: for shared tenant data, read queries should only filter by tenantId. userId should be reserved for mutations (to track ownership or modification) or for genuinely personal data (like a user's wardrobe in our system).

The Fix: A Surgical Strike on userId

My session was dedicated to systematically removing userId: ctx.user.id from read-focused tRPC query procedures within src/server/trpc/routers/projects.ts. This was a precise operation, ensuring that mutations (like update, delete, publishBlog) retained userId in their WHERE clauses to maintain ownership and prevent unauthorized modifications.

Here's a simplified example of the transformation:

// Before: Only the project creator could see their project details
// src/server/trpc/routers/projects.ts -> overview procedure (simplified)
.query("overview", {
  input: z.object({ projectId: z.string().uuid() }),
  async resolve({ input, ctx }) {
    const project = await ctx.db.project.findUnique({
      where: {
        id: input.projectId,
        userId: ctx.user.id, // This made the project invisible to others
        tenantId: ctx.tenant.id,
      },
      // ...
    });
    return project;
  },
})

// After: Any tenant member can see any project within their tenant
.query("overview", {
  input: z.object({ projectId: z.string().uuid() }),
  async resolve({ input, ctx }) {
    const project = await ctx.db.project.findUnique({
      where: {
        id: input.projectId,
        tenantId: ctx.tenant.id, // Now filters only by tenant
      },
      // ...
    });
    return project;
  },
})

I identified and modified 10 specific query procedures related to project details, stats, notes, documents, and blog posts. After the changes, all queries now filter solely by tenantId for shared data visibility.

Verification was key:

• Confirmed TypeScript was clean.
• All 271 unit tests passed.
• Committed the changes (fbf1d16) with a clear message: "fix: remove userId from project query procedures for shared tenant visibility".
• Deployed directly to production.

This fix, combined with earlier work to remove userId from projects.list, projects.get, and queries in memory.ts and consolidation.ts, should resolve the core visibility issue for ckb-nyx.

Beyond the Code: Lessons Learned from the Trenches

Development isn't just about writing code; it's about navigating unexpected challenges. This session had its share of "pain points" that turned into valuable lessons.

1. Auth.js Magic Link Hashing: Don't Assume External Parity

The Problem: I needed to programmatically generate Auth.js magic link tokens for a specific user to facilitate testing and onboarding. My initial thought was to use standard shell utilities like sha256sum to hash the token, assuming Auth.js used a common hashing scheme. The Failure: The generated hash didn't match what Auth.js expected internally. The Lesson: Auth.js's internal hashing (and many similar libraries) often involves more than just a raw hash. It typically uses the Web Crypto API, often with a secret appended (like AUTH_SECRET in our case). Relying on external, generic tools for cryptographic operations that are part of a library's internal flow is risky. The Takeaway: When dealing with library-specific cryptographic operations, always consult the library's source or documentation for the correct method. In our case, using Node.js's crypto.subtle.digest("SHA-256", ...) within the app container was the correct, reliable approach.

2. WhatsApp and Single-Use Magic Links: Beware the Bot Crawl

The Problem: After generating a magic link, I tried sending it to a test user via WhatsApp for quick verification. The Failure: WhatsApp's bot automatically crawls URLs pasted into chats to generate previews. This "crawl" consumed the single-use verification token immediately, rendering the link useless for the actual user. The Lesson: Any platform that automatically previews or crawls URLs can prematurely invalidate single-use tokens (like magic links, password reset links). The Takeaway: Educate users to send such links as plain text without previews, or stick to email delivery where this behavior is less common. For critical flows, consider adding a confirmation step after the link is clicked but before the token is consumed.

3. Schema Drift and Raw SQL: Production is Not Development

The Problem: I was performing a raw SQL INSERT statement for workflow_insights data during the nyx to ckb-nyx data migration. My local development environment had a new column, insightScope, in this table. The Failure: The INSERT failed on the production server because the insightScope column hadn't been migrated yet. The Lesson: Production schemas are not always identical to development schemas, especially when new features with database changes are in progress but not fully deployed. The Takeaway: Always verify schema consistency before running raw SQL queries that involve new columns. Use proper ORM migrations or carefully staged ALTER TABLE scripts to ensure your production database is up-to-date with your application's expectations.

What's Next? Continuous Improvement

While the core visibility issue is addressed, the journey continues:

1. Verification: The immediate next step is to confirm that Lisa (or any viewer on ckb-nyx) can indeed load project detail pages.
2. Audit Remaining Routers: A broader audit is needed for other routers (admin.ts, nyxbook.ts, axiom.ts, code-analysis.ts) to ensure no other shared data is inadvertently filtered by userId. (wardrobe.ts is intentionally user-scoped, so it's excluded).
3. Mutation userId Review: Currently, mutations in projects.ts still filter by userId (e.g., update, delete). This means only the original creator can modify a project. We need to decide if this is the desired behavior for all shared projects or if team members should be able to edit each other's work.
4. Production Cron Jobs: Setting up AUDIT_CRON_SECRET and configuring an hourly cron job is pending from earlier sessions.
5. Schema Migration: The insightScope column needs to be properly migrated to the workflow_insights table on production.

This session was a stark reminder of the complexities of multi-tenant architecture and the importance of meticulous query design. Every WHERE clause has implications, and understanding the distinction between user-scoped and tenant-scoped data is paramount for building robust, secure, and usable applications.

{
  "thingsDone": [
    "Removed userId from 10 project-related query procedures in tRPC router for shared tenant visibility.",
    "Ensured all relevant queries now filter by tenantId only, while mutations retain userId for ownership.",
    "Verified TypeScript cleanliness and 271/271 test passes.",
    "Committed changes with 'fix: remove userId from project query procedures for shared tenant visibility' (fbf1d16).",
    "Deployed fix to production server.",
    "Previously added enforceMutationRole middleware to all 19 feature routers.",
    "Previously removed userId from queries in projects.list, projects.get, memory.ts, and consolidation.ts.",
    "Previously fixed login page UX for expired magic links.",
    "Performed full data copy from nyx to ckb-nyx tenant.",
    "Set up Lisa (lisa@tastehub.io) as a viewer on ckb-nyx tenant."
  ],
  "pains": [
    "Failed to generate Auth.js magic link tokens using shell `sha256sum` due to internal Auth.js hashing differences (Web Crypto API + AUTH_SECRET).",
    "Failed to send magic links via WhatsApp as the bot crawled and consumed single-use verification tokens.",
    "Failed raw SQL INSERT for workflow_insights with `insightScope` column on production due to schema drift."
  ],
  "successes": [
    "Successfully identified and fixed the root cause of shared tenant data visibility issues by adjusting query filters.",
    "Implemented a reliable workaround for Auth.js magic link generation using Node.js `crypto.subtle.digest`.",
    "Identified a critical behavior of WhatsApp bots regarding single-use URLs.",
    "Corrected SQL INSERT statement by removing non-existent column for successful data migration."
  ],
  "techStack": [
    "tRPC",
    "TypeScript",
    "Next.js",
    "Auth.js",
    "Node.js",
    "PostgreSQL",
    "Prisma (implied by `ctx.db.project`)",
    "Multi-tenant architecture"
  ]
}