Skip to content

feat: auto generate unique book no

Problem

book_no values are currently assigned manually, which can lead to gaps and collisions. The system needs a reliable way to automatically generate a unique book number that fills gaps efficiently and scales to large datasets.

Requirement

Introduce a backend endpoint to generate the smallest available positive integer book_no that does not already exist.

Endpoint GET /api/v1/patients/book_no/generate

Expected Behavior

  • Start checking from book_no = 1
  • Return the first missing number in the sequence
  • If no gaps exist, return the next highest number
  • If no records exist, return 1

Examples

  • Existing: [1,2,3,5,6] → Generated: 4
  • Existing: [1,2,3,4,5] → Generated: 6
  • Existing: [1…2456] except 1023 → Generated: 1023

Implementation Notes

  • Must scale to thousands+ records
  • Do NOT assume contiguous or small values
  • Avoid loading all rows into application memory
  • Prefer ordered queries, window functions, or DB-level gap detection
  • Enforce UNIQUE constraint on book_no
  • Handle race conditions gracefully during patient creation

Acceptance Criteria

  • Endpoint returns a non-existing book_no
  • Works efficiently on large datasets
  • No changes to existing patient creation APIs
  • Follows backend architecture and error-handling standards
Edited by Kushal Lagichetty