Modelo de Datos: Autenticacion¶

Identificador: MDL-AUTH-001 Version: 1.0.0 Fecha: 2025-12-07 Estado: Aprobado Autor: DatabaseDrone / SpecQueen Technical Division Modulo Funcional: MTS-AUTH-001

1. Introduccion
1.1. Principios
2. Entidades del Servidor
2.1. srv_users
- 2.1.1. Columnas
- 2.1.2. Reglas de Negocio
2.2. srv_sessions
- 2.2.1. Columnas
- 2.2.2. Duracion de Sesion por Tier
2.3. srv_devices
- 2.3.1. Columnas
2.4. srv_user_relations
- 2.4.1. Columnas
- 2.4.2. Estructura de Permissions (Descifrado en Cliente)
2.5. srv_invitations
- 2.5.1. Columnas
3. Entidades del Cliente
3.1. cli_auth_state (LOCAL_ONLY)
- 3.1.1. Clasificacion: LOCAL_ONLY
3.2. cli_keychain_items (LOCAL_ONLY)
4. Funciones de Base de Datos
4.1. Funcion: Crear Usuario desde Firebase
4.2. Funcion: Verificar Limite de Dispositivos
4.3. Funcion: Revocar Sesiones Antiguas
5. Migraciones
5.1. Migration 001: Create Auth Schema
6. Referencias Cruzadas

1. Introduccion¶

Este documento define el modelo de datos para el modulo de autenticacion de MedTime. Incluye tablas del servidor (PostgreSQL) y tablas del cliente (SQLite/Realm), respetando la arquitectura Zero-Knowledge.

1.1. Principios¶

Principio	Aplicacion
Firebase Auth	Autenticacion delegada a Firebase, servidor solo valida tokens
Zero-Knowledge	Servidor no almacena credenciales ni datos de sesion sensibles
Blind Index	Email y telefono almacenados como hash para lookup
RLS	Row Level Security basado en Firebase UID

2. Entidades del Servidor¶

2.1. srv_users¶

Tabla principal de usuarios. Solo almacena metadata y blind indexes.

CREATE TABLE srv_users (
    -- Primary Key
    user_id         UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    -- Firebase Integration
    firebase_uid    VARCHAR(128) NOT NULL UNIQUE,

    -- Blind Indexes (SHA-256 hashes for lookup, NOT for authentication)
    email_hash      CHAR(64),           -- SHA-256 of lowercase email
    phone_hash      CHAR(64),           -- SHA-256 of E.164 phone

    -- User Classification
    role            VARCHAR(20) NOT NULL CHECK (role IN ('PI', 'PD', 'CS', 'CR')),
    tier            VARCHAR(20) NOT NULL DEFAULT 'free' CHECK (tier IN ('free', 'pro', 'perfect')),

    -- Audit Timestamps
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    deleted_at      TIMESTAMPTZ,        -- Soft delete

    -- Constraints
    CONSTRAINT chk_cs_always_free CHECK (role != 'CS' OR tier = 'free'),
    CONSTRAINT chk_pd_no_direct_account CHECK (role != 'PD')  -- PD managed via cli_dependents
);

-- Indexes
CREATE UNIQUE INDEX idx_users_firebase_uid ON srv_users(firebase_uid);
CREATE INDEX idx_users_email_hash ON srv_users(email_hash) WHERE email_hash IS NOT NULL;
CREATE INDEX idx_users_phone_hash ON srv_users(phone_hash) WHERE phone_hash IS NOT NULL;
CREATE INDEX idx_users_role_tier ON srv_users(role, tier);
CREATE INDEX idx_users_deleted ON srv_users(deleted_at) WHERE deleted_at IS NOT NULL;

-- RLS Policy
ALTER TABLE srv_users ENABLE ROW LEVEL SECURITY;

CREATE POLICY users_self_access ON srv_users
    FOR ALL
    USING (firebase_uid = current_setting('app.firebase_uid', true));

CREATE POLICY users_admin_access ON srv_users
    FOR ALL
    USING (current_setting('app.is_admin', true) = 'true');

2.1.1. Columnas¶

