PostgreSQL Nanoid Identifiers

This skill provides guidance for implementing nanoid-based identifiers in PostgreSQL, with a focus on Supabase integration.

Philosophy: Use nanoid for public-facing identifiers (URLs, APIs, exports). Use UUID for internal references to auth.users. Prefixes provide context and prevent ID collisions across entities.

Quick Reference

Core Principle: Hybrid ID Approach

+-------------------+     +------------------+
|     profiles      |     |   auth.users     |
+-------------------+     +------------------+
| user_id (UUID) PK |---->| id (UUID)    PK  |
| public_id (nanoid)|     |                  |
+-------------------+     +------------------+

• user_id: Primary key AND foreign key to auth.users (UUID) - used for RLS and joins
• public_id: Exposed in URLs, APIs, exports (nanoid with prefix)

This approach provides:

• Direct RLS policies using auth.uid() = user_id
• Clean foreign key relationship
• URL-friendly public identifiers
• Clear separation of internal vs public identity

Standard Prefixes

Table Definition Patterns

Pattern 1: User-Linked Tables (Recommended)

For tables with 1:1 relationship to auth.users:

-- Hybrid approach: UUID as PK, nanoid as public_id
CREATE TABLE public.profiles (
  -- Primary key is also foreign key to auth.users
  user_id UUID PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,

  -- Public identifier for URLs/APIs
  public_id TEXT UNIQUE NOT NULL DEFAULT nanoid('usr_'),

  -- Profile data
  display_name TEXT,
  avatar_url TEXT,

  -- Timestamps
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),

  -- Constraints
  CONSTRAINT profiles_public_id_format CHECK (public_id ~ '^usr_[0-9a-zA-Z]{17}$')
);

COMMENT ON COLUMN public.profiles.user_id IS 'Internal ID - use for RLS and joins';
COMMENT ON COLUMN public.profiles.public_id IS 'Public ID - use in URLs and APIs';

-- Index for public_id lookups (user_id already indexed as PK)
CREATE INDEX profiles_public_id_idx ON public.profiles(public_id);

Pattern 2: Standalone Tables

For tables not directly linked to auth.users:

CREATE TABLE public.products (
  -- nanoid as primary key
  id TEXT PRIMARY KEY DEFAULT nanoid('prd_'),

  -- Data
  name TEXT NOT NULL,
  price NUMERIC(10,2) NOT NULL,

  -- Ownership (if needed)
  created_by UUID REFERENCES auth.users(id),

  -- Timestamps
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),

  -- Constraints
  CONSTRAINT products_id_format CHECK (id ~ '^prd_[0-9a-zA-Z]{17}$')
);

Migration Pattern

-- Migration: Add nanoid to existing table
-- Step 1: Add the column
ALTER TABLE public.customers
ADD COLUMN public_id TEXT;

-- Step 2: Generate IDs for existing rows
UPDATE public.customers
SET public_id = nanoid('cus_')
WHERE public_id IS NULL;

-- Step 3: Add constraints
ALTER TABLE public.customers
ALTER COLUMN public_id SET NOT NULL,
ADD CONSTRAINT customers_public_id_unique UNIQUE (public_id),
ADD CONSTRAINT customers_public_id_format CHECK (public_id ~ '^cus_[0-9a-zA-Z]{17}$');

-- Step 4: Set default for new rows
ALTER TABLE public.customers
ALTER COLUMN public_id SET DEFAULT nanoid('cus_');

API Response Pattern

Always return public_id in API responses, never internal UUIDs:

// Good: Return public_id
return {
  id: profile.public_id,    // usr_V1StGXR8_Z5jdHi
  name: profile.display_name,
  // Never expose user_id (UUID) in API
}

// Bad: Exposing internal UUID
return {
  id: profile.user_id,      // Don't do this!
  ...
}

Query Patterns

// Lookup by public_id (for API routes)
const { data } = await supabase
  .from('profiles')
  .select('*')
  .eq('public_id', 'usr_V1StGXR8_Z5jdHi')
  .single()

// Current user's profile (RLS handles auth)
const { data } = await supabase
  .from('profiles')
  .select('*')
  .single()  // RLS filters to current user

TypeScript Types

// Type-safe prefixed IDs
type UserPublicId = `usr_${string}`
type OrgId = `org_${string}`
type OrderId = `ord_${string}`

interface Profile {
  user_id: string        // UUID - internal use only
  public_id: UserPublicId  // nanoid - for APIs
  displayName: string
}

// Validation helper
function isValidUserPublicId(id: string): id is UserPublicId {
  return /^usr_[0-9a-zA-Z]{17}$/.test(id)
}

// API response type (excludes internal IDs)
interface ProfileResponse {
  id: UserPublicId  // Map public_id to 'id' in response
  displayName: string
}

When to Use What

Common Mistakes

1. Exposing auth.users UUID in APIs - Always use nanoid public_id
2. Using nanoid as PK for user tables - Use UUID from auth.users as PK
3. Inconsistent prefix lengths - Keep random part at 17 chars
4. Missing CHECK constraints - Always validate format
5. Not indexing public_id - Add index for lookup performance
6. Using nanoid for auth FK - Use UUID for auth.users references

Performance Notes

• nanoid generation: ~110,000 IDs/second
• Collision probability: Negligible at 17 random chars
• Index performance: Comparable to UUID
• Storage: ~21 bytes vs 16 bytes for UUID (minimal difference)

Additional Resources

For detailed implementation, see reference files:

• references/installation.md - PostgreSQL function setup
• references/prefix-conventions.md - Complete prefix guidelines