Skip to content

[Feature Request] Tenant-based/partitioned autoincrement #1976

Open
@sajadmh

Description

@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

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Type

    No type

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions