User Data Extension¶

The PostgreSQL Permissions Model provides two flexible approaches for extending user data beyond the core auth.user_info table. You can either modify the auth.user_data table directly or create separate application-specific tables that reference auth.user_info.user_id.

Two Extension Approaches¶

Approach 1: Extend auth.user_data Table¶

Modify the existing auth.user_data table by adding custom columns for your application's needs.

Pros: - Simple and direct - Built-in functions for data management - Centralized user data storage - Automatic user_id relationship

Cons: - All applications share the same table - Schema changes affect entire system - Less flexible for complex data structures

Approach 2: Create Separate Application Tables¶

Create dedicated tables in your application schema that reference auth.user_info.user_id as a foreign key.

Pros: - Complete schema control - Application-specific data isolation - Support for complex relationships - No impact on other applications

Cons: - Manual relationship management - No built-in helper functions - Requires custom queries

graph LR
    subgraph "User Core"
        UI["auth.user_info<br/>- user_id<br/>- username<br/>- email<br/>- display_name"]
    end

    subgraph "Approach 1: Extend user_data"
        UD["auth.user_data<br/>- user_id<br/>- employee_number<br/>- department<br/>- hire_date<br/>- has_children<br/>- is_casual_driver<br/>- custom_field_n"]
    end

    subgraph "Approach 2: Separate Tables"
        EMP["app.employee_details<br/>- user_id<br/>- employee_number<br/>- department<br/>- hire_date<br/>- manager_user_id"]

        PREF["app.user_preferences<br/>- user_id<br/>- theme<br/>- language<br/>- notifications"]

        PROF["app.user_profile<br/>- user_id<br/>- bio<br/>- avatar_url<br/>- social_links"]
    end

    UI -->|1:1| UD
    UI -->|1:1| EMP
    UI -->|1:1| PREF
    UI -->|1:1| PROF

Approach 1: Extending auth.user_data¶

Modifying the Table¶

-- Add custom columns to auth.user_data table
ALTER TABLE auth.user_data
ADD COLUMN employee_number text,
ADD COLUMN department text,
ADD COLUMN hire_date date,
ADD COLUMN salary_band integer,
ADD COLUMN has_children boolean DEFAULT false,
ADD COLUMN is_casual_driver boolean DEFAULT false,
ADD COLUMN emergency_contact_name text,
ADD COLUMN emergency_contact_phone text;

-- Add constraints if needed
ALTER TABLE auth.user_data
ADD CONSTRAINT uk_user_data_employee_number UNIQUE (employee_number);

-- Add indexes for commonly queried fields
CREATE INDEX idx_user_data_department ON auth.user_data(department);
CREATE INDEX idx_user_data_hire_date ON auth.user_data(hire_date);

Using Built-in Functions¶

-- Update user data using the built-in function
SELECT auth.update_user_data(
    _user_id := 'user-uuid',
    _data := '{
        "employee_number": "EMP001",
        "department": "Engineering",
        "hire_date": "2024-01-15",
        "salary_band": 5,
        "has_children": true,
        "emergency_contact_name": "Jane Doe",
        "emergency_contact_phone": "+1-555-0123"
    }'::jsonb
);

Querying Extended Data¶

-- Get complete user profile
SELECT
    u.user_id,
    u.username,
    u.email,
    u.display_name,
    ud.employee_number,
    ud.department,
    ud.hire_date,
    ud.has_children,
    ud.is_casual_driver
FROM auth.user_info u
LEFT JOIN auth.user_data ud ON u.user_id = ud.user_id
WHERE u.username = 'john.doe';

Complex Queries with Extended Data¶

-- Find users by department and hire date
SELECT
    u.display_name,
    u.email,
    ud.employee_number,
    ud.hire_date
FROM auth.user_info u
JOIN auth.user_data ud ON u.user_id = ud.user_id
WHERE ud.department = 'Engineering'
  AND ud.hire_date >= '2024-01-01'
ORDER BY ud.hire_date;

-- Get users with children for benefits reporting
SELECT
    u.display_name,
    ud.employee_number,
    ud.department,
    ud.emergency_contact_name
FROM auth.user_info u
JOIN auth.user_data ud ON u.user_id = ud.user_id
WHERE ud.has_children = true
ORDER BY ud.department, u.display_name;

Approach 2: Separate Application Tables¶

Creating Application-Specific Tables¶

-- Create schema for your application
CREATE SCHEMA IF NOT EXISTS myapp;

