PolicyPlugin deep nested include generates malformed PostgreSQL alias reference (42703)

[pkudinov]

Description and expected behavior

With PolicyPlugin enabled, a deep nested include query generates broken SQL alias references on PostgreSQL and fails with SQLSTATE 42703 (column ...productSku does not exist).

This reproduces on 3.4.1 (PostgreSQL, useCompactAliasNames: false).

Minimal schema:

  plugin policy {
    provider = '@zenstackhq/plugin-policy'
  }

  plugin prisma {
    provider = '@core/prisma'
    output = 'prisma/schema.prisma'
  }

  model Store {
    id                  String @id
    customerOrders      CustomerOrder[]
    productCatalogItems ProductCatalogItem[]
    @@allow('all', true)
  }

  model CustomerOrder {
    id                          String @id
    storeId                     String
    store                       Store @relation(fields: [storeId], references: [id], onDelete: Cascade)
    customerOrderPaymentSummary CustomerOrderPaymentSummary[]
    @@allow('all', true)
  }

  model CustomerOrderPaymentSummary {
    id                              String @id
    customerOrderId                 String
    customerOrder                   CustomerOrder @relation(fields: [customerOrderId], references: [id], onDelete: Cascade)
    customerOrderPaymentSummaryLine CustomerOrderPaymentSummaryLine[]
    @@allow('all', true)
  }

  model PaymentTransaction {
    id                              String @id
    customerOrderPaymentSummaryLine CustomerOrderPaymentSummaryLine[]
    paymentTransactionLineItem      PaymentTransactionLineItem[]
    @@allow('all', true)
  }

  model CustomerOrderPaymentSummaryLine {
    customerOrderPaymentSummaryId String
    lineIndex                    Int
    paymentTransactionId         String

    customerOrderPaymentSummary  CustomerOrderPaymentSummary @relation(fields: [customerOrderPaymentSummaryId], references: [id], onDelete: Cascade)
    paymentTransaction           PaymentTransaction @relation(fields: [paymentTransactionId], references: [id], onDelete: Cascade)

    @@id([customerOrderPaymentSummaryId, lineIndex])
    @@allow('all', true)
  }

  model ProductCatalogItem {
    storeId                    String
    sku                        String

    store                      Store @relation(fields: [storeId], references: [id], onDelete: Cascade)
    paymentTransactionLineItem PaymentTransactionLineItem[]

    @@id([storeId, sku])
    @@allow('all', true)
  }

  model InventoryReservation {
    id                         String @id
    paymentTransactionLineItem PaymentTransactionLineItem[]
    @@allow('all', true)
  }

  model PaymentTransactionLineItem {
    paymentTransactionId   String
    lineNumber             Int
    storeId                String
    productSku             String
    inventoryReservationId String?

    paymentTransaction     PaymentTransaction @relation(fields: [paymentTransactionId], references: [id], onDelete: Cascade)
    productCatalogItem     ProductCatalogItem @relation(fields: [storeId, productSku], references: [storeId, sku])
    inventoryReservation   InventoryReservation? @relation(fields: [inventoryReservationId], references: [id], onDelete: SetNull)

    @@id([paymentTransactionId, lineNumber])
    @@allow('all', true)
  }

Repro query:

  await db.customerOrderPaymentSummary.findUnique({
    where: { id: 'summary_1' },
    include: {
      customerOrder: true,
      customerOrderPaymentSummaryLine: {
        include: {
          paymentTransaction: {
            include: {
              paymentTransactionLineItem: {
                include: {
                  productCatalogItem: true,
                  inventoryReservation: true,
                },
              },
            },
          },
        },
      },
    },
  });

Observed error:

  error: column $$_CustomerOrderPaymentSummary$customerOrderPaymentSummaryLine$.productSku does not exist
  SQLSTATE: 42703

Expected behavior: the query should execute successfully and return nested data; generated aliases should be valid and consistently referenced.

Screenshots

N/A

Environment (please complete the following information):

• ZenStack version: 3.4.1
• Database type: PostgreSQL (16)
• Node.js/Bun version: Node.js 22.x
• Package manager: pnpm

Additional context

Generated SQL contains very long aliases in this deep nested include path, e.g.:

  - "$$_CustomerOrderPaymentSummary$customerOrderPaymentSummaryLine$paymentTransaction$paymentTransactionLineItem$sub"
  - "$$_CustomerOrderPaymentSummary$customerOrderPaymentSummaryLine$paymentTransaction$paymentTransactionLineItem$productCatalogItem$sub"

But later SQL references a malformed alias segment (...$customerOrderPaymentSummaryLine$.productSku), causing the missing-column failure.

Potentially related areas:

• packages/orm/src/client/crud/dialects/lateral-join-dialect-base.ts
• packages/orm/src/client/query-utils.ts (tmpAlias)
• packages/orm/src/client/executor/zenstack-query-executor.ts (processTempAlias)