track_indices

Document and audit database indices in your Ecto schemas.

track_indices provides a simple way to:

• Document database indices directly in your schema modules
• Automatically verify that your documentation matches actual database indices
• Catch missing or undocumented indices in CI/CD pipelines

Installation

Add track_indices to your list of dependencies in mix.exs:

def deps do
  [
    {:track_indices, "~> 0.1.0"}
  ]
end

Configuration

Add to your config/config.exs:

config :track_indices, repo: YourApp.Repo

Usage

1. Document Indices in Your Schemas

Add use TrackIndices to your schema modules and document indices with @indices:

defmodule MyApp.Accounts.User do
  use Ecto.Schema
  use TrackIndices

  @indices [
    %{columns: [:email], unique: true},
    %{columns: [:inserted_at], unique: false}
  ]

  schema "users" do
    field :email, :string
    field :name, :string

    timestamps()
  end
end

2. Run the Audit

Check that your documented indices match the database:

mix track_indices.audit

Example output:

=== Index Audit ===

Table: users
Table: posts
Table: comments

=== Summary ===
✅ Matching: 15
⚠️  Mismatches: 0
➕ Extra indices (in DB, not documented): 0
➖ Missing indices (documented, not in DB): 0

✅ Audit passed: All indices match documentation

3. List All Documented Indices

View all documented indices across your schemas:

mix track_indices.list

Filter by table:

mix track_indices.list --table users

Verbose output:

mix track_indices.list --verbose

4. Use in CI/CD

Add to your CI pipeline to catch index mismatches:

# .github/workflows/ci.yml
- name: Check database indices
  run: mix track_indices.audit

The task exits with code 1 if there are any mismatches, failing your CI build.

Index Documentation Format

Each index is documented as a map with the following keys:

• :columns - List of column names (atoms) - required
• :unique - Boolean indicating if the index is unique - required
• :where - Partial index condition as a string - optional

Examples

Basic Index

@indices [
  %{columns: [:user_id], unique: false}
]

Unique Index

@indices [
  %{columns: [:email], unique: true}
]

Composite Index

@indices [
  %{columns: [:user_id, :post_id], unique: true}
]

Partial Index

@indices [
  %{columns: [:email], unique: true, where: "email IS NOT NULL"}
]

Complete Example

defmodule MyApp.Events.Invitation do
  use Ecto.Schema
  use TrackIndices

  @indices [
    # Foreign key lookups
    %{columns: [:event_id], unique: false},
    %{columns: [:user_id], unique: false},

    # Unique constraints
    %{columns: [:email, :event_id], unique: true, where: "email IS NOT NULL"},

    # Query optimization
    %{columns: [:status, :created_at], unique: false}
  ]

  schema "invitations" do
    field :email, :string
    field :status, :string

    belongs_to :event, MyApp.Events.Event
    belongs_to :user, MyApp.Accounts.User

    timestamps()
  end
end

Commands

mix track_indices.audit

Audits database indices against schema documentation.

Options:

• --verbose - Show detailed information about each index

Example:

mix track_indices.audit --verbose

Output includes:

• Index names
• Column lists
• Unique constraints
• Partial index conditions
• Comparison results

Exit codes:

• 0 - All indices match documentation
• 1 - Mismatches found

mix track_indices.list

Lists all documented indices.

Options:

• --table TABLE_NAME - Filter by specific table
• --verbose - Show detailed information

Example:

mix track_indices.list --table users --verbose

Limitations

• Currently only supports PostgreSQL
• Does not audit index types (B-tree, GiST, etc.)
• Does not audit index storage parameters