Skip to main content

​
Introduction

Meridian uses Convex as its backend-as-a-service platform. The backend handles data storage, real-time synchronization, authentication, and AI agent orchestration.

​
Architecture

The Convex backend is organized into several functional modules:
  • Schema Definition - Type-safe data models defined in convex/schema.ts
  • Query Log - SQL query execution tracking and history
  • Insights Cache - AI-generated data insights with intelligent caching
  • Agent System - AI agents for query generation and data analysis
  • File Management - CSV/JSON file uploads and DuckDB integration
  • Notifications - Real-time activity broadcasting
  • Authentication - User management via Convex Auth

​
Core Tables

Meridian’s data model consists of seven primary tables:

queryLog

Tracks all SQL queries executed by users with metadata and results

insightsCache

Stores AI-generated insights with intelligent caching by data signature

agentThreads

Conversation threads for AI agents (Query Agent & Analysis Agent)

agentMessages

Individual messages within agent conversation threads

files

Uploaded CSV/JSON files with DuckDB processing metadata

notifications

Real-time activity notifications for collaborative features

​
Authentication

All API operations require authentication via Convex Auth. The checkAuth utility function validates user sessions: 
import { checkAuth } from './authFns'

export const myQuery = query({
  handler: async (ctx) => {
    const userId = await checkAuth(ctx)
    // userId is guaranteed to be valid or an error is thrown
  }
})

​
Indexing Strategy

Tables are indexed for optimal query performance:
  • User-scoped queries - by_userId indexes for filtering user data
  • Composite indexes - Multi-field indexes for complex filtering (e.g., by_userId_tableName)
  • Temporal queries - by_tableName_createdAt for time-based filtering
  • Unique lookups - by_agentThreadId, by_cacheKey for direct access

​
Real-time Features

Convex provides automatic real-time synchronization: 
// Frontend code - automatically updates on data changes
const logs = useQuery(api.queryLog.getQueryLogs, { tableName })
const notifications = useQuery(api.notifications.getLatestNotification, { tableName })

​
Type Safety

The schema provides full TypeScript type safety via code generation: 
import { Id } from './_generated/dataModel'
import { api } from './_generated/api'

type QueryLogId = Id<'queryLog'>
type InsightsCacheRecord = typeof api.insights.getCachedInsights._returnType

​
Data Validation

All fields use Convex validators for runtime type checking: 
import { v } from 'convex/values'

// String fields
v.string()

// Optional fields
v.optional(v.string())

// Complex objects
v.object({
  rowCount: v.optional(v.number()),
  columnCount: v.optional(v.number())
})

// Arrays
v.array(v.string())

// Union types
v.union(v.literal('query'), v.literal('analysis'))

// Any type (use sparingly)
v.any()

​
Query Patterns

​
Filtering by User

const userLogs = await ctx.db
  .query('queryLog')
  .withIndex('by_userId', q => q.eq('userId', userId))
  .collect()

​
Composite Filtering

const tableLogs = await ctx.db
  .query('queryLog')
  .withIndex('by_userId_tableName', q => 
    q.eq('userId', userId).eq('tableName', tableName)
  )
  .order('desc')
  .take(100)

​
Unique Lookups

const cached = await ctx.db
  .query('insightsCache')
  .withIndex('by_cacheKey', q => q.eq('cacheKey', key))
  .first()

​
Next Steps

Schema Reference

Detailed table schemas with all fields and validators

Query Log API

Query execution tracking and history replay

Insights Cache

AI insights generation and caching system

Convex Docs

Official Convex documentation

Build docs developers (and LLMs) love

Get started for freeTalk to us