All census instances in read-only mode


Hello all,

As of yesterday (28/05/2015 at around 13:00 GMT), we moved all census instances into read-only mode. This was not planned in advance, and therefore we were not able to give notice.

What happened

For several months, we have been having problems with the Google Spreadsheets backend that we use to store all census data. This has been discussed on the mailing list for census admins.

The problem is the way we communicate with the Spreadsheets API, and the 3rd party libraries we employ to do so.

Yesterday, a related problem appeared - one that we cannot easily work around: We use a library to connect to Google Spreadsheets via Google ClientLogin.

ClientLogin support was shutoff for Google Spreadsheets in late April 2015, and went into effect at sometime in the last 24 hours. This change hit many people, who, like us, were still using ClientLogin and were unaware of the change in service from Google.

The only way to fix this, and retain existing functionality, is to replace the 3rd party code we depend on for Google Spreadsheets, and change the authentication method we use. This is not a simple fix.

Who is effected

Every census instance is effected: Global, Regional and Local.

What we are doing about it

In order to provide an immediate solution, we changed parts of the code that require authentication, by disabling them. Essentially, that means we had to shut down all user data, and therefore, user actions: login, submit and review.

A fix for this was done and deployed to all instances within a few hours of us discovering the issue. This means that all instances are now live, but all are in read-only mode.

Going forward, we face two choices for resolving this issue:

  1. Refactoring the current code around auth and spreadsheet connection (which could get a little complicated due to the way we need to connect to Google Spreadsheets).
  2. Staying in read-only mode for around 2 weeks while we work on the larger change (which has already started) of moving all census data to a proper database.

We have currently decided on option 2, for the following reasons:

  • We are getting rid of our direct dependency on Google Spreadsheets via the port to a database anyway
  • If we fix the new Spreadsheets auth issue, it will take time away from our current focus on porting to a database, which we are doing to make the census more scalable and stable for future growth

We already started on the port to a proper database a week ago. We expect this to take another 2 weeks.

We are sorry for the inconvenience this causes. Please let us know your thoughts and concerns below.


Thanks for the update @pwalsh. Is it safe for census owners to update their configuration file to inform visitors of the situation?

pinned #3


That’s tricky, because it is safe to change it, but you will not be able to run the reload action (as you can’t login).

So in this case, please update the config, and then let me know here so I can reload your instance to pick up the changes.


Hi @pwalsh Australia’s Regional Open Data Census configuration file is ready for reloading. Thanks for your help.


That’s done, thanks @Stephen


Hi @pwalsh, do you have an update on how the fix is progressing? Can census admins help in anyway?


Hi @Stephen. Progress is slower than expected. I have had some trouble with developers I hired to help get this done quickly. I’ll update on progress next Monday.


Hi all,

An update: As stated earlier, we had some trouble with the process of on-boarding developers to help with the refactor. This has been resolved, and I’m actively working on the changes with another developer who works with Open Knowledge.

Progress was stymied by this, but things are moving along again. I’ll update here every few days, and I’ll contact all census admins as soon as we have an initial version to test.

Please note that the changes we are making are for the good of the census for many years to come, and provide a solid foundation for us for iterate quickly in extending the current functionality.

Thanks for your patience and understanding.


Hello everyone,

An update on the census refactor:

We plan to do the first “soft launch” of the new census database next Thursday (9th July 2015).

“Soft launch” means that not all sites will immediately go live on this date.

Rather, we’ll have a gradual rollout so that we can watch for any issues due to the new architecture. The first live site will be the US City census, followed several days later by the main local census database, and a few days after that, the global census database.

Again, I thank you all for your patience as we’ve made these large scale changes, and we look forward to renewed census activity shortly.


Thanks for make it work! I’ll be ready to test it :wink:


Hello everyone,

We planned to soft launch today, the 9th of July, but unfortunately the new code is still not ready for deployment.

The work is very close to done: I’m in the middle of a time consuming task to update the HTML templates to work with the new data model from the database. As soon as I’m able to finish this to satisfaction (all data is displayed correctly), I’ll be able to release the new version for testing.

Data for all censuses: Global, Local, US and Germany has been migrated and is ready for testing as soon as I get this last step sorted.




Glad to hear it’s almost good to go. It sounds like there have been some big code changes – are you using a different backend platform now?


Hi @oxguy3 yes it has been a big change and like all refactors the devil is in the details. I’ll post about the changes and the new codebase in more detail once we launch it. The basics are that it is still a Node app, but the old Google Spreadsheets “database” has been replaced by Postgres, and the app is now multi-tenant, so where we currently manage almost 50 census instances, they will now run out of a single code base.


Sounds awesome, can’t wait to see it live! :smile:


Hi all,

I’ve deployed the first test version of the new code, minus the submission flow.

Any census admins can check the data and help us to identify problems.

All domains are available on notice next.


Docs have been updated (but there is more we can do there): and

The database will be reloaded again in a couple days, after we get some feedback on bugs and issues.

So, any changes made while we are on the next subdomain will not be kept.

The most notable user-facing differences are:

The most notable site admin differences are:

Scope of changes

The immediate need for the refactor was to fix the ongoing issues with the database and authentication. As part of the refactor, we decided to change the app from a single tenant app (one distinct codebase per census instance) to a multi-tenant one (one codebase serves all census instances). By far, this was the most time-consuming aspect of the work.

Reporting issues

Please report any issues on the issue tracker for the code:

If you have an issue that is not technical, feel free to add here as well.


@pwalsh, thanks to you and the team for the great work being done, it’ll be worth the wait.

Some question about the census being partitioned by year:

  • does the year end on 31 December?
  • do all entries get reset to blank on 1 January?
  • are there plans to enable changes over years to be easily compared, as done in the global index?


Hi @Stephen

  1. The year ends on Dec 31st, and starts on Jan 1st.
  2. Entries do not get reset exactly, it is just that when the new year starts, no entries exist for that year. Then, the submission flow (which I have not deployed yet) takes care of creating new entry “objects” for the new year.
  3. For the comparison - there are no current plans, but it is a really good suggestion and one I’d like to see, especially for local/regional census instances. Can you add an issue for this to GitHub?


Done -


Great job @pwalsh!

What is timeline to upgrade all other instances with new database?

I’m admin on hr-city census and I’d like to get also upgrade with translation as it’s hard to work with locals when GUI is on English.

Could you squizze in with upgrade also Croatian translation *.po?