Skip to content

UUID Identity Transition Standard

  • Status: CANONICAL / BINDING
  • Effective date: 2026-08-20
  • Decision: ../decisions/0011-uuid-first-identity-and-dual-key-coexistence.md
  • Current schema authority: .agents/rules/DB_SCHEMA.md
  • Quality gates: FOUNDATION_QUALITY_GATES.md
  • Migration template: ../templates/UUID_IDENTITY_MIGRATION_TEMPLATE.md

0. Current scope correction — 2026-09-14

The direct user decision reactivates existing NS1-009 for Legacy and Logistics Workspace. Both runtimes must ultimately stop depending on numeric row, tenant and relation identities; Legacy-source exclusion is no longer the UUID migration policy. This does not retire Legacy itself, change unrelated business behavior, authorize Production effects or weaken existing UUIDs.

First inventory all application-owned base tables visible through the exact target's information_schema, including non-T_* names. Confirm metadata privileges and reconcile that inventory with callers before calling it complete. Add only missing row UUID columns; preserve valid existing values, column contracts, constraints and history. Cover both runtimes' INSERTs before completing backfill/NOT NULL/unique work. Then migrate relation UUIDs and readers/writers, prove different numeric aliases preserve one identity across DBs, swap PK/FKs, and finally remove numeric aliases. Each table-family wave has its own target/backup/readback and rollback evidence.

The existing NS1-009 Task, manifest and EXECUTION row own all remaining stages. A completed source patch or historical Company/Booking fixture is not completion of this all-table outcome. New source slices admit exact paths; no broad all-table DDL or generic trigger is authorized.

1. Objective

The Logistics Workspace must be UUID-only at every product and application boundary while coexisting safely with a Legacy schema and runtime that still use numeric identifiers.

The transition is complete only when:

  • Logistics Workspace callers never need or submit numeric entity IDs;
  • every migrated row has one stable canonical UUID;
  • every migrated relation has a UUID reference;
  • dual-key values are transactionally consistent during coexistence;
  • Legacy-origin writes remain visible without changing Legacy business code;
  • numeric callers/data are proven zero before physical removal.

2. Terminology

Term Meaning
canonical UUID stable entity identity used by Logistics Workspace contracts
legacy numeric alias current auto-increment ID or numeric foreign key retained only for Legacy compatibility
row UUID the entity row's UUID BINARY(16)
tenant UUID T_CLIENT.UUID, stored on tenant-owned rows as TENANT_ID
shadow UUID FK coexistence column such as BOOKING_UUID beside BOOKING_ID
identity bridge temporary DB/MySQL-adapter mechanism that maps and validates numeric/UUID values
logical UUID-only no numeric identifier in product/domain/application contracts
physical UUID-only numeric PK/FK columns and compatibility bridge removed

3. Identity Types

@ctc/platform-core owns infrastructure-free branded values and ports:

type Uuid = string & { readonly __brand: "Uuid" };
type UuidV7 = Uuid & { readonly __version: 7 };
type TenantUuid = Uuid & { readonly __entity: "Tenant" };
type EntityUuid<T extends string> = Uuid & { readonly __entity: T };

interface IdGenerator {
  next(): UuidV7;
}

Rules:

  • parsing validates canonical UUID syntax;
  • generation is injected, never hidden in domain/application code;
  • tests use deterministic generators;
  • UUID is an identifier, not authorization or a secret;
  • LegacyNumericId may exist only inside @ctc/db-mysql compatibility code and is not exported through business/public contracts.

4. UUID Generation And Storage

Generation

  • Generate UUIDv7 in application/bootstrap infrastructure.
  • Use a vetted RFC 9562 implementation or a repository-owned implementation with version, variant, uniqueness and clock-regression tests. Monotonic behavior within one millisecond is optional and must not be claimed unless implemented and tested.
  • Do not use crypto.randomUUID() for new entity identity because it generates UUIDv4.
  • Do not use MySQL UUID() as the canonical Logistics Workspace generator.

Storage

UUID BINARY(16) NOT NULL
  • Store RFC/network byte order.
  • For UUIDv7, do not use MySQL time-part swapping.
  • Convert at the DB adapter boundary.
  • Application/domain types never contain Buffer, RowDataPacket or binary database representation.
  • Serialize lowercase 8-4-4-4-12 UUID strings.

