Part 013 — Key-Value and Flexible Attribute Patterns

Enterprise systems almost always need flexible attributes.

CPQ and order-management systems need them even more.

A catalog may need different attributes per product family. A tenant may need configurable behaviour. A product offering may have optional parameters. A quote line may carry dynamic inputs. An integration may send external fields that cannot be predicted perfectly. A UI may render dynamic forms. A rule engine may need structured parameters.

The dangerous conclusion is:

Because attributes are dynamic, the database model should be dynamic too.

That is only sometimes true.

The senior-engineer question is not:

Can PostgreSQL store flexible attributes?

It can.

The real question is:

Which parts of the data must remain strongly modelled because they affect correctness, constraints, lifecycle, search, pricing, approval, fulfilment, reporting, integration contracts, and auditability — and which parts are safe to keep flexible?

Flexible attribute modelling is a boundary-design problem.

It touches:

• relational modelling;
• JSONB modelling;
• key-value tables;
• EAV anti-patterns;
• tenant configuration;
• product catalog extensibility;
• validation;
• indexing;
• migration;
• reporting;
• MyBatis mapping;
• query planner behaviour;
• operational debugging;
• data governance.

In Java/JAX-RS systems, this becomes even more important because flexible attributes often pass through multiple layers:

HTTP request
  -> DTO / request model
  -> validation
  -> service logic
  -> MyBatis mapper
  -> SQL / JSONB / attribute table
  -> PostgreSQL constraints and indexes
  -> event/outbox payload
  -> reporting/read model/search index

If this chain is not designed intentionally, flexible attributes become untyped, unvalidated, unindexed, unreconcilable production data.

1. Core mental model

A flexible attribute is data whose shape varies by context.

The context may be:

• product type;
• product family;
• tenant;
• market;
• channel;
• lifecycle state;
• version;
• integration partner;
• pricing rule;
• UI form;
• business process;
• feature configuration;
• experimental rollout.

A flexible attribute model usually optimizes one dimension by weakening another.

No model is universally correct.

The right model depends on the attribute's role.

Ask these questions:

1. Does the attribute affect business correctness?
2. Does it participate in pricing, eligibility, approval, fulfilment, or compliance?
3. Does it need a database constraint?
4. Does it need to be searched or filtered frequently?
5. Does it need reporting or analytics?
6. Does it need historical versioning?
7. Does it appear in external contracts or events?
8. Does it need type validation?
9. Does it need tenant-specific override?
10. Does it need migration over time?

The more answers are yes, the less safe it is to hide the attribute in an unmanaged dynamic structure.

2. What problem flexible attributes solve

Flexible attributes exist because enterprise data does not always evolve at the same speed as database schema.

They solve real problems:

• product catalog extensibility;
• tenant-specific configuration;
• market-specific fields;
• integration partner-specific metadata;
• dynamic UI forms;
• optional product parameters;
• sparse attributes;
• backward-compatible payload storage;
• evolving rule parameters;
• audit detail capture;
• integration payload preservation.

Without flexible modelling, teams may create schema churn:

alter table product_offering add column attribute_1 text;
alter table product_offering add column attribute_2 text;
alter table product_offering add column misc_flag boolean;
alter table product_offering add column special_config jsonb;

This is not modelling. It is schema entropy.

But unmanaged flexibility causes the opposite failure:

create table product_attribute (
    entity_id uuid not null,
    key text not null,
    value text
);

Then everything becomes a string.

Pricing logic starts parsing strings. Reporting joins the same table twenty times. Bugs appear because bandwidth, Bandwidth, and band_width all exist. No one knows which keys are mandatory. Values are not typed. Indexes are either useless or too broad.

The goal is not maximum flexibility.

The goal is controlled variability.

3. The modelling spectrum

Think of flexible attributes as a spectrum.

Strict relational columns
  -> relational extension table
  -> typed key-value table
  -> governed JSONB
  -> raw JSONB payload
  -> ungoverned EAV

The further right you go, the more flexible the model becomes.

But you lose:

• database constraints;
• static query clarity;
• planner predictability;
• type safety;
• simple indexing;
• reporting friendliness;
• migration certainty;
• code readability;
• operational debugging.

