-- ============================================================================ -- 03 Content: categories, posts, media, reactions, comments, vetting, reports -- (Designed in Phase 1 so later phases need no data migration.) -- ============================================================================ CREATE TABLE categories ( id smallint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, name varchar(60) NOT NULL UNIQUE, slug varchar(80) NOT NULL UNIQUE, description varchar(255), icon varchar(40), sort_order smallint NOT NULL DEFAULT 0, is_active boolean NOT NULL DEFAULT true, created_at timestamptz NOT NULL DEFAULT now() ); CREATE TABLE posts ( id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, uuid uuid NOT NULL DEFAULT gen_random_uuid() UNIQUE, author_id bigint REFERENCES users(id) ON DELETE SET NULL, -- NULL when anonymous (see post_anonymous_authors) is_anonymous boolean NOT NULL DEFAULT false, title varchar(160) NOT NULL, slug varchar(200) NOT NULL UNIQUE, body text NOT NULL, category_id smallint REFERENCES categories(id), state_id smallint NOT NULL REFERENCES states(id), lga_id integer NOT NULL REFERENCES lgas(id), town_id integer NOT NULL REFERENCES towns(id), address_hint varchar(200), latitude numeric(9,6), longitude numeric(9,6), occurred_at timestamptz, -- when the author saw it -- lifecycle status varchar(16) NOT NULL DEFAULT 'pending' CHECK (status IN ('draft','pending','approved','rejected','removed','legal_hold')), resolution_status varchar(16) NOT NULL DEFAULT 'open' CHECK (resolution_status IN ('open','claimed','in_progress','fix_reported','resolved','lapsed')), published_at timestamptz, -- moderation and distribution switches no_ads boolean NOT NULL DEFAULT false, -- sensitive content, keep ads off allow_social_syndication boolean NOT NULL DEFAULT true, -- author consent syndication_blocked boolean NOT NULL DEFAULT false, -- set by moderators/legal hidden_from_search boolean NOT NULL DEFAULT false, duplicate_of_post_id bigint REFERENCES posts(id) ON DELETE SET NULL, -- denormalised counters (updated by queued jobs) support_count integer NOT NULL DEFAULT 0, evidence_request_count integer NOT NULL DEFAULT 0, confirm_count integer NOT NULL DEFAULT 0, comment_count integer NOT NULL DEFAULT 0, share_count integer NOT NULL DEFAULT 0, view_count bigint NOT NULL DEFAULT 0, trending_score double precision NOT NULL DEFAULT 0, search_vector tsvector GENERATED ALWAYS AS ( setweight(to_tsvector('simple'::regconfig, coalesce(title,'')), 'A') || setweight(to_tsvector('simple'::regconfig, coalesce(address_hint,'')), 'B') || setweight(to_tsvector('simple'::regconfig, coalesce(body,'')), 'C') ) STORED, -- soft delete / undo-delete deleted_at timestamptz, deleted_by_type varchar(8) CHECK (deleted_by_type IN ('user','staff','system')), deleted_by_id bigint, purge_after timestamptz, created_at timestamptz NOT NULL DEFAULT now(), updated_at timestamptz NOT NULL DEFAULT now(), CONSTRAINT anonymous_has_no_author CHECK (is_anonymous = false OR author_id IS NULL) ); CREATE INDEX posts_feed_idx ON posts (published_at DESC) WHERE status = 'approved' AND deleted_at IS NULL; CREATE INDEX posts_trending_idx ON posts (trending_score DESC) WHERE status = 'approved' AND deleted_at IS NULL; CREATE INDEX posts_popular_idx ON posts (support_count DESC) WHERE status = 'approved' AND deleted_at IS NULL; CREATE INDEX posts_location_idx ON posts (state_id, lga_id, town_id); CREATE INDEX posts_author_idx ON posts (author_id) WHERE author_id IS NOT NULL; CREATE INDEX posts_category_idx ON posts (category_id); CREATE INDEX posts_resolution_idx ON posts (resolution_status); CREATE INDEX posts_search_idx ON posts USING gin (search_vector); CREATE INDEX posts_title_trgm ON posts USING gin (title gin_trgm_ops); CREATE INDEX posts_deleted_idx ON posts (purge_after) WHERE deleted_at IS NOT NULL; -- Anonymous authorship: real identity is kept (so abuse can be handled and the -- author can manage the post) but encrypted by the application and never -- reachable through search, feeds, RSS, social posts or ordinary admin screens. CREATE TABLE post_anonymous_authors ( post_id bigint PRIMARY KEY REFERENCES posts(id) ON DELETE CASCADE, author_ref_encrypted text NOT NULL, created_at timestamptz NOT NULL DEFAULT now() ); CREATE TABLE anonymous_identity_access_log ( id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, post_id bigint NOT NULL REFERENCES posts(id) ON DELETE CASCADE, staff_id bigint NOT NULL REFERENCES staff_users(id), reason text NOT NULL, approved_by_staff_id bigint REFERENCES staff_users(id), ip inet, created_at timestamptz NOT NULL DEFAULT now() ); CREATE TABLE post_media ( id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, post_id bigint NOT NULL REFERENCES posts(id) ON DELETE CASCADE, kind varchar(6) NOT NULL CHECK (kind IN ('photo','video')), storage_disk varchar(20) NOT NULL DEFAULT 's3', original_path varchar(300) NOT NULL, optimized_path varchar(300), thumbnail_path varchar(300), hls_playlist_path varchar(300), mime_type varchar(60) NOT NULL, size_bytes bigint NOT NULL, width integer, height integer, duration_seconds integer, perceptual_hash char(16), -- duplicate detection for images sha256 char(64), -- exact-duplicate detection exif_stripped boolean NOT NULL DEFAULT false, -- GPS and device data removed before publishing processing_status varchar(12) NOT NULL DEFAULT 'queued' CHECK (processing_status IN ('queued','processing','ready','failed')), alt_text varchar(200), sort_order smallint NOT NULL DEFAULT 0, created_at timestamptz NOT NULL DEFAULT now() ); CREATE INDEX post_media_post_idx ON post_media (post_id, sort_order); CREATE INDEX post_media_sha_idx ON post_media (sha256); CREATE INDEX post_media_phash_idx ON post_media (perceptual_hash); CREATE TABLE tags ( id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY, name citext NOT NULL UNIQUE, slug varchar(80) NOT NULL UNIQUE ); CREATE TABLE post_tags ( post_id bigint NOT NULL REFERENCES posts(id) ON DELETE CASCADE, tag_id integer NOT NULL REFERENCES tags(id) ON DELETE CASCADE, PRIMARY KEY (post_id, tag_id) ); -- Automated vetting (seconds) and the human review queue behind it CREATE TABLE post_vetting_results ( id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, post_id bigint NOT NULL REFERENCES posts(id) ON DELETE CASCADE, stage varchar(20) NOT NULL CHECK (stage IN ('duplicate','banned_terms','image_hash','nsfw','ai_review','human_review')), outcome varchar(10) NOT NULL CHECK (outcome IN ('pass','flag','fail')), score numeric(5,4), details jsonb NOT NULL DEFAULT '{}'::jsonb, created_at timestamptz NOT NULL DEFAULT now() ); CREATE INDEX post_vetting_post_idx ON post_vetting_results (post_id); CREATE TABLE moderation_queue ( id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, subject_type varchar(12) NOT NULL CHECK (subject_type IN ('post','comment','group','user','champion')), subject_id bigint NOT NULL, reason varchar(30) NOT NULL, -- auto_flag, user_report, legal_notice priority smallint NOT NULL DEFAULT 5, status varchar(12) NOT NULL DEFAULT 'open' CHECK (status IN ('open','in_review','resolved')), assigned_staff_id bigint REFERENCES staff_users(id), decision varchar(16) CHECK (decision IN ('approved','rejected','removed','escalated','no_action')), decision_note text, decided_at timestamptz, created_at timestamptz NOT NULL DEFAULT now() ); CREATE INDEX moderation_queue_open_idx ON moderation_queue (priority, created_at) WHERE status <> 'resolved'; CREATE TABLE post_status_history ( id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, post_id bigint NOT NULL REFERENCES posts(id) ON DELETE CASCADE, from_status varchar(16), to_status varchar(16) NOT NULL, changed_by_type varchar(8) NOT NULL CHECK (changed_by_type IN ('user','staff','system')), changed_by_id bigint, note text, created_at timestamptz NOT NULL DEFAULT now() ); -- Reactions: 'dislike' is deliberately replaced by evidence-oriented reactions CREATE TABLE post_reactions ( post_id bigint NOT NULL REFERENCES posts(id) ON DELETE CASCADE, user_id bigint NOT NULL REFERENCES users(id) ON DELETE CASCADE, type varchar(20) NOT NULL CHECK (type IN ('support','needs_evidence','not_absurdity')), created_at timestamptz NOT NULL DEFAULT now(), PRIMARY KEY (post_id, user_id) ); CREATE INDEX post_reactions_user_idx ON post_reactions (user_id); -- "I've seen this too": independent confirmation, optionally with location proof CREATE TABLE post_confirmations ( post_id bigint NOT NULL REFERENCES posts(id) ON DELETE CASCADE, user_id bigint NOT NULL REFERENCES users(id) ON DELETE CASCADE, note varchar(300), latitude numeric(9,6), longitude numeric(9,6), created_at timestamptz NOT NULL DEFAULT now(), PRIMARY KEY (post_id, user_id) ); CREATE TABLE comments ( id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, post_id bigint NOT NULL REFERENCES posts(id) ON DELETE CASCADE, user_id bigint NOT NULL REFERENCES users(id) ON DELETE CASCADE, parent_id bigint REFERENCES comments(id) ON DELETE CASCADE, body varchar(2000) NOT NULL, status varchar(10) NOT NULL DEFAULT 'visible' CHECK (status IN ('visible','hidden','removed')), like_count integer NOT NULL DEFAULT 0, deleted_at timestamptz, deleted_by_type varchar(8) CHECK (deleted_by_type IN ('user','staff','system')), deleted_by_id bigint, purge_after timestamptz, created_at timestamptz NOT NULL DEFAULT now(), updated_at timestamptz NOT NULL DEFAULT now() ); CREATE INDEX comments_post_idx ON comments (post_id, created_at) WHERE deleted_at IS NULL; CREATE TABLE comment_likes ( comment_id bigint NOT NULL REFERENCES comments(id) ON DELETE CASCADE, user_id bigint NOT NULL REFERENCES users(id) ON DELETE CASCADE, created_at timestamptz NOT NULL DEFAULT now(), PRIMARY KEY (comment_id, user_id) ); CREATE TABLE reports ( id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, reporter_user_id bigint REFERENCES users(id) ON DELETE SET NULL, subject_type varchar(12) NOT NULL CHECK (subject_type IN ('post','comment','group','user','champion')), subject_id bigint NOT NULL, reason varchar(30) NOT NULL CHECK (reason IN ('false_info','defamation','privacy','adult','violence','spam','duplicate','impersonation','other')), details varchar(1000), status varchar(10) NOT NULL DEFAULT 'open' CHECK (status IN ('open','reviewing','closed')), created_at timestamptz NOT NULL DEFAULT now() ); CREATE INDEX reports_subject_idx ON reports (subject_type, subject_id); -- Legal/takedown notices handled by the Legal/Takedown Officer CREATE TABLE legal_notices ( id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, post_id bigint REFERENCES posts(id) ON DELETE SET NULL, complainant_name varchar(150) NOT NULL, complainant_contact varchar(200) NOT NULL, claim_type varchar(20) NOT NULL CHECK (claim_type IN ('defamation','privacy','copyright','other')), details text NOT NULL, status varchar(20) NOT NULL DEFAULT 'received' CHECK (status IN ('received','under_review','content_removed','rejected','counter_notice','closed')), handled_by_staff_id bigint REFERENCES staff_users(id), resolution_note text, created_at timestamptz NOT NULL DEFAULT now(), closed_at timestamptz ); CREATE TRIGGER posts_updated BEFORE UPDATE ON posts FOR EACH ROW EXECUTE FUNCTION set_updated_at(); CREATE TRIGGER comments_updated BEFORE UPDATE ON comments FOR EACH ROW EXECUTE FUNCTION set_updated_at();