Chapter 6: Postgres Schema Design - Users, Orgs, and Roles

Theoretical Foundations

At its heart, a SaaS application is a multi-tenant system. Unlike a single-user application where data ownership is trivial, a SaaS must serve multiple distinct customers (tenants) within a single application instance and database. The foundational challenge is ensuring that data isolation is absolute. A user from Organization A must never, under any circumstances, access or even be aware of the data belonging to Organization B. This is not merely an application-level concern; it is a data-level guarantee that must be enforced at the lowest possible layer: the database itself.

To achieve this, we design a schema that models three core entities: Users, Organizations, and Roles. Think of this as the architectural blueprint for a high-security apartment building. The Users are the residents, the Organizations are the individual apartment units, and the Roles are the keys that grant access to specific units and rooms within them. Without a robust key management system (Roles), a resident could potentially wander into another's apartment. Our goal is to design a system where the building's security (the database) automatically checks every key before granting access.

This design directly builds upon concepts from previous chapters, particularly the Authentication flow. When a user logs in, we don't just create a generic session; we establish a secure context. This context must include not just who the user is, but which organization they are acting on behalf of and what permissions they hold within that organization. The schema we design here is the data structure that makes that session context meaningful and enforceable.

The Analogy: A Corporate Office Building

Imagine a large corporate office building (our SaaS application). This building houses multiple companies (Organizations), each with its own employees (Users).

The Building (The Database): The entire physical structure. All companies share the same building infrastructure.
A Company (An Organization): A specific company, like "Acme Corp" or "Globex Inc." Each company has its own office space, its own projects, and its own confidential documents.
An Employee (A User): A person who works in the building. An employee can work for one company, or they might be a consultant who works for multiple companies.
A Keycard (A Role): A keycard that grants access. A keycard for Acme Corp might open the main office door and the engineering department, but not the finance department. A different keycard for Globex Inc. might have different permissions.

