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.

33.7 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.

/**
 * Entity-Centric Audit Log Configuration
 *
 * Audit logging is organized around business entities (Customer, Sales Order, etc.),
 * not individual database tables. Each entity is composed of one or more tables.
 * When a row in any of those tables changes, the audit system attributes the change
 * to the correct business entity and records the actual field-level diff.
 *
 * The generated Supabase `Database` type (`./types`) is the source of truth for
 * table names and column names used throughout this config.
 *
 * Table roles:
 * - "root": The primary table for this entity. Its PK is the entity ID.
 * - "extension": A 1:1 extension table whose PK equals the parent FK
 *           (e.g., customerPayment PK = customerId). Entity ID resolution
 *           is the same as root, but INSERT events are skipped.
 * - { entityIdColumn }: A child table with its own surrogate PK. The named
 *           column contains the parent entity ID.
 * - { resolve: { junction, fk, entityIdColumn } }: An indirect child linked
 *           through a junction table. Requires a DB query at audit time.
 */

import type { Database } from "./types";

// ---------------------------------------------------------------------------
// Schema-derived helpers
// ---------------------------------------------------------------------------

type PublicSchema = Database["public"];

/** All table names present in the public schema. */
export type TableName = Extract<keyof PublicSchema["Tables"], string>;

/** All column names of a given public table. */
export type ColumnOf<T extends TableName> = Extract<
  keyof PublicSchema["Tables"][T]["Row"],
  string
>;

// ---------------------------------------------------------------------------
// Config types (generic on the table they describe)
// ---------------------------------------------------------------------------

/**
 * `createFields` controls which columns appear in the diff for INSERT events.
 * By default, INSERT audit entries carry a null diff (rendered as "Created").
 * List columns here to surface their initial values. Every table logs its
 * INSERTs except extension tables (created 1:1 with their parent).
 *
 * `snapshotFields` declares FK columns whose target row's display values
 * should be captured into the diff at write time. The audit handler resolves
 * each listed column's id against the target table and stores the picked
 * columns under `snapshot.old` / `snapshot.new` on the diff entry. The
 * snapshot is frozen in the audit row forever — historical accuracy is
 * preserved if the FK target is later renamed or deleted.
 *
 * Each variant declares `createFields` directly (rather than sharing a base
 * via intersection) so TS can narrow `T` — and autocomplete column names —
 * inside object-literal config entries.
 */
type SnapshotFields<T extends TableName> = {
  [Col in ColumnOf<T>]?: {
    [Target in TableName]: {
      table: Target;
      displayColumns: readonly ColumnOf<Target>[];
    };
  }[TableName];
};

/**
 * Flattened view of one snapshot declaration, returned by
 * `getSnapshotFields`. Lives at type-level so consumers (the audit handler)
 * can iterate without re-parsing the typed config.
 */
export type SnapshotFieldEntry = {
  column: string;
  table: string;
  displayColumns: readonly string[];
};

type RootTable<T extends TableName> = {
  role: "root";
  createFields?: readonly ColumnOf<T>[];
  snapshotFields?: SnapshotFields<T>;
};

type ExtensionTable<T extends TableName> = {
  role: "extension";
  createFields?: readonly ColumnOf<T>[];
  snapshotFields?: SnapshotFields<T>;
};

type ChildTable<T extends TableName> = {
  /**
   * The column naming the parent entity. A list attributes the row to every
   * distinct, non-null parent it names (a settlement row belongs to the
   * payment that made it, the one it was applied through, and the prior
   * credit it draws on).
   */
  entityIdColumn: ColumnOf<T> | readonly ColumnOf<T>[];
  createFields?: readonly ColumnOf<T>[];
  snapshotFields?: SnapshotFields<T>;
};

/**
 * Distributed union so `fk` and `entityIdColumn` are checked against the
 * chosen `junction` table's columns.
 */
type IndirectResolve = {
  [J in TableName]: {
    /** Junction table name (e.g. "customerContact"). */
    junction: J;
    /** Column in the junction pointing to this table's PK (e.g. "contactId"). */
    fk: ColumnOf<J>;
    /** Column in the junction pointing to the parent entity (e.g. "customerId"). */
    entityIdColumn: ColumnOf<J>;
  };
}[TableName];

