ADR: Universal User Table

Date: 2026-03-02 Status: Accepted — Phase 1 deployed to staging 2026-03-04 Supersedes: 2025-12-30-universal-user-table.mdDecision Makers: Architecture Team, Founders

Executive Summary

The EmProps platform has no canonical User record in the database. Twenty+ tables store user_id as a bare UUID with no foreign key constraints and no corresponding row anywhere. All auth identity is derived from JWT tokens at request time, meaning users don't exist between requests — they're just claims.

This creates the "one user custodial model" problem: emergibles NFT collections default to SYSTEM_USER_ID (the zero UUID), meaning every collection created through emergibles is anonymous. There's no foundation for user-owned assets, cross-app identity, account linking, or the custody transfer needed to graduate custodial NFTs to real users.

This ADR establishes the user table as the canonical identity record and user_identity as the provider-mapping child table, and defines the migration strategy for connecting the existing codebase to this model.

Context

The Five Auth Providers In Use

Provider	Used By	ID Format	Notes
Dynamic Labs	emprops-studio	UUID string	Live, primary studio auth
Privy	Emergibles, Monitor, emprops-studio (migration)	DID (`did:privy:abc123`)	Primary auth for emergibles; being adopted across apps
Stack Auth	Monitor	Stack User ID	Being phased out
Worldcoin	Emergibles (legacy)	Wallet address (0x...)	SIWE via MiniKit — NextAuth code still exists but Privy is primary
Farcaster	Emergibles (via Privy), miniapp	FID (integer as string)	Privy supports as native linked account; miniapp uses SDK context

None of these produce the same ID format. The same human using two different providers has two completely different identities in the current system.

Update (2026-03-04): Emergibles has migrated from Worldcoin SIWE/NextAuth to Privy as its primary auth provider. Privy login methods: wallet, Twitter, Farcaster. Privy user IDs are DIDs like did:privy:clxyz.... NextAuth/Worldcoin code remains but is no longer the active auth path.

What Exists Today

No user table. The closest things are:

Table	What It Tracks	Problem
`miniapp_user`	Farcaster users only	Siloed, no relation to other providers
`profile`	Orphaned display metadata	No FK to any identity
`customer`	Stripe billing identity	No FK to users
`social_link`	Custodial social ownership	Maps collections to social handles, not users

20+ tables with user_id:job, project, project_history, flat_file, chat_message, api_key, user_api_key, role, credits_balance, credits_history, miniapp_claim_activity, miniapp_generation, miniapp_payment, emerge_reward_claim_history, pfp_revert_schedule, redis_step_archive, redis_workflow_archive, and more — all bare UUIDs, no FK constraints, no guarantee the value refers to anything real.

The Custodial NFT Problem

When emergibles generates an NFT collection, it sends this to the EmProps API:

typescript

{
  concept: "...",
  social_org: 'farcaster',
  social_identifier: body.social_identifier,  // user's Farcaster handle
  save: true,
  project_id: process.env.NFT_PROJECT_ID,   // shared platform project
  // user_id: not passed → defaults to SYSTEM_USER_ID
}

The collection ends up owned by SYSTEM_USER_ID = "00000000-0000-0000-0000-000000000000". Every collection from every emergibles user is attributed to the same phantom account. There's no path to:

Show a user their own collections
Transfer ownership when a Worldcoin user is also the Farcaster user
Enforce any access control on collection mutations

Decision

Schema: Two-Table Identity Model

user (canonical internal identity)
 └── user_identity (one row per provider per user)

Why child table, not wider user table: Each provider has different credential shapes. Adding columns to user for each provider creates a sparse table where 4 out of 5 provider columns are always null. A child table is normalized, lets us add providers without schema changes, and makes account linking (same user, multiple providers) a first-class concept.

`user` Table

prisma

model user {
  id            String    @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid
  email         String?   @unique @db.VarChar(255)
  display_name  String?   @db.VarChar(255)
  avatar_url    String?   @db.VarChar(500)
  created_at    DateTime  @default(now()) @db.Timestamptz
  updated_at    DateTime  @updatedAt @db.Timestamptz
  last_login_at DateTime? @db.Timestamptz
  is_active     Boolean   @default(true)
  is_blocked    Boolean   @default(false)

  identities    user_identity[]
  miniapp_user  miniapp_user?         // Farcaster extension, eventually deprecated
  profile       profile?              // Display preferences

  @@index([email])
  @@index([created_at])
}

`user_identity` Table

prisma

