DH EMR cloud project

Database setup scripts

These set up and check the Supabase project your organization owns. The step-by-step is in the guides: Setting up DH EMR Clinic and Cloud sync setup for DH EMR Field. In short: open your project's SQL Editor, start a new query, press Copy under setup.sql below, paste, and click Run; then do the same with verify.sql.

-- ============================================================================
-- DH EMR cloud schema v4.1
--
-- One-shot setup for a new org project. Paste the ENTIRE file into the
-- Supabase SQL Editor and click Run. Safe to re-run: tables and columns use
-- IF NOT EXISTS, policies and triggers are drop-then-create.
--
-- Carries forward every v3.0 hardening rule (device kill switch, first
-- admin wins for DEVICES, immutable ids, soft deletes only, server-side
-- synced_at) and adds what the two-mode product needs:
--
--   FIELD MODE  (offline-first, shared publishable key, device identity)
--     Works exactly like v3.0. The org never creates user accounts.
--
--   CLINIC MODE (connected, per-user Supabase Auth, roles, patient flow)
--     Each staff member signs in with their own account. The shared-key
--     (anon) surface for records, devices and config CLOSES the moment the
--     org switches mode; the single exception is the orgMode config row,
--     which stays readable so a device holding only URL + key can discover
--     that it must show a sign-in screen.
--
-- The mode lives in config under key 'orgMode' ({"mode":"field"} or
-- {"mode":"clinic"}); absent means field, so v3.0 orgs upgrade in place
-- with no behavior change.
--
-- STAFF ACCOUNT BOOTSTRAP (deliberate design, do not "simplify"):
-- signing up NEVER grants anything. Every new account starts pending
-- (not activated, not admin). The FIRST admin is created by the operator
-- with one SQL line (SETUP.md section 4) right after running this file;
-- after that, admins approve accounts inside the app. There is no
-- auto-admin path: the publishable key is shared by design and Supabase
-- signups are open by default, so any signup-time privilege grant would
-- be claimable by anyone holding the key, including exactly during the
-- windows (virgin project, sole admin revoked) when it matters most.
-- The device fleet keeps v3.0's first-admin-wins because device
-- registration is the field-mode enrollment path and carries no
-- cross-user authority; staff accounts DO carry authority, so they get
-- the stricter rule.
--
-- AUTHENTICATED ACCESS WORKS IN BOTH MODES (deliberate): an org moving to
-- clinic mode onboards staff and drains every field iPad's unsynced
-- records BEFORE flipping the mode; the flip then closes the anon surface
-- with nothing stranded. Without SQL-editor bootstrap the authenticated
-- surface is inert (no activated accounts can exist), so leaving the
-- Email provider enabled on a field org exposes nothing.
--
-- HONEST LIMIT (field mode, unchanged from v3.0): every device shares one
-- key, so Postgres cannot tell devices apart and anyone holding the key
-- can read everything. Clinic mode is the fix; field mode documents the
-- limit.
-- ============================================================================


-- ============================================================================
-- 1. TABLES
-- ============================================================================

CREATE TABLE IF NOT EXISTS records (
  id UUID PRIMARY KEY,                     -- client-generated, no default
  device_id TEXT,
  site TEXT,
  date TEXT,                               -- encounter date as TEXT; client owns formatting
  mrn TEXT,
  given_name TEXT,
  family_name TEXT,
  name TEXT,
  sex TEXT,
  dob TEXT,
  phone TEXT,
  pregnant TEXT,
  breastfeeding TEXT,
  temp TEXT,
  bp TEXT,
  weight TEXT,
  allergies TEXT,
  current_meds TEXT,
  pmh TEXT,
  chief_concern TEXT,
  labs JSONB DEFAULT '{}'::jsonb,
  lab_comments TEXT,
  urinalysis JSONB DEFAULT '{}'::jsonb,
  blood_glucose TEXT,
  diagnosis TEXT,
  diagnosis_codes JSONB DEFAULT '[]'::jsonb,
  medications JSONB DEFAULT '[]'::jsonb,
  treatment_notes TEXT,
  treatment TEXT,
  procedures JSONB DEFAULT '[]'::jsonb,
  transport TEXT,
  travel_time TEXT,
  access_to_care JSONB,
  referral_type TEXT,
  referral_date TEXT,
  referral_status TEXT,
  imaging JSONB,
  surgery JSONB,
  provider TEXT,
  notes TEXT,
  custom_fields JSONB DEFAULT '{}'::jsonb,
  template_id TEXT,
  template_name TEXT,
  age_estimated BOOLEAN DEFAULT false,
  saved_at TIMESTAMPTZ,                    -- client clock; unreliable, display only
  sync_version INTEGER DEFAULT 1,
  deleted BOOLEAN DEFAULT false,           -- soft delete; nothing hard-deletes
  synced_at TIMESTAMPTZ DEFAULT now(),     -- server clock, trigger-maintained
  -- v4 additions
  user_id UUID,                            -- clinic mode: auth.uid() of the creator
  flow_station TEXT,                       -- clinic mode: current station on the flow board
  flow_updated_at TIMESTAMPTZ              -- when the station last changed
);

-- Idempotent upgrades for projects created on older schemas.
ALTER TABLE records ADD COLUMN IF NOT EXISTS diagnosis_codes JSONB DEFAULT '[]'::jsonb;
ALTER TABLE records ADD COLUMN IF NOT EXISTS custom_fields JSONB DEFAULT '{}'::jsonb;
ALTER TABLE records ADD COLUMN IF NOT EXISTS template_id TEXT;
ALTER TABLE records ADD COLUMN IF NOT EXISTS template_name TEXT;
ALTER TABLE records ADD COLUMN IF NOT EXISTS user_id UUID;
ALTER TABLE records ADD COLUMN IF NOT EXISTS flow_station TEXT;
ALTER TABLE records ADD COLUMN IF NOT EXISTS flow_updated_at TIMESTAMPTZ;

CREATE TABLE IF NOT EXISTS devices (
  id TEXT PRIMARY KEY,                     -- client-chosen id; TEXT, not UUID
  name TEXT NOT NULL,
  role TEXT DEFAULT 'standard',            -- 'standard' | 'admin'; trigger-enforced
  org_name TEXT,
  last_sync_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ DEFAULT now(),
  revoked_at TIMESTAMPTZ                   -- kill switch: NULL = active
);
ALTER TABLE devices ADD COLUMN IF NOT EXISTS revoked_at TIMESTAMPTZ;
COMMENT ON COLUMN devices.revoked_at IS
  'Set to now() to cut a lost or stolen device off from writing. Records it already pushed are kept. Only the SQL editor can clear this.';

CREATE TABLE IF NOT EXISTS config (
  key TEXT PRIMARY KEY,
  value JSONB,
  updated_at TIMESTAMPTZ DEFAULT now()     -- trigger-maintained in v4 (v3 was default-only)
);

-- Clinic mode staff accounts. One row per auth user.
-- role is the clinical station role (reception | nurse | provider |
-- pharmacy); admin is a separate flag because a provider can also be the
-- org admin. TEXT without a CHECK on purpose, like devices.role: the org
-- customizes its station list in config and the client enforces it.
CREATE TABLE IF NOT EXISTS users_profiles (
  id UUID PRIMARY KEY REFERENCES auth.users (id) ON DELETE CASCADE,
  display_name TEXT NOT NULL DEFAULT '',
  role TEXT DEFAULT 'provider',
  is_admin BOOLEAN NOT NULL DEFAULT false, -- NOT NULL: a NULL here would slip
                                           -- through boolean guards as
                                           -- "unknown" and dodge the
                                           -- last-admin protection
  activated_at TIMESTAMPTZ,                -- NULL = awaiting admin approval
  created_at TIMESTAMPTZ DEFAULT now(),
  revoked_at TIMESTAMPTZ                   -- kill switch, same semantics as devices
);
-- Idempotent hardening for projects that created the table before the
-- NOT NULL constraint existed.
UPDATE users_profiles SET is_admin = false WHERE is_admin IS NULL;
ALTER TABLE users_profiles ALTER COLUMN is_admin SET NOT NULL;
COMMENT ON COLUMN users_profiles.activated_at IS
  'NULL means the account is waiting for approval. The first admin is created from the SQL editor (SETUP.md section 4); after that, admins approve accounts in the app.';
