Skip to main content

​
Schema Overview

Meridian’s Convex schema is defined in convex/schema.ts. All tables are strongly typed with runtime validation using Convex validators.
The schema provides precise TypeScript types that are automatically generated in convex/_generated/dataModel.d.ts.

​
files

Stores metadata for uploaded CSV and JSON files with DuckDB processing status.

​
Fields

​
storageId
string
required
Convex/R2 storage identifier for the uploaded file
​
fileName
string
required
Original filename (e.g., sales_data.csv)
​
fileType
string
required
MIME type (e.g., text/csv, application/json)
​
fileSize
number
required
File size in bytes
​
uploadedBy
string
required
User ID of the uploader
​
uploadedAt
number
required
Unix timestamp (milliseconds) of upload time
​
duckdbTableName
string
Name of the DuckDB table created from this file
​
duckdbProcessed
boolean
Whether the file has been successfully loaded into DuckDB

​
Indexes

  • by_uploadedBy - Query files by user

​
Example Usage

convex/csv.ts
export const saveFile = mutation({
  args: {
    storageId: v.string(),
    fileName: v.string(),
    fileType: v.string(),
    fileSize: v.number(),
  },
  handler: async (ctx, args) => {
    const userId = await checkAuth(ctx)
    
    const fileId = await ctx.db.insert('files', {
      storageId: args.storageId,
      fileName: args.fileName,
      fileType: args.fileType,
      fileSize: args.fileSize,
      uploadedBy: userId,
      uploadedAt: Date.now(),
    })
    
    return fileId
  },
})

// Get all files for current user
export const getFiles = query({
  handler: async (ctx) => {
    const userId = await checkAuth(ctx)
    
    return await ctx.db
      .query('files')
      .filter(q => q.eq(q.field('uploadedBy'), userId))
      .order('desc')
      .collect()
  },
})

​
insightsCache

Caches AI-generated insights to avoid redundant API calls for identical data.

​
Fields

​
cacheKey
string
required
Hash of tableName|query|columnCount|rowCount for cache lookups
​
tableName
string
required
DuckDB table name the insights are for
​
query
string
required
SQL query that produced the analyzed data
​
dataHash
string
required
Secondary hash for data signature validation
​
insights
array
required
Array of insight objects with structured findings
​
statisticalFindings
any
required
Full statistical analysis results including means, outliers, trends
​
createdAt
number
required
Unix timestamp when insights were generated
​
userId
string
required
User who generated the insights

​
Indexes

  • by_cacheKey - Fast cache lookups
  • by_userId - Query user’s cached insights

​
Example Usage

See Insights Cache API for detailed examples.

​
queryLog

Tracks all SQL queries executed against DuckDB tables with execution metadata.

​
Fields

​
query
string
required
The SQL query string executed (e.g., SELECT * FROM sales WHERE region = 'West')
​
executedAt
number
required
Unix timestamp (milliseconds) of execution time
​
userId
string
required
User who executed the query
​
tableName
string
required
DuckDB table the query was executed against
​
success
boolean
required
Whether the query executed successfully
​
error
string
Error message if success is false
​
sequenceNumber
number
required
Global sequence number for temporal ordering (used for rollback/replay)
​
resultMetadata
object
Query result statistics

​
Indexes

  • by_userId - Query logs by user
  • by_sequenceNumber - Temporal ordering for replay
  • by_tableName - Query logs by table
  • by_userId_tableName - Combined filtering (most commonly used)

​
Example Usage

See Query Log API for detailed examples.

​
agentThreads

Conversation threads for AI agents (Query Agent and Analysis Agent).

​
Fields

​
userId
string
required
User who owns this conversation thread
​
tableName
string
required
DuckDB table being analyzed in this thread
​
agentThreadId
string
required
External thread ID from Convex Agent system
​
agentName
string
required
Name of the agent (e.g., Query Agent, Analysis Agent)
​
title
string
Thread title (auto-generated from first user message)
​
createdAt
number
required
Unix timestamp when thread was created
​
updatedAt
number
required
Unix timestamp of last update
​
lastMessageAt
number
required
Unix timestamp of last message (for sorting)
​
lastMessageSummary
string
Preview of the last message (first 200 characters)
​
lastMode
union
Mode of the last message: query or analysis

​
Indexes

  • by_agentThreadId - Lookup thread by external ID
  • by_user_table - List threads for a user/table combination
  • by_user_table_lastMessageAt - Sorted thread list (most recent first)