A senior engineer does not reject flexibility.

A senior engineer assigns flexibility to the least dangerous layer.

4. Pattern 1 — normal relational columns

Use normal columns when the attribute is part of the stable domain model.

Example:

create table quote_item (
    quote_item_id uuid primary key,
    quote_id uuid not null references quote(quote_id),
    product_offering_id uuid not null,
    quantity integer not null check (quantity > 0),
    status text not null,
    effective_date date not null,
    created_at timestamptz not null default now(),
    updated_at timestamptz not null default now()
);

This is the strongest model.

It supports:

• not null;
• check constraints;
• foreign keys;
• indexes;
• type correctness;
• query planner statistics;
• simple reporting;
• clear MyBatis mapping;
• stable API contract;
• migration visibility.

Use columns when:

• the attribute participates in core workflows;
• the attribute is used in predicates or joins;
• the attribute affects state transitions;
• the attribute is required for audit/compliance;
• the attribute is needed by many services/read models;
• invalid values would cause production incidents.

Bad use of flexible attributes:

{
  "quantity": "5",
  "status": "APPROVED",
  "effectiveDate": "2026-07-11"
}

If quantity, status, and effectiveDate drive business correctness, they should not be hidden inside JSON unless there is a very explicit reason.

5. Pattern 2 — relational extension table

A relational extension table stores optional attributes in typed columns but separates them from the core table.

Example:

create table product_offering (
    product_offering_id uuid primary key,
    code text not null unique,
    name text not null,
    product_family text not null,
    status text not null
);

create table broadband_offering_detail (
    product_offering_id uuid primary key references product_offering(product_offering_id),
    download_mbps integer not null check (download_mbps > 0),
    upload_mbps integer not null check (upload_mbps > 0),
    access_technology text not null,
    contention_ratio text
);

This pattern is useful when different product families have different stable structures.

It preserves:

• type safety;
• constraints;
• queryability;
• indexing;
• family-specific validation;
• manageable migrations.

Trade-off:

• more tables;
• more joins;
• more code paths;
• more mapper definitions;
• more lifecycle coordination.

Use it when variability exists, but each variant is still stable enough to model explicitly.

6. Pattern 3 — typed attribute table

A typed attribute table is a controlled alternative to raw EAV.

Example:

create table attribute_definition (
    attribute_definition_id uuid primary key,
    attribute_key text not null unique,
    display_name text not null,
    value_type text not null check (value_type in ('TEXT', 'INTEGER', 'DECIMAL', 'BOOLEAN', 'DATE', 'JSON')),
    is_required boolean not null default false,
    is_searchable boolean not null default false,
    is_reportable boolean not null default false,
    created_at timestamptz not null default now()
);

create table product_attribute_value (
    product_id uuid not null,
    attribute_definition_id uuid not null references attribute_definition(attribute_definition_id),
    value_text text,
    value_integer bigint,
    value_decimal numeric(19, 4),
    value_boolean boolean,
    value_date date,
    value_json jsonb,
    created_at timestamptz not null default now(),
    primary key (product_id, attribute_definition_id),
    check (
        num_nonnulls(value_text, value_integer, value_decimal, value_boolean, value_date, value_json) = 1
    )
);

This is more disciplined than a single value text column.

It allows:

• typed values;
• attribute definitions;
• basic validation;
• metadata-driven UI;
• searchable/reportable flags;
• indexes per value type;
• explicit governance.

But it is still complex.

Queries become harder:

select p.product_id, p.code
from product p
join product_attribute_value bandwidth
  on bandwidth.product_id = p.product_id
join attribute_definition bandwidth_def
  on bandwidth_def.attribute_definition_id = bandwidth.attribute_definition_id
 and bandwidth_def.attribute_key = 'download_mbps'
where bandwidth.value_integer >= 100;

If many filters are required, EAV-like structures become planner and readability problems.

7. Pattern 4 — JSONB attribute bag

A JSONB attribute bag stores flexible attributes in one column.

Example:

create table product_offering (
    product_offering_id uuid primary key,
    code text not null unique,
    product_family text not null,
    status text not null,
    attributes jsonb not null default '{}'::jsonb,
    created_at timestamptz not null default now(),
    updated_at timestamptz not null default now()
);

