josedario87
80d0042c7e
All checks were successful
build-and-deploy / build-and-deploy (push) Successful in 1m4s
- Agregar botón "Crear Webhook de Debug" en WebhookReceiverSection - Detectar si ya existe un webhook apuntando al receptor de debug - Permitir eliminar el webhook de debug - Incluir todos los eventos disponibles al crear el webhook - También incluye mejoras previas de manejo de media y mensajes
86 lines
3.4 KiB
SQL
86 lines
3.4 KiB
SQL
-- =====================================================
|
|
-- Migration 002: Enhanced Messages Support
|
|
-- =====================================================
|
|
-- Adds fields for:
|
|
-- - Group participant tracking (participant_jid, push_name)
|
|
-- - Media caching (media_cached, media_local_path, media_size_bytes)
|
|
-- - Message reactions
|
|
-- - Presence caching
|
|
-- =====================================================
|
|
|
|
-- Add participant tracking fields to messages
|
|
ALTER TABLE messages ADD COLUMN IF NOT EXISTS participant_jid VARCHAR(100);
|
|
ALTER TABLE messages ADD COLUMN IF NOT EXISTS push_name VARCHAR(255);
|
|
|
|
-- Add media caching fields
|
|
ALTER TABLE messages ADD COLUMN IF NOT EXISTS media_cached BOOLEAN DEFAULT FALSE;
|
|
ALTER TABLE messages ADD COLUMN IF NOT EXISTS media_local_path TEXT;
|
|
ALTER TABLE messages ADD COLUMN IF NOT EXISTS media_size_bytes BIGINT;
|
|
|
|
-- Create table for message reactions
|
|
CREATE TABLE IF NOT EXISTS message_reactions (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
message_id UUID NOT NULL REFERENCES messages(id) ON DELETE CASCADE,
|
|
instance_id UUID NOT NULL REFERENCES instances(id) ON DELETE CASCADE,
|
|
reactor_jid VARCHAR(100) NOT NULL,
|
|
reactor_name VARCHAR(255),
|
|
emoji VARCHAR(20) NOT NULL,
|
|
timestamp TIMESTAMPTZ DEFAULT NOW(),
|
|
|
|
CONSTRAINT unique_reaction UNIQUE (message_id, reactor_jid)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_reactions_message ON message_reactions(message_id);
|
|
CREATE INDEX IF NOT EXISTS idx_reactions_instance ON message_reactions(instance_id);
|
|
|
|
-- Create table for presence caching (optional, for "last seen" persistence)
|
|
CREATE TABLE IF NOT EXISTS presence_cache (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
instance_id UUID NOT NULL REFERENCES instances(id) ON DELETE CASCADE,
|
|
jid VARCHAR(100) NOT NULL,
|
|
presence VARCHAR(20) CHECK (presence IN ('available', 'unavailable', 'composing', 'recording', 'paused')),
|
|
last_seen TIMESTAMPTZ,
|
|
updated_at TIMESTAMPTZ DEFAULT NOW(),
|
|
|
|
CONSTRAINT unique_presence UNIQUE (instance_id, jid)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_presence_instance ON presence_cache(instance_id);
|
|
|
|
-- Create table for group metadata caching
|
|
CREATE TABLE IF NOT EXISTS group_metadata (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
instance_id UUID NOT NULL REFERENCES instances(id) ON DELETE CASCADE,
|
|
jid VARCHAR(100) NOT NULL,
|
|
subject VARCHAR(255),
|
|
description TEXT,
|
|
owner_jid VARCHAR(100),
|
|
participants JSONB DEFAULT '[]',
|
|
announce_only BOOLEAN DEFAULT FALSE,
|
|
restrict_edit BOOLEAN DEFAULT FALSE,
|
|
created_at TIMESTAMPTZ DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ DEFAULT NOW(),
|
|
|
|
CONSTRAINT unique_group UNIQUE (instance_id, jid)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_group_metadata_instance ON group_metadata(instance_id);
|
|
|
|
-- Add trigger for presence_cache updated_at
|
|
CREATE TRIGGER update_presence_cache_updated_at
|
|
BEFORE UPDATE ON presence_cache
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION update_updated_at_column();
|
|
|
|
-- Add trigger for group_metadata updated_at
|
|
CREATE TRIGGER update_group_metadata_updated_at
|
|
BEFORE UPDATE ON group_metadata
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION update_updated_at_column();
|
|
|
|
-- Add index for media caching lookups
|
|
CREATE INDEX IF NOT EXISTS idx_messages_media_cached ON messages(media_cached) WHERE media_cached = TRUE;
|
|
|
|
-- Add last_message_type to chats for preview icons
|
|
ALTER TABLE chats ADD COLUMN IF NOT EXISTS last_message_type VARCHAR(50);
|