Policy plugin SQL generation bug for complex computed fields in SQLite

[lombo75]

Description and expected behavior
I think there is a bug or limitation in the ZenStack policy plugin’s SQL rewriting for computed fields—especially with complex subqueries or correlated subqueries in SQLite.

The computed field SQL for [total], [subTotal] etc., is valid on its own.
When you use the policy-wrapped client ([db] with [PolicyPlugin]), ZenStack rewrites the query to enforce row-level security.
This rewriting sometimes produces malformed SQL (e.g., stray or mismatched braces, or invalid subquery placement) that SQLite cannot parse, resulting in errors like:
SqliteError: unrecognized token: "}"
Evidence:

The same computed field works with the raw client ([rawDb])) but fails with the policy-wrapped client.
The error is not a TypeScript or schema error, but a runtime SQL parse error after ZenStack’s policy transformation.

total: (qb, { modelAlias }) =>
sqlCOALESCE(( SELECT SUM((("WorkItem"."budgetedPrice" * "WorkItem"."quantity") * (1 + COALESCE("WorkItem"."profit", 0) / 100.0)) - COALESCE("WorkItem"."adjustment", 0)) FROM "WorkItem" INNER JOIN "WorkOrder" ON "WorkItem"."workOrderId" = "WorkOrder"."id" WHERE "WorkOrder"."projectId" = ${sql.ref(${modelAlias}.projectId)} ), 0),

subTotal: (qb, { modelAlias }) =>
sql( COALESCE(( SELECT SUM((("WorkItem"."budgetedPrice" * "WorkItem"."quantity") * (1 + COALESCE("WorkItem"."profit", 0) / 100.0)) - COALESCE("WorkItem"."adjustment", 0)) FROM "WorkItem" INNER JOIN "WorkOrder" ON "WorkItem"."workOrderId" = "WorkOrder"."id" WHERE "WorkOrder"."projectId" = ${sql.ref(${modelAlias}.projectId)} ), 0) * (1 + COALESCE(${sql.ref(${modelAlias}.tax)}, 0) / 100.0) ) - COALESCE(${sql.ref(${modelAlias}.discount)}, 0),

Environment (please complete the following information):

• ZenStack version: [e.g., 3..6.4]
• Database type: [e.g. Sqlite]
• Node.js/Bun version: [e.g., v25.8.2]
• Package manager: [pnpm]

Additional context
Evidence:

