PostgreSQL Nanoid Identifiers Skill

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

| Use Case | ID Type | Example | |----------|---------|---------| | Public API/URLs | nanoid with prefix | usr_V1StGXR8_Z5jdHi | | Primary key (user tables) | UUID | auth.users.id as PK+FK | | Primary key (other tables) | UUID or nanoid | Context-dependent | | Join tables | UUID FK | Reference auth.users directly |

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

| Entity | Prefix | Length | Example | Regex Pattern | |--------|--------|--------|---------|---------------| | User (profile) | usr_ | 21 | usr_V1StGXR8_Z5jdHi | ^usr_[0-9a-zA-Z]{17}$ | | Organization | org_ | 21 | org_kJ7mNpQ2xWzL9aB | ^org_[0-9a-zA-Z]{17}$ | | Team | team_ | 22 | team_uV4wX7yZaB3cD | ^team_[0-9a-zA-Z]{17}$ | | Customer | cus_ | 21 | cus_oP8qR1sTuV4wX | ^cus_[0-9a-zA-Z]{17}$ | | Product | prd_ | 21 | prd_mN3kL9pQwE7rT | ^prd_[0-9a-zA-Z]{17}$ | | Order | ord_ | 21 | ord_xYz7aBcDeF2gH | ^ord_[0-9a-zA-Z]{17}$ | | Invoice | inv_ | 21 | inv_9sK3pLmNqR5tU | ^inv_[0-9a-zA-Z]{17}$ | | Subscription | sub_ | 21 | sub_gH2iJ5kL8mN9 | ^sub_[0-9a-zA-Z]{17}$ | | Transaction | txn_ | 21 | txn_aB4cD7eF0gH3 | ^txn_[0-9a-zA-Z]{17}$ | | Session | ses_ | 21 | ses_rT5vU8wX2zY4 | ^ses_[0-9a-zA-Z]{17}$ | | Project | proj_ | 22 | proj_aB3cD6eF9gH | ^proj_[0-9a-zA-Z]{17}$ | | Workspace | ws_ | 20 | ws_kL2mN5pQ8rS1tU | ^ws_[0-9a-zA-Z]{17}$ | | File | file_ | 22 | file_vW4xY7zA0bC | ^file_[0-9a-zA-Z]{17}$ | | API Key | key_ | 21 | key_dE3fG6hI9jK2 | ^key_[0-9a-zA-Z]{17}$ | | Webhook | whk_ | 21 | whk_lM4nO7pQ0rS | ^whk_[0-9a-zA-Z]{17}$ |

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

| Scenario | Use | |----------|-----| | User profile table PK | UUID (from auth.users) | | User profile public ID | nanoid with prefix | | Standalone table PK | nanoid with prefix | | Foreign key to auth.users | UUID | | Public API endpoint | nanoid (public_id) | | Internal service-to-service | UUID | | URL slugs | nanoid (URL-safe by default) | | Export/Import IDs | nanoid (human-readable) | | Legacy table migration | Add public_id column |

Common Mistakes

Exposing auth.users UUID in APIs - Always use nanoid public_id
Using nanoid as PK for user tables - Use UUID from auth.users as PK
Inconsistent prefix lengths - Keep random part at 17 chars
Missing CHECK constraints - Always validate format
Not indexing public_id - Add index for lookup performance
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

Agent Skills: PostgreSQL Nanoid Identifiers

Install this agent skill to your local

Skill Files