# PRD: Parent-Scoped Sibling-Max Defaults

Status: draft
Issue: https://github.com/pingcap-inc/NexusCRM/issues/1672
Parent: https://github.com/pingcap-inc/NexusCRM/issues/1671
Date: 2026-06-01

## Status Quo Understanding

NexusCRM already has a metadata-driven autonumber engine for formatted
identifiers. That engine is activated by field-level autonumber metadata,
reserves from `_System_AutoNumberCounter`, renders a string value, and treats
the target field as engine-owned on the write path. It is correct for values
such as globally increasing record numbers and prefix-scoped formatted names.

That engine is not the same behavior as a parent-scoped sibling integer
default. The new behavior is a field defaulting primitive: when a child record
is inserted, and a configured numeric order field is omitted, the platform
should read existing siblings under the same parent and write the next integer.
If the caller supplies the order value explicitly, the platform must preserve
that value.

Today, the records insert pipeline applies static field defaults, system
fields, formatted autonumbers, before-save automation, formulas, validation
rules, uniqueness checks, and then persists the filtered logical fields. A
nullable numeric field with no static default and no payload value simply does
not receive a value from the platform. When the physical column permits null,
the stored row remains null.

The UI API Object Info response is cached by object and user. It currently
projects field shape and capability, not request-dependent default values. The
UI API Record Defaults create path already returns a default record payload and
layout for create forms. REST SObject Describe projects Salesforce-shaped field
metadata including static `defaultValue` and `defaultedOnCreate`.

Single-record create performs its final preparation inside the write
transaction. Formatted autonumbers are assigned first, compound fields are
resolved, before-save automation runs, the record is re-normalized, and then
formulas, validation rules, uniqueness checks, and SQL insert see the final
record image. Sibling-max defaulting must join that transactional path rather
than the earlier pre-transaction request preparation path.

The external Salesforce CRM app pack currently carries standard child object
fields that are close to this problem space. Examples include campaign member
status ordering, quote line item display numbering, order item numbering, and
line/order fields on sales child objects. Those are L1 app facts. The platform
change must introduce only the generic L0 mechanism that any app pack can
activate.

## Salesforce Parity Understanding

Salesforce exposes multiple related but distinct concepts:

- Formatted autonumber fields, such as quote and order item numbers, are
  system-generated identifiers. They are described as autonumber/defaulted-on
  create fields and are not equivalent to a caller-overridable sibling order
  integer.
- Salesforce exposes public child ordering fields that are caller-writeable
  when their field metadata marks them createable/updateable. For example, the
  Object Reference describes sales line-item `SortOrder` fields as integer
  ordering fields with create/update capability. The parity signal is the
  caller-writeability of the ordering fact, not Salesforce's internal defaulting
  implementation.
- Salesforce documentation describes the public field metadata and UI API
  contracts, but it does not publish a reusable metadata primitive named
  "sibling max default." NexusCRM therefore should model the behavior as a
  generic platform primitive while keeping the wire-visible outcomes aligned
  with Salesforce-style create defaults and describe metadata.
- UI API Record Defaults is the correct Salesforce-aligned surface for
  parent-aware form prefill. Object Info and REST SObject Describe are metadata
  surfaces and should stay static for standard v63-compatible requests.
- The issue's request for dynamic `defaultValue` on object-info/describe needs
  a contract correction. This PRD does not change standard UI API Object Info
  or REST SObject Describe. If the team later wants dynamic metadata defaults,
  that should be a separate Nexus-extension PRD with explicit cache semantics.

Reference material:

- Salesforce Object Reference for the Salesforce Platform:
  https://resources.docs.salesforce.com/latest/latest/en-us/sfdc/pdf/object_reference.pdf
- Salesforce LWC `getRecordCreateDefaults` documentation:
  https://developer.salesforce.com/docs/platform/lwc/guide/reference-wire-adapters-create-record-values
- NexusCRM Salesforce v63 public API contract:
  `docs/design/salesforce-v63-api-contract.md`

## Problem Statement

Pack authors need a metadata-driven way to say: "when this numeric child field
is omitted during create, default it to one greater than the maximum value of
the same field among records with the same parent." Without that primitive,
child records that rely on deterministic sibling ordering store null unless
every caller manually computes and supplies the next value.

That makes forms, standard actions, flows, imports, API clients, and future app
packs duplicate the same ordering policy. Duplication creates races, inconsistent
behavior between entry points, and pressure to add object-specific branches to
generic records code. Those branches would violate the L0 boundary because the
records engine would learn application object and field names.

