"id" fields in goose_db_version are not unique

[kevinburke]

If you run "goose down" goose will write a new row to the database with is_applied set to false, so there will be two rows for the same version; one with is_applied true and a newer one with is_applied set to false.

The key to determining the application state is to look at the most recent row for each version. You likely want a query like this:

WITH most_recent_migration AS (
	SELECT DISTINCT ON (version_id) version_id, is_applied, id 
	FROM goose_db_version 
	ORDER BY version_id, id DESC
)
SELECT version_id FROM most_recent_migration
WHERE is_applied = true
ORDER BY id DESC

This is a bad pattern because it can easily lead to errors, however I'm not sure what to do about it.