/rls-patterns

name: rls-patterns
description: Row Level Security patterns for database operations. Use when writing Prisma/database code, creating API routes that access data, or implementing webhooks. Enforces withUserContext, withAdminContext, or withSystemContext helpers. NEVER use direct prisma calls.
user-invocable: false
allowed-tools: Read, Grep, Glob

RLS Patterns Skill

Purpose

Enforce Row Level Security (RLS) patterns for all database operations. This skill ensures data isolation and prevents cross-user data access at the database level.

When This Skill Applies

Invoke this skill when:

• Writing any Prisma database query
• Creating or modifying API routes that access the database
• Implementing webhook handlers that write to the database
• Working with user data, payments, subscriptions, or enrollments
• Accessing admin-only tables (disputes, webhook_events)

Critical Rules

NEVER Do This

// ❌ FORBIDDEN - Direct Prisma calls bypass RLS
const user = await prisma.user.findUnique({ where: { user_id } });

// ❌ FORBIDDEN - No context set
const payments = await prisma.payments.findMany();

**ESLint will block direct Prisma calls.** See `eslint.config.mjs` for enforcement rules.

ALWAYS Do This

import {
  withUserContext,
  withAdminContext,
  withSystemContext,
} from "@/lib/rls-context";

// ✅ CORRECT - User context for user operations
const user = await withUserContext(prisma, userId, async (client) => {
  return client.user.findUnique({ where: { user_id: userId } });
});

// ✅ CORRECT - Admin context for admin operations
const webhooks = await withAdminContext(prisma, userId, async (client) => {
  return client.webhook_events.findMany();
});

// ✅ CORRECT - System context for webhooks/background tasks
const event = await withSystemContext(prisma, "webhook", async (client) => {
  return client.webhook_events.create({ data: eventData });
});

Context Helper Reference

`withUserContext(prisma, userId, callback)`

**Use for**: All user-facing operations

• User profile access
• Payment history
• Subscription management
• Course enrollments

const payments = await withUserContext(prisma, userId, async (client) => {
  return client.payments.findMany({ where: { user_id: userId } });
});

`withAdminContext(prisma, userId, callback)`

**Use for**: Admin-only operations (requires admin role in `user_roles` table)

• Viewing all webhook events
• Managing disputes
• Accessing payment failures

const disputes = await withAdminContext(prisma, adminUserId, async (client) => {
  return client.disputes.findMany();
});

`withSystemContext(prisma, contextType, callback)`

**Use for**: Webhooks and background jobs

• Stripe webhook handlers
• Clerk webhook handlers
• Background job processing

// Stripe webhook handler
await withSystemContext(prisma, "webhook", async (client) => {
  await client.payments.create({ data: paymentData });
});

Admin Pages: Force Dynamic Rendering

**CRITICAL**: Admin pages using RLS queries MUST force runtime rendering:

// app/admin/some-page/page.tsx
import { withAdminContext } from "@/lib/rls-context";
import { prisma } from "@/lib/prisma";

// REQUIRED - RLS context unavailable at build time
export const dynamic = "force-dynamic";

async function getAdminData() {
  return await withAdminContext(prisma, userId, async (client) => {
    return client.someTable.findMany();
  });
}

Without `export const dynamic = 'force-dynamic'`, Next.js will try to pre-render at build time, causing "permission denied" errors.

Protected Tables

User Data Tables (User Isolation)

| Table | Policy Type | Access | | ------------------- | -------------- | ---------------------- | | `user` | User isolation | Own data only | | `payments` | User isolation | Own payments only | | `subscriptions` | User isolation | Own subscriptions only | | `invoices` | User isolation | Own invoices only | | `course_enrollment` | User isolation | Own enrollments only |

Admin/System Tables (Role-Based)

| Table | Policy Type | Access | | --------------------- | ------------ | ------------------------ | | `webhook_events` | Admin+System | Admins and webhooks only | | `disputes` | Admin only | Admins only | | `payment_failures` | Admin only | Admins only | | `trial_notifications` | Admin+System | Admins and system only |

Testing Requirements

Always test with `{{PROJECT}}_app_user` role (not `{{PROJECT}}_user` superuser):

# Basic RLS functionality test
node scripts/test-rls-phase3-simple.js

# Comprehensive security validation
cat scripts/rls-phase4-final-validation.sql | \
  docker exec -i {{PROJECT_NAME}}-postgres-1 psql -U {{PROJECT}}_app_user -d {{PROJECT}}_dev

Common Patterns

API Route with User Context

// app/api/user/payments/route.ts
import { NextResponse } from "next/server";
import { requireAuth } from "@/lib/auth";
import { withUserContext } from "@/lib/rls-context";
import { prisma } from "@/lib/prisma";

export async function GET() {
  const { userId } = await requireAuth();

  const payments = await withUserContext(prisma, userId, async (client) => {
    return client.payments.findMany({
      where: { user_id: userId },
      orderBy: { created_at: "desc" },
    });
  });

  return NextResponse.json(payments);
}

Webhook Handler with System Context

// app/api/webhooks/stripe/route.ts
import { withSystemContext } from "@/lib/rls-context";
import { prisma } from "@/lib/prisma";

export async function POST(req: Request) {
  // Verify webhook signature first...

  await withSystemContext(prisma, "webhook", async (client) => {
    await client.webhook_events.create({
      data: {
        event_type: event.type,