COMMENT ON COLUMN users_profiles.revoked_at IS
  'Set to now() to lock the account out. Only an admin or the SQL editor can clear this.';


-- ============================================================================
-- 2. HELPER FUNCTIONS
--
-- All three are SECURITY DEFINER, and that is REQUIRED, not a convenience:
-- these helpers are called from inside RLS policies on the very tables
-- they read (config policies call dh_org_mode which reads config; profile
-- policies call dh_active_profile which reads users_profiles). As invoker,
-- the inner read would re-enter the same policy and Postgres would abort
-- with infinite recursion. As definer they bypass RLS for a single
-- self-filtered read; none of them contains current_user logic, so the
-- v3.0 "never SECURITY DEFINER" rule (which is about the TRIGGERS below,
-- where current_user distinguishes app callers from the SQL editor) does
-- not apply to them.
--
-- Corollary: never add FORCE ROW LEVEL SECURITY to any of these tables.
-- The definer bypass works because the table owner is exempt from RLS;
-- FORCE removes that exemption and every helper re-enters its own
-- table's policies - all four tables then fail closed with recursion
-- errors.
-- ============================================================================

-- The org's mode. Absent config row means 'field' so v3.0 projects upgrade
-- with no behavior change.
CREATE OR REPLACE FUNCTION dh_org_mode()
RETURNS TEXT
LANGUAGE sql STABLE
SECURITY DEFINER
SET search_path = public, pg_temp
AS $$
  SELECT COALESCE((SELECT value->>'mode' FROM config WHERE key = 'orgMode'), 'field');
$$;

-- Caller has an activated, non-revoked staff account.
CREATE OR REPLACE FUNCTION dh_active_profile()
RETURNS BOOLEAN
LANGUAGE sql STABLE
SECURITY DEFINER
SET search_path = public, pg_temp
AS $$
  SELECT EXISTS (
    SELECT 1 FROM users_profiles p
    WHERE p.id = auth.uid()
      AND p.activated_at IS NOT NULL
      AND p.revoked_at IS NULL
  );
$$;

-- Caller is an active admin.
CREATE OR REPLACE FUNCTION dh_is_admin()
RETURNS BOOLEAN
LANGUAGE sql STABLE
SECURITY DEFINER
SET search_path = public, pg_temp
AS $$
  SELECT EXISTS (
    SELECT 1 FROM users_profiles p
    WHERE p.id = auth.uid()
      AND p.is_admin
      AND p.activated_at IS NOT NULL
      AND p.revoked_at IS NULL
  );
$$;


-- ============================================================================
-- 3. DEVICE RULES TRIGGER (unchanged semantics from v3.0)
--
-- RLS cannot compare OLD and NEW, so the rules that need that live here.
--
-- Deliberately NOT SECURITY DEFINER: inside a SECURITY DEFINER function
-- current_user is the function OWNER, not the caller, so the bypass check
-- below would match on every call and the trigger would enforce nothing at
-- all. As security invoker, current_user is 'anon' or 'authenticated' for
-- an app caller and 'postgres' or 'service_role' from the SQL editor,
-- which is exactly the distinction needed.
-- ============================================================================

CREATE OR REPLACE FUNCTION dh_enforce_device_rules()
RETURNS TRIGGER
LANGUAGE plpgsql
SET search_path = public, pg_temp
AS $$
DECLARE
  other_admins INT;
BEGIN
  -- The SQL editor and service_role skip all rules; this is how the
  -- operator promotes, demotes, or clears a revocation.
  IF current_user NOT IN ('anon', 'authenticated') THEN
    RETURN NEW;
  END IF;

  IF TG_OP = 'INSERT' THEN
    -- First admin wins: a brand-new device may register as admin ONLY when
    -- the project has zero active admin devices (genuine first run, so the
    -- org creator gets an admin device without opening the SQL editor).
    -- Silent demotion, no error. Device admin only means "may edit org
    -- config from this device in field mode"; it grants nothing over
    -- other devices or staff accounts.
    IF COALESCE(NEW.role, 'standard') = 'admin' THEN
      SELECT count(*) INTO other_admins
      FROM devices WHERE role = 'admin' AND revoked_at IS NULL;
      IF other_admins > 0 THEN
        NEW.role := 'standard';
      END IF;
    END IF;
    -- A device cannot register itself pre-revoked, or pre-dated.
    NEW.revoked_at := NULL;
    RETURN NEW;
  END IF;

  -- UPDATE rules.
  -- Rewriting a device id would orphan every record that points at it.
  IF NEW.id IS DISTINCT FROM OLD.id THEN
    RAISE EXCEPTION 'A device id cannot be changed.';
  END IF;

  -- Revocation is sticky. Without this the kill switch is decorative.
  IF OLD.revoked_at IS NOT NULL AND NEW.revoked_at IS NULL THEN
    RAISE EXCEPTION 'This device has been revoked. Only an administrator can restore it.';
  END IF;

  -- Promotion via the app is allowed only when NO other active admin
  -- device exists (recovery path when the admin fleet is empty or
  -- revoked). Demotion is always allowed: it can only reduce access.
  IF NEW.role IS DISTINCT FROM OLD.role AND NEW.role = 'admin' THEN
    SELECT count(*) INTO other_admins
    FROM devices
    WHERE role = 'admin' AND revoked_at IS NULL AND id <> NEW.id;
    IF other_admins > 0 THEN
      RAISE EXCEPTION 'Only an administrator can make another device an admin. Ask whoever set up this project.';
    END IF;
  END IF;

  RETURN NEW;
END;
$$;

DROP TRIGGER IF EXISTS trg_devices_rules ON devices;
CREATE TRIGGER trg_devices_rules
  BEFORE INSERT OR UPDATE ON devices
  FOR EACH ROW
  EXECUTE FUNCTION dh_enforce_device_rules();


-- ============================================================================
-- 4. USER PROFILE RULES TRIGGER (clinic mode)
--
-- Stricter than the device rules, on purpose (see the bootstrap note in
-- the header): app callers can never grant admin, never activate, never
-- revoke or restore except through an admin account, and signing up never
-- yields anything but a pending row. SECURITY INVOKER for the same reason
-- as the device trigger. Note the SQL-editor path (signup trigger runs as
-- definer) bypasses this function entirely; the signup function itself
-- only ever writes pending rows.
-- ============================================================================

CREATE OR REPLACE FUNCTION dh_enforce_user_rules()
RETURNS TRIGGER
LANGUAGE plpgsql
SET search_path = public, pg_temp
AS $$
DECLARE
  other_admins INT;