Example value:

{
  "downloadMbps": 500,
  "uploadMbps": 100,
  "accessTechnology": "fiber",
  "contractTermMonths": 24,
  "installationRequired": true
}

This model works well when:

• attributes are sparse;
• attributes vary by product family;
• not all attributes are queried frequently;
• payload is mostly carried through rather than heavily joined;
• validation can be enforced in application/domain layer;
• some targeted expression indexes are enough;
• reporting can use read models or ETL.

It fails when:

• all important fields move into JSONB;
• no schema governance exists;
• attribute names are inconsistent;
• values use mixed types;
• high-cardinality filtering is done on many JSON keys;
• reporting expects relational semantics;
• migrations need to rewrite huge JSONB payloads frequently.

8. Pattern 5 — configuration table

A configuration table stores operational or business configuration.

Example:

create table tenant_config (
    tenant_id uuid not null,
    config_key text not null,
    config_value jsonb not null,
    version integer not null default 1,
    updated_at timestamptz not null default now(),
    updated_by text not null,
    primary key (tenant_id, config_key)
);

This is useful for:

• feature toggles;
• tenant-specific settings;
• UI behaviour;
• integration endpoints;
• rule parameters;
• thresholds;
• workflow routing configuration.

But config tables can become hidden business logic.

Bad signs:

• no ownership of config keys;
• values are edited manually in production;
• no audit trail;
• no validation;
• no rollout process;
• config changes bypass code review;
• service behaviour changes without deployment evidence;
• incident RCA says “bad config value” but no one knows who changed it.

A production-grade config table needs:

• key registry;
• validation;
• ownership;
• audit;
• deployment/change process;
• fallback default;
• versioning;
• rollback plan;
• observability.

9. Pattern 6 — raw integration payload storage

Sometimes you need to store external payloads exactly or almost exactly as received.

Example:

create table external_order_payload (
    payload_id uuid primary key,
    external_system text not null,
    external_reference text not null,
    received_at timestamptz not null default now(),
    payload jsonb not null,
    processing_status text not null,
    error_message text,
    unique (external_system, external_reference)
);

This is valid for:

• traceability;
• debugging;
• replay;
• integration audit;
• schema evolution handling;
• partner-specific payloads.

But do not confuse raw payload storage with domain model.

Raw external payload should often be transformed into canonical relational/domain tables.

Good boundary:

external payload table
  -> validation / mapping
  -> canonical domain tables
  -> outbox / read model

Bad boundary:

external payload table
  -> all business logic reads nested JSON forever

10. EAV anti-pattern

EAV means Entity-Attribute-Value.

Classic structure:

create table entity_attribute_value (
    entity_id uuid not null,
    attribute_name text not null,
    attribute_value text,
    primary key (entity_id, attribute_name)
);

It looks elegant at first.

It is generic. It avoids schema migration. It supports arbitrary attributes.

Then the system grows.

Common failures:

• values are stored as text;
• type validation is weak;
• constraints are externalized into application code;
• queries require self-joins;
• reports become complex and slow;
• indexes are either broad or too many;
• attribute names drift;
• missing mandatory attributes are hard to enforce;
• query planner has poor visibility into semantics;
• MyBatis mapping becomes awkward;
• schema looks simple but business logic becomes hidden.

Example of EAV query pain:

select e.entity_id
from entity_attribute_value speed
join entity_attribute_value region
  on region.entity_id = speed.entity_id
join entity_attribute_value contract
  on contract.entity_id = speed.entity_id
where speed.attribute_name = 'download_mbps'
  and speed.attribute_value::integer >= 100
  and region.attribute_name = 'region'
  and region.attribute_value = 'APAC'
  and contract.attribute_name = 'contract_term_months'
  and contract.attribute_value::integer = 24;

Problems:

• runtime casts;
• weak selectivity;
• multiple self-joins;
• hard-to-read SQL;
• bad error behaviour on invalid values;
• difficult index design;
• fragile attribute naming.

EAV is not always forbidden.

But ungoverned EAV is usually a production debt amplifier.