Columna	Tipo	Nullable	Descripcion
user_id	UUID	No	Identificador unico interno
firebase_uid	VARCHAR(128)	No	UID de Firebase Authentication
email_hash	CHAR(64)	Si	SHA-256 del email (blind index)
phone_hash	CHAR(64)	Si	SHA-256 del telefono E.164
role	VARCHAR(20)	No	Rol: PI, PD, CS, CR
tier	VARCHAR(20)	No	Tier: free, pro, perfect
created_at	TIMESTAMPTZ	No	Fecha de creacion
updated_at	TIMESTAMPTZ	No	Ultima actualizacion
deleted_at	TIMESTAMPTZ	Si	Fecha de borrado (soft delete)

2.1.2. Reglas de Negocio¶

Regla	Constraint	Descripcion
RN-AUTH-001	chk_cs_always_free	CS siempre tiene tier=free
RN-AUTH-002	chk_pd_no_direct_account	PD no tiene cuenta directa (gestionado por CR)

2.2. srv_sessions¶

Registro de sesiones activas. El servidor solo almacena metadata, no tokens.

CREATE TABLE srv_sessions (
    -- Primary Key
    session_id      UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    -- Foreign Keys
    user_id         UUID NOT NULL REFERENCES srv_users(user_id) ON DELETE CASCADE,
    device_id       UUID REFERENCES srv_devices(device_id) ON DELETE SET NULL,

    -- Session Metadata
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    expires_at      TIMESTAMPTZ NOT NULL,
    last_activity   TIMESTAMPTZ NOT NULL DEFAULT NOW(),

    -- Revocation
    revoked_at      TIMESTAMPTZ,
    revoked_reason  VARCHAR(100),

    -- Security (hashed, not actual values)
    ip_hash         CHAR(64),           -- SHA-256 of IP address
    user_agent_hash CHAR(64),           -- SHA-256 of User-Agent

    -- Session Type
    mfa_verified    BOOLEAN NOT NULL DEFAULT FALSE,

    -- Constraints
    CONSTRAINT chk_session_dates CHECK (expires_at > created_at)
);

-- Indexes
CREATE INDEX idx_sessions_user ON srv_sessions(user_id);
CREATE INDEX idx_sessions_device ON srv_sessions(device_id);
CREATE INDEX idx_sessions_expires ON srv_sessions(expires_at) WHERE revoked_at IS NULL;
CREATE INDEX idx_sessions_active ON srv_sessions(user_id, revoked_at) WHERE revoked_at IS NULL;
CREATE INDEX idx_sessions_last_activity ON srv_sessions(last_activity) WHERE revoked_at IS NULL;

-- RLS Policy
ALTER TABLE srv_sessions ENABLE ROW LEVEL SECURITY;

CREATE POLICY sessions_self_access ON srv_sessions
    FOR ALL
    USING (user_id IN (
        SELECT user_id FROM srv_users
        WHERE firebase_uid = current_setting('app.firebase_uid', true)
    ));

2.2.1. Columnas¶

Columna	Tipo	Nullable	Descripcion
session_id	UUID	No	Identificador unico de sesion
user_id	UUID	No	FK a srv_users
device_id	UUID	Si	FK a srv_devices
created_at	TIMESTAMPTZ	No	Inicio de sesion
expires_at	TIMESTAMPTZ	No	Expiracion de sesion
last_activity	TIMESTAMPTZ	No	Ultima actividad
revoked_at	TIMESTAMPTZ	Si	Fecha de revocacion
revoked_reason	VARCHAR(100)	Si	Razon de revocacion
ip_hash	CHAR(64)	Si	Hash de IP (auditoria)
user_agent_hash	CHAR(64)	Si	Hash de User-Agent
mfa_verified	BOOLEAN	No	Si MFA fue verificado

2.2.2. Duracion de Sesion por Tier¶

Tier	Duracion	Max Dispositivos
free	N/A (local only)	1
pro	30 dias	3
perfect	7 dias	5

2.3. srv_devices¶

Dispositivos registrados por usuario.