BEGIN
  IF current_user NOT IN ('anon', 'authenticated') THEN
    RETURN NEW;
  END IF;

  IF TG_OP = 'INSERT' THEN
    -- No self-service privileges, ever. There is deliberately NO
    -- first-admin window here (unlike devices): the first admin comes
    -- from the SQL editor.
    NEW.is_admin := false;
    NEW.activated_at := NULL;
    NEW.revoked_at := NULL;
    RETURN NEW;
  END IF;

  -- UPDATE rules.
  IF NEW.id IS DISTINCT FROM OLD.id THEN
    RAISE EXCEPTION 'An account id cannot be changed.';
  END IF;

  IF NOT dh_is_admin() THEN
    -- Non-admins may edit their own display name and station role only.
    IF NEW.is_admin IS DISTINCT FROM OLD.is_admin THEN
      RAISE EXCEPTION 'Only an administrator can change admin status.';
    END IF;
    IF NEW.activated_at IS DISTINCT FROM OLD.activated_at THEN
      RAISE EXCEPTION 'Only an administrator can approve an account.';
    END IF;
    IF NEW.revoked_at IS DISTINCT FROM OLD.revoked_at THEN
      RAISE EXCEPTION 'Only an administrator can revoke or restore an account.';
    END IF;
  ELSE
    -- The last active admin cannot remove or disable themselves; the org
    -- would be locked out of its own settings and, because there is no
    -- self-service admin path, only the SQL editor could recover it.
    -- Guard all three ways an admin could vanish: demotion, revocation,
    -- deactivation. The comparison is null-strict (IS DISTINCT FROM true)
    -- and the count runs under an advisory lock so two admins removing
    -- themselves at the same moment cannot both slip through.
    IF OLD.id = auth.uid()
       AND OLD.is_admin AND OLD.activated_at IS NOT NULL AND OLD.revoked_at IS NULL
       AND (NEW.is_admin IS DISTINCT FROM true
            OR NEW.activated_at IS NULL
            OR NEW.revoked_at IS NOT NULL)
    THEN
      PERFORM pg_advisory_xact_lock(hashtext('dh_users_admin_guard'));
      SELECT count(*) INTO other_admins
      FROM users_profiles
      WHERE is_admin AND activated_at IS NOT NULL AND revoked_at IS NULL
        AND id <> OLD.id;
      IF other_admins = 0 THEN
        RAISE EXCEPTION 'You are the only administrator. Make someone else an admin first.';
      END IF;
    END IF;
  END IF;

  RETURN NEW;
END;
$$;

DROP TRIGGER IF EXISTS trg_users_rules ON users_profiles;
CREATE TRIGGER trg_users_rules
  BEFORE INSERT OR UPDATE ON users_profiles
  FOR EACH ROW
  EXECUTE FUNCTION dh_enforce_user_rules();


-- ============================================================================
-- 5. PROFILE AUTO-CREATION ON SIGNUP
--
-- Standard Supabase pattern. SECURITY DEFINER is REQUIRED (the trigger
-- fires in auth schema context, which cannot write public tables as the
-- caller), and it is safe ONLY because this function grants nothing: the
-- row it inserts is always pending (is_admin false, activated_at NULL).
-- Do not add privilege logic here; as definer it bypasses the rules
-- trigger's app-caller checks, so anything written here is law.
-- ============================================================================

CREATE OR REPLACE FUNCTION dh_handle_new_user()
RETURNS TRIGGER
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public, pg_temp
AS $$
BEGIN
  INSERT INTO users_profiles (id, display_name, is_admin, activated_at)
  VALUES (
    NEW.id,
    COALESCE(NEW.raw_user_meta_data->>'display_name', split_part(NEW.email, '@', 1), ''),
    false,
    NULL
  )
  ON CONFLICT (id) DO NOTHING;
  RETURN NEW;
END;
$$;

DROP TRIGGER IF EXISTS trg_on_auth_user_created ON auth.users;
CREATE TRIGGER trg_on_auth_user_created
  AFTER INSERT ON auth.users
  FOR EACH ROW
  EXECUTE FUNCTION dh_handle_new_user();


-- ============================================================================
-- 6. RECORD RULES TRIGGER (v4)
--
-- Identity and attribution are immutable through the app, in both modes.
-- v3 had no trigger on records at all; clinic mode makes one necessary
-- because per-user attribution is only worth having if staff cannot
-- rewrite it. SECURITY INVOKER, same reasoning as the device trigger.
-- ============================================================================

CREATE OR REPLACE FUNCTION dh_enforce_record_rules()
RETURNS TRIGGER
LANGUAGE plpgsql
SET search_path = public, pg_temp
AS $$
BEGIN
  IF current_user NOT IN ('anon', 'authenticated') THEN
    RETURN NEW;
  END IF;

  IF TG_OP = 'INSERT' THEN
    -- A device (anon has no auth.uid) can never claim authorship for a
    -- new row; a signed-in insert is already forced to self-attribute by
    -- the auth_insert_records policy.
    IF auth.uid() IS NULL THEN
      NEW.user_id := NULL;
    END IF;
    RETURN NEW;
  END IF;

  -- UPDATE rules.
  -- Rewriting a record id would break sync identity on every device.
  IF NEW.id IS DISTINCT FROM OLD.id THEN
    RAISE EXCEPTION 'A record id cannot be changed.';
  END IF;

  -- Attribution rules. Two failure modes bound this design:
  -- a raise on anon writes would wedge a whole device's sync batch the
  -- first time a field iPad round-trips a clinic-era row whose user_id
  -- it never knew (one poisoned row aborts a PostgREST bulk upsert),
  -- and a naive uuid = auth.uid() comparison is NULL for anon callers,
  -- which plpgsql IF treats as false and the guard silently passes.
  -- So: devices get COERCED (attribution preserved, sync never wedges);
  -- signed-in users may backfill an unattributed row to themselves,
  -- admins may reassign, and everything else raises.
  IF NEW.user_id IS DISTINCT FROM OLD.user_id THEN
    IF auth.uid() IS NULL THEN
      NEW.user_id := OLD.user_id;
    ELSIF NOT (OLD.user_id IS NULL AND NEW.user_id = auth.uid())
          AND NOT dh_is_admin() THEN
      RAISE EXCEPTION 'The author of a visit cannot be changed.';
    END IF;
  END IF;

  RETURN NEW;
END;
$$;

DROP TRIGGER IF EXISTS trg_records_rules ON records;
CREATE TRIGGER trg_records_rules
  BEFORE INSERT OR UPDATE ON records
  FOR EACH ROW
  EXECUTE FUNCTION dh_enforce_record_rules();


-- ============================================================================
-- 7. SERVER TIMESTAMPS
-- ============================================================================

-- Server-side timestamp on every records write (field device clocks are
-- unreliable). Incremental pull keys off this.
CREATE OR REPLACE FUNCTION update_synced_at()
RETURNS TRIGGER
LANGUAGE plpgsql
SET search_path = public, pg_temp
AS $$
BEGIN
  NEW.synced_at = now();
  RETURN NEW;
END;
$$;

DROP TRIGGER IF EXISTS trg_records_synced_at ON records;
CREATE TRIGGER trg_records_synced_at
  BEFORE INSERT OR UPDATE ON records
  FOR EACH ROW
  EXECUTE FUNCTION update_synced_at();

-- v4: config.updated_at is now trigger-maintained, so devices can detect
-- config changes reliably (v3 left it to the client and it drifted).
CREATE OR REPLACE FUNCTION dh_touch_config_updated_at()
RETURNS TRIGGER
LANGUAGE plpgsql
SET search_path = public, pg_temp
AS $$
BEGIN
  NEW.updated_at = now();
  RETURN NEW;
END;
$$;

DROP TRIGGER IF EXISTS trg_config_updated_at ON config;
CREATE TRIGGER trg_config_updated_at
  BEFORE INSERT OR UPDATE ON config
  FOR EACH ROW
  EXECUTE FUNCTION dh_touch_config_updated_at();


-- ============================================================================
-- 8. CONFIG RULES TRIGGER
--
-- The orgMode row is the master switch, so it gets special handling:
-- - The shared key can never flip the org into clinic mode (that would
--   lock every field device out: a denial of service by anyone holding
--   the key).
-- - Only an admin account can change the mode in either direction.
--   Flipping BACK to field mode through the app is deliberately allowed
--   for admins: it is the recovery path when records were stranded on
--   field devices, and SETUP.md documents the sequence. It reopens the
--   shared-key surface, so the app must confirm loudly.
-- ============================================================================

