greyhaven-ai/database-conventions

Grey Haven Database Conventions

Database schema standards for Drizzle ORM (TypeScript) and SQLModel (Python).

Follow these conventions for all Grey Haven multi-tenant database schemas.

Supporting Documentation

• examples/ - Complete schema examples (all files <500 lines)
  • drizzle-schemas.md - TypeScript/Drizzle examples
  • sqlmodel-schemas.md - Python/SQLModel examples
  • migrations.md - Migration patterns
  • rls-policies.md - Row Level Security
• reference/ - Detailed references (all files <500 lines)
  • field-naming.md - Naming conventions
  • indexing.md - Index patterns
  • relationships.md - Foreign keys and relations
• templates/ - Copy-paste schema templates
• checklists/ - Schema validation checklists

Critical Rules

1. snake_case Fields (ALWAYS)

Database columns MUST use snake_case, never camelCase.

// ✅ CORRECT
export const users = pgTable("users", {
  id: uuid("id").primaryKey().defaultRandom(),
  created_at: timestamp("created_at").defaultNow().notNull(),
  tenant_id: uuid("tenant_id").notNull(),
  email_address: text("email_address").notNull(),
});

// ❌ WRONG - Don't use camelCase
export const users = pgTable("users", {
  createdAt: timestamp("createdAt"),  // WRONG!
  tenantId: uuid("tenantId"),        // WRONG!
});

2. tenant_id Required (Multi-Tenant)

Every table MUST include tenant_id for data isolation.

// TypeScript - Drizzle
export const organizations = pgTable("organizations", {
  id: uuid("id").primaryKey().defaultRandom(),
  tenant_id: uuid("tenant_id").notNull(),  // REQUIRED
  name: text("name").notNull(),
});

# Python - SQLModel
class Organization(SQLModel, table=True):
    id: UUID = Field(default_factory=uuid4, primary_key=True)
    tenant_id: UUID = Field(foreign_key="tenants.id", index=True)  # REQUIRED
    name: str = Field(max_length=255)

See examples/drizzle-schemas.md and examples/sqlmodel-schemas.md for complete examples.

3. Standard Timestamps

All tables must have created_at and updated_at.

// TypeScript - Reusable timestamps
export const baseTimestamps = {
  created_at: timestamp("created_at").defaultNow().notNull(),
  updated_at: timestamp("updated_at").defaultNow().notNull().$onUpdate(() => new Date()),
};

export const teams = pgTable("teams", {
  id: uuid("id").primaryKey().defaultRandom(),
  ...baseTimestamps,  // Spread operator
  tenant_id: uuid("tenant_id").notNull(),
  name: text("name").notNull(),
});

# Python - Mixin pattern
class TimestampMixin:
    created_at: datetime = Field(default_factory=datetime.utcnow)
    updated_at: datetime = Field(default_factory=datetime.utcnow, sa_column_kwargs={"onupdate": datetime.utcnow})

class Team(TimestampMixin, SQLModel, table=True):
    id: UUID = Field(default_factory=uuid4, primary_key=True)
    tenant_id: UUID = Field(index=True)
    name: str = Field(max_length=255)

4. Row Level Security (RLS)

Enable RLS on all tables with tenant_id.

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

-- Tenant isolation policy
CREATE POLICY "tenant_isolation" ON users
  FOR ALL TO authenticated
  USING (tenant_id = (current_setting('request.jwt.claims')::json->>'tenant_id')::uuid);

See examples/rls-policies.md for complete RLS patterns.

Quick Reference

Field Naming Patterns

Boolean fields: Prefix with is_, has_, can_

is_active: boolean("is_active")
has_access: boolean("has_access")
can_edit: boolean("can_edit")

Timestamp fields: Suffix with _at

created_at: timestamp("created_at")
updated_at: timestamp("updated_at")
deleted_at: timestamp("deleted_at")
last_login_at: timestamp("last_login_at")

Foreign keys: Suffix with _id

tenant_id: uuid("tenant_id")
user_id: uuid("user_id")
organization_id: uuid("organization_id")

See reference/field-naming.md for complete naming guide.

Indexing Patterns

Always index:

• tenant_id (for multi-tenant queries)
• Foreign keys (for joins)
• Unique constraints (email, slug)
• Frequently queried fields

// Composite index for tenant + lookup
export const usersIndex = index("users_tenant_email_idx").on(
  users.tenant_id,
  users.email_address
);

See reference/indexing.md for index strategies.

Relationships

One-to-many:

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

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

See reference/relationships.md for all relationship patterns.

Drizzle ORM (TypeScript)

Installation:

bun add drizzle-orm postgres
bun add -d drizzle-kit

Basic schema:

// db/schema.ts
import { pgTable, uuid, text, timestamp, boolean } from "drizzle-orm/pg-core";

export const users = pgTable("users", {
  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(),
  email_address: text("email_address").notNull().unique(),
  is_active: boolean("is_active").default(true).notNull(),
});

Generate migration:

bun run drizzle-kit generate:pg
bun run drizzle-kit push:pg

See examples/migrations.md for migration workflow.

SQLModel (Python)

Installation:

pip install sqlmodel psycopg2-binary

Basic model:

# app/models/user.py
from sqlmodel import Field, SQLModel
from uuid import UUID, uuid4
from datetime import datetime

class User(SQLModel, table=True):
    __tablename__ = "users"
    
    id: UUID = Field(default_factory=uuid4, primary_key=True)
    created_at: datetime = Field(default_factory=datetime.utcnow)
    updated_at: datetime = Field(default_factory=datetime.utcnow)
    tenant_id: UUID = Field(foreign_key="tenants.id", index=True)
    email_address: str = Field(unique=True, index=True, max_length=255)
    is_active: bool = Field(default=True)

Generate migration:

alembic revision --autogenerate -m "Add users table"
alembic upgrade head

See examples/migrations.md for Alembic setup.

When to Apply This Skill

Use this skill when:

• ✅ Designing new database schemas
• ✅ Creating Drizzle or SQLModel models
• ✅ Writing database migrations
• ✅ Setting up RLS policies
• ✅ Adding indexes for performance
• ✅ Defining table relationships
• ✅ Reviewing database code in PRs
• ✅ User mentions: "database", "schema", "Drizzle", "SQLModel", "migration", "RLS", "tenant_id", "snake_case"

Template References

• TypeScript: cvi-template (Drizzle ORM + PlanetScale)
• Python: cvi-backend-template (SQLModel + PostgreSQL)

Critical Reminders

1. snake_case - ALL database fields use snake_case (never camelCase)
2. tenant_id - Required on all tables for multi-tenant isolation
3. Timestamps - created_at and updated_at on all tables
4. RLS policies - Enable on all tables with tenant_id
5. Indexing - Index tenant_id, foreign keys, and unique fields
6. Migrations - Always use migrations (Drizzle Kit or Alembic)
7. Field naming - Booleans use is_/has_/can_ prefix, timestamps use _at suffix
8. No raw SQL - Use ORM for queries (prevents SQL injection)
9. Soft deletes - Use deleted_at timestamp, not hard deletes
10. Foreign keys - Always define relationships explicitly

Next Steps

• Need examples? See examples/ for Drizzle and SQLModel schemas
• Need references? See reference/ for naming, indexing, relationships
• Need templates? See templates/ for copy-paste schema starters
• Need checklists? Use checklists/ for schema validation