5 ms·

Use UUIDv7 and be done with it. It solves your database indexing problem, too.

mort96 1y ago
Except that it leaks the creation timestamp. You really have to think hard about: 1) are IDs of resources created by one person (such as their account, uploaded images, whatever) accessible to another person? and 2) does it matter that you leak the creation time? In some situations, it doesn't matter; creation times are always intentionally leaked anyway. For example, here on HN, I can just click your username and see that you've been a user since 2012, and all your posts are publicly timestamped. That's not sensitive info, so accounts and comments on HN could use UUIDv7. But there are other times where we may not want everyone to see the account creation time of everyone else.
- taylodl 1y ago
  If your system needs to prevent leakage of creation time - typically due to regulatory or privacy requirements - you can use AES-SIV to encrypt UUIDv7 identifiers. All APIs and URLs would expose only the encrypted form of the ID. When performing record lookups, your data tier would decrypt the ID to retrieve the original UUIDv7. This preserves the indexing and sortability benefits of UUIDv7 while mitigating timestamp leakage. This approach is compatible with technologies like GraphQL, but you’ll need to treat the encrypted ID as an opaque string in your schema. Your resolvers will decrypt the ID before querying the database. Just ensure that: - The encryption is deterministic (so the same UUIDv7 always maps to the same encrypted ID). - You handle decryption securely and efficiently in your resolver logic. This pattern works well for systems where external exposure of creation time is undesirable, but internal sortability and uniqueness are still critical.
  - mort96 1y ago
    This works, but now you have a very sensitive secret to keep track of. Losing that secret immediately destroys your database (you can't just create a new one like you would if you e.g lost an API key for a third party service), and leaking the key leaks all creation dates. For lots of use cases, this UUID secret would be the most critical secret of all; and for lots of other use cases, it would be the only secret. Plus, the UUIDs obviously wouldn't be encrypted at rest, so all creation dates would leak if you had any kind of database leak (although you could argue that leaking creation timestamps would be the least of your concerns if you had a DB leak). So it's a valid solution for sure, but certainly not without trade-offs.
    - taylodl 1y ago
      The encryption key in an AES-SIV-based UUIDv7 scheme would indeed become a critical secret, and its compromise could expose creation timestamps. That said, I’d like to clarify a few points: Database Integrity: Losing the encryption key does not destroy the database. The core data remains intact. What’s affected is the ability to interpret the encrypted timestamp metadata. External systems relying on those timestamps may encounter decryption errors if using an outdated key, but this is a deliberate safeguard. Rather than returning incorrect data, the system would fail securely - prompting the external system to re-retrieve the record or fall back to alternative metadata. Key Rotation as a Mitigation Strategy: Regular key rotation is a best practice in cryptographic systems and is especially important here. By rotating keys periodically and tagging external identifiers with a key identifier, we can scope the impact of any key compromise. The key identifier is not embedded in the UUIDv7 itself, nor is it visible externally. Instead, it is used internally - either as metadata or as part of the encryption context in AES-SIV - to ensure the correct key and cipher configuration are applied during decryption. Older keys can be retained for decryption during a grace period and then retired. AES-SIV’s misuse resistance ensures that decryption with the wrong key or context fails securely, preventing silent errors. Risk Context: If the database were compromised, timestamp leakage would likely be among the least critical concerns. Encrypting timestamps helps mitigate passive metadata leakage in lower-risk scenarios (e.g., internal misuse or partial leaks), not full-blown breaches. Use Case Sensitivity: This approach isn’t universally applicable. For systems where timestamp inference is low-risk, the added complexity may not be justified. But for privacy-sensitive environments - where timestamp leakage could enable profiling or behavioral inference - AES-SIV with key rotation offers a strong balance of security and operational resilience. While the encryption key is sensitive, the system can be designed to handle key rotation gracefully, fail securely, and preserve data integrity even in the face of key loss or compromise. As you can tell, I've had to worry about this stuff!
      - mort96 1y ago
        Your comment reads a lot like a ChatGPT-generated counter argument, so I'll be brief, in case I'm wasting my time arguing with a bot. 1. The database remains readable if you lose the key, but all IDs which have been given out to clients will become invalid. This can be anywhere between an inconvenience and a catastrophe. The statement "What’s affected is the ability to interpret the encrypted timestamp metadata" is completely wrong: there is no "encrypted timestamp metadata", what's lost is the ability to translate IDs given out to clients into IDs in the database. Very ChatGPT-like mistake to make. 2. The moment a key is leaked, all IDs which have been handed out to clients so far since the last key rotation suddenly reveals tinestamps. 3. The statement "the database were compromised, timestamp leakage would likely be among the least critical concerns" just repeats what I said in my comment. 4. This point also literally repeats what I wrote as if it's an argument against what I wrote. Please have the decency of writing your arguments yourself, this is ridiculous.

Except that it leaks the creation timestamp. You really have to think hard about: 1) are IDs of resources created by one person (such as their account, uploaded images, whatever) accessible to another person? and 2) does it matter that you leak the creation time? In some situations, it doesn't matter; creation times are always intentionally leaked anyway. For example, here on HN, I can just click your username and see that you've been a user since 2012, and all your posts are publicly timestamped. That's not sensitive info, so accounts and comments on HN could use UUIDv7. But there are other times where we may not want everyone to see the account creation time of everyone else.