CREATE TABLE srv_devices (
    -- Primary Key
    device_id       UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    -- Foreign Key
    user_id         UUID NOT NULL REFERENCES srv_users(user_id) ON DELETE CASCADE,

    -- Device Info (encrypted where sensitive)
    device_name_enc BYTEA,              -- Encrypted device name
    platform        VARCHAR(20) NOT NULL CHECK (platform IN ('ios', 'android', 'web')),
    os_version      VARCHAR(50),
    app_version     VARCHAR(20),

    -- Push Notifications (encrypted token)
    push_token_enc  BYTEA,              -- Encrypted FCM/APNs token
    push_enabled    BOOLEAN NOT NULL DEFAULT TRUE,

    -- Sync State
    last_sync_at    TIMESTAMPTZ,
    sync_version    BIGINT NOT NULL DEFAULT 0,

    -- Audit
    registered_at   TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    last_seen_at    TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    deactivated_at  TIMESTAMPTZ,

    -- Constraints
    CONSTRAINT chk_device_platform CHECK (platform IN ('ios', 'android', 'web'))
);

-- Indexes
CREATE INDEX idx_devices_user ON srv_devices(user_id);
CREATE INDEX idx_devices_platform ON srv_devices(platform);
CREATE INDEX idx_devices_active ON srv_devices(user_id, deactivated_at) WHERE deactivated_at IS NULL;
CREATE INDEX idx_devices_sync ON srv_devices(user_id, last_sync_at);

-- RLS Policy
ALTER TABLE srv_devices ENABLE ROW LEVEL SECURITY;

CREATE POLICY devices_self_access ON srv_devices
    FOR ALL
    USING (user_id IN (
        SELECT user_id FROM srv_users
        WHERE firebase_uid = current_setting('app.firebase_uid', true)
    ));

2.3.1. Columnas¶

Columna	Tipo	Nullable	Cifrado	Descripcion
device_id	UUID	No	No	Identificador unico
user_id	UUID	No	No	FK a srv_users
device_name_enc	BYTEA	Si	Si	Nombre del dispositivo cifrado
platform	VARCHAR(20)	No	No	ios, android, web
os_version	VARCHAR(50)	Si	No	Version del SO
app_version	VARCHAR(20)	Si	No	Version de la app
push_token_enc	BYTEA	Si	Si	Token push cifrado
push_enabled	BOOLEAN	No	No	Si push esta habilitado
last_sync_at	TIMESTAMPTZ	Si	No	Ultima sincronizacion
sync_version	BIGINT	No	No	Version de sync
registered_at	TIMESTAMPTZ	No	No	Fecha de registro
last_seen_at	TIMESTAMPTZ	No	No	Ultima actividad
deactivated_at	TIMESTAMPTZ	Si	No	Fecha de desactivacion

2.4. srv_user_relations¶

Relaciones entre usuarios (cuidador-paciente).

CREATE TABLE srv_user_relations (
    -- Primary Key
    relation_id     UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    -- Foreign Keys
    owner_user_id   UUID NOT NULL REFERENCES srv_users(user_id) ON DELETE CASCADE,
    related_user_id UUID NOT NULL REFERENCES srv_users(user_id) ON DELETE CASCADE,

    -- Relation Type
    relation_type   VARCHAR(20) NOT NULL CHECK (relation_type IN ('caregiver_of', 'patient_of')),

    -- Permissions (encrypted JSON blob)
    permissions_enc BYTEA,              -- Encrypted permissions object

    -- Audit
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    created_by      UUID REFERENCES srv_users(user_id),
    revoked_at      TIMESTAMPTZ,
    revoked_by      UUID REFERENCES srv_users(user_id),

    -- Constraints
    CONSTRAINT chk_no_self_relation CHECK (owner_user_id != related_user_id),
    CONSTRAINT uq_relation UNIQUE (owner_user_id, related_user_id, relation_type)
);

-- Indexes
CREATE INDEX idx_relations_owner ON srv_user_relations(owner_user_id);
CREATE INDEX idx_relations_related ON srv_user_relations(related_user_id);
CREATE INDEX idx_relations_active ON srv_user_relations(owner_user_id, revoked_at) WHERE revoked_at IS NULL;

-- RLS Policy
ALTER TABLE srv_user_relations ENABLE ROW LEVEL SECURITY;