The critical rule is: An employee with an Acme Corp keycard cannot enter a Globex Inc. office. This rule isn't enforced by the employee's good intentions; it's enforced by the building's electronic locks. This is the principle of Row Level Security (RLS). We will configure the database (the building's security system) to automatically check the user's keycard (their role and organization) before allowing any operation (reading, writing, deleting) on any data (the offices and documents).

The Data Model: Users, Organizations, and Roles

Let's break down the schema entities in detail.

Organizations (organizations table): This is the root tenant entity. Every piece of data in the application (with very few exceptions, like global admin logs) must belong to an organization. An organization has attributes like name, billing_details, and a subscription_status. This table is the top-level container for all tenant-specific data.
Users (users table): This table stores individual user profiles. It's crucial to distinguish between the authentication identity (handled by a provider like Auth0 or Supabase Auth) and the application profile. The users table typically holds the user's name, avatar, and preferences. It does not store password hashes; that's the job of the authentication provider. The link between a user and an organization is not direct; it's mediated through a role.
Roles and Permissions (roles table, permissions table, and a junction table): This is the most complex part of the model. A "Role" is a named collection of permissions (e.g., "Admin," "Editor," "Viewer"). A user can have different roles in different organizations. For instance, Alice is an "Admin" in "Acme Corp" but a "Viewer" in "Globex Inc." This is a many-to-many relationship: a user can belong to many organizations, and an organization has many users, with a specific role defining the relationship.

To model this, we typically use a junction table, often called organization_members or user_roles. This table links a user_id to an organization_id and specifies a role_id.

A junction table named organization_members visually connects user_id, organization_id, and role_id to establish a many-to-many relationship between users and organizations. — A junction table named `organization_members` visually connects `user_id`, `organization_id`, and `role_id` to establish a many-to-many relationship between users and organizations.

The Enforcement Mechanism: Row Level Security (RLS)

This is the "how" of data isolation. RLS is a feature of PostgreSQL (and other enterprise databases) that allows you to define policies on tables. These policies are like rules that are automatically applied to every query. The database engine evaluates the policy before executing the query. If the policy's condition is not met for a given row, that row is filtered out, as if it never existed.

How it works under the hood: When a query is executed, the database's query planner intercepts it. It then appends the RLS policy's condition to the WHERE clause of the query. For example, if you run SELECT * FROM projects, and there's an RLS policy that says projects.organization_id = current_setting('app.current_org_id')::uuid, the database actually executes SELECT * FROM projects WHERE projects.organization_id = current_setting('app.current_org_id')::uuid.

The key here is current_setting. This is a PostgreSQL function that retrieves a session-level configuration parameter. Our application, upon user login and organization selection, will set this parameter for the duration of the database connection. This is how we pass the secure context from our application layer to the database layer.

Analogy: The RLS policy is the building's security guard. The guard doesn't know you personally. They only know the rule: "Only individuals with a keycard for this specific office can enter." When you try to enter an office (run a query), the guard (the database) checks your keycard (the session variable) against the office's ownership (the organization_id on the data row). If they don't match, you are denied entry (the row is not returned).

Integrating with the Payment Stack

The final piece of the puzzle is connecting this data model to the business logic of a SaaS: subscriptions and billing.

The organizations table is the natural place to store subscription state. A column like subscription_status can hold values like 'active', 'past_due', 'canceled'. This state is a direct reflection of the information from our payment provider (e.g., Stripe).

Why is this integration critical? Because subscription state dictates feature entitlement. A user in an organization with an 'active' subscription should have access to premium features, while a user in a 'canceled' organization should be restricted.

This is where the concept of billing entitlements comes in. Entitlements are the specific features or limits granted based on a subscription plan. For example:

Free Plan: Can create 5 projects.
Pro Plan: Can create unlimited projects and use AI features.
Enterprise Plan: All Pro features plus SSO and dedicated support.

Our application logic must check these entitlements before allowing an action. For example, before creating a new project, the application would query the user's current organization's subscription_status. If the status is 'free' and the project count is already 5, the application would block the creation.

This creates a powerful synergy:

RLS ensures data isolation at the database level.
The Schema (Users, Orgs, Roles) provides the structure for multi-tenancy and access control.
The Payment Integration injects a business state (subscription_status) into the schema, which the application logic uses to enforce feature gating and entitlements.

By combining these three elements, we create a robust, secure, and scalable foundation for an AI-ready SaaS boilerplate. The database is no longer just a passive store of data; it is an active participant in enforcing the business's security and billing rules.

Basic Code Example

In the context of a SaaS application, a Delegation Strategy is often used by a Supervisor Node (e.g., an AI orchestrator or a backend API) to assign tasks to a Worker Agent (e.g., a specific microservice or a background job processor). To ensure robustness, the parameters defining this delegation must be strictly validated. This is where Zod Schemas come in: they define the structure of the data at runtime, preventing malformed requests from propagating through the system.

The following example demonstrates a "Hello World" level implementation of a Zod schema used to validate a delegation request in a Node.js/TypeScript environment. We will simulate a scenario where an API endpoint receives a request to delegate a task (e.g., "Generate a vector embedding") to a specific worker.

The Core Concept

We will create a simple API handler (simulated) that:

Receives a JSON payload.
Validates it against a Zod schema (DelegationRequestSchema).
If valid, it constructs a typed object ready for processing.
If invalid, it returns a structured error response.

This ensures that the "Supervisor" never passes invalid instructions to the "Worker," maintaining system stability.

// file: delegation-example.ts

import { z } from 'zod';

// ============================================================================
// 1. DEFINING THE ZOD SCHEMA
// ============================================================================

/**

 * Represents the expected shape of a delegation request.
 * This schema enforces runtime validation for the incoming payload.
 * 
 * @schema
 * @property {string} taskId - A unique identifier for the task (UUID format).
 * @property {string} workerType - The specific worker agent to handle the task (e.g., 'embedding-generator').
 * @property {object} payload - The data required by the worker.
 * @property {number} priority - A numeric priority level (1-10).
 */
const DelegationRequestSchema = z.object({
  taskId: z.string().uuid({ message: "Task ID must be a valid UUID." }),
  workerType: z.enum(['embedding-generator', 'data-processor', 'report-builder']),
  payload: z.record(z.unknown()), // Flexible object for arbitrary data
  priority: z.number().min(1).max(10).default(5),
});

// Infer the TypeScript type directly from the Zod schema for compile-time safety
type DelegationRequest = z.infer<typeof DelegationRequestSchema>;

// ============================================================================
// 2. THE DELEGATION LOGIC (SIMULATED SUPERVISOR NODE)
// ============================================================================

/**

 * Simulates the Supervisor Node processing a delegation request.
 * 
 * @param input - The raw JSON object received from the API.
 * @returns A structured result indicating success or failure.
 */
function processDelegation(input: unknown) {
  // Step A: Validate the input against the Zod schema
  const validationResult = DelegationRequestSchema.safeParse(input);

  // Step B: Handle validation failure
  if (!validationResult.success) {
    // Map Zod errors to a clean API response format
    const formattedErrors = validationResult.error.flatten().fieldErrors;

    return {
      status: 'error' as const,
      message: 'Invalid delegation request.',
      errors: formattedErrors,
    };
  }

  // Step C: Extract typed data (TypeScript now knows the shape is correct)
  const { data } = validationResult;

  // Step D: Construct the delegation instruction for the Worker
  const delegationInstruction = {
    id: data.taskId,
    target: data.workerType,
    data: data.payload,
    urgency: data.priority,
    timestamp: new Date().toISOString(),
  };

  // In a real app, we would send this instruction to a queue (e.g., Redis/BullMQ)
  console.log(`[Supervisor] Delegating task ${data.taskId} to ${data.workerType}`);

  return {
    status: 'success' as const,
    message: 'Task delegated successfully.',
    instruction: delegationInstruction,
  };
}

// ============================================================================
// 3. EXAMPLE USAGE (SIMULATING API CALLS)
// ============================================================================

// Example 1: Valid Request
const validPayload = {
  taskId: '550e8400-e29b-41d4-a716-446655440000',
  workerType: 'embedding-generator',
  payload: { text: 'Hello World' },
  priority: 8,
};

// Example 2: Invalid Request (Wrong UUID format, missing priority)
const invalidPayload = {
  taskId: 'not-a-uuid',
  workerType: 'unknown-worker',
  payload: null,
};

// Execute simulation
console.log('--- Processing Valid Request ---');
console.log(JSON.stringify(processDelegation(validPayload), null, 2));

console.log('\n--- Processing Invalid Request ---');
console.log(JSON.stringify(processDelegation(invalidPayload), null, 2));

Detailed Line-by-Line Explanation

1. Importing and Defining the Schema

import { z } from 'zod';: Imports the Zod library. Zod is a TypeScript-first schema declaration and validation library.
const DelegationRequestSchema = z.object({...}): Defines the shape of the expected data object.
- taskId: z.string().uuid(...): Ensures the taskId is a string and strictly matches the UUID format. If the input is not a valid UUID, Zod throws a specific error message.
- workerType: z.enum([...]): Restricts the workerType to a specific list of allowed strings. This acts as a whitelist, preventing typos or unauthorized worker types.
- payload: z.record(z.unknown()): Allows an object with string keys and values of any type (unknown). This is useful for flexible data payloads that vary by worker type.
- priority: z.number().min(1).max(10).default(5): Ensures priority is a number between 1 and 10. If omitted in the input, it defaults to 5.
type DelegationRequest = z.infer<typeof DelegationRequestSchema>: This is the magic of Zod. It extracts the TypeScript type definition directly from the runtime schema. Now, if you change the schema (e.g., add a new field), the TypeScript type updates automatically. No manual interface duplication is needed.

2. The Delegation Logic Function

function processDelegation(input: unknown): The function accepts unknown because we cannot trust external data (e.g., from an API request) until it has been validated.
DelegationRequestSchema.safeParse(input): This is the core validation method.
- Unlike .parse() (which throws an exception on failure), safeParse returns a discriminated union: either { success: true, data: T } or { success: false, error: ZodError }.
- This prevents the Node.js process from crashing on bad input.
if (!validationResult.success): Checks if validation failed.
- validationResult.error.flatten().fieldErrors: Zod provides a method to transform the complex error object into a simpler format, grouping errors by field name. This is ideal for returning to a frontend form or logging.
const { data } = validationResult;: If validation succeeded, we extract the data object. TypeScript now knows this object conforms to the DelegationRequest type, allowing safe access to properties like data.taskId without type guards.
delegationInstruction: We construct a new object formatted specifically for the Worker Agent. This decouples the internal API schema from the external worker schema.

3. Execution Simulation

validPayload: Contains all required fields in the correct format. The function will return a success status.
invalidPayload: Contains a malformed UUID and an invalid workerType. The function will catch these errors and return a structured error object.

Visualizing the Data Flow

The following diagram illustrates how the Zod schema acts as a gatekeeper between the incoming request and the internal business logic.

A Zod schema validates incoming request data, allowing only valid data to pass through to the internal business logic while catching and returning structured errors for invalid data.

Common Pitfalls in TypeScript & Zod

When implementing delegation strategies and runtime validation, developers often encounter these specific issues:

Over-reliance on any or Ignoring unknown:
- The Issue: Defining the input parameter as any defeats the purpose of TypeScript. You lose compile-time safety and intellisense.
- The Fix: Always type input from external sources as unknown. Force yourself to validate it with Zod before narrowing the type.
Async/Await Loops in High-Volume Delegation:
- The Issue: When the Supervisor Node delegates thousands of tasks, using await inside a forEach or synchronous loop can block the event loop, causing Vercel timeouts or slow API responses.
- The Fix: Use Promise.all() for concurrent delegation or push tasks to a message queue (like Redis/BullMQ) immediately after validation, letting background workers handle the processing.
Schema Hallucination (Drift):
- The Issue: The Zod schema is defined in one file, but the TypeScript types are manually written in another. Over time, they drift apart, leading to runtime errors where valid TypeScript code fails because the runtime validation rejects it.
- The Fix: Always use z.infer<typeof MySchema> to generate types. Never manually write an interface that matches a Zod schema.
Circular Dependencies:
- The Issue: In complex SaaS apps, schemas might reference each other (e.g., an OrgSchema references a UserSchema). If imported incorrectly, this can cause runtime errors or fail to compile.
- The Fix: Centralize schemas in a dedicated schemas.ts file or use lazy evaluation with z.lazy(() => OtherSchema) for recursive structures.
Ignoring Validation Errors in Production:
- The Issue: Logging the entire Zod error object (which can be massive) in production logs can consume storage and expose sensitive data if the payload contains PII.
- The Fix: Sanitize error logs. Only log the error.flatten().fieldErrors or a generic message. Never log the raw input in production environments.

The chapter continues with advanced code, exercises and solutions with analysis, you can find them on the ebook on Leanpub.com or Amazon

Loading knowledge check...

Code License: All code examples are released under the MIT License. Github repo.

All textual explanations, original diagrams, and illustrations are the intellectual property of the author. To support the maintenance of this site via AdSense, please read this content exclusively online. Copying, redistribution, or reproduction is strictly prohibited.