packages/database/src/schema/
├── index.ts          # Main exports and relations
├── users.ts          # User management
├── auth.ts           # Authentication system
├── subscriptions.ts  # Payment/billing
├── content.ts        # User-generated content
└── audit.ts          # System logging

// packages/database/src/schema/users.ts
import { pgTable, uuid, text, timestamp, boolean } from 'drizzle-orm/pg-core';

export const users = pgTable('users', {
  id: uuid('id').primaryKey().defaultRandom(),
  email: text('email').notNull().unique(),
  name: text('name').notNull(),
  avatar: text('avatar_url'),
  role: text('role', { enum: ['user', 'admin'] }).default('user').notNull(),
  emailVerified: boolean('email_verified').default(false).notNull(),
  isActive: boolean('is_active').default(true).notNull(),
  lastLoginAt: timestamp('last_login_at'),
  createdAt: timestamp('created_at').defaultNow().notNull(),
  updatedAt: timestamp('updated_at').defaultNow().notNull(),
}, (table) => ({
  emailIdx: uniqueIndex('users_email_idx').on(table.email),
  roleIdx: index('users_role_idx').on(table.role),
  activeUsersIdx: index('users_active_idx').on(table.isActive),
}));

// Type inference
export type User = typeof users.$inferSelect;
export type CreateUser = typeof users.$inferInsert;
export type UpdateUser = Partial<CreateUser>;

export const userProfiles = pgTable('user_profiles', {
  id: uuid('id').primaryKey().defaultRandom(),
  userId: uuid('user_id').references(() => users.id).notNull(),
  bio: text('bio'),
  website: text('website'),
  location: text('location'),
  timezone: text('timezone').default('UTC'),
  language: text('language').default('en'),
  theme: text('theme', { enum: ['light', 'dark', 'system'] }).default('system'),
  notifications: boolean('notifications_enabled').default(true),
  createdAt: timestamp('created_at').defaultNow().notNull(),
  updatedAt: timestamp('updated_at').defaultNow().notNull(),
}, (table) => ({
  userIdIdx: uniqueIndex('profiles_user_id_idx').on(table.userId),
}));

// packages/database/src/schema/auth.ts
export const accounts = pgTable('accounts', {
  id: uuid('id').primaryKey().defaultRandom(),
  userId: uuid('user_id').references(() => users.id, { onDelete: 'cascade' }).notNull(),
  provider: text('provider').notNull(), // 'github', 'google', etc.
  providerAccountId: text('provider_account_id').notNull(),
  type: text('type').notNull(), // 'oauth', 'email', etc.
  accessToken: text('access_token'),
  refreshToken: text('refresh_token'),
  expiresAt: timestamp('expires_at'),
  tokenType: text('token_type'),
  scope: text('scope'),
  idToken: text('id_token'),
  sessionState: text('session_state'),
  createdAt: timestamp('created_at').defaultNow().notNull(),
  updatedAt: timestamp('updated_at').defaultNow().notNull(),
}, (table) => ({
  providerIdx: uniqueIndex('accounts_provider_idx')
    .on(table.provider, table.providerAccountId),
  userIdIdx: index('accounts_user_id_idx').on(table.userId),
}));

export const sessions = pgTable('sessions', {
  id: uuid('id').primaryKey().defaultRandom(),
  userId: uuid('user_id').references(() => users.id, { onDelete: 'cascade' }).notNull(),
  token: text('token').notNull().unique(),
  expiresAt: timestamp('expires_at').notNull(),
  ipAddress: text('ip_address'),
  userAgent: text('user_agent'),
  isActive: boolean('is_active').default(true).notNull(),
  createdAt: timestamp('created_at').defaultNow().notNull(),
}, (table) => ({
  tokenIdx: uniqueIndex('sessions_token_idx').on(table.token),
  userIdIdx: index('sessions_user_id_idx').on(table.userId),
  expiresAtIdx: index('sessions_expires_at_idx').on(table.expiresAt),
}));

export const verificationTokens = pgTable('verification_tokens', {
  id: uuid('id').primaryKey().defaultRandom(),
  email: text('email').notNull(),
  token: text('token').notNull().unique(),
  type: text('type', { 
    enum: ['email_verification', 'password_reset', 'magic_link'] 
  }).notNull(),
  expiresAt: timestamp('expires_at').notNull(),
  usedAt: timestamp('used_at'),
  createdAt: timestamp('created_at').defaultNow().notNull(),
}, (table) => ({
  tokenIdx: uniqueIndex('verification_tokens_token_idx').on(table.token),
  emailIdx: index('verification_tokens_email_idx').on(table.email),
  typeIdx: index('verification_tokens_type_idx').on(table.type),
}));

