rckrdmrd 59f1e3badf Initial commit - erp-core

2026-01-04 06:12:07 -06:00

26 KiB

Raw Permalink Blame History

DDL-SPEC: Schema core_users

Identificacion

Campo	Valor
Schema	core_users
Modulo	MGN-002
Version	1.0
Estado	En Diseño
Autor	System
Fecha	2025-12-05

Descripcion General

El schema core_users contiene la tabla principal de usuarios y todas las tablas relacionadas con la gestion de perfiles, preferencias y cambios de identidad. Es el schema central de identidad del sistema.

Alcance

Tabla principal de usuarios
Avatares e historial de imagenes
Solicitudes de cambio de email
Preferencias de usuario
Activacion de cuentas

Nota: El historial de passwords reside en core_auth.password_history por coherencia con el modulo de autenticacion.

Diagrama Entidad-Relacion

erDiagram
    tenants ||--o{ users : "contains"
    users ||--o| user_preferences : "has"
    users ||--o{ user_avatars : "uploads"
    users ||--o{ email_change_requests : "requests"
    users ||--o{ user_activation_tokens : "receives"
    users }o--o{ user_roles : "has"
    roles ||--o{ user_roles : "assigned to"

    users {
        uuid id PK
        uuid tenant_id FK
        varchar email UK
        varchar password_hash
        varchar first_name
        varchar last_name
        varchar phone
        varchar avatar_url
        varchar avatar_thumbnail_url
        enum status
        boolean is_active
        timestamptz email_verified_at
        timestamptz last_login_at
        integer failed_login_attempts
        timestamptz locked_until
        jsonb metadata
        timestamptz created_at
        uuid created_by FK
        timestamptz updated_at
        uuid updated_by FK
        timestamptz deleted_at
        uuid deleted_by FK
    }

    user_preferences {
        uuid id PK
        uuid user_id FK
        uuid tenant_id FK
        varchar language
        varchar timezone
        varchar date_format
        varchar time_format
        varchar theme
        boolean sidebar_collapsed
        boolean compact_mode
        varchar font_size
        jsonb notifications
        jsonb dashboard
        jsonb metadata
        timestamptz created_at
        timestamptz updated_at
    }

    user_avatars {
        uuid id PK
        uuid user_id FK
        uuid tenant_id FK
        varchar original_url
        varchar main_url
        varchar thumbnail_url
        varchar mime_type
        integer file_size
        boolean is_current
        timestamptz created_at
    }

    email_change_requests {
        uuid id PK
        uuid user_id FK
        uuid tenant_id FK
        varchar current_email
        varchar new_email
        varchar token_hash
        timestamptz expires_at
        timestamptz completed_at
        timestamptz created_at
    }

    user_activation_tokens {
        uuid id PK
        uuid user_id FK
        uuid tenant_id FK
        varchar token_hash
        timestamptz expires_at
        timestamptz used_at
        timestamptz created_at
    }

    user_roles {
        uuid id PK
        uuid user_id FK
        uuid role_id FK
        uuid tenant_id FK
        timestamptz created_at
        uuid created_by FK
    }

    roles {
        uuid id PK
        uuid tenant_id FK
        varchar name
        varchar description
        boolean is_system
        timestamptz created_at
    }

Tablas

1. users

Tabla principal que almacena la informacion de todos los usuarios del sistema.

Estructura

Columna	Tipo	Nullable	Default	Descripcion
`id`	UUID	NOT NULL	gen_random_uuid()	PK
`tenant_id`	UUID	NOT NULL	-	FK → core_tenants.tenants
`email`	VARCHAR(255)	NOT NULL	-	Email unico por tenant
`password_hash`	VARCHAR(255)	NOT NULL	-	Hash bcrypt del password
`first_name`	VARCHAR(100)	NOT NULL	-	Nombre
`last_name`	VARCHAR(100)	NOT NULL	-	Apellido
`phone`	VARCHAR(20)	NULL	-	Telefono E.164
`avatar_url`	VARCHAR(500)	NULL	-	URL avatar principal
`avatar_thumbnail_url`	VARCHAR(500)	NULL	-	URL thumbnail 50x50
`status`	user_status	NOT NULL	'pending_activation'	Estado del usuario
`is_active`	BOOLEAN	NOT NULL	false	Si puede operar
`email_verified_at`	TIMESTAMPTZ	NULL	-	Cuando verifico email
`last_login_at`	TIMESTAMPTZ	NULL	-	Ultimo login
`failed_login_attempts`	INTEGER	NOT NULL	0	Intentos fallidos
`locked_until`	TIMESTAMPTZ	NULL	-	Bloqueado hasta
`metadata`	JSONB	NULL	'{}'	Datos adicionales
`created_at`	TIMESTAMPTZ	NOT NULL	NOW()	Fecha creacion
`created_by`	UUID	NULL	-	Usuario que creo
`updated_at`	TIMESTAMPTZ	NOT NULL	NOW()	Ultima actualizacion
`updated_by`	UUID	NULL	-	Usuario que actualizo
`deleted_at`	TIMESTAMPTZ	NULL	-	Soft delete timestamp
`deleted_by`	UUID	NULL	-	Usuario que elimino

Enum user_status

CREATE TYPE core_users.user_status AS ENUM (
    'pending_activation',
    'active',
    'inactive',
    'locked'
);

Constraints

-- Primary Key
CONSTRAINT pk_users PRIMARY KEY (id),

-- Unique (email unico por tenant, excluyendo eliminados)
CONSTRAINT uk_users_tenant_email UNIQUE (tenant_id, email) WHERE deleted_at IS NULL,

-- Foreign Keys
CONSTRAINT fk_users_tenant
    FOREIGN KEY (tenant_id) REFERENCES core_tenants.tenants(id) ON DELETE CASCADE,
CONSTRAINT fk_users_created_by
    FOREIGN KEY (created_by) REFERENCES core_users.users(id) ON DELETE SET NULL,
CONSTRAINT fk_users_updated_by
    FOREIGN KEY (updated_by) REFERENCES core_users.users(id) ON DELETE SET NULL,
CONSTRAINT fk_users_deleted_by
    FOREIGN KEY (deleted_by) REFERENCES core_users.users(id) ON DELETE SET NULL,

-- Check
CONSTRAINT chk_users_email_format
    CHECK (email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'),
CONSTRAINT chk_users_phone_format
    CHECK (phone IS NULL OR phone ~* '^\+[0-9]{10,15}$'),
CONSTRAINT chk_users_failed_attempts
    CHECK (failed_login_attempts >= 0)

Indices

CREATE INDEX idx_users_tenant_id ON core_users.users(tenant_id);
CREATE INDEX idx_users_email ON core_users.users(email);
CREATE INDEX idx_users_status ON core_users.users(status) WHERE deleted_at IS NULL;
CREATE INDEX idx_users_is_active ON core_users.users(is_active) WHERE deleted_at IS NULL;
CREATE INDEX idx_users_created_at ON core_users.users(created_at DESC);
CREATE INDEX idx_users_full_name ON core_users.users(tenant_id, first_name, last_name) WHERE deleted_at IS NULL;
CREATE INDEX idx_users_not_deleted ON core_users.users(tenant_id) WHERE deleted_at IS NULL;

2. user_preferences

Almacena las preferencias personalizadas de cada usuario.

Estructura

Columna	Tipo	Nullable	Default	Descripcion
`id`	UUID	NOT NULL	gen_random_uuid()	PK
`user_id`	UUID	NOT NULL	-	FK → users (UNIQUE)
`tenant_id`	UUID	NOT NULL	-	FK → tenants
`language`	VARCHAR(5)	NOT NULL	'es'	Codigo idioma ISO
`timezone`	VARCHAR(50)	NOT NULL	'America/Mexico_City'	IANA timezone
`date_format`	VARCHAR(20)	NOT NULL	'DD/MM/YYYY'	Formato fecha
`time_format`	VARCHAR(5)	NOT NULL	'24h'	12h o 24h
`currency`	VARCHAR(3)	NOT NULL	'MXN'	Moneda ISO 4217
`number_format`	VARCHAR(10)	NOT NULL	'es-MX'	Formato numeros
`theme`	VARCHAR(10)	NOT NULL	'system'	light, dark, system
`sidebar_collapsed`	BOOLEAN	NOT NULL	false	Sidebar colapsado
`compact_mode`	BOOLEAN	NOT NULL	false	Modo compacto
`font_size`	VARCHAR(10)	NOT NULL	'medium'	small, medium, large
`notifications`	JSONB	NOT NULL	'{}'	Config notificaciones
`dashboard`	JSONB	NOT NULL	'{}'	Config dashboard
`metadata`	JSONB	NULL	'{}'	Datos adicionales
`created_at`	TIMESTAMPTZ	NOT NULL	NOW()	Fecha creacion
`updated_at`	TIMESTAMPTZ	NOT NULL	NOW()	Ultima actualizacion

Constraints

CONSTRAINT pk_user_preferences PRIMARY KEY (id),
CONSTRAINT uk_user_preferences_user UNIQUE (user_id),
CONSTRAINT fk_user_preferences_user
    FOREIGN KEY (user_id) REFERENCES core_users.users(id) ON DELETE CASCADE,
CONSTRAINT fk_user_preferences_tenant
    FOREIGN KEY (tenant_id) REFERENCES core_tenants.tenants(id) ON DELETE CASCADE,
CONSTRAINT chk_user_preferences_language
    CHECK (language IN ('es', 'en', 'pt')),
CONSTRAINT chk_user_preferences_theme
    CHECK (theme IN ('light', 'dark', 'system')),
CONSTRAINT chk_user_preferences_font_size
    CHECK (font_size IN ('small', 'medium', 'large')),
CONSTRAINT chk_user_preferences_time_format
    CHECK (time_format IN ('12h', '24h'))

Indices

CREATE INDEX idx_user_preferences_user_id ON core_users.user_preferences(user_id);
CREATE INDEX idx_user_preferences_tenant_id ON core_users.user_preferences(tenant_id);

3. user_avatars

Historial de avatares subidos por usuarios.

Estructura

Columna	Tipo	Nullable	Default	Descripcion
`id`	UUID	NOT NULL	gen_random_uuid()	PK
`user_id`	UUID	NOT NULL	-	FK → users
`tenant_id`	UUID	NOT NULL	-	FK → tenants
`original_url`	VARCHAR(500)	NOT NULL	-	URL original
`main_url`	VARCHAR(500)	NOT NULL	-	URL 200x200
`thumbnail_url`	VARCHAR(500)	NOT NULL	-	URL 50x50
`mime_type`	VARCHAR(50)	NOT NULL	-	image/jpeg, etc
`file_size`	INTEGER	NOT NULL	-	Tamano en bytes
`is_current`	BOOLEAN	NOT NULL	true	Si es el actual
`created_at`	TIMESTAMPTZ	NOT NULL	NOW()	Fecha subida

Constraints

CONSTRAINT pk_user_avatars PRIMARY KEY (id),
CONSTRAINT fk_user_avatars_user
    FOREIGN KEY (user_id) REFERENCES core_users.users(id) ON DELETE CASCADE,
CONSTRAINT fk_user_avatars_tenant
    FOREIGN KEY (tenant_id) REFERENCES core_tenants.tenants(id) ON DELETE CASCADE,
CONSTRAINT chk_user_avatars_mime_type
    CHECK (mime_type IN ('image/jpeg', 'image/png', 'image/webp')),
CONSTRAINT chk_user_avatars_file_size
    CHECK (file_size > 0 AND file_size <= 10485760) -- 10MB max

Indices

CREATE INDEX idx_user_avatars_user_id ON core_users.user_avatars(user_id);
CREATE INDEX idx_user_avatars_current ON core_users.user_avatars(user_id) WHERE is_current = true;

4. email_change_requests

Solicitudes de cambio de email pendientes.

Estructura

Columna	Tipo	Nullable	Default	Descripcion
`id`	UUID	NOT NULL	gen_random_uuid()	PK
`user_id`	UUID	NOT NULL	-	FK → users
`tenant_id`	UUID	NOT NULL	-	FK → tenants
`current_email`	VARCHAR(255)	NOT NULL	-	Email actual
`new_email`	VARCHAR(255)	NOT NULL	-	Nuevo email
`token_hash`	VARCHAR(255)	NOT NULL	-	Hash del token
`expires_at`	TIMESTAMPTZ	NOT NULL	-	Expiracion (24h)
`completed_at`	TIMESTAMPTZ	NULL	-	Cuando se completo
`created_at`	TIMESTAMPTZ	NOT NULL	NOW()	Fecha solicitud

Constraints

CONSTRAINT pk_email_change_requests PRIMARY KEY (id),
CONSTRAINT fk_email_change_requests_user
    FOREIGN KEY (user_id) REFERENCES core_users.users(id) ON DELETE CASCADE,
CONSTRAINT fk_email_change_requests_tenant
    FOREIGN KEY (tenant_id) REFERENCES core_tenants.tenants(id) ON DELETE CASCADE

Indices

CREATE INDEX idx_email_change_requests_user ON core_users.email_change_requests(user_id);
CREATE INDEX idx_email_change_requests_active ON core_users.email_change_requests(user_id)
    WHERE completed_at IS NULL AND expires_at > NOW();

5. user_activation_tokens

Tokens para activacion de cuentas nuevas.

Estructura

Columna	Tipo	Nullable	Default	Descripcion
`id`	UUID	NOT NULL	gen_random_uuid()	PK
`user_id`	UUID	NOT NULL	-	FK → users
`tenant_id`	UUID	NOT NULL	-	FK → tenants
`token_hash`	VARCHAR(255)	NOT NULL	-	Hash del token
`expires_at`	TIMESTAMPTZ	NOT NULL	-	Expiracion (24h)
`used_at`	TIMESTAMPTZ	NULL	-	Cuando se uso
`created_at`	TIMESTAMPTZ	NOT NULL	NOW()	Fecha creacion

Constraints

CONSTRAINT pk_user_activation_tokens PRIMARY KEY (id),
CONSTRAINT fk_user_activation_tokens_user
    FOREIGN KEY (user_id) REFERENCES core_users.users(id) ON DELETE CASCADE,
CONSTRAINT fk_user_activation_tokens_tenant
    FOREIGN KEY (tenant_id) REFERENCES core_tenants.tenants(id) ON DELETE CASCADE

Indices

CREATE INDEX idx_user_activation_tokens_user ON core_users.user_activation_tokens(user_id);
CREATE INDEX idx_user_activation_tokens_active ON core_users.user_activation_tokens(user_id)
    WHERE used_at IS NULL AND expires_at > NOW();

6. user_roles (Junction Table)

Relacion muchos-a-muchos entre usuarios y roles.

Estructura

Columna	Tipo	Nullable	Default	Descripcion
`id`	UUID	NOT NULL	gen_random_uuid()	PK
`user_id`	UUID	NOT NULL	-	FK → users
`role_id`	UUID	NOT NULL	-	FK → roles
`tenant_id`	UUID	NOT NULL	-	FK → tenants
`created_at`	TIMESTAMPTZ	NOT NULL	NOW()	Fecha asignacion
`created_by`	UUID	NULL	-	Quien asigno

Constraints

CONSTRAINT pk_user_roles PRIMARY KEY (id),
CONSTRAINT uk_user_roles UNIQUE (user_id, role_id),
CONSTRAINT fk_user_roles_user
    FOREIGN KEY (user_id) REFERENCES core_users.users(id) ON DELETE CASCADE,
CONSTRAINT fk_user_roles_role
    FOREIGN KEY (role_id) REFERENCES core_roles.roles(id) ON DELETE CASCADE,
CONSTRAINT fk_user_roles_tenant
    FOREIGN KEY (tenant_id) REFERENCES core_tenants.tenants(id) ON DELETE CASCADE,
CONSTRAINT fk_user_roles_created_by
    FOREIGN KEY (created_by) REFERENCES core_users.users(id) ON DELETE SET NULL

Indices

CREATE INDEX idx_user_roles_user ON core_users.user_roles(user_id);
CREATE INDEX idx_user_roles_role ON core_users.user_roles(role_id);
CREATE INDEX idx_user_roles_tenant ON core_users.user_roles(tenant_id);

Row Level Security (RLS)

Politicas de Seguridad

-- Habilitar RLS
ALTER TABLE core_users.users ENABLE ROW LEVEL SECURITY;
ALTER TABLE core_users.user_preferences ENABLE ROW LEVEL SECURITY;
ALTER TABLE core_users.user_avatars ENABLE ROW LEVEL SECURITY;
ALTER TABLE core_users.email_change_requests ENABLE ROW LEVEL SECURITY;
ALTER TABLE core_users.user_activation_tokens ENABLE ROW LEVEL SECURITY;
ALTER TABLE core_users.user_roles ENABLE ROW LEVEL SECURITY;

-- Politica: Usuarios solo ven usuarios de su tenant
CREATE POLICY tenant_isolation_users ON core_users.users
    FOR ALL
    USING (tenant_id = current_setting('app.current_tenant_id')::uuid);

-- Politica: Usuario ve su propio perfil o es admin
CREATE POLICY self_or_admin_users ON core_users.users
    FOR SELECT
    USING (
        id = current_setting('app.current_user_id')::uuid
        OR current_setting('app.is_admin')::boolean = true
    );

-- Politica: Solo el usuario ve sus preferencias
CREATE POLICY owner_only_preferences ON core_users.user_preferences
    FOR ALL
    USING (user_id = current_setting('app.current_user_id')::uuid);

-- Politica: Solo el usuario ve sus avatares
CREATE POLICY owner_only_avatars ON core_users.user_avatars
    FOR ALL
    USING (user_id = current_setting('app.current_user_id')::uuid);

-- Politica: Solo el usuario ve sus solicitudes de cambio
CREATE POLICY owner_only_email_changes ON core_users.email_change_requests
    FOR ALL
    USING (user_id = current_setting('app.current_user_id')::uuid);

Triggers

1. Actualizar updated_at automaticamente

CREATE OR REPLACE FUNCTION core_users.update_updated_at()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_users_updated_at
BEFORE UPDATE ON core_users.users
FOR EACH ROW
EXECUTE FUNCTION core_users.update_updated_at();

CREATE TRIGGER trg_user_preferences_updated_at
BEFORE UPDATE ON core_users.user_preferences
FOR EACH ROW
EXECUTE FUNCTION core_users.update_updated_at();

2. Crear preferencias por defecto al crear usuario

CREATE OR REPLACE FUNCTION core_users.create_default_preferences()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO core_users.user_preferences (user_id, tenant_id)
    VALUES (NEW.id, NEW.tenant_id);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_create_default_preferences
AFTER INSERT ON core_users.users
FOR EACH ROW
EXECUTE FUNCTION core_users.create_default_preferences();

3. Marcar avatar anterior como no actual

CREATE OR REPLACE FUNCTION core_users.update_current_avatar()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.is_current = true THEN
        UPDATE core_users.user_avatars
        SET is_current = false
        WHERE user_id = NEW.user_id
          AND id != NEW.id
          AND is_current = true;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_update_current_avatar
AFTER INSERT OR UPDATE ON core_users.user_avatars
FOR EACH ROW
WHEN (NEW.is_current = true)
EXECUTE FUNCTION core_users.update_current_avatar();

4. Sincronizar avatar_url en users

CREATE OR REPLACE FUNCTION core_users.sync_avatar_url()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.is_current = true THEN
        UPDATE core_users.users
        SET avatar_url = NEW.main_url,
            avatar_thumbnail_url = NEW.thumbnail_url
        WHERE id = NEW.user_id;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_sync_avatar_url
AFTER INSERT OR UPDATE ON core_users.user_avatars
FOR EACH ROW
WHEN (NEW.is_current = true)
EXECUTE FUNCTION core_users.sync_avatar_url();

Funciones de Utilidad

1. Buscar usuarios por texto

CREATE OR REPLACE FUNCTION core_users.search_users(
    p_tenant_id UUID,
    p_search TEXT,
    p_limit INTEGER DEFAULT 20,
    p_offset INTEGER DEFAULT 0
)
RETURNS TABLE (
    id UUID,
    email VARCHAR,
    first_name VARCHAR,
    last_name VARCHAR,
    status user_status,
    avatar_thumbnail_url VARCHAR
) AS $$
BEGIN
    RETURN QUERY
    SELECT
        u.id,
        u.email,
        u.first_name,
        u.last_name,
        u.status,
        u.avatar_thumbnail_url
    FROM core_users.users u
    WHERE u.tenant_id = p_tenant_id
      AND u.deleted_at IS NULL
      AND (
          u.email ILIKE '%' || p_search || '%'
          OR u.first_name ILIKE '%' || p_search || '%'
          OR u.last_name ILIKE '%' || p_search || '%'
          OR CONCAT(u.first_name, ' ', u.last_name) ILIKE '%' || p_search || '%'
      )
    ORDER BY u.first_name, u.last_name
    LIMIT p_limit
    OFFSET p_offset;
END;
$$ LANGUAGE plpgsql STABLE;

2. Obtener usuario con roles

CREATE OR REPLACE FUNCTION core_users.get_user_with_roles(p_user_id UUID)
RETURNS TABLE (
    user_data JSONB,
    roles JSONB
) AS $$
BEGIN
    RETURN QUERY
    SELECT
        to_jsonb(u.*) - 'password_hash' AS user_data,
        COALESCE(
            jsonb_agg(
                jsonb_build_object('id', r.id, 'name', r.name)
            ) FILTER (WHERE r.id IS NOT NULL),
            '[]'::jsonb
        ) AS roles
    FROM core_users.users u
    LEFT JOIN core_users.user_roles ur ON u.id = ur.user_id
    LEFT JOIN core_roles.roles r ON ur.role_id = r.id
    WHERE u.id = p_user_id
    GROUP BY u.id;
END;
$$ LANGUAGE plpgsql STABLE;

Scripts de Migracion

Crear Schema

-- 001_create_schema_core_users.sql
CREATE SCHEMA IF NOT EXISTS core_users;

COMMENT ON SCHEMA core_users IS 'Schema para gestion de usuarios, perfiles y preferencias';

-- Crear enum
CREATE TYPE core_users.user_status AS ENUM (
    'pending_activation',
    'active',
    'inactive',
    'locked'
);

Crear Tablas

-- 002_create_tables_core_users.sql

-- users
CREATE TABLE IF NOT EXISTS core_users.users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL,
    email VARCHAR(255) NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    phone VARCHAR(20),
    avatar_url VARCHAR(500),
    avatar_thumbnail_url VARCHAR(500),
    status core_users.user_status NOT NULL DEFAULT 'pending_activation',
    is_active BOOLEAN NOT NULL DEFAULT false,
    email_verified_at TIMESTAMPTZ,
    last_login_at TIMESTAMPTZ,
    failed_login_attempts INTEGER NOT NULL DEFAULT 0,
    locked_until TIMESTAMPTZ,
    metadata JSONB DEFAULT '{}',
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    created_by UUID,
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_by UUID,
    deleted_at TIMESTAMPTZ,
    deleted_by UUID,

    CONSTRAINT uk_users_tenant_email UNIQUE (tenant_id, email),
    CONSTRAINT chk_users_email_format
        CHECK (email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'),
    CONSTRAINT chk_users_phone_format
        CHECK (phone IS NULL OR phone ~* '^\+[0-9]{10,15}$'),
    CONSTRAINT chk_users_failed_attempts
        CHECK (failed_login_attempts >= 0)
);

-- user_preferences
CREATE TABLE IF NOT EXISTS core_users.user_preferences (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL UNIQUE,
    tenant_id UUID NOT NULL,
    language VARCHAR(5) NOT NULL DEFAULT 'es',
    timezone VARCHAR(50) NOT NULL DEFAULT 'America/Mexico_City',
    date_format VARCHAR(20) NOT NULL DEFAULT 'DD/MM/YYYY',
    time_format VARCHAR(5) NOT NULL DEFAULT '24h',
    currency VARCHAR(3) NOT NULL DEFAULT 'MXN',
    number_format VARCHAR(10) NOT NULL DEFAULT 'es-MX',
    theme VARCHAR(10) NOT NULL DEFAULT 'system',
    sidebar_collapsed BOOLEAN NOT NULL DEFAULT false,
    compact_mode BOOLEAN NOT NULL DEFAULT false,
    font_size VARCHAR(10) NOT NULL DEFAULT 'medium',
    notifications JSONB NOT NULL DEFAULT '{}',
    dashboard JSONB NOT NULL DEFAULT '{}',
    metadata JSONB DEFAULT '{}',
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),

    CONSTRAINT fk_user_preferences_user
        FOREIGN KEY (user_id) REFERENCES core_users.users(id) ON DELETE CASCADE,
    CONSTRAINT chk_user_preferences_language
        CHECK (language IN ('es', 'en', 'pt')),
    CONSTRAINT chk_user_preferences_theme
        CHECK (theme IN ('light', 'dark', 'system')),
    CONSTRAINT chk_user_preferences_font_size
        CHECK (font_size IN ('small', 'medium', 'large')),
    CONSTRAINT chk_user_preferences_time_format
        CHECK (time_format IN ('12h', '24h'))
);

-- user_avatars
CREATE TABLE IF NOT EXISTS core_users.user_avatars (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL,
    tenant_id UUID NOT NULL,
    original_url VARCHAR(500) NOT NULL,
    main_url VARCHAR(500) NOT NULL,
    thumbnail_url VARCHAR(500) NOT NULL,
    mime_type VARCHAR(50) NOT NULL,
    file_size INTEGER NOT NULL,
    is_current BOOLEAN NOT NULL DEFAULT true,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),

    CONSTRAINT fk_user_avatars_user
        FOREIGN KEY (user_id) REFERENCES core_users.users(id) ON DELETE CASCADE,
    CONSTRAINT chk_user_avatars_mime_type
        CHECK (mime_type IN ('image/jpeg', 'image/png', 'image/webp')),
    CONSTRAINT chk_user_avatars_file_size
        CHECK (file_size > 0 AND file_size <= 10485760)
);

-- email_change_requests
CREATE TABLE IF NOT EXISTS core_users.email_change_requests (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL,
    tenant_id UUID NOT NULL,
    current_email VARCHAR(255) NOT NULL,
    new_email VARCHAR(255) NOT NULL,
    token_hash VARCHAR(255) NOT NULL,
    expires_at TIMESTAMPTZ NOT NULL,
    completed_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),

    CONSTRAINT fk_email_change_requests_user
        FOREIGN KEY (user_id) REFERENCES core_users.users(id) ON DELETE CASCADE
);

-- user_activation_tokens
CREATE TABLE IF NOT EXISTS core_users.user_activation_tokens (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL,
    tenant_id UUID NOT NULL,
    token_hash VARCHAR(255) NOT NULL,
    expires_at TIMESTAMPTZ NOT NULL,
    used_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),

    CONSTRAINT fk_user_activation_tokens_user
        FOREIGN KEY (user_id) REFERENCES core_users.users(id) ON DELETE CASCADE
);

-- user_roles
CREATE TABLE IF NOT EXISTS core_users.user_roles (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL,
    role_id UUID NOT NULL,
    tenant_id UUID NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    created_by UUID,

    CONSTRAINT uk_user_roles UNIQUE (user_id, role_id),
    CONSTRAINT fk_user_roles_user
        FOREIGN KEY (user_id) REFERENCES core_users.users(id) ON DELETE CASCADE,
    CONSTRAINT fk_user_roles_created_by
        FOREIGN KEY (created_by) REFERENCES core_users.users(id) ON DELETE SET NULL
);

Consideraciones de Performance

Tabla	Volumen Esperado	Estrategia
users	Medio (miles)	Indices por tenant, paginacion
user_preferences	1:1 con users	Join eficiente por PK
user_avatars	Bajo (1-5 por user)	Cleanup de antiguos
email_change_requests	Bajo	TTL cleanup
user_activation_tokens	Bajo	TTL cleanup
user_roles	Bajo (1-5 por user)	Indices compuestos

Notas de Seguridad

Nunca exponer password_hash - Excluir en queries SELECT
RLS obligatorio - Aislamiento por tenant
Soft delete - Mantener historial para auditoria
Tokens hasheados - No almacenar tokens planos
Validacion de email - Prevenir inyeccion

Historial de Cambios

Version	Fecha	Autor	Cambios
1.0	2025-12-05	System	Creacion inicial

Aprobaciones

Rol	Nombre	Fecha	Firma
DBA	-	-	[ ]
Tech Lead	-	-	[ ]
Security	-	-	[ ]

26 KiB Raw Permalink Blame History

DDL-SPEC: Schema core_users

Identificacion

Descripcion General

Alcance

Diagrama Entidad-Relacion

Tablas

1. users

Estructura

Enum user_status

Constraints

Indices

2. user_preferences

Estructura

Constraints

Indices

3. user_avatars

Estructura

Constraints

Indices

4. email_change_requests

Estructura

Constraints

Indices

5. user_activation_tokens

Estructura

Constraints

Indices

6. user_roles (Junction Table)

Estructura

Constraints

Indices

Row Level Security (RLS)

Politicas de Seguridad

Triggers

1. Actualizar updated_at automaticamente

2. Crear preferencias por defecto al crear usuario

3. Marcar avatar anterior como no actual

4. Sincronizar avatar_url en users

Funciones de Utilidad

1. Buscar usuarios por texto

2. Obtener usuario con roles

Scripts de Migracion

Crear Schema

Crear Tablas

Consideraciones de Performance

Notas de Seguridad

Historial de Cambios

Aprobaciones

26 KiB

Raw Permalink Blame History