## Solution

Add a generic field metadata property for parent-scoped sibling-max defaulting.
The property names the parent lookup field that defines the sibling group for
the target numeric field. At create time, when the target field is absent from
the normalized record image, the records engine computes:

`COALESCE(MAX(target_field WHERE parent_field = parent_value), 0) + 1`

The computed integer is written into the record image before final validation
and before the SQL insert. When the caller or before-save automation supplies
any explicit value, including `0` or an explicit null that normal validation
permits, the engine leaves it unchanged.

This primitive is intentionally separate from formatted autonumber assignment.
Formatted autonumber fields are engine-owned identifiers; sibling-max defaulted
fields are caller-overridable ordering fields. They can share low-level SQL
identifier validation helpers, but they must not share write-protection
semantics.

Parent-scoped defaults must also be visible to create forms. The platform should
compute the same value for UI API Record Defaults when the request includes the
configured parent field value. REST SObject Describe remains a static metadata
surface, but it must mark sibling-max targets as `defaultedOnCreate=true` so
required-field validation and public describe agree that omitted create is
server-defaulted. Dynamic parent-specific `defaultValue` on standard Object Info
or REST SObject Describe metadata surfaces is out of scope for this PRD.

## User Stories

1. As an app-pack author, I want to declare a parent-scoped numeric default in
   field metadata, so that I can activate sibling ordering without writing Go
   code.
2. As an app-pack author, I want the declaration to name the parent lookup
   field, so that the same child object can have different sibling groups for
   different parent relationships when future packs need that shape.
3. As a platform engineer, I want the records engine to compute the value
   generically, so that L0 does not branch on standard object or field names.
4. As an API client, I want omitted ordering fields to be defaulted server-side,
   so that I do not have to race other clients by calculating the next value
   myself.
5. As an API client, I want an explicit supplied value to be preserved, so that
   imports and reorder workflows can intentionally place a child row.
6. As a UI user creating a child record from a parent, I want the create-default
   response to show the next order value, so that the form can prefill the value
   without encoding object-specific policy.
7. As a UI user, I want the saved record to use the server-computed value, so
   that stale form defaults do not become the source of truth.
8. As an admin, I want formulas, validation rules, uniqueness checks, and the
   final insert to see the same sibling-max defaulted value, so that business
   rules run against the persisted record image.
9. As an app-pack installer, I want invalid metadata combinations to fail during
   pack validation, so that a bad declaration does not become a runtime insert
   failure.
10. As a platform operator, I want the migration to be idempotent, so that clean
    databases and upgraded databases converge on the same schema.
11. As a platform engineer, I want the resolver to validate SQL identifiers
    through existing whitelist rules, so that metadata cannot become SQL
    injection text.
12. As a platform engineer, I want the resolver to run inside the caller's
    insert transaction, so that the computed value sees the same transactional
    state as the rest of the insert pipeline.
13. As a platform engineer, I want null parent values to produce a structured
    validation error, so that misconfigured or incomplete creates do not store
    null silently.
14. As a pack author, I want the defaulting primitive to work for any object
    with a parent lookup and numeric target field, so that CRM and non-CRM packs
    can both use it.
15. As a test author, I want the behavior verified with generic probe objects,
    so that the tests prove the L0 substrate rather than CRM-specific facts.
16. As a frontend engineer, I want parent-context create defaults exposed through
    a stable API contract, so that forms can prefill without encoding field-name
    policy in React components.
17. As a Salesforce-compatible API consumer, I want describe and object-info
    fields to continue matching v63 semantics when no parent context is supplied,
    so that existing clients do not observe dynamic defaults unexpectedly.
18. As a future app-pack author, I want the behavior to compose with static
    defaults predictably, so that one field does not have two active defaulting
    policies.
19. As a bulk import caller, I want omitted sibling-max fields to fail closed
    until bulk support exists, so that bulk operations never silently store null
    for a field whose metadata says the platform should default it.
20. As a reviewer, I want the PR to include a clean scan for hardcoded standard
    object and field literals in engine code, so that the public-core boundary
    remains intact.

## Implementation Decisions

- Introduce a dedicated sibling-max default metadata property instead of
  treating this as a new formatted-autonumber scope. The canonical property
  should be named around the behavior, for example `sibling_max_parent_field`.
  This avoids reusing formatted-autonumber ownership semantics that reject
  explicit caller values.
- Store the property on the field metadata row for the target field. The value
  is the API name of the parent lookup field on the same object.