type IndirectTable<T extends TableName> = {
  resolve: IndirectResolve;
  createFields?: readonly ColumnOf<T>[];
  snapshotFields?: SnapshotFields<T>;
};

export type TableConfig<T extends TableName = TableName> =
  | RootTable<T>
  | ExtensionTable<T>
  | ChildTable<T>
  | IndirectTable<T>;

type EntityTables = {
  [T in TableName]?: TableConfig<T>;
};

type EntityConfig = {
  label: string;
  tables: EntityTables;
};

// ---------------------------------------------------------------------------
// Entity definitions
// ---------------------------------------------------------------------------

export const auditConfig = {
  entities: {
    customer: {
      label: "Customer",
      tables: {
        customer: {
          role: "root",
          snapshotFields: {
            customerTypeId: { table: "customerType", displayColumns: ["name"] },
            customerStatusId: {
              table: "customerStatus",
              displayColumns: ["name"]
            }
          }
        },
        customerPayment: { role: "extension" }, // PK = customerId
        customerShipping: { role: "extension" }, // PK = customerId
        customerTax: { role: "extension" }, // PK = customerId
        customerBankAccount: { entityIdColumn: "customerId" },
        contact: {
          resolve: {
            junction: "customerContact",
            fk: "contactId",
            entityIdColumn: "customerId"
          }
        },
        address: {
          resolve: {
            junction: "customerLocation",
            fk: "addressId",
            entityIdColumn: "customerId"
          }
        }
      }
    },

    supplier: {
      label: "Supplier",
      tables: {
        supplier: {
          role: "root",
          snapshotFields: {
            supplierTypeId: { table: "supplierType", displayColumns: ["name"] }
          }
        },
        supplierPayment: { role: "extension" }, // PK = supplierId
        supplierShipping: { role: "extension" }, // PK = supplierId
        supplierTax: { role: "extension" }, // PK = supplierId
        supplierBankAccount: { entityIdColumn: "supplierId" },
        contact: {
          resolve: {
            junction: "supplierContact",
            fk: "contactId",
            entityIdColumn: "supplierId"
          }
        },
        address: {
          resolve: {
            junction: "supplierLocation",
            fk: "addressId",
            entityIdColumn: "supplierId"
          }
        }
      }
    },

    item: {
      label: "Item",
      tables: {
        item: { role: "root" },
        itemShelfLife: { role: "root" },
        itemCost: { role: "extension" }, // PK = itemId
        itemPlanning: {
          role: "extension", // unique on (itemId, locationId); entityId = itemId
          snapshotFields: {
            locationId: { table: "location", displayColumns: ["name"] }
          }
        },
        itemReplenishment: { role: "extension" }, // PK = itemId
        itemUnitSalePrice: { role: "extension" }, // PK = itemId
        supplierPart: { entityIdColumn: "itemId" },
        customerPartToItem: { entityIdColumn: "itemId" }
      }
    },

    itemShelfLife: {
      label: "Item Shelf Life",
      tables: {
        itemShelfLife: {
          role: "root",
          createFields: [
            "mode",
            "days",
            "triggerProcessId",
            "triggerTiming",
            "calculateFromBom"
          ],
          snapshotFields: {
            triggerProcessId: { table: "process", displayColumns: ["name"] }
          }
        }
      }
    },

    salesOrder: {
      label: "Sales Order",
      tables: {
        salesOrder: {
          role: "root",
          snapshotFields: {
            // No FK constraint on this column, so the schema-discovered map
            // can't resolve it — declare the target explicitly.
            salesPersonId: { table: "user", displayColumns: ["fullName"] }
          }
        },
        salesOrderLine: {
          entityIdColumn: "salesOrderId",
          snapshotFields: {
            locationId: { table: "location", displayColumns: ["name"] },
            modelUploadId: { table: "modelUpload", displayColumns: ["name"] }
          }
        },
        salesOrderPayment: { role: "extension" }, // PK = salesOrderId
        salesOrderShipment: {
          role: "extension", // PK = salesOrderId
          snapshotFields: {
            supplierId: { table: "supplier", displayColumns: ["name"] },
            supplierLocationId: {
              table: "supplierLocation",
              displayColumns: ["name"]
            }
          }
        }
      }
    },

    // A lease classification override is audit-logged against its agreement
    // (spec §4); the agreement's own edits come along with it.
    rentalAgreement: {
      label: "Rental Agreement",
      tables: {
        rentalAgreement: { role: "root" },
        rentalAgreementLine: { entityIdColumn: "rentalAgreementId" }
      }
    },

    // A lessee lease, with its assets and payment rows; a classification
    // override is audit-logged against it.
    lease: {
      label: "Lease",
      tables: {
        lease: { role: "root" },
        leaseLine: { entityIdColumn: "leaseId" },
        leasePayment: { entityIdColumn: "leaseId" }
      }
    },

    purchaseOrder: {
      label: "Purchase Order",
      tables: {
        purchaseOrder: {
          role: "root",
          snapshotFields: {
            supplierId: { table: "supplier", displayColumns: ["name"] },
            supplierLocationId: {
              table: "supplierLocation",
              displayColumns: ["name"]
            }
          }
        },
        purchaseOrderLine: {
          entityIdColumn: "purchaseOrderId",
          snapshotFields: {
            // No FK constraint on this column — resolve explicitly.
            locationId: { table: "location", displayColumns: ["name"] }
          }
        },
        purchaseOrderPayment: {
          role: "extension", // PK = purchaseOrderId
          snapshotFields: {
            paymentTermId: { table: "paymentTerm", displayColumns: ["name"] },
            invoiceSupplierId: {
              table: "supplier",
              displayColumns: ["name"]
            },
            invoiceSupplierLocationId: {
              table: "supplierLocation",
              displayColumns: ["name"]
            }
          }
        },
        purchaseOrderDelivery: {
          role: "extension", // PK = purchaseOrderId
          snapshotFields: {
            shippingMethodId: {
              table: "shippingMethod",
              displayColumns: ["name"]
            },
            shippingTermId: { table: "shippingTerm", displayColumns: ["name"] },
            locationId: { table: "location", displayColumns: ["name"] },
            customerId: { table: "customer", displayColumns: ["name"] },
            customerLocationId: {
              table: "customerLocation",
              displayColumns: ["name"]
            }
          }
        }
      }
    },

    salesInvoice: {
      label: "Sales Invoice",
      tables: {
        salesInvoice: {
          role: "root",
          // No FK constraint on this column — resolve explicitly.
          snapshotFields: {
            assignee: { table: "user", displayColumns: ["fullName"] }
          }
        },
        salesInvoiceLine: { entityIdColumn: "invoiceId" },
        salesInvoiceShipment: {
          role: "extension", // PK = salesInvoiceId
          // These columns have no FK constraints — resolve explicitly.
          snapshotFields: {
            locationId: { table: "location", displayColumns: ["name"] },
            shippingMethodId: {
              table: "shippingMethod",
              displayColumns: ["name"]
            },
            shippingTermId: { table: "shippingTerm", displayColumns: ["name"] }
          }
        }
      }
    },

    purchaseInvoice: {
      label: "Purchase Invoice",
      tables: {
        purchaseInvoice: { role: "root" },
        purchaseInvoiceLine: {
          entityIdColumn: "invoiceId",
          // No FK constraint on this column — resolve explicitly.
          snapshotFields: {
            assetId: { table: "fixedAsset", displayColumns: ["name"] }
          }
        }
      }
    },

    salesQuote: {
      label: "Quote",
      tables: {
        quote: { role: "root" },
        quoteLine: { entityIdColumn: "quoteId" }
      }
    },

    supplierQuote: {
      label: "Supplier Quote",
      tables: {
        supplierQuote: { role: "root" },
        supplierQuoteLine: { entityIdColumn: "supplierQuoteId" }
      }
    },

    productionJob: {
      label: "Job",
      tables: {
        job: {
          role: "root",
          // No FK constraints on these columns — resolve explicitly.
          snapshotFields: {
            modelUploadId: { table: "modelUpload", displayColumns: ["name"] },
            quoteLineId: { table: "quoteLine", displayColumns: ["description"] }
          }
        },
        jobOperation: { entityIdColumn: "jobId" },
        jobMaterial: {
          entityIdColumn: "jobId",
          snapshotFields: {
            // No FK constraint on this column — resolve explicitly.
            substitutedFromItemId: {
              table: "item",
              displayColumns: ["readableId"]
            }
          }
        },
        jobMakeMethod: { entityIdColumn: "jobId" }
      }
    },

    employee: {
      label: "Employee",
      tables: {
        employee: { role: "root" },
        employeeJob: { role: "extension" } // PK = employee id
      }
    },

    itarCertification: {
      label: "ITAR Certification",
      tables: {
        itarCertification: {
          role: "root",
          createFields: [
            "type",
            "userId",
            "docVersion",
            "docHash",
            "fullLegalName",
            "title",
            "complianceContact",
            "certifiedAt",
            "expiresAt",
            "ipAddress",
            "userAgent"
          ]
        }
      }
    },

    invite: {
      label: "Invite",
      tables: {
        invite: {
          role: "root",
          createFields: [
            "email",
            "role",
            "createdBy",
            "attestedBy",
            "attestedAt"
          ]
        }
      }
    },

    nonConformance: {
      label: "Non-Conformance",
      tables: {
        nonConformance: { role: "root" },
        nonConformanceItem: { entityIdColumn: "nonConformanceId" },
        nonConformanceActionTask: { entityIdColumn: "nonConformanceId" },
        nonConformanceApprovalTask: { entityIdColumn: "nonConformanceId" }
      }
    },

    changeOrder: {
      label: "Change Notice",
      tables: {
        changeOrder: { role: "root" },
        changeOrderAffectedItem: { entityIdColumn: "changeOrderId" },
        changeOrderSupersession: { entityIdColumn: "changeOrderId" },
        changeOrderActionTask: { entityIdColumn: "changeOrderId" }
      }
    },

    gauge: {
      label: "Gauge",
      tables: {
        gauge: { role: "root" },
        gaugeCalibrationRecord: { entityIdColumn: "gaugeId" }
      }
    },

    shipment: {
      label: "Shipment",
      tables: {
        shipment: { role: "root" },
        shipmentLine: { entityIdColumn: "shipmentId" }
      }
    },

    receipt: {
      label: "Receipt",
      tables: {
        receipt: { role: "root" },
        receiptLine: { entityIdColumn: "receiptId" }
      }
    },

    warehouseTransfer: {
      label: "Warehouse Transfer",
      tables: {
        warehouseTransfer: { role: "root" },
        warehouseTransferLine: { entityIdColumn: "transferId" }
      }
    },

    stockTransfer: {
      label: "Stock Transfer",
      tables: {
        stockTransfer: { role: "root" },
        stockTransferLine: { entityIdColumn: "stockTransferId" }
      }
    },

    inventoryCount: {
      label: "Inventory Count",
      tables: {
        inventoryCount: { role: "root" },
        // Line INSERTs (bulk snapshot generation) are auto-skipped for non-root
        // tables; only UPDATE/DELETE on a counted line are logged.
        inventoryCountLine: {
          entityIdColumn: "inventoryCountId",
          // These columns have no FK constraints — resolve explicitly.
          snapshotFields: {
            locationId: { table: "location", displayColumns: ["name"] },
            storageUnitId: { table: "storageUnit", displayColumns: ["name"] },
            trackedEntityId: {
              table: "trackedEntity",
              displayColumns: ["readableId"]
            }
          }
        }
      }
    },

    workCenter: {
      label: "Work Center",
      tables: {
        workCenter: { role: "root" },
        workCenterProcess: { entityIdColumn: "workCenterId" }
      }
    },

    maintenanceSchedule: {
      label: "Maintenance Schedule",
      tables: {
        maintenanceSchedule: { role: "root" },
        maintenanceScheduleItem: {
          entityIdColumn: "maintenanceScheduleId"
        }
      }
    },

    maintenanceDispatch: {
      label: "Maintenance Dispatch",
      tables: {
        maintenanceDispatch: { role: "root" },
        maintenanceDispatchEvent: { entityIdColumn: "maintenanceDispatchId" },
        maintenanceDispatchComment: {
          entityIdColumn: "maintenanceDispatchId"
        }
      }
    },

    pricingRule: {
      label: "Pricing Rule",
      tables: {
        pricingRule: { role: "root" }
      }
    },

    priceOverride: {
      label: "Price Override",
      tables: {
        customerItemPriceOverride: { role: "root" },
        customerItemPriceOverrideBreak: {
          entityIdColumn: "customerItemPriceOverrideId"
        }
      }
    },

    priceOverrideBreak: {
      label: "Price Override Break",
      tables: {
        customerItemPriceOverrideBreak: {
          role: "root",
          createFields: ["quantity", "overridePrice", "active"]
        }
      }
    },

    fixedAsset: {
      label: "Fixed Asset",
      tables: {
        fixedAsset: { role: "root" }
      }
    },

    accountingPeriod: {
      label: "Accounting Period",
      tables: {
        accountingPeriod: {
          role: "root",
          createFields: ["startDate", "endDate", "status"]
        }
      }
    },

    journalEntry: {
      label: "Journal Entry",
      tables: {
        journal: { role: "root" },
        journalLine: {
          entityIdColumn: "journalId",
          createFields: ["accountId", "description", "amount"],
          snapshotFields: {
            accountId: { table: "account", displayColumns: ["number", "name"] }
          }
        }
      }
    },

    // A settlement row is shown under every payment it involves — the one
    // that made it, the one a memo was applied through, and the prior credit
    // it draws on — and under the memo it applies; a null column is skipped.
    payment: {
      label: "Payment",
      tables: {
        payment: { role: "root" },
        invoiceSettlement: {
          entityIdColumn: [
            "paymentId",
            "appliedViaPaymentId",
            "sourcePaymentId"
          ]
        }
      }
    },

    memo: {
      label: "Memo",
      tables: {
        memo: { role: "root" },
        invoiceSettlement: { entityIdColumn: "memoId" }
      }
    },

    reimbursement: {
      label: "Reimbursement",
      tables: {
        reimbursement: { role: "root" },
        reimbursementLine: { entityIdColumn: "reimbursementId" }
      }
    },

    pickingList: {
      label: "Picking List",
      tables: {
        pickingList: { role: "root" },
        pickingListLine: { entityIdColumn: "pickingListId" }
      }
    },

    depreciationRun: {
      label: "Depreciation Run",
      tables: {
        depreciationRun: { role: "root" },
        depreciationRunLine: { entityIdColumn: "depreciationRunId" }
      }
    },

    revenueRecognitionRun: {
      label: "Revenue Recognition Run",
      tables: {
        revenueRecognitionRun: { role: "root" },
        revenueRecognitionRunLine: { entityIdColumn: "runId" }
      }
    }
  } satisfies Record<string, EntityConfig>,

  /**
   * Human-readable labels for table names, used in the UI to show
   * provenance (e.g. "Contact" instead of "customerContact").
   * Tables not listed here fall back to a camelCase → Title Case conversion.
   */
  tableLabels: {
    journal: "Journal Entry",
    invoiceSettlement: "Settlement",
    revenueRecognitionRunLine: "Recognition Line",
    customer: "Customer",
    customerPayment: "Payment",
    customerShipping: "Shipping",
    customerTax: "Tax",
    customerBankAccount: "Bank Account",
    contact: "Contact",
    address: "Address",
    supplier: "Supplier",
    supplierPayment: "Payment",
    supplierShipping: "Shipping",
    supplierTax: "Tax",
    supplierBankAccount: "Bank Account",
    supplierPart: "Supplier Part",
    item: "Item",
    itemShelfLife: "Shelf Life",
    itemCost: "Cost",
    itemPlanning: "Planning",
    itemReplenishment: "Replenishment",
    itemUnitSalePrice: "Unit Sale Price",
    customerPartToItem: "Customer Part Mapping",
    salesOrder: "Sales Order",
    salesOrderLine: "Line Item",
    salesOrderPayment: "Payment",
    salesOrderShipment: "Shipment",
    rentalAgreement: "Rental Agreement",
    rentalAgreementLine: "Unit",
    lease: "Lease",
    leaseLine: "Asset",
    leasePayment: "Payment",
    purchaseOrder: "Purchase Order",
    purchaseOrderLine: "Line Item",
    purchaseOrderPayment: "Payment",
    purchaseOrderDelivery: "Delivery",
    salesInvoice: "Sales Invoice",
    salesInvoiceLine: "Line Item",
    salesInvoiceShipment: "Shipment",
    purchaseInvoice: "Purchase Invoice",
    purchaseInvoiceLine: "Line Item",
    quote: "Quote",
    quoteLine: "Line Item",
    supplierQuote: "Supplier Quote",
    supplierQuoteLine: "Line Item",
    job: "Job",
    jobOperation: "Operation",
    jobMaterial: "Material",
    jobMakeMethod: "Make Method",
    employee: "Employee",
    employeeJob: "Job Info",
    nonConformance: "Non-Conformance",
    nonConformanceItem: "Item",
    nonConformanceActionTask: "Action Task",
    nonConformanceApprovalTask: "Approval Task",
    gauge: "Gauge",
    gaugeCalibrationRecord: "Calibration Record",
    shipment: "Shipment",
    shipmentLine: "Line Item",
    receipt: "Receipt",
    receiptLine: "Line Item",
    warehouseTransfer: "Warehouse Transfer",
    warehouseTransferLine: "Line Item",
    stockTransfer: "Stock Transfer",
    stockTransferLine: "Line Item",
    inventoryCount: "Inventory Count",
    inventoryCountLine: "Line Item",
    workCenter: "Work Center",
    workCenterProcess: "Process",
    maintenanceSchedule: "Maintenance Schedule",
    maintenanceScheduleItem: "Schedule Item",
    maintenanceDispatch: "Dispatch",
    maintenanceDispatchEvent: "Dispatch Event",
    maintenanceDispatchComment: "Dispatch Comment",
    pricingRule: "Pricing Rule",
    customerItemPriceOverride: "Price Override",
    customerItemPriceOverrideBreak: "Quantity Break",
    fixedAsset: "Fixed Asset",
    accountingPeriod: "Accounting Period",
    group: "Group",
    location: "Location",
    user: "User"
  } satisfies Partial<Record<TableName, string>>,

  /** Fields to skip in diff computation */
  skipFields: ["updatedAt", "updatedBy", "embedding"],

  /** Retention period before archival (days) */
  retentionDays: 30,

  /** Archive storage path template (company-bucket keys keep the companyId
   * prefix) */
  archivePath: "{companyId}/audit-logs/{year}/{month}.jsonl.gz"
} as const;

