ADR: Universal User Table for Multi-Provider Authentication

Date: 2025-12-30 Status: Proposed Author: Architecture Team

Context

The EmProps platform currently lacks a unified User table in the database. User identity is derived entirely from JWT tokens at request time, with the extracted user_id stored across 20+ tables without foreign key constraints or a canonical user record.

Current State

Authentication Providers:

• Dynamic Labs - Primary auth for emprops-studio (live)
• Privy - Newly integrated for monitor app, planned for studio
• Stack Auth - Used by monitor app (being phased out)

Database Architecture Issues:

1. No User Table - All tables reference user_id (UUID) without a corresponding User model
2. No Foreign Keys - Database integrity not enforced for user references
3. No User Persistence - User email, display name, creation time not stored
4. Provider Lock-in - User IDs are provider-specific (Dynamic ID, Privy DID, etc.)
5. No Account Linking - Same person using different providers = different identities

Tables Currently Referencing user_id (20+):

Category	Tables
Core Business	api_key, job, project, project_history, flat_file, chat_message
Credits	credits_balance, credits_history
Auth	user_api_key, role
MiniApp	miniapp_claim_activity, miniapp_generation, miniapp_payment
Social	emerge_reward_claim_history, pfp_revert_schedule
Archive	redis_step_archive, redis_workflow_archive

Existing User-Adjacent Tables:

• customer - Stripe-specific, minimal data
• miniapp_user - Farcaster-specific (farcaster_id, wallet_address)
• profile - Orphaned profile data (no FK constraints)

Decision

Implement a Universal User Table that:

1. Serves as the canonical user identity across all providers
2. Maps external provider IDs to internal UUIDs
3. Supports account linking for multi-provider identities
4. Enforces referential integrity via foreign keys

Schema Design

// ============================================================================
// UNIVERSAL USER MODEL
// ============================================================================

