Adding a New Table

This guide walks through the complete process of adding a new table. Follow it exactly -- missing a step (especially RLS setup) can create a security vulnerability that compiles and runs without errors.

Before You Start

Decide whether the table is:

• Tenant-scoped: Data belongs to an organization and must be isolated by RLS. Most feature tables are this type.
• Global: Reference data shared across all tenants, or user-scoped data that spans organizations.

If you are unsure, it is tenant-scoped. The cost of adding unnecessary RLS is near-zero. The cost of missing it is a data breach.

Step 1: Choose the Schema File

Place the table definition in the appropriate domain file in packages/db/src/schema/:

Domain	File	When to use
Identity/Auth	auth.ts	User, session, org, membership tables
AI	ai.ts	Model registry, usage tracking, batch jobs
Research	research.ts	Plans, steps, artifacts, extractions
Search	search.ts	Chunks, embeddings, search indexes
MCP	mcp.ts	PATs, MCP-specific tables
New domain	Create newdomain.ts	When no existing file fits

If you create a new schema file, add it to packages/db/src/schema/index.ts:

export * from "./newdomain";

Step 2: Define the Table

Tenant-Scoped Table

import { relations } from "drizzle-orm";
import { index, integer, jsonb, pgTable, text } from "drizzle-orm/pg-core";

import { user } from "./auth";
import { tenantColumns, tenantPolicies } from "./base";

export const featureItem = pgTable(
  "feature_item",
  {
    id: text("id").primaryKey(),
    ...tenantColumns, // organizationId, createdAt, updatedAt
    userId: text("user_id")
      .notNull()
      .references(() => user.id),
    name: text("name").notNull(),
    status: myStatusEnum("status").notNull().default("pending"),
    metadata: jsonb("metadata").$type<Record<string, unknown>>(),
  },
  (table) => [
    ...tenantPolicies("feature_item", "organization_id"),
    index("feature_item_organizationId_idx").on(table.organizationId),
    index("feature_item_userId_idx").on(table.userId),
    index("feature_item_status_idx").on(table.status),
  ],
).enableRLS();

Checklist for tenant-scoped tables:

• ...tenantColumns spread (or manual organization_id column)
• ...tenantPolicies("table_name", "organization_id") in the constraints callback
• .enableRLS() chained on the table definition
• index("table_organizationId_idx").on(table.organizationId) for RLS performance
• Foreign keys use .references(() => parentTable.id, { onDelete: "cascade" }) or { onDelete: "set null" }
• Every FK column has an index

Global Table

import { pgTable, text, boolean } from "drizzle-orm/pg-core";
import { baseColumns } from "./base";

export const globalConfig = pgTable("global_config", {
  id: text("id").primaryKey(),
  ...baseColumns, // createdAt, updatedAt
  key: text("key").notNull().unique(),
  value: text("value").notNull(),
  active: boolean("active").notNull().default(true),
});

No tenantPolicies, no .enableRLS(), no organization_id.

Step 3: Define Drizzle Relations

Add relation definitions in the same file, after the table definitions:

export const featureItemRelations = relations(featureItem, ({ one }) => ({
  user: one(user, {
    fields: [featureItem.userId],
    references: [user.id],
  }),
}));

If the new table is a child of an existing table, update the parent's relations to include the new many() reference:

// In the parent's relation definition, add:
featureItems: many(featureItem),

See Relationships for detailed conventions.

Step 4: Export from Index

Add the export to packages/db/src/schema/index.ts if you created a new file:

export * from "./newdomain";

If you added to an existing file, the barrel export already covers it.

Step 5: Generate the Migration

pnpm db:generate

This produces a new .sql file in packages/db/src/migrations/. Review it carefully:

• Verify CREATE TABLE has the expected columns and types
• Verify ALTER TABLE ... ENABLE ROW LEVEL SECURITY appears for tenant-scoped tables
• Verify CREATE POLICY statements match the four CRUD policies
• Verify CREATE INDEX statements exist for all declared indexes
• Verify foreign key constraints reference the correct tables

Manual SQL Additions

Some features require raw SQL that Drizzle Kit cannot generate:

• GRANT statements: If this is the first table with RLS, verify that 0001_setup_role_grants.sql has run. The ALTER DEFAULT PRIVILEGES in that migration automatically grants the authenticated role access to new tables.
• Generated columns: tsvector columns for full-text search must be added as raw SQL appended to the generated migration:

  ALTER TABLE feature_item
    ADD COLUMN search_vector tsvector
    GENERATED ALWAYS AS (to_tsvector('english', name)) STORED;
  CREATE INDEX feature_item_search_idx ON feature_item USING gin(search_vector);

• HNSW indexes: pgvector indexes must be added as raw SQL:

  CREATE INDEX document_chunk_embedding_idx
    ON document_chunk USING hnsw (embedding halfvec_cosine_ops);

Step 6: Apply the Migration

pnpm db:migrate

This applies the migration to your local database. Verify with Drizzle Studio:

pnpm db:studio

Step 7: Add Seed Data (If Needed)

For reference data, add to or create a seed file in packages/db/src/seeds/ and wire it into the appropriate tier. See Seeding for the full pattern.

For development data, add realistic sample rows that exercise the table's features (multiple statuses, edge cases, relationships to existing seed data).

Step 8: Update Non-Tenant Table Documentation

If the new table is global (no RLS), add it to the non-tenant table list in packages/db/CLAUDE.md:

## Non-Tenant Tables

These intentionally skip RLS: `user`, `session`, `account`, `verification`,
`organization`, `ai_model`, `ai_model_pricing`, `personal_access_token`,
`your_new_table` (reason it skips RLS).

Step 9: Run Checks

pnpm ci:check

This runs formatting, linting, type checking, tests, and doc-update detection. Address any failures before committing.

Quick Reference: Complete Tenant-Scoped Table Checklist

1. Define table with tenantColumns, tenantPolicies(), .enableRLS(), and indexes
2. Define Drizzle relations (both sides of each relationship)
3. Export from index.ts if new file
4. pnpm db:generate and review migration SQL
5. pnpm db:migrate to apply locally
6. Add seed data if needed
7. pnpm ci:check to verify everything passes

Common Mistakes

Forgetting .enableRLS(): The table will have policies defined but RLS not enabled. PostgreSQL ignores policies when RLS is disabled, so the table is completely open. This compiles, runs, and passes single-tenant tests.

Using tenantColumns but not tenantPolicies(): The table has an organization_id column but no policies. All rows are visible to all tenants.

Using tenantPolicies() with the wrong column name: The default parameter is "tenant_id", not "organization_id". Always pass the column name explicitly: tenantPolicies("my_table", "organization_id").

Missing FK index: PostgreSQL does not automatically index foreign key columns. Without an explicit index, JOIN and CASCADE operations scan the full table.

Importing bare db in a router: If your new table is tenant-scoped but you test it with the bare db connection, the test will pass (superuser bypasses RLS) but production code using withTenantContext() may behave differently due to the transaction scope.