// ---------------------------------------------------------------------------
// FK display registry
// ---------------------------------------------------------------------------

/**
 * Display columns per FK *target* table, keyed by the table being pointed at
 * — one entry per referenced table, reused by every FK that points there.
 *
 * The audit handler discovers which diff columns are FKs (and what they
 * reference) from the schema itself via the `get_foreign_key_map` RPC, then
 * uses this registry to decide what to read from the target row and freeze
 * into the diff as `snapshot.old` / `snapshot.new`. A target table missing
 * from this registry degrades gracefully: the diff keeps the raw id.
 *
 * Per-column `snapshotFields` on a table config still win over this registry
 * (use them to pick different columns for one specific FK).
 *
 * A single-column list renders as an inline pill in the audit drawer; a
 * multi-column list renders as an expanded section.
 */
export const fkDisplayRegistry: {
  [T in TableName]?: readonly ColumnOf<T>[];
} = {
  // ability has no name of its own — it displays via the process one hop away
  // (see fkDisplayHops below).
  account: ["number", "name"],
  address: ["addressLine1", "city"],
  assemblyInstruction: ["name"],
  changeOrder: ["changeOrderId"],
  changeOrderRequiredAction: ["name"],
  changeOrderType: ["name"],
  company: ["name"],
  contact: ["fullName"],
  costCenter: ["name"],
  customer: ["name"],
  customerLocation: ["name"],
  customerStatus: ["name"],
  customerType: ["name"],
  department: ["name"],
  employeeType: ["name"],
  fixedAsset: ["name"],
  fixedAssetClass: ["name"],
  gauge: ["gaugeId"],
  gaugeType: ["name"],
  inspectionDocument: ["fileName"],
  inventoryCount: ["inventoryCountId"],
  item: ["readableId"],
  itemPostingGroup: ["name"],
  job: ["jobId"],
  jobMaterial: ["description"],
  jobOperation: ["description"],
  location: ["name"],
  maintenanceDispatch: ["maintenanceDispatchId"],
  maintenanceFailureMode: ["name"],
  maintenanceSchedule: ["name"],
  modelUpload: ["name"],
  nonConformance: ["nonConformanceId"],
  nonConformanceRequiredAction: ["name"],
  nonConformanceType: ["name"],
  nonConformanceWorkflow: ["name"],
  paymentTerm: ["name"],
  pricingRule: ["name"],
  procedure: ["name"],
  process: ["name"],
  purchaseInvoice: ["invoiceId"],
  purchaseOrder: ["purchaseOrderId"],
  purchaseOrderLine: ["description"],
  quote: ["quoteId"],
  receipt: ["receiptId"],
  rework: ["reason"],
  salesInvoice: ["invoiceId"],
  salesOrder: ["salesOrderId"],
  salesOrderLine: ["description"],
  shift: ["name"],
  shipment: ["shipmentId"],
  shippingMethod: ["name"],
  shippingTerm: ["name"],
  stockTransfer: ["stockTransferId"],
  storageUnit: ["name"],
  supplier: ["name"],
  supplierLocation: ["name"],
  supplierQuote: ["supplierQuoteId"],
  supplierType: ["name"],
  trackedEntity: ["readableId"],
  unitOfMeasure: ["name"],
  user: ["fullName"],
  warehouseTransfer: ["transferId"],
  workCenter: ["name"]
} as const;