model user_identity {
  id               String    @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid
  user_id          String    @db.Uuid
  user             user      @relation(fields: [user_id], references: [id], onDelete: Cascade)

  provider         String    @db.VarChar(30)   // See provider values below
  provider_user_id String    @db.VarChar(500)  // External ID (DID, UUID, FID, 0x address)
  provider_email   String?   @db.VarChar(255)
  provider_metadata Json?    @db.JsonB          // Full JWT claims or profile data

  linked_at        DateTime  @default(now()) @db.Timestamptz
  last_used_at     DateTime? @db.Timestamptz

  @@unique([provider, provider_user_id])
  @@index([user_id])
  @@index([provider])
}

Valid provider values:

Value	Source	ID Example
`dynamic`	Dynamic Labs JWT `sub`	`9e69f...` (UUID)
`privy`	Privy JWT `sub`	`did:privy:clxyz...`
`stack`	Stack Auth JWT `sub`	Stack User ID
`worldcoin`	Worldcoin SIWE wallet address	`0xABC...`
`farcaster`	Farcaster FID from SDK	`"12345"`

Account Linking Strategy

When a user authenticates for the first time:

Look up user_identity by (provider, provider_user_id)
If found → return existing user.id
If not found AND email exists → look for existing user with same email → link the new provider to that user
If not found AND no email match → create new user + user_identity

This handles the common case: a creator who uses emprops-studio (Dynamic/Privy) and also uses emergibles (Worldcoin) with the same email will end up with one user record linking both provider accounts.

miniapp_user Lifecycle

miniapp_user is the current Farcaster-specific user table. It contains fields like canCreateWorkflows, canGenerate, isBlocked that are emergibles/miniapp-specific permissions.

Decision:

Phase 1: Add optional user_id FK to miniapp_user. Farcaster users get a user record, miniapp_user.user_id points to it.
Future (separate ADR): Once all Farcaster identity lives in user_identity, audit the Farcaster-specific permission fields — migrate what's needed to user (e.g., is_blocked) and deprecate miniapp_user.

This avoids a big-bang migration of a live table.

What This Unlocks

Capability	Before	After
NFT collections owned by real users	All → SYSTEM_USER_ID	Each collection → real `user.id`
Show user their collections	Impossible	`WHERE user_id = internalUserId`
Same person on two platforms = one identity	Two different IDs	One `user`, two `user_identity` rows
Custody transfer	Collections stuck at social_link	Transfer from `social_link` to `user.id`
FK integrity	20+ orphaned UUID columns	Enforceable after backfill
Block a user platform-wide	Per-app only	`user.is_blocked = true`

Migration Strategy

Staged Deployment — Emergibles First

Updated 2026-03-04: Restructured from the original 6-phase plan into independently-deployable stages. Emergibles adopts the user table first, then miniapp, then remaining apps. Each stage is independently deployable and non-breaking.

Stage	What	Deploy Target	Breaking?
0	Create `user` + `user_identity` tables	Migration only	No
1	New `POST /api/auth/resolve` endpoint + `getOrCreateUser`	emprops-api	No
2	Middleware resolves provider_id → internal UUID	emprops-api	No (fallback)
3	Wire emergibles to pass Privy token + user_id	emergibles	No
4	Backfill `miniapp_user` + wire miniapp	miniapp	No
5+	Remaining apps (Monitor, Studio)	Per-app	No
N	Add FK constraints to existing tables	Maintenance window	Careful deploy

Key decision: Separate /api/auth/resolve endpoint — The existing /api/auth/session endpoint is left unchanged. A new /api/auth/resolve endpoint handles user creation/lookup. This is additive and avoids modifying a live endpoint used by multiple apps.

Key decision: Privy auth for emergibles — Emergibles uses Privy (not Worldcoin SIWE) for client auth. The emergibles Next.js API routes pass the Privy access token to /api/auth/resolve, which verifies the JWT and returns an internal UUID.

Soft Migration Design (Stage 2)

The middleware currently sets:

req.headers["user_id"] = providerUserId  // e.g., did:privy:abc123

After Stage 2, the middleware resolves the internal UUID first and falls back:

req.headers["user_id"] = internalUuid ?? providerUserId  // internal preferred
req.headers["provider_user_id"] = providerUserId          // always available
req.headers["auth_provider"] = provider

Existing code that uses req.headers["user_id"] continues to work — it just gets the internal UUID for users who have been resolved via /api/auth/resolve, and the provider ID for everyone else. Over time, as users authenticate, all active users will have internal UUIDs.

What This Does NOT Change

