Modelo de dados

As entidades e como elas se relacionam.

Data Model - Asender Ecosystem

Modelos de dados de todo o ecossistema. Separado por componente.

1. Relay PHP (Multi-tenant)

Tabela: tenants

id               PK
tenant_id        string unique       tnt_xxx
name             string
api_key_hash     string unique       sha256
status           enum                active|suspended|deleted
smtp_config      json                {host, port, encryption, user, pass}
default_from     string
default_from_name string
hourly_limit     int nullable
daily_limit      int nullable
batch_size       int nullable
send_delay_ms    int nullable
metadata         json
created_at       timestamp
updated_at       timestamp
suspended_at     timestamp nullable
deleted_at       timestamp nullable

Tabela: templates

id               PK
template_id      string unique       tpl_xxx
tenant_id        string fk           tnt_xxx
name             string
description      string nullable
subject          string
body_text        text
body_html        text
variables        json                [{name, required, default}]
metadata         json
created_at       timestamp
updated_at       timestamp
deleted_at       timestamp nullable

Tabela: emails (alteracao)

+ tenant_id     string                    tnt_xxx
+ template_id   string nullable           tpl_xxx (se veio de template)
... (todas as outras colunas ja existentes)

Tabela: logs (alteracao)

+ tenant_id     string nullable
... (colunas existentes)

Tabela: settings (sem mudanca)

Tabela: rate_limits (sem mudanca)

2. Asender Core (Laravel + PostgreSQL)

Users & Tenants

users

id              bigint PK
email           string unique
name            string
password        string hashed
email_verified_at timestamp nullable
two_factor_secret text nullable encrypted
two_factor_recovery_codes text nullable encrypted
last_login_at   timestamp
created_at, updated_at

tenants

id              bigint PK
public_id       string unique            acc_xxx
name            string
slug            string unique            (usado em subdomain)
plan_id         bigint fk
status          enum                      active|suspended|trial
trial_ends_at   timestamp nullable
metadata        jsonb
created_at, updated_at

tenant_user (pivo)

id              bigint PK
tenant_id       bigint fk
user_id         bigint fk
role            enum                      owner|admin|developer|viewer
invited_by      bigint fk nullable
accepted_at     timestamp nullable
created_at
unique (tenant_id, user_id)

api_keys

id              bigint PK
tenant_id       bigint fk
user_id         bigint fk                (quem criou)
name            string
key_hash        string unique
prefix          string                    pri 8 chars visiveis
scopes          json                      ["emails:send", "emails:read", ...]
last_used_at    timestamp
expires_at      timestamp nullable
revoked_at      timestamp nullable
created_at

invitations

id              bigint PK
tenant_id       bigint fk
email           string
role            enum
token           string unique
expires_at      timestamp
accepted_at     timestamp nullable
created_at

Dominios & Reputacao

domains

id              bigint PK
tenant_id       bigint fk
domain          string                    example.com
verified        boolean
verification_token string
dkim_selector   string default='asender'
dkim_private_key text encrypted
dkim_public_key text
dmarc_policy    enum                      none|quarantine|reject
spf_record_detected boolean
dkim_record_detected boolean
dmarc_record_detected boolean
last_checked_at timestamp
created_at, updated_at

suppression_list

id              bigint PK
tenant_id       bigint fk
address         string                    email ou phone
type            enum                      email|phone
reason          enum                      bounce|complaint|unsubscribe|manual
source_message_id string nullable
created_at
unique (tenant_id, address, type)

Email / SMTP

email_messages

id              bigint PK
tenant_id       bigint fk
message_id      string unique             msg_xxx
domain_id       bigint fk nullable
template_id     bigint fk nullable
campaign_id     bigint fk nullable
relay_id        bigint fk nullable        (se enviou via relay)
provider        string                    ses|sendgrid|relay|mailgun|...
from_email      string
from_name       string
to_addresses    jsonb
cc_addresses    jsonb
bcc_addresses   jsonb
reply_to        jsonb
subject         string
body_text       text
body_html       text
status          enum                      queued|processing|sent|failed|bounced|complained
attempts        int
last_error      text nullable
tags            jsonb
metadata        jsonb
scheduled_at    timestamp
created_at
sent_at         timestamp nullable
indexes: (tenant_id, status), (tenant_id, created_at), (message_id)

email_events

Em PostgreSQL (canonico) + ClickHouse (analytics).

