convex/
├── schema.ts         # Main schema definition
├── users.ts          # User queries and mutations
├── auth.ts           # Authentication functions
├── subscriptions.ts  # Payment/billing functions
├── content.ts        # User-generated content
└── audit.ts          # System logging

// convex/schema.ts
import { defineSchema, defineTable } from 'convex/server';
import { v } from 'convex/values';

export default defineSchema({
  users: defineTable({
    email: v.string(),
    name: v.string(),
    avatarUrl: v.optional(v.string()),
    role: v.union(v.literal('user'), v.literal('admin')),
    emailVerified: v.optional(v.boolean()),
    isActive: v.optional(v.boolean()),
    lastLoginAt: v.optional(v.number()),
    createdAt: v.number(),
    updatedAt: v.number(),
  })
    .index('by_email', ['email'])
    .index('by_role', ['role'])
    .index('by_isActive', ['isActive']),
});

// Type inference - use in your functions
// Doc<"users"> gives you the full document type

// convex/schema.ts (continued)
export default defineSchema({
  // ... users table above

  userProfiles: defineTable({
    userId: v.id('users'),
    bio: v.optional(v.string()),
    website: v.optional(v.string()),
    location: v.optional(v.string()),
    timezone: v.optional(v.string()),
    language: v.optional(v.string()),
    theme: v.optional(v.union(v.literal('light'), v.literal('dark'), v.literal('system'))),
    notificationsEnabled: v.optional(v.boolean()),
    createdAt: v.number(),
    updatedAt: v.number(),
  }).index('by_userId', ['userId']),
});

// convex/schema.ts (continued)
export default defineSchema({
  // ... previous tables

  accounts: defineTable({
    userId: v.id('users'),
    provider: v.string(), // 'github', 'google', etc.
    providerAccountId: v.string(),
    type: v.string(), // 'oauth', 'email', etc.
    accessToken: v.optional(v.string()),
    refreshToken: v.optional(v.string()),
    expiresAt: v.optional(v.number()),
    tokenType: v.optional(v.string()),
    scope: v.optional(v.string()),
    idToken: v.optional(v.string()),
    sessionState: v.optional(v.string()),
    createdAt: v.number(),
    updatedAt: v.number(),
  })
    .index('by_provider_providerAccountId', ['provider', 'providerAccountId'])
    .index('by_userId', ['userId']),
});

// convex/schema.ts (continued)
export default defineSchema({
  // ... previous tables

  sessions: defineTable({
    userId: v.id('users'),
    token: v.string(),
    expiresAt: v.number(),
    ipAddress: v.optional(v.string()),
    userAgent: v.optional(v.string()),
    isActive: v.optional(v.boolean()),
    createdAt: v.number(),
  })
    .index('by_token', ['token'])
    .index('by_userId', ['userId'])
    .index('by_expiresAt', ['expiresAt']),
});

// convex/schema.ts (continued)
export default defineSchema({
  // ... previous tables

  verificationTokens: defineTable({
    email: v.string(),
    token: v.string(),
    type: v.union(
      v.literal('email_verification'),
      v.literal('password_reset'),
      v.literal('magic_link')
    ),
    userId: v.optional(v.id('users')),
    expiresAt: v.number(),
    usedAt: v.optional(v.number()),
    createdAt: v.number(),
  })
    .index('by_token', ['token'])
    .index('by_email', ['email'])
    .index('by_type', ['type'])
    .index('by_expiresAt', ['expiresAt']),
});

// convex/schema.ts (continued)
export default defineSchema({
  // ... previous tables

  subscriptionPlans: defineTable({
    name: v.string(),
    description: v.optional(v.string()),
    priceCents: v.number(), // Store in cents
    currency: v.optional(v.string()),
    interval: v.union(v.literal('month'), v.literal('year')),
    features: v.optional(v.array(v.string())),
    isActive: v.optional(v.boolean()),
    sortOrder: v.optional(v.number()),
    createdAt: v.number(),
    updatedAt: v.number(),
  })
    .index('by_name', ['name'])
    .index('by_isActive', ['isActive'])
    .index('by_sortOrder', ['sortOrder']),
});

