Document Type: Canon
Status: Canon
Version: v1.2
Authority: HeadOffice
Applies To: Research Brain database structure, insight linkage, experiment linkage, competitor tracking, win/loss logging, and pattern reporting
Parent: Research Brain Architecture
Last Reviewed: 2026-03-15
Purpose
This document defines the canonical database structure for Research Brain.
Its purpose is to enforce structural logging discipline, preserve cross-table linkage, prevent deletion drift, and ensure research intelligence remains cumulative and auditable across MWMS.
This schema governs how insight records, supporting evidence, experiment linkage, competitor tracking, win/loss intelligence, and pattern reports are stored at database level.
Scope
This canon applies to:
• Research Brain database tables
• Research Brain enum definitions
• Research Brain linkage rules
• archive-only enforcement
• experiment-to-insight relationship requirements
• competitor logging structure
• pattern reporting storage
This document governs the canonical database design for Research Brain.
It does not govern:
• live campaign execution
• capital allocation
• scaling approval
• experiment design logic beyond required linkage
• dashboard presentation layer
Those remain governed by Research Brain Canon, Research Brain Architecture, Affiliate Brain, Finance Brain, and related operational systems.
Definition / Rules
Design Rules
The following hard rules are enforced by schema design and accompanying database controls:
• no deletion, archive only
• Insight_ID is the primary linkage key across tables
• experiments must reference an Insight_ID
• competitor tracking is capped structurally by process, with max 5 primary competitors per domain enforced operationally through is_primary plus reporting discipline
Postgres Schema Overview
The schema contains the following core tables:
• research_insights — root record
• research_evidence — evidence supporting an insight
• research_experiments — experiment records linked to an insight
• research_competitors — top competitors per domain
• research_competitor_updates — competitor change log
• research_win_loss — won/lost outcomes, objections, and displacement signals
• research_patterns — quarterly clustering and meta-analysis output
The schema also defines these enums for consistency:
• research_domain_tag
• research_evidence_type
• research_confidence_level
• research_outcome_class
• research_severity
Full SQL (Supabase / Postgres)
Paste the following into the Supabase SQL editor and run it.
-- =========================================================
-- Research Brain Database Schema
-- Status: Canon | Version: v1.2 | Last Reviewed: 2026-03-15
-- =========================================================-- -----------------------------
-- 0) Extensions (if needed)
-- -----------------------------
create extension if not exists "pgcrypto";-- -----------------------------
-- 1) ENUMS
-- -----------------------------
DO $$ BEGIN
CREATE TYPE research_domain_tag AS ENUM ('affiliate','ppl','aibs','opportunity','headoffice','cross_domain');
EXCEPTION WHEN duplicate_object THEN NULL; END $$;DO $$ BEGIN
CREATE TYPE research_evidence_type AS ENUM ('course','newsletter','competitor_site','test_data','survey','interview','market_tool','manual_observation','other');
EXCEPTION WHEN duplicate_object THEN NULL; END $$;DO $$ BEGIN
CREATE TYPE research_confidence_level AS ENUM ('low','medium','high');
EXCEPTION WHEN duplicate_object THEN NULL; END $$;DO $$ BEGIN
CREATE TYPE research_outcome_class AS ENUM ('win','loss','inconclusive','hold');
EXCEPTION WHEN duplicate_object THEN NULL; END $$;DO $$ BEGIN
CREATE TYPE research_severity AS ENUM ('low','medium','high','critical');
EXCEPTION WHEN duplicate_object THEN NULL; END $$;-- -----------------------------
-- 2) CORE TABLE: research_insights
-- -----------------------------
CREATE TABLE IF NOT EXISTS public.research_insights (
insight_id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
title text NOT NULL,
raw_observation text NOT NULL,
domain_tag research_domain_tag NOT NULL DEFAULT 'cross_domain',
theme_tag text NOT NULL,
confidence_level research_confidence_level NOT NULL DEFAULT 'low',
evidence_density integer NOT NULL DEFAULT 1 CHECK (evidence_density >= 1),
source_summary text NULL,
notes text NULL,
status text NOT NULL DEFAULT 'active' CHECK (status IN ('active','archived')),
created_at timestamptz NOT NULL DEFAULT now(),
updated_at timestamptz NOT NULL DEFAULT now(),
archived_at timestamptz NULL
);CREATE INDEX IF NOT EXISTS idx_research_insights_domain ON public.research_insights(domain_tag);
CREATE INDEX IF NOT EXISTS idx_research_insights_theme ON public.research_insights(theme_tag);
CREATE INDEX IF NOT EXISTS idx_research_insights_created ON public.research_insights(created_at DESC);-- -----------------------------
-- 3) SUPPORT TABLE: research_evidence
-- -----------------------------
CREATE TABLE IF NOT EXISTS public.research_evidence (
evidence_id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
insight_id uuid NOT NULL REFERENCES public.research_insights(insight_id) ON UPDATE CASCADE,
evidence_type research_evidence_type NOT NULL,
source_name text NULL,
source_link text NULL,
excerpt_summary text NOT NULL,
is_independent boolean NOT NULL DEFAULT true,
captured_at timestamptz NOT NULL DEFAULT now(),
created_at timestamptz NOT NULL DEFAULT now(),
status text NOT NULL DEFAULT 'active' CHECK (status IN ('active','archived')),
archived_at timestamptz NULL
);CREATE INDEX IF NOT EXISTS idx_research_evidence_insight ON public.research_evidence(insight_id);
CREATE INDEX IF NOT EXISTS idx_research_evidence_type ON public.research_evidence(evidence_type);-- -----------------------------
-- 4) LINK TABLE: research_experiments
-- -----------------------------
CREATE TABLE IF NOT EXISTS public.research_experiments (
experiment_id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
insight_id uuid NOT NULL REFERENCES public.research_insights(insight_id) ON UPDATE CASCADE,
hypothesis_statement text NOT NULL,
learning_objective text NOT NULL,
traffic_source text NOT NULL,
funnel_stage text NOT NULL,
test_type text NOT NULL,
started_at timestamptz NULL,
ended_at timestamptz NULL,
outcome_class research_outcome_class NOT NULL DEFAULT 'hold',
statistical_confidence text NULL,
revenue_impact_estimate numeric NULL,
learning_notes text NULL,
status text NOT NULL DEFAULT 'active' CHECK (status IN ('active','archived')),
created_at timestamptz NOT NULL DEFAULT now(),
updated_at timestamptz NOT NULL DEFAULT now(),
archived_at timestamptz NULL
);CREATE INDEX IF NOT EXISTS idx_research_experiments_insight ON public.research_experiments(insight_id);
CREATE INDEX IF NOT EXISTS idx_research_experiments_outcome ON public.research_experiments(outcome_class);
CREATE INDEX IF NOT EXISTS idx_research_experiments_created ON public.research_experiments(created_at DESC);-- -----------------------------
-- 5) COMPETITOR REGISTRY: research_competitors
-- -----------------------------
CREATE TABLE IF NOT EXISTS public.research_competitors (
competitor_id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
domain_tag research_domain_tag NOT NULL,
competitor_name text NOT NULL,
competitor_type text NULL,
homepage_url text NULL,
is_primary boolean NOT NULL DEFAULT false,
notes text NULL,
status text NOT NULL DEFAULT 'active' CHECK (status IN ('active','archived')),
created_at timestamptz NOT NULL DEFAULT now(),
updated_at timestamptz NOT NULL DEFAULT now(),
archived_at timestamptz NULL,
UNIQUE(domain_tag, competitor_name)
);CREATE INDEX IF NOT EXISTS idx_research_competitors_domain ON public.research_competitors(domain_tag);
CREATE INDEX IF NOT EXISTS idx_research_competitors_primary ON public.research_competitors(domain_tag, is_primary);-- -----------------------------
-- 6) COMPETITOR CHANGE LOG: research_competitor_updates
-- -----------------------------
CREATE TABLE IF NOT EXISTS public.research_competitor_updates (
update_id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
competitor_id uuid NOT NULL REFERENCES public.research_competitors(competitor_id) ON UPDATE CASCADE,
update_type text NOT NULL,
update_summary text NOT NULL,
source_link text NULL,
severity research_severity NOT NULL DEFAULT 'low',
observed_at timestamptz NOT NULL DEFAULT now(),
created_at timestamptz NOT NULL DEFAULT now(),
status text NOT NULL DEFAULT 'active' CHECK (status IN ('active','archived')),
archived_at timestamptz NULL
);CREATE INDEX IF NOT EXISTS idx_comp_updates_competitor ON public.research_competitor_updates(competitor_id);
CREATE INDEX IF NOT EXISTS idx_comp_updates_observed ON public.research_competitor_updates(observed_at DESC);-- -----------------------------
-- 7) WIN/LOSS: research_win_loss
-- -----------------------------
CREATE TABLE IF NOT EXISTS public.research_win_loss (
win_loss_id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
domain_tag research_domain_tag NOT NULL,
linked_experiment_id uuid NULL REFERENCES public.research_experiments(experiment_id) ON UPDATE CASCADE,
insight_id uuid NULL REFERENCES public.research_insights(insight_id) ON UPDATE CASCADE,
outcome_class research_outcome_class NOT NULL,
competitor_mentioned text NULL,
objection_category text NULL,
mechanism_acceptance boolean NULL,
displacement_signal boolean NOT NULL DEFAULT false,
refund_signal boolean NOT NULL DEFAULT false,
notes text NULL,
occurred_at timestamptz NOT NULL DEFAULT now(),
created_at timestamptz NOT NULL DEFAULT now(),
status text NOT NULL DEFAULT 'active' CHECK (status IN ('active','archived')),
archived_at timestamptz NULL
);CREATE INDEX IF NOT EXISTS idx_win_loss_domain ON public.research_win_loss(domain_tag);
CREATE INDEX IF NOT EXISTS idx_win_loss_outcome ON public.research_win_loss(outcome_class);
CREATE INDEX IF NOT EXISTS idx_win_loss_occurred ON public.research_win_loss(occurred_at DESC);-- -----------------------------
-- 8) PATTERNS: research_patterns
-- -----------------------------
CREATE TABLE IF NOT EXISTS public.research_patterns (
pattern_id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
period_start date NOT NULL,
period_end date NOT NULL,
domain_tag research_domain_tag NOT NULL DEFAULT 'cross_domain',
pattern_title text NOT NULL,
pattern_summary text NOT NULL,
theme_tags text[] NULL,
key_signals text NULL,
recommended_questions text NULL,
confidence_level research_confidence_level NOT NULL DEFAULT 'medium',
created_at timestamptz NOT NULL DEFAULT now(),
status text NOT NULL DEFAULT 'active' CHECK (status IN ('active','archived')),
archived_at timestamptz NULL
);CREATE INDEX IF NOT EXISTS idx_patterns_period ON public.research_patterns(period_start DESC, period_end DESC);
CREATE INDEX IF NOT EXISTS idx_patterns_domain ON public.research_patterns(domain_tag);-- -----------------------------
-- 9) UPDATED_AT TRIGGERS
-- -----------------------------
CREATE OR REPLACE FUNCTION public.mwms_set_updated_at()
RETURNS trigger AS $$
BEGIN
NEW.updated_at = now();
RETURN NEW;
END;
$$ LANGUAGE plpgsql;DROP TRIGGER IF EXISTS trg_research_insights_updated ON public.research_insights;
CREATE TRIGGER trg_research_insights_updated
BEFORE UPDATE ON public.research_insights
FOR EACH ROW EXECUTE FUNCTION public.mwms_set_updated_at();DROP TRIGGER IF EXISTS trg_research_experiments_updated ON public.research_experiments;
CREATE TRIGGER trg_research_experiments_updated
BEFORE UPDATE ON public.research_experiments
FOR EACH ROW EXECUTE FUNCTION public.mwms_set_updated_at();DROP TRIGGER IF EXISTS trg_research_competitors_updated ON public.research_competitors;
CREATE TRIGGER trg_research_competitors_updated
BEFORE UPDATE ON public.research_competitors
FOR EACH ROW EXECUTE FUNCTION public.mwms_set_updated_at();-- -----------------------------
-- 10) NO-DELETE GUARD (archive only)
-- -----------------------------
CREATE OR REPLACE FUNCTION public.mwms_block_delete()
RETURNS trigger AS $$
BEGIN
RAISE EXCEPTION 'Delete is prohibited. Archive only.';
END;
$$ LANGUAGE plpgsql;DO $$
DECLARE
t text;
BEGIN
FOREACH t IN ARRAY ARRAY[
'research_insights',
'research_evidence',
'research_experiments',
'research_competitors',
'research_competitor_updates',
'research_win_loss',
'research_patterns'
]
LOOP
EXECUTE format('DROP TRIGGER IF EXISTS trg_%s_block_delete ON public.%s;', t, t);
EXECUTE format('CREATE TRIGGER trg_%s_block_delete BEFORE DELETE ON public.%s FOR EACH ROW EXECUTE FUNCTION public.mwms_block_delete();', t, t);
END LOOP;
END $$;
Operational Steps
Step 1 – Create Tables
Paste the SQL above into the Supabase SQL editor and run it.
Step 2 – Create the First Live Insight_ID
Insert one record into research_insights.
Add at least one record into research_evidence referencing that insight.
This acts as activation proof.
Step 3 – Enforce Insight_ID Requirement in Affiliate and PPL Process
Update the operational checklist so no experiments are permitted without Insight_ID linkage.
Step 4 – Add the Top 5 Competitors Per Domain
Insert competitor rows into research_competitors.
Mark up to five entries as is_primary = true per domain_tag.
Step 5 – Start Logging Win/Loss
Every completed test must add a row into research_win_loss within 48 hours.
Drift Protection
The system must prevent:
• deletion of research records
• experiments existing without Insight_ID linkage
• standalone test data outside research linkage
• competitor tracking without structured registry records
• retroactive removal of research history
• outcome reporting without archival discipline
Research data must remain cumulative, linked, and archive-only.
Architectural Intent
This schema exists to give Research Brain a durable and enforceable storage layer.
It turns research discipline into database-level structure so insights, evidence, experiments, competitor intelligence, win/loss records, and patterns remain connected over time.
This supports cumulative learning, cross-brain traceability, and long-term evidence integrity across MWMS.
Final Rule
If research data can be deleted, disconnected, or logged outside Insight_ID linkage, the Research Brain loses structural memory.
The schema must preserve continuity before convenience.
Change Log
Version: v1.2
Date: 2026-03-15
Author: MWMS HeadOffice
Change: Standardised the page fully to the locked cleanup format for this pass. Preserved the full SQL block, schema purpose, scope, design rules, table and enum structure, operational steps, drift protection, and architectural intent. Added a dedicated Final Rule section and updated the review date.
Version: v1.1
Date: 2026-03-14
Author: MWMS HeadOffice
Change: Rebuilt Research Brain Database Schema to align with MWMS document standards. Added Document Type header, formalised Purpose / Scope / Definition / Rules structure, preserved the full SQL block, normalised operational step formatting, and retained the original archive-only, Insight_ID linkage, and no-delete enforcement logic.
Version: v1.0
Date: 2026-03-03
Author: HeadOffice
Change: Created Research Brain table system including research_insights, research_evidence, research_experiments, research_competitors, research_competitor_updates, research_win_loss, and research_patterns. Implemented evidence and confidence structure, Insight_ID linkage discipline, updated timestamp triggers, and no-delete enforcement trigger.
END – RESEARCH BRAIN DATABASE SCHEMA v1.2