dashboard.md

type	spec
title	Dashboard de Engajamento da Comunidade — He4rt Developers
module	activity
status	proposed
date	2026-06-20
author	danielhe4rt

Gerado por investigação multi-agente (10 nichos · 68 KPIs · verificação adversarial) contra o banco de produção em 2026-06-20. Leia a seção 0 (caveats de janela de dados) antes de confiar em qualquer número — 5 pipelines de ingestão estão quebrados/vazios.

Dashboard de Engajamento da Comunidade — He4rt Developers

Especificação Definitiva (gerada 2026-06-20)

Como ler este documento: os KPIs estão organizados por camada (North Star → Input → Health → Business → Content → Cross-platform). Cada KPI traz definição, fonte de dados, SQL exato (já verificado contra o banco), valor medido (corrigido onde o verdict apontou), visualização, alvo e limiar de alerta. KPIs cujo SQL foi marcado sql_correct=false sem correção válida estão CAVEATED ou REMOVIDOS; niches sem dados estão listados como BLOQUEADO — sem dados ainda.

---

0. CAVEATS DE JANELA DE DADOS (LEIA ANTES DE QUALQUER NÚMERO)

#	Caveat	Impacto
C1	Metadata de qualidade de texto parou em ~18-23/03/2026 (reações, replies, edições, menções, kind/source_kind).	Taxas de reação/reply/edição/menção SÓ válidas até 2026-03-18 (medidas na janela 16/02-18/03). 0% pós-corte = artefato, não queda.
C2	Pipeline de voz join/left quebrou ~06/06/2026 (só muted/unmuted desde então).	Voice-hours/sessões/concorrência/WAU-voz válidos só até 2026-06-06.
C3	Tabelas de conteúdo (embeds/attachments/threads/activity_reactions) = backfill único de 01-02/05/2026.	Link/media share NÃO trendáveis; caem a 0% em abr-jun como artefato de ETL. Use como snapshot/lifetime.
C4	discord_members.external_identity_id = NULL em 100% (0/25.413).	Impossível ligar membros a messages/voice; coorte joined_at→retenção incomputável; bots não-filtráveis por is_bot.
C5	messages.sent_at tem lixo histórico (min 1999-12-31; 108.928 linhas pré-2019; 269.826 NULL).	Todo trend filtra COALESCE(sent_at,created_at) >= '2019-01-01'; totais usam COALESCE.
C6	Janelas desalinhadas entre fontes: messages (longo), voice (2020+), discord_event_logs (~30d desde 19/05), whatsapp_event_logs (~7d desde 14/06), moderation_events (2019→2025-10).	Cross-source válido SÓ na sobreposição (>= 2026-06-14 para WA×Discord). Nunca combinar moderation_events com discord_event_logs (gap de 7 meses).
C7	Hoje (2026-06-20) é dia PARCIAL (corte ~18-19h BRT) e a semana corrente é parcial.	Nunca ler como queda; hachurar/excluir de médias e alertas.
C8	Pipelines VAZIOS: moderação (cases/actions/appeals/reports/audit/rules), economia (transactions/wallets/characters_wallet/interactions), gamificação dormente desde 2023.	KPIs respectivos = "sem dados (pipeline vazio)", não zero-atividade.
C9	source_kind 100% NULL na janela recente de texto e sem is_bot em discord_event_logs.	Bots/webhooks NÃO excluíveis das KPIs headline de volume — ancorar em "chat orgânico" (exclui Trabalho/Working + Reunião Semanal).
C10	Todo bucketing usa UM único AT TIME ZONE 'America/Sao_Paulo' (sem dupla conversão). Semanas começam na segunda (date_trunc('week')).	—

---

1. CAMADA NORTH STAR

1.1 Membros Ativos Mensais (MAU) — texto ⭐ NORTH STAR PRINCIPAL

Campo	Valor
Definição	external_identity_id distintos com >=1 mensagem nos últimos 30 dias completos (21/05-19/06).
Fonte	messages
SQL	WITH base AS (SELECT external_identity_id, COALESCE(sent_at,created_at) AT TIME ZONE 'America/Sao_Paulo' AS local_ts FROM messages WHERE COALESCE(sent_at,created_at) >= '2019-01-01') SELECT COUNT(DISTINCT external_identity_id) AS mau FROM base WHERE local_ts >= '2026-05-21' AND local_ts < '2026-06-20';
Valor medido	390 (corrigido de 391 — valor canônico via filtro local-tz)
Viz	big number + sparkline mensal
Alvo	>= 400
Alerta	< 300 por 2 semanas consecutivas

1.2 Voice-hours por semana (voz)

Campo	Valor
Definição	Total de voice-hours pareadas (joined→left por external_identity_id+channel_name, cap 12h) por semana.
Fonte	voice_messages (state joined/left)
SQL	WITH ordered AS (SELECT external_identity_id, channel_name, state, occurred_at, LEAD(state) OVER w AS next_state, LEAD(occurred_at) OVER w AS next_occurred FROM voice_messages WHERE state IN ('joined','left') AND occurred_at >= '2020-01-01' WINDOW w AS (PARTITION BY external_identity_id, channel_name ORDER BY occurred_at)), sessions AS (SELECT occurred_at, EXTRACT(EPOCH FROM (next_occurred - occurred_at))/3600.0 AS hours FROM ordered WHERE state='joined' AND next_state='left' AND next_occurred > occurred_at AND next_occurred - occurred_at <= interval '12 hours') SELECT date_trunc('week', occurred_at AT TIME ZONE 'America/Sao_Paulo')::date AS week, ROUND(SUM(hours)::numeric,1) AS voice_hours FROM sessions GROUP BY 1 ORDER BY 1;
Valor medido	Semanas válidas (BRT): 05-04=380,3h; 05-11=393,5h; 05-18=608,2h (pico); 05-25=522,3h; 06-01=503,5h. Total lifetime ~109.473h / 170.861 sessões. 0/null após 06-06 (C2).
Viz	line chart semanal + marcador vertical em 06-06
Alvo	>= 500 voice-hours/semana
Alerta	semana COMPLETA < 350h, OU joined-events nos últimos 3 dias = 0 (pipeline morto)

1.3 Crescimento Líquido de Membros (janela 32d)

Campo	Valor
Definição	GUILD_MEMBER_ADD − GUILD_MEMBER_REMOVE na janela logada (~32d).
Fonte	discord_event_logs
SQL	SELECT COUNT(*) FILTER (WHERE event_type='GUILD_MEMBER_ADD') AS adds, COUNT(*) FILTER (WHERE event_type='GUILD_MEMBER_REMOVE') AS removes, COUNT(*) FILTER (WHERE event_type='GUILD_MEMBER_ADD') - COUNT(*) FILTER (WHERE event_type='GUILD_MEMBER_REMOVE') AS net_growth FROM discord_event_logs WHERE event_type IN ('GUILD_MEMBER_ADD','GUILD_MEMBER_REMOVE');
Valor medido	+313 (698 adds − 385 removes), 2026-05-19 a 2026-06-20
Viz	stacked bar (adds ↑ / removes ↓) + linha net, diário
Alvo	>= +250/30d
Alerta	net 7d rolling <= 0 em semana completa

1.4 Membros Discord Ativos ao Vivo (headline)

Campo	Valor
Definição	Não-bots com left_at IS NULL na tabela fresca discord_members (substitui guild member_count 31d stale).
Fonte	discord_members
SQL	SELECT COUNT(*) FILTER (WHERE left_at IS NULL AND NOT is_bot) AS live_active_members, (SELECT member_count FROM discord_guilds WHERE id=1) AS guild_reported_stale, (SELECT EXTRACT(DAY FROM now() - synced_at)::int FROM discord_guilds WHERE id=1) AS guild_count_days_stale FROM discord_members;
Valor medido	25.031 humanos ativos (vs guild stale 24.723, 31d desatualizado; 5 bots)
Viz	big-number + delta WoW
Alvo	crescimento monotônico WoW
Alerta	queda vs dia anterior (churn líquido negativo sustentado)

1.5 Mensagens WhatsApp por dia

Campo	Valor
Definição	Volume diário de messages.upsert do WhatsApp (display tz).
Fonte	whatsapp_event_logs (type=messages.upsert)
SQL	SELECT (received_at AT TIME ZONE 'America/Sao_Paulo')::date AS dia, COUNT(*) AS upserts FROM whatsapp_event_logs WHERE type='messages.upsert' GROUP BY 1 ORDER BY 1;
Valor medido	06-15:1333, 06-16:1512, 06-17:1444, 06-18:2156 (pico), 06-19:1772, 06-20:680 (PARCIAL). Média dias completos ~1643/dia.
Viz	bar chart diário, último dia hachurado
Alvo	>= 1500/dia (dias completos)
Alerta	< 1000/dia em dia COMPLETO

1.6 Taxa de Conversão TYPING_START → MESSAGE_CREATE (60s)

Campo	Valor
Definição	% de eventos typing onde o mesmo usuário emite MESSAGE_CREATE em até 60s. Funil intenção→post.
Fonte	discord_event_logs
SQL	WITH typing AS (SELECT user_id, created_at FROM discord_event_logs WHERE event_type='TYPING_START' AND user_id IS NOT NULL), msgs AS (SELECT user_id, created_at FROM discord_event_logs WHERE event_type='MESSAGE_CREATE' AND user_id IS NOT NULL) SELECT COUNT(*) AS typing_events, ROUND(100.0 * COUNT(*) FILTER (WHERE EXISTS (SELECT 1 FROM msgs m WHERE m.user_id=t.user_id AND m.created_at>=t.created_at AND m.created_at<=t.created_at + interval '60 seconds'))/NULLIF(COUNT(*),0),2) AS conversion_pct FROM typing t;
Valor medido	90,61% (29.558 de 32.622)
Viz	gauge + sparkline 7d
Alvo	> 85%
Alerta	< 75% em dia completo

1.7 Eventos agendados criados (proxy de reuniões — tabelas vazias)