/**
 * Junction FK targets whose display value lives one hop away, keyed by the
 * table being pointed at. A column like `invoiceSupplierContactId` references
 * a `supplierContact` link row, which has no displayable columns of its own —
 * the person's name is on `contact`, reachable via the junction's `column`.
 *
 * The audit handler resolves these in two batched lookups: junction rows
 * (id → hop column) then display rows (hop id → displayColumns). A junction
 * table listed here must NOT also appear in `fkDisplayRegistry` — hops take
 * precedence.
 */
export type FkDisplayHop = {
  /** Column on the junction row holding the display row's id. */
  column: string;
  /** Table the display columns are read from. */
  table: string;
  displayColumns: readonly string[];
};

export const fkDisplayHops: {
  [T in TableName]?: {
    [D in TableName]: {
      column: ColumnOf<T>;
      table: D;
      displayColumns: readonly ColumnOf<D>[];
    };
  }[TableName];
} = {
  ability: {
    column: "processId",
    table: "process",
    displayColumns: ["name"]
  },
  customerContact: {
    column: "contactId",
    table: "contact",
    displayColumns: ["fullName"]
  },
  customerItemPriceOverride: {
    column: "itemId",
    table: "item",
    displayColumns: ["readableId"]
  },
  fulfillment: {
    column: "jobId",
    table: "job",
    displayColumns: ["jobId"]
  },
  jobMakeMethod: {
    column: "itemId",
    table: "item",
    displayColumns: ["readableId"]
  },
  makeMethod: {
    column: "itemId",
    table: "item",
    displayColumns: ["readableId"]
  },
  opportunity: {
    column: "customerId",
    table: "customer",
    displayColumns: ["name"]
  },
  supplierContact: {
    column: "contactId",
    table: "contact",
    displayColumns: ["fullName"]
  },
  supplierInteraction: {
    column: "supplierId",
    table: "supplier",
    displayColumns: ["name"]
  }
} as const;