// packages/database/src/schema/subscriptions.ts
export const subscriptionPlans = pgTable('subscription_plans', {
  id: uuid('id').primaryKey().defaultRandom(),
  name: text('name').notNull().unique(),
  description: text('description'),
  price: integer('price_cents').notNull(), // Store in cents
  currency: text('currency').default('USD').notNull(),
  interval: text('interval', { enum: ['month', 'year'] }).notNull(),
  features: text('features').array(), // JSON array of features
  isActive: boolean('is_active').default(true).notNull(),
  sortOrder: integer('sort_order').default(0),
  createdAt: timestamp('created_at').defaultNow().notNull(),
  updatedAt: timestamp('updated_at').defaultNow().notNull(),
}, (table) => ({
  nameIdx: uniqueIndex('plans_name_idx').on(table.name),
  activeIdx: index('plans_active_idx').on(table.isActive),
  sortOrderIdx: index('plans_sort_order_idx').on(table.sortOrder),
}));

export const subscriptions = pgTable('subscriptions', {
  id: uuid('id').primaryKey().defaultRandom(),
  userId: uuid('user_id').references(() => users.id, { onDelete: 'cascade' }).notNull(),
  planId: uuid('plan_id').references(() => subscriptionPlans.id).notNull(),
  status: text('status', {
    enum: ['active', 'inactive', 'canceled', 'past_due', 'unpaid']
  }).notNull(),
  stripeCustomerId: text('stripe_customer_id'),
  stripeSubscriptionId: text('stripe_subscription_id'),
  currentPeriodStart: timestamp('current_period_start'),
  currentPeriodEnd: timestamp('current_period_end'),
  cancelAtPeriodEnd: boolean('cancel_at_period_end').default(false),
  canceledAt: timestamp('canceled_at'),
  trialEnd: timestamp('trial_end'),
  createdAt: timestamp('created_at').defaultNow().notNull(),
  updatedAt: timestamp('updated_at').defaultNow().notNull(),
}, (table) => ({
  userIdIdx: uniqueIndex('subscriptions_user_id_idx').on(table.userId),
  stripeCustomerIdx: index('subscriptions_stripe_customer_idx').on(table.stripeCustomerId),
  statusIdx: index('subscriptions_status_idx').on(table.status),
}));

export const payments = pgTable('payments', {
  id: uuid('id').primaryKey().defaultRandom(),
  userId: uuid('user_id').references(() => users.id).notNull(),
  subscriptionId: uuid('subscription_id').references(() => subscriptions.id),
  amount: integer('amount_cents').notNull(),
  currency: text('currency').default('USD').notNull(),
  status: text('status', {
    enum: ['pending', 'succeeded', 'failed', 'canceled', 'refunded']
  }).notNull(),
  stripePaymentIntentId: text('stripe_payment_intent_id'),
  stripeChargeId: text('stripe_charge_id'),
  description: text('description'),
  metadata: text('metadata'), // JSON string for additional data
  createdAt: timestamp('created_at').defaultNow().notNull(),
}, (table) => ({
  userIdIdx: index('payments_user_id_idx').on(table.userId),
  statusIdx: index('payments_status_idx').on(table.status),
  stripePaymentIntentIdx: index('payments_stripe_pi_idx').on(table.stripePaymentIntentId),
  createdAtIdx: index('payments_created_at_idx').on(table.createdAt),
}));

// packages/database/src/schema/content.ts
export const posts = pgTable('posts', {
  id: uuid('id').primaryKey().defaultRandom(),
  userId: uuid('user_id').references(() => users.id, { onDelete: 'cascade' }).notNull(),
  title: text('title').notNull(),
  content: text('content'),
  excerpt: text('excerpt'),
  slug: text('slug').notNull(),
  status: text('status', { 
    enum: ['draft', 'published', 'archived'] 
  }).default('draft').notNull(),
  featured: boolean('featured').default(false),
  viewCount: integer('view_count').default(0),
  publishedAt: timestamp('published_at'),
  createdAt: timestamp('created_at').defaultNow().notNull(),
  updatedAt: timestamp('updated_at').defaultNow().notNull(),
}, (table) => ({
  slugIdx: uniqueIndex('posts_slug_idx').on(table.slug),
  userIdIdx: index('posts_user_id_idx').on(table.userId),
  statusIdx: index('posts_status_idx').on(table.status),
  publishedAtIdx: index('posts_published_at_idx').on(table.publishedAt),
  featuredIdx: index('posts_featured_idx').on(table.featured),
}));

export const categories = pgTable('categories', {
  id: uuid('id').primaryKey().defaultRandom(),
  name: text('name').notNull().unique(),
  slug: text('slug').notNull().unique(),
  description: text('description'),
  color: text('color').default('#6366f1'),
  sortOrder: integer('sort_order').default(0),
  isActive: boolean('is_active').default(true),
  createdAt: timestamp('created_at').defaultNow().notNull(),
}, (table) => ({
  nameIdx: uniqueIndex('categories_name_idx').on(table.name),
  slugIdx: uniqueIndex('categories_slug_idx').on(table.slug),
}));

