Data Integrity System

The Data Integrity System is an automated framework for detecting and fixing data inconsistencies in the Convex database. It validates denormalized fields, relational consistency, and calculated values across 12+ tables.

Architecture Overview

┌─────────────────────────────────────────────────────────────────────┐
│                    INTEGRITY AUDIT SYSTEM                           │
├─────────────────────────────────────────────────────────────────────┤
│                                                                     │
│  ┌──────────────────┐  ┌──────────────────┐  ┌──────────────────┐  │
│  │  Data Context    │  │   Validators     │  │  Fix Mutations   │  │
│  │  (dataContext)   │  │   (lib/)         │  │    (fixes/)      │  │
│  │                  │  │                  │  │                  │  │
│  │ - fetchAuditData │  │ - Pure functions │  │ - milestoneXyz   │  │
│  │ - 8 tables in    │  │ - Accept context │  │ - obsCascade     │  │
│  │   parallel       │  │ - Run in parallel│  │ - commitmentSum  │  │
│  │                  │  │                  │  │ - nameConsistency│  │
│  └──────────────────┘  └──────────────────┘  └──────────────────┘  │
│           │                    │                                    │
│           └────────────────────┼────────────────────────────────┐  │
│                                ▼                                │  │
│                        ┌──────────────────┐                     │  │
│                        │     Runner       │                     │  │
│                        │   (runner.ts)    │                     │  │
│                        │                  │                     │  │
│                        │ - Fetch once     │◄────────────────────┘  │
│                        │ - Validate all   │                        │
│                        │ - runFix         │                        │
│                        │ - runFromDashboard                        │
│                        └──────────────────┘                        │
│                                │                                    │
│         ┌──────────────────────┼──────────────────────┐            │
│         ▼                      ▼                      ▼            │
│  ┌─────────────┐       ┌──────────────┐      ┌────────────┐        │
│  │   Store     │       │   Discord    │      │  Cron Job  │        │
│  │   Reports   │       │   Alerts     │      │  (daily)   │        │
│  └─────────────┘       └──────────────┘      └────────────┘        │
│                                                                     │
└─────────────────────────────────────────────────────────────────────┘

Performance

The system uses a fetch-once, validate-parallel pattern for optimal performance:

Metric	Value
Data fetch	~8 parallel queries
Validation	~70ms (8 validators in parallel)
Full audit	~3s total

Previously: ~24 sequential queries. Now: ~8 parallel queries + parallel validation.

File Structure

packages/convex/integrityAudit/
├── index.ts                    # Main exports
├── runner.ts                   # Orchestration (runAll, runFix, runFromDashboard)
├── dataContext.ts              # AuditDataContext type & fetchAuditData()
├── discord.ts                  # Discord bot API integration
├── queries.ts                  # Internal queries for data fetching
├── mutations.ts                # Mutation handlers
├── checks/
│   └── utils.ts                # Helper: fetchAllPages
└── fixes/
    ├── types.ts                # FixResult types
    ├── milestoneConsistencyFix.ts
    ├── obsCascadeFix.ts
    ├── commitmentTimeSumFix.ts
    ├── designElementNameFix.ts
    ├── coordinatorNameFix.ts
    ├── assigneeNameFix.ts
    └── issueNameFix.ts

packages/convex/lib/integrityValidators/
├── types.ts                    # ValidationResult, AuditDataContext
├── milestoneConsistency.ts     # Pure validator function
├── obsCascade.ts
├── commitmentTimeSum.ts
├── totalTimeConsistency.ts
├── designElementNameConsistency.ts
├── coordinatorNameConsistency.ts
├── assigneeNameConsistency.ts
└── issueNameConsistency.ts

Validators

Core Validators

Validator	What it Validates	Error Type
Milestone Consistency	`dods.milestoneNumber` matches `issues.milestoneNumber`	`milestone_mismatch`
OBS Cascade	When issue is OBS, related `dodNotes.isObsolete` must be true	`obs_cascade_missing`, `obs_cascade_orphan`
Commitment Time Sum	Sum of `dodNotes.estimateTime` equals `assigneeReservations.currentCommitmentTime`	`commitment_sum_mismatch`
Total Time Consistency	Calculated total times match expected values	`total_time_mismatch`

Name Consistency Validators

Validator	Tables Affected	Field Synced
Design Element Name	issues, solutions, dods, assigneeReservations, milestones, dodNotifications, dodNotes	`designElementName`
Coordinator Name	issues, solutions, milestones, assigneeReservations	`coordinatorName`
Assignee Name	dods, dodNotes	`assigneeUserName`, `assigneeName`
Issue Name	dods, solutions, dodNotes	`issueName`

Running Checks

Run Modes

The integrity audit system supports three run modes:

Mode	Description
`checkOnly`	Run validation checks only (default)
`dryRunAutoFix`	Run checks, then simulate fixes without applying
`executeAutoFix`	Run checks, then apply fixes to failed validators

Run All Checks

typescript

await ctx.runAction(api.integrityAudit.runner.runAll, {
  milestoneNumber: 74,        // Optional: scope to milestone
  sendDiscordAlert: true,     // Optional: notify Discord (defaults to true)
  runMode: "checkOnly",       // Optional: checkOnly | dryRunAutoFix | executeAutoFix
});

Run from Dashboard

A public action is available for running audits directly from the Convex dashboard:

typescript

await ctx.runAction(api.integrityAudit.runner.runFromDashboard, {
  runMode: "checkOnly",       // Optional: defaults to "checkOnly"
  sendDiscordAlert: true,     // Optional: defaults to true
});

