brennan•5d ago

Strange Table Corruption on Database Import?

I'm attaching a markdown report of the problem, analysis, research and findings. I think I can work around it, by creating the separate table (teams) and just removing (orgs), but would be nice to know what is causing this issue and how to avoid / fix it.

message.txt

1 Reply

brennanOP•5d ago

Convex Database Import Corruption Bug Issue Summary After importing production data into a local Convex development environment using convex import --replace-all, the orgs table becomes corrupted. Insert operations to the orgs table return IDs from the images table namespace but don't actually create any documents. Discovery Timeline Initial Symptoms 1. Running migration scripts to create personal organizations for users was failing 2. Error messages showed:

Found ID "md711jajjm11vz1eyrer5t9g5x7pz5dk" from table 'images', which does not match the table name in validator 'v.id("orgs")'

3. The orgs table wasn't appearing in the Convex dashboard Investigation Process #### 1. Schema Verification - Confirmed the orgs table was properly defined in convex/schema.ts - Schema definition was correct with proper types:

  orgs: defineTable({
    name: v.string(),
    ownerId: v.id("users"),
    plan: v.optional(v.string()),
    createdAt: v.number(),
    context: v.optional(v.string()),
    schemaSpaces: v.optional(v.string()),
    scope: v.optional(v.id("orgs"))
  })

  orgs: defineTable({
    name: v.string(),
    ownerId: v.id("users"),
    plan: v.optional(v.string()),
    createdAt: v.number(),
    context: v.optional(v.string()),
    schemaSpaces: v.optional(v.string()),
    scope: v.optional(v.id("orgs"))
  })

#### 2. User ID Verification - Initially suspected the ownerId might be using Clerk ID instead of Convex user ID - Added extensive logging to verify:

  Current user: {
    _id: 'j574zcb[REDACTED]',  // Valid Convex ID
    clerkId: 'user_2zwP[REDACTED]',
    email: '[REDACTED]',
    name: '[REDACTED]'
  }

  Current user: {
    _id: 'j574zcb[REDACTED]',  // Valid Convex ID
    clerkId: 'user_2zwP[REDACTED]',
    email: '[REDACTED]',
    name: '[REDACTED]'
  }

- Confirmed user ID was correct and could be read from database #### 3. Insert Operation Testing Created debug mutations to test the issue: Test 1: Direct org insertion

const orgId = await ctx.db.insert("orgs", {
  name: "Test Org",
  ownerId: user._id,
  plan: 'free',
  createdAt: Date.now(),
  context: "personal",
  schemaSpaces: undefined,
  scope: undefined,
})

const orgId = await ctx.db.insert("orgs", {
  name: "Test Org",
  ownerId: user._id,
  plan: 'free',
  createdAt: Date.now(),
  context: "personal",
  schemaSpaces: undefined,
  scope: undefined,
})

Results: - Insert returned ID: md72zwsmjygjrc4hq8hdp3ptmx7pyw2d (images table pattern) - Reading back the ID: null (document doesn't exist) - Delete attempt: Error: Delete on nonexistent document ID Test 2: Query the returned IDs directly - Attempted to query IDs like md748zp83d4vgnnnqk33cwst8s7py2sp - Result: Documents don't exist anywhere in the database #### 4. Alternative Table Testing Created identical teams and teamUsers tables to test if corruption was table-specific:

teams: defineTable({
  name: v.string(),
  ownerId: v.id("users"),
  plan: v.optional(v.string()),
  createdAt: v.number(),
  context: v.optional(v.string()),
  schemaSpaces: v.optional(v.string()),
  scope: v.optional(v.id("teams"))
})

teams: defineTable({
  name: v.string(),
  ownerId: v.id("users"),
  plan: v.optional(v.string()),
  createdAt: v.number(),
  context: v.optional(v.string()),
  schemaSpaces: v.optional(v.string()),
  scope: v.optional(v.id("teams"))
})

Result: Teams table worked perfectly! - Insert operations returned valid team IDs - Documents were created and could be read back - Join table teamUsers also worked correctly Root Cause Analysis The issue is table-name specific corruption in Convex's internal ID generation system: 1. When production data is imported with convex import --replace-all 2. If the production database doesn't have an orgs table 3. But does have an images table (even if empty) 4. Convex's internal table ID mapping gets corrupted 5. The string "orgs" gets mapped to the images table's ID namespace 6. All inserts to "orgs" return image IDs but don't create documents Evidence - Teams table (identical schema) works fine - Only "orgs" table name is affected - IDs returned match images table pattern (md7...) - Documents are never actually created - The corruption persists across server restarts Attempted Solutions 1. Initialize tables - Created dummy entries and deleted them (failed) 2. Force create tables - Created test data to initialize tables (failed)
3. Clean and reinitialize - Removed corrupted references and retried (failed) 4. Direct database operations - Bypassed abstractions (failed) 5. Alternative table names - Used "teams" instead of "orgs" (SUCCESS!) 6. Create table from dashboard - "orgs" was never showing in the Convex dashboard. Create Table was silently failing for "orgs" but not for any other table name not in the schema. I remove the orgs table. Did a convex dev. Then put the orgs table back into the schema and then did another convex dev. The problem went away. It seems like it was just some weird anomaly where the randomly generated ids weren't random enough and just happened collide. Is there an easier way to do this? Nearest I can tell, it seems like ids for a specific table get some kind random prefix assigned to the table so ids can be checked that they are from that table. It just happened there was "collision" with that random prefix generation.

Strange Table Corruption on Database Import?

Did you find this page helpful?