11. EAV vs JSONB

EAV and JSONB both support flexibility, but fail differently.

Rule of thumb:

• Use JSONB when attributes are naturally document-like and usually retrieved together.
• Use typed attribute tables when attribute metadata and typed validation matter heavily.
• Use relational columns when attributes are core to correctness.
• Avoid raw EAV unless you have governance, metadata, and known query patterns.

12. Attribute criticality classification

Before choosing a model, classify each attribute.

The mistake is treating all flexible attributes the same.

A product color, a pricing tier, a regulatory eligibility flag, and a partner debug payload do not deserve the same storage model.

13. Queryability trade-off

Flexible models usually make writes and schema evolution easier.

They can make queries much harder.

Questions to ask before choosing JSONB/EAV:

• Will API filtering depend on this attribute?
• Will it appear in where clauses?
• Will it be sorted?
• Will it be joined to reference data?
• Will it be grouped in reports?
• Will it need uniqueness?
• Will it need range queries?
• Will it be used in partial indexes?
• Will users search by it interactively?
• Will support engineers query it during incidents?

If yes, do not hide it casually.

Example JSONB predicate:

select product_offering_id, code
from product_offering
where attributes ->> 'accessTechnology' = 'fiber';

This may be acceptable with an expression index:

create index idx_product_offering_access_technology
on product_offering ((attributes ->> 'accessTechnology'));

But if dozens of attributes need this treatment, you may be rebuilding relational schema manually through expression indexes.

That is a design smell.

14. Constraint trade-off

Relational columns can enforce constraints directly.

quantity integer not null check (quantity > 0)

JSONB cannot enforce equivalent domain rules naturally without additional mechanisms.

Possible mechanisms:

• application validation;
• generated columns;
• check constraints using JSON expressions;
• trigger validation;
• schema registry;
• migration-time validation;
• test coverage;
• read model validation;
• ingestion pipeline validation.

Example check constraint:

alter table product_offering
add constraint chk_contract_term_months_positive
check (
    not (attributes ? 'contractTermMonths')
    or ((attributes ->> 'contractTermMonths')::integer > 0)
);

This has problems:

• casting can fail if value is not numeric;
• constraint becomes hard to maintain;
• nested validation becomes ugly;
• error messages may be poor;
• many constraints over JSONB become unreadable.

This does not mean never use JSONB constraints.

It means JSONB validation should be deliberate, not accidental.

15. Indexing dynamic attributes

Indexing flexible attributes requires query-pattern discipline.

15.1 GIN index for containment

create index idx_product_attributes_gin
on product_offering using gin (attributes);

Useful for queries like:

select product_offering_id
from product_offering
where attributes @> '{"installationRequired": true}'::jsonb;

Good for containment.

Not always ideal for scalar equality/range sorting.

15.2 Expression index

create index idx_product_download_mbps
on product_offering (((attributes ->> 'downloadMbps')::integer));

Useful for targeted attributes.

Risk:

• invalid casts;
• index only helps exact matching query expression;
• many expression indexes increase write overhead;
• changing JSON key name breaks query/index contract.

15.3 Partial index

create index idx_fiber_offerings
on product_offering (product_family, code)
where attributes ->> 'accessTechnology' = 'fiber';

Useful for high-value subset queries.

Risk:

• only helps if query predicate matches;
• easy to create too many niche indexes;
• requires review of actual workload.

15.4 Generated column as bridge

A generated column can expose important JSONB attribute as relational field.

alter table product_offering
add column access_technology text
    generated always as (attributes ->> 'accessTechnology') stored;

create index idx_product_offering_access_technology_generated
on product_offering (access_technology);

This can be useful when an attribute starts in JSONB but becomes important for search/reporting.

But if many generated columns are needed, the data model may need to evolve toward relational columns.

16. Validation strategy

Flexible data needs explicit validation.

Validation should happen at multiple layers.

For Java/JAX-RS:

• validate request DTOs before service execution;
• validate dynamic attributes against a registry;
• reject unknown keys unless extension is intentional;
• reject wrong types;
• normalize attribute names;
• version attribute definitions;
• keep validation errors domain-readable;
• log validation failures without leaking sensitive data.

