imagy-notifications — Modelo de Datos

Base de Datos

• Nombre: imagy_notifications
• Usuario: imagy_notifications_app (NOBYPASSRLS)
• RLS: Habilitado en todas las tablas con tenant_id

Diagrama ER

Tablas

notification_templates

Plantillas de notificacion configurables por tenant, canal e idioma. Definen el contenido que se envia al destinatario.

Columna	Tipo	Descripcion
id	UUID PK	Identificador unico
tenant_id	UUID	Tenant propietario (RLS). NULL para plantillas globales del sistema
code	VARCHAR(100)	Codigo unico de la plantilla dentro del tenant (ej: loan_approved, otp_verification)
channel	VARCHAR(20)	Canal de envio: sms, email, whatsapp, push, webhook
locale	VARCHAR(10)	Codigo de idioma (ej: es, en, pt)
subject	VARCHAR(500)	Asunto (aplica para email y push). Soporta interpolacion de variables
body	TEXT	Cuerpo del mensaje con variables
variables	JSONB	Array con los nombres de variables esperadas. Usado para validacion
is_active	BOOLEAN	Si la plantilla esta activa
row_version	INT	Concurrencia optimista
created_at	TIMESTAMPTZ	Fecha de creacion
updated_at	TIMESTAMPTZ	Ultima modificacion

Constraint unico: (tenant_id, code, channel, locale) — una sola plantilla por combinacion de tenant, codigo, canal e idioma.

Ejemplo de body con variables:

Hola {{borrower_name}}, tu credito #{{loan_id}} por {{amount}} {{currency}} ha sido aprobado.
Puedes consultar los detalles en {{portal_url}}.

Ejemplo de body para email (HTML):

<h1>Credito Aprobado</h1>
<p>Hola {{borrower_name}},</p>
<p>Tu solicitud de credito por <strong>{{amount}} {{currency}}</strong> ha sido aprobada.</p>
<p>Referencia: {{loan_id}}</p>
<a href="{{portal_url}}">Ver detalles</a>

notification_channels

Configuracion de canales de notificacion por tenant. Define que proveedores usa cada tenant y con que credenciales.

Columna	Tipo	Descripcion
id	UUID PK	Identificador unico
tenant_id	UUID	Tenant propietario (RLS)
channel	VARCHAR(20)	Canal: sms, email, whatsapp, push, webhook
provider	VARCHAR(50)	Proveedor: twilio, sns, ses, meta_whatsapp, fcm, apns, webhook
credentials	JSONB	Credenciales del proveedor (cifradas con KMS)
config	JSONB	Configuracion adicional del canal
is_active	BOOLEAN	Si el canal esta habilitado para este tenant
row_version	INT	Concurrencia optimista
created_at	TIMESTAMPTZ	Fecha de creacion
updated_at	TIMESTAMPTZ	Ultima modificacion

Constraint unico: (tenant_id, channel) — un solo proveedor por canal por tenant.

Ejemplo de credentials (SMS - Twilio):

{
  "account_sid": "AC...",
  "auth_token": "encrypted:...",
  "from_number": "+15551234567"
}

Ejemplo de credentials (Email - SES):

{
  "region": "us-east-1",
  "from_address": "noreply@fintech-abc.reimaginetech.io",
  "configuration_set": "fintech-abc-notifications"
}

Ejemplo de credentials (WhatsApp - Meta):

{
  "phone_number_id": "123456789",
  "access_token": "encrypted:...",
  "business_account_id": "987654321"
}

Ejemplo de config (Webhook):

{
  "endpoint_url": "https://api.cliente.com/webhooks/notifications",
  "secret": "encrypted:...",
  "timeout_ms": 5000,
  "retry_count": 3,
  "headers": {
    "X-Custom-Header": "value"
  }
}

notification_logs

Registro de cada notificacion enviada. Permite auditoria, reenvio y analisis de entregabilidad.

