Compass/backend/supabase/private_users.sql

CREATE TABLE IF NOT EXISTS private_users (
    data JSONB NOT NULL,
    id TEXT NOT NULL,
    CONSTRAINT private_users_pkey PRIMARY KEY (id)
);

ALTER TABLE private_users
    ADD CONSTRAINT private_users_id_fkey
        FOREIGN KEY (id)
            REFERENCES users(id)
            ON DELETE CASCADE;

-- Row Level Security
ALTER TABLE private_users ENABLE ROW LEVEL SECURITY;

-- Policies
DROP POLICY IF EXISTS "private read" ON private_users;

CREATE POLICY "private read" ON private_users
    FOR SELECT
    USING ((firebase_uid() = id));

-- Indexes
-- Removed redundant index creation because PRIMARY KEY already creates a unique index on id