fix: guests

* make guest status a flag on users
* add logout handlers
* add logout notification for other users

db/schema/functions/001_helpers.sql

@@ -68,24 +68,6 @@ EXCEPTION
 END;
 $$ LANGUAGE plpgsql STABLE;
 
--- Set current guest session ID for RLS
-CREATE OR REPLACE FUNCTION public.set_current_guest_session_id(guest_session_id UUID)
-RETURNS VOID AS $$
-BEGIN
-    PERFORM set_config('app.current_guest_session_id', guest_session_id::TEXT, false);
-END;
-$$ LANGUAGE plpgsql;
-
--- Get current guest session ID for RLS
-CREATE OR REPLACE FUNCTION public.current_guest_session_id()
-RETURNS UUID AS $$
-BEGIN
-    RETURN NULLIF(current_setting('app.current_guest_session_id', true), '')::UUID;
-EXCEPTION
-    WHEN OTHERS THEN RETURN NULL;
-END;
-$$ LANGUAGE plpgsql STABLE;
-
 -- Check if current user is a server admin
 CREATE OR REPLACE FUNCTION public.is_server_admin()
 RETURNS BOOLEAN AS $$
@@ -318,4 +300,34 @@ COMMENT ON FUNCTION scene.clear_stale_instance_members(DOUBLE PRECISION) IS
 GRANT EXECUTE ON FUNCTION scene.clear_all_instance_members() TO chattyness_app;
 GRANT EXECUTE ON FUNCTION scene.clear_stale_instance_members(DOUBLE PRECISION) TO chattyness_app;
 
+-- =============================================================================
+-- Guest Cleanup Functions
+-- =============================================================================
+
+-- Clean up stale guest accounts that haven't been active in 7 days
+-- Guests are users with the 'guest' tag in auth.users
+-- Uses SECURITY DEFINER to bypass RLS
+CREATE OR REPLACE FUNCTION auth.cleanup_stale_guests()
+RETURNS INTEGER AS $$
+DECLARE
+    deleted_count INTEGER;
+BEGIN
+    WITH deleted AS (
+        DELETE FROM auth.users
+        WHERE 'guest' = ANY(tags)
+          AND last_seen_at < now() - interval '7 days'
+        RETURNING id
+    )
+    SELECT count(*) INTO deleted_count FROM deleted;
+
+    RETURN deleted_count;
+END;
+$$ LANGUAGE plpgsql SECURITY DEFINER;
+
+COMMENT ON FUNCTION auth.cleanup_stale_guests() IS
+    'Removes guest accounts (users with guest tag) inactive for 7+ days. Run via cron.';
+
+-- Grant execute to chattyness_app
+GRANT EXECUTE ON FUNCTION auth.cleanup_stale_guests() TO chattyness_app;
+
 COMMIT;

db/schema/policies/001_rls.sql

@@ -259,16 +259,6 @@ CREATE POLICY auth_sessions_admin ON auth.sessions
 
 GRANT SELECT, INSERT, UPDATE, DELETE ON auth.sessions TO chattyness_app;
 
--- auth.guest_sessions
-ALTER TABLE auth.guest_sessions ENABLE ROW LEVEL SECURITY;
-
-CREATE POLICY auth_guest_sessions_all ON auth.guest_sessions
-    FOR ALL TO chattyness_app
-    USING (true)
-    WITH CHECK (true);
-
-GRANT SELECT, INSERT, UPDATE, DELETE ON auth.guest_sessions TO chattyness_app;
-
 -- auth.tower_sessions
 ALTER TABLE auth.tower_sessions ENABLE ROW LEVEL SECURITY;
 
@@ -727,14 +717,8 @@ CREATE POLICY scene_instance_members_select ON scene.instance_members
 
 CREATE POLICY scene_instance_members_own ON scene.instance_members
     FOR ALL TO chattyness_app
-    USING (
-        user_id = public.current_user_id()
-        OR guest_session_id = public.current_guest_session_id()
-    )
-    WITH CHECK (
-        user_id = public.current_user_id()
-        OR guest_session_id = public.current_guest_session_id()
-    );
+    USING (user_id = public.current_user_id())
+    WITH CHECK (user_id = public.current_user_id());
 
 GRANT SELECT, INSERT, UPDATE, DELETE ON scene.instance_members TO chattyness_app;
 
@@ -929,16 +913,12 @@ CREATE POLICY chat_messages_select ON chat.messages
 
 CREATE POLICY chat_messages_insert ON chat.messages
     FOR INSERT TO chattyness_app
-    WITH CHECK (
-        user_id = public.current_user_id()
-        OR guest_session_id = public.current_guest_session_id()
-    );
+    WITH CHECK (user_id = public.current_user_id());
 
 CREATE POLICY chat_messages_update ON chat.messages
     FOR UPDATE TO chattyness_app
     USING (
         user_id = public.current_user_id()
-        OR guest_session_id = public.current_guest_session_id()
         OR public.is_server_moderator()
     );
 

db/schema/tables/030_realm.sql

@@ -102,35 +102,6 @@ ALTER TABLE realm.realms
     ADD CONSTRAINT fk_realm_realms_default_scene
     FOREIGN KEY (default_scene_id) REFERENCES realm.scenes(id) ON DELETE SET NULL;
 