Columna	Tipo	Descripcion
id	UUID PK	Identificador unico
tenant_id	UUID	Tenant propietario (RLS)
template_code	VARCHAR(100)	Codigo de la plantilla usada (nullable para OTP)
channel	VARCHAR(20)	Canal usado: sms, email, whatsapp, push, webhook
recipient	VARCHAR(500)	Destinatario (telefono, email, device token, URL)
status	VARCHAR(20)	queued, sent, delivered, failed, bounced
provider	VARCHAR(50)	Proveedor que proceso el envio
provider_message_id	VARCHAR(255)	ID del mensaje en el proveedor (para tracking)
provider_response	JSONB	Respuesta completa del proveedor
variables_used	JSONB	Variables interpoladas en el envio (para debug/reenvio)
metadata	JSONB	Metadata adicional (origin_service, origin_reference, etc.)
sent_at	TIMESTAMPTZ	Timestamp de envio al proveedor
delivered_at	TIMESTAMPTZ	Timestamp de confirmacion de entrega (nullable)
failed_at	TIMESTAMPTZ	Timestamp de fallo (nullable)

Estados del ciclo de vida:

Estado	Descripcion
queued	Notificacion en cola, pendiente de envio
sent	Enviada al proveedor exitosamente
delivered	Confirmacion de entrega al destinatario (cuando el proveedor lo reporta)
failed	Fallo en el envio (proveedor rechazo o error de red)
bounced	Rebotada (email invalido, numero inexistente)

otp_codes

Codigos de un solo uso (One-Time Password) generados para verificacion.

Columna	Tipo	Descripcion
id	UUID PK	Identificador unico
tenant_id	UUID	Tenant propietario (RLS)
code	VARCHAR(10)	Codigo OTP generado (6 digitos por defecto)
channel	VARCHAR(20)	Canal de envio: sms, email, whatsapp
destination	VARCHAR(255)	Destino (telefono o email)
purpose	VARCHAR(50)	Proposito: contact_verification, signature_otp, login_otp, transaction_otp
origin_service	VARCHAR(100)	Servicio que solicito el OTP: imagsign, imaglend, imagflow, imagid
origin_reference	VARCHAR(255)	Referencia en el servicio origen (ej: signature_request_id)
attempts	INT	Intentos de verificacion realizados (default 0)
max_attempts	INT	Maximo de intentos permitidos (default 3)
expires_at	TIMESTAMPTZ	Fecha de expiracion del codigo
verified_at	TIMESTAMPTZ	Fecha de verificacion exitosa (nullable)
created_at	TIMESTAMPTZ	Fecha de creacion

Reglas de negocio:

• El codigo expira despues de 5 minutos por defecto (configurable por tenant).
• Maximo 3 intentos de verificacion. Al exceder, el codigo se invalida.
• Un mismo destino no puede tener mas de 5 OTPs activos en una ventana de 15 minutos (rate limiting).
• El codigo se almacena hasheado (bcrypt) en la base de datos. La comparacion se hace via hash.

Tablas de Soporte

idempotency_keys

CREATE TABLE idempotency_keys (
    key VARCHAR(255) NOT NULL,
    tenant_id UUID NOT NULL,
    response_status INT NOT NULL,
    response_body JSONB NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    expires_at TIMESTAMPTZ NOT NULL DEFAULT NOW() + INTERVAL '24 hours',
    PRIMARY KEY (key, tenant_id)
);

processed_events

CREATE TABLE processed_events (
    event_id UUID PRIMARY KEY,
    event_type VARCHAR(100) NOT NULL,
    processed_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    source VARCHAR(100) NOT NULL
);

RLS Policies

-- Todas las tablas principales tienen RLS por tenant_id
ALTER TABLE notification_templates ENABLE ROW LEVEL SECURITY;
ALTER TABLE notification_channels ENABLE ROW LEVEL SECURITY;
ALTER TABLE notification_logs ENABLE ROW LEVEL SECURITY;
ALTER TABLE otp_codes ENABLE ROW LEVEL SECURITY;