​
Example Usage

convex/agent_utils.ts
export const listAgentThreads = query({
  args: { tableName: v.string() },
  handler: async (ctx, { tableName }) => {
    const userId = await checkAuth(ctx)
    
    const threads = await ctx.db
      .query('agentThreads')
      .withIndex('by_user_table_lastMessageAt', q =>
        q.eq('userId', userId).eq('tableName', tableName)
      )
      .collect()
    
    // Sort by most recent first
    return threads.sort((a, b) => b.lastMessageAt - a.lastMessageAt)
  },
})

​
agentMessages

Individual messages within agent conversation threads.

​
Fields

​
threadId
Id<'agentThreads'>
required
Reference to the parent thread
​
role
union
required
Message sender: user or assistant
​
userId
string
User ID (only for user messages)
​
agentName
string
Agent name (only for assistant messages)
​
mode
union
required
Message mode: query (SQL generation) or analysis (data exploration)
​
content
string
required
Full message content (user input or assistant response)
​
description
string
Summary or description of the response
​
commands
array
SQL commands generated by Query Agent (array of strings)
​
toolSteps
array
Tool execution steps for Analysis Agent
​
createdAt
number
required
Unix timestamp of message creation

​
Indexes

  • by_thread - Query all messages in a thread (chronologically)

​
Example Usage

convex/agent_utils.ts
export const getAgentThreadMessages = query({
  args: { agentThreadId: v.string() },
  handler: async (ctx, { agentThreadId }) => {
    const userId = await checkAuth(ctx)
    
    // Get thread
    const thread = await ctx.db
      .query('agentThreads')
      .withIndex('by_agentThreadId', q => q.eq('agentThreadId', agentThreadId))
      .unique()
    
    if (!thread || thread.userId !== userId) {
      throw new Error('Thread not found or access denied')
    }
    
    // Get messages
    const messages = await ctx.db
      .query('agentMessages')
      .withIndex('by_thread', q => q.eq('threadId', thread._id))
      .collect()
    
    return messages.sort((a, b) => a.createdAt - b.createdAt)
  },
})

​
notifications

Real-time activity notifications for collaborative features.

​
Fields

​
tableName
string
required
DuckDB table the notification relates to
​
userId
string
required
User who triggered the notification
​
userName
string
Display name of the user
​
userImage
string
Avatar URL of the user
​
type
union
required
Notification type: query, agent_query, agent_analysis, insights_generated, chart_created
​
message
string
required
Human-readable notification message
​
metadata
any
Additional context data (e.g., query text, chart config)
​
createdAt
number
required
Unix timestamp of notification creation

​
Indexes

  • by_tableName - Query notifications for a table
  • by_tableName_createdAt - Time-ordered notifications

​
Example Usage

convex/notifications.ts
export const broadcastNotification = mutation({
  args: {
    tableName: v.string(),
    type: v.union(
      v.literal('query'),
      v.literal('agent_query'),
      v.literal('agent_analysis'),
      v.literal('insights_generated'),
      v.literal('chart_created')
    ),
    message: v.string(),
    metadata: v.optional(v.any()),
  },
  handler: async (ctx, args) => {
    const userId = await getAuthUserId(ctx)
    if (!userId) throw new Error('Not authenticated')
    
    // Get user info
    const user = await ctx.db.get(userId as Id<'users'>)
    
    return await ctx.db.insert('notifications', {
      tableName: args.tableName,
      userId: userId,
      userName: user?.name,
      userImage: user?.image,
      type: args.type,
      message: args.message,
      metadata: args.metadata,
      createdAt: Date.now(),
    })
  },
})

// Subscribe to latest notification
export const getLatestNotification = query({
  args: { tableName: v.string() },
  handler: async (ctx, { tableName }) => {
    return await ctx.db
      .query('notifications')
      .withIndex('by_tableName_createdAt', q => q.eq('tableName', tableName))
      .order('desc')
      .first()
  },
})

​
Type Generation

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

type QueryLog = Doc<'queryLog'>
type QueryLogId = Id<'queryLog'>
type InsightsCache = Doc<'insightsCache'>
type AgentThread = Doc<'agentThreads'>
type AgentMessage = Doc<'agentMessages'>
type File = Doc<'files'>
type Notification = Doc<'notifications'>

​
See Also

Query Log API

Query execution tracking system

Insights Cache

AI insights generation and caching

Convex Validators

Learn about Convex type validation

Build docs developers (and LLMs) love

Get started for freeTalk to us