CREATE POLICY relations_participant_access ON srv_user_relations
    FOR ALL
    USING (
        owner_user_id IN (SELECT user_id FROM srv_users WHERE firebase_uid = current_setting('app.firebase_uid', true))
        OR
        related_user_id IN (SELECT user_id FROM srv_users WHERE firebase_uid = current_setting('app.firebase_uid', true))
    );

2.4.1. Columnas¶

Columna	Tipo	Nullable	Cifrado	Descripcion
relation_id	UUID	No	No	Identificador unico
owner_user_id	UUID	No	No	Usuario propietario de la relacion
related_user_id	UUID	No	No	Usuario relacionado
relation_type	VARCHAR(20)	No	No	Tipo: caregiver_of, patient_of
permissions_enc	BYTEA	Si	Si	Permisos granulares cifrados
created_at	TIMESTAMPTZ	No	No	Fecha de creacion
created_by	UUID	Si	No	Quien creo la relacion
revoked_at	TIMESTAMPTZ	Si	No	Fecha de revocacion
revoked_by	UUID	Si	No	Quien revoco

2.4.2. Estructura de Permissions (Descifrado en Cliente)¶

{
  "ver_medicamentos": true,
  "ver_adherencia": true,
  "registrar_tomas": false,
  "recibir_alertas_tomas": true,
  "ver_recetas": false,
  "ver_citas": false,
  "ver_analisis": false
}

2.5. srv_invitations¶

Invitaciones pendientes para cuidadores solidarios.

CREATE TABLE srv_invitations (
    -- Primary Key
    invitation_id   UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    -- Foreign Key
    inviter_user_id UUID NOT NULL REFERENCES srv_users(user_id) ON DELETE CASCADE,

    -- Invitee (blind index, no PII)
    invitee_email_hash CHAR(64),        -- SHA-256 of email
    invitee_phone_hash CHAR(64),        -- SHA-256 of phone

    -- Token (hashed, actual token sent via email/sms)
    token_hash      CHAR(64) NOT NULL,  -- SHA-256 of invitation token

    -- Status
    status          VARCHAR(20) NOT NULL DEFAULT 'pending'
                    CHECK (status IN ('pending', 'accepted', 'declined', 'expired', 'cancelled')),

    -- Permissions offered (encrypted)
    permissions_enc BYTEA,

    -- Expiration
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    expires_at      TIMESTAMPTZ NOT NULL,
    responded_at    TIMESTAMPTZ,

    -- Accepted user (if accepted)
    accepted_user_id UUID REFERENCES srv_users(user_id),

    -- Constraints
    CONSTRAINT chk_has_contact CHECK (invitee_email_hash IS NOT NULL OR invitee_phone_hash IS NOT NULL),
    CONSTRAINT chk_expiry CHECK (expires_at > created_at)
);

-- Indexes
CREATE INDEX idx_invitations_inviter ON srv_invitations(inviter_user_id);
CREATE INDEX idx_invitations_token ON srv_invitations(token_hash);
CREATE INDEX idx_invitations_email ON srv_invitations(invitee_email_hash) WHERE invitee_email_hash IS NOT NULL;
CREATE INDEX idx_invitations_pending ON srv_invitations(status, expires_at) WHERE status = 'pending';

-- RLS Policy
ALTER TABLE srv_invitations ENABLE ROW LEVEL SECURITY;

CREATE POLICY invitations_inviter_access ON srv_invitations
    FOR ALL
    USING (inviter_user_id IN (
        SELECT user_id FROM srv_users
        WHERE firebase_uid = current_setting('app.firebase_uid', true)
    ));

2.5.1. Columnas¶

Columna	Tipo	Nullable	Descripcion
invitation_id	UUID	No	Identificador unico
inviter_user_id	UUID	No	FK al usuario que invita
invitee_email_hash	CHAR(64)	Si	Hash del email del invitado
invitee_phone_hash	CHAR(64)	Si	Hash del telefono
token_hash	CHAR(64)	No	Hash del token de invitacion
status	VARCHAR(20)	No	Estado de la invitacion
permissions_enc	BYTEA	Si	Permisos ofrecidos (cifrados)
created_at	TIMESTAMPTZ	No	Fecha de creacion
expires_at	TIMESTAMPTZ	No	Fecha de expiracion
responded_at	TIMESTAMPTZ	Si	Fecha de respuesta
accepted_user_id	UUID	Si	FK al usuario que acepto

