-- ============================================================================ -- 08 Platform services: social publishing, notifications, search, RSS pings, -- support desk, Sniffer AI, sharing, referrals, undo-delete trash -- ============================================================================ -- ---------------------------------------------------------------------------- -- Social auto-posting: OFFICIAL Absurdities NG accounts only, after approval only -- ---------------------------------------------------------------------------- CREATE TABLE social_accounts ( id smallint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, platform varchar(12) NOT NULL UNIQUE CHECK (platform IN ('youtube','facebook','instagram','x','tiktok')), account_label varchar(100), external_account_id varchar(100), access_token_encrypted text, refresh_token_encrypted text, token_expires_at timestamptz, scopes text[] NOT NULL DEFAULT '{}', is_enabled boolean NOT NULL DEFAULT false, connection_status varchar(12) NOT NULL DEFAULT 'disconnected' CHECK (connection_status IN ('disconnected','connected','expired','error')), connected_by_staff_id bigint REFERENCES staff_users(id), connected_at timestamptz, last_error varchar(300), updated_at timestamptz NOT NULL DEFAULT now() ); CREATE TABLE social_caption_templates ( id smallint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, platform varchar(12) NOT NULL CHECK (platform IN ('youtube','facebook','instagram','x','tiktok')), name varchar(60) NOT NULL, template text NOT NULL, -- {title} {state} {lga} {link} {hashtags} {officials} is_default boolean NOT NULL DEFAULT false, UNIQUE (platform, name) ); CREATE TABLE social_publish_jobs ( id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, post_id bigint NOT NULL REFERENCES posts(id) ON DELETE CASCADE, platform varchar(12) NOT NULL CHECK (platform IN ('youtube','facebook','instagram','x','tiktok')), status varchar(12) NOT NULL DEFAULT 'queued' CHECK (status IN ('queued','publishing','published','failed','skipped','withdrawn')), skip_reason varchar(60), -- pending, anonymous_opt_out, legal_hold, blocked ... caption text, external_post_id varchar(120), external_url varchar(400), attempts smallint NOT NULL DEFAULT 0, last_error varchar(400), scheduled_for timestamptz NOT NULL DEFAULT now(), published_at timestamptz, created_at timestamptz NOT NULL DEFAULT now(), UNIQUE (post_id, platform) ); CREATE INDEX social_jobs_queue_idx ON social_publish_jobs (status, scheduled_for) WHERE status IN ('queued','failed'); -- ---------------------------------------------------------------------------- -- Push notifications (FCM for Flutter, Web Push for browsers) and in-app inbox -- ---------------------------------------------------------------------------- CREATE TABLE push_devices ( id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, user_id bigint REFERENCES users(id) ON DELETE CASCADE, platform varchar(8) NOT NULL CHECK (platform IN ('android','ios','web')), token text NOT NULL UNIQUE, -- FCM token or Web Push endpoint keys jsonb, -- Web Push p256dh/auth app_version varchar(20), locale varchar(8), state_id smallint, is_active boolean NOT NULL DEFAULT true, last_seen_at timestamptz NOT NULL DEFAULT now(), created_at timestamptz NOT NULL DEFAULT now() ); CREATE INDEX push_devices_user_idx ON push_devices (user_id) WHERE is_active; CREATE TABLE notification_types ( code varchar(40) PRIMARY KEY, -- post_approved, claim_made, comment_reply ... label varchar(80) NOT NULL, default_push boolean NOT NULL DEFAULT true, default_email boolean NOT NULL DEFAULT false, default_in_app boolean NOT NULL DEFAULT true ); CREATE TABLE notifications ( id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, user_id bigint NOT NULL REFERENCES users(id) ON DELETE CASCADE, type varchar(40) NOT NULL REFERENCES notification_types(code), title varchar(120) NOT NULL, body varchar(300), data jsonb NOT NULL DEFAULT '{}'::jsonb, -- deep link target, never anonymous-author details read_at timestamptz, pushed_at timestamptz, created_at timestamptz NOT NULL DEFAULT now() ); CREATE INDEX notifications_user_idx ON notifications (user_id, created_at DESC); CREATE INDEX notifications_unread_idx ON notifications (user_id) WHERE read_at IS NULL; CREATE TABLE notification_preferences ( user_id bigint NOT NULL REFERENCES users(id) ON DELETE CASCADE, type varchar(40) NOT NULL REFERENCES notification_types(code), push boolean NOT NULL DEFAULT true, email boolean NOT NULL DEFAULT false, in_app boolean NOT NULL DEFAULT true, PRIMARY KEY (user_id, type) ); -- Quiet hours live in user_profiles.notification_prefs (jsonb). CREATE TABLE broadcasts ( -- admin-sent targeted notifications id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, title varchar(120) NOT NULL, body varchar(300) NOT NULL, audience jsonb NOT NULL DEFAULT '{}'::jsonb, -- {"state_ids":[..],"min_trust_level":1} sent_by_staff_id bigint NOT NULL REFERENCES staff_users(id), scheduled_for timestamptz, sent_at timestamptz, recipients_count integer NOT NULL DEFAULT 0, created_at timestamptz NOT NULL DEFAULT now() ); -- ---------------------------------------------------------------------------- -- Search (open to everyone, including logged-out visitors) -- ---------------------------------------------------------------------------- CREATE TABLE search_queries ( id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, query_text varchar(200) NOT NULL, normalized varchar(200) NOT NULL, results_count integer NOT NULL DEFAULT 0, filters jsonb NOT NULL DEFAULT '{}'::jsonb, session_hash char(16), -- no raw user id: analytics only clicked_result_type varchar(12), clicked_result_id bigint, created_at timestamptz NOT NULL DEFAULT now() ); CREATE INDEX search_queries_normalized_idx ON search_queries (normalized, created_at DESC); CREATE INDEX search_queries_zero_idx ON search_queries (created_at DESC) WHERE results_count = 0; CREATE TABLE user_search_history ( -- members only, user-clearable id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, user_id bigint NOT NULL REFERENCES users(id) ON DELETE CASCADE, query_text varchar(200) NOT NULL, created_at timestamptz NOT NULL DEFAULT now() ); CREATE INDEX user_search_history_idx ON user_search_history (user_id, created_at DESC); CREATE TABLE saved_searches ( id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, user_id bigint NOT NULL REFERENCES users(id) ON DELETE CASCADE, name varchar(80) NOT NULL, query_text varchar(200), filters jsonb NOT NULL DEFAULT '{}'::jsonb, notify boolean NOT NULL DEFAULT true, last_notified_at timestamptz, created_at timestamptz NOT NULL DEFAULT now() ); CREATE TABLE search_synonyms ( -- Pidgin, spellings, NEPA/PHCN/DisCo ... id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY, term citext NOT NULL, synonym citext NOT NULL, UNIQUE (term, synonym) ); CREATE TABLE search_stop_words ( word citext PRIMARY KEY ); -- ---------------------------------------------------------------------------- -- RSS: outbound ping list (admin text area, one provider per line) -- ---------------------------------------------------------------------------- CREATE TABLE ping_providers ( id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY, url varchar(400) NOT NULL UNIQUE, ping_type varchar(10) NOT NULL DEFAULT 'auto' CHECK (ping_type IN ('auto','xmlrpc','websub','indexnow')), sort_order integer NOT NULL DEFAULT 0, is_enabled boolean NOT NULL DEFAULT true, -- a leading "#" in the text area disables a line last_pinged_at timestamptz, last_status varchar(20), last_error varchar(300), created_at timestamptz NOT NULL DEFAULT now() ); CREATE TABLE ping_log ( id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, provider_id integer NOT NULL REFERENCES ping_providers(id) ON DELETE CASCADE, post_id bigint REFERENCES posts(id) ON DELETE SET NULL, http_status integer, success boolean NOT NULL, response_excerpt varchar(300), duration_ms integer, created_at timestamptz NOT NULL DEFAULT now() ); CREATE INDEX ping_log_provider_idx ON ping_log (provider_id, created_at DESC); -- ---------------------------------------------------------------------------- -- Support desk (Supervisors): tickets, messages, live chat -- ---------------------------------------------------------------------------- CREATE TABLE support_tickets ( id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, ticket_number varchar(16) NOT NULL UNIQUE, user_id bigint REFERENCES users(id) ON DELETE SET NULL, requester_email citext, subject varchar(160) NOT NULL, category varchar(20) NOT NULL DEFAULT 'general' CHECK (category IN ('general','account','post','advertiser','payment','shop','legal','bug')), priority varchar(8) NOT NULL DEFAULT 'normal' CHECK (priority IN ('low','normal','high','urgent')), status varchar(10) NOT NULL DEFAULT 'open' CHECK (status IN ('open','pending','resolved','closed')), assigned_staff_id bigint REFERENCES staff_users(id), channel varchar(10) NOT NULL DEFAULT 'web' CHECK (channel IN ('web','app','email','chat')), created_at timestamptz NOT NULL DEFAULT now(), updated_at timestamptz NOT NULL DEFAULT now(), resolved_at timestamptz ); CREATE INDEX support_tickets_queue_idx ON support_tickets (status, priority, created_at); CREATE TABLE ticket_messages ( id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, ticket_id bigint NOT NULL REFERENCES support_tickets(id) ON DELETE CASCADE, sender_type varchar(6) NOT NULL CHECK (sender_type IN ('user','staff','system')), sender_id bigint, body text NOT NULL, is_internal_note boolean NOT NULL DEFAULT false, attachments jsonb NOT NULL DEFAULT '[]'::jsonb, created_at timestamptz NOT NULL DEFAULT now() ); CREATE INDEX ticket_messages_ticket_idx ON ticket_messages (ticket_id, created_at); CREATE TABLE chat_sessions ( id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, user_id bigint REFERENCES users(id) ON DELETE SET NULL, guest_name varchar(80), assigned_staff_id bigint REFERENCES staff_users(id), status varchar(8) NOT NULL DEFAULT 'waiting' CHECK (status IN ('waiting','active','closed')), started_at timestamptz NOT NULL DEFAULT now(), ended_at timestamptz ); CREATE TABLE chat_messages ( id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, session_id bigint NOT NULL REFERENCES chat_sessions(id) ON DELETE CASCADE, sender_type varchar(6) NOT NULL CHECK (sender_type IN ('user','guest','staff')), sender_id bigint, body varchar(2000) NOT NULL, created_at timestamptz NOT NULL DEFAULT now() ); -- ---------------------------------------------------------------------------- -- Sniffer AI Assistant -- ---------------------------------------------------------------------------- CREATE TABLE sniffer_topic_suggestions ( -- researched topic ideas, refreshed on a schedule id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, title varchar(160) NOT NULL, summary varchar(500) NOT NULL, category_id smallint REFERENCES categories(id), state_id smallint REFERENCES states(id), lga_id integer REFERENCES lgas(id), signal_sources jsonb NOT NULL DEFAULT '[]'::jsonb, -- trending posts, zero-result searches, public news filming_tips jsonb NOT NULL DEFAULT '[]'::jsonb, expires_at timestamptz NOT NULL, created_at timestamptz NOT NULL DEFAULT now() ); CREATE INDEX sniffer_suggestions_idx ON sniffer_topic_suggestions (state_id, expires_at); CREATE TABLE sniffer_conversations ( id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, user_id bigint NOT NULL REFERENCES users(id) ON DELETE CASCADE, created_at timestamptz NOT NULL DEFAULT now() ); CREATE TABLE sniffer_messages ( id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, conversation_id bigint NOT NULL REFERENCES sniffer_conversations(id) ON DELETE CASCADE, role varchar(10) NOT NULL CHECK (role IN ('user','assistant')), content text NOT NULL, safety_flags jsonb NOT NULL DEFAULT '[]'::jsonb, created_at timestamptz NOT NULL DEFAULT now() ); CREATE TABLE sniffer_usage ( -- per-user daily limit and cost control user_id bigint NOT NULL REFERENCES users(id) ON DELETE CASCADE, usage_date date NOT NULL, requests integer NOT NULL DEFAULT 0, PRIMARY KEY (user_id, usage_date) ); -- ---------------------------------------------------------------------------- -- Sharing analytics and referrals (rewards come later; tracking is ready now) -- ---------------------------------------------------------------------------- CREATE TABLE share_events ( id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, user_id bigint REFERENCES users(id) ON DELETE SET NULL, target_type varchar(10) NOT NULL CHECK (target_type IN ('platform','app','post','profile','group','claim','product')), target_id bigint, channel varchar(14) NOT NULL CHECK (channel IN ('whatsapp','facebook','instagram','x','telegram','linkedin','email','sms','copy_link','native')), created_at timestamptz NOT NULL DEFAULT now() ); CREATE INDEX share_events_target_idx ON share_events (target_type, target_id); CREATE TABLE referrals ( id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, referrer_user_id bigint NOT NULL REFERENCES users(id) ON DELETE CASCADE, referred_user_id bigint NOT NULL UNIQUE REFERENCES users(id) ON DELETE CASCADE, channel varchar(14), first_post_at timestamptz, -- referral counts as "activated" once they post reward_status varchar(16) NOT NULL DEFAULT 'not_enabled' CHECK (reward_status IN ('not_enabled','pending','granted')), created_at timestamptz NOT NULL DEFAULT now() ); CREATE INDEX referrals_referrer_idx ON referrals (referrer_user_id); -- ---------------------------------------------------------------------------- -- Cookie consent record (NDPA) and analytics rollups -- ---------------------------------------------------------------------------- CREATE TABLE consent_records ( id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, user_id bigint REFERENCES users(id) ON DELETE CASCADE, session_hash char(16), analytics boolean NOT NULL DEFAULT false, advertising boolean NOT NULL DEFAULT false, policy_version varchar(10) NOT NULL, created_at timestamptz NOT NULL DEFAULT now() ); CREATE TABLE daily_stats ( stat_date date NOT NULL, metric varchar(40) NOT NULL, -- signups, active_users, posts_created, posts_approved, claims, resolved ... state_id smallint NOT NULL DEFAULT 0, value bigint NOT NULL DEFAULT 0, PRIMARY KEY (stat_date, metric, state_id) ); CREATE TABLE post_daily_views ( post_id bigint NOT NULL REFERENCES posts(id) ON DELETE CASCADE, view_date date NOT NULL, views integer NOT NULL DEFAULT 0, unique_views integer NOT NULL DEFAULT 0, PRIMARY KEY (post_id, view_date) ); -- ---------------------------------------------------------------------------- -- Undo-delete: one cross-table view of everything currently in the trash. -- Purging is done by a scheduled job once purge_after has passed. -- ---------------------------------------------------------------------------- CREATE OR REPLACE VIEW trash_items AS SELECT 'post'::text AS item_type, id AS item_id, title AS label, deleted_at, deleted_by_type, deleted_by_id, purge_after, author_id AS owner_user_id FROM posts WHERE deleted_at IS NOT NULL UNION ALL SELECT 'comment', id, left(body, 60), deleted_at, deleted_by_type, deleted_by_id, purge_after, user_id FROM comments WHERE deleted_at IS NOT NULL UNION ALL SELECT 'group', id, name::text, deleted_at, deleted_by_type, deleted_by_id, purge_after, created_by_user_id FROM groups WHERE deleted_at IS NOT NULL UNION ALL SELECT 'user', id, username::text, deleted_at, deleted_by_type, deleted_by_id, purge_after, id FROM users WHERE deleted_at IS NOT NULL;