Title: Handling Implicit Many-to-Many Relationship Join Tables in ZenStack ZModel: Schema Mismatch and Workarounds

[m839336369]

Description and expected behavior
When defining an implicit many-to-many relationship in ZenStack's ZModel schema, the automatically generated join table (e.g., _ApiToService) is placed in the PostgreSQL publicschema by default. This occurs regardless of the defaultSchemasetting specified in the datasource block. Consequently, when the application attempts to query the relationship, it constructs SQL that references the join table in the non-publicschema (e.g., tcm._ApiToService), leading to a "relation does not exist" database error because the table exists only in public.

reason: 'db-query-error',
model: undefined,
dbErrorCode: '42P01',
dbErrorMessage: 'relation "public._ApiToService" does not exist',
rejectedByPolicyReason: undefined,
sql: `select "Service"."id" as "id", "Service"."name" as "name", "Service"."description" as "description", "Service"."status" as "status", "Service"."createdAt" as "createdAt", "Service"."updatedAt" as "updatedAt", "$$t1"."$data" as "apis" from "tcm"."service" as "Service" left join lateral (select coalesce(jsonb_agg(jsonb_build_object('id', "$$t2"."id", 'route', "$$t2"."route", 'endpoint', "$$t2"."endpoint", 'status', "$$t2"."status", 'openapi', "$$t2"."openapi", 'createdAt', "$$t2"."createdAt", 'updatedAt', "$$t2"."updatedAt")), '[]'::jsonb) as "$data" from "tcm"."api" as "$$t2" where "$$t2"."id" in (select "_ApiToService"."A" from "public"."_ApiToService" where "Service"."id" = "_ApiToService"."B")) as "$$t1" on true where true `,
sqlParams: [],
[cause]: error: relation "public._ApiToService" does not exist

Screenshots
If applicable, add screenshots to help explain your problem.

Environment (please complete the following information):

"devDependencies": {
"@types/pg": "^8.20.0",
"@zenstackhq/cli": "^3.5.6",
"@zenstackhq/openapi": "^2.22.1",
"prisma": "^7.7.0",
"rolldown": "^1.0.0-rc.15",
"rolldown-plugin-dts": "^0.23.2",
"typescript": "~5.7.2"
},
"dependencies": {
"@zenstackhq/orm": "^3.5.6",
"@zenstackhq/plugin-policy": "^3.5.6",
"@zenstackhq/schema": "^3.5.6",
"@zenstackhq/zod": "^3.5.6",
"pg": "^8.20.0",
}

Additional context
Add any other context about the problem here.

[ymc9]

Fixed in 3.6.2