Skip to content

Open-source manufacturing ERP, MES and QMS. Quoting, MRP, inventory, shop floor, quality and lot/serial traceability on one Postgres schema, with a REST API and MCP server. Self-host or use Carbon Cloud.

20.1 KB
Raw Blame
// SPDX-License-Identifier: AGPL-3.0-only
// Carbon (github.com/crbnos/carbon). Modified or adapted versions of this file,
// including ports, remain AGPLv3; serving them over a network requires releasing their source.

import type { PoolClient } from "pg";
import { getGroupId, groups } from "../seed-data.ts";
import { resolveDate } from "./dates.ts";
import { insertId, nextJournalEntryId, quote, resetSequences } from "./sql.ts";
import type { Ctx } from "./types.ts";

/**
 * Tables the seed never touches: identity/access, accounting reference, the
 * list tables bootstrap creates, and infrastructure the developer owns.
 * Everything else with a `companyId` is business data this seed owns, so it is
 * discovered from information_schema and wiped — a business table added to the
 * schema later is cleaned automatically instead of leaking across re-runs.
 */
const PRESERVED_TABLES = new Set([
  // Created by bootstrap()
  "accountDefault",
  "changeOrderRequiredAction",
  "changeOrderType",
  "customerStatus",
  "employee",
  "employeeJob",
  // Console PIN hashes belong to the employees above, not to the story
  "employeePin",
  "employeeType",
  "fiscalYearSettings",
  "fixedAssetClass",
  "gaugeType",
  "group",
  "location",
  "maintenanceFailureMode",
  "nonConformanceRequiredAction",
  "nonConformanceType",
  "paymentTerm",
  "periodCloseTaskDefinition",
  "returnReason",
  "scrapReason",
  "sequence",
  "unitOfMeasure",
  "userToCompany",
  // Accounting records with trigger-enforced immutability (Posted journals
  // cannot be deleted or updated — so neither can the periods they point at)
  "accountingPeriod",
  "journal",
  "journalLine",
  // Infrastructure owned by the developer or the platform
  "apiKey",
  "auditLogArchive",
  "companyIntegration",
  "companyUsage",
  "customField",
  "eventSystemSubscription",
  // Integration mappings, and the job markers that ride on them — including the
  // marker of the very job that runs this wipe, whose snapshot path it holds.
  "externalIntegrationMapping",
  "invite",
  "notificationPreference",
  "oauthClient",
  "oauthCode",
  "oauthToken",
  "searchIndexRegistry",
  // The change log open clients read to learn what the wipe just deleted: every
  // delete below writes to it, so it can never be empty afterwards. It prunes
  // itself by age, and backups already leave it alone (STRUCTURAL_TABLES).
  "tableChange",
  "tableView",
  "userAttributeCategory",
  "userModulePreference",
  "webhook"
]);

/**
 * MRP planning output — regenerated on the next run, and deleted before the
 * FK-nulling pass because their CHECKs cannot survive it. The first four are
 * also TRANSIENT_TABLES in packages/jobs/src/backups/schema.ts; planningAction
 * is NOT (a backup keeps it: Dismissed and assigneeOverridden are the planner's
 * own state), it is only transient for a dataset apply.
 */
const TRANSIENT_MRP_TABLES = [
  "demandForecastSource",
  "demandActual",
  "supplyForecast",
  "supplyActual",
  // planningAction_change_target_chk: a change action must keep its job or
  // PO line, and the FK-nulling pass would clear jobId while jobs still exist.
  "planningAction"
];

// Their content CHECK (imagePath OR modelUploadId) cannot survive the FK-nulling
// pass, which nulls modelUploadId on a slide that shows a 3D model.
const MODEL_SLIDE_TABLES = [
  "assemblyInstructionStepSlide",
  "methodOperationStepSlide",
  "jobOperationStepSlide",
  "quoteOperationStepSlide"
];

// Other tables whose CHECK needs one of several nullable FKs to stay set, so
// the FK-nulling pass would empty all of them and trip the CHECK. A new table
// with a CHECK like that goes here (see .claude/rules/onboarding-company-templates.md).
const PARENT_CHECK_TABLES = [
  // customerContractInvoiceLine_parent_check: an invoice OR a credit memo
  "customerContractInvoiceLine",
  // trainingAssignment_training_or_name: a training OR an external name
  "trainingAssignment"
];

type ForeignKey = {
  child: string;
  parent: string;
  columns: string[];
  notNull: string[];
};

