September last year I met up with an old room mate. She told me about a student project called Making Commitments Matter (MCM). They had created a database for UN resolutions. Even though the team was quite large, spanning all across Europe, they didn't have anyone in the group who had a sufficient level of IT know how to bring it all online. Once again it shows that Universities usually are unable to bring together different departments. So I had no choice but to help them out!

Step one defining a database schema for the excel files they currently used to store the information. Contrary to the document systems of the various UN organizations which scatter the various PDF's across different sites, the aim of resolutionfinder.org (previously called UN-informed.org) is to bring all information to one database. Furthermore resolutionfinder.org would also show the entire history of documents and each of the clauses there in. This again is a huge leap forward, since right now its essentially impossible to determine if a given document or clause is actually the most current version and how things changed over time. Especially this history was a big challenge in the definition of the schema.

Aside from this the two years that MCM spend interviewing key stakeholders like NGO's, government officials and researchers have lead to a fairly complex set of additional attributes that also need to be stored for each document and clause. The fact that each clause is stored separately is also a key improvement since many documents contain decisions on many different topics. With this database it will be possible to search for just the clauses for a given area as both documents and individual clauses are tagged.

Using the admin generator of the symfony PHP framework I started building up an interface to the database in order to be able to get feedback from the MCM team. By way of a call for help on my blog Dennis Riedel, living in Barcelona, joined the effort and began working on an import tool to move the data from the excel files into the database schema. At a project meeting in Helsinki in early December 2009, we used this prototype to define the final specifications for the database.

The goal was to have a working frontend ready for the  conference MCM was organizing on the topic of “UN information management” end of May 2010. No small undertaking for a small team of two guys in their spare time, neither of whom being real experts in frontend development. So I turned to Liip for help. At Liip we have budgets to support technically or socially relevant efforts. The process is basically pitching the idea to the entire team to get development resources. In the end resolutionfinder.org was allocated 12 man days development as well as 1 man day for server administration and a server to run the application on!

Christoph and Jordi both stepped up on short notice to help me build up a frontend that actually exceeded by far the hopes of the MCM team. The site leverages Apache Solr for the search with features boolean operators and facette filtering. We display the documents, clauses as well as their relation and history. Its also possible to register to store bookmarks to documents and clauses as well as comment them. Most amazingly we still have a few days of development left that we will use to further optimize the UX on the search interface. I should also thank Stefan Sicher who delivered a very sleek and clean design just days before the conference.

Obviously there is still lots left to do. As mentioned before we want to further improve the UX on the search. We are also considering adding alternative search approaches, for example some people might want to search chronologically, others organization etc. We also want to add data mining capabilities so that we can automatically import voting records and maybe eventually even entire documents. Supporting more languages both for the interface and the content also is high on our list. We might also provide the content via an RDF database to enable others to come up with queries of their own. The team is obviously full of ideas and is actively looking for companies or developers willing to donate resources. The  source code of the entire application is of course open source: patches welcome!

Here is an example search for “security council” documents, for regional education topics outside of africa. Here is an example document and example clause with a history on the topic of malaria. Additional information on the technology can be found in the case studies publised as Lucid Imagination and symfony. I also published a series of posts on my personal blog. Overall it was a very fun and rewarding experience and obviously I will continue to work on the project in my spare time. For Liip I can already see how several of the lessons learned during the project have paid off on client projects as well. So overall I guess its a win for all!