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.

Current Tech Stack

• Database: PostgreSQL (production) via pg (node-postgres) -- direct SQL queries
• Dev Database: SQLite via SQLITE_DB_PATH for local development
• Connection: src/db/connection.ts exports query() and getClient()
• Migrations: Raw SQL files in src/db/migrations/ (auto-run on startup)
• No ORM: Direct SQL queries only (no ORM)

When This Skill Applies

• Writing any database query (raw SQL via query() or getClient())
• Creating or modifying Express API routes that access the database
• Implementing webhook handlers that write to the database
• Working with user data, tokens, or planning sessions
• Accessing admin-only tables

Critical Rules

NEVER Do This

// FORBIDDEN - Unparameterized queries are SQL injection risks
const result = await query(`SELECT * FROM linear_tokens WHERE org_id = '${orgId}'`);

// FORBIDDEN - Direct queries without considering data isolation
const tokens = await query('SELECT * FROM linear_tokens');

ALWAYS Do This

import { query, getClient } from '{{DB_CONNECTION_IMPORT}}';

// CORRECT - Parameterized queries
const result = await query(
  'SELECT * FROM linear_tokens WHERE organization_id = $1',
  [orgId]
);

// CORRECT - Transactions with getClient() for multi-step operations
const client = await getClient();
try {
  await client.query('BEGIN');
  await client.query(
    'INSERT INTO planning_sessions (id, org_id, status) VALUES ($1, $2, $3)',
    [sessionId, orgId, 'active']
  );
  await client.query('COMMIT');
} catch (error) {
  await client.query('ROLLBACK');
  throw error;
} finally {
  client.release();
}

RLS Architecture: Current State and Target