grey-haven-data-modeling

Compare original and translation side by side

🇺🇸

Original

English

🇨🇳

Translation

Chinese

Grey Haven Data Modeling Standards

Grey Haven数据建模标准

Design database schemas for Grey Haven Studio's multi-tenant SaaS applications using SQLModel (FastAPI) and Drizzle ORM (TanStack Start) with PostgreSQL and RLS.

使用SQLModel（FastAPI）、Drizzle ORM（TanStack Start）结合PostgreSQL和RLS，为Grey Haven Studio的多租户SaaS应用设计数据库模式。

Multi-Tenant Principles

多租户原则

CRITICAL: Every Table Requires tenant_id

⚠️ 关键要求：每张表都必须包含tenant_id

typescript

// ✅ CORRECT - Drizzle
export const users = pgTable("users", {
  id: uuid("id").primaryKey().defaultRandom(),
  tenant_id: uuid("tenant_id").notNull(), // REQUIRED!
  created_at: timestamp("created_at").defaultNow().notNull(),
  updated_at: timestamp("updated_at").defaultNow().notNull(),
  // ... other fields
});

python

undefined

typescript

// ✅ CORRECT - Drizzle
export const users = pgTable("users", {
  id: uuid("id").primaryKey().defaultRandom(),
  tenant_id: uuid("tenant_id").notNull(), // REQUIRED!
  created_at: timestamp("created_at").defaultNow().notNull(),
  updated_at: timestamp("updated_at").defaultNow().notNull(),
  // ... other fields
});

python

undefined

✅ CORRECT - SQLModel

class User(SQLModel, table=True): tablename = "users"

id: UUID = Field(default_factory=uuid4, primary_key=True)
tenant_id: UUID = Field(foreign_key="tenants.id", index=True) # REQUIRED!
created_at: datetime = Field(default_factory=datetime.utcnow)
updated_at: datetime = Field(default_factory=datetime.utcnow)
# ... other fields

undefined

class User(SQLModel, table=True): tablename = "users"

id: UUID = Field(default_factory=uuid4, primary_key=True)
tenant_id: UUID = Field(foreign_key="tenants.id", index=True) # REQUIRED!
created_at: datetime = Field(default_factory=datetime.utcnow)
updated_at: datetime = Field(default_factory=datetime.utcnow)
# ... other fields

undefined

Naming Conventions

命名规范

ALWAYS use snake_case (never camelCase):

typescript

// ✅ CORRECT
email_address: text("email_address")
created_at: timestamp("created_at")
is_active: boolean("is_active")
tenant_id: uuid("tenant_id")

// ❌ WRONG
emailAddress: text("emailAddress")  // WRONG!
createdAt: timestamp("createdAt")   // WRONG!

始终使用snake_case（绝不要用camelCase）：

typescript

// ✅ CORRECT
email_address: text("email_address")
created_at: timestamp("created_at")
is_active: boolean("is_active")
tenant_id: uuid("tenant_id")

// ❌ WRONG
emailAddress: text("emailAddress")  // WRONG!
createdAt: timestamp("createdAt")   // WRONG!

Standard Fields (Required on All Tables)

标准字段（所有表必填）

typescript

// Every table should have:
id: uuid("id").primaryKey().defaultRandom()
created_at: timestamp("created_at").defaultNow().notNull()
updated_at: timestamp("updated_at").defaultNow().notNull()
tenant_id: uuid("tenant_id").notNull()
deleted_at: timestamp("deleted_at") // For soft deletes (optional)

typescript

// Every table should have:
id: uuid("id").primaryKey().defaultRandom()
created_at: timestamp("created_at").defaultNow().notNull()
updated_at: timestamp("updated_at").defaultNow().notNull()
tenant_id: uuid("tenant_id").notNull()
deleted_at: timestamp("deleted_at") // For soft deletes (optional)

Core Tables

核心表

1. Tenants Table (Root)

1. 租户表（根表）

typescript

