Adds partial unique indexes preventing duplicate trials per experiment.

A trial has either a session_id or a user_id (never both, unless linked), so the indexes are partial (WHERE ... IS NOT NULL) and keep both columns nullable.

Host DBs that may already contain duplicate trials must run deduplicate_trials/0 FIRST, or this migration will fail.

Options

• :concurrently - when true, builds the indexes with CREATE INDEX CONCURRENTLY so the build does not take an ACCESS EXCLUSIVE lock that blocks writes. Defaults to false. Required for large production tables.

Simple usage (small tables / dev — runs inside one transaction)

defmodule MyApp.Repo.Migrations.ExAbbyTrialUniqueness do
  use Ecto.Migration

  def up do
    ExAbby.Migrations.deduplicate_trials()
    ExAbby.Migrations.add_trial_uniqueness()
  end

  def down, do: ExAbby.Migrations.remove_trial_uniqueness()
end

Production usage (large tables) — split into TWO migrations

CREATE INDEX CONCURRENTLY cannot run inside a transaction, so the dedup (which must stay transactional) and the concurrent index build must be separate migrations:

# 1. _dedup migration (transactional — default)
defmodule MyApp.Repo.Migrations.ExAbbyDedupTrials do
  use Ecto.Migration
  def up, do: ExAbby.Migrations.deduplicate_trials()
  def down, do: :ok
end

# 2. _uniqueness migration (concurrent — no transaction/lock)
defmodule MyApp.Repo.Migrations.ExAbbyTrialUniqueness do
  use Ecto.Migration
  @disable_ddl_transaction true
  @disable_migration_lock true

  def up, do: ExAbby.Migrations.add_trial_uniqueness(concurrently: true)
  def down, do: ExAbby.Migrations.remove_trial_uniqueness(concurrently: true)
end

Merges duplicate trials so add_trial_uniqueness/0 can run safely.

For each (experiment_id, session_id) and (experiment_id, user_id) group, success counts/amounts are summed onto the lowest-id surviving row, the earliest non-null success dates are kept, and the higher-id duplicate rows are deleted. Run this BEFORE add_trial_uniqueness/0 on any DB that may already hold duplicates.

Removes the partial unique indexes added by add_trial_uniqueness/1.

Accepts the same :concurrently option so a concurrent build can be reversed with a concurrent drop (required when the migration sets @disable_ddl_transaction true).

Upgrade from v1 to v2 (adds archive fields if they don't exist).

Usage in host app migration:

defmodule MyApp.Repo.Migrations.ExAbbyV2 do
  use Ecto.Migration
  def up, do: ExAbby.Migrations.v1_to_v2()
  def down, do: ExAbby.Migrations.v2_to_v1()
end

Downgrade from v2 to v1 (removes archive fields).