PostgREST surface area / GraphQL Migration

[jshearer]

1. Catalog Lifecycle (drafts, publications, discovers, evolutions)

This is the largest and most complex domain. It covers the entire user workflow of creating, editing, validating, and publishing catalog specifications.

PostgREST endpoints in use:

Endpoint	Type	UI	flowctl	Operations	GraphQL?
drafts	table	SELECT, INSERT, DELETE	SELECT, INSERT, DELETE	Full CRUD for change-sets	None
drafts_ext	view	SELECT	SELECT	List drafts with computed fields	None
draft_specs	table	SELECT, INSERT, UPDATE, DELETE, UPSERT	SELECT, INSERT, DELETE	Core spec editing	None
draft_specs_ext	view	SELECT	SELECT	Extended draft specs with authz	None
draft_errors	view	SELECT	SELECT	Validation error display	None
publications	table	INSERT, SELECT (polling)	INSERT	Trigger publish, poll for completion	None
publication_specs_ext	view	SELECT	SELECT	Publication history with user info	Partial: flowctl catalog history uses GraphQL PublicationHistoryQuery; UI still uses PostgREST
discovers	table	INSERT, SELECT (polling)	INSERT, SELECT	Auto-discovery of connector resources	None
view_logs	RPC	yes	yes	Stream job logs during publish/discover	None

Existing GraphQL coverage: flowctl's catalog history command uses a PublicationHistoryQuery via GraphQL, which queries publication history for a specific live spec. The UI does not use this yet.

RFC for this work lives at #2782

Migration notes: This is the hardest domain to migrate because:

