Rls Patterns

RLS Patterns Skill

TEMPLATE: This skill uses {{PLACEHOLDER}} tokens. Replace with your project values before use.

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

• Writing any database query (ORM or raw SQL)
• 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

Critical Rules

NEVER Do This

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

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

Linting will block direct DB calls. See linting configuration for enforcement rules.

ALWAYS Do This

import {
  withUserContext,
  withAdminContext,
  withSystemContext,
} from "{{RLS_IMPORT}}";

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

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

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

Context Helper Reference

withUserContext(db, userId, callback)

Use for: All user-facing operations

• User profile access
• Payment history
• Subscription management
• Enrollments and personal data

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

withAdminContext(db, userId, callback)

Use for: Admin-only operations (requires admin role)

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

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

withSystemContext(db, contextType, callback)

Use for: Webhooks and background jobs

• Webhook handlers (Stripe, auth provider, etc.)
• Background job processing
• System-initiated operations

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

Admin Pages: Force Dynamic Rendering

CRITICAL: Admin pages using RLS queries MUST force runtime rendering (in Next.js):

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

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

Without forced dynamic rendering, frameworks may try to pre-render at build time, causing "permission denied" errors.

Protected Tables

User Data Tables (User Isolation)

Admin/System Tables (Role-Based)

Testing Requirements

Always test with the application-level DB user role (not a superuser):

# Basic RLS functionality test
{{RLS_TEST_COMMAND}}

# Comprehensive security validation
{{RLS_VALIDATION_COMMAND}}

Common Patterns

API Route with User Context

import { NextResponse } from "next/server";
import { requireAuth } from "{{AUTH_IMPORT}}";
import { withUserContext } from "{{RLS_IMPORT}}";
import { db } from "{{DB_IMPORT}}";

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

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

  return NextResponse.json(payments);
}

Webhook Handler with System Context

import { withSystemContext } from "{{RLS_IMPORT}}";
import { db } from "{{DB_IMPORT}}";

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

  await withSystemContext(db, "webhook", async (client) => {
    await client.webhook_events.create({
      data: {
        event_type: event.type,
        payload: event.data,
        processed_at: new Date(),
      },
    });
  });

  return new Response("OK", { status: 200 });
}

Authoritative References

• RLS Implementation Guide: docs/database/RLS_IMPLEMENTATION_GUIDE.md
• RLS Policy Catalog: docs/database/RLS_POLICY_CATALOG.md
• Migration SOP: docs/database/RLS_DATABASE_MIGRATION_SOP.md
• Linting Rules: Check linting config for direct DB call enforcement
• RLS Context Helpers: {{RLS_CONTEXT_FILE}}