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

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

• public_id: Exposed in URLs, APIs, exports (nanoid with prefix)
• user_id: Internal reference to Supabase auth (UUID)
• id: Can be nanoid for new tables, UUID for legacy

Standard Prefixes

Table Definition Pattern

-- Example: profiles table with nanoid
CREATE TABLE public.profiles (
  -- Primary key using nanoid
  id TEXT NOT NULL DEFAULT nanoid('usr_') PRIMARY KEY,

  -- Reference to Supabase auth (UUID)
  user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,

  -- 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_id_format CHECK (id ~ '^usr_[0-9a-zA-Z]{17}$'),
  CONSTRAINT profiles_user_id_unique UNIQUE (user_id)
);

-- Index for user lookups
CREATE INDEX profiles_user_id_idx ON public.profiles(user_id);

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 nanoid in API responses, never internal UUIDs:

// Good: Return public_id
return {
  id: profile.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!
  ...
}

TypeScript Types

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

interface Profile {
  id: UserId
  displayName: string
}

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

When to Use What

Common Mistakes

1. Exposing auth.users UUID in APIs - Always use nanoid public_id
2. Inconsistent prefix lengths - Keep random part at 17 chars
3. Missing CHECK constraints - Always validate format
4. Not indexing public_id - Add index for lookup performance
5. 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