Phase 3: Migrate Relational Queries from Drizzle RQB v1 → v2

phase3-rqb-v2-migration.md

Phase 3: Migrate Relational Queries from RQB v1 → v2

Context

After Phases 0-2, the codebase is on drizzle-orm@1.0.0-beta.15-859cf75 with:

• db.query → v2 relational query API (new syntax)
• db._query → v1 compat shim (old syntax, currently used by all 54 files)

Phase 3 migrates each file from db._query (v1) → db.query (v2), batch by batch.

PR: https://github.com/FlatFilers/obvious/pull/6499

Query Translation Cheat Sheet

Where Clauses

// BEFORE (v1): operator-based
where: eq(schema.threads.id, threadId)
where: and(eq(schema.threads.id, threadId), isNull(schema.threads.deletedAt))
where: or(eq(t.status, 'active'), eq(t.status, 'pending'))
where: inArray(t.id, ids)
where: gt(t.creditsRemaining, '0')
where: isNotNull(t.col)
where: not(eq(t.artifactId, input.artifactId))
where: sql`${schema.webhookSubscriptions.events} @> ${JSON.stringify([eventType])}::jsonb`

// BEFORE (v1): callback-based (same operators, just destructured)
where: (t, { eq, and, isNull }) => and(eq(t.id, id), isNull(t.deletedAt))

// AFTER (v2): object notation
where: { id: threadId }
where: { id: threadId, deletedAt: { isNull: true } }
where: { OR: [{ status: 'active' }, { status: 'pending' }] }
where: { id: { in: ids } }
where: { creditsRemaining: { gt: '0' } }
where: { col: { isNotNull: true } }
where: { artifactId: { ne: input.artifactId } }
where: { RAW: (t) => sql`${t.events} @> ${JSON.stringify([eventType])}::jsonb` }

Complex Nested Conditions

// BEFORE: and(eq(t.workspaceId, wsId), gt(t.credits, '0'), or(isNull(t.expiresAt), gt(t.expiresAt, now)))
// AFTER:
where: {
  workspaceId: wsId,
  creditsRemaining: { gt: '0' },
  OR: [
    { expiresAt: { isNull: true } },
    { expiresAt: { gt: new Date() } }
  ]
}

OrderBy

// BEFORE
orderBy: desc(t.createdAt)
orderBy: [asc(t.name), desc(t.createdAt)]

// AFTER
orderBy: { createdAt: 'desc' }
orderBy: { name: 'asc', createdAt: 'desc' }

With (Eager Loading) — UNCHANGED

// Same in both v1 and v2
with: { creator: true }
with: { creator: { columns: { id: true, name: true, email: true } } }
with: { template: true, creator: { columns: { id: true, name: true } } }

Columns — UNCHANGED

// Same in both v1 and v2
columns: { id: true, name: true, email: true }

Batch Plan

Each commit migrates a domain group from db._query → db.query.

#	Commit Message	Files	~Queries
1	refactor: migrate webhook + slack queries to RQB v2	6 files	~20
2	refactor: migrate template queries to RQB v2	3 files (template.service.ts is heaviest)	~40
3	refactor: migrate credit + analytics queries to RQB v2	3 files	~22
4	refactor: migrate thread + message queries to RQB v2	3 files	~6
5	refactor: migrate project + permissions queries to RQB v2	6 files	~35
6	refactor: migrate route queries to RQB v2	5 files	~5
7	refactor: migrate inngest queries to RQB v2	7 files	~10
8	refactor: migrate agent + e2e + auth queries to RQB v2	8 files	~25
9	test: update test mocks for RQB v2	test files	varies

Files by Batch

Batch 1: Webhook + Slack

• apps/api/src/services/webhook-subscription.service.ts
• apps/api/src/services/webhooks/webhook-authenticator.ts
• apps/api/src/routes/webhooks/unified.route.ts
• apps/api/src/routes/slack/status.route.ts
• apps/api/src/routes/slack/connect.route.ts
• apps/api/src/routes/slack/handlers/app-mention.handler.ts
• apps/api/src/routes/slack/handlers/member-joined-channel.handler.ts
• apps/api/src/routes/slack/handlers/message.handler.ts
• apps/api/src/routes/slack/link-user.route.ts
• apps/api/src/routes/slack/link-workspace.route.ts
• apps/api/src/routes/slack/oauth-callback.route.ts

Batch 2: Templates

• apps/api/src/services/template.service.ts (heaviest — ~20 queries)
• apps/api/src/services/template-publish.service.ts
• apps/api/src/services/template-preview.service.ts

Batch 3: Credits + Analytics

• apps/api/src/services/credit-balance.service.ts
• apps/api/src/services/credits-usage.service.ts
• apps/api/src/services/analytics.service.ts

Batch 4: Threads + Messages

• apps/api/src/services/thread.service.ts
• apps/api/src/services/message.service.ts
• apps/api/src/services/thread-search/indexing.service.ts

Batch 5: Project + Permissions

• apps/api/src/services/project.service.ts
• apps/api/src/services/permissions.service.ts
• apps/api/src/services/pinned-artifacts.service.ts
• apps/api/src/services/published-artifact.service.ts
• apps/api/src/services/god-mode.service.ts
• apps/api/src/services/feature-flag.service.ts

Batch 6: Routes

• apps/api/src/routes/tasks.ts
• apps/api/src/routes/liveblocks.ts
• apps/api/src/routes/public-api.route.ts
• apps/api/src/routes/hydrate.spec.md (if queries in spec)