- Surface the property through the authoritative field metadata path used by the
  records engine. The implementation must update the system table manifest,
  generated constants and models, the shared field metadata model, metadata
  repository scan/write paths, pack field-spec parsing, pack apply, pack verify,
  and metadata cache invalidation so every caller reads one source of truth.
- The target field must use the explicit integer logical/storage shape. In v1,
  only field types `int` and `integer` are valid sibling-max targets. These map
  through the existing SQL resolver to integer-backed storage and describe as
  integer fields.
- `double`, `number`, `currency`, and `percent` are not valid sibling-max
  targets in v1, even when a caller supplies `Scale: 0`. NexusCRM currently
  does not persist `Precision`/`Scale` on `_System_Field`, and the normal
  `double` logical type resolves to fractional `DECIMAL(65,30)` storage. Treating
  a bare `double` as "integer-compatible" would let fractional values persist
  and later be truncated by the `MAX + 1` resolver.
- First-class zero-scale decimal support is a separate substrate change. It
  must add persisted precision/scale metadata to the pack, metadata CRUD,
  schema persistence, describe, validation, and runtime paths before
  sibling-max can accept decimal-backed targets.
- Runtime must still defend against drift. After reading `MAX(target)`, the
  resolver must reject non-finite or fractional aggregate values instead of
  casting them to `int64`, even though valid v1 integer-backed fields should not
  produce such values.
- The parent field must exist on the same object, must be a reference-like
  parent field, and must be persistable. A metadata declaration that points to a
  missing, non-reference, polymorphic-only, virtual, or same target field is
  invalid.
- A field must not declare both a static default and a sibling-max default. The
  sibling-max default is request-data-dependent and must be the only metadata
  defaulting policy for that field.
- A field must not declare both formatted-autonumber metadata and sibling-max
  default metadata. Formatted autonumber assignment owns identifiers; sibling
  max defaulting preserves explicit caller values.
- The create pipeline computes sibling-max defaults inside the transactional
  create lifecycle after formatted autonumber assignment, compound-field
  resolution, before-save automation, and post-automation normalization, but
  before formula snapshotting, validation rules, uniqueness checks, and SQL
  insert. This avoids computing a value for one parent and then persisting the
  row under a different parent if automation changes the lookup.
- Because before-save automation can repoint the parent lookup, the records
  pipeline must re-run the existing non-polymorphic lookup access/existence gate
  on the final pre-save image before sibling-max defaulting reads that parent
  value.
- Effective schema decorators must treat a non-blank `SiblingMaxParentField` as
  a `DefaultedOnCreate` contract. This is not a second source of truth: the
  sibling-max metadata property remains the authored fact, and the records layer
  remains the only owner of the dynamic `MAX + 1` value. The decorator only
  propagates the static create-default capability to REST required-field
  validation and SObject Describe.
- Before-save automation does not see a platform-synthesized sibling-max value
  in the first version. If automation needs to control the order value, it can
  set the target field explicitly; that value is then preserved.
- The resolver must query through the active transaction executor when one is
  present. It must not open a second transaction or read outside the write
  transaction.
- Empty sibling sets produce `1`. Existing sibling values produce `MAX + 1`.
  Null target values on existing siblings are ignored by SQL `MAX`.
- The `MAX + 1` computation is an integer computation. If a drifted physical
  column, stale metadata row, or legacy import makes `MAX(target)` fractional,
  NaN, or infinite, the create/default preview fails closed with field-integrity
  diagnostics instead of truncating.
- Defaulting applies only when the target field is absent from the normalized
  record image after normal create/FLS pruning and before final validation.
  Explicit `0` is a value and is preserved. Explicit `null`, empty strings, and
  malformed numeric strings are not silently defaulted; they proceed through the
  normal schema validation and nillability rules.
- If the parent value is missing or blank when the target field is absent and
  needs defaulting, return a neutral field-integrity error that the REST
  boundary maps to `FIELD_INTEGRITY_EXCEPTION`. The `fields` array should include
  both the target field and the configured parent field.
- Explicit caller values are allowed only when normal createability/FLS permits
  the field. Omitted system defaults are written by the platform after payload
  pruning and do not make the field user-writeable.
