Authentification data structure example

pg.sql

-- Enable extensions for trigram index support
CREATE EXTENSION IF NOT EXISTS pg_trgm;

CREATE SCHEMA IF NOT EXISTS auth;

-- =============================================================================
-- Domains with proper validation
-- =============================================================================

-- Email domain: lowercase normalized, validated format
-- Using TEXT + generated column approach for proper Unicode handling
CREATE DOMAIN auth.email AS TEXT
  CHECK (
    VALUE = lower(VALUE) AND
    VALUE ~ '^[^@\s]+@[^@\s]+\.[^@\s]+$' AND
    length(VALUE) <= 254
  );
  COMMENT ON DOMAIN auth.email IS 'Email address - simple email validation';

-- Phone domain with E.164 format validation
CREATE DOMAIN auth.phone_e164 AS TEXT
  CHECK (
    VALUE ~ '^\+[1-9][0-9]{1,14}$'
  );
COMMENT ON DOMAIN auth.phone_e164 IS 'Phone number in E.164 format: +[country][number], 1-15 digits';

-- Role name (role id) domain
CREATE DOMAIN auth.role_name AS TEXT
  CHECK (
    VALUE ~ '^[a-z][a-z0-9_]{0,62}$' AND
    VALUE = lower(VALUE)
  );
COMMENT ON DOMAIN auth.role_name IS 'Role name/id: lowercase snake_case, 1-63 chars';