-- Employee details table
CREATE TABLE myapp.employee_details (
    user_id uuid PRIMARY KEY REFERENCES auth.user_info(user_id) ON DELETE CASCADE,
    employee_number text UNIQUE NOT NULL,
    department text,
    hire_date date,
    salary_band integer,
    manager_user_id uuid REFERENCES auth.user_info(user_id),
    office_location text,
    job_title text,
    created_at timestamptz DEFAULT now(),
    updated_at timestamptz DEFAULT now()
);

-- User preferences table
CREATE TABLE myapp.user_preferences (
    user_id uuid PRIMARY KEY REFERENCES auth.user_info(user_id) ON DELETE CASCADE,
    theme text DEFAULT 'light',
    language text DEFAULT 'en',
    timezone text DEFAULT 'UTC',
    email_notifications boolean DEFAULT true,
    sms_notifications boolean DEFAULT false,
    created_at timestamptz DEFAULT now(),
    updated_at timestamptz DEFAULT now()
);

-- User profile table
CREATE TABLE myapp.user_profile (
    user_id uuid PRIMARY KEY REFERENCES auth.user_info(user_id) ON DELETE CASCADE,
    bio text,
    avatar_url text,
    phone_number text,
    address jsonb,
    social_links jsonb,
    skills text[],
    created_at timestamptz DEFAULT now(),
    updated_at timestamptz DEFAULT now()
);

Managing Separate Table Data¶

-- Insert employee details
INSERT INTO myapp.employee_details
    (user_id, employee_number, department, hire_date, job_title)
VALUES
    ('user-uuid', 'EMP001', 'Engineering', '2024-01-15', 'Senior Developer');

-- Update user preferences
INSERT INTO myapp.user_preferences
    (user_id, theme, language, timezone)
VALUES
    ('user-uuid', 'dark', 'en', 'America/New_York')
ON CONFLICT (user_id)
DO UPDATE SET
    theme = EXCLUDED.theme,
    language = EXCLUDED.language,
    timezone = EXCLUDED.timezone,
    updated_at = now();

-- Add user profile data
INSERT INTO myapp.user_profile
    (user_id, bio, phone_number, skills)
VALUES
    ('user-uuid',
     'Senior software developer with 10 years experience',
     '+1-555-0123',
     ARRAY['PostgreSQL', 'Python', 'JavaScript']);

Complex Relationships¶

-- Create manager-employee relationships
SELECT
    e.employee_number,
    e.job_title,
    u.display_name as employee_name,
    m.display_name as manager_name
FROM myapp.employee_details e
JOIN auth.user_info u ON e.user_id = u.user_id
LEFT JOIN auth.user_info m ON e.manager_user_id = m.user_id
WHERE e.department = 'Engineering'
ORDER BY m.display_name NULLS LAST, e.job_title;

-- Find employees without managers (department heads)
SELECT
    u.display_name,
    e.job_title,
    e.department
FROM myapp.employee_details e
JOIN auth.user_info u ON e.user_id = u.user_id
WHERE e.manager_user_id IS NULL;

Application Functions¶

-- Create helper functions for your application
CREATE OR REPLACE FUNCTION myapp.get_user_complete_profile(
    _user_id uuid
) RETURNS TABLE (
    user_id uuid,
    username text,
    email text,
    display_name text,
    employee_number text,
    department text,
    job_title text,
    theme text,
    language text,
    bio text
) AS $$
BEGIN
    RETURN QUERY
    SELECT
        u.user_id,
        u.username,
        u.email,
        u.display_name,
        ed.employee_number,
        ed.department,
        ed.job_title,
        up.theme,
        up.language,
        upr.bio
    FROM auth.user_info u
    LEFT JOIN myapp.employee_details ed ON u.user_id = ed.user_id
    LEFT JOIN myapp.user_preferences up ON u.user_id = up.user_id
    LEFT JOIN myapp.user_profile upr ON u.user_id = upr.user_id
    WHERE u.user_id = _user_id;
END;
$$ LANGUAGE plpgsql;

-- Usage
SELECT * FROM myapp.get_user_complete_profile('user-uuid');

Decision Matrix¶

When to Use auth.user_data Extension¶

Choose this approach when: - Single application using the permission model - Simple, flat data structure - Need built-in function support - Want centralized user data management - Data fields are common across all system users

Example scenarios:

-- Simple employee data
ALTER TABLE auth.user_data
ADD COLUMN employee_id text,
ADD COLUMN department text,
ADD COLUMN hire_date date;