model user {
  id              String    @id @default(uuid()) @db.Uuid
  email           String?   @db.VarChar(255)
  display_name    String?   @db.VarChar(255)
  avatar_url      String?   @db.VarChar(500)

  // Timestamps
  created_at      DateTime  @default(now()) @db.Timestamptz
  updated_at      DateTime  @updatedAt @db.Timestamptz
  last_login_at   DateTime? @db.Timestamptz

  // Status
  is_active       Boolean   @default(true)
  is_blocked      Boolean   @default(false)

  // Auth provider accounts (one user can have multiple)
  auth_accounts   user_auth_account[]

  // Existing relations (to be added via migration)
  // api_keys        api_key[]
  // jobs            job[]
  // projects        project[]
  // credits_balance credits_balance[]
  // etc.

  // Link to legacy tables
  customer        customer?
  miniapp_user    miniapp_user?
  profile         profile?

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

// ============================================================================
// AUTH PROVIDER ACCOUNT LINKING
// ============================================================================

model user_auth_account {
  id                String    @id @default(uuid()) @db.Uuid
  user_id           String    @db.Uuid
  user              user      @relation(fields: [user_id], references: [id], onDelete: Cascade)

  // Provider identification
  provider          String    @db.VarChar(20)  // 'dynamic', 'privy', 'stack', 'farcaster'
  provider_user_id  String    @db.VarChar(255) // External ID (did:privy:xxx, dynamic UUID, etc.)

  // Provider-specific metadata
  provider_email    String?   @db.VarChar(255)
  provider_metadata Json?     @db.JsonB        // Verified credentials, wallet addresses, etc.

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

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

User ID Format

Internal User ID: Standard UUID v4 (e.g., 550e8400-e29b-41d4-a716-446655440000)

This internal UUID is what all tables will reference via foreign keys. The provider-specific IDs are stored in user_auth_account for lookup.

Authentication Flow (Updated)

1. User authenticates via provider (Dynamic/Privy/Stack)
2. Frontend receives JWT token
3. API validates JWT via JWKS (existing flow)
4. API extracts provider_user_id from JWT claims
5. NEW: Look up user_auth_account by (provider, provider_user_id)
   - If found: Return existing user
   - If not found: Create new user + auth_account (first-time login)
6. Return unified AuthUser with internal user.id
7. All database operations use internal user.id

Migration Strategy

Phase 1: Create Tables (Non-Breaking)

1. Add user and user_auth_account tables
2. No changes to existing tables yet
3. Deploy and verify table creation

-- Migration: 001_create_user_tables.sql
CREATE TABLE "user" (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email VARCHAR(255),
  display_name VARCHAR(255),
  avatar_url VARCHAR(500),
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW(),
  last_login_at TIMESTAMPTZ,
  is_active BOOLEAN DEFAULT TRUE,
  is_blocked BOOLEAN DEFAULT FALSE
);

CREATE TABLE user_auth_account (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES "user"(id) ON DELETE CASCADE,
  provider VARCHAR(20) NOT NULL,
  provider_user_id VARCHAR(255) NOT NULL,
  provider_email VARCHAR(255),
  provider_metadata JSONB,
  linked_at TIMESTAMPTZ DEFAULT NOW(),
  last_used_at TIMESTAMPTZ,
  UNIQUE(provider, provider_user_id)
);

CREATE INDEX idx_user_email ON "user"(email);
CREATE INDEX idx_user_auth_user_id ON user_auth_account(user_id);
CREATE INDEX idx_user_auth_provider ON user_auth_account(provider);

Phase 2: Update Auth Flow (Soft Migration)

1. Modify auth session validation to create/lookup users
2. Return internal user.id alongside provider ID
3. Applications start using internal ID for new records
4. Existing records still work (no FK constraints yet)

// In session.ts - after JWT verification
async function getOrCreateUser(
  providerUserId: string,
  provider: 'dynamic' | 'privy' | 'stack',
  claims: Record<string, unknown>
): Promise<User> {
  // Try to find existing auth account
  let authAccount = await prisma.userAuthAccount.findUnique({
    where: { provider_provider_user_id: { provider, provider_user_id: providerUserId } },
    include: { user: true }
  });

  if (authAccount) {
    // Update last used
    await prisma.userAuthAccount.update({
      where: { id: authAccount.id },
      data: { last_used_at: new Date() }
    });
    return authAccount.user;
  }

  // Create new user + auth account
  const user = await prisma.user.create({
    data: {
      email: claims.email as string | null,
      display_name: extractDisplayName(claims, provider),
      auth_accounts: {
        create: {
          provider,
          provider_user_id: providerUserId,
          provider_email: claims.email as string | null,
          provider_metadata: claims as any,
        }
      }
    }
  });

  return user;
}

Phase 3: Backfill Existing Data

1. Extract unique user_ids from all tables
2. Determine provider from context (studio = Dynamic, monitor = Stack/Privy)
3. Create user records for historical data

-- Migration: 002_backfill_users.sql

-- Create users from existing job records (assuming Dynamic provider for studio)
INSERT INTO "user" (id, created_at)
SELECT DISTINCT user_id, MIN(created_at)
FROM job
WHERE user_id IS NOT NULL
  AND user_id NOT IN (SELECT id FROM "user")
GROUP BY user_id;

-- Create auth accounts for backfilled users
INSERT INTO user_auth_account (user_id, provider, provider_user_id, linked_at)
SELECT id, 'dynamic', id::text, created_at
FROM "user"
WHERE id NOT IN (SELECT user_id FROM user_auth_account);

Phase 4: Add Foreign Keys (Breaking Change)

1. Add nullable FK columns to existing tables
2. Backfill FK references
3. Add FK constraints
4. Eventually make NOT NULL

-- Migration: 003_add_foreign_keys.sql

-- Add FK to job table
ALTER TABLE job
ADD CONSTRAINT fk_job_user
FOREIGN KEY (user_id) REFERENCES "user"(id);

-- Repeat for other tables...

Phase 5: Link Legacy Tables

1. Add user_id to customer, miniapp_user, profile
2. Link existing records where possible
3. Update queries to join through user table

Account Linking Strategy

When a user signs in with a new provider but same email:

async function linkAccountIfSameEmail(
  newProvider: string,
  newProviderUserId: string,
  email: string
): Promise<User | null> {
  if (!email) return null;

  // Find existing user with same email
  const existingUser = await prisma.user.findFirst({
    where: { email },
    include: { auth_accounts: true }
  });

  if (!existingUser) return null;

  // Check if this provider account already linked
  const alreadyLinked = existingUser.auth_accounts.some(
    a => a.provider === newProvider && a.provider_user_id === newProviderUserId
  );

  if (alreadyLinked) return existingUser;

  // Link new provider account to existing user
  await prisma.userAuthAccount.create({
    data: {
      user_id: existingUser.id,
      provider: newProvider,
      provider_user_id: newProviderUserId,
      provider_email: email,
    }
  });

  return existingUser;
}

API Response Changes

Before (current):

{
  "user": {
    "id": "did:privy:abc123",
    "email": "user@example.com",
    "provider": "privy"
  }
}

After (with universal user):

{
  "user": {
    "id": "550e8400-e29b-41d4-a716-446655440000",
    "email": "user@example.com",
    "displayName": "John Doe",
    "provider": "privy",
    "providerUserId": "did:privy:abc123",
    "linkedAccounts": ["dynamic", "privy"]
  }
}

Consequences

Positive

1. Single Source of Truth - One user table for all identity needs
2. Referential Integrity - Foreign keys ensure data consistency
3. Provider Agnostic - Easy to add new auth providers
4. Account Linking - Users can link multiple identities
5. Audit Trail - Track user creation, last login, account changes
6. Future-Proof - Clean foundation for user management features

Negative

1. Migration Complexity - Multi-phase migration with careful coordination
2. Backward Compatibility - APIs must support both old and new formats during transition
3. Performance - Additional lookup required (provider_id → user_id)
4. Data Backfill - Historical user_ids may not have provider context

Risks

1. Duplicate Users - Same person with different providers before linking
2. Data Loss - Orphaned records if backfill fails
3. Downtime - FK constraints require careful deployment
4. API Breaking Changes - Clients expecting provider IDs need updates

Mitigations

1. Soft Migration - Run old and new systems in parallel
2. Feature Flags - Gradually enable new auth flow
3. Validation Scripts - Verify data integrity before adding constraints
4. Rollback Plan - Keep ability to revert to provider-only IDs

Implementation Timeline

Phase	Description	Duration	Risk
1	Create tables	1 day	Low
2	Update auth flow	2-3 days	Medium
3	Backfill data	1-2 days	Medium
4	Add foreign keys	1 day	High
5	Link legacy tables	2-3 days	Medium
6	Remove legacy code	Ongoing	Low

Total Estimated Effort: 1-2 weeks

References

• JWKS Unified Token Verification ADR
• Prisma Schema
• Auth Session Endpoint