Skip to main content
AiVault uses Convex as its database with three main tables: tools, bookmarks, and reviews.

​
Schema Overview

The schema is defined in convex/schema.ts using Convex’s type-safe schema builder.
convex/schema.ts
import { defineSchema, defineTable } from "convex/server";
import { v } from "convex/values";

export default defineSchema({
  tools: defineTable({...}),
  bookmarks: defineTable({...}),
  reviews: defineTable({...})
});

​
Tools Table

Stores all AI tools submitted to the directory.

​
Fields

convex/schema.ts
tools: defineTable({
  // Core fields
  name: v.string(),
  slug: v.string(),
  description: v.string(),
  longDescription: v.optional(v.string()),
  category: v.string(),
  tags: v.array(v.string()),
  websiteUrl: v.string(),
  logoUrl: v.optional(v.string()),
  
  // Pricing
  pricing: v.string(), // "Free", "Freemium", "Paid"
  pricingDetails: v.optional(v.string()),
  
  // Metadata
  upvotes: v.number(),
  submittedBy: v.string(), // userId
  approved: v.boolean(),
  createdAt: v.number(),
  featured: v.optional(v.boolean()),
  isNew: v.optional(v.boolean()),
  
  // Rich content
  features: v.optional(v.array(v.string())),
  useCases: v.optional(v.array(v.string())),
  pros: v.optional(v.array(v.string())),
  cons: v.optional(v.array(v.string())),
  platforms: v.optional(v.array(v.string())),
  lastUpdated: v.optional(v.string()),
  
  // Social links
  twitterUrl: v.optional(v.string()),
  githubUrl: v.optional(v.string()),
  discordUrl: v.optional(v.string()),
})

​
Indices

Convex uses indices for efficient querying:
convex/schema.ts
.index("by_slug", ["slug"])
.index("by_category", ["category"])
.index("by_approved", ["approved"])
.index("by_submittedBy", ["submittedBy"])
.index("by_upvotes", ["upvotes"])
.index("by_createdAt", ["createdAt"])
Indices are required for efficient filtering in Convex. Every query should use an index as the starting point.

​
Key Patterns

FieldTypePurpose
slugstringUnique URL-friendly identifier
approvedbooleanAdmin approval status (moderation)
submittedBystringClerk user ID of submitter
upvotesnumberPopularity metric
createdAtnumberUnix timestamp (milliseconds)
pricingstring”Free”, “Freemium”, or “Paid”
platformsstring[]”Web”, “iOS”, “Android”, “Desktop”, “API”, “Chrome Extension”

​
Bookmarks Table

Tracks user-saved tools.
convex/schema.ts
bookmarks: defineTable({
  userId: v.string(),
  toolId: v.id("tools"),
})
  .index("by_userId", ["userId"])
  .index("by_toolId", ["toolId"])
  .index("by_userId_and_toolId", ["userId", "toolId"])

​
Relationships

  • userId → Clerk user identifier
  • toolId → Foreign key reference to tools table

​
Query Patterns

// Get all bookmarks for a user
await ctx.db
  .query("bookmarks")
  .withIndex("by_userId", q => q.eq("userId", userId))
  .collect();

// Check if user bookmarked a specific tool
await ctx.db
  .query("bookmarks")
  .withIndex("by_userId_and_toolId", q => 
    q.eq("userId", userId).eq("toolId", toolId)
  )
  .first();
The composite index by_userId_and_toolId enables efficient bookmark existence checks without scanning all user bookmarks.

​
Reviews Table

User-submitted ratings and comments.
convex/schema.ts
reviews: defineTable({
  userId: v.string(),
  toolId: v.id("tools"),
  rating: v.number(),
  comment: v.string(),
  createdAt: v.number(),
})
  .index("by_toolId", ["toolId"])
  .index("by_userId", ["userId"])

​
Fields

FieldTypeDescription
userIdstringClerk user ID
toolIdId<"tools">Reference to tool
ratingnumber1-5 star rating
commentstringReview text
createdAtnumberTimestamp

​
Query Examples

// Get all reviews for a tool
const reviews = await ctx.db
  .query("reviews")
  .withIndex("by_toolId", q => q.eq("toolId", toolId))
  .collect();

// Calculate average rating
const avgRating = reviews.reduce((sum, r) => sum + r.rating, 0) / reviews.length;

​
Relationships Diagram

Convex doesn’t enforce foreign key constraints at the database level. Referential integrity is maintained through application logic.

​
Type Safety

Convex automatically generates TypeScript types from the schema: 
import { Doc, Id } from "./_generated/dataModel";

// Get full tool type
type Tool = Doc<"tools">;

// Get tool ID type
type ToolId = Id<"tools">;

// Use in functions
function displayTool(tool: Tool) {
  console.log(tool.name); // ✓ Type-safe
  console.log(tool.invalid); // ✗ Type error
}

​
Schema Migrations

Convex handles schema changes automatically:
  1. Update schema.ts
  2. Push changes: npx convex dev
  3. Convex validates and migrates data
Adding required fields to existing tables requires providing default values or backfilling data first.

​
Best Practices

Start every query with .withIndex(). Table scans are slow and may hit Convex limits.
// ✓ Good
await ctx.db.query("tools").withIndex("by_category", q => q.eq("category", cat)).collect();

// ✗ Bad - table scan
await ctx.db.query("tools").filter(q => q.eq(q.field("category"), cat)).collect();
Mark fields that may not always be present as optional with v.optional().
logoUrl: v.optional(v.string()) // May be missing
Use Date.now() for timestamps (Unix milliseconds) for efficient sorting and filtering.
createdAt: Date.now() // 1709467200000
If you frequently query by multiple fields, create a composite index.
.index("by_userId_and_toolId", ["userId", "toolId"])

​
Next Steps

Convex Backend

Learn how to query and mutate data

Authentication

Understand user identity and permissions

Build docs developers (and LLMs) love

Get started for freeTalk to us