Document Type: Specification
Status: Active
Version: v1.1
Authority: Affiliate Brain (Audience Automation-Ready Layer)
Applies To: Audience-state derivation SQL functions, batch derivation execution, and automation-ready state calculation infrastructure
Parent: Audience State Derivation Job Spec
Last Reviewed: 2026-03-15
Purpose
Derivation SQL Functions provides the copy-paste SQL functions used to derive audience_state from audience_events.
This page exists to define the function layer responsible for converting logged audience events into a derived audience state record suitable for downstream audience intelligence systems.
It includes the following core functions:
• derive_audience_state(p_user_id uuid)
• run_audience_derivation_batch(p_limit int)
These functions are designed to be executed by service_role only.
RLS remains enabled.
service_role bypasses RLS by design.
Scope
This specification applies to:
• SQL-based audience-state derivation logic
• single-user state derivation
• batch derivation execution
• metadata-read utility functions
• automation-ready derivation infrastructure linked to audience progression systems
This document governs the SQL function layer for calculating audience state from event history.
It does not govern:
• table creation by itself
• upstream event validation contracts by themselves
• frontend execution logic
• scheduler implementation by itself
• production role hardening by itself
• broader audience architecture outside derivation logic
Those remain governed by Audience State Derivation Job Spec, Audience State Transition Rules, upstream contracts and policies, and future scheduler specifications.
Definition / Rules
Important Notes
These functions assume the following tables already exist:
• public.audience_events
• public.audience_state
Validation enforcement is assumed upstream through contracts and policies.
These functions still apply anti-inflation and dedupe safeguards.
Event weights and state logic follow:
• Audience State Transition Rules (baseline)
Function Set Overview
The SQL function layer consists of four main functions.
- Utility Function: _meta_num(meta jsonb, key text)
Purpose:
Safely read a numeric value from metadata JSON.
- Utility Function: _meta_text(meta jsonb, key text)
Purpose:
Safely read a text value from metadata JSON.
- Derivation Function: derive_audience_state(p_user_id uuid)
Purpose:
Derive a single user’s audience state from recent events.
This function:
• reads prior state
• deduplicates event patterns
• applies qualification rules
• calculates engagement score
• evaluates stage progression
• applies retention downgrades
• upserts audience_state
• logs state changes when needed
- Batch Function: run_audience_derivation_batch(p_limit int default 250)
Purpose:
Run derivation across a batch of recent users.
This function loops through distinct recent users and calls derive_audience_state for each one.
SQL – Functions
— ======================================================
— 1) Utility: safe numeric read from metadata
— ======================================================
create or replace function public._meta_num(meta jsonb, key text)
returns numeric
language sql
immutable
as $$
select
case
when meta ? key then (meta ->> key)::numeric
else null
end; — ====================================================== — 2) Utility: safe text read from metadata — ====================================================== create or replace function public._meta_text(meta jsonb, key text) returns text language sql immutable as $$ select case when meta ? key then (meta ->> key) else null end; $$; — ====================================================== — 3) Derive single user’s state from recent events — ====================================================== create or replace function public.derive_audience_state(p_user_id uuid) returns void language plpgsql security definer as $$ declare v_prior_state text; v_new_state text; v_score numeric := 0; v_last_event_type text := null; v_now timestamptz := now(); — activity windows v_7d timestamptz := now() – interval ‘7 days’; v_30d timestamptz := now() – interval ’30 days’; v_60d timestamptz := now() – interval ’60 days’; v_90d timestamptz := now() – interval ’90 days’; v_120d timestamptz := now() – interval ‘120 days’; v_sessions_30d int := 0; v_qualified_views_30d int := 0; v_advocacy_60d int := 0; v_long_form_30d int := 0; v_repeat_session_hits_30d int := 0; v_inactivity_days int := 0; begin if p_user_id is null then return; end if; — Fetch prior state (if any) select current_state into v_prior_state from public.audience_state where user_id = p_user_id; — —————————————————————- — Dedupe model (lightweight): — For scoring, ignore events that are exact duplicates within 3 seconds. — Implemented by grouping on (session_id, event_type, created_at bucket) — —————————————————————- with base as ( select ae.created_at, ae.session_id, ae.event_type, ae.event_category, ae.metadata, date_trunc(‘second’, ae.created_at) as ts_sec, row_number() over ( partition by ae.session_id, ae.event_type, date_trunc(‘second’, ae.created_at) order by ae.created_at asc ) as rn from public.audience_events ae where ae.user_id = p_user_id and ae.created_at >= v_120d and ae.event_category in (‘engagement’,’subscription’,’advocacy’,’retention’,’attribution’,’system’) ), events as ( select * from base where rn = 1 ), scored as ( select created_at, session_id, event_type, event_category, metadata, — Qualified content_view logic: — duration_seconds >= 20 OR scroll_depth_percent >= 30 case when event_type = ‘content_view’ then case when coalesce(public._meta_num(metadata,’duration_seconds’),0) >= 20 or coalesce(public._meta_num(metadata,’scroll_depth_percent’),0) >= 30 then 1 else 0 end else 1 end as is_qualified from events ) select — Score aggregation (baseline weights) coalesce(sum( case when is_qualified = 0 then 0 when event_type = ‘content_view’ then 1 when event_type = ‘scroll_depth_75’ then 2 when event_type = ‘video_completion_75’ then 3 when event_type = ‘multi_internal_click’ then 2 when event_type = ‘long_form_completion’ then 4 when event_type = ‘repeat_session_30d’ then 3 when event_type = ‘high_frequency_session’ then 3 when event_type = ’email_subscribe’ then 6 when event_type = ‘asset_download’ then 4 when event_type = ‘content_share’ then 5 when event_type = ‘referral_click’ then 4 when event_type = ‘comment_repeat’ then 3 when event_type = ‘mention_tag’ then 3 when event_type = ‘advocacy_repeat’ then 3 else 0 end ),0) as total_score, — Session count in 30d (select count(distinct session_id) from scored where created_at >= v_30d) as sessions_30d, — Qualified views 30d (select count(*) from scored where created_at >= v_30d and event_type = ‘content_view’ and is_qualified = 1) as qualified_views_30d, — Advocacy events in 60d (select count(*) from scored where created_at >= v_60d and event_category = ‘advocacy’) as advocacy_60d, — Long form completion in 30d (select count(*) from scored where created_at >= v_30d and event_type = ‘long_form_completion’) as long_form_30d, — repeat_session_30d occurrences in 30d (select count(*) from scored where created_at >= v_30d and event_type = ‘repeat_session_30d’) as repeat_session_hits_30d, — last event type (select event_type from scored order by created_at desc limit 1) as last_event into v_score, v_sessions_30d, v_qualified_views_30d, v_advocacy_60d, v_long_form_30d, v_repeat_session_hits_30d, v_last_event_type; — —————————————————————- — Determine inactivity days (based on last event) — —————————————————————- if v_last_event_type is null then v_inactivity_days := 999; else select extract(day from (v_now – max(created_at)))::int into v_inactivity_days from public.audience_events where user_id = p_user_id; end if; — —————————————————————- — Compute NEW STATE (ordered, no skipping) — States here follow the Transition Rules doc you published: — S0, S1, S2, S3, S4, S5 — —————————————————————- v_new_state := ‘S0’; — S0 -> S1 if exists ( select 1 from public.audience_events where user_id = p_user_id and created_at >= v_7d and event_type in (‘scroll_depth_75′,’video_completion_75′,’long_form_completion’) ) or (v_score >= 5 and exists ( select 1 from public.audience_events where user_id = p_user_id and created_at >= v_7d )) then v_new_state := ‘S1’; end if; — S1 -> S2 if v_new_state = ‘S1’ then if exists ( select 1 from public.audience_events where user_id = p_user_id and created_at >= v_30d and event_type = ‘repeat_session_30d’ ) or (v_score >= 12) or (v_sessions_30d >= 2 and v_qualified_views_30d >= 3) then v_new_state := ‘S2’; end if; end if; — S2 -> S3 (Subscriber requires consent_recorded=true) if v_new_state = ‘S2′ then if exists ( select 1 from public.audience_events where user_id = p_user_id and created_at >= v_120d and event_type in (’email_subscribe’,’asset_download’) and coalesce((metadata->>’consent_recorded’)::boolean,false) = true ) then v_new_state := ‘S3’; end if; end if; — S3 -> S4 (Advocate) if v_new_state = ‘S3’ then if exists ( select 1 from public.audience_events where user_id = p_user_id and created_at >= v_60d and event_type in (‘content_share’,’referral_click’,’comment_repeat’,’mention_tag’,’advocacy_repeat’) ) then v_new_state := ‘S4’; end if; end if; — S4 -> S5 (Core Asset) if v_new_state = ‘S4’ then — Must meet at least 2 conditions in 60d — 1) >=3 advocacy events in 60d — 2) score >=35 — 3) repeat_session_30d occurs >=2 times in 30d — 4) long_form_completion >=1 in 30d if ( (case when v_advocacy_60d >= 3 then 1 else 0 end) + (case when v_score >= 35 then 1 else 0 end) + (case when v_repeat_session_hits_30d >= 2 then 1 else 0 end) + (case when v_long_form_30d >= 1 then 1 else 0 end) ) >= 2 then v_new_state := ‘S5’; end if; end if; — —————————————————————- — Apply retention downgrades LAST (R1/R2/R3 baseline) — —————————————————————- if v_new_state = ‘S3’ and v_inactivity_days >= 45 then v_new_state := ‘S2’; end if; if v_new_state = ‘S4’ and v_inactivity_days >= 60 then v_new_state := ‘S3’; end if; if v_new_state = ‘S5’ and v_inactivity_days >= 75 then v_new_state := ‘S4’; end if; — —————————————————————- — Upsert into audience_state — —————————————————————- insert into public.audience_state (user_id, current_state, state_updated_at, engagement_score, last_event_type, metadata) values (p_user_id, v_new_state, v_now, v_score, v_last_event_type, ‘{}’::jsonb) on conflict (user_id) do update set current_state = excluded.current_state, state_updated_at = excluded.state_updated_at, engagement_score = excluded.engagement_score, last_event_type = excluded.last_event_type; — —————————————————————- — Log state change (system event) if prior != new or first insert — —————————————————————- if v_prior_state is distinct from v_new_state then insert into public.audience_events ( user_id, session_id, event_type, event_category, event_source, utm_source, utm_medium, utm_campaign, utm_content, utm_term, metadata ) values ( p_user_id, ‘system’, ‘state_change_logged’, ‘system’, ‘derivation_job’, null,null,null,null,null, jsonb_build_object( ‘prior_state’, v_prior_state, ‘new_state’, v_new_state, ‘derived_at’, v_now, ‘derived_by’, ‘derive_audience_state’ ) ); end if; end; $$; — ====================================================== — 4) Batch runner: derive for recent users — ====================================================== create or replace function public.run_audience_derivation_batch(p_limit int default 250) returns int language plpgsql security definer as $$ declare r record; v_count int := 0; begin for r in select distinct user_id from public.audience_events where user_id is not null order by max(created_at) over (partition by user_id) desc limit p_limit loop perform public.derive_audience_state(r.user_id); v_count := v_count + 1; end loop; return v_count; end; $$; Execution Assumptions These functions assume: • upstream validation enforcement already exists • event contracts and policies are enforced before derivation • anti-inflation and dedupe safeguards are still required at function level • baseline transition logic follows Audience State Transition Rules Security Notes These functions are intended for service_role execution only. RLS remains enabled, but service_role bypasses RLS by design. Before production, the following hardening should be applied: • revoke execute on functions from public • grant execute only to service_role or a dedicated restricted role • consider Edge Function execution if abuse risk becomes material Next Layer A future scheduler specification may run the following command: select public.run_audience_derivation_batch(250); Suggested frequency during early stage: • every 15 minutes or • hourly Final Rule Audience state derivation must remain deterministic, service-controlled, and aligned with the published state-transition logic. If derivation logic drifts away from the declared transition model, audience-state integrity becomes unreliable. Drift Protection The system must prevent: • audience-state derivation running without upstream table assumptions being met • public execution access remaining open in production • event inflation or duplicate events corrupting state progression • derivation rules drifting away from Audience State Transition Rules • state changes being written without traceable system logging • execution convenience overriding service-role security discipline This function layer must remain controlled, auditable, and structurally aligned with the wider audience-state system. Architectural Intent Derivation SQL Functions exists to provide the executable SQL logic that converts raw audience event history into structured audience-state outcomes inside the Affiliate Brain audience system. Its role is to bridge audience-event collection and downstream audience intelligence by supplying a repeatable, automation-ready derivation layer that can later be scheduled safely under service-role control. Change Log Version: v1.1 Date: 2026-03-15 Author: MWMS HeadOffice / Affiliate Brain Change: Rebuilt page to align with the locked MWMS document standard for this cleanup pass. Converted Last Updated to Last Reviewed, replaced Linked Canon formatting with Applies To structure, introduced Purpose / Scope / Definition / Rules structure, preserved the original SQL functions in full, and added Execution Assumptions, Security Notes, Final Rule, Drift Protection, and Architectural Intent sections. Version: v1.0 Date: 2026-02-25 Author: Affiliate Brain Change: Initial creation of Derivation SQL Functions providing copy-paste SQL for audience-state derivation and batch execution. END – DERIVATION SQL FUNCTIONS v1.1