-- ============================================================================= -- NordaBiz - Migration: News Tables -- ============================================================================= -- Created: 2025-12-29 -- Description: Creates tables for company news/mentions and user notifications -- ============================================================================= -- ============================================================================= -- TABLE: company_news -- ============================================================================= -- Stores news and mentions for companies. -- Sources: web search (Brave), social media, press releases, awards. -- Supports AI filtering and manual moderation. CREATE TABLE IF NOT EXISTS company_news ( id SERIAL PRIMARY KEY, -- Company reference company_id INTEGER NOT NULL REFERENCES companies(id) ON DELETE CASCADE, -- News content title VARCHAR(500) NOT NULL, -- News headline summary TEXT, -- Short description/excerpt content TEXT, -- Full content if available -- Source information source_url VARCHAR(1000), -- Original article/post URL source_name VARCHAR(255), -- e.g., "Gazeta Wyborcza", "Facebook", "LinkedIn" source_type VARCHAR(50), -- web, facebook, linkedin, instagram, press, award -- News classification news_type VARCHAR(50) DEFAULT 'news_mention', -- Supported types: -- news_mention - General media mention -- press_release - Official company press release -- award - Award or recognition -- social_post - Social media post -- event - Event announcement -- financial - Financial news -- partnership - Partnership or collaboration announcement -- Dates published_date TIMESTAMP, -- When the news was originally published discovered_at TIMESTAMP DEFAULT NOW(), -- When our system found it -- AI filtering and relevance is_approved BOOLEAN DEFAULT FALSE, -- Passed AI filter and is approved for display is_visible BOOLEAN DEFAULT TRUE, -- Visible on company profile (can be hidden by user) relevance_score NUMERIC(3,2), -- 0.00 - 1.00 (AI calculated relevance) ai_summary TEXT, -- Gemini-generated summary ai_tags TEXT[], -- AI-extracted tags (PostgreSQL array) -- Moderation workflow moderation_status VARCHAR(20) DEFAULT 'pending', -- pending, approved, rejected moderated_by INTEGER REFERENCES users(id), -- Admin who moderated moderated_at TIMESTAMP, -- When moderation happened rejection_reason VARCHAR(255), -- If rejected, why -- Engagement tracking view_count INTEGER DEFAULT 0, -- Number of views on platform -- Timestamps created_at TIMESTAMP DEFAULT NOW(), updated_at TIMESTAMP DEFAULT NOW(), -- Unique constraint: prevent duplicate news for same company CONSTRAINT uq_company_news_url UNIQUE (company_id, source_url) ); -- Indexes for company_news CREATE INDEX IF NOT EXISTS idx_company_news_company_id ON company_news(company_id); CREATE INDEX IF NOT EXISTS idx_company_news_source_type ON company_news(source_type); CREATE INDEX IF NOT EXISTS idx_company_news_news_type ON company_news(news_type); CREATE INDEX IF NOT EXISTS idx_company_news_is_approved ON company_news(is_approved); CREATE INDEX IF NOT EXISTS idx_company_news_published_date ON company_news(published_date DESC); CREATE INDEX IF NOT EXISTS idx_company_news_discovered_at ON company_news(discovered_at DESC); CREATE INDEX IF NOT EXISTS idx_company_news_moderation ON company_news(moderation_status); -- Composite index for efficient querying of approved, visible news CREATE INDEX IF NOT EXISTS idx_company_news_approved_visible ON company_news(company_id, is_approved, is_visible) WHERE is_approved = TRUE AND is_visible = TRUE; -- ============================================================================= -- TABLE: user_notifications -- ============================================================================= -- In-app notifications for users. -- Supports notification center and unread badges. CREATE TABLE IF NOT EXISTS user_notifications ( id SERIAL PRIMARY KEY, -- User reference user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE, -- Notification content title VARCHAR(255) NOT NULL, -- Notification title message TEXT, -- Full notification message notification_type VARCHAR(50) DEFAULT 'info', -- Supported types: -- news - New company news -- system - System announcements -- message - Private message notification -- event - Event reminder/update -- alert - Important alert -- Related entity (optional polymorphic reference) related_type VARCHAR(50), -- company_news, event, message, etc. related_id INTEGER, -- ID of related entity -- Status is_read BOOLEAN DEFAULT FALSE, -- Has user read the notification? read_at TIMESTAMP, -- When was it read? -- Action action_url VARCHAR(500), -- URL to navigate when clicked -- Timestamps created_at TIMESTAMP DEFAULT NOW() ); -- Indexes for user_notifications CREATE INDEX IF NOT EXISTS idx_user_notifications_user_id ON user_notifications(user_id); CREATE INDEX IF NOT EXISTS idx_user_notifications_type ON user_notifications(notification_type); CREATE INDEX IF NOT EXISTS idx_user_notifications_is_read ON user_notifications(is_read); CREATE INDEX IF NOT EXISTS idx_user_notifications_created_at ON user_notifications(created_at DESC); -- Composite index for unread notifications badge (most common query) CREATE INDEX IF NOT EXISTS idx_user_notifications_unread ON user_notifications(user_id, is_read, created_at DESC) WHERE is_read = FALSE; -- ============================================================================= -- COMMENTS -- ============================================================================= COMMENT ON TABLE company_news IS 'News and mentions for companies from various sources (web, social media, press)'; COMMENT ON TABLE user_notifications IS 'In-app notifications for users with read/unread tracking'; -- company_news columns COMMENT ON COLUMN company_news.company_id IS 'FK to companies table'; COMMENT ON COLUMN company_news.title IS 'News headline (max 500 chars)'; COMMENT ON COLUMN company_news.summary IS 'Short excerpt or description'; COMMENT ON COLUMN company_news.content IS 'Full article content if scraped'; COMMENT ON COLUMN company_news.source_url IS 'Original URL of the news article'; COMMENT ON COLUMN company_news.source_name IS 'Name of the source (e.g., Gazeta Wyborcza)'; COMMENT ON COLUMN company_news.source_type IS 'Type of source: web, facebook, linkedin, instagram, press, award'; COMMENT ON COLUMN company_news.news_type IS 'Classification: news_mention, press_release, award, social_post, event, financial, partnership'; COMMENT ON COLUMN company_news.published_date IS 'Original publication date'; COMMENT ON COLUMN company_news.discovered_at IS 'When our system found this news'; COMMENT ON COLUMN company_news.is_approved IS 'TRUE if passed AI filter and approved for display'; COMMENT ON COLUMN company_news.is_visible IS 'TRUE if visible on company profile'; COMMENT ON COLUMN company_news.relevance_score IS 'AI-calculated relevance score (0.00-1.00)'; COMMENT ON COLUMN company_news.ai_summary IS 'Gemini-generated summary'; COMMENT ON COLUMN company_news.ai_tags IS 'Array of AI-extracted tags'; COMMENT ON COLUMN company_news.moderation_status IS 'Status: pending, approved, rejected'; COMMENT ON COLUMN company_news.moderated_by IS 'Admin user ID who moderated'; COMMENT ON COLUMN company_news.moderated_at IS 'Timestamp of moderation'; COMMENT ON COLUMN company_news.rejection_reason IS 'Reason if rejected'; COMMENT ON COLUMN company_news.view_count IS 'Number of views on platform'; -- user_notifications columns COMMENT ON COLUMN user_notifications.user_id IS 'FK to users table'; COMMENT ON COLUMN user_notifications.title IS 'Notification title (max 255 chars)'; COMMENT ON COLUMN user_notifications.message IS 'Full notification message'; COMMENT ON COLUMN user_notifications.notification_type IS 'Type: news, system, message, event, alert'; COMMENT ON COLUMN user_notifications.related_type IS 'Type of related entity (company_news, event, message)'; COMMENT ON COLUMN user_notifications.related_id IS 'ID of related entity'; COMMENT ON COLUMN user_notifications.is_read IS 'TRUE if user has read the notification'; COMMENT ON COLUMN user_notifications.read_at IS 'Timestamp when notification was read'; COMMENT ON COLUMN user_notifications.action_url IS 'URL to navigate when clicked'; -- ============================================================================= -- MIGRATION COMPLETE -- ============================================================================= -- To run this migration: -- psql -h 10.22.68.249 -U nordabiz_app -d nordabiz -f migrate_news_tables.sql -- -- Or via Python: -- python migrate_add_news_tables.py -- =============================================================================