// convex/schema.ts (continued)
export default defineSchema({
  // ... previous tables

  subscriptions: defineTable({
    userId: v.id('users'),
    planId: v.id('subscriptionPlans'),
    status: v.union(
      v.literal('active'),
      v.literal('inactive'),
      v.literal('canceled'),
      v.literal('past_due'),
      v.literal('unpaid')
    ),
    stripeCustomerId: v.optional(v.string()),
    stripeSubscriptionId: v.optional(v.string()),
    currentPeriodStart: v.optional(v.number()),
    currentPeriodEnd: v.optional(v.number()),
    cancelAtPeriodEnd: v.optional(v.boolean()),
    canceledAt: v.optional(v.number()),
    trialEnd: v.optional(v.number()),
    createdAt: v.number(),
    updatedAt: v.number(),
  })
    .index('by_userId', ['userId'])
    .index('by_stripeCustomerId', ['stripeCustomerId'])
    .index('by_status', ['status']),
});

// convex/schema.ts (continued)
export default defineSchema({
  // ... previous tables

  payments: defineTable({
    userId: v.id('users'),
    subscriptionId: v.optional(v.id('subscriptions')),
    amountCents: v.number(),
    currency: v.optional(v.string()),
    status: v.union(
      v.literal('pending'),
      v.literal('succeeded'),
      v.literal('failed'),
      v.literal('canceled'),
      v.literal('refunded')
    ),
    stripePaymentIntentId: v.optional(v.string()),
    stripeChargeId: v.optional(v.string()),
    description: v.optional(v.string()),
    metadata: v.optional(v.string()), // JSON string for additional data
    createdAt: v.number(),
  })
    .index('by_userId', ['userId'])
    .index('by_status', ['status'])
    .index('by_stripePaymentIntentId', ['stripePaymentIntentId'])
    .index('by_createdAt', ['createdAt']),
});

// convex/schema.ts (continued)
export default defineSchema({
  // ... previous tables

  posts: defineTable({
    userId: v.id('users'),
    title: v.string(),
    content: v.optional(v.string()),
    excerpt: v.optional(v.string()),
    slug: v.string(),
    status: v.union(v.literal('draft'), v.literal('published'), v.literal('archived')),
    featured: v.optional(v.boolean()),
    viewCount: v.optional(v.number()),
    publishedAt: v.optional(v.number()),
    // Denormalized for performance
    authorName: v.optional(v.string()),
    createdAt: v.number(),
    updatedAt: v.number(),
  })
    .index('by_slug', ['slug'])
    .index('by_userId', ['userId'])
    .index('by_status', ['status'])
    .index('by_publishedAt', ['publishedAt'])
    .index('by_featured', ['featured']),
});

// convex/schema.ts (continued)
export default defineSchema({
  // ... previous tables

  categories: defineTable({
    name: v.string(),
    slug: v.string(),
    description: v.optional(v.string()),
    color: v.optional(v.string()),
    sortOrder: v.optional(v.number()),
    isActive: v.optional(v.boolean()),
    createdAt: v.number(),
  })
    .index('by_name', ['name'])
    .index('by_slug', ['slug']),

  tags: defineTable({
    name: v.string(),
    slug: v.string(),
    usageCount: v.optional(v.number()),
    createdAt: v.number(),
  })
    .index('by_name', ['name'])
    .index('by_slug', ['slug'])
    .index('by_usageCount', ['usageCount']),

  // Many-to-many relationship
  postTags: defineTable({
    postId: v.id('posts'),
    tagId: v.id('tags'),
    createdAt: v.number(),
  })
    .index('by_postId', ['postId'])
    .index('by_tagId', ['tagId']),
});

// convex/schema.ts (continued)
export default defineSchema({
  // ... previous tables

  auditLogs: defineTable({
    userId: v.optional(v.id('users')),
    action: v.string(), // 'CREATE', 'UPDATE', 'DELETE'
    entity: v.string(), // Table name
    entityId: v.optional(v.string()),
    changes: v.optional(v.string()), // JSON string of changes
    ipAddress: v.optional(v.string()),
    userAgent: v.optional(v.string()),
    metadata: v.optional(v.string()), // Additional context
    createdAt: v.number(),
  })
    .index('by_userId', ['userId'])
    .index('by_action', ['action'])
    .index('by_entity', ['entity'])
    .index('by_createdAt', ['createdAt']),
});

