Gateway Structured Select

POST /gateway/fetch and POST /gateway/data now accept an additive structured-select body for PostgreSQL-backed Athena clients and direct x-pg-uri requests.

This does not replace the legacy columns + conditions contract. Both shapes remain valid.

Supported request shapes

Athena accepts two structured-select inputs:

1. Canonical nested select objects
2. Compatibility select strings

Canonical object payload

{
  "table_name": "orchestral_sections",
  "select": {
    "name": true,
    "instruments": {
      "select": {
        "name": true
      },
      "where": {
        "active": {
          "eq": true
        }
      },
      "orderBy": {
        "name": "asc"
      },
      "limit": 10
    }
  },
  "where": {
    "name": {
      "neq": "brass"
    }
  },
  "orderBy": {
    "name": "asc"
  },
  "limit": 100
}

Response shape is unchanged:

{
  "status": "success",
  "message": "Fetched 2 rows",
  "data": [
    {
      "name": "strings",
      "instruments": [
        {
          "name": "violin"
        }
      ]
    },
    {
      "name": "woodwinds",
      "instruments": [
        {
          "name": "flute"
        }
      ]
    }
  ],
  "cache_key": "..."
}

Compatibility select string

Use this for migration from PostgREST/Supabase-style callers:

{
  "table_name": "orchestral_sections",
  "select": "name,instruments(name)",
  "where": {
    "instruments.name": {
      "eq": "flute"
    }
  }
}

String select is compatibility sugar. The object form is the canonical Athena contract.

Cross-schema compatibility select string

{
  "table_name": "public.chat_subscriptions",
  "select": "user_id,users:athena.users(id,username,image)",
  "where_filters": [
    {
      "column": "user_id",
      "operator": "eq",
      "value": "ef7a4c74-cc35-4d32-945a-a5271279ecdb",
      "column_cast": "text"
    }
  ],
  "orderBy": [
    {
      "column": "user_id",
      "direction": "desc"
    }
  ],
  "limit": 1
}

This is the server-supported cross-schema relation grammar for Athena 2.4.0 compatibility work:

• schema-qualified base tables such as public.chat_subscriptions
• relation-side schema qualification such as users:athena.users(id,username,image)

Validation failures use the normalized gateway envelope:

{
  "status": "error",
  "message": "Invalid relation-select compatibility query",
  "error": "table 'athena.user' was not found for structured gateway fetch",
  "code": "VALIDATION_FAILED",
  "data": {
    "operation": "fetch"
  }
}

Query semantics

• Root and relation filters support eq, neq, gt, lt, and in.
• Relation-level where, orderBy, limit, and offset are supported.
• Canonical relation projections default to left-join semantics.
• Compatibility select strings support !inner when parent-row filtering is required.
• Nested collection !inner filters parent rows when the child collection is empty.
• Relation joins resolve from database foreign-key metadata first, then from a verified *_id fallback. If Athena cannot resolve the edge safely, provide foreign_key.
• When API-key enforcement is enabled, Athena checks read rights for every referenced table in the structured tree, not just the root table.
• schema_name still works for PostgreSQL-backed requests when table_name is not already schema-qualified.

Current scope

• Structured select is supported on PostgreSQL-backed gateway fetch execution.
• Legacy columns payloads remain the right choice for wildcard fetches.
• Nested wildcard projection (* inside structured relation/object select) is not supported.
• Bare where / orderBy keys do not opt into structured select. Athena only treats the body as structured when select is present.
• Non-PostgreSQL gateway fetch backends keep the legacy columns/conditions contract.
• First-class direct AST request bodies (operation / from / fields) are not part of the current public /gateway/fetch contract.
• Relation on shapes and schema-qualified relation selectors combined with via are not supported in this release and return 400 VALIDATION_FAILED.

See Gateway API for the full route overview and Direct PostgreSQL Routing (x-pg-uri) for auth/routing behavior.