init.sql

-- uazapi-webhook database schema

-- Tabela principal: fila persistente de mensagens brutas
CREATE TABLE raw_messages (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    uazapi_message_id VARCHAR(255) UNIQUE,
    instance_id VARCHAR(100),
    sender_jid VARCHAR(100) NOT NULL,
    chat_jid VARCHAR(100) NOT NULL,
    message_type VARCHAR(50),
    payload JSONB NOT NULL,
    received_at TIMESTAMPTZ DEFAULT NOW(),
    status VARCHAR(20) DEFAULT 'queued',
    retry_count INT DEFAULT 0,
    processed_at TIMESTAMPTZ,
    error_message TEXT
);

-- Tabela de pacotes agrupados (resultado do debounce)
CREATE TABLE message_groups (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    sender_jid VARCHAR(100) NOT NULL,
    chat_jid VARCHAR(100) NOT NULL,
    messages JSONB NOT NULL,
    message_count INT NOT NULL,
    first_message_at TIMESTAMPTZ NOT NULL,
    last_message_at TIMESTAMPTZ NOT NULL,
    grouped_at TIMESTAMPTZ DEFAULT NOW(),
    status VARCHAR(20) DEFAULT 'pending',
    dispatch_target VARCHAR(100),
    dispatch_result JSONB,
    dispatched_at TIMESTAMPTZ
);

-- Tabela de configuracao de rotas
CREATE TABLE webhook_routes (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR(100) NOT NULL,
    chat_jid_filter VARCHAR(100),
    message_type_filter VARCHAR(50),
    destination_type VARCHAR(50) NOT NULL,
    destination_config JSONB NOT NULL,
    enabled BOOLEAN DEFAULT true,
    priority INT DEFAULT 0,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Tabela de dead letters
CREATE TABLE dead_letters (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    raw_message_id UUID REFERENCES raw_messages(id),
    message_group_id UUID REFERENCES message_groups(id),
    error_message TEXT,
    failed_at TIMESTAMPTZ DEFAULT NOW()
);

-- Indexes
CREATE INDEX idx_raw_messages_status ON raw_messages(status);
CREATE INDEX idx_raw_messages_sender_jid ON raw_messages(sender_jid);
CREATE INDEX idx_raw_messages_chat_jid ON raw_messages(chat_jid);
CREATE INDEX idx_raw_messages_received_at ON raw_messages(received_at);
CREATE INDEX idx_message_groups_status ON message_groups(status);
CREATE INDEX idx_message_groups_sender_jid ON message_groups(sender_jid);
CREATE INDEX idx_message_groups_chat_jid ON message_groups(chat_jid);
CREATE INDEX idx_dead_letters_failed_at ON dead_letters(failed_at);

-- Rota default: log-only
INSERT INTO webhook_routes (name, destination_type, destination_config, enabled, priority)
VALUES ('log-only', 'log', '{}', true, 0);