async function companyScopedTables(client: PoolClient): Promise<string[]> {
  const res = await client.query<{ table_name: string }>(
    `SELECT t.table_name
     FROM information_schema.tables t
     WHERE t.table_schema = 'public' AND t.table_type = 'BASE TABLE'
       AND EXISTS (
         SELECT 1 FROM information_schema.columns c
         WHERE c.table_schema = 'public' AND c.table_name = t.table_name
           AND c.column_name = 'companyId'
       )
     ORDER BY t.table_name`
  );
  return res.rows.map((r) => r.table_name);
}

async function foreignKeys(client: PoolClient): Promise<ForeignKey[]> {
  const res = await client.query<{
    child: string;
    parent: string;
    columns: string[];
    notnull: string[] | null;
  }>(
    `SELECT
       con.conrelid::regclass::text AS child,
       con.confrelid::regclass::text AS parent,
       (SELECT array_agg(a.attname::text ORDER BY k.ord)
          FROM unnest(con.conkey) WITH ORDINALITY k(attnum, ord)
          JOIN pg_attribute a ON a.attrelid = con.conrelid AND a.attnum = k.attnum) AS columns,
       (SELECT array_agg(a.attname::text)
          FROM unnest(con.conkey) k(attnum)
          JOIN pg_attribute a ON a.attrelid = con.conrelid AND a.attnum = k.attnum
         WHERE a.attnotnull) AS notnull
     FROM pg_constraint con
     JOIN pg_class rel ON rel.oid = con.conrelid
     JOIN pg_namespace ns ON ns.oid = rel.relnamespace
     WHERE con.contype = 'f' AND ns.nspname = 'public'`
  );
  const unquote = (s: string) => s.replace(/"/g, "");
  return res.rows.map((r) => ({
    child: unquote(r.child),
    parent: unquote(r.parent),
    columns: r.columns,
    notNull: r.notnull ?? []
  }));
}

// companyId is part of most composite FKs but is never the discriminator —
// a MATCH SIMPLE constraint is satisfied as soon as any column is NULL.
function payloadColumns(fk: ForeignKey): string[] {
  return fk.columns.filter((c) => c !== "companyId");
}

function isNullable(fk: ForeignKey): boolean {
  const payload = payloadColumns(fk);
  return payload.length > 0 && payload.every((c) => !fk.notNull.includes(c));
}

// Children before parents. Only NOT NULL edges constrain the order, because
// every nullable edge has already been broken by nullNullableReferences().
// That subgraph is acyclic by construction: a NOT NULL cycle is uninsertable.
function deletionOrder(tables: string[], hardEdges: ForeignKey[]): string[] {
  const set = new Set(tables);
  const parentsOf = new Map(tables.map((t) => [t, new Set<string>()]));
  for (const fk of hardEdges) {
    if (fk.child === fk.parent) continue;
    parentsOf.get(fk.child)?.add(fk.parent);
  }

  const remaining = new Set(set);
  const order: string[] = [];
  while (remaining.size > 0) {
    const inbound = new Map([...remaining].map((t) => [t, 0]));
    for (const child of remaining) {
      for (const parent of parentsOf.get(child) ?? []) {
        if (remaining.has(parent)) {
          inbound.set(parent, (inbound.get(parent) ?? 0) + 1);
        }
      }
    }
    const ready = [...remaining].filter((t) => inbound.get(t) === 0);
    if (ready.length === 0) {
      throw new Error(
        `Seed: cycle in NOT NULL foreign keys among ${[...remaining].join(", ")}`
      );
    }
    for (const t of ready) {
      order.push(t);
      remaining.delete(t);
    }
  }
  return order;
}

// Breaks every optional reference into the delete set — including the traps:
// makeMethod.changeOrderId (ON DELETE SET NULL), item.changeOrderId,
// job.salesOrderId, kanban.jobId, itemLedger.correctionOfItemLedgerId, and the
// customer/supplier/quote/job reference cycles.
async function nullNullableReferences(
  client: PoolClient,
  companyId: string,
  fks: ForeignKey[],
  deleteSet: Set<string>
): Promise<number> {
  let count = 0;
  for (const fk of fks) {
    if (!deleteSet.has(fk.parent)) continue;
    if (!isNullable(fk)) continue;
    const payload = payloadColumns(fk);
    const assignments = payload.map((c) => `${quote(c)} = NULL`).join(", ");
    const anySet = payload.map((c) => `${quote(c)} IS NOT NULL`).join(" OR ");
    await client.query(
      `UPDATE ${quote(fk.child)} SET ${assignments}
       WHERE "companyId" = $1 AND (${anySet})`,
      [companyId]
    );
    count += 1;
  }
  return count;
}

// Rows a trigger forbids deleting, which the wipe would otherwise hit halfway
// through. Refusing before any write keeps the failure readable.
async function assertWipeable(ctx: Ctx): Promise<void> {
  const { client, companyId } = ctx;
  const partners = await client.query<{ count: number }>(
    `SELECT (SELECT count(*) FROM customer
              WHERE "companyId" = $1 AND "intercompanyCompanyId" IS NOT NULL)
          + (SELECT count(*) FROM supplier
              WHERE "companyId" = $1 AND "intercompanyCompanyId" IS NOT NULL)
            AS count`,
    [companyId]
  );
  const partnerCount = Number(partners.rows[0]?.count ?? 0);
  if (partnerCount > 0) {
    throw new Error(
      `Seed: this company trades with other companies in its group (${partnerCount} intercompany customer/supplier record(s)). Demo data can only be applied to a company without intercompany partners.`
    );
  }
  const charges = await client.query<{ count: number }>(
    `SELECT count(*) AS count FROM "charge"
     WHERE "companyId" = $1 AND status <> 'Draft'`,
    [companyId]
  );
  const chargeCount = Number(charges.rows[0]?.count ?? 0);
  if (chargeCount > 0) {
    throw new Error(
      `Seed: this company has ${chargeCount} posted or voided charge(s), which cannot be deleted. Demo data can only be applied to a company without posted charges.`
    );
  }
  // Same shape as the charge guard, and for the same reason:
  // `check_reimbursement_draft_mutation` (migration 20260923231244) refuses to
  // DELETE any row that is not Draft and does NOT honour app.sync_in_progress,
  // so without this the apply aborted mid-wipe on a raw trigger error. The row
  // cannot be walked back to Draft either — unlike an invoice, Posted -> Draft
  // is not an allowed transition.
  const reimbursements = await client.query<{ count: number }>(
    `SELECT count(*) AS count FROM "reimbursement"
     WHERE "companyId" = $1 AND status <> 'Draft'`,
    [companyId]
  );
  const reimbursementCount = Number(reimbursements.rows[0]?.count ?? 0);
  if (reimbursementCount > 0) {
    throw new Error(
      `Seed: this company has ${reimbursementCount} posted or voided reimbursement(s), which cannot be deleted. Demo data can only be applied to a company without posted reimbursements.`
    );
  }
}

/**
 * `Reimbursement` is here for the same reason every other document source is:
 * its journal credits an employee payable against documents this wipe deletes.
 * In practice `assertWipeable` refuses a non-Draft reimbursement before the
 * wipe starts, so today this is the second line of defence — but the two lists
 * must stay in step, or relaxing that guard would silently leave a live journal
 * behind for a document that no longer exists.
 */
const DOCUMENT_JOURNAL_SOURCES = [
  "Sales Invoice",
  "Purchase Invoice",
  "Payment",
  "Credit Memo",
  "Debit Memo",
  "Purchase Receipt",
  "Sales Shipment",
  "Inventory Adjustment",
  "Reimbursement"
];

/**
 * Posted journals cannot be deleted (journal_posted_immutable), but their
 * documents are about to be: void each as the posting functions do, so the GL
 * carries no balances for documents that no longer exist.
 */
async function reverseDocumentJournals(ctx: Ctx): Promise<void> {
  const { client, companyId } = ctx;
  const journals = await client.query<{
    id: string;
    journalEntryId: string;
    description: string | null;
    sourceType: string;
  }>(
    `SELECT j.id, j."journalEntryId", j.description, j."sourceType"
     FROM journal j
     WHERE j."companyId" = $1 AND j.status = 'Posted'
       AND j."sourceType"::text = ANY($2::text[])
       AND (
         EXISTS (
           SELECT 1 FROM "journalLine" l
           WHERE l."journalId" = j.id AND l."companyId" = $1 AND l."documentId" IN (
             SELECT id FROM "salesInvoice" WHERE "companyId" = $1
             UNION ALL SELECT id FROM "purchaseInvoice" WHERE "companyId" = $1
             UNION ALL SELECT id FROM payment WHERE "companyId" = $1
             UNION ALL SELECT id FROM memo WHERE "companyId" = $1
             UNION ALL SELECT id FROM receipt WHERE "companyId" = $1
             UNION ALL SELECT id FROM shipment WHERE "companyId" = $1
             UNION ALL SELECT id FROM "reimbursement" WHERE "companyId" = $1
             UNION ALL SELECT id FROM "itemLedger" WHERE "companyId" = $1))
         -- a zero-cash payment's journal has no lines to match on
         OR j.id IN (SELECT "journalId" FROM payment WHERE "companyId" = $1)
         OR j.id IN (SELECT "journalId" FROM memo WHERE "companyId" = $1)
         OR j.id IN (
              SELECT "journalId" FROM "reimbursement" WHERE "companyId" = $1))
     ORDER BY j."postingDate", j."journalEntryId"`,
    [companyId, DOCUMENT_JOURNAL_SOURCES]
  );
  if (journals.rows.length === 0) return;
  const today = resolveDate(ctx.anchor, 0);
  const period = await client.query<{ id: string }>(
    `SELECT id FROM "accountingPeriod"
     WHERE "companyId" = $1 AND "startDate" <= $2 AND "endDate" >= $2
     ORDER BY id LIMIT 1`,
    [companyId, today]
  );
  for (const journal of journals.rows) {
    const voidId = await insertId(ctx, "journal", {
      journalEntryId: await nextJournalEntryId(ctx),
      accountingPeriodId: period.rows[0]?.id,
      description: `VOID ${journal.description ?? journal.journalEntryId}`,
      postingDate: today,
      sourceType: journal.sourceType,
      status: "Posted",
      postedAt: `${today}T12:00:00Z`,
      postedBy: ctx.userId
    });
    // As the invoice voids: negated amount and quantity, same references (a
    // payment or memo void keeps the quantity).
    await client.query(
      `INSERT INTO "journalLine"
         ("journalId", "accountId", description, amount, quantity, "documentType",
          "documentId", "documentLineReference", "journalLineReference", accrual,
          "companyId", "createdBy")
       SELECT $1, "accountId", 'VOID: ' || COALESCE(description, ''), -amount,
              CASE WHEN "documentType" IN ('Payment', 'Memo') THEN quantity ELSE -quantity END,
              "documentType", "documentId", "documentLineReference",
              "journalLineReference", accrual, "companyId", $2
       FROM "journalLine" WHERE "journalId" = $3 AND "companyId" = $4`,
      [voidId, ctx.userId, journal.id, companyId]
    );
  }
}

export async function wipeCompanyBusinessData(ctx: Ctx): Promise<void> {
  const { client, companyId } = ctx;

  await assertWipeable(ctx);
  await reverseDocumentJournals(ctx);

  const tables = await companyScopedTables(client);
  const deleteSet = new Set(tables.filter((t) => !PRESERVED_TABLES.has(t)));

  // Clear MRP output before anything nulls FKs into it. These tables carry a
  // discriminator CHECK ("Sales Order" requires salesOrderLineId NOT NULL, and
  // so on for jobId / demandProjectionId) over columns that are all nullable,
  // so nullNullableReferences below — which nulls every nullable FK whose
  // parent is being deleted — would violate the CHECK and abort the wipe.
  // Deleting outright is safe for a dataset apply: MRP regenerates these on
  // its next run (the planner's dismissals and assignments on planningAction
  // go with the rest of the company's data, as the apply intends).
  for (const t of [
    ...TRANSIENT_MRP_TABLES,
    ...MODEL_SLIDE_TABLES,
    ...PARENT_CHECK_TABLES
  ]) {
    if (deleteSet.has(t)) {
      await client.query(`DELETE FROM ${quote(t)} WHERE "companyId" = $1`, [
        companyId
      ]);
    }
  }
  const fks = (await foreignKeys(client)).filter(
    (fk) =>
      deleteSet.has(fk.parent) &&
      (deleteSet.has(fk.child) || PRESERVED_TABLES.has(fk.child))
  );

  // The prevent_posted_{sales,purchase}_invoice_deletion interceptors refuse
  // DELETE for any status but Draft, even under app.sync_in_progress. This is a
  // re-seed wipe, not an app delete of a posted document, so reset them to
  // Draft first — otherwise re-applying over Paid / Voided invoices fails.
  for (const t of ["salesInvoice", "purchaseInvoice"]) {
    if (deleteSet.has(t)) {
      await client.query(
        `UPDATE ${quote(t)} SET status = 'Draft'
         WHERE "companyId" = $1 AND status <> 'Draft'`,
        [companyId]
      );
    }
  }

  // Settlements point at their target invoice/memo through ON DELETE RESTRICT
  // foreign keys, so they must go before the invoice headers below. Payments
  // and memos go with them: both carry a customer-XOR-supplier CHECK that the
  // FK-nulling pass would violate when it nulls the party they point at.
  for (const t of ["invoiceSettlement", "payment", "memo"]) {
    if (deleteSet.has(t)) {
      await client.query(`DELETE FROM ${quote(t)} WHERE "companyId" = $1`, [
        companyId
      ]);
    }
  }

  // Delete lines with check constraints that would block nulling their item FKs.
  // salesOrderLine.itemId can't be set NULL (check requires it for Part/Material/Tool/etc).
  // Cascade via the parent headers — the headers are in deleteSet and will be deleted again
  // (harmlessly) in the topo pass.
  for (const t of [
    "purchaseInvoice",
    "salesInvoice",
    "salesOrder",
    "purchaseOrder",
    "quote",
    "salesRfq",
    "supplierQuote",
    "purchasingRfq"
  ]) {
    if (deleteSet.has(t)) {
      await client.query(`DELETE FROM ${quote(t)} WHERE "companyId" = $1`, [
        companyId
      ]);
    }
  }

  await nullNullableReferences(client, companyId, fks, deleteSet);

  const hardEdges = fks.filter(
    (fk) => deleteSet.has(fk.child) && !isNullable(fk)
  );
  for (const table of deletionOrder([...deleteSet], hardEdges)) {
    await client.query(`DELETE FROM ${quote(table)} WHERE "companyId" = $1`, [
      companyId
    ]);
  }

  // Customer/supplier interceptors mint an org group per partner (id = the
  // partner's id) and per type, and nothing deletes them with the owner, so the
  // previous story's partners linger in group pickers. Drop the orphans;
  // bootstrap's roots are kept by id and employee-type groups are untouched.
  await client.query(
    `DELETE FROM "group" g
     WHERE g."companyId" = $1 AND g.id <> ALL($2::text[]) AND (
          (g."isCustomerOrgGroup" AND NOT EXISTS (
            SELECT 1 FROM customer o WHERE o.id = g.id AND o."companyId" = $1))
       OR (g."isSupplierOrgGroup" AND NOT EXISTS (
            SELECT 1 FROM supplier o WHERE o.id = g.id AND o."companyId" = $1))
       OR (g."isCustomerTypeGroup" AND NOT EXISTS (
            SELECT 1 FROM "customerType" o WHERE o.id = g.id AND o."companyId" = $1))
       OR (g."isSupplierTypeGroup" AND NOT EXISTS (
            SELECT 1 FROM "supplierType" o WHERE o.id = g.id AND o."companyId" = $1)))`,
    [companyId, groups.map((g) => getGroupId(g.idPrefix, companyId))]
  );

  // location is preserved, but only the bootstrap one — the rest are seed data.
  // Re-point the employee at it first; employeeJob is preserved and NOT NULL.
  await client.query(
    `UPDATE "employeeJob" SET "locationId" = $2
     WHERE "companyId" = $1 AND "locationId" IS DISTINCT FROM $2`,
    [companyId, ctx.locationId]
  );
  await client.query(
    `DELETE FROM location WHERE "companyId" = $1 AND id <> $2`,
    [companyId, ctx.locationId]
  );

  // Run N and run N+1 must produce identical readable ids.
  await resetSequences(client, companyId);

  await assertWipeClean(ctx, deleteSet);
}

// Fails loudly if anything survived, so a table the wipe can't reach shows up
// as a seed error rather than as data quietly accumulating across re-runs.
export async function assertWipeClean(
  ctx: Ctx,
  deleteSet: Set<string>
): Promise<void> {
  const tables = [...deleteSet];
  const union = tables
    .map(
      (t) =>
        `SELECT '${t}' AS table_name, count(*)::int AS count FROM ${quote(t)} WHERE "companyId" = $1`
    )
    .join(" UNION ALL ");
  const res = await ctx.client.query<{ table_name: string; count: number }>(
    `SELECT * FROM (${union}) s WHERE count > 0 ORDER BY table_name`,
    [ctx.companyId]
  );
  if (res.rows.length > 0) {
    const detail = res.rows.map((r) => `${r.table_name}=${r.count}`).join(", ");
    throw new Error(`Seed: wipe left rows behind — ${detail}`);
  }
}