generate schema fails with circular reference when resolving result set for correlated IN ACL subquery

[graydonpleasants]

Summary

powersync generate schema fails with an internal error when a Sync Streams Edition 3 config uses a correlated IN subquery that references the outer row (upa.project_id = p.id) inside an ACL CTE. The same config passes powersync validate.

Environment

• PowerSync Service: journeyapps/powersync-service:1.21.0 (local Docker)
• CLI versions tested: powersync@0.9.3, 0.9.4, 0.9.5, 0.9.6, and latest — all fail
• API URL: http://localhost:8080
• Auth: PS_ADMIN_TOKEN matching self-hosted api.tokens

Error

Error: Failed to generate schema, :: internal error: circular reference
when resolving result set

Minimal repro

config:
  edition: 3

streams:
  user_projects:
    auto_subscribe: false
    accept_potentially_dangerous_queries: true
    with:
      active_project_ids: |
        SELECT DISTINCT p.id
        FROM projects p
        JOIN users u ON true
        WHERE u.cognito_id = auth.parameter('sub')
          AND p.is_deleted = false
          AND connection.parameter('project_id') IS NOT NULL
          AND p.id::text = connection.parameter('project_id')
          AND p.id IN (
            SELECT upa.project_id
            FROM user_project_access upa
            WHERE upa.user_id = u.id
              AND upa.project_id = p.id
              AND upa.project_id::text = connection.parameter('project_id')
          )
    query: |
      SELECT id, name FROM projects WHERE id IN active_project_ids

Steps:

# Start PowerSync service with Postgres source (any DB with projects/users/user_project_access tables)
PS_ADMIN_TOKEN=<token> npx powersync@0.9.4 validate --directory=. --api-url=http://localhost:8080
# => passes

PS_ADMIN_TOKEN=<token> npx powersync@0.9.4 generate schema \
  --output=ts --output-path=/tmp/schema.ts \
  --api-url=http://localhost:8080 --directory=.
# => circular reference error

Bisection notes

• Removing only AND upa.project_id = p.id from the subquery makes generate schema succeed.
• When both outer p.id and inner upa.project_id are already constrained by connection.parameter('project_id'), the correlation appears logically redundant for ACL semantics.
• validate does not catch this; only generate schema fails.

Workaround

Omit AND upa.project_id = p.id from the IN subquery when the active project is already scoped via connection.parameter('project_id') on both sides.

Expected behavior

generate schema should succeed (or validate should warn) for configs that pass validation and work at runtime.

[simolus3]

If the service accepts it, I wonder if this is due to the CLI using an outdated version of @powersync/sync-rules?

[stevensJourney]

If the service accepts it, I wonder if this is due to the CLI using an outdated version of @powersync/sync-rules?

We just released powersync@0.9.6 which should use the latest version of @powersync/sync-rules. @graydonpleasants could you please test with this version?