feat: add UUID v7 support

[Zyles]

feat: add UUID v7 support for entity IDs with per-tenant configuration

Add support for UUID v7 as an alternative ID format, configurable per tenant. This allows tenants to choose between nanoid (existing default) and UUID v7 for better compatibility with external systems and time-ordered ID requirements.

All entity ID columns expanded from varchar(12/21) to varchar(36) to accommodate UUID v7 format. Existing nanoid IDs continue to work without migration.

PostgreSQL 18 will have improved support for UUID v7 natively using UUID column type allowing for faster indexing since UUID v7 is time-ordered.

Key Changes

Database Schema (packages/schemas)

• Add tenant_id_config table with unified id_format column
• Expand all entity ID columns to varchar(36):
  • Primary tables: users, organizations, roles, organization_roles, applications
  • 40+ foreign key columns across related tables

Core Libraries (packages/core)

• Add id-format library for tenant-specific ID format management
  • Multi-level caching (in-memory + Redis) for performance
  • Automatic fallback to environment variable defaults
• Add tenant-id-config queries for configuration persistence
• Update entity creation routes to use configured ID format:
  • Users, organizations, roles, organization roles, applications
• Integrate ID format library into tenant context

Shared Utilities (packages/shared)

• Add UUID v7 generation support via uuid package
• Add generateUuidV7() and generateId(format, size?) functions
• Add IdFormat type: 'nanoid' | 'uuidv7'
• Add DEFAULT_ID_FORMAT environment variable (defaults to 'nanoid')
• Consolidate from 4 separate format env vars to single unified setting

[github-actions[bot]]

COMPARE TO master

Total Size Diff :warning: :chart_with_upwards_trend: +69.42 KB

Diff by File

