# Full Convex Migration Design — Prisma/Supabase → Convex Only

**Date:** 2026-03-03
**Status:** Approved
**Approach:** "Flip the Switch" via data-source routing
**Target:** Eliminate Prisma (PostgreSQL) and Supabase entirely. Convex becomes the single source of truth.

---

## Current State

- **343 API routes** — all defaulting to Prisma
- **60 routes** already have `getDataSource()` routing blocks
- **166 Convex functions** (96 mutations + 70 queries) across 21 domains
- **21 Convex schema files** with 91 models
- **Supabase** used as: read replica (dual-write) + file storage (3 buckets)
- **Data-source router** at `lib/data-source.ts` — env var `DS_<DOMAIN>=convex` flips per domain

## Target State

- All 21 domains set to `DS_<DOMAIN>=convex`
- Prisma, Supabase, and dual-write code removed
- Convex native file storage for CVs, attachments, blog images
- No PostgreSQL, no Supabase dependency
- `legacyId` field on all Convex docs for backward compatibility during transition

---

## Migration Waves

| Wave | Domains                                                     | ~Routes | Risk     | Notes                  |
| ---- | ----------------------------------------------------------- | ------- | -------- | ---------------------- |
| 1    | content, reviews, analytics                                 | 15      | Low      | Read-heavy, few writes |
| 2    | careers, expenses, nurture                                  | 12      | Low      | Isolated domains       |
| 3    | leads, social, customers                                    | 30      | Medium   | CRM core               |
| 4    | support, workflows, real_estate                             | 20      | Medium   | Active features        |
| 5    | bookings, notifications, quotations, whatsapp, voice_agents | 50      | High     | Core business          |
| 6    | billing, organizations, auth                                | 30      | Critical | Payments, identity     |
| 7    | Cleanup — remove Prisma/Supabase code                       | —       | Low      | Deletion only          |

## Per-Domain Migration Cycle

For each domain in a wave:

1. **Audit** — List all API routes, verify Convex functions exist for each Prisma operation
2. **Gap-fill** — Write missing Convex mutations/queries (some routes have operations not yet in Convex)
3. **Wire** — Add `getDataSource()` routing to unwired routes (if not already present)
4. **Migrate data** — Run script: Prisma → Convex bulk insert with type transforms
5. **Flip** — Set `DS_<DOMAIN>=convex` in environment
6. **Validate** — Run tests, E2E checks, compare response shapes
7. **Rollback plan** — Unset env var → instantly back to Prisma

---

## File Storage Migration

Convex native file storage replaces all 3 Supabase buckets.

| Supabase Bucket       | Volume                  | Convex Approach                                                   |
| --------------------- | ----------------------- | ----------------------------------------------------------------- |
| `job-cvs`             | PDFs, max 10MB          | HTTP action → upload URL → `storageId` on `job_applications`      |
| `contact-attachments` | Images/PDFs, max 3×10MB | HTTP action → upload URL → `storageId[]` on `contact_submissions` |
| `blog-images`         | Images, internal        | HTTP action → upload URL → `storageId` on `blog_posts`            |

### Convex Storage Pattern

```typescript
// Generate upload URL (HTTP action)
export const generateUploadUrl = action({
  handler: async (ctx) => {
    return await ctx.storage.generateUploadUrl();
  },
});

// Serve file (query)
export const getFileUrl = query({
  args: { storageId: v.id("_storage") },
  handler: async (ctx, args) => {
    return await ctx.storage.getUrl(args.storageId);
  },
});
```

### File Migration Script

1. List all files in each Supabase bucket
2. Download each file
3. Upload to Convex storage via `ctx.storage.store()`
4. Update document references (old URL → new `storageId`)

---

## Data Migration Scripts

One script per domain. Each script:

1. Reads all records from Prisma (PostgreSQL)
2. Transforms data types
3. Bulk-inserts into Convex
4. Verifies record count matches
5. Logs failures for manual review

### Type Transformations

