Database Schema

Overview

Your Bible uses Convex as its real-time database backend for storing user-generated content: collections, verses, notes, and AI-generated stories. The schema is defined in convex/schema.ts and provides automatic type generation and real-time synchronization.

Convex automatically generates TypeScript types from the schema, ensuring type safety across your application.

Schema Definition

The complete schema as defined in convex/schema.ts:

import { defineSchema, defineTable } from "convex/server";
import { v } from "convex/values";

export default defineSchema({
  collections: defineTable({
    name: v.string(),
    userId: v.string(),
  }),
  collectionVerses: defineTable({
    bibleId: v.string(),
    verseId: v.string(),
    chapterId: v.string(),
    verseText: v.string(),
    collectionId: v.id("collections"),
  }).index("by_collection_id", ["collectionId"]),
  notes: defineTable({
    chapterId: v.string(),
    content: v.string(),
    userId: v.string(),
  }).index("by_chapter_id", ["chapterId"]),
  stories: defineTable({
    title: v.string(),
    bibleId: v.string(),
    chapterId: v.string(),
    chapterReference: v.string(),
    userId: v.string(),
    perspective: v.string(),
    setting: v.string(),
    tone: v.string(),
    storyLength: v.string(),
    story: v.string(),
  }).index("by_user_id", ["userId"]),
});

Tables

collections

User-created collections for organizing favorite Bible verses.

Purpose: Store collection metadata (name, owner) Fields:

_id

Id<'collections'>

Auto-generated unique identifier for the collection.Type: Convex IDGenerated: Automatically by ConvexExample: k17abc123def456

name

string

required

Display name of the collection.Example: "Favorite Psalms", "Comfort Verses", "Daily Reading"Constraints:

Minimum 1 character
Maximum 100 characters (enforced by app, not schema)

Validation: See src/schemas/collection-schema.ts

userId

string

required

ID of the user who owns this collection.Source: Better Auth user IDPurpose: Ensures users only see their own collectionsExample: "user_abc123"

_creationTime

number

Timestamp when the collection was created.Type: Unix timestamp (milliseconds)Generated: Automatically by ConvexExample: 1709481234567

Indexes: None (small table, queries by userId are fast) Relationships:

One-to-many with collectionVerses (one collection has many verses)

Usage Example:

// Create a collection
const collectionId = await ctx.runMutation(api.collections.create, {
  name: "My Favorites",
  userId: currentUser.id,
});

// Query user's collections
const myCollections = await ctx.runQuery(api.collections.list, {
  userId: currentUser.id,
});

collectionVerses

Individual verses stored within collections. Links Bible verses to collections.

Purpose: Store verses added to collections with full metadata Fields:

_id

Id<'collectionVerses'>

Auto-generated unique identifier for the verse entry.Generated: Automatically by Convex

bibleId

string

required

ID of the Bible translation this verse belongs to.Source: API.Bible translation IDExample: "de4e12af7f28f599-02" (KJV), "592420522e16049f-01" (ASV)Purpose: Allows verses from different translations

verseId

string

required

Unique identifier for the specific verse.Source: API.Bible verse IDFormat: {bookId}.{chapterId}.{verseNumber}Example: "GEN.1.1", "PSA.23.1", "JHN.3.16"

chapterId

string

required

ID of the chapter containing this verse.Source: API.Bible chapter IDFormat: {bookId}.{chapterNumber}Example: "GEN.1", "PSA.23", "JHN.3"Purpose: Enable navigation to full chapter context

verseText

string

required

The actual text content of the verse.Source: API.Bible verse contentExample: "In the beginning God created the heaven and the earth."Purpose: Display verse without additional API calls

collectionId

Id<'collections'>

required

Reference to the parent collection.Type: Foreign key to collections tablePurpose: Links verse to its collection

Indexes:

by_collection_id

index

Index on collectionId field for efficient verse queries.Purpose: Quickly retrieve all verses in a collectionQuery pattern: ctx.db.query("collectionVerses").withIndex("by_collection_id", q => q.eq("collectionId", id))

Relationships:

Many-to-one with collections (many verses belong to one collection)

Usage Example:

// Add verse to collection
await ctx.runMutation(api.collectionVerses.add, {
  collectionId: collection._id,
  bibleId: "de4e12af7f28f599-02",
  verseId: "JHN.3.16",
  chapterId: "JHN.3",
  verseText: "For God so loved the world...",
});

// Get all verses in a collection
const verses = await ctx.runQuery(api.collectionVerses.list, {
  collectionId: collection._id,
});

notes

User’s personal notes for Bible chapters with rich text formatting.

Purpose: Store chapter-specific notes with rich text content Fields:

_id

Id<'notes'>

Auto-generated unique identifier for the note.

chapterId

string

required