// Drizzle
export const tenants = pgTable("tenants", {
  id: uuid("id").primaryKey().defaultRandom(),
  name: text("name").notNull(),
  slug: text("slug").notNull().unique(),
  is_active: boolean("is_active").default(true).notNull(),
  created_at: timestamp("created_at").defaultNow().notNull(),
  updated_at: timestamp("updated_at").defaultNow().notNull(),
});

python

undefined

typescript

// Drizzle
export const tenants = pgTable("tenants", {
  id: uuid("id").primaryKey().defaultRandom(),
  name: text("name").notNull(),
  slug: text("slug").notNull().unique(),
  is_active: boolean("is_active").default(true).notNull(),
  created_at: timestamp("created_at").defaultNow().notNull(),
  updated_at: timestamp("updated_at").defaultNow().notNull(),
});

python

undefined

SQLModel

class Tenant(SQLModel, table=True): tablename = "tenants"

id: UUID = Field(default_factory=uuid4, primary_key=True)
name: str = Field(max_length=255)
slug: str = Field(max_length=100, unique=True)
is_active: bool = Field(default=True)
created_at: datetime = Field(default_factory=datetime.utcnow)
updated_at: datetime = Field(default_factory=datetime.utcnow)

undefined

class Tenant(SQLModel, table=True): tablename = "tenants"

id: UUID = Field(default_factory=uuid4, primary_key=True)
name: str = Field(max_length=255)
slug: str = Field(max_length=100, unique=True)
is_active: bool = Field(default=True)
created_at: datetime = Field(default_factory=datetime.utcnow)
updated_at: datetime = Field(default_factory=datetime.utcnow)

undefined

2. Users Table (With Tenant Isolation)

2. 用户表（带租户隔离）

typescript

// Drizzle
export const users = pgTable("users", {
  id: uuid("id").primaryKey().defaultRandom(),
  tenant_id: uuid("tenant_id").notNull(),
  email_address: text("email_address").notNull().unique(),
  full_name: text("full_name").notNull(),
  is_active: boolean("is_active").default(true).notNull(),
  created_at: timestamp("created_at").defaultNow().notNull(),
  updated_at: timestamp("updated_at").defaultNow().notNull(),
  deleted_at: timestamp("deleted_at"),
});

// Index for tenant_id
export const usersTenantIndex = index("users_tenant_id_idx").on(users.tenant_id);

python

undefined

typescript

// Drizzle
export const users = pgTable("users", {
  id: uuid("id").primaryKey().defaultRandom(),
  tenant_id: uuid("tenant_id").notNull(),
  email_address: text("email_address").notNull().unique(),
  full_name: text("full_name").notNull(),
  is_active: boolean("is_active").default(true).notNull(),
  created_at: timestamp("created_at").defaultNow().notNull(),
  updated_at: timestamp("updated_at").defaultNow().notNull(),
  deleted_at: timestamp("deleted_at"),
});

// Index for tenant_id
export const usersTenantIndex = index("users_tenant_id_idx").on(users.tenant_id);

python

undefined

SQLModel

class User(SQLModel, table=True): tablename = "users"

id: UUID = Field(default_factory=uuid4, primary_key=True)
tenant_id: UUID = Field(foreign_key="tenants.id", index=True)
email_address: str = Field(max_length=255, unique=True)
full_name: str = Field(max_length=255)
is_active: bool = Field(default=True)
created_at: datetime = Field(default_factory=datetime.utcnow)
updated_at: datetime = Field(default_factory=datetime.utcnow)
deleted_at: Optional[datetime] = None

undefined

class User(SQLModel, table=True): tablename = "users"

id: UUID = Field(default_factory=uuid4, primary_key=True)
tenant_id: UUID = Field(foreign_key="tenants.id", index=True)
email_address: str = Field(max_length=255, unique=True)
full_name: str = Field(max_length=255)
is_active: bool = Field(default=True)
created_at: datetime = Field(default_factory=datetime.utcnow)
updated_at: datetime = Field(default_factory=datetime.utcnow)
deleted_at: Optional[datetime] = None

undefined

Relationships

关联关系