3. Entidades del Cliente¶

3.1. cli_auth_state (LOCAL_ONLY)¶

Estado de autenticacion local.

// iOS - Swift/Realm
class AuthState: Object {
    @Persisted(primaryKey: true) var id: String = "auth_state"

    // User Info (decrypted locally)
    @Persisted var userId: String?
    @Persisted var firebaseUid: String?
    @Persisted var email: String?
    @Persisted var displayName: String?
    @Persisted var role: String?          // PI, CS, CR
    @Persisted var tier: String?          // free, pro, perfect

    // Session
    @Persisted var isAuthenticated: Bool = false
    @Persisted var sessionExpiresAt: Date?
    @Persisted var mfaVerified: Bool = false

    // Local Security
    @Persisted var pinHash: String?       // Argon2 hash of PIN
    @Persisted var biometricEnabled: Bool = false
    @Persisted var failedAttempts: Int = 0
    @Persisted var lockedUntil: Date?

    // Sync State
    @Persisted var lastSyncAt: Date?
    @Persisted var syncVersion: Int64 = 0
}

// Android - Kotlin/Room
@Entity(tableName = "cli_auth_state")
data class AuthState(
    @PrimaryKey
    val id: String = "auth_state",

    // User Info
    val userId: String?,
    val firebaseUid: String?,
    val email: String?,
    val displayName: String?,
    val role: String?,
    val tier: String?,

    // Session
    val isAuthenticated: Boolean = false,
    val sessionExpiresAt: Long?, // Unix timestamp
    val mfaVerified: Boolean = false,

    // Local Security
    val pinHash: String?,
    val biometricEnabled: Boolean = false,
    val failedAttempts: Int = 0,
    val lockedUntil: Long?,

    // Sync State
    val lastSyncAt: Long?,
    val syncVersion: Long = 0
)

3.1.1. Clasificacion: LOCAL_ONLY¶

Esta entidad nunca se sincroniza. Contiene:

Estado de sesion local
Hash del PIN (Argon2)
Configuracion biometrica
Contador de intentos fallidos

3.2. cli_keychain_items (LOCAL_ONLY)¶

Referencia a items almacenados en Keychain/Keystore.

Key	Descripcion	Proteccion
`medtime.master_key`	Master Key derivada de credenciales	Secure Enclave / StrongBox
`medtime.firebase_token`	Token de Firebase (Pro/Perfect)	Biometric / PIN
`medtime.encryption_salt`	Salt para derivacion de claves	Device-bound
`medtime.recovery_key_enc`	Recovery key cifrada (Perfect)	Biometric

4. Funciones de Base de Datos¶

4.1. Funcion: Crear Usuario desde Firebase¶

CREATE OR REPLACE FUNCTION create_user_from_firebase(
    p_firebase_uid VARCHAR(128),
    p_email VARCHAR(255),
    p_phone VARCHAR(20),
    p_role VARCHAR(20),
    p_tier VARCHAR(20)
) RETURNS UUID AS $$
DECLARE
    v_user_id UUID;
    v_email_hash CHAR(64);
    v_phone_hash CHAR(64);
BEGIN
    -- Generate blind indexes
    IF p_email IS NOT NULL THEN
        v_email_hash := encode(sha256(lower(p_email)::bytea), 'hex');
    END IF;

    IF p_phone IS NOT NULL THEN
        v_phone_hash := encode(sha256(p_phone::bytea), 'hex');
    END IF;

    -- Enforce CS always free
    IF p_role = 'CS' AND p_tier != 'free' THEN
        RAISE EXCEPTION 'Cuidador Solidario must always be free tier';
    END IF;

    -- Insert user
    INSERT INTO srv_users (firebase_uid, email_hash, phone_hash, role, tier)
    VALUES (p_firebase_uid, v_email_hash, v_phone_hash, p_role, COALESCE(p_tier, 'free'))
    RETURNING user_id INTO v_user_id;

    -- Audit
    INSERT INTO srv_audit_trail (user_id, entity_type, entity_id_hash, action, timestamp)
    VALUES (v_user_id, 'user', encode(sha256(v_user_id::text::bytea), 'hex'), 'CREATE', NOW());

    RETURN v_user_id;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