- For UI API Record Defaults, support generic parent context parameters named
  `parentFieldApiName` and `parentRecordId`. When those parameters match a
  configured sibling-max parent field, return the computed value in the default
  record field value. The preview must be user-aware: the target field and
  parent lookup field must be createable for the caller, and the parent record
  must pass the same non-polymorphic lookup access/existence gate used by
  single-record insert before the aggregate query runs. Because the computed
  value is derived from live sibling data, the target field must also be
  readable for the caller before the aggregate query runs. If FLS prevents the
  caller from creating either field, or from reading the target field, the
  preview is suppressed instead of leaking a data-derived value.
- Frontend cache keys for record defaults must include both parent context
  parameters.
- UI clients must treat sibling-max values returned by Record Defaults as
  display hints unless the user intentionally edits the field. Create forms
  must omit the target field from the save payload when the displayed
  sibling-max value is unchanged, so the server recomputes inside the write
  transaction. If the user edits the value, the form sends it as an explicit
  placement value.
- Standard-action create preview responses must carry sibling-max hint
  provenance for previewed parent images, because those routes can derive the
  target parent from action input rather than URL parent context. The final
  submit path must omit unchanged preview-owned sibling-max hints just like
  Record Defaults hints. The provenance must come from the records preview
  write path that actually populated the sibling-max value, not from final field
  presence, so action/Flow-authored explicit ordering values are preserved as
  explicit placements.
- No-DML Flow and standard-action create previews must make earlier same-run
  preview creates visible to later sibling-max computations through the
  existing synthetic preview-record registry. The preview resolver computes
  `MAX(persisted siblings, prior synthetic siblings for the same object and
  parent) + 1`, ignores nil synthetic target values like SQL `MAX` ignores
  `NULL`, rejects fractional/non-finite synthetic values like persisted drift,
  and never looks ahead to future preview creates.
- When related-list create routes carry explicit parent context, frontend record
  defaults must disambiguate the parent lookup against that context instead of
  picking the first reference prefill. If `_parentObjectApiName` and
  `_parentRecordId` are present, the UI should pass a parent field only when a
  single-target reference/master-detail field has the same query value and
  references that parent object. If no such field matches, suppress the parent
  context rather than falling back to an unrelated lookup such as `OwnerId`.
- Standard UI API Object Info and REST SObject Describe do not emit dynamic
  parent-specific defaults in this PRD. REST SObject Describe should still
  report `defaultedOnCreate=true` for sibling-max targets because omitted create
  is server-defaulted.
- Pack apply and metadata authoring must validate declarations before records
  runtime sees them. Create-field, batch-create-field, object-create, and
  field-update paths must all reject missing, non-reference, or polymorphic
  parent fields. Field update must support merge/clear semantics for the
  property. Runtime still validates defensively because metadata can drift or be
  written by older tools.
- Runtime validation runs before explicit-value preservation, so a drifted field
  that combines static defaults or non-writable target metadata with
  sibling-max metadata cannot silently bypass the resolver by making the target
  appear present.
- Pack payload activation belongs in the external L1 app pack. The platform PR
  should add only generic substrate and generic test packs.
- Bulk insert support is explicitly deferred, but bulk must fail closed. Until
  batch sibling-max assignment is implemented, a bulk create that omits a
  configured sibling-max field must return a structured field-level error for
  that row or request. Bulk rows that provide explicit values for every
  configured sibling-max field can proceed through normal validation.

## Metadata/Schema Changes

- Add a nullable field-metadata column for the sibling-max parent field. The
  canonical first-version name is `sibling_max_parent_field`.
- Add the column to the system table manifest and regenerate shared constants
  and generated table models so metadata code can refer to one canonical column
  name.
- Add the property to the shared field metadata model so the metadata service,
  records engine, UI API Record Defaults service, pack apply, and verification
  all observe the same field fact.
- Extend metadata repository scanning and field metadata persistence so the
  property round-trips through normal schema load, field create/update, and
  cache rebuild paths.
- Extend source-backed pack field specs to parse and apply the property from
  pack payloads. Pack validation must reject invalid declarations before they
  reach runtime.
- Pack field specs do not gain `precision` or `scale` in this PR. Pack authors
  activate sibling-max by declaring the target field as `type: "integer"` or
  `type: "int"` plus `siblingMaxParentField`. A pack field with
  `type: "double"` and `siblingMaxParentField` is invalid, because the pack
  authoring surface cannot express the zero-scale decimal contract required to
  make `double` safe for sibling-max.
- Extend pack and L0 verification to detect missing columns, invalid parent
  references, invalid target field types, and forbidden combinations with
  static defaults or formatted-autonumber metadata.
- Add an idempotent migration for existing databases and verify it against a
  clean database and a database where the column already exists.

## Testing Decisions

