Skip to main content

D25 — Internal bigint PK + external UUIDv7 on every domain entity (supersedes the UUID-PK rule)

Architecture decision record, relocated verbatim from the retired single-file docs/tenancy/ decision log. Status, thematic clusters, and how to record a new ADR: the decision log index.

User decision — replaces the earlier "tenant-scoped models use a UUID primary key" convention (the old TenantIsolationTest rule + the tenant model's UUIDGenerator). Every domain entity now has:

  • an auto-incrementing bigint primary key — used for fast, compact foreign keys + relations INTERNALLY and never exposed; and
  • a unique uuid (UUIDv7) — the ONLY identifier exposed externally (URLs, API id, DTOs).

This preserves the opacity the old rule wanted (no enumerable / count-leaking ids leave the app) while gaining bigint join/index performance internally. Chosen interactively: uniform (incl. tenants), uuid on all domain entity tables (pure pivots / unexposed reference tables excluded), format UUIDv7.

Why UUIDv7 (verified against the stack): Postgres 18.4 has native uuidv7() + gen_random_uuid() and the native 16-byte uuid type; Laravel has Str::uuid7(). UUIDv7 is time-ordered, so the unique uuid index stays locality-friendly under write load. ULID was rejected (no native PG type — $table->ulid() → char(26)); v4 was the fallback only if zero temporal leak were required (v7 encodes a coarse creation timestamp — an accepted, mild trade-off).

Mechanism:

  • Generation is DB-side (Postgres owns it, like the bigint sequence): the uuid column carries DEFAULT uuidv7() (PG18 native), so EVERY write path — Eloquent, raw SQL, bulk inserts, other services — gets a uuid; the invariant is enforced at the database, not the app. Modules\Foundation\Concerns\HasPublicUuid therefore does NOT generate. It (a) sets getRouteKeyName() = 'uuid' so route-model binding + JSON:API ids resolve by uuid, and (b) on the created event loads the DB-generated uuid back into the model — Eloquent's INSERT returns only the primary key, so without this a freshly-created model wouldn't know its uuid (one extra SELECT per Eloquent create; bulk insert() skips it but the DB still fills the column). Applied to User, ClientUser, TenantRole, Tenant.
  • Migration convention: $table->id(); $table->uuid('uuid')->unique()->default(new Expression('uuidv7()')); on every entity table; internal FKs use foreignId(...)->constrained(...). PostgreSQL has no unsigned integer types — id() = bigserial and foreignId() both emit plain bigint (unsignedBigInteger would be identical on PG), so we use bigint throughout; signed range (~9.2e18) is far beyond any id count.
  • Tenancy: tenancy.models.id_generator => null → the tenants table auto-increments a bigint PK; stancl's GeneratesIds keys RLS off that bigint (getTenantKey()), so the RLS session var stables.current_tenant + every tenant_id FK are bigint. The SPA sends the tenant's uuid in X-Tenant; identification resolves it via the RequestDataTenantResolver tenant_model_column => 'uuid' (no custom resolver needed). uuid is added to the Tenant's VirtualColumn getCustomColumns() so it stays a real column, not virtualized into data. (Confirmed in passing: internachi/modular auto-loads module migrations — the Authorization tables migrate without an explicit loadMigrationsFrom.)
  • External exposure: ClientUserData.id + TenantSummaryData.id = the uuid; tenant_id is surfaced as the tenant's uuid (a client is always in its tenant's context); the whoami probe returns the tenant uuid. The bigint never appears in any API/URL.

Codified + enforced. $table->identifiers() — a Blueprint macro registered in FoundationServiceProvider — adds an entity's two identifier columns: the bigint PK + uuid (DEFAULT uuidv7(), unique), in one call; use it for every new entity table. The convention is mandatory-unless-excluded via two guards:

  • tests/Architecture/ModelConventionsTest — every model in a module \Models namespace must use HasPublicUuid, unless added to its exclusion list (currently just the TenantRolePermission pivot). A new model that forgets the trait fails until the trait is added or it is consciously excluded.
  • tests/Feature/SchemaConventionTest — introspects the REALIZED schema after migrate:fresh: every public table must have a bigint id + a uuid column defaulting to uuidv7(), unless on its EXCLUDED list (framework/vendor tables + pure pivots). Catches a non-compliant table however it was created and verifies the columns + DB default actually exist.

CommandExecuted/QueryExecuted widened tenantId to int|string|null. RLS re-verified — tenants:verify-isolation green on all four tenant tables, and the RLS canary + per-model isolation suites pass against bigint keys. Backoffice spatie roles/permissions keep their stock bigint keys (central, never exposed — no uuid); pure pivots have no uuid. Full suite + PHPStan L6 + Pint + scramble:export all green.

← Decision log index