Bad validation model:

accept any JSON
  -> store in JSONB
  -> hope downstream logic knows what to do

Better validation model:

accept request JSON
  -> parse into known envelope
  -> validate dynamic attributes against definition
  -> store canonical JSONB
  -> project searchable/reportable fields
  -> emit governed event payload

17. Attribute schema registry

A schema registry is not only for Kafka events.

Flexible attributes also need a registry.

Minimum metadata:

create table flexible_attribute_definition (
    attribute_key text primary key,
    owner_team text not null,
    description text not null,
    value_type text not null,
    allowed_entity_type text not null,
    is_required boolean not null default false,
    is_searchable boolean not null default false,
    is_reportable boolean not null default false,
    is_sensitive boolean not null default false,
    lifecycle_status text not null check (lifecycle_status in ('DRAFT', 'ACTIVE', 'DEPRECATED', 'REMOVED')),
    introduced_in_version text,
    deprecated_in_version text,
    created_at timestamptz not null default now(),
    updated_at timestamptz not null default now()
);

This helps answer:

• Who owns this attribute?
• What type is it?
• Is it allowed for this entity?
• Is it required?
• Is it searchable?
• Is it reportable?
• Is it sensitive?
• Is it deprecated?
• What migration path exists?

Without this, flexible attributes become tribal knowledge.

18. Naming discipline

Attribute naming drift creates long-term data bugs.

Examples of drift:

downloadMbps
download_mbps
bandwidthDownMbps
downstreamBandwidth
speed

Once these appear in production data, every query, index, mapping, report, and event consumer becomes more complex.

Rules:

• choose one naming convention;
• enforce it at validation boundary;
• reject unknown aliases unless explicitly mapped;
• document keys;
• version changes;
• migrate old keys;
• avoid ambiguous names;
• include units in names when relevant.

Prefer:

{
  "downloadMbps": 500,
  "contractTermMonths": 24
}

Avoid:

{
  "download": 500,
  "term": 24
}

Ambiguous fields become production interpretation bugs.

19. Unit discipline

Dynamic attributes often hide units.

Bad:

{
  "speed": 500,
  "duration": 24,
  "amount": 99.99
}

Better:

{
  "downloadMbps": 500,
  "contractTermMonths": 24,
  "monthlyRecurringChargeAmount": "99.99",
  "currencyCode": "USD"
}

For money, prefer explicit structured representation rather than floating values:

{
  "amountMinor": 9999,
  "currencyCode": "USD"
}

or relational columns:

amount numeric(19, 4) not null,
currency_code char(3) not null

Unit ambiguity causes correctness bugs that are hard to detect with database constraints.

20. MyBatis integration concerns

Flexible attributes often enter MyBatis as:

• Map<String, Object>;
• raw String JSON;
• Jackson JsonNode;
• custom DTO;
• domain-specific attribute object;
• custom TypeHandler for JSONB.

Avoid letting raw Map<String, Object> spread across service code.

It weakens:

• type safety;
• validation;
• refactoring;
• testability;
• code navigation;
• error handling.

Better approach:

API DTO
  -> domain attribute model
  -> validation
  -> canonical JSON serialization
  -> MyBatis JSONB TypeHandler

Mapper boundary should be boring.

Example conceptual mapper:

<insert id="insertProductOffering">
  insert into product_offering (
      product_offering_id,
      code,
      product_family,
      status,
      attributes
  ) values (
      #{id},
      #{code},
      #{productFamily},
      #{status},
      #{attributes, typeHandler=com.example.JsonbTypeHandler}
  )
</insert>

The mapper should not be responsible for business interpretation of dynamic attributes.

21. Safe dynamic ORDER BY and filters

Dynamic attributes often drive dynamic filtering.

Risky pattern:

order by ${sortField} ${sortDirection}

This can become SQL injection if not strictly whitelisted.

Safer pattern:

API sort field
  -> whitelist mapping
  -> known SQL expression

Example application mapping:

Map<String, String> allowedSorts = Map.of(
    "code", "po.code",
    "downloadMbps", "((po.attributes ->> 'downloadMbps')::integer)",
    "createdAt", "po.created_at"
);