When to Use Separate Tables¶

Choose this approach when: - Multiple applications using the same permission model - Complex relational data structures - Need application-specific schemas - Want complete control over data model - Different applications have vastly different user data needs

Example scenarios:

-- Complex HR system
CREATE TABLE hr.employees (...);
CREATE TABLE hr.performance_reviews (...);
CREATE TABLE hr.time_off_requests (...);

-- E-commerce system
CREATE TABLE shop.customer_profiles (...);
CREATE TABLE shop.shipping_addresses (...);
CREATE TABLE shop.order_history (...);

Hybrid Approach¶

You can also combine both approaches:

-- Use auth.user_data for common fields
ALTER TABLE auth.user_data
ADD COLUMN employee_number text,
ADD COLUMN department text;

-- Use separate tables for complex application-specific data
CREATE TABLE myapp.project_assignments (
    assignment_id uuid PRIMARY KEY,
    user_id uuid REFERENCES auth.user_info(user_id),
    project_id uuid,
    role text,
    start_date date,
    end_date date
);

Performance Considerations¶

Indexing Strategy¶

-- For auth.user_data approach
CREATE INDEX idx_user_data_employee_number ON auth.user_data(employee_number);
CREATE INDEX idx_user_data_department ON auth.user_data(department);
CREATE INDEX idx_user_data_hire_date ON auth.user_data(hire_date);

-- For separate tables approach
CREATE INDEX idx_employee_details_department ON myapp.employee_details(department);
CREATE INDEX idx_employee_details_manager ON myapp.employee_details(manager_user_id);
CREATE INDEX idx_user_preferences_lookup ON myapp.user_preferences(user_id);

Query Optimization¶

-- Efficient joins for separate table approach
-- Use INNER JOIN when data must exist
SELECT u.display_name, ed.department
FROM auth.user_info u
INNER JOIN myapp.employee_details ed ON u.user_id = ed.user_id
WHERE ed.department = 'Engineering';

-- Use LEFT JOIN when data is optional
SELECT u.display_name, COALESCE(up.theme, 'default') as theme
FROM auth.user_info u
LEFT JOIN myapp.user_preferences up ON u.user_id = up.user_id;

Data Migration Patterns¶

From auth.user_data to Separate Tables¶

-- Migrate existing data to separate table
INSERT INTO myapp.employee_details (user_id, employee_number, department, hire_date)
SELECT user_id, employee_number, department, hire_date
FROM auth.user_data
WHERE employee_number IS NOT NULL;

-- Verify migration
SELECT COUNT(*) FROM myapp.employee_details;
SELECT COUNT(*) FROM auth.user_data WHERE employee_number IS NOT NULL;

From Separate Tables to auth.user_data¶

-- Add columns to auth.user_data
ALTER TABLE auth.user_data
ADD COLUMN employee_number text,
ADD COLUMN department text;

-- Migrate data
UPDATE auth.user_data
SET
    employee_number = ed.employee_number,
    department = ed.department
FROM myapp.employee_details ed
WHERE auth.user_data.user_id = ed.user_id;

Best Practices¶

General Guidelines¶

Plan for Growth: Consider future data needs when choosing approach
Consistent Naming: Use clear, consistent column/table naming
Foreign Key Constraints: Always reference auth.user_info.user_id
Cascade Deletes: Use ON DELETE CASCADE for cleanup
Audit Trail: Consider created_at/updated_at timestamps

Security Considerations¶

-- Secure sensitive data in separate tables
CREATE TABLE myapp.sensitive_employee_data (
    user_id uuid PRIMARY KEY REFERENCES auth.user_info(user_id),
    ssn_encrypted bytea,  -- Encrypted sensitive data
    salary_encrypted bytea,
    background_check_date date
);

-- Grant limited access
GRANT SELECT ON myapp.employee_details TO app_user;
GRANT SELECT ON myapp.sensitive_employee_data TO hr_admin ONLY;

Maintenance¶

-- Clean up orphaned data (if not using CASCADE)
DELETE FROM myapp.employee_details
WHERE user_id NOT IN (SELECT user_id FROM auth.user_info);

-- Find users without extended data
SELECT u.user_id, u.username
FROM auth.user_info u
LEFT JOIN myapp.employee_details ed ON u.user_id = ed.user_id
WHERE ed.user_id IS NULL;

What's Next¶

Review User Functions reference
Learn about User Identity management
Explore Groups for user organization
Understand Permissions assignment