Batch 7: Inngest

• apps/api/src/inngest/webhook-triggered-task-executor.ts
• apps/api/src/inngest/slack-agent-response.ts
• apps/api/src/inngest/objective-rebirth-checker.ts
• apps/api/src/inngest/obvious-agent-execution.ts
• apps/api/src/inngest/pdf-indexing.ts
• apps/api/src/inngest/file-extraction.ts
• apps/api/src/inngest/artifact-indexing.ts

Batch 8: Agents + E2E + Auth + Utils

• apps/api/src/agents/obvious-v2/tools/slack-operations.tool.ts
• apps/api/src/agents/obvious-v2/tools/comments.tool.ts
• apps/api/src/e2e-harness/multi-agent-orchestration.ts
• apps/api/src/e2e-harness/sandbox-timeout-scenarios.ts
• apps/api/src/e2e-harness/shared.ts
• apps/api/src/e2e-harness/shelldren-scenarios.ts
• apps/api/src/auth/better-auth.ts
• apps/api/src/utils/workspace-utils.ts

Batch 9: Tests

• apps/api/src/services/__tests__/credit-balance.service.test.ts
• apps/api/src/routes/__tests__/threads-access-control.e2e-test.ts
• apps/api/src/routes/__tests__/projects.e2e-test.ts
• apps/api/src/routes/__tests__/thread-folders-access-control.e2e-test.ts
• apps/api/src/routes/__tests__/checkpoints.e2e-test.ts
• apps/api/src/routes/__tests__/custom-modes-access-control.e2e-test.ts
• apps/api/src/services/workbook-export.service.ts
• apps/api/src/services/e2b-shell.service.ts
• apps/api/src/services/task.service.ts
• apps/api/src/services/objective-tracking.service.ts

Verification Per Batch

After each commit:

bun obvious typecheck --changed
bun obvious test --changed

After all batches complete:

bun run test                    # full suite
bun obvious typecheck           # full typecheck
npx drizzle-kit generate        # verify migration gen still works
bun obvious up                  # verify API starts and queries work

Final Cleanup (after Phase 3)

1. Delete 23 old relations() blocks from schema.ts
2. Remove import { relations } from 'drizzle-orm/_relations' from schema.ts
3. Clean up unused operator imports from migrated files (only where they're truly unused — many files still need eq, and, etc. for db.select/update/delete)

Learnings from Phase 0-2 Implementation

Version Discovery

• 1.0.0-beta.2 does NOT exist on npm. The beta dist-tag points to 1.0.0-beta.15-859cf75. Always check npm view drizzle-orm dist-tags --json first.
• Pin the exact version (1.0.0-beta.15-859cf75), not a range — this is a beta.

Breaking API Changes Not in Release Notes

1. drizzle() constructor now requires a config object: drizzle({ client, schema }) not drizzle(client, { schema }). This affected 5 files (db/index.ts, migrate.ts, reset.ts, verify-migrations.ts, e2e-setup.ts).
2. relations function moved from 'drizzle-orm' to 'drizzle-orm/_relations' for v1 compat. The new v2 API (defineRelations) is still in 'drizzle-orm'.
3. $inferSelect / $inferInsert no longer directly indexable on generic PgTableWithColumns<any>. Must use InferSelectModel<T> / InferInsertModel<T> from 'drizzle-orm' with a conditional type: T extends Table ? InferSelectModel<T> : never.

Generic Type Constraints

• PgTableWithColumns<any> alone no longer satisfies Table<TableConfig<Columns>> in drizzle v1.
• Fix: change T extends PgTableWithColumns<any> → T extends Table & PgTableWithColumns<any>.
• Some drizzle utility functions (getTableColumns, createInsertSchema) need explicit as Table casts when called with the generic T.

Type Strictness Regressions (92 errors)

All are pre-existing patterns exposed by drizzle v1's stricter type inference:

• Implicit any (22 errors): Column/table iteration APIs (getTableColumns()) now return stricter types.
• null vs string (9 errors): drizzle now correctly types nullable columns as string | null instead of string.
• {} → string (6 errors): inngest step.run() return types interact with drizzle's stricter generics.
• unknown types (8 errors): Similar step.run() interactions producing unknown instead of expected types.
• v2 where type mismatch (4 errors): published-artifact.service.ts and task.service.ts pass raw SQL to where on db._query — fixed naturally during Phase 3 migration.

drizzle-kit up

• drizzle-kit up failed with "No snapshot was found" despite 162 snapshot files in drizzle/meta/. Journal version is already "1". Likely already at latest format.

v2 Relations API (defineRelations)

• Syntax: defineRelations(schema, (r) => ({ tableName: { rel: r.one.target({ from: r.source.col, to: r.target.col }) } }))
• fields → from, references → to (single values for single-column relations)
• many() with no config for simple reverse relations
• Pass both schema AND relations to drizzle() for the compat bridge: drizzle({ client, schema, relations })

Practical Advice for Phase 3

• Do NOT remove old relations() from schema.ts until all files are migrated — db._query depends on them.
• Many files still need eq, and, sql for db.select/update/delete — only remove imports from files that truly no longer use them.
• The 4 type errors for raw SQL in where clauses will fix themselves when migrated to v2 object syntax.
• @kubiks/otel-drizzle@^2.1.0 peer dep is drizzle-orm: >=0.28.0 (no upper bound) — works fine with v1 beta.