| Prisma Type         | Convex Type               | Transform                  |
| ------------------- | ------------------------- | -------------------------- |
| `DateTime`          | `v.number()`              | `.getTime()`               |
| `Decimal`           | `v.number()`              | `parseFloat()`             |
| `Json`              | `v.any()`                 | Pass through               |
| `String @id` (CUID) | Auto `_id` + `legacyId`   | Store old ID as `legacyId` |
| `enum`              | `v.union(v.literal(...))` | String pass-through        |
| FK relations        | Denormalized or indexed   | Flatten or add indexes     |

### ID Mapping

Prisma uses CUID strings as primary keys. Convex uses internal `_id`. Every Convex document gets a `legacyId: v.string()` field containing the old Prisma ID. During transition, API routes can look up by `legacyId`. After full migration, switch to native Convex `_id`.

---

## Dual-Write & Supabase Removal

### During Migration (Waves 1–6)

- Domains on Convex: write only to Convex (no dual-write, no `saveXToBoth()`)
- Domains still on Prisma: keep existing dual-write to Supabase
- `sync_queue` stays active until all domains are on Convex

### After All Domains Flipped (Wave 7 Cleanup)

Remove:

- `lib/dual-database.ts` (~1,400 lines)
- `lib/sync-queue.ts` (~272 lines)
- `lib/supabase.ts` (~236 lines)
- `lib/database-sync/` directory (~348 lines)
- All `saveXToBoth()` calls from API routes
- All Supabase env vars (`NEXT_PUBLIC_SUPABASE_URL`, `SUPABASE_SERVICE_ROLE_KEY`, etc.)
- `@supabase/supabase-js` dependency from `package.json`
- `lib/data-source.ts` routing layer (everything is Convex now)
- Prisma schema, `lib/db.ts`, `@prisma/client`, `prisma` dev dependency

---

## Testing Strategy

### Per Domain Before Flipping

1. **Unit tests** — Convex functions produce correct results (mock `ctx.db`)
2. **Integration tests** — API routes return same response shapes on Convex vs Prisma
3. **Data integrity** — Migration script produces correct record counts and field values

### Comparison Test Pattern

```typescript
// Call API with DS_DOMAIN=prisma, record response shape
// Call API with DS_DOMAIN=convex, record response shape
// Assert shapes match (IDs differ, but structure and field names are identical)
```

### Rollback Verification

For each wave, verify that unsetting `DS_<DOMAIN>` env var instantly restores Prisma behavior with no data loss.

---

## Risks & Mitigations

| Risk                                              | Impact          | Mitigation                                                       |
| ------------------------------------------------- | --------------- | ---------------------------------------------------------------- |
| Convex query performance differs from Prisma      | Slow pages      | Benchmark before flipping; add indexes                           |
| Missing Convex functions for edge-case operations | 500 errors      | Thorough audit per domain; gap-fill step                         |
| File storage migration corrupts files             | Broken uploads  | Checksum verification; keep Supabase read-only during transition |
| ID mapping breaks cross-domain references         | Orphaned data   | `legacyId` field + migration script handles FK mapping           |
| Convex rate limits on bulk inserts                | Migration fails | Batch inserts with throttling; retry logic                       |

---

## Key Files

| File                    | Role                                 |
| ----------------------- | ------------------------------------ |
| `lib/data-source.ts`    | Domain routing (Prisma vs Convex)    |
| `lib/convex-client.ts`  | ConvexHttpClient helpers             |
| `lib/db.ts`             | Prisma client (to be removed)        |
| `lib/supabase.ts`       | Supabase clients (to be removed)     |
| `lib/dual-database.ts`  | Dual-write sync (to be removed)      |
| `lib/sync-queue.ts`     | Transactional outbox (to be removed) |
| `convex/schema/`        | 21 domain schema files               |
| `convex/*/mutations.ts` | Domain mutations                     |
| `convex/*/queries.ts`   | Domain queries                       |

---

## Success Criteria

- All 343 API routes serving from Convex
- All file uploads using Convex native storage
- Zero Prisma/Supabase imports remaining in codebase
- All existing tests passing against Convex backend
- Production data fully migrated with verified integrity
- Rollback possible at any wave by reverting env vars