// convex/users.ts
import { v } from 'convex/values';
import { query } from './_generated/server';

// Get user with subscription and profile
export const getUserWithDetails = query({
  args: { userId: v.id('users') },
  handler: async (ctx, args) => {
    const user = await ctx.db.get(args.userId);
    if (!user) return null;

    const [profile, subscription, posts] = await Promise.all([
      ctx.db
        .query('userProfiles')
        .withIndex('by_userId', (q) => q.eq('userId', args.userId))
        .first(),
      ctx.db
        .query('subscriptions')
        .withIndex('by_userId', (q) => q.eq('userId', args.userId))
        .first(),
      ctx.db
        .query('posts')
        .withIndex('by_userId', (q) => q.eq('userId', args.userId))
        .filter((q) => q.eq(q.field('status'), 'published'))
        .take(10),
    ]);

    // Get subscription plan if subscription exists
    const plan = subscription ? await ctx.db.get(subscription.planId) : null;

    return {
      ...user,
      profile,
      subscription: subscription ? { ...subscription, plan } : null,
      recentPosts: posts,
    };
  },
});

// convex/posts.ts
export const getPostWithTags = query({
  args: { postId: v.id('posts') },
  handler: async (ctx, args) => {
    const post = await ctx.db.get(args.postId);
    if (!post) return null;

    // Get post tags
    const postTags = await ctx.db
      .query('postTags')
      .withIndex('by_postId', (q) => q.eq('postId', args.postId))
      .collect();

    // Get tag details
    const tags = await Promise.all(postTags.map((pt) => ctx.db.get(pt.tagId)));

    return {
      ...post,
      tags: tags.filter((t) => t !== null),
    };
  },
});

// GOOD: Consistent, descriptive names
export default defineSchema({
  userProfiles: defineTable({
    userId: v.id('users'),
    firstName: v.string(),
    lastName: v.string(),
    createdAt: v.number(),
  }),
});

// AVOID: Inconsistent, unclear names
// profiles, fName, lName, created

// GOOD: Appropriate types and validators
export default defineSchema({
  users: defineTable({
    email: v.string(), // Required string
    name: v.string(), // Required string
    role: v.union(v.literal('user'), v.literal('admin')), // Enum
    isActive: v.optional(v.boolean()), // Optional with default handling in code
    createdAt: v.number(), // Timestamps as numbers (milliseconds)
  }),
});

// AVOID: Generic types, missing constraints
// role: v.string() - No enum validation
// created: v.string() - String instead of number timestamp

// GOOD: Strategic indexes for common queries
export default defineSchema({
  posts: defineTable({
    userId: v.id('users'),
    title: v.string(),
    status: v.union(v.literal('draft'), v.literal('published')),
    publishedAt: v.optional(v.number()),
    createdAt: v.number(),
  })
    // Index for finding user's posts
    .index('by_userId', ['userId'])
    // Index for published posts query
    .index('by_status', ['status'])
    // Index for recent published posts
    .index('by_publishedAt', ['publishedAt'])
    // Composite index for user's posts by status
    .index('by_userId_status', ['userId', 'status']),
});

// AVOID: Over-indexing (slows down writes)
// or missing strategic indexes for common queries

// GOOD: Proper references and cascade handling in code
export const deleteUser = mutation({
  args: { userId: v.id('users') },
  handler: async (ctx, args) => {
    // Delete related data first (cascade in code)
    const accounts = await ctx.db
      .query('accounts')
      .withIndex('by_userId', (q) => q.eq('userId', args.userId))
      .collect();

    for (const account of accounts) {
      await ctx.db.delete(account._id);
    }

    const sessions = await ctx.db
      .query('sessions')
      .withIndex('by_userId', (q) => q.eq('userId', args.userId))
      .collect();

    for (const session of sessions) {
      await ctx.db.delete(session._id);
    }

    // Finally delete the user
    await ctx.db.delete(args.userId);
  },
});

