update to support user expire, timeout, and disconnect

db/schema/functions/001_helpers.sql

@@ -277,4 +277,45 @@ $$ LANGUAGE plpgsql;
 
 COMMENT ON FUNCTION audit.log_event IS 'Helper to create audit log entries';
 
+-- =============================================================================
+-- Instance Member Maintenance Functions
+-- =============================================================================
+
+-- Clear all instance members (for server startup cleanup)
+-- Uses SECURITY DEFINER to bypass RLS
+CREATE OR REPLACE FUNCTION scene.clear_all_instance_members()
+RETURNS BIGINT AS $$
+DECLARE
+    deleted_count BIGINT;
+BEGIN
+    DELETE FROM scene.instance_members;
+    GET DIAGNOSTICS deleted_count = ROW_COUNT;
+    RETURN deleted_count;
+END;
+$$ LANGUAGE plpgsql SECURITY DEFINER;
+
+COMMENT ON FUNCTION scene.clear_all_instance_members() IS
+    'Clears all instance members on server startup. Bypasses RLS.';
+
+-- Clear stale instance members based on last_moved_at threshold
+-- Uses SECURITY DEFINER to bypass RLS
+CREATE OR REPLACE FUNCTION scene.clear_stale_instance_members(threshold_seconds DOUBLE PRECISION)
+RETURNS BIGINT AS $$
+DECLARE
+    deleted_count BIGINT;
+BEGIN
+    DELETE FROM scene.instance_members
+    WHERE last_moved_at < NOW() - make_interval(secs => threshold_seconds);
+    GET DIAGNOSTICS deleted_count = ROW_COUNT;
+    RETURN deleted_count;
+END;
+$$ LANGUAGE plpgsql SECURITY DEFINER;
+
+COMMENT ON FUNCTION scene.clear_stale_instance_members(DOUBLE PRECISION) IS
+    'Clears stale instance members older than threshold. Bypasses RLS.';
+
+-- Grant execute to chattyness_app
+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;
+
 COMMIT;