Indexed Case-Insensitive Text Search

This guide explains how to add an optional indexed text-search filter to a customizable service without removing existing GraphQL filters or row-level security (RLS). The Media template's movie-title filter is a concrete example, not a complete implementation for every customer service. Adapt the authorization rules, identifiers and query composition to your service.

Reference implementations

The Media template reference PR demonstrates movie-title filtering, workflow adoption and API tests. The managed Image example below illustrates how the same pattern applies to a multi-tenant service.

These examples show service-specific implementations, not a new shared-library API or a requirement to copy every change. Check the implementation's release status and deploy a backend that exposes the new fields before adopting them in a client.

When this is useful

PostgreSQL's trigram indexes can accelerate substring searches such as ILIKE '%needle%'. With RLS enabled, PostgreSQL restricts when it can evaluate non-leakproof predicates, which can prevent an otherwise suitable trigram index from being used. See the PostgreSQL documentation on row security and trigram indexes.

Changing ILIKE to lower(column) LIKE ..., adding a normalized column, or simplifying a permission policy does not by itself remove this restriction. Do not disable RLS or require privileged changes to PostgreSQL's built-in functions to optimize a search. AWS Aurora, for example, does not provide PostgreSQL superuser privileges through its rds_superuser role.

Existing GraphQL filters remain valid without that privileged workaround. If the existing performance is acceptable, no new search API is required. Check important workflows and their timeout limits before changing database providers. Development reset scripts and hosted provisioning are different paths; change a bootstrap command only if it actually runs against a provider that does not support it.

Which template services need this pattern?

Check for the combination of a real trigram index, RLS on its table, and a search that uses that column. An installed extension or an unused migration helper is not enough to establish exposure.

In the reference template:

• Media has RLS-protected, trigram-indexed search columns; movie title is the worked example.
• Catalog has trigram indexes but does not enable RLS on those tables, so this particular workaround is unnecessary.
• Channel uses RLS and has a title filter, but has no existing trigram index whose benefit would be lost through this change.
• Entitlement and VOD-to-Live have no corresponding indexed RLS search path to adapt.

Your customizations can change this assessment. Do not add search functions to an unaffected service just to make every service look the same.

Keep search and final authorization separate

The example has two parts:

1. A narrowly granted SQL function finds matching candidate IDs using the existing trigram index. It executes with the table owner's privileges and explicitly checks the required permission.
2. An additive GraphQL filter restricts the ordinary table query to those candidate IDs. That query still executes with the GraphQL role, so its normal RLS policies determine the returned rows and counts.

The SQL function returns IDs, not protected records directly to the GraphQL client. It is not exposed as a standalone GraphQL query. No new search database, synchronized search table or external search engine is needed for this example.

:::warning Candidate visibility is a security decision A SECURITY DEFINER function normally bypasses the table owner's RLS policies. Applying RLS to the final GraphQL query does not make the candidate function equivalent to every possible row policy. A SQL role granted EXECUTE can call the function directly and obtain candidate IDs, even if an additional customer policy hides those rows from an ordinary query. The app_hidden schema name is not an access-control boundary.

Review who can execute the function and whether IDs, existence or timing reveal restricted information. For multi-tenant services, restrict candidate generation by both tenant and environment, using the trusted request context. For ownership, group-membership or other custom visibility rules, add the appropriate restrictions or retain the existing filtering path. Do not adopt a function with broader visibility than your security requirements allow. :::

Media example

Existing queries continue to work

The original filter remains available:

query Movies($term: String!) {
  movies(filter: { title: { includesInsensitive: $term } }) {
    nodes {
      id
      title
    }
  }
}

The example adds titleIncludesInsensitive to MovieFilter:

query Movies($term: String!, $after: Cursor) {
  movies(
    filter: {
      and: [
        { titleIncludesInsensitive: $term }
        { publishStatus: { equalTo: NOT_PUBLISHED } }
      ]
    }
    orderBy: TITLE_ASC
    first: 30
    after: $after
  ) {
    nodes {
      id
      title
    }
    totalCount
    pageInfo {
      hasNextPage
      endCursor
    }
  }
}

Pass literal user text, not SQL wildcard patterns. The adapter escapes %, _ and backslashes in the same way as the existing includesInsensitive operator. It preserves case-insensitive substring matching; it does not turn the search into prefix or fuzzy matching.

