Skip to content

Open source, privacy-first web analytics. Lightweight, cookie-free Google Analytics alternative. Self-hosted or cloud.

22.3 KB
Raw Blame
defmodule Plausible.Billing.MigrationAudit do
  @moduledoc """
  Read-only audit report for the Paddle Classic->Billing migration.
  Paddle is only called when explicitly asked for with `catalog: true`,
  and then with read-only requests.

  ## Answering the audit asked for in the Paddle Billing project

  | Item                                            | Where                                                                                           |
  | ----------------------------------------------- | ----------------------------------------------------------------------------------------------- |
  | Active subscriptions by plan id                 | `plan_resolution/1`, `:by_plan_id`                                                              |
  | Plan generation                                 | `plan_resolution/1`, `:by_generation`                                                           |
  | Enterprise plans attached to live subscriptions | `plan_resolution/1`, `:enterprise_plans`                                                        |
  | Active, paused, trialing, past-due              | `subscribers/1`, `:statuses`                                                                    |
  | Plans with multiple currency prices             | `classic_catalog/1`, `:plans`                                                                   |
  | Customer-specific or negotiated plans           | `subscribers/1`, `:enterprise` and `:vs_list_price`; `classic_catalog/1`, `:plans` `:by_origin` |
  | Recurring or one-time modifiers                 | `classic_catalog/1`, `:modifiers`                                                               |
  | Effective currency and amount paid              | `subscribers/1`, `:billed` and `:vs_list_price`                                                 |
  """

  use Plausible.Repo

  import Ecto.Query, only: [from: 2, dynamic: 2]

  require Plausible.Billing.Subscription.Status

  alias Plausible.Billing.{EnterprisePlan, Plans, Subscription}
  alias Plausible.Billing.MigrationAudit.{ClassicCatalog, Subscribers}
  alias Plausible.Billing.Subscription.Status

  @plan_files [
    legacy_plans: 1,
    plans_v1: 1,
    plans_v2: 2,
    plans_v3: 3,
    plans_v4: 4,
    plans_v5: 5
  ]

  @classic_id_format ~r/^\d+$/

  @type category ::
          :free
          | :manual
          | :enterprise
          | :unresolved
          | {:regular, generation :: integer(), kind :: atom()}

  @type problem :: %{check: atom(), file: String.t() | nil, message: String.t()}

  @doc """
  bin/plausible rpc "IO.puts(Plausible.Billing.MigrationAudit.json_report())"

    or, from a remote shell:

  iex> IO.puts(Plausible.Billing.MigrationAudit.json_report())
  """
  @spec json_report(Keyword.t()) :: String.t()
  def json_report(opts \\ []) do
    opts
    |> Keyword.put_new(:catalog, true)
    |> collect()
    |> update_in([:plan_resolution, :unresolved], fn rows ->
      %{count: length(rows), rows: "left out, see plan_resolution/1"}
    end)
    |> to_json()
  end

  @spec collect(Keyword.t()) :: map()
  def collect(opts \\ []) do
    result = %{
      plan_resolution: plan_resolution(opts),
      plan_files: plan_files_integrity(opts),
      subscribers: subscribers(opts)
    }

    if Keyword.get(opts, :catalog, false) do
      Map.put(result, :classic_catalog, classic_catalog_result(opts))
    else
      result
    end
  end

  defp classic_catalog_result(opts) do
    case classic_catalog(opts) do
      {:ok, catalog} ->
        catalog

      {:error, {part, reason}} ->
        %{error: %{failed_call: part, reason: inspect(reason)}}
    end
  end

  @doc """
  Like `collect/1`, but prints a readable report, and returns the same data.
  """
  def run(opts \\ []) do
    result = collect(opts)

    print_plan_resolution(result.plan_resolution)
    print_plan_files(result.plan_files)
    print_subscribers(result.subscribers)

    case Map.fetch(result, :classic_catalog) do
      {:ok, catalog} ->
        print_classic_catalog(catalog)

      :error ->
        log("Classic catalog and modifiers skipped. Run with `catalog: true` to include them.")
    end

    result
  end

  @doc """
  Subscribers by status, effective amounts and enterprise plans. Database only,
  see `Plausible.Billing.MigrationAudit.Subscribers.audit/1` for the result and options.
  """
  defdelegate subscribers(opts \\ []), to: Subscribers, as: :audit

  @doc """
  Plan catalog and modifiers from Paddle Classic. Makes read-only API calls, see
  `Plausible.Billing.MigrationAudit.ClassicCatalog.audit/1` for the result and options.
  """
  defdelegate classic_catalog(opts \\ []), to: ClassicCatalog, as: :audit

  @doc """
  Encodes a result of this module as pretty JSON.
  """
  @spec to_json(term()) :: String.t()
  def to_json(result), do: Jason.encode!(result, pretty: true)

  # --- plan resolution ----------------------------------------------------------

  @doc """
  Resolves subscriptions to plans exactly like the app does
  (`Plausible.Billing.Plans.get_subscription_plan/1`) and summarises the result.

  Options:

    * `:scope` - `:live` (default) audits every subscription that could matter
      for a port: active, past due or paused, plus cancelled ones that are still
      inside the paid period. `:current` audits the last subscription of every
      team, which is the one the app uses to determine a team's plan.
    * `:today` - the date used for "still inside the paid period" (default:
      today, UTC).
  """
  @spec plan_resolution(Keyword.t()) :: map()
  def plan_resolution(opts \\ []) do
    scope = Keyword.get(opts, :scope, :live)
    today = Keyword.get(opts, :today, Date.utc_today())

    subscriptions = Repo.all(subscriptions_query(scope, today))
    classified = Enum.map(subscriptions, &{&1, classify(&1)})

    %{
      scope: scope,
      today: today,
      total: length(subscriptions),
      by_category: count_by(classified, fn {_s, category} -> category_label(category) end),
      by_plan_id: by_plan_id(classified),
      by_generation: by_generation(classified),
      unresolved:
        for {subscription, :unresolved} <- classified do
          %{
            subscription_id: subscription.id,
            team_id: subscription.team_id,
            paddle_plan_id: subscription.paddle_plan_id,
            status: subscription.status,
            next_bill_date: subscription.next_bill_date
          }
        end,
      teams_with_multiple_subscriptions: teams_with_multiple_subscriptions(today),
      enterprise_plans: enterprise_plans_summary(today)
    }
  end

  @doc """
  Classifies the plan of a single subscription.
  """
  @spec classify(Subscription.t()) :: category()
  def classify(%Subscription{} = subscription) do
    if Subscription.manual_subscription?(subscription) do
      :manual
    else
      case Plans.get_subscription_plan(subscription) do
        nil -> :unresolved
        :free_10k -> :free
        %EnterprisePlan{} -> :enterprise
        %{generation: generation, kind: kind} -> {:regular, generation, kind}
      end
    end
  end

  defp subscriptions_query(:live, today) do
    from(s in Subscription, where: ^live_condition(today), order_by: [asc: s.id])
  end

  defp subscriptions_query(:current, _today) do
    # one row per team: the last subscription, as in Teams.last_subscription_query/0
    from(s in Subscription,
      distinct: s.team_id,
      order_by: [asc: s.team_id, desc: s.inserted_at, desc: s.id]
    )
  end

  defp live_condition(today) do
    live = [Status.active(), Status.past_due(), Status.paused()]

    dynamic(
      [s],
      s.status in ^live or (s.status == ^Status.deleted() and s.next_bill_date >= ^today)
    )
  end

  defp count_by(items, fun) do
    items
    |> Enum.frequencies_by(fun)
    |> Enum.map(fn {category, n} -> %{category: category, subscriptions: n} end)
    |> Enum.sort_by(&{-&1.subscriptions, &1.category})
  end

  defp category_label({:regular, generation, kind}), do: "v#{generation} #{kind}"
  defp category_label(other), do: Atom.to_string(other)

  defp by_plan_id(classified) do
    classified
    |> Enum.group_by(fn {s, category} -> {s.paddle_plan_id, category_label(category)} end)
    |> Enum.map(fn {{plan_id, label}, items} ->
      %{paddle_plan_id: plan_id, category: label, subscriptions: length(items)}
    end)
    |> Enum.sort_by(&{-&1.subscriptions, &1.paddle_plan_id})
  end

  # For every generation of the static plans: how many live subscriptions and
  # distinct plan ids are in use, against how many plan ids the files contain.
  # This is the list of Billing prices that are actually needed per generation.
  defp by_generation(classified) do
    in_use =
      for {s, {:regular, generation, _kind}} <- classified, do: {generation, s.paddle_plan_id}

    ids_in_files =
      Plans.all()
      |> Enum.flat_map(fn plan ->
        for id <- [plan.monthly_product_id, plan.yearly_product_id],
            is_binary(id),
            do: {plan.generation, id}
      end)

    for generation <- ids_in_files |> Enum.map(&elem(&1, 0)) |> Enum.uniq() |> Enum.sort() do
      used = for {^generation, id} <- in_use, do: id

      %{
        generation: generation,
        subscriptions: length(used),
        distinct_plan_ids_in_use: used |> Enum.uniq() |> length(),
        plan_ids_in_files: for({^generation, _} <- ids_in_files, do: 1) |> length()
      }
    end
  end

  defp teams_with_multiple_subscriptions(today) do
    all =
      from(s in Subscription, group_by: s.team_id, having: count(s.id) > 1, select: s.team_id)

    live =
      from(s in Subscription,
        where: ^live_condition(today),
        group_by: s.team_id,
        having: count(s.id) > 1,
        select: s.team_id
      )

    %{any_status: length(Repo.all(all)), live: length(Repo.all(live))}
  end

  defp enterprise_plans_summary(today) do
    attached = fn condition ->
      from(ep in EnterprisePlan,
        as: :ep,
        where:
          exists(
            from(s in Subscription,
              where: s.team_id == parent_as(:ep).team_id,
              where: s.paddle_plan_id == parent_as(:ep).paddle_plan_id,
              where: ^condition
            )
          ),
        select: count(ep.id)
      )
      |> Repo.one()
    end

    %{
      total: Repo.aggregate(EnterprisePlan, :count),
      attached_to_live_subscription: attached.(live_condition(today)),
      attached_to_any_subscription: attached.(dynamic([s], true))
    }
  end

  @doc """
  Checks the static plan files and returns a list of problems (empty if all is
  well). Reads the files from the release's `priv` directory.

  Options:

    * `:priv_dir` - directory to read from (default: the app's `priv`).
  """
  @spec plan_files_integrity(Keyword.t()) :: [problem()]
  def plan_files_integrity(opts \\ []) do
    priv_dir = Keyword.get(opts, :priv_dir, Application.app_dir(:plausible, "priv"))

    files =
      for {name, _generation} <- @plan_files, into: %{} do
        file = Atom.to_string(name)
        {file, priv_dir |> Path.join("#{file}.json") |> read_json()}
      end

    prices = priv_dir |> Path.join("plan_prices.json") |> read_json()

    check_plan_files(files, prices)
  end

  defp read_json(path) do
    with {:ok, content} <- File.read(path),
         {:ok, decoded} <- Jason.decode(content) do
      decoded
    else
      {:error, %Jason.DecodeError{} = error} ->
        {:error, "#{Path.basename(path)}: invalid JSON (#{Exception.message(error)})"}

      {:error, reason} ->
        {:error, "#{Path.basename(path)}: #{:file.format_error(reason)}"}
    end
  end

  @doc """
  Pure part of `plan_files_integrity/1`, for testing and for use on data that
  isn't on disk.

  `files` maps a file name without extension (for example `"plans_v1"`) to its
  decoded JSON (a list of plan maps), `prices` is
  the decoded `plan_prices.json` (plan id => price). A value of
  `{:error, message}` stands for a file that couldn't be read.

  Checks:

    * `:unreadable` - a file is missing or isn't valid
    * `:generation_mismatch` - a plan's `generation` doesn't match its file
    * `:bad_id_format` - an id isn't a numeric (Paddle Classic) id
    * `:same_monthly_and_yearly_id` - a plan uses one id for both intervals
    * `:duplicate_id` - an id is used more than once
    * `:duplicate_plan` - two plans in a file share kind and pageview limit
    * `:missing_price` / `:orphan_price` / `:bad_price` - `plan_prices.json` has
      to hold a positive price for every plan id, and nothing else
  """
  @spec check_plan_files(map(), map() | {:error, String.t()}) :: [problem()]
  def check_plan_files(files, prices) do
    {readable, unreadable} =
      Enum.split_with(files, fn {_name, content} -> is_list(content) end)

    readable = Map.new(readable)

    unreadable_problems =
      for {name, {:error, message}} <- unreadable do
        problem(:unreadable, name, message)
      end

    prices_problems =
      case prices do
        {:error, message} -> [problem(:unreadable, "plan_prices.json", message)]
        prices when is_map(prices) -> check_prices(readable, prices)
      end

    unreadable_problems ++
      Enum.flat_map(readable, fn {name, plans} -> check_file(name, plans) end) ++
      check_duplicate_ids(readable) ++
      prices_problems
  end

  defp problem(check, file, message), do: %{check: check, file: file, message: message}

  defp file_generation(name) do
    Keyword.fetch!(@plan_files, String.to_existing_atom(name))
  end

  defp plan_ids(plan) do
    for key <- ["monthly_product_id", "yearly_product_id"],
        id = plan[key],
        not is_nil(id),
        do: {key, id}
  end

  defp check_file(name, plans) do
    generation = file_generation(name)

    per_plan =
      plans
      |> Enum.with_index(1)
      |> Enum.flat_map(fn {plan, index} ->
        label = "#{name} ##{index} (#{plan["kind"]}, #{plan["monthly_pageview_limit"]})"

        generation_problems =
          if plan["generation"] != generation do
            [
              problem(
                :generation_mismatch,
                name,
                "#{label}: generation is #{inspect(plan["generation"])}, expected #{generation}"
              )
            ]
          else
            []
          end

        id_problems =
          for {key, id} <- plan_ids(plan),
              not (is_binary(id) and Regex.match?(@classic_id_format, id)) do
            problem(:bad_id_format, name, "#{label}: #{key} #{inspect(id)} isn't a numeric id")
          end

        same_id_problems =
          case plan_ids(plan) do
            [{_, id}, {_, id}] ->
              [
                problem(
                  :same_monthly_and_yearly_id,
                  name,
                  "#{label}: monthly and yearly both use #{inspect(id)}"
                )
              ]

            _ ->
              []
          end

        generation_problems ++ id_problems ++ same_id_problems
      end)

    duplicate_plans =
      plans
      |> Enum.frequencies_by(&{&1["kind"], &1["monthly_pageview_limit"]})
      |> Enum.filter(fn {_key, count} -> count > 1 end)
      |> Enum.map(fn {{kind, limit}, count} ->
        problem(
          :duplicate_plan,
          name,
          "#{count} plans with kind #{inspect(kind)} and limit #{inspect(limit)}"
        )
      end)

    per_plan ++ duplicate_plans
  end

  defp check_duplicate_ids(files) do
    for {id, owners} <- ids_with_owners(files), length(owners) > 1 do
      problem(
        :duplicate_id,
        nil,
        "#{inspect(id)} is used #{length(owners)} times (#{Enum.join(owners, ", ")})"
      )
    end
  end

  defp ids_with_owners(files) do
    files
    |> Enum.flat_map(fn {name, plans} ->
      for plan <- plans, {key, id} <- plan_ids(plan), do: {id, "#{name} #{key}"}
    end)
    |> Enum.group_by(&elem(&1, 0), &elem(&1, 1))
  end

  defp check_prices(files, prices) do
    known_ids =
      for {_name, plans} <- files,
          plan <- plans,
          {_key, id} <- plan_ids(plan),
          into: MapSet.new(),
          do: id

    missing =
      for id <- known_ids, not Map.has_key?(prices, id) do
        problem(:missing_price, "plan_prices.json", "no price for plan id #{inspect(id)}")
      end

    orphans =
      for {id, _price} <- prices, not MapSet.member?(known_ids, id) do
        problem(:orphan_price, "plan_prices.json", "price for unknown plan id #{inspect(id)}")
      end

    bad =
      for {id, price} <- prices, not (is_number(price) and price > 0) do
        problem(
          :bad_price,
          "plan_prices.json",
          "price for #{inspect(id)} is #{inspect(price)}, expected a positive number"
        )
      end

    Enum.sort_by(missing ++ orphans ++ bad, & &1.message)
  end

  # --- printing ------------------------------------------------------------------------

  defp print_plan_resolution(result) do
    log("Subscription plan resolution (scope: #{result.scope}, as of #{result.today})")
    log("  subscriptions audited: #{result.total}")

    log("  by category:")
    for row <- result.by_category, do: log("    #{pad(row.category, 22)} #{row.subscriptions}")

    log("  by generation (regular plans only):")

    for g <- result.by_generation do
      log(
        "    v#{g.generation}: #{g.subscriptions} subscriptions on " <>
          "#{g.distinct_plan_ids_in_use} of #{g.plan_ids_in_files} plan ids"
      )
    end

    log("  by plan id (top 15):")

    for row <- Enum.take(result.by_plan_id, 15) do
      log("    #{pad(row.paddle_plan_id, 22)} #{pad(row.category, 16)} #{row.subscriptions}")
    end

    multiple = result.teams_with_multiple_subscriptions

    log(
      "  teams with more than one subscription row: #{multiple.any_status} " <>
        "(#{multiple.live} with more than one live row)"
    )

    enterprise = result.enterprise_plans

    log(
      "  enterprise plans: #{enterprise.total} total, " <>
        "#{enterprise.attached_to_live_subscription} attached to a live subscription, " <>
        "#{enterprise.attached_to_any_subscription} attached to any subscription"
    )

    case result.unresolved do
      [] ->
        log("  unresolved subscriptions: none")

      unresolved ->
        log("  UNRESOLVED subscriptions (#{length(unresolved)}), these lose their plan limits:")

        for row <- unresolved do
          log(
            "    subscription ##{row.subscription_id} team ##{row.team_id} " <>
              "plan #{inspect(row.paddle_plan_id)} #{row.status} next_bill_date=#{row.next_bill_date}"
          )
        end
    end
  end

  defp print_plan_files([]), do: log("Plan files: no problems found")

  defp print_plan_files(problems) do
    log("Plan files: #{length(problems)} problem(s)")

    for p <- problems do
      log("  [#{p.check}] #{p.file || "-"}: #{p.message}")
    end
  end

  defp print_subscribers(result) do
    log("Subscribers (as of #{result.as_of})")
    statuses = result.statuses
    log("  current subscription of #{statuses.teams_with_subscription} teams, by status:")
    for row <- statuses.by_status, do: log("    #{pad(row.status, 22)} #{row.teams}")
    log("  teams in the app's own trial without a subscription: #{statuses.teams_on_app_trial}")

    log("  paid by live subscribers (kind, currency, interval):")

    for row <- result.billed do
      log(
        "    #{pad(row.kind, 11)} #{pad(row.currency, 4)} #{pad(row.interval, 8)} " <>
          "#{pad(row.subscriptions, 7)} total #{row.total} (min #{row.min}, max #{row.max})" <>
          if(row.unparsable_amounts > 0,
            do: ", #{row.unparsable_amounts} unparsable",
            else: ""
          )
      )
    end

    log("  regular plans, amount paid vs the USD list price:")

    for row <- result.vs_list_price.by_currency do
      log(
        "    #{pad(row.currency, 4)} #{row.subscriptions} subscriptions: #{row.equal} equal, " <>
          "#{row.lower} lower, #{row.higher} higher, #{row.no_list_price} without list price, " <>
          "#{row.unparsable} unparsable"
      )
    end

    log("  most common off-list price points (top 10):")

    for row <- Enum.take(result.vs_list_price.off_list_price_points, 10) do
      log(
        "    plan #{pad(row.plan_id, 8)} #{row.currency} #{pad(row.interval, 8)} paid #{row.paid} " <>
          "vs list #{row.list_price}: #{row.subscriptions}"
      )
    end

    enterprise = result.enterprise

    log(
      "  enterprise: #{enterprise.live_subscriptions} live subscriptions on " <>
        "#{enterprise.distinct_plan_ids} plan ids, #{enterprise.distinct_price_points} distinct price points"
    )

    log(
      "    plan ids shared by several teams: #{enterprise.plan_ids_shared_by_several_teams}, " <>
        "teams with several enterprise plans: #{enterprise.teams_with_several_enterprise_plans}"
    )

    log("    top price points:")

    for row <- Enum.take(enterprise.top_price_points, 10) do
      log(
        "      #{row.currency} #{pad(row.interval, 8)} #{pad(row.amount, 10)} #{row.subscriptions}"
      )
    end
  end

  defp print_classic_catalog(%{error: %{failed_call: part, reason: reason}}) do
    log("Classic catalog: failed to fetch #{part}: #{reason}")
  end

  defp print_classic_catalog(%{plans: plans, modifiers: modifiers}) do
    log("Classic catalog: #{plans.total} plans")
    log("  by origin: #{inspect(plans.by_origin)}")

    log(
      "  with a live subscription, by origin: #{inspect(plans.with_live_subscription_by_origin)}"
    )

    log(
      "  single currency: #{plans.single_currency}, multiple currencies: #{plans.multi_currency} " <>
        "(#{plans.multi_currency_parity} at parity, #{plans.multi_currency_differing} differing)"
    )

    log("  multiple currencies by origin: #{inspect(plans.multi_currency_by_origin)}")

    log("  without a recurring price: #{plans.without_recurring_price}")

    for row <- plans.currency_sets do
      log("    currencies #{Enum.join(row.currencies, ",")}: #{row.plans} plans")
    end

    case plans.live_plan_ids_missing_from_catalog do
      [] -> log("  every live plan id is in the catalog")
      ids -> log("  live plan ids missing from the catalog: #{Enum.join(ids, ", ")}")
    end

    log(
      "Modifiers: #{modifiers.total} (#{modifiers.recurring} recurring, #{modifiers.one_time} one-time, " <>
        "#{modifiers.recurrence_unknown} unknown) on #{modifiers.subscriptions_with_modifiers} subscriptions, " <>
        "#{modifiers.of_which_live} live"
    )

    log("  discounts: #{modifiers.discounts}, surcharges: #{modifiers.surcharges}")
    log("  #{modifiers.not_covered}")
  end

  defp pad(value, width), do: value |> to_string() |> String.pad_trailing(width)

  defp log(message), do: IO.puts("[#{inspect(__MODULE__)}] #{message}")
end