// SAFE: Adding optional fields (backward compatible)
export default defineSchema({
  users: defineTable({
    // ... existing fields
    email: v.string(),
    name: v.string(),

    // New optional fields - safe to add
    bio: v.optional(v.string()),
    preferences: v.optional(v.string()),
  }),
});

// Your code handles undefined values
export const getUser = query({
  args: { userId: v.id('users') },
  handler: async (ctx, args) => {
    const user = await ctx.db.get(args.userId);
    return {
      ...user,
      bio: user?.bio ?? '', // Default for optional field
    };
  },
});

// SAFE: Adding new tables doesn't affect existing data
export default defineSchema({
  // ... existing tables

  // New table - safe to add anytime
  notifications: defineTable({
    userId: v.id('users'),
    type: v.string(),
    message: v.string(),
    read: v.optional(v.boolean()),
    createdAt: v.number(),
  }).index('by_userId', ['userId']),
});

// GOOD: Denormalized for read performance
export default defineSchema({
  posts: defineTable({
    userId: v.id('users'),
    title: v.string(),
    content: v.optional(v.string()),
    // Denormalized author info - avoids join for common queries
    authorName: v.string(),
    authorAvatarUrl: v.optional(v.string()),
    // Cached counts - updated when comments/views change
    viewCount: v.optional(v.number()),
    commentCount: v.optional(v.number()),
    createdAt: v.number(),
  }),
});

// When author updates their name, update denormalized data
export const updateUserName = mutation({
  args: { userId: v.id('users'), name: v.string() },
  handler: async (ctx, args) => {
    await ctx.db.patch(args.userId, { name: args.name });

    // Update denormalized author names in posts
    const posts = await ctx.db
      .query('posts')
      .withIndex('by_userId', (q) => q.eq('userId', args.userId))
      .collect();

    for (const post of posts) {
      await ctx.db.patch(post._id, { authorName: args.name });
    }
  },
});

// convex/schema.ts
import { defineSchema, defineTable } from 'convex/server';
import { v } from 'convex/values';

export default defineSchema({
  // User management
  users: defineTable({
    email: v.string(),
    name: v.string(),
    avatarUrl: v.optional(v.string()),
    role: v.union(v.literal('user'), v.literal('admin')),
    emailVerified: v.optional(v.boolean()),
    isActive: v.optional(v.boolean()),
    lastLoginAt: v.optional(v.number()),
    createdAt: v.number(),
    updatedAt: v.number(),
  })
    .index('by_email', ['email'])
    .index('by_role', ['role']),

  userProfiles: defineTable({
    userId: v.id('users'),
    bio: v.optional(v.string()),
    timezone: v.optional(v.string()),
    theme: v.optional(v.union(v.literal('light'), v.literal('dark'), v.literal('system'))),
    createdAt: v.number(),
    updatedAt: v.number(),
  }).index('by_userId', ['userId']),

  // Authentication
  sessions: defineTable({
    userId: v.id('users'),
    token: v.string(),
    expiresAt: v.number(),
    createdAt: v.number(),
  })
    .index('by_token', ['token'])
    .index('by_userId', ['userId']),

  verificationTokens: defineTable({
    email: v.string(),
    token: v.string(),
    type: v.union(v.literal('email_verification'), v.literal('password_reset')),
    expiresAt: v.number(),
    createdAt: v.number(),
  })
    .index('by_token', ['token'])
    .index('by_email', ['email']),

  // Subscriptions
  subscriptions: defineTable({
    userId: v.id('users'),
    status: v.union(v.literal('active'), v.literal('canceled'), v.literal('past_due')),
    stripeSubscriptionId: v.optional(v.string()),
    currentPeriodEnd: v.optional(v.number()),
    createdAt: v.number(),
    updatedAt: v.number(),
  })
    .index('by_userId', ['userId'])
    .index('by_status', ['status']),

  // Audit
  auditLogs: defineTable({
    userId: v.optional(v.id('users')),
    action: v.string(),
    entity: v.string(),
    createdAt: v.number(),
  })
    .index('by_userId', ['userId'])
    .index('by_createdAt', ['createdAt']),
});