The new field belongs to the existing filter type rather than a separate collection argument. It can therefore participate in existing and, or, not and relation-filter compositions. Bulk operations that accept MovieFilter can use the same filter, provided their target-selection implementation evaluates it through the same GraphQL schema and authorization context. Verify this in your service rather than assuming every custom bulk implementation does so.

Backend changes

For the movie-title example, the change consists of:

• One SQL migration defining a function that returns matching movie IDs.
• One small PostGraphile v4 plugin that adds the field to MovieFilter and registers its SQL resolver with the connection-filter plugin.
• Registration of that plugin with the service's existing plugins.
• Integration tests and regenerated database and GraphQL artifacts.

Keep table, schema and function names fixed by service code. Bind the search value as a SQL parameter; never construct SQL identifiers or statements from request text. Use a fixed, trusted search_path, revoke default PUBLIC execution, and grant execution only to the intended application role. See PostgreSQL's security-definer function guidance.

The Media example reuses the stock Movies read permission because that table's stock visibility rule is permission-based. Do not copy that permission check as a replacement for customer-specific row restrictions. Managed multi-tenant services need tenant and environment restrictions inside candidate generation as well as final RLS.

Workflow changes

The Movie Explorer keeps its existing filter input and query shape. Its filter transformation can choose the new field for a title search while leaving unrelated filters unchanged. The same transformed filter must continue to select the same records for list results and bulk actions. Regenerate workflow types after adding the backend filter field.

The reference workflow retains the legacy path for empty or short terms. This is a performance choice, not a restriction on what users may search for. A term of three or more characters is not necessarily selective; a common word can still match most records.

Managed Image example

The managed Image reference implementation applies the same pattern to two non-null columns: title and source file name. Its additive fields are ImageFilter.titleIncludesInsensitive and ImageFilter.sourceFileNameIncludesInsensitive. For example, after deploying a backend version that exposes these fields:

query Images($term: String!) {
  images(filter: { sourceFileNameIncludesInsensitive: $term }, first: 30) {
    nodes {
      id
      title
      sourceFileName
    }
    totalCount
  }
}

Candidate generation in that implementation checks the image permission and restricts both tenant and environment from the trusted request context. The final query still applies RLS. Existing title, file-name, tag and other filters remain available; this is not an automatic rewrite of every string filter. Customers consuming the managed API do not install the SQL functions themselves.

Adapt the example to your service

Before adding another field, check:

• Index: Does the exact searched column have a suitable trigram index?
• Identifier: Does the function return the correct identifier type and uniquely identify the target rows? Do not assume every entity has a single integer id.
• Visibility: Which permissions and tenant, environment, ownership or other restrictions must apply to candidate generation?
• Nulls: Movie titles are not nullable. A nullable column needs separate tests for SQL's three-valued logic, especially under not; excluding nulls from candidates is not automatically equivalent to negating ILIKE.
• Composition: Do custom resolvers, relation filters and bulk operations actually use the same registered filter implementation?
• Performance: Are the intended queries selective enough for candidate generation to help?

Prefer a few reviewed service-specific functions over a generic function that accepts arbitrary table names or attempts to reconstruct arbitrary RLS policies. No shared-library upgrade is inherently required for this pattern. Test the adapter when upgrading PostGraphile or the connection-filter plugin.

Performance and validation

Candidate generation is not always faster. Selective searches can use GIN to find a small set of IDs, while broad searches can spend more time materializing candidates and looking up rows than an ordinary scan would. Pagination does not necessarily limit candidate generation, and requesting totalCount can require examining the whole matching set.

Compare both paths using representative data, the real application role and the target PostgreSQL engine:

• Selective, broad, short, empty and no-match terms.
• Literal wildcard characters and SQL-looking input.
• Other filters, nested compositions, ordering, counts and cursor continuation.
• Denied permissions, missing authorization context and additional row policies.
• Cross-tenant and same-tenant/different-environment data where applicable.
• Direct SQL function grants and candidate visibility.
• The actual bulk-action target-selection path.

Inspect execution plans with RLS in effect. An owner-role plan alone does not establish how the application query behaves. Do not treat a small synthetic benchmark as an Aurora production latency guarantee. Keep the existing path for workloads where it performs better.

Deploy without breaking existing callers

Adding the filter field does not require existing API consumers to change. Deploy the backend capability before deploying a workflow or client that uses it. Reset pagination when a search or filtering path changes; do not carry a cursor between different filters.

If the new path needs to be rolled back, route callers back to the original filter first. Leave an adopted field available while clients may still use it; removing it later is an API compatibility change.