When a plugin is activated, the activation hook is the only code that runs on that request. If you flush rewrite rules on activation, your rule isn’t in the array of rules that everything gets rebuilt from, unless you explicitly add it first in the activation hook.
register_activation_hook( __FILE__, 'myplugin_flush_rewrites' );
function myplugin_flush_rewrites() {
// call your function that adds the rule here
// (it should also be hooked into 'init')
foo_add_rewrite_rule();
flush_rewrite_rules();
}
Your rule still has to be added on init
on every request, because some other code, or a permalink settings page load, can flush rules on any request.