Archiving a Discourse Forum

Discourse is a forum platform, which allows threaded discussions. It looks nice, and works smoothly. However it is somewhat hard to archive such a forum.

There are a couple of posts showing how to archive Discourse:

In the end I made a new wget script to download a Discourse forum. The key thing which the other solutions lacked was that they did not include all page pre-requisites like the pace css. In order to do that, I tweaked the wget script as:

time wget --mirror \
      --page-requisites \
      --span-hosts \
      --domains=PRIVATE-DISCOURSE.COM, \
      --convert-links \
      --adjust-extension \
      --compression=auto \
      --reject-regex "/search" \
      --no-if-modified-since \
      --no-check-certificate \
      --execute robots=off \
      --random-wait \
      --wait=1 \
      --user-agent="Googlebot/2.1 (+" \
      --no-cookies \
      --tries=3 \

The key thing missing in the other scripts was --span-hosts to enable downloading CSS and other static content, and adding --domains=PRIVATE-DISCOURSE.COM, to limit downloading content to domains directly associated with your own Discourse instance.

When you use the script, you need to replace YOUR.PRIVATE-DISCOURSE.COM and PRIVATE-DISCOURSE.COM with the URL and second level domain of your own instance. For Example: and

The script will take it’s time, and you easily need to wait a couple of hours for the download to complete. This is by design to not overload your Discourse instance.

Good luck!

How to apply Atlassian’s Security Requirements for addons

Around November 2019 Atlassian has published new security requirements for cloud applications. The requirements come into force on January 1, 2020. However Atlassian has not published guidance on how to apply these security requirements in their officially supported javascript framework ACE (Atlassian Connect Express).

In this post I share my interpretation of the security requirements in the form of javascript code for ACE. I’ll only cover the requirements which need to be handled in a typical ACE installation on e.g. Heroku. Heroku already provides TLS, and things like not exposing secrets in source repositories fall outside the scope of this article. The code uses two npm packages: helmet and express-sslify. You normally need to add the code below to your app.js.

// security configuration
import helmet from 'helmet';
import enforce from 'express-sslify';
import nocache from 'nocache';

After importing these, you need to configure the libraries:

