[dataconnect] @index directive should support operator classes (gin_trgm_ops) — every deploy proposes DROP on manually-created pg_trgm indexes

[sonnynz]

Issue

The @index directive in schema.gql doesn't support PostgreSQL operator classes (e.g. gin_trgm_ops). For fuzzy-search use cases requiring pg_trgm trigram indexes, the only path today is to create them directly in Postgres via raw SQL (CREATE INDEX foo ON tbl USING gin (col gin_trgm_ops)).

Because those indexes aren't expressible in schema.gql, every subsequent firebase deploy --only dataconnect proposes DROP INDEX foo as a "destructive change" and aborts.

Reproduction

1. In Postgres: CREATE EXTENSION pg_trgm; CREATE INDEX my_trgm_idx ON paper USING gin (title gin_trgm_ops);
2. Add nothing related to the index in schema.gql.
3. Run firebase deploy --only dataconnect --force.
4. Observe:

⚠  dataconnect: PostgreSQL schema is incompatible with the SQL Connect Schema.
Those SQL statements will migrate it to be compatible:

/** Destructive: drop index "my_trgm_idx" from table: "paper" */
DROP INDEX "public"."my_trgm_idx"

Error: Command aborted. Your database schema is incompatible with your SQL Connect schema.

Why schemaValidation: COMPATIBLE doesn't help

The docs at https://firebase.google.com/docs/sql-connect/manage-schemas-and-connectors explicitly list DROP SCHEMA / DROP TABLE / DROP COLUMN as the operations COMPATIBLE protects. DROP INDEX is not in that list, and verified empirically: with schemaValidation: COMPATIBLE set and --force passed, the deploy still aborts on index-drop proposals.

Use case

Trigram indexes are how mature platforms (Algolia, Typesense internally, Elasticsearch with the wildcard field type) deliver typo-tolerance / fuzzy search on text columns. There's no SQL Connect-native alternative — @searchable is FTS-only (no edit-distance), and Native SQL (Preview) addresses query side, not schema.

Prior art

Prisma had the same issue: prisma/prisma#7515. Their resolution allowed @@index([col(ops: raw("gin_trgm_ops"))], type: Gin) in the Prisma schema, which made migrate aware of the index without it being the literal one it manages.

Proposed solutions (any of these would work)

1. Extend @index to accept an ops argument: @index(type: GIN, ops: "gin_trgm_ops"). Mirrors Prisma's resolution exactly.
2. A @preserveIndex(name: String) directive at the @table level — declares "an index of this name exists, please don't touch it".
3. A dataconnect/sql/ migration directory where arbitrary SQL DDL lives alongside the GraphQL schema. Schema-diff would mark anything created via these files as managed.

Option 1 is closest to existing primitives and would be the smallest API surface change.

Current workaround (not great)

Patching the deploy workflow to grep stdout for Destructive: drop index .*_trgm_idx and bypass the abort if those are the only destructive proposals. Brittle (depends on CLI output wording) but it's the only path that doesn't silently break the indexes.

Environment

• firebase-tools 15.8.0
• Cloud SQL Postgres 16
• dataconnect.yaml with schemaValidation: "COMPATIBLE" set correctly at the postgresql level

[fredzqm]

Thank you @sonnynz for the feedback! We totally missed it.

Yeah, we should make COMPATIBLE_MODE tolerate untracked index and gradually close the gaps to represent all possible pg index eventually.

Assigning to @itsrakhil to look into it~

[couzip]

Same problem here with PostGIS instead of pg_trgm — a GiST functional index for geo (nearest-neighbor) search isn't expressible via @index, so every firebase deploy proposes DROP INDEX and aborts, even with schemaValidation: COMPATIBLE.

@index(ops:) or @preserveIndex(name:) would cover the PostGIS case too. +1 for making COMPATIBLE_MODE tolerate untracked indexes.