How to implement row-level security with WorkOS FGA and Postgres: tutorial and code

This tutorial demonstrates how to implement row-level security in a Next.js application using WorkOS Fine-Grained Authorization (FGA) integrated with Postgres. The system is designed to ensure that users only see tickets they're authorized to view, with different permissions for admins, support agents, and customers. It integrates traditional database relationships with FGA's permission model using the Prisma ORM to interact with the Postgres database. The system establishes a basic relationship schema between organizations, users, and tickets, and then maps these relationships to FGA permissions. Two common patterns for implementing row-level security are discussed: pre-filtering and post-filtering. Pre-filtering is recommended and involves querying WorkOS FGA first to get authorized resource IDs, which are then used in the SQL WHERE clause. The system includes API tests that verify its permission model works correctly, and it provides a flexible permission modeling approach with built-in inheritance and relationship rules.