Auth provider decisions — we keep using Dynamic, Privy, Stack, Worldcoin, Farcaster as-is
The custodial model (social_link, is_custodial) for collections that were already created anonymously — those remain; custody transfer is a separate feature
The JWT verification system (unified-jwt.ts) — it stays, we just add a DB lookup step after it

Consequences

Positive

Single source of truth for user identity across all five providers and all three apps
Referential integrity achievable after backfill (Phase 6)
Account linking built in by design, not bolted on later
Emergibles NFTs are attributable — escapes the SYSTEM_USER_ID trap
Future-proof — add a sixth auth provider by adding a new provider value, no schema change
Audit foundation — created_at, last_login_at, linked_at give visibility into user behavior

Negative

Additional DB lookup on every authenticated request (Phase 3) — single indexed unique lookup, negligible in practice; can be cached in Redis if needed
Backfill ambiguity — historical user_id values in old records may be Dynamic IDs, Privy DIDs, or Stack IDs — we can't always determine which provider created them without provider context at creation time
Duplicate user risk — the same person on two providers without a shared email cannot be auto-linked; they get two user records until they self-merge (see User Merge in implementation guide)

Real Complexity Areas

These are the actual difficult parts — not duration but downstream effect:

Area	Why It's Complex
Middleware change (Stage 2)	Every authenticated request in every app flows through this. A bug here breaks all auth. Must be deployed with fallback and monitored closely.
Emergibles user resolution (Stage 3)	Emergibles Next.js API routes call `/api/auth/resolve` before forwarding to NFT endpoints. If emprops-api is down, resolution fails — but graceful degradation means collections still work (just without user attribution).
Historical user_id backfill (Stage N)	20+ tables have orphaned `user_id` values. Some are Dynamic UUIDs, some Privy DIDs, some unknown. Full FK enforcement requires knowing the provider context for every historical record — which isn't always available. Stage N may never be 100% complete.
User merge	Two `user` records confirmed to be the same person must be collapsed atomically — all owned records, all `user_identity` rows reassigned from secondary to primary, secondary deleted. This is a core platform operation, not an edge case. Full spec in implementation guide.

Risks

Risk	Mitigation
Auth middleware regression	Soft fallback to provider ID if user lookup fails; never throw on lookup error
Emergibles NFT creation without user_id	Graceful degradation: if `/api/auth/resolve` fails, collection still created with SYSTEM_USER_ID fallback. No login failure.
FK constraints fail on Stage N	Stage N is explicitly deferrable — the system works without it; add constraints only after full audit
Duplicate user records	Resolved via user merge — atomic transaction that collapses two records into one; user-initiated via profile settings

Alternatives Considered

1. Widen the `user` table with one column per provider

Rejected. Five providers × different metadata shapes = sparse table with 20+ nullable columns. Adding a sixth provider requires a schema migration. The child table is the correct normalization.

2. Use Privy as the universal identity provider

Ideal long-term, not feasible now. Privy can federate Worldcoin, Farcaster, wallet sign-in, etc. under one DID. But emergibles uses MiniKit (Worldcoin's own wallet), and Farcaster identity comes from the miniapp SDK context — not Privy. Migrating to a Privy-as-federation model requires product changes to the auth flows in emergibles and miniapp. This ADR doesn't require that — it's compatible with a future Privy migration.

3. Keep provider IDs everywhere, just add FK validation

Rejected. Would require one FK constraint per provider per table, or a polymorphic FK — both are worse than a single canonical UUID. Also doesn't solve account linking.

Superseded ADR (2025-12-30) — Original proposal
JWKS Unified Token Verification — Auth infrastructure this builds on
Emergibles NFT Capability Improvements — Emergibles features that require this
Miniapp Multi-Environment Deployment — Farcaster identity context
Privy Migration for EmProps Studio — Future auth consolidation path
Implementation Guide: apps/docs/src/impl/2026-03-02-universal-user-table.md

ADR: Universal User Table ​

Executive Summary ​

Context ​

The Five Auth Providers In Use ​

What Exists Today ​

The Custodial NFT Problem ​

Decision ​

Schema: Two-Table Identity Model ​

user Table ​

user_identity Table ​

Account Linking Strategy ​

miniapp_user Lifecycle ​

What This Unlocks ​

Migration Strategy ​

Staged Deployment — Emergibles First ​

Soft Migration Design (Stage 2) ​

What This Does NOT Change ​

Consequences ​

Positive ​

Negative ​

Real Complexity Areas ​

Risks ​

Alternatives Considered ​

1. Widen the user table with one column per provider ​

2. Use Privy as the universal identity provider ​

3. Keep provider IDs everywhere, just add FK validation ​

Related Documents ​