Identifier strategy
The rule for choosing record IDs, usually compact sequential numbers, globally generated random IDs, or both for different jobs.
See it
What it is
An identifier strategy is the rule for creating stable names for records across tables, APIs, URLs, logs, and imported systems. Sequential bigint identity values are compact, index well, and are easy to inspect. Random UUIDv4 values can be generated anywhere but scatter inserts through a B-tree. UUIDv7, standardized in RFC 9562, keeps global generation while roughly sorting IDs by creation time.
Reach for bigint identity keys inside one database when small indexes and simple operations matter most. Reach for UUIDv7 when several writers need IDs before inserting, datasets will be merged, or public identifiers should not reveal a row count. Some systems use both: a bigint primary key for joins and a unique UUIDv7 public ID for APIs.
Gotcha: an identifier is not authorization, and an unguessable UUID does not make a record private. Never use an editable business value such as email as the permanent ID. Pick one representation at system boundaries, keep IDs immutable, enforce uniqueness in the database, and define how imports preserve or map external IDs before the first migration.
Ask AI for it
Apply one identifier strategy to this PostgreSQL schema. Use bigint GENERATED ALWAYS AS IDENTITY as the internal primary key and add a public_id UUID column containing an application-generated UUIDv7 defined by RFC 9562, with NOT NULL and UNIQUE constraints, for URLs and APIs. Keep both values immutable, reference the bigint from local foreign keys, accept only public_id at external endpoints, and perform authorization after lookup. Provide the migration, TypeScript ID-generation code, indexes, collision handling, and a backfill that assigns IDs in bounded batches without changing existing foreign keys.