Migration: MongoDB to PostgreSQL

This package migrates the Leadmetrics production data from Azure Cosmos DB for MongoDB API into the v3 PostgreSQL database managed by Prisma. The migrator is a TypeScript CLI (@leadmetrics/migration) that reads from MongoDB via a cursor-based batch loop, maps each document to a Prisma-compatible row through a typed CollectionMap file, and writes to PostgreSQL using createMany with skipDuplicates: true so that every step is safe to re-run. All writes are skipped in dry-run mode, which is useful for inspecting field-mapping logic without touching the target database.

Quick start


# 1. Copy and fill in the two required variables
cp migration/.env.example migration/.env
 
# 2. Dry-run — validates mapping logic, no DB writes
pnpm --filter @leadmetrics/migration migrate:dry
 
# 3. Live run — writes to PostgreSQL
pnpm --filter @leadmetrics/migration migrate

Document	Contents
architecture.md	Folder structure, CollectionMap pattern, RelatedWriter, runner flow, dynamic imports
usage.md	Environment setup, all run commands, flags, re-run safety, adding steps
map-files.md	Annotated map file template, relatedWriter and setup() examples, authoring tips
known-issues.md	Skip breakdowns, legacy plans, auto-generated invoice numbers, passwords

Phase 1 results

These are the final row counts after a full live run against the production MongoDB instance.

Step	Key	Migrated	Skipped	Target tables
1	offering	1 offering, 5 regions, 10 plans	0	`offering`, `region`, `plan`
2	tenant	280 tenants, competitors expanded	0	`tenant`, `competitor`
3	user	359 users, 359 accounts	0	`user`, `account`
4	member	0 (main), 1,407 (relatedWriter)	359 (by design)	`tenant_member`
5	subscription	220	66	`subscription`
6	invoice	286	15	`invoice`

The 359 “skipped” rows in the member step are not data loss — mapDoc intentionally returns null for all documents; all 1,407 tenant_member rows are written by the relatedWriter. See known-issues.md for the breakdown of the 66 subscription skips and 15 invoice skips.

Phase 2 results

Step	Key	Migrated	Skipped	Target tables
7	strategy	8 strategies, 189 logs	0	`strategy`, `strategy_log`
8	strategy-history	31	0	`strategy_version`
9	deliverable-plan	22	0	`deliverable_plan`
10	goal	35	1	`goal`
11	client-context	29	0	`client_context`
12	report	51	0	`report`

The 1 skipped goal belongs to a tenant (6936d131994cdc348bf14c9e) that has no GoalsGroup in MongoDB. See known-issues.md for Phase 2 skip details.

Phase 3 results

Step	Key	Migrated	Skipped	Target tables
13	keyword	205,721	21	`keyword`
14	keyword-group	49 groups, 910 items	0	`keyword_group`, `keyword_group_item`
15	channel	228	5	`connected_channel`

The 5 channel skips: 2 docs with null tenantId, 2 “Bing WebMaster Tools” and 1 “ScholarCRM” with no matching row in channel_master. OAuth tokens (tokenInfo) are not migrated — tenants must reconnect channels after migration.

The keyword step processes 205K+ docs in ~84 seconds at batch size 1,000. Run it as a standalone step (--only keyword) to avoid shell timeouts.

Phase 4 results

Step	Key	Migrated	Skipped	Target tables
16	lead	20,137 leads, 44,128 contacts, 13,790 activity rows	89	`lead`, `lead_contact`, `lead_activity`

The 89 skipped leads have a tenantId not present in the migrated tenant table (orphaned). The contact collection (4 records, no tenantId field) and media_asset (Azure Blob Storage URLs; v3 source enum is pixabay\|unsplash only) were not migrated — see known-issues.md.

Phase 5 results

Step	Key	Migrated	Skipped	Target tables
17	blog-post	1,837 posts, 13,729 FAQs	247	`blog_post`, `blog_post_faq`
18	social-post	717	0	`social_post`

The 247 skipped blog posts have a tenantId not present in the migrated tenant table (orphaned). The v2 Activity collection (37,209 task-tracker items) was intentionally not migrated — see known-issues.md.

BlogPost.activityId and SocialPost.activityId were changed from String @unique to String? @unique (with onDelete: SetNull) before this phase ran. Migrated posts have activityId: null.