ID of the Bible chapter this note is about.Source: API.Bible chapter IDFormat: {bookId}.{chapterNumber}Example: "GEN.1", "MAT.5", "REV.22"Uniqueness: One note per chapter per user (enforced by app logic)

content

string

required

Rich text content of the note.Format: JSON string from Plate.js editorExample:

[
  {"type": "h3", "children": [{"text": "My Thoughts"}]},
  {"type": "p", "children": [{"text": "This chapter shows..."}]}
]

Editor: Plate.js rich text editorFeatures: Bold, italic, headings, lists, quotes, etc.

userId

string

required

ID of the user who owns this note.Purpose: Ensures notes are private to each user

Indexes:

by_chapter_id

index

Index on chapterId field for efficient note retrieval.Purpose: Quickly find note for current chapterQuery pattern: ctx.db.query("notes").withIndex("by_chapter_id", q => q.eq("chapterId", id))

Relationships:

Independent table (references Bible chapters via ID string)

Usage Example:

// Create or update note
await ctx.runMutation(api.notes.upsert, {
  chapterId: "GEN.1",
  content: JSON.stringify(editorContent),
  userId: currentUser.id,
});

// Get note for chapter
const note = await ctx.runQuery(api.notes.get, {
  chapterId: "GEN.1",
  userId: currentUser.id,
});

stories

AI-generated stories based on Bible passages with customizable parameters.

Purpose: Store AI-generated stories with generation parameters and metadata Fields:

_id

Id<'stories'>

Auto-generated unique identifier for the story.

title

string

required

User-provided title for the story.Example: "Creation from Adam's Perspective", "A Journey Through the Red Sea"Constraints: Minimum 1 character, maximum 200 characters

bibleId

string

required

ID of the Bible translation used as source.Source: API.Bible translation IDPurpose: Link story to original biblical text

chapterId

string

required

ID of the chapter the story is based on.Format: {bookId}.{chapterNumber}Example: "GEN.1", "EXO.14"

chapterReference

string

required

Human-readable chapter reference.Example: "Genesis 1", "Exodus 14", "Psalm 23"Purpose: Display-friendly reference

userId

string

required

ID of the user who generated this story.Purpose: Show user’s own stories, enforce rate limits

perspective

string

required

Narrative perspective for the story.Examples: "first-person", "third-person observer", "from Mary's perspective"Usage: Passed to AI for story generation

setting

string

required

Setting or context for the story.Examples: "ancient Israel", "modern day", "fantasy world"Usage: Influences AI story generation

tone

string

required

Emotional tone of the story.Examples: "contemplative", "adventurous", "solemn", "joyful"Usage: Guides AI’s writing style

storyLength

string

required

Desired length of the generated story.Options: "short", "medium", "long"Approximate lengths:

Short: 200-400 words
Medium: 400-800 words
Long: 800-1500 words

story

string

required

The AI-generated story content.Format: Plain text or markdownSource: Google Gemini AI responseLength: Varies based on storyLength parameter

Indexes:

by_user_id

index

Index on userId field for efficient user story queries.Purpose: List all stories created by a userQuery pattern: ctx.db.query("stories").withIndex("by_user_id", q => q.eq("userId", id))

Relationships:

Independent table (references Bible chapters and users via ID strings)

Usage Example:

// Create story
const storyId = await ctx.runMutation(api.stories.create, {
  title: "Creation Story",
  bibleId: "de4e12af7f28f599-02",
  chapterId: "GEN.1",
  chapterReference: "Genesis 1",
  userId: currentUser.id,
  perspective: "first-person from God's perspective",
  setting: "the beginning of time",
  tone: "majestic and powerful",
  storyLength: "medium",
  story: generatedStoryText,
});

// Get user's stories
const myStories = await ctx.runQuery(api.stories.list, {
  userId: currentUser.id,
});

Relationships Diagram

Indexes and Performance

Why Indexes Matter

Indexes dramatically improve query performance for common access patterns:

by_collection_id

Table: collectionVersesPurpose: Efficiently query all verses in a collectionWithout index: O(n) - scans all versesWith index: O(log n) - fast lookup

by_chapter_id

Table: notesPurpose: Quickly find note for current chapterBenefit: Instant note loading when viewing chapters

by_user_id

Table: storiesPurpose: List all stories for a userBenefit: Fast story dashboard loading

Query Patterns

// Efficient: Uses by_collection_id index
const verses = await ctx.db
  .query("collectionVerses")
  .withIndex("by_collection_id", (q) =>
    q.eq("collectionId", collectionId)
  )
  .collect();

Schema Migrations

Convex handles schema migrations automatically. Changes are applied when you save the schema file.

Adding a New Field

Update Schema

Edit convex/schema.ts to add the new field:

collections: defineTable({
  name: v.string(),
  userId: v.string(),
  description: v.optional(v.string()), // New field
}),

Save and Deploy

Convex automatically detects the change and updates the schema.

