Migration helpers for creating Spector event tables.

Basic Usage

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

  def up, do: Spector.Migration.up(table: "events")
  def down, do: Spector.Migration.down(table: "events")
end

Options

• :table (required) - The database table name for the events
• :shards - List of table names for sharded setups (creates multiple tables)
• :hashed - Add a hash column for hash chain integrity (default: false)
• :links - List of link tables to create: [{"table_name", :foreign_key}, ...]

With Table Sharding

For sharded event tables, use :shards to create multiple tables:

def up do
  Spector.Migration.up(shards: ["events_0", "events_1", "events_2", "events_3"])
end

def down do
  Spector.Migration.down(shards: ["events_0", "events_1", "events_2", "events_3"])
end

When combining shards with links, a link table is created for each shard by concatenating the shard name to the link table name. For example:

Spector.Migration.up(
  shards: ["events_0", "events_1"],
  links: [{"ancestors", :ancestor_id}]
)

This creates four tables: events_0, events_1, events_0_ancestors, and events_1_ancestors. If you need a different naming convention, omit the :links option and use link_up/2 separately for each shard.

With Hash Chain Integrity

If using hashed events, include the :hashed option:

def up, do: Spector.Migration.up(table: "events", hashed: true)
def down, do: Spector.Migration.down(table: "events")

With Event Links

To create join tables for event linking (e.g., ancestry tracking):

def up do
  Spector.Migration.up(
    table: "events",
    links: [{"event_ancestors", :ancestor_id}]
  )
end

def down do
  Spector.Migration.down(
    table: "events",
    links: [{"event_ancestors", :ancestor_id}]
  )
end

Each link tuple creates a join table with event_id and the specified foreign key, along with indexes for efficient queries. A database trigger enforces that both ends of a link must have the same parent_id (i.e., links can only connect events within the same record).

Typed Link Tables

To distinguish different relationship types on the same link table, use the :typed option to add a type integer column:

def up do
  Spector.Migration.up(
    table: "events",
    links: [{"event_links", :linked_id, typed: true}]
  )
end

The type column allows a single link table to represent multiple relationship types (e.g., "parent", "sibling", "reference") by assigning each type an integer value.

It is recommended to use an Ecto schema for typed link tables. See the Event Links Guide for examples.

Generated Schema

The migration creates a table with:

• id - UUIDv7 primary key
• parent_id - Foreign key reference to the first event (self-referential)
• payload - Map/JSONB column storing the event data
• schema - Integer identifying which schema this event belongs to
• action - Integer identifying the action (insert, update, delete, or custom)
• hash - Binary column for SHA-256 hash (only if hashed: true)
• inserted_at, updated_at - Timestamps with microsecond precision

Indexes are created on schema and parent_id for efficient queries.