Store Data

Learn how to store data for organizations in your application.

When working with organizations, you typically want to store data that belongs to each organization and can be accessed by organization members.

Database Schema

Add Organization ID to Your Tables

Add an organizationId field to tables that should be scoped to organizations:

lib/db/schema/posts.ts

import { pgTable, text, timestamp } from 'drizzle-orm/pg-core';

import { organizations } from './organizations';
import { users } from './users';

export const postsTable = pgTable('posts', {
  id: text('id').primaryKey(),
  title: text('title').notNull(),
  content: text('content').notNull(),
  authorId: text('authorId')
    .notNull()
    .references(() => users.id, { onDelete: 'cascade' }),
  organizationId: text('organizationId')
    .notNull()
    .references(() => organizations.id, { onDelete: 'cascade' }),
  createdAt: timestamp('createdAt').defaultNow().notNull(),
  updatedAt: timestamp('updatedAt').defaultNow().notNull()
});

export const postsRelations = relations(postsTable, ({ one }) => ({
  author: one(users, {
    fields: [postsTable.authorId],
    references: [users.id]
  }),
  organization: one(organizations, {
    fields: [postsTable.organizationId],
    references: [organizations.id]
  })
}));

This allows:

All members of an organization to access the posts
The author to be tracked separately
Data to be properly scoped to organizations

Creating Organization-Scoped Data

Using tRPC

Use protectedOrganizationProcedure to automatically scope data to the active organization:

trpc/routers/posts.ts

import { createTRPCRouter, protectedOrganizationProcedure } from '@/trpc/init';
import { z } from 'zod';

import { assertUserIsOrgMember } from '@/lib/auth/server';
import { db } from '@/lib/db';
import { postsTable } from '@/lib/db/schema';

export const postsRouter = createTRPCRouter({
  create: protectedOrganizationProcedure
    .input(
      z.object({
        title: z.string().min(1),
        content: z.string().min(1)
      })
    )
    .mutation(async ({ input, ctx }) => {
      // ctx.organization is guaranteed to exist
      // User membership is already verified

      const [post] = await db
        .insert(postsTable)
        .values({
          title: input.title,
          content: input.content,
          authorId: ctx.user.id,
          organizationId: ctx.organization.id
        })
        .returning();

      return post;
    })
});

Verifying Membership

If you need to verify membership manually:

lib/auth/verify-membership.ts

import { assertUserIsOrgMember } from '@/lib/auth/server';

export async function verifyMembership(organizationId: string, userId: string) {
  // This will throw an error if user is not a member
  const { organization, membership } = await assertUserIsOrgMember(
    organizationId,
    userId
  );

  return { organization, membership };
}

Querying Organization Data

List Organization Posts

Query posts for the active organization:

trpc/routers/posts.ts

list: protectedOrganizationProcedure.query(async ({ ctx }) => {
  // Automatically scoped to ctx.organization.id
  const posts = await db.query.postsTable.findMany({
    where: eq(postsTable.organizationId, ctx.organization.id),
    orderBy: desc(postsTable.createdAt),
  });

  return posts;
}),

Get Single Post

Get a single post, ensuring it belongs to the organization:

trpc/routers/posts.ts

getById: protectedOrganizationProcedure
  .input(z.object({ id: z.string() }))
  .query(async ({ input, ctx }) => {
    const post = await db.query.postsTable.findFirst({
      where: and(
        eq(postsTable.id, input.id),
        eq(postsTable.organizationId, ctx.organization.id)
      ),
    });

    if (!post) {
      throw new TRPCError({
        code: "NOT_FOUND",
        message: "Post not found",
      });
    }

    return post;
  }),

Client-Side Usage

Creating Posts

Create posts from the UI:

components/create-post-form.tsx

'use client';

import { trpc } from '@/trpc/client';
import { useForm } from 'react-hook-form';

import { useActiveOrganization } from '@/hooks/use-active-organization';

export function CreatePostForm() {
  const { activeOrganization } = useActiveOrganization();
  const utils = trpc.useUtils();
  const createPost = trpc.posts.create.useMutation({
    onSuccess: () => {
      utils.posts.list.invalidate();
    }
  });

  const onSubmit = async (data: { title: string; content: string }) => {
    if (!activeOrganization) {
      throw new Error('No active organization');
    }

    await createPost.mutateAsync(data);
  };

  return <form onSubmit={handleSubmit(onSubmit)}>{/* form fields */}</form>;
}

Listing Posts

List posts for the active organization:

components/posts-list.tsx

'use client';

import { trpc } from '@/trpc/client';

export function PostsList() {
  const { data: posts, isLoading } = trpc.posts.list.useQuery();

  if (isLoading) return <div>Loading...</div>;
  if (!posts?.length) return <div>No posts found</div>;

  return (
    <div>
      {posts.map((post) => (
        <div key={post.id}>
          <h3>{post.title}</h3>
          <p>{post.content}</p>
        </div>
      ))}
    </div>
  );
}

Updating Organization Data

Update with Permission Check

Only allow admins to update organization-scoped data:

trpc/routers/posts.ts

update: protectedOrganizationProcedure
  .input(
    z.object({
      id: z.string(),
      title: z.string().optional(),
      content: z.string().optional(),
    })
  )
  .mutation(async ({ input, ctx }) => {
    // Check if user is admin or owner
    const isAdmin =
      ctx.membership.role === "admin" || ctx.membership.role === "owner";

    const post = await db.query.postsTable.findFirst({
      where: and(
        eq(postsTable.id, input.id),
        eq(postsTable.organizationId, ctx.organization.id)
      ),
    });

    if (!post) {
      throw new TRPCError({ code: "NOT_FOUND" });
    }

    // Only author or admin can update
    if (post.authorId !== ctx.user.id && !isAdmin) {
      throw new TRPCError({
        code: "FORBIDDEN",
        message: "You can only edit your own posts or be an admin",
      });
    }

    const [updatedPost] = await db
      .update(postsTable)
      .set({
        title: input.title,
        content: input.content,
        updatedAt: new Date(),
      })
      .where(eq(postsTable.id, input.id))
      .returning();

    return updatedPost;
  }),

Best Practices

Always scope by organizationId - Never query without organization scope
Use protectedOrganizationProcedure - Automatically handles scoping
Verify membership - Always verify user is a member before operations
Check permissions - Verify roles before allowing modifications
Cascade deletes - Use onDelete: "cascade" for organization-scoped data
Index organizationId - Add database indexes on organizationId for performance