-- ============================================================================ -- 06 Ads (tables reserved now; ads stay switched OFF at launch) -- Fill rule: DIRECT campaigns first; an EXTERNAL provider fills a placement -- only when no direct ad matches; then HOUSE ad; otherwise the slot collapses. -- Profit sharing has its OWN switch, separate from the ads switch. -- ============================================================================ -- Named slots on pages (header, in-feed, sidebar, post footer, ...) CREATE TABLE ad_placements ( id smallint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, code varchar(40) NOT NULL UNIQUE, -- e.g. home_below_hero, post_in_feed_3 name varchar(80) NOT NULL, page_type varchar(20) NOT NULL, -- home, post, search, profile, group, shop width integer, height integer, device varchar(8) NOT NULL DEFAULT 'all' CHECK (device IN ('all','desktop','mobile')), is_enabled boolean NOT NULL DEFAULT true, floor_cpm_kobo integer NOT NULL DEFAULT 0, -- minimum direct bid; 0 = pure "direct first" external_fallback boolean NOT NULL DEFAULT true, house_fallback boolean NOT NULL DEFAULT true, sort_order smallint NOT NULL DEFAULT 0 ); -- External networks and custom tags (AdSense, Ezoic, Media.net, ...). Admin-only. CREATE TABLE ad_providers ( id smallint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, name varchar(60) NOT NULL UNIQUE, kind varchar(12) NOT NULL CHECK (kind IN ('adsense','custom_tag','house')), script_src varchar(300), -- must be on the CSP allowlist tag_html text, -- pasted by Admin only; rendered sandboxed allowed_script_domains text[] NOT NULL DEFAULT '{}', requires_consent boolean NOT NULL DEFAULT true, -- blocked until cookie consent is given is_enabled boolean NOT NULL DEFAULT false, updated_by_staff_id bigint REFERENCES staff_users(id) ON DELETE SET NULL, created_at timestamptz NOT NULL DEFAULT now(), updated_at timestamptz NOT NULL DEFAULT now() ); CREATE TABLE ad_placement_providers ( placement_id smallint NOT NULL REFERENCES ad_placements(id) ON DELETE CASCADE, provider_id smallint NOT NULL REFERENCES ad_providers(id) ON DELETE CASCADE, priority smallint NOT NULL DEFAULT 1, slot_reference varchar(100), -- e.g. the AdSense ad-slot id PRIMARY KEY (placement_id, provider_id) ); -- Pages or posts where ads must never appear CREATE TABLE ad_exclusions ( id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, scope varchar(12) NOT NULL CHECK (scope IN ('category','state','post','page','group')), scope_id bigint NOT NULL, reason varchar(200), created_at timestamptz NOT NULL DEFAULT now(), UNIQUE (scope, scope_id) ); -- Advertisers (2FA is mandatory for them; enforced by the application) CREATE TABLE advertisers ( id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, user_id bigint NOT NULL UNIQUE REFERENCES users(id) ON DELETE CASCADE, business_name varchar(150) NOT NULL, business_type varchar(20) NOT NULL DEFAULT 'individual' CHECK (business_type IN ('individual','company')), cac_number_encrypted text, contact_phone varchar(20), status varchar(12) NOT NULL DEFAULT 'pending' CHECK (status IN ('pending','approved','suspended','rejected')), approved_by_staff_id bigint REFERENCES staff_users(id), approved_at timestamptz, created_at timestamptz NOT NULL DEFAULT now(), updated_at timestamptz NOT NULL DEFAULT now() ); CREATE TABLE advertiser_wallets ( advertiser_id bigint PRIMARY KEY REFERENCES advertisers(id) ON DELETE CASCADE, balance_kobo bigint NOT NULL DEFAULT 0 CHECK (balance_kobo >= 0), updated_at timestamptz NOT NULL DEFAULT now() ); CREATE TABLE ad_campaigns ( id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, advertiser_id bigint NOT NULL REFERENCES advertisers(id) ON DELETE CASCADE, name varchar(120) NOT NULL, objective varchar(12) NOT NULL DEFAULT 'traffic' CHECK (objective IN ('awareness','traffic','conversions')), pricing_model varchar(4) NOT NULL DEFAULT 'cpm' CHECK (pricing_model IN ('cpm','cpc')), bid_kobo integer NOT NULL CHECK (bid_kobo > 0), daily_budget_kobo bigint NOT NULL CHECK (daily_budget_kobo > 0), total_budget_kobo bigint, starts_at timestamptz NOT NULL, ends_at timestamptz, status varchar(12) NOT NULL DEFAULT 'draft' CHECK (status IN ('draft','in_review','active','paused','completed','rejected')), -- targeting used by the delivery algorithm target_states smallint[] NOT NULL DEFAULT '{}', target_lgas integer[] NOT NULL DEFAULT '{}', target_categories smallint[] NOT NULL DEFAULT '{}', target_interests text[] NOT NULL DEFAULT '{}', target_age_ranges text[] NOT NULL DEFAULT '{}', target_genders text[] NOT NULL DEFAULT '{}', target_devices text[] NOT NULL DEFAULT '{}', frequency_cap_per_day smallint NOT NULL DEFAULT 3, reviewed_by_staff_id bigint REFERENCES staff_users(id), review_note text, deleted_at timestamptz, purge_after timestamptz, created_at timestamptz NOT NULL DEFAULT now(), updated_at timestamptz NOT NULL DEFAULT now(), CONSTRAINT campaign_dates CHECK (ends_at IS NULL OR ends_at > starts_at) ); CREATE INDEX ad_campaigns_active_idx ON ad_campaigns (status, starts_at, ends_at) WHERE deleted_at IS NULL; CREATE TABLE ad_creatives ( id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, campaign_id bigint NOT NULL REFERENCES ad_campaigns(id) ON DELETE CASCADE, format varchar(12) NOT NULL CHECK (format IN ('banner','native','video')), headline varchar(90), body varchar(200), media_path varchar(255), alt_text varchar(200), destination_url varchar(500) NOT NULL, call_to_action varchar(30), review_status varchar(10) NOT NULL DEFAULT 'pending' CHECK (review_status IN ('pending','approved','rejected')), reviewed_by_staff_id bigint REFERENCES staff_users(id), rejection_reason varchar(300), created_at timestamptz NOT NULL DEFAULT now() ); CREATE TABLE advertiser_ledger ( id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, advertiser_id bigint NOT NULL REFERENCES advertisers(id), entry_type varchar(12) NOT NULL CHECK (entry_type IN ('deposit','spend','refund','adjustment')), amount_kobo bigint NOT NULL, balance_after_kobo bigint NOT NULL, reference varchar(100), campaign_id bigint REFERENCES ad_campaigns(id), note varchar(200), created_at timestamptz NOT NULL DEFAULT now() ); CREATE INDEX advertiser_ledger_idx ON advertiser_ledger (advertiser_id, created_at DESC); -- Every ad request is logged with how it was filled so fill rate can be tracked. -- High volume: partition by month once traffic justifies it. CREATE TABLE ad_requests ( id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, placement_id smallint NOT NULL REFERENCES ad_placements(id), page_type varchar(20), post_id bigint, user_id bigint, session_hash char(16), filled_by varchar(8) NOT NULL CHECK (filled_by IN ('direct','external','house','none')), campaign_id bigint REFERENCES ad_campaigns(id), creative_id bigint REFERENCES ad_creatives(id), provider_id smallint REFERENCES ad_providers(id), state_id smallint, device varchar(8), created_at timestamptz NOT NULL DEFAULT now() ); CREATE INDEX ad_requests_placement_idx ON ad_requests (placement_id, created_at DESC); CREATE INDEX ad_requests_campaign_idx ON ad_requests (campaign_id, created_at DESC); CREATE TABLE ad_events ( id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, request_id bigint REFERENCES ad_requests(id) ON DELETE SET NULL, event_type varchar(10) NOT NULL CHECK (event_type IN ('impression','viewable','click')), campaign_id bigint REFERENCES ad_campaigns(id), creative_id bigint REFERENCES ad_creatives(id), provider_id smallint REFERENCES ad_providers(id), post_id bigint, creator_user_id bigint, -- the member whose content hosted the ad cost_kobo bigint NOT NULL DEFAULT 0, revenue_kobo bigint NOT NULL DEFAULT 0, invalid boolean NOT NULL DEFAULT false, -- click-fraud / bot filter result ip_hash char(16), created_at timestamptz NOT NULL DEFAULT now() ); CREATE INDEX ad_events_campaign_idx ON ad_events (campaign_id, created_at DESC); CREATE INDEX ad_events_creator_idx ON ad_events (creator_user_id, created_at DESC) WHERE creator_user_id IS NOT NULL; -- ---------------------------------------------------------------------------- -- Creator revenue sharing. Earnings are ALWAYS recorded in the ledger, even while -- sharing is switched off, so the Super Admin can see what creators would have -- earned before turning sharing on. Only DIRECT ad revenue is shareable by default. -- ---------------------------------------------------------------------------- CREATE TABLE creator_eligibility ( user_id bigint PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE, status varchar(12) NOT NULL DEFAULT 'not_eligible' CHECK (status IN ('not_eligible','eligible','applied','approved','suspended')), followers_snapshot integer NOT NULL DEFAULT 0, approved_posts_snapshot integer NOT NULL DEFAULT 0, views_90d_snapshot bigint NOT NULL DEFAULT 0, two_factor_required_met boolean NOT NULL DEFAULT false, kyc_status varchar(10) NOT NULL DEFAULT 'none' CHECK (kyc_status IN ('none','pending','verified','rejected')), payout_bank_code varchar(10), payout_account_encrypted text, paystack_recipient_code varchar(60), evaluated_at timestamptz, approved_at timestamptz, created_at timestamptz NOT NULL DEFAULT now() ); CREATE TABLE creator_earnings_ledger ( id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, user_id bigint NOT NULL REFERENCES users(id), period_date date NOT NULL, post_id bigint, gross_revenue_kobo bigint NOT NULL, share_percent numeric(5,2), -- percentage applied if sharing was on, null if off earned_kobo bigint NOT NULL DEFAULT 0, is_payable boolean NOT NULL DEFAULT false, -- false while sharing is off ("shadow" earnings) payout_id bigint, created_at timestamptz NOT NULL DEFAULT now() ); CREATE INDEX creator_earnings_user_idx ON creator_earnings_ledger (user_id, period_date DESC); CREATE TABLE creator_payouts ( id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, user_id bigint NOT NULL REFERENCES users(id), amount_kobo bigint NOT NULL CHECK (amount_kobo > 0), status varchar(12) NOT NULL DEFAULT 'pending' CHECK (status IN ('pending','approved','processing','paid','failed','cancelled')), paystack_transfer_code varchar(60), approved_by_staff_id bigint REFERENCES staff_users(id), failure_reason varchar(200), created_at timestamptz NOT NULL DEFAULT now(), paid_at timestamptz ); -- Dated history of profit-sharing switch changes, percentages and criteria CREATE TABLE profit_sharing_periods ( id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, enabled_from timestamptz NOT NULL, enabled_until timestamptz, share_percent numeric(5,2) NOT NULL CHECK (share_percent BETWEEN 0 AND 100), criteria jsonb NOT NULL DEFAULT '{}'::jsonb, changed_by_staff_id bigint NOT NULL REFERENCES staff_users(id), created_at timestamptz NOT NULL DEFAULT now() ); CREATE TRIGGER ad_providers_updated BEFORE UPDATE ON ad_providers FOR EACH ROW EXECUTE FUNCTION set_updated_at(); CREATE TRIGGER advertisers_updated BEFORE UPDATE ON advertisers FOR EACH ROW EXECUTE FUNCTION set_updated_at(); CREATE TRIGGER ad_campaigns_updated BEFORE UPDATE ON ad_campaigns FOR EACH ROW EXECUTE FUNCTION set_updated_at();