- Test external behavior first. The primary test should create a generic parent
  object and generic child object, declare a sibling-max default on the child,
  then create records through the records service and assert persisted values.
- Use generic probe object and field names, not CRM object names. The test must
  prove multi-app-neutral substrate behavior.
- Cover empty sibling set defaulting to `1`.
- Cover multiple inserts under the same parent producing `1`, `2`, and `3` when
  the field is omitted.
- Cover separate parent records maintaining independent sibling sequences.
- Cover explicit supplied value preservation, followed by an omitted insert that
  uses `MAX + 1`.
- Cover explicit `0`, explicit `null`, empty string, and absent target values so
  the absent-only defaulting contract is pinned.
- Cover missing parent value returning `FIELD_INTEGRITY_EXCEPTION` rather than
  storing null.
- Cover before-save automation changing the parent lookup before sibling-max
  computation; the inserted child must use the final parent value.
- Cover before-save automation setting the target field explicitly; the
  resolver must preserve the automation value.
- Cover invalid metadata combinations at pack/schema validation time: missing
  parent field, non-reference parent field, non-numeric target field,
  formatted-autonumber plus sibling-max, and static default plus sibling-max.
- Cover UI API Record Defaults behavior with parent context present and absent,
  including frontend/server cache keys.
- Cover UI form submission semantics: unchanged sibling-max prefill omitted
  from create payloads and recomputed server-side; user-edited value submitted
  and preserved.
- Cover Object Info and REST SObject Describe staying static for standard v63
  requests: no dynamic parent-specific `defaultValue`, and REST Describe reports
  static `defaultedOnCreate=true` for sibling-max targets.
- Cover bulk fail-closed behavior for omitted sibling-max target fields and
  explicit-value bulk pass-through.
- Reuse existing records insert integration seams where possible. Add lower-level
  resolver unit tests only for edge cases that are expensive to express through
  the records service, such as identifier validation and nil database/transaction
  plumbing.
- Regression commands should include the autonumber package, records package,
  UI API/rest surfaces touched by the default-value hint, and pack validation
  tests. Backend commands that touch control-plane packs must set the official
  app-packs root to the external checkout.

## Out of Scope

- Bulk insert sibling-max assignment. Bulk must fail closed for omitted
  sibling-max target fields until that support lands.
- A general expression/default language.
- Admin UI for authoring this property.
- Reordering existing siblings.
- Enforcing uniqueness of sibling order values.
- Pessimistic locking or gap-free sequence guarantees under concurrent inserts.
- Object-specific activation in platform code.
- Moving current Salesforce CRM pack payloads between repositories.
- Changing existing formatted-autonumber semantics.
- Guaranteeing that a UI prefilled default remains the saved value if another
  sibling is inserted before save.

## Further Notes

Concurrency is intentionally optimistic. Two concurrent inserts under the same
parent can compute the same next value. This matches the issue's requested
behavior and keeps the primitive a defaulting mechanism rather than a durable
sequence allocator. If an app needs strict uniqueness, it should add metadata
constraints or validation rules separately.

The canonical term for this feature is "sibling-max default." "Per-parent
autonumber" is useful shorthand, but it is misleading in NexusCRM because
formatted autonumber fields are currently engine-owned and caller write
protected. The design should use "sibling-max default" in metadata validation,
errors, tests, and documentation.

Salesforce parity should be stated as outcome parity, not implementation parity.
Salesforce exposes child ordering fields and create-default metadata; it does
not document the internal SQL algorithm. NexusCRM's `MAX + 1` resolver is the
generic substrate chosen to produce the required outcome without baking CRM
facts into L0.

No new root `CONTEXT.md` is proposed in this PRD. The repository currently uses
architecture and design documents rather than the CONTEXT glossary convention.
If the team adopts that convention later, "sibling-max default" should be added
as a domain term there.

## Issue Amendment

The issue acceptance criterion that says UI API describe/object-info should emit
a computed parent-context `defaultValue` should be amended before implementation.
For v63-compatible behavior, the required UI contract should be:

- UI API Record Defaults emits the computed value in the default record when
  `parentFieldApiName` and `parentRecordId` are present and match the metadata.
- Standard UI API Object Info and REST SObject Describe remain static. REST
  SObject Describe reports sibling-max targets as `defaultedOnCreate=true`, but
  does not emit a dynamic parent-specific `defaultValue`.
- Any dynamic Object Info or REST Describe behavior should be handled by a
  separate Nexus-extension PRD, not this one.