Skip to content
TheChatArchiveThe Chat Archive
GPT 5.1 Codex Max· openaiHugging Face Frontier Reasoning Traces

Build a SvelteKit + Prisma full-stack app with row level security simulation. Wrap the…

  1. User

    Build a SvelteKit + Prisma full-stack app with row level security simulation. Wrap the Prisma client in a proxy that automatically injects a where clause with tenantId from the user context into every find and update query to prevent data leaks.

  2. Assistant

    Providing full example for Prisma row-level security simulation

    // prisma/schema.prisma
    
    datasource db {
      provider = "sqlite"
      url      = "file:./dev.db"
    }
    
    generator client {
      provider = "prisma-client-js"
    }
    
    model Tenant {
      id    Int    @id @default(autoincrement())
      name  String
      users User[]
      todos Todo[]
    }
    
    model User {
      id       Int    @id @default(autoincrement())
      email    String @unique
      tenantId Int
      tenant   Tenant @relation(fields: [tenantId], references: [id])
      todos    Todo[]
    }
    
    model Todo {
      id       Int     @id @default(autoincrement())
      text     String
      done     Boolean @default(false)
      tenantId Int
      userId   Int
      tenant   Tenant  @relation(fields: [tenantId], references: [id])
      user     User    @relation(fields: [userId], references: [id])
    
      @@index([tenantId])
    }
    
    // src/lib/prisma.ts
    
    import { PrismaClient } from '@prisma/client';
    
    export const prisma = new PrismaClient();
    
    // src/lib/rls-prisma.ts
    
    import type { PrismaClient } from '@prisma/client';
    
    const prismaMethodsToWrap = new Set([
      'findMany', 'findFirst', 'findUnique',
      'update', 'updateMany', 'delete', 'deleteMany',
      'create', 'createMany', 'upsert', 'count'
    ]);
    
    function mergeWhere(originalWhere: any = {}, tenantId: number) {
      // Merge tenantId into existing where, combining with AND
      if (originalWhere && typeof originalWhere === 'object') {
        // If originalWhere already has tenantId, trust it (to avoid conflicts)
        if ('tenantId' in originalWhere) {
          return originalWhere;
        }
        return { AND: [originalWhere, { tenantId }] };
      }
      return { tenantId };
    }
    
    export function withTenantId(prisma: PrismaClient, tenantId: number) {
      return new Proxy(prisma, {
        get(target, prop: string | symbol) {
          const orig = Reflect.get(target, prop);
    
          // If accessing a model (e.g. user, todo)
          if (typeof prop === 'string' && orig && typeof orig === 'object' && !prismaMethodsToWrap.has(prop)) {
            return new Proxy(orig, {
              get(modelTarget, method: string | symbol) {
                if (typeof method !== 'string') return Reflect.get(modelTarget, method);
    
                if (!prismaMethodsToWrap.has(method)) {
                  // Pass through non-targeted methods ($connect, $transaction, others)
                  const fn = Reflect.get(modelTarget, method);
                  if (typeof fn === 'function') return fn.bind(modelTarget);
                  return fn;
                }
    
                return async function (...args: any[]) {
                  // args[0] is usually the query object with where, data, etc.
                  const query = args[0] || {};
    
                  const modifiedQuery = { ...query };
    
                  // Inject tenantId in queries depending on method
                  if (['findMany', 'findFirst', 'findUnique', 'count', 'update', 'updateMany', 'delete', 'deleteMany'].includes(method)) {
                    modifiedQuery.where = mergeWhere(query.where, tenantId);
                  } else if (['create'].includes(method)) {
                    // For create, inject tenantId into data if not present
                    if (!modifiedQuery.data) modifiedQuery.data = {};
                    if (modifiedQuery.data && !('tenantId' in modifiedQuery.data)) {
                      modifiedQuery.data.tenantId = tenantId;
                    }
                  } else if (method === 'createMany') {
                    // For createMany, inject tenantId to each item if not set
                    if (modifiedQuery.data && Array.isArray(modifiedQuery.data)) {
                      modifiedQuery.data = modifiedQuery.data.map(item => ({
                        tenantId,
                        ...item
                      }));
                    }
                  } else if (method === 'upsert') {
                    // upsert has where, create, update
                    modifiedQuery.where = mergeWhere(query.where, tenantId);
                    if (!modifiedQuery.create) modifiedQuery.create = {};
                    if (!('tenantId' in modifiedQuery.create)) {
                      modifiedQuery.create.tenantId = tenantId;
                    }
                    if (modifiedQuery.update && !('tenantId' in modifiedQuery.update)) {
                      modifiedQuery.update.tenantId = tenantId;
                    }
                  }
    
                  return orig[method](modifiedQuery);
                };
              }
            });
          }
    
          // Pass through for any other props/methods (e.g. $connect)
          return typeof orig === 'function' ? orig.bind(target) : orig;
        }
      });
    }
    
    // src/hooks.server.ts
    
    import type { Handle } from '@sveltejs/kit';
    
    export const handle: Handle = async ({ event, resolve }) => {
      // Simulate user authentication & set tenantId in locals
      // In real app: extract user from session or JWT
    
      event.locals.user = {
        id: 42,
        tenantId: 1
      };
    
      return resolve(event);
    };
    
    // src/routes/api/todos/+server.ts
    
    import type { RequestHandler } from '@sveltejs/kit';
    import { prisma } from '$lib/prisma';
    import { withTenantId } from '$lib/rls-prisma';
    
    export const GET: RequestHandler = async ({ locals }) => {
      if (!locals.user) return new Response('Unauthorized', { status: 401 });
    
      const rlsPrisma = withTenantId(prisma, locals.user.tenantId);
    
      const todos = await rlsPrisma.todo.findMany();
    
      return new Response(JSON.stringify(todos), {
        headers: { 'Content-Type': 'application/json' }
      });
    };
    
    export const POST: RequestHandler = async ({ request, locals }) => {
      if (!locals.user) return new Response('Unauthorized', { status: 401 });
    
      const { text, done } = await request.json();
    
      const rlsPrisma = withTenantId(prisma, locals.user.tenantId);
    
      const todo = await rlsPrisma.todo.create({
        data: {
          text,
          done: done ?? false,
          userId: locals.user.id // preserve user association
          // tenantId is injected automatically by proxy
        }
      });
    
      return new Response(JSON.stringify(todo), {
        headers: { 'Content-Type': 'application/json' }
      });
    };
    
    <!-- src/routes/+page.svelte -->
    
    <script lang="ts">
      import { onMount } from 'svelte';
      let todos = [];
    
      async function loadTodos() {
        const res = await fetch('/api/todos');
        todos = await res.json();
      }
    
      onMount(() => {
        loadTodos();
      });
    </script>
    
    <h1>Your Todos</h1>
    
    <ul>
      {#each todos as todo}
        <li>{todo.text} {todo.done ? '✅' : ''}</li>
      {/each}
    </ul>
    

    This setup simulates row-level security by automatically injecting the current user's tenantId into all relevant Prisma queries, preventing cross-tenant data leaks. The proxy wraps the Prisma client and mutates query where and data parameters accordingly.

    Note: This is a simplified demonstration and does not replace real database-level row-level security features. Below is a minimal SvelteKit + Prisma example showing how you can “simulate” row-level security in the application layer by wrapping the Prisma client with a proxy that injects tenantId into every query. This protects against accidental cross-tenant data leaks in your application code. (Note: true RLS is best enforced at the database layer where supported.)


    1) Prisma schema

    Create prisma/schema.prisma (using SQLite here for simplicity):

    datasource db {
      provider = "sqlite"
      url      = "file:./dev.db"
    }
    
    generator client {
      provider = "prisma-client-js"
    }
    
    model Tenant {
      id    Int     @id @default(autoincrement())
      name  String
      users User[]
      todos Todo[]
    }
    
    model User {
      id       Int    @id @default(autoincrement())
      email    String @unique
      tenantId Int
      tenant   Tenant @relation(fields: [tenantId], references: [id])
      todos    Todo[]
    }
    
    model Todo {
      id       Int    @id @default(autoincrement())
      text     String
      done     Boolean @default(false)
      tenantId Int
      userId   Int
      tenant   Tenant @relation(fields: [tenantId], references: [id])
      user     User   @relation(fields: [userId], references: [id])
    
      @@index([tenantId])
    }
    

    Run:

    npx prisma db push
    

    (or npx prisma migrate dev if you prefer migrations).


    2) Basic Prisma client

    src/lib/prisma.ts:

    import { PrismaClient } from '@prisma/client';
    
    export const prisma = new PrismaClient();
    

    3) RLS-style Prisma proxy

    src/lib/rls-prisma.ts:

    // A simple “application-level RLS” proxy to inject tenantId in queries.
    
    type PrismaModelName = string;
    
    /**
     * List of Prisma model methods that should enforce tenant scoping.
     * Extend as needed (e.g., aggregate, groupBy, etc.).
     */
    const METHODS_TO_ENFORCE = [
      'findMany',
      'findFirst',
      'findUnique',
      'count',
      'update',
      'updateMany',
      'delete',
      'deleteMany',
      'upsert',
      'create',
      'createMany',
    ];
    
    function injectTenantWhere(args: any, tenantId: number) {
      const query = args?.[0] ?? {};
      // Merge tenantId into existing where clause.
      query.where = {
        ...(query.where || {}),
        tenantId,
      };
      args[0] = query;
      return args;
    }
    
    function injectTenantData(args: any, tenantId: number) {
      const query = args?.[0] ?? {};
      // For create/upsert/update with data, ensure data.tenantId is set.
      if (query.data) {
        query.data = { ...query.data, tenantId };
      }
      args[0] = query;
      return args;
    }
    
    /**
     * Returns a prisma client proxy that automatically injects `tenantId`
     * into `where` clauses (and data payloads for writes) for the given user.
     */
    export function withTenant(prisma: any, tenantId: number) {
      if (!tenantId) {
        throw new Error('Tenant ID missing. Refusing to create RLS Prisma proxy.');
      }
    
      // Proxy each Prisma model (e.g., prisma.user, prisma.todo).
      const modelProxy = (model: any) =>
        new Proxy(model, {
          get(target, prop: string, receiver) {
            const orig = Reflect.get(target, prop, receiver);
            if (typeof orig !== 'function') return orig;
    
            // Wrap methods we want to enforce
            if (METHODS_TO_ENFORCE.includes(prop)) {
              return (...args: any[]) => {
                // If it's a read method, force "where.tenantId".
                if (prop.startsWith('find') || prop === 'count' || prop.startsWith('delete')) {
                  args = injectTenantWhere(args, tenantId);
                }
                // If it's a write method, also force data.tenantId.
                if (prop.startsWith('create') || prop.startsWith('update') || prop === 'upsert') {
                  args = injectTenantWhere(args, tenantId);
                  args = injectTenantData(args, tenantId);
                }
                return orig(...args);
              };
            }
            // Not a method we enforce? call directly.
            return (...args: any[]) => orig.apply(target, args);
          },
        });
    
      // Proxy top-level Prisma client so that each model is wrapped.
      return new Proxy(prisma, {
        get(target, prop: PrismaModelName, receiver) {
          const value = Reflect.get(target, prop, receiver);
          // If value has model methods, wrap it; else return as is ($transaction, etc.).
          if (value && typeof value === 'object' && !(value instanceof Function)) {
            return modelProxy(value);
          }
          return value;
        },
      });
    }
    

    4) Simulated authentication in hooks.server.ts

    src/hooks.server.ts (very simplified, fakes a logged-in user):

    import type { Handle } from '@sveltejs/kit';
    
    /**
     * Pretend we extracted user + tenant from a session/cookie/JWT.
     * In real apps, you'd do proper auth here.
     */
    export const handle: Handle = async ({ event, resolve }) => {
      // Fake logged-in user with tenantId=1
      event.locals.user = {
        id: 123,
        email: 'user@example.com',
        tenantId: 1
      };
    
      return resolve(event);
    };
    

    5) Example API route using the RLS proxy

    src/routes/api/todos/+server.ts:

    import type { RequestHandler } from '@sveltejs/kit';
    import { prisma } from '$lib/prisma';
    import { withTenant } from '$lib/rls-prisma';
    
    export const GET: RequestHandler = async ({ locals }) => {
      // Get the tenantId from locals.user (populated by hooks.server.ts).
      const tenantId = locals.user?.tenantId;
      const db = withTenant(prisma, tenantId);
    
      // Will automatically inject `where: { tenantId }`
      const todos = await db.todo.findMany({
        // other filters can go here; tenantId will be added or merged automatically
        where: { done: false },
        orderBy: { id: 'desc' }
      });
    
      return new Response(JSON.stringify(todos), { status: 200 });
    };
    
    export const POST: RequestHandler = async ({ request, locals }) => {
      const tenantId = locals.user?.tenantId;
      const db = withTenant(prisma, tenantId);
    
      const { text } = await request.json();
      // Will automatically set data.tenantId = tenantId and add where.tenantId
      const newTodo = await db.todo.create({
        data: {
          text,
          done: false,
          userId: locals.user.id
        }
      });
    
      return new Response(JSON.stringify(newTodo), { status: 201 });
    };
    

    6) Minimal front-end page

    src/routes/+page.svelte:

    <script lang="ts">
      import { onMount } from 'svelte';
    
      let todos: any[] = [];
      let newTodo = '';
    
      onMount(async () => {
        const res = await fetch('/api/todos');
        todos = await res.json();
      });
    
      async function addTodo() {
        if (!newTodo.trim()) return;
        const res = await fetch('/api/todos', {
          method: 'POST',
          headers: { 'content-type': 'application/json' },
          body: JSON.stringify({ text: newTodo })
        });
        const created = await res.json();
        todos = [created, ...todos];
        newTodo = '';
      }
    </script>
    
    <h1>My Tenant-Scoped Todos</h1>
    
    <form on:submit|preventDefault={addTodo}>
      <input bind:value={newTodo} placeholder="What to do?" />
      <button type="submit">Add</button>
    </form>
    
    <ul>
      {#each todos as todo}
        <li>{todo.text} ({todo.done ? 'done' : 'todo'})</li>
      {/each}
    </ul>
    

    7) Running the app

    npm install
    npx prisma db push   # or npx prisma migrate dev
    npm run dev
    

    Visit http://localhost:5173/ (or the port SvelteKit shows). Any request that goes through withTenant(prisma, tenantId) will automatically be scoped to the user’s tenantId on reads and writes.