34 lines
1.6 KiB
SQL
34 lines
1.6 KiB
SQL
-- ============================================================================
|
|
-- MIGRATION: Add message read tracking
|
|
-- ============================================================================
|
|
-- Run this migration to add the read_at field to messages table
|
|
-- This enables tracking of read/unread messages for notifications
|
|
-- Date: 2025-12-17
|
|
-- ============================================================================
|
|
|
|
-- Add read_at column to messages table if it doesn't exist
|
|
ALTER TABLE messages ADD COLUMN IF NOT EXISTS read_at TIMESTAMP DEFAULT NULL;
|
|
|
|
-- Create index on read_at for faster unread message queries
|
|
CREATE INDEX IF NOT EXISTS idx_messages_read_at ON messages(read_at);
|
|
|
|
-- Create index for unread message queries (conversation + read status + sender)
|
|
CREATE INDEX IF NOT EXISTS idx_messages_unread ON messages(conversation_id, read_at, sender_id);
|
|
|
|
-- ============================================================================
|
|
-- VERIFICATION QUERIES (Run these to verify the changes)
|
|
-- ============================================================================
|
|
-- Check if read_at column exists:
|
|
-- SELECT column_name, data_type FROM information_schema.columns
|
|
-- WHERE table_name='messages' AND column_name='read_at';
|
|
|
|
-- Count messages with and without read_at:
|
|
-- SELECT
|
|
-- COUNT(*) as total_messages,
|
|
-- COUNT(CASE WHEN read_at IS NOT NULL THEN 1 END) as read_messages,
|
|
-- COUNT(CASE WHEN read_at IS NULL THEN 1 END) as unread_messages
|
|
-- FROM messages;
|
|
|
|
-- Check indexes were created:
|
|
-- SELECT indexname FROM pg_indexes WHERE tablename='messages';
|