bug: GET /api/ops/auth-report 500s — SQLAlchemy auto-correlation in get_agent_subscription (#1093, v0.6.1)

[vybe]

Summary

GET /api/ops/auth-report returns HTTP 500 on the v0.6.1 (#1093 SQLAlchemy-Core db rewrite) release. The failure is a compile-time InvalidRequestError in db/subscriptions.py::get_agent_subscription, where a correlated agent-count scalar subquery loses its FROM clause to SQLAlchemy auto-correlation. Because it fails at statement-compile time it 500s for every call regardless of data or backend, and it reproduces identically on both SQLite and PostgreSQL. Token decrypt/inject (subscription auth) is unaffected — only paths that call get_agent_subscription (the fleet auth-report, per-agent subscription-status lookups) break.

Component

Backend / Subscriptions (db layer)

Priority

P1

Error

sqlalchemy.exc.InvalidRequestError: Select statement '<...Select object...>' returned no FROM clauses due to auto-correlation; specify correlate(<tables>) to control correlation manually.

Abbreviated stack:

routers/ops.py  ->  get_auth_report
database.py     ->  get_agent_subscription
db/subscriptions.py -> get_agent_subscription  (conn.execute(stmt))
sqlalchemy ... compiler.visit_select -> _setup_select_stack -> selectable._get_display_froms
  -> InvalidRequestError: returned no FROM clauses due to auto-correlation

Location

• File: src/backend/db/subscriptions.py
• Function: SubscriptionOperations.get_agent_subscription (+ helper _agent_count_subquery)

Root Cause

_agent_count_subquery() builds a scalar subquery whose only FROM is agent_ownership, correlated to the outer query via agent_ownership.c.subscription_id == subscription_credentials.c.id:

@staticmethod
def _agent_count_subquery():
    return (
        select(func.count())
        .select_from(agent_ownership)
        .where(and_(
            agent_ownership.c.subscription_id == subscription_credentials.c.id,
            agent_ownership.c.deleted_at.is_(None),
        ))
        .scalar_subquery()
        .label("agent_count")
    )

This is fine in callers whose outer FROM is subscription_credentials JOIN users (e.g. list_subscriptions, get_subscription): only subscription_credentials is auto-correlated, agent_ownership stays in the subquery FROM.

But get_agent_subscription also joins agent_ownership in its outer query (to filter by agent_name):

select(*self._subscription_select_columns(), self._agent_count_subquery())
.select_from(
    subscription_credentials
    .join(users, subscription_credentials.c.owner_id == users.c.id)
    .join(agent_ownership, agent_ownership.c.subscription_id == subscription_credentials.c.id)
)
.where(and_(agent_ownership.c.agent_name == <agent_name>, agent_ownership.c.deleted_at.is_(None)))

Now the enclosing query contains both subscription_credentials and agent_ownership, so SQLAlchemy auto-correlates both out of the subquery — removing agent_ownership, the subquery's only FROM — leaving "no FROM clauses" and raising at compile time.

Reproduction Steps

1. Deploy Trinity at a commit including the #1093 SQLAlchemy-Core db rewrite (v0.6.1).
2. Have at least one agent registered (with or without a subscription).
3. As an admin, call GET /api/ops/auth-report (equivalently, invoke db.get_agent_subscription(<agent_name>)).
4. Observe HTTP 500 with the InvalidRequestError: returned no FROM clauses due to auto-correlation.

(Reproduces on both SQLite and PostgreSQL backends.)

Suggested Fix

Alias agent_ownership inside the count subquery so its FROM table is always distinct from any agent_ownership in the enclosing query — auto-correlation then only removes subscription_credentials (the intended correlation):

from sqlalchemy import and_, func, select  # existing imports

@staticmethod
def _agent_count_subquery():
    ao = agent_ownership.alias("ao_count")
    return (
        select(func.count())
        .select_from(ao)
        .where(and_(
            ao.c.subscription_id == subscription_credentials.c.id,
            ao.c.deleted_at.is_(None),
        ))
        .scalar_subquery()
        .label("agent_count")
    )

Alternatively, pin correlation explicitly on the subquery with .correlate(subscription_credentials). The alias approach is the most robust because it makes the helper safe in every caller, including those that join agent_ownership.

Environment

• Trinity version: v0.6.1 (bd11ea96)
• Backend: FastAPI + SQLAlchemy Core (#1093)
• Reproduced on SQLite and PostgreSQL

Related

• src/backend/db/subscriptions.py (get_agent_subscription, _agent_count_subquery)
• src/backend/routers/ops.py (get_auth_report)
• Introduced by the #1093 SQLAlchemy-Core database rewrite.

[vybe]

Additional affected endpoint found via a full GET-surface sweep: GET /api/subscriptions/agents/{agent}/auth (routers/subscriptions.py) also calls get_agent_subscription and 500s with the identical auto-correlation error. So the single _agent_count_subquery fix resolves both /api/ops/auth-report and /api/subscriptions/agents/{agent}/auth.

Sweep context: across all 197 GET endpoints on v0.6.1, the only DB-layer 500s were these two (same root cause) plus one unrelated missing-facade-method bug (db.get_full_capabilities). Confirmed dialect-independent — reproduces on both SQLite and PostgreSQL.

[vybe]

/claim

[github-actions]

Claimed by @vybe. Label status-in-progress added.

Next steps:

1. Create branch: feature/1199-<short-slug>
2. Implement the feature
3. Open a PR with Fixes #1199