database-conventions

name	database-conventions
description	Apply Grey Haven database conventions - snake_case fields, multi-tenant with tenant_id and RLS, proper indexing, migrations for Drizzle (TypeScript) and SQLModel (Python). Use when designing schemas, writing database code, creating migrations, setting up RLS policies, or when user mentions 'database', 'schema', 'Drizzle', 'SQLModel', 'migration', 'RLS', 'tenant_id', 'snake_case', 'indexes', or 'foreign keys'.
skills	["code-style","data-validation"]
allowed-tools	["Read","Write","MultiEdit","Bash","Grep","Glob","TodoWrite"]

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