schema.sql

-- =============================================================================
-- PowerSync Load Testing - Supabase Schema
-- =============================================================================
-- Run this SQL in your Supabase SQL Editor to create all required tables.
-- Tables are organized into: scenario data tables + metrics tables.
-- =============================================================================

-- =====================
-- Scenario 1: Broadcast
-- One write fans out to every connected client.
-- =====================
CREATE TABLE IF NOT EXISTS broadcast_rows (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    label TEXT NOT NULL,
    payload TEXT,
    written_by TEXT NOT NULL DEFAULT 'orchestrator',
    scenario TEXT NOT NULL DEFAULT 'broadcast',
    created_at TIMESTAMPTZ DEFAULT NOW(),
    write_timestamp BIGINT NOT NULL
);

-- Enable replication for PowerSync
ALTER TABLE broadcast_rows REPLICA IDENTITY FULL;

-- =====================
-- Scenario 2: Scoped fan-out
-- Each write reaches only the clients that are members of its scope.
-- A scope is an arbitrary grouping of clients: 2 members models a pairwise
-- channel, 3-10 members models a small shared channel.
-- =====================
CREATE TABLE IF NOT EXISTS scopes (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    label TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

ALTER TABLE scopes REPLICA IDENTITY FULL;

CREATE TABLE IF NOT EXISTS scope_members (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    scope_id UUID NOT NULL REFERENCES scopes(id),
    user_id TEXT NOT NULL,
    UNIQUE(scope_id, user_id)
);
CREATE INDEX IF NOT EXISTS idx_scope_members_user_id ON scope_members(user_id);
CREATE INDEX IF NOT EXISTS idx_scope_members_scope_id ON scope_members(scope_id);

ALTER TABLE scope_members REPLICA IDENTITY FULL;

CREATE TABLE IF NOT EXISTS scoped_rows (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    scope_id UUID NOT NULL REFERENCES scopes(id),
    writer_id TEXT NOT NULL,
    payload TEXT NOT NULL,
    scenario TEXT NOT NULL DEFAULT 'scoped-fanout',
    write_timestamp BIGINT NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_scoped_rows_scope_id ON scoped_rows(scope_id);

ALTER TABLE scoped_rows REPLICA IDENTITY FULL;

-- =====================
-- Scenario 3: Initial sync
-- Each client syncs its own accumulated backlog on connect.
-- =====================
CREATE TABLE IF NOT EXISTS backlog_rows (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    -- Per-client ownership, so this measures a realistic per-client backlog
    -- rather than one shared set of N rows synced to every client.
    owner_id TEXT NOT NULL,
    label TEXT NOT NULL,
    payload TEXT,
    scenario TEXT NOT NULL DEFAULT 'initial-sync',
    write_timestamp BIGINT NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_backlog_rows_owner_id ON backlog_rows(owner_id);
CREATE INDEX IF NOT EXISTS idx_backlog_rows_scenario ON backlog_rows(scenario);

ALTER TABLE backlog_rows REPLICA IDENTITY FULL;

-- =====================
-- Metrics Tables
-- =====================

-- Raw latency samples (one per synced row per client)
CREATE TABLE IF NOT EXISTS latency_samples (
    id BIGSERIAL PRIMARY KEY,
    run_id TEXT NOT NULL,
    scenario TEXT NOT NULL,
    user_id TEXT NOT NULL,
    table_name TEXT NOT NULL,
    row_id TEXT,
    write_timestamp BIGINT NOT NULL,
    receive_timestamp BIGINT NOT NULL,
    end_to_end_latency_ms BIGINT NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_latency_samples_run_id ON latency_samples(run_id);

-- Sync completion samples (for the initial-sync scenario)
CREATE TABLE IF NOT EXISTS sync_complete_samples (
    id BIGSERIAL PRIMARY KEY,
    run_id TEXT NOT NULL,
    scenario TEXT NOT NULL,
    user_id TEXT NOT NULL,
    duration_ms BIGINT NOT NULL,
    rows_synced INT,
    created_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_sync_complete_samples_run_id ON sync_complete_samples(run_id);

-- Aggregated results per test run
CREATE TABLE IF NOT EXISTS scenario_results (
    id BIGSERIAL PRIMARY KEY,
    run_id TEXT NOT NULL UNIQUE,
    scenario TEXT NOT NULL,
    total_clients INT NOT NULL,
    total_writes INT DEFAULT 0,
    total_latency_samples INT DEFAULT 0,
    p50_ms DOUBLE PRECISION,
    p95_ms DOUBLE PRECISION,
    p99_ms DOUBLE PRECISION,
    min_ms DOUBLE PRECISION,
    max_ms DOUBLE PRECISION,
    mean_ms DOUBLE PRECISION,
    -- Checksum: connection integrity tracking
    connected_at_write_time INT DEFAULT 0,
    clients_that_received INT DEFAULT 0,
    total_disconnects INT DEFAULT 0,
    -- Denominator for clients_that_received: how many clients were expected
    -- to receive at least one row (for fan-out scenarios). 0 means "all connected".
    expected_recipients INT DEFAULT 0,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Per-run metadata written by the orchestrator (e.g., expected recipient
-- denominator for fan-out scenarios). Optional; aggregator tolerates absence.
CREATE TABLE IF NOT EXISTS run_metadata (
    run_id TEXT PRIMARY KEY,
    expected_recipients INT,
    total_writes INT,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Client readiness signals (for Cloud Run coordination)
CREATE TABLE IF NOT EXISTS client_ready_signals (
    id BIGSERIAL PRIMARY KEY,
    run_id TEXT NOT NULL,
    task_index INT NOT NULL,
    clients_ready INT NOT NULL,
    clients_alive INT NOT NULL DEFAULT 0,
    disconnect_count INT NOT NULL DEFAULT 0,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    UNIQUE(run_id, task_index)
);
CREATE INDEX IF NOT EXISTS idx_client_ready_signals_run_id ON client_ready_signals(run_id);



-- Create a role/user with replication privileges for PowerSync.
--
-- DELIBERATELY COMMENTED OUT. This role bypasses RLS, so it must never be
-- created with a password that lives in version control. Uncomment the
-- statement below, substitute a strong randomly generated password, and store
-- that password in your secret manager -- do not commit it.
--
-- CREATE ROLE powersync_role WITH REPLICATION BYPASSRLS LOGIN PASSWORD 'replace-me';
-- Set up permissions for the newly created role
-- Read-only (SELECT) access is required
GRANT SELECT ON ALL TABLES IN SCHEMA public TO powersync_role;  

-- Optionally, grant SELECT on all future tables (to cater for schema additions)
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO powersync_role;

-- Create a publication to replicate tables. The publication must be named "powersync"
DROP PUBLICATION IF EXISTS powersync;
CREATE PUBLICATION powersync FOR ALL TABLES;