Then only inject the mapped expression after strict whitelist validation.

Even then, review performance.

Dynamic sorting on JSONB attributes can cause expensive sorts unless supported by indexes or constrained result sets.

22. Migration strategy for flexible attributes

Flexible attributes still require migrations.

Common migrations:

• rename key;
• split key;
• merge keys;
• change type;
• move JSONB key to relational column;
• move relational column to JSONB metadata;
• backfill missing default;
• deprecate attribute;
• add index on attribute;
• remove unsupported values.

Example JSONB key rename:

update product_offering
set attributes = (attributes - 'speed') || jsonb_build_object('downloadMbps', attributes -> 'speed')
where attributes ? 'speed'
  and not attributes ? 'downloadMbps';

For large tables, do not run massive updates casually.

Use:

• chunking;
• batching;
• checkpointing;
• throttling;
• validation query;
• rollback or roll-forward plan;
• observability;
• maintenance window if needed.

Flexible does not mean migration-free.

It means migration complexity moves from DDL to data semantics.

23. Reporting impact

Flexible attributes are painful for reporting unless planned.

Reporting wants stable columns.

Business users ask:

• How many products have attribute X?
• What is average value of attribute Y?
• Which tenants override rule Z?
• Which quotes used configuration version N?
• How does attribute value correlate with order fallout?

If attributes live only as ungoverned JSONB/EAV, reporting becomes fragile.

Options:

1. Extract important attributes to relational read model.
2. Use materialized view.
3. Use generated columns.
4. Use ETL into warehouse/lake.
5. Maintain attribute definition metadata.
6. Mark reportable attributes explicitly.

Example reporting projection:

create materialized view product_offering_attribute_report_mv as
select
    product_offering_id,
    code,
    product_family,
    attributes ->> 'accessTechnology' as access_technology,
    ((attributes ->> 'downloadMbps')::integer) as download_mbps,
    ((attributes ->> 'contractTermMonths')::integer) as contract_term_months
from product_offering
where status = 'ACTIVE';

This is not always the final design, but it shows the principle:

Do not force every reporting query to rediscover dynamic structure from scratch.

24. Operational debugging impact

Flexible attributes make debugging harder unless observable.

During an incident, engineers need to answer quickly:

• Which rows contain the problematic attribute?
• Which value variants exist?
• Which tenant/product/order is affected?
• When did the key appear?
• Which application version wrote it?
• Which migration changed it?
• Which event emitted it?
• Which downstream consumer failed on it?

Useful debugging queries:

select attributes ? 'downloadMbps' as has_download_mbps, count(*)
from product_offering
group by 1;

select jsonb_typeof(attributes -> 'downloadMbps') as type, count(*)
from product_offering
where attributes ? 'downloadMbps'
group by 1;

select attributes ->> 'accessTechnology' as access_technology, count(*)
from product_offering
where attributes ? 'accessTechnology'
group by 1
order by count(*) desc;

These should not be emergency inventions.

For important flexible attributes, keep diagnostic queries in runbooks or dashboards.

25. Security and privacy concerns

Dynamic attributes can accidentally store sensitive data.

Risks:

• PII hidden inside JSONB;
• credentials stored as config values;
• logs printing full attribute maps;
• event payloads leaking sensitive keys;
• reporting exports exposing dynamic fields;
• backup/restore carrying sensitive unclassified data;
• row-level security not considering JSON content;
• data retention rules missing nested fields.

Mitigations:

• classify attributes;
• mark sensitive keys in registry;
• redact logs;
• avoid storing secrets in generic config table;
• use secret manager for secrets;
• validate events before publish;
• review export/report fields;
• include JSONB fields in privacy scans;
• define deletion/retention behaviour.

If a field is sensitive, dynamic storage does not make it exempt from privacy governance.

26. Performance failure modes

Common performance failures:

26.1 Too many JSONB expression indexes

Every index adds write overhead.

If product updates touch JSONB frequently, many expression indexes can slow writes and increase bloat.

26.2 Runtime casting in predicates

where (attributes ->> 'downloadMbps')::integer >= 100

This may fail or become expensive if values are malformed.