// ---------------------------------------------------------------------------
// Derived types
// ---------------------------------------------------------------------------

export type AuditEntityType = keyof typeof auditConfig.entities;

export type AuditableTable = string;

/** @deprecated Use AuditableTable or AuditEntityType instead */
export type AuditableEntity = AuditableTable;

// ---------------------------------------------------------------------------
// Lookup indexes (computed once at import time)
// ---------------------------------------------------------------------------

type EntityTableEntry = {
  entityType: AuditEntityType;
  label: string;
  tableConfig: TableConfig;
};

/** Map from table name → array of entity configs that include it */
const _tableIndex = new Map<string, EntityTableEntry[]>();

/** Deduplicated set of all auditable table names */
const _allTables = new Set<string>();

for (const [entityType, entityConfig] of Object.entries(auditConfig.entities)) {
  for (const [tableName, tableConfig] of Object.entries(entityConfig.tables)) {
    _allTables.add(tableName);

    const existing = _tableIndex.get(tableName) ?? [];
    existing.push({
      entityType: entityType as AuditEntityType,
      label: entityConfig.label,
      tableConfig
    });
    _tableIndex.set(tableName, existing);
  }
}

// ---------------------------------------------------------------------------
// Public helpers
// ---------------------------------------------------------------------------

/** Check if a table is tracked by any audit entity */
export function isAuditableTable(table: string): boolean {
  return _tableIndex.has(table);
}