id              bigint PK
tenant_id       bigint fk
message_id      string fk
event_type      enum                      sent|delivered|bounced|complained|opened|clicked|unsubscribed
event_data      jsonb                     (url, ip, user agent, etc)
created_at      timestamp

email_templates

id              bigint PK
tenant_id       bigint fk
public_id       string unique             tpl_xxx
name            string
subject         string
body_mjml       text nullable              (fonte MJML)
body_html       text                       (compilado ou direto)
body_text       text nullable
variables       jsonb
tags            jsonb
version         int
created_at, updated_at

relays

id              bigint PK
tenant_id       bigint fk
public_id       string unique             rel_xxx
name            string
url             string                     https://relay.xxx.com
master_key_encrypted text                  (encrypted at rest)
status          enum                       active|error|disabled
last_health_check_at timestamp
health_data     jsonb
tenants_count   int                        (N tenants internos no relay)
created_at, updated_at

SMS

sms_messages

id              bigint PK
tenant_id       bigint fk
message_id      string unique             sms_xxx
campaign_id     bigint fk nullable
gateway_id      bigint fk
from_number     string
to_number       string
body            text
segments        int                        (quantos SMS foram enviados)
status          enum                       queued|sending|sent|delivered|failed|undelivered
last_error      text nullable
metadata        jsonb
scheduled_at    timestamp
created_at, updated_at
sent_at         timestamp nullable
delivered_at    timestamp nullable

sms_gateways

id              bigint PK
tenant_id       bigint fk
provider        enum                       twilio|sns|zenvia|plivo|custom
name            string
config          jsonb encrypted            (credenciais)
default         boolean
priority        int
created_at, updated_at

Push

push_apps

id              bigint PK
tenant_id       bigint fk
public_id       string unique             psh_xxx
name            string
platform        enum                       ios|android|web
fcm_server_key  text encrypted nullable
apns_key_id     string nullable
apns_team_id    string nullable
apns_key_p8     text encrypted nullable
apns_bundle_id  string nullable
vapid_public    text nullable
vapid_private   text encrypted nullable
created_at, updated_at

push_devices

id              bigint PK
tenant_id       bigint fk
app_id          bigint fk
token           text
platform        enum
user_id_external string nullable            (id do usuario no sistema do customer)
topics          jsonb                       (array)
metadata        jsonb
last_seen_at    timestamp
created_at
indexes: (tenant_id, token) unique

push_messages

id              bigint PK
tenant_id       bigint fk
message_id      string unique             psh_msg_xxx
campaign_id     bigint fk nullable
app_id          bigint fk
target_type     enum                       device|topic|segment
target_value    string                     (token ou nome do topic ou segment_id)
title           string
body            text
data            jsonb
status          enum                       queued|sent|delivered|failed
sent_count      int
delivered_count int
failed_count    int
created_at, sent_at

Campaigns

campaigns

id              bigint PK
tenant_id       bigint fk
public_id       string unique             cam_xxx
name            string
channel         enum                       email|sms|push|multi
template_id     bigint fk nullable
list_id         bigint fk nullable
segment_id      bigint fk nullable
status          enum                       draft|scheduled|running|paused|completed|cancelled
scheduled_at    timestamp nullable
started_at      timestamp nullable
completed_at    timestamp nullable
total_recipients int
stats           jsonb                      (sent, delivered, opened, clicked, etc)
ab_test_config  jsonb nullable
created_by      bigint fk (user)
created_at, updated_at

campaign_recipients

id              bigint PK
campaign_id     bigint fk
contact_id      bigint fk
variant         string                     (A, B, C pra A/B)
status          enum                       pending|sent|failed|skipped
message_id      string nullable            (vinculo com email_messages/sms_messages/push_messages)
processed_at    timestamp nullable

Contacts

contacts

id              bigint PK
tenant_id       bigint fk
public_id       string unique             cnt_xxx
email           string nullable
phone           string nullable
first_name      string nullable
last_name       string nullable
attributes      jsonb                      (campos custom)
subscribed      boolean default true
tags            jsonb
source          string
last_activity_at timestamp
created_at, updated_at
unique (tenant_id, email) where email IS NOT NULL
unique (tenant_id, phone) where phone IS NOT NULL

contact_lists

id              bigint PK
tenant_id       bigint fk
public_id       string unique             lst_xxx
name            string
description     text nullable
contacts_count  int
created_at, updated_at