-- =============================================================================
-- Enums (Rust sqlx maps these with #[derive(sqlx::Type)])
-- =============================================================================

CREATE TYPE auth.user_status AS ENUM (
  'pending',    -- Awaiting email/phone verification
  'active',     -- Fully active user
  'suspended',  -- Temporarily disabled
  'deleted'     -- Soft-deleted (retained for audit)
);

CREATE TYPE auth.oauth_provider AS ENUM (
  'google',
  'github',
  'microsoft',
  'apple',
  'facebook'
);


-- =============================================================================
-- Tables
-- =============================================================================

-- Roles table
CREATE TABLE auth.role (
  id           auth.role_name PRIMARY KEY, -- Role name
  description  TEXT,
  permissions  JSONB NOT NULL DEFAULT '{}'::JSONB,
  is_system    BOOLEAN NOT NULL DEFAULT FALSE,
  created_at   TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at   TIMESTAMPTZ NOT NULL DEFAULT now(),
  deleted_at   TIMESTAMPTZ,                -- Soft-delete timestamp for roles
  CONSTRAINT role_permissions_valid_ck
    CHECK (jsonb_typeof(permissions) = 'object')
);
COMMENT ON TABLE auth.role IS 'User roles with associated permissions';
COMMENT ON COLUMN auth.role.permissions IS 'JSON object mapping permission keys to boolean or scope values';

-- Protect system roles
CREATE INDEX role_is_system_ix ON auth.role (is_system) WHERE is_system = TRUE;

-- Users table
CREATE TABLE auth.user (
  id              UUID PRIMARY KEY DEFAULT uuidv7(), -- PK: UUIDv7 for time-ordered IDs
  id_role         auth.role_name NOT NULL              -- FK: restrict deletion if in use
                  CONSTRAINT user_id_role_fk
                  REFERENCES auth.role(id)
                  ON UPDATE CASCADE ON DELETE RESTRICT,

  -- Primary email and phone (from first OAuth provider or manually set)
  email           auth.email,                     -- Email, UNIQUE by index for active
  email_verified  BOOLEAN NOT NULL DEFAULT FALSE,
  phone           auth.phone_e164,
  phone_verified  BOOLEAN NOT NULL DEFAULT FALSE, -- Phone, E.164 format, UNIQUE by index for active

  -- Account status
  status          auth.user_status NOT NULL DEFAULT 'pending',

  -- Security
  password        TEXT -- Hashed password, NULL for OAuth-only users. Argon2id ~97 chars, bcrypt ~60 chars
                  CONSTRAINT user_password_hash_len_ck
                  CHECK (password IS NULL OR length(password) BETWEEN 50 AND 255),
  failed_login_attempts  SMALLINT NOT NULL DEFAULT 0
                         CONSTRAINT user_failed_login_range_ck
                         CHECK (failed_login_attempts BETWEEN 0 AND 100),
  locked_until           TIMESTAMPTZ,

  -- Timestamps
  created_at     TIMESTAMPTZ NOT NULL DEFAULT now(),  -- Creation timestamp (UTC)
  updated_at     TIMESTAMPTZ NOT NULL DEFAULT now(),  -- Last update timestamp (UTC)
  deleted_at     TIMESTAMPTZ,                         -- Soft-delete timestamp (NULL if active)

  -- Ensure phone_verified is FALSE when phone is NULL
  CONSTRAINT user_phone_verified_ck
    CHECK (phone IS NOT NULL OR phone_verified = FALSE),

  -- Ensure email_verified is FALSE for pending users
  CONSTRAINT user_email_verified_ck
    CHECK (email IS NOT NULL OR email_verified = FALSE),

  -- Password required for non-OAuth active users (enforced at app level typically)
  CONSTRAINT user_locked_until_ck
    CHECK (locked_until IS NULL OR locked_until > created_at),

  -- At least one auth method required for active users
  CONSTRAINT user_auth_method_ck
    CHECK (status != 'active' OR password IS NOT NULL OR email IS NOT NULL)
);
COMMENT ON TABLE auth.user IS 'Core user accounts';
COMMENT ON COLUMN auth.user.password IS 'Argon2id hashed password, NULL for OAuth-only accounts';
COMMENT ON COLUMN auth.user.email IS 'Primary email address (stored lowercase for case-insensitive matching)';

-- Unique constraints for active users only (soft-delete friendly)
CREATE UNIQUE INDEX user_email_active_ux
  ON auth.user (email)
  WHERE deleted_at IS NULL AND email IS NOT NULL;  -- no two active users can share the same email
CREATE UNIQUE INDEX user_phone_active_ux
  ON auth.user (phone)
  WHERE deleted_at IS NULL AND phone IS NOT NULL;  -- no two active users share the same phone number

-- For admin queries on problem accounts
CREATE INDEX user_status_suspended_ix
  ON auth.user (id)
  WHERE status IN ('suspended', 'pending') AND deleted_at IS NULL;


-- User Profile table 1:1
CREATE TABLE auth.user_profile (
  id_user         UUID PRIMARY KEY
                  CONSTRAINT user_profile_id_user_fk
                  REFERENCES auth.user(id) ON DELETE CASCADE,

  -- Display info
  display_name    TEXT NOT NULL
                  CONSTRAINT user_profile_display_name_len_ck
                  CHECK (length(display_name) BETWEEN 1 AND 100),
  display_name_normalized TEXT GENERATED ALWAYS AS (
    lower(normalize(display_name, NFC))
  ) STORED, -- Normalized version for searching (NORMALIZE function)
  avatar_url      TEXT
                  CONSTRAINT user_profile_avatar_url_ck
                  CHECK (
                    avatar_url IS NULL OR
                    (length(avatar_url) <= 2048 AND avatar_url ~ '^https?://')
                  ),
  locale          VARCHAR(35) NOT NULL DEFAULT 'en'  -- BCP 47 max length
                  CONSTRAINT user_profile_locale_ck
                  CHECK (locale ~ '^[a-z]{2,3}(-[A-Za-z]{4})?(-([A-Z]{2}|[0-9]{3}))?(-([A-Za-z0-9]{5,8}|[0-9][A-Za-z0-9]{3}))*$'),
  timezone        VARCHAR(64) NOT NULL DEFAULT 'UTC'  -- IANA timezone
                  CONSTRAINT user_profile_timezone_ck
                  CHECK (timezone ~ '^[A-Za-z_]+(/[A-Za-z_]+)*$'),

  -- Optional extended info (JSON for flexibility)
  metadata        JSONB NOT NULL DEFAULT '{}'::JSONB
                  CONSTRAINT user_profile_metadata_ck
                  CHECK (jsonb_typeof(metadata) = 'object'),

  -- Timestamps
  created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at      TIMESTAMPTZ NOT NULL DEFAULT now()
);
COMMENT ON TABLE auth.user_profile IS 'Extended user profile information';
COMMENT ON COLUMN auth.user_profile.display_name_normalized IS 'NFC-normalized lowercase display name for searching';

-- Trigram index on normalized name for fuzzy search
CREATE INDEX user_profile_display_name_trgm_ix
  ON auth.user_profile
  USING GIN (display_name_normalized gin_trgm_ops);


-- User Sessions table
CREATE TABLE auth.user_session (
  id             UUID PRIMARY KEY DEFAULT uuidv7(),
  id_user        UUID NOT NULL
                 CONSTRAINT user_session_id_user_fk
                 REFERENCES auth.user(id) ON DELETE CASCADE,
  device_id      TEXT
                 CONSTRAINT user_session_device_id_ck
                 CHECK (device_id IS NULL OR length(device_id) BETWEEN 1 AND 255),
  device_name    VARCHAR(255),        -- "iPhone 15 Pro", "Chrome on macOS"
  device_type    VARCHAR(30),         -- 'mobile', 'tablet', 'desktop', 'unknown'
  client_version VARCHAR(50),         -- App version if applicable


  -- Token storage (SHA-256 = 32 bytes, hash of the actual token)
  -- SELECT encode(refresh_token, 'hex') AS token_hex FROM
  refresh_token  BYTEA NOT NULL
                 CONSTRAINT user_session_token_len_ck
                 CHECK (octet_length(refresh_token) = 32),

  ip_created_by   INET,               -- IP at session creation
  ip_address      INET,               -- Last seen IP
  ip_country      VARCHAR(2),         -- ISO country code from IP

  -- Session metadata (user agent, IP, device fingerprint)
  metadata       JSONB NOT NULL DEFAULT '{}'::JSONB
                 CONSTRAINT user_session_metadata_ck
                 CHECK (jsonb_typeof(metadata) = 'object'),

  -- Timestamps
  expires_at     TIMESTAMPTZ NOT NULL, -- Session validity
  last_used_at   TIMESTAMPTZ NOT NULL DEFAULT now(),
  created_at     TIMESTAMPTZ NOT NULL DEFAULT now(),

  CONSTRAINT user_session_expires_ck
    CHECK (expires_at > created_at)
);
COMMENT ON TABLE auth.user_session IS 'User sessions - validity controlled solely by expires_at';
COMMENT ON COLUMN auth.user_session.refresh_token IS 'SHA-256 hash of the refresh token (32 bytes)';

-- Token lookup
CREATE UNIQUE INDEX user_session_token_ux
  ON auth.user_session (refresh_token);

-- Active sessions per user
CREATE INDEX user_session_id_user_ix
  ON auth.user_session (id_user, expires_at DESC);

-- Cleanup job index (find expired sessions to delete)
CREATE INDEX user_session_expires_at_ix
  ON auth.user_session (expires_at);


-- OAuth Provider Links table
CREATE TABLE auth.auth_provider (
  id_user         UUID NOT NULL
                  CONSTRAINT auth_provider_id_user_fk
                  REFERENCES auth.user(id) ON DELETE CASCADE,

  -- Provider identification
  provider        auth.oauth_provider NOT NULL,
  provider_uid   TEXT NOT NULL     -- Provider's unique user ID
                  CONSTRAINT auth_provider_provider_uid_len_ck
                  CHECK (length(provider_uid) BETWEEN 1 AND 255),

  -- Provider-supplied data
  email           auth.email,      -- Email from this provider
  name            VARCHAR(255),    -- Name from provider
  avatar_url      TEXT,            -- Avatar from provider
  provider_data   JSONB NOT NULL DEFAULT '{}'::JSONB
                  CONSTRAINT auth_provider_provider_data_ck
                  CHECK (jsonb_typeof(provider_data) = 'object'),

  -- OAuth scopes granted (stored as JSON array)
  scopes          JSONB NOT NULL DEFAULT '[]'::JSONB
                  CONSTRAINT auth_provider_scopes_ck
                  CHECK (jsonb_typeof(scopes) = 'array'),

  -- Timestamps
  linked_at       TIMESTAMPTZ NOT NULL DEFAULT now(),
  last_login_at   TIMESTAMPTZ,
  updated_at      TIMESTAMPTZ NOT NULL DEFAULT now(),

  PRIMARY KEY(id_user, provider)
);
COMMENT ON TABLE auth.auth_provider IS 'OAuth provider links for federated authentication';

-- Unique constraint: one user per provider account
CREATE UNIQUE INDEX auth_provider_provider_uid_ux
  ON auth.auth_provider (provider, provider_uid);

-- Index for user lookups
CREATE INDEX auth_provider_id_user_ix
  ON auth.auth_provider (id_user);

CREATE INDEX auth_provider_email_ix
  ON auth.auth_provider (email) WHERE email IS NOT NULL;



-- =============================================================================
-- Functions
-- =============================================================================

-- Normalize email before insert/update (ensures consistency)
CREATE OR REPLACE FUNCTION auth.normalize_email(email TEXT)
RETURNS auth.email
IMMUTABLE
PARALLEL SAFE
LANGUAGE SQL
RETURN lower(trim(email));

-- Auto-update updated_at timestamp
CREATE OR REPLACE FUNCTION auth.set_updated_at()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
  NEW.updated_at = now();
  RETURN NEW;
END;
$$;

-- Create user with profile atomically
CREATE OR REPLACE FUNCTION auth.create_user_with_profile(
  p_email TEXT,
  p_password_hash TEXT DEFAULT NULL,
  p_role auth.role_name DEFAULT 'user',
  p_display_name TEXT DEFAULT NULL,
  p_locale VARCHAR(35) DEFAULT 'en',
  p_timezone VARCHAR(64) DEFAULT 'UTC'
)
RETURNS UUID
LANGUAGE plpgsql
AS $$
DECLARE
  v_id_user UUID;
  v_display_name TEXT;
BEGIN
  v_display_name := COALESCE(p_display_name, split_part(p_email, '@', 1));

  INSERT INTO auth.user (email, password, id_role)
  VALUES (auth.normalize_email(p_email), p_password_hash, p_role)
  RETURNING id INTO v_id_user;

  INSERT INTO auth.user_profile (id_user, display_name, locale, timezone)
  VALUES (v_id_user, v_display_name, p_locale, p_timezone);

  RETURN v_id_user;
END;
$$;

-- Link OAuth provider to user
CREATE OR REPLACE FUNCTION auth.link_oauth_provider(
  p_id_user UUID,
  p_provider auth.oauth_provider,
  p_provider_uid TEXT,
  p_email TEXT DEFAULT NULL,
  p_email_verified BOOLEAN DEFAULT FALSE,
  p_provider_data JSONB DEFAULT '{}'::JSONB
)
RETURNS UUID
LANGUAGE plpgsql
AS $$
DECLARE
  v_id UUID;
BEGIN
  INSERT INTO auth.auth_provider (
    id_user, provider, provider_uid,
    email, email_verified, provider_data
  )
  VALUES (
    p_id_user, p_provider, p_provider_uid,
    CASE WHEN p_email IS NOT NULL THEN auth.normalize_email(p_email) END,
    p_email_verified, p_provider_data
  )
  ON CONFLICT (id_user, provider) DO UPDATE SET
    provider_uid = EXCLUDED.provider_uid,
    email = EXCLUDED.email,
    email_verified = EXCLUDED.email_verified,
    provider_data = EXCLUDED.provider_data,
    last_login_at = now(),
    updated_at = now()
  RETURNING id INTO v_id;

  RETURN v_id;
END;
$$;

-- Validate and refresh session (sliding expiration)
CREATE OR REPLACE FUNCTION auth.touch_session(
  p_token_hash BYTEA,
  p_extend_by INTERVAL DEFAULT INTERVAL '7 days'
)
RETURNS UUID
LANGUAGE SQL
AS $$
  UPDATE auth.user_session
  SET
    last_used_at = now(),
    expires_at = now() + p_extend_by
  WHERE refresh_token = p_token_hash
    AND expires_at > now()
  RETURNING id_user;
$$;

-- Revoke session (set expiry to now)
CREATE OR REPLACE FUNCTION auth.revoke_session(p_session_id UUID)
RETURNS BOOLEAN
LANGUAGE SQL
AS $$
  UPDATE auth.user_session
  SET expires_at = now()
  WHERE id = p_session_id
    AND expires_at > now()
  RETURNING TRUE;
$$;

-- Revoke all user sessions (logout everywhere)
CREATE OR REPLACE FUNCTION auth.revoke_all_sessions(p_id_user UUID)
RETURNS INT
LANGUAGE SQL
AS $$
  UPDATE auth.user_session
  SET expires_at = now()
  WHERE id_user = p_id_user
    AND expires_at > now()
  RETURNING 1;
$$;

-- Cleanup expired sessions (run periodically via pg_cron or app)
CREATE OR REPLACE FUNCTION auth.cleanup_expired_sessions(
  p_older_than INTERVAL DEFAULT INTERVAL '30 days'
)
RETURNS INT
LANGUAGE SQL
AS $$
  DELETE FROM auth.user_session
  WHERE expires_at < now() - p_older_than
  RETURNING 1;
$$;

-- =============================================================================
-- Triggers
-- =============================================================================

CREATE TRIGGER user_role_updated_at_tr
  BEFORE UPDATE ON auth.role
  FOR EACH ROW EXECUTE FUNCTION auth.set_updated_at();

CREATE TRIGGER user_updated_at_tr
  BEFORE UPDATE ON auth.user
  FOR EACH ROW EXECUTE FUNCTION auth.set_updated_at();

CREATE TRIGGER user_profile_updated_at_tr
  BEFORE UPDATE ON auth.user_profile
  FOR EACH ROW EXECUTE FUNCTION auth.set_updated_at();

CREATE TRIGGER auth_provider_updated_at_tr
  BEFORE UPDATE ON auth.auth_provider
  FOR EACH ROW EXECUTE FUNCTION auth.set_updated_at();

-- =============================================================================
-- Views
-- =============================================================================

-- View: Consolidated user info with profile and linked auth providers
CREATE OR REPLACE VIEW auth.v_user_full AS
SELECT
  u.id,
  u.email,
  u.email_verified,
  u.phone,
  u.phone_verified,
  u.status,
  u.id_role,
  r.permissions AS role_permissions,
  u.created_at,
  p.display_name,
  p.avatar_url,
--   p.avatar_blurhash,
  p.locale,
  p.timezone,
  COALESCE(
    jsonb_agg(
      jsonb_build_object(
        'provider', ap.provider,
        'email', ap.email,
        'linked_at', ap.linked_at,
        'last_login_at', ap.last_login_at
      )
    ) FILTER (WHERE ap.provider IS NOT NULL),
    '[]'::JSONB
  ) AS providers,
  (SELECT COUNT(*)::INT
   FROM auth.user_session s
   WHERE s.id_user = u.id
     AND s.expires_at > now()) AS active_sessions
FROM auth.user u
LEFT JOIN auth.role r ON r.id = u.id_role
LEFT JOIN auth.user_profile p ON p.id_user = u.id
LEFT JOIN auth.auth_provider ap ON ap.id_user = u.id
WHERE u.deleted_at IS NULL
GROUP BY u.id, r.id, p.id_user;
COMMENT ON VIEW auth.v_user_full IS 'Complete user view with profile, role, and OAuth providers';

-- =============================================================================
-- Initial Data
-- =============================================================================

INSERT INTO auth.role (id, description, permissions, is_system) VALUES
  ('admin', 'Full system administrator', '{"*": true}'::JSONB, TRUE),
  ('user', 'Standard user account', '{"profile:read": true, "profile:write": true}'::JSONB, TRUE),
  ('guest', 'Limited guest access', '{"profile:read": true}'::JSONB, TRUE)
ON CONFLICT (id) DO NOTHING;