convex-migration

name	convex-migration
description	Guide migration from the current Drizzle ORM / Neon Postgres stack to Convex. Use when the user asks about Convex, migrating the database, replacing Drizzle, or setting up Convex schema, functions, or queries. Provides schema translation, function patterns, and migration considerations specific to this project. Use when this capability is needed.
metadata	{"author":"frodeste"}

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

Table	Purpose	Key Fields
sync_state	Tracks each sync run	serial PK, sync_type enum, status enum, timestamps
customer_mapping	Rubic customerNo -> Tripletex customerId	composite PK (rubic_id, env), hash
product_mapping	Rubic productCode -> Tripletex productId	composite PK (rubic_id, env), hash
invoice_mapping	Rubic invoiceId -> Tripletex invoiceId	composite PK (rubic_id, env), payment_synced

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:

// convex/schema.ts
import { defineSchema, defineTable } from "convex/server";
import { syncType, syncStatus, tripletexEnv } from "./validators";

export default defineSchema({
	syncState: defineTable({
		syncType: syncType,
		tripletexEnv: tripletexEnv,
		lastSyncAt: v.optional(v.number()), // epoch ms
		status: syncStatus,
		errorMessage: v.optional(v.string()),
		recordsProcessed: v.number(),
		recordsFailed: v.number(),
		startedAt: v.number(), // epoch ms
		completedAt: v.optional(v.number()),
	})
		.index("by_type_and_env", ["syncType", "tripletexEnv"])
		.index("by_status", ["status"]),

	customerMapping: defineTable({
		rubicCustomerNo: v.string(),
		tripletexEnv: tripletexEnv,
		tripletexCustomerId: v.number(),
		lastSyncedAt: v.number(),
		: v.(v.()),
	})
		.(, [, ]),

	: ({
		: v.(),
		: tripletexEnv,
		: v.(),
		: v.(),
		: v.(v.()),
	})
		.(, [, ]),

	: ({
		: v.(),
		: tripletexEnv,
		: v.(),
		: v.(),
		: v.(),
		: v.(),
	})
		.(, [, ]),
});

Key Translation Rules

Drizzle / Postgres	Convex
serial("id").primaryKey()	Auto-generated _id (no serial IDs)
pgEnum("name", [...])	v.union(v.literal("a"), v.literal("b"))
Composite primary key	.index("name", ["field1", "field2"]) + unique enforcement in mutations
timestamp({ withTimezone: true })	v.number() (epoch ms via Date.now())
varchar("col", { length: N })	v.string() (no length limits in Convex)
integer("col")	v.number()
boolean("col")	v.boolean()
text("col")	v.string()
.notNull()	Field is required by default
.default(value)	Set in mutation handler, not in schema
NULL / nullable	v.optional(v.string())

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:

// convex/validators.ts
import { v } from "convex/values";

export const syncType = v.union(
	v.literal("customers"),
	v.literal("products"),
	v.literal("invoices"),
	v.literal("payments"),
);

export const syncStatus = v.union(
	v.literal("running"),
	v.literal("success"),
	v.literal("failed"),
);

export const tripletexEnv = v.union(
	v.literal("sandbox"),
	v.literal("production"),
);

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

Query (read data):

// convex/syncState.ts
import { query } from "./_generated/server";
import { syncType, tripletexEnv } from "./validators";

export const getLatest = query({
	args: { syncType, tripletexEnv },
	handler: async (ctx, args) => {
		return await ctx.db
			.query("syncState")
			.withIndex("by_type_and_env", (q) =>
				q.eq("syncType", args.syncType).eq("tripletexEnv", args.tripletexEnv),
			)
			.order("desc")
			.first();
	},
});

Mutation (write data):

// convex/syncState.ts
import { mutation } from "./_generated/server";
import { syncType, tripletexEnv } from "./validators";

export const startSync = mutation({
	args: { syncType, tripletexEnv },
	handler: async (ctx, args) => {
		return await ctx.db.insert("syncState", {
			syncType: args.syncType,
			tripletexEnv: args.tripletexEnv,
			status: "running",
			recordsProcessed: 0,
			recordsFailed: 0,
			startedAt: Date.now(),
		});
	},
});

Action (external API calls):

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

// convex/sync.ts
import { action } from "./_generated/server";
import { api } from "./_generated/api";
import { tripletexEnv } from "./validators";

export const syncCustomers = action({
	args: { tripletexEnv },
	handler: async (ctx, args) => {
		// Call external APIs
		const customers = await fetchFromRubic();

		// Write to Convex DB via mutations
		for (const customer of customers) {
			await ctx.runMutation(api.customerMapping.upsert, {
				rubicCustomerNo: customer.customerNo,
				tripletexEnv: args.tripletexEnv,
				// ...
			});
		}
	},
});

Next.js Integration

// In Server Components or Route Handlers
import { fetchQuery, fetchMutation } from "convex/nextjs";
import { api } from "@/convex/_generated/api";

// Read
const state = await fetchQuery(api.syncState.getLatest, {
	syncType: "customers",
	tripletexEnv: "production",
});

// Write
await fetchMutation(api.syncState.startSync, {
	syncType: "customers",
	tripletexEnv: "production",
});

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

---

Converted and distributed by TomeVault — claim your Tome and manage your conversions.