Database Schema

The OpenScouter database is a PostgreSQL schema managed by Supabase. It contains 29 tables across four functional domains: identity and access, study management, test data capture, and reporting. Row-Level Security is enabled on all tables.

Domain Overview

Domain	Tables
Identity and access	profiles, tester_profiles, organizations, organization_members
Study management	jobs, job_testers, job_tasks, capacity_quiz_attempts, quiz_questions
Test data capture	sessions, session_events, facial_snapshots, voice_segments, session_notes
Reporting	reports, job_reports, report_findings, nd_stratification
Payments	payouts, payout_line_items, stripe_connect_accounts

Core Tables

profiles

Extends the Supabase auth.users table with application-level data. One row per authenticated user.

Column	Type	Notes
id	uuid	Primary key. Foreign key to auth.users.id
email	text	Unique. Mirrors auth.users.email
full_name	text	Display name
role	user_role	Enum: business, tester, admin
organization_id	uuid	Foreign key to organizations. Null for testers
created_at	timestamptz	Set on insert
updated_at	timestamptz	Updated by trigger on any change

tester_profiles

Extended profile for users with role = tester. Contains neurodivergent category declarations and capability information.

Column	Type	Notes
id	uuid	Primary key. Foreign key to profiles.id
nd_categories	nd_category[]	Array of ND category enums. See enum types below
assistive_tech	text[]	List of assistive technologies used
bio	text	Short tester biography
capacity_verified	boolean	True if quiz passed. Default false
capacity_score	integer	Score from capacity quiz attempt
stripe_connect_id	text	Stripe Connect account ID for payouts
telegram_chat_id	text	Telegram chat ID for notifications
available	boolean	Whether tester is accepting new studies
timezone	text	IANA timezone string

jobs

A study commissioned by a business. One row per study.

Column	Type	Notes
id	uuid	Primary key
organization_id	uuid	Foreign key to organizations
title	text	Study title
target_url	text	URL to be tested
brief	text	Study instructions provided to testers
status	job_status	Enum: draft, active, in_progress, completed, cancelled
nd_requirements	nd_category[]	Required ND categories for tester matching
tester_count	integer	Number of testers required
compensation_gbp	integer	Payment per tester in pence
tone_profile	tone_profile	Enum: standard, supportive, moderate, restorative
notes_confirmed	boolean	True when all testers have confirmed findings. Gates Agent 3
stripe_payment_id	text	Stripe Checkout session ID
created_at	timestamptz
updated_at	timestamptz

Note

The notes_confirmed column is the human-in-the-loop gate. The Report Writer agent checks this field before processing. Setting it to true on a study with unconfirmed session_notes records will result in incomplete reports.

sessions

One row per tester per study. Represents a single test run.

Column	Type	Notes
id	uuid	Primary key
job_id	uuid	Foreign key to jobs
tester_id	uuid	Foreign key to profiles
status	session_status	Enum: pending, active, completed, cancelled
test_token	text	48-char hex token for extension authentication
test_token_expires_at	timestamptz	Token TTL (24h from issuance)
test_token_revoked	boolean	Manual revocation flag
started_at	timestamptz	When tester clicked Start Test
ended_at	timestamptz	When tester clicked Stop Test
camera_consent	boolean	Tester granted camera access
microphone_consent	boolean	Tester granted microphone access
used_fallback_model	boolean	True if a fallback AI provider was used for any AI step
created_at	timestamptz

session_events

Browser events captured by the Chrome extension during a test.

Column	Type	Notes
id	uuid	Primary key
session_id	uuid	Foreign key to sessions
task_id	text	Task identifier at time of event
event_type	text	click, scroll, focus, blur, keydown, etc.
timestamp	bigint	Unix millisecond timestamp
x	integer	Pointer X coordinate
y	integer	Pointer Y coordinate
target_selector	text	CSS selector of the target element
target_text	text	Visible text of the target element
is_rage_click	boolean	Three or more clicks on same element within 500ms
time_on_element_ms	integer	Pointer dwell time in milliseconds
scroll_depth	numeric	Fractional page scroll depth at time of event
url	text	Page URL at time of event
viewport_width	integer
viewport_height	integer

facial_snapshots

Facial expression data from camera captures during the test.

Column	Type	Notes
id	uuid	Primary key
session_id	uuid	Foreign key to sessions
timestamp	bigint	Unix millisecond timestamp
emotion	text	Emotion label from DeepFace
confidence	numeric	DeepFace confidence score 0-1
face_detected	boolean	False if no face was found in the frame
action_units	jsonb	FACS Action Unit values from DeepFace
excluded	boolean	Tester-flagged for exclusion during cooldown

voice_segments

Transcribed voice segments from microphone capture.

Column	Type	Notes
id	uuid	Primary key
session_id	uuid	Foreign key to sessions
task_id	text	Task at time of segment
start_ms	bigint	Segment start timestamp
end_ms	bigint	Segment end timestamp
text	text	Transcribed text
sentiment	text	positive, neutral, negative
keywords	text[]	Extracted keywords
confidence	numeric	Transcription confidence 0-1
pii_detected	boolean	True if PII patterns found
processed	boolean	False if PII pending sanitisation
excluded	boolean	Tester-flagged for exclusion

session_notes

AI-generated findings produced by Agent 2 (Analyst). Testers review and confirm these before reports are generated.

Column	Type	Notes
id	uuid	Primary key
session_id	uuid	Foreign key to sessions
finding_text	text	AI-generated barrier description
plain_english	text	Plain English summary
wcag_criterion	text	WCAG success criterion code (e.g., 1.3.1)
wcag_level	text	A, AA, or AAA
severity	finding_severity	Enum: critical, major, minor, advisory
confidence	finding_confidence	Enum: high, medium, low
bucket_metadata	jsonb	30-second bucket data for this finding
confirmed	boolean	Tester confirmed this finding. Default false
rejected	boolean	Tester rejected this finding. Default false
tester_annotation	text	Optional tester note
created_at	timestamptz
updated_at	timestamptz