Stability

  • Never regenerate an assigned UUID because a row moved, was restored, or came from Legacy.
  • Existing valid UUID values remain stable even if created before this decision.
  • Nil/zero UUID is invalid for persisted identities.

5. Table Classes

5.1 Existing shared table

During coexistence:

ID          BIGINT/INT ...       -- Legacy physical PK/alias
UUID        BINARY(16) NOT NULL  -- canonical row identity
CLIENT_ID   INT ...              -- Legacy tenant alias where applicable
TENANT_ID   BINARY(16) NOT NULL  -- canonical tenant identity

Required indexes after backfill:

UNIQUE(UUID)
UNIQUE(TENANT_ID, UUID) for tenant-owned parents used by composite UUID FKs
INDEX(TENANT_ID, DELETED_AT, UUID) or workload-specific equivalent

The exact index order is based on measured access paths, not copied mechanically.

5.2 Logistics Workspace-only table

Default:

UUID        BINARY(16) PRIMARY KEY
TENANT_ID   BINARY(16) NOT NULL
CREATED_AT  DATETIME(6) NOT NULL
UPDATED_AT  DATETIME(6) NOT NULL
DELETED_AT  DATETIME(6) NULL

No numeric identity is added unless an approved external compatibility requirement exists.

5.3 Pure join table

Use:

(TENANT_ID, LEFT_UUID, RIGHT_UUID)

as a composite business key when the relation has no independent lifecycle. The 2026-09-14 all-table direction also requires a stable row UUID for application-owned join base tables; retain the composite business uniqueness rather than treating a surrogate UUID as duplicate-relation prevention.

5.4 Global table

A global catalog table omits TENANT_ID only when the current module registry explicitly classifies it as global.

6. Column Naming

Meaning Coexistence column
row identity UUID
tenant identity TENANT_ID
relation to Booking BOOKING_UUID
relation to User USER_UUID
pickup Booking relation PICKUP_BOOKING_UUID
return Booking relation RETURN_BOOKING_UUID
polymorphic relation <NAME>_TYPE + <NAME>_UUID

Rules:

  • Keep current numeric names unchanged for Legacy.
  • Add UUID relation columns with exact semantic names.
  • Do not create generic PARENT_UUID, REF_UUID or ENTITY_UUID unless the relation is genuinely generic and type-qualified.
  • Provider/business reference numbers are not converted merely because their names end in _ID.

7. Public And Application Contract

Logistics Workspace routes and payloads:

/bookings/{bookingUuid}
?cursor=<opaque UUID-based cursor>

{
  "uuid": "...",
  "tenantUuid": "...",
  "billingCompanyUuid": "...",
  "pickupTerminalUuid": "..."
}

Forbidden:

/bookings/57290
{ "ID": 57290 }
{ "bookingId": 57290 }
{ "clientId": 2 }
{ "uuid": "...", "id": 57290 }

A compatibility adapter may accept a numeric value only from an explicitly classified Legacy transport surface. It converts immediately to UUID and does not pass the numeric value into the application handler.

8. Tenant Identity

For a tenant-owned row:

CLIENT_ID
= Legacy deployment-local alias

TENANT_ID
= T_CLIENT.UUID and Logistics Workspace authority

Every Logistics Workspace query/write:

  1. obtains tenantUuid from authenticated request context;
  2. scopes SQL by TENANT_ID;
  3. validates referenced parent TENANT_ID;
  4. optionally resolves CLIENT_ID inside the compatibility adapter;
  5. never trusts tenant identity from request payload.

During the first migration stage, a query may temporarily require both predicates:

WHERE TENANT_ID = ?
  AND CLIENT_ID = ?

The numeric predicate is a compatibility defense, not product authority.

9. Dual-Key Relation Contract

For current numeric BOOKING_ID, add BOOKING_UUID.

Logistics Workspace write algorithm

  1. Parse/authorize bookingUuid and tenantUuid.
  2. Resolve the active parent by (TENANT_ID, UUID).
  3. Obtain its internal numeric alias.
  4. Write BOOKING_ID and BOOKING_UUID in one transaction.
  5. Reread by UUID.
  6. Fail on missing, cross-tenant, deleted, duplicate, mismatch or affected-row conflict.

