[proposal] entity-level unique: a business key over more than one field

[delchev]

The problem

Some of the most important invariants an application has span more than one column, and none of
them are expressible:

• one row per (tenant, application) — a tenant cannot be provisioned the same application twice;
• one assignment per (tenant, user);
• one price per (product, priceList, validFrom).

unique today is a field attribute, so it covers a single-column business key (a code, an
external message id) and nothing else. checks: covers row conditions and aggregate guards, not
uniqueness. The result is an entity that is fully modelled except for the one rule that defines what
a row is, and that rule then lives in one of three bad places: a read-then-write in hand-written
code (a race, and one every writer has to remember), a hand-edited constraint the next Generate
knows nothing about, or nowhere at all until duplicates appear in production.

This is squarely at the data-model altitude — the same altitude unique, required and checks
already occupy — and it generalises to nearly every application.

The proposed shape

An entity-level unique block, alongside fields / relations / checks:

entities:
  - name: TenantApplication
    unique:
      - { fields: [tenant, application], message: "This application is already provisioned for the tenant" }
    relations:
      - { name: tenant,      to: Tenant,      kind: manyToOne, required: true }
      - { name: application, to: Application, kind: manyToOne, required: true }
    fields:
      - { name: provisionedOn, type: timestamp }

fields: names fields or to-one relations — a relation contributes the foreign key it stores,
which is what a pair like (tenant, application) actually means. message: is optional and reads
like a checks message.

Expected behaviour

Normative, stated platform-neutrally.

• The declaration produces a composite uniqueness constraint over the named columns, in the
  declared order, enforced by the data layer — not by a check the application performs before
  writing, which is the race the construct exists to remove.
• A write that violates it is rejected as a conflict, reported the way a single-field unique
  violation is, naming the constraint and the authored message when one is given. A caller must be
  able to tell a duplicate apart from a generic failure.
• Parse rules. Every name resolves to a field or a to-one relation of the same entity. A
  cross-model relation is rejected: the consumer stores a projection, so there is no local column to
  constrain. A list of one is rejected too, naming unique: true on that field as the way to say it
  — two spellings for one meaning is how models drift.
• Null members follow the platform's data layer (a row with a null member is not constrained by
  it). Authors who mean "always unique" mark the members required: true; the specification says so
  rather than pretending the semantics are uniform.
• Existing files are unaffected: this adds a key that nothing uses today.

Prior art / workarounds

Enforced in a hand-written API layer with a lookup before the insert — acceptable at low volume,
race-prone by construction, and invisible to every other writer (an import, a message consumer, a
scheduled generate). The alternative seen in practice is a constraint added by hand to the
generated schema, which survives exactly until the next Generate. Both are worse than one line in
the model.

[delchev]

Implementation tracked in the Dirigible reference implementation as eclipse-dirigible/dirigible#6763 — it follows this proposal rather than leading it.