Campo	Valor
Definição	Eventos Discord GUILD_SCHEDULED_EVENT_* distintos na janela ~30d (proxy de "meetings/mês"; tabelas meetings VAZIAS).
Fonte	discord_event_logs
SQL	SELECT COUNT(DISTINCT COALESCE(payload #>> '{d,id}', payload #>> '{id}', payload #>> '{guild_scheduled_event_id}', payload #>> '{d,guild_scheduled_event_id}')) AS distinct_events FROM discord_event_logs WHERE event_type LIKE 'GUILD_SCHEDULED_EVENT_%';
Valor medido	3 (#4 Reunião Semanal, AULÃO: SYSTEM DESIGN, Reunião Delas #4)
Viz	big number + lista de eventos
Alvo	>= 4 eventos/30d
Alerta	0 eventos agendados em janela de 14d

1.8 Retenção Week-1 (coortes maduras)

Campo	Valor
Definição	% de membros de uma coorte semanal (1ª atividade em messages∪voice) ativos na semana N+1, agregado sobre coortes com >=2 semanas decorridas.
Fonte	messages.sent_at (>=2019) ∪ voice_messages.occurred_at, chave external_identity_id
SQL	WITH activity AS (SELECT external_identity_id, sent_at AS ts FROM messages WHERE sent_at >= '2019-01-01' UNION ALL SELECT external_identity_id, occurred_at FROM voice_messages WHERE occurred_at >= '2019-01-01'), weekly AS (SELECT DISTINCT external_identity_id, date_trunc('week',(ts AT TIME ZONE 'America/Sao_Paulo'))::date AS act_week FROM activity), first_week AS (SELECT external_identity_id, MIN(act_week) AS cohort_week FROM weekly GROUP BY 1), pu AS (SELECT DISTINCT w.external_identity_id, fw.cohort_week, (w.act_week-fw.cohort_week)/7 AS week_n FROM weekly w JOIN first_week fw USING(external_identity_id)), cs AS (SELECT cohort_week, COUNT(DISTINCT external_identity_id) FILTER (WHERE week_n=0) AS size, COUNT(DISTINCT external_identity_id) FILTER (WHERE week_n=1) AS r1 FROM pu GROUP BY 1) SELECT ROUND(100.0*SUM(r1)/NULLIF(SUM(size),0),1) AS w1 FROM cs WHERE cohort_week <= (date_trunc('week', now() AT TIME ZONE 'America/Sao_Paulo')::date - INTERVAL '2 weeks');
Valor medido	21,3% (W1 maduro); W4 maduro = 9,8%
Viz	big number + sparkline das 12 coortes
Alvo	30%
Alerta	< 15%

1.9 Avg Reactions per Reacted Message (viralidade — LIFETIME) ⚠️ CAVEAT C3

Campo	Valor
Definição	Média de reactions_total apenas em mensagens com >=1 reação (lifetime). Não trendável recentemente.
Fonte	messages.reactions_total
SQL (corrigido)	-- avg-when-reacted: SELECT ROUND(AVG(reactions_total)::numeric,4) AS avg_when_reacted FROM messages WHERE reactions_total > 0 AND COALESCE(sent_at,created_at) >= '2019-01-01'; -- overall (query SEPARADA, sem o filtro >0): SELECT ROUND(AVG(reactions_total)::numeric,4) AS overall FROM messages WHERE COALESCE(sent_at,created_at) >= '2019-01-01';
Valor medido	3,869 reações/mensagem reagida (lifetime); overall = 0,0877. ⚠️ Correção: o SQL original duplicava o alias — overall precisa de query separada.
Viz	big number + sparkline anual + anotação de gap de ingestão
Alvo	> 3,5 reações/mensagem reagida
Alerta	média mensal < 2,5 (em janela com ingestão válida)

1.10 Casos de Moderação por Semana ❌ BLOQUEADO — sem dados

Campo	Valor
Definição	KPI-alvo do guarda-corpo: casos abertos/semana. NÃO MENSURÁVEL — moderation_cases = 0 linhas.
Fonte	moderation_cases (VAZIA)
SQL	SELECT date_trunc('week', created_at AT TIME ZONE 'America/Sao_Paulo')::date wk, count(*) FROM moderation_cases WHERE created_at >= '2019-01-01' GROUP BY 1 ORDER BY 1;
Valor medido	SEM DADOS (0 linhas — pipeline não populado)
Viz	placeholder "sem dados (pipeline vazio)"
Alvo	n/a até pipeline gravar
Alerta	0 casos com chat ativo = pipeline desligado (FIRING)

1.11 Participação em Season Ativa ❌ BLOQUEADO — gamificação dormente

Campo	Valor
Definição	Share de characters rankeados na season ativa. 0 seasons ativas hoje.
Fonte	seasons, seasons_rankings
SQL	SELECT s.id, s.name, (SELECT COUNT(DISTINCT sr.character_id) FROM seasons_rankings sr WHERE sr.season_id::text = s.id::text) AS ranked_chars FROM seasons s WHERE now() BETWEEN COALESCE(s.started_at,'-infinity') AND COALESCE(s.ended_at,'infinity');
Valor medido	0 seasons ativas (ambas terminaram até 2023-12-31)
Viz	big-number + status badge (none)
Alvo	1 season ativa sempre; >5% rankeados
Alerta	0 seasons ativas (FIRING)

1.12 Level-ups semanais (liveness de gamificação) ❌ DORMENTE desde 2023

Campo	Valor
Definição	Level-ups/semana (heartbeat do loop de XP).
Fonte	characters_leveling_logs
SQL	SELECT date_trunc('week', created_at AT TIME ZONE 'America/Sao_Paulo') AS wk, COUNT(*) AS levelups, COUNT(DISTINCT character_id) AS chars FROM characters_leveling_logs GROUP BY 1 ORDER BY 1 DESC LIMIT 12;
Valor medido	Último level-up 2023-03-14. 2023-01=1021 → 02=392 → 03=134 → 0. Semanas 2026 = 0.
Viz	time-series com marcador "sem dados desde 2023-03"
Alvo	>100/semana quando vivo
Alerta	0 por 7+ dias (FIRING há ~3 anos)

---

2. CAMADA INPUT

2.1 DAU médio / WAU — texto (CORRIGIDO)

Campo	Valor
Definição	DAU médio dos últimos 30 dias completos + WAU. Correção de bug de fronteira de fuso.
Fonte	messages
SQL (corrigido)	WITH daily AS (SELECT (COALESCE(sent_at,created_at) AT TIME ZONE 'America/Sao_Paulo')::date AS d, external_identity_id FROM messages WHERE (COALESCE(sent_at,created_at) AT TIME ZONE 'America/Sao_Paulo') >= '2026-05-21' AND (COALESCE(sent_at,created_at) AT TIME ZONE 'America/Sao_Paulo') < '2026-06-20'), per_day AS (SELECT d, COUNT(DISTINCT external_identity_id) AS dau FROM daily GROUP BY d) SELECT ROUND(AVG(dau),1) AS avg_dau_30d FROM per_day;
Valor medido	DAU médio = 38,7 (corrigido de 37,6); WAU(13-19/06)=165; range diário 9-84 (corrigido de 17-84)
Viz	line chart diário com banda partial-day + média móvel 7d
Alvo	DAU médio >= 45
Alerta	DAU médio < 25 (excluindo feriados)

2.2 Mensagens por membro ativo (30d) — CORRIGIDO

Campo	Valor
Definição	Total msgs / membros ativos distintos (30d completos). Inflada pelo canal Trabalho/Working.
Fonte	messages
SQL	WITH base AS (SELECT * FROM messages WHERE (COALESCE(sent_at,created_at) AT TIME ZONE 'America/Sao_Paulo') >= '2026-05-21' AND (COALESCE(sent_at,created_at) AT TIME ZONE 'America/Sao_Paulo') < '2026-06-20') SELECT COUNT(*) AS total, COUNT(DISTINCT external_identity_id) AS active, ROUND(COUNT(*)::numeric/NULLIF(COUNT(DISTINCT external_identity_id),0),1) AS msgs_per_active FROM base;
Valor medido	27.418 msgs / 390 ativos = 70,3 (denominador corrigido para 390, consistente com MAU)
Viz	bar + média vs mediana lado a lado
Alvo	monitorar tendência (sem alvo absoluto)
Alerta	queda > 30% MoM

2.3 WAU-voz (usuários ativos de voz/semana)

Campo	Valor
Definição	external_identity_id distintos com >=1 sessão de voz pareada na semana.
Fonte	voice_messages
SQL	WITH ordered AS (SELECT external_identity_id, channel_name, state, occurred_at, LEAD(state) OVER w AS next_state, LEAD(occurred_at) OVER w AS next_occurred FROM voice_messages WHERE state IN ('joined','left') AND occurred_at >= '2020-01-01' WINDOW w AS (PARTITION BY external_identity_id, channel_name ORDER BY occurred_at)), sessions AS (SELECT external_identity_id, occurred_at FROM ordered WHERE state='joined' AND next_state='left' AND next_occurred > occurred_at AND next_occurred - occurred_at <= interval '12 hours') SELECT date_trunc('week', occurred_at AT TIME ZONE 'America/Sao_Paulo')::date AS week, COUNT(DISTINCT external_identity_id) AS active_voice_users FROM sessions GROUP BY 1 ORDER BY 1;
Valor medido	05-04=321, 05-11=300, 05-18=387 (pico), 05-25=245, 06-01=314. 0 após 06-06 (C2).
Viz	line chart semanal
Alvo	>= 300/semana
Alerta	WAU-voz semana completa < 200

2.4 Pico de Concorrência de Voz (diário)

Campo	Valor
Definição	Máx. usuários simultâneos em voz/dia (running sum +1 join / −1 left).
Fonte	voice_messages
SQL	WITH evt AS (SELECT occurred_at, CASE WHEN state='joined' THEN 1 WHEN state='left' THEN -1 ELSE 0 END AS delta, (occurred_at AT TIME ZONE 'America/Sao_Paulo')::date AS day FROM voice_messages WHERE state IN ('joined','left') AND occurred_at >= now() - interval '60 days'), running AS (SELECT day, SUM(delta) OVER (PARTITION BY day ORDER BY occurred_at, delta DESC) AS concurrent FROM evt) SELECT day, MAX(concurrent) AS peak_concurrent FROM running GROUP BY day ORDER BY day DESC;
Valor medido	06-01=45, 05-25=55, 05-11=55, 05-04=51 (picos de reunião) vs baseline 2-10. Não confiável após 06-06 (C2).
Viz	bar chart diário (30d), destacar reuniões
Alvo	pico semanal >= 40 em dias de reunião
Alerta	pico de dia de reunião < 20

2.5 Daily Member Adds / Removes

Campo	Valor
Definição	Contagem bruta de GUILD_MEMBER_ADD e REMOVE/dia (display tz).
Fonte	discord_event_logs
SQL	SELECT (created_at AT TIME ZONE 'America/Sao_Paulo')::date AS day, COUNT(*) FILTER (WHERE event_type='GUILD_MEMBER_ADD') AS adds, COUNT(*) FILTER (WHERE event_type='GUILD_MEMBER_REMOVE') AS removes FROM discord_event_logs WHERE event_type IN ('GUILD_MEMBER_ADD','GUILD_MEMBER_REMOVE') GROUP BY 1 ORDER BY 1;
Valor medido	Adds ~22/dia (pico 47 em 05-23); removes ~12/dia (spike 26 em 06-16); 06-20 PARCIAL (6/7)
Viz	grouped daily bar
Alvo	adds >= 20/dia
Alerta	adds 0 em dia completo (provável outage de ingestão)

2.6 Tamanho de coorte semanal (novos ativos/semana)

Campo	Valor
Definição	Membros distintos cuja PRIMEIRA atividade (messages∪voice) caiu naquela semana. Denominador de toda retenção.
Fonte	messages ∪ voice_messages
SQL	WITH activity AS (SELECT external_identity_id, sent_at AS ts FROM messages WHERE sent_at >= '2019-01-01' UNION ALL SELECT external_identity_id, occurred_at FROM voice_messages WHERE occurred_at >= '2019-01-01'), weekly AS (SELECT external_identity_id, date_trunc('week',(ts AT TIME ZONE 'America/Sao_Paulo'))::date AS act_week FROM activity), first_week AS (SELECT external_identity_id, MIN(act_week) AS cohort_week FROM weekly GROUP BY 1) SELECT cohort_week, COUNT(*) AS new_active_members FROM first_week WHERE cohort_week >= (date_trunc('week', now() AT TIME ZONE 'America/Sao_Paulo')::date - INTERVAL '12 weeks') GROUP BY 1 ORDER BY 1;
Valor medido	04-27=114, 05-04=83, 05-18=109, 06-01=84, 06-08=43, 06-15=41 (PARCIAL)
Viz	column chart, última barra hachurada
Alvo	n/a
Alerta	queda > 40% vs média 8 semanas (semana completa)

2.7 Novos membros Discord por semana (joined_at) — INPUT NÃO-LINKADO ⚠️ C4

Campo	Valor
Definição	Novos membros/semana via discord_members.joined_at (is_bot=false). Não cruzável com retenção (external_identity_id NULL).
Fonte	discord_members.joined_at
SQL	SELECT date_trunc('week', joined_at AT TIME ZONE 'America/Sao_Paulo')::date AS join_week, COUNT(*) AS new_members FROM discord_members WHERE is_bot=false AND joined_at IS NOT NULL AND joined_at >= (now() - INTERVAL '13 weeks') GROUP BY 1 ORDER BY 1;
Valor medido	04-27=155, 05-18=219 (pico), 06-08=142, 06-15=122 (parcial)
Viz	column chart com badge "não-linkado à retenção"
Alvo	n/a
Alerta	queda > 40% vs média 8 semanas

2.8 Chats ativos distintos / dia (WhatsApp)

Campo	Valor
Definição	chat_jid distintos com >=1 upsert/dia (largura do engajamento).
Fonte	whatsapp_event_logs
SQL	SELECT (received_at AT TIME ZONE 'America/Sao_Paulo')::date AS dia, COUNT(DISTINCT chat_jid) AS chats_ativos FROM whatsapp_event_logs WHERE type='messages.upsert' GROUP BY 1 ORDER BY 1;
Valor medido	06-15:6, 06-16:4, 06-17:4, 06-18:4, 06-19:4, 06-20:4. Total 6 chats distintos.
Viz	line chart
Alvo	>= 5 chats ativos/dia
Alerta	<= 2 em dia completo

2.9 Participantes distintos ativos/dia (WhatsApp DAU)

Campo	Valor
Definição	Remetentes distintos (payload key.participant) com >=1 msg/dia.
Fonte	whatsapp_event_logs
SQL	SELECT (received_at AT TIME ZONE 'America/Sao_Paulo')::date AS dia, COUNT(DISTINCT payload->'key'->>'participant') AS participantes FROM whatsapp_event_logs WHERE type='messages.upsert' GROUP BY 1 ORDER BY 1;
Valor medido	06-15:121, 06-16:114, 06-17:89, 06-18:134 (pico), 06-19:106, 06-20:70 (parcial)
Viz	line chart DAU
Alvo	>= 100 DAU/dia
Alerta	< 60 em dia completo

2.10 Throughput Horário de Eventos (hora-do-dia, BRT)

Campo	Valor
Definição	Total de eventos por hora local (display tz) — janelas de pico.
Fonte	discord_event_logs
SQL	SELECT EXTRACT(HOUR FROM created_at AT TIME ZONE 'America/Sao_Paulo')::int AS hour_local, COUNT(*) AS total_events, COUNT(*) FILTER (WHERE event_type='MESSAGE_CREATE') AS messages, COUNT(*) FILTER (WHERE event_type='VOICE_STATE_UPDATE') AS voice FROM discord_event_logs GROUP BY 1 ORDER BY 1;
Valor medido	Pico 22h BRT (17.471 eventos / 7.456 msgs, ~30% acima de 21h). Bloco forte 11h-23h. Voz pico 19h (1.531).
Viz	24-bar hora-do-dia (events + voice overlay)
Alvo	n/a (perfil)
Alerta	achatamento do pico 22h

2.11 Volume de Eventos de Voz (VOICE_STATE_UPDATE)

Campo	Valor
Definição	Contagem de transições de estado de voz/dia (proxy de voz síncrona via event-logs).
Fonte	discord_event_logs
SQL	SELECT (created_at AT TIME ZONE 'America/Sao_Paulo')::date AS day_local, COUNT(*) AS voice_events FROM discord_event_logs WHERE event_type='VOICE_STATE_UPDATE' GROUP BY 1 ORDER BY 1 DESC;
Valor medido	12.775 total (~399/dia). Pico 19h (1.531), 2º lugar 20h (1.087) — correção: não 18h.
Viz	bar diário + heatmap hora-do-dia
Alvo	> 300/dia
Alerta	< 100/dia em dia completo

2.12 Total RSVP interest signals (eventos)

Campo	Valor
Definição	GUILD_SCHEDULED_EVENT_USER_ADD = membros marcando interesse.
Fonte	discord_event_logs
SQL	SELECT COUNT(*) AS rsvp_add FROM discord_event_logs WHERE event_type='GUILD_SCHEDULED_EVENT_USER_ADD';
Valor medido	40 RSVPs
Viz	big number
Alvo	crescimento vs 30d anterior
Alerta	< 5 RSVPs em janela de 14d

2.13 Unique interested users (eventos)

Campo	Valor
Definição	Usuários distintos que deram RSVP a >=1 evento.
Fonte	discord_event_logs
SQL	SELECT COUNT(DISTINCT user_id) AS unique_interested_users FROM discord_event_logs WHERE event_type='GUILD_SCHEDULED_EVENT_USER_ADD' AND user_id IS NOT NULL;
Valor medido	33 usuários
Viz	big number
Alvo	trend up MoM
Alerta	queda > 40% vs mediana 30d (excl. semana parcial)

2.14 Weekly RSVP trend (eventos)

Campo	Valor
Definição	RSVP adds/removes/usuários distintos por semana ISO. Semana atual PARCIAL.
Fonte	discord_event_logs
SQL	SELECT to_char(date_trunc('week', created_at AT TIME ZONE 'America/Sao_Paulo'), 'YYYY-MM-DD') AS week_start, COUNT(*) FILTER (WHERE event_type='GUILD_SCHEDULED_EVENT_USER_ADD') AS rsvp_add, COUNT(*) FILTER (WHERE event_type='GUILD_SCHEDULED_EVENT_USER_REMOVE') AS rsvp_remove, COUNT(DISTINCT user_id) FILTER (WHERE event_type='GUILD_SCHEDULED_EVENT_USER_ADD') AS unique_users FROM discord_event_logs WHERE event_type IN ('GUILD_SCHEDULED_EVENT_USER_ADD','GUILD_SCHEDULED_EVENT_USER_REMOVE') GROUP BY 1 ORDER BY 1;
Valor medido	05-18:8/1/7, 05-25:9/6/9, 06-01:9/4/8, 06-08:5/0/4, 06-15:9/1/6 (PARCIAL)
Viz	stacked/line por semana
Alvo	trailing 4-week não declinante
Alerta	2 semanas completas consecutivas < 50% da média

2.15 Acoes de auto-mod por semana (tendência)

Campo	Valor
Definição	Volume semanal de automod e bans (display tz). Semana atual PARCIAL.
Fonte	discord_event_logs
SQL	SELECT date_trunc('week', created_at AT TIME ZONE 'America/Sao_Paulo')::date wk, count(*) FILTER (WHERE event_type='AUTO_MODERATION_ACTION_EXECUTION') automod, count(*) FILTER (WHERE event_type='GUILD_BAN_ADD') bans FROM discord_event_logs WHERE event_type IN ('AUTO_MODERATION_ACTION_EXECUTION','GUILD_BAN_ADD') AND created_at >= '2026-05-19' GROUP BY 1 ORDER BY 1;
Valor medido	05-18:8/2, 05-25:2/17, 06-01:1/9, 06-08:8/11, 06-15:26/20 (PARCIAL)
Viz	linha dupla, última barra hachurada
Alvo	estável; spikes investigados
Alerta	semana completa > 3x mediana das 4 anteriores

2.16 XP velocity (mediana de dias entre level-ups) ❌ HISTÓRICO (dormente)

Campo	Valor
Definição	Mediana de dias entre level-ups consecutivos do mesmo character. Só transições pré-2023.
Fonte	characters_leveling_logs
SQL	WITH paired AS (SELECT character_id, level, created_at, LAG(created_at) OVER (PARTITION BY character_id ORDER BY level) AS prev FROM characters_leveling_logs) SELECT ROUND(AVG(EXTRACT(EPOCH FROM (created_at - prev))/86400.0)::numeric,2) AS avg_days, ROUND(percentile_cont(0.5) WITHIN GROUP (ORDER BY EXTRACT(EPOCH FROM (created_at - prev))/86400.0)::numeric,2) AS median_days, COUNT(*) AS transitions FROM paired WHERE prev IS NOT NULL AND created_at >= prev;
Valor medido	Mediana 13,92 dias, média 79,5d, 1.638 transições (todas pré-2023-03)
Viz	big-number + histograma de gaps
Alvo	mediana 7-14 dias
Alerta	mediana > 30 dias (quando vivo)

2.17 Daily-bonus claim adoption ❌ DORMENTE

Campo	Valor
Definição	% de characters que já reivindicaram daily_bonus + recência.
Fonte	characters
SQL	SELECT COUNT(*) AS total_chars, COUNT(*) FILTER (WHERE daily_bonus_claimed_at IS NOT NULL) AS ever_claimed, ROUND(100.0*COUNT(*) FILTER (WHERE daily_bonus_claimed_at IS NOT NULL)/NULLIF(COUNT(*),0),3) AS adoption_pct, MAX(daily_bonus_claimed_at AT TIME ZONE 'America/Sao_Paulo') AS last_claim FROM characters;
Valor medido	0,889% (350/39.388); último claim 2023-05-20 12:49 BRT (~3 anos atrás)
Viz	big-number + badge de recência
Alvo	>10% dos ativos
Alerta	0 claims em 7 dias (FIRING)

2.18 Interaction-driven XP/coin awards ❌ BLOQUEADO — sem dados

Campo	Valor
Definição	Volume de interactions + XP/coins/semana. interactions = 0 linhas.
Fonte	interactions (VAZIA)
SQL	SELECT type, provider, COUNT(*) AS n, SUM(xp_awarded) AS xp, SUM(coins_awarded) AS coins FROM interactions GROUP BY type, provider ORDER BY n DESC;
Valor medido	SEM DADOS (0 linhas)
Viz	empty-state card
Alvo	volume semanal > 0
Alerta	0 interactions em 24h (FIRING)

2.19 Reaction Rate (% msgs reagidas, por ano) ⚠️ C1

Campo	Valor
Definição	Share de mensagens com >=1 reação, por ano (ingestão decaiu — ler por ano).
Fonte	messages.reactions_total
SQL	SELECT date_trunc('year',(COALESCE(sent_at,created_at) AT TIME ZONE 'America/Sao_Paulo'))::date AS yr, COUNT(*) AS msgs, ROUND(100.0*COUNT(*) FILTER (WHERE reactions_total>0)/COUNT(*),3) AS pct_reacted FROM messages WHERE COALESCE(sent_at,created_at) >= '2019-01-01' GROUP BY 1 ORDER BY 1;
Valor medido	2020:6,69% (pico) → 2023:0,79% → 2026:0,04% (decaimento = gap de ingestão, NÃO colapso real)
Viz	bar anual + banda de gap 2024-2026
Alvo	baseline ~5-7% (2020)
Alerta	mês completo < 1,5% (após corrigir ingestão)

---

3. CAMADA HEALTH

3.1 Stickiness DAU/MAU — texto (CORRIGIDO)

Campo	Valor
Definição	DAU_médio / MAU (30d completos). Frequência de retorno.
Fonte	messages
SQL (corrigido)	WITH daily AS (SELECT (COALESCE(sent_at,created_at) AT TIME ZONE 'America/Sao_Paulo')::date AS d, external_identity_id FROM messages WHERE (COALESCE(sent_at,created_at) AT TIME ZONE 'America/Sao_Paulo') >= '2026-05-21' AND (COALESCE(sent_at,created_at) AT TIME ZONE 'America/Sao_Paulo') < '2026-06-20'), per_day AS (SELECT d, COUNT(DISTINCT external_identity_id) AS dau FROM daily GROUP BY d) SELECT ROUND(100.0*AVG(dau)/(SELECT COUNT(DISTINCT external_identity_id) FROM daily),2) AS stickiness_pct FROM per_day;
Valor medido	9,91% (corrigido de 9,63%; abaixo do benchmark ~20%)
Viz	gauge + trend
Alvo	>= 15%
Alerta	< 8%

3.2 Concentração de canais (share top-1 — texto)

Campo	Valor
Definição	% do canal #1 no total de msgs (30d), join com discord_channels.
Fonte	messages + discord_channels
SQL	WITH base AS (SELECT m.channel_id FROM messages m WHERE COALESCE(m.sent_at,m.created_at) >= '2026-05-21' AND COALESCE(m.sent_at,m.created_at) < '2026-06-20'), ch AS (SELECT b.channel_id, dc.name, COUNT(*) AS n FROM base b LEFT JOIN discord_channels dc ON dc.discord_channel_id=b.channel_id GROUP BY b.channel_id, dc.name) SELECT name, n, ROUND(100.0*n/SUM(n) OVER (),2) AS pct FROM ch ORDER BY n DESC LIMIT 5;
Valor medido	Top-1 'Trabalho/Working' 62,57%; Top-2 'Reunião Semanal' 30,35% (acum. 92,9%); 'bate-papo' 3,99%
Viz	treemap / pareto de canais
Alvo	top-1 < 40%
Alerta	top-1 > 65%

3.3 Share do top 1% de membros (power-law — texto)

Campo	Valor
Definição	% das msgs geradas pelo 1% (e 10%) mais ativo (30d).
Fonte	messages
SQL	WITH per_user AS (SELECT external_identity_id, COUNT(*) AS msgs FROM messages WHERE COALESCE(sent_at,created_at) >= '2026-05-21' AND COALESCE(sent_at,created_at) < '2026-06-20' GROUP BY external_identity_id), ranked AS (SELECT msgs, NTILE(100) OVER (ORDER BY msgs DESC) AS pctile, SUM(msgs) OVER () AS total FROM per_user) SELECT ROUND(100.0*SUM(msgs) FILTER (WHERE pctile=1)/MAX(total),2) AS top1pct, ROUND(100.0*SUM(msgs) FILTER (WHERE pctile<=10)/MAX(total),2) AS top10pct FROM ranked;
Valor medido	Top 1% = 34,28%; Top 10% = 87,96%
Viz	lorenz curve / concentração
Alvo	top 1% < 25%
Alerta	top 1% > 40%

3.4 Median session duration (voz)

Campo	Valor
Definição	Mediana (P50) de minutos por sessão de voz pareada (distribuição right-skewed).
Fonte	voice_messages
SQL	WITH ordered AS (SELECT external_identity_id, channel_name, state, occurred_at, LEAD(state) OVER w AS next_state, LEAD(occurred_at) OVER w AS next_occurred FROM voice_messages WHERE state IN ('joined','left') AND occurred_at >= '2020-01-01' WINDOW w AS (PARTITION BY external_identity_id, channel_name ORDER BY occurred_at)), sessions AS (SELECT EXTRACT(EPOCH FROM (next_occurred - occurred_at))/3600.0 AS hours FROM ordered WHERE state='joined' AND next_state='left' AND next_occurred > occurred_at AND next_occurred - occurred_at <= interval '12 hours') SELECT ROUND((PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY hours)*60)::numeric,1) AS median_min, ROUND((AVG(hours)*60)::numeric,1) AS mean_min FROM sessions;
Valor medido	Mediana 5,3 min, Média 38,4 min (gap confirma skew)
Viz	single stat (mediana) + stat secundário (média)
Alvo	mediana subindo
Alerta	mediana < 3 min

3.5 Voice drop-in (bounce) rate

Campo	Valor
Definição	Share de sessões pareadas < 1 minuto (joins acidentais/curiosidade).
Fonte	voice_messages
SQL	WITH ordered AS (SELECT external_identity_id, channel_name, state, occurred_at, LEAD(state) OVER w AS next_state, LEAD(occurred_at) OVER w AS next_occurred FROM voice_messages WHERE state IN ('joined','left') AND occurred_at >= '2020-01-01' WINDOW w AS (PARTITION BY external_identity_id, channel_name ORDER BY occurred_at)), sessions AS (SELECT EXTRACT(EPOCH FROM (next_occurred - occurred_at))/60.0 AS minutes FROM ordered WHERE state='joined' AND next_state='left' AND next_occurred > occurred_at AND next_occurred - occurred_at <= interval '12 hours') SELECT ROUND((COUNT(*) FILTER (WHERE minutes < 1)::numeric / COUNT(*)),3) AS drop_in_rate, COUNT(*) FILTER (WHERE minutes < 1) AS under_1min, COUNT(*) FILTER (WHERE minutes >= 120) AS over_2h FROM sessions;
Valor medido	0,354 (35,4%; 60.554 de 170.861 < 1 min)
Viz	gauge + histograma de buckets
Alvo	< 30%
Alerta	> 45%

3.6 Voice session pairing completeness (instrumentação)

Campo	Valor
Definição	Share de 'joined' com 'left' correspondente. Saúde de instrumentação.
Fonte	voice_messages
SQL	WITH ordered AS (SELECT external_identity_id, channel_name, state, occurred_at, LEAD(state) OVER w AS next_state FROM voice_messages WHERE state IN ('joined','left') AND occurred_at >= '2020-01-01' WINDOW w AS (PARTITION BY external_identity_id, channel_name ORDER BY occurred_at)) SELECT COUNT(*) FILTER (WHERE state='joined') AS joins, COUNT(*) FILTER (WHERE state='joined' AND next_state='left') AS matched, ROUND((COUNT(*) FILTER (WHERE state='joined' AND next_state='left')::numeric / NULLIF(COUNT(*) FILTER (WHERE state='joined'),0)),3) AS pairing_rate FROM ordered;
Valor medido	0,878 (173.528/197.743; 12,2% orphan joins)
Viz	single stat com threshold vermelho/verde
Alvo	>= 0,90
Alerta	< 0,80 (instrumentação degradando)

3.7 Daily Churn Ratio (removes/adds — Discord)

Campo	Valor
Definição	Removes/adds por dia. >1.0 = guild encolheu.
Fonte	discord_event_logs
SQL	WITH d AS (SELECT (created_at AT TIME ZONE 'America/Sao_Paulo')::date AS day, COUNT(*) FILTER (WHERE event_type='GUILD_MEMBER_ADD') AS adds, COUNT(*) FILTER (WHERE event_type='GUILD_MEMBER_REMOVE') AS removes FROM discord_event_logs WHERE event_type IN ('GUILD_MEMBER_ADD','GUILD_MEMBER_REMOVE') GROUP BY 1) SELECT day, adds, removes, ROUND(removes::numeric/NULLIF(adds,0),3) AS churn_ratio FROM d ORDER BY day;
Valor medido	Média 0,552; range 0,191-2,600; dias neg: 06-05 (1,167), 06-16 (2,600), 06-20 parcial (1,167)
Viz	line diário com banda 1.0
Alvo	< 0,60
Alerta	> 1,0 em dia completo, ou 3-day rolling > 0,80

3.8 Weekly Net Growth Trend (Discord)

Campo	Valor
Definição	Net member change por semana ISO (display tz). Semana atual PARCIAL.
Fonte	discord_event_logs
SQL	WITH w AS (SELECT date_trunc('week',(created_at AT TIME ZONE 'America/Sao_Paulo'))::date AS wk, COUNT(*) FILTER (WHERE event_type='GUILD_MEMBER_ADD') AS adds, COUNT(*) FILTER (WHERE event_type='GUILD_MEMBER_REMOVE') AS removes FROM discord_event_logs WHERE event_type IN ('GUILD_MEMBER_ADD','GUILD_MEMBER_REMOVE') GROUP BY 1) SELECT wk, adds, removes, adds-removes AS net, ROUND(removes::numeric/NULLIF(adds,0),3) AS churn_ratio FROM w ORDER BY wk;
Valor medido	05-18:+106 (0,354), 05-25:+45 (0,659), 06-01:+54 (0,609), 06-08:+64 (0,549), 06-15:+44 (0,639, PARCIAL) — desacelerando
Viz	bar por semana, atual hachurada
Alvo	net >= +50/semana completa
Alerta	2 semanas completas consecutivas com net < +30

3.9 Early Churn Rate (7-day, coorte 30d)

Campo	Valor
Definição	Dos que entraram nos últimos 30d, % que saíram em 7 dias. Sinal de qualidade de onboarding.
Fonte	discord_members
SQL	SELECT ROUND(100.0 * COUNT(*) FILTER (WHERE joined_at >= now() - interval '30 days' AND left_at IS NOT NULL AND left_at <= joined_at + interval '7 days') / NULLIF(COUNT(*) FILTER (WHERE joined_at >= now() - interval '30 days'),0),2) AS early_churn_7d_pct, COUNT(*) FILTER (WHERE joined_at >= now() - interval '30 days') AS joined_last_30d FROM discord_members WHERE NOT is_bot;
Valor medido	15,43% (100 de 648)
Viz	single stat + trend semanal de coorte
Alvo	< 10%
Alerta	> 20% na coorte trailing-30d

3.10 Tenure Distribution of Churned Members

Campo	Valor
Definição	Histograma de tenure (<1d…365+d) dos que saíram.
Fonte	discord_members
SQL	WITH t AS (SELECT EXTRACT(EPOCH FROM (left_at - joined_at))/86400.0 AS tenure_days FROM discord_members WHERE left_at IS NOT NULL AND joined_at IS NOT NULL AND NOT is_bot) SELECT CASE WHEN tenure_days<1 THEN 'a. <1 day' WHEN tenure_days<7 THEN 'b. 1-7 days' WHEN tenure_days<30 THEN 'c. 7-30 days' WHEN tenure_days<90 THEN 'd. 30-90 days' WHEN tenure_days<365 THEN 'e. 90-365 days' ELSE 'f. 365+ days' END AS bucket, COUNT(*) AS n, ROUND(100.0*COUNT(*)/SUM(COUNT(*)) OVER (),1) AS pct FROM t GROUP BY 1 ORDER BY 1;
Valor medido	377 churned: <1d 70 (18,6%), 1-7d 37 (9,8%), 7-30d 33, 30-90d 37, 90-365d 50, 365+d 150 (39,8%); mediana 106,7d
Viz	histograma horizontal
Alvo	<1d+1-7d combinado < 20% (atual 28,4%)
Alerta	<7d share > 40% em uma semana

3.11 Razão de churn de grupo WhatsApp (adds/removes)

Campo	Valor
Definição	Total adds / removes na janela. >1 = entrada supera saída.
Fonte	whatsapp_event_logs
SQL	SELECT ROUND(SUM(CASE WHEN payload->>'action'='add' THEN jsonb_array_length(payload->'participants') ELSE 0 END)::numeric / NULLIF(SUM(CASE WHEN payload->>'action'='remove' THEN jsonb_array_length(payload->'participants') ELSE 0 END),0),2) AS churn_ratio FROM whatsapp_event_logs WHERE type='group-participants.update';
Valor medido	3,66 (278 adds / 76 removes)
Viz	gauge / big number
Alvo	>= 1.5
Alerta	< 1.0

3.12 Índice de concentração de chat WhatsApp (top-chat share)

Campo	Valor
Definição	% de mensagens concentradas no chat líder.
Fonte	whatsapp_event_logs
SQL	SELECT chat_jid, COUNT(*) AS msgs, ROUND(100.0*COUNT(*)/SUM(COUNT(*)) OVER (),1) AS pct_share FROM whatsapp_event_logs WHERE type='messages.upsert' GROUP BY 1 ORDER BY msgs DESC;
Valor medido	Top chat 87,5% (7955/9087); 2º 8,1%; só 6 chats distintos
Viz	stacked bar / treemap
Alvo	< 70% no top chat
Alerta	> 85% (estado atual ALERTA)

3.13 XP concentration (top-1% / top-10% share) ❌ HISTÓRICO

Campo	Valor
Definição	Share de XP total do top 1% e 10% dos characters.
Fonte	characters
SQL	WITH ranked AS (SELECT experience, NTILE(100) OVER (ORDER BY experience DESC) AS pct FROM characters WHERE experience > 0) SELECT ROUND(100.0*SUM(experience) FILTER (WHERE pct=1)/NULLIF(SUM(experience),0),2) AS top1pct_share, ROUND(100.0*SUM(experience) FILTER (WHERE pct<=10)/NULLIF(SUM(experience),0),2) AS top10pct_share FROM ranked;
Valor medido	Top 1% = 50,60%; Top 10% = 97,11%
Viz	lorenz curve / stacked share
Alvo	top 10% < 70%
Alerta	top 1% > 40% (FIRING)

3.14 Character creation vs activation gap

Campo	Valor
Definição	% de characters com XP>0 vs total. Expõe gap entre criação de conta e gamificação.
Fonte	characters
SQL	SELECT COUNT(*) AS total_chars, COUNT(*) FILTER (WHERE experience > 0) AS chars_with_xp, ROUND(100.0*COUNT(*) FILTER (WHERE experience > 0)/NULLIF(COUNT(*),0),2) AS activation_pct, MAX(created_at) AS last_char_created FROM characters;
Valor medido	70,82% têm XP (legado); 29,2% (11.492) com 0 XP; último char 2026-06-20 (contas crescem, gamificação parada)
Viz	dual-axis: chars criados vs level-ups
Alvo	>60% dos novos ganham XP em 30d
Alerta	XP-activation de novos < 20%

3.15 Taxa de reversão de moderação (unban/ban) ❌ legado até 2025-10

Campo	Valor
Definição	Unbans/bans no histórico. Proxy de falsos-positivos.
Fonte	moderation_events
SQL	SELECT count(*) FILTER (WHERE type='ban') bans, count(*) FILTER (WHERE type='unban') unbans, round(count(*) FILTER (WHERE type='unban')::numeric / NULLIF(count(*) FILTER (WHERE type='ban'),0) * 100, 2) reversal_pct FROM moderation_events WHERE occurred_at >= '2019-01-01';
Valor medido	2,22% (9 unbans / 406 bans)
Viz	big number + meta
Alvo	< 5%
Alerta	> 10%

3.16 Cobertura de motivo de moderação ❌ legado

Campo	Valor
Definição	% de eventos com reason preenchido (auditabilidade).
Fonte	moderation_events
SQL	SELECT round(count(*) FILTER (WHERE reason IS NOT NULL)::numeric / count(*) * 100,2) reason_coverage_pct FROM moderation_events WHERE occurred_at >= '2019-01-01';
Valor medido	37,18% (177/476)
Viz	gauge
Alvo	> 90%
Alerta	< 70%

3.17 Auto-mod por dia ⚠️ denominador

Campo	Valor
Definição	Média diária de AUTO_MODERATION_ACTION_EXECUTION (~30d).
Fonte	discord_event_logs
SQL	SELECT round(count(*)::numeric / 30, 2) automod_per_day FROM discord_event_logs WHERE event_type='AUTO_MODERATION_ACTION_EXECUTION' AND created_at >= '2026-05-19';
Valor medido	1,50/dia (/30) ou 1,55/dia (normalizado pelo span real de 29 dias — escolher denominador explícito)
Viz	big number + sparkline semanal
Alvo	> 0 e estável
Alerta	0 por > 3 dias (automod quebrado)

3.18 Bans por dia ⚠️ denominador

Campo	Valor
Definição	Média diária de GUILD_BAN_ADD (~30d).
Fonte	discord_event_logs
SQL	SELECT round(count(*)::numeric / 30, 2) bans_per_day FROM discord_event_logs WHERE event_type='GUILD_BAN_ADD' AND created_at >= '2026-05-19';
Valor medido	1,97/dia (/30) ou 2,03/dia (/29 span real); 59 bans, 59 alvos distintos via payload
Viz	big number + barras por semana
Alvo	baseline baixo e estável
Alerta	spike > 3x média 7d (possível raid)

3.19 Concentração do auto-mod (ações/alvo)

Campo	Valor
Definição	Ações de automod / usuários-alvo distintos. Alto = reincidentes.
Fonte	discord_event_logs
SQL	SELECT count(DISTINCT user_id) distinct_targets, count(*) actions, round(count(*)::numeric/NULLIF(count(DISTINCT user_id),0),2) actions_per_target FROM discord_event_logs WHERE event_type='AUTO_MODERATION_ACTION_EXECUTION' AND created_at >= '2026-05-19';
Valor medido	5,63 ações/alvo (45 ações em 8 alvos)
Viz	gauge + tabela top-ofensores
Alvo	próximo de 1.0
Alerta	> 5 (reincidência alta — escalonar)

3.20 DAU por sinal de evento (Discord real-time)

Campo	Valor
Definição	user_id distintos ativos/dia local (texto+voz+reação). Denominador de normalização.
Fonte	discord_event_logs
SQL	SELECT (created_at AT TIME ZONE 'America/Sao_Paulo')::date AS day_local, COUNT(DISTINCT user_id) AS dau, COUNT(*) FILTER (WHERE event_type='MESSAGE_CREATE') AS messages FROM discord_event_logs GROUP BY 1 ORDER BY 1 DESC LIMIT 14;
Valor medido	06-19=72, 06-18=139, 06-17=184, 06-15=170, 06-10=195; 06-20=38 (PARCIAL); range ~54-195
Viz	line diário, dia parcial hachurado
Alvo	> 100 em dias úteis
Alerta	< 50 em dia útil completo

3.21 Taxa de Edição de Mensagens (real-time)

Campo	Valor
Definição	MESSAGE_UPDATE/MESSAGE_CREATE. Cuidado/churn. ⚠️ denominador poluído por bot channels.
Fonte	discord_event_logs
SQL	SELECT ROUND(COUNT(*) FILTER (WHERE event_type='MESSAGE_UPDATE')::numeric / NULLIF(COUNT(*) FILTER (WHERE event_type='MESSAGE_CREATE'),0),4) AS edit_rate FROM discord_event_logs;
Valor medido	0,2783 (16.009/57.529); range semanal 0,18 (06-01 bot-inflado) a 0,52 (05-18)
Viz	line semanal com banda
Alvo	0,15-0,30
Alerta	jump > 0,50 (edit-spam) ou drop com spike de msgs (bot flooding)

3.22 Taxa de Deleção de Mensagens (real-time)

Campo	Valor
Definição	MESSAGE_DELETE/MESSAGE_CREATE. Spikes = moderação/spam.
Fonte	discord_event_logs
SQL	SELECT ROUND(COUNT(*) FILTER (WHERE event_type='MESSAGE_DELETE')::numeric / NULLIF(COUNT(*) FILTER (WHERE event_type='MESSAGE_CREATE'),0),4) AS delete_rate FROM discord_event_logs;
Valor medido	0,0063 (362/57.529) — muito baixo/saudável
Viz	daily trend com marcadores
Alvo	< 0,02
Alerta	> 0,05 em um dia

3.23 Thread Creation & Archive Health (snapshot) ⚠️ C3

Campo	Valor
Definição	Threads spawned + % arquivadas. Snapshot-only.
Fonte	message_threads
SQL	SELECT COUNT(*) AS total_threads, COUNT(*) FILTER (WHERE archived) AS archived_threads, COUNT(*) FILTER (WHERE NOT archived) AS active_threads, ROUND(100.0*COUNT(*) FILTER (WHERE archived)/COUNT(*),2) AS pct_archived, ROUND(AVG(auto_archive_duration)) AS avg_auto_archive_min FROM message_threads;
Valor medido	251 threads, 248 arquivadas (98,8%), 3 ativas (~1 thread/13.000 msgs)
Viz	stat cards
Alvo	crescer criação de threads
Alerta	n/a (volume baixo)

3.24 RSVP cancel rate (eventos)

Campo	Valor
Definição	USER_REMOVE/USER_ADD. Interesse retirado.
Fonte	discord_event_logs
SQL	SELECT ROUND(100.0 * COUNT(*) FILTER (WHERE event_type='GUILD_SCHEDULED_EVENT_USER_REMOVE') / NULLIF(COUNT(*) FILTER (WHERE event_type='GUILD_SCHEDULED_EVENT_USER_ADD'),0),1) AS cancel_rate_pct FROM discord_event_logs WHERE event_type IN ('GUILD_SCHEDULED_EVENT_USER_ADD','GUILD_SCHEDULED_EVENT_USER_REMOVE');
Valor medido	30,0% (12/40)
Viz	gauge
Alvo	< 20%
Alerta	> 35%

3.25 Repeat RSVP rate (cross-event loyalty)

Campo	Valor
Definição	Share de RSVPers que deram RSVP a >1 evento.
Fonte	discord_event_logs
SQL	SELECT ROUND(100.0 * COUNT(*) FILTER (WHERE n_events >= 2) / NULLIF(COUNT(*),0),1) AS repeat_rate_pct FROM (SELECT user_id, COUNT(DISTINCT COALESCE(payload #>> '{guild_scheduled_event_id}', payload #>> '{d,guild_scheduled_event_id}')) AS n_events FROM discord_event_logs WHERE event_type='GUILD_SCHEDULED_EVENT_USER_ADD' AND user_id IS NOT NULL GROUP BY user_id) sub;
Valor medido	12,1% (4 de 33)
Viz	donut (1 evento vs 2+)
Alvo	> 30%
Alerta	< 10%

3.26 Taxa de 'one-and-done' por coorte

Campo	Valor
Definição	% da coorte ativa em exatamente 1 semana. ⚠️ filtrar coortes imaturas na exibição.
Fonte	messages ∪ voice_messages
SQL	WITH activity AS (SELECT external_identity_id, sent_at AS ts FROM messages WHERE sent_at >= '2019-01-01' UNION ALL SELECT external_identity_id, occurred_at FROM voice_messages WHERE occurred_at >= '2019-01-01'), weekly AS (SELECT DISTINCT external_identity_id, date_trunc('week',(ts AT TIME ZONE 'America/Sao_Paulo'))::date AS act_week FROM activity), first_week AS (SELECT external_identity_id, MIN(act_week) AS cohort_week FROM weekly GROUP BY 1), spans AS (SELECT w.external_identity_id, fw.cohort_week, COUNT(*) AS active_weeks FROM weekly w JOIN first_week fw USING(external_identity_id) GROUP BY 1,2) SELECT cohort_week, COUNT(*) AS size, ROUND(100.0*COUNT(*) FILTER (WHERE active_weeks=1)/COUNT(*),1) AS pct_one_and_done, ROUND(AVG(active_weeks),2) AS avg_active_weeks FROM spans WHERE cohort_week >= (date_trunc('week', now() AT TIME ZONE 'America/Sao_Paulo')::date - INTERVAL '12 weeks') GROUP BY 1 ORDER BY 1;
Valor medido	Maduras: 2026-03-23=63,4%, 04-27=64,0%, 05-04=56,6%, 04-06=42,4% (melhor); faixa 57-65%
Viz	bar por coorte (excluir/distinguir imaturas)
Alvo	< 50%
Alerta	> 70% em coorte madura

3.27 Vida média da coorte (avg semanas ativas)

Campo	Valor
Definição	Média de semanas distintas ativas (proxy de lifetime).
Fonte	messages ∪ voice_messages
SQL	WITH activity AS (SELECT external_identity_id, sent_at AS ts FROM messages WHERE sent_at >= '2019-01-01' UNION ALL SELECT external_identity_id, occurred_at FROM voice_messages WHERE occurred_at >= '2019-01-01'), weekly AS (SELECT DISTINCT external_identity_id, date_trunc('week',(ts AT TIME ZONE 'America/Sao_Paulo'))::date AS act_week FROM activity), first_week AS (SELECT external_identity_id, MIN(act_week) AS cohort_week FROM weekly GROUP BY 1), spans AS (SELECT w.external_identity_id, fw.cohort_week, COUNT(*) AS active_weeks FROM weekly w JOIN first_week fw USING(external_identity_id) GROUP BY 1,2) SELECT cohort_week, ROUND(AVG(active_weeks),2) AS avg_active_weeks, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY active_weeks) AS median_active_weeks FROM spans WHERE cohort_week >= (date_trunc('week', now() AT TIME ZONE 'America/Sao_Paulo')::date - INTERVAL '12 weeks') GROUP BY 1 ORDER BY 1;
Valor medido	Maduras ~1,7-1,9 semanas (04-06=1,92, 05-04=1,77); mediana = 1 semana quase sempre
Viz	line por coorte
Alvo	>= 2,5 semanas
Alerta	< 1,4 em coorte madura

3.28 WAU unificado (messages∪voice — âncora)

Campo	Valor
Definição	Membros distintos ativos (messages∪voice)/semana, 13 semanas. ⚠️ borda esquerda e semana atual parciais.
Fonte	messages ∪ voice_messages
SQL	WITH activity AS (SELECT external_identity_id, sent_at AS ts FROM messages WHERE sent_at >= '2019-01-01' UNION ALL SELECT external_identity_id, occurred_at FROM voice_messages WHERE occurred_at >= '2019-01-01') SELECT date_trunc('week', ts AT TIME ZONE 'America/Sao_Paulo')::date AS wk, COUNT(DISTINCT external_identity_id) AS wau FROM activity WHERE ts >= (now() - INTERVAL '13 weeks') GROUP BY 1 ORDER BY 1;
Valor medido	Picos: 2026-03-23=558 (evento), 05-18=430, 04-27=417; vale: 06-08=190, 06-15=186 (PARCIAL)
Viz	line chart, semana atual hachurada
Alvo	n/a (tendência)
Alerta	queda sustentada > 30% em semanas completas

3.29 Legacy meetings table population (data-integrity guard) ❌

Campo	Valor
Definição	Row counts das tabelas canônicas de meetings. Se 0, todo KPI legado retorna falso-zero.
Fonte	meetings, meeting_participants, meeting_types, seasons
SQL	SELECT (SELECT COUNT(*) FROM meetings) AS meetings, (SELECT COUNT(*) FROM meeting_participants) AS participants, (SELECT COUNT(*) FROM meeting_types) AS meeting_types, (SELECT COALESCE(SUM(meeting_count),0) FROM seasons) AS season_meeting_count;
Valor medido	meetings=0, participants=0, meeting_types=0, season_meeting_count=0 — TODAS VAZIAS
Viz	status tiles (vermelho se 0)
Alvo	> 0 para todas
Alerta	meetings=0 com RSVPs fluindo (FIRING)

---

4. CAMADA BUSINESS

4.1 Join-Request Approval Rate (Discord onboarding)

Campo	Valor
Definição	Share de join requests resolvidos que foram APPROVED. Permeabilidade do gate.
Fonte	discord_event_logs
SQL	SELECT COUNT(*) FILTER (WHERE event_type='GUILD_JOIN_REQUEST_UPDATE' AND payload->>'status'='APPROVED') AS approved, COUNT(*) FILTER (WHERE event_type='GUILD_JOIN_REQUEST_DELETE') AS deleted, ROUND(COUNT(*) FILTER (WHERE event_type='GUILD_JOIN_REQUEST_UPDATE' AND payload->>'status'='APPROVED')::numeric / NULLIF(COUNT(*) FILTER (WHERE event_type='GUILD_JOIN_REQUEST_UPDATE' AND payload->>'status'='APPROVED') + COUNT(*) FILTER (WHERE event_type='GUILD_JOIN_REQUEST_DELETE'),0),4) AS approval_rate FROM discord_event_logs;
Valor medido	0,9648 (658 approved / (658+24 deleted)). Nota: 5 updates null-status excluídos.
Viz	funil + gauge
Alvo	> 0,90
Alerta	< 0,80 rolling week (possível raid/misconfig)

4.2 Membership-Screening Gate Pass Rate

Campo	Valor
Definição	Share de GUILD_MEMBER_ADD com pending=true (screening enforçado).
Fonte	discord_event_logs
SQL	SELECT COUNT(*) AS add_events, COUNT(*) FILTER (WHERE (payload->>'pending')::boolean IS TRUE) AS pending_on_join, COUNT(*) FILTER (WHERE (payload#>>'{user,bot}')::boolean IS TRUE) AS bot_adds, COUNT(DISTINCT payload#>>'{user,id}') AS distinct_joiners FROM discord_event_logs WHERE event_type='GUILD_MEMBER_ADD';
Valor medido	0,994 (694/698 pending); 0 bot adds; 692 joiners distintos. user_id da coluna é NULL — usar payload.
Viz	single stat + sparkline
Alvo	informativo (~1.0 confirma screening)
Alerta	queda súbita de pending share (screening desligado)

4.3 Booster (Premium) Penetration

Campo	Valor
Definição	Share de membros ativos que são boosters.
Fonte	discord_members
SQL	SELECT COUNT(*) AS active_boosters, ROUND(100.0*COUNT(*)/NULLIF((SELECT COUNT(*) FROM discord_members WHERE left_at IS NULL AND NOT is_bot),0),3) AS pct_of_active FROM discord_members WHERE premium_since IS NOT NULL AND left_at IS NULL AND NOT is_bot;
Valor medido	13 boosters = 0,052% dos ativos
Viz	stat card + trend
Alvo	informativo
Alerta	abaixo do threshold de tier de boost

4.4 Variação líquida de participantes WhatsApp

Campo	Valor
Definição	Adds − removes/dia (payload jsonb, exclui action=promote).
Fonte	whatsapp_event_logs
SQL	SELECT (received_at AT TIME ZONE 'America/Sao_Paulo')::date AS dia, SUM(CASE WHEN payload->>'action'='add' THEN jsonb_array_length(payload->'participants') WHEN payload->>'action'='remove' THEN -jsonb_array_length(payload->'participants') ELSE 0 END) AS net FROM whatsapp_event_logs WHERE type='group-participants.update' GROUP BY 1 ORDER BY 1;
Valor medido	06-18:+177 (pico onboarding), liquido total +202 (278 adds / 76 removes)
Viz	diverging bar + linha net
Alvo	net positivo semanal
Alerta	net negativo em 2+ dias completos consecutivos

4.5 Donator conversion rate ❌ legado/importado

Campo	Valor
Definição	Share de users is_donator (geral e entre logados).
Fonte	users
SQL	SELECT COUNT(*) AS users, COUNT(*) FILTER (WHERE is_donator) AS donators, ROUND(100.0*COUNT(*) FILTER (WHERE is_donator)/NULLIF(COUNT(*),0),3) AS conv_all_pct, ROUND(100.0*COUNT(*) FILTER (WHERE first_login_at IS NOT NULL AND is_donator)/NULLIF(COUNT(*) FILTER (WHERE first_login_at IS NOT NULL),0),2) AS conv_active_pct FROM users;
Valor medido	0,090% (41/45.723); 0 dos 43 logados são donators (sets disjuntos — is_donator importado)
Viz	funnel (users→logados→donators)
Alvo	> 1%
Alerta	< 0,5%

4.6 Avg RSVPs per event (interest depth)

Campo	Valor
Definição	Média de RSVP adds por evento distinto (proxy de presença média).
Fonte	discord_event_logs
SQL	SELECT ROUND(COUNT(*)::numeric / NULLIF(COUNT(DISTINCT COALESCE(payload #>> '{guild_scheduled_event_id}', payload #>> '{d,guild_scheduled_event_id}')),0),2) AS avg_rsvp_per_event FROM discord_event_logs WHERE event_type='GUILD_SCHEDULED_EVENT_USER_ADD';
Valor medido	13,33/evento; bimodal: Reunião Semanal=18, AULÃO=19, Reunião Delas=3
Viz	bar por evento + linha de média
Alvo	>= 15
Alerta	< 5

4.7 Per-event RSVP funnel (adds vs removes)

Campo	Valor
Definição	Adds/removes por evento (presença por meeting).
Fonte	discord_event_logs
SQL	SELECT COALESCE(payload #>> '{guild_scheduled_event_id}', payload #>> '{d,guild_scheduled_event_id}') AS event_id, COUNT(*) FILTER (WHERE event_type='GUILD_SCHEDULED_EVENT_USER_ADD') AS adds, COUNT(*) FILTER (WHERE event_type='GUILD_SCHEDULED_EVENT_USER_REMOVE') AS removes FROM discord_event_logs WHERE event_type IN ('GUILD_SCHEDULED_EVENT_USER_ADD','GUILD_SCHEDULED_EVENT_USER_REMOVE') GROUP BY 1 ORDER BY adds DESC;
Valor medido	AULÃO: 19/4 (21%); Reunião Semanal #4: 18/8 (44% cancel!); Reunião Delas: 3/0
Viz	grouped bar por evento
Alvo	cancel por evento < 20%
Alerta	qualquer evento > 40% cancel

4.8 Coins in circulation ❌ BLOQUEADO — sem dados

Campo	Valor
Definição	Soma de balances. wallets=0, characters_wallet=0.
Fonte	wallets, characters_wallet (VAZIAS)
SQL	SELECT (SELECT COALESCE(SUM(balance),0) FROM wallets) AS wallets_total, (SELECT COALESCE(SUM(balance),0) FROM characters_wallet) AS characters_wallet_total;
Valor medido	0 / sem ledger
Viz	empty-state card
Alvo	base não-zero crescente (pós-launch)
Alerta	0 wallets (FIRING)

4.9 Transaction volume by type ❌ BLOQUEADO — sem dados

Campo	Valor
Definição	Count + soma de transações por tipo. transactions=0.
Fonte	transactions (VAZIA)
SQL	SELECT type, COUNT(*) AS tx, SUM(amount) AS total_amount FROM transactions GROUP BY type ORDER BY tx DESC;
Valor medido	SEM DADOS (0 linhas)
Viz	empty-state card
Alvo	não-zero pós-launch
Alerta	0 transações em 7 dias (FIRING)

4.10 Breaches de SLA de apelação ❌ BLOQUEADO — sem dados

Campo	Valor
Definição	% de apelações resolvidas após sla_deadline. moderation_appeals=0.
Fonte	moderation_appeals (VAZIA)
SQL	SELECT count(*) total, count(*) FILTER (WHERE (resolved_at IS NOT NULL AND resolved_at > sla_deadline) OR (resolved_at IS NULL AND sla_deadline < now())) breaches FROM moderation_appeals;
Valor medido	SEM DADOS (0 linhas)
Viz	gauge placeholder
Alvo	< 5% breach
Alerta	> 10% breach

4.11 Taxa de apelação e reversão via appeals ❌ BLOQUEADO — sem dados

Campo	Valor
Definição	Apelações por ação + % aprovadas. appeals=0, actions=0.
Fonte	moderation_appeals + moderation_actions (VAZIAS)
SQL	SELECT (SELECT count(*) FROM moderation_appeals)::numeric / NULLIF((SELECT count(*) FROM moderation_actions),0) appeal_rate;
Valor medido	SEM DADOS
Viz	dois big numbers placeholder
Alvo	overturn < 15%
Alerta	overturn alto

---

5. CAMADA CONTENT

5.1 Taxa de reação (texto, janela válida) ⚠️ C1

Campo	Valor
Definição	% de msgs com reactions_count>0. MEDIDA na janela válida 16/02-18/03/2026.
Fonte	messages.reactions_count
SQL	WITH base AS (SELECT * FROM messages WHERE COALESCE(sent_at,created_at) >= '2026-02-16' AND COALESCE(sent_at,created_at) < '2026-03-18') SELECT ROUND(100.0*COUNT(*) FILTER (WHERE reactions_count>0)/COUNT(*),2) AS pct_reacted FROM base;
Valor medido	0,05% na janela válida; lifetime 2,19%
Viz	line com flag 'metadata indisponível após 18/03'
Alvo	> 1% (restaurar captura primeiro)
Alerta	ZERO onde metadata deveria existir (ingestão quebrada)

5.2 Taxa de resposta (reply rate, janela válida) ⚠️ C1

Campo	Valor
Definição	% de msgs que são replies (FK OU kind='reply'). Janela válida 16/02-18/03.
Fonte	messages.reply_to_message_id + kind
SQL	WITH base AS (SELECT * FROM messages WHERE COALESCE(sent_at,created_at) >= '2026-02-16' AND COALESCE(sent_at,created_at) < '2026-03-18') SELECT ROUND(100.0*COUNT(*) FILTER (WHERE reply_to_message_id IS NOT NULL OR kind='reply')/COUNT(*),2) AS pct_reply FROM base;
Valor medido	0,74% (combinado) na janela válida
Viz	line com flag metadata
Alvo	> 3%
Alerta	0% pós-18/03 = artefato

5.3 Taxa de edição (texto, janela válida) ⚠️ C1

Campo	Valor
Definição	% de msgs com edited_at não-nulo. Janela válida 16/02-18/03.
Fonte	messages.edited_at
SQL	WITH base AS (SELECT * FROM messages WHERE COALESCE(sent_at,created_at) >= '2026-02-16' AND COALESCE(sent_at,created_at) < '2026-03-18') SELECT ROUND(100.0*COUNT(*) FILTER (WHERE edited_at IS NOT NULL)/COUNT(*),2) AS pct_edited FROM base;
Valor medido	0,03% na janela válida; lifetime 0,91%
Viz	small number + flag
Alvo	informativo
Alerta	0% pós-18/03 = ingestão quebrada

5.4 Atividade de menções ⚠️ C1

Campo	Valor
Definição	Menções capturadas (message_mentions). Tabela parou em 2026-03-23.
Fonte	message_mentions + messages
SQL	SELECT (SELECT COUNT(*) FROM message_mentions) AS total_mention_rows, (SELECT COUNT(DISTINCT mentioned_identity_id) FROM message_mentions) AS distinct_mentioned, (SELECT MAX(COALESCE(m.sent_at,m.created_at)) FROM message_mentions mm JOIN messages m ON m.id=mm.message_id) AS last_mention_at;
Valor medido	170.847 menções históricas, 21.298 identidades distintas; última 2026-03-23
Viz	rede de menções / counter com data da última captura
Alvo	restaurar captura
Alerta	nenhuma nova linha em 7 dias

5.5 Heatmap hora x dia-da-semana (texto)

Campo	Valor
Definição	Distribuição de msgs por (DOW, hora) BRT, 90 dias. DOW: 0=domingo.
Fonte	messages
SQL	WITH base AS (SELECT COALESCE(sent_at,created_at) AT TIME ZONE 'America/Sao_Paulo' AS ts FROM messages WHERE COALESCE(sent_at,created_at) >= '2026-03-20' AND COALESCE(sent_at,created_at) < '2026-06-20') SELECT EXTRACT(DOW FROM ts)::int AS dow, EXTRACT(HOUR FROM ts)::int AS hour, COUNT(*) AS n FROM base GROUP BY 1,2 ORDER BY n DESC LIMIT 10;
Valor medido	Pico SEGUNDA 22h (9.252) e 23h (5.077) = reunião semanal; secundário QUARTA 19-20h
Viz	heatmap 7x24
Alvo	informativo (timing)
Alerta	n/a

5.6 Distribuição por kind / source_kind (texto) ⚠️ C9

Campo	Valor
Definição	Mix de tipos (kind) e origem (source_kind), ~6 meses. NULL domina + webhook infla.
Fonte	messages.kind + source_kind
SQL	SELECT kind, source_kind, COUNT(*) AS n, ROUND(100.0*COUNT(*)/SUM(COUNT(*)) OVER (),2) AS pct FROM messages WHERE COALESCE(sent_at,created_at) >= '2026-01-01' GROUP BY kind, source_kind ORDER BY n DESC LIMIT 10;
Valor medido	NULL/NULL = 83,37%; default+webhook 14,18%; default+bot 1,57%; default+user só 0,31%
Viz	stacked bar com flag '83% NULL'
Alvo	reduzir NULL share pós-correção
Alerta	NULL share > 80%

5.7 Atividade horária WhatsApp (pico de horário)

Campo	Valor
Definição	Distribuição de upserts por hora (display tz).
Fonte	whatsapp_event_logs
SQL	SELECT EXTRACT(HOUR FROM received_at AT TIME ZONE 'America/Sao_Paulo')::int AS hora, COUNT(*) AS upserts FROM whatsapp_event_logs WHERE type='messages.upsert' GROUP BY 1 ORDER BY 2 DESC;
Valor medido	Picos: 14h(860), 21h(805), 11h(733). Dois picos: comercial (10-14h) e noite (21h).
Viz	heatmap hora-x-dia / bar por hora
Alvo	n/a
Alerta	n/a

5.8 Taxa de reação WhatsApp (reactions/upserts)

Campo	Valor
Definição	reactions/upserts (proxy de ressonância). Denominadores de tipos diferentes — válido para tendência.
Fonte	whatsapp_event_logs
SQL	SELECT ROUND(100.0*(SELECT COUNT(*) FROM whatsapp_event_logs WHERE type='messages.reaction')/NULLIF((SELECT COUNT(*) FROM whatsapp_event_logs WHERE type='messages.upsert'),0),2) AS reaction_rate_pct;
Valor medido	12,63% (1148/9087); tendência de alta (8,3%→14,1%)
Viz	line com banda de meta
Alvo	>= 12%
Alerta	< 8% por dia completo

5.9 Reactions per Message (real-time) ⚠️ C9

Campo	Valor
Definição	MESSAGE_REACTION_ADD/MESSAGE_CREATE. ⚠️ denominador inflado por bot channels.
Fonte	discord_event_logs
SQL	SELECT ROUND(COUNT(*) FILTER (WHERE event_type='MESSAGE_REACTION_ADD')::numeric / NULLIF(COUNT(*) FILTER (WHERE event_type='MESSAGE_CREATE'),0),4) AS reactions_per_msg FROM discord_event_logs;
Valor medido	0,1155 (6.644/57.529, raw); human-only ~0,13-0,15
Viz	weekly trend (bot channels excluídos)
Alvo	> 0,12
Alerta	< 0,08 por semana completa

5.10 Reaction Rate by Message Kind (texto)

Campo	Valor
Definição	Quais tipos de mensagem atraem reações (boost vs reply vs default).
Fonte	messages.kind, reactions_total
SQL	SELECT m.kind, COUNT(*) AS msgs, ROUND(100.0*COUNT(*) FILTER (WHERE m.reactions_total>0)/COUNT(*),3) AS pct_reacted, ROUND(AVG(m.reactions_total)::numeric,4) AS avg_react FROM messages m WHERE COALESCE(m.sent_at,m.created_at) >= '2019-01-01' GROUP BY m.kind ORDER BY COUNT(*) * -1 LIMIT 10;
Valor medido	boost 15,69% (avg 0,62)
Viz	grouped bar por kind
Alvo	n/a (comparativo)
Alerta	n/a

5.11 Viral Concentration (top 1% reaction share) ⚠️ C3

Campo	Valor
Definição	% das reações capturadas pelo top 1% de msgs mais reagidas.
Fonte	messages.reactions_total
SQL	SELECT ROUND(100.0 * SUM(reactions_total) FILTER (WHERE rnk <= GREATEST(1,(cnt/100))) / SUM(reactions_total),2) AS pct_held_by_top1pct FROM (SELECT reactions_total, ROW_NUMBER() OVER (ORDER BY reactions_total * -1) AS rnk, COUNT(*) OVER () AS cnt FROM messages WHERE reactions_total > 0) t;
Valor medido	24,30% (721 msgs detêm 67.824 de 279.074 reações)
Viz	lorenz / gauge
Alvo	20-30% típico
Alerta	> 50% (excesso de dependência de pinned)

5.12 Viral Content Distribution (reaction buckets) ⚠️ C3

Campo	Valor
Definição	Histograma de msgs por intensidade de reação (0…26+).
Fonte	messages.reactions_total
SQL	SELECT CASE WHEN reactions_total=0 THEN '0' WHEN reactions_total BETWEEN 1 AND 2 THEN '1-2' WHEN reactions_total BETWEEN 3 AND 5 THEN '3-5' WHEN reactions_total BETWEEN 6 AND 10 THEN '6-10' WHEN reactions_total BETWEEN 11 AND 25 THEN '11-25' ELSE '26+' END AS bucket, COUNT(*) AS msgs FROM messages WHERE COALESCE(sent_at,created_at) >= '2019-01-01' GROUP BY 1 ORDER BY MIN(reactions_total);
Valor medido	0:3.111.480
Viz	histograma horizontal (log)
Alvo	n/a
Alerta	trackear 26+ /mês pós-correção

5.13 Top Shared Domains (link virality) ⚠️ C3

Campo	Valor
Definição	Domínios mais compartilhados (de classified links — só 3,1% dos embeds têm source_domain).
Fonte	message_embeds.source_domain
SQL	SELECT source_domain, COUNT(*) AS shares, ROUND(100.0*COUNT(*)/SUM(COUNT(*)) OVER(),2) AS pct FROM message_embeds WHERE source_domain IS NOT NULL AND source_domain <> '' GROUP BY source_domain ORDER BY COUNT(*) * -1 LIMIT 20;
Valor medido	youtube 19,88%
Viz	bar / treemap top 15 (rotular 'de links classificados')
Alvo	trackear dev-domain share
Alerta	n/a

5.14 Link Share Rate (% msgs com embed) ⚠️ C3

Campo	Valor
Definição	Share de msgs com >=1 embed. 0% após mar/2026 = gap de ETL, não real.
Fonte	message_embeds JOIN messages
SQL	SELECT date_trunc('month',(COALESCE(m.sent_at,m.created_at) AT TIME ZONE 'America/Sao_Paulo'))::date AS mo, ROUND(100.0*COUNT(DISTINCT e.message_id)/COUNT(*),3) AS pct_link_share FROM messages m LEFT JOIN message_embeds e ON e.message_id=m.id WHERE COALESCE(m.sent_at,m.created_at) >= '2025-12-01' AND COALESCE(m.sent_at,m.created_at) < '2026-06-21' GROUP BY 1 ORDER BY 1;
Valor medido	Dez-25:20,6%
Viz	line mensal com banda 'ETL gap' Abr-Jun
Alvo	20-30%
Alerta	mês completo = 0% (após corrigir ETL)

5.15 Media Share Mix (attachment content-type) ⚠️ C3

Campo	Valor
Definição	Distribuição de attachments por MIME class. 70% text/* = code/log dumps.
Fonte	message_attachments.content_type
SQL	SELECT split_part(content_type,'/',1) AS media_class, COUNT(*) AS attachments, ROUND(100.0*COUNT(*)/SUM(COUNT(*)) OVER(),2) AS pct, ROUND(AVG(size)/1024.0/1024.0,3) AS avg_mb FROM message_attachments GROUP BY 1 ORDER BY COUNT(*) * -1;
Valor medido	text 70,19% (avg 0,007MB)
Viz	donut + avg-size
Alvo	n/a (perfil)
Alerta	n/a

5.16 Top Emojis (reaction vocabulary) ⚠️ C3

Campo	Valor
Definição	Emojis de reação mais usados (cultura in-group).
Fonte	activity_reactions.emoji_name, count
SQL	SELECT emoji_name, COUNT(*) AS rows, SUM(count) AS total_reactions FROM activity_reactions GROUP BY emoji_name ORDER BY SUM(count) * -1 LIMIT 15;
Valor medido	pepeOK 26.638
Viz	emoji leaderboard / bar
Alvo	n/a (perfil cultural)
Alerta	n/a

5.17 Top Viral Messages (Hall of Fame) ⚠️ C3

Campo	Valor
Definição	Mensagens com mais reactions_total de todos os tempos.
Fonte	messages.reactions_total
SQL	SELECT m.id, (m.sent_at AT TIME ZONE 'America/Sao_Paulo')::date AS day_brt, m.reactions_total, m.reactions_count AS distinct_emojis, LEFT(REGEXP_REPLACE(COALESCE(m.content,''),'\s+',' ','g'),50) AS preview FROM messages m WHERE m.reactions_total > 0 ORDER BY m.reactions_total * -1 LIMIT 10;
Valor medido	#1: 3.089 (2021-03-11 rules); #2: 3.062 (2021-03-11 welcome); #3: 1.258 (2020-06-10). Topo = anúncios pinned.
Viz	tabela ranqueada com preview
Alvo	n/a (leaderboard)
Alerta	n/a

5.18 Distribuição de ações de moderação por tipo (histórico) ❌ legado

Campo	Valor
Definição	Mix de tipos de evento (ban/warn/mute/unban/unmute).
Fonte	moderation_events
SQL	SELECT type, count(*) n, round(count(*)::numeric/sum(count(*)) over()*100,1) pct FROM moderation_events WHERE occurred_at >= '2019-01-01' GROUP BY type ORDER BY n DESC;
Valor medido	ban=406 (85,3%), warn=47 (9,9%), mute=12 (2,5%), unban=9 (1,9%), unmute=2 (0,4%); total 476
Viz	barras horizontais / donut
Alvo	warn/mute idealmente > ban (escalonamento gradual)
Alerta	ban dominando > 80% (falta de degraus)

5.19 Gatilhos de auto-mod por tipo de regra

Campo	Valor
Definição	Quais regras do automod disparam mais (rule_trigger_type).
Fonte	discord_event_logs
SQL	SELECT payload->>'rule_trigger_type' rule_type, count(*) n FROM discord_event_logs WHERE event_type='AUTO_MODERATION_ACTION_EXECUTION' AND created_at >= '2026-05-19' GROUP BY 1 ORDER BY 2 DESC;
Valor medido	rule_trigger_type=3: 34
Viz	barras por tipo de regra
Alvo	n/a (diagnóstico)
Alerta	uma regra dominando = possível ataque

5.20 Badge claim penetration ❌ DORMENTE

Campo	Valor
Definição	Share de characters com >=1 badge. ⚠️ usar COUNT(DISTINCT c.id) no denominador (future-proof).
Fonte	characters, characters_badges, badges
SQL (corrigido)	SELECT COUNT(DISTINCT c.id) AS total_chars, COUNT(DISTINCT cb.character_id) AS chars_with_badge, ROUND(100.0*COUNT(DISTINCT cb.character_id)/NULLIF(COUNT(DISTINCT c.id),0),2) AS penetration_pct FROM characters c LEFT JOIN characters_badges cb ON cb.character_id=c.id;
Valor medido	2,21% (872/39.388); 1 badge único ('2023 Beta Tester', inativo), todos num batch de 21min em 2023-03-14
Viz	gauge + badge leaderboard
Alvo	> 15% com múltiplos badges ativos
Alerta	0 badges ativos (FIRING)

5.21 RSVP by day-of-week (eventos)

Campo	Valor
Definição	Distribuição de RSVP adds por weekday (display tz) — guia de scheduling.
Fonte	discord_event_logs
SQL	SELECT to_char(created_at AT TIME ZONE 'America/Sao_Paulo','ID') AS iso_dow, to_char(created_at AT TIME ZONE 'America/Sao_Paulo','Day') AS day_name, COUNT(*) AS rsvp_add FROM discord_event_logs WHERE event_type='GUILD_SCHEDULED_EVENT_USER_ADD' GROUP BY 1,2 ORDER BY 1;
Valor medido	Mon=18 (45%), Wed=9, Thu=5, Fri=4, Tue=2, Sat=1, Sun=1
Viz	bar horizontal por weekday
Alvo	informativo
Alerta	n/a

---

6. CAMADA CROSS-PLATFORM

6.1 Volume cross-platform: Discord/WhatsApp (janela 7d sobreposta)

Campo	Valor
Definição	Razão Discord (MESSAGE_CREATE) / WhatsApp (messages.upsert) por dia, >= 2026-06-14.
Fonte	discord_event_logs + whatsapp_event_logs
SQL	WITH wa AS (SELECT (received_at AT TIME ZONE 'America/Sao_Paulo')::date dia, COUNT(*) FILTER (WHERE type='messages.upsert') m FROM whatsapp_event_logs WHERE received_at >= '2026-06-14 00:00:00-03' GROUP BY 1), dc AS (SELECT (created_at AT TIME ZONE 'America/Sao_Paulo')::date dia, COUNT(*) FILTER (WHERE event_type='MESSAGE_CREATE') m FROM discord_event_logs WHERE created_at >= '2026-06-14 00:00:00-03' GROUP BY 1) SELECT COALESCE(wa.dia,dc.dia) dia, wa.m wa_msgs, dc.m dc_msgs, ROUND(dc.m::numeric/NULLIF(wa.m,0),1) dc_per_wa FROM wa FULL JOIN dc ON wa.dia=dc.dia ORDER BY 1;
Valor medido	7d total: WhatsApp 9087 vs Discord 8382 (DC/WA=0,92). Após 06-16 WhatsApp domina diário.
Viz	grouped bar (WA vs Discord) por dia
Alvo	n/a (mix de canal)
Alerta	n/a

6.2 Taxa de reação comparada (WhatsApp vs Discord, mesma janela)

Campo	Valor
Definição	Reaction rate de cada plataforma na janela sobreposta.
Fonte	discord_event_logs + whatsapp_event_logs
SQL	WITH wa AS (SELECT (received_at AT TIME ZONE 'America/Sao_Paulo')::date dia, COUNT(*) FILTER (WHERE type='messages.upsert') m, COUNT(*) FILTER (WHERE type='messages.reaction') r FROM whatsapp_event_logs WHERE received_at >= '2026-06-14 00:00:00-03' GROUP BY 1), dc AS (SELECT (created_at AT TIME ZONE 'America/Sao_Paulo')::date dia, COUNT(*) FILTER (WHERE event_type='MESSAGE_CREATE') m, COUNT(*) FILTER (WHERE event_type='MESSAGE_REACTION_ADD') r FROM discord_event_logs WHERE created_at >= '2026-06-14 00:00:00-03' GROUP BY 1) SELECT COALESCE(wa.dia,dc.dia) dia, ROUND(100.0*wa.r/NULLIF(wa.m,0),1) wa_pct, ROUND(100.0*dc.r/NULLIF(dc.m,0),1) dc_pct FROM wa FULL JOIN dc ON wa.dia=dc.dia ORDER BY 1;
Valor medido	Discord tende maior (~12-20%), ambos mesma ordem de grandeza. 06-19: WA 14,1% / DC 20,0%.
Viz	dual line chart
Alvo	n/a (benchmark)
Alerta	n/a

6.3 Distribuição de canal de aquisição (first-touch text vs voice)

Campo	Valor
Definição	Classifica 1ª atividade: text-only/voice-only/text-first/voice-first. ~70% entram por voz.
Fonte	messages ∪ voice_messages
SQL	WITH msg AS (SELECT external_identity_id, MIN(sent_at) AS first_msg FROM messages WHERE sent_at >= '2019-01-01' GROUP BY 1), voi AS (SELECT external_identity_id, MIN(occurred_at) AS first_voi FROM voice_messages WHERE occurred_at >= '2019-01-01' GROUP BY 1), combined AS (SELECT m.first_msg, v.first_voi FROM msg m FULL OUTER JOIN voi v USING(external_identity_id)) SELECT CASE WHEN first_msg IS NULL THEN 'voice_only' WHEN first_voi IS NULL THEN 'text_only' WHEN first_msg<=first_voi THEN 'text_first' ELSE 'voice_first' END AS first_touch, COUNT(*) AS members FROM combined GROUP BY 1 ORDER BY 2 DESC;
Valor medido	voice_only 10.732
Viz	donut / stacked bar
Alvo	n/a
Alerta	n/a

6.4 Retenção W1 por canal de primeira atividade (text vs voice)

Campo	Valor
Definição	Compara retenção W1 entre text-first e voice-first (coortes maduras).
Fonte	messages ∪ voice_messages
SQL	(ver SQL completo do niche cohort-retention KPI 'Retenção W1 por canal'; FULL OUTER JOIN msg×voi, tie-break text, janela BETWEEN now-12w AND now-2w) ... SELECT channel, COUNT(*) AS cohort_size, ROUND(100.0*AVG(r1),1) AS w1_retention FROM ret GROUP BY 1 ORDER BY 3 DESC;
Valor medido	text-first 18,9% (n=243); voice-first 16,3% (n=698). Voz domina volume, texto retém marginalmente mais.
Viz	grouped bar
Alvo	n/a (diagnóstico)
Alerta	gap > 15 p.p.

6.5 Voice-only vs also-text member split

Campo	Valor
Definição	Entre usuários de voz, share que NUNCA enviou texto (lurkers silenciosos).
Fonte	voice_messages + messages
SQL	WITH voice_users AS (SELECT DISTINCT external_identity_id FROM voice_messages WHERE occurred_at >= '2020-01-01'), text_users AS (SELECT DISTINCT external_identity_id FROM messages WHERE COALESCE(sent_at, created_at) >= '2019-01-01') SELECT (SELECT COUNT(*) FROM voice_users) AS voice_users_total, (SELECT COUNT(*) FROM voice_users v WHERE EXISTS (SELECT 1 FROM text_users t WHERE t.external_identity_id=v.external_identity_id)) AS voice_and_text, (SELECT COUNT(*) FROM voice_users v WHERE NOT EXISTS (SELECT 1 FROM text_users t WHERE t.external_identity_id=v.external_identity_id)) AS voice_only;
Valor medido	19.896 usuários de voz: 9.429 (47,4%) também texto; 10.467 (52,6%) voice-ONLY
Viz	donut chart
Alvo	converter voice-only em participantes de texto
Alerta	n/a (segmento)

6.6 Voice retention (multi-month return rate)

Campo	Valor
Definição	Share de usuários de voz ativos em >=2 meses distintos (hábito recorrente).
Fonte	voice_messages
SQL	WITH user_months AS (SELECT external_identity_id, COUNT(DISTINCT date_trunc('month', occurred_at AT TIME ZONE 'America/Sao_Paulo')) AS active_months FROM voice_messages WHERE occurred_at >= '2020-01-01' GROUP BY 1) SELECT COUNT(*) AS total, COUNT(*) FILTER (WHERE active_months>=2) AS multi_month, COUNT(*) FILTER (WHERE active_months>=6) AS six_plus, ROUND((COUNT(*) FILTER (WHERE active_months>=2)::numeric / COUNT(*)),3) AS multi_month_rate FROM user_months;
Valor medido	0,501 (9.963/19.896 retornam em 2+ meses); 1.961 long-haulers 6+ meses (9,9%)
Viz	single stat + cohort line
Alvo	>= 0,50
Alerta	< 0,40

6.7 Month-over-month voice retention ⚠️ definição de "ativo" inconsistente

Campo	Valor
Definição	Dos ativos no mês M, quantos também ativos em M-1. ⚠️ "ativo" = qualquer linha (pós-06-06 mute-only infla junho).
Fonte	voice_messages
SQL	WITH monthly AS (SELECT DISTINCT external_identity_id, date_trunc('month', occurred_at AT TIME ZONE 'America/Sao_Paulo')::date AS mon FROM voice_messages WHERE occurred_at >= '2025-06-01') SELECT mon, COUNT(*) AS active_users, COUNT(*) FILTER (WHERE EXISTS (SELECT 1 FROM monthly p WHERE p.external_identity_id=m.external_identity_id AND p.mon = m.mon - interval '1 month')) AS retained_from_prev FROM monthly m GROUP BY mon ORDER BY mon;
Valor medido	2026-03=951/190 (20%), 04=816/296 (36%), 05=920/290 (32%), 06=425/201 (junho PARCIAL+poluído por mute-only, não comparável)
Viz	line (active vs retained) 12 meses
Alvo	MoM >= 30%
Alerta	mês completo com MoM < 20%

6.8 Curva de retenção por coorte (heatmap W0..W8) ⚠️ renderizar células futuras como cinza

Campo	Valor
Definição	Triângulo de retenção das ~12 coortes; células de semanas não-decorridas VAZIAS (não 0%).
Fonte	messages ∪ voice_messages
SQL	(ver SQL completo do niche cohort; pw com week_n; GROUP BY cohort_week com FILTER week_n=1/2/4/8) ... SELECT cohort_week, COUNT(DISTINCT external_identity_id) FILTER (WHERE week_n=0) AS size, ROUND(100.0*COUNT(DISTINCT external_identity_id) FILTER (WHERE week_n=1)/NULLIF(COUNT(DISTINCT external_identity_id) FILTER (WHERE week_n=0),0),1) AS w1, ... w2, w4, w8 FROM pw WHERE cohort_week >= (date_trunc('week', now() AT TIME ZONE 'America/Sao_Paulo')::date - INTERVAL '12 weeks') GROUP BY 1 ORDER BY 1;
Valor medido	2026-04-06 (n=59): W1 27,1/W2 15,3/W4 6,8/W8 1,7; 2026-05-04 (n=83): W1 30,1/W2 16,9/W4 13,3
Viz	cohort heatmap (triângulo), células futuras cinza
Alvo	W4 >= 12% em todas as coortes
Alerta	W1 < 12% em coorte madura

6.9 Top voice channels by hours (concentração) ⚠️ exclui 53,5% das horas (IDs numéricos)

Campo	Valor
Definição	Voice-hours/sessões/users/avg por channel_name (exclui IDs numéricos não-resolvidos).
Fonte	voice_messages
SQL	... sessions AS (SELECT channel_name, external_identity_id, EXTRACT(EPOCH FROM (next_occurred - occurred_at))/3600.0 AS hours FROM ordered WHERE state='joined' AND next_state='left' AND ... <= interval '12 hours') SELECT channel_name, COUNT(*) AS sessions, COUNT(DISTINCT external_identity_id) AS users, ROUND(SUM(hours)::numeric,1) AS voice_hours, ROUND((AVG(hours)*60)::numeric,1) AS avg_min FROM sessions WHERE channel_name !~ '^[0-9]+$' GROUP BY 1 ORDER BY voice_hours DESC LIMIT 10;
Valor medido	🗣 Conversando 19.321h; ✅Reunião Semanal 10.745h (15min, alta rotatividade); 🏢 Trabalho 5.700h (82min). ⚠️ Exclui 4.700 canais de ID numérico = 58.562h (53,5%); ranking é HISTÓRICO.
Viz	bar horizontal top 10 + tabela avg-min
Alvo	n/a (monitor)
Alerta	canal único > 60% das horas semanais (base mal-medida — caveat)

---

7. LAYOUT ASCII DO DASHBOARD

╔══════════════════════════════════════════════════════════════════════════════╗
║  HE4RT DEVELOPERS — ENGAGEMENT COMMAND CENTER          📅 2026-06-20 (PARCIAL) ║
╠══════════════════════════════════════════════════════════════════════════════╣
║  🔴 BANNER DE SAÚDE DE INGESTÃO                                                ║
║  ┌────────────────────────────────────────────────────────────────────────┐  ║
║  │ ⚠ Metadata de texto parou 18/03  ⚠ Voz join/left morta desde 06/06     │  ║
║  │ ⚠ Conteúdo = backfill único 01-05  ⚠ Moderação+Economia VAZIAS         │  ║
║  │ ⚠ Gamificação dormente desde 2023  ⚠ external_identity_id 100% NULL    │  ║
║  └────────────────────────────────────────────────────────────────────────┘  ║
╠═════════════════ NORTH STAR ═════════════════════════════════════════════════╣
║ ┌───────────┐ ┌───────────┐ ┌───────────┐ ┌───────────┐ ┌───────────┐        ║
║ │ MAU texto │ │Voice-hrs/w│ │ Net growth│ │ Live mbrs │ │ WA msgs/d │        ║
║ │   390 ⭐  │ │  503,5h▼  │ │  +313     │ │  25.031   │ │  1.643avg │        ║
║ │ ▁▂▃▄▅▆▇   │ │ │06-06 ✂  │ │ ▇▅▅▆▅     │ │ +308 WoW  │ │ ▃▅▆█▆▂(P) │        ║
║ └───────────┘ └───────────┘ └───────────┘ └───────────┘ └───────────┘        ║
║ ┌───────────┐ ┌───────────┐ ┌───────────┐                                     ║
║ │TYPING→MSG │ │ Retenção  │ │ Eventos/  │   ❌ Casos mod/sem: SEM DADOS        ║
║ │  90,6% 🟢 │ │  W1 21,3% │ │  30d: 3   │   ❌ Season ativa: 0 (FIRING)        ║
║ └───────────┘ └───────────┘ └───────────┘   ❌ Level-ups/sem: 0 (dormente)     ║
╠═══════════ INPUT ═══════════╦═══════════ HEALTH ════════════════════════════╣
║ DAU médio 38,7 │ WAU 165    ║ Stickiness 9,91% ▼gauge  │ Pairing 87,8%       ║
║ Heatmap 7x24 (seg 22h pico) ║ Concentração canal 62,6% ⚠ │ top1% mbrs 34,3%  ║
║ ▓▓▓░░ hora-dia              ║ Early churn 7d 15,4%  │ Drop-in voz 35,4%      ║
║ WA DAU ▃▅▆█▆ │ chats 4-6/d  ║ Churn ratio 0,55 ▏1.0│ XP conc top1% 50,6%❌   ║
║ RSVP 40 │ unique 33         ║ Cancel RSVP 30% │ Reunião#4 44%⚠              ║
╠═══════════ BUSINESS ════════╬═══════════ CONTENT ════════════════════════════╣
║ Approval 96,5% │ Screen 99% ║ Reação(válida) 0,05%⚠ │ Reply 0,74%⚠           ║
║ Boosters 13   │ WA net +202 ║ Top domains: YT20% GH12% TW8%                  ║
║ AULÃO 19/4 │ Sem#4 18/8     ║ Media mix: text70% img13% │ Top emoji pepeOK    ║
║ ❌ Coins: VAZIO │ Tx: VAZIO ║ Hall of Fame: 3.089 (2021-03-11 rules)         ║
║ ❌ SLA appeals: SEM DADOS   ║ Link share 30,5%mar →0%abr-jun (ETL gap)⚠     ║
╠═══════════════════════ CROSS-PLATFORM ═══════════════════════════════════════╣
║ Discord/WA 0,92 (7d)  │ Voice-only 52,6%  │ First-touch: 70% voz             ║
║ Retenção heatmap W0..W8 (células futuras CINZA)  │ Multi-month voz 50,1%      ║
║ ◧ text-first W1 18,9% vs voice-first 16,3%        │ Top voz: Conversando(hist)║
╚══════════════════════════════════════════════════════════════════════════════╝
  Legenda: ⭐ north-star · ⚠ caveat de janela · ❌ sem dados/dormente · ✂ quebra de pipeline · (P) parcial

---

8. CADÊNCIA DE REVISÃO

Frequência	Foco	KPIs / Cards
Diária (manhã)	Pulso operacional + saúde de ingestão	Banner de ingestão (datas das últimas reação/reply/voz join), DAU texto, WA msgs/dia, WA DAU, adds/removes Discord, auto-mod/dia, bans/dia, throughput horário. Sempre hachurar o dia parcial.
Semanal (segunda)	Tendência e retenção	MAU, voice-hours/semana, WAU unificado, net growth semanal, retenção W1, coortes (heatmap), early churn 7d, RSVP semanal, concentração de canais, cancel rate por evento. Excluir semana corrente parcial dos baselines.
Mensal	Estrutura, negócio, decaimento	Power-law (top1%/top10% texto+XP), donator conversion, booster penetration, first-touch/acquisition mix, multi-month voice retention, viralidade lifetime (top domains, Hall of Fame, media mix), distribuição de moderação histórica. Revisão das KPIs bloqueadas (pipeline vazio/dormente).
Ad-hoc / contínuo	Alertas de quebra	Qualquer card com FIRING de "pipeline morto/vazio" (voz join/left=0 em 3d, automod=0 em 3d, link-share mês=0%, season ativa=0, cases=0 com chat ativo).

---

9. ROTEAMENTO DE ALERTAS

Alerta	Severidade	Destinatário / Canal	Ação
Voz join/left = 0 nos últimos 3 dias	🔴 Crítico	#dev-infra + on-call (bot-discord/integration-discord)	Investigar ETL de voz; restaurar emissão joined/left
Metadata texto (reação/reply/edição/menção) sem novas linhas em 7d	🔴 Crítico	#dev-infra	Restaurar ingestão de reactions/replies/edits/mentions + kind/source_kind
Link-share mês completo = 0%	🟠 Alto	#dev-data	Agendar ingestão incremental de embeds/attachments/threads
moderation_cases=0 com chat ativo / season ativa=0 / interactions=0 24h	🟠 Alto	#produto + #moderacao	Confirmar deploy do pipeline; sinalizar como gap, não zero
Churn ratio > 1,0 em dia completo OU spike automod/bans > 3x média 7d	🟠 Alto	#moderacao + #community-leads	Verificar raid/ataque; correlacionar GUILD_BAN_ADD
Early churn 7d > 20% / one-and-done > 70% coorte madura	🟡 Médio	#community-leads	Iniciativa de onboarding (janela crítica W0→W1)
Cancel rate de evento > 40% / approval rate < 0,80	🟡 Médio	#eventos	A/B test de timing de lembretes; checar screening
Stickiness < 8% / DAU médio < 25 / MAU < 300 por 2 semanas	🟡 Médio	#produto	Rituais/automações de retorno diário
live_active_members cai vs dia anterior (sustentado)	🟡 Médio	#community-leads	Investigar churn líquido

Roteamento técnico: alertas de ingestão (🔴) devem disparar via webhook do Grafana/Metabase → Discord #dev-infra E criar issue automática em he4rt/he4rt-bot-api com label type:fix + mod:bot-discord (ou mod:integration-discord). Alertas de produto/comunidade (🟡) via digest semanal.

---

10. RECOMENDAÇÃO DE TOOLING

Arquitetura recomendada: Postgres (read replica) → Metabase, com Filament widgets no panel-admin para os cards executivos de topo.

Camada	Ferramenta	Justificativa
Fonte	Postgres (read replica de prod_he4rtdevs)	Todas as 68 KPIs já têm SQL verificado contra este banco. Usar replica para não competir com a app.
Exploração & dashboards analíticos	Metabase	SQL nativo, cohort heatmaps, agendamento de alertas por email/Discord, modelo de permissões. Ideal para os ~50 KPIs detalhados, heatmaps de coorte e Pareto/Lorenz. Cada SQL deste doc vira uma "question"; agrupar por camada em coleções (North Star / Input / Health / Business / Content / Cross-platform).
Alertas de série temporal / pipeline health	Grafana (opcional, se já houver stack de observabilidade)	Melhor para os alertas de "pipeline morto" (voz=0, automod=0, metadata sem novas linhas) com webhooks para #dev-infra. Banner de saúde de ingestão como painel dedicado.
Cards executivos no produto	Filament v5 widgets no módulo panel-admin	Para os 8-10 cards de topo (MAU, voice-hours, net growth, live members, retenção W1) visíveis a leads dentro do /admin. Seguir convenção: widgets em panel-admin, lógica de domínio (queries) em Actions dos módulos de domínio, NUNCA SQL cru no widget. Usar database-query read-only e config('app.display_timezone') para datas.

Regras de implementação obrigatórias (transversais a qualquer ferramenta):

1. Sempre AT TIME ZONE 'America/Sao_Paulo' único (nunca duplo) — e aplicá-lo TAMBÉM no WHERE quando o bucketing for local (corrigir o bug de fronteira de DAU/Stickiness).
2. Filtrar COALESCE(sent_at,created_at) >= '2019-01-01' em todo trend de messages.
3. Marca d'água de validade em todo card afetado por C1-C3 (metadata/voz/conteúdo) — renderizar período de corte visualmente.
4. Hachurar dia/semana parciais (C7) e nunca incluí-los em médias/alertas.
5. Banner de saúde de ingestão no topo, dirigido pelas datas das últimas reação/reply/edição/menção capturadas e por joined-events de voz nos últimos 3 dias.
6. Ancorar engajamento em "chat orgânico" (exclui Trabalho/Working + Reunião Semanal) ao lado dos agregados, já que bots não são filtráveis hoje (C9).
7. Remediação prioritária de dados: popular discord_members.external_identity_id (ponte discord_user_id → external_identities) — desbloqueia coortes joined_at→retenção e exclusão de bots em todo o dashboard.

dashboard.sql

-- =====================================================================
-- He4rt Developers — Pack de queries de engajamento VÁLIDAS
-- Gerado 2026-06-20 a partir da investigação multi-agente (verificadas).
-- Inclui APENAS KPIs com pipeline saudável e SQL que sobreviveu à verificação
-- adversarial (sql_correct=true OU com correção aplicada).
-- Excluídos: moderação/economia (tabelas vazias), gamificação (dormente 2023),
-- e métricas de qualidade de texto/conteúdo fora da janela válida.
-- TZ de exibição: America/Sao_Paulo | hoje: 2026-06-20
-- =====================================================================


-- #####################################################################
-- NICHO: Engajamento de Texto (Discord messages)
-- #####################################################################

-- [1] Membros Ativos Mensais (MAU) - texto  (north_star)
-- Definição: Contagem de external_identity_id distintos que enviaram >=1 mensagem nos ultimos 30 dias completos (21/05-19/06, exclui dia parcial de hoje). North-star do engajamento de texto: mede alcance real da conversa.
-- Valor medido: MAU(30d) = 391 membros distintos
-- ✔ Correção verificada: MAU(30d) = 390 (not 391)
-- Viz: big number + sparkline mensal  | Alvo: >= 400  | Alerta: < 300 por 2 semanas consecutivas
WITH base AS (SELECT external_identity_id, COALESCE(sent_at,created_at) AT TIME ZONE 'America/Sao_Paulo' AS local_ts FROM messages WHERE COALESCE(sent_at,created_at) >= '2019-01-01') SELECT COUNT(DISTINCT external_identity_id) AS mau FROM base WHERE local_ts >= '2026-05-21' AND local_ts < '2026-06-20';

-- [2] DAU medio / WAU - texto  (input)
-- Definição: DAU medio dos ultimos 30 dias completos e WAU (semana completa 13-19/06). DAU calculado por dia em America/Sao_Paulo e depois media. Dia de hoje (parcial) EXCLUIDO.
-- Valor medido: DAU medio(30d) = 37,6; WAU(13-19/06) = 165; faixa DAU diaria observada 17-84 (hoje parcial=15)
-- ✔ Correção verificada: avg_dau_30d = 38.7 (not 37.6); daily DAU range 9-84 (not 17-84); WAU=165 (correct); n_days should be 30, not 31
-- Viz: line chart diario com banda partial-day destacada  | Alvo: DAU medio >= 45  | Alerta: DAU medio < 25 (excluindo feriados)
WITH daily AS (SELECT (COALESCE(sent_at,created_at) AT TIME ZONE 'America/Sao_Paulo')::date AS d, external_identity_id FROM messages WHERE COALESCE(sent_at,created_at) >= '2026-05-21' AND COALESCE(sent_at,created_at) < '2026-06-20'), per_day AS (SELECT d, COUNT(DISTINCT external_identity_id) AS dau FROM daily GROUP BY d) SELECT ROUND(AVG(dau),1) AS avg_dau_30d FROM per_day;

-- [3] Stickiness DAU/MAU - texto  (health)
-- Definição: Razao DAU_medio / MAU nos ultimos 30 dias completos. Mede frequencia de retorno: quao 'pegajosa' e a conversa de texto.
-- Valor medido: Stickiness = 9,63% (abaixo do benchmark saudavel ~20%)
-- ✔ Correção verificada: Stickiness = 9.91% (not 9.63%)
-- Viz: gauge + trend  | Alvo: >= 15%  | Alerta: < 8%
WITH daily AS (SELECT (COALESCE(sent_at,created_at) AT TIME ZONE 'America/Sao_Paulo')::date AS d, external_identity_id FROM messages WHERE COALESCE(sent_at,created_at) >= '2026-05-21' AND COALESCE(sent_at,created_at) < '2026-06-20'), per_day AS (SELECT d, COUNT(DISTINCT external_identity_id) AS dau FROM daily GROUP BY d) SELECT ROUND(100.0*AVG(dau)/(SELECT COUNT(DISTINCT external_identity_id) FROM daily),2) AS stickiness_pct FROM per_day;

-- [4] Mensagens por membro ativo (30d)  (input)
-- Definição: Total de mensagens / membros ativos distintos nos ultimos 30 dias completos. Intensidade de uso por pessoa. Atencao: inflada pelo canal Trabalho/Working (feed de status).
-- Valor medido: 27.418 msgs / 391 ativos = 70,1 msgs por membro ativo (mediana muito menor devido a power-law)
-- ✔ Correção verificada: 27.418 msgs / 390 ativos = 70.3 (denominator should be 390, not 391)
-- Viz: bar + media vs mediana lado a lado  | Alvo: monitorar tendencia (nao alvo absoluto)  | Alerta: queda > 30% MoM
WITH base AS (SELECT * FROM messages WHERE COALESCE(sent_at,created_at) >= '2026-05-21' AND COALESCE(sent_at,created_at) < '2026-06-20') SELECT COUNT(*) AS total, COUNT(DISTINCT external_identity_id) AS active, ROUND(COUNT(*)::numeric/NULLIF(COUNT(DISTINCT external_identity_id),0),1) AS msgs_per_active FROM base;

-- [5] Concentracao de canais (share top-1)  (health)
-- Definição: Participacao do canal #1 no total de mensagens dos ultimos 30 dias completos, com join messages.channel_id = discord_channels.discord_channel_id. Mede saude da distribuicao da conversa.
-- Valor medido: Top-1 'Trabalho/Working' = 62,57%; Top-2 'Reuniao Semanal' = 30,35% (top-2 acumulado 92,9%); 'bate-papo' so 3,99%
-- Viz: treemap / pareto de canais  | Alvo: top-1 < 40% (conversa mais distribuida)  | Alerta: top-1 > 65%
WITH base AS (SELECT m.channel_id FROM messages m WHERE COALESCE(m.sent_at,m.created_at) >= '2026-05-21' AND COALESCE(m.sent_at,m.created_at) < '2026-06-20'), ch AS (SELECT b.channel_id, dc.name, COUNT(*) AS n FROM base b LEFT JOIN discord_channels dc ON dc.discord_channel_id=b.channel_id GROUP BY b.channel_id, dc.name) SELECT name, n, ROUND(100.0*n/SUM(n) OVER (),2) AS pct FROM ch ORDER BY n DESC LIMIT 5;

-- [6] Share do top 1% de membros (power-law)  (health)
-- Definição: % das mensagens geradas pelo 1% (e 10%) mais ativo dos membros nos ultimos 30 dias. Diagnostica dependencia de poucos super-usuarios.
-- Valor medido: Top 1% = 34,28% das mensagens; Top 10% = 87,96%. 391 membros ativos no periodo.
-- Viz: lorenz curve / concentracao  | Alvo: top 1% < 25%  | Alerta: top 1% > 40%
WITH per_user AS (SELECT external_identity_id, COUNT(*) AS msgs FROM messages WHERE COALESCE(sent_at,created_at) >= '2026-05-21' AND COALESCE(sent_at,created_at) < '2026-06-20' GROUP BY external_identity_id), ranked AS (SELECT msgs, NTILE(100) OVER (ORDER BY msgs DESC) AS pctile, SUM(msgs) OVER () AS total FROM per_user) SELECT ROUND(100.0*SUM(msgs) FILTER (WHERE pctile=1)/MAX(total),2) AS top1pct, ROUND(100.0*SUM(msgs) FILTER (WHERE pctile<=10)/MAX(total),2) AS top10pct FROM ranked;

-- [7] Taxa de reacao (janela valida)  (content)
-- Definição: % de mensagens com reactions_count>0. MEDIDA na ultima janela com metadata valido (16/02-18/03/2026), pois a ingestao de reacoes parou em ~2026-03-23. Proxy de ressonancia do conteudo.
-- Valor medido: 0,05% na janela valida recente; lifetime 72.131/3.292.538 = 2,19%; 2024-ate-mar/2026 = 0,66%
-- Viz: line com flag de 'metadata indisponivel' apos 18/03  | Alvo: > 1% (restaurar captura primeiro)  | Alerta: ZERO em janela onde metadata deveria existir (= ingestao quebrada)
WITH base AS (SELECT * FROM messages WHERE COALESCE(sent_at,created_at) >= '2026-02-16' AND COALESCE(sent_at,created_at) < '2026-03-18') SELECT ROUND(100.0*COUNT(*) FILTER (WHERE reactions_count>0)/COUNT(*),2) AS pct_reacted FROM base;

-- [8] Taxa de resposta (reply rate, janela valida)  (content)
-- Definição: % de mensagens que sao replies (reply_to_message_id IS NOT NULL OR kind='reply'). kind='reply' captura mais que a FK. Mede densidade conversacional. Janela valida 16/02-18/03/2026.
-- Valor medido: 0,74% (combinado) na janela valida; via FK isolada 0,72%; kind='reply' adiciona pouco neste periodo; 2024->mar2026 pct_reply_kind=2,24% vs FK=0,90%
-- Viz: line com flag metadata  | Alvo: > 3% (conversas mais encadeadas)  | Alerta: 0% pos-18/03 = artefato de ingestao
WITH base AS (SELECT * FROM messages WHERE COALESCE(sent_at,created_at) >= '2026-02-16' AND COALESCE(sent_at,created_at) < '2026-03-18') SELECT ROUND(100.0*COUNT(*) FILTER (WHERE reply_to_message_id IS NOT NULL OR kind='reply')/COUNT(*),2) AS pct_reply FROM base;

-- [9] Taxa de edicao (janela valida)  (content)
-- Definição: % de mensagens com edited_at IS NOT NULL na janela valida 16/02-18/03/2026. Sinal de cuidado/correcao do autor.
-- Valor medido: 0,03% na janela valida; lifetime 30.043/3.292.538 = 0,91%; 2024->mar2026 = 0,22%
-- Viz: small number + flag  | Alvo: informativo (sem alvo)  | Alerta: 0% pos-18/03 = ingestao quebrada
WITH base AS (SELECT * FROM messages WHERE COALESCE(sent_at,created_at) >= '2026-02-16' AND COALESCE(sent_at,created_at) < '2026-03-18') SELECT ROUND(100.0*COUNT(*) FILTER (WHERE edited_at IS NOT NULL)/COUNT(*),2) AS pct_edited FROM base;

-- [10] Atividade de mencoes  (content)
-- Definição: Mencoes capturadas (message_mentions) e mencoes a everyone/role. Mede o quanto a conversa puxa pessoas para dentro. Tabela message_mentions tambem parou em 2026-03-23.
-- Valor medido: 170.847 mencoes historicas, 21.298 identidades mencionadas distintas; ULTIMA mencao registrada 2026-03-23 (ingestao parou)
-- Viz: rede de mencoes / counter com data da ultima captura  | Alvo: restaurar captura + crescer mencoes/membro  | Alerta: nenhuma nova linha em message_mentions por 7 dias
SELECT (SELECT COUNT(*) FROM message_mentions) AS total_mention_rows, (SELECT COUNT(DISTINCT mentioned_identity_id) FROM message_mentions) AS distinct_mentioned, (SELECT MAX(COALESCE(m.sent_at,m.created_at)) FROM message_mentions mm JOIN messages m ON m.id=mm.message_id) AS last_mention_at;

-- [11] Heatmap hora x dia-da-semana (texto)  (input)
-- Definição: Distribuicao de mensagens por (DOW, hora) em America/Sao_Paulo nos ultimos 90 dias. Revela janelas de pico para timing de anuncios/eventos. DOW: 0=domingo.
-- Valor medido: Pico forte SEGUNDA (dow=1) 22h (9.252) e 23h (5.077) BRT = reuniao semanal; pico secundario QUARTA (dow=3) 19-20h; manha 11h (qui/sex) ativa
-- Viz: heatmap 7x24  | Alvo: informativo (timing)  | Alerta: n/a
WITH base AS (SELECT COALESCE(sent_at,created_at) AT TIME ZONE 'America/Sao_Paulo' AS ts FROM messages WHERE COALESCE(sent_at,created_at) >= '2026-03-20' AND COALESCE(sent_at,created_at) < '2026-06-20') SELECT EXTRACT(DOW FROM ts)::int AS dow, EXTRACT(HOUR FROM ts)::int AS hour, COUNT(*) AS n FROM base GROUP BY 1,2 ORDER BY n DESC LIMIT 10;

-- [12] Distribuicao por kind / source_kind  (content)
-- Definição: Mix de tipos de mensagem (kind) e origem (source_kind) nos ultimos ~6 meses. Importante porque NULL domina e webhook infla volume sem ser chat humano.
-- Valor medido: kind NULL / source NULL = 83,37% (metadata ausente); default+webhook = 14,18%; default+bot = 1,57%; reply+bot 0,44%; user real (default+user) so 0,31%
-- Viz: stacked bar kind x source  | Alvo: reduzir NULL share apos correcao de ingestao  | Alerta: NULL share > 80% (perda de metadata)
SELECT kind, source_kind, COUNT(*) AS n, ROUND(100.0*COUNT(*)/SUM(COUNT(*)) OVER (),2) AS pct FROM messages WHERE COALESCE(sent_at,created_at) >= '2026-01-01' GROUP BY kind, source_kind ORDER BY n DESC LIMIT 10;

-- #####################################################################
-- NICHO: Engajamento de Voz (voice_messages)
-- #####################################################################

-- [13] Voice-hours per week  (north_star)
-- Definição: Total paired voice-hours (joined→left per external_identity_id+channel_name, sessions capped at 12h) bucketed by week in display tz. The core measure of voice engagement volume.
-- Valor medido: Last reliable weeks (BRT): 2026-05-04=380.3h, 05-11=393.5h, 05-18=608.2h (peak), 05-25=522.3h, 06-01=503.5h. Total all-time ~109,473.4h across 170,861 sessions. Weeks after 2026-06-06 are 0/null due to pipeline break.
-- ✔ Correção verificada: Confirmed exactly: 170,861 sessions, 109,473.4h total. Weekly BRT: 05-04=380.3, 05-11=393.5, 05-18=608.2 (peak), 05-25=522.3, 06-01=503.5. Single AT TIME ZONE (no double-convert), occurred_at>=2020-01-01 filter valid (min row 2020-01-01, 0 nulls). 06-01 week is partial (cut by pipeline break) and correctly flagged.
-- Viz: line chart (weekly), with vertical marker at 2026-06-06 pipeline break  | Alvo: Sustain >=500 voice-hours/week  | Alerta: Alert if a COMPLETE week < 350h, OR if joined-event count for the trailing 3 days = 0 (pipeline-dead signal)
WITH ordered AS (SELECT external_identity_id, channel_name, state, occurred_at, LEAD(state) OVER w AS next_state, LEAD(occurred_at) OVER w AS next_occurred FROM voice_messages WHERE state IN ('joined','left') AND occurred_at >= '2020-01-01' WINDOW w AS (PARTITION BY external_identity_id, channel_name ORDER BY occurred_at)), sessions AS (SELECT occurred_at, EXTRACT(EPOCH FROM (next_occurred - occurred_at))/3600.0 AS hours FROM ordered WHERE state='joined' AND next_state='left' AND next_occurred > occurred_at AND next_occurred - occurred_at <= interval '12 hours') SELECT date_trunc('week', occurred_at AT TIME ZONE 'America/Sao_Paulo')::date AS week, ROUND(SUM(hours)::numeric,1) AS voice_hours FROM sessions GROUP BY 1 ORDER BY 1;

-- [14] Weekly active voice users (WAU-voice)  (input)
-- Definição: COUNT(DISTINCT external_identity_id) with at least one paired voice session in the week. Drives voice-hours.
-- Valor medido: BRT weeks: 05-04=321, 05-11=300, 05-18=387 (peak), 05-25=245, 06-01=314 users. 0 after 2026-06-06 (pipeline).
-- ✔ Correção verificada: Confirmed: 05-04=321, 05-11=300, 05-18=387, 05-25=245, 06-01=314. Uses COUNT(DISTINCT external_identity_id) correctly.
-- Viz: line chart weekly  | Alvo: >=300 active voice users/week  | Alerta: Alert if complete-week WAU-voice < 200
WITH ordered AS (SELECT external_identity_id, channel_name, state, occurred_at, LEAD(state) OVER w AS next_state, LEAD(occurred_at) OVER w AS next_occurred FROM voice_messages WHERE state IN ('joined','left') AND occurred_at >= '2020-01-01' WINDOW w AS (PARTITION BY external_identity_id, channel_name ORDER BY occurred_at)), sessions AS (SELECT external_identity_id, occurred_at FROM ordered WHERE state='joined' AND next_state='left' AND next_occurred > occurred_at AND next_occurred - occurred_at <= interval '12 hours') SELECT date_trunc('week', occurred_at AT TIME ZONE 'America/Sao_Paulo')::date AS week, COUNT(DISTINCT external_identity_id) AS active_voice_users FROM sessions GROUP BY 1 ORDER BY 1;

-- [15] Median session duration  (health)
-- Definição: Median (P50) minutes per paired voice session. Median used instead of mean because distribution is heavily right-skewed (drop-ins distort the mean).
-- Valor medido: Median = 5.3 min, Mean = 38.4 min (all-time). Large gap confirms right-skew.
-- ✔ Correção verificada: Confirmed: median=5.3 min, mean=38.4 min (170,861 sessions). PERCENTILE_CONT used correctly; median chosen over mean appropriately given right-skew.
-- Viz: single stat + secondary mean stat to show skew  | Alvo: Median trending up (more sticky sessions)  | Alerta: Watch if median drops below 3 min
WITH ordered AS (SELECT external_identity_id, channel_name, state, occurred_at, LEAD(state) OVER w AS next_state, LEAD(occurred_at) OVER w AS next_occurred FROM voice_messages WHERE state IN ('joined','left') AND occurred_at >= '2020-01-01' WINDOW w AS (PARTITION BY external_identity_id, channel_name ORDER BY occurred_at)), sessions AS (SELECT EXTRACT(EPOCH FROM (next_occurred - occurred_at))/3600.0 AS hours FROM ordered WHERE state='joined' AND next_state='left' AND next_occurred > occurred_at AND next_occurred - occurred_at <= interval '12 hours') SELECT ROUND((PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY hours)*60)::numeric,1) AS median_min, ROUND((AVG(hours)*60)::numeric,1) AS mean_min FROM sessions;

-- [16] Voice drop-in (bounce) rate  (health)
-- Definição: Share of paired sessions lasting < 1 minute. Proxy for accidental/curiosity joins that don't convert to real participation.
-- Valor medido: Drop-in rate = 0.354 (35.4%; 60,554 of 170,861 sessions < 1 min). Buckets: <1m=60,554, 1-5m=24,069, 5-30m=34,188, 30-120m=37,427, >2h=14,623.
-- ✔ Correção verificada: Confirmed: 0.354 (60,554/170,861 < 1 min). Buckets verified: <1m=60,554, 1-5m=24,069, 5-30m=34,188, 30-120m=37,427, >2h=14,623.
-- Viz: gauge + duration-bucket histogram  | Alvo: Reduce below 30%  | Alerta: Alert if > 45%
WITH ordered AS (SELECT external_identity_id, channel_name, state, occurred_at, LEAD(state) OVER w AS next_state, LEAD(occurred_at) OVER w AS next_occurred FROM voice_messages WHERE state IN ('joined','left') AND occurred_at >= '2020-01-01' WINDOW w AS (PARTITION BY external_identity_id, channel_name ORDER BY occurred_at)), sessions AS (SELECT EXTRACT(EPOCH FROM (next_occurred - occurred_at))/60.0 AS minutes FROM ordered WHERE state='joined' AND next_state='left' AND next_occurred > occurred_at AND next_occurred - occurred_at <= interval '12 hours') SELECT ROUND((COUNT(*) FILTER (WHERE minutes < 1)::numeric / COUNT(*)),3) AS drop_in_rate, COUNT(*) FILTER (WHERE minutes < 1) AS under_1min, COUNT(*) FILTER (WHERE minutes >= 120) AS over_2h FROM sessions;

-- [17] Voice session pairing completeness  (health)
-- Definição: Share of 'joined' events that have a matching subsequent 'left' (per user+channel). A data-quality / instrumentation health metric — low values mean missed disconnects and undercounted hours.
-- Valor medido: Pairing rate = 0.878 (173,528 matched / 197,743 joins). 24,215 orphan joins (12.2%).
-- ✔ Correção verificada: Confirmed: 0.878 (173,528 matched / 197,743 joins). 24,215 orphan joins (12.2%). 2,275 sessions exceeded 12h cap (verified).
-- Viz: single stat with red/green threshold  | Alvo: >= 0.90  | Alerta: Alert if < 0.80 (instrumentation degrading)
WITH ordered AS (SELECT external_identity_id, channel_name, state, occurred_at, LEAD(state) OVER w AS next_state FROM voice_messages WHERE state IN ('joined','left') AND occurred_at >= '2020-01-01' WINDOW w AS (PARTITION BY external_identity_id, channel_name ORDER BY occurred_at)) SELECT COUNT(*) FILTER (WHERE state='joined') AS joins, COUNT(*) FILTER (WHERE state='joined' AND next_state='left') AS matched, ROUND((COUNT(*) FILTER (WHERE state='joined' AND next_state='left')::numeric / NULLIF(COUNT(*) FILTER (WHERE state='joined'),0)),3) AS pairing_rate FROM ordered;

-- [18] Peak voice concurrency (daily)  (input)
-- Definição: Max simultaneous users in voice per day, approximated by the running sum of +1(joined)/-1(left) over occurred_at within the day (display tz). Proxy for live-room peak load and event attendance.
-- Valor medido: Recent BRT peaks: 06-01=45, 05-25=55 (weekly meeting spikes) vs single-digit baseline (2-10) on ordinary days. Goes to single digits / unreliable after 06-06 (only mute/unmute events remain).
-- ✔ Correção verificada: Reproduced peaks: 05-25=55, 05-11=55, 05-04=51, 04-27=47, 06-01=45. Report cited 06-01=45 and 05-25=55 as examples (correct). Running-sum proxy ordered by occurred_at then delta DESC processes joins before lefts on ties, marginally inflating peak; acceptable for a proxy. Channel-agnostic so unaffected by the channel_name resolution issue.
-- Viz: bar chart daily (last 30d), highlight weekly meeting peaks  | Alvo: Sustain weekly peak >= 40 on meeting days  | Alerta: Alert if weekly meeting-day peak < 20
WITH evt AS (SELECT occurred_at, CASE WHEN state='joined' THEN 1 WHEN state='left' THEN -1 ELSE 0 END AS delta, (occurred_at AT TIME ZONE 'America/Sao_Paulo')::date AS day FROM voice_messages WHERE state IN ('joined','left') AND occurred_at >= now() - interval '60 days'), running AS (SELECT day, SUM(delta) OVER (PARTITION BY day ORDER BY occurred_at, delta DESC) AS concurrent FROM evt) SELECT day, MAX(concurrent) AS peak_concurrent FROM running GROUP BY day ORDER BY day DESC;

-- [19] Top voice channels by hours (concentration)  (content)
-- Definição: Voice-hours, sessions, distinct users and avg session length per channel_name (excluding unresolved numeric-ID names). Shows where engagement concentrates and channel personality.
-- Valor medido: 🗣 Conversando 19,321.1h/4,834u/39.8min; ✅Reunião Semanal 10,744.8h/11,255u/15.1min (high churn event); 🏢 Trabalho/Working 5,700.0h/945u/82.4min (longest); 📖 Estudando 5,334.4h/53.0min; 😴 Ausente 3,446.4h/79.2min; 🗣 Conversando 2 2,865.7h; Spaces 2,008.2h/5,502u/9.5min.
-- ✔ Correção verificada: Numbers for named channels reproduce exactly (Conversando 19,321.1h, Reuniao Semanal 10,744.8h, Trabalho 5,700.0h, etc). BUT the exclusion regex (channel_name !~ '^[0-9]+$') drops 4,700 distinct unresolved-ID channels totalling 58,562h = 53.5% of all 109,482 voice-hours and 66,166 sessions. The report claimed only '~15' such channels. The concentration denominator therefore covers under half of real voice-hours, so the 'single channel > 60% of weekly hours' alert is computed against a wrong base. Worse: in May-Jun 2026, 100% of join/left rows carry numeric channel_names, so this chart shows ZERO recent activity and is entirely historical.
-- Viz: horizontal bar (top 10) + table with avg-min column  | Alvo: n/a (distribution monitor)  | Alerta: Flag if a single channel exceeds 60% of weekly hours (over-concentration)
WITH ordered AS (SELECT external_identity_id, channel_name, state, occurred_at, LEAD(state) OVER w AS next_state, LEAD(occurred_at) OVER w AS next_occurred FROM voice_messages WHERE state IN ('joined','left') AND occurred_at >= '2020-01-01' WINDOW w AS (PARTITION BY external_identity_id, channel_name ORDER BY occurred_at)), sessions AS (SELECT channel_name, external_identity_id, EXTRACT(EPOCH FROM (next_occurred - occurred_at))/3600.0 AS hours FROM ordered WHERE state='joined' AND next_state='left' AND next_occurred > occurred_at AND next_occurred - occurred_at <= interval '12 hours') SELECT channel_name, COUNT(*) AS sessions, COUNT(DISTINCT external_identity_id) AS users, ROUND(SUM(hours)::numeric,1) AS voice_hours, ROUND((AVG(hours)*60)::numeric,1) AS avg_min FROM sessions WHERE channel_name !~ '^[0-9]+$' GROUP BY 1 ORDER BY voice_hours DESC LIMIT 10;

-- [20] Voice mute ratio  (health)
-- Definição: Mute events per joined session. Low values = users speak/stay unmuted; very high values may indicate passive listening. NOTE: muted/unmuted instrumentation only became reliable from ~2026-06-06; the all-time number understates current behavior.
-- Valor medido: All-time = 0.009 (1,808 mutes / 197,743 joins) — artificially low because mute events were barely captured before 06-06. Post-06-07: 1,746 muted + 953 unmuted events exist but with 0 joins to ratio against (pipeline split).
-- ✔ Correção verificada: Confirmed: 0.009 (1,808 muted / 197,743 joins). Note total muted is only 1,808 across all time; the report's narrative '1,746 muted + 953 unmuted post-06-07' overstates post-break mutes (actual post-06-06 muted is well under 1,808). Ratio KPI itself is correct.
-- Viz: single stat (scoped to >= 2026-06-07 window once join tracking is restored)  | Alvo: Establish baseline once join+mute tracking coexist  | Alerta: Re-baseline after pipeline fix
SELECT ROUND((COUNT(*) FILTER (WHERE state='muted')::numeric / NULLIF(COUNT(*) FILTER (WHERE state='joined'),0)),3) AS mute_per_join FROM voice_messages WHERE occurred_at >= '2020-01-01';

-- [21] Voice-only vs also-text member split  (cross_platform)
-- Definição: Among all voice users, share who NEVER appear in messages (voice-only / silent listeners) vs those who also post text. Identifies a re-engagement segment.
-- Valor medido: 19,896 voice users total: 9,429 (47.4%) also post text, 10,467 (52.6%) are voice-ONLY (never sent a text message).
-- ✔ Correção verificada: Confirmed: 19,896 voice users; 9,429 (47.4%) also-text; 10,467 (52.6%) voice-only. messages filtered COALESCE(sent_at,created_at)>=2019-01-01 correctly excludes the 1999 junk (108,928 junk rows verified). Joined on external_identity_id (correct unifying key).
-- Viz: donut chart  | Alvo: Convert voice-only into text participants (reduce voice-only share)  | Alerta: n/a (segment monitor)
WITH voice_users AS (SELECT DISTINCT external_identity_id FROM voice_messages WHERE occurred_at >= '2020-01-01'), text_users AS (SELECT DISTINCT external_identity_id FROM messages WHERE COALESCE(sent_at, created_at) >= '2019-01-01') SELECT (SELECT COUNT(*) FROM voice_users) AS voice_users_total, (SELECT COUNT(*) FROM voice_users v WHERE EXISTS (SELECT 1 FROM text_users t WHERE t.external_identity_id=v.external_identity_id)) AS voice_and_text, (SELECT COUNT(*) FROM voice_users v WHERE NOT EXISTS (SELECT 1 FROM text_users t WHERE t.external_identity_id=v.external_identity_id)) AS voice_only;

-- [22] Voice retention (multi-month return rate)  (business)
-- Definição: Share of voice users active in >=2 distinct calendar months (display tz). Measures whether voice creates a recurring habit, not a one-off.
-- Valor medido: Multi-month retention = 0.501 (9,963 of 19,896 return in 2+ months); 9,933 one-month-only; 1,961 are 6+ month long-haulers (9.9%). Active in last 30d = 625 (inflated by mute/unmute-only rows post-06-06).
-- ✔ Correção verificada: Confirmed: 0.501 (9,963/19,896); 6+ month=1,961 (9.9%). Single AT TIME ZONE month bucketing correct.
-- Viz: single stat + cohort line for month-over-month retention  | Alvo: >= 0.50 multi-month return  | Alerta: Alert if multi-month rate drops below 0.40
WITH user_months AS (SELECT external_identity_id, COUNT(DISTINCT date_trunc('month', occurred_at AT TIME ZONE 'America/Sao_Paulo')) AS active_months FROM voice_messages WHERE occurred_at >= '2020-01-01' GROUP BY 1) SELECT COUNT(*) AS total, COUNT(*) FILTER (WHERE active_months>=2) AS multi_month, COUNT(*) FILTER (WHERE active_months>=6) AS six_plus, ROUND((COUNT(*) FILTER (WHERE active_months>=2)::numeric / COUNT(*)),3) AS multi_month_rate FROM user_months;

-- [23] Month-over-month voice retention  (business)
-- Definição: Of users active in voice in month M, how many were also active in month M-1. A rolling stickiness signal.
-- Valor medido: Active/retained by month (BRT): 2026-03=951/190 (20%), 04=816/296 (36%), 05=920/290 (32%), 06=425/201 (47% but June PARTIAL & join-tracking dead after 06-06). 2025-07=462/138 (30%).
-- ✔ Correção verificada: Values reproduce exactly (2026-03=951/190, 04=816/296, 05=920/290, 06=425/201). BUT 'active' is defined as ANY voice_messages row (no state filter), so post-06-06 the muted/unmuted-only rows count as active. June 2026 active=425 includes mute-only users who have no paired session, while every other voice KPI defines activity via paired joined->left sessions. This is an inconsistent activity definition that inflates June and makes the June MoM number non-comparable. The report flags June as partial but does not flag the definition mismatch.
-- Viz: line chart (retained vs active) last 12 months  | Alvo: MoM retention >= 30%  | Alerta: Alert on a complete month with MoM retention < 20%
WITH monthly AS (SELECT DISTINCT external_identity_id, date_trunc('month', occurred_at AT TIME ZONE 'America/Sao_Paulo')::date AS mon FROM voice_messages WHERE occurred_at >= '2025-06-01') SELECT mon, COUNT(*) AS active_users, COUNT(*) FILTER (WHERE EXISTS (SELECT 1 FROM monthly p WHERE p.external_identity_id=m.external_identity_id AND p.mon = m.mon - interval '1 month')) AS retained_from_prev FROM monthly m GROUP BY mon ORDER BY mon;

-- #####################################################################
-- NICHO: Crescimento e Churn de Membros
-- #####################################################################

-- [24] Net Member Growth (window)  (north_star)
-- Definição: GUILD_MEMBER_ADD events minus GUILD_MEMBER_REMOVE events over the logged window. The single number that says whether the community is growing.
-- Valor medido: +313 net (698 adds - 385 removes) over 2026-05-19 to 2026-06-20 (~32 days)
-- ✔ Correção verificada: +313 net (698 adds - 385 removes) — confirmed exactly
-- Viz: Stacked bar (adds up / removes down) + net line overlay, daily  | Alvo: >= +250 net per ~30d window  | Alerta: Alert if rolling 7-day net <= 0 on any full (non-partial) week
SELECT COUNT(*) FILTER (WHERE event_type='GUILD_MEMBER_ADD') AS adds, COUNT(*) FILTER (WHERE event_type='GUILD_MEMBER_REMOVE') AS removes, COUNT(*) FILTER (WHERE event_type='GUILD_MEMBER_ADD') - COUNT(*) FILTER (WHERE event_type='GUILD_MEMBER_REMOVE') AS net_growth FROM discord_event_logs WHERE event_type IN ('GUILD_MEMBER_ADD','GUILD_MEMBER_REMOVE');

-- [25] Daily Churn Ratio (removes/adds)  (health)
-- Definição: Removes divided by adds per day (display tz). >1.0 means the guild shrank that day. A normalized ratio that is comparable across days regardless of traffic volume.
-- Valor medido: Window avg 0.552. Daily range 0.19 to 2.60. Negative-net days: 2026-06-05 (1.167), 2026-06-16 (2.60), 2026-06-20 (1.167, PARTIAL day)
-- ✔ Correção verificada: Window avg 0.552, daily range 0.191-2.600; neg-net days 06-05 (1.167), 06-16 (2.600), 06-20 partial (1.167) — confirmed. Single AT TIME ZONE, no double-conversion.
-- Viz: Line chart, daily, with 1.0 reference band; shade current partial day  | Alvo: < 0.60  | Alerta: Alert if churn_ratio > 1.0 on any full day, or 3-day rolling avg > 0.80
WITH d AS (SELECT (created_at AT TIME ZONE 'America/Sao_Paulo')::date AS day, COUNT(*) FILTER (WHERE event_type='GUILD_MEMBER_ADD') AS adds, COUNT(*) FILTER (WHERE event_type='GUILD_MEMBER_REMOVE') AS removes FROM discord_event_logs WHERE event_type IN ('GUILD_MEMBER_ADD','GUILD_MEMBER_REMOVE') GROUP BY 1) SELECT day, adds, removes, ROUND(removes::numeric/NULLIF(adds,0),3) AS churn_ratio FROM d ORDER BY day;

-- [26] Daily Member Adds / Removes  (input)
-- Definição: Raw count of GUILD_MEMBER_ADD and GUILD_MEMBER_REMOVE events per day in display tz. The two input flows that drive net growth.
-- Valor medido: Adds avg ~22/day (peak 47 on 05-23). Removes avg ~12/day (spike 26 on 06-16). 2026-06-20 is PARTIAL (6 adds / 7 removes by 18:37).
-- ✔ Correção verificada: Peak adds 47 on 05-23, removes spike 26 on 06-16, 06-20 partial 6/7 — confirmed exactly
-- Viz: Grouped daily bar chart  | Alvo: Adds >= 20/day sustained  | Alerta: Alert if adds 0 for a full day (likely ingestion outage, not real)
SELECT (created_at AT TIME ZONE 'America/Sao_Paulo')::date AS day, COUNT(*) FILTER (WHERE event_type='GUILD_MEMBER_ADD') AS adds, COUNT(*) FILTER (WHERE event_type='GUILD_MEMBER_REMOVE') AS removes FROM discord_event_logs WHERE event_type IN ('GUILD_MEMBER_ADD','GUILD_MEMBER_REMOVE') GROUP BY 1 ORDER BY 1;

-- [27] Weekly Net Growth Trend  (north_star)
-- Definição: Net member change bucketed by ISO week (display tz), to smooth daily noise and reveal the growth trajectory. Current week flagged PARTIAL.
-- Valor medido: 05-18: +106 (churn 0.354) | 05-25: +45 (0.659) | 06-01: +54 (0.609) | 06-08: +64 (0.549) | 06-15: +44 (0.639, PARTIAL). Net is decelerating vs the first full week.
-- ✔ Correção verificada: 05-18 +106 (0.354) | 05-25 +45 (0.659) | 06-01 +54 (0.609) | 06-08 +64 (0.549) | 06-15 +44 (0.639, PARTIAL) — confirmed exactly. Partial-week flag correct.
-- Viz: Bar chart per week, current week styled as partial/striped  | Alvo: Net >= +50/full week  | Alerta: Alert if two consecutive full weeks show net < +30
WITH w AS (SELECT date_trunc('week',(created_at AT TIME ZONE 'America/Sao_Paulo'))::date AS wk, COUNT(*) FILTER (WHERE event_type='GUILD_MEMBER_ADD') AS adds, COUNT(*) FILTER (WHERE event_type='GUILD_MEMBER_REMOVE') AS removes FROM discord_event_logs WHERE event_type IN ('GUILD_MEMBER_ADD','GUILD_MEMBER_REMOVE') GROUP BY 1) SELECT wk, adds, removes, adds-removes AS net, ROUND(removes::numeric/NULLIF(adds,0),3) AS churn_ratio, CASE WHEN wk=date_trunc('week',DATE '2026-06-20')::date THEN 'PARTIAL' ELSE 'full' END AS week_status FROM w ORDER BY wk;

-- [28] Join-Request Approval Rate  (business)
-- Definição: Share of resolved join requests that were APPROVED, of all resolved requests (APPROVED via GUILD_JOIN_REQUEST_UPDATE status='APPROVED' + abandoned/rejected via GUILD_JOIN_REQUEST_DELETE). Measures how permeable the onboarding gate is.
-- Valor medido: 0.9648 (658 approved / (658 + 24 deleted)). 698 actual member adds slightly exceed approvals (rejoins/instant joins).
-- ✔ Correção verificada: 0.9648 (658 approved / (658+24 deleted)) — confirmed. Minor: 5 null-status UPDATE events exist and are excluded from the denominator (unresolved requests); defensible and does not change the figure.
-- Viz: Funnel: Join Requests -> Approved -> Member Add; plus single gauge for rate  | Alvo: Maintain > 0.90 (gate not over-rejecting); investigate if it drops, may signal raid filtering  | Alerta: Alert if approval_rate < 0.80 over a rolling week (possible raid or screening misconfig)
SELECT COUNT(*) FILTER (WHERE event_type='GUILD_JOIN_REQUEST_UPDATE' AND payload->>'status'='APPROVED') AS approved, COUNT(*) FILTER (WHERE event_type='GUILD_JOIN_REQUEST_DELETE') AS deleted, ROUND(COUNT(*) FILTER (WHERE event_type='GUILD_JOIN_REQUEST_UPDATE' AND payload->>'status'='APPROVED')::numeric / NULLIF(COUNT(*) FILTER (WHERE event_type='GUILD_JOIN_REQUEST_UPDATE' AND payload->>'status'='APPROVED') + COUNT(*) FILTER (WHERE event_type='GUILD_JOIN_REQUEST_DELETE'),0),4) AS approval_rate FROM discord_event_logs;

-- [29] Membership-Screening Gate Pass Rate  (business)
-- Definição: Share of GUILD_MEMBER_ADD events arriving with pending=true (must complete Discord membership screening before participating). Shows how much of the join flow is gated.
-- Valor medido: 0.994 (694 of 698 joins arrive pending). 0 bot adds detected in payload->user->bot.
-- ✔ Correção verificada: 0.994 (694/698 pending), 0 bot adds, 692 distinct joiners — confirmed exactly. Correctly uses payload#>>'{user,id}' (the user_id column is 99% NULL).
-- Viz: Single stat card with sparkline  | Alvo: Informational; near 1.0 confirms screening is enforced on all joins  | Alerta: Alert if pending share suddenly drops (screening disabled) — a security/quality regression
SELECT COUNT(*) AS add_events, COUNT(*) FILTER (WHERE (payload->>'pending')::boolean IS TRUE) AS pending_on_join, COUNT(*) FILTER (WHERE (payload#>>'{user,bot}')::boolean IS TRUE) AS bot_adds, COUNT(DISTINCT payload#>>'{user,id}') AS distinct_joiners FROM discord_event_logs WHERE event_type='GUILD_MEMBER_ADD';

-- [30] Early Churn Rate (7-day, last 30d cohort)  (health)
-- Definição: Of members who joined in the last 30 days, the share who left within 7 days of joining. The sharpest onboarding-quality signal — these never stuck. Uses discord_members.joined_at/left_at (fresh table).
-- Valor medido: 15.43% (100 of 648 recent joiners left within 7 days)
-- ✔ Correção verificada: 15.43% (100 of 648) — confirmed exactly. now()-based 30d window on timestamptz is correct (absolute time).
-- Viz: Single stat card + trend line of weekly cohort early-churn  | Alvo: < 10%  | Alerta: Alert if early-churn rate > 20% for the trailing 30-day cohort
SELECT ROUND(100.0 * COUNT(*) FILTER (WHERE joined_at >= now() - interval '30 days' AND left_at IS NOT NULL AND left_at <= joined_at + interval '7 days') / NULLIF(COUNT(*) FILTER (WHERE joined_at >= now() - interval '30 days'),0),2) AS early_churn_7d_pct, COUNT(*) FILTER (WHERE joined_at >= now() - interval '30 days') AS joined_last_30d, COUNT(*) FILTER (WHERE joined_at >= now() - interval '30 days' AND left_at IS NOT NULL AND left_at <= joined_at + interval '7 days') AS churned_within_7d FROM discord_members WHERE NOT is_bot;

-- [31] Tenure Distribution of Churned Members  (health)
-- Definição: Histogram of how long churned members (left_at IS NOT NULL) stayed before leaving, bucketed from <1 day to 365+ days. Distinguishes 'never onboarded' churn from 'lost a veteran' churn.
-- Valor medido: 377 churned w/ tenure. <1d: 70 (18.6%) | 1-7d: 37 (9.8%) | 7-30d: 33 (8.8%) | 30-90d: 37 (9.8%) | 90-365d: 50 (13.3%) | 365+d: 150 (39.8%). Median leaver tenure 106.7 days, avg 480.9 days.
-- ✔ Correção verificada: 377 churned: <1d 70 (18.6%) | 1-7d 37 (9.8%) | 7-30d 33 (8.8%) | 30-90d 37 (9.8%) | 90-365d 50 (13.3%) | 365+d 150 (39.8%); median 106.7d, avg 480.9d — confirmed exactly
-- Viz: Horizontal bar histogram by tenure bucket  | Alvo: Reduce <1d + 1-7d combined share (currently 28.4%) below 20%  | Alerta: Alert if <7-day tenure share of churn exceeds 40% in a week (onboarding failure or raid cleanup)
WITH t AS (SELECT EXTRACT(EPOCH FROM (left_at - joined_at))/86400.0 AS tenure_days FROM discord_members WHERE left_at IS NOT NULL AND joined_at IS NOT NULL AND NOT is_bot) SELECT CASE WHEN tenure_days<1 THEN 'a. <1 day' WHEN tenure_days<7 THEN 'b. 1-7 days' WHEN tenure_days<30 THEN 'c. 7-30 days' WHEN tenure_days<90 THEN 'd. 30-90 days' WHEN tenure_days<365 THEN 'e. 90-365 days' ELSE 'f. 365+ days' END AS bucket, COUNT(*) AS n, ROUND(100.0*COUNT(*)/SUM(COUNT(*)) OVER (),1) AS pct FROM t GROUP BY 1 ORDER BY 1;

-- [32] Current Active Member Count (live)  (north_star)
-- Definição: Count of non-bot members with left_at IS NULL from the fresh discord_members table. Replaces the stale discord_guilds.member_count for the live headline number.
-- Valor medido: 25,031 active humans (vs stale guild member_count 24,723, synced 31 days ago — a 308-member gap). 5 bots, 377 lifetime churned-with-tenure.
-- ✔ Correção verificada: 25031 active humans, stale guild count 24723, 31 days stale, 5 bots — confirmed exactly
-- Viz: Big-number stat card with WoW delta  | Alvo: Monotonic growth week over week  | Alerta: Alert if live active count drops vs prior day (sustained net-negative churn)
SELECT COUNT(*) FILTER (WHERE left_at IS NULL AND NOT is_bot) AS live_active_members, (SELECT member_count FROM discord_guilds WHERE id=1) AS guild_reported_stale, (SELECT EXTRACT(DAY FROM now() - synced_at)::int FROM discord_guilds WHERE id=1) AS guild_count_days_stale FROM discord_members;

-- [33] Booster (Premium) Penetration  (business)
-- Definição: Share of active members who are server boosters (premium_since IS NOT NULL). A monetary/loyalty health signal tied to retention of the most invested members.
-- Valor medido: 13 active boosters = 0.052% of active members
-- ✔ Correção verificada: 13 active boosters = 0.052% of active members — confirmed exactly
-- Viz: Stat card with booster count + trend  | Alvo: Informational baseline; track absolute booster count for tier maintenance  | Alerta: Alert if active booster count drops below boost-tier threshold needed to keep current perks
SELECT COUNT(*) AS active_boosters, ROUND(100.0*COUNT(*)/NULLIF((SELECT COUNT(*) FROM discord_members WHERE left_at IS NULL AND NOT is_bot),0),3) AS pct_of_active FROM discord_members WHERE premium_since IS NOT NULL AND left_at IS NULL AND NOT is_bot;

-- #####################################################################
-- NICHO: WhatsApp e Comparacao Cross-Platform
-- #####################################################################

-- [34] Mensagens WhatsApp por dia (upserts/dia)  (north_star)
-- Definição: Volume diario de eventos messages.upsert do WhatsApp, bucketizado no fuso de exibicao (America/Sao_Paulo). E o pulso de atividade do canal WhatsApp.
-- Valor medido: 06-14:190(parcial/inicio), 06-15:1333, 06-16:1512, 06-17:1444, 06-18:2156(pico), 06-19:1772, 06-20:680(PARCIAL ate 18:54). Media dias completos (15-19): ~1643/dia.
-- ✔ Correção verificada: 06-14:190, 06-15:1333, 06-16:1512, 06-17:1444, 06-18:2156, 06-19:1772, 06-20:680(parcial). Total 9087. Exact match.
-- Viz: bar chart diario com ultimo dia hachurado (parcial)  | Alvo: manter >= 1500/dia em dias completos  | Alerta: < 1000/dia em dia COMPLETO (excluir dia corrente parcial)
SELECT (received_at AT TIME ZONE 'America/Sao_Paulo')::date AS dia, COUNT(*) AS upserts FROM whatsapp_event_logs WHERE type='messages.upsert' GROUP BY 1 ORDER BY 1;

-- [35] Chats ativos distintos / dia  (input)
-- Definição: Numero de chat_jid distintos com ao menos 1 messages.upsert no dia. Mede largura do engajamento entre grupos.
-- Valor medido: 06-14:2, 06-15:6, 06-16:4, 06-17:4, 06-18:4, 06-19:4, 06-20:4. Total distintos no periodo: 6 chats (apenas).
-- ✔ Correção verificada: 06-14:2, 06-15:6, 06-16:4, 06-17:4, 06-18:4, 06-19:4, 06-20:4. 6 distinct total. Exact match.
-- Viz: line chart  | Alvo: >= 5 chats ativos/dia  | Alerta: <= 2 chats ativos em dia completo
SELECT (received_at AT TIME ZONE 'America/Sao_Paulo')::date AS dia, COUNT(DISTINCT chat_jid) AS chats_ativos FROM whatsapp_event_logs WHERE type='messages.upsert' GROUP BY 1 ORDER BY 1;

-- [36] Indice de concentracao de chat (HHI / top-chat share)  (health)
-- Definição: Percentual de todas as mensagens WhatsApp concentradas no chat lider. Mede risco de dependencia de um unico grupo.
-- Valor medido: Top chat (120363423768795942@g.us): 7955 msgs = 87,5% share. 2o chat: 8,1%. 3o: 2,5%. 4o: 1,8%. Concentracao extrema.
-- ✔ Correção verificada: Top 87.5% (7955/9087), 2nd 8.1%, 3rd 2.5%, 4th 1.8%, plus two chats with 1 msg. Exact match.
-- Viz: stacked bar / treemap por chat  | Alvo: < 70% no top chat  | Alerta: > 85% no top chat (estado atual: 87,5% - ALERTA)
SELECT chat_jid, COUNT(*) AS msgs, ROUND(100.0*COUNT(*)/SUM(COUNT(*)) OVER (),1) AS pct_share FROM whatsapp_event_logs WHERE type='messages.upsert' GROUP BY 1 ORDER BY msgs DESC;

-- [37] Taxa de reacao WhatsApp (reactions/upserts)  (content)
-- Definição: Razao entre eventos messages.reaction e messages.upsert. Proxy de qualidade/ressonancia do conteudo (mensagens que provocam resposta emocional).
-- Valor medido: Agregado: 12,63% (1148 reactions / 9087 upserts). Por dia: 06-15:8,3% -> 06-18:12,3% -> 06-19:14,1% -> 06-20:14,1%. Tendencia de ALTA.
-- ✔ Correção verificada: Aggregate 12.63% (1148/9087). Per-day 06-15:8.3, 06-16:13.8, 06-17:12.0, 06-18:12.3, 06-19:14.1, 06-20:14.1. Exact match. (Note 06-14:22.6 is a low-volume outlier the finding omitted from the trend narrative.)
-- Viz: line chart com banda de meta  | Alvo: >= 12%  | Alerta: < 8% por dia completo
SELECT ROUND(100.0*(SELECT COUNT(*) FROM whatsapp_event_logs WHERE type='messages.reaction')/NULLIF((SELECT COUNT(*) FROM whatsapp_event_logs WHERE type='messages.upsert'),0),2) AS reaction_rate_pct;

-- [38] Variacao liquida de participantes de grupo  (business)
-- Definição: Soma diaria de participantes adicionados menos removidos, lendo o payload jsonb (action add/remove, jsonb_array_length(participants)). Crescimento liquido da base de grupos.
-- Valor medido: 06-15:-16, 06-16:+12, 06-17:+2, 06-18:+177(pico onboarding), 06-19:+22, 06-20:+5. Liquido total janela: +202 (278 adds, 76 removes).
-- ✔ Correção verificada: 06-15:-16, 06-16:+12, 06-17:+2, 06-18:+177, 06-19:+22, 06-20:+5. Net +202 (278 adds, 76 removes). Exact match.
-- Viz: diverging bar (verde add / vermelho remove) + linha net  | Alvo: net positivo semanal  | Alerta: net negativo em 2+ dias completos consecutivos
SELECT (received_at AT TIME ZONE 'America/Sao_Paulo')::date AS dia, SUM(CASE WHEN payload->>'action'='add' THEN jsonb_array_length(payload->'participants') WHEN payload->>'action'='remove' THEN -jsonb_array_length(payload->'participants') ELSE 0 END) AS net FROM whatsapp_event_logs WHERE type='group-participants.update' GROUP BY 1 ORDER BY 1;

-- [39] Razao de churn de grupo (adds por remove)  (health)
-- Definição: Total de adds dividido por total de removes na janela. Mede saude do crescimento: >1 indica entrada superando saida.
-- Valor medido: 3,66 adds por remove (278 adds / 76 removes) na janela de 7 dias.
-- ✔ Correção verificada: 278/76 = 3.66. Exact match.
-- Viz: gauge / big number  | Alvo: >= 1.5  | Alerta: < 1.0 (saidas superam entradas)
SELECT ROUND(SUM(CASE WHEN payload->>'action'='add' THEN jsonb_array_length(payload->'participants') ELSE 0 END)::numeric / NULLIF(SUM(CASE WHEN payload->>'action'='remove' THEN jsonb_array_length(payload->'participants') ELSE 0 END),0),2) AS churn_ratio FROM whatsapp_event_logs WHERE type='group-participants.update';

-- [40] Atividade horaria WhatsApp (pico de horario)  (content)
-- Definição: Distribuicao de messages.upsert por hora do dia no fuso de exibicao. Identifica janelas de maior engajamento para timing de conteudo/anuncios.
-- Valor medido: Pico: 14h(860), 21h(805), 11h(733), 12h(688), 10h(652), 17h(630). Dois picos: comercial (10h-14h) e noite (21h).
-- ✔ Correção verificada: 14h:860, 21h:805, 11h:733, 12h:688, 10h:652, 17h:630. Exact match.
-- Viz: heatmap hora-x-dia ou bar por hora  | Alvo: n/a (mapa de calor descritivo)  | Alerta: n/a
SELECT EXTRACT(HOUR FROM received_at AT TIME ZONE 'America/Sao_Paulo')::int AS hora, COUNT(*) AS upserts FROM whatsapp_event_logs WHERE type='messages.upsert' GROUP BY 1 ORDER BY 2 DESC;

-- [41] Volume cross-platform: Discord/WhatsApp na mesma janela 7d  (cross_platform)
-- Definição: Razao de mensagens Discord (MESSAGE_CREATE) sobre WhatsApp (messages.upsert) por dia, restrito a janela sobreposta (>= 2026-06-14). Compara peso relativo dos canais.
-- Valor medido: Janela 7d total: WhatsApp 9087 vs Discord 8382 msgs (Discord/WA = 0,92). Por dia dc_per_wa: 06-14:1.8, 06-15:2.7(evento Discord), 06-16:0.5, 06-17:1.4, 06-18:0.3, 06-19:0.5, 06-20:0.2. Apos 06-16 WhatsApp domina o volume diario.
-- ✔ Correção verificada: Per-day dc_per_wa: 06-14:1.8, 06-15:2.7, 06-16:0.5, 06-17:1.4, 06-18:0.3, 06-19:0.5, 06-20:0.2. 7d totals WA 9087 vs Discord 8382 (0.92). Exact match.
-- Viz: grouped bar (WA vs Discord) por dia  | Alvo: n/a (monitoramento de mix de canal)  | Alerta: n/a
WITH wa AS (SELECT (received_at AT TIME ZONE 'America/Sao_Paulo')::date dia, COUNT(*) FILTER (WHERE type='messages.upsert') m FROM whatsapp_event_logs WHERE received_at >= '2026-06-14 00:00:00-03' GROUP BY 1), dc AS (SELECT (created_at AT TIME ZONE 'America/Sao_Paulo')::date dia, COUNT(*) FILTER (WHERE event_type='MESSAGE_CREATE') m FROM discord_event_logs WHERE created_at >= '2026-06-14 00:00:00-03' GROUP BY 1) SELECT COALESCE(wa.dia,dc.dia) dia, wa.m wa_msgs, dc.m dc_msgs, ROUND(dc.m::numeric/NULLIF(wa.m,0),1) dc_per_wa FROM wa FULL JOIN dc ON wa.dia=dc.dia ORDER BY 1;

-- [42] Taxa de reacao comparada (WhatsApp vs Discord, mesma janela)  (cross_platform)
-- Definição: Reaction rate de cada plataforma na janela sobreposta: WA = messages.reaction/messages.upsert; Discord = MESSAGE_REACTION_ADD/MESSAGE_CREATE. Compara engajamento qualitativo entre canais.
-- Valor medido: WA% vs Discord%: 06-15:8.3/13.7, 06-16:13.8/23.1, 06-17:12.0/15.0, 06-18:12.3/11.9, 06-19:14.1/20.0, 06-20:14.1/8.2. Discord tende a maior taxa de reacao, mas ambos na mesma ordem de grandeza (~12-20%).
-- ✔ Correção verificada: WA/DC: 06-15:8.3/13.7, 06-16:13.8/23.1, 06-17:12.0/15.0, 06-18:12.3/11.9, 06-19:14.1/20.0, 06-20:14.1/8.2. Exact match. (06-14:22.6/6.7 omitted from narrative as low-volume edge.)
-- Viz: dual line chart  | Alvo: n/a (benchmark)  | Alerta: n/a
WITH wa AS (SELECT (received_at AT TIME ZONE 'America/Sao_Paulo')::date dia, COUNT(*) FILTER (WHERE type='messages.upsert') m, COUNT(*) FILTER (WHERE type='messages.reaction') r FROM whatsapp_event_logs WHERE received_at >= '2026-06-14 00:00:00-03' GROUP BY 1), dc AS (SELECT (created_at AT TIME ZONE 'America/Sao_Paulo')::date dia, COUNT(*) FILTER (WHERE event_type='MESSAGE_CREATE') m, COUNT(*) FILTER (WHERE event_type='MESSAGE_REACTION_ADD') r FROM discord_event_logs WHERE created_at >= '2026-06-14 00:00:00-03' GROUP BY 1) SELECT COALESCE(wa.dia,dc.dia) dia, ROUND(100.0*wa.r/NULLIF(wa.m,0),1) wa_pct, ROUND(100.0*dc.r/NULLIF(dc.m,0),1) dc_pct FROM wa FULL JOIN dc ON wa.dia=dc.dia ORDER BY 1;

-- [43] Participantes distintos ativos / dia (WhatsApp)  (input)
-- Definição: Contagem de remetentes distintos (payload key.participant) que enviaram ao menos 1 mensagem no dia. Mede usuarios ativos reais, nao apenas volume.
-- Valor medido: 06-14:16, 06-15:121, 06-16:114, 06-17:89, 06-18:134, 06-19:106, 06-20:70(parcial). Pico de DAU em 06-18 (134), alinhado ao pico de onboarding (+177 net).
-- ✔ Correção verificada: 06-14:16, 06-15:121, 06-16:114, 06-17:89, 06-18:134, 06-19:106, 06-20:70(parcial). Exact match.
-- Viz: line chart DAU  | Alvo: >= 100 DAU/dia  | Alerta: < 60 em dia completo
SELECT (received_at AT TIME ZONE 'America/Sao_Paulo')::date AS dia, COUNT(DISTINCT payload->'key'->>'participant') AS participantes FROM whatsapp_event_logs WHERE type='messages.upsert' GROUP BY 1 ORDER BY 1;

-- #####################################################################
-- NICHO: Gamificacao e Economia
-- #####################################################################

-- [44] Weekly level-ups (gamification liveness)  (north_star)
-- Definição: Count of level-up events per ISO week from characters_leveling_logs, bucketed in America/Sao_Paulo. The primary heartbeat of the XP loop.
-- Valor medido: Last level-up ever recorded: 2023-03-14. Monthly shape near the end: 2023-01=1021, 2023-02=392, 2023-03=134, then ZERO. Yearly totals: 2020=89, 2021=1481, 2022=1407, 2023=1547. Current weeks (2026) = 0 level-ups. The loop is dormant, not partial.
-- ✔ Correção verificada: Confirmed. Single AT TIME ZONE (no double-convert). Monthly shape reproduced exactly: 2023-01=1021, 2023-02=392, 2023-03=134, then 0. Last level-up 2023-03-13 23:28 SP (report says 2023-03-14 = the UTC date; SP date is 2023-03-13). Total 4524 logs. Dormant since 2023-03, so 'partial current week' framing is correctly N/A.
-- Viz: Time-series line, partial current bucket flagged  | Alvo: >100 level-ups/week when system is live  | Alerta: Alert if 0 level-ups for 7+ consecutive days (currently FIRING for ~3 years)
SELECT date_trunc('week', created_at AT TIME ZONE 'America/Sao_Paulo') AS wk, COUNT(*) AS levelups, COUNT(DISTINCT character_id) AS chars FROM characters_leveling_logs GROUP BY 1 ORDER BY 1 DESC LIMIT 12;

-- [45] XP velocity (median days between level-ups)  (input)
-- Definição: Median elapsed days between consecutive level-ups for the same character (LAG over level ordering). Lower = faster progression. Computed over historical transitions only.
-- Valor medido: Median 13.92 days between level-ups, mean 79.5 days (heavy right tail), across 1,638 historical level-up transitions. All transitions are pre-2023-03; no recent velocity.
-- ✔ Correção verificada: Confirmed exactly: median 13.92 days, mean 79.50 days, 1638 transitions. ::numeric cast avoids integer truncation in percentile_cont. PARTITION BY character_id ORDER BY level with created_at>=prev guard is sound.
-- Viz: Big-number (median) + histogram of gap days  | Alvo: median 7-14 days (steady progression)  | Alerta: Alert if median > 30 days (stalled progression)
WITH paired AS (SELECT character_id, level, created_at, LAG(created_at) OVER (PARTITION BY character_id ORDER BY level) AS prev FROM characters_leveling_logs) SELECT ROUND(AVG(EXTRACT(EPOCH FROM (created_at - prev))/86400.0)::numeric,2) AS avg_days, ROUND(percentile_cont(0.5) WITHIN GROUP (ORDER BY EXTRACT(EPOCH FROM (created_at - prev))/86400.0)::numeric,2) AS median_days, COUNT(*) AS transitions FROM paired WHERE prev IS NOT NULL AND created_at >= prev;

-- [46] XP concentration (top-1% / top-10% share)  (health)
-- Definição: Share of total XP held by the top 1% and top 10% of XP-earning characters. A fairness/health signal; extreme concentration indicates the loop rewards a tiny core.
-- Valor medido: Top 1% of XP-holding characters own 50.60% of all XP; top 10% own 97.11%. Extreme concentration. Of 39,388 characters: 11,492 have 0 XP, 26,604 have <1k, 1,220 have 1k-10k, only 72 have >=10k.
-- ✔ Correção verificada: Confirmed exactly: top 1% = 50.60%, top 10% = 97.11%, over 27896 chars with XP>0. NTILE(100) over experience DESC, 100.0* and NULLIF guard correct.
-- Viz: Lorenz curve / stacked share bar  | Alvo: top 10% share < 70%  | Alerta: Alert if top 1% share > 40% (currently 50.6%, FIRING)
WITH ranked AS (SELECT experience, NTILE(100) OVER (ORDER BY experience DESC) AS pct FROM characters WHERE experience > 0) SELECT ROUND(100.0*SUM(experience) FILTER (WHERE pct=1)/NULLIF(SUM(experience),0),2) AS top1pct_share, ROUND(100.0*SUM(experience) FILTER (WHERE pct<=10)/NULLIF(SUM(experience),0),2) AS top10pct_share FROM ranked;

-- [47] Badge claim penetration  (content)
-- Definição: Share of characters that have claimed at least one badge. Measures reach of the badge/reward content.
-- Valor medido: 2.21% penetration (872 of 39,388 characters). Only ONE badge exists ('2023 Beta Tester', active=false), all 872 claims happened in a single 21-minute window on 2023-03-14 (03:52-04:13). No badge activity since.
-- ✔ Correção verificada: Confirmed: 2.21% (872/39388). Denominator safe ONLY because every character has at most one badge (verified join_rows=distinct_chars=39388). COUNT(*) on a LEFT JOIN would inflate the denominator if any character ever held multiple badges; with multiple active badges this KPI would silently over-count. Recommend COUNT(DISTINCT c.id) for the denominator to be future-proof.
-- Viz: Gauge + badge leaderboard table  | Alvo: >15% penetration with multiple active badges  | Alerta: Alert if active badges = 0 (currently 0 active badges, FIRING)
SELECT COUNT(*) AS total_chars, COUNT(DISTINCT cb.character_id) AS chars_with_badge, ROUND(100.0*COUNT(DISTINCT cb.character_id)/NULLIF(COUNT(*),0),2) AS penetration_pct FROM characters c LEFT JOIN characters_badges cb ON cb.character_id=c.id;

-- [48] Daily-bonus claim adoption  (input)
-- Definição: Share of characters that have ever claimed the daily_bonus, plus recency of last claim. Proxy for retention-loop engagement (the only economy-adjacent signal with data).
-- Valor medido: 0.889% adoption (350 of 39,388 characters ever claimed). Last claim 2023-05-20 15:49 (Sao Paulo). No daily-bonus claims in ~3 years.
-- ✔ Correção verificada: Confirmed: 0.889% adoption (350/39388 ever claimed). Single AT TIME ZONE correct. Last claim is 2023-05-20 12:49 SP, NOT 15:49. The report's '15:49 (Sao Paulo)' is the UTC value mislabeled as SP (15:49 UTC = 12:49 SP). Recency conclusion (~3 years stale) unaffected.
-- Viz: Big-number + last-claim recency badge  | Alvo: >10% of active characters claim daily  | Alerta: Alert if 0 claims in last 7 days (FIRING)
SELECT COUNT(*) AS total_chars, COUNT(*) FILTER (WHERE daily_bonus_claimed_at IS NOT NULL) AS ever_claimed, ROUND(100.0*COUNT(*) FILTER (WHERE daily_bonus_claimed_at IS NOT NULL)/NULLIF(COUNT(*),0),3) AS adoption_pct, MAX(daily_bonus_claimed_at AT TIME ZONE 'America/Sao_Paulo') AS last_claim FROM characters;

-- [49] Donator conversion rate  (business)
-- Definição: Share of users flagged is_donator, both over all users and over users who ever logged in (first_login_at not null). The monetization conversion signal.
-- Valor medido: 41 donators out of 45,723 users = 0.090% overall conversion. Of the 43 users who ever logged in (first_login_at set), 0 are donators (0.00%) — donators and the logged-in cohort are disjoint, suggesting is_donator was backfilled/imported rather than driven by the portal login flow.
-- ✔ Correção verificada: Confirmed exactly: 41 donators / 45723 users = 0.090%; 43 logged-in users (first_login_at not null); 0 of them are donators = 0.00%. Disjoint-set claim verified. NULLIF guards correct.
-- Viz: Funnel (users -> logged-in -> donators) + big-number  | Alvo: >1% donator conversion  | Alerta: Alert if conversion < 0.5%
SELECT COUNT(*) AS users, COUNT(*) FILTER (WHERE is_donator) AS donators, ROUND(100.0*COUNT(*) FILTER (WHERE is_donator)/NULLIF(COUNT(*),0),3) AS conv_all_pct, COUNT(*) FILTER (WHERE first_login_at IS NOT NULL) AS active_users, ROUND(100.0*COUNT(*) FILTER (WHERE first_login_at IS NOT NULL AND is_donator)/NULLIF(COUNT(*) FILTER (WHERE first_login_at IS NOT NULL),0),2) AS conv_active_pct FROM users;

-- [50] Coins in circulation  (business)
-- Definição: Sum of balances across all character/user wallets. Measures the monetary base of the economy.
-- Valor medido: UNMEASURABLE / 0. wallets table = 0 rows, characters_wallet = 0 rows. No coins exist in the system; the economy ledger was never populated.
-- ✔ Correção verificada: Confirmed: wallets=0 rows, characters_wallet=0 rows. COALESCE(SUM,0) correctly yields 0. Structurally unmeasurable / empty-state, correctly flagged.
-- Viz: Big-number (currently empty-state card)  | Alvo: non-zero, growing base once economy launches  | Alerta: Alert if 0 wallets exist (FIRING)
SELECT (SELECT COALESCE(SUM(balance),0) FROM wallets) AS wallets_total, (SELECT COALESCE(SUM(balance),0) FROM characters_wallet) AS characters_wallet_total, (SELECT COUNT(*) FROM wallets) AS wallet_rows, (SELECT COUNT(*) FROM characters_wallet) AS char_wallet_rows;

-- [51] Transaction volume by type  (business)
-- Definição: Count and summed amount of transactions grouped by type (earn, spend, daily_bonus, etc.) per week. The core economy throughput metric.
-- Valor medido: UNMEASURABLE / 0. transactions table = 0 rows. No transaction types, no volume.
-- ✔ Correção verificada: Confirmed: transactions=0 rows. Unmeasurable / empty-state, correctly flagged.
-- Viz: Stacked bar by type over time (empty-state card)  | Alvo: non-zero once economy launches  | Alerta: Alert if 0 transactions in last 7 days (FIRING)
SELECT type, COUNT(*) AS tx, SUM(amount) AS total_amount FROM transactions GROUP BY type ORDER BY tx DESC;

-- [52] Interaction-driven XP/coin awards  (input)
-- Definição: Volume of interactions and XP/coins awarded per week, grouped by interaction type and provider. The upstream input feeding the XP and economy loops.
-- Valor medido: UNMEASURABLE / 0. interactions table = 0 rows. The award ledger that should drive XP velocity and coin emission is empty — confirming the gamification engine is not currently writing events.
-- ✔ Correção verificada: Confirmed: interactions=0 rows. Unmeasurable / empty-state, correctly flagged.
-- Viz: Stacked area by type (empty-state card)  | Alvo: non-zero weekly award volume  | Alerta: Alert if 0 interactions in last 24h (FIRING)
SELECT type, provider, COUNT(*) AS n, SUM(xp_awarded) AS xp, SUM(coins_awarded) AS coins FROM interactions GROUP BY type, provider ORDER BY n DESC;

-- [53] Character creation vs activation gap  (health)
-- Definição: Characters are still created daily, but XP/level/badge engagement is zero. Tracks the share of characters with any XP vs total, exposing the activation gap between account creation and gamification participation.
-- Valor medido: 70.8% of characters have some XP (27,896 / 39,388) but that XP is legacy; new characters keep arriving (last 2026-06-20 21:37) while 29.2% (11,492) have 0 XP and ZERO recent level-ups exist. Growth in accounts is not converting into gamification activity.
-- ✔ Correção verificada: Confirmed exactly: 70.82% of characters have XP>0 (27896/39388), 29.2% (11492) have 0 XP, last character created 2026-06-20 18:37 SP. Single AT TIME ZONE correct.
-- Viz: Dual-axis: characters created vs level-ups over time  | Alvo: >60% of new characters earn XP within 30 days  | Alerta: Alert if new-character XP-activation < 20%
SELECT COUNT(*) AS total_chars, COUNT(*) FILTER (WHERE experience > 0) AS chars_with_xp, ROUND(100.0*COUNT(*) FILTER (WHERE experience > 0)/NULLIF(COUNT(*),0),2) AS activation_pct, MAX(created_at) AS last_char_created FROM characters;

-- #####################################################################
-- NICHO: Funil de Eventos em Tempo Real (Discord)
-- #####################################################################

-- [54] Taxa de Conversão TYPING_START → MESSAGE_CREATE (60s)  (north_star)
-- Definição: Of all typing-start events, the % where the same user emits a MESSAGE_CREATE within 60 seconds. Measures intent-to-post follow-through — the core real-time funnel for this niche.
-- Valor medido: 90.61% (29,558 of 32,622 typing events converted to a message within 60s)
-- ✔ Correção verificada: 90.61% (29,558 of 32,622) — matches exactly
-- Viz: Big-number gauge + 7-day trend line  | Alvo: > 85%  | Alerta: < 75% sustained over a complete day = users typing but not posting (friction, slow channels, or moderation deleting drafts)
WITH typing AS (SELECT user_id, created_at FROM discord_event_logs WHERE event_type='TYPING_START' AND user_id IS NOT NULL), msgs AS (SELECT user_id, created_at FROM discord_event_logs WHERE event_type='MESSAGE_CREATE' AND user_id IS NOT NULL) SELECT COUNT(*) AS typing_events, ROUND(100.0 * COUNT(*) FILTER (WHERE EXISTS (SELECT 1 FROM msgs m WHERE m.user_id=t.user_id AND m.created_at>=t.created_at AND m.created_at<=t.created_at + interval '60 seconds'))/NULLIF(COUNT(*),0),2) AS conversion_pct FROM typing t;

-- [55] Reações por Mensagem  (content)
-- Definição: MESSAGE_REACTION_ADD divided by MESSAGE_CREATE. Proxy for how much content resonates / sparks lightweight engagement.
-- Valor medido: 0.1155 reactions/message (6,644 reactions over 57,529 messages, raw). NOTE: raw msg count inflated by bot channels; human-only ratio is higher (~0.13-0.15 in weeks without the bot firehose).
-- ✔ Correção verificada: 0.1155 (6,644 / 57,529) — matches exactly
-- Viz: Weekly trend line  | Alvo: > 0.12  | Alerta: < 0.08 over a complete week = content not landing / passive audience
SELECT ROUND(COUNT(*) FILTER (WHERE event_type='MESSAGE_REACTION_ADD')::numeric / NULLIF(COUNT(*) FILTER (WHERE event_type='MESSAGE_CREATE'),0),4) AS reactions_per_msg FROM discord_event_logs;

-- [56] Taxa de Edição de Mensagens (MESSAGE_UPDATE/MESSAGE_CREATE)  (health)
-- Definição: Fraction of messages that get edited. High values can signal either care (refining posts) or churn (typos, reposts). Tracked as a health/quality signal.
-- Valor medido: 0.2783 (16,009 edits / 57,529 messages). Per-week ranges 0.18 (06-01, bot-inflated denominator) to 0.52 (05-18).
-- ✔ Correção verificada: 0.2783 (16,009 / 57,529) — matches exactly
-- Viz: Weekly trend line with band overlay  | Alvo: 0.15 – 0.30 (stable band)  | Alerta: Sudden jump > 0.50 = possible edit-spam / bot loop; sudden drop with msg spike = bot flooding the denominator
SELECT ROUND(COUNT(*) FILTER (WHERE event_type='MESSAGE_UPDATE')::numeric / NULLIF(COUNT(*) FILTER (WHERE event_type='MESSAGE_CREATE'),0),4) AS edit_rate FROM discord_event_logs;

-- [57] Taxa de Deleção de Mensagens (MESSAGE_DELETE/MESSAGE_CREATE)  (health)
-- Definição: Fraction of messages deleted. Spikes indicate moderation sweeps, spam removal, or user regret.
-- Valor medido: 0.0063 (362 deletes / 57,529 messages) — very low/healthy
-- ✔ Correção verificada: 0.0063 (362 / 57,529) — matches exactly
-- Viz: Daily trend with anomaly markers  | Alvo: < 0.02  | Alerta: > 0.05 over a day = spam wave or mass moderation action — investigate MESSAGE_DELETE_BULK too
SELECT ROUND(COUNT(*) FILTER (WHERE event_type='MESSAGE_DELETE')::numeric / NULLIF(COUNT(*) FILTER (WHERE event_type='MESSAGE_CREATE'),0),4) AS delete_rate FROM discord_event_logs;

-- [58] Volume de Eventos de Voz (VOICE_STATE_UPDATE)  (input)
-- Definição: Count of voice state transitions — proxy for synchronous (voice) engagement volume, a key real-time signal distinct from text.
-- Valor medido: 12,775 total over 32 days (~399/day avg). Evening-skewed: peaks 19h BRT (1,531) and 18h (1,025).
-- ✔ Correção verificada: 12,775 total (~399/day). Hourly peak 19h=1,531; 2nd place is 20h=1,087, NOT 18h.
-- Viz: Daily bar chart + hour-of-day heatmap  | Alvo: Maintain > 300/day  | Alerta: < 100/day on a complete day = voice channels going quiet
SELECT (created_at AT TIME ZONE 'America/Sao_Paulo')::date AS day_local, COUNT(*) AS voice_events FROM discord_event_logs WHERE event_type='VOICE_STATE_UPDATE' GROUP BY 1 ORDER BY 1 DESC;

-- [59] Adoção de Slash Commands (usuários distintos & cmds/usuário)  (business)
-- Definição: Distinct users issuing slash commands and average commands per user (INTERACTION_CREATE). Measures uptake of the bot's command surface. Actor must be read from payload->member->user->id (user_id column is NULL for these rows).
-- Valor medido: 75 distinct slash users, 240 named-command events, 3.20 commands/user over 32 days
-- ✔ Correção verificada: 75 distinct users, 240 named events, 3.20 cmds/user — matches exactly
-- Viz: Number cards (distinct users, cmds/user) + top-command bar chart  | Alvo: Growing distinct users week over week  | Alerta: Distinct slash users < 5% of DAU = bot commands undiscovered/underused
SELECT COUNT(DISTINCT COALESCE(payload->'member'->'user'->>'id', payload->'user'->>'id')) AS slash_users, COUNT(*) AS slash_events, ROUND(COUNT(*)::numeric / NULLIF(COUNT(DISTINCT COALESCE(payload->'member'->'user'->>'id', payload->'user'->>'id')),0),2) AS cmds_per_user FROM discord_event_logs WHERE event_type='INTERACTION_CREATE' AND COALESCE(payload->'data'->>'name', payload->>'name') IS NOT NULL;

-- [60] Top Slash Commands por Volume  (business)
-- Definição: Breakdown of which slash commands are actually used (payload->data->name). Drives decisions on which bot features to invest in or deprecate.
-- Valor medido: /apresentar=128 (onboarding, top), /sala=59 (voice room), /editar-perfil=21, /sala-limite=10, /perfil=7, /perguntar=4. (interaction_type=5 'unknown' n=93 are modal submits, type=3 are component interactions.)
-- ✔ Correção verificada: /apresentar=128 (63 users), /sala=59 (14), /editar-perfil=21 (6), /sala-limite=10, /perfil=7, /perguntar=4 — matches. 'unknown'=126 (not 93 as summary narrative stated).
-- Viz: Horizontal bar chart  | Alvo: n/a (distribution)  | Alerta: Onboarding command (/apresentar) dropping = funnel-top problem
SELECT COALESCE(payload->'data'->>'name', payload->>'name','unknown') AS command_name, COUNT(*) AS cnt, COUNT(DISTINCT COALESCE(payload->'member'->'user'->>'id', payload->'user'->>'id')) AS distinct_users FROM discord_event_logs WHERE event_type='INTERACTION_CREATE' GROUP BY 1 ORDER BY cnt DESC LIMIT 15;

-- [61] Throughput Horário de Eventos (hora-do-dia, BRT)  (input)
-- Definição: Total events bucketed by local hour (America/Sao_Paulo). Identifies peak engagement windows for scheduling events, announcements and moderation staffing.
-- Valor medido: Peak at 22h BRT (17,471 events / 7,456 msgs — ~30% above next hour). Strong block 11h-23h. Trough 06h-07h (~650-900). Voice peaks 19h (1,531).
-- ✔ Correção verificada: Peak 22h BRT = 17,471 events / 7,456 msgs (~30% over 21h=10,405) — matches exactly
-- Viz: 24-bar hour-of-day chart (events + voice overlay)  | Alvo: n/a (profile)  | Alerta: Flattening of the 22h peak = loss of prime-time engagement
SELECT EXTRACT(HOUR FROM created_at AT TIME ZONE 'America/Sao_Paulo')::int AS hour_local, COUNT(*) AS total_events, COUNT(*) FILTER (WHERE event_type='MESSAGE_CREATE') AS messages, COUNT(*) FILTER (WHERE event_type='VOICE_STATE_UPDATE') AS voice FROM discord_event_logs GROUP BY 1 ORDER BY 1;

-- [62] Top Canais por Volume de Evento (humano vs bot)  (content)
-- Definição: Channels ranked by total events, paired with distinct-user count to separate genuine community channels from single-author bot/webhook firehoses.
-- Valor medido: Top human channel: 853401652471398400 (22,563 events, 812 users). #1 by volume 1455191905523732510 (49,862 events, 424 users). BOT/WEBHOOK firehoses flagged: 1086368144911765647 (15,569 msgs, 1 author), 559224135251656704 (9,282 msgs, 2 authors), 540987396532207638 / 1045804587195576451 (1 author each).
-- ✔ Correção verificada: Bot firehoses confirmed: 1086368144911765647 = 15,569 msgs / 1 author; 559224135251656704 = 9,282 / 2 authors; 540987396532207638 = 1,027 / 1; 1045804587195576451 = 697 / 1. Top human channel 853401652471398400 = 22,563 events / 812 users.
-- Viz: Ranked table with a distinct-users column highlighted; flag rows where authors<=2  | Alvo: n/a (distribution)  | Alerta: A new channel appearing with thousands of events and 1 author = misbehaving bot/webhook
SELECT channel_id, COUNT(*) AS total_events, COUNT(*) FILTER (WHERE event_type='MESSAGE_CREATE') AS messages, COUNT(DISTINCT user_id) AS distinct_users FROM discord_event_logs WHERE channel_id IS NOT NULL GROUP BY channel_id ORDER BY total_events DESC LIMIT 15;

-- [63] Usuários Ativos Diários (DAU) por sinal de evento  (health)
-- Definição: Distinct user_id active per local day across text+voice+reaction events. The denominator for normalizing all engagement rates.
-- Valor medido: Recent complete days: 06-19=72, 06-18=139, 06-17=184, 06-15=170, 06-10=195. Today 06-20=38 (PARTIAL day, do not read as a drop). Range ~54-195 DAU.
-- ✔ Correção verificada: Matches: 06-19=72, 06-18=139, 06-17=184, 06-15=170, 06-10=195; today 06-20=38 (PARTIAL). Uses COUNT(DISTINCT user_id) correctly.
-- Viz: Daily line chart with the current (partial) day greyed/striped  | Alvo: > 100 on weekdays  | Alerta: < 50 on a complete weekday = engagement contraction
SELECT (created_at AT TIME ZONE 'America/Sao_Paulo')::date AS day_local, COUNT(DISTINCT user_id) AS dau, COUNT(*) FILTER (WHERE event_type='MESSAGE_CREATE') AS messages FROM discord_event_logs WHERE created_at >= '2026-05-19' GROUP BY 1 ORDER BY 1 DESC LIMIT 14;

-- #####################################################################
-- NICHO: Retencao por Coorte
-- #####################################################################

-- [64] Retenção Week-1 (coortes maduras)  (north_star)
-- Definição: % de membros de uma coorte semanal (semana da primeira atividade em messages∪voice por external_identity_id) que voltam a estar ativos na semana N+1. Agregado sobre coortes com >=2 semanas decorridas para evitar censura à direita.
-- Valor medido: 21.3% (W1 maduro). W4 maduro = 9.8%
-- ✔ Correção verificada: 21.3% (reproduced exactly)
-- Viz: big number + sparkline  | Alvo: 30%  | Alerta: < 15%
WITH activity AS (SELECT external_identity_id, sent_at AS ts FROM messages WHERE sent_at >= '2019-01-01' UNION ALL SELECT external_identity_id, occurred_at FROM voice_messages WHERE occurred_at >= '2019-01-01'), weekly AS (SELECT DISTINCT external_identity_id, date_trunc('week',(ts AT TIME ZONE 'America/Sao_Paulo'))::date AS act_week FROM activity), first_week AS (SELECT external_identity_id, MIN(act_week) AS cohort_week FROM weekly GROUP BY 1), pu AS (SELECT DISTINCT w.external_identity_id, fw.cohort_week, (w.act_week-fw.cohort_week)/7 AS week_n FROM weekly w JOIN first_week fw USING(external_identity_id)), cs AS (SELECT cohort_week, COUNT(DISTINCT external_identity_id) FILTER (WHERE week_n=0) AS size, COUNT(DISTINCT external_identity_id) FILTER (WHERE week_n=1) AS r1 FROM pu GROUP BY 1) SELECT ROUND(100.0*SUM(r1)/NULLIF(SUM(size),0),1) AS w1 FROM cs WHERE cohort_week <= (date_trunc('week', now() AT TIME ZONE 'America/Sao_Paulo')::date - INTERVAL '2 weeks');

-- [65] Curva de retenção por coorte (heatmap W0..W8)  (health)
-- Definição: Para cada uma das ~12 coortes semanais recentes, % retido em week_n = 1,2,4,8. Triângulo de retenção; células de semanas ainda não decorridas devem aparecer vazias (censura), não como 0%.
-- Valor medido: Ex.: coorte 2026-04-06 (n=59): W1 27.1%, W2 15.3%, W4 6.8%, W8 1.7%. Coorte 2026-05-04 (n=83): W1 30.1%, W2 16.9%, W4 13.3%. Coortes >= 2026-05-25 ainda censuradas em W4/W8.
-- ✔ Correção verificada: Reproduced exactly: 2026-04-06 (n=59) W1 27.1/W2 15.3/W4 6.8/W8 1.7; 2026-05-04 (n=83) W1 30.1/W2 16.9/W4 13.3
-- Viz: cohort heatmap (triângulo)  | Alvo: W4 >= 12% em todas as coortes  | Alerta: W1 < 12% em coorte madura
WITH activity AS (SELECT external_identity_id, sent_at AS ts FROM messages WHERE sent_at >= '2019-01-01' UNION ALL SELECT external_identity_id, occurred_at FROM voice_messages WHERE occurred_at >= '2019-01-01'), weekly AS (SELECT external_identity_id, date_trunc('week',(ts AT TIME ZONE 'America/Sao_Paulo'))::date AS act_week FROM activity), first_week AS (SELECT external_identity_id, MIN(act_week) AS cohort_week FROM weekly GROUP BY 1), pw AS (SELECT DISTINCT w.external_identity_id, fw.cohort_week, (w.act_week-fw.cohort_week)/7 AS week_n FROM weekly w JOIN first_week fw USING(external_identity_id)) SELECT cohort_week, COUNT(DISTINCT external_identity_id) FILTER (WHERE week_n=0) AS size, ROUND(100.0*COUNT(DISTINCT external_identity_id) FILTER (WHERE week_n=1)/NULLIF(COUNT(DISTINCT external_identity_id) FILTER (WHERE week_n=0),0),1) AS w1, ROUND(100.0*COUNT(DISTINCT external_identity_id) FILTER (WHERE week_n=2)/NULLIF(COUNT(DISTINCT external_identity_id) FILTER (WHERE week_n=0),0),1) AS w2, ROUND(100.0*COUNT(DISTINCT external_identity_id) FILTER (WHERE week_n=4)/NULLIF(COUNT(DISTINCT external_identity_id) FILTER (WHERE week_n=0),0),1) AS w4, ROUND(100.0*COUNT(DISTINCT external_identity_id) FILTER (WHERE week_n=8)/NULLIF(COUNT(DISTINCT external_identity_id) FILTER (WHERE week_n=0),0),1) AS w8 FROM pw WHERE cohort_week >= (date_trunc('week', now() AT TIME ZONE 'America/Sao_Paulo')::date - INTERVAL '12 weeks') GROUP BY 1 ORDER BY 1;

-- [66] Taxa de 'one-and-done' por coorte  (health)
-- Definição: % de membros da coorte que ficaram ativos em exatamente 1 semana (a própria semana de origem) e nunca mais. Mede falha de ativação inicial. Coortes recentes são otimisticamente censuradas (parecem piores).
-- Valor medido: Coortes maduras: 2026-03-23 63.4%, 2026-04-27 64.0%, 2026-05-04 56.6%, 2026-04-06 42.4% (melhor). Faixa típica 57-65%.
-- ✔ Correção verificada: Reproduced exactly (2026-03-23=63.4, 2026-04-27=64.0, 2026-05-04=56.6, 2026-04-06=42.4)
-- Viz: bar chart por coorte  | Alvo: < 50%  | Alerta: > 70% em coorte madura
WITH activity AS (SELECT external_identity_id, sent_at AS ts FROM messages WHERE sent_at >= '2019-01-01' UNION ALL SELECT external_identity_id, occurred_at FROM voice_messages WHERE occurred_at >= '2019-01-01'), weekly AS (SELECT DISTINCT external_identity_id, date_trunc('week',(ts AT TIME ZONE 'America/Sao_Paulo'))::date AS act_week FROM activity), first_week AS (SELECT external_identity_id, MIN(act_week) AS cohort_week FROM weekly GROUP BY 1), spans AS (SELECT w.external_identity_id, fw.cohort_week, COUNT(*) AS active_weeks FROM weekly w JOIN first_week fw USING(external_identity_id) GROUP BY 1,2) SELECT cohort_week, COUNT(*) AS size, ROUND(100.0*COUNT(*) FILTER (WHERE active_weeks=1)/COUNT(*),1) AS pct_one_and_done, ROUND(AVG(active_weeks),2) AS avg_active_weeks FROM spans WHERE cohort_week >= (date_trunc('week', now() AT TIME ZONE 'America/Sao_Paulo')::date - INTERVAL '12 weeks') GROUP BY 1 ORDER BY 1;

-- [67] Retenção W1 por canal de primeira atividade (text vs voice)  (content)
-- Definição: Compara retenção Week-1 entre membros cujo primeiro toque foi texto (messages) vs voz (voice_messages), nas coortes maduras das últimas ~12 semanas. Mostra qual porta de entrada gera vínculo mais durável.
-- Valor medido: text-first: 18.9% (n=243); voice-first: 16.3% (n=698). Voz é canal de entrada dominante em volume, mas texto retém marginalmente mais.
-- ✔ Correção verificada: Reproduced exactly: text 18.9% (n=243), voice 16.3% (n=698)
-- Viz: grouped bar  | Alvo: n/a (diagnóstico)  | Alerta: gap > 15 p.p.
WITH msg AS (SELECT external_identity_id, MIN(sent_at) AS first_msg FROM messages WHERE sent_at >= '2019-01-01' GROUP BY 1), voi AS (SELECT external_identity_id, MIN(occurred_at) AS first_voi FROM voice_messages WHERE occurred_at >= '2019-01-01' GROUP BY 1), ft AS (SELECT COALESCE(m.external_identity_id,v.external_identity_id) AS eid, LEAST(COALESCE(m.first_msg,'infinity'::timestamptz),COALESCE(v.first_voi,'infinity'::timestamptz)) AS first_ts, CASE WHEN m.first_msg IS NULL THEN 'voice' WHEN v.first_voi IS NULL THEN 'text' WHEN m.first_msg<=v.first_voi THEN 'text' ELSE 'voice' END AS channel FROM msg m FULL OUTER JOIN voi v USING(external_identity_id)), cohort AS (SELECT eid, channel, date_trunc('week', first_ts AT TIME ZONE 'America/Sao_Paulo')::date AS cohort_week FROM ft), activity AS (SELECT external_identity_id, sent_at AS ts FROM messages WHERE sent_at >= '2019-01-01' UNION ALL SELECT external_identity_id, occurred_at FROM voice_messages WHERE occurred_at >= '2019-01-01'), weekly AS (SELECT DISTINCT external_identity_id AS eid, date_trunc('week', ts AT TIME ZONE 'America/Sao_Paulo')::date AS act_week FROM activity), ret AS (SELECT c.channel, c.eid, MAX(CASE WHEN (w.act_week-c.cohort_week)/7=1 THEN 1 ELSE 0 END) AS r1 FROM cohort c JOIN weekly w ON w.eid=c.eid WHERE c.cohort_week BETWEEN (date_trunc('week', now() AT TIME ZONE 'America/Sao_Paulo')::date - INTERVAL '12 weeks') AND (date_trunc('week', now() AT TIME ZONE 'America/Sao_Paulo')::date - INTERVAL '2 weeks') GROUP BY 1,2) SELECT channel, COUNT(*) AS cohort_size, ROUND(100.0*AVG(r1),1) AS w1_retention FROM ret GROUP BY 1 ORDER BY 3 DESC;

-- [68] Distribuição de canal de aquisição (first-touch)  (input)
-- Definição: Para todos os membros históricos com atividade, classifica a primeira atividade como text-only / voice-only / text-first / voice-first. Mostra de onde vêm os novos membros ativos.
-- Valor medido: voice_only 10.732, voice_first 4.909, text_only 4.801, text_first 4.255 — ~70% entram por voz.
-- ✔ Correção verificada: Reproduced exactly: voice_only 10,732; voice_first 4,909; text_only 4,801; text_first 4,255
-- Viz: donut / stacked bar  | Alvo: n/a  | Alerta: n/a
WITH msg AS (SELECT external_identity_id, MIN(sent_at) AS first_msg FROM messages WHERE sent_at >= '2019-01-01' GROUP BY 1), voi AS (SELECT external_identity_id, MIN(occurred_at) AS first_voi FROM voice_messages WHERE occurred_at >= '2019-01-01' GROUP BY 1), combined AS (SELECT m.first_msg, v.first_voi FROM msg m FULL OUTER JOIN voi v USING(external_identity_id)) SELECT CASE WHEN first_msg IS NULL THEN 'voice_only' WHEN first_voi IS NULL THEN 'text_only' WHEN first_msg<=first_voi THEN 'text_first' ELSE 'voice_first' END AS first_touch, COUNT(*) AS members FROM combined GROUP BY 1 ORDER BY 2 DESC;

-- [69] Tamanho de coorte semanal (novos ativos/semana)  (input)
-- Definição: Nº de membros distintos cuja PRIMEIRA atividade (messages∪voice) ocorreu naquela semana. É o denominador de todas as taxas de retenção e mede o fluxo de aquisição ativada.
-- Valor medido: Últimas coortes: 2026-04-27=114, 2026-05-04=83, 2026-05-18=109, 2026-06-01=84, 2026-06-08=43, 2026-06-15=41 (PARCIAL). Faixa ~40-160.
-- ✔ Correção verificada: Reproduced exactly (2026-04-27=114, 2026-05-04=83, 2026-05-18=109, 2026-06-01=84, 2026-06-08=43, 2026-06-15=41 partial)
-- Viz: column chart, última barra hachurada (parcial)  | Alvo: n/a  | Alerta: queda > 40% vs média de 8 semanas (em semana completa)
WITH activity AS (SELECT external_identity_id, sent_at AS ts FROM messages WHERE sent_at >= '2019-01-01' UNION ALL SELECT external_identity_id, occurred_at FROM voice_messages WHERE occurred_at >= '2019-01-01'), weekly AS (SELECT external_identity_id, date_trunc('week',(ts AT TIME ZONE 'America/Sao_Paulo'))::date AS act_week FROM activity), first_week AS (SELECT external_identity_id, MIN(act_week) AS cohort_week FROM weekly GROUP BY 1) SELECT cohort_week, COUNT(*) AS new_active_members FROM first_week WHERE cohort_week >= (date_trunc('week', now() AT TIME ZONE 'America/Sao_Paulo')::date - INTERVAL '12 weeks') GROUP BY 1 ORDER BY 1;

-- [70] Vida média da coorte (avg semanas ativas)  (health)
-- Definição: Média de semanas distintas em que membros de uma coorte estiveram ativos (inclui a semana de origem). Proxy de lifetime/engajamento durável. Coortes recentes subavaliadas por censura.
-- Valor medido: Coortes maduras ~1.7-1.9 semanas (ex.: 2026-04-06=1.92, 2026-05-04=1.77, 2026-03-23=1.78). Mediana = 1 semana em praticamente todas as coortes.
-- ✔ Correção verificada: Reproduced exactly (2026-04-06=1.92, 2026-05-04=1.77, 2026-03-23=1.78; median=1 nearly everywhere)
-- Viz: line por coorte  | Alvo: >= 2.5 semanas  | Alerta: < 1.4 em coorte madura
WITH activity AS (SELECT external_identity_id, sent_at AS ts FROM messages WHERE sent_at >= '2019-01-01' UNION ALL SELECT external_identity_id, occurred_at FROM voice_messages WHERE occurred_at >= '2019-01-01'), weekly AS (SELECT DISTINCT external_identity_id, date_trunc('week',(ts AT TIME ZONE 'America/Sao_Paulo'))::date AS act_week FROM activity), first_week AS (SELECT external_identity_id, MIN(act_week) AS cohort_week FROM weekly GROUP BY 1), spans AS (SELECT w.external_identity_id, fw.cohort_week, COUNT(*) AS active_weeks FROM weekly w JOIN first_week fw USING(external_identity_id) GROUP BY 1,2) SELECT cohort_week, COUNT(*) AS size, ROUND(AVG(active_weeks),2) AS avg_active_weeks, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY active_weeks) AS median_active_weeks FROM spans WHERE cohort_week >= (date_trunc('week', now() AT TIME ZONE 'America/Sao_Paulo')::date - INTERVAL '12 weeks') GROUP BY 1 ORDER BY 1;

-- [71] WAU (usuários ativos por semana) — âncora de contexto  (health)
-- Definição: Membros distintos ativos (messages∪voice) por semana, últimas 13 semanas. Contextualiza tamanhos de coorte e expõe spikes de evento. Borda esquerda e semana atual são parciais.
-- Valor medido: Picos: 2026-03-23=558 (provável evento), 2026-05-18=430, 2026-04-27=417. Vale recente: 2026-06-08=190, 2026-06-15=186 (PARCIAL).
-- ✔ Correção verificada: Reproduced exactly (2026-03-23=558 spike, 2026-05-18=430, 2026-04-27=417, 2026-06-08=190, 2026-06-15=186 partial)
-- Viz: line chart  | Alvo: n/a (tendência)  | Alerta: queda sustentada > 30% em semanas completas
WITH activity AS (SELECT external_identity_id, sent_at AS ts FROM messages WHERE sent_at >= '2019-01-01' UNION ALL SELECT external_identity_id, occurred_at FROM voice_messages WHERE occurred_at >= '2019-01-01') SELECT date_trunc('week', ts AT TIME ZONE 'America/Sao_Paulo')::date AS wk, COUNT(DISTINCT external_identity_id) AS wau FROM activity WHERE ts >= (now() - INTERVAL '13 weeks') GROUP BY 1 ORDER BY 1;

-- [72] Novos membros Discord por semana (joined_at) — INPUT não-linkado  (input)
-- Definição: Novos membros por semana via discord_members.joined_at (is_bot=false). É o funil bruto de entrada no servidor. ATENÇÃO: não pode ser cruzado com retenção de atividade porque external_identity_id é 100% NULL em discord_members.
-- Valor medido: 2026-04-27=155, 2026-05-18=219 (pico), 2026-06-08=142, 2026-06-15=122 (parcial). Faixa ~70-220/semana.
-- ✔ Correção verificada: Reproduced exactly (2026-04-27=155, 2026-05-18=219 peak, 2026-06-08=142, 2026-06-15=122 partial)
-- Viz: column chart  | Alvo: n/a  | Alerta: queda > 40% vs média 8 semanas
SELECT date_trunc('week', joined_at AT TIME ZONE 'America/Sao_Paulo')::date AS join_week, COUNT(*) AS new_members FROM discord_members WHERE is_bot=false AND joined_at IS NOT NULL AND joined_at >= (now() - INTERVAL '13 weeks') GROUP BY 1 ORDER BY 1;

-- #####################################################################
-- NICHO: Viralidade e Conteudo
-- #####################################################################

-- [73] Avg Reactions per Reacted Message (engagement depth)  (north_star)
-- Definição: Mean reactions_total across only messages that received at least one reaction. Measures how strongly the community amplifies content that already resonated. Computed on the valid reaction window (history through 2023; degraded after).
-- Valor medido: 3.869 reactions per reacted message (lifetime); overall avg across ALL messages = 0.0877. 72,131 messages ever reacted, 279,074 total reactions.
-- ✔ Correção verificada: avg-when-reacted = 3.8690 (correct); true overall across all 2019+ messages = 0.0877 (needs separate query, not the duplicated alias)
-- Viz: Big-number stat with sparkline (yearly, since reaction data is not reliable weekly)  | Alvo: > 3.5 reactions per reacted message  | Alerta: Alert if monthly avg-when-reacted drops below 2.5 (in a window with valid reaction ingestion)
SELECT ROUND(AVG(reactions_total)::numeric,4) AS avg_reactions_when_reacted, ROUND(AVG(reactions_total)::numeric,4) AS overall FROM messages WHERE reactions_total > 0 AND COALESCE(sent_at,created_at) >= '2019-01-01';

-- [74] Reaction Rate (% of messages reacted)  (input)
-- Definição: Share of messages that received >=1 reaction. Breadth of virality. Must be read per-year because reaction ingestion degraded over time.
-- Valor medido: 2019: 2.25% | 2020: 6.69% | 2021: 1.72% | 2022: 2.05% | 2023: 0.79% | 2024: 0.78% | 2025: 0.77% | 2026: 0.04%. Clear monotonic decay = reaction ingestion gap, NOT real engagement collapse.
-- Viz: Yearly bar chart with an ingestion-gap annotation band over 2024-2026  | Alvo: Baseline ~5-7% (2020 peak); recent values invalid due to ingestion gap  | Alerta: Once ingestion is fixed, alert if a full month < 1.5%
SELECT date_trunc('year',(COALESCE(sent_at,created_at) AT TIME ZONE 'America/Sao_Paulo'))::date AS yr, COUNT(*) AS msgs, ROUND(100.0*COUNT(*) FILTER (WHERE reactions_total>0)/COUNT(*),3) AS pct_reacted FROM messages WHERE COALESCE(sent_at,created_at) >= '2019-01-01' GROUP BY 1 ORDER BY 1;

-- [75] Viral Concentration (top 1% reaction share)  (content)
-- Definição: Percentage of all reactions captured by the top 1% most-reacted messages. High value => virality driven by a few breakout posts (announcements, anniversaries) rather than broad engagement.
-- Valor medido: Top 1% of reacted messages (721 msgs) hold 24.30% of all 279,074 reactions (67,824 reactions). Heavy concentration.
-- Viz: Lorenz/concentration curve or single gauge  | Alvo: Monitor; 20-30% is typical for community announcements  | Alerta: Alert if > 50% (over-reliance on a handful of pinned/announcement posts)
SELECT ROUND(100.0 * SUM(reactions_total) FILTER (WHERE rnk <= GREATEST(1,(cnt/100))) / SUM(reactions_total),2) AS pct_held_by_top1pct FROM (SELECT reactions_total, ROW_NUMBER() OVER (ORDER BY reactions_total * -1) AS rnk, COUNT(*) OVER () AS cnt FROM messages WHERE reactions_total > 0) t;

-- [76] Viral Content Distribution (reaction buckets)  (content)
-- Definição: Histogram of messages by reaction-intensity bucket. Shows the long tail of virality and how many true 'viral' (26+ reactions) posts exist.
-- Valor medido: 0:3,111,480 | 1-2:55,673 | 3-5:8,750 | 6-10:2,850 | 11-25:2,598 | 26+ (viral):2,259. Only 2,259 lifetime 'viral' messages (0.07% of all).
-- Viz: Horizontal bar histogram (log scale)  | Alvo: n/a (distribution)  | Alerta: Track count of 26+ posts/month once ingestion fixed
SELECT CASE WHEN reactions_total=0 THEN '0' WHEN reactions_total BETWEEN 1 AND 2 THEN '1-2' WHEN reactions_total BETWEEN 3 AND 5 THEN '3-5' WHEN reactions_total BETWEEN 6 AND 10 THEN '6-10' WHEN reactions_total BETWEEN 11 AND 25 THEN '11-25' ELSE '26+' END AS bucket, COUNT(*) AS msgs FROM messages WHERE COALESCE(sent_at,created_at) >= '2019-01-01' GROUP BY 1 ORDER BY MIN(reactions_total);

-- [77] Top Shared Domains (link virality)  (content)
-- Definição: Most-shared external domains from message_embeds.source_domain. Reveals what content the community circulates. Note: only 31,273 of 993,194 embeds carry a source_domain.
-- Valor medido: youtube 19.88% (6,217) | github 11.79% (3,688) | twitter 8.31% (2,599) | tenor 4.99% | rythmbot 4.89% | spotify 4.85% | soundcloud 3.18% | coodesh 3.05% | twitch 1.93% | linkedin 1.21% | stackoverflow 1.02%. Top 3 = ~40% of all classified links.
-- Viz: Horizontal bar / treemap of top 15 domains  | Alvo: Track dev-domain share (github+stackoverflow+dev.to+MDN) as a 'learning content' proxy  | Alerta: n/a
SELECT source_domain, COUNT(*) AS shares, ROUND(100.0*COUNT(*)/SUM(COUNT(*)) OVER(),2) AS pct FROM message_embeds WHERE source_domain IS NOT NULL AND source_domain <> '' GROUP BY source_domain ORDER BY COUNT(*) * -1 LIMIT 20;

-- [78] Link Share Rate (% messages with embed)  (input)
-- Definição: Share of messages carrying at least one embed (link unfurl). Must be read on the backfill-covered window (through ~Mar 2026); zero afterward is an ingestion artifact.
-- Valor medido: Dec-2025: 20.6% | Jan-2026: 24.0% | Feb-2026: 23.8% | Mar-2026: 30.5% | Apr-2026: 0% | May-2026: 0% | Jun-2026: 0%. ~20-30% link-share is healthy; the 0% from Apr onward is the ETL backfill gap.
-- Viz: Monthly line with ingestion-gap shading on Apr-Jun 2026  | Alvo: 20-30% of messages carry a link  | Alerta: Alert if a full month = 0% (signals ETL stopped) once incremental ingestion exists
SELECT date_trunc('month',(COALESCE(m.sent_at,m.created_at) AT TIME ZONE 'America/Sao_Paulo'))::date AS mo, ROUND(100.0*COUNT(DISTINCT e.message_id)/COUNT(*),3) AS pct_link_share FROM messages m LEFT JOIN message_embeds e ON e.message_id=m.id WHERE COALESCE(m.sent_at,m.created_at) >= '2025-12-01' AND COALESCE(m.sent_at,m.created_at) < '2026-06-21' GROUP BY 1 ORDER BY 1;

-- [79] Media Share Mix (attachment content-type)  (content)
-- Definição: Distribution of attachments by top-level MIME class. Profiles what kind of media the community uploads. 70% being text/* signals pasted code/log snippets rather than images.
-- Valor medido: text 70.19% (68,942, avg 0.007MB) | NULL 16.50% (16,208, avg 0.293MB) | image 12.90% (12,675, avg 0.296MB) | video 0.25% (245, avg 7.36MB) | application 0.14% (134) | audio 0.02% (17). text/plain dominates = code/log dumps.
-- Viz: Donut chart of media classes + avg-size annotation  | Alvo: n/a (profile)  | Alerta: n/a
SELECT split_part(content_type,'/',1) AS media_class, COUNT(*) AS attachments, ROUND(100.0*COUNT(*)/SUM(COUNT(*)) OVER(),2) AS pct, ROUND(AVG(size)/1024.0/1024.0,3) AS avg_mb FROM message_attachments GROUP BY 1 ORDER BY COUNT(*) * -1;

-- [80] Reaction Rate by Message Kind  (content)
-- Definição: Which message types attract reactions. Compares default vs reply vs boost vs system messages. Behavior-changing: informs what content format to encourage.
-- Valor medido: boost: 15.69% reacted (avg 0.62) | reply: 6.72% (avg 0.127) | thread_created: 2.52% | default: 3.04% (avg 0.122) | pin: 0.16%. Boosts and replies are reacted to 2-5x more than the baseline default message.
-- Viz: Grouped bar: pct_reacted by kind  | Alvo: n/a (comparative)  | Alerta: n/a
SELECT m.kind, COUNT(*) AS msgs, ROUND(100.0*COUNT(*) FILTER (WHERE m.reactions_total>0)/COUNT(*),3) AS pct_reacted, ROUND(AVG(m.reactions_total)::numeric,4) AS avg_react FROM messages m WHERE COALESCE(m.sent_at,m.created_at) >= '2019-01-01' GROUP BY m.kind ORDER BY COUNT(*) * -1 LIMIT 10;

-- [81] Thread Creation & Archive Health  (health)
-- Definição: Number of threads spawned from messages and the share auto-archived. Thread creation is a strong signal of deep, branching conversation (high-quality content).
-- Valor medido: 251 threads total, 248 archived (98.8%), 3 active, avg auto-archive 3,317 min (~2.3 days). Thread usage is extremely rare relative to 3.29M messages (~1 thread per 13,000 messages) and snapshot-only.
-- Viz: Stat cards: total threads, % archived  | Alvo: Grow thread creation; current usage is negligible  | Alerta: n/a (volume too low to alert)
SELECT COUNT(*) AS total_threads, COUNT(*) FILTER (WHERE archived) AS archived_threads, COUNT(*) FILTER (WHERE NOT archived) AS active_threads, ROUND(100.0*COUNT(*) FILTER (WHERE archived)/COUNT(*),2) AS pct_archived, ROUND(AVG(auto_archive_duration)) AS avg_auto_archive_min FROM message_threads;

-- [82] Top Emojis (reaction vocabulary)  (content)
-- Definição: Most-used reaction emojis from the normalized activity_reactions table. Reveals community sentiment/culture (custom emojis vs unicode).
-- Valor medido: pepeOK 26,638 | he4rt 25,438 | 💤 24,421 | PogChamp 23,167 | linkpepehype 21,613 | ✅ 13,229 | Topkek 7,735 | 🔥 6,655 | peepoLove 6,042 | 👍 5,285. Custom community emojis (pepeOK, he4rt, linkpepehype) outrank unicode — strong in-group culture.
-- Viz: Emoji leaderboard / bar with emoji labels  | Alvo: n/a (culture profile)  | Alerta: n/a
SELECT emoji_name, COUNT(*) AS rows, SUM(count) AS total_reactions FROM activity_reactions GROUP BY emoji_name ORDER BY SUM(count) * -1 LIMIT 15;

-- [83] Top Viral Messages (leaderboard)  (content)
-- Definição: Highest reactions_total messages of all time. Drives a 'Hall of Fame' card and shows what content format goes viral (announcements, anniversaries, rules).
-- Valor medido: #1: 3,089 reactions (2021-03-11, rules/help post) | #2: 3,062 (2021-03-11, Discord welcome) | #3: 1,258 (2020-06-10) | #4: 1,108 (2021-03-19) | anniversary posts (2022-09-27, 479) and 'bom dia' community posts also rank high. Top virality = pinned announcements + community-moment posts.
-- Viz: Ranked table with message preview + date + reaction count  | Alvo: n/a (leaderboard)  | Alerta: n/a
SELECT m.id, (m.sent_at AT TIME ZONE 'America/Sao_Paulo')::date AS day_brt, m.reactions_total, m.reactions_count AS distinct_emojis, LEFT(REGEXP_REPLACE(COALESCE(m.content,''),'\s+',' ','g'),50) AS preview FROM messages m WHERE m.reactions_total > 0 ORDER BY m.reactions_total * -1 LIMIT 10;

-- #####################################################################
-- NICHO: Meetings e Eventos da Comunidade
-- #####################################################################

-- [84] Scheduled events created (window)  (north_star)
-- Definição: Distinct Discord GUILD_SCHEDULED_EVENT_* events seen in the ~30-day window. Proxy for the originally-intended 'meetings por mês' KPI while the meetings table is empty.
-- Valor medido: 3 distinct events in 2026-05-19..2026-06-20 (#4 Reunião Semanal, AULÃO: SYSTEM DESIGN, Reunião Delas #4)
-- ✔ Correção verificada: 3 distinct events
-- Viz: big number + sparkline  | Alvo: >= 4 events / 30d (>=1 weekly meeting + occasional aulão)  | Alerta: 0 events scheduled in a 14-day trailing window
SELECT COUNT(DISTINCT COALESCE(payload #>> '{d,id}', payload #>> '{id}', payload #>> '{guild_scheduled_event_id}', payload #>> '{d,guild_scheduled_event_id}')) AS distinct_events FROM discord_event_logs WHERE event_type LIKE 'GUILD_SCHEDULED_EVENT_%';

-- [85] Total RSVP interest signals  (input)
-- Definição: Count of GUILD_SCHEDULED_EVENT_USER_ADD events = community members marking interest in attending a scheduled event.
-- Valor medido: 40 RSVP adds
-- ✔ Correção verificada: 40 RSVP adds
-- Viz: big number  | Alvo: growth vs prior 30d  | Alerta: < 5 RSVPs over a 14-day trailing window
SELECT COUNT(*) AS rsvp_add FROM discord_event_logs WHERE event_type='GUILD_SCHEDULED_EVENT_USER_ADD';

-- [86] Unique interested users  (input)
-- Definição: Distinct users who RSVP'd to at least one scheduled event. Proxy for 'participantes únicos'.
-- Valor medido: 33 unique interested users
-- ✔ Correção verificada: 33 unique users
-- Viz: big number  | Alvo: trend up MoM  | Alerta: drop > 40% vs trailing 30d median (excluding partial week)
SELECT COUNT(DISTINCT user_id) AS unique_interested_users FROM discord_event_logs WHERE event_type='GUILD_SCHEDULED_EVENT_USER_ADD' AND user_id IS NOT NULL;

-- [87] Avg RSVPs per event (interest depth)  (business)
-- Definição: Mean number of RSVP adds per distinct scheduled event = how much interest each meeting attracts. Ratio metric, proxy for 'presença média'.
-- Valor medido: 13.33 RSVPs/event (40 adds / 3 events); but bimodal: Reunião Semanal=18, AULÃO=19, Reunião Delas=3
-- ✔ Correção verificada: 13.33 RSVPs/event
-- Viz: bar per event + avg line  | Alvo: >= 15 RSVPs/event  | Alerta: avg < 5 RSVPs/event
SELECT ROUND(COUNT(*)::numeric / NULLIF(COUNT(DISTINCT COALESCE(payload #>> '{guild_scheduled_event_id}', payload #>> '{d,guild_scheduled_event_id}')),0),2) AS avg_rsvp_per_event FROM discord_event_logs WHERE event_type='GUILD_SCHEDULED_EVENT_USER_ADD';

-- [88] RSVP cancel rate  (health)
-- Definição: USER_REMOVE / USER_ADD = share of interest signals later withdrawn. Health/quality signal for event scheduling and reminders.
-- Valor medido: 30.0% (12 removes / 40 adds)
-- ✔ Correção verificada: 30.0%
-- Viz: gauge  | Alvo: < 20%  | Alerta: > 35%
SELECT ROUND(100.0 * COUNT(*) FILTER (WHERE event_type='GUILD_SCHEDULED_EVENT_USER_REMOVE') / NULLIF(COUNT(*) FILTER (WHERE event_type='GUILD_SCHEDULED_EVENT_USER_ADD'),0),1) AS cancel_rate_pct FROM discord_event_logs WHERE event_type IN ('GUILD_SCHEDULED_EVENT_USER_ADD','GUILD_SCHEDULED_EVENT_USER_REMOVE');

-- [89] Repeat RSVP rate (cross-event loyalty)  (health)
-- Definição: Share of unique RSVPers who RSVP'd to more than one distinct event. Proxy for 'presença repetida'.
-- Valor medido: 12.1% (4 of 33 users RSVP'd to 2 events; 29 to a single event)
-- ✔ Correção verificada: 12.1% (4 of 33)
-- Viz: donut (1 event vs 2+ events)  | Alvo: > 30%  | Alerta: < 10%
SELECT ROUND(100.0 * COUNT(*) FILTER (WHERE n_events >= 2) / NULLIF(COUNT(*),0),1) AS repeat_rate_pct FROM (SELECT user_id, COUNT(DISTINCT COALESCE(payload #>> '{guild_scheduled_event_id}', payload #>> '{d,guild_scheduled_event_id}')) AS n_events FROM discord_event_logs WHERE event_type='GUILD_SCHEDULED_EVENT_USER_ADD' AND user_id IS NOT NULL GROUP BY user_id) sub;

-- [90] Weekly RSVP trend  (input)
-- Definição: RSVP adds, removes and unique users bucketed by ISO week in display tz. Trend metric; current week is PARTIAL.
-- Valor medido: 2026-05-18: 8 add/1 rem/7u | 05-25: 9/6/9 | 06-01: 9/4/8 | 06-08: 5/0/4 | 06-15: 9/1/6 (PARTIAL, week not closed). Flat ~8-9 adds/week.
-- ✔ Correção verificada: 2026-05-18:8/1/7 | 05-25:9/6/9 | 06-01:9/4/8 | 06-08:5/0/4 | 06-15:9/1/6 (PARTIAL)
-- Viz: stacked/line chart by week  | Alvo: non-declining trailing 4-week avg  | Alerta: two consecutive closed weeks below 50% of trailing avg
SELECT to_char(date_trunc('week', created_at AT TIME ZONE 'America/Sao_Paulo'), 'YYYY-MM-DD') AS week_start, COUNT(*) FILTER (WHERE event_type='GUILD_SCHEDULED_EVENT_USER_ADD') AS rsvp_add, COUNT(*) FILTER (WHERE event_type='GUILD_SCHEDULED_EVENT_USER_REMOVE') AS rsvp_remove, COUNT(DISTINCT user_id) FILTER (WHERE event_type='GUILD_SCHEDULED_EVENT_USER_ADD') AS unique_users FROM discord_event_logs WHERE event_type IN ('GUILD_SCHEDULED_EVENT_USER_ADD','GUILD_SCHEDULED_EVENT_USER_REMOVE') GROUP BY 1 ORDER BY 1;

-- [91] RSVP by day-of-week  (content)
-- Definição: Distribution of RSVP adds across weekday in display tz. Proxy for 'presença por week_day' — when interest signals land, informs scheduling.
-- Valor medido: Mon=18, Tue=2, Wed=9, Thu=5, Fri=4, Sat=1, Sun=1 — heavy Monday concentration (45% of RSVPs)
-- ✔ Correção verificada: Mon=18, Tue=2, Wed=9, Thu=5, Fri=4, Sat=1, Sun=1 (Mon=45%)
-- Viz: horizontal bar by weekday  | Alvo: informational  | Alerta: n/a
SELECT to_char(created_at AT TIME ZONE 'America/Sao_Paulo','ID') AS iso_dow, to_char(created_at AT TIME ZONE 'America/Sao_Paulo','Day') AS day_name, COUNT(*) AS rsvp_add FROM discord_event_logs WHERE event_type='GUILD_SCHEDULED_EVENT_USER_ADD' GROUP BY 1,2 ORDER BY 1;

-- [92] Per-event RSVP funnel (adds vs removes)  (business)
-- Definição: Adds and removes per scheduled event = which events retain committed attendees. Proxy for presença por meeting.
-- Valor medido: AULÃO SYSTEM DESIGN(1499..): 19 add/4 rem (21% cancel) | Reunião Semanal #4(1394..): 18 add/8 rem (44% cancel!) | Reunião Delas #4(1464..): 3 add/0 rem
-- ✔ Correção verificada: 1499..:19/4 | 1394..:18/8 | 1464..:3/0
-- Viz: grouped bar per event  | Alvo: per-event cancel < 20%  | Alerta: any event with cancel rate > 40%
SELECT COALESCE(payload #>> '{guild_scheduled_event_id}', payload #>> '{d,guild_scheduled_event_id}') AS event_id, COUNT(*) FILTER (WHERE event_type='GUILD_SCHEDULED_EVENT_USER_ADD') AS adds, COUNT(*) FILTER (WHERE event_type='GUILD_SCHEDULED_EVENT_USER_REMOVE') AS removes FROM discord_event_logs WHERE event_type IN ('GUILD_SCHEDULED_EVENT_USER_ADD','GUILD_SCHEDULED_EVENT_USER_REMOVE') GROUP BY 1 ORDER BY adds DESC;

-- [93] Legacy meetings table population (data-integrity guard)  (health)
-- Definição: Row counts of the canonical meetings tables. If zero, every legacy meeting/season/presença KPI silently returns 0 — this is a pipeline integrity alarm, not a real engagement reading.
-- Valor medido: meetings=0, participants=0, meeting_types=0, season_meeting_count=0 — ALL EMPTY
-- ✔ Correção verificada: meetings=0, participants=0, meeting_types=0, season_meeting_count=0
-- Viz: status tiles (red if 0)  | Alvo: > 0 for all (meetings should accumulate over time)  | Alerta: meetings=0 while scheduled-event RSVPs are flowing (current state — FIRING)
SELECT (SELECT COUNT(*) FROM meetings) AS meetings, (SELECT COUNT(*) FROM meeting_participants) AS participants, (SELECT COUNT(*) FROM meeting_types) AS meeting_types, (SELECT COALESCE(SUM(meeting_count),0) FROM seasons) AS season_meeting_count;

-- #####################################################################
-- NICHO: Saude de Moderacao (guarda-corpo)
-- #####################################################################

-- [94] Acoes de auto-moderacao por dia  (health)
-- Definição: Media diaria de execucoes de auto-moderacao do Discord (AUTO_MODERATION_ACTION_EXECUTION) na janela de 30 dias disponivel. Mede a carga que o guarda-corpo automatizado esta absorvendo.
-- Valor medido: 1.50 acoes/dia (45 acoes em 30 dias)
-- ✔ Correção verificada: 1.50/dia se /30 (reproduzido); 1.55/dia se normalizado pelo span real de 29 dias (45/29). Diferenca pequena, escolha de denominador.
-- Viz: big number + sparkline semanal  | Alvo: >0 e estavel (sinal de que o automod esta ativo)  | Alerta: 0 por >3 dias = automod possivelmente quebrado/desligado
SELECT round(count(*)::numeric / 30, 2) automod_per_day FROM discord_event_logs WHERE event_type='AUTO_MODERATION_ACTION_EXECUTION' AND created_at >= '2026-05-19';

-- [95] Bans por dia  (health)
-- Definição: Media diaria de GUILD_BAN_ADD na janela de 30 dias. Indicador de pressao de enforcement severo.
-- Valor medido: 1.97 bans/dia (59 bans em 30 dias; 59 usuarios distintos via payload->user->id)
-- ✔ Correção verificada: 1.97/dia se /30 (reproduzido); 2.03/dia se /29 (span real). 59 bans, 59 alvos distintos.
-- Viz: big number + barras por semana  | Alvo: baseline historico baixo e estavel  | Alerta: spike >3x a media de 7 dias = possivel raid/ataque
SELECT round(count(*)::numeric / 30, 2) bans_per_day FROM discord_event_logs WHERE event_type='GUILD_BAN_ADD' AND created_at >= '2026-05-19';

-- [96] Concentracao do auto-mod (acoes por alvo)  (health)
-- Definição: Acoes de auto-mod divididas por usuarios-alvo distintos. Alto valor = poucos reincidentes consomem o automod; baixo = problema difuso.
-- Valor medido: 5.63 acoes/alvo (45 acoes em 8 alvos distintos)
-- ✔ Correção verificada: 5.63 acoes/alvo (45 acoes / 8 alvos distintos) — reproduzido exatamente. user_id da coluna esta preenchido aqui (0 NULLs) e bate com o payload.
-- Viz: gauge + tabela top-ofensores  | Alvo: proximo de 1.0 (acoes pulverizadas)  | Alerta: >5 = reincidencia alta, considerar ban/escalonamento manual
SELECT count(DISTINCT user_id) distinct_targets, count(*) actions, round(count(*)::numeric/NULLIF(count(DISTINCT user_id),0),2) actions_per_target FROM discord_event_logs WHERE event_type='AUTO_MODERATION_ACTION_EXECUTION' AND created_at >= '2026-05-19';

-- [97] Acoes de auto-mod por semana (tendencia)  (input)
-- Definição: Volume semanal de automod e bans no display tz. Semana atual e PARCIAL.
-- Valor medido: 2026-05-18: automod=8,bans=2 | 05-25: 2,17 | 06-01: 1,9 | 06-08: 8,11 | 06-15(PARCIAL): 26,20
-- ✔ Correção verificada: 2026-05-18: automod=8,bans=2 | 05-25: 2,17 | 06-01: 1,9 | 06-08: 8,11 | 06-15(PARCIAL): 26,20 — reproduzido exatamente.
-- Viz: linha dupla (automod vs bans), ultima barra hachurada (parcial)  | Alvo: estavel; spikes investigados  | Alerta: semana completa >3x mediana das 4 anteriores
SELECT date_trunc('week', created_at AT TIME ZONE 'America/Sao_Paulo')::date wk, count(*) FILTER (WHERE event_type='AUTO_MODERATION_ACTION_EXECUTION') automod, count(*) FILTER (WHERE event_type='GUILD_BAN_ADD') bans FROM discord_event_logs WHERE event_type IN ('AUTO_MODERATION_ACTION_EXECUTION','GUILD_BAN_ADD') AND created_at >= '2026-05-19' GROUP BY 1 ORDER BY 1;

-- [98] Taxa de reversao (unban/ban)  (health)
-- Definição: Proporcao de unbans sobre bans no historico de moderation_events. Proxy de falsos-positivos / arrependimento de enforcement.
-- Valor medido: 2.22% (9 unbans / 406 bans)
-- ✔ Correção verificada: 2.22% (9 unbans / 406 bans) — reproduzido exatamente.
-- Viz: big number + meta  | Alvo: <5% (poucos bans revertidos = decisoes corretas)  | Alerta: >10% = enforcement impreciso / muitos falsos positivos
SELECT count(*) FILTER (WHERE type='ban') bans, count(*) FILTER (WHERE type='unban') unbans, round(count(*) FILTER (WHERE type='unban')::numeric / NULLIF(count(*) FILTER (WHERE type='ban'),0) * 100, 2) reversal_pct FROM moderation_events WHERE occurred_at >= '2019-01-01';

-- [99] Cobertura de motivo de moderacao  (health)
-- Definição: % de eventos de moderacao que tem reason preenchido. Mede a qualidade do registro (auditabilidade do guarda-corpo).
-- Valor medido: 37.18% (177 de 476 eventos com reason)
-- ✔ Correção verificada: 37.18% (177/476) — reproduzido exatamente.
-- Viz: gauge  | Alvo: >90% (toda acao deve ter justificativa)  | Alerta: <70% = lacuna de auditabilidade
SELECT round(count(*) FILTER (WHERE reason IS NOT NULL)::numeric / count(*) * 100,2) reason_coverage_pct FROM moderation_events WHERE occurred_at >= '2019-01-01';

-- [100] Gatilhos de auto-mod por tipo de regra  (content)
-- Definição: Quais regras do Discord automod disparam mais (rule_trigger_type no payload: 3=keyword preset/spam, 1=keyword custom).
-- Valor medido: rule_trigger_type=3: 34 acoes | rule_trigger_type=1: 11 acoes (total 45)
-- ✔ Correção verificada: rule_trigger_type=3: 34 | rule_trigger_type=1: 11 (total 45) — reproduzido exatamente.
-- Viz: barras por tipo de regra  | Alvo: n/a (diagnostico)  | Alerta: uma regra dominando pode indicar ataque coordenado
SELECT payload->>'rule_trigger_type' rule_type, count(*) n FROM discord_event_logs WHERE event_type='AUTO_MODERATION_ACTION_EXECUTION' AND created_at >= '2026-05-19' GROUP BY 1 ORDER BY 2 DESC;