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                           │
├─────────────────────────────────────────────────────────────────────┤
│                                                                     │
│  ┌──────────────────┐  ┌──────────────────┐  ┌──────────────────┐  │
│  │   Validators     │  │    Checkers      │  │  Fix Mutations   │  │
│  │   (lib/)         │  │    (checks/)     │  │    (fixes/)      │  │
│  │                  │  │                  │  │                  │  │
│  │ - Milestone      │  │ - run actions    │  │ - milestoneXyz   │  │
│  │ - OBS Cascade    │  │   per check      │  │ - obsCascade     │  │
│  │ - Time Sum       │  │ - fetch & run    │  │ - commitmentSum  │  │
│  │ - Name Sync      │  │   validators     │  │ - nameConsistency│  │
│  └──────────────────┘  └──────────────────┘  └──────────────────┘  │
│                                │                                    │
│                                ▼                                    │
│                        ┌──────────────────┐                         │
│                        │     Runner       │                         │
│                        │   (runner.ts)    │                         │
│                        │                  │                         │
│                        │ - runAll         │                         │
│                        │ - runCheck       │                         │
│                        │ - runFix         │                         │
│                        │ - runCheckAndFix │                         │
│                        └──────────────────┘                         │
│                                │                                    │
│         ┌──────────────────────┼──────────────────────┐            │
│         ▼                      ▼                      ▼            │
│  ┌─────────────┐       ┌──────────────┐      ┌────────────┐        │
│  │   Store     │       │   Discord    │      │  Cron Job  │        │
│  │   Reports   │       │   Alerts     │      │  (daily)   │        │
│  └─────────────┘       └──────────────┘      └────────────┘        │
│                                                                     │
└─────────────────────────────────────────────────────────────────────┘

File Structure

packages/convex/integrityAudit/
├── index.ts                    # Main exports
├── runner.ts                   # Orchestration (runAll, runCheck, runFix)
├── discord.ts                  # Discord bot API integration
├── queries.ts                  # Internal queries for data fetching
├── mutations.ts                # Mutation handlers
├── checks/
│   ├── utils.ts                # Helper: fetchAllPages, auditQueries
│   ├── milestoneConsistency.ts
│   ├── obsCascade.ts
│   ├── commitmentTimeSum.ts
│   ├── totalTimeConsistency.ts
│   ├── designElementNameConsistency.ts
│   ├── coordinatorNameConsistency.ts
│   ├── assigneeNameConsistency.ts
│   └── issueNameConsistency.ts
└── fixes/
    ├── types.ts                # FixResult types
    ├── milestoneConsistencyFix.ts
    ├── obsCascadeFix.ts
    ├── commitmentTimeSumFix.ts
    └── *NameFix.ts             # Name sync fixes

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 All Checks

typescript

await ctx.runAction(api.integrityAudit.runner.runAll, {
  milestoneNumber: 74,        // Optional: scope to milestone
  sendDiscordAlert: true,     // Optional: notify Discord
});

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 styled embeds with:

Status emoji per check (pass/fail/warning)
Record counts and duration
Error details (max 5 shown)
Button linking to Convex dashboard

@YourName Data Integrity Audit Complete (Milestone 75)

✅ Milestone Consistency
Checked: 847 records | Duration: 234ms

❌ Total Time Consistency
Checked: 523 records | Duration: 189ms
Errors (3):
• DOD abc123: expected 40, actual 35
• DOD def456: expected 20, actual 25

[🔍 View in Convex Dashboard]

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? }`
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 ​

File Structure ​

Validators ​

Core Validators ​

Name Consistency Validators ​

Running Checks ​

Run All Checks ​

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

File Structure

Validators

Core Validators

Name Consistency Validators

Running Checks

Run All Checks

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