# If running npx convex dev, it auto-deploys
# Otherwise:
npx convex deploy

Update Types

Convex regenerates TypeScript types automatically. Import updated types:

import { Doc } from "convex/_generated/dataModel";

type Collection = Doc<"collections">;
// Now includes optional 'description' field

Handle Existing Data

Existing documents won’t have the new field. Use optional fields or provide defaults:

const description = collection.description ?? "No description";

Adding an Index

// Add index to schema
collections: defineTable({
  name: v.string(),
  userId: v.string(),
})
.index("by_user_id", ["userId"]), // New index

// Use in queries
const userCollections = await ctx.db
  .query("collections")
  .withIndex("by_user_id", (q) => q.eq("userId", userId))
  .collect();

Removing a Field

Be careful when removing fields. Ensure no code references the field before removal.

Update All Code

Remove all references to the field from your codebase.

Update Schema

Remove the field from the schema definition.

Deploy Changes

Convex will ignore the field in existing documents, but won’t delete the data.

Clean Up Data (Optional)

Write a migration script to remove the field from existing documents if needed.

Best Practices

Use Indexes for Common Queries

Add indexes for fields you frequently query:

// Good: Index on commonly queried field
.index("by_user_id", ["userId"])

// Consider: Compound indexes for complex queries
.index("by_user_and_date", ["userId", "_creationTime"])

Keep Documents Small

Store large text in separate documents or external storage
Current design is good: story content is reasonable size
Avoid storing binary data directly in Convex

Use Optional Fields for Flexibility

// Good: Optional field for future features
tags: v.optional(v.array(v.string())),

// Can add later without breaking existing documents

Validate Data in Mutations

// Validate before inserting
export const create = mutation({
  args: {
    name: v.string(),
    userId: v.string(),
  },
  handler: async (ctx, args) => {
    if (args.name.length < 1 || args.name.length > 100) {
      throw new Error("Invalid name length");
    }
    return await ctx.db.insert("collections", args);
  },
});

Use Consistent ID Formats

API.Bible IDs: Store as-is (e.g., "GEN.1.1")
User IDs: Store from Better Auth (e.g., "user_abc123")
Convex IDs: Use generated IDs (e.g., Id<"collections">)

Data Access Patterns

Reading Data

// Queries are reactive - updates automatically
const collections = useQuery(api.collections.list, {
  userId: user.id,
});

// Re-renders when data changes

Pagination

// Paginate large datasets
export const listPaginated = query({
  args: {
    userId: v.string(),
    paginationOpts: paginationOptsValidator,
  },
  handler: async (ctx, args) => {
    return await ctx.db
      .query("stories")
      .withIndex("by_user_id", (q) => q.eq("userId", args.userId))
      .order("desc")
      .paginate(args.paginationOpts);
  },
});

Security Considerations

Always validate user access in Convex functions. The schema doesn’t enforce permissions.

Access Control Example

// Validate user can access collection
export const get = query({
  args: { id: v.id("collections"), userId: v.string() },
  handler: async (ctx, args) => {
    const collection = await ctx.db.get(args.id);
    
    if (!collection) {
      throw new Error("Collection not found");
    }
    
    if (collection.userId !== args.userId) {
      throw new Error("Unauthorized");
    }
    
    return collection;
  },
});

Convex Documentation

Official Convex documentation and guides

Project Structure

How Convex integrates with the project

Integrations

Convex integration details

API Reference

API documentation for Convex functions

Contributing

Reference

Overview

Schema Definition

Tables

collections

collectionVerses

notes

stories

Relationships Diagram

Indexes and Performance

Why Indexes Matter

by_collection_id

by_chapter_id

by_user_id

Query Patterns

Schema Migrations

Adding a New Field

Adding an Index

Removing a Field

Best Practices

Data Access Patterns

Reading Data

Security Considerations

Access Control Example

Convex Documentation

Project Structure

Integrations

API Reference

Build docs developers (and LLMs) love

Contributing

Reference

​Overview

​Schema Definition

​Tables

​collections

​collectionVerses

​notes

​stories

​Relationships Diagram

​Indexes and Performance

​Why Indexes Matter

by_collection_id

by_chapter_id

by_user_id

​Query Patterns

​Schema Migrations

​Adding a New Field

​Adding an Index

​Removing a Field

​Best Practices

​Data Access Patterns

​Reading Data

​Pagination

​Security Considerations

​Access Control Example

​Related Documentation

Convex Documentation

Project Structure

Integrations

API Reference

Build docs developers (and LLMs) love

Overview

Schema Definition

Tables

collections

collectionVerses

notes

stories

Relationships Diagram

Indexes and Performance

Why Indexes Matter

Query Patterns

Schema Migrations

Adding a New Field

Adding an Index

Removing a Field

Best Practices

Data Access Patterns

Reading Data

Pagination

Security Considerations

Access Control Example

Related Documentation