Data migration

One-shot backfill scripts per domain. SQL Server → Postgres. Idempotent (run anytime; upsert by stable id mapping). Mapping tables for entities that change shape.

Migration

Approach

  1. Pull from SQL Server with a TS script using mssql (Tedious) driver.
  2. Transform in TS — collapse enums, generate UUID v7 from legacy composite key (deterministic via UUID v5 namespace) so re-runs land same UUIDs.
  3. Upsert into Postgres via Drizzle's onConflictDoUpdate.

ID mapping

v1 uses BaseObj composite keys (or int identity). v2 uses UUID v7. Deterministic mapping:

// scripts/migrate/id.ts
import { v5 as uuidv5 } from "uuid";

const NS = "spacehub-v1-mapping-namespace-uuid";   // any fixed UUID

export function mapId(table: string, legacyId: string | number): string {
  return uuidv5(`${table}:${legacyId}`, NS);
}

// Same legacy id always maps to same UUID. Re-runs idempotent.

Per-domain scripts

scripts/migrate/
├── 01-users.ts                  # User + UserMore + Customer → users + profiles + property_owners
├── 02-properties.ts             # Property + Floor + Room
├── 03-contracts.ts              # Contract + ContractItem + ContractRoom + ContractPaymentChart
├── 04-invoices.ts               # Bill + InvoiceBill + Sale + Discount → invoices + invoice_items
├── 05-payments.ts               # Payment + StatementLog → payments + payment_events + bank_*
├── 06-ebarimt.ts                # SaleEbarimt + EbarimtRecord → ebarimt_* columns on invoices
├── 07-files.ts                  # SPImage blobs → R2 + files table
├── _lib.ts                      # shared: id mapping, status enum collapsers, money parsers
└── run.ts                       # orchestrator: runs in order with checkpointing

Example script — properties

// scripts/migrate/02-properties.ts
import sql from "mssql";
import { db, properties, floors, rooms } from "@spacehub/db";
import { mapId } from "./_lib";

async function migrateProperties() {
  const mssqlPool = await sql.connect(process.env.MSSQL_URL!);
  const result = await mssqlPool.request().query(`
    SELECT p.Oid as legacyId, p.Name, p.Address, p.City, p.PropertyOwnerOid as poId,
           p.TotalArea, p.FloorCount, p.BuiltYear
    FROM Property p
    WHERE p.IsDeleted = 0
  `);

  for (const row of result.recordset) {
    await db.insert(properties).values({
      id: mapId("property", row.legacyId),
      ownerId: mapId("propertyOwner", row.poId),
      name: row.Name,
      address: row.Address,
      city: row.City ?? "Улаанбаатар",
      totalArea: row.TotalArea ?? null,
      floorCount: row.FloorCount ?? null,
      builtYear: row.BuiltYear ?? null,
      status: "active",
    }).onConflictDoUpdate({
      target: properties.id,
      set: {
        name: sql`excluded.name`,
        address: sql`excluded.address`,
        updatedAt: new Date(),
      },
    });
  }
  console.log(`Migrated ${result.recordset.length} properties`);
  await mssqlPool.close();
}

Enum collapsing

v1 has many 2-enum drift cases (KNOWN_ISSUES #3). Migration is when we resolve them — pick canonical value per row.

// _lib.ts
export function collapseInvoiceStatus(old: number, oldType: number): InvoiceStatus {
  // If new enum (oldType) is set, prefer it; fall back to legacy
  if (oldType != null) return mapNew[oldType];
  return mapOld[old];
}

Money parsing

v1 stores decimal as decimal(18,2). v2 uses BigInt minor units. Multiply by 100:

export function toMinor(decimal: string | number): bigint {
  const s = typeof decimal === "number" ? decimal.toFixed(2) : decimal;
  const [whole, frac = ""] = s.split(".");
  const paddedFrac = (frac + "00").slice(0, 2);
  return BigInt(whole.replace("-", "") + paddedFrac) * (s.startsWith("-") ? -1n : 1n);
}

Orchestrator + checkpointing

// scripts/migrate/run.ts
const stages = [
  { name: "users", fn: migrateUsers },
  { name: "properties", fn: migrateProperties },
  { name: "contracts", fn: migrateContracts },
  { name: "invoices", fn: migrateInvoices },
  { name: "payments", fn: migratePayments },
  { name: "ebarimt", fn: migrateEbarimt },
  { name: "files", fn: migrateFiles },
];

for (const s of stages) {
  console.log(`▶ ${s.name}...`);
  const started = Date.now();
  await s.fn();
  console.log(`✓ ${s.name} done in ${(Date.now() - started)/1000}s`);
  await db.insert(migrationCheckpoints).values({ stage: s.name, completedAt: new Date() })
    .onConflictDoUpdate({ target: migrationCheckpoints.stage, set: { completedAt: new Date() } });
}

Validation

Nightly parity check while in dual-write:

// scripts/parity/check.ts
const v1Count = await mssql.request().query("SELECT COUNT(*) c FROM Contract WHERE IsDeleted = 0");
const v2Count = await db.select({ c: count() }).from(contracts);
if (Math.abs(v1Count.recordset[0].c - v2Count[0].c) > 5) {
  await alertSlack(`Contract count drift: v1=${v1Count.recordset[0].c} v2=${v2Count[0].c}`);
}

// also sample 10 random contracts, compare field-by-field

File migration (images)

v1's SPImage stored blobs in SQL Server. Migration:

  1. Stream SPImage blobs in batches of 100.
  2. Upload each to R2 at owners/{ownerId}/legacy/{spImageId}.{ext}.
  3. Insert files row.
  4. Update referencing entity (Property, Profile) with new url.
  5. Run inside BullMQ migrate-image queue, concurrency 4, exp backoff on R2 errors.

Run modes

Build steps

  1. scripts/migrate/ dir + _lib.ts with mapId + enum collapsers + money parser.
  2. One script per domain, ordered by dependency.
  3. Orchestrator with checkpointing in migration_checkpoints table.
  4. Parity check script + scheduled GitHub Action.
  5. File migration via BullMQ worker (R2 uploads).
  6. Dry-run mode (--dry-run flag) for stage testing.

Open questions

All pages complete. Back to Index or Decision log.