4.2. Funcion: Verificar Limite de Dispositivos¶

CREATE OR REPLACE FUNCTION check_device_limit(p_user_id UUID)
RETURNS BOOLEAN AS $$
DECLARE
    v_tier VARCHAR(20);
    v_device_count INT;
    v_max_devices INT;
BEGIN
    -- Get user tier
    SELECT tier INTO v_tier FROM srv_users WHERE user_id = p_user_id;

    -- Get current device count
    SELECT COUNT(*) INTO v_device_count
    FROM srv_devices
    WHERE user_id = p_user_id AND deactivated_at IS NULL;

    -- Set limit based on tier
    v_max_devices := CASE v_tier
        WHEN 'free' THEN 1
        WHEN 'pro' THEN 3
        WHEN 'perfect' THEN 5
        ELSE 1
    END;

    RETURN v_device_count < v_max_devices;
END;
$$ LANGUAGE plpgsql;

4.3. Funcion: Revocar Sesiones Antiguas¶

CREATE OR REPLACE FUNCTION revoke_oldest_sessions(
    p_user_id UUID,
    p_keep_count INT DEFAULT 1
) RETURNS INT AS $$
DECLARE
    v_revoked INT;
BEGIN
    WITH sessions_to_revoke AS (
        SELECT session_id
        FROM srv_sessions
        WHERE user_id = p_user_id
          AND revoked_at IS NULL
        ORDER BY created_at DESC
        OFFSET p_keep_count
    )
    UPDATE srv_sessions
    SET revoked_at = NOW(),
        revoked_reason = 'device_limit_exceeded'
    WHERE session_id IN (SELECT session_id FROM sessions_to_revoke);

    GET DIAGNOSTICS v_revoked = ROW_COUNT;
    RETURN v_revoked;
END;
$$ LANGUAGE plpgsql;

5. Migraciones¶

5.1. Migration 001: Create Auth Schema¶

-- Migration: 001_create_auth_schema
-- Date: 2025-12-07
-- Author: DatabaseDrone

BEGIN;

-- Create users table
CREATE TABLE IF NOT EXISTS srv_users (
    user_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    firebase_uid VARCHAR(128) NOT NULL UNIQUE,
    email_hash CHAR(64),
    phone_hash CHAR(64),
    role VARCHAR(20) NOT NULL CHECK (role IN ('PI', 'PD', 'CS', 'CR')),
    tier VARCHAR(20) NOT NULL DEFAULT 'free' CHECK (tier IN ('free', 'pro', 'perfect')),
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    deleted_at TIMESTAMPTZ,
    CONSTRAINT chk_cs_always_free CHECK (role != 'CS' OR tier = 'free'),
    CONSTRAINT chk_pd_no_direct_account CHECK (role != 'PD')
);

-- Create sessions table
CREATE TABLE IF NOT EXISTS srv_sessions (
    session_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL REFERENCES srv_users(user_id) ON DELETE CASCADE,
    device_id UUID,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    expires_at TIMESTAMPTZ NOT NULL,
    last_activity TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    revoked_at TIMESTAMPTZ,
    revoked_reason VARCHAR(100),
    ip_hash CHAR(64),
    user_agent_hash CHAR(64),
    mfa_verified BOOLEAN NOT NULL DEFAULT FALSE,
    CONSTRAINT chk_session_dates CHECK (expires_at > created_at)
);

-- Create devices table
CREATE TABLE IF NOT EXISTS srv_devices (
    device_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL REFERENCES srv_users(user_id) ON DELETE CASCADE,
    device_name_enc BYTEA,
    platform VARCHAR(20) NOT NULL CHECK (platform IN ('ios', 'android', 'web')),
    os_version VARCHAR(50),
    app_version VARCHAR(20),
    push_token_enc BYTEA,
    push_enabled BOOLEAN NOT NULL DEFAULT TRUE,
    last_sync_at TIMESTAMPTZ,
    sync_version BIGINT NOT NULL DEFAULT 0,
    registered_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    last_seen_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    deactivated_at TIMESTAMPTZ
);