The client never submits BOOKING_ID.

Nullability

For an optional relation:

numeric null/legacy-zero and UUID null
= no relation

numeric present and UUID present
= relation

only one side present
= transition defect unless a bridge is actively repairing a Legacy-origin write

Legacy zero sentinels are normalized at the adapter/bridge boundary; they do not become valid UUID identities.

Mismatch handling

If both values exist but resolve to different parents, fail closed and emit a reconciliation incident. Do not silently choose one side.

10. Legacy-Origin Compatibility Bridge

A bridge is allowed only for a named table/relation during an approved coexistence period.

Existing row-UUID path

Current Legacy KJDatabase handling already injects UUIDv7 into covered single-row inserts. The transition preserves that behavior and inventories uncovered raw, bulk and INSERT ... SELECT writers. It does not add a second generic row-UUID mechanism.

Responsibilities

  • assign a missing row UUID only for an explicitly inventoried Legacy writer not covered by the existing path;
  • fill shadow UUID FK from the numeric parent;
  • optionally fill numeric alias from UUID for an approved Logistics Workspace DB path;
  • enforce same-tenant relation;
  • reject mismatch.

Restrictions

  • no business status/validation policy;
  • no provider call;
  • no cross-module side effect;
  • no generic trigger generator applied to every table without review;
  • no silent error swallowing;
  • no permanent ownership.

Implementation preference

Use the existing UUIDv7 insert path for covered row creation. Use explicit table-family migrations and named BEFORE INSERT / BEFORE UPDATE relation bridges only where real-time Legacy visibility requires shadow UUID population or mismatch validation. A trigger that generates row UUIDs requires separate binlog/replication and UUIDv7 verification. Use reconciliation for detection/repair and as the fallback when a trigger is not safe.

Each bridge records:

  • exact table and columns;
  • writer classes it supports;
  • performance/locking impact;
  • metrics and mismatch query;
  • removal Task and caller-zero condition.

11. Backfill And Constraint Sequence

For each table family:

inventory
-> add nullable row/tenant/relation UUID columns
-> backfill in bounded batches
-> verify null/duplicate/orphan/mismatch counts
-> add indexes/unique keys
-> install dual-key writer/bridge
-> observe
-> make required UUID columns NOT NULL
-> add UUID FK constraints where safe
-> switch Logistics Workspace reads to UUID
-> prove numeric caller-zero
-> retire numeric aliases

Do not combine all table families into one broad production DDL.

Backfill rules

  • generate stable UUIDv7 only for rows with no valid UUID;
  • never replace an existing non-null valid UUID;
  • populate TENANT_ID from the exact active T_CLIENT.UUID;
  • populate shadow UUID FKs by tenant-scoped joins;
  • batch by current primary key or another stable cursor;
  • record row counts before/after;
  • stop on duplicate/orphan/cross-tenant ambiguity.

12. Constraints And Indexes

Required after data is clean:

  • row UUID unique;
  • tenant UUID non-null for tenant-owned active rows;
  • shadow UUID FK indexed;
  • same-tenant composite reference where feasible;
  • query-specific index for actual sort/filter/pagination;
  • no duplicate unique constraint without measured purpose.

Physical FK constraints are added after backfill and compatibility writer verification. Application validation remains required even when a DB FK exists.

Soft-delete behavior is explicit. DELETED_AT does not automatically make uniqueness or FK semantics correct.

13. Query And Pagination Rules

  • Logistics Workspace repositories read and reread by UUID.
  • Numeric IDs may be selected only inside the compatibility adapter when needed for dual-write.
  • Public results omit numeric IDs.
  • Sort/pagination does not rely on auto-increment ID.
  • Use deterministic cursor ordering such as (createdAt, uuid) or a business sort plus UUID tie-breaker.
  • UUIDv7 order is useful but does not replace explicit business ordering or timestamp fields.

14. Logistics Workspace Write Example

Create Booking command
  tenantUuid
  generated bookingUuid
  billingCompanyUuid
  pickupTerminalUuid
  returnTerminalUuid

MySQL adapter
  resolve tenant/client alias
  resolve company/terminal numeric aliases
  INSERT UUID + ID-compatible foreign keys atomically
  authoritative SELECT by TENANT_ID + UUID

