Description
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