26.3 Low-selectivity dynamic filters

Indexing a boolean-like JSON key may not help much if most rows share the same value.

26.4 Huge attribute payloads

Large JSONB payloads can increase TOAST usage, IO, vacuum cost, and update amplification.

26.5 Frequent partial JSON updates

Even if you update one key, PostgreSQL still handles row versioning at tuple level. Large JSONB columns can amplify update cost.

26.6 Reporting scans over JSONB

Aggregating JSONB keys over millions of rows can become expensive without projections.

Performance design must follow actual query patterns.

27. Correctness failure modes

Common correctness failures:

• missing required attribute;
• wrong type stored;
• same concept stored under multiple keys;
• invalid unit;
• stale default value;
• tenant override not applied;
• attribute used in pricing but not versioned;
• attribute changed after quote snapshot;
• event payload includes new attribute unsupported by consumer;
• report excludes rows because key is missing;
• migration rewrites only some variants;
• null vs missing key semantics confused.

Important distinction:

{
  "discountCode": null
}

is not always the same as:

{}

Missing means not present.

Null may mean explicitly empty.

Your domain model must define this.

28. Flexible attributes in CPQ/catalog/order context

Typical CPQ use cases:

Critical CPQ rule:

If an attribute affects price, eligibility, contract terms, approval, or fulfilment, treat it as governed data, not casual metadata.

29. Immutable snapshot concern

In quote/order systems, flexible attributes often need snapshot semantics.

Example:

• product attribute changes tomorrow;
• old quote must still reflect yesterday's catalog state;
• order fulfilment must use quote-time selected attributes;
• audit must explain historical decision.

If quote items reference live product attributes, historical correctness breaks.

Better:

create table quote_item_snapshot (
    quote_item_id uuid primary key,
    product_offering_id uuid not null,
    selected_attributes jsonb not null,
    catalog_version text not null,
    pricing_context jsonb not null,
    captured_at timestamptz not null default now()
);

This does not mean all quote data should be JSONB.

It means flexible and versioned context may need immutable capture.

Important fields may still deserve relational columns in the snapshot table.

30. Event and CDC impact

Flexible attributes often flow into events.

Risks:

• consumer does not understand new key;
• consumer assumes wrong type;
• sensitive attribute leaks;
• event schema allows arbitrary object with no governance;
• replay of old events fails against new parser;
• CDC emits raw table JSONB not intended as public contract.

Guidelines:

• do not expose raw internal attribute bag as external event contract without review;
• version event payloads;
• separate internal storage shape from event schema;
• validate outbound payload;
• maintain backward compatibility;
• document unknown-key behaviour;
• ensure consumers are idempotent and tolerant where appropriate.

Database flexibility must not become event-contract chaos.

31. Java/JAX-RS API design impact

For APIs, avoid presenting arbitrary flexible attributes without a clear contract.

Bad API smell:

{
  "id": "...",
  "attributes": {
    "anything": "goes"
  }
}

Better API envelope:

{
  "id": "...",
  "productFamily": "BROADBAND",
  "attributes": {
    "downloadMbps": 500,
    "uploadMbps": 100,
    "accessTechnology": "fiber"
  },
  "attributeSchemaVersion": "broadband-v3"
}

Even better for critical fields:

{
  "id": "...",
  "productFamily": "BROADBAND",
  "downloadMbps": 500,
  "uploadMbps": 100,
  "accessTechnology": "fiber",
  "additionalAttributes": {
    "installationWindowPreference": "WEEKDAY"
  }
}

The API should communicate what is stable and what is extensible.

32. Code review checklist

When reviewing a PR that adds flexible attributes, ask:

Modelling

• Is this attribute truly flexible or actually core domain data?
• Why is it not a relational column?
• Does it affect pricing, approval, fulfilment, or compliance?
• Does it need historical snapshot semantics?
• Is it tenant-specific, product-specific, or globally meaningful?

Validation

• Where are allowed keys defined?
• Where are types validated?
• Are required attributes enforced?
• Are unknown keys allowed or rejected?
• Is null vs missing key defined?

Query and index

• Will APIs filter/sort/search by this attribute?
• Is there an index for high-volume predicates?
• Is the index selective?
• Are casts safe?
• Was EXPLAIN checked?

Migration

• How will this attribute evolve?
• Is there a backfill?
• Is the migration idempotent?
• Is there validation after migration?
• Can old and new app versions coexist?

MyBatis/JDBC

• Is JSONB mapping handled by a TypeHandler?
• Is dynamic SQL whitelist-protected?
• Are attribute maps leaking everywhere?
• Are tests covering serialization/deserialization?

Reporting

• Is the attribute reportable?
• Does it need projection to a read model?
• Is there a materialized view or ETL path?
• Are reports resilient to missing/malformed values?

Security/privacy

• Can the attribute contain sensitive data?
• Is it redacted from logs?
• Is it included in retention/deletion policy?
• Is it safe to emit in events?

Observability

• Can support engineers query affected rows?
• Are malformed values detectable?
• Are validation failures logged safely?
• Is there a dashboard or diagnostic query for important attributes?

33. Internal verification checklist

Do not assume CSG implementation details. Verify.

Schema and data model

• Which tables store JSONB attributes?
• Which tables use EAV-like key-value structures?
• Which tables store tenant configuration?
• Which catalog/product/quote/order tables contain dynamic attributes?
• Which attributes are core but currently hidden in JSONB/EAV?

Attribute governance

• Is there an attribute definition table?
• Is there a schema registry for dynamic attributes?
• Who owns attribute keys?
• Are attribute names versioned/deprecated?
• Are units documented?

Validation

• Where does attribute validation happen?
• Are unknown keys rejected?
• Are wrong types rejected?
• Are required attributes enforced?
• Are malformed production rows monitored?

MyBatis/JDBC

• Which TypeHandlers handle JSONB?
• Are mappers using Map<String, Object> heavily?
• Are dynamic filters/sorts whitelisted?
• Are JSONB predicates indexed?
• Are mapper tests covering dynamic attributes?

Migration

• Are JSONB key migrations present?
• Are backfills chunked and resumable?
• Are attribute migrations tested on production-like data volume?
• Are old and new keys supported during rolling deployment?
• Are migrations reversible or roll-forward safe?

Reporting and events

• Which reports depend on dynamic attributes?
• Are attributes projected into read models?
• Are JSONB fields emitted via CDC/outbox?
• Are consumers tolerant to schema evolution?
• Are sensitive attributes filtered from events?

Operations

• Are there slow queries on JSONB/EAV tables?
• Are there bloated JSONB-heavy tables?
• Are there incidents caused by bad config/attributes?
• Are support queries documented?
• Are ownership and escalation paths clear?

34. Failure-mode table

35. Practical decision framework

Use this sequence:

1. Is it core to correctness?
   -> relational column.

2. Is it stable but variant-specific?
   -> extension table.

3. Is it dynamic but governed and typed?
   -> typed attribute table or governed JSONB.

4. Is it document-like and mostly retrieved as a unit?
   -> JSONB.

5. Is it external payload for audit/replay?
   -> raw JSONB payload plus canonical mapping.

6. Is it needed heavily in reporting/search?
   -> relational projection/read model/materialized view.

7. Is it arbitrary and ungoverned?
   -> push back; require governance before storing.

Do not start with storage type.

Start with business role and failure mode.

36. Summary

Flexible attributes are necessary in enterprise systems, especially CPQ, catalog, tenant configuration, integration, and dynamic form contexts.

But flexibility is not free.

Every flexible attribute design trades off:

• correctness;
• validation;
• indexing;
• reporting;
• migration;
• observability;
• security;
• API contract clarity;
• event compatibility;
• operational debugging.

The mature posture is:

• model core invariants relationally;
• use JSONB for governed document-like flexibility;
• avoid untyped EAV unless the governance model is strong;
• create projections for search/reporting;
• validate dynamic attributes explicitly;
• version and document attribute definitions;
• treat attribute migrations as real production migrations;
• review flexible data like architecture, not convenience.

In senior backend engineering, flexible schema is not a shortcut.

It is a controlled escape hatch that must be surrounded by validation, ownership, migration discipline, and observability.