contact_list_members

id              bigint PK
list_id         bigint fk
contact_id      bigint fk
added_at        timestamp
unique (list_id, contact_id)

segments

id              bigint PK
tenant_id       bigint fk
public_id       string unique             seg_xxx
name            string
rules           jsonb                      (AST de regras: AND/OR de conditions)
cached_count    int nullable
cached_at       timestamp nullable
created_at, updated_at

Billing

plans

id              bigint PK
name            string
slug            string unique
price_cents     int
billing_cycle   enum                       monthly|yearly
features        jsonb
limits          jsonb                      {emails_per_month, sms, push, contacts, etc}
active          boolean

subscriptions

id              bigint PK
tenant_id       bigint fk
plan_id         bigint fk
status          enum                       trialing|active|past_due|canceled
trial_ends_at   timestamp nullable
current_period_start timestamp
current_period_end   timestamp
stripe_subscription_id string nullable
cancel_at       timestamp nullable
created_at, updated_at

usage_counters

id              bigint PK
tenant_id       bigint fk
period          string                     2026-04 (YYYY-MM)
emails_sent     int default 0
sms_sent        int default 0
push_sent       int default 0
unique (tenant_id, period)

Webhooks

webhook_endpoints

id              bigint PK
tenant_id       bigint fk
url             string
secret          string encrypted
events          jsonb                      (array de event types)
active          boolean
created_at, updated_at

webhook_deliveries

id              bigint PK
endpoint_id     bigint fk
event_type      string
payload         jsonb
response_status int nullable
response_body   text nullable
attempts        int
next_retry_at   timestamp nullable
delivered_at    timestamp nullable
created_at

Automations

automations

id              bigint PK
tenant_id       bigint fk
public_id       string unique             aut_xxx
name            string
trigger_type    string                     contact_created|event|scheduled|tag_added|...
trigger_config  jsonb
status          enum                       draft|active|paused
flow            jsonb                      (DAG de steps)
created_at, updated_at

automation_runs

id              bigint PK
automation_id   bigint fk
contact_id      bigint fk
status          enum                       running|completed|failed|skipped
current_step    string nullable
context         jsonb
started_at      timestamp
completed_at    timestamp nullable

3. ClickHouse (Analytics Events)

Schema append-only pra volumes altos.

CREATE TABLE events_email (
    tenant_id String,
    message_id String,
    event_type Enum('sent','delivered','bounced','complained','opened','clicked','unsubscribed'),
    timestamp DateTime64(3),
    ip Nullable(String),
    user_agent Nullable(String),
    url Nullable(String),
    country Nullable(String),
    device_type Nullable(String),
    email_client Nullable(String)
) ENGINE = MergeTree ORDER BY (tenant_id, timestamp);

-- Analogo para sms, push

Materialized views pra pre-aggregations de dashboards.

4. Encriptacao de Dados Sensiveis

Laravel Crypt (AES-256-CBC) em campos marcados encrypted:

• api_keys.key_hash (ja eh hash, nao precisa encrypt)
• domains.dkim_private_key
• relays.master_key_encrypted
• sms_gateways.config
• push_apps.fcm_server_key
• push_apps.apns_key_p8
• push_apps.vapid_private
• webhook_endpoints.secret
• users.two_factor_secret

Chave mestre em env (APP_KEY).

5. Indices Criticos

Alem das FKs, indices compostos para queries comuns:

• email_messages (tenant_id, status, created_at DESC) - dashboard recents
• email_messages (tenant_id, campaign_id) - campaign stats
• email_events (tenant_id, message_id) - lookup por mensagem
• email_events (tenant_id, event_type, created_at) - aggregates
• contacts (tenant_id, email) - unique + lookup
• usage_counters (tenant_id, period) - unique + lookup
• Partial indexes pra soft deletes (WHERE deleted_at IS NULL)

6. Convencoes

• IDs internos: bigint auto-increment
• IDs publicos: string prefix_hex (nunca expor IDs internos na API)
• Timestamps: timestamptz em PG, DATETIME em SQLite
• JSON: jsonb em PG (indexavel), TEXT em SQLite
• Enums: string check constraint em PG, VARCHAR em SQLite/MySQL
• Soft delete: deleted_at nullable + index parcial
• Audit: created_at, updated_at em toda tabela mutavel