One-to-Many

一对多

typescript

// Drizzle - User has many Posts
export const posts = pgTable("posts", {
  id: uuid("id").primaryKey().defaultRandom(),
  tenant_id: uuid("tenant_id").notNull(),
  user_id: uuid("user_id").notNull(),
  title: text("title").notNull(),
  // ... other fields
});

// Relations
export const usersRelations = relations(users, ({ many }) => ({
  posts: many(posts),
}));

export const postsRelations = relations(posts, ({ one }) => ({
  user: one(users, {
    fields: [posts.user_id],
    references: [users.id],
  }),
}));

typescript

// Drizzle - User has many Posts
export const posts = pgTable("posts", {
  id: uuid("id").primaryKey().defaultRandom(),
  tenant_id: uuid("tenant_id").notNull(),
  user_id: uuid("user_id").notNull(),
  title: text("title").notNull(),
  // ... other fields
});

// Relations
export const usersRelations = relations(users, ({ many }) => ({
  posts: many(posts),
}));

export const postsRelations = relations(posts, ({ one }) => ({
  user: one(users, {
    fields: [posts.user_id],
    references: [users.id],
  }),
}));

Many-to-Many

多对多

typescript

// Drizzle - User has many Roles through UserRoles
export const user_roles = pgTable("user_roles", {
  id: uuid("id").primaryKey().defaultRandom(),
  tenant_id: uuid("tenant_id").notNull(),
  user_id: uuid("user_id").notNull(),
  role_id: uuid("role_id").notNull(),
  created_at: timestamp("created_at").defaultNow().notNull(),
});

// Indexes for join table
export const userRolesUserIndex = index("user_roles_user_id_idx").on(user_roles.user_id);
export const userRolesRoleIndex = index("user_roles_role_id_idx").on(user_roles.role_id);

typescript

// Drizzle - User has many Roles through UserRoles
export const user_roles = pgTable("user_roles", {
  id: uuid("id").primaryKey().defaultRandom(),
  tenant_id: uuid("tenant_id").notNull(),
  user_id: uuid("user_id").notNull(),
  role_id: uuid("role_id").notNull(),
  created_at: timestamp("created_at").defaultNow().notNull(),
});

// Indexes for join table
export const userRolesUserIndex = index("user_roles_user_id_idx").on(user_roles.user_id);
export const userRolesRoleIndex = index("user_roles_role_id_idx").on(user_roles.role_id);

RLS Policies

RLS策略

Enable RLS on All Tables

为所有表启用RLS

sql

-- Enable RLS
ALTER TABLE users ENABLE ROW LEVEL SECURITY;

-- Tenant isolation policy
CREATE POLICY "tenant_isolation"
ON users
FOR ALL
USING (tenant_id = current_setting('app.tenant_id')::uuid);

-- Admin override policy
CREATE POLICY "admin_override"
ON users
FOR ALL
TO admin_role
USING (true);

sql

-- Enable RLS
ALTER TABLE users ENABLE ROW LEVEL SECURITY;

-- Tenant isolation policy
CREATE POLICY "tenant_isolation"
ON users
FOR ALL
USING (tenant_id = current_setting('app.tenant_id')::uuid);

-- Admin override policy
CREATE POLICY "admin_override"
ON users
FOR ALL
TO admin_role
USING (true);

Indexes

索引

Required Indexes

必填索引

typescript

// ALWAYS index tenant_id
export const usersTenantIndex = index("users_tenant_id_idx").on(users.tenant_id);

// Index foreign keys
export const postsUserIndex = index("posts_user_id_idx").on(posts.user_id);

// Composite indexes for common queries
export const postsCompositeIndex = index("posts_tenant_user_idx")
  .on(posts.tenant_id, posts.user_id);

typescript

// ALWAYS index tenant_id
export const usersTenantIndex = index("users_tenant_id_idx").on(users.tenant_id);

// Index foreign keys
export const postsUserIndex = index("posts_user_id_idx").on(posts.user_id);

