Database Documentation

Supabase Project

Atributo	Valor
Project ref	osytjlchrelbuphsvfax
Regiao	sa-east-1 (Sao Paulo)
Plan	Pro
PostgreSQL	15.x
URL base	https://osytjlchrelbuphsvfax.supabase.co

---

Convenções de Schema

Identificadores

• Toda tabela usa id uuid PRIMARY KEY DEFAULT gen_random_uuid() como chave primaria.
• FKs seguem o padrão tabela_referênciada_id (ex: account_id, contact_id).
• Colunas booleanas usam prefixo is_ (ex: is_active, is_dynamic).

Timestamps

• created_at timestamptz NOT NULL DEFAULT now() — presente em todas as tabelas.
• updated_at timestamptz NOT NULL DEFAULT now() — tabelas editaveis via trigger.
• deleted_at timestamptz DEFAULT NULL — soft-delete em 4 tabelas críticas.

Multi-tenancy via account_id

• Toda tabela de dados de usuário tem account_id uuid NOT NULL REFERENCES accounts(id).
• Tabelas filhas (sem account_id direto) usam RLS via EXISTS subquery para o parent.
• Excecoes: contact_tags, webhook_logs, automation_steps, campaign_sends, automation_execution_steps, segment_members.

Tipos de dados

• Moeda/valores: DECIMAL(12,2) ou DECIMAL(15,2) — nunca float.
• Texto curto com opções fixas: text com CHECK (col IN (...)) — sem ENUM (exceto shipment_tracking_status).
• Payloads flexiveis: jsonb com default '{}' ou '[]'.
• Arrays fixos: text[] (ex: events, scopes).
• Telefones: formato E.164 (+[1-9][0-9]{1,14}) com constraint CHECK.

---

Extensoes Habilitadas

Extensao	Finalidade
pg_trgm	Trigram indexes para busca fuzzy/ILIKE em contacts.name
pg_cron	Cron jobs diretamente no banco
pg_net	HTTP requests assíncronos (não usado ativamente)
pgmq	Message queue (habilitado, não em uso ativo)

---

RLS (Row Level Security)

RLS é habilitado em todas as tabelas do schema public. Existem dois padroes:

Padrão 1 — account_id direto

Para tabelas que tem account_id na própria tabela:

USING (account_id IN (SELECT get_user_account_ids()))

Padrão 2 — EXISTS subquery (tabelas filhas)

Para tabelas sem account_id, que dependem de uma tabela parent:

USING (EXISTS (
  SELECT 1 FROM parent_table
  WHERE parent_table.id = child_table.parent_id
  AND parent_table.account_id IN (SELECT get_user_account_ids())
))

Funcoes auxiliares de RLS

-- Retorna todos os account_ids do usuário autenticado (multi-conta)
get_user_account_ids() RETURNS SETOF uuid

-- Retorna um account_id único (legado, single-account)
get_user_account_id() RETURNS uuid

---

BaseRepository Pattern

O projeto usa um BaseRepository<T> genérico em TypeScript localizado em: src/lib/repositories/base.ts

Métodos disponíveis:

Método	Descrição
findById(id, accountId?)	Busca por PK, opcionalmente scoped por account
findAll(accountId, págination, filters?)	Listagem páginada (offset-based) com filtros
findAllCursor(accountId, opts)	Listagem cursor-based (mais eficiente para grandes volumes)
create(accountId, input)	INSERT com account_id automático
update(id, accountId, input)	UPDATE scoped por account
delete(id, accountId)	DELETE fisico scoped por account
softDelete(id, accountId)	SET deleted_at = now()
count(accountId, filters?)	Contagem com filtros

Páginação:

• Offset-based (findAll): padrão para listagens com total de páginas. Ineficiente acima de ~10k rows por conta.
• Cursor-based (findAllCursor): para feeds e timelines. Usa gt/lt no campo orderBy.

---

Funcoes RPC Disponíveis

Analytics de Mensagens

• message_stats(p_account_id) — totais (sent, received, delivered, read)
• message_stats_by_day(p_account_id, p_days_back) — métricas diarias com dias vazios preenchidos
• delivery_funnel(p_account_id) — funil sent -> delivered -> read
• campaign_stats_agg(p_campaign_id) — stats agregadas de campanha

Revenue e Ecommerce