CREATE OR REPLACE FUNCTION dh_enforce_config_rules()
RETURNS TRIGGER
LANGUAGE plpgsql
SET search_path = public, pg_temp
AS $$
BEGIN
  IF current_user NOT IN ('anon', 'authenticated') THEN
    RETURN NEW;
  END IF;

  IF NEW.key = 'orgMode' THEN
    IF COALESCE(NEW.value->>'mode', 'field') <> 'field' AND NOT dh_is_admin() THEN
      RAISE EXCEPTION 'Only an administrator account can switch this organization to clinic mode.';
    END IF;
    IF TG_OP = 'UPDATE'
       AND COALESCE(OLD.value->>'mode', 'field') = 'clinic'
       AND COALESCE(NEW.value->>'mode', 'field') <> 'clinic'
       AND NOT dh_is_admin() THEN
      RAISE EXCEPTION 'Only an administrator account can change this organization''s mode.';
    END IF;
  END IF;

  RETURN NEW;
END;
$$;

DROP TRIGGER IF EXISTS trg_config_rules ON config;
CREATE TRIGGER trg_config_rules
  BEFORE INSERT OR UPDATE ON config
  FOR EACH ROW
  EXECUTE FUNCTION dh_enforce_config_rules();


-- ============================================================================
-- 9. ROW LEVEL SECURITY
--
-- anon policies = FIELD MODE, gated on dh_org_mode(); they reproduce v3.0
-- exactly while the org is in field mode and evaporate in clinic mode
-- (single exception: the orgMode config row stays anon-readable for
-- bootstrap discovery).
-- authenticated policies = staff accounts; they work in BOTH modes (see
-- header) but are inert until the SQL-editor bootstrap creates an admin.
-- WITH CHECK is always spelled out rather than left implicit, so a later
-- edit to USING cannot silently widen what may be written.
-- No DELETE for app roles anywhere: records use the soft-delete flag.
-- ============================================================================

ALTER TABLE records ENABLE ROW LEVEL SECURITY;
ALTER TABLE devices ENABLE ROW LEVEL SECURITY;
ALTER TABLE config ENABLE ROW LEVEL SECURITY;
ALTER TABLE users_profiles ENABLE ROW LEVEL SECURITY;

-- ---- records: field mode (anon) ----
DROP POLICY IF EXISTS "anon_read_records" ON records;
CREATE POLICY "anon_read_records" ON records
  FOR SELECT TO anon
  USING ((SELECT dh_org_mode()) = 'field');

DROP POLICY IF EXISTS "anon_insert_records" ON records;
CREATE POLICY "anon_insert_records" ON records
  FOR INSERT TO anon
  WITH CHECK (
    (SELECT dh_org_mode()) = 'field'
    AND device_id IS NOT NULL
    AND EXISTS (SELECT 1 FROM devices d WHERE d.id = device_id AND d.revoked_at IS NULL)
  );

DROP POLICY IF EXISTS "anon_update_records" ON records;
CREATE POLICY "anon_update_records" ON records
  FOR UPDATE TO anon
  USING (
    (SELECT dh_org_mode()) = 'field'
    AND device_id IS NOT NULL
    AND EXISTS (SELECT 1 FROM devices d WHERE d.id = device_id AND d.revoked_at IS NULL)
  )
  WITH CHECK (
    (SELECT dh_org_mode()) = 'field'
    AND device_id IS NOT NULL
    AND EXISTS (SELECT 1 FROM devices d WHERE d.id = device_id AND d.revoked_at IS NULL)
  );

-- ---- records: staff accounts (authenticated) ----
-- Helpers are wrapped in scalar subqueries so they evaluate once per
-- statement (initplan), not once per row - same pattern as the anon
-- policies' dh_org_mode() calls.
DROP POLICY IF EXISTS "auth_read_records" ON records;
CREATE POLICY "auth_read_records" ON records
  FOR SELECT TO authenticated
  USING ((SELECT dh_active_profile()));

DROP POLICY IF EXISTS "auth_insert_records" ON records;
CREATE POLICY "auth_insert_records" ON records
  FOR INSERT TO authenticated
  WITH CHECK (
    (SELECT dh_active_profile())
    AND user_id = auth.uid()
    AND device_id IS NOT NULL
    AND EXISTS (SELECT 1 FROM devices d WHERE d.id = device_id AND d.revoked_at IS NULL)
  );

DROP POLICY IF EXISTS "auth_update_records" ON records;
CREATE POLICY "auth_update_records" ON records
  FOR UPDATE TO authenticated
  USING ((SELECT dh_active_profile()))
  WITH CHECK (
    (SELECT dh_active_profile())
    AND device_id IS NOT NULL
    AND EXISTS (SELECT 1 FROM devices d WHERE d.id = device_id AND d.revoked_at IS NULL)
  );

-- ---- devices ----
-- anon read is field-mode only. (The device-rules trigger's admin-count
-- SELECT runs as the caller, but anon can only reach that trigger through
-- the field-gated insert/update policies, so gating the read costs
-- nothing and stops clinic-mode fleet enumeration by old key holders.)
DROP POLICY IF EXISTS "anon_read_devices" ON devices;
CREATE POLICY "anon_read_devices" ON devices
  FOR SELECT TO anon
  USING ((SELECT dh_org_mode()) = 'field');

DROP POLICY IF EXISTS "anon_insert_devices" ON devices;
CREATE POLICY "anon_insert_devices" ON devices
  FOR INSERT TO anon
  WITH CHECK ((SELECT dh_org_mode()) = 'field');

DROP POLICY IF EXISTS "anon_update_devices" ON devices;
CREATE POLICY "anon_update_devices" ON devices
  FOR UPDATE TO anon
  USING ((SELECT dh_org_mode()) = 'field')
  WITH CHECK ((SELECT dh_org_mode()) = 'field');

DROP POLICY IF EXISTS "auth_read_devices" ON devices;
CREATE POLICY "auth_read_devices" ON devices
  FOR SELECT TO authenticated
  USING ((SELECT dh_active_profile()));

DROP POLICY IF EXISTS "auth_insert_devices" ON devices;
CREATE POLICY "auth_insert_devices" ON devices
  FOR INSERT TO authenticated
  WITH CHECK ((SELECT dh_active_profile()));

DROP POLICY IF EXISTS "auth_update_devices" ON devices;
CREATE POLICY "auth_update_devices" ON devices
  FOR UPDATE TO authenticated
  USING ((SELECT dh_active_profile()))
  WITH CHECK ((SELECT dh_active_profile()));

-- ---- config ----
-- anon: full read in field mode; in clinic mode exactly ONE row stays
-- visible (orgMode), so a device holding only URL + key can discover it
-- must show the sign-in screen. Config never carries credentials
-- (working rule, enforced by review).
DROP POLICY IF EXISTS "anon_read_config" ON config;
CREATE POLICY "anon_read_config" ON config
  FOR SELECT TO anon
  USING ((SELECT dh_org_mode()) = 'field' OR key = 'orgMode');

DROP POLICY IF EXISTS "anon_write_config" ON config;
CREATE POLICY "anon_write_config" ON config
  FOR INSERT TO anon
  WITH CHECK ((SELECT dh_org_mode()) = 'field');

DROP POLICY IF EXISTS "anon_update_config" ON config;
CREATE POLICY "anon_update_config" ON config
  FOR UPDATE TO anon
  USING ((SELECT dh_org_mode()) = 'field')
  WITH CHECK ((SELECT dh_org_mode()) = 'field');

-- Staff read config once active; pending accounts see only orgMode.
DROP POLICY IF EXISTS "auth_read_config" ON config;
CREATE POLICY "auth_read_config" ON config
  FOR SELECT TO authenticated
  USING ((SELECT dh_active_profile()) OR key = 'orgMode');