// define helmet middleware early
// use helmet only in poduction/prodtest
let prodEnv = false;
if ( app.get('env') == 'production' || app.get('env') == 'prodtest') {
    prodEnv = true;
console.log('prodEnv is: '+prodEnv+' env is: '+app.get('env'));

// Sets "Strict-Transport-Security: max-age=34560000; includeSubDomains".
// HSTS minimum is one year, use 400 days
// fourhundredDaysInSeconds := 400 * 24 * 60 * 60 = 34560000
const fourhundredDaysInSeconds = 34560000;
if (prodEnv) {
    // for Heroku: trustProtoHeader: true, otherwise not necessarily
    app.use(enforce.HTTPS({ trustProtoHeader: true }));
        maxAge: fourhundredDaysInSeconds,
        includeSubDomains: false
        policy: ['no-referrer']
    console.log('started security policy');


// Mount the static files directory
const staticDir = path.join(__dirname, 'public');

// use the nocache after mounting the staticDir

A short note on using nocache after mounting the staticDir: the static files usually are resource files like css and javascript and don’t contain changing data, so caching is allowed. I think this use cases falls under the exemption in article 5. of the security requirements. Using noCache after staticDir does not set the Cache-Control header for these resources.

The requirement that the application must authenticate and authorize all requests can be easily handled by ACE:

app.get('/configure', addon.authenticate(), function(req, res) {

I.e. use addon.authenticate() in your route definitions. However make sure you don’t require authentication for atlassian-connect.json.

One further hint: Don’t use helmet.frameguard. ACE apps run as an iframe in the Atlassian application, and must be embeddable in an iframe.

Photo by REVOLT on Unsplash

How to upgrade ACE to AUI 8.5.1

Atlassian has developed AUI, the Atlassian User Interface. This is a set of UI components and a front end library do develop applications according to the Atlassian Design Guidelines. As of 2019-11, version 8.5.1 is the most recent version.

A second component to build apps on Atlassian is ACE, or Atlassian Connect Express, a framework in javascript. This framework currently uses an outdated ACE version by default. In the head of the html delivered by ACE, AUI 5.8.12 is used:

<meta charset="utf-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta name="ap-local-base-url" content="{{localBaseUrl}}">
<link rel="stylesheet" href="//" media="all">
<link rel="stylesheet" href="//" media="all">
<!--[if IE 9]><link rel="stylesheet" href="//" media="all"><![endif]-->
<link rel="stylesheet" href="/css/addon.css" type="text/css" />
<script src="//"></script>
<script src="//" type="text/javascript"></script>
<script src="//" type="text/javascript"></script>
<script src="//"></script>
<script src="//"></script>
<script src="" type="text/javascript"></script>

The update to AUI 8.5.1 is not described in the documentation. Here is the header you need to use:

<meta charset="utf-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta name="ap-local-base-url" content="{{localBaseUrl}}">
<script src="{{hostScriptUrl}}" type="text/javascript"></script>
  <link rel="stylesheet" type="text/css" href=""/>
<script src=""></script>
<script src=""></script>
<script src=""></script>
<link rel="stylesheet" href="/css/addon.css" type="text/css" />

It’s simple once you know what to do.

How to manage and grow a technical remote team

When you need to grow your team, be it from just yourself or from a few collaborators, you need to make sure the new team members fit in. This is even more important if you grow a remote team.

My experience in growing smallish teams, mostly in software development and scientific collaboration, is positive. I think most technical teams can work remotely. And I also believe that hiring without being in the same room is possible for many roles.

How to manage a remote team? If you work on a project with much coordination, I recommend to have a short daily (video) standup. Just let every team member tell what they did the day before, and what they plan to do today. That works surprisingly well to keep everybody up to date. And this also creates accountability for each team member.

Remote work can be lonely. Some team members thrive in such an environment, whereas others prefer much direct communication. Just be aware of the personal team member preferences, and help each team member find a role that fits their personality.

Pair team members on tasks: For each major task, pair two team members to work on this task. As an example, for a web application, let at least two people work on the front end, and also at least two people on the back end. Pairing helps team members to learn from each other, and helps the team to keep knowledge if a team member leaves.

Hiring and getting new members into the team should be handled by a proven, standard process. Be prepared that some hires don’t work out! Sometimes it’s not your fault, nor the fault of the new hire. Maybe it’s just the interaction between the new hire and the old team. What worked very well for me is to hire new team members on a project base first. When such a first project works out, you can hire them longer term.

How to remove mojibake from mysql dump files

Sometimes a mysql dump file contains latin-1 text encoded wrongly as UTF-8 unicode encoding. That leads to some characters out of the ASCII range being garbled and displayed as two UTF-8 characters. This is called mojibake. E.g. “l’Oreal” for “L’Oreal”.
When you dump such a database using:

mysqldump --opt -h localhost -u dbuser -p db > dump.sql

You get the mojibake in this dump file.

How to correct the mojibake

Luckily there is a python3 tool called ftfy to remove mojibake and replace it with the garbled characters.  Ftfy is a nifty work of programming and educated guesswork, because ftfy must guess what the original encoding was, and which transformation to apply to the dump file.  To make this guessing work, ftfy uses a line by line approach to guessing any encoding mistakes.  However this approach does not work with standard mysqldump files, because they can contain extremely long lines. This causes the ftfy guessing algorithm to not work effectively, because the algorithm assumes that lines are not very long in order to guess if there are wrong character sequences in a line.

The right mysqldump

Reduce the length of the lines in mysqldump by using the command line parameter –skip-extended. This parameter writes multiple SQL INSERT statements per table, such that each INSERT is on a new line. The drawback is that dumping and restoring a database is slower.

mysqldump --opt --skip-extended -h localhost -u dbuser -p db > dump.sql

This dump still contains the mojibake and is now ready to be processed further.

Using ltfy

First install lftfy:

virtualenv -p python3 venv
source ./venv/bin/activate
pip install ftfy

Then prepare a file to call ftfy on your mysql dump file (with thanks to Pielo):

import ftfy
# Set input_file
input_file = open('dump.sql', 'r', encoding='utf-8')
# Set output file
output_file = open('dump.utf8.sql', 'w')

# Create fixed output stream
stream = ftfy.fix_file(

# Save stream to output file
stream_iterator = iter(stream)
while stream_iterator:
		line = next(stream_iterator)
	except StopIteration:

Then you just need to call:


Thereafter you can just restore the dump file into mysql:

mysql -h localhost -u dbuser -p db < backup.sql

Beware of duplicates introduced by removing mojibake in SQL

Sometimes removing the mojibake can result in duplicate rows in the database, even when there was a UNIQUE KEY constraint or UNIQUE index.  The reason is that different two character encodings for a single UTF-8 character can be recognized by ftfy.  This then leads to duplicate rows.

Normalize Python DB API calls between SQLite and MySQL/PGSQL

Somehow there is no single formatting for parameters/variables in python DB API calls.  Looking at the DB API specification, it seems that specific database drivers can be written  with ‘?’ or ‘%s’, or even other conventions.

qmarkQuestion mark style, e.g. ...WHERE name=?
numericNumeric, positional style, e.g. ...WHERE name=:1
namedNamed style, e.g. ...WHERE name=:name
formatANSI C printf format codes, e.g. ...WHERE name=%s
pyformatPython extended format codes, e.g. ...WHERE name=%(name)s

How do you know the ‘paramstyle’ of your database connection?  For SQLite use:

>>> import sqlite3
>>> sqlite3.paramstyle

Using a library like sqlalchemy circumvents this problem.  In case you need to use the DB API drivers, a simple function which formats SQL strings can be used to enhance portability. The function below does this. It is a quick 80% solution. Adapt as needed.

def fs(sql_string):
""" format sql string according to db engine used """
# normalize dbapi parameters, always use %s (MySQL, PG) in sql_string,
# set escape_string to r'?' for sqlite
# e.g. %s and ?
# escape_string = r'%s '
escape_string = r'? '
# autoincrement_string = "AUTO_INCREMENT" # for MySQL
autoincrement_string = "AUTOINCREMENT" # for sqlite
return_string = sql_string
return_string = re.sub(r'%s ', escape_string, return_string)
return_string = re.sub(r'AUTO_INCREMENT', autoincrement_string, return_string)
return return_string

It is possible (and likely) that the SQL for the DDL will be different between e.g. SQLite and MySQL.  For example, in MySQL you’d use ‘AUTO_INCREMENT’, whereas in SQLite you’d use ‘AUTOINCREMENT’.  By extending the above approach to also replacing these strings, you can further abstract the database code.