• Publications involve complex server-side orchestration (build, validate, authorize, commit)
• The proposed approach is a createBuild / activateBuild mutation pair
• Snapshot-based authorization (Update validation/publication to use snapshots #2781) is a prerequisite for acceptable publication performance through GraphQL
• Need to figure out what to do about drafts, as well as migrating the UI; flowctl ought to be fairly straightforward
• view_logs is a polling-based log streaming mechanism that will need a GraphQL equivalent. Maybe polling, maybe SSE

Estimated size: XL. This is probably 40-50% of the total migration work.

---

2. Live Specs

The "read" side of the catalog: querying what's currently deployed.

Endpoint	Type	UI	flowctl	Operations	GraphQL?
live_specs	table	SELECT	SELECT	Fetch active specs	GraphQL exists (backend + flowctl)
live_specs_ext	view	SELECT (heavy)	SELECT	Denormalized view with connector info, shard labels, stats	Partial: GraphQL liveSpecs query covers core fields; _ext columns (connector image, shard labels, writes_to/reads_from as text) not all exposed
live_spec_flows	table	SELECT	-	Data lineage (source/target relationships)	GraphQL exists as readsFrom/writesTo/readBy/writtenBy on LiveSpec type

Existing GraphQL coverage:

• Backend: liveSpecs query with filtering by prefix, name, catalog type, data plane. Includes readsFrom, writesTo, readBy, writtenBy relationship traversal.
• flowctl: catalog list and catalog status fully migrated to GraphQL.
• UI: Uses GraphQL liveSpecs query in src/api/gql/liveSpecs.ts for entity listing (catalog names + types). But the main entity tables and detail views still use live_specs_ext via PostgREST extensively (dozens of query variants in src/api/liveSpecsExt.ts and src/hooks/useLiveSpecsExt.ts).

Migration notes: The live_specs_ext view is the single most heavily queried PostgREST endpoint in the UI. It joins live_specs with connectors, connector_tags, user profiles, and data planes to produce a denormalized row. Migrating this requires either:

• Expanding the GraphQL LiveSpec type to expose all the joined fields the UI needs (connector image name/tag, logo URL, shard labels, writes_to/reads_from text columns, last_pub_user_* fields)
• Or building new UI components that compose multiple GraphQL queries

Estimated size: L. High value because live_specs_ext is so heavily used. Already ~50% complete

---

3. Connectors

Endpoint	Type	UI	flowctl	Operations	GraphQL?
connectors	table	SELECT (with nested connector_tags)	SELECT	Connector registry/menu	GraphQL exists (backend); UI not yet migrated
connector_tags	table	SELECT	SELECT	Connector versions and schemas	GraphQL exists as ConnectorSpec (backend); UI not yet migrated

• Backend: connectors query (paginated, filterable by protocol), connector lookup (by image name or ID), connectorSpec resolver (full image name to spec). PR graphql: add basic connector queries #2757 is merged.
• flowctl: encrypt.rs already migrated to use connectorSpec GraphQL query.
• UI: Travis has been integrating locally off the PR branch. The UI still queries connectors and connector_tags via PostgREST in the meantime.

Estimated size: S. Backend ready; UI work in progress (estuary/ui#1949)

---

4. Authorization and Grants

Endpoint	Type	UI	flowctl	Operations	GraphQL?
combined_grants_ext	view	SELECT	SELECT	Combined user+role grants view	None
user_grants	table	SELECT, INSERT, UPDATE, DELETE	SELECT, INSERT, UPDATE, DELETE	Per-user prefix permissions	None
role_grants	table	SELECT, INSERT, DELETE	SELECT, INSERT, UPDATE, DELETE	Role-to-role delegation	None
auth_roles	RPC	SELECT (GQL for support users, PostgREST for regular users)	-	Get user's authorized prefixes	Partial: GraphQL prefixes query used in UI for support staff only (regular users still use PostgREST due to snapshot staleness bounded by MIN_REFRESH_INTERVAL=20s / MAX_REFRESH_INTERVAL=5min, worst case ~5min not ~30s; see GRAPHQL.md); flowctl auth list fully migrated
directives	table	SELECT, INSERT, UPDATE	-	Privileged operations (org provisioning, grant tokens)	None
applied_directives	table	SELECT, UPDATE	-	User-applied directives with claims	None
exchange_directive_token	RPC	yes	-	Redeem bearer tokens for directives	None

Existing GraphQL coverage:

• auth_roles RPC: the GraphQL prefixes query exists and is used by flowctl (auth list) and the UI's EntitiesHydratorForSupport. However, the UI only uses GraphQL for users with the support role. Regular users still use PostgREST via EntitiesHydrator → useHydrateStateWithPostgres because the auth snapshot refreshes on an interval, and first-time-user flows can hit the worst case and not see their newly-created tenant.
• inviteLinks (query, create, redeem, delete) are fully on GraphQL in both backend and UI. These partially overlap with the directives mechanism but are a separate system.

Migration notes: combined_grants_ext is a complex view joining user_grants, role_grants, and user profile information. The UI uses it for the access grants admin panel and user information lookups. user_grants and role_grants are full CRUD from both UI and flowctl. Directives are a somewhat isolated subsystem (org provisioning, grant links) used in onboarding flows that we may want to transition away from now that we have an actual API.

Estimated size: L. CRUD operations on grants tables + the combined_grants_ext view denormalization + directives subsystem.

---

5. Alerts and Monitoring

Endpoint	Type	UI	flowctl	Operations	GraphQL?
alert_history	table	-	-	(direct table not queried)	GraphQL only
alert_subscriptions	table	SELECT (legacy check)	-	Legacy subscription check	GraphQL primary
alert_data_processing	table	INSERT, UPDATE, DELETE	-	Per-task data processing alert config	Being replaced by GraphQL
Alert queries	GQL	SELECT	SELECT	Alert history, active alerts	GraphQL only
Alert subscriptions	GQL	Full CRUD	Full CRUD	Subscription management	GraphQL only
Alert types	GQL	SELECT	-	Alert type metadata	GraphQL only

Existing GraphQL coverage: This domain is the most fully migrated:

• Alert history querying: GraphQL only (UI AlertHistory table component uses gql directly)
• Alert subscriptions: Full CRUD via GraphQL mutations in both UI and flowctl
• Alert types: GraphQL query in UI (AlertTypeQuery)
• Active alerts badge: GraphQL in UI

Remaining PostgREST usage:

• alert_data_processing table: Being removed as part of Customizable alerting #2868
• alert_subscriptions table: one legacy PostgREST query (getNotificationSubscriptionForUser) that checks whether a specific email is subscribed to a prefix. This could likely be replaced by the existing GraphQL alertSubscriptions query.

Estimated size: S. Just alert_data_processing CRUD and one legacy subscription check to migrate.

---

6. Infrastructure (Data Planes, Storage Mappings)

Endpoint	Type	UI	flowctl	Operations	GraphQL?
data_planes	table	SELECT (via GQL + PostgREST)	SELECT	Data plane listing	GraphQL primary
storage_mappings	table	SELECT (via GQL + PostgREST)	SELECT	Storage config	GraphQL primary
republish_prefix	RPC	yes	-	Republish after storage mapping change	None

Existing GraphQL coverage: This domain is almost fully migrated:

• Data planes: GraphQL dataPlanes query used by UI (src/api/gql/dataPlanes.ts) and available in backend
• Storage mappings: Full GraphQL coverage in UI. Queries (storageMappings), mutations (createStorageMapping, updateStorageMapping), and health checks (testConnectionHealth) all implemented in src/api/gql/storageMappings.ts

Remaining PostgREST usage:

• data_planes via PostgREST: flowctl still uses PostgREST (local_specs.rs). UI has some residual PostgREST calls for data plane lookups outside the new storage mapping admin flow.
• storage_mappings via PostgREST: flowctl still uses PostgREST (local_specs.rs). The UI's entities store hydrator (useStorageMappingsHydrator) still calls getAllStorageMappingStores() via PostgREST.
• republish_prefix RPC: called from the UI after storage mapping updates. No GraphQL equivalent.

Estimated size: S. Mostly cleanup of residual PostgREST calls and migrating republish_prefix.

---

7. Statistics and Billing

Endpoint	Type	UI	flowctl	Operations	GraphQL?
catalog_stats	table	SELECT	-	Usage metrics (bytes, docs, by grain)	None
task_stats_by_day	view	SELECT	-	Per-task daily stats rollup	None
invoices_ext	view	SELECT	-	Billing invoices	None
billing	edge function	yes	-	Stripe integration (setup intent, payment methods, invoices)	None
tenants	table	SELECT	-	Tenant details (trial info, payment provider, billing)	None

Existing GraphQL coverage: None.

Migration notes:

• The billing edge functions (supabase/functions/billing/) wrap Stripe API calls and are separate from PostgREST. We want to migrate all of them into GraphQL queries/mutations. See Migrate billing edge functions to GraphQL #2879 for the edge function migration
• The tenants table is used for billing context (trial dates, payment provider, GCM account ID) and to check hide_preview flags.
• catalog_stats is heavily used in the UI dashboard for usage charts.
• billing_report_202308 (generating invoices) remains in the database for now. Ultimately I would love to also migrate this to Rust, but that's out of scope.

Estimated size: M-L. catalog_stats has complex time-series queries with grain filtering. Billing is a separate subsystem with its own edge functions. tenants is relatively simple reads.

---

8. Schema

Endpoint	Type	UI	flowctl	Operations	GraphQL?
inferred_schemas	table	SELECT	SELECT	Auto-inferred collection schemas	None
registered_avro_schemas	table	-	-	Avro schema registry (used by Dekaf)	None

Existing GraphQL coverage: None.

Migration notes: inferred_schemas is a straightforward read (fetch schema JSON by collection name). registered_avro_schemas is primarily consumed by Dekaf (data plane component), not the UI or flowctl directly. The UI reads inferred_schemas during capture/materialization editing.

Estimated size: S.

---

9. Auth and Tokens

Endpoint	Type	UI	flowctl	Operations	GraphQL?
refresh_tokens	table	SELECT, UPDATE	-	Token management admin UI	None
create_refresh_token	RPC	yes	-	Generate refresh tokens	None
generate_access_token	RPC	-	yes (flow-client-next)	Exchange refresh token for access token	None
user_info_summary	RPC	yes	-	Current user profile/summary	None
oauth	edge function	yes	-	OAuth connector config encryption	None

Existing GraphQL coverage: None.

Migration notes: Token management and the OAuth edge function are tied to Supabase's auth system. generate_access_token and create_refresh_token are part of the authentication flow that is tightly coupled to Supabase JWT infrastructure. These may be among the last things to migrate because they depend on how authentication works post-Supabase. user_info_summary is called on every UI session start.

Related: #2774

Estimated size: M. Auth/token RPCs are small individually but coupled to Supabase auth infrastructure.

---

10. Misc

Endpoint	Type	UI	flowctl	Operations	GraphQL?
draft_collections_eligible_for_deletion	RPC	yes	-	Capture deletion workflow	None
generate_opengraph_value	RPC	-	-	OG metadata (unused in current UI?)	None
tier_line_items	RPC	-	-	Billing tier calculation	None

Estimated size: XS.