In short, the answers to all of your questions are "yes".
We set out to provide a JDBC-based set of templates, as we figured that would be the one most commonly used. But knowing that other implementations would prove useful, it's all based on interfaces from which you can implement whatever kind of connection repository you want. Some work has been done in the community already to provide JPA-based, Mongo-based, and SQLite-based implementations. If you decide to implement a Cassandra-based implementation, then it'd be awesome if you could share your work with the community.
And yes, you'd need to implement both ConnectionRepository and UsersConnectionRepository. The UsersConnectionRepository is one that deals with connections across all users, while ConnectionRepository focuses on connections for a specific user.
At first glance, it may seem that ConnectionRepository is the most useful of the two. In fact, it's the ConnectionRepository that ConnectController uses to manage connections for the current user. And in an application that only cares about creating connections between its users and some provider, then UsersConnectionRepository is only a means to an end...that is, it only exists to create a ConnectionRepository for the current user.
But if the app offers a sign-in-with-provider option, then UsersConnectionRepository is useful for more than just being a ConnectionRepository factory. In that scenario, there isn't a notion of a "current user" yet...they're still signing in. Therefore, ConnectionRepository is useless for helping a user sign in. UsersConnectionRepository, having the ability to deal with all connections for all users is able to help ProviderSignInController find a user who has a connection that matches the connection details obtained while signing in with Facebook/Twitter/etc.
Spring Social Project Lead