Tender: Membership management system

Summary

The project must produce a web based membership management system to allow FFII staff to manage members and supporters, and to allow members and supporters to subscribe, unsubscribe, change their data, or request to become members.

In addition, the project must migrate the existing FFII's system with migration scripts - both the migrated database and the migration scripts must be delivered.

Tendering process

We'll pay 4 thousand euro for this project. This is not a lowest-bidder tender, so we'll judge proposals based on quality. Send your proposal and we will contact you with questions. We will consider every proposal and everyone will be contacted. You will have a chance to clarify points in your proposal.

Note that, since you will be given access to our existing supporter database, you will be required to sign a NDA.

The deadline for this proposal is 23 September 2007, 23:59 UTC.

See how this tender relates to the web site tender for more information.

Questions on this tender should go to board@ffii.org. Please also subscribe to the [ffii:listinfo:polis-parl polis-parl mailing list], as any minor changes or clarifications will be announced there.

Existing system

We have a PostgreSQL relational database containing, among others, about 110 thousand records with information on people. The data stored are an 8-character long user id, the person's name, postal address, and email address, and whether the person is a supporter, contributing member, or active member of the FFII. Partial documentation of the database is included in the appendix below. In addition, we have a custom made, web-based membership management application called Aktiv, accessible at http://aktiv.ffii.org/. Aktiv allows users to register on their own, select whether they want to be contributing members, or active members, or supporters, and retrieve their passwords. The FFII's wikis use the database to perform user authentication.

There are several things about aktiv that we don't like. Many of them could be fixed. However, aktiv's database is large (more than 100 tables, storing much information besides members and supporters), and we think that a small, focused, entirely new system will be better in the long term. Another problem is that aktiv's Perl code is hard to read.

Functional requirements

The data that definitely needs to be stored for each person registered is:

If budget allows, we also want the following:

The user interface shall be web based and will provide users with the following features:

Nonfunctional requirements

The application must be usable with all common web browsers, including text browser with no JavaScript support, and should generally aim to be at least 'AA'-conformant as per the W3C's WAI. Must be Unicode, with localisable user interface.

We want good, well documented code. We prefer minimal functionality with good code, than more functionality with inferior code. If, for example, you use Python, you should follow PEP 8 (Style Guide for Python Code) and PEP 257 (Docstring Conventions), and use the unittest module to create testing suites testing all public functionality of your modules. You should put as much code as possible in modules/packages, with simple, clean APIs. Ideally, you should spend so much time in simplifying your APIs so that we'd wonder why it took so long for such an obvious, simple thing. Of course, we also want results, the budget is small, and we don't want to wait too much, so you shouldn't try to be too perfect.

Software to build on

