Document Type: Specification
Status: Active
Version: v1.1
Authority: Affiliate Brain (Audience Infrastructure Layer)
Applies To: Audience Engine event logging tables, derived audience state storage, and append-only infrastructure setup in Supabase
Parent: Supabase Event Schema
Last Reviewed: 2026-03-14
Purpose
This document defines the SQL required to create the Audience Engine logging tables in Supabase.
Scope of this SQL layer:
• creates tables only
• adds basic indexes
• adds simple constraints
• does not enable automation
• does not implement RLS, which is a separate layer
Execution is deferred until HeadOffice approval.
Scope
This specification applies to:
• creation of public.audience_events
• creation of public.audience_state
• base sanity constraints
• base index creation
• append-only doctrine for event logging
• separation between raw event storage and derived state storage
This document defines the SQL structure only.
It does not govern:
• RLS implementation
• event validation logic
• trigger design
• scheduled derivation jobs
• automation rollout
• audience capital allocation
Those remain governed by separate controlled documents.
Definition / Rules
SQL – Create Tables
Paste the following into Supabase SQL editor when approved for execution.
-- ======================================================
-- Audience Engine - Event Logging Tables
-- Status: Active | Version: v1.1 | Last Reviewed: 2026-03-14
-- ======================================================-- 1) Raw event log table (append-only by policy)
create table if not exists public.audience_events (
id uuid primary key default gen_random_uuid(),
created_at timestamptz not null default now(), -- Identity / session
user_id uuid null,
session_id text not null, -- Classification
event_type text not null,
event_category text not null,
event_source text not null, -- Attribution
utm_source text null,
utm_medium text null,
utm_campaign text null,
utm_content text null,
utm_term text null, -- Extensible payload
metadata jsonb not null default '{}'::jsonb
);-- Basic sanity constraints
do $$
begin
if not exists (
select 1 from pg_constraint
where conname = 'audience_events_event_type_chk'
) then
alter table public.audience_events
add constraint audience_events_event_type_chk
check (length(event_type) > 0);
end if;
end $$;do $$
begin
if not exists (
select 1 from pg_constraint
where conname = 'audience_events_event_category_chk'
) then
alter table public.audience_events
add constraint audience_events_event_category_chk
check (length(event_category) > 0);
end if;
end $$;do $$
begin
if not exists (
select 1 from pg_constraint
where conname = 'audience_events_event_source_chk'
) then
alter table public.audience_events
add constraint audience_events_event_source_chk
check (length(event_source) > 0);
end if;
end $$;do $$
begin
if not exists (
select 1 from pg_constraint
where conname = 'audience_events_session_id_chk'
) then
alter table public.audience_events
add constraint audience_events_session_id_chk
check (length(session_id) > 0);
end if;
end $$;-- Optional: event category enum-like constraint (tight governance)
-- Note: If you want maximum flexibility during build phase, comment this out.
do $$
begin
if not exists (
select 1 from pg_constraint
where conname = 'audience_events_event_category_allowed'
) then
alter table public.audience_events
add constraint audience_events_event_category_allowed
check (event_category in ('engagement','subscription','advocacy','retention','attribution','system'));
end if;
end $$;-- 2) Derived current state table (computed by logic layer, not manually edited)
create table if not exists public.audience_state (
user_id uuid primary key,
current_state text not null,
state_updated_at timestamptz not null default now(),
engagement_score numeric not null default 0,
last_event_type text null,
metadata jsonb not null default '{}'::jsonb
);do $$
begin
if not exists (
select 1 from pg_constraint
where conname = 'audience_state_current_state_chk'
) then
alter table public.audience_state
add constraint audience_state_current_state_chk
check (length(current_state) > 0);
end if;
end $$;-- ======================================================
-- Indexes (performance)
-- ======================================================-- audience_events
create index if not exists idx_audience_events_created_at
on public.audience_events (created_at desc);create index if not exists idx_audience_events_user_id
on public.audience_events (user_id);create index if not exists idx_audience_events_session_id
on public.audience_events (session_id);create index if not exists idx_audience_events_event_type
on public.audience_events (event_type);create index if not exists idx_audience_events_event_category
on public.audience_events (event_category);create index if not exists idx_audience_events_utm_campaign
on public.audience_events (utm_campaign);-- audience_state
create index if not exists idx_audience_state_current_state
on public.audience_state (current_state);create index if not exists idx_audience_state_state_updated_at
on public.audience_state (state_updated_at desc);
Append-Only Policy (Doctrine)
audience_events is treated as append-only.
No UPDATE or DELETE should be allowed once RLS is implemented.
All derived logic must write to audience_state only.
Next Layer (Not Included Here)
To complete deployment later, the following additional layers will be required:
• RLS policies
• event validation function, optional but recommended
• state derivation job, trigger or scheduled function
These are separate controlled documents and are not included in this page.
Drift Protection
The system must prevent:
• manual editing of raw event history once enforcement is active
• mixing raw event storage with derived state mutation logic
• using this page as authority for automation rollout
• assuming RLS is implemented when only tables exist
• treating audience_state as manual-entry storage
This page defines base infrastructure only.
It does not authorize full deployment.
Architectural Intent
Audience Events SQL exists to give Audience Engine a clean foundational storage layer for raw event capture and derived audience-state storage.
Its role is to separate append-only behavioral logging from computed state, making future validation, RLS enforcement, and derivation logic easier to govern and audit inside MWMS.
Change Log
Version: v1.1
Date: 2026-03-14
Author: MWMS HeadOffice / Affiliate Brain
Change: Rebuilt page to align with MWMS document standards. Removed duplicated title text, added standardised document header, replaced legacy metadata with compliant structure, introduced Purpose / Scope / Definition / Rules format, preserved the SQL as a full copy-paste block, and made constraint creation safer with existence checks to reduce rerun failures in Supabase.
Version: v1.0
Date: 2026-02-25
Author: Affiliate Brain
Change: Initial creation of Audience Events SQL defining base SQL for public.audience_events and public.audience_state, including basic constraints, indexes, append-only doctrine, and deferred next-layer requirements.
END – AUDIENCE EVENTS SQL v1.1