• revenue_overview(account, start, end) — receita total e atribuida
• revenue_by_day(account, start, end) — receita diaria
• revenue_by_automation(account, start, end) — receita por automação
• revenue_by_campaign(account, start, end) — receita por campanha
• revenue_with_costs(account, start, end) — receita com COGS, fees, ad spend, ROAS
• revenue_by_link(account, start, end, limit) — receita por link rastreado
• conversion_funnel_basic(account, start, end) — funil clicks -> orders

Analytics de Eventos

• contact_event_timeline(contact_id, limit, cursor, metric_names) — timeline páginada por cursor
• metric_counts(account_id, start, end) — contagem por métrica para dashboard

RFM

• compute_rfm_scores(p_account_id) — calcula scores RFM de todos os contatos
• rfm_distribution(p_account_id) — distribuição de segmentos RFM

Links

• link_stats_overview(account, from, to) — overview de links criados e cliques
• top_tracked_links(account, from, to, limit) — ranking de links por cliques

Rastreamento

• tracking_status_counts(account_id) — contagem de rastreamentos por status

Automações

• toggle_automation_active(automation_id) — alterna is_active
• replace_automation_steps(automation_id, steps) — substitui todos os steps atomicamente
• get_automation_execution_stats(automation_id) — stats de execução

Segmentos

• evaluate_contact_filters(contact_id, conditions) — avalia se contato passa nos filtros
• recalculate_segment(segment_id) — recalcula membros de um segmento

Pastas

• get_folder_tree(account_id, entity_type?) — arvore recursiva de pastas

Admin

• seed_system_metrics(account_id) — cria métricas sistema para uma nova conta

---

pg_cron Jobs Ativos

Job	Schedule	Comando
recalculate_all_segments	*/5 * * * * (a cada 5 min)	Recalcula todos os segmentos dinâmicos
execute-scheduled-campaigns	* * * * * (a cada 1 min)	Transita campanhas de scheduled para sending

---

Storage Buckets

Bucket	Acesso	Limite	Tipos permitidos
template-media	Upload autenticado, leitura pública	100MB	image/jpeg, image/png, image/webp, video/mp4, application/pdf

---

Triggers de Banco

Trigger	Tabela	Evento	Função
on_auth_user_created	auth.users	AFTER INSERT	handle_new_user() — cria account + account_user
trg_update_contact_metrics_summary	events	AFTER INSERT	update_contact_metrics_summary() — atualiza contadores
accounts_updated_at	accounts	BEFORE UPDATE	update_updated_at_column()
message_templates_updated_at	message_templates	BEFORE UPDATE	update_updated_at_column()
brand_context_updated_at	brand_context	BEFORE UPDATE	update_updated_at_column()
quick_replies_updated_at	quick_replies	BEFORE UPDATE	update_updated_at_column()
billing_subscriptions_updated_at	billing_subscriptions	BEFORE UPDATE	update_updated_at_column()
set_updated_at_segments	segments	BEFORE UPDATE	update_updated_at_column()
set_updated_at_ecommerce_integrations	ecommerce_integrations	BEFORE UPDATE	update_updated_at_column()
set_updated_at_ecommerce_orders	ecommerce_orders	BEFORE UPDATE	update_updated_at_column()
set_updated_at_ecommerce_carts	ecommerce_abandoned_carts	BEFORE UPDATE	update_updated_at_column()
set_updated_at_contact_rfm_scores	contact_rfm_scores	BEFORE UPDATE	update_updated_at_column()
trg_shipment_tracking_updated_at	shipment_trackings	BEFORE UPDATE	update_shipment_tracking_updated_at()
trg_tracking_settings_updated_at	tracking_settings	BEFORE UPDATE	update_shipment_tracking_updated_at()
set_tracked_links_updated_at	tracked_links	BEFORE UPDATE	update_updated_at_column()
set_updated_at_ad_spend	ad_spend	BEFORE UPDATE	update_updated_at_column()

---

Autovacuum Ajustado

Tabelas com alto volume de INSERT/UPDATE tem autovacuum acelerado:

• events: scale_factor=0.05, analyze_scale_factor=0.02
• contact_metrics_summary: mesmas configurações

---

Arquivos de Schema Detalhado

• schema — Schema completo de todas as tabelas, agrupadas por domínio