export const tags = pgTable('tags', {
  id: uuid('id').primaryKey().defaultRandom(),
  name: text('name').notNull().unique(),
  slug: text('slug').notNull().unique(),
  usageCount: integer('usage_count').default(0),
  createdAt: timestamp('created_at').defaultNow().notNull(),
}, (table) => ({
  nameIdx: uniqueIndex('tags_name_idx').on(table.name),
  slugIdx: uniqueIndex('tags_slug_idx').on(table.slug),
  usageCountIdx: index('tags_usage_count_idx').on(table.usageCount),
}));

// Many-to-many relationship
export const postTags = pgTable('post_tags', {
  postId: uuid('post_id').references(() => posts.id, { onDelete: 'cascade' }).notNull(),
  tagId: uuid('tag_id').references(() => tags.id, { onDelete: 'cascade' }).notNull(),
  createdAt: timestamp('created_at').defaultNow().notNull(),
}, (table) => ({
  pk: primaryKey({ columns: [table.postId, table.tagId] }),
  postIdIdx: index('post_tags_post_id_idx').on(table.postId),
  tagIdIdx: index('post_tags_tag_id_idx').on(table.tagId),
}));

// packages/database/src/schema/audit.ts
export const auditLogs = pgTable('audit_logs', {
  id: uuid('id').primaryKey().defaultRandom(),
  userId: uuid('user_id').references(() => users.id),
  action: text('action').notNull(), // 'CREATE', 'UPDATE', 'DELETE'
  entity: text('entity').notNull(), // Table name
  entityId: uuid('entity_id'),
  changes: text('changes'), // JSON string of changes
  ipAddress: text('ip_address'),
  userAgent: text('user_agent'),
  metadata: text('metadata'), // Additional context
  createdAt: timestamp('created_at').defaultNow().notNull(),
}, (table) => ({
  userIdIdx: index('audit_logs_user_id_idx').on(table.userId),
  actionIdx: index('audit_logs_action_idx').on(table.action),
  entityIdx: index('audit_logs_entity_idx').on(table.entity),
  entityIdIdx: index('audit_logs_entity_id_idx').on(table.entityId),
  createdAtIdx: index('audit_logs_created_at_idx').on(table.createdAt),
}));

// packages/database/src/schema/index.ts
import { relations } from 'drizzle-orm';

export const usersRelations = relations(users, ({ one, many }) => ({
  profile: one(userProfiles, {
    fields: [users.id],
    references: [userProfiles.userId],
  }),
  accounts: many(accounts),
  sessions: many(sessions),
  subscription: one(subscriptions, {
    fields: [users.id],
    references: [subscriptions.userId],
  }),
  posts: many(posts),
  payments: many(payments),
}));

export const subscriptionsRelations = relations(subscriptions, ({ one }) => ({
  user: one(users, {
    fields: [subscriptions.userId],
    references: [users.id],
  }),
  plan: one(subscriptionPlans, {
    fields: [subscriptions.planId],
    references: [subscriptionPlans.id],
  }),
}));

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

export const postTagsRelations = relations(postTags, ({ one }) => ({
  post: one(posts, {
    fields: [postTags.postId],
    references: [posts.id],
  }),
  tag: one(tags, {
    fields: [postTags.tagId],
    references: [tags.id],
  }),
}));

// Get user with subscription and profile
const userWithDetails = await db.query.users.findFirst({
  where: eq(users.id, userId),
  with: {
    profile: true,
    subscription: {
      with: {
        plan: true,
      },
    },
    posts: {
      where: eq(posts.status, 'published'),
      limit: 10,
    },
  },
});

// ✅ Good - Consistent, descriptive names
export const userProfiles = pgTable('user_profiles', {
  id: uuid('id').primaryKey().defaultRandom(),
  userId: uuid('user_id').references(() => users.id),
  firstName: text('first_name').notNull(),
  lastName: text('last_name').notNull(),
  createdAt: timestamp('created_at').defaultNow().notNull(),
});

// ❌ Avoid - Inconsistent, unclear names
export const Profile = pgTable('profiles', {
  ID: uuid('ID').primaryKey(),
  user: uuid('user').references(() => users.id),
  fName: text('fname').notNull(),
  lName: text('lname').notNull(),
  created: timestamp('created').defaultNow(),
});

// ✅ Good - Appropriate types and constraints
export const users = pgTable('users', {
  id: uuid('id').primaryKey().defaultRandom(),
  email: text('email').notNull().unique(),
  name: text('name').notNull(),
  role: text('role', { enum: ['user', 'admin'] }).default('user').notNull(),
  isActive: boolean('is_active').default(true).notNull(),
  createdAt: timestamp('created_at').defaultNow().notNull(),
});