The returned Booking contains only UUID relations.

15. Reconciliation

Every migrated relation provides read-only queries/metrics for:

  • row UUID null;
  • nil/invalid UUID;
  • duplicate UUID;
  • tenant UUID missing/mismatch;
  • numeric parent missing;
  • UUID parent missing;
  • numeric/UUID parents disagree;
  • cross-tenant relation;
  • deleted-parent relation where prohibited;
  • bridge-trigger error count;
  • legacy-origin rows awaiting repair.

Acceptance requires zero unexplained defects. Repair is idempotent and logs exact before/after identity without exposing secrets.

16. Security And Privacy

  • UUID does not grant access.
  • Every read/write still enforces tenant, capability and record policy.
  • Do not use UUID as a password-reset/session secret.
  • Avoid exposing server-only numeric aliases in errors or client telemetry.
  • Enumeration resistance is improved only incidentally and is not a security control.

17. Performance

  • BINARY(16) is the storage representation.
  • UUIDv7 preserves chronological high-order bytes and is preferable for new ordered identifiers.
  • During coexistence, numeric PK may remain the clustered key while UUID is a unique secondary key.
  • Measure index size, insert/update latency, join plan, rows examined and buffer impact.
  • Do not change the physical primary key until the table-family cutover has a measured online-DDL and rollback plan.

18. Testing

Minimum layers per migrated table family:

Unit/contract

  • UUID parsing/version/variant;
  • deterministic generator;
  • binary round trip;
  • public payload contains no numeric identity;
  • dual-key mismatch classification.

Real MySQL

  • row UUID uniqueness;
  • tenant backfill;
  • relation backfill;
  • Logistics Workspace dual-write;
  • Legacy-origin bridge;
  • cross-tenant rejection;
  • null/zero sentinel handling;
  • duplicate, rollback and authoritative reread;
  • reconciliation query returns zero after fixture cleanup.

Actual route

  • C1/C2 route uses UUID path/payload;
  • numeric route/payload is rejected or redirected only by an explicit Legacy adapter;
  • browser state and links contain no numeric entity identity;
  • fallback Legacy operation remains usable during coexistence.

19. Architecture Gates

Block new code on:

  • numeric public entity/tenant identifiers;
  • numeric route params for migrated Logistics Workspace routes;
  • application/domain import of LegacyNumericId;
  • public result containing both UUID and numeric alias;
  • UUID generation inside domain logic;
  • DB UUID bytes outside adapter;
  • dual-write without same-tenant/mismatch/readback tests;
  • migrated table write that updates only one side;
  • Logistics Workspace-only table with auto-increment identity without approved exception;
  • broad permanent trigger or mapping framework with no table-family owner.

Pre-existing numeric paths are registered transition debt and cannot be copied into a new slice.

20. Rollback And Retirement

Coexistence rollback

  • roll back application to the previous numeric-compatible writer;
  • keep additive UUID columns and backfilled values;
  • disable only the affected bridge if it caused the issue;
  • do not drop columns during incident response without separate approval.

Final retirement

A numeric key is removed only after:

  • route/API/job/import/report callers are zero;
  • Legacy writer is retired or UUID-aware;
  • mismatch/orphan/null counts are zero;
  • backups and rollback artifact exist;
  • read/write performance is accepted;
  • the exact table family has a cutover manifest.

The final target is UUID-only storage and contracts, but the transition prioritizes operational continuity over cosmetic schema purity.

21. References

Primary engineering inputs:

  • RFC 9562, UUID format and UUIDv7;
  • MySQL UUID_TO_BIN / BIN_TO_UUID conversion behavior;
  • scripts/migrations/2026-04-28_task-424_uuid_audit_columns_plan.md;
  • scripts/migrations/2026-04-28_task-425_uuid_audit_columns.php;
  • scripts/migrations/2026-04-29_task-428_uuid_insert_defaults.md;
  • app/utils/Uuid.php;
  • .agents/rules/DB_SCHEMA.md.

Current repository decisions and verified target-environment evidence remain authoritative for rollout.

---

Source: new-system/docs/architecture/UUID_IDENTITY_TRANSITION_STANDARD.md — ctc repository · This page is an English translation of the source document. Identifiers, file paths and code symbols are unchanged.