[Feature Request] Tenant-based/partitioned autoincrement

[sajadmh]

Is your feature request related to a problem? Please describe.

You can define @unique() @default(autoincrement()) on a field, but you cannot autoincrement with a constraint like tenantId or organizationId.

• This is useful for things like accounting invoices that need a unique id (database) and a human-readable referenceId (UI) for users to reference.
• It's also useful for jobs, e.g. Workday's "job requisition ID" (JR-ID) that candidates and recruiters can reference in a readable way, e.g. "J-012345".

Companies often require an internal increment to know which invoice, job, opening they might be on and what the next increment will be.

Describe the solution you'd like
Create a Zenstack feature that handles autoincrement on a field, taking a partition to autoincrement against. Proposed syntax:

model Invoice {
  id          String  @id @default(uuid())
  tenantId    String

  // proposal 1:
  referenceId Int     @autoincrement(partition: tenantId)
  // or proposal 2:
  referenceId Int     @autoincrement(by: tenantId)
  
  @@unique([referenceId, tenantId])
}

Simply increment to the last number, even if records are deleted in between (e.g. 1-2-[deleted]-4-5 increment to 6).

Describe alternatives you've considered

• Originally I created a postgres sequence + nextval() but this requires setup for each tenant.
• Database triggers
• Dedicated sequencing table
• Application-level sequence management (hacky findFirst... orderBy referenceId desc... return last result, increment by +1...)

References

https://stackoverflow.com/questions/41336686/auto-increment-considering-tenant-id
https://stackoverflow.com/questions/51825936/auto-increment-ids-starting-at-1-per-tenant-in-a-single-database