Name	Diff
packages/cli/src/commands/database/seed/index.ts	:chart_with_upwards_trend: +1.13 KB
packages/cli/src/commands/database/seed/tables.ts	:chart_with_upwards_trend: +3.28 KB
packages/cli/src/commands/database/seed/utils.ts	:chart_with_upwards_trend: +1.56 KB
packages/cli/src/commands/install/utils.ts	:chart_with_upwards_trend: +201 Bytes
packages/core/src/libraries/id-format.test.ts	:chart_with_upwards_trend: +7.42 KB
packages/core/src/libraries/id-format.ts	:chart_with_upwards_trend: +3.51 KB
packages/core/src/libraries/user.ts	:chart_with_upwards_trend: +223 Bytes
packages/core/src/queries/tenant-id-config.test.ts	:chart_with_upwards_trend: +3.14 KB
packages/core/src/queries/tenant-id-config.ts	:chart_with_upwards_trend: +1.62 KB
packages/core/src/routes/applications/application.ts	:chart_with_upwards_trend: +30 Bytes
packages/core/src/routes/organization-role/index.ts	:chart_with_upwards_trend: +58 Bytes
packages/core/src/routes/organization/index.ts	:chart_with_upwards_trend: +76 Bytes
packages/core/src/routes/role.ts	:chart_with_upwards_trend: +27 Bytes
packages/core/src/routes/saml-application/index.ts	:chart_with_upwards_trend: +18 Bytes
packages/core/src/tenants/Libraries.ts	:chart_with_upwards_trend: +151 Bytes
packages/core/src/tenants/Queries.ts	:chart_with_upwards_trend: +135 Bytes
packages/core/src/utils/SchemaRouter.ts	:chart_with_upwards_trend: +490 Bytes
packages/schemas/alterations/next-1762400000.1-add-uuid-support-tenant-config.ts	:chart_with_upwards_trend: +3.06 KB
packages/schemas/alterations/next-1762400000.2-add-uuid-support-primary-tables.ts	:chart_with_upwards_trend: +2.07 KB
packages/schemas/alterations/next-1762400000.3-add-uuid-support-user-fks.ts	:warning: :chart_with_upwards_trend: +10.89 KB
packages/schemas/alterations/next-1762400000.4-add-uuid-support-org-role-fks.ts	:warning: :chart_with_upwards_trend: +14.98 KB
packages/schemas/alterations/next-1762400000.5-add-uuid-support-app-fks.ts	:warning: :chart_with_upwards_trend: +10.45 KB
packages/schemas/src/seeds/index.ts	:chart_with_upwards_trend: +39 Bytes
packages/schemas/src/seeds/tenant-id-config.ts	:chart_with_upwards_trend: +705 Bytes
packages/schemas/tables/application_secrets.sql	0 Bytes
packages/schemas/tables/application_sign_in_experiences.sql	0 Bytes
packages/schemas/tables/application_user_consent_organization_resource_scopes.sql	0 Bytes
packages/schemas/tables/application_user_consent_organization_scopes.sql	0 Bytes
packages/schemas/tables/application_user_consent_organizations.sql	0 Bytes
packages/schemas/tables/application_user_consent_resource_scopes.sql	0 Bytes
packages/schemas/tables/application_user_consent_user_scopes.sql	0 Bytes
packages/schemas/tables/applications.sql	0 Bytes
packages/schemas/tables/applications_roles.sql	0 Bytes
packages/schemas/tables/daily_active_users.sql	0 Bytes
packages/schemas/tables/organization_application_relations.sql	0 Bytes
packages/schemas/tables/organization_invitation_role_relations.sql	0 Bytes
packages/schemas/tables/organization_invitations.sql	0 Bytes
packages/schemas/tables/organization_jit_email_domains.sql	0 Bytes
packages/schemas/tables/organization_jit_roles.sql	0 Bytes
packages/schemas/tables/organization_jit_sso_connectors.sql	0 Bytes
packages/schemas/tables/organization_role_application_relations.sql	0 Bytes
packages/schemas/tables/organization_role_resource_scope_relations.sql	0 Bytes
packages/schemas/tables/organization_role_scope_relations.sql	0 Bytes
packages/schemas/tables/organization_role_user_relations.sql	0 Bytes
packages/schemas/tables/organization_roles.sql	0 Bytes
packages/schemas/tables/organization_user_relations.sql	0 Bytes
packages/schemas/tables/organizations.sql	0 Bytes
packages/schemas/tables/personal_access_tokens.sql	0 Bytes
packages/schemas/tables/roles.sql	0 Bytes
packages/schemas/tables/roles_scopes.sql	0 Bytes
packages/schemas/tables/saml_application_configs.sql	0 Bytes
packages/schemas/tables/saml_application_secrets.sql	0 Bytes
packages/schemas/tables/saml_application_sessions.sql	0 Bytes
packages/schemas/tables/secrets.sql	0 Bytes
packages/schemas/tables/sso_connector_idp_initiated_auth_configs.sql	0 Bytes
packages/schemas/tables/subject_tokens.sql	0 Bytes
packages/schemas/tables/tenant_id_config.sql	:chart_with_upwards_trend: +608 Bytes
packages/schemas/tables/user_sso_identities.sql	0 Bytes
packages/schemas/tables/users.sql	0 Bytes
packages/schemas/tables/users_roles.sql	0 Bytes
packages/schemas/tables/verification_records.sql	0 Bytes
packages/schemas/tables/verification_statuses.sql	0 Bytes
packages/shared/package.json	:chart_with_upwards_trend: +23 Bytes
packages/shared/src/node/env/GlobalValues.ts	:chart_with_upwards_trend: +335 Bytes
packages/shared/src/utils/id.test.ts	:chart_with_upwards_trend: +1.85 KB
packages/shared/src/utils/id.ts	:chart_with_upwards_trend: +1.11 KB
pnpm-lock.yaml	:chart_with_upwards_trend: +349 Bytes

[Zyles]

I created this feature because it's the only blocker for us to use logto in our environment. We use UUIDs across our rest APIs. Implementing nanoid would require mapping nanoid to uuid and it feels like a hacky solution.

PostgreSQL 18 will also have improved UUIDv7 support: https://www.thenile.dev/blog/uuidv7

Backwards compatible and selectable if you prefer to use nanoid.

Let me know what you think.

[wangsijie]

@Zyles We’re also planning to upgrade the Postgres version. I’ll get back to you once I have a final answer from the team about this.

[wangsijie]

Hi @Zyles

Thanks for the clear write-up, the idea makes sense. But merging it would change the data structure and can’t be fully isolated, so it doesn’t fit our roadmap.

If you need it, forking and extending might be the better path.

Appreciate the contribution! 🙌

[Zyles]

Hi @Zyles

Thanks for the clear write-up, the idea makes sense. But merging it would change the data structure and can’t be fully isolated, so it doesn’t fit our roadmap.

If you need it, forking and extending might be the better path.

Appreciate the contribution! 🙌

I would prefer not having to fork and patch.

What type of isolation do you have problems with?

I could make it so that this is only applicable on new deployments, so you will need to explicitly set the ID generation on project installation and it will migrate either varchar or UUID for the column. This will keep the old varchar lengths vs UUID type in DB. Nothing really changes in currently running projects.

[Zyles]

This pattern also opens up for using other ID generation libraries if someone else is using something of the 20+ libraries out there since you are just working against one function (generateId).