Convex Migration Guide

This project currently uses Drizzle ORM + Neon Postgres. This skill guides migration to Convex as the backend database.

Current Stack (What We're Migrating From)

• ORM: Drizzle ORM 0.45.x with drizzle-kit for migrations
• Database: Neon Postgres (serverless)
• Schema: src/db/schema.ts -- 4 tables with enums, composite primary keys
• Client: src/db/client.ts -- Drizzle client with Neon connection
• Migrations: src/db/migrations/ -- SQL migration files

Current Tables

Current Enums

• sync_type: "customers" | "products" | "invoices" | "payments"
• sync_status: "running" | "success" | "failed"
• tripletex_env: "sandbox" | "production"

Convex Equivalents

Schema Translation

The current Drizzle schema in src/db/schema.ts maps to Convex like this:

typescript
1// convex/schema.ts
2import { defineSchema, defineTable } from "convex/server";
3import { syncType, syncStatus, tripletexEnv } from "./validators";
4
5export default defineSchema({
6	syncState: defineTable({
7		syncType: syncType,
8		tripletexEnv: tripletexEnv,
9		lastSyncAt: v.optional(v.number()), // epoch ms
10		status: syncStatus,
11		errorMessage: v.optional(v.string()),
12		recordsProcessed: v.number(),
13		recordsFailed: v.number(),
14		startedAt: v.number(), // epoch ms
15		completedAt: v.optional(v.number()),
16	})
17		.index("by_type_and_env", ["syncType", "tripletexEnv"])
18		.index("by_status", ["status"]),
19
20	customerMapping: defineTable({
21		rubicCustomerNo: v.string(),
22		tripletexEnv: tripletexEnv,
23		tripletexCustomerId: v.number(),
24		lastSyncedAt: v.number(),
25		hash: v.optional(v.string()),
26	})
27		.index("by_rubic_and_env", ["rubicCustomerNo", "tripletexEnv"]),
28
29	productMapping: defineTable({
30		rubicProductCode: v.string(),
31		tripletexEnv: tripletexEnv,
32		tripletexProductId: v.number(),
33		lastSyncedAt: v.number(),
34		hash: v.optional(v.string()),
35	})
36		.index("by_rubic_and_env", ["rubicProductCode", "tripletexEnv"]),
37
38	invoiceMapping: defineTable({
39		rubicInvoiceId: v.number(),
40		tripletexEnv: tripletexEnv,
41		rubicInvoiceNumber: v.number(),
42		tripletexInvoiceId: v.number(),
43		lastSyncedAt: v.number(),
44		paymentSynced: v.boolean(),
45	})
46		.index("by_rubic_and_env", ["rubicInvoiceId", "tripletexEnv"]),
47});

Key Translation Rules

Function Patterns

Convex replaces raw SQL / Drizzle queries with typed functions. Extract shared validators into a convex/validators.ts file so they can be reused across schema and functions:

typescript
1// convex/validators.ts
2import { v } from "convex/values";
3
4export const syncType = v.union(
5	v.literal("customers"),
6	v.literal("products"),
7	v.literal("invoices"),
8	v.literal("payments"),
9);
10
11export const syncStatus = v.union(
12	v.literal("running"),
13	v.literal("success"),
14	v.literal("failed"),
15);
16
17export const tripletexEnv = v.union(
18	v.literal("sandbox"),
19	v.literal("production"),
20);

Use these validators in function args for consistent type safety (not v.string()):

Query (read data):

typescript
1// convex/syncState.ts
2import { query } from "./_generated/server";
3import { syncType, tripletexEnv } from "./validators";
4
5export const getLatest = query({
6	args: { syncType, tripletexEnv },
7	handler: async (ctx, args) => {
8		return await ctx.db
9			.query("syncState")
10			.withIndex("by_type_and_env", (q) =>
11				q.eq("syncType", args.syncType).eq("tripletexEnv", args.tripletexEnv),
12			)
13			.order("desc")
14			.first();
15	},
16});

Mutation (write data):

typescript
1// convex/syncState.ts
2import { mutation } from "./_generated/server";
3import { syncType, tripletexEnv } from "./validators";
4
5export const startSync = mutation({
6	args: { syncType, tripletexEnv },
7	handler: async (ctx, args) => {
8		return await ctx.db.insert("syncState", {
9			syncType: args.syncType,
10			tripletexEnv: args.tripletexEnv,
11			status: "running",
12			recordsProcessed: 0,
13			recordsFailed: 0,
14			startedAt: Date.now(),
15		});
16	},
17});

Action (external API calls):

Sync logic that calls Rubic/Tripletex APIs should use actions, since they can call external services:

typescript
1// convex/sync.ts
2import { action } from "./_generated/server";
3import { api } from "./_generated/api";
4import { tripletexEnv } from "./validators";
5
6export const syncCustomers = action({
7	args: { tripletexEnv },
8	handler: async (ctx, args) => {
9		// Call external APIs
10		const customers = await fetchFromRubic();
11
12		// Write to Convex DB via mutations
13		for (const customer of customers) {
14			await ctx.runMutation(api.customerMapping.upsert, {
15				rubicCustomerNo: customer.customerNo,
16				tripletexEnv: args.tripletexEnv,
17				// ...
18			});
19		}
20	},
21});

Next.js Integration

typescript
1// In Server Components or Route Handlers
2import { fetchQuery, fetchMutation } from "convex/nextjs";
3import { api } from "@/convex/_generated/api";
4
5// Read
6const state = await fetchQuery(api.syncState.getLatest, {
7	syncType: "customers",
8	tripletexEnv: "production",
9});
10
11// Write
12await fetchMutation(api.syncState.startSync, {
13	syncType: "customers",
14	tripletexEnv: "production",
15});

Requires NEXT_PUBLIC_CONVEX_URL and CONVEX_URL environment variables.

Migration Steps

1. Install Convex: bun add convex and npx convex dev to initialize
2. Create schema: convex/schema.ts (see translation above)
3. Create functions: convex/*.ts for queries, mutations, actions
4. Migrate data: Write a one-time script to read from Neon and insert into Convex
5. Update API routes: Replace Drizzle queries with Convex function calls
6. Update sync logic: Move src/sync/ orchestration to Convex actions
7. Remove Drizzle: Remove drizzle-orm, drizzle-kit, @neondatabase/serverless, and src/db/

Key Differences to Keep in Mind

• No raw SQL -- all data access is through Convex functions
• No migrations -- schema changes are applied automatically by npx convex dev / npx convex deploy
• No connection pooling -- Convex handles connections internally
• Composite uniqueness -- enforce in mutation handlers (query by index, check existence), not at schema level
• Timestamps -- use Date.now() (epoch ms) instead of SQL TIMESTAMP WITH TIME ZONE
• Realtime by default -- Convex queries are reactive; the dashboard would get live sync status updates for free

Reference

• Convex Docs
• Convex Schema Docs
• Convex Next.js Guide
• Convex Functions