Skip to content

0011 — UUID-First Identity And Dual-Key Coexistence

  • Status: ACCEPTED / BINDING
  • Effective date: 2026-08-20
  • Decision owner: Product/architecture
  • Current schema authority: .agents/rules/DB_SCHEMA.md
  • Implementation standard: ../architecture/UUID_IDENTITY_TRANSITION_STANDARD.md
  • Supersedes: any Logistics Workspace assumption that numeric database IDs are valid public/domain identifiers

1. Context

The operating Legacy application uses deployment-local numeric primary and foreign keys. The shared MySQL schema already contains UUID groundwork on many tables, and historical migration work added/backfilled UUID BINARY(16) columns without completing uniqueness, foreign-key shadow columns, public API conversion, or caller retirement. Current Legacy KJDatabase insert handling already assigns UUIDv7 to common single-row inserts; uncovered insert forms and relation UUIDs still require table-family verification.

The Logistics Workspace must not reproduce numeric-ID coupling. At the same time, changing Legacy writers while the Legacy application remains the production fallback creates unnecessary outage risk.

2. Decision

2.1 Canonical identity

The Logistics Workspace uses UUID as the only canonical identity in:

  • URLs and route parameters;
  • request and response payloads;
  • domain/application commands and queries;
  • React state, selection and cache keys;
  • audit/entity references;
  • jobs, events, files and external correlation where an entity identity is required.

Public contracts use explicit names such as uuid, tenantUuid, bookingUuid and userUuid. A bare numeric id, clientId, bookingId or similar field is not a Logistics Workspace public identity.

2.2 UUID version and representation

  • New identifiers are UUIDv7.
  • Application and test composition obtain identifiers through an injected ID-generator port.
  • MySQL stores UUID values as BINARY(16) in RFC/network byte order.
  • API and logs serialize lowercase canonical UUID strings.
  • UUIDv7 values are not byte-swapped. UUID_TO_BIN(..., 1) / BIN_TO_UUID(..., 1) are not used for Logistics Workspace UUIDv7 storage.
  • A previously assigned valid UUID is stable. Existing UUIDs are not regenerated merely to normalize their version.

2.3 Shared Legacy tables

During coexistence, an existing shared table keeps:

ID / numeric foreign keys
= Legacy compatibility aliases

UUID / TENANT_ID / *_UUID
= Logistics Workspace canonical identity and relations

For a migrated relation, the Logistics Workspace writer stores the numeric and UUID values in the same transaction. The request supplies only UUID. The MySQL adapter resolves the numeric alias from the authoritative UUID and tenant scope.

Both values must refer to the same active parent. Mismatch, orphan, cross-tenant reference, one-sided partial write, or duplicate UUID is an error.

2.4 Legacy-origin writes

The 2026-09-14 direct user decision reactivates NS1-009 and includes both Legacy and Logistics Workspace readers/writers in the UUID transition. Preserve business behavior while migrating identity; numeric aliases are temporary in both runtimes. This supersedes the former Legacy-source exclusion, not DB-effect or data-preservation requirements.

Where Legacy can still create or change a migrated row, the existing verified row-UUID insert path remains the first compatibility mechanism. A narrowly scoped database identity bridge may then:

  • assign a missing row UUID only for a separately inventoried writer not covered by the existing insert path;
  • populate a missing UUID foreign key from the numeric relation;
  • populate a required numeric compatibility key from UUID for an approved Logistics Workspace path;
  • reject dual-key mismatch.

The bridge is versioned migration code, contains no business policy, is observable/reconciled, and has an explicit removal gate. Broad generic triggers across all tables and replacement of the existing UUIDv7 insert mechanism are prohibited.

2.5 Logistics Workspace-only tables

A table created exclusively for the Logistics Workspace uses UUID primary/foreign keys from its first migration. It does not add an auto-increment numeric identity merely for consistency with Legacy.

Pure join tables may use a tenant-scoped composite UUID key when they do not need an independent lifecycle identity.

2.6 Tenant identity

  • T_CLIENT.UUID is the canonical tenant identity.
  • Tenant-owned shared rows gain/use TENANT_ID BINARY(16) while retaining numeric CLIENT_ID for Legacy coexistence.
  • Logistics Workspace request context contains tenantUuid; numeric CLIENT_ID is resolved only inside the MySQL compatibility boundary.
  • Same-tenant relation checks remain mandatory even though UUIDs are globally unique.

2.7 Final state

After Legacy caller-zero and data-zero are proven for a table family:

  1. disable/remove the identity bridge;
  2. stop writing numeric aliases;
  3. prove no numeric callers or orphaned/mismatched rows;
  4. remove numeric foreign keys and indexes;
  5. migrate/drop numeric primary keys where approved;
  6. retain UUID-only public, application and storage contracts.

Physical primary-key replacement is a separate table-family cutover. Logical UUID-only Logistics Workspace operation does not wait for that high-risk table rebuild.

3. Rejected Alternatives

Immediate UUID primary-key replacement

Rejected because it would rebuild shared tables and foreign keys while Legacy still depends on numeric identities.

Expose both identifiers to clients

Rejected because it leaks compatibility concerns, creates ambiguous authority and lets callers submit mismatched values.

Mapping table only

Rejected as the default because every relation would require extra indirection and the shared tables already have/need row UUID columns. A mapping table may be used only for an external system whose schema cannot accept a UUID column.

Async reconciliation only

Rejected as the sole coexistence mechanism where Legacy writes must become immediately visible to Logistics Workspace workflows. Reconciliation remains mandatory as a detector/repair path, not the only synchronization contract.

Change all Legacy writers in one unverified cutover

Rejected. Incremental Legacy identity conversion is now required by the 2026-09-14 decision and owned by NS1-009 alongside Logistics Workspace conversion. Do not preserve numeric dependency indefinitely or infer a Production deployment from source authority.

4. Consequences

  • The MySQL adapter temporarily carries dual-key complexity.
  • Table families migrate incrementally, beginning with Platform identity and Booking, then MK records.
  • UUID indexes add storage/write cost and require measured DDL/query planning.
  • Every migrated write needs consistency, duplicate, tenant, rollback and authoritative-readback tests.
  • Numeric IDs may appear in server-only compatibility diagnostics but never as product authority.
  • The final numeric-key removal remains separately authorized and reversible per table family.

5. Required Execution Owners

  • NS1-007: UUID value contract, ID generator, public API boundary and Legacy-ID bridge port.
  • NS1-008: tenant/user/session/access UUID contracts.
  • NS1-009: reactivated on 2026-09-14 for all application-table UUID coverage, Legacy and Logistics Workspace writer/reader conversion, relation reconciliation, approved PK/FK swap, numeric retirement and cross-database identity proof. Source progress is not live DB completion.
  • NS1-010: UUID route/request/response contracts.
  • NS1-013: architecture scans and migration consistency gates.
  • NS1-014: actual C1/C2 UUID-only acceptance.
  • NS1-015: Booking create/update using UUID-only public contracts.
  • NS2-*: MK records and relations follow this decision from first implementation.

6. Effect Boundary

This decision authorizes repository planning and implementation under an active Task. It does not by itself authorize:

  • applying a migration to Local, QA or Production;
  • changing Legacy business behavior unrelated to the authorized UUID migration;
  • dropping numeric columns or constraints;
  • changing route/writer ownership;
  • deploying or cutting over a runtime.

---

Source: new-system/docs/decisions/0011-uuid-first-identity-and-dual-key-coexistence.md — ctc repository · This page is an English translation of the source document. Identifiers, file paths and code symbols are unchanged.