Tenant Store CRUD Operations

Introduction

In our last lesson, we laid the architectural groundwork for our multi-tenant system by designing a robust data model. We established the core tenants, users, and memberships tables and decided to use a JSONB column for flexible tenant-specific configurations. This design is the blueprint for ensuring every piece of data in our system has a clear owner.

Now, it's time to move from blueprint to construction. This lesson focuses on implementing a TenantStore—a dedicated service with CRUD (Create, Read, Update, Delete) operations for managing our tenants. You will build a set of API endpoints using Node.js and Express that bring our data model to life, allowing us to programmatically create, retrieve, and modify tenant records. As a seasoned developer, you'll recognize this as a foundational step in building any scalable SaaS application.

This service is a central component, often called "Tenant Management" or "Onboarding," as shown in the high-level architecture diagram below.

The Multi-Tenancy Model in Practice

As a quick refresher, we are implementing the shared database, shared schema model. All our tenants' data will coexist in the same tables, with a tenant_id column ensuring strict data separation. This is the most common and cost-effective approach for modern SaaS applications.

Building the Tenant Store CRUD API

We will now build the API endpoints to manage our tenants table. For this task, the video "Build a CRUD API with TypeScript, Express, MongoDB, Zod and Jest" by Coding Garden is an exceptionally practical guide. While it uses MongoDB, the principles of setting up Express routes, handlers, and using Zod for validation are directly applicable. We will adapt its structure for our PostgreSQL database.

Your experience with Node.js and backend development will make this process very familiar. We'll structure our API with clear separation of concerns: routes, validation schemas, and request handlers.

1. Project Setup and Database Connection

First, let's establish the connection to our PostgreSQL database. A common practice is to create a dedicated module that initializes and exports the database client, making it available throughout the application.

The following segment from the video demonstrates this pattern. Although it's for MongoDB, the concept of creating a reusable database client instance is the same.

Here's how you might implement a similar connection module for PostgreSQL using the pg library:

// src/db.ts
import { Pool } from 'pg';
import dotenv from 'dotenv';

dotenv.config();

const pool = new Pool({
    connectionString: process.env.DATABASE_URL,
});

export default pool;

This snippet creates a connection pool that our application handlers can use to query the database.

2. Schema Validation with Zod

Before processing any incoming data, we must validate it. Zod is a powerful TypeScript-first schema declaration and validation library that helps ensure data integrity. We'll define Zod schemas that correspond to our tenants table structure from the previous lesson.

// src/schemas/tenant.schema.ts
import { z } from 'zod';

export const tenantConfigurationSchema = z.object({
  // Example configuration fields
  maxUsers: z.number().optional(),
  features: z.array(z.string()).optional(),
  customDomain: z.string().optional(),
  apiKeys: z.record(z.string()).optional(), // e.g., { "STRIPE_API_KEY": "sk_..." }
});

export const createTenantSchema = z.object({
  body: z.object({
    name: z.string({ required_error: 'Name is required' }).min(2, 'Name must be at least 2 characters'),
    planType: z.enum(['free', 'pro', 'enterprise']).default('free'),
    configurations: tenantConfigurationSchema.optional(),
  }),
});

export const updateTenantSchema = z.object({
  body: z.object({
    name: z.string().min(2, 'Name must be at least 2 characters').optional(),
    planType: z.enum(['free', 'pro', 'enterprise']).optional(),
    configurations: tenantConfigurationSchema.optional(),
  }),
  params: z.object({
    id: z.string().uuid({ message: 'Invalid UUID' }),
  }),
});

export type CreateTenantInput = z.infer<typeof createTenantSchema>['body'];
export type UpdateTenantInput = z.infer<typeof updateTenantSchema>;

These schemas define the expected shape and types for creating and updating a tenant, including the flexible configurations object.

3. Implementing the CRUD Handlers and Routes

Now, let's build the Express routes and the handler functions for each CRUD operation. We will create an express.Router for /tenants and implement handlers for POST, GET, PUT, and DELETE.

The following video segments provide a complete, real-time walkthrough of this process. As you watch, mentally substitute the MongoDB commands with the equivalent PostgreSQL queries you would write using the pg library.

Create a Tenant (POST /tenants)

This endpoint will handle new tenant creation, which corresponds to the "Tenant Onboarding" process discussed in real-world systems.

An equivalent handler for PostgreSQL might look like this:

// src/handlers/tenant.handler.ts
import { Request, Response } from 'express';
import pool from '../db';
import { CreateTenantInput } from '../schemas/tenant.schema';

export async function createTenantHandler(req: Request<{}, {}, CreateTenantInput>, res: Response) {
    const { name, planType, configurations } = req.body;
    try {
        const result = await pool.query(
            'INSERT INTO tenants (name, plan_type, configurations) VALUES ($1, $2, $3) RETURNING *',
            [name, planType, JSON.stringify(configurations || {})]
        );
        res.status(201).json(result.rows[0]);
    } catch (error) {
        // Handle potential errors, e.g., unique constraint violation
        res.status(500).json({ error: 'Internal Server Error' });
    }
}

Read Tenants (GET /tenants and GET /tenants/:id)

These endpoints are for retrieving a list of all tenants or a single tenant by their ID.

Update a Tenant (PUT /tenants/:id)

This endpoint allows for modification of a tenant's data, such as their plan or their JSONB configurations.

Delete a Tenant (DELETE /tenants/:id)

Finally, this endpoint handles the removal of a tenant.

By following these patterns, you can assemble a complete tenants.router.ts file that wires up these handlers to the Express application, providing a fully functional administrative API for managing tenants.

The Importance of Tenant Isolation at the API Layer

The CRUD API we've just designed is an administrative interface. A system administrator would use it to manage tenants. In later lessons, when we build the main API that serves tenant users, we will introduce another layer of middleware. This middleware will resolve the tenant context from each incoming request (e.g., from a JWT or subdomain) and ensure that all subsequent database queries are automatically scoped to that tenant's tenant_id.

The article "Building a Multi-Tenant AI Agent Platform" provides excellent insight into this next step, which we will tackle soon.

For now, our focus remains on the foundational CRUD service for managing the tenants themselves.

Conclusion

In this lesson, you've translated our conceptual tenant data model into a concrete and functional TenantStore API. By implementing the full range of CRUD operations, you've built the essential administrative service for managing tenants in our multi-tenant AI infrastructure.

Key Takeaways:

• CRUD Implementation: You've implemented a standard RESTful API for tenants using Express, with handlers for creating, reading, updating, and deleting records.
• Schema Validation: Using Zod to validate incoming requests is a critical practice for ensuring data integrity and application security.
• Database Interaction: You've seen how to structure request handlers to interact with a PostgreSQL database, adapting patterns from a real-world tutorial.
• Administrative vs. Tenant-Scoped APIs: You understand the distinction between the administrative API for managing tenants and the future tenant-scoped API that will serve end-users.

With the TenantStore in place, we can now create and manage the customers of our service. The next logical step is to define what resources each of these tenants can access. In the upcoming lesson, we will do just that by creating mappings between tenants and the agents available to them, taking our first step into tenant-specific resource management.