/**
 * Columns to include in the diff for INSERT events on this table.
 * Returns an empty array when no createFields are configured.
 */
export function getCreateFields(config: TableConfig): readonly string[] {
  return config.createFields ?? [];
}

/**
 * FK columns on this table whose target row's display values should be
 * snapshotted into the diff at write time. Each entry maps a column name
 * to its target table and the columns on that table to read as the human
 * display values. A single-column list yields a Linear-style inline pill
 * in the audit drawer; a multi-column list renders as an expanded section.
 */
export function getSnapshotFields(
  config: TableConfig
): readonly SnapshotFieldEntry[] {
  const snapshot = config.snapshotFields;
  if (!snapshot) return [];
  const entries: SnapshotFieldEntry[] = [];
  for (const [column, spec] of Object.entries(snapshot) as [
    string,
    { table: string; displayColumns: readonly string[] } | undefined
  ][]) {
    if (!spec) continue;
    entries.push({
      column,
      table: spec.table,
      displayColumns: spec.displayColumns
    });
  }
  return entries;
}

/** @deprecated Use isAuditableTable instead */
export function isAuditableEntity(table: string): boolean {
  return isAuditableTable(table);
}

/**
 * Get all entity configs that track a given table.
 * Returns an array because a table can belong to multiple entities
 * (e.g. `contact` belongs to both `customer` and `supplier`).
 */