We have a tradition on using free software, so all software you use for this project must be free. Note that several such systems already exist; two that we know are Galette and CiviCRM, and you may propose to use them if you think they match our needs. If you want to propose something based on MySQL, we will appreciate it if you port them to PostgreSQL, as we have a tradition of using it. Offering an existing system in your proposal means you may be able to offer more for the same budget (but don't forget that a large part of the budget is the migration, mentioned below).

It goes without saying that we like standard libraries. We dislike fat dependencies, but on the other hand we also like code reuse. If you prefer another language, feel free to propose it. Note that we use Debian, and it is important that as many dependencies as possible are already standard Debian packages.

Environment, middleware, etc. Hmmm, webware, zope/plone, sqlalchemy, 4suite, turbogears, django (these are some of the Python tools), we are at a loss here. Choose your favourite tools and propose them. Or don't use any of those. Again, we like simplicity, we like small, we like stand-alone, we dislike fat, but we also like reuse, efficiency, and low cost. And maintainability, of course. If you propose a super environment with which you can develop the application in 5 minutes, but makes it difficult to install and our administrators must spend too long in order to learn to install and maintain the system, it might count against you.

Back-end. Well, PostgreSQL is the obvious choice here. We've discussed about LDAP, which has the advantage that many tools (pam+nss, Moin, several issue tracking systems, etc.) that we already use or we will use in the future have out-of-the-box support for it. On the downside, we don't have any tradition in LDAP and many of our administrators don't have any experience, so we are reluctant on putting such a dependency on our systems. We also aren't experienced enough to know the degree to which the LDAP data model fits our requirements. But if you can convince us that it will be great for us on the long term, feel free to propose it. However, if you use anything else than PostgreSQL, you must provide a way to sync the new database with the old one (see Migration below).

You got the point? Make your proposal. Feel free to relax the rules and propose that we should really use flat vcard text files instead, if you think you can convince us that it could reliably handle millions of registrations. We presented you our problems and our thoughts, and we are open to all serious alternatives.

License

The copyright of the software you write will remain with you (unless you wish to donate it to the FFII), but you will license it to us under the GNU General Public License version 2.0 or, at our option, any later version. If you want to offer it under a different license that is incompatible to the GPL (e.g. because you reuse code with such an incompatible license), your proposal will be considered; however, GPL incompatibility may count against you.

Similarly, the documentation should be licensed under the GNU FDL, or a free Creative Commons license (one that allows derivatives and commercial use), or even the GPL if it fits, or public domain, choose what you like.

Repository

You should put your software on a publicly accessible repository such as Sourceforge or Savannah, which should also offer a bug tracking facility.

Migration

You will be given access to our database and will need to develop scripts to migrate it. You must deliver both the migrated database _and_ the migration scripts (because we might want to redo the migration).

The new database must integrate with the current FFII database; this is because there are other applications (namely economic-majority.com) that make use of the database; if we simply migrate the existing database to a new one, then existing applications must continue to use the old database, and the two databases will get out of sync. Instead, the new database tables must be created inside the existing database, and the old tables that they replace must be replaced with backwards compatible read-only views that get their data from the new tables. (If existing applications need to write to the old views, this is not your problem; it will be our problem to either convert the views to be writable, or to convert our old applications to use the new tables.)

(This, of course, works only if the new database is PostgreSQL-based. If you feel there are compelling reasons to move to another backend, then you must provide a way to periodically copy data from the new backend. In addition, you must provide clean APIs, usable from Perl and PHP, so that old Perl and PHP applications can be converted to use the new storage system.)

Timeline

We'd like to have everything yesterday, of course. But we may be able to wait for 3 months or so. Propose what you think you will be able to do.

Payment

The exact payment schedule will be negotiated with us, but we generally intend to pay in two halves:

Appendix: Description of the existing database

This text it is accompanied by a drawing (PNG, Dia source) of the physical model of the database. You need to look at both the drawing and this text in order to understand the database. In the drawing, the fields that form the primary key are underlined, and arrows denote foreign keys; they point from the referring table to the referenced table.

The database has more than 100 tables, of which only a few are shown in the drawing, and of those fewer yet are interesting for the membership management system, so we describe only those. pnom stores the people with their full names. pass stores DES-encrypted passwords. mail stores the email addresses, the part before @ being the mailuser and the part after it being the mailhost. More than one email address may be stored for each person, although this is not required for the new system (in fact it is disliked); in that case, mailref distinguishes between them, the primary email address usually having mailref='norm'. padr contains the postal addresses; again many to one, distinguished by addrref in the same manner (and also disliked). str, urb and plz are, respectively, the street (may be more than one line), city, and postal code. land, the country, is a foreign key to isoland, the list of countries (the key is the lower-cased two-character ISO country code). asoc indicates whether the user has selected, in the web interface, that they be active member (asolvl=3), contributing member (asolvl=2), supporter (asolvl=1), subscriber only using FFII information (asolvl=0), or a person who was one of those in the past but has now chosen to terminate their association (asolvl=-1). The org field is generally irrelevant; it was intended for managing many organisations; however, practically all records refer to the FFII.

There are two more tables, not shown in the drawing. aptiko_pnom_details is a hack intended to work around some deficiencies of the rest. Its primary key is uid. last_name and first_names have been separated from pnom.pnom with an algorithm; in some cases, the separation may be wrong. In addition, there are the fields is_member, member_since, and is_active_member. The last one is actually a text field that contains a remark, such as "Yes, according to GA decision of 2005-11-29"; to convert it to a boolean, check whether the first character is a Y or a N. aptiko_pnom_details doesn't have a record for each record in pnom; it mostly contains members, or people who were marked as members but their status later changed or was found to be incorrect.

Finally, asolack (probably short for "association level acknowledge") contains some information on the history of members; it has fields asolack (surrogate key), uid, ackdat (date of decision), acklvl (actual participation level), ackuid (person responsible for this record), ackrem (a remark), and timestamp (time of insertion of record in the database). asolack is essentially a redundant key which is formed from the combination of uid and ackdat. acklvl is the same as asoc.asolvl, but whereas the latter is what the user has requested in the web interface, the former is what has actually been granted to them; this is mostly important for people who have requested to become members, because granting of membership has to go through the board. The official way to determine if someone is a member is from aptiko_pnom_details.is_member, however; but asolack may contain the history of membership (it is the more recent asolack record that counts).

More information about the database may be found at http://a2e.de/i2p/ffiidb/.

jobs/membersys (last edited 2009-08-15 22:27:00 by localhost)

Hosting sponsored by Netgate and Init Seven AG