taylodl 1y ago
If your system needs to prevent leakage of creation time - typically due to regulatory or privacy requirements - you can use AES-SIV to encrypt UUIDv7 identifiers. All APIs and URLs would expose only the encrypted form of the ID. When performing record lookups, your data tier would decrypt the ID to retrieve the original UUIDv7. This preserves the indexing and sortability benefits of UUIDv7 while mitigating timestamp leakage. This approach is compatible with technologies like GraphQL, but you’ll need to treat the encrypted ID as an opaque string in your schema. Your resolvers will decrypt the ID before querying the database. Just ensure that: - The encryption is deterministic (so the same UUIDv7 always maps to the same encrypted ID). - You handle decryption securely and efficiently in your resolver logic. This pattern works well for systems where external exposure of creation time is undesirable, but internal sortability and uniqueness are still critical.
- mort96 1y ago
  This works, but now you have a very sensitive secret to keep track of. Losing that secret immediately destroys your database (you can't just create a new one like you would if you e.g lost an API key for a third party service), and leaking the key leaks all creation dates. For lots of use cases, this UUID secret would be the most critical secret of all; and for lots of other use cases, it would be the only secret. Plus, the UUIDs obviously wouldn't be encrypted at rest, so all creation dates would leak if you had any kind of database leak (although you could argue that leaking creation timestamps would be the least of your concerns if you had a DB leak). So it's a valid solution for sure, but certainly not without trade-offs.
  - taylodl 1y ago
    The encryption key in an AES-SIV-based UUIDv7 scheme would indeed become a critical secret, and its compromise could expose creation timestamps. That said, I’d like to clarify a few points: Database Integrity: Losing the encryption key does not destroy the database. The core data remains intact. What’s affected is the ability to interpret the encrypted timestamp metadata. External systems relying on those timestamps may encounter decryption errors if using an outdated key, but this is a deliberate safeguard. Rather than returning incorrect data, the system would fail securely - prompting the external system to re-retrieve the record or fall back to alternative metadata. Key Rotation as a Mitigation Strategy: Regular key rotation is a best practice in cryptographic systems and is especially important here. By rotating keys periodically and tagging external identifiers with a key identifier, we can scope the impact of any key compromise. The key identifier is not embedded in the UUIDv7 itself, nor is it visible externally. Instead, it is used internally - either as metadata or as part of the encryption context in AES-SIV - to ensure the correct key and cipher configuration are applied during decryption. Older keys can be retained for decryption during a grace period and then retired. AES-SIV’s misuse resistance ensures that decryption with the wrong key or context fails securely, preventing silent errors. Risk Context: If the database were compromised, timestamp leakage would likely be among the least critical concerns. Encrypting timestamps helps mitigate passive metadata leakage in lower-risk scenarios (e.g., internal misuse or partial leaks), not full-blown breaches. Use Case Sensitivity: This approach isn’t universally applicable. For systems where timestamp inference is low-risk, the added complexity may not be justified. But for privacy-sensitive environments - where timestamp leakage could enable profiling or behavioral inference - AES-SIV with key rotation offers a strong balance of security and operational resilience. While the encryption key is sensitive, the system can be designed to handle key rotation gracefully, fail securely, and preserve data integrity even in the face of key loss or compromise. As you can tell, I've had to worry about this stuff!
    - mort96 1y ago
      Your comment reads a lot like a ChatGPT-generated counter argument, so I'll be brief, in case I'm wasting my time arguing with a bot. 1. The database remains readable if you lose the key, but all IDs which have been given out to clients will become invalid. This can be anywhere between an inconvenience and a catastrophe. The statement "What’s affected is the ability to interpret the encrypted timestamp metadata" is completely wrong: there is no "encrypted timestamp metadata", what's lost is the ability to translate IDs given out to clients into IDs in the database. Very ChatGPT-like mistake to make. 2. The moment a key is leaked, all IDs which have been handed out to clients so far since the last key rotation suddenly reveals tinestamps. 3. The statement "the database were compromised, timestamp leakage would likely be among the least critical concerns" just repeats what I said in my comment. 4. This point also literally repeats what I wrote as if it's an argument against what I wrote. Please have the decency of writing your arguments yourself, this is ridiculous.