Database Schema

Convex database structure and relationships

Dirly uses Convex as its database with three main tables: tools, bookmarks, reviews and more

Schema Overview

The schema is defined in convex/schema.ts using Convex’s type-safe schema builder.


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.

Pattern Document Structure (Key Fields)

Field	Type	Description / Purpose
slug	string	Unique slug for URLs
approved	boolean	Whether the pattern is approved by moderators
submittedBy	string	Clerk ID of the submitting user
upvotes	number	Number of upvotes / popularity score
createdAt	number	Creation timestamp (milliseconds since Unix epoch)
pricing	string	`"Free"`, `"Freemium"`, or `"Paid"`
platforms	string[]	Supported platforms (e.g. `["Web", "iOS", "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"])

Review / Rating Fields

Field	Type	Description
userId	string	Clerk user ID of the user who left the review
toolId	Id`<"tools">`	Reference to the reviewed tool
rating	number	Star rating from 1 to 5
comment	string	Optional review text / comment
createdAt	number	Creation timestamp (Unix milliseconds)

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;

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:

Update schema.ts
Push changes: npx convex dev
Convex validates and migrates data

Adding required fields to existing tables requires providing default values or backfilling data first.