export function getEntityConfigsForTable(
  table: string
): readonly EntityTableEntry[] {
  return _tableIndex.get(table) ?? [];
}

/** Get deduplicated list of all auditable table names */
export function getAuditableTableNames(): string[] {
  return Array.from(_allTables);
}

/** Get list of all entity type keys */
export function getEntityTypes(): AuditEntityType[] {
  return Object.keys(auditConfig.entities) as AuditEntityType[];
}

/** Get human-readable label for an entity type */
export function getEntityLabel(entityType: AuditEntityType): string {
  return auditConfig.entities[entityType]?.label ?? entityType;
}

/** Get human-readable label for a table name */
export function getTableLabel(tableName: string): string {
  const labels = auditConfig.tableLabels as Record<string, string | undefined>;
  return (
    labels[tableName] ??
    tableName
      .replace(/([A-Z])/g, " $1")
      .trim()
      .replace(/^./, (c) => c.toUpperCase())
  );
}

/**
 * Check if a table config is a root table (PK = entity ID)
 */
export function isRootTable(
  config: TableConfig
): config is RootTable<TableName> {
  return "role" in config && config.role === "root";
}

/**
 * Check if a table config is a 1:1 extension table (PK = parent FK).
 * Entity ID resolution is the same as root (use recordId), but
 * INSERT events are skipped since they are just empty default rows.
 */
export function isExtensionTable(
  config: TableConfig
): config is ExtensionTable<TableName> {
  return "role" in config && config.role === "extension";
}

/**
 * Check if a table config is a direct child (has entityIdColumn)
 */
export function isChildTable(
  config: TableConfig
): config is ChildTable<TableName> {
  return "entityIdColumn" in config && !("resolve" in config);
}

/**
 * Check if a table config requires junction table resolution
 */
export function isIndirectTable(
  config: TableConfig
): config is IndirectTable<TableName> {
  return "resolve" in config;
}