// ❌ Avoid - Generic types, missing constraints
export const users = pgTable('users', {
  id: text('id'), // No primary key
  email: text('email'), // No unique constraint
  name: text('name'), // Nullable when shouldn't be
  role: text('role'), // No enum constraint
  active: text('active'), // String instead of boolean
  created: text('created'), // Text instead of timestamp
});

// ✅ Good - Strategic indexes for common queries
export const posts = pgTable('posts', {
  id: uuid('id').primaryKey().defaultRandom(),
  userId: uuid('user_id').references(() => users.id).notNull(),
  title: text('title').notNull(),
  status: text('status', { enum: ['draft', 'published'] }).notNull(),
  publishedAt: timestamp('published_at'),
  createdAt: timestamp('created_at').defaultNow().notNull(),
}, (table) => ({
  // Index for finding user's posts
  userIdIdx: index('posts_user_id_idx').on(table.userId),
  // Index for published posts query
  statusPublishedIdx: index('posts_status_published_idx')
    .on(table.status, table.publishedAt),
  // Composite index for recent published posts
  publishedAtIdx: index('posts_published_at_idx').on(table.publishedAt),
}));

// ❌ Avoid - Over-indexing or missing strategic indexes
export const posts = pgTable('posts', {
  // ... columns
}, (table) => ({
  // Too many indexes (slows down writes)
  titleIdx: index('posts_title_idx').on(table.title),
  contentIdx: index('posts_content_idx').on(table.content), // Rarely queried
  userIdIdx: index('posts_user_id_idx').on(table.userId),
  // Missing strategic indexes for common query patterns
}));

// ✅ Good - Proper foreign key constraints and cascading
export const accounts = pgTable('accounts', {
  id: uuid('id').primaryKey().defaultRandom(),
  userId: uuid('user_id')
    .references(() => users.id, { onDelete: 'cascade' })
    .notNull(),
  provider: text('provider').notNull(),
  providerAccountId: text('provider_account_id').notNull(),
}, (table) => ({
  // Ensure unique provider + account combination
  providerAccountIdx: uniqueIndex('accounts_provider_account_idx')
    .on(table.provider, table.providerAccountId),
}));

// ❌ Avoid - Missing constraints
export const accounts = pgTable('accounts', {
  id: uuid('id').primaryKey().defaultRandom(),
  userId: uuid('user_id'), // No foreign key reference
  provider: text('provider'), // Should not be nullable
  providerAccountId: text('provider_account_id'), // Should not be nullable
  // Missing unique constraint allows duplicate accounts
});

// ✅ Safe - Adding nullable columns
export const users = pgTable('users', {
  // ... existing columns
  bio: text('bio'), // Nullable, safe to add
  preferences: text('preferences'), // Nullable, safe to add
});

// ✅ Safe - Adding indexes
export const users = pgTable('users', {
  // ... columns
}, (table) => ({
  // ... existing indexes
  bioIdx: index('users_bio_idx').on(table.bio), // Safe to add
}));

// ⚠️  Careful - Adding non-nullable columns (need default)
export const users = pgTable('users', {
  // ... existing columns
  isActive: boolean('is_active').default(true).notNull(), // Safe with default
});

// ❌ Dangerous - Breaking changes
export const users = pgTable('users', {
  // ... existing columns
  // Renaming columns requires careful migration
  // displayName: text('display_name').notNull(), // Was 'name'
  // Dropping columns loses data
});

// ✅ Good - Normalized with strategic denormalization
export const posts = pgTable('posts', {
  id: uuid('id').primaryKey().defaultRandom(),
  userId: uuid('user_id').references(() => users.id).notNull(),
  title: text('title').notNull(),
  content: text('content'),
  // Denormalized for performance (avoid join for common queries)
  authorName: text('author_name').notNull(),
  viewCount: integer('view_count').default(0),
  commentCount: integer('comment_count').default(0),
  createdAt: timestamp('created_at').defaultNow().notNull(),
});

// ❌ Avoid - Over-normalized (requires many joins)
export const posts = pgTable('posts', {
  id: uuid('id').primaryKey().defaultRandom(),
  userId: uuid('user_id').references(() => users.id).notNull(),
  title: text('title').notNull(),
  content: text('content'),
  // Forcing joins for every query
  createdAt: timestamp('created_at').defaultNow().notNull(),
});

// For large tables, consider partitioning
export const auditLogs = pgTable('audit_logs', {
  id: uuid('id').primaryKey().defaultRandom(),
  userId: uuid('user_id').references(() => users.id),
  action: text('action').notNull(),
  createdAt: timestamp('created_at').defaultNow().notNull(),
}, (table) => ({
  // Partition by created_at for time-based queries
  createdAtIdx: index('audit_logs_created_at_idx').on(table.createdAt),
}));