-- Add foreign key for sessions -> devices (after devices created)
ALTER TABLE srv_sessions
    ADD CONSTRAINT fk_sessions_device
    FOREIGN KEY (device_id) REFERENCES srv_devices(device_id) ON DELETE SET NULL;

-- Create user_relations table
CREATE TABLE IF NOT EXISTS srv_user_relations (
    relation_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    owner_user_id UUID NOT NULL REFERENCES srv_users(user_id) ON DELETE CASCADE,
    related_user_id UUID NOT NULL REFERENCES srv_users(user_id) ON DELETE CASCADE,
    relation_type VARCHAR(20) NOT NULL CHECK (relation_type IN ('caregiver_of', 'patient_of')),
    permissions_enc BYTEA,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    created_by UUID REFERENCES srv_users(user_id),
    revoked_at TIMESTAMPTZ,
    revoked_by UUID REFERENCES srv_users(user_id),
    CONSTRAINT chk_no_self_relation CHECK (owner_user_id != related_user_id),
    CONSTRAINT uq_relation UNIQUE (owner_user_id, related_user_id, relation_type)
);

-- Create invitations table
CREATE TABLE IF NOT EXISTS srv_invitations (
    invitation_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    inviter_user_id UUID NOT NULL REFERENCES srv_users(user_id) ON DELETE CASCADE,
    invitee_email_hash CHAR(64),
    invitee_phone_hash CHAR(64),
    token_hash CHAR(64) NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'pending'
        CHECK (status IN ('pending', 'accepted', 'declined', 'expired', 'cancelled')),
    permissions_enc BYTEA,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    expires_at TIMESTAMPTZ NOT NULL,
    responded_at TIMESTAMPTZ,
    accepted_user_id UUID REFERENCES srv_users(user_id),
    CONSTRAINT chk_has_contact CHECK (invitee_email_hash IS NOT NULL OR invitee_phone_hash IS NOT NULL),
    CONSTRAINT chk_expiry CHECK (expires_at > created_at)
);

-- Create all indexes
CREATE UNIQUE INDEX idx_users_firebase_uid ON srv_users(firebase_uid);
CREATE INDEX idx_users_email_hash ON srv_users(email_hash) WHERE email_hash IS NOT NULL;
CREATE INDEX idx_users_phone_hash ON srv_users(phone_hash) WHERE phone_hash IS NOT NULL;
CREATE INDEX idx_users_role_tier ON srv_users(role, tier);

CREATE INDEX idx_sessions_user ON srv_sessions(user_id);
CREATE INDEX idx_sessions_device ON srv_sessions(device_id);
CREATE INDEX idx_sessions_expires ON srv_sessions(expires_at) WHERE revoked_at IS NULL;
CREATE INDEX idx_sessions_last_activity ON srv_sessions(last_activity) WHERE revoked_at IS NULL;

CREATE INDEX idx_devices_user ON srv_devices(user_id);
CREATE INDEX idx_devices_active ON srv_devices(user_id, deactivated_at) WHERE deactivated_at IS NULL;

CREATE INDEX idx_relations_owner ON srv_user_relations(owner_user_id);
CREATE INDEX idx_relations_related ON srv_user_relations(related_user_id);

CREATE INDEX idx_invitations_inviter ON srv_invitations(inviter_user_id);
CREATE INDEX idx_invitations_token ON srv_invitations(token_hash);

COMMIT;

6. Referencias Cruzadas¶

Documento	Relacion
DB-ERD-001	Diagrama ER completo
MTS-AUTH-001	Especificacion funcional
05-seguridad-servidor.md	RLS y auditoria
04-seguridad-cliente.md	Keychain/Keystore
INV-015	Firebase Auth docs
INV-016	Firebase Admin SDK

Documento generado por DatabaseDrone - SpecQueen Technical Division "La autenticacion segura es la primera linea de defensa."