-- Politica de aislamiento por tenant
CREATE POLICY tenant_isolation ON notification_templates
    FOR ALL USING (
        tenant_id = get_current_tenant_id()
        OR tenant_id IS NULL  -- plantillas globales visibles para todos
    );

CREATE POLICY tenant_isolation ON notification_channels
    FOR ALL USING (tenant_id = get_current_tenant_id());

CREATE POLICY tenant_isolation ON notification_logs
    FOR ALL USING (tenant_id = get_current_tenant_id());

CREATE POLICY tenant_isolation ON otp_codes
    FOR ALL USING (tenant_id = get_current_tenant_id());

-- Control plane (platform admin sin tenant context)
CREATE POLICY control_plane ON notification_templates
    FOR ALL USING (
        current_setting('app.current_tenant_id', true) IS NULL
        OR current_setting('app.current_tenant_id', true) = ''
    );

CREATE POLICY control_plane ON notification_channels
    FOR ALL USING (
        current_setting('app.current_tenant_id', true) IS NULL
        OR current_setting('app.current_tenant_id', true) = ''
    );

Indices

-- Plantillas: busqueda por tenant + code + channel
CREATE UNIQUE INDEX idx_templates_unique
    ON notification_templates(tenant_id, code, channel, locale);
CREATE INDEX idx_templates_tenant_active
    ON notification_templates(tenant_id, is_active)
    WHERE is_active = true;

-- Canales: busqueda por tenant + channel
CREATE UNIQUE INDEX idx_channels_tenant_channel
    ON notification_channels(tenant_id, channel);
CREATE INDEX idx_channels_tenant_active
    ON notification_channels(tenant_id, is_active)
    WHERE is_active = true;

-- Logs: busqueda por tenant + fecha + estado
CREATE INDEX idx_logs_tenant_sent
    ON notification_logs(tenant_id, sent_at DESC);
CREATE INDEX idx_logs_tenant_status
    ON notification_logs(tenant_id, status);
CREATE INDEX idx_logs_tenant_recipient
    ON notification_logs(tenant_id, recipient);
CREATE INDEX idx_logs_provider_message
    ON notification_logs(provider_message_id);
CREATE INDEX idx_logs_template
    ON notification_logs(tenant_id, template_code);

-- OTP: busqueda por destino + proposito
CREATE INDEX idx_otp_destination_active
    ON otp_codes(tenant_id, destination, purpose)
    WHERE verified_at IS NULL AND expires_at > NOW();
CREATE INDEX idx_otp_expires
    ON otp_codes(expires_at)
    WHERE verified_at IS NULL;
CREATE INDEX idx_otp_origin
    ON otp_codes(origin_service, origin_reference);

-- Limpieza
CREATE INDEX idx_idempotency_expires ON idempotency_keys(expires_at);
CREATE INDEX idx_processed_events_date ON processed_events(processed_at);

Particionamiento

La tabla notification_logs se particiona por mes para mantener el rendimiento a medida que crece el volumen de envios:

CREATE TABLE notification_logs (
    id UUID NOT NULL,
    tenant_id UUID NOT NULL,
    -- ... demas columnas
    sent_at TIMESTAMPTZ NOT NULL
) PARTITION BY RANGE (sent_at);

-- Particiones mensuales
CREATE TABLE notification_logs_2026_01 PARTITION OF notification_logs
    FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');
CREATE TABLE notification_logs_2026_02 PARTITION OF notification_logs
    FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');
-- ... se crean automaticamente via cron job

Retencion de Datos

Tabla	Retencion	Estrategia
notification_logs	90 dias en hot storage	Particiones antiguas se mueven a cold storage
otp_codes	30 dias	Eliminacion via cron job (codigos expirados/verificados)
notification_templates	Indefinida	Sin eliminacion automatica
notification_channels	Indefinida	Sin eliminacion automatica
idempotency_keys	24 horas	Eliminacion via TTL
processed_events	7 dias	Eliminacion via cron job