-- Staff config writes are ADMIN ONLY - tighter than field mode, where the
-- client gates admin actions (documented honest limit).
DROP POLICY IF EXISTS "auth_write_config" ON config;
CREATE POLICY "auth_write_config" ON config
  FOR INSERT TO authenticated
  WITH CHECK ((SELECT dh_is_admin()));

DROP POLICY IF EXISTS "auth_update_config" ON config;
CREATE POLICY "auth_update_config" ON config
  FOR UPDATE TO authenticated
  USING ((SELECT dh_is_admin()))
  WITH CHECK ((SELECT dh_is_admin()));

-- ---- users_profiles ----
-- Active staff see the roster (the flow board shows names). A pending or
-- revoked account sees ONLY its own row (the app needs it to render the
-- "awaiting approval" screen). anon sees nothing.
DROP POLICY IF EXISTS "auth_read_profiles" ON users_profiles;
CREATE POLICY "auth_read_profiles" ON users_profiles
  FOR SELECT TO authenticated
  USING (id = auth.uid() OR (SELECT dh_active_profile()));

-- Signup normally flows through the auth trigger; direct insert is limited
-- to the caller's own row and the rules trigger forces it pending.
DROP POLICY IF EXISTS "auth_insert_profiles" ON users_profiles;
CREATE POLICY "auth_insert_profiles" ON users_profiles
  FOR INSERT TO authenticated
  WITH CHECK (id = auth.uid());

-- Self-edits (display name, station role) plus admin edits of anyone;
-- the rules trigger decides which FIELDS each caller may change.
DROP POLICY IF EXISTS "auth_update_profiles" ON users_profiles;
CREATE POLICY "auth_update_profiles" ON users_profiles
  FOR UPDATE TO authenticated
  USING (id = auth.uid() OR (SELECT dh_is_admin()))
  WITH CHECK (id = auth.uid() OR (SELECT dh_is_admin()));

-- ---- no hard deletes, any table, any app role ----
DROP POLICY IF EXISTS "no_delete_records" ON records;
CREATE POLICY "no_delete_records" ON records FOR DELETE USING (false);
DROP POLICY IF EXISTS "no_delete_devices" ON devices;
CREATE POLICY "no_delete_devices" ON devices FOR DELETE USING (false);
DROP POLICY IF EXISTS "no_delete_config" ON config;
CREATE POLICY "no_delete_config" ON config FOR DELETE USING (false);
DROP POLICY IF EXISTS "no_delete_profiles" ON users_profiles;
CREATE POLICY "no_delete_profiles" ON users_profiles FOR DELETE USING (false);


-- ============================================================================
-- 10. INDEXES
-- ============================================================================

CREATE INDEX IF NOT EXISTS idx_records_date ON records (date);
CREATE INDEX IF NOT EXISTS idx_records_site ON records (site);
CREATE INDEX IF NOT EXISTS idx_records_deleted ON records (deleted);
CREATE INDEX IF NOT EXISTS idx_records_device_id ON records (device_id);
CREATE INDEX IF NOT EXISTS idx_records_saved_at ON records (saved_at);
CREATE INDEX IF NOT EXISTS idx_records_mrn ON records (mrn);
CREATE INDEX IF NOT EXISTS idx_records_referral_type ON records (referral_type);
CREATE INDEX IF NOT EXISTS idx_records_referral_date ON records (referral_date);
CREATE INDEX IF NOT EXISTS idx_records_provider ON records (provider);
CREATE INDEX IF NOT EXISTS idx_records_synced_at ON records (synced_at);
-- v4: the two indexes that previously existed only in migrations, plus
-- keyset pull and flow board support.
CREATE INDEX IF NOT EXISTS idx_records_template_id ON records (template_id);
CREATE INDEX IF NOT EXISTS idx_devices_active ON devices (id) WHERE revoked_at IS NULL;
CREATE INDEX IF NOT EXISTS idx_records_synced_at_id ON records (synced_at, id);
CREATE INDEX IF NOT EXISTS idx_records_date_flow ON records (date, flow_station);


-- ============================================================================
-- 11. REALTIME
-- Optional but recommended. Without this a device subscribes successfully
-- and shows Live while never receiving anything, so records only move on
-- the periodic sync. Safe to re-run. devices and users_profiles are
-- deliberately NOT published: revocation lands on the next write or sync.
-- Realtime respects RLS, so clinic-mode anon subscribers receive nothing
-- from records and only the orgMode row from config.
-- ============================================================================

DO $$
BEGIN
  IF EXISTS (SELECT 1 FROM pg_publication WHERE pubname = 'supabase_realtime') THEN
    IF NOT EXISTS (SELECT 1 FROM pg_publication_tables
      WHERE pubname = 'supabase_realtime' AND schemaname = 'public' AND tablename = 'records') THEN
      ALTER PUBLICATION supabase_realtime ADD TABLE public.records;
    END IF;
    IF NOT EXISTS (SELECT 1 FROM pg_publication_tables
      WHERE pubname = 'supabase_realtime' AND schemaname = 'public' AND tablename = 'config') THEN
      ALTER PUBLICATION supabase_realtime ADD TABLE public.config;
    END IF;
  END IF;
END $$;