The same computed field works with the raw client (rawDb but fails with the policy-wrapped client.
The error is not a TypeScript or schema error, but a runtime SQL parse error after ZenStack’s policy transformation.

What to do:

Report this to the ZenStack maintainers with a minimal reproduction (computed field, policy, and failing query).
As a workaround, avoid selecting these computed fields through the policy-wrapped client, or compute them in route code as you’ve done.

Summary:
This is not a bug in your SQL or schema, but a ZenStack policy plugin SQL generation bug for complex computed fields in SQLite.

[ymc9]

Hi @lombo75 , thanks for reporting this. Could you share the related zmodel schema (with policy expressions), and the ORM query that triggers the issue? Thanks!

[lombo75]

model EntityId {
id String @id @default(cuid()) @meta(name: "description", value: "The unique identifier of the entity, generated as a CUID string")
entityIdType String @meta(name: "description", value: "The type of the entity, e.g., 'Bid', 'Estimate', 'WorkOrder'")
@@DeleGate(entityIdType) // This allows us to use the EntityId model as a base for different types of entities (e.g., Bid, Estimate, WorkOrder) while keeping common fields in one place.
}

model AuditFields extends EntityId {
createdBy String? @default("system") @meta(name: "description", value: "The ID of the user who created the record, or 'system' if created by the system")
createdAt DateTime @default(now()) @meta(name: "description", value: "The timestamp when the record was created")
updatedBy String? @default("system") @meta(name: "description", value: "The ID of the user who last updated the record, or 'system' if updated by the system")
updatedAt DateTime @updatedat @meta(name: "description", value: "The timestamp when the record was last updated")
auditType String @meta(name: "description", value: "The type of audit, e.g., 'BidAudit', 'EstimateAudit'")
// This allows us to include the common audit fields in the AuditFields model without having to redefine them in every model that requires auditing.
@@DeleGate(auditType)
}

model WorkItem extends AuditFields {
itemNo String @unique @meta(name: "description", value: "The unique identifier for the work item")
name String @meta(name: "description", value: "The name of the work item")
description String? @meta(name: "description", value: "A description of the work item")
quantity Int @gte(0) @meta(name: "description", value: "The quantity of the work item")
budgetedPrice Float @gte(0) @meta(name: "description", value: "The budgeted price for the work item")
actualPrice Float @gte(0) @meta(name: "description", value: "The actual price for the work item")
adjustment Float? @gte(0) @meta(name: "description", value: "The adjustment amount for the work item")
profit Float? @gte(0) @meta(name: "description", value: "The profit for the work item")
scopeOfWorks String? @meta(name: "description", value: "The scope of works for the work item")
workItemStatus WorkItemStatus @meta(name: "description", value: "The status of the work item")

// Relations
workOrder WorkOrder @relation(fields: [workOrderId], references: [id], onDelete: Cascade, onUpdate: Cascade) @meta(name: "description", value: "The work order that this work item belongs to")
unit WorkItemUnitType @relation(fields: [workItemUnitTypeId], references: [id], onDelete: Restrict, onUpdate: Restrict) @meta(name: "description", value: "The unit type of the work item")
workItemUnitTypeId String @meta(name: "description", value: "The ID of the unit type for the work item")
workItemDefaultId String? @meta(name: "description", value: "The ID of the default settings for the work item, if applicable")
workOrderId String @meta(name: "description", value: "The ID of the work order that this work item belongs to")
}

model WorkOrder extends AuditFields {
workOrderName String @meta(name: "description", value: "The name of the work order")
description String? @meta(name: "description", value: "A description of the work order")
project Project? @relation(fields: [projectId], references: [id], onDelete: Cascade, onUpdate: Cascade) @meta(name: "description", value: "The project that this work order is associated with, if applicable")
workItems WorkItem[] @meta(name: "description", value: "The work items associated with the work order")
issuedOn DateTime @default(now()) @meta(name: "description", value: "The date when the work order was issued")
dueOn DateTime? @meta(name: "description", value: "The date when the work order is due")
completionOn DateTime? @meta(name: "description", value: "The date when the work order was completed")

// Relations
workOrderStatus WorkOrderStatus @meta(name: "description", value: "The status of the work order")
workOrderType WorkOrderType @meta(name: "description", value: "The type of the work order")

// Relations
projectId String? @meta(name: "description", value: "The ID of the project associated with the work order")

@@unique([projectId, workOrderName, zone], name: "unique_work_order")
}

model Project extends AuditFields {
projectNo Int @unique @meta(name: "description", value: "The unique number of the project")
name String @meta(name: "description", value: "The name of the project")
description String? @meta(name: "description", value: "The description of the project")
status ProjectStatus @relation(fields: [statusId], references: [id]) @meta(name: "description", value: "The status of the project")
statusId String @meta(name: "description", value: "The ID of the project status associated with the project")
startDate DateTime? @meta(name: "description", value: "The planned start date of the project")
endDate DateTime? @meta(name: "description", value: "The planned end date of the project")
progress Int @default(0) @gte(0) @LTe(100) @meta(name: "description", value: "The progress of the project in percentage")
workOrders WorkOrder[] @meta(name: "description", value: "The work orders associated with the project")

}

model Quote extends AuditFields {
name String @meta(name: "description", value: "The name of the quote")
quoteNo Int @meta(name: "description", value: "The number of the quote")
revision Int @gte(0) @meta(name: "description", value: "The revision number of the quote")
attention String @meta(name: "description", value: "The attention of the quote")
total Float @gte(0) @computed @meta(name: "description", value: "The total amount of the quote")
discount Float? @gte(0) @meta(name: "description", value: "The discount applied to the quote")
tax Float? @gte(0) @meta(name: "description", value: "The tax applied to the quote")
subTotal Float @gte(0) @computed @meta(name: "description", value: "The subtotal amount of the quote")
balanceDue Float @gte(0) @computed @meta(name: "description", value: "The balance due for the quote")
balancePaid Float @gte(0) @computed @meta(name: "description", value: "The balance paid for the quote")
shipping Float? @gte(0) @meta(name: "description", value: "The shipping cost for the quote")
notes String? @meta(name: "description", value: "Additional notes for the quote")
submittedBy String? @meta(name: "description", value: "The person who submitted the quote")
submittedOn DateTime? @meta(name: "description", value: "The date when the quote was submitted")
dueDate DateTime? @meta(name: "description", value: "The due date for the quote")

// Relations
project Project @relation(fields: [projectId], references: [id], onDelete: Cascade, onUpdate: Cascade) @meta(name: "description", value: "The project associated with the quote")
quoteStatus QuoteStatus @default(DRAFT) @meta(name: "description", value: "The status of the quote, such as 'Draft', 'Submitted', 'Approved', etc.")
projectId String @meta(name: "description", value: "The ID of the project associated with the quote")

@@unique([projectId, quoteNo, revision])
}

const project = await db.project.findFirst({
where: { id },
select: { id: true }
})

if (!project) return NextResponse.json({ error: 'Project not found.' }, { status: 404 })

const quotes = await db.quote.findMany({
  where: { projectId: id },
  select: {
    id: true,
    entityIdType: true,
    createdBy: true,
    createdAt: true,
    updatedBy: true,
    updatedAt: true,
    name: true,
    quoteNo: true,
    revision: true,
    attention: true,
    discount: true,
    tax: true,
    shipping: true,
    notes: true,
    submittedBy: true,
    submittedOn: true,
    dueDate: true,
    quoteStatus: true,
    projectId: true,

total:true,
subTotal:true
}
})

i have configured policy plugin but dont have any access policies on the models yet

[lombo75]

i removed @gte(0) from the total and subtotal, balanceDue, balancePaid fields and that fixed total and subTotal but i am still having issues with balanceDue and balancePaid fields
balanceDue: (_qb: unknown, { modelAlias }: { modelAlias: string }) =>
sql( ( COALESCE(( SELECT SUM((("WorkItem"."budgetedPrice" * "WorkItem"."quantity") * (1 + COALESCE("WorkItem"."profit", 0) / 100.0)) - COALESCE("WorkItem"."adjustment", 0)) FROM "WorkItem" INNER JOIN "WorkOrder" ON "WorkItem"."workOrderId" = "WorkOrder"."id" WHERE "WorkOrder"."projectId" = ${sql.ref(${modelAlias}.projectId)} ), 0) * (1 + COALESCE(${sql.ref(${modelAlias}.tax)}, 0) / 100.0) ) - COALESCE(${sql.ref(${modelAlias}.discount)}, 0) ) - COALESCE(( SELECT SUM("Transaction"."amount") FROM "TransactionTerm" INNER JOIN "Transaction" ON "TransactionTerm"."transactionId" = "Transaction"."id" WHERE "TransactionTerm"."quoteId" = ${sql.ref(${modelAlias}.id)} AND "Transaction"."transactionType" = 'CREDIT' AND "Transaction"."transactionStatus" = 'PAID' ), 0),

balancePaid: (qb, { modelAlias }) =>
qb
.selectFrom('Quote')
.leftJoin('TransactionTerm', 'Quote.id', 'TransactionTerm.quoteId')
.innerJoin('Project', 'Quote.projectId', 'Project.id')
.innerJoin('Transaction', 'TransactionTerm.transactionId', 'Transaction.id')
.whereRef('Quote.id', '=', sql.ref(${modelAlias}.id))
.where('Transaction.transactionType', '=', 'CREDIT')
.where('Transaction.transactionStatus', '=', 'PAID')
.select(sqlCOALESCE(SUM("Transaction"."amount"), 0).as('balancePaid'))

[lombo75]

@ymc9 got it working all good, closing this issue!

declaring ref externally helped

const projectRef = sql.ref(modelAlias + '.projectId')

total: (_qb: unknown, { modelAlias }: { modelAlias: string }) => {
const projectRef = sql.ref(modelAlias + '.projectId')

  return sql<number>`COALESCE((
    SELECT SUM((("WorkItem"."budgetedPrice" * "WorkItem"."quantity") * (1 + COALESCE("WorkItem"."profit", 0) / 100.0)) - COALESCE("WorkItem"."adjustment", 0))
    FROM "WorkItem"
    INNER JOIN "WorkOrder" ON "WorkItem"."workOrderId" = "WorkOrder"."id"
    WHERE "WorkOrder"."projectId" = ${projectRef}
  ), 0)`
},
subTotal: (_qb: unknown, { modelAlias }: { modelAlias: string }) => {
  const projectRef = sql.ref(modelAlias + '.projectId')
  const taxRef = sql.ref(modelAlias + '.tax')
  const discountRef = sql.ref(modelAlias + '.discount')

  return sql<number>`(
    COALESCE((
      SELECT SUM((("WorkItem"."budgetedPrice" * "WorkItem"."quantity") * (1 + COALESCE("WorkItem"."profit", 0) / 100.0)) - COALESCE("WorkItem"."adjustment", 0))
      FROM "WorkItem"
      INNER JOIN "WorkOrder" ON "WorkItem"."workOrderId" = "WorkOrder"."id"
      WHERE "WorkOrder"."projectId" = ${projectRef}
    ), 0)
    * (1 + COALESCE(${taxRef}, 0) / 100.0)
  ) - COALESCE(${discountRef}, 0)`
},
balanceDue: (_qb: unknown, { modelAlias }: { modelAlias: string }) => {
  const projectRef = sql.ref(modelAlias + '.projectId')
  const taxRef = sql.ref(modelAlias + '.tax')
  const discountRef = sql.ref(modelAlias + '.discount')
  const quoteIdRef = sql.ref(modelAlias + '.id')

  return sql<number>`(
    (
      COALESCE((
        SELECT SUM((("WorkItem"."budgetedPrice" * "WorkItem"."quantity") * (1 + COALESCE("WorkItem"."profit", 0) / 100.0)) - COALESCE("WorkItem"."adjustment", 0))
        FROM "WorkItem"
        INNER JOIN "WorkOrder" ON "WorkItem"."workOrderId" = "WorkOrder"."id"
        WHERE "WorkOrder"."projectId" = ${projectRef}
      ), 0)
      * (1 + COALESCE(${taxRef}, 0) / 100.0)
    ) - COALESCE(${discountRef}, 0)
  ) - COALESCE((
    SELECT SUM("Transaction"."amount")
    FROM "TransactionTerm"
    INNER JOIN "Transaction" ON "TransactionTerm"."id" = "Transaction"."id"
    WHERE "TransactionTerm"."quoteId" = ${quoteIdRef}
      AND "Transaction"."transactionType" = 'CREDIT'
      AND "Transaction"."transactionStatus" = 'PAID'
  ), 0)`
},
balancePaid: (qb: any, { modelAlias }: { modelAlias: string }) => {
  const quoteIdRef = sql.ref(modelAlias + '.id')

  return (
    qb
      .selectFrom('Transaction')
      // Transaction -> TransactionTerm relation is via Transaction.transactionTermId -> TransactionTerm.id
      .innerJoin('TransactionTerm', 'Transaction.id', 'TransactionTerm.id')
      .whereRef('TransactionTerm.quoteId', '=', quoteIdRef)
      .where('Transaction.transactionType', '=', 'CREDIT')
      .where('Transaction.transactionStatus', '=', 'PAID')
      .select(sql<number>`COALESCE(SUM("Transaction"."amount"), 0)`.as('balancePaid'))
  )
}