RFC: Expose cubes and views in multiple PostgreSQL schemas

## Problem

The SQL API currently presents every public cube and view as a table in one flat `public` schema. That is manageable for a small model. In a large semantic layer, PostgreSQL clients show one long table list with no useful grouping for users to browse.

We would like to expose areas such as `sales`, `finance`, and `ops` as PostgreSQL schemas. Shared models, such as a date dimension, should be reusable across those areas. This would give SQL and BI users a navigable namespace without duplicating semantic models or changing how they are queried or secured.

## Why existing features do not cover this

Views curate semantic members. The `public` flag controls whether a model is exposed. Multitenancy can select a different model repository.

Those features decide what a user can see, but they do not create several PostgreSQL schemas within one SQL API connection.

This proposal adds that missing projection. A cube or view keeps one semantic identity while appearing in one or more SQL namespaces.

## Proposed model contract

A cube or view may declare an explicit array of SQL schemas.

JavaScript:

```js
cube('orders', {
sqlSchemas: ['sales', 'finance'],
// ...
});
```

YAML:

```yaml
cubes:
- name: orders
sql_schemas:
- sales
- finance
```

The array is explicit because the same model may belong to several departments or product definitions. One `date` model, for example, can be queried as both `sales.date` and `finance.date`.

These names are projections of the same model, not copies.

When `sql_schemas` is absent, the model keeps its existing `public.` name. This preserves the current behavior for every existing project.

An explicit array replaces that default. To keep a model in `public` as well as another schema, authors include `public` themselves:

```yaml
sql_schemas:
- public
- sales
```

The array must be non-empty and contain unique, valid schema names. Reserved PostgreSQL namespaces are rejected.

The existing `public` setting still applies. A model with `public: false` remains hidden everywhere, even if it declares `sql_schemas`.

Each projection resolves to the same cube or view, so joins, member visibility, access policies, and query rewriting remain attached to that model.

Schema exposure is not transitive. Exposing a view in `sales` does not also expose its contributing cubes there.

An extending model inherits `sql_schemas` if it does not declare the property. If it provides its own array, that array replaces the inherited value rather than merging with it.

## PostgreSQL resolution and discovery

CubeSQL resolves qualified names by both schema and relation name. Bare names still use `public`; custom schemas are not added silently to the search path.

The same cube can therefore resolve through several qualified names. Those projections also remain distinct when aliased together in one query, including a cross-schema self-join.

The PostgreSQL and Redshift-compatible catalogs advertise the projected schemas, relations, columns, descriptions, types, privileges, and statistics.

Each projection has its own catalog identity and non-colliding OIDs. Explicit projections are sorted before OIDs are assigned, so equivalent metadata produces stable results.

## Backward compatibility

The new metadata field is optional. Metadata produced by an older Cube version does not include it, so CubeSQL continues to expose those models in `public`.

The typed `public` field is optional for the same reason. An absent value is treated as visible, while an explicit `false` remains hidden.

Existing projects need no changes. A model moves away from its implicit `public` name only when its author adds `sql_schemas`.

## Reference implementation

The reference branch adds the property to cube and view validation, schema compilation, `/v1/meta`, the OpenAPI contract, and the generated Rust client.

CubeSQL builds a `CatalogProjection` for each visible `(schema, relation)` pair. Resolution and catalog providers use this shared representation rather than rebuilding placement rules independently.

The branch also updates schema privileges and search-path visibility so discovery tools receive consistent answers for dynamic schemas.

Public documentation is included in the cube and view references, the SQL API query-format guide, and the `cubejs-testing` development guide.

## Status

There is now a working reference implementation on [`marciodfg/cube:feat/sql-api-schemas`](https://github.com/marciodfg/cube/tree/feat/sql-api-schemas).

The branch is based on `cube-js/cube` `master` at `28262851b`. Its current head is [`efa326689`](https://github.com/marciodfg/cube/commit/efa326689).

It includes the model contract, metadata plumbing, CubeSQL resolution, catalog discovery, documentation, and end-to-end tests. The latest full smoke run passed all 62 tests and 41 snapshots.

## Validation and edge cases

The branch passes the full CubeSQL Rust suite, Cube client compatibility tests, schema-compiler tests, TypeScript checks, linting, formatting, and the complete `smoke:cubesql` suite.

The smoke suite starts a real Cube project and queries CubeSQL through both `node-postgres` and a Dockerized PostgreSQL 16 `psql` client.

It covers multiple projections of cubes and views, explicit and implicit `public`, hidden models, invalid schema lookups, catalog discovery, OIDs, descriptions, privileges, and prepared execution.

It also covers non-transitive views, joins between cubes in different schemas, and a self-join between two schema projections of the same cube.

A normal `/v1/meta` request verifies that `public` and `sqlSchemas` reach the transport contract. A standalone HTTP transport test verifies that CubeSQL filters non-public models before building catalog projections.

RBAC smoke tests compare row and member policy results through the `public`, `sales`, and `finance` projections. A second session verifies that unauthorized projections neither resolve nor appear in the catalog.

## Scope and non-goals

This proposal is only about PostgreSQL schemas. It does not add virtual databases, cross-database queries, database-specific authorization, or database-scoped catalogs.

CubeSQL currently accepts a startup database name but does not provide meaningful database isolation. Virtual databases need their own session, resolution, catalog, authorization, and compatibility design.

REST and GraphQL query behavior does not change. The REST metadata response gains the optional `sqlSchemas` field used by CubeSQL.

Schemas are not inferred from folders or views. Placement is explicit because a model may appear in several schemas and view exposure must remain non-transitive.

Pre-aggregations and upstream data-source selection are unchanged.

## Prior art

- [#1483](https://github.com/cube-js/cube/issues/1483) requested multiple schemas and noted that clients could not discover them through the SQL API.
- [#2145](https://github.com/cube-js/cube/issues/2145) led to public visibility controls, which solve exposure but not namespace organization.
- [#10934](https://github.com/cube-js/cube/pull/10934) and [#10935](https://github.com/cube-js/cube/pull/10935) expose the SQL connection database name to request context.

The database-name work may help a future virtual-database proposal, but this schema change does not depend on it.

## Feedback and next steps

The main questions are whether `sql_schemas` is the right name, whether an explicit array should replace the `public` default, and whether the catalog and OID approach fits CubeSQL.

Feedback on the contract, implementation direction, and phase-one scope would be especially helpful.

If the direction looks right, the working branch is ready to open as a draft PR. Virtual database projections can remain a separate discussion.

Review the reference branch and its changes to cube and view validation, schema compilation, /v1/meta, the OpenAPI contract, and the generated Rust client. Then inspect CubeSQL's CatalogProjection, resolution, catalog providers, and the listed end-to-end tests. Done means the contract, schema projections, discovery, permissions, compatibility behavior, and documentation agree with the proposal.

Written by the indexing model from the issue text.