reports

Individual tester reports produced by Agent 3 (Report Writer).

Column	Type	Notes
id	uuid	Primary key
session_id	uuid	Foreign key to sessions
job_id	uuid	Foreign key to jobs
tester_id	uuid	Foreign key to profiles
report_text	text	Full report content
tone_profile	tone_profile	Tone profile used for this report
used_fallback_model	boolean	True if a fallback AI provider was used
generated_at	timestamptz	When Agent 3 completed

job_reports

Cross-test synthesis reports produced by Agent 4 (Synthesizer).

Column	Type	Notes
id	uuid	Primary key
job_id	uuid	Foreign key to jobs
synthesis_text	text	Full synthesized report
nd_stratification	jsonb	Findings stratified by ND category
cross_tester_boosts	jsonb	Barriers boosted by 3+ tester corroboration
used_fallback_model	boolean
generated_at	timestamptz

payouts

Payment records for tester compensation.

Column	Type	Notes
id	uuid	Primary key
tester_id	uuid	Foreign key to profiles
job_id	uuid	Foreign key to jobs
amount_gbp	integer	Amount in pence
status	payout_status	Enum: pending, processing, paid, failed
stripe_payout_id	text	Stripe payout object ID
paid_at	timestamptz	When Stripe confirmed delivery

quiz_questions and capacity_quiz_attempts

quiz_questions stores the accessibility knowledge questions used to verify tester capability. capacity_quiz_attempts records each tester’s quiz attempts.

Column	Type	Notes
quiz_questions.id	uuid	Primary key
quiz_questions.question_text	text
quiz_questions.nd_category	nd_category	Category this question tests
quiz_questions.correct_answer	text
quiz_questions.difficulty	integer	1-3 scale
capacity_quiz_attempts.tester_id	uuid	Foreign key to profiles
capacity_quiz_attempts.score	integer	Questions answered correctly
capacity_quiz_attempts.passed	boolean	True if score meets threshold
capacity_quiz_attempts.attempted_at	timestamptz

Enum Types

nd_category

The 13 supported neurodivergent categories.

CREATE TYPE nd_category AS ENUM (
  'autism',
  'adhd',
  'dyslexia',
  'dyscalculia',
  'dyspraxia',
  'tourette_syndrome',
  'hyperlexia',
  'sensory_processing',
  'irlen_syndrome',
  'anxiety_disorder',
  'ocd',
  'acquired_brain_injury',
  'other'
);

user_role

CREATE TYPE user_role AS ENUM ('business', 'tester', 'admin');

job_status

CREATE TYPE job_status AS ENUM (
  'draft',
  'active',
  'in_progress',
  'completed',
  'cancelled'
);

session_status

CREATE TYPE session_status AS ENUM (
  'pending',
  'active',
  'completed',
  'cancelled'
);

finding_severity

CREATE TYPE finding_severity AS ENUM (
  'critical',
  'major',
  'minor',
  'advisory'
);

finding_confidence

CREATE TYPE finding_confidence AS ENUM ('high', 'medium', 'low');

tone_profile

CREATE TYPE tone_profile AS ENUM (
  'standard',
  'supportive',
  'moderate',
  'restorative'
);

payout_status

CREATE TYPE payout_status AS ENUM (
  'pending',
  'processing',
  'paid',
  'failed'
);

Row-Level Security Policy Examples

RLS is enabled on all 29 tables. Below are representative policy examples for the most commonly accessed tables.

profiles

Users can read their own profile. Admins can read all profiles.

CREATE POLICY "users_read_own_profile"
ON profiles FOR SELECT
USING (id = auth.uid());

CREATE POLICY "admins_read_all_profiles"
ON profiles FOR SELECT
USING ((auth.jwt() ->> 'role') = 'admin');

jobs

Organizations read their own studies. Testers read studies they are matched to.

CREATE POLICY "org_read_own_jobs"
ON jobs FOR SELECT
USING (
  organization_id = (auth.jwt() -> 'organization_id')::uuid
);

CREATE POLICY "tester_read_matched_jobs"
ON jobs FOR SELECT
USING (
  id IN (
    SELECT job_id FROM sessions
    WHERE tester_id = auth.uid()
    AND status != 'cancelled'
  )
);

session_events

Testers can insert events for their own active sessions. No direct reads from session_events by testers or businesses. Reads go through aggregate views.

CREATE POLICY "tester_insert_own_events"
ON session_events FOR INSERT
WITH CHECK (
  session_id IN (
    SELECT id FROM sessions
    WHERE tester_id = auth.uid()
    AND status = 'active'
  )
);

session_notes

Testers can read and update notes for their own sessions.

CREATE POLICY "tester_read_own_notes"
ON session_notes FOR SELECT
USING (
  session_id IN (
    SELECT id FROM sessions
    WHERE tester_id = auth.uid()
  )
);

CREATE POLICY "tester_update_own_notes"
ON session_notes FOR UPDATE
USING (
  session_id IN (
    SELECT id FROM sessions
    WHERE tester_id = auth.uid()
  )
)
WITH CHECK (
  session_id IN (
    SELECT id FROM sessions
    WHERE tester_id = auth.uid()
  )
);

Note

Service role key operations bypass RLS. The Synthesizer agent uses the service role key to read session_notes across all testers for a given study. All service role operations are logged and audited. Each use must be accompanied by an inline comment explaining why RLS bypass is required.