This action has sensible defaults and can be invoked without any arguments for a quick full audit with Discord notifications.

Run Single Check

typescript

await ctx.runAction(api.integrityAudit.runner.runCheck, {
  checkName: "milestoneConsistency",
  milestoneNumber: 74,
});

Run Check with Auto-Fix

typescript

// Step 1: Dry run (simulate)
const { checkResult, fixResult } = await ctx.runAction(
  api.integrityAudit.runner.runCheckAndFix,
  {
    validator: "designElementNameConsistency",
    autoFix: true,
    dryRun: true,  // Simulate only
  }
);

// Step 2: Review results, then apply
const { fixResult: actual } = await ctx.runAction(
  api.integrityAudit.runner.runCheckAndFix,
  {
    validator: "designElementNameConsistency",
    autoFix: true,
    dryRun: false,  // Apply fixes
  }
);

Fix Mutations

Dry-Run Pattern

All fixes follow a dry-run-first pattern:

Run fix with dryRun: true to simulate
Review results (fixed, failed, skipped counts)
Run with dryRun: false to apply

System Attribution

All fixes are marked with a system user for audit trail:

typescript

// Patches include:
{
  designElementName: expected,
  lastChangedBy: "system:integrity-fix:designElementName",
  updatedAt: Date.now(),
}

Batched Operations

Fixes handle large datasets via pagination:

typescript

while (!isDone) {
  const page = await ctx.db
    .query("issues")
    .paginate({ cursor, numItems: 100 });

  for (const record of page.page) {
    // Process each record
  }

  cursor = page.continueCursor;
  isDone = page.isDone;
}

Discord Integration

Setup

Required environment variables:

bash

DISCORD_BOT_TOKEN=your_bot_token
DISCORD_INTEGRITY_CHANNEL_ID=123456789012345678
DISCORD_INTEGRITY_MENTION_USER_ID=987654321098765432
CONVEX_DASHBOARD_URL=https://dashboard.convex.dev/t/team/project/deployment

Alert Format

Discord receives a compact summary format optimized for large datasets:

Pass/fail counts per validator
Total records checked
Duration and scope information
Button linking to Convex dashboard

@YourName Data Integrity Audit Complete

Scope: Full | Duration: 3.2s

✅ 6 passed | ❌ 2 failed

Failed:
• totalTimeConsistency (3 errors)
• assigneeNameConsistency (12 errors)

[🔍 View in Convex Dashboard]

The compact format prevents Discord API 400 errors on large datasets with many error details.

Automatic Scheduling

A daily cron job runs all checks at 6 AM UTC:

typescript

// packages/convex/crons.ts
crons.daily(
  "integrity-audit-daily",
  { hourUTC: 6, minuteUTC: 0 },
  internal.integrityAudit.runner.runAll,
  { sendDiscordAlert: true }
);

Storage Schema

Check Results

typescript

integrityCheckResults: defineTable({
  runId: v.string(),
  timestamp: v.number(),
  validator: v.string(),
  isValid: v.boolean(),
  checkedRecords: v.number(),
  duration: v.number(),
  errors: v.array(v.object({
    type: v.string(),
    message: v.string(),
    recordId: v.string(),
    expected: v.optional(v.string()),
    actual: v.optional(v.string()),
  })),
  expiresAt: v.number(),  // 30-day retention
})

Fix Results

typescript

integrityFixResults: defineTable({
  runId: v.string(),
  timestamp: v.number(),
  validator: v.string(),
  processed: v.number(),
  fixed: v.number(),
  failed: v.number(),
  skipped: v.number(),
  dryRun: v.boolean(),
  expiresAt: v.number(),
})

Quick Reference

Action	Function	Args
Run all checks	`runner.runAll`	`{ milestoneNumber?, sendDiscordAlert?, runMode? }`
Dashboard action	`runner.runFromDashboard`	`{ runMode?, sendDiscordAlert? }`
Run one check	`runner.runCheck`	`{ checkName, milestoneNumber? }`
Fix validator	`runner.runFix`	`{ validator, dryRun? }`
Check + fix	`runner.runCheckAndFix`	`{ validator, autoFix, dryRun? }`

Design Principles

Dry-Run First - Always test fixes before applying
Batched Operations - Handle 10k+ records via pagination
System Attribution - All fixes marked for audit trail
Graceful Degradation - Missing env vars skip Discord, don't crash
30-Day Retention - Results auto-expire
No Hard Deletes - Use soft-delete only
Modular Validators - Each check is independent
Type Safety - Full TypeScript with strict mode

Data Integrity System ​

Architecture Overview ​

Performance ​

File Structure ​

Validators ​

Core Validators ​

Name Consistency Validators ​

Running Checks ​

Run Modes ​

Run All Checks ​

Run from Dashboard ​

Run Single Check ​

Run Check with Auto-Fix ​

Fix Mutations ​

Dry-Run Pattern ​

System Attribution ​

Batched Operations ​

Discord Integration ​

Setup ​

Alert Format ​

Automatic Scheduling ​

Storage Schema ​

Check Results ​

Fix Results ​

Quick Reference ​

Design Principles ​

Data Integrity System

Architecture Overview

Performance

File Structure

Validators

Core Validators

Name Consistency Validators

Running Checks

Run Modes

Run All Checks

Run from Dashboard

Run Single Check

Run Check with Auto-Fix

Fix Mutations

Dry-Run Pattern

System Attribution

Batched Operations

Discord Integration

Setup

Alert Format

Automatic Scheduling

Storage Schema

Check Results

Fix Results

Quick Reference

Design Principles