neiam /gitgud

defmodule GitGud.Repo.Migrations.ScopeSigningKeysToNamespace do
  use Ecto.Migration

  @moduledoc """
  Move package signing keys from the repository to the namespace that
  owns it — the organization, or the user for a personal repository.

  Per-repository keys were wrong in the way that only shows up on the
  consuming end. A machine installing from five repositories in one
  organization needed five entries in `/etc/apt/keyrings` and five
  `signed-by=` clauses, for what is, to anyone reading the sources
  list, one publisher. The key is a *trust anchor for a publisher*,
  and the publisher is the namespace.

  One key per namespace per format, so `organization_id` and `user_id`
  are both nullable and exactly one is set — enforced by a check
  constraint rather than by convention, because a row with neither
  would be a key nothing can ever find again.

  Existing rows are folded onto their namespace, oldest first. Where
  two repositories in the same namespace had each generated a key, the
  younger one loses: the older has had more time to be pinned by a
  client, and breaking the newer pin is the smaller harm.
  """

  def up do
    alter table(:package_signing_keys) do
      add :organization_id, references(:organizations, on_delete: :delete_all)
      add :user_id, references(:users, on_delete: :delete_all)
    end

    execute """
    UPDATE package_signing_keys k
       SET organization_id = r.organization_id,
           user_id = CASE WHEN r.organization_id IS NULL THEN r.owner_id END
      FROM repositories r
     WHERE r.id = k.repository_id
    """

    # Fold duplicates onto one key per namespace, keeping the oldest.
    execute """
    DELETE FROM package_signing_keys k
     USING package_signing_keys other
     WHERE k.format = other.format
       AND k.id > other.id
       AND (
             (k.organization_id IS NOT NULL AND k.organization_id = other.organization_id)
          OR (k.user_id IS NOT NULL AND k.user_id = other.user_id)
       )
    """

    # Anything that couldn't be attributed to a namespace has no way to
    # be looked up again; it is not a key, it is a leak.
    execute "DELETE FROM package_signing_keys WHERE organization_id IS NULL AND user_id IS NULL"

    drop unique_index(:package_signing_keys, [:repository_id, :format])

    alter table(:package_signing_keys) do
      remove :repository_id
    end

    create unique_index(:package_signing_keys, [:organization_id, :format],
             where: "organization_id IS NOT NULL",
             name: :package_signing_keys_organization_format_index
           )

    create unique_index(:package_signing_keys, [:user_id, :format],
             where: "user_id IS NOT NULL",
             name: :package_signing_keys_user_format_index
           )

    create constraint(:package_signing_keys, :package_signing_keys_one_owner,
             check: "(organization_id IS NULL) <> (user_id IS NULL)"
           )
  end

  def down do
    drop constraint(:package_signing_keys, :package_signing_keys_one_owner)

    drop index(:package_signing_keys, [:user_id, :format],
           name: :package_signing_keys_user_format_index
         )

    drop index(:package_signing_keys, [:organization_id, :format],
           name: :package_signing_keys_organization_format_index
         )

    # There is no faithful way back: one namespace key stood in for
    # every repository under it, and which repository should now own it
    # is not recoverable. Start clean rather than guess.
    execute "DELETE FROM package_signing_keys"

    alter table(:package_signing_keys) do
      add :repository_id, references(:repositories, on_delete: :delete_all), null: false
      remove :organization_id
      remove :user_id
    end

    create unique_index(:package_signing_keys, [:repository_id, :format])
  end
end