// Composite indexes for common queries
export const postsCompositeIndex = index("posts_tenant_user_idx")
  .on(posts.tenant_id, posts.user_id);

Migrations

迁移

Drizzle Kit

bash

undefined

bash

undefined

Generate migration

bun run db:generate

Apply migration

bun run db:migrate

Rollback migration (manual)

undefined

undefined

Alembic (SQLModel)

Alembic（SQLModel）

bash

undefined

bash

undefined

Generate migration

alembic revision --autogenerate -m "add users table"

Apply migration

alembic upgrade head

Rollback migration

alembic downgrade -1

undefined

alembic downgrade -1

undefined

Supporting Documentation

配套文档

All supporting files are under 500 lines per Anthropic best practices:

examples/ - Complete schema examples
- drizzle-models.md - Drizzle schema examples
- sqlmodel-models.md - SQLModel examples
- relationships.md - Relationship patterns
- rls-policies.md - RLS policy examples
- INDEX.md - Examples navigation
reference/ - Data modeling references
- naming-conventions.md - Field naming rules
- indexes.md - Index strategies
- migrations.md - Migration patterns
- INDEX.md - Reference navigation
templates/ - Copy-paste ready templates
- drizzle-table.ts - Drizzle table template
- sqlmodel-table.py - SQLModel table template
checklists/ - Schema checklists
- schema-checklist.md - Pre-PR schema validation

所有配套文件遵循Anthropic最佳实践，单文件不超过500行：

examples/ - 完整模式示例
- drizzle-models.md - Drizzle schema示例
- sqlmodel-models.md - SQLModel示例
- relationships.md - 关联关系模式
- rls-policies.md - RLS策略示例
- INDEX.md - 示例导航
reference/ - 数据建模参考
- naming-conventions.md - 字段命名规则
- indexes.md - 索引策略
- migrations.md - 迁移模式
- INDEX.md - 参考导航
templates/ - 可直接复制使用的模板
- drizzle-table.ts - Drizzle表模板
- sqlmodel-table.py - SQLModel表模板
checklists/ - 模式检查清单
- schema-checklist.md - PR前模式验证

When to Apply This Skill

何时应用本规范

Use this skill when:

Creating new database tables
Designing multi-tenant data models
Adding relationships between tables
Creating RLS policies
Generating database migrations
Refactoring existing schemas
Implementing soft deletes
Adding indexes for performance

在以下场景使用本规范：

创建新数据库表时
设计多租户数据模型时
为表添加关联关系时
创建RLS策略时
生成数据库迁移时
重构现有模式时
实现软删除时
添加性能优化索引时

Template Reference

模板参考

These patterns are from Grey Haven's production templates:

cvi-template: Drizzle ORM + PostgreSQL + RLS
cvi-backend-template: SQLModel + PostgreSQL + Alembic

这些模式源自Grey Haven的生产模板：

cvi-template: Drizzle ORM + PostgreSQL + RLS
cvi-backend-template: SQLModel + PostgreSQL + Alembic

Critical Reminders

关键提醒

tenant_id: Required on EVERY table (no exceptions!)
snake_case: All fields use snake_case (NEVER camelCase)
Timestamps: created_at and updated_at on all tables
Indexes: Always index tenant_id and foreign keys
RLS policies: Enable RLS on all tables for tenant isolation
Soft deletes: Use deleted_at instead of hard deletes
Foreign keys: Explicitly define relationships
Migrations: Test both up and down migrations
Email fields: Name as email_address (not email)
Boolean fields: Use is_/has_/can_ prefix

tenant_id: 每张表都必须包含（无例外！）
snake_case: 所有字段使用snake_case（绝不用camelCase）
时间戳: 所有表都要有created_at和updated_at
索引: 始终为tenant_id和外键创建索引
RLS策略: 为所有表启用RLS以实现租户隔离
软删除: 使用deleted_at而非硬删除
外键: 显式定义关联关系
迁移: 测试正向和回滚迁移
邮箱字段: 命名为email_address（而非email）
布尔字段: 使用is_/has_/can_前缀