Configurable Claim Types Without an EAV Trap

Do not replace inheritance with a pure key/value model. Inheritance is not the cause of the 30-second page; the retrieve-and-filter-in-microflow pattern is. Keep Claim as the base entity with the 70% shared attributes, status, dates, Policy and Customer associations. Add ClaimType as a persisted reference entity so business users can add type metadata, validation rules and display labels without a model change. Put type-specific fields that are searched, sorted, validated or used in entity access into real columns or 1-1 detail entities, not into ClaimAttribute rows. A key/value model forces joins, defeats composite indexes, makes XPath constraints unreadable and pushes validation into every microflow. For genuinely optional, non-queried extras, use a single JSON or long-text extension attribute on Claim, with a microflow validating its schema against ClaimType. Be honest about the zero-redeploy goal: arbitrary new queryable fields still need a schema change. A realistic target is Claim plus ClaimType for configuration, detail entities for the three current types, and a controlled extension attribute for display-only additions. That preserves type safety and reporting performance while giving the business quarterly configuration for labels, required-field rules and workflow routing.

Retrieving all 4.2 million claims moves every row and column into the runtime before any filter is applied. The microflow filter cannot use a database index, and the page pays for network transfer, object instantiation and memory. Add a composite index on Claim(Status, CreatedDate) in the order used by the overview query, plus an index on the Policy association if the page filters by policy. Replace the retrieve-all with an XPath constraint that includes Status and a CreatedDate range, and use the Retrieve activity with a range and sort order so the database does paging. Build the count separately with a count retrieve rather than counting a list in a microflow. Avoid OR conditions that prevent index use; split them into separate queries or use two indexed attributes. If the page needs a search field, use starts-with or equals on an indexed attribute and never contains on a large text column. Check the query plan in PostgreSQL, because separate single-column indexes may lead to a bitmap AND rather than an efficient composite scan. In the microflow, remove loops that retrieve associations per row; retrieve the needed associations in the same query or use a view entity. Finally, set a page size of 20 to 50 and test with production-like data, not a 1,000-row acceptance database.

Treat this as a two-release migration, not a big-bang rename. First add ClaimType, the extension attribute and any detail entities through database synchronization in acceptance, with a backup and a timed rehearsal against a copy of production. Create ClaimType records for Auto, Property and Liability, then backfill the Claim-to-ClaimType association in batches of 10,000 using a microflow that commits per batch so the transaction log does not grow without limit. Keep the existing specializations and dual-write during release one: every new claim still creates the specialization and also sets ClaimType. Make the overview and integrations read from the new fields behind a constant or feature switch. Run reconciliation counts by subtype and by ClaimType, and compare totals for a sample of 1,000 claims. In release two, after the business signs off, switch writes to the new model, make the old specialization pages read-only, and remove the specializations only when no integration or report depends on them. The non-null constraint, if any, must be added after backfill or with a default. Keep a rollback path: the old columns remain until release two, so reverting the deployment does not lose data.