--- =============================================================================
--- Guest Sessions (created here since it references realm tables)
--- =============================================================================
--- Note: current_instance_id FK is added in 045_scene.sql after scene.instances exists
-
-CREATE TABLE auth.guest_sessions (
-    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
-
-    guest_name public.display_name NOT NULL,
-    token_hash TEXT NOT NULL,
-
-    user_agent TEXT,
-    ip_address INET,
-
-    current_realm_id UUID REFERENCES realm.realms(id) ON DELETE SET NULL,
-    current_instance_id UUID,  -- FK added in 045_scene.sql
-
-    expires_at TIMESTAMPTZ NOT NULL,
-    last_activity_at TIMESTAMPTZ NOT NULL DEFAULT now(),
-    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
-
-    CONSTRAINT uq_auth_guest_sessions_token UNIQUE (token_hash)
-);
-
-COMMENT ON TABLE auth.guest_sessions IS 'Anonymous guest sessions';
-
-CREATE INDEX idx_auth_guest_sessions_expires ON auth.guest_sessions (expires_at);
-CREATE INDEX idx_auth_guest_sessions_ip ON auth.guest_sessions (ip_address);
-
 -- =============================================================================
 -- Realm Memberships
 -- =============================================================================

db/schema/tables/045_scene.sql

@@ -40,30 +40,19 @@ CREATE INDEX idx_scene_instances_scene ON scene.instances (scene_id);
 CREATE INDEX idx_scene_instances_type ON scene.instances (scene_id, instance_type);
 CREATE INDEX idx_scene_instances_expires ON scene.instances (expires_at) WHERE expires_at IS NOT NULL;
 
--- =============================================================================
--- Add FK from auth.guest_sessions to scene.instances
--- =============================================================================
--- guest_sessions.current_instance_id was added without FK in 030_realm.sql
--- Now we can add the constraint since scene.instances exists
--- =============================================================================
-
-ALTER TABLE auth.guest_sessions
-    ADD CONSTRAINT fk_auth_guest_sessions_instance
-    FOREIGN KEY (current_instance_id) REFERENCES scene.instances(id) ON DELETE SET NULL;
-
 -- =============================================================================
 -- Instance Members (renamed from realm.channel_members)
 -- =============================================================================
 -- Users currently present in an instance with their positions.
 -- Note: instance_id is actually scene_id in this system (scenes are used directly as instances).
+-- Guests are regular users with the 'guest' tag in auth.users.
 -- =============================================================================
 
 CREATE TABLE scene.instance_members (
     id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
 
     instance_id UUID NOT NULL REFERENCES realm.scenes(id) ON DELETE CASCADE,
-    user_id UUID REFERENCES auth.users(id) ON DELETE CASCADE,
-    guest_session_id UUID REFERENCES auth.guest_sessions(id) ON DELETE CASCADE,
+    user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
 
     position public.virtual_point NOT NULL DEFAULT ST_SetSRID(ST_MakePoint(400, 300), 0),
 
@@ -74,19 +63,13 @@ CREATE TABLE scene.instance_members (
     joined_at TIMESTAMPTZ NOT NULL DEFAULT now(),
     last_moved_at TIMESTAMPTZ NOT NULL DEFAULT now(),
 
-    CONSTRAINT chk_scene_instance_members_user_or_guest CHECK (
-        (user_id IS NOT NULL AND guest_session_id IS NULL) OR
-        (user_id IS NULL AND guest_session_id IS NOT NULL)
-    ),
-    CONSTRAINT uq_scene_instance_members_user UNIQUE (instance_id, user_id),
-    CONSTRAINT uq_scene_instance_members_guest UNIQUE (instance_id, guest_session_id)
+    CONSTRAINT uq_scene_instance_members_user UNIQUE (instance_id, user_id)
 );
 
-COMMENT ON TABLE scene.instance_members IS 'Users in an instance with positions';
+COMMENT ON TABLE scene.instance_members IS 'Users in an instance with positions (guests are users with guest tag)';
 
 CREATE INDEX idx_scene_instance_members_instance ON scene.instance_members (instance_id);
-CREATE INDEX idx_scene_instance_members_user ON scene.instance_members (user_id) WHERE user_id IS NOT NULL;
-CREATE INDEX idx_scene_instance_members_guest ON scene.instance_members (guest_session_id) WHERE guest_session_id IS NOT NULL;
+CREATE INDEX idx_scene_instance_members_user ON scene.instance_members (user_id);
 CREATE INDEX idx_scene_instance_members_position ON scene.instance_members USING GIST (position);
 
 -- =============================================================================

db/schema/tables/050_chat.sql

@@ -28,9 +28,8 @@ CREATE TABLE chat.messages (
 
     instance_id UUID NOT NULL REFERENCES scene.instances(id) ON DELETE CASCADE,
 
-    -- Sender (either user or guest)
-    user_id UUID REFERENCES auth.users(id) ON DELETE SET NULL,
-    guest_session_id UUID REFERENCES auth.guest_sessions(id) ON DELETE SET NULL,
+    -- Sender (all users including guests - guests have 'guest' tag in auth.users)
+    user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE SET NULL,
 
     -- Cached sender info (in case account deleted)
     sender_name public.display_name NOT NULL,
@@ -51,13 +50,7 @@ CREATE TABLE chat.messages (
     deleted_at TIMESTAMPTZ,
 
     -- Timestamps
-    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
-
-    -- Either user_id or guest_session_id must be set
-    CONSTRAINT chk_chat_messages_sender CHECK (
-        (user_id IS NOT NULL AND guest_session_id IS NULL) OR
-        (user_id IS NULL AND guest_session_id IS NOT NULL)
-    )
+    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
 );
 
 COMMENT ON TABLE chat.messages IS 'Instance messages (design supports future time-based partitioning)';