Postgresql - optimizing multicolumn search with "prefix" search pattern as one of the filters

I'm now working on the project that automates "contracts" creation and signing. Recently I received a requirements to implement search functionality for "contracts", so I struggle to find performant way for doing it (I'm kinda new to postgres so far). Contract table looks like CREATE TABLE contracts( id UUID, status TEXT, name TEXT, issuers JSOB, approver JSONB, period JSONB created TIMESTAMP WITHOUT TIME ZONE ) *status* - contract status, one of *draft, signed, active, expired* *issuers* - json array of objects like {"id": , email: , name: , ...} *approver* - json obj like {"id": , email: , name: , ...} *period* - json obj like {"start": , "end": ...} Search requirements: 1. "prefix" search with user-specified value where any of contract name, contract issuer (any) email, contract issuer (any) name, approver name, approver email matches condition. 2. Filtering by contract status using IN operator. 3. Ordering by created timestamp, period start date, period end date 4. Paging As application is user-centric all queries will contain either issuer id, approver id or both as additional filter criteria. Search examples: 1. For issuer with id "1" display contracts matching predicate "test@gmail" order by created timestamp. Such search is supposed to return following contracts {"name": "test@gmail contract name e.g.", issuers:[{id: "1"...}...], ...} {"name": "Name", issuers: [{id: "1"...}, {... email: "test@gmail.com"...}...]} {"name": "Name1",issuers:[{id: "1"...}...], approver: {.."email": "test@gmail.com"..}} 2. For approver with id "11" display contracts matching predicate "aggre" order by start date. Such search is supposed to return following contracts {"name": "aggreement 11232", approver:{"id": "11"...}, ...} {"name": "Name", issuers: [{"name: "Aggre Xyz"...},...], approver:["id": "11"...}} 3. For issuer with id "1" and approver with id "11" display contracts matching predicate "aggre" order by start date. Such search is supposed to return following contracts {"name": "aggreement 11232", approver:{"id": "11"...}, issuers: [{id: "1"...} ...} Considerations: 1. With current db layout I have only gin index for the rescue, which will not help with the sorting as far as I know, apart from other complications it brings. 2. Rebuilding the schema to more classical one to many contract-issuer relation + saving approver and period objects fields as columns. On the one hand it will let utilize btree indexes for at least issuer id/approver id filtering + will let perform efficient sorting. On the other hand finding matches by issuer name/email doesn't seem easy and would involve many joins + plus it's a huge change for the schema. 3. Adding separate "search-optimized" table. Two options 3.1 CREATE TABLE search( issuer_id UUID, approver_id UUID, attribute_name TEXT, attribute_value TEXT, contract_id ) *attribute_name* - contract-name, issuer_name... With that approach search seems to be pretty easy, but amount of additional rows is a concern 3.2 Smth like this with attributes merged in array CREATE TABLE search( issuer_id UUID, approver_id UUID, search_attributes TEXT[], contract_id ) search attributes will contain all the strings that might match for "prefix" search. While it complicates the writes (one more db object to be updated...) it seems to be beneficial for reads. SELECT * FROM contracts INNER JOIN ( SELECT contract_id FROM search WHERE issuer_id = 'id' AND EXISTS( SELECT 1 FROM unnest(search_attributes) attr, WHERE attr LIKE 'input%' ) I would appreciate any suggestion.