Database Schema Definition

Relevant source files

The following files were used as context for generating this wiki page:

• convex/schema.ts
• convex/betterAuth/schema.ts
• convex/model/topics/mutations.ts
• convex/model/affiliateLinks/mutations.ts
• convex/lib/utils.ts
• AGENTS.md

The database schema defines the structure, relationships, and constraints for all data stored within the Convex backend. It organizes information into domain-specific tables such as talks, topics, speakers, and users, while utilizing join tables to manage many-to-many relationships.

This system relies on Convex for transactional integrity and Better Auth for user identity management. The schema is defined primarily in convex/schema.ts, which aggregates sub-schemas from specific feature modules.

Sources: convex/schema.ts:1-12, README.md:1-12, AGENTS.md:65-72

Core Data Models

The system architecture follows a feature-based organization. Each domain (e.g., topics, affiliate links) defines its own table structure which is then imported into the main schema.

Application Tables

The application logic revolves around content consumption (talks and clips) and organization (topics and collections).

Table Name	Primary Purpose	Key Fields
talks	Stores primary video or audio content.	title, slug, speakerId
topics	Categorizes talks by biblical themes.	title, slug, description
speakers	Profiles of individuals delivering talks.	firstName, lastName, ministry
clips	Segments of talks highlighted by users.	talkId, startTime, endTime
affiliateLinks	External resource recommendations.	title, url, type

Sources: convex/schema.ts:4-10, convex/model/topics/mutations.ts:79-85, convex/model/affiliateLinks/mutations.ts:20-30

Relationship Diagram

The following diagram illustrates how the core entities relate to one another, including the join tables used for categorization and user personalization.

Authentication Schema

User-related tables are managed via the Better Auth integration. These tables handle session management, account linking, and identity verification.

User and Session Tables

The user table stores profile information, while the session and account tables manage access tokens and third-party provider links.

Table	Index	Fields
user	email_name, userId	name, email, role, banned
session	token, userId	expiresAt, ipAddress, userAgent
account	accountId, providerId	userId, accessToken, password
verification	identifier	value, expiresAt

Sources: convex/betterAuth/schema.ts:10-70

Relationship and Join Tables

Many-to-many relationships are explicitly defined using join tables to facilitate efficient indexing and querying.

Content Categorization

The talksOnTopics table maps talks to their respective topics.

• Indices: by_talkId, by_topicId, and a composite by_talkId_and_topicId.
• Logic: Mutations like addTalkToTopic validate the existence of both entities and ensure no duplicate associations exist before insertion.

Sources: convex/schema.ts:22-31, convex/model/topics/mutations.ts:36-50

User Engagement

Several tables track user interactions with content:

• userFavoriteTalks: Connects a userId (string) to a talkId (Convex ID).
• userFinishedTalks: Tracks completion, indexed by userId to allow listing a user's history.
• userFavoriteSpeakers: Allows users to follow specific speakers.

Sources: convex/schema.ts:40-66

Schema Constraints and Utilities

Slug Management

Most entities utilize unique slugs for URL routing. The generateSlug utility ensures uniqueness by checking the by_slug index and appending numeric suffixes if a collision occurs.

export async function generateSlug(
  ctx: QueryCtx | MutationCtx,
  table: SlugTable,
  baseText: string,
  excludeId?: string
) {
  const baseSlug = slugify(baseText);
  const baseSlugExists = await slugExists(ctx, table, baseSlug, excludeId);

  if (!baseSlugExists) return baseSlug;

  let counter = 2;
  let candidateSlug = `${baseSlug}-${counter}`;

  while (await slugExists(ctx, table, candidateSlug, excludeId)) {
    counter += 1;
    candidateSlug = `${baseSlug}-${counter}`;
  }
  return candidateSlug;
}

Sources: convex/lib/utils.ts:101-125, convex/model/topics/mutations.ts:82-84

System Fields

Every document in the schema automatically includes Convex system fields:

• _id: A unique v.id(tableName).
• _creationTime: A v.number() timestamp.

Sources: convex/_generated/ai/guidelines.md:144-148

Summary

The Database Schema Definition provides a relational structure optimized for a content platform. By separating core entities from their relationships via join tables and utilizing a dedicated authentication sub-schema, the architecture supports complex queries (like finding all talks for a specific topic) while maintaining strict type safety and transactional consistency through Convex.