Hybrid Query Engine

The query engine can transparently route requests through a denormalised JSONB index. This hybrid approach keeps write operations on the primary tables while enabling fast reads for list and search screens. Its primary target is user-defined entities (from the Entities module) which are stored as JSONB documents.

Entity type	Defined in	Storage	Primary use	Example
System entities	Module data/entities.ts (MikroORM)	Relational tables (one per entity)	Core platform objects extended with custom fields	auth.user, directory.organization
User entities	Module ce.ts / admin UI	JSONB documents in the Entities module	Customer-specific records managed at runtime	inventory:item, CRM pipelines

The hybrid query engine optimises user entities, while the JSONB indexing layer targets system entities with custom fields (flattening base columns + cf:<key> values). Treat them as complementary mechanisms, not replacements for each other.

How it works

1. Each entity that opts in gains an *_index table storing flattened rows (user entity payloads).
2. Background jobs or CLI commands (yarn mercato entities install --reindex) populate and refresh the index.
3. When the index is up to date, the query engine executes filters and sorts against the JSONB payload, bypassing expensive joins.
4. If the index is missing or stale, the query engine automatically falls back to the relational query plan.

Opting in

src/modules/inventory/index.ts

export const metadata = {
  id: 'inventory',
  title: 'Inventory',
  version: '0.1.0',
  description: 'Track stock levels for sellable items.',
  queryIndex: {
    entities: ['inventory:item'],
  },
};

• Add the entity id you want indexed.
• Run yarn modules:prepare and yarn db:generate to create the index tables.
• Trigger an initial backfill (for example, yarn mercato entities sync-index --entity inventory:item --tenant <tenantId>).

Working with custom fields

• User entities: custom fields are already part of the JSONB payload, so the hybrid index copies them verbatim.
• System entities: continue using the JSONB indexing layer to flatten base columns and cf:<key> values. The hybrid index does not replace that mechanism.

Monitoring freshness

• Inspect modules.generated.ts to confirm the entity lists hybridQuery: true.
• Each index record carries an updated_at timestamp. Use it to detect stale data and schedule incremental refreshes.
• The engine logs when it falls back to relational mode, making it easy to flag missing indexes during development.
• Use the Entities module dashboards to confirm user-entity records stay in sync after edits in the admin UI.

When you need fast lists for system entities enriched with module-defined custom fields, enable the JSONB indexing layer. When you need performant queries over runtime-defined user entities, opt into the hybrid query engine.