Remove routes.source_type / routes.source_id in favour of individual columns

Currently the routes table has these two columns: source_type and source_id. The source_type column can either be Namespace, or Project.

Because there are two columns this means you need to use both when filtering routes. For example:

SELECT *
FROM routes
WHERE source_type = 'Project'
AND source_id = 13083

Combining such a query with other conditions and JOINs can lead to less than ideal query plan. Another problem is that we're storing string values in source_type, with no limitation on the length. This means we're wasting both disk and index space.

We need to adjust the routes table so these two columns are replaced with the following columns:

• namespace_id for namespace routes
• project_id for project routes

This allows changing the above query to:

SELECT *
FROM routes
WHERE project_id = 13083

This also optimizes index and disk usage because we can now store integer values, instead of string values. Finally, this allows us to set up foreign keys. This ensures a route can not point to orphaned data, and routes are removed automatically when their source object is removed (if desired).

This does mean that we need to add 1 column per source type. This however is still much better than the existing setup. Besides, it's unlikely we'll add many more over time (I don't see us using more than a handful of source types).

cc @dzaporozhets @smcgivern @rspeicher @DouweM