-- ============================================================================
-- DH EMR v4.1 verification suite
--
-- Paste the ENTIRE file into the Supabase SQL Editor and click Run AFTER
-- running setup.sql. It seeds throwaway rows, attacks the rules as the
-- anon and authenticated roles, prints a PASS/FAIL table, and ROLLS BACK,
-- so it changes nothing.
--
-- Every row must say PASS. Row 0 is the positive control: if it FAILS,
-- the anon role is missing table grants and every other PASS is
-- meaningless because writes were blocked by permissions, not by policy.
--
-- Section B (rows 9+) seeds rows directly in auth.users to simulate
-- signups. If your Supabase version rejects that insert, row 9 says SKIP
-- (with the database's reason) and the rows after it will show FAIL noise;
-- the section A results above stay valid either way, and the clinic-mode
-- checks can be done by hand per SETUP.md section 5.
--
-- Best run on a project WITHOUT real staff accounts yet (fresh project or
-- field-mode org). On a project with existing active admins, row 14 says
-- SKIP (the sole-admin guard cannot be probed when other admins exist).
-- ============================================================================

BEGIN;

CREATE TEMP TABLE dh_verify (n INT, result TEXT, check_name TEXT, detail TEXT) ON COMMIT DROP;
GRANT INSERT, SELECT ON dh_verify TO anon, authenticated;
CREATE TEMP TABLE dh_env (k TEXT PRIMARY KEY, v TEXT) ON COMMIT DROP;
GRANT SELECT ON dh_env TO anon, authenticated;

-- ---------------------------------------------------------------------------
-- Seed (as the trusted SQL-editor role; triggers bypass on purpose)
-- ---------------------------------------------------------------------------
DELETE FROM config WHERE key = 'orgMode';   -- start in field mode; all rolled back
INSERT INTO devices (id, name, role) VALUES ('dh-verify-admin', 'verify admin device', 'admin');
INSERT INTO devices (id, name, role) VALUES ('dh-verify-std', 'verify standard device', 'standard');
INSERT INTO devices (id, name, role, revoked_at) VALUES ('dh-verify-revoked', 'verify revoked device', 'standard', now());
INSERT INTO records (id, device_id, given_name, mrn)
VALUES ('00000000-0000-4000-8000-00000000d0c1', 'dh-verify-revoked', 'RevokedWrote', 'VERIFY0');

-- ===========================================================================
-- SECTION A: field mode, anon role (v3.0 rules carried forward + v4 gates)
-- ===========================================================================
SET LOCAL ROLE anon;

-- 0. control: an active device can write a record
DO $$ BEGIN
  INSERT INTO records (id, device_id, given_name, mrn)
  VALUES ('00000000-0000-4000-8000-00000000d0c2', 'dh-verify-std', 'Control', 'VERIFY1');
  INSERT INTO dh_verify VALUES (0, 'PASS', 'control: an active device can write a record', '');
EXCEPTION WHEN OTHERS THEN
  INSERT INTO dh_verify VALUES (0, 'FAIL', 'control: an active device can write a record', SQLERRM);
END $$;

-- 1. a standard device cannot promote itself to admin
DO $$ BEGIN
  UPDATE devices SET role = 'admin' WHERE id = 'dh-verify-std';
  INSERT INTO dh_verify VALUES (1, 'FAIL', 'a standard device cannot promote itself to admin', 'update was allowed');
EXCEPTION WHEN OTHERS THEN
  INSERT INTO dh_verify VALUES (1, 'PASS', 'a standard device cannot promote itself to admin', SQLERRM);
END $$;

-- 2. a device id cannot be changed
DO $$ BEGIN
  UPDATE devices SET id = 'dh-verify-std-2' WHERE id = 'dh-verify-std';
  INSERT INTO dh_verify VALUES (2, 'FAIL', 'a device id cannot be changed', 'update was allowed');
EXCEPTION WHEN OTHERS THEN
  INSERT INTO dh_verify VALUES (2, 'PASS', 'a device id cannot be changed', SQLERRM);
END $$;

-- 3. a record cannot be written for an unregistered device
DO $$ BEGIN
  INSERT INTO records (id, device_id, given_name, mrn)
  VALUES ('00000000-0000-4000-8000-00000000d0c3', 'dh-verify-ghost', 'Ghost', 'VERIFY2');
  INSERT INTO dh_verify VALUES (3, 'FAIL', 'a record cannot be written for an unregistered device', 'insert was allowed');
EXCEPTION WHEN OTHERS THEN
  INSERT INTO dh_verify VALUES (3, 'PASS', 'a record cannot be written for an unregistered device', SQLERRM);
END $$;

-- 4. a revoked device cannot un-revoke itself
DO $$ BEGIN
  UPDATE devices SET revoked_at = NULL WHERE id = 'dh-verify-revoked';
  INSERT INTO dh_verify VALUES (4, 'FAIL', 'a revoked device cannot un-revoke itself', 'update was allowed');
EXCEPTION WHEN OTHERS THEN
  INSERT INTO dh_verify VALUES (4, 'PASS', 'a revoked device cannot un-revoke itself', SQLERRM);
END $$;

-- 5. a revoked device cannot write new records
DO $$ BEGIN
  INSERT INTO records (id, device_id, given_name, mrn)
  VALUES ('00000000-0000-4000-8000-00000000d0c4', 'dh-verify-revoked', 'Zombie', 'VERIFY3');
  INSERT INTO dh_verify VALUES (5, 'FAIL', 'a revoked device cannot write new records', 'insert was allowed');
EXCEPTION WHEN OTHERS THEN
  INSERT INTO dh_verify VALUES (5, 'PASS', 'a revoked device cannot write new records', SQLERRM);
END $$;

-- 6. a revoked device cannot alter records it wrote earlier (silent 0 rows)
DO $$
DECLARE n_updated INT;
BEGIN
  UPDATE records SET notes = 'tampered' WHERE id = '00000000-0000-4000-8000-00000000d0c1';
  GET DIAGNOSTICS n_updated = ROW_COUNT;
  IF n_updated = 0 THEN
    INSERT INTO dh_verify VALUES (6, 'PASS', 'a revoked device cannot alter records it wrote earlier', '0 rows updated');
  ELSE
    INSERT INTO dh_verify VALUES (6, 'FAIL', 'a revoked device cannot alter records it wrote earlier', n_updated || ' rows updated');
  END IF;
EXCEPTION WHEN OTHERS THEN
  INSERT INTO dh_verify VALUES (6, 'PASS', 'a revoked device cannot alter records it wrote earlier', SQLERRM);
END $$;

-- 7. a record id cannot be changed
DO $$ BEGIN
  UPDATE records SET id = '00000000-0000-4000-8000-00000000dead'
  WHERE id = '00000000-0000-4000-8000-00000000d0c2';
  INSERT INTO dh_verify VALUES (7, 'FAIL', 'a record id cannot be changed', 'update was allowed');
EXCEPTION WHEN OTHERS THEN
  INSERT INTO dh_verify VALUES (7, 'PASS', 'a record id cannot be changed', SQLERRM);
END $$;

-- 8. the shared key cannot switch the org to clinic mode
DO $$ BEGIN
  INSERT INTO config (key, value) VALUES ('orgMode', '{"mode":"clinic"}');
  INSERT INTO dh_verify VALUES (8, 'FAIL', 'the shared key cannot switch the org to clinic mode', 'insert was allowed');
EXCEPTION WHEN OTHERS THEN
  INSERT INTO dh_verify VALUES (8, 'PASS', 'the shared key cannot switch the org to clinic mode', SQLERRM);
END $$;

RESET ROLE;

-- ===========================================================================
-- SECTION B: staff accounts and clinic mode
-- ===========================================================================

-- Remember whether this project already has active admin accounts (used
-- to SKIP the sole-admin probe rather than report false alarms).
INSERT INTO dh_env
SELECT 'preexisting_admins', count(*)::text
FROM users_profiles
WHERE is_admin AND activated_at IS NOT NULL AND revoked_at IS NULL;

-- Simulate two signups (fires the same trigger a real signup does).
-- Wrapped so a version-sensitive auth.users shape cannot abort the whole
-- transaction and hide section A's results.
DO $$ BEGIN
  INSERT INTO auth.users (instance_id, id, aud, role, email, encrypted_password,
                          email_confirmed_at, created_at, updated_at,
                          confirmation_token, recovery_token,
                          email_change_token_new, email_change)
  VALUES ('00000000-0000-0000-0000-000000000000', '00000000-0000-4000-8000-0000000000a1',
          'authenticated', 'authenticated', 'dh-verify-lead@example.org', '',
          now(), now(), now(), '', '', '', ''),
         ('00000000-0000-0000-0000-000000000000', '00000000-0000-4000-8000-0000000000a2',
          'authenticated', 'authenticated', 'dh-verify-staff@example.org', '',
          now(), now(), now(), '', '', '', '');
EXCEPTION WHEN OTHERS THEN
  INSERT INTO dh_verify VALUES (9, 'SKIP', 'section B skipped: auth.users seeding rejected on this Supabase version', SQLERRM);
END $$;

-- 9. signing up grants nothing: both profiles pending, neither admin
DO $$
DECLARE bad INT;
BEGIN
  IF EXISTS (SELECT 1 FROM dh_verify WHERE n = 9 AND result = 'SKIP') THEN
    NULL;  -- seeding failed; the SKIP row already explains why
  ELSE
    SELECT count(*) INTO bad FROM users_profiles
    WHERE id IN ('00000000-0000-4000-8000-0000000000a1', '00000000-0000-4000-8000-0000000000a2')
      AND (is_admin OR activated_at IS NOT NULL);
    IF bad = 0 AND (SELECT count(*) FROM users_profiles
                    WHERE id IN ('00000000-0000-4000-8000-0000000000a1',
                                 '00000000-0000-4000-8000-0000000000a2')) = 2 THEN
      INSERT INTO dh_verify VALUES (9, 'PASS', 'signing up grants nothing (pending, not admin)', '');
    ELSE
      INSERT INTO dh_verify VALUES (9, 'FAIL', 'signing up grants nothing (pending, not admin)', bad || ' profiles had privileges');
    END IF;
  END IF;
END $$;

-- Act as the pending lead account.
SET LOCAL ROLE authenticated;
SELECT set_config('request.jwt.claims', '{"sub":"00000000-0000-4000-8000-0000000000a1","role":"authenticated"}', true);
SELECT set_config('request.jwt.claim.sub', '00000000-0000-4000-8000-0000000000a1', true);

-- 10. a pending account cannot read records
DO $$
DECLARE n INT;
BEGIN
  SELECT count(*) INTO n FROM records;
  IF n = 0 THEN
    INSERT INTO dh_verify VALUES (10, 'PASS', 'a pending account cannot read records', '');
  ELSE
    INSERT INTO dh_verify VALUES (10, 'FAIL', 'a pending account cannot read records', n || ' rows visible');
  END IF;
END $$;

-- 11. a pending account cannot approve itself
DO $$ BEGIN
  UPDATE users_profiles SET activated_at = now()
  WHERE id = '00000000-0000-4000-8000-0000000000a1';
  INSERT INTO dh_verify VALUES (11, 'FAIL', 'a pending account cannot approve itself', 'update was allowed');
EXCEPTION WHEN OTHERS THEN
  INSERT INTO dh_verify VALUES (11, 'PASS', 'a pending account cannot approve itself', SQLERRM);
END $$;

-- 12. a pending account sees only its own profile row
DO $$
DECLARE n INT;
BEGIN
  SELECT count(*) INTO n FROM users_profiles;
  IF n = 1 THEN
    INSERT INTO dh_verify VALUES (12, 'PASS', 'a pending account sees only its own profile row', '');
  ELSE
    INSERT INTO dh_verify VALUES (12, 'FAIL', 'a pending account sees only its own profile row', n || ' rows visible');
  END IF;
END $$;

RESET ROLE;

-- Bootstrap the first admin exactly as SETUP.md documents.
UPDATE users_profiles SET is_admin = true, activated_at = now()
WHERE id = '00000000-0000-4000-8000-0000000000a1';

SET LOCAL ROLE authenticated;
SELECT set_config('request.jwt.claims', '{"sub":"00000000-0000-4000-8000-0000000000a1","role":"authenticated"}', true);
SELECT set_config('request.jwt.claim.sub', '00000000-0000-4000-8000-0000000000a1', true);

-- 13. the bootstrapped admin can write config
DO $$ BEGIN
  INSERT INTO config (key, value) VALUES ('dh-verify-key', '{"ok":true}');
  INSERT INTO dh_verify VALUES (13, 'PASS', 'the bootstrapped admin can write config', '');
EXCEPTION WHEN OTHERS THEN
  INSERT INTO dh_verify VALUES (13, 'FAIL', 'the bootstrapped admin can write config', SQLERRM);
END $$;

-- 14. the only admin cannot revoke, demote, or deactivate themselves
--     (skipped when the project already has other active admins - the
--     guard correctly allows self-removal in that case)
DO $$ BEGIN
  IF (SELECT v FROM dh_env WHERE k = 'preexisting_admins') <> '0' THEN
    INSERT INTO dh_verify VALUES (14, 'SKIP', 'the only admin cannot disable themselves', 'project already has active admins; guard not probeable');
  ELSE
    BEGIN
      UPDATE users_profiles SET revoked_at = now()
      WHERE id = '00000000-0000-4000-8000-0000000000a1';
      INSERT INTO dh_verify VALUES (14, 'FAIL', 'the only admin cannot disable themselves', 'self-revoke was allowed');
    EXCEPTION WHEN OTHERS THEN
      INSERT INTO dh_verify VALUES (14, 'PASS', 'the only admin cannot disable themselves', SQLERRM);
    END;
  END IF;
END $$;

-- 15. an admin can switch the org to clinic mode
DO $$ BEGIN
  INSERT INTO config (key, value) VALUES ('orgMode', '{"mode":"clinic"}')
  ON CONFLICT (key) DO UPDATE SET value = EXCLUDED.value;
  INSERT INTO dh_verify VALUES (15, 'PASS', 'an admin can switch the org to clinic mode', '');
EXCEPTION WHEN OTHERS THEN
  INSERT INTO dh_verify VALUES (15, 'FAIL', 'an admin can switch the org to clinic mode', SQLERRM);
END $$;

-- 16. an active staff account reads records in clinic mode
DO $$
DECLARE n INT;
BEGIN
  SELECT count(*) INTO n FROM records;
  IF n >= 2 THEN
    INSERT INTO dh_verify VALUES (16, 'PASS', 'an active staff account reads records in clinic mode', n || ' rows');
  ELSE
    INSERT INTO dh_verify VALUES (16, 'FAIL', 'an active staff account reads records in clinic mode', n || ' rows visible');
  END IF;
END $$;

RESET ROLE;
-- RESET ROLE does not clear GUCs: blank the JWT claims so the anon checks
-- below run as a true key-only caller, not as the admin's leftover
-- identity. Empty string, not NULL: auth.uid() nullif()s '' to NULL.
SELECT set_config('request.jwt.claims', '', true);
SELECT set_config('request.jwt.claim.sub', '', true);
SET LOCAL ROLE anon;

-- 17. clinic mode: the shared key cannot read records
DO $$
DECLARE n INT;
BEGIN
  SELECT count(*) INTO n FROM records;
  IF n = 0 THEN
    INSERT INTO dh_verify VALUES (17, 'PASS', 'clinic mode: the shared key cannot read records', '');
  ELSE
    INSERT INTO dh_verify VALUES (17, 'FAIL', 'clinic mode: the shared key cannot read records', n || ' rows visible');
  END IF;
END $$;

-- 18. clinic mode: the shared key cannot write records
DO $$ BEGIN
  INSERT INTO records (id, device_id, given_name, mrn)
  VALUES ('00000000-0000-4000-8000-00000000d0c5', 'dh-verify-std', 'Locked', 'VERIFY4');
  INSERT INTO dh_verify VALUES (18, 'FAIL', 'clinic mode: the shared key cannot write records', 'insert was allowed');
EXCEPTION WHEN OTHERS THEN
  INSERT INTO dh_verify VALUES (18, 'PASS', 'clinic mode: the shared key cannot write records', SQLERRM);
END $$;

-- 19. clinic mode: the shared key cannot register devices
DO $$ BEGIN
  INSERT INTO devices (id, name) VALUES ('dh-verify-intruder', 'intruder');
  INSERT INTO dh_verify VALUES (19, 'FAIL', 'clinic mode: the shared key cannot register devices', 'insert was allowed');
EXCEPTION WHEN OTHERS THEN
  INSERT INTO dh_verify VALUES (19, 'PASS', 'clinic mode: the shared key cannot register devices', SQLERRM);
END $$;

-- 20. clinic mode: the shared key sees exactly one config row (orgMode)
DO $$
DECLARE n INT; k TEXT;
BEGIN
  SELECT count(*), min(key) INTO n, k FROM config;
  IF n = 1 AND k = 'orgMode' THEN
    INSERT INTO dh_verify VALUES (20, 'PASS', 'clinic mode: the shared key sees only the orgMode row', '');
  ELSE
    INSERT INTO dh_verify VALUES (20, 'FAIL', 'clinic mode: the shared key sees only the orgMode row', n || ' rows visible');
  END IF;
END $$;

-- 21. clinic mode: the shared key cannot enumerate the device fleet
DO $$
DECLARE n INT;
BEGIN
  SELECT count(*) INTO n FROM devices;
  IF n = 0 THEN
    INSERT INTO dh_verify VALUES (21, 'PASS', 'clinic mode: the shared key cannot enumerate the device fleet', '');
  ELSE
    INSERT INTO dh_verify VALUES (21, 'FAIL', 'clinic mode: the shared key cannot enumerate the device fleet', n || ' rows visible');
  END IF;
END $$;

RESET ROLE;

-- Revoke the second (still pending) account, then prove stickiness.
UPDATE users_profiles SET revoked_at = now()
WHERE id = '00000000-0000-4000-8000-0000000000a2';

SET LOCAL ROLE authenticated;
SELECT set_config('request.jwt.claims', '{"sub":"00000000-0000-4000-8000-0000000000a2","role":"authenticated"}', true);
SELECT set_config('request.jwt.claim.sub', '00000000-0000-4000-8000-0000000000a2', true);

-- 22. a revoked account cannot read records
DO $$
DECLARE n INT;
BEGIN
  SELECT count(*) INTO n FROM records;
  IF n = 0 THEN
    INSERT INTO dh_verify VALUES (22, 'PASS', 'a revoked account cannot read records', '');
  ELSE
    INSERT INTO dh_verify VALUES (22, 'FAIL', 'a revoked account cannot read records', n || ' rows visible');
  END IF;
END $$;

-- 23. a revoked account cannot un-revoke itself
DO $$ BEGIN
  UPDATE users_profiles SET revoked_at = NULL
  WHERE id = '00000000-0000-4000-8000-0000000000a2';
  INSERT INTO dh_verify VALUES (23, 'FAIL', 'a revoked account cannot un-revoke itself', 'update was allowed');
EXCEPTION WHEN OTHERS THEN
  INSERT INTO dh_verify VALUES (23, 'PASS', 'a revoked account cannot un-revoke itself', SQLERRM);
END $$;

-- 24. a revoked account cannot see the staff roster (only its own row)
DO $$
DECLARE n INT;
BEGIN
  SELECT count(*) INTO n FROM users_profiles;
  IF n <= 1 THEN
    INSERT INTO dh_verify VALUES (24, 'PASS', 'a revoked account sees at most its own profile row', '');
  ELSE
    INSERT INTO dh_verify VALUES (24, 'FAIL', 'a revoked account sees at most its own profile row', n || ' rows visible');
  END IF;
END $$;

RESET ROLE;

-- ---------------------------------------------------------------------------
-- Results, then roll everything back.
-- ---------------------------------------------------------------------------
SELECT n, result, check_name, left(detail, 80) AS detail
FROM dh_verify ORDER BY n;

ROLLBACK;

-- ============================================================================
-- Roll a v4.1 project back to v3.0 behavior.
--
-- Use this only if the v4 machinery misbehaves in the field. It restores
-- the exact v3.0 policy surface (shared-key field mode, no mode gate, no
-- staff accounts) and removes every v4 trigger and function.
--
-- Deliberately KEPT, because dropping them destroys data and unused
-- objects cost nothing: the users_profiles table and its rows, the new
-- records columns (user_id, flow_station, flow_updated_at), all indexes,
-- and any orgMode config row (it becomes inert once the functions that
-- read it are gone).
--
-- Safe to re-run. After running this, re-running v3.0 setup.sql is a
-- no-op; re-running v4.1 setup.sql re-applies v4.
-- ============================================================================

-- 1. Restore the v3.0 anon policies (same names, no TO clause, no mode gate).
DROP POLICY IF EXISTS "anon_read_records" ON records;
CREATE POLICY "anon_read_records" ON records
  FOR SELECT USING (true);

DROP POLICY IF EXISTS "anon_insert_records" ON records;
CREATE POLICY "anon_insert_records" ON records
  FOR INSERT WITH CHECK (
    device_id IS NOT NULL
    AND EXISTS (SELECT 1 FROM devices d WHERE d.id = device_id AND d.revoked_at IS NULL)
  );

DROP POLICY IF EXISTS "anon_update_records" ON records;
CREATE POLICY "anon_update_records" ON records
  FOR UPDATE
  USING (
    device_id IS NOT NULL
    AND EXISTS (SELECT 1 FROM devices d WHERE d.id = device_id AND d.revoked_at IS NULL)
  )
  WITH CHECK (
    device_id IS NOT NULL
    AND EXISTS (SELECT 1 FROM devices d WHERE d.id = device_id AND d.revoked_at IS NULL)
  );

DROP POLICY IF EXISTS "anon_read_devices" ON devices;
CREATE POLICY "anon_read_devices" ON devices
  FOR SELECT USING (true);

DROP POLICY IF EXISTS "anon_insert_devices" ON devices;
CREATE POLICY "anon_insert_devices" ON devices
  FOR INSERT WITH CHECK (true);

DROP POLICY IF EXISTS "anon_update_devices" ON devices;
CREATE POLICY "anon_update_devices" ON devices
  FOR UPDATE USING (true) WITH CHECK (true);

DROP POLICY IF EXISTS "anon_read_config" ON config;
CREATE POLICY "anon_read_config" ON config
  FOR SELECT USING (true);

DROP POLICY IF EXISTS "anon_write_config" ON config;
CREATE POLICY "anon_write_config" ON config
  FOR INSERT WITH CHECK (true);

DROP POLICY IF EXISTS "anon_update_config" ON config;
CREATE POLICY "anon_update_config" ON config
  FOR UPDATE USING (true) WITH CHECK (true);

-- 2. Remove the v4 staff-account policies (the table itself stays).
DROP POLICY IF EXISTS "auth_read_records" ON records;
DROP POLICY IF EXISTS "auth_insert_records" ON records;
DROP POLICY IF EXISTS "auth_update_records" ON records;
DROP POLICY IF EXISTS "auth_read_devices" ON devices;
DROP POLICY IF EXISTS "auth_insert_devices" ON devices;
DROP POLICY IF EXISTS "auth_update_devices" ON devices;
DROP POLICY IF EXISTS "auth_read_config" ON config;
DROP POLICY IF EXISTS "auth_write_config" ON config;
DROP POLICY IF EXISTS "auth_update_config" ON config;
DROP POLICY IF EXISTS "auth_read_profiles" ON users_profiles;
DROP POLICY IF EXISTS "auth_insert_profiles" ON users_profiles;
DROP POLICY IF EXISTS "auth_update_profiles" ON users_profiles;

-- 3. Remove the v4 triggers and functions. The device-rules trigger and
--    update_synced_at stay: they are v3.0 machinery.
DROP TRIGGER IF EXISTS trg_users_rules ON users_profiles;
DROP TRIGGER IF EXISTS trg_config_rules ON config;
DROP TRIGGER IF EXISTS trg_records_rules ON records;
DROP TRIGGER IF EXISTS trg_config_updated_at ON config;

-- The signup trigger lives on auth.users, which the postgres role does
-- not own on every stack; DROP TRIGGER there can fail with 'must be owner
-- of relation users' and abort the whole rollback. Dropping the function
-- with CASCADE removes the dependent trigger without needing ownership.
DROP FUNCTION IF EXISTS dh_handle_new_user() CASCADE;

DROP FUNCTION IF EXISTS dh_enforce_user_rules();
DROP FUNCTION IF EXISTS dh_enforce_config_rules();
DROP FUNCTION IF EXISTS dh_enforce_record_rules();
DROP FUNCTION IF EXISTS dh_touch_config_updated_at();
DROP FUNCTION IF EXISTS dh_org_mode();
DROP FUNCTION IF EXISTS dh_active_profile();
DROP FUNCTION IF EXISTS dh_is_admin();

-- 4. Confirm: this should return no rows. trg_on_auth_user_created is in
--    the list because its drop is the permission-fragile one; if it shows
--    up here, the signup trigger survived and is still creating profile
--    rows.
SELECT tgname FROM pg_trigger
WHERE tgname IN ('trg_users_rules', 'trg_config_rules', 'trg_records_rules',
                 'trg_config_updated_at', 'trg_on_auth_user_created');

The scripts are published straight from the supabase folder of the DH EMR source, which also holds the same instructions as SETUP.md.