lywsvip/supabase
1
0
Fork 0
mirror of https://github.com/supabase/supabase.git synced 2026-09-07 18:38:50 +08:00
Code Issues Packages Projects Releases Wiki Activity
38,336 Commits 1,677 Branches 60 Tags
create-pull-request/patch
Commit Graph

13 Commits

Author SHA1 Message Date
Charis
ec64135f9d perf(pg-meta): scope column privileges query to a single table (#48553) 
## I have read the
[CONTRIBUTING.md](https://github.com/supabase/supabase/blob/master/CONTRIBUTING.md)
file.

YES

## What kind of change does this PR introduce?

Performance improvement

## What is the current behavior?

The column privileges page in Studio only ever renders one table, but
the underlying query still `aclexplode`s every column in the whole
schema and filters the result client-side.

## What is the new behavior?

Adds a scoped SQL path that prunes `pg_class`/`pg_namespace` to the
requested schema+table before exploding ACLs, gated behind the
`pgMetaScopedIntrospection` flag, with a plan-guard test asserting
`pg_class`/`pg_attribute` stay index-driven. Studio's query hook and
cache keys now thread the selected table through so column-privilege
invalidation and cold-load races are scoped correctly, and the page
fetches per-table instead of per-schema.

## Additional context

<!-- This is an auto-generated comment: release notes by coderabbit.ai
-->

## Summary by CodeRabbit

* **Improvements**
* Column privileges are now scoped to the selected schema and table for
more accurate results.
* Changing schemas automatically updates the table selection and
refreshes the displayed privileges.
* Privilege updates now refresh only the relevant schema, table, and
column data.
* Loading states are handled more accurately when no table is selected.

* **Bug Fixes**
* Improved consistency between scoped and unscoped column privilege
results, including table-, column-, and grant-option privileges.

<!-- end of auto-generated comment: release notes by coderabbit.ai -->
 2026-07-31 15:26:37 -04:00
Andrew Valleteau
6c6a721cb7 fix(pg-meta): scope remaining O(catalog) introspection queries behind pgMetaScopedIntrospection (#48148) 
## I have read the
[CONTRIBUTING.md](https://github.com/supabase/supabase/blob/master/CONTRIBUTING.md)
file.

YES

## What kind of change does this PR introduce?

Bug fix (performance), follow-up to #47894, plus regression-guard tests.

## What is the current behavior?

#47894 scoped the Table Editor and entity-definition introspection
queries, but four more `@supabase/pg-meta` query families still do
O(catalog) work per request. On a production project with a very large
catalog (hundreds of schemas, ~465K `pg_constraint` rows) they run 5 to
55 seconds each, trip the 58s `statement_timeout`, and spill sorts to
temp files. During a recent "DB CPU > 85%" incident on such a project,
24 of 27 active backends were running these queries concurrently.

1. **`tables.retrieve()` (single-table lookup by name+schema or id)**:
the `tables`/`columns` CTEs scan the whole catalog (`pg_class`,
`pg_constraint`, `pg_index`, all of `pg_attribute`, per-table sizes) and
the one-table predicate is applied only on the outer select. Same bug
class #47894 fixed for the OID-based table editor query; this sibling
path never got the treatment. It accounted for 94 of the 96
statement-timeout cancellations in the incident.
2. **Types listing**: the `t_enums` and `t_attributes` subqueries
aggregate the entire `pg_enum` and every composite relation before the
wrapper's schema filter applies.
3. **Table privileges**: `aclexplode` + double `pg_roles` join + GROUP
BY over every relation in the database; schema/OID filters applied only
after aggregation, in both `list()` and `retrieve()`.
4. **Row counts**: `getTableRowsCountSql` treats `reltuples = -1`
(never-analyzed table) as "small table, run exact count(*)". A freshly
bulk-loaded multi-million-row table times out on every Table Editor
pagination render.

Two Studio-side amplifiers turned one slow query into a sustained load
storm:

- `useTableQuery` (behind `tables.retrieve()`) mounts once per visible
foreign-key grid cell via `ForeignKeyFormatter`, so a single Table
Editor view fires ~20 concurrent copies against the FK target table. A
timed-out query caches nothing, and TanStack retries errored no-data
queries on every observer mount by default, so scrolling kept re-issuing
the 58s scan.
- `useTableApiAccessQuery` fetched table privileges for the entire
database and filtered down to one schema client-side.

## What is the new behavior?

**pg-meta (all behind the existing `pgMetaScopedIntrospection` flag,
same rollout mechanism as #47894; `scoped: false` keeps serving the
current SQL):**

- `tables.retrieve()`: the identifier is resolved to a scalar
`targetOid` init-plan and pushed into the base scan, primary-key,
relationships (both FK directions kept: `conrelid` or `confrelid`) and
columns CTEs. A materialized `target` CTE was deliberately avoided: it
acts as an optimization barrier and forces the very seq scans being
removed.
- Types: filter `pg_type`/`pg_namespace` first, then compute
enums/attributes per surviving row via correlated index-scan subqueries
(`pg_enum(enumtypid, enumsortorder)`, `pg_attribute(attrelid, attnum)`).
- Table privileges: schema/OID predicates injected into the base WHERE
before `aclexplode`/GROUP BY for `list()` and `retrieve()`.
- Row counts: `reltuples = -1` is treated as "unknown" and gated on
physical size via `pg_relation_size` (a cheap stat call; `relpages` is
equally stale pre-vacuum). At or below `THRESHOLD_ESTIMATE_BYTES`
(~10MB, derived from `THRESHOLD_COUNT` at a conservative ~200 bytes/row)
the exact count runs as before: fast by construction, and it avoids
bogus estimates since Postgres floors never-vacuumed heaps at 10 pages,
so an empty table would otherwise report ~2K estimated rows. Above the
gate the count routes through the EXPLAIN-based
`pg_temp.count_estimate`, or returns `-1`/`is_estimate = true` in
read-only contexts where the temp function cannot be created. The scoped
branch embeds the estimated select via `literal()` instead of legacy's
apostrophe-only escaping, so it stays correct under
`standard_conforming_strings = off`. `enforceExactCount` unchanged.

**Studio:**

- The flag decision is contained in the data layer instead of
prop-drilled: a small imperative accessor
(`apps/studio/data/scoped-introspection.ts`) is hydrated from `useFlag`
via a one-line `useSyncScopedIntrospection()` call in `DefaultLayout`,
and the query functions read it internally when building the pg-meta
SQL. `DefaultLayout` is shared by both the Next and TanStack router
trees; hydrating from `_app.tsx` alone would leave TanStack-served pages
permanently unscoped since `routes/__root.tsx` mounts its own flag
provider. Cold loads cannot race the flag: the query functions await a
readiness promise that resolves only after the sync hook has hydrated
the accessor with a loaded flag store (immediately on self-hosted where
flags are disabled; a 5s safety net armed lazily on the first `ready()`
call - not at module import, which would let the timer expire before a
project page ever mounts - bounds genuine ConfigCat outages). No
component threading, no query-key changes (remaining tradeoff,
documented in the module: a mid-session flag flip can serve stale-keyed
caches until refetch, fine for a session-stable rollout flag). #47894's
existing threading is left as-is and gets deleted together with the flag
in the cleanup PR. Also fixes the previously-missing `scoped`
pass-through in `getTableRowsCount`.
- Flag-independent hardening: `useTableQuery` now sets `retryOnMount:
false`, `refetchOnWindowFocus: false` and `staleTime: 5min`. Errored
(timed-out) queries no longer refire on every grid cell remount, while
stale successful metadata still revalidates on mount after `staleTime`.
- `useTableApiAccessQuery` now passes `includedSchemas: [schemaName]`;
the client-side filter stays as a safety net.
- The rows-count query is `enabled`-gated on the permission check
settling, so a transiently-false `canSQLAdminWrite` can no longer cache
a read-only `-1` count for a writable user (read replicas short-circuit
synchronously as before).

**Regression guards (extending the #47894 infrastructure):**

- Execution-based scoped-vs-legacy equivalence tests for all four
queries: both variants run against the test database and are compared
with raw `toEqual` - no normalization, ids included (types across 6
option combos, privileges incl. multi-grantee + PUBLIC,
`tables.retrieve` for both identifier branches, row counts for every
case where the two paths must agree). Two documented exceptions where
only the LEGACY side is sorted, because a de-normalized diagnostic run
proved legacy emits genuinely plan-dependent order there (an
adversarial-FK fixture shows it is neither oid, name, nor creation
order): the `types.list` outer row order (scoped adds `order by t.oid`;
legacy has no ORDER BY) and the `tables.retrieve` relationships array
(scoped orders by `constraint_name` + column-name tie-breakers - a
composite two-column FK expands to 4 entries sharing one
constraint_name). Everything else (privileges via `aclexplode` over the
same relacl, columns by `ordinal_position`, primary keys by `indkey`
order, enums by `enumsortorder`) is byte-identical between the two paths
with no test-side help. The one intentional value divergence,
never-analyzed tables above the size gate where legacy's exact count is
the timeout bug itself, is asserted explicitly as a divergence.
- Plan-guard budgets for every scoped query against the stress catalog
(extended with 200 enums + 200 composite types). Residual seq scans are
justified in-budget: `pg_constraint` max 2 (no index on `confrelid`),
`pg_attrdef` max 1, `pg_authid` max 2 (scales with role count, not
schema count).
- Legacy templates carry a FROZEN do-not-edit marker (they must keep
matching production behavior until the flag cleanup deletes them); the
ordinary test suite runs against the legacy default, so behavioral drift
there fails regular tests.

### Validation

- pg-meta: typecheck clean; the affected suites (types,
table-privileges, tables, rows-count, catalog-plan-guard) pass in full.
- Cross-version: the scoped-vs-legacy equivalence and rows-count
behavioral suites were validated on PostgreSQL 14, 15, and 17 (identical
results on all three). Two version-marginal planner choices surfaced on
17 (`pg_type` / `pg_class` seq scan vs full-index bitmap for per-schema
listings, both structurally unavoidable without an index leading on the
namespace column) and are carried as justified plan-guard budget
entries. A full 468-test suite run sequentially: 452 passed, 16 failures
verified environmental (13 timeouts in an untouched file that passes
27/27 in isolation on the marathon-run cluster, 3 cluster-global role
collisions from container reuse).
- Studio: `pnpm --filter studio typecheck` clean; 39/39 tests across the
touched data hooks; eslint clean on touched files.

### Rollout

Same staged ConfigCat rollout as #47894 via `pgMetaScopedIntrospection`
(user-email targeting first, then percentage, then 100%). The
`useTableQuery` hardening and the API-access schema scoping ship
unflagged (behavior-safe). Gate before percentage rollout: functionally
verify the FK popover/selector UX under the new
`staleTime`/`retryOnMount` settings (a just-edited FK target must not
look stale anywhere Studio does not already refetch on save). Once fully
rolled out, the legacy templates and flag get deleted together with
#47894's in one cleanup PR.

🤖 Generated with [Claude Code](https://claude.com/claude-code)

---------

Co-authored-by: Claude Fable 5 <noreply@anthropic.com>
 2026-07-24 08:07:08 +02:00
Andrew Valleteau
768ea1001b fix(studio): scope table editor introspection CTEs to target table OID (#47894) 
## I have read the
[CONTRIBUTING.md](https://github.com/supabase/supabase/blob/master/CONTRIBUTING.md)
file.

YES

## What kind of change does this PR introduce?

Bug fix (performance), plus a regression-guard test suite and docs.

## What is the current behavior?

Studio's introspection queries in `@supabase/pg-meta` do `O(catalog)`
work for per-table requests. On databases with very large catalogs
(hundreds of thousands of relations/constraints — real deployments reach
this) they take tens of seconds per dashboard interaction, trip
`statement_timeout`, and create heavy CPU/memory pressure when several
tabs open concurrently. Two instances of the same bug class:

**1. Table Editor query (`getTableEditorSql`)** — fetches metadata for
ONE table by OID, but five catalog scans are unscoped and only filtered
at the top-level join:

- `primary_keys` CTE — scans all of `pg_index` (`where i.indisprimary`)
- `index_cols` CTE — scans all unique indexes
- `relationships` CTE — scans every FK in `pg_constraint` (and is
scanned twice by the two subplans)
- `uniques` subquery (inside `columns`) — scans all single-column unique
constraints
- `check_constraints` subquery (inside `columns`) — scans all
single-column check constraints

The planner cannot push the outer join qual into grouped / `distinct on`
subqueries, so each is computed over the full catalog and thrown away.
`tables-paginated.ts` was previously rewritten to avoid exactly this
pattern; the single-table query never got the same treatment.

**2. Entity definitions (`getTableDefinitionSql` /
`getEntityDefinitionsSql`)** — the vendored `pg_get_tabledef` plpgsql
function scans the entire `information_schema.columns` view once **per
column** (plus `information_schema.tables` once per call) just to decide
whether a name needs double-quoting — a pure string property of a name
it already holds — and its per-index partial-index lookup casts
`relnamespace::regnamespace::text` across every `pg_class` row. On a
12K-table catalog this makes a single entity's DDL cost ~3.7s and a
default 100-entity definitions page ~6 minutes.

## What is the new behavior?

**Fix 1 — scope the Table Editor CTEs to the requested OID** (`id` is
validated non-null and interpolated via `literal()`, same as the
existing `base_table_info` filter):

- `primary_keys` / `index_cols`: `and i.indrelid = <id>`
- `relationships`: `and (c.conrelid = <id> or c.confrelid = <id>)`
- `uniques` / `check_constraints`: `and conrelid = <id>`

Semantics are unchanged: the top-level select already filtered every CTE
to the target table, so rows for other tables were computed and
discarded. The `pg_index`/`pg_constraint` lookups become index scans
returning a handful of rows. One residual scan is structural: PostgreSQL
has no index on `pg_constraint.confrelid`, so the incoming-FK half of
`relationships` is a single filtered seq scan of `pg_constraint` — still
one cheap pass instead of materializing every FK row twice.

**Fix 2 — remove the O(catalog) scans inside `pg_get_tabledef`**: the
information_schema uppercase checks are replaced with direct regex tests
on the name in hand (preserving the original's `quote_ident` behavior
for schemas that need quoting), and the partial-index lookup is scoped
by the already-resolved table OID. Original statements are kept as
comments, matching the vendored file's convention.

**Regression guard** — so this bug class stays out:

- `test/db/stress-catalog.ts` builds a synthetic catalog (default 2,000
tables with PKs, unique + check constraints, FK chains and an FK hub;
`PG_META_STRESS_TABLES` scales it to incident size).
- `test/db/plan-guard.ts` provides `EXPLAIN (ANALYZE, FORMAT
JSON)`-based budget assertions: a query's plan may only seq-scan a
scaling catalog if its budget entry carries a written structural
justification (e.g. no index on `pg_constraint.confrelid`; no index on
`pg_class.relnamespace` for per-schema listings), plus a per-query time
bound (the only guard available for opaque plpgsql internals like
`pg_get_tabledef`).
- `test/sql/studio/catalog-plan-guard.test.ts` applies budgets to the
hot-path studio queries: table editor, constraints, FK listing, entity
types, tables-paginated, columns, indexes, table/entity definitions,
views. Reverting either fix makes the suite fail immediately with the
offending scans listed.
- `test/sql/studio/table-editor.test.ts` (new — none existed) asserts
the Table Editor query's semantics: primary keys, unique indexes, both
FK directions, `is_unique`, check definitions, column comments.
- A new package `README.md` documents the plan-guard budget entry as a
requirement for any new introspection query.

### Validation (synthetic 12,000-table catalog, PostgreSQL 17.6)

- **Output equivalence, fix 1:** for 12 relation types (regular,
composite PK, partitioned parent + partition, view, materialized view,
constraint-free table, FK hub/chain/tail, and a fixture with
enums/domains/generated/identity columns and duplicate check
constraints), the `entity` jsonb from the old and new query is
byte-identical.
- **Output equivalence, fix 2:** byte-identical DDL across 13 fixture
combinations (serial/identity/generated/array columns, case-sensitive
and keyword names, mixed-case schemas, partitions, unlogged +
reloptions, partial/expression indexes, external PK/FK/comments/trigger
variants).
- **Performance, fix 1:** Table Editor query `EXPLAIN ANALYZE` ~1,630ms
→ ~30ms (~50×); the gap grows with catalog size since the old query is
O(catalog) per call.
- **Performance, fix 2:** single entity definition 3,672ms → 63ms; a
100-entity definitions page ~6min → 0.87s. The plan-guard bound for
`getEntityDefinitionsSql` tightens accordingly from 15s/25 entities to
3s/100 entities (330ms measured at default test scale).

Verified locally: `catalog-plan-guard` (12 tests), `table-editor`,
`tables-paginated` (16 tests) pass; `typecheck` clean.

### Rollout

Per review, the new behavior ships **dark** behind the
`pgMetaScopedIntrospection` ConfigCat flag (default off = legacy SQL,
kept as full duplicated templates in pg-meta and verified byte-identical
to the pre-PR queries). Studio reads the flag in the query hooks and
threads it through (flag state is part of the React Query keys). The
rollout is staged in the ConfigCat dashboard via user-email targeting
(like every other ConfigCat flag): target the reporting user's email
first, then a percentage rollout, then 100%. Server-side AI callers of
`getEntityDefinitionsSql` stay on the legacy path. Once fully rolled
out, delete the legacy templates + flag in a cleanup PR.

🤖 Generated with [Claude Code](https://claude.com/claude-code)

Closes: PGMETA-122

<!-- This is an auto-generated comment: release notes by coderabbit.ai
-->
## Summary by CodeRabbit

- **Bug Fixes**
- Improved table editor SQL to correctly scope primary keys, indexes,
uniques, checks, and relationships to the selected table.
- Optimized table definition SQL to reduce unnecessary catalog scanning
for uppercase-name detection and partial-index detection.

- **Tests**
- Added SQL generator tests for table editor metadata (keys, indexes,
relationships, comments, and constraints).
- Added catalog query plan guard coverage with a stress catalog and
EXPLAIN-based scoping/performance budgets.

- **Documentation**
- Expanded documentation on catalog query plan safeguards and how to
keep new introspection queries properly scoped.
<!-- end of auto-generated comment: release notes by coderabbit.ai -->

---------

Co-authored-by: Claude Fable 5 <noreply@anthropic.com>
 2026-07-14 13:16:11 +02:00
Alaister Young
9af6e65df4 fix(studio): DOM-nesting hydration errors, ghost deleted-snippet nav, and migrations query 400s (#47667) 
App-level fixes that reproduce on BOTH the Next and TanStack builds —
split out of #47657 (which stays TanStack-only) for reviewability. All
were found by a full-site click-through of the dashboard.

## Invalid HTML nesting (React 19 "will cause a hydration error" console
errors)

- **FormLayout description rendered in a `<p>`**
(`packages/ui-patterns`): consumers pass arbitrary JSX (the RowEditor's
`created_at` timezone note passes a `<div>` with `<p>`s) →
`<p>`-in-`<p>` / `<div>`-in-`<p>`. Container is now a `<div>` with
identical classes (Tailwind preflight makes them render the same).
- **Switch toggles nested inside Tooltip trigger buttons**
(button-in-button) in ColumnEditor ("Allow Nullable" + "Is Unique"),
ExtensionRow, and PublicationsTableItem → repo-standard `TooltipTrigger
asChild` + `<div>` wrapper.
- **Saved log queries rendered a `<div>` directly inside `<tbody>`**
(`/logs/explorer/saved`) → rows are now proper `<tr><td colSpan>`
wrappers; the component itself is untouched (it's valid in its sidebar
usage).
- **Nested anchors in observability metric cards**: a card-level
`<Link>` wrapped MetricCard's "More information" `<Link>` (identical
URLs) → the chevron affordance renders as a `<span>` when no `href` is
passed; clicks bubble to the card link, tooltips preserved.
Design-system standalone usage unaffected.
- **`objectFit="cover"` passed to modern `next/image`** on the featured
integration card (unknown-prop warning) — the className already had
`object-cover`; prop dropped.

## Ghost dead-snippet after deletion

Deleting the active SQL snippet left its id in `useDashboardHistory`
(`history.sql`), so the "SQL Editor" nav item navigated to
`/sql/<deleted-id>` — content fetch 404s, no editor pane renders, and a
phantom tab reappears. Fixed both ends: delete flows now purge dashboard
history (and the tabs store clears a stale `previewTabId`), and
`/sql/[id]` treats a snippet 404 as "clean up + `router.replace` to
`/sql/new` + toast" instead of rendering the dead state. Unit tests for
the store/history cleanup.

## `pg-meta` migrations query 400s on every project load

`ActivityStats` on project home runs the migrations list query, whose
SQL was a bare `select * from supabase_migrations.schema_migrations` —
that table only exists once a migration has run, so every other project
logged a failed `?key=migrations` request on every load (visible in
production consoles too). The SQL is now guarded with `to_regclass` +
`query_to_xml` (same pattern as the advisor lints' `storage.buckets`
guard), returning zero rows instead of erroring; legacy version-only
tables still work. Tested against real dockerized Postgres (absent
table, populated ordering, special chars, legacy schema) + MSW hook
tests.

Found and verified via /test-supabase-local (browser click-through +
console audit on both builds).

## To test

Console must stay free of React DOM-nesting errors ("cannot be a
descendant of" / "cannot contain a nested") on each surface:

1. Table editor → Insert row panel (`created_at` field renders its
timezone note) and Edit column panel ("Allow Nullable"/"Is Unique"
tooltips still hover).
2. `/database/extensions` and `/database/publications` → toggle switches
render, tooltips hover.
3. `/logs/explorer/saved` (with ≥1 saved query) → rows render full-width
inside the table, hover shows Actions.
4. `/observability` → no nested-anchor error on load; card body click
and the chevron both navigate; label help-icons still show tooltips.
5. `/integrations` → no `objectFit` unknown-prop warning; featured card
images still cover.
6. **Ghost snippet**: open a SQL snippet → delete it via the sidebar →
click the "SQL Editor" nav item → lands on `/sql/new` (no phantom tab,
no 404 content fetch). Direct-load `/sql/<random-uuid>` → toast +
redirect to `/sql/new`.
7. **Migrations 400**: load project home with a project that has never
run a migration → the `pg-meta/<ref>/query?key=migrations` request
returns **200** with `[]` (previously a 400 on every load). Database →
Migrations still lists real migrations when they exist.


<!-- This is an auto-generated comment: release notes by coderabbit.ai
-->
## Summary by CodeRabbit

## Summary by CodeRabbit

* **Bug Fixes**
* Deleted SQL snippets are fully removed from dashboard history and
stale editor/tab state; users are redirected with a toast.
  * Closing preview tabs no longer leaves stale references.
* Improved toggle/tooltip/dialog interactions to avoid broken UI,
including metric headers showing tooltips even without direct links.
* Migrations display safely when migration tables/relations are missing.

* **UI Improvements**
* Refreshed layout for saved queries, form descriptions, and integration
imagery.

* **Tests**
* Added coverage for snippet history cleanup, tab removal, migrations
SQL behavior, and query edge cases.
<!-- end of auto-generated comment: release notes by coderabbit.ai -->


---

### Review feedback: `query_to_xml` breaks on Multigres (Ivan)

The defensive migrations query (added here to stop the `?key=migrations`
400 when the table doesn't exist yet) originally guarded with
`query_to_xml`, which is forbidden through Multigres's pooler (MUL-736 /
PSQL-1318). Rewritten without `query_to_xml`/`xmltable` using the
splinter#170 pattern: a PL/pgSQL `do` block guarded by `to_regclass`
(PL/pgSQL defers planning, so a missing table never errors) stashes the
rows into a transaction-local GUC via `set_config`, and a trailing
`select` reads them back with `jsonb_array_elements`. Verified that
postgres-meta sends the whole SQL as one simple-query string → single
implicit transaction → the local GUC survives to the `select` and
doesn't leak into the pooled connection. 6/6 dockerized-Postgres tests
(absent table → `[]`, populated/ordered/special-chars, legacy
version-only table, full pg-meta-shaped multi-statement string, GUC
non-leakage).

Note (out of scope, pre-existing):
`packages/pg-meta/src/sql/studio/advisor/lints.ts` still uses
`query_to_xml` — a separate pre-existing Multigres risk that should get
its own splinter-pattern sync.

---------

Co-authored-by: Alaister Young <10985857+alaister@users.noreply.github.com>
Co-authored-by: Joshen Lim <joshenlimek@gmail.com>
Co-authored-by: Saxon Fletcher <saxonafletcher@gmail.com>
 2026-07-08 12:32:11 +08:00
oniani1
99db104cf5 fix(pg-meta): escape unencrypted FDW server options via format() %L (#47014) 
Closes #47012

## What kind of change does this PR introduce?

Bug fix.

## What is the current behavior?

Unencrypted FDW server option values are pre-escaped with
`literal(value).replace(/'/g, "''")` and embedded inside the outer
`create server` `E'...'` string built by `format()`. That nests the
value inside two `E'...'` literals, so backslashes are decoded twice. A
value like `domain\user` aborts wrapper creation with `invalid Unicode
escape`, and `p@ss\w0rd` is silently stored as `p@ssw0rd`.

## What is the new behavior?

Unencrypted option values are passed as `format()` `%L` arguments, the
same way the encrypted options already supply their secret id, so
Postgres escapes each value exactly once.

Before and after, on postgres:16:

| Value | Before | After |
| --- | --- | --- |
| `domain\user` | aborts (invalid Unicode escape) | stored `domain\user`
|
| `p@ss\w0rd` | stored `p@ssw0rd` | stored `p@ss\w0rd` |
| `a\b` | stored `a`+backspace | stored `a\b` |

## Additional context

Added a unit test in `packages/pg-meta/test/sql/studio/fdw.test.ts`
asserting unencrypted options use a `%L` placeholder with the value as a
`format()` argument, and that the old double-escaped form is gone. The
encrypted-option path is unchanged.


<!-- This is an auto-generated comment: release notes by coderabbit.ai
-->

## Summary by CodeRabbit

* **Bug Fixes**
* Improved handling of special characters in foreign data wrapper server
option values.

* **Tests**
* Added test coverage for foreign data wrapper configuration, including
edge cases with special characters.

<!-- end of auto-generated comment: release notes by coderabbit.ai -->
 2026-06-22 15:45:56 +08:00
Vaibhav
c9b8768940 fix: cmdk search (#47035) 
- closes https://github.com/supabase/supabase/issues/47033

<!-- This is an auto-generated comment: release notes by coderabbit.ai
-->

## Summary by CodeRabbit

* **New Features**
* Table search now matches fully-qualified table names (e.g.,
`schema.table`) when no specific schema is selected, enabling searches
to work seamlessly across all schemas.

* **Tests**
* Added comprehensive test coverage validating schema-qualified table
name matching in search filters.

<!-- end of auto-generated comment: release notes by coderabbit.ai -->
 2026-06-17 06:50:45 -06:00
Charis
a4334a2cc7 feat(studio): paginate Schema Designer via useInfiniteTablesQuery (#46402) 
## I have read the
[CONTRIBUTING.md](https://github.com/supabase/supabase/blob/master/CONTRIBUTING.md)
file.

YES

## What kind of change does this PR introduce?

Performance improvement / feature

## What is the current behavior?

The Schema Designer fetches all tables in a single request via
`useTablesQuery`. For schemas with 400+ tables this blocks first paint
on a large payload.

## What is the new behavior?

`SchemaGraph` uses `useInfiniteTablesQuery` (pageSize: 100) so the first
100 tables paint immediately. A "Load more tables" button appears above
the legend whenever more pages remain, letting users load the rest on
demand.

## Additional context

<!-- This is an auto-generated comment: release notes by coderabbit.ai
-->
## Summary by CodeRabbit

* **New Features**
* Added a "Find table…" selector and keyboard shortcut to quickly locate
and focus tables; supports incremental loading and debounced name search
(with literal wildcard handling).
* Schema Graph shows a bottom "Load more tables" control with loading
state and preserves view after loading more.

* **Refactor**
* Table listing switched to infinite/paginated retrieval and improved
"no tables" logic; server-side name filtering supported.

* **Tests**
* E2E tests add a schema-visualizer wait helper and update flows to
support the paginated visualizer.

<!-- review_stack_entry_start -->

[![Review Change
Stack](https://storage.googleapis.com/coderabbit_public_assets/review-stack-in-coderabbit-ui.svg)](https://app.coderabbit.ai/change-stack/supabase/supabase/pull/46402?utm_source=github_walkthrough&utm_medium=github&utm_campaign=change_stack)

<!-- review_stack_entry_end -->
<!-- end of auto-generated comment: release notes by coderabbit.ai -->
 2026-05-29 15:12:22 -04:00
Charis
426b0183af feat(studio): add useInfiniteTablesQuery hook for paginated tables (#46285) 
## I have read the
[CONTRIBUTING.md](https://github.com/supabase/supabase/blob/master/CONTRIBUTING.md)
file.

YES

## What kind of change does this PR introduce?

Feature — adds a new data-layer hook. No callers are switched over in
this PR.

## What is the current behavior?

The Schema Designer and Database > Tables list both call
`useTablesQuery({ includeColumns: true })`, which fetches the full
schema (every table, with every column, every relationship) in one
round-trip. For customers with many tables this never loads. Towards
FE-3428.

## What is the new behavior?

Introduces `useInfiniteTablesQuery` — a cursor-paginated tables hook
ready to be consumed by the two pages above in follow-up PRs.

- `pg-meta`: new `getTablesPaginatedSql` SQL builder that picks a page
of table OIDs first (cheap `pg_class` index scan) and constrains every
enrichment CTE (primary keys, relationships, columns) to that set.
Pagination is by `c.oid > $afterOid` rather than `OFFSET`, so deep pages
stay O(limit). Relationships use a `UNION ALL` keyed by `table_id` so
the downstream join is a plain equi-join.
- `pg-meta`: `COLUMNS_SQL` is now produced by `getColumnsSql({ filter
})`, letting a paginating caller push a table-OID predicate into the
WHERE clause directly. The bare `COLUMNS_SQL` export is preserved for
the 5 existing callers (`pg-meta-columns`, `pg-meta-tables`,
`pg-meta-views`, `pg-meta-materialized-views`,
`pg-meta-foreign-tables`).
- `studio`: `useInfiniteTablesQuery` wires the new SQL into
`useInfiniteQuery` via `executeSql`. `initialPageParam: 0`,
`getNextPageParam` returns the last row's `id` or `undefined` on a short
last page.
- Tests: 12 new tests in `pg-meta` covering cursor invariants (no
overlap / no gap), schema filtering, primary-key / relationship / column
shape, and output parity against the existing `pgTableZod` schema.

## Additional context

<!-- This is an auto-generated comment: release notes by coderabbit.ai
-->
## Summary by CodeRabbit

* **New Features**
* Enabled infinite scrolling for table listings with cursor-based
pagination and optional page sizing.
* Added an option to include per-table column data in paginated results.
  * Made column queries filterable for more targeted metadata retrieval.

* **Tests**
* Added comprehensive tests validating pagination, schema scoping,
column inclusion, and relationship/PK shaping.

<!-- review_stack_entry_start -->

[![Review Change
Stack](https://storage.googleapis.com/coderabbit_public_assets/review-stack-in-coderabbit-ui.svg)](https://app.coderabbit.ai/change-stack/supabase/supabase/pull/46285?utm_source=github_walkthrough&utm_medium=github&utm_campaign=change_stack)

<!-- review_stack_entry_end -->
<!-- end of auto-generated comment: release notes by coderabbit.ai -->
 2026-05-26 14:55:13 -04:00
Charis
62d59d596b convert advisor and auth queries in pgmeta to safesql (#44998) 
<!-- This is an auto-generated comment: release notes by coderabbit.ai
-->
## Summary by CodeRabbit

* **Refactor**
* Improved SQL construction across the studio to make queries safer and
more consistent.
* Safer parameter handling for optional schema and remediation links to
prevent injection risks.
* Deterministic query header formatting and stable date/comments in
generated SQL.
* More robust user-count and paginated-user queries for accurate counts,
sorting and pagination.
  * Updated tests to align with the new safe query handling.
<!-- end of auto-generated comment: release notes by coderabbit.ai -->
 2026-04-20 11:34:00 -04:00
Joshen Lim
98b1b79909 Chore/shift manual queries into pg meta 04 (#43956) 
## Context

Shifts all remaining dashboard queries into pg-meta so that we
centralize all manually written queries in one place
Having them in packages/pg-meta also allows us to write tests for them

## To test

Just needs a smoke test on
- Role Impersonation
- Lints
- Data API
- Database
  - Enumerated Types
- Integrations
  - Foreign Data Wrappers
  - Vault
 2026-03-24 16:23:13 +08:00
Joshen Lim
fc7fb50aa2 Organize pg meta queries for studio (#43562) 
## Context

We reached to a consensus that we'll keep manually written queries
within the `pg-meta` package

I'll shift queries over in several PRs but wanted to double check if the
changes here is a good folder structure

## Changes involved

- Am organizing dashboard queries in `pg-meta` based on product
  - So folder structure will be `/sql/studio/[product]/*.ts`
- Every constant will be exported directly from `pg-meta`'s index
- So consumption in dashboard will just be `import { xxx } from
'@supabase/pg-meta`

cc @avallete and @soedirgo on this one - lemme know if this is alright,
or if there's a preferred way to do this
 2026-03-10 09:34:11 +07:00
Ivan Vasilov
1cd1ebfc7f chire: Sort imports in all packages, cms, design-system and ui-library apps (#41610) 
Sorted all imports in all packages, `cms`, `design-system` and
`ui-library` apps by running `pnpm format` on them.

All changes in this PR are done by the script.
 2026-02-05 13:54:10 +01:00
Ali Waseem
7682bac0f3 Fix: Updated to use optimized search columns when getting user counts (#40107) 
* Updated to use optimized search columns when getting user counts

* added unit tests for query builders

* minor

* Nit fix keywords URL param not getting loaded into search input field

* removed user footer

* updated tests for upstream

* updated integration tests

* updated tests to be int for paginated

* updated schema

* updated types

* updated type imports

* updated type imports

* Apply suggestion from @avallete

Co-authored-by: Andrew Valleteau <avallete@users.noreply.github.com>

* updated literal import

* refactor: use common escaping (#40186)

refactor: use utils for sql escaping

---------

Co-authored-by: Joshen Lim <joshenlimek@gmail.com>
Co-authored-by: Andrew